public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("mssql10::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::notice("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } // error encountered: ALTER DATABASE statement not allowed within multi-statement transaction. // so do the assembly configuration before the db structure / data creation transaction // see BEGIN TRANSACTION below if (isset($db_doc->inlineAssembly)) { foreach ($db_doc->inlineAssembly as $assembly) { $assembly_file_name = dirname($files[0]) . '/' . $assembly['name']; if (!is_readable($assembly_file_name)) { throw new exception("assembly file " . $assembly_file_name . " not readable"); } $assembly_name = substr(basename($assembly_file_name), 0, -4); dbsteward::info("Including " . $assembly_name . " assembly inline from " . $assembly_file_name); $afh = fopen($assembly_file_name, "rb"); $assembly_contents = fread($afh, filesize($assembly_file_name)); fclose($afh); $assembly_binary_hex = '0x' . bin2hex($assembly_contents); $ddl = "CREATE ASSEMBLY " . $assembly_name . "\n" . "FROM " . $assembly_binary_hex . "\n" . "WITH PERMISSION_SET = SAFE;\n\n"; $build_file_ofs->write($ddl); } unset($db_doc->inlineAssembly); } $build_file_ofs->write("BEGIN TRANSACTION;\n\n"); dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; dbx::set_default_schema($db_doc, 'dbo'); // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { //@TODO: implement mssql10_language ? no relevant conversion exists see other TODO's stating this } } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::notice("Defining structure"); mssql10::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::notice("Defining data inserts"); mssql10::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; $build_file_ofs->write("COMMIT TRANSACTION;\n\n"); return $db_doc; }
private function diff($old, $new, $expected1, $expected3, $message = '') { dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $old . '</dbsteward>'); dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $new . '</dbsteward>'); $ofs1 = new mock_output_file_segmenter(); $ofs3 = new mock_output_file_segmenter(); // same structure as mysql5_diff::update_structure foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE); mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema); mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema); // mysql5_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE); } foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE); } $actual1 = trim($ofs1->_get_output()); $actual3 = trim($ofs3->_get_output()); $this->assertEquals($expected1, $actual1, "during stage 1: {$message}"); $this->assertEquals($expected3, $actual3, "during stage 3: {$message}"); }
/** * Setup file pointers then call diff_doc_work() to do the actual diffing * @param string $old_xml_file * @param string $new_xml_file * @param SimpleXMLElement $old_database * @param SimpleXMLElement $new_database * @param string $upgrade_prefix */ public static function diff_doc($old_xml_file, $new_xml_file, $old_database, $new_database, $upgrade_prefix) { $timestamp = date('r'); $old_set_new_set = "-- Old definition: " . $old_xml_file . "\n" . "-- New definition: " . $new_xml_file . "\n"; // setup file pointers, depending on stage file mode -- single (all the same) or multiple if (dbsteward::$single_stage_upgrade) { $single_stage_upgrade_file = $upgrade_prefix . '_single_stage.sql'; $single_stage_fp = fopen($single_stage_upgrade_file, 'w'); if ($single_stage_fp === false) { throw new exception("failed to open upgrade single stage output file " . $single_stage_upgrade_file . ' for write'); } $stage1_ofs = new output_file_segmenter($single_stage_upgrade_file, 1, $single_stage_fp, $single_stage_upgrade_file); $stage1_ofs->set_header("-- DBSteward single stage upgrade changes - generated " . $timestamp . "\n" . $old_set_new_set); $stage2_ofs =& $stage1_ofs; $stage3_ofs =& $stage1_ofs; $stage4_ofs =& $stage1_ofs; } else { $stage1_ofs = new output_file_segmenter($upgrade_prefix . '_stage1_schema', 1); $stage1_ofs->set_header("-- DBSteward stage 1 structure additions and modifications - generated " . $timestamp . "\n" . $old_set_new_set); $stage2_ofs = new output_file_segmenter($upgrade_prefix . '_stage2_data', 1); $stage2_ofs->set_header("-- DBSteward stage 2 data definitions removed - generated " . $timestamp . "\n" . $old_set_new_set); $stage3_ofs = new output_file_segmenter($upgrade_prefix . '_stage3_schema', 1); $stage3_ofs->set_header("-- DBSteward stage 3 structure changes, constraints and removals - generated " . $timestamp . "\n" . $old_set_new_set); $stage4_ofs = new output_file_segmenter($upgrade_prefix . '_stage4_data', 1); $stage4_ofs->set_header("-- DBSteward stage 4 data definition changes and additions - generated " . $timestamp . "\n" . $old_set_new_set); } dbsteward::$old_database = $old_database; dbsteward::$new_database = $new_database; static::diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs); }
public function setUp() { dbsteward::set_sql_format('pgsql8'); 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; $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; $db = new SimpleXMLElement($xml); dbsteward::$new_database = $db; dbsteward::$old_database = $db; }
/** * Creates SQL diff of two SQL dumps * * @return array output files list */ public static function diff_sql($old_sql_files, $new_sql_files, $upgrade_prefix) { dbsteward::$old_database = pgsql8_dump_loader::load_database($old_sql_files); $old_sql_xml_file = $upgrade_prefix . '_old_sql.xml'; xml_parser::save_xml($old_sql_xml_file, dbsteward::$old_database->saveXML()); dbsteward::$new_database = pgsql8_dump_loader::load_database($new_sql_files); $new_sql_xml_file = $upgrade_prefix . '_new_sql.xml'; xml_parser::save_xml($new_sql_xml_file, dbsteward::$new_database->saveXML()); self::diff_xml(array($old_sql_xml_file), array($new_sql_xml_file), $upgrade_prefix); }
private function common_diff($xml_a, $xml_b, $expected1, $expected3, $message = '') { dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $xml_a . '</dbsteward>'); dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $xml_b . '</dbsteward>'); $ofs1 = new mock_output_file_segmenter(); $ofs3 = new mock_output_file_segmenter(); pgsql8_diff_tables::diff_tables($ofs1, $ofs3, dbsteward::$old_database->schema, dbsteward::$new_database->schema); $actual1 = trim($ofs1->_get_output()); $actual3 = trim($ofs3->_get_output()); $this->assertEquals($expected1, $actual1, "during stage 1: {$message}"); $this->assertEquals($expected3, $actual3, "during stage 3: {$message}"); }
/** * @group pgsql8 */ public function testTableColumnTypeQuotingPgsql8() { dbsteward::set_sql_format('pgsql8'); dbsteward::$quote_all_names = TRUE; dbsteward::$single_stage_upgrade = TRUE; $doc_empty = simplexml_load_string($this->xml_empty); $doc_empty = xml_parser::composite_doc(FALSE, $doc_empty); dbsteward::$old_database = $doc_empty; $doc = simplexml_load_string($this->xml); $doc = xml_parser::composite_doc(FALSE, $doc); dbsteward::$new_database = $doc; $table_dependency = xml_parser::table_dependency_order($doc); //var_dump(xml_parser::format_xml($doc_empty->saveXML())); //var_dump(xml_parser::format_xml($doc->saveXML())); $schema = $doc->schema; $table = $schema->table; // make sure the type is named with quoting as part of a definition build $expected = "CREATE TYPE \"schema1\".\"enumCamelCaseType\" AS ENUM ('Read','Write','Delete');"; $mofs = new mock_output_file_segmenter(); pgsql8::build_schema($doc, $mofs, $table_dependency); $actual = trim($mofs->_get_output()); $this->assertContains($expected, $actual); // make sure the type is referred to with quoting in a table creation as part of a definition build $expected_column = '"table_shable_mode" "enumCamelCaseType"'; $this->assertContains($expected_column, $actual); // make sure the type is referred to with quoting when generating table create statements $expected = '"table_shable_mode" "enumCamelCaseType"'; $sql = pgsql8_table::get_creation_sql($schema, $table); $this->assertContains($expected, $sql); // make sure create table quotes the type name $expected = '"table_shable_mode" "enumCamelCaseType"'; $mofs = new mock_output_file_segmenter(); var_dump(dbx::get_tables($schema)); pgsql8_diff_tables::diff_tables($mofs, $mofs, NULL, $schema); $actual = trim($mofs->_get_output()); $this->assertContains($expected, $actual); // make sure insert statements are made that match the XML definition $expected = "INSERT INTO \"schema1\".\"table_shable\" (\"table_shable_id\", \"table_shable_value\", \"table_shable_mode\") VALUES (1, E'shim sham', BETA);"; $actual = trim(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $this->assertContains($expected, $actual); }
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; mysql5::$swap_function_delimiters = FALSE; 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); }
private function common_structure($old, $new) { dbsteward::$old_database = new SimpleXMLElement($old); dbsteward::$new_database = new SimpleXMLElement($new); mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database); $ofs1 = new mock_output_file_segmenter(); $ofs3 = new mock_output_file_segmenter(); mysql5_diff::revoke_permissions($ofs1, $ofs3); mysql5_diff::update_structure($ofs1, $ofs3); mysql5_diff::update_permissions($ofs1, $ofs3); // @TODO: assert expected = actual // echo "\n\nofs 1:\n\n"; // echo $ofs1->_get_output(); // echo "\n\nofs 3:\n\n"; // echo $ofs3->_get_output(); }
protected function upgrade_db($format, $ofs1, $ofs2, $ofs3, $ofs4) { dbsteward::set_sql_format($format); $doc_a = new SimpleXMLElement($this->{$format . '_xml_a'}); $doc_b = new SimpleXMLElement($this->{$format . '_xml_b'}); dbsteward::$old_database = $doc_a; dbsteward::$new_database = $doc_b; $diff_class = $format . '_diff'; $diff_class::$old_table_dependency = xml_parser::table_dependency_order($doc_a); $diff_class::$new_table_dependency = xml_parser::table_dependency_order($doc_b); $diff_class::diff_doc_work($ofs1, $ofs2, $ofs3, $ofs4); }
private function doTestUpgradesInOrder($format) { dbsteward::set_sql_format($format); $doc = $this->doc_with; $actual = $this->capture(function ($ofs) use($doc) { dbsteward::$new_database = $doc; dbsteward::$old_database = $doc; dbsteward::$single_stage_upgrade = true; format_diff::$as_transaction = false; format_diff::update_structure($ofs, $ofs); }); if ($format == 'pgsql8') { $expected = <<<SQL DROP VIEW IF EXISTS "public"."view2"; DROP VIEW IF EXISTS "public"."view1"; CREATE OR REPLACE VIEW "public"."view2" AS SELECT * FROM elsewhere; ALTER VIEW "public"."view2" OWNER TO deployment; CREATE OR REPLACE VIEW "public"."view1" AS SELECT * FROM view2; ALTER VIEW "public"."view1" OWNER TO deployment; SQL; } else { $expected = <<<SQL DROP VIEW IF EXISTS `view2`; DROP VIEW IF EXISTS `view1`; CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `view2` AS SELECT * FROM elsewhere; CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `view1` AS SELECT * FROM view2; SQL; } $this->assertEquals($expected, $actual); }
protected function upgrade_db($format) { dbsteward::set_sql_format($format); $ofs = new mock_output_file_segmenter(); $doc_a = new SimpleXMLElement($this->{$format . '_xml_a'}); $doc_b = new SimpleXMLElement($this->{$format . '_xml_b'}); dbsteward::$old_database = $doc_a; dbsteward::$new_database = $doc_b; mysql5_diff::diff_doc_work($ofs, $ofs, $ofs, $ofs); }
private function diff($old, $new, $expected1, $expected3, $message = '') { dbsteward::$old_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $old . '</dbsteward>')); dbsteward::$new_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $new . '</dbsteward>')); $ofs1 = new mock_output_file_segmenter(); $ofs3 = new mock_output_file_segmenter(); mysql5_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database); mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database); mysql5_diff::update_structure($ofs1, $ofs3); $actual1 = trim($ofs1->_get_output()); $actual3 = trim($ofs3->_get_output()); $this->assertEquals($expected1, $actual1, "during stage 1: {$message}"); $this->assertEquals($expected3, $actual3, "during stage 3: {$message}"); }
private function common($a, $b, $expected, $type = 'all') { $dbs_a = new SimpleXMLElement($a); $dbs_b = new SimpleXMLElement($b); $ofs = new mock_output_file_segmenter(); dbsteward::$old_database = $dbs_a; dbsteward::$new_database = $dbs_b; mysql5_diff_constraints::diff_constraints_table($ofs, $dbs_a->schema, $dbs_a->schema->table, $dbs_b->schema, $dbs_b->schema->table, $type, true); mysql5_diff_constraints::diff_constraints_table($ofs, $dbs_a->schema, $dbs_a->schema->table, $dbs_b->schema, $dbs_b->schema->table, $type, false); $actual = trim(preg_replace("/--.*\n/", '', $ofs->_get_output())); $this->assertEquals($expected, $actual); }
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); }
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); }
public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("mysql5::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::notice("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } // $build_file_ofs->write("START TRANSACTION;\n\n"); dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { dbsteward::warning("Ignoring language {$language['name']} declaration because MySQL does not support languages other than 'sql'"); } } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::info("Defining structure"); mysql5::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::info("Defining data inserts"); mysql5::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; // $build_file_ofs->write("COMMIT TRANSACTION;\n\n"); return $db_doc; }
public function testExceptionIsThrownWithDupes() { dbsteward::set_sql_format('pgsql8'); dbsteward::$quote_all_names = TRUE; dbsteward::$single_stage_upgrade = TRUE; $doc_empty = simplexml_load_string($this->xml_empty); $doc_empty = xml_parser::composite_doc(FALSE, $doc_empty); dbsteward::$old_database = $doc_empty; $doc = simplexml_load_string($this->xml); $doc = xml_parser::composite_doc(FALSE, $doc); dbsteward::$new_database = $doc; $table_dependency = xml_parser::table_dependency_order($doc); $schema = $doc->schema; $table = $schema->table; // make sure the type is named with quoting as part of a definition build $mofs = new mock_output_file_segmenter(); try { pgsql8::build_schema($doc, $mofs, $table_dependency); } catch (Exception $e) { $this->assertContains('duplicate index name', strtolower($e->getMessage())); return; } $this->fail("build_schema did not detect duplicate index names"); }
private function diff($old, $new, $expected) { $ofs = new mock_output_file_segmenter(); $old = '<dbsteward><database/>' . $old . '</dbsteward>'; $new = '<dbsteward><database/>' . $new . '</dbsteward>'; $old_doc = simplexml_load_string($old); $new_doc = simplexml_load_string($new); dbsteward::$old_database = $old_doc; dbsteward::$new_database = $new_doc; pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order($old_doc); pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order($new_doc); pgsql8_diff_types::apply_changes($ofs, $old_doc->schema, $new_doc->schema); $sql = trim(preg_replace('/\\n\\n+/', "\n", preg_replace('/^--.*$/m', '', $ofs->_get_output()))); $this->assertEquals($expected, $sql); }
private function common_drop($xml_a, $xml_b, $expected, $message = NULL) { dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $xml_a . '</dbsteward>'); dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $xml_b . '</dbsteward>'); $ofs = new mock_output_file_segmenter(); mysql5_diff_tables::drop_tables($ofs, dbsteward::$old_database->schema, dbsteward::$new_database->schema); $actual = trim($ofs->_get_output()); $this->assertEquals($expected, $actual, $message); }
protected function transaction_statement_check($is_transactional) { dbsteward::$old_database = new SimpleXMLElement($this->oldxml); dbsteward::$new_database = new SimpleXMLElement($this->newxml); pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database); pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database); $ofs = new mock_output_file_segmenter(); pgsql8_diff::diff_doc_work($ofs, $ofs, $ofs, $ofs); if ($is_transactional) { $this->assertRegExp('/^BEGIN;/im', $ofs->_get_output(), 'output contains BEGIN statement'); $this->assertRegExp('/^COMMIT;/im', $ofs->_get_output(), 'output contains COMMIT statement'); } else { $this->assertRegExp('/^(?!BEGIN;).*$/im', $ofs->_get_output(), 'output contains BEGIN statement'); $this->assertRegExp('/^(?!COMMIT;).*$/im', $ofs->_get_output(), 'output contains COMMIT statement'); } }
public function testAutoIncrement() { $xml = <<<XML <schema name="public" owner="NOBODY"> <table name="test" primaryKey="id" owner="NOBODY"> <column name="id" type="int auto_increment"/> </table> </schema> XML; $schema = new SimpleXMLElement($xml); $this->assertEquals("CREATE TABLE `test` (\n `id` int\n);", mysql5_table::get_creation_sql($schema, $schema->table)); // ensure that foreign auto_increment flags aren't transferred to the table definition $xml = <<<XML <dbsteward> <database/> <schema name="public" owner="NOBODY"> <table name="test" primaryKey="id" owner="NOBODY"> <column name="id" type="int auto_increment"/> <column name="fk" foreignSchema="public" foreignTable="other" foreignColumn="id"/> </table> <table name="other" primaryKey="id" onwer="NOBODY"> <column name="id" type="int auto_increment"/> </table> </schema> </dbsteward> XML; $dbs = new SimpleXMLElement($xml); dbsteward::$new_database = $dbs; $this->assertEquals("CREATE TABLE `test` (\n `id` int,\n `fk` int\n);", mysql5_table::get_creation_sql($dbs->schema, $dbs->schema->table[0])); }
public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("pgsql8::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::info("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } if (!dbsteward::$generate_slonik) { $build_file_ofs->write("BEGIN;\n\n"); } dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation code refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; dbx::set_default_schema($db_doc, 'public'); // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { $build_file_ofs->write(pgsql8_language::get_creation_sql($language)); } } // by default, postgresql will validate the contents of LANGUAGE SQL functions during creation // because we are creating all functions before tables, this doesn't work when LANGUAGE SQL functions // refer to tables yet to be created. // scan language="sql" functions for <functionDefiniton>s that contain FROM (<TABLE>) statements $set_check_function_bodies = TRUE; // on in default postgresql configs dbx::set_default_schema($db_doc, 'public'); foreach ($db_doc->schema as $schema) { foreach ($schema->function as $function) { if (pgsql8_function::has_definition($function)) { $definition = pgsql8_function::get_definition($function); if (strcasecmp($definition['language'], 'sql') == 0 && $definition['sqlFormat'] == 'pgsql8' && !is_null($referenced_table_name = static::function_definition_references_table($definition))) { $table_schema_name = sql_parser::get_schema_name($referenced_table_name, $db_doc); $node_schema = dbx::get_schema($db_doc, $table_schema_name); $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($referenced_table_name)); if ($node_table) { // the referenced table is in the definition // turn off check_function_bodies $set_check_function_bodies = FALSE; $set_check_function_bodies_info = "Detected LANGUAGE SQL function " . $schema['name'] . '.' . $function['name'] . " referring to table " . $table_schema_name . '.' . $node_table['name'] . " in the database definition"; dbsteward::info($set_check_function_bodies_info); break 2; } } } } } if (!$set_check_function_bodies) { $build_file_ofs->write("\n"); $build_file_ofs->write("SET check_function_bodies = FALSE; -- DBSteward " . $set_check_function_bodies_info . "\n\n"); } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::info("Defining structure"); pgsql8::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::info("Defining data inserts"); pgsql8::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; if (!dbsteward::$generate_slonik) { $build_file_ofs->write("COMMIT;\n\n"); } if (dbsteward::$generate_slonik) { $replica_sets = static::get_slony_replica_sets($db_doc); foreach ($replica_sets as $replica_set) { // output preamble file standalone for tool chains that use the preamble to do additional slonik commands pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_preamble.slonik"); // output paths specificity standalone for tool chains that use the store path slonik statements separately pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_paths.slonik"); // output create set file standalone for tool chains that use the create_set slonik separately $create_set_filename = $output_prefix . '_slony_replica_set_' . $replica_set['id'] . '_create_set.slonik'; pgsql8::build_slonik_create_set($db_doc, $replica_set, $create_set_filename); pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); pgsql8::build_slonik_store_nodes($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); // build full subscribe steps that creates sets and subscribes nodes $subscribe_filename = $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik"; pgsql8::build_slonik_preamble($db_doc, $replica_set, $subscribe_filename); // create_set does one time slony configuration comparison. // so append the content of _create_set into _subscribe built earlier file_put_contents($subscribe_filename, file_get_contents($create_set_filename), FILE_APPEND); foreach ($replica_set->slonyReplicaSetNode as $replica_set_node) { pgsql8::build_slonik_subscribe_set_node($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik", $replica_set_node); } static::slony_ids_required_during_build($replica_set, $db_doc); } $count = 0; foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) { $count += count(self::$sequence_slony_ids[$slony_set_id]); } dbsteward::notice("[slony] ID summary: " . count(self::$table_slony_ids) . " tables " . $count . " sequences"); dbsteward::notice("[slony] table ID segments: " . static::slony_id_segment_summary(self::$table_slony_ids)); // keep this from bombing on there being no ids in $sequence_slony_ids // if there were none returned (i.e. either there weren't any defined // or they were all set to IGNORE_REQUIRED which hopefully doesn't happen // because why would you do that for all of them) if (!empty(self::$sequence_slony_ids)) { foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) { $console_line = "[slony] sequence ID segments"; if ($slony_set_id != 'NoSlonySet') { $console_line .= " for slonySetId {$slony_set_id}"; } $console_line .= ": "; dbsteward::notice($console_line . static::slony_id_segment_summary(self::$sequence_slony_ids[$slony_set_id])); } } } return $db_doc; }
protected function diff_definitions($output_prefix) { dbsteward::$old_database = new SimpleXMLElement($this->oldxml); dbsteward::$new_database = new SimpleXMLElement($this->newxml); pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database); pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database); $output_prefix_path = dirname(__FILE__) . '/../testdata/' . $output_prefix; pgsql8::build_upgrade('', 'old_SlonyStageTransactionalityTest', dbsteward::$old_database, array(), $output_prefix_path, 'new_SlonyStageTransactionalityTest', dbsteward::$new_database, array()); return $output_prefix_path; }