/**
* @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');
}
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);
}
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}");
}
private function common($old, $new, $expected)
{
$ofs = new mock_output_file_segmenter();
$old_schema = new SimpleXMLElement($old);
$old_table = $old_schema->table;
$new_schema = new SimpleXMLElement($new);
$new_table = $new_schema->table;
pgsql8_diff_tables::update_table_options($ofs, $ofs, $old_schema, $old_table, $new_schema, $new_table);
$actual = trim($ofs->_get_output());
$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);
}
}
}
public static function diff_constraints_table($ofs, $old_schema, $old_table, $new_schema, $new_table, $type, $drop_constraints = false)
{
pgsql8::set_context_replica_set_id($new_table);
if ($drop_constraints) {
// drop constraints that no longer exist or are modified
foreach (self::get_drop_constraints($old_schema, $old_table, $new_schema, $new_table, $type) as $constraint) {
$ofs->write(pgsql8_table::get_constraint_drop_sql($constraint) . "\n");
}
} else {
if (!dbsteward::$ignore_oldnames) {
// if it is a renamed table
if (pgsql8_diff_tables::is_renamed_table($new_schema, $new_table)) {
// remove all constraints and recreate with new table name conventions
foreach (pgsql8_constraint::get_table_constraints(dbsteward::$old_database, $old_schema, $old_table, $type) as $constraint) {
// rewrite the constraint definer to refer to the new table name
// so the constraint by the old name, but part of the new table
// will be referenced properly in the drop statement
$constraint['schema_name'] = $new_schema['name'];
$constraint['table_name'] = $new_table['name'];
$ofs->write(pgsql8_table::get_constraint_drop_sql($constraint) . "\n");
}
// add all still-define constraints back and any new ones to the table
foreach (pgsql8_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, $type) as $constraint) {
$ofs->write(pgsql8_table::get_constraint_sql($constraint) . "\n");
}
// this gets any new constraints as well.
// return so that get_new_costraint() doesnt duplicate any new constraints
return;
}
}
// add new constraints
foreach (self::get_new_constraints($old_schema, $old_table, $new_schema, $new_table, $type) as $constraint) {
$ofs->write(pgsql8_table::get_constraint_sql($constraint) . "\n");
}
}
}