/**
  * Creates new schemas (not the objects inside the schemas)
  *
  * @param  object  $ofs output file pointer
  * @return void
  */
 protected static function create_new_schemas($ofs)
 {
     foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
         if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == null) {
             dbsteward::info("Create New Schema " . $new_schema['name']);
             pgsql8::set_context_replica_set_id($new_schema);
             $ofs->write(format_schema::get_creation_sql($new_schema));
         }
     }
 }
 /**
  * Does this table constrain against a renamed table?
  * 
  * @param object $db_doc
  * @param object $schema
  * @param object $table
  * return boolean
  */
 public static function constrains_against_renamed_table($db_doc, $schema, $table)
 {
     foreach (format_constraint::get_table_constraints($db_doc, $schema, $table, 'constraint') as $constraint) {
         if (pgsql8_table::constraint_depends_on_renamed_table($db_doc, $constraint)) {
             dbsteward::info("NOTICE: " . $schema['name'] . "." . $table['name'] . " constrains against a renamed table with constraint " . $constraint['name']);
             return TRUE;
         }
     }
     return FALSE;
 }
 /**
  * Updates objects in schemas.
  *
  * @param $ofs1  stage1 output file segmenter
  * @param $ofs3  stage3 output file segmenter
  */
 public static function update_structure($ofs1, $ofs3)
 {
     if (!mysql5::$use_schema_name_prefix) {
         if (count(dbsteward::$new_database->schema) > 1) {
             throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
         }
         if (count(dbsteward::$old_database->schema) > 1) {
             throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
         }
     } else {
         dbsteward::info("Drop Old Schemas");
         self::drop_old_schemas($ofs3);
     }
     mysql5_diff_views::drop_views_ordered($ofs1, dbsteward::$old_database, dbsteward::$new_database);
     //@TODO: implement mysql5_language ? no relevant conversion exists see other TODO's stating this
     //mysql5_diff_languages::diff_languages($ofs1);
     // if the table dependency order is unknown, bang them in natural order
     if (!is_array(mysql5_diff::$new_table_dependency)) {
         foreach (dbx::get_schemas(dbsteward::$new_database) as $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']);
             mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
             mysql5_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
             mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $old_schema, $new_schema);
             // remove old constraints before table contraints, so the SQL statements succeed
             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);
             mysql5_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) {
             $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
             mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE);
         }
     } else {
         $processed_schemas = array();
         for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
             // find the necessary pointers
             $item = mysql5_diff::$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']);
             // 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)) {
                 mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
                 mysql5_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(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
             // find the necessary pointers
             $item = mysql5_diff::$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!");
             }
             // @NOTICE: when dropping constraints, dbx::renamed_table_check_pointer() is not called for $old_table
             // as mysql5_diff_tables::diff_constraints_table() will do rename checking when recreating constraints for renamed tables
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', TRUE);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', TRUE);
         }
         $processed_schemas = array();
         for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
             // find the necessary pointers
             $item = mysql5_diff::$new_table_dependency[$i];
             $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']);
             // 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)) {
                 mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $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']);
             }
             dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table, $new_table);
             // mysql5_diff_indexes::diff_indexes_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', FALSE);
             mysql5_diff_triggers::diff_triggers_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', FALSE);
         }
         // drop old tables in reverse dependency order
         for ($i = count(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
             // find the necessary pointers
             $item = mysql5_diff::$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!");
             }
             mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema, $old_table, $new_table);
         }
     }
     mysql5_diff_views::create_views_ordered($ofs3, dbsteward::$old_database, dbsteward::$new_database);
 }
 /**
  * composite postgresql schema_to_xml() database_to_xml() data outputs onto a dbsteward database definition
  *
  * @param SimpleXMLElement $base          full definition element to add data to
  * @param SimpleXMLElement $pgdatafiles   postgres table data XML from database_to_xml() to overlay in
  *
  * @return void
  */
 public static function xml_composite_pgdata(&$base, $pgdatafiles)
 {
     // psql -U deployment megatrain_nkiraly -c "select database_to_xml(true, false, 'http://dbsteward.org/pgdataxml');"
     /*
         <megatrain_nkiraly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://dbsteward.org/pgdataxml">
         <public>
         <system_status_list>
         <row>
         <system_status_list_id>1</system_status_list_id>
         <system_status>Active</system_status>
         </row>
         </system_status_list>
         </public>
         <search_results>
         </search_results>
         </megatrain_nkiraly>
         /**/
     foreach ($pgdatafiles as $file) {
         $file_name = realpath($file);
         dbsteward::notice("Loading postgres data XML " . $file_name);
         $xml_contents = @file_get_contents($file_name);
         if ($xml_contents === FALSE) {
             throw new exception("Failed to load postgres data XML from disk: " . $file_name);
         }
         $doc = simplexml_load_string($xml_contents);
         if ($doc === FALSE) {
             throw new Exception("failed to simplexml_load_string() contents of " . $file_name);
         }
         dbsteward::info("Compositing postgres data (size=" . strlen($xml_contents) . ")");
         foreach ($doc as $schema) {
             foreach ($schema as $table) {
                 $table_xpath = "schema[@name='" . $schema->getName() . "']/table[@name='" . $table->getName() . "']";
                 $nodes = $base->xpath($table_xpath);
                 if (count($nodes) != 1 || $nodes === FALSE) {
                     var_dump($nodes);
                     throw new exception("xpath did not yield one table match: " . $table_xpath . " - do the schema and table exist in your DBSteward XML?");
                 }
                 $node = $nodes[0];
                 // check for actual row children
                 $rows = $table->children();
                 if (count($rows) == 0) {
                     //throw new exception("table " . $table->getName() . " has no row children");
                 } else {
                     // first row members used to designate columns attribute
                     $row = $rows[0];
                     $columns = '';
                     $i = 0;
                     foreach ($row as $column) {
                         $columns .= $column->getName();
                         if ($i < count($row) - 1) {
                             $columns .= ', ';
                         }
                         $i++;
                     }
                     // switch rows pointer back to the document
                     $rows = $node->addChild('rows');
                     $rows->addAttribute('columns', $columns);
                     // pump all of the row cols
                     foreach ($table as $row) {
                         if (strcasecmp($row->getName(), 'row') != 0) {
                             throw new exception("schema->table->row iterator expected row tag but found " . $row->getName());
                         }
                         $node_row = $rows->addChild('row');
                         foreach ($row as $column) {
                             $node_row->addChild('col', self::ampersand_magic($column));
                         }
                     }
                 }
             }
         }
         // revalidate composited xml
         self::validate_xml($base->asXML());
     }
 }
 /**
  * Creates new schemas (not the objects inside the schemas)
  *
  * @param $ofs  output file segmenter
  */
 private static function create_new_schemas($ofs)
 {
     foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
         if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == NULL) {
             dbsteward::info("Crate New Schema " . $new_schema['name']);
             $ofs->write(mssql10_schema::get_creation_sql($new_schema));
         }
     }
 }
 /**
  * Does the specified constraint depend on a table
  * that has been renamed in the specified database definition?
  * 
  * @param object $db_doc
  * @param object $constraint
  * @return boolean
  * @throws exception
  */
 public static function constraint_depends_on_renamed_table($db_doc, $constraint)
 {
     if (dbsteward::$ignore_oldnames) {
         // don't check if ignore_oldnames is on
         return FALSE;
     }
     if (strpos($constraint['definition'], 'REFERENCES') !== FALSE) {
         //echo $constraint['schema_name'] . "." . $constraint['table_name'] . "  " . $constraint['name'] . " definition = " . $constraint['definition'] . "\n";
         if (preg_match(static::PATTERN_CONSTRAINT_REFERENCES_TABLE, $constraint['definition'], $matches) > 0) {
             $references_schema_name = $matches[1];
             $references_table_name = $matches[2];
         } else {
             throw new exception("Failed to parse REFERENCES definition for renamed table dependencies");
         }
         $references_schema = dbx::get_schema($db_doc, $references_schema_name);
         if (!$references_schema) {
             throw new exception("constraint references schema '" . $references_schema_name . "' not found in specified db_doc, check caller");
         }
         $references_table = dbx::get_table($references_schema, $references_table_name);
         if (!$references_table) {
             throw new exception("constraint references table '" . $references_table_name . "' not found in specified db_doc, schema " . $references_schema_name . ", check caller");
         }
         if (sql99_diff_tables::is_renamed_table($references_schema, $references_table)) {
             dbsteward::info("NOTICE: constraint " . $constraint['name'] . " for " . $constraint['schema_name'] . "." . $constraint['table_name'] . " references a renamed table -- " . $constraint['definition']);
             return TRUE;
         }
     }
     return FALSE;
 }
