/**
     * @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);
 }
Beispiel #5
0
 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);
 }
Beispiel #7
0
 /**
  * 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");
         }
     }
 }