public function setUp()
    {
        dbsteward::set_sql_format('mysql5');
        dbsteward::$quote_schema_names = TRUE;
        dbsteward::$quote_table_names = TRUE;
        dbsteward::$quote_column_names = TRUE;
        dbsteward::$quote_function_names = TRUE;
        dbsteward::$quote_object_names = TRUE;
        mysql5::$use_auto_increment_table_options = FALSE;
        mysql5::$use_schema_name_prefix = FALSE;
        $xml = <<<XML
<dbsteward>
  <database>
    <host>db-host</host>
    <name>dbsteward</name>
    <role>
      <application>dbsteward_phpunit_app</application>
      <owner>deployment</owner>
      <replication/>
      <readonly/>
    </role>
  </database>
</dbsteward>
XML;
        $this->dbdoc = new SimpleXMLElement($xml);
    }
 /**
  * This works differently than sql99_diff_indexes::diff_indexes_table because of the way
  * mysql5 handles indexes on foreign keys. If we DROP then CREATE an index being used in an FK
  * in separate statements, like we do for other formats, MySQL errors because an FK constraint
  * relies on that index. If you DROP and CREATE in the same statement, though, MySQL is happy.
  * So, just mash all the index changes for each table into a single ALTER TABLE and be done with it.
  */
 public static function diff_indexes_table($ofs, $old_schema, $old_table, $new_schema, $new_table)
 {
     $bits = self::diff_indexes_table_bits($old_schema, $old_table, $new_schema, $new_table);
     if (!empty($bits)) {
         $ofs->write('ALTER TABLE ' . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']) . "\n  " . implode(",\n  ", $bits) . ";\n\n");
     }
 }
 public function setUp()
 {
     dbsteward::set_sql_format('mysql5');
     dbsteward::$quote_schema_names = TRUE;
     dbsteward::$quote_table_names = TRUE;
     dbsteward::$quote_column_names = TRUE;
     mysql5::$use_auto_increment_table_options = FALSE;
     mysql5::$use_schema_name_prefix = FALSE;
 }
Beispiel #4
0
 /**
  * Returns full definition of the column.
  *
  * @param add_defaults whether default value should be added in case NOT
  *        NULL constraint is specified but no default value is set
  *
  * @return full definition of the column
  */
 public static function get_full_definition($db_doc, $node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_auto_increment = false)
 {
     // ignore AUTO_INCREMENT flags for now
     $is_auto_increment = static::is_auto_increment($node_column['type']);
     $orig_type = (string) $node_column['type'];
     $node_column['type'] = static::un_auto_increment($node_column['type']);
     $column_type = static::column_type($db_doc, $node_schema, $node_table, $node_column);
     $definition = mysql5::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
     $nullable = static::null_allowed($node_table, $node_column);
     $is_timestamp = static::is_timestamp($node_column);
     if ($include_null_definition) {
         if ($nullable) {
             if ($is_timestamp) {
                 $definition .= " NULL";
             }
         } else {
             $definition .= " NOT NULL";
         }
     }
     if ($include_auto_increment && $is_auto_increment) {
         $definition .= " AUTO_INCREMENT";
     }
     if (strlen($node_column['default']) > 0) {
         if (static::is_serial($node_column['type'])) {
             $note = "Ignoring default '{$node_column['default']}' on {$node_schema['name']}.{$node_table['name']}.{$node_column['name']} because it is a serial type";
             dbsteward::warning($note . "\n");
         } else {
             $definition .= " DEFAULT " . $node_column['default'];
         }
     } else {
         if ($add_defaults && $is_timestamp) {
             if ($nullable) {
                 $definition .= " DEFAULT NULL";
             } else {
                 $definition .= " DEFAULT CURRENT_TIMESTAMP";
             }
         } else {
             if (!$nullable && $add_defaults) {
                 $default_col_value = self::get_default_value($node_column['type']);
                 if ($default_col_value != null) {
                     $definition .= " DEFAULT " . $default_col_value;
                 }
             }
         }
     }
     if (strlen($node_column['description']) > 0) {
         $definition .= " COMMENT " . mysql5::quote_string_value($node_column['description']);
     }
     // restore the original type of the column
     $node_column['type'] = $orig_type;
     return $definition;
 }
 public function setUp()
 {
     dbsteward::set_sql_format('mysql5');
     dbsteward::$quote_schema_names = TRUE;
     dbsteward::$quote_table_names = TRUE;
     dbsteward::$quote_column_names = TRUE;
     mysql5::$swap_function_delimiters = FALSE;
     mysql5::$use_auto_increment_table_options = FALSE;
     mysql5::$use_schema_name_prefix = FALSE;
     $this->config = $GLOBALS['db_config']->mysql5_config;
     $this->connect();
     $this->setup_shim();
 }
 protected function extract($sql)
 {
     $schemaname = __CLASS__;
     $sql = rtrim($sql, ';');
     $sql = "DROP DATABASE IF EXISTS {$schemaname};\nCREATE DATABASE {$schemaname};\nUSE {$schemaname};\n{$sql};";
     $this->query($sql);
     $xml = mysql5::extract_schema($this->conn->get_dbhost(), $this->conn->get_dbport(), $schemaname, $this->conn->get_dbuser(), $this->conn->get_dbpass());
     $dbdoc = simplexml_load_string($xml);
     foreach ($dbdoc->schema as $schema) {
         if (strcmp($schema['name'], $schemaname) == 0) {
             if (static::DEBUG) {
                 echo "Got schema:\n" . $schema->asXML() . "\n";
             }
             return $schema;
         }
     }
     echo $dbdoc->asXML() . "\n";
     throw new exception("No schema named {$schemaname} was found!?");
 }
 public static function get_drop_sql($node_schema, $node_trigger)
 {
     if (strcasecmp($node_trigger['sqlFormat'], dbsteward::get_sql_format())) {
         $note = "Ignoring {$node_trigger['sqlFormat']} trigger '{$node_trigger['name']}'";
         dbsteward::warning($note);
         return "-- {$note}\n";
     }
     $events = self::get_events($node_trigger);
     if (count($events) == 1) {
         return "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name']) . ";\n";
     } else {
         $ddl = "";
         foreach ($events as $event) {
             if ($event = self::validate_event($event)) {
                 $ddl .= "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . "_{$event}") . ";\n";
             }
         }
         return $ddl;
     }
 }
    public function setUp()
    {
        dbsteward::set_sql_format('mysql5');
        dbsteward::$quote_schema_names = TRUE;
        dbsteward::$quote_table_names = TRUE;
        dbsteward::$quote_column_names = TRUE;
        dbsteward::$quote_function_names = TRUE;
        dbsteward::$quote_object_names = TRUE;
        mysql5::$use_auto_increment_table_options = FALSE;
        mysql5::$use_schema_name_prefix = FALSE;
        $db_doc_xml = <<<XML
<dbsteward>
  <database>
    <role>
      <owner>the_owner</owner>
      <customRole>SOMEBODY</customRole>
    </role>
  </database>
</dbsteward>
XML;
        dbsteward::$new_database = new SimpleXMLElement($db_doc_xml);
    }
    public function testCreateTable()
    {
        $xml = <<<XML
<schema name="public" owner="NOBODY">
  <table name="test" primaryKey="id" owner="NOBODY" description="test description">
    <tableOption sqlFormat="mysql5" name="auto_increment" value="5"/>
    <tableOption sqlFormat="mysql5" name="engine" value="InnoDB"/>
    <tableOption sqlFormat="pgsql8" name="inherits" value="other"/>
    <column name="id" type="int"/>
    <column name="foo" type="int"/>
  </table>
</schema>
XML;
        $schema = new SimpleXMLElement($xml);
        mysql5::$use_auto_increment_table_options = TRUE;
        $expected = <<<SQL
CREATE TABLE `test` (
  `id` int,
  `foo` int
)
AUTO_INCREMENT=5
ENGINE=InnoDB
COMMENT 'test description';
SQL;
        $actual = mysql5_table::get_creation_sql($schema, $schema->table);
        $this->assertEquals($expected, $actual);
        mysql5::$use_auto_increment_table_options = FALSE;
        $expected = <<<SQL
CREATE TABLE `test` (
  `id` int,
  `foo` int
)
ENGINE=InnoDB
COMMENT 'test description';
SQL;
        $actual = mysql5_table::get_creation_sql($schema, $schema->table);
        $this->assertEquals($expected, $actual);
    }
 public static function get_permission_sql($db_doc, $node_schema, $node_object, $node_permission, $action = 'grant')
 {
     if (strcasecmp($node_permission->getName(), 'grant') != 0 && strcasecmp($node_permission->getName(), 'revoke') != 0) {
         throw new exception("Cannot extract permission rights from node that is not grant or revoke");
     }
     if (!isset($node_permission['operation']) || strlen($node_permission['operation']) == 0) {
         throw new exception("node_permission operation definition is empty");
     }
     $object_name = '';
     $object_type = strtoupper($node_object->getName());
     $privileges = array_map(function ($p) use($object_type) {
         return mysql5_permission::get_real_privilege($p, $object_type);
     }, static::get_permission_privileges($node_permission));
     $roles = static::get_permission_roles($db_doc, $node_permission);
     $with = static::get_permission_options_sql($node_permission);
     switch ($object_type) {
         case 'SCHEMA':
             // all tables on current database, because no schemas
             $object_name = '*';
             break;
         case 'VIEW':
             return "-- Ignoring permissions on view '{$node_object['name']}' because MySQL uses SQL SECURITY DEFINER semantics\n";
         case 'TABLE':
             $object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_object['name']);
             break;
         case 'FUNCTION':
             $object_name = "FUNCTION " . mysql5::get_fully_qualified_object_name($node_schema['name'], $node_object['name'], 'function');
             break;
         case 'SEQUENCE':
             // sequences exist as rows in a table for mysql
             $object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], mysql5_sequence::TABLE_NAME);
             break;
         default:
             throw new exception("unknown object type encountered: " . $object_type);
     }
     $sql = static::get_sql(strtoupper($action), $object_name, $privileges, array_map('mysql5::get_quoted_object_name', $roles), $with) . "\n";
     return $sql;
 }
 /**
  * @group mysql5
  */
 public function testUpgradeNewTableMysql5()
 {
     $this->apply_options_mysql5();
     $this->setup_mysql5();
     // upgrade from base
     // to base + strict action table + new resolution table
     // check null specificity
     $base_db_doc = xml_parser::xml_composite(array($this->xml_file_a));
     $newtable_db_doc = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_b, $this->xml_file_c));
     mysql5::build_upgrade('', 'newtable_upgrade_test_pgsql8_base', $base_db_doc, array(), $this->output_prefix, 'newtable_upgrade_test_pgsql8_newtable', $newtable_db_doc, array());
     $text = file_get_contents($this->output_prefix . '_upgrade_single_stage.sql');
     // make sure NOT NULL is specified for description column
     $this->assertContains('`description` character varying(200) NOT NULL', $text);
     // make sure NOT NULL is specified for resolution column
     $this->assertContains('`resolution` character varying(16) NOT NULL', $text);
     // make sure NOT NULL is NOT specified for points column
     $this->assertNotContains('`points` int NOT NULL', $text);
 }
    public function testDropSchemaWithObjects()
    {
        mysql5::$use_schema_name_prefix = TRUE;
        $old = <<<XML
<schema name="s1" owner="NOBODY">
  <table name="table1" owner="NOBODY" primaryKey="col1">
    <column name="col1" type="int" />
  </table>
</schema>
<schema name="s2" owner="NOBODY">
  <table name="table2" owner="NOBODY" primaryKey="col1">
    <column name="col1" type="int" />
  </table>
  <type name="yesno" type="enum">
    <enum name="yes"/>
    <enum name="no"/>
  </type>
  <function name="test_concat" returns="text" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="a test function that concats strings">
    <functionParameter name="param1" type="text" />
    <functionParameter name="param2" type="text" />
    <functionDefinition language="sql" sqlFormat="mysql5">
      RETURN CONCAT(param1, param2);
    </functionDefinition>
  </function>
  <sequence name="the_sequence" owner="NOBODY" max="10" cycle="true" inc="3" start="2"/>
  <trigger name="trigger" sqlFormat="mysql5" table="table2" when="BEFORE" event="insert" function="EXECUTE xyz"/>
  <view name="view" owner="NOBODY" description="Description goes here">
    <viewQuery sqlFormat="mysql5">SELECT * FROM table2</viewQuery>
  </view>
</schema>
XML;
        $new = <<<XML
<schema name="s1" owner="NOBODY">
  <table name="table1" owner="NOBODY" primaryKey="col1">
    <column name="col1" type="int" />
  </table>
</schema>
XML;
        $expected1 = <<<SQL
DROP VIEW IF EXISTS `s2_view`;
SQL;
        $expected3 = <<<SQL
-- dropping enum type yesno. references to type yesno will be replaced with the type 'text'
DROP FUNCTION IF EXISTS `s2_test_concat`;
DELETE FROM `__sequences` WHERE `name` IN ('the_sequence');
-- `s2_table2` triggers, indexes, constraints will be implicitly dropped when the table is dropped
-- `s2_table2` will be dropped later according to table dependency order
DROP TABLE `s2_table2`;
SQL;
        $this->diff($old, $new, $expected1, $expected3);
    }
 private function common($xml, $expected)
 {
     $dbs = new SimpleXMLElement($xml);
     $ofs = new mock_output_file_segmenter();
     dbsteward::$new_database = $dbs;
     $table_dependency = xml_parser::table_dependency_order($dbs);
     mysql5::build_data($dbs, $ofs, $table_dependency);
     $actual = $ofs->_get_output();
     // get rid of extra whitespace
     $expected = preg_replace("/^ +/m", "", $expected);
     $expected = trim(preg_replace("/\n+/", "\n", $expected));
     // echo $actual;
     // get rid of comments
     $actual = preg_replace("/\\s*-- .*\$/m", '', $actual);
     // get rid of extra whitespace
     $actual = preg_replace("/^ +/m", "", $actual);
     $actual = trim(preg_replace("/\n+/", "\n", $actual));
     $this->assertEquals($expected, $actual);
 }
 private static function get_recreate_table_sql($schema, $table)
 {
     $fq_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']);
     $fq_tmp_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name'] . '_DBSTEWARD_MIGRATION');
     // utilize MySQL's CREATE TABLE ... SELECT syntax for cleaner recreation
     // see: http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html
     $sql = "CREATE TABLE {$fq_tmp_name}";
     $opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($schema, $table));
     if (!empty($opt_sql)) {
         $sql .= "\n" . $opt_sql;
     }
     if (strlen($table['description']) > 0) {
         $sql .= "\nCOMMENT " . mysql5::quote_string_value($table['description']);
     }
     $sql .= "\nSELECT * FROM {$fq_name};\n";
     $sql .= "DROP TABLE {$fq_name};\n";
     $sql .= "RENAME TABLE {$fq_tmp_name} TO {$fq_name};";
     return $sql;
 }
    public function testBuildSchema()
    {
        $xml = <<<XML
<dbsteward>
  <database>
    <host>db-host</host>
    <name>dbsteward</name>
    <role>
      <application>dbsteward_phpunit_app</application>
      <owner>deployment</owner>
      <replication/>
      <readonly/>
    </role>
    <!-- should be ignored -->
    <slony>
      <masterNode id="1"/>
      <replicaNode id="2" providerId="1"/>
      <replicaNode id="3" providerId="2"/>
      <replicationSet id="1"/>
      <replicationUpgradeSet id="2"/>
    </slony>
    <!-- should be ignored -->
    <configurationParameter name="TIME ZONE" value="America/New_York"/>
  </database>
  <!-- should be ignored -->
  <language name="plpgsql" procedural="true" owner="ROLE_OWNER"/>

  <schema name="public" owner="ROLE_OWNER">
    <grant operation="SELECT,UPDATE,DELETE" role="ROLE_OWNER"/>

    <type type="enum" name="permission_level">
      <enum name="guest"/>
      <enum name="user"/>
      <enum name="admin"/>
    </type>

    <function name="a_function" returns="text" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="a test function">
      <functionParameter name="config_parameter" type="text"/>
      <functionParameter name="config_value" type="text"/>
      <!-- should be ignored -->
      <functionDefinition language="plpgsql" sqlFormat="pgsql8">
        DECLARE
          q text;
          name text;
          n text;
        BEGIN
          SELECT INTO name current_database();
          q := 'ALTER DATABASE ' || name || ' SET ' || config_parameter || ' ''' || config_value || ''';';
          n := 'DB CONFIG CHANGE: ' || q;
          RAISE NOTICE '%', n;
          EXECUTE q;
          RETURN n;
        END;
      </functionDefinition>
      <functionDefinition language="sql" sqlFormat="mysql5">
        BEGIN
          RETURN config_parameter;
        END
      </functionDefinition>
      <grant operation="EXECUTE" role="ROLE_APPLICATION"/>
    </function>

    <table name="user" owner="ROLE_OWNER" primaryKey="user_id" slonyId="1">
      <column name="user_id" type="int auto_increment" null="false"/>
      <column name="group_id" foreignSchema="public" foreignTable="group" foreignColumn="group_id" null="false"/>
      <column name="username" type="varchar(80)"/>
      <column name="user_age" type="numeric"/>
      <constraint name="username_unq" type="Unique" definition="(`username`)"/>
      <grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
    </table>

    <table name="group" owner="ROLE_OWNER" primaryKey="group_id" slonyId="2">
      <column name="group_id" type="int auto_increment" null="false"/>
      <column name="permission_level" type="permission_level"/> <!-- enum type -->
      <column name="group_name" type="character varying(100)" unique="true"/>
      <column name="group_enabled" type="boolean" null="false" default="true"/>
      <grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
    </table>

    <sequence name="a_sequence" owner="ROLE_OWNER">
      <grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
    </sequence>

    <trigger name="a_trigger" sqlFormat="mysql5" table="user" when="before" event="insert" function="EXECUTE xyz"/>

    <!-- should be ignored -->
    <trigger name="a_trigger" sqlFormat="pgsql8" table="group" when="before" event="delete" function="EXECUTE xyz;"/>

    <view name="a_view" owner="ROLE_OWNER" description="Description goes here">
      <viewQuery sqlFormat="mysql5">SELECT * FROM user, group</viewQuery>
      <!-- should be ignored -->
      <viewQuery sqlFormat="pgsql8">SELECT * FROM pgsql8table</viewQuery>
      <grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
    </view>
  </schema>

  <schema name="hotel" owner="ROLE_OWNER">
    <table name="rate" owner="ROLE_OWNER" primaryKey="rate_id" slonyId="1">
      <column name="rate_id" type="integer" null="false"/>
      <column name="rate_group_id" foreignSchema="hotel" foreignTable="rate_group" foreignColumn="rate_group_id" null="false"/>
      <column name="rate_name" type="character varying(120)"/>
      <column name="rate_value" type="numeric"/>
    </table>
    <table name="rate_group" owner="ROLE_OWNER" primaryKey="rate_group_id" slonyId="2">
      <column name="rate_group_id" type="integer" null="false"/>
      <column name="rate_group_name" type="character varying(100)"/>
      <column name="rate_group_enabled" type="boolean" null="false" default="true"/>
    </table>
  </schema>
</dbsteward>
XML;
        $expected = <<<SQL
GRANT SELECT, UPDATE, DELETE ON * TO `deployment`;

DROP FUNCTION IF EXISTS `public_a_function`;
CREATE DEFINER = deployment FUNCTION `public_a_function` (`config_parameter` text, `config_value` text)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'a test function'
BEGIN
  RETURN config_parameter;
END;

GRANT EXECUTE ON FUNCTION `public_a_function` TO `dbsteward_phpunit_app`;

CREATE TABLE `public_user` (
  `user_id` int NOT NULL,
  `group_id` int NOT NULL,
  `username` varchar(80),
  `user_age` numeric
);

GRANT SELECT, UPDATE, DELETE ON `public_user` TO `dbsteward_phpunit_app`;

CREATE TABLE `public_group` (
  `group_id` int NOT NULL,
  `permission_level` ENUM('guest','user','admin'),
  `group_name` character varying(100),
  `group_enabled` boolean NOT NULL DEFAULT true
);

GRANT SELECT, UPDATE, DELETE ON `public_group` TO `dbsteward_phpunit_app`;

CREATE TABLE IF NOT EXISTS `__sequences` (
  `name` VARCHAR(100) NOT NULL,
  `increment` INT(11) unsigned NOT NULL DEFAULT 1,
  `min_value` INT(11) unsigned NOT NULL DEFAULT 1,
  `max_value` BIGINT(20) unsigned NOT NULL DEFAULT 18446744073709551615,
  `cur_value` BIGINT(20) unsigned DEFAULT 1,
  `start_value` BIGINT(20) unsigned DEFAULT 1,
  `cycle` BOOLEAN NOT NULL DEFAULT FALSE,
  `should_advance` BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (`name`)
) ENGINE = MyISAM;

DROP FUNCTION IF EXISTS `currval`;
CREATE FUNCTION `currval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  DECLARE val BIGINT(20);
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    SELECT `currval` INTO val FROM  `__sequences_currvals` WHERE `name` = seq_name;
    RETURN val;
  END IF;
END;

DROP FUNCTION IF EXISTS `lastval`;
CREATE FUNCTION `lastval` ()
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    RETURN @__sequences_lastval;
  END IF;
END;

DROP FUNCTION IF EXISTS `nextval`;
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  DECLARE advance BOOLEAN;

  CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
    `name` VARCHAR(100) NOT NULL,
    `currval` BIGINT(20),
    PRIMARY KEY (`name`)
  );

  SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;
  SELECT `should_advance` INTO advance FROM `__sequences` WHERE `name` = seq_name;

  IF @__sequences_lastval IS NOT NULL THEN

    IF advance = TRUE THEN
      UPDATE `__sequences`
      SET `cur_value` = IF (
        (`cur_value` + `increment`) > `max_value`,
        IF (`cycle` = TRUE, `min_value`, NULL),
        `cur_value` + `increment`
      )
      WHERE `name` = seq_name;

      SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;

    ELSE
      UPDATE `__sequences`
      SET `should_advance` = TRUE
      WHERE `name` = seq_name;
    END IF;

    REPLACE INTO `__sequences_currvals` (`name`, `currval`)
    VALUE (seq_name, @__sequences_lastval);
  END IF;

  RETURN @__sequences_lastval;
END;

DROP FUNCTION IF EXISTS `setval`;
CREATE FUNCTION `setval` (`seq_name` varchar(100), `value` bigint(20), `advance` BOOLEAN)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN

  UPDATE `__sequences`
  SET `cur_value` = value,
      `should_advance` = advance
  WHERE `name` = seq_name;

  IF advance = FALSE THEN
    CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
      `name` VARCHAR(100) NOT NULL,
      `currval` BIGINT(20),
      PRIMARY KEY (`name`)
    );

    REPLACE INTO `__sequences_currvals` (`name`, `currval`)
    VALUE (seq_name, value);
    SET @__sequences_lastval = value;
  END IF;

  RETURN value;
END;

INSERT INTO `__sequences`
  (`name`, `increment`, `min_value`, `max_value`, `cur_value`, `start_value`, `cycle`)
VALUES
  ('a_sequence', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

GRANT SELECT, UPDATE, DELETE ON `__sequences` TO `dbsteward_phpunit_app`;

DROP TRIGGER IF EXISTS `public_a_trigger`;
CREATE TRIGGER `public_a_trigger` BEFORE INSERT ON `public_user`
FOR EACH ROW EXECUTE xyz;

CREATE TABLE `hotel_rate` (
  `rate_id` integer NOT NULL,
  `rate_group_id` integer NOT NULL,
  `rate_name` character varying(120),
  `rate_value` numeric
);

CREATE TABLE `hotel_rate_group` (
  `rate_group_id` integer NOT NULL,
  `rate_group_name` character varying(100),
  `rate_group_enabled` boolean NOT NULL DEFAULT true
);

ALTER TABLE `public_user`
  ADD INDEX `group_id` (`group_id`) USING BTREE,
  ADD PRIMARY KEY (`user_id`),
  MODIFY `user_id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `public_group`
  ADD UNIQUE INDEX `group_name` (`group_name`) USING BTREE,
  ADD PRIMARY KEY (`group_id`),
  MODIFY `group_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `hotel_rate`
  ADD INDEX `rate_group_id` (`rate_group_id`) USING BTREE,
  ADD PRIMARY KEY (`rate_id`);
ALTER TABLE `hotel_rate_group`
  ADD PRIMARY KEY (`rate_group_id`);

ALTER TABLE `public_user`
  ADD UNIQUE INDEX `username_unq` (`username`),
  ADD CONSTRAINT `user_group_id_fkey` FOREIGN KEY `user_group_id_fkey` (`group_id`) REFERENCES `public_group` (`group_id`);

ALTER TABLE `hotel_rate`
  ADD CONSTRAINT `rate_rate_group_id_fkey` FOREIGN KEY `rate_rate_group_id_fkey` (`rate_group_id`) REFERENCES `hotel_rate_group` (`rate_group_id`);

CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `public_a_view`
AS SELECT * FROM user, group;
SQL;
        $dbs = new SimpleXMLElement($xml);
        $ofs = new mock_output_file_segmenter();
        dbsteward::$new_database = $dbs;
        $table_dependency = xml_parser::table_dependency_order($dbs);
        mysql5::build_schema($dbs, $ofs, $table_dependency);
        $actual = $ofs->_get_output();
        // var_dump($actual);
        // get rid of comments
        // $expected = preg_replace('/\s*-- .*(\n\s*)?/','',$expected);
        // // get rid of extra whitespace
        // $expected = trim(preg_replace("/\n\n/","\n",$expected));
        $expected = preg_replace("/^ +/m", "", $expected);
        $expected = trim(preg_replace("/\n+/", "\n", $expected));
        // echo $actual;
        // get rid of comments
        $actual = preg_replace("/\\s*-- .*\$/m", '', $actual);
        // get rid of extra whitespace
        // $actual = trim(preg_replace("/\n\n+/","\n",$actual));
        $actual = preg_replace("/^ +/m", "", $actual);
        $actual = trim(preg_replace("/\n+/", "\n", $actual));
        $this->assertEquals($expected, $actual);
    }
 private static function diff_single($ofs, $old_seq, $new_seq)
 {
     $sql = array();
     if (!dbsteward::$ignore_oldnames && !empty($new_seq['oldSequenceName'])) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = '{$new_seq['name']}'";
     }
     if ($new_seq['inc'] == null && $old_seq['inc'] != null) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = DEFAULT';
     }
     if ($new_seq['inc'] != null && strcasecmp($new_seq['inc'], $old_seq['inc']) != 0) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = ' . $new_seq['inc'];
     }
     if ($new_seq['min'] == null && $old_seq['min'] != null) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = DEFAULT';
     } elseif ($new_seq['min'] != null && strcasecmp($new_seq['min'], $old_seq['min']) != 0) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = ' . $new_seq['min'];
     }
     if ($new_seq['max'] == null && $old_seq['max'] != null) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = DEFAULT';
     } elseif ($new_seq['max'] != null && strcasecmp($new_seq['max'], $old_seq['max']) != 0) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = ' . $new_seq['max'];
     }
     if ($new_seq['start'] != null && strcasecmp($new_seq['start'], $old_seq['start']) != 0) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CUR_COL) . ' = ' . $new_seq['start'];
     }
     if ($new_seq['cycle'] == null && $old_seq['cycle'] != null) {
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = DEFAULT';
     } elseif ($new_seq['cycle'] != null && strcasecmp($new_seq['cycle'], $old_seq['cycle']) != 0) {
         $value = strcasecmp($new_seq['cycle'], 'false') == 0 ? 'FALSE' : 'TRUE';
         $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = ' . $value;
     }
     if (!empty($sql)) {
         $out = "UPDATE " . mysql5::get_quoted_table_name(mysql5_sequence::TABLE_NAME);
         $out .= "\nSET " . implode(",\n    ", $sql);
         $out .= "\nWHERE " . mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = ";
         $out .= "'" . $old_seq['name'] . "'";
         $ofs->write("{$out};\n");
     }
 }
    public function testDelimiters()
    {
        mysql5::$swap_function_delimiters = TRUE;
        $actual = mysql5_sequence::get_shim_creation_sql();
        $actual = trim(preg_replace('/--.*(\\n\\s*)?/', '', $actual));
        $expected = <<<SQL
CREATE TABLE IF NOT EXISTS `__sequences` (
  `name` VARCHAR(100) NOT NULL,
  `increment` INT(11) unsigned NOT NULL DEFAULT 1,
  `min_value` INT(11) unsigned NOT NULL DEFAULT 1,
  `max_value` BIGINT(20) unsigned NOT NULL DEFAULT 18446744073709551615,
  `cur_value` BIGINT(20) unsigned DEFAULT 1,
  `start_value` BIGINT(20) unsigned DEFAULT 1,
  `cycle` BOOLEAN NOT NULL DEFAULT FALSE,
  `should_advance` BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (`name`)
) ENGINE = MyISAM;

DELIMITER \$_\$
DROP FUNCTION IF EXISTS `currval`\$_\$
CREATE FUNCTION `currval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  DECLARE val BIGINT(20);
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    SELECT `currval` INTO val FROM  `__sequences_currvals` WHERE `name` = seq_name;
    RETURN val;
  END IF;
END\$_\$

DROP FUNCTION IF EXISTS `lastval`\$_\$
CREATE FUNCTION `lastval` ()
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    RETURN @__sequences_lastval;
  END IF;
END\$_\$

DROP FUNCTION IF EXISTS `nextval`\$_\$
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
  DECLARE advance BOOLEAN;

  CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
    `name` VARCHAR(100) NOT NULL,
    `currval` BIGINT(20),
    PRIMARY KEY (`name`)
  );

  SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;
  SELECT `should_advance` INTO advance FROM `__sequences` WHERE `name` = seq_name;
  
  IF @__sequences_lastval IS NOT NULL THEN

    IF advance = TRUE THEN
      UPDATE `__sequences`
      SET `cur_value` = IF (
        (`cur_value` + `increment`) > `max_value`,
        IF (`cycle` = TRUE, `min_value`, NULL),
        `cur_value` + `increment`
      )
      WHERE `name` = seq_name;

      SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;

    ELSE
      UPDATE `__sequences`
      SET `should_advance` = TRUE
      WHERE `name` = seq_name;
    END IF;

    REPLACE INTO `__sequences_currvals` (`name`, `currval`)
    VALUE (seq_name, @__sequences_lastval);
  END IF;

  RETURN @__sequences_lastval;
END\$_\$

DROP FUNCTION IF EXISTS `setval`\$_\$
CREATE FUNCTION `setval` (`seq_name` varchar(100), `value` bigint(20), `advance` BOOLEAN)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN

  UPDATE `__sequences`
  SET `cur_value` = value,
      `should_advance` = advance
  WHERE `name` = seq_name;

  IF advance = FALSE THEN
    CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
      `name` VARCHAR(100) NOT NULL,
      `currval` BIGINT(20),
      PRIMARY KEY (`name`)
    );

    REPLACE INTO `__sequences_currvals` (`name`, `currval`)
    VALUE (seq_name, value);
    SET @__sequences_lastval = value;
  END IF;

  RETURN value;
END\$_\$
DELIMITER ;
SQL;
        $this->assertEquals($expected, $actual);
    }
    public function testNullToNotNullDoesNotUpdate()
    {
        mysql5::$use_schema_name_prefix = TRUE;
        $old = <<<XML
<schema name="s1" owner="NOBODY">
  <table name="t1" owner="NOBODY" primaryKey="col1">
    <column name="col1" type="int" default="2" null="true" />
  </table>
</schema>
XML;
        $new = <<<XML
<schema name="s1" owner="NOBODY">
  <table name="t1" owner="NOBODY" primaryKey="col1">
    <column name="col1" type="int" default="2" null="false" />
  </table>
</schema>
XML;
        $expected1 = "ALTER TABLE `s1_t1`\n  MODIFY COLUMN `col1` int NOT NULL DEFAULT 2;";
        $this->common_diff($old, $new, $expected1, '', 'Changing NULL->NOT NULL should only result in a single stage 1 ALTER');
    }
    public function testDropAddAlter()
    {
        $old = <<<XML
<schema name="public" owner="NOBODY">
  <table name="test" primaryKey="a" owner="NOBODY">
    <tableOption sqlFormat="mysql5" name="engine" value="InnoDB"/>
    <tableOption sqlFormat="mysql5" name="auto_increment" value="5"/>
    <column name="a" type="int"/>
  </table>
</schema>
XML;
        $new = <<<XML
<schema name="public" owner="NOBODY">
  <table name="test" primaryKey="a" owner="NOBODY">
    <tableOption sqlFormat="mysql5" name="auto_increment" value="10"/>
    <tableOption sqlFormat="mysql5" name="row_format" value="compressed"/>
    <column name="a" type="int"/>
  </table>
</schema>
XML;
        mysql5::$use_auto_increment_table_options = TRUE;
        $expected = <<<SQL
-- Table `test` must be recreated to drop options: engine
CREATE TABLE `test_DBSTEWARD_MIGRATION`
AUTO_INCREMENT=10
ROW_FORMAT=compressed
SELECT * FROM `test`;
DROP TABLE `test`;
RENAME TABLE `test_DBSTEWARD_MIGRATION` TO `test`;
SQL;
        $this->common($old, $new, $expected);
        mysql5::$use_auto_increment_table_options = FALSE;
        $expected = <<<SQL
-- Table `test` must be recreated to drop options: engine
CREATE TABLE `test_DBSTEWARD_MIGRATION`
ROW_FORMAT=compressed
SELECT * FROM `test`;
DROP TABLE `test`;
RENAME TABLE `test_DBSTEWARD_MIGRATION` TO `test`;
SQL;
        $this->common($old, $new, $expected);
    }
 /**
  * Creates and returns SQL command for dropping the sequence.
  *
  * @return string
  */
 public static function get_drop_sql($node_schema, $node_sequences)
 {
     $table_name = mysql5::get_quoted_table_name(self::TABLE_NAME);
     $seq_col = mysql5::get_quoted_column_name(self::SEQ_COL);
     $sequences = dbx::to_array($node_sequences);
     if (count($sequences) === 0) {
         return '';
     }
     $sequence_names = "('" . implode("', '", array_map(function ($n) {
         if ($n instanceof SimpleXMLElement) {
             return $n['name'];
         }
         return $n;
     }, $sequences)) . "')";
     return "DELETE FROM {$table_name} WHERE {$seq_col} IN {$sequence_names};";
 }
Beispiel #21
0
 protected static function get_dimension_list($node_schema, $node_table, $node_index)
 {
     $dimensions = array();
     foreach ($node_index->indexDimension as $dimension) {
         // mysql only supports indexed columns, not indexed expressions like in pgsql or mssql
         if (!mysql5_table::contains_column($node_table, $dimension)) {
             throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'");
         }
         $dimensions[] = mysql5::get_quoted_column_name($dimension);
     }
     return $dimensions;
 }
Beispiel #22
0
 protected static function drop_old_schemas($ofs)
 {
     $drop_sequences = array();
     if (is_array(mysql5_diff::$old_table_dependency)) {
         $deps = mysql5_diff::$old_table_dependency;
         $processed_schemas = array();
         foreach ($deps as $dep) {
             $old_schema = $dep['schema'];
             if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) {
                 // this schema is being dropped, drop all children objects in it
                 if (!in_array(trim($old_schema['name']), $processed_schemas)) {
                     // this schema hasn't been processed yet, go ahead and drop views, types, functions, sequences
                     // only do it once per schema
                     foreach ($old_schema->type as $node_type) {
                         $ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n");
                     }
                     foreach ($old_schema->function as $node_function) {
                         $ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n");
                     }
                     foreach ($old_schema->sequence as $node_sequence) {
                         $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
                     }
                     $processed_schemas[] = trim($old_schema['name']);
                 }
                 if ($dep['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
                     // don't do anything with this table, it is a magic internal DBSteward value
                     continue;
                 }
                 // constraints, indexes, triggers will be deleted along with the tables they're attached to
                 // tables will drop themselves later on
                 // $ofs->write(mysql5_table::get_drop_sql($old_schema, $dep['table']) . "\n");
                 $table_name = mysql5::get_fully_qualified_table_name($dep['schema']['name'], $dep['table']['name']);
                 $ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n");
                 $ofs->write("-- {$table_name} will be dropped later according to table dependency order\n");
                 // table sequences need dropped separately
                 foreach (mysql5_table::get_sequences_needed($old_schema, $dep['table']) as $node_sequence) {
                     $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
                 }
             }
         }
     } else {
         foreach (dbsteward::$old_database->schema as $old_schema) {
             if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) {
                 foreach ($old_schema->type as $node_type) {
                     $ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n");
                 }
                 foreach ($old_schema->function as $node_function) {
                     $ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n");
                 }
                 foreach ($old_schema->sequence as $node_sequence) {
                     $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
                 }
                 foreach ($old_schema->table as $node_table) {
                     // tables will drop themselves later on
                     // $ofs->write(mysql5_table::get_drop_sql($old_schema, $node_table) . "\n");
                     $table_name = mysql5::get_fully_qualified_table_name($old_schema['name'], $node_table['name']);
                     $ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n");
                     $ofs->write("-- {$table_name} will be dropped later according to table dependency order\n");
                     foreach (mysql5_table::get_sequences_needed($old_schema, $node_table) as $node_sequence) {
                         $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
                     }
                 }
             }
         }
     }
 }
    public function testDelimiters()
    {
        $xml = <<<XML
<schema name="test" owner="ROLE_OWNER">
  <function name="test_fn" returns="text">
    <functionParameter name="a" type="text"/>
    <functionParameter name="b" type="int"/>
    <functionParameter name="c" type="date"/>
    <functionDefinition language="sql" sqlFormat="mysql5">
BEGIN
  DECLARE val BIGINT(20);
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    SELECT `currval` INTO val FROM  `__sequences_currvals` WHERE `name` = seq_name;
    RETURN val;
  END IF;
END;
    </functionDefinition>
  </function>
</schema>
XML;
        $schema = new SimpleXMLElement($xml);
        $expected = <<<SQL
DROP FUNCTION IF EXISTS `test_fn`;
CREATE DEFINER = CURRENT_USER FUNCTION `test_fn` (`a` text, `b` int, `c` date)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
  DECLARE val BIGINT(20);
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    SELECT `currval` INTO val FROM  `__sequences_currvals` WHERE `name` = seq_name;
    RETURN val;
  END IF;
END;
SQL;
        $actual = trim(mysql5_function::get_creation_sql($schema, $schema->function));
        $this->assertEquals($expected, $actual);
        mysql5::$swap_function_delimiters = TRUE;
        $expected = <<<SQL
DROP FUNCTION IF EXISTS `test_fn`;
DELIMITER \$_\$
CREATE DEFINER = CURRENT_USER FUNCTION `test_fn` (`a` text, `b` int, `c` date)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
  DECLARE val BIGINT(20);
  IF @__sequences_lastval IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
  ELSE
    SELECT `currval` INTO val FROM  `__sequences_currvals` WHERE `name` = seq_name;
    RETURN val;
  END IF;
END\$_\$
DELIMITER ;
SQL;
        $actual = trim(mysql5_function::get_creation_sql($schema, $schema->function));
        $this->assertEquals($expected, $actual);
    }
Beispiel #24
0
    public static function get_triggers_needed($schema, $table)
    {
        $triggers = array();
        foreach ($table->column as $column) {
            // we need a trigger for each serial column
            if (mysql5_column::is_serial($column['type'])) {
                $trigger_name = mysql5_column::get_serial_trigger_name($schema, $table, $column);
                $sequence_name = mysql5_column::get_serial_sequence_name($schema, $table, $column);
                $table_name = $table['name'];
                $column_name = mysql5::get_quoted_column_name($column['name']);
                $xml = <<<XML
<trigger name="{$trigger_name}"
         sqlFormat="mysql5"
         when="BEFORE"
         event="INSERT"
         table="{$table_name}"
         forEach="ROW"
         function="SET NEW.{$column_name} = COALESCE(NEW.{$column_name}, nextval('{$sequence_name}'));"/>
XML;
                $triggers[] = new SimpleXMLElement($xml);
            }
            // @TODO: convert DEFAULT expressions (not constants) to triggers for pgsql compatibility
        }
        return $triggers;
    }
    protected function setup_mysql5()
    {
        $xml = <<<XML
<dbsteward>
  <database>
    <sqlformat>mysql5</sqlformat>
    <role>
      <application>app_application</application>
      <owner>postgres</owner>
      <replication>app_slony</replication>
      <readonly>app_readonly</readonly>
    </role>
  </database>
  <schema name="app" owner="ROLE_OWNER">
    <table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk">
      <column name="id" type="character varying(32)" null="false"/>
      <column name="action" type="character varying(32)"/>
      <column name="description" type="character varying(200)"/>
      <rows columns="id, action, description">
        <row>
          <col>1</col>
          <col>Row 1</col>
          <col>Action 1 Description</col>
        </row>
        <row>
          <col>2</col>
          <col>Row 2</col>
          <col>Action 2 Description</col>
        </row>
        <row>
          <col>3</col>
          <col>Row 3</col>
          <col>Action 3 Description</col>
        </row>
        <row>
          <col>4</col>
          <col>Row 4</col>
          <col>Action 4 Description</col>
        </row>
        <row>
          <col>5</col>
          <col>Row 5</col>
          <col>Action 5 Description</col>
        </row>
      </rows>
    </table>
  </schema>
</dbsteward>
XML;
        $xml_data_overlay = <<<XML
<dbsteward>
  <database>
    <role>
      <application>client_app_application</application>
      <owner>postgres</owner>
      <replication>client_app_slony</replication>
      <readonly>client_app_readonly</readonly>
    </role>
  </database>
  <schema name="app" owner="ROLE_OWNER">
    <table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk">
      <rows columns="id, description">
        <row>
          <col>2</col>
          <col>Action 2 Alternate Description</col>
        </row>
        <row>
          <col>3</col>
          <col>Action 3 Alternate Description</col>
        </row>
        <row>
          <col>5</col>
          <col>Action 5 Alternate Description</col>
        </row>          
      </rows>
    </table>
  </schema>
</dbsteward>
XML;
        $this->xml_file_a = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_a.xml';
        $this->xml_file_b = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_b.xml';
        $this->xml_file_c = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_c.xml';
        $this->set_xml_content_a($xml);
        $this->set_xml_content_b($xml);
        $this->set_xml_content_c($xml_data_overlay);
        $this->output_prefix = dirname(__FILE__) . '/../testdata/mysql5_unit_test_identical';
        dbsteward::$single_stage_upgrade = TRUE;
        $old_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_c));
        $new_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_b, $this->xml_file_c));
        mysql5::build_upgrade('', 'identical_diff_test_mysql5_old', $old_db_doc_comp, array(), $this->output_prefix, 'identical_diff_test_mysql5_new', $new_db_doc_comp, array());
    }
 protected function do_upgrade($sql_format)
 {
     $old_db_doc = simplexml_load_file($this->xml_file_a);
     $new_db_doc = simplexml_load_file($this->xml_file_b);
     $this->output_prefix = dirname(__FILE__) . '/testdata/' . $sql_format . '_unit_test_xml_a';
     // need to unfortunately do the one thing austin told me not to:
     // use more than one format type per run
     if (strcasecmp($sql_format, 'pgsql8') == 0) {
         pgsql8::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
     } else {
         if (strcasecmp($sql_format, 'mysql5') == 0) {
             mysql5::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
         } else {
             $this->fail("This test only uses pgsql8 and mysql5 formats, but can be expanded.");
         }
     }
 }
 public static function get_constraint_drop_sql($constraint, $with_alter_table = TRUE)
 {
     if (!is_array($constraint)) {
         throw new exception("constraint is not an array?");
     }
     if (strlen($constraint['table_name']) == 0) {
         var_dump(array_keys($constraint));
         throw new exception("table_name is blank");
     }
     // because MySQL refuses to have consistent syntax
     switch (strtoupper($constraint['type'])) {
         case 'CHECK':
             // @TODO: Implement compatibility
             dbsteward::warning("Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint");
             return "-- Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint";
             break;
         case 'UNIQUE':
             $drop = "INDEX " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         case 'PRIMARY KEY':
             $drop = "PRIMARY KEY";
             break;
         case 'FOREIGN KEY':
             $drop = "FOREIGN KEY " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         case 'KEY':
             $drop = "KEY " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         default:
             // we shouldn't actually ever get here.
             throw new Exception("Unimplemented MySQL constraint {$constraint['type']}");
     }
     $sql = '';
     if ($with_alter_table) {
         $sql .= "ALTER TABLE " . mysql5::get_fully_qualified_table_name($constraint['schema_name'], $constraint['table_name']) . " ";
     }
     $sql .= "DROP {$drop}";
     if ($with_alter_table) {
         $sql .= ";";
     }
     return $sql;
 }
Beispiel #28
0
 /**
  * escape data types that need it
  *
  * @param mixed $value value to check for escaping
  *
  * @value mixed value, escaped as necessary
  */
 public static function value_escape($type, $value, $db_doc = NULL)
 {
     if (strlen($value) > 0) {
         // data types that should be quoted
         $enum_regex = dbx::enum_regex($db_doc);
         if (strlen($enum_regex) > 0) {
             $enum_regex = '|' . $enum_regex;
         }
         $PATTERN_QUOTED_TYPES = "/^char.*|^string|^date.*|^time.*|^varchar.*|^interval|^money.*|^inet" . $enum_regex . "/i";
         // strip quoting if it is a quoted type, it will be added after conditional conversion
         if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
             $value = mysql5::strip_single_quoting($value);
         }
         // complain when assholes use colon time notation instead of postgresql verbose for interval expressions
         if (dbsteward::$require_verbose_interval_notation) {
             if (preg_match('/interval/i', $type) > 0) {
                 if (substr($value, 0, 1) != '@') {
                     throw new exception("bad interval value: " . $value . " -- interval types must be postgresql verbose format: '@ 2 hours 30 minutes' etc for cfxn comparisons to work");
                 }
             }
         }
         // mssql doesn't understand epoch
         if (stripos('date', $type) !== FALSE && strcasecmp($value, 'epoch') == 0) {
             $value = '1970-01-01';
         }
         // special case for postgresql type value conversion
         // the boolean type for the column would have been translated to char(1) by xml_parser::mysql5_type_convert()
         if (strcasecmp($type, 'char(1)') == 0) {
             $value = mysql5::boolean_value_convert($value);
         } else {
             if (strcasecmp($type, 'datetimeoffset(7)') == 0) {
                 $value = date('c', strtotime($value));
                 // use date()'s ISO 8601 date format to be conformant
             } else {
                 if (strcasecmp($type, 'datetime2') == 0) {
                     $value = date('Y-m-dTG:i:s', strtotime($value));
                     // use date() to make date format conformant
                 } else {
                     if (strcasecmp($type, 'time') == 0 && strlen($value) > 8) {
                         $value = substr($value, 0, 8);
                     }
                 }
             }
         }
         if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
             $value = static::quote_string_value($value);
         }
     } else {
         // value is zero length, make it NULL
         $value = "NULL";
     }
     return $value;
 }
Beispiel #29
0
 public static function get_drop_sql($node_schema, $node_view)
 {
     return "DROP VIEW IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']) . ";";
 }
Beispiel #30
0
 protected static function define_sql_format_default_values($sql_format, $options)
 {
     ///// sql_format-specific default options
     $dbport = FALSE;
     if (strcasecmp($sql_format, 'pgsql8') == 0) {
         dbsteward::$create_languages = TRUE;
         dbsteward::$quote_schema_names = FALSE;
         dbsteward::$quote_table_names = FALSE;
         dbsteward::$quote_column_names = FALSE;
         $dbport = '5432';
     } else {
         if (strcasecmp($sql_format, 'mssql10') == 0) {
             // needed for MSSQL keyword-named-columns like system_user
             dbsteward::$quote_table_names = TRUE;
             dbsteward::$quote_column_names = TRUE;
             $dbport = '1433';
         } else {
             if (strcasecmp($sql_format, 'mysql5') == 0) {
                 dbsteward::$quote_schema_names = TRUE;
                 dbsteward::$quote_table_names = TRUE;
                 dbsteward::$quote_column_names = TRUE;
                 $dbport = '3306';
                 if (isset($options['useautoincrementoptions'])) {
                     mysql5::$use_auto_increment_table_options = TRUE;
                 }
                 if (isset($options['useschemaprefix'])) {
                     mysql5::$use_schema_name_prefix = TRUE;
                 }
             }
         }
     }
     if (strcasecmp($sql_format, 'pgsql8') != 0) {
         if (isset($options['pgdataxml'])) {
             dbsteward::error("pgdataxml parameter is not supported by " . dbsteward::get_sql_format() . " driver");
             exit(1);
         }
     }
     return $dbport;
 }