Exemple #7
0
 public function xml_data_insert($def_file, $data_file)
 {
     dbsteward::info("Automatic insert data into " . $def_file . " from " . $data_file);
     $def_doc = simplexml_load_file($def_file);
     if (!$def_doc) {
         throw new exception("Failed to load " . $def_file);
     }
     $data_doc = simplexml_load_file($data_file);
     if (!$data_doc) {
         throw new exception("Failed to load " . $data_file);
     }
     // for each of the tables defined, act on rows addColumns definitions
     foreach ($data_doc->schema as $data_schema) {
         $xpath = "schema[@name='" . $data_schema['name'] . "']";
         $def_schema = $def_doc->xpath($xpath);
         if (count($def_schema) == 0) {
             throw new exception("definition " . $xpath . " not found");
         }
         if (count($def_schema) > 1) {
             throw new exception("more than one " . $xpath . " found");
         }
         $def_schema = $def_schema[0];
         foreach ($data_schema->table as $data_table) {
             $xpath = "table[@name='" . $data_table['name'] . "']";
             $def_table = $def_schema->xpath($xpath);
             if (count($def_table) == 0) {
                 throw new exception("definition " . $xpath . " not found");
             }
             if (count($def_table) > 1) {
                 throw new exception("more than one " . $xpath . " found");
             }
             $def_table = $def_table[0];
             if (!isset($data_table->rows) || !isset($data_table->rows->row)) {
                 throw new exception($xpath . " rows->row definition is incomplete");
             }
             if (count($data_table->rows->row) > 1) {
                 throw new exception("Unexpected: more than one rows->row found in " . $xpath . " definition");
             }
             $definition_columns = preg_split("/[\\,\\s]+/", $def_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
             $new_columns = preg_split("/[\\,\\s]+/", $data_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
             for ($i = 0; $i < count($new_columns); $i++) {
                 $new_column = $new_columns[$i];
                 dbsteward::info("Adding rows column " . $new_column . " to definition table " . $def_table['name']);
                 if (in_array($new_column, $definition_columns)) {
                     throw new exception("new column " . $new_column . " is already defined in dbsteward definition file");
                 }
                 $def_table->rows['columns'] = $def_table->rows['columns'] . ", " . $new_column;
                 $col_value = $data_table->rows->row->col[$i];
                 // add the value to each row col set in def_table->rows
                 foreach ($def_table->rows->row as $row) {
                     $row->addChild('col', $col_value);
                 }
             }
         }
     }
     $def_file_modified = $def_file . '.xmldatainserted';
     dbsteward::notice("Saving modified dbsteward definition as " . $def_file_modified);
     return xml_parser::save_xml($def_file_modified, $def_doc->saveXML());
 }
Exemple #8
0
 public static function extract_schema($host, $port, $database, $user, $password)
 {
     $databases = explode(',', $database);
     dbsteward::notice("Connecting to mysql5 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
     // if not supplied, ask for the password
     if ($password === FALSE) {
         echo "Password: "******"Analyzing database {$database}");
         $db->use_database($database);
         $node_schema = $doc->addChild('schema');
         $node_schema['name'] = $database;
         $node_schema['owner'] = 'ROLE_OWNER';
         // extract global and schema permissions under the public schema
         foreach ($db->get_global_grants($user) as $db_grant) {
             $node_grant = $node_schema->addChild('grant');
             // There are 28 permissions encompassed by the GRANT ALL statement
             $node_grant['operation'] = $db_grant->num_ops == 28 ? 'ALL' : $db_grant->operations;
             $node_grant['role'] = self::translate_role_name($user, $doc);
             if ($db_grant->is_grantable) {
                 $node_grant['with'] = 'GRANT';
             }
         }
         $enum_types = array();
         $enum_type = function ($obj, $mem, $values) use(&$enum_types) {
             // if that set of values is defined by a previous enum, use that
             foreach ($enum_types as $name => $enum) {
                 if ($enum === $values) {
                     return $name;
                 }
             }
             // otherwise, make a new one
             $name = "enum_" . md5(implode('_', $values));
             $enum_types[$name] = $values;
             return $name;
         };
         foreach ($db->get_tables() as $db_table) {
             dbsteward::info("Analyze table options/partitions " . $db_table->table_name);
             $node_table = $node_schema->addChild('table');
             $node_table['name'] = $db_table->table_name;
             $node_table['owner'] = 'ROLE_OWNER';
             // because mysql doesn't have object owners
             $node_table['description'] = $db_table->table_comment;
             $node_table['primaryKey'] = '';
             if (stripos($db_table->create_options, 'partitioned') !== FALSE && ($partition_info = $db->get_partition_info($db_table))) {
                 $node_partition = $node_table->addChild('tablePartition');
                 $node_partition['sqlFormat'] = 'mysql5';
                 $node_partition['type'] = $partition_info->type;
                 switch ($partition_info->type) {
                     case 'HASH':
                     case 'LINEAR HASH':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'expression');
                         $opt->addAttribute('value', $partition_info->expression);
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'number');
                         $opt->addAttribute('value', $partition_info->number);
                         break;
                     case 'KEY':
                     case 'LINEAR KEY':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'columns');
                         $opt->addAttribute('value', $partition_info->columns);
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'number');
                         $opt->addAttribute('value', $partition_info->number);
                         break;
                     case 'LIST':
                     case 'RANGE':
                     case 'RANGE COLUMNS':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', $partition_info->type == 'RANGE COLUMNS' ? 'columns' : 'expression');
                         $opt->addAttribute('value', $partition_info->expression);
                         foreach ($partition_info->segments as $segment) {
                             $node_seg = $node_partition->addChild('tablePartitionSegment');
                             $node_seg->addAttribute('name', $segment->name);
                             $node_seg->addAttribute('value', $segment->value);
                         }
                         break;
                 }
             }
             foreach ($db->get_table_options($db_table) as $name => $value) {
                 if (strcasecmp($name, 'auto_increment') === 0 && !static::$use_auto_increment_table_options) {
                     // don't extract auto_increment tableOptions if we're not using them
                     continue;
                 }
                 $node_option = $node_table->addChild('tableOption');
                 $node_option['sqlFormat'] = 'mysql5';
                 $node_option['name'] = $name;
                 $node_option['value'] = $value;
             }
             dbsteward::info("Analyze table columns " . $db_table->table_name);
             foreach ($db->get_columns($db_table) as $db_column) {
                 $node_column = $node_table->addChild('column');
                 $node_column['name'] = $db_column->column_name;
                 if (!empty($db_column->column_comment)) {
                     $node_column['description'] = $db_column->column_comment;
                 }
                 // returns FALSE if not serial, int/bigint if it is
                 $type = $db->is_serial_column($db_table, $db_column);
                 if (!$type) {
                     $type = $db_column->column_type;
                     if (stripos($type, 'enum') === 0) {
                         $values = $db->parse_enum_values($db_column->column_type);
                         $type = $enum_type($db_table->table_name, $db_column->column_name, $values);
                     }
                     if ($db_column->is_auto_increment) {
                         $type .= ' AUTO_INCREMENT';
                     }
                 }
                 if ($db_column->is_auto_update) {
                     $type .= ' ON UPDATE CURRENT_TIMESTAMP';
                 }
                 $node_column['type'] = $type;
                 // @TODO: if there are serial sequences/triggers for the column then convert to serial
                 if ($db_column->column_default !== NULL) {
                     $node_column['default'] = mysql5::escape_default_value($db_column->column_default);
                 } elseif (strcasecmp($db_column->is_nullable, 'YES') === 0) {
                     $node_column['default'] = 'NULL';
                 }
                 $node_column['null'] = strcasecmp($db_column->is_nullable, 'YES') === 0 ? 'true' : 'false';
             }
             // get all plain and unique indexes
             dbsteward::info("Analyze table indexes " . $db_table->table_name);
             foreach ($db->get_indices($db_table) as $db_index) {
                 // don't process primary key indexes here
                 if (strcasecmp($db_index->index_name, 'PRIMARY') === 0) {
                     continue;
                 }
                 // implement unique indexes on a single column as unique column, but only if the index name is the column name
                 if ($db_index->unique && count($db_index->columns) == 1 && strcasecmp($db_index->columns[0], $db_index->index_name) === 0) {
                     $column = $db_index->columns[0];
                     $node_column = dbx::get_table_column($node_table, $column);
                     if (!$node_column) {
                         throw new Exception("Unexpected: Could not find column node {$column} for unique index {$db_index->index_name}");
                     } else {
                         $node_column = $node_column[0];
                     }
                     $node_column['unique'] = 'true';
                 } else {
                     $node_index = $node_table->addChild('index');
                     $node_index['name'] = $db_index->index_name;
                     $node_index['using'] = strtolower($db_index->index_type);
                     $node_index['unique'] = $db_index->unique ? 'true' : 'false';
                     $i = 1;
                     foreach ($db_index->columns as $column_name) {
                         $node_index->addChild('indexDimension', $column_name)->addAttribute('name', $column_name . '_' . $i++);
                     }
                 }
             }
             // get all primary/foreign keys
             dbsteward::info("Analyze table constraints " . $db_table->table_name);
             foreach ($db->get_constraints($db_table) as $db_constraint) {
                 if (strcasecmp($db_constraint->constraint_type, 'primary key') === 0) {
                     $node_table['primaryKey'] = implode(',', $db_constraint->columns);
                 } elseif (strcasecmp($db_constraint->constraint_type, 'foreign key') === 0) {
                     // mysql sees foreign keys as indexes pointing at indexes.
                     // it's therefore possible for a compound index to point at a compound index
                     if (!$db_constraint->referenced_columns || !$db_constraint->referenced_table_name) {
                         throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} does not refer to any foreign columns");
                     }
                     if (count($db_constraint->referenced_columns) == 1 && count($db_constraint->columns) == 1) {
                         // not a compound index, define the FK inline in the column
                         $column = $db_constraint->columns[0];
                         $ref_column = $db_constraint->referenced_columns[0];
                         $node_column = dbx::get_table_column($node_table, $column);
                         if (!$node_column) {
                             throw new Exception("Unexpected: Could not find column node {$column} for foreign key constraint {$db_constraint->constraint_name}");
                         }
                         $node_column['foreignSchema'] = $db_constraint->referenced_table_schema;
                         $node_column['foreignTable'] = $db_constraint->referenced_table_name;
                         $node_column['foreignColumn'] = $ref_column;
                         unset($node_column['type']);
                         // inferred from referenced column
                         $node_column['foreignKeyName'] = $db_constraint->constraint_name;
                         // RESTRICT is the default, leave it implicit if possible
                         if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
                             $node_column['foreignOnDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
                         }
                         if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
                             $node_column['foreignOnUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
                         }
                     } elseif (count($db_constraint->referenced_columns) > 1 && count($db_constraint->referenced_columns) == count($db_constraint->columns)) {
                         $node_fkey = $node_table->addChild('foreignKey');
                         $node_fkey['columns'] = implode(', ', $db_constraint->columns);
                         $node_fkey['foreignSchema'] = $db_constraint->referenced_table_schema;
                         $node_fkey['foreignTable'] = $db_constraint->referenced_table_name;
                         $node_fkey['foreignColumns'] = implode(', ', $db_constraint->referenced_columns);
                         $node_fkey['constraintName'] = $db_constraint->constraint_name;
                         // RESTRICT is the default, leave it implicit if possible
                         if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
                             $node_fkey['onDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
                         }
                         if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
                             $node_fkey['onUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
                         }
                     } else {
                         var_dump($db_constraint);
                         throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} has mismatched columns");
                     }
                 } elseif (strcasecmp($db_constraint->constraint_type, 'unique') === 0) {
                     dbsteward::warning("Ignoring UNIQUE constraint '{$db_constraint->constraint_name}' because they are implemented as indices");
                 } elseif (strcasecmp($db_constraint->constraint_type, 'check') === 0) {
                     // @TODO: implement CHECK constraints
                 } else {
                     throw new exception("unknown constraint_type {$db_constraint->constraint_type}");
                 }
             }
             foreach ($db->get_table_grants($db_table, $user) as $db_grant) {
                 dbsteward::info("Analyze table permissions " . $db_table->table_name);
                 $node_grant = $node_table->addChild('grant');
                 $node_grant['operation'] = $db_grant->operations;
                 $node_grant['role'] = self::translate_role_name($user, $doc);
                 if ($db_grant->is_grantable) {
                     $node_grant['with'] = 'GRANT';
                 }
             }
         }
         foreach ($db->get_sequences() as $db_seq) {
             $node_seq = $node_schema->addChild('sequence');
             $node_seq['name'] = $db_seq->name;
             $node_seq['owner'] = 'ROLE_OWNER';
             $node_seq['start'] = $db_seq->start_value;
             $node_seq['min'] = $db_seq->min_value;
             $node_seq['max'] = $db_seq->max_value;
             $node_seq['inc'] = $db_seq->increment;
             $node_seq['cycle'] = $db_seq->cycle ? 'true' : 'false';
             // the sequences table is a special case, since it's not picked up in the tables loop
             $seq_table = $db->get_table(mysql5_sequence::TABLE_NAME);
             foreach ($db->get_table_grants($seq_table, $user) as $db_grant) {
                 $node_grant = $node_seq->addChild('grant');
                 $node_grant['operation'] = $db_grant->operations;
                 $node_grant['role'] = self::translate_role_name($doc, $user);
                 if ($db_grant->is_grantable) {
                     $node_grant['with'] = 'GRANT';
                 }
             }
         }
         foreach ($db->get_functions() as $db_function) {
             dbsteward::info("Analyze function " . $db_function->routine_name);
             $node_fn = $node_schema->addChild('function');
             $node_fn['name'] = $db_function->routine_name;
             $node_fn['owner'] = 'ROLE_OWNER';
             $node_fn['returns'] = $type = $db_function->dtd_identifier;
             if (strcasecmp($type, 'enum') === 0) {
                 $node_fn['returns'] = $enum_type($db_function->routine_name, 'returns', $db->parse_enum_values($db_function->dtd_identifier));
             }
             $node_fn['description'] = $db_function->routine_comment;
             if (isset($db_function->procedure) && $db_function->procedure) {
                 $node_fn['procedure'] = 'true';
             }
             // $node_fn['procedure'] = 'false';
             $eval_type = $db_function->sql_data_access;
             // srsly mysql? is_deterministic varchar(3) not null default '', contains YES or NO
             $determinism = strcasecmp($db_function->is_deterministic, 'YES') === 0 ? 'DETERMINISTIC' : 'NOT DETERMINISTIC';
             $node_fn['cachePolicy'] = mysql5_function::get_cache_policy_from_characteristics($determinism, $eval_type);
             $node_fn['mysqlEvalType'] = str_replace(' ', '_', $eval_type);
             // INVOKER is the default, leave it implicit when possible
             if (strcasecmp($db_function->security_type, 'definer') === 0) {
                 $node_fn['securityDefiner'] = 'true';
             }
             foreach ($db_function->parameters as $param) {
                 $node_param = $node_fn->addChild('functionParameter');
                 // not supported in mysql functions, even though it's provided?
                 // $node_param['direction'] = strtoupper($param->parameter_mode);
                 $node_param['name'] = $param->parameter_name;
                 $node_param['type'] = $type = $param->dtd_identifier;
                 if (strcasecmp($type, 'enum') === 0) {
                     $node_param['type'] = $enum_type($db_function->routine_name, $param->parameter_name, $db->parse_enum_values($param->dtd_identifier));
                 }
                 if (isset($param->direction)) {
                     $node_param['direction'] = $param->direction;
                 }
             }
             $node_def = $node_fn->addChild('functionDefinition', $db_function->routine_definition);
             $node_def['language'] = 'sql';
             $node_def['sqlFormat'] = 'mysql5';
         }
         foreach ($db->get_triggers() as $db_trigger) {
             dbsteward::info("Analyze trigger " . $db_trigger->name);
             $node_trigger = $node_schema->addChild('trigger');
             foreach ((array) $db_trigger as $k => $v) {
                 $node_trigger->addAttribute($k, $v);
             }
             $node_trigger->addAttribute('sqlFormat', 'mysql5');
         }
         foreach ($db->get_views() as $db_view) {
             dbsteward::info("Analyze view " . $db_view->view_name);
             if (!empty($db_view->view_name) && empty($db_view->view_query)) {
                 throw new Exception("Found a view in the database with an empty query. User '{$user}' problaby doesn't have SELECT permissions on tables referenced by the view.");
             }
             $node_view = $node_schema->addChild('view');
             $node_view['name'] = $db_view->view_name;
             $node_view['owner'] = 'ROLE_OWNER';
             $node_view->addChild('viewQuery', $db_view->view_query)->addAttribute('sqlFormat', 'mysql5');
         }
         foreach ($enum_types as $name => $values) {
             $node_type = $node_schema->addChild('type');
             $node_type['type'] = 'enum';
             $node_type['name'] = $name;
             foreach ($values as $v) {
                 $node_type->addChild('enum')->addAttribute('name', $v);
             }
         }
     }
     xml_parser::validate_xml($doc->asXML());
     return xml_parser::format_xml($doc->saveXML());
 }
