/** * @group pgsql8 */ public function testThrowWhenChangedToSerial() { $none = <<<XML <schema name="public" owner="ROLE_OWNER"> </schema> XML; $old = <<<XML <schema name="public" owner="ROLE_OWNER"> <table name="sometable" owner="ROLE_OWNER" primaryKey="id"> <column name="id" type="int"/> </table> </schema> XML; $new = <<<XML <schema name="public" owner="ROLE_OWNER"> <table name="sometable" owner="ROLE_OWNER" primaryKey="id"> <column name="id" type="serial"/> </table> </schema> XML; $none_schema = new SimpleXMLElement($none); $old_schema = new SimpleXMLElement($old); $new_schema = new SimpleXMLElement($new); $ofs = new mock_output_file_segmenter(); // make sure that creating a *new* serial *doesn't* throw pgsql8_diff_tables::diff_tables($ofs, $ofs, $none_schema, $new_schema, $none_schema->table, $new_schema->table); try { // changing int -> serial *should* throw pgsql8_diff_tables::diff_tables($ofs, $ofs, $old_schema, $new_schema, $old_schema->table, $new_schema->table); } catch (Exception $ex) { $this->assertEquals("Table public.sometable column id has linked type serial -- Column types cannot be altered to serial. If this column cannot be recreated as part of database change control, a user defined serial should be created, and corresponding nextval() defined as the default for the column.", $ex->getMessage()); return; } $this->fail('Expected exception because of changing to a serial type'); }
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); }
/** * Updates objects in schemas. * * @param $ofs1 stage1 output file segmenter * @param $ofs3 stage3 output file segmenter */ public static function update_structure($ofs1, $ofs3) { $type_modified_columns = array(); pgsql8_diff_languages::diff_languages($ofs1); // drop all views in all schemas, regardless whether dependency order is known or not pgsql8_diff_views::drop_views_ordered($ofs1, dbsteward::$old_database, dbsteward::$new_database); // if the table dependency order is unknown, bang them in natural order if (!is_array(self::$new_table_dependency)) { foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { pgsql8::set_context_replica_set_id($new_schema); //@NOTICE: @TODO: this does not honor old*Name attributes, does it matter? $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); pgsql8_diff_types::apply_changes($ofs1, $old_schema, $new_schema, $type_modified_columns); pgsql8_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema); pgsql8_diff_sequences::diff_sequences($ofs1, $old_schema, $new_schema); // remove old constraints before table contraints, so the SQL statements succeed pgsql8_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', true); pgsql8_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', true); pgsql8_diff_tables::drop_tables($ofs3, $old_schema, $new_schema); pgsql8_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema); pgsql8_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema); pgsql8_diff_tables::diff_clusters($ofs1, $old_schema, $new_schema); pgsql8_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', false); pgsql8_diff_triggers::diff_triggers($ofs1, $old_schema, $new_schema); } // non-primary key constraints may be inter-schema dependant, and dependant on other's primary keys // and therefore should be done after object creation sections foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { pgsql8::set_context_replica_set_id($new_schema); $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); pgsql8_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', false); } } else { // use table dependency order to do structural changes in an intelligent order $processed_schemas = array(); for ($i = 0; $i < count(self::$new_table_dependency); $i++) { // find the necessary pointers $item = self::$new_table_dependency[$i]; // @NOTICE: dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME is NOT checked here because these are schema operations $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); pgsql8::set_context_replica_set_id($new_schema); // do all types and functions on their own before table creation // see next loop for other once per schema work if (!in_array(trim($new_schema['name']), $processed_schemas)) { pgsql8::set_context_replica_set_id($new_schema); pgsql8_diff_types::apply_changes($ofs1, $old_schema, $new_schema, $type_modified_columns); pgsql8_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema); $processed_schemas[] = trim($new_schema['name']); } } // remove all old constraints before new contraints, in reverse dependency order for ($i = count(self::$old_table_dependency) - 1; $i >= 0; $i--) { // find the necessary pointers $item = self::$old_table_dependency[$i]; if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); $new_table = null; if ($new_schema != null) { pgsql8::set_context_replica_set_id($new_schema); $new_table = dbx::get_table($new_schema, $item['table']['name']); } $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); $old_table = null; if ($old_schema != null) { $old_table = dbx::get_table($old_schema, $item['table']['name']); } if ($old_table == null) { throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!"); } // @NOTICE: when dropping constraints, dbx::renamed_table_check_pointer() is not called for $old_table // as pgsql8_diff_tables::diff_constraints_table() will do rename checking when recreating constraints for renamed tables pgsql8_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', true); pgsql8_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', true); } $processed_schemas = array(); for ($i = 0; $i < count(self::$new_table_dependency); $i++) { // find the necessary pointers $item = self::$new_table_dependency[$i]; $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); $new_table = null; if ($new_schema != null) { pgsql8::set_context_replica_set_id($new_schema); $new_table = dbx::get_table($new_schema, $item['table']['name']); } $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); // schema level stuff should only be done once, keep track of which ones we have done // see above for pre table creation stuff // see below for post table creation stuff if (!in_array($new_schema['name'], $processed_schemas)) { pgsql8_diff_sequences::diff_sequences($ofs1, $old_schema, $new_schema); $processed_schemas[] = $new_schema['name']; } if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $old_table = null; if ($old_schema != null) { $old_table = dbx::get_table($old_schema, $item['table']['name']); } // if they are defined in the old definition, // old_schema and old_table are already established pointers // when a table has an oldTableName oldSchemaName specified, // dbx::renamed_table_check_pointer() will modify these pointers to be the old table dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table); if ($old_table) { if (!$old_schema) { // nkiraly: is this still happening when dbx::renamed_table_check_pointer() changes the $old_table pointer? throw new exception("old_table '" . $old_table['name'] . "' error: old_table is defined but old_schema is not - this cannot be for differs to diff"); } } pgsql8_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table, $new_table); pgsql8_diff_indexes::diff_indexes_table($ofs1, $old_schema, $old_table, $new_schema, $new_table); pgsql8_diff_tables::diff_clusters_table($ofs1, $old_schema, $old_table, $new_schema, $new_table); pgsql8_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', false); pgsql8_diff_triggers::diff_triggers_table($ofs1, $old_schema, $old_table, $new_schema, $new_table); pgsql8_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', false); } // drop old tables in reverse dependency order for ($i = count(self::$old_table_dependency) - 1; $i >= 0; $i--) { // find the necessary pointers $item = self::$old_table_dependency[$i]; if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); $new_table = null; if ($new_schema != null) { $new_table = dbx::get_table($new_schema, $item['table']['name']); } $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); $old_table = null; if ($old_schema != null) { $old_table = dbx::get_table($old_schema, $item['table']['name']); } if ($old_table == null) { throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!"); } pgsql8_diff_tables::drop_tables($ofs3, $old_schema, $new_schema, $old_table, $new_table); } } pgsql8_diff_views::create_views_ordered($ofs3, dbsteward::$old_database, dbsteward::$new_database); }