public function testCharTypeColumnsEscaped() { $schema = simplexml_load_string($this->xml); $table = $schema->table; $expected = 'INSERT INTO "public"."i_test" ("pk", "col1") VALUES (1, E\'hi\');'; $actual = trim(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $this->assertEquals($expected, $actual); }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $tables_count = count($tables); $limit_to_tables_count = count(dbsteward::$limit_to_tables); for ($i = 0; $i < $tables_count; $i++) { $schema = $tables[$i]['schema']; $table = $tables[$i]['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column $node_rows =& dbx::get_table_rows($table); $columns = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); if (isset($table['primaryKey']) && strlen($table['primaryKey']) > 0 && in_array(dbsteward::string_cast($table['primaryKey']), $columns)) { $pk_column = dbsteward::string_cast($table['primaryKey']); // only do it if the primary key column is also a serial/bigserial $nodes = xml_parser::inheritance_get_column($table, $pk_column); if (count($nodes) != 1) { var_dump($nodes); throw new exception("Failed to find primary key column '" . $pk_column . "' for " . $schema['name'] . "." . $table['name']); } $pk = $nodes[0]; $pk_column_type = strtolower(dbsteward::string_cast($pk['type'])); if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $pk_column_type) > 0) { // only set the pkey to MAX() if serialStart is not defined if (!isset($pk['serialStart'])) { $sql = "SELECT setval(pg_get_serial_sequence('" . $schema['name'] . "." . $table['name'] . "', '" . $pk_column . "'), MAX({$pk_column}), TRUE) FROM " . $schema['name'] . "." . $table['name'] . ";\n"; $ofs->write($sql); } } } // check if primary key is a column of this table - FS#17481 $primary_keys_exist = self::primary_key_split($table['primaryKey']); foreach ($table->column as $column) { // while looping through columns, check to see if primary key is one of them // if it is remove it from the primary keys array, at the end of loop array should be empty $key = array_search($column['name'], $primary_keys_exist); if (is_numeric($key)) { unset($primary_keys_exist[$key]); } } // throw an error if the table is using a primaryKey column that does not actually exist if (!empty($primary_keys_exist)) { if (empty($table['inheritsTable'])) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } else { dbsteward::info('Primary key ' . $table['primaryKey'] . ' does not exist as a column in child table ' . $table['name'] . ', but may exist in parent table'); } } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
/** * @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 data in table definitions * * @param ofs output file segmenter * @param $old_database original database * @param $new_database new database */ private static function update_data($ofs, $delete_mode = false) { if (self::$new_table_dependency != null && count(self::$new_table_dependency) > 0) { for ($i = 0; $i < count(self::$new_table_dependency); $i++) { // go in reverse when in delete mode if ($delete_mode) { $item = self::$new_table_dependency[count(self::$new_table_dependency) - 1 - $i]; } else { $item = self::$new_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; } $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']); } $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); if ($new_schema == null) { throw new exception("schema " . $item['schema']['name'] . " not found in new database"); } $new_table = dbx::get_table($new_schema, $item['table']['name']); if ($new_table == null) { throw new exception("table " . $item['table']['name'] . " not found in new database schema " . $new_schema['name']); } pgsql8::set_context_replica_set_id($new_schema); // if the table was renamed, get old definition pointers for comparison if (pgsql8_diff_tables::is_renamed_table($new_schema, $new_table)) { dbsteward::info("NOTICE: " . $new_schema['name'] . "." . $new_table['name'] . " used to be called " . $new_table['oldTableName'] . " -- will diff data against that definition"); $old_schema = pgsql8_table::get_old_table_schema($new_schema, $new_table); $old_table = pgsql8_table::get_old_table($new_schema, $new_table); } $ofs->write(pgsql8_diff_tables::get_data_sql($old_schema, $old_table, $new_schema, $new_table, $delete_mode)); } } else { // dependency order unknown, hit them in natural order 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_data($ofs, $old_schema, $new_schema); } } }