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; }
/** * 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};"; }
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; }
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); }
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; }
/** * 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; }
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']) . ";"; }
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; }