Exemple #9
0
 /**
  * compare composite db doc to specified database
  *
  * @return string XML
  */
 public static function compare_db_data($db_doc, $host, $port, $database, $user, $password)
 {
     dbsteward::notice("Connecting to pgsql8 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
     // if not supplied, ask for the password
     if ($password === FALSE) {
         // @TODO: mask the password somehow without requiring a PHP extension
         echo "Password: "******"host={$host} port={$port} dbname={$database} user={$user} password={$password}");
     dbsteward::info("Comparing composited dbsteward definition data rows to postgresql database connection table contents");
     // compare the composited dbsteward document to the established database connection
     // effectively looking to see if rows are found that match primary keys, and if their contents are the same
     foreach ($db_doc->schema as $schema) {
         foreach ($schema->table as $table) {
             if (isset($table->rows)) {
                 $table_name = dbsteward::string_cast($schema['name']) . '.' . dbsteward::string_cast($table['name']);
                 $primary_key_cols = self::primary_key_split($table['primaryKey']);
                 $cols = preg_split("/[\\,\\s]+/", $table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
                 $col_types = array();
                 foreach ($table->column as $table_column) {
                     $type = '';
                     // foreign keyed columns inherit their foreign reference type
                     if (isset($table_column['foreignTable']) && isset($table_column['foreignColumn'])) {
                         if (strlen($type) > 0) {
                             throw new exception("type of " . $type . " was found for " . dbsteward::string_cast($cols[$j]) . " in table " . dbsteward::string_cast($table['name']) . " but it is foreign keyed!");
                         }
                         $foreign = array();
                         dbx::foreign_key($db_doc, $schema, $table, $table_column, $foreign);
                         // don't need to error-check, foreign_key() is self-checking if it doesnt find the fkey col it will complain
                         $type = $foreign['column']['type'];
                     } else {
                         $type = dbsteward::string_cast($table_column['type']);
                     }
                     if (strlen($type) == 0) {
                         throw new exception($table_name . " column " . $table_column['name'] . " type not found!");
                     }
                     $col_types[dbsteward::string_cast($table_column['name'])] = $type;
                 }
                 foreach ($table->rows->row as $row) {
                     // glue the primary key expression together for the where
                     $primary_key_expression = '';
                     for ($k = 0; $k < count($primary_key_cols); $k++) {
                         $column_name = pgsql8::get_quoted_column_name($primary_key_cols[$k]);
                         $pk_index = array_search($primary_key_cols[$k], $cols);
                         if ($pk_index === FALSE) {
                             throw new exception("failed to find " . $schema['name'] . "." . $table['name'] . " primary key column " . $primary_key_cols[$k] . " in cols list (" . implode(", ", $cols) . ")");
                         }
                         $primary_key_expression .= $column_name . " = " . pgsql8::value_escape($col_types[$primary_key_cols[$k]], $row->col[$pk_index], $db_doc);
                         if ($k < count($primary_key_cols) - 1) {
                             $primary_key_expression .= ' AND ';
                         }
                     }
                     $sql = "SELECT *\n              FROM " . $table_name . "\n              WHERE " . $primary_key_expression;
                     $rs = pgsql8_db::query($sql);
                     // is the row supposed to be deleted?
                     if (strcasecmp('true', $row['delete']) == 0) {
                         if (pg_num_rows($rs) > 0) {
                             dbsteward::notice($table_name . " row marked for DELETE found WHERE " . $primary_key_expression);
                         }
                     } else {
                         if (pg_num_rows($rs) == 0) {
                             dbsteward::notice($table_name . " does not contain row WHERE " . $primary_key_expression);
                         } else {
                             if (pg_num_rows($rs) > 1) {
                                 dbsteward::notice($table_name . " contains more than one row WHERE " . $primary_key_expression);
                                 while (($db_row = pg_fetch($rs)) !== FALSE) {
                                     dbsteward::notice("\t" . implode(', ', $db_row));
                                 }
                             } else {
                                 $db_row = pg_fetch_assoc($rs);
                                 // make sure any aspects of the $row are present in the $db_row
                                 for ($i = 0; $i < count($cols); $i++) {
                                     $xml_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($row->col[$i]));
                                     $db_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($db_row[$cols[$i]]));
                                     $values_match = FALSE;
                                     // evaluate if they are equal
                                     $values_match = $xml_value == $db_value;
                                     // if they are not PHP equal, and are alternate expressionable, ask the database
                                     if (!$values_match && preg_match('/^time.*|^date.*|^interval/i', $col_types[$cols[$i]]) > 0) {
                                         // do both describe atleast some value (greater than zero len?)
                                         if (strlen($xml_value) > 0 && strlen($db_value) > 0) {
                                             $sql = "SELECT '{$xml_value}'::" . $col_types[$cols[$i]] . " = '{$db_value}'::" . $col_types[$cols[$i]] . " AS equal_eval";
                                             $values_match = pgsql8_db::query_str($sql) == 't';
                                         }
                                     }
                                     if (!$values_match) {
                                         dbsteward::warning($table_name . " row column WHERE (" . $primary_key_expression . ") " . $cols[$i] . " data does not match database row column: '" . $xml_value . "' VS '" . $db_value . "'");
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
         }
     }
     //xml_parser::validate_xml($db_doc->asXML());
     return xml_parser::format_xml($db_doc->saveXML());
 }
 /**
  * Generate column defaults from column definitions, returns FALSE if
  * no defaults were defined, otherwise return the
  * ALTER TABLE ALTER COLUMN SET statements needed.
  * 
  * Don't know if this would work with functions referenced as function(argument1 ... argumentN)
  * 
  * @param type $node_schema
  * @param type $node_table
  * @param type $node_column
  * @param type $add_defaults
  * @param type $include_null_definition
  * @param type $include_default_nextval
  * @return boolean|string
  */
 public static function set_column_defaults($node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_default_nextval = TRUE)
 {
     $fq_table_name = pgsql8::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
     $base_sql = "ALTER TABLE " . $fq_table_name . " ALTER COLUMN " . pgsql8::get_quoted_column_name($node_column['name']) . " SET";
     $sql = $base_sql;
     $changes = FALSE;
     if (strlen($node_column['default']) > 0) {
         if (!$include_default_nextval && static::has_default_nextval($node_table, $node_column)) {
             // if the default is a nextval expression, don't specify it in the regular full definition
             // because if the sequence has not been defined yet,
             // the nextval expression will be evaluated inline and fail
             dbsteward::info("Skipping " . $node_column['name'] . " default expression \"" . $node_column['default'] . "\" - this default expression will be applied after all sequences have been created");
             return $changes;
         } else {
             $sql .= " DEFAULT " . $node_column['default'];
             $changes = TRUE;
         }
     } else {
         if (!pgsql8_column::null_allowed($node_table, $node_column) && $add_defaults) {
             $default_col_value = pgsql8_column::get_default_value($node_column['type']);
             if ($default_col_value != null) {
                 $sql .= " DEFAULT " . $default_col_value;
                 $changes = TRUE;
             }
         }
     }
     if ($include_null_definition && !pgsql8_column::null_allowed($node_table, $node_column)) {
         if ($changes) {
             $sql .= ";\n";
             $sql .= $base_sql . " NOT NULL";
         } else {
             $sql .= " NOT NULL";
             $changes = TRUE;
         }
     }
     // no changes? we don't have a default for this column... keep going pls
     if (!$changes) {
         return $changes;
     }
     $sql .= ";\n";
     return $sql;
 }
 /**
  * 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);
         }
     }
 }
Exemple #12
0
 public function build_upgrade($old_output_prefix, $old_composite_file, $old_db_doc, $old_files, $new_output_prefix, $new_composite_file, $new_db_doc, $new_files)
 {
     // place the upgrade files with the new_files set
     $upgrade_prefix = $new_output_prefix . '_upgrade';
     // mssql10_diff needs these to intelligently create SQL difference statements in dependency order
     dbsteward::info("Calculating old table foreign key dependency order..");
     mssql10_diff::$old_table_dependency = xml_parser::table_dependency_order($old_db_doc);
     dbsteward::info("Calculating new table foreign key dependency order..");
     mssql10_diff::$new_table_dependency = xml_parser::table_dependency_order($new_db_doc);
     mssql10_diff::diff_doc($old_composite_file, $new_composite_file, $old_db_doc, $new_db_doc, $upgrade_prefix);
     return $new_db_doc;
 }
 /**
  * Adds commands for modification of columns to the list of
  * commands.
  *
  * @param commands list of commands
  * @param old_table original table
  * @param new_table new table
  * @param drop_defaults_columns list for storing columns for which default value should be dropped
  */
 private static function add_modify_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns)
 {
     foreach (dbx::get_table_columns($new_table) as $new_column) {
         if (!mssql10_table::contains_column($old_table, $new_column['name'])) {
             continue;
         }
         if (!dbsteward::$ignore_oldnames && mssql10_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) {
             // oldColumnName renamed column ? skip definition diffing on it, it is being renamed
             continue;
         }
         $quoted_table_name = mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']);
         $old_column = dbx::get_table_column($old_table, $new_column['name']);
         $new_column_name = mssql10::get_quoted_column_name($new_column['name']);
         $old_column_type = null;
         if ($old_column) {
             $old_column_type = mssql10_column::column_type(dbsteward::$old_database, $new_schema, $old_table, $old_column, $foreign);
         }
         $new_column_type = mssql10_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign);
         if (strcmp($old_column_type, $new_column_type) != 0) {
             // ALTER TYPE .. USING support by looking up the new type in the xml definition
             $type_using = '';
             $type_using_comment = '';
             if (isset($new_column['convertUsing'])) {
                 $type_using = ' USING ' . $new_column['convertUsing'] . ' ';
                 $type_using_comment = '- found XML convertUsing: ' . $new_column['convertUsing'] . ' ';
             }
             // if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum
             if (mssql10_column::enum_type_check(dbsteward::$new_database, $new_schema, $new_table, $new_column, $drop_sql, $add_sql)) {
                 // enum types rewritten as varchar(255)
                 $new_column_type = 'varchar(255)';
                 $commands[] = array('stage' => 'AFTER1', 'command' => $drop_sql);
                 $commands[] = array('stage' => 'AFTER1', 'command' => $add_sql);
             }
             $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . $type_using . " /* TYPE change - table: " . $new_table['name'] . " original: " . $old_column_type . " new: " . $new_column_type . ' ' . $type_using_comment . '*/');
         }
         $old_default = isset($old_column['default']) ? $old_column['default'] : '';
         $new_default = isset($new_column['default']) ? $new_column['default'] : '';
         // has the default has changed?
         if (strcmp($old_default, $new_default) != 0) {
             // in MSSQL, violating the SQL standard,
             // inline column default definitions are translated to be table constraints
             // the constraint name is somewhat predictable, but not always.
             // some versions apped random numebrs when implicitly creating the constraint
             // was there a constraint before?
             if (strlen($old_default) > 0) {
                 $commands[] = array('stage' => 'BEFORE1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' DROP CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $old_column['name'] . ';');
             }
             // is there now a default constraint?
             if (strlen($new_default) > 0) {
                 $commands[] = array('stage' => 'AFTER1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' ADD CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $new_column['name'] . ' DEFAULT ' . $new_default . ' FOR ' . $new_column_name . ';');
             }
         }
         if (strcasecmp($old_column['null'], $new_column['null']) != 0) {
             if (mssql10_column::null_allowed($new_table, $new_column)) {
                 $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NULL");
             } else {
                 if (mssql10_diff::$add_defaults) {
                     $default_value = mssql10_column::get_default_value($new_column_type);
                     if ($default_value != NULL) {
                         $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $default_value);
                         $drop_defaults_columns[] = $new_column;
                     }
                 }
                 // if the default value is defined in the dbsteward XML
                 // set the value of the column to the default in end of stage 1 so that NOT NULL can be applied in stage 3
                 // this way custom <sql> tags can be avoided for upgrade generation if defaults are specified
                 if (strlen($new_column['default']) > 0) {
                     $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " SET " . $new_column_name . " = " . $new_column['default'] . " WHERE " . $new_column_name . " IS NULL; -- has_default_now: make modified column that is null the default value before NOT NULL hits");
                 }
                 // before altering column, remove any constraint that would stop us from doing so
                 foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) {
                     if (preg_match('/' . $new_column['name'] . '[\\s,=)]/', $constraint['definition']) > 0) {
                         $commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_drop_sql_change_statement($constraint));
                     }
                 }
                 $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NOT NULL");
                 // add the constraint back on
                 foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) {
                     if (preg_match('/' . $new_column['name'] . '[\\s,=\\)]/', $constraint['definition']) > 0) {
                         $commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_sql_change_statement($constraint));
                     }
                 }
             }
         }
         // for identity()'d columns (serial in dbsteward definition) in mssql that are to no longer be
         // we must recreate the table with out the identity attribute
         if (preg_match('/int\\sidentity.*$/', $old_column['type']) > 0 && ($new_column['type'] == 'int' || $new_column['type'] == 'bigint')) {
             dbsteward::warning("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " requires rebuild to drop identity property on " . $new_column['name']);
             // create a "deep copy" of the table so column and rows
             // references are not altered in the original old DOM
             $table_for_modifying_xml = $new_table->asXML();
             $table_for_modifying = simplexml_load_string($table_for_modifying_xml);
             // @NOTICE: we do this because of by reference nature of get_table_column()
             // any subsequent references to old table's pkey column(s) would show the type as int/bigint and not the identity() definition
             // get the column then modify the type to remove the identity
             $old_id_pkey_col = dbx::get_table_column($table_for_modifying, $old_column['name']);
             $table_for_modifying['name'] = 'tmp_identity_drop_' . $table_for_modifying['name'];
             if (preg_match('/^int/', $old_column['type']) > 0) {
                 $old_id_pkey_col['type'] = 'int';
             } else {
                 $old_id_pkey_col['type'] = 'bigint';
             }
             // see FS#25730 - dbsteward not properly upgrading serial to int
             // http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/
             // section start comment
             $identity_transition_commands = array('-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild is necessary');
             // get the creation sql for a temporary table
             $identity_transition_commands[] = mssql10_table::get_creation_sql($new_schema, $table_for_modifying);
             // copy over all the old data into new data, it's the only way
             $identity_transition_commands[] = "IF EXISTS(SELECT * FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']) . ")\n          EXEC('INSERT INTO " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($table_for_modifying['name']) . " ( " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . ")\n            SELECT " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . "\n            FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " WITH (HOLDLOCK TABLOCKX)');";
             // drop FKEYs other tables have to the table
             $other_tables_foreign_keying_constraints = dbx::get_tables_foreign_keying_to_table(dbsteward::$new_database, mssql10_diff::$new_table_dependency, $new_schema, $new_table);
             dbsteward::info("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " rebuild has " . count($other_tables_foreign_keying_constraints) . " foreign key references to drop and reapply");
             foreach ($other_tables_foreign_keying_constraints as $constraint) {
                 $identity_transition_commands[] = mssql10_table::get_constraint_drop_sql($constraint);
             }
             // drop the old table
             $identity_transition_commands[] = "DROP TABLE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . ";";
             // rename temporary table to original name
             // NOTE: sp_rename only takes an identifier for the new name, if you schema qualify the new name it will get doubled on the table name
             $identity_transition_commands[] = "EXECUTE sp_rename '" . $new_schema['name'] . "." . $table_for_modifying['name'] . "', '" . $new_table['name'] . "', 'OBJECT';";
             // mssql10_table:::get_creation_sql() only creates the table
             // now that it has been renamed, recreate the table's indexes keys and triggers
             $tc_buffer = fopen("php://memory", "rw");
             $tc_ofs = new output_file_segmenter('identity_transition_command', 1, $tc_buffer, 'identity_transition_command_buffer');
             mssql10_diff_indexes::diff_indexes_table($tc_ofs, NULL, NULL, $new_schema, $new_table);
             mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'primaryKey', FALSE);
             mssql10_diff_triggers::diff_triggers_table($tc_ofs, NULL, NULL, $new_schema, $new_table);
             mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'constraint', FALSE);
             rewind($tc_buffer);
             while (($tc_line = fgets($tc_buffer, 4096)) !== false) {
                 $identity_transition_commands[] = $tc_line;
             }
             unset($tc_ofs);
             // restore FKEYs other tables have to the table
             foreach ($other_tables_foreign_keying_constraints as $constraint) {
                 $identity_transition_commands[] = mssql10_table::get_constraint_sql($constraint);
             }
             // section end comment
             $identity_transition_commands[] = '-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild end' . "\n";
             // put all of the identity_transition_commands into the command list as BEFORE3's
             // this will make the identity column changes occur at the beginning of stage 3
             foreach ($identity_transition_commands as $itc) {
                 $commands[] = array('stage' => 'BEFORE3', 'command' => $itc);
             }
         }
     }
 }