/**
* Parses CREATE TRIGGER command.
*
* @param database database
* @param command CREATE TRIGGER command
*
* @throws ParserException Thrown if problem occured while parsing the
* command.
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN, trim($command), $matches) > 0) {
$trigger_name = trim($matches[1]);
$when = $matches[2];
$events = array();
if (strlen($matches[3]) > 0) {
$events[] = $matches[3];
}
if (strlen($matches[4]) > 0) {
$events[] = $matches[4];
}
if (strlen($matches[5]) > 0) {
$events[] = $matches[5];
}
$table_name = trim($matches[6]);
$fireOn = $matches[7];
$procedure = $matches[8];
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database));
$node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name));
if ($node_table == null) {
throw new exception("Failed to find trigger table " . $trigger->get_table_name());
}
$node_trigger =& dbx::get_table_trigger($node_schema, $node_table, $trigger_name, true);
dbx::set_attribute($node_trigger, 'when', strcasecmp('BEFORE', $when) == 0 ? 'BEFORE' : 'AFTER');
dbx::set_attribute($node_trigger, 'forEach', strcasecmp('ROW', $when) == 0 ? 'ROW' : 'STATEMENT');
dbx::set_attribute($node_trigger, 'function', trim($procedure));
dbx::set_attribute($node_trigger, 'event', implode(', ', $events));
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Creates and returns SQL for dropping the trigger.
*
* @return created SQL
*/
public static function get_drop_sql($node_schema, $node_trigger)
{
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == null) {
throw new exception("Failed to find trigger table " . $node_trigger['table'] . " in schema node " . $node_schema['name']);
}
$table_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_table['name']);
$ddl = "DROP TRIGGER " . pgsql8::get_quoted_object_name($node_trigger['name']) . " ON " . $table_name . ";\n";
return $ddl;
}
/**
* Outputs DDL for differences in triggers.
*
* @param $ofs output file pointer
* @param $oldSchema original schema
* @param $newSchema new schema
*/
public static function diff_triggers($ofs, $old_schema, $new_schema)
{
foreach (dbx::get_tables($new_schema) as $new_table) {
if ($old_schema == null) {
$old_table = null;
} else {
$old_table = dbx::get_table($old_schema, $new_table['name']);
}
static::diff_triggers_table($ofs, $old_schema, $old_table, $new_schema, $new_table);
}
}
/**
* Outputs commands for differences in constraints.
*
* @param object $ofs output file pointer
* @param object $old_schema original schema
* @param object $new_schema new schema
* @param string $type type of constraints to process
* @param boolean $drop_constraints
*/
public static function diff_constraints($ofs, $old_schema, $new_schema, $type, $drop_constraints)
{
foreach (dbx::get_tables($new_schema) as $new_table) {
if ($old_schema == null) {
$old_table = null;
} else {
$old_table = dbx::get_table($old_schema, $new_table['name']);
}
static::diff_constraints_table($ofs, $old_schema, $old_table, $new_schema, $new_table, $type, $drop_constraints);
}
}
/**
* Outputs DDL for differences in triggers.
*
* @param $ofs output file pointer
* @param $oldSchema original schema
* @param $newSchema new schema
*/
public static function diff_triggers($ofs, $old_schema, $new_schema)
{
foreach (dbx::get_tables($new_schema) as $new_table) {
pgsql8::set_context_replica_set_id($new_table);
if ($old_schema == null) {
$old_table = null;
} else {
$old_table = dbx::get_table($old_schema, $new_table['name']);
}
self::diff_triggers_table($ofs, $old_schema, $old_table, $new_schema, $new_table);
}
}
public static function get_creation_sql($node_schema, $node_trigger)
{
$events = self::get_events($node_trigger);
if (strcasecmp($node_trigger['sqlFormat'], dbsteward::get_sql_format())) {
$note = "Ignoring {$node_trigger['sqlFormat']} trigger '{$node_trigger['name']}'";
dbsteward::warning($note);
return "-- {$note}\n";
}
if (empty($node_trigger['function'])) {
throw new Exception("No trigger body defined for trigger '{$node_trigger['name']}'");
}
if (!($when = self::validate_when($node_trigger['when']))) {
throw new Exception("Invalid WHEN clause for trigger '{$node_trigger['name']}': '{$node_trigger['when']}'");
}
$notes = "";
if (count($events) == 0) {
throw new Exception("No events were given for trigger {$node_trigger['name']}");
} elseif (count($events) > 1) {
$notes .= "-- You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time\n";
$notes .= "-- generating separate triggers for each event\n";
dbsteward::warning("You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time");
dbsteward::warning(" generating separate triggers for each event");
}
if (!empty($node_trigger['forEach']) && strcasecmp($node_trigger['forEach'], 'row')) {
dbsteward::error($notes .= "-- You specified a forEach value of {$node_trigger['forEach']} on trigger {$node_trigger['name']} but MySQL only supports ROW - ignoring\n");
}
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == null) {
throw new exception("Failed to find trigger's table '{$node_trigger['table']}' in schema node '{$node_schema['name']}'");
}
$table_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
// always drop triggers before creating them
$ddl = static::get_drop_sql($node_schema, $node_trigger);
$single = count($events) == 1;
foreach ($events as $event) {
if (!($event = self::validate_event($event))) {
throw new Exception("Invalid event on trigger '{$node_trigger['name']}': '{$event}'");
}
// @TODO: use something like get_fully_qualified_object_name
$trigger_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . ($single ? '' : "_{$event}"));
$trigger_fn = trim($node_trigger['function']);
if (substr($trigger_fn, -1) != ';') {
$trigger_fn .= ';';
}
$ddl .= <<<SQL
CREATE TRIGGER {$trigger_name} {$when} {$event} ON {$table_name}
FOR EACH ROW {$trigger_fn}
SQL;
}
return $notes . $ddl;
}
/**
* Parses CREATE TABLE command.
*
* @param database database
* @param command CREATE TABLE command
*
*/
public static function parse($database, $command)
{
$line = $command;
if (preg_match(self::PATTERN_TABLE_NAME, $line, $matches) > 0) {
$table_name = trim($matches[1]);
$line = preg_replace(self::PATTERN_TABLE_NAME, '', $line);
} else {
throw new exception("Cannot parse command: " . $line);
}
$table_name = sql_parser::get_schema_name($table_name, $database) . '.' . sql_parser::get_object_name($table_name);
$schema_name = sql_parser::get_schema_name($table_name, $database);
$node_schema = dbx::get_schema($database, $schema_name);
if ($node_schema == null) {
throw new exception("Cannot get schema '" . $schema_name . "'. Need to issue 'CREATE SCHEMA " . $schema_name . ";' before 'CREATE TABLE " . $table_name . "...;'?");
}
$node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name), true);
self::parse_rows($node_schema, $node_table, sql_parser::remove_last_semicolon($line));
}
/**
* Parses CREATE INDEX command.
*
* @param database database
* @param command CREATE INDEX command
*/
public static function parse($database, $command)
{
if (preg_match(self::CREATE_PATTERN, trim($command), $matches) > 0) {
$unique_value = strlen(trim($matches[1])) > 0 ? 'true' : 'false';
$index_name = $matches[2];
$table_name = $matches[3];
$using = trim($matches[4]);
if ($index_name == null || $table_name == null || $using == null) {
throw new exception("Cannot parse command: " . $command);
}
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name(trim($table_name), $database));
$node_table =& dbx::get_table($node_schema, sql_parser::get_object_name(trim($table_name)));
if ($node_table == null) {
throw new exception("Failed to find table: " . $table_name);
}
$node_index =& dbx::create_table_index($node_table, $index_name);
dbx::set_attribute($node_index, 'using', $using);
dbx::set_attribute($node_index, 'unique', $unique_value);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
public static function parse($database, $command)
{
if (preg_match(self::PATTERN_INSERT_INTO, $command, $matches) > 0) {
$line = $command;
$table_name = $matches[1];
$columns_signature = md5($matches[2]);
if (!isset(self::$columns_cache[$columns_signature])) {
self::$columns_cache[$columns_signature] = $columns = self::column_split($matches[2], array('"'));
} else {
$columns = self::$columns_cache[$columns_signature];
}
$data_row = self::column_split($matches[3], array("'"));
if (count($columns) != count($data_row)) {
throw new exception("column count " . count($columns) . " does not match data_row count " . count($data_row));
}
// merge together as an alpha index row
for ($i = 0; $i < count($columns); $i++) {
$row[$columns[$i]] = $data_row[$i];
}
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database));
if ($node_schema == null) {
throw new exception("Failed to find schema for data append: " . sql_parser::get_schema_name($table_name, $database));
}
$node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name));
if ($node_table == null) {
throw new exception("Failed to find table for data append: " . $table_name);
}
try {
pgsql8_table::add_data_row($node_table, $row);
} catch (Exception $e) {
var_dump($command);
throw $e;
}
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Attepts to find a column on a foreign table.
* Walks up table inheritance chains.
* If the foreign column is itself a foreign key, resolves the type of that column before returning.
*/
private static function resolve_foreign_column($db_doc, $local_schema, $local_table, $local_colname, $foreign_schema, $foreign_table, $foreign_colname, $visited = array())
{
// walk up the foreign table inheritance chain to find the foreign column definition
$fschema = $foreign_schema;
$ftable = $foreign_table;
do {
$foreign_column = dbx::get_table_column($ftable, $foreign_colname);
if ($ftable['inheritsSchema']) {
$fschema = dbx::get_schema($db_doc, (string) $ftable['inheritsSchema']);
}
if ($ftable['inheritsTable']) {
$ftable = dbx::get_table($fschema, (string) $ftable['inheritsTable']);
} else {
$ftable = null;
}
} while (!$foreign_column && !!$fschema && !!$ftable);
if (!$foreign_column) {
// column wasn't found in any referenced tables
throw new Exception("Local column {$local_schema['name']}.{$local_table['name']}.{$local_colname} references unknown column {$foreign_schema['name']}.{$foreign_table['name']}.{$foreign_colname}");
}
// column type is missing, and resolved foreign is also a foreign key?
// recurse and find the cascading foreign key
if (empty($foreign_column['type']) && !empty($foreign_column['foreignTable'])) {
// make sure we don't visit the same column twice
$foreign_col = format::get_fully_qualified_column_name($foreign_schema['name'], $foreign_table['name'], $foreign_column['name']);
if (in_array($foreign_col, $visited)) {
$local = format::get_fully_qualified_column_name($local_schema['name'], $local_table['name'], $local_colname);
throw new Exception("Foreign key cyclic dependency detected! Local column {$local} pointing to foreign column {$foreign_col}");
}
$visited[] = $foreign_col;
$nested_fkey = self::foreign_key_lookup($db_doc, $foreign_schema, $foreign_table, $foreign_column, $visited);
// make a separate clone of the column element because we are specifying the type only for foreign key type referencing
$foreign_column = new SimpleXMLElement($foreign_column->asXML());
$foreign_column['type'] = (string) $nested_fkey['column']['type'];
}
return $foreign_column;
}
/**
* Parses ALTER TABLE command.
*
* @param database database
* @param command ALTER TABLE command
*
*/
public static function parse($database, $command)
{
$line = $command;
if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) {
$table_name = trim($matches[1]);
$table_owner = sql_parser::remove_last_semicolon(trim($matches[2]));
$schema_name = sql_parser::get_schema_name($table_name, $database);
$node_schema = dbx::get_schema($database, $schema_name);
if ($node_schema == null) {
throw new exception("schema " . $schema_name . " not found in database object");
}
// is it actually a sequence ownership reference?
if (substr($table_name, -4) == '_seq') {
//@TODO: figure out what sequences are not table linked and need to have ownership set on them
} else {
$node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name));
if ($node_table == null) {
throw new exception("table " . sql_parser::get_object_name($table_name) . " not found in " . $node_schema['name'] . " schema object");
}
dbx::set_attribute($node_table, 'owner', $table_owner);
}
} else {
if (preg_match(self::PATTERN_START, $line, $matches) > 0) {
$table_name = trim($matches[1]);
} else {
throw new exception("Cannot parse command: " . $line);
}
$schema_name = sql_parser::get_schema_name($table_name, $database);
$node_schema = dbx::get_schema($database, $schema_name);
$node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name));
$line = sql_parser::remove_last_semicolon($matches[2]);
self::parse_rows($database, $node_schema, $node_table, $line);
}
}
public function testSlonyId()
{
// make sure if require_slony_set_id is TRUE then there are changes
dbsteward::$require_slony_id = TRUE;
dbsteward::$slonyid_start_value = 9001;
$this->do_slony_numbering();
$out_schema = dbx::get_schema($this->slonyid_doc, 'someapp');
// test that users table column user_id with type serial got set to slonyId 9001
$out_table_users = dbx::get_table($out_schema, 'users');
$out_column_users_user_id = dbx::get_table_column($out_table_users, 'user_id');
$this->assertEquals('9001', $out_column_users_user_id['slonyId']);
// test that log table now has slonyId 9002
$out_table_log = dbx::get_table($out_schema, 'log');
$this->assertEquals('9002', $out_table_log['slonyId']);
// test that log table log_id serial now has slonyId 9003
$out_column_log_log_id = dbx::get_table_column($out_table_log, 'log_id');
$this->assertEquals('9003', $out_column_log_log_id['slonyId']);
}
/**
* If this table was renamed, get the old table definition node for this table
* as defined by oldTableName / oldSchemaName
*
* @param object $schema schema the table exists in
* @param object $table table node
* @return object old table node object
*/
public static function &get_old_table($schema, $table)
{
if (!isset($table['oldTableName'])) {
return NULL;
}
$old_schema = static::get_old_table_schema($schema, $table);
$old_table = dbx::get_table($old_schema, $table['oldTableName']);
return $old_table;
}
protected function compare_xml_definition()
{
$doc_a = simplexml_load_file($this->xml_file_a);
$doc_b = simplexml_load_file($this->xml_file_b);
// are all of the schemas defined in A in B?
foreach ($doc_a->schema as $schema_a) {
$schema_b = dbx::get_schema($doc_b, $schema_a['name']);
$this->assertTrue(is_object($schema_b), $schema_a['name'] . ' schema_b object pointer not found');
$this->assertEquals($schema_a['name'], $schema_b['name']);
// are all of the tables defined in A in B?
foreach ($schema_a->table as $table_a) {
$table_b = dbx::get_table($schema_b, $table_a['name']);
$this->assertTrue(is_object($table_b), $table_a['name'] . ' table_b object pointer not found');
$this->assertEquals($table_a['name'], $table_b['name']);
}
}
}
public static function slony_diff($old_files, $new_files)
{
if (!is_array($old_files)) {
$old_files = array($old_files);
}
$old_output_prefix = dirname($old_files[0]) . '/' . substr(basename($old_files[0]), 0, -4);
if (!is_array($new_files)) {
$new_files = array($new_files);
}
$new_output_prefix = dirname($new_files[0]) . '/' . substr(basename($new_files[0]), 0, -4);
$old_db_doc = xml_parser::xml_composite($old_output_prefix, $old_files, $old_slony_composite_file);
$new_db_doc = xml_parser::xml_composite($new_output_prefix, $new_files, $new_slony_composite_file);
foreach ($old_db_doc->schema as $old_schema) {
$new_schema = dbx::get_schema($new_db_doc, $old_schema['name']);
if (!$new_schema) {
dbsteward::warning("new definition missing schema " . $old_schema['name']);
continue 1;
}
foreach ($old_schema->table as $old_table) {
$new_table = dbx::get_table($new_schema, $old_table['name']);
if (!$new_table) {
dbsteward::warning("new definition missing table " . $old_schema['name'] . "." . $old_table['name']);
continue 1;
}
if (strcmp($old_table['slonyId'], $new_table['slonyId']) != 0) {
dbsteward::info("table " . $old_schema['name'] . "." . $old_table['name'] . "\told slonyId " . $old_table['slonyId'] . " new slonyId " . $new_table['slonyId']);
continue 1;
}
}
foreach ($old_schema->sequence as $old_sequence) {
$new_sequence = dbx::get_sequence($new_schema, $old_sequence['name']);
if (!$new_sequence) {
dbsteward::warning("new definition missing sequence " . $old_schema['name'] . "." . $old_sequence['name']);
continue 1;
}
if (strcmp($old_sequence['slonyId'], $new_sequence['slonyId']) != 0) {
dbsteward::info("sequence " . $old_schema['name'] . "." . $old_sequence['name'] . "\told slonyId " . $old_sequence['slonyId'] . " new slonyId " . $new_sequence['slonyId']);
continue 1;
}
}
}
}
public static function foreign_key($db_doc, $node_schema, $node_table, $column, &$foreign)
{
$fschema = strlen($column['foreignSchema']) == 0 ? (string) $node_schema['name'] : (string) $column['foreignSchema'];
$foreign['schema'] = dbx::get_schema($db_doc, $fschema);
if (!$foreign['schema']) {
throw new exception("Failed to find foreign schema '" . dbsteward::string_cast($column['foreignSchema']) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
$foreign['table'] = dbx::get_table($foreign['schema'], $column['foreignTable']);
if (!$foreign['table']) {
throw new exception("Failed to find foreign table '" . $column['foreignTable'] . "' for " . $node_schema['name'] . "." . $node_table['name'] . "." . $column['name']);
}
// if foreignColumn is not set
// the column is assumed to be the same name as the referring column
if (isset($column['foreignColumn']) && strlen($column['foreignColumn'])) {
$foreignColumn = $column['foreignColumn'];
} else {
$foreignColumn = $column['name'];
}
$foreign['column'] = dbx::get_table_column($foreign['table'], $foreignColumn);
if (!$foreign['column']) {
var_dump($foreign['column']);
throw new exception("Failed to find foreign column '" . dbsteward::string_cast($foreignColumn) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
// column type is missing, and resolved foreign is also a foreign key?
// recurse and find the cascading foreign key
if (strlen($foreign['column']['type']) == 0 && isset($foreign['column']['foreignColumn'])) {
dbsteward::trace("Seeking nested foreign key for " . dbsteward::string_cast($foreign['schema']['name']) . "." . dbsteward::string_cast($foreign['table']['name']) . "." . $foreign['column']['name']);
$nested_fkey = array();
self::foreign_key($db_doc, $foreign['schema'], $foreign['table'], $foreign['column'], $nested_fkey);
//var_dump($nested_fkey['column']);
// make a separate clone of the column element because we are specifying the type only for foreign key type referencing
$foreign['column'] = new SimpleXMLElement($foreign['column']->asXML());
$foreign['column']['type'] = $nested_fkey['column']['type'];
}
$foreign['name'] = pgsql8_index::index_name($node_table['name'], $column['name'], 'fkey');
$table_name = format::get_fully_qualified_table_name($foreign['schema']['name'], $foreign['table']['name']);
$column_name = format::get_quoted_column_name($foreign['column']['name']);
$foreign['references'] = "{$table_name}({$column_name})";
}
/**
* 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);
}
}
}
/**
* Outputs DDL for addition, removal and modifications of table columns
*
* @param $ofs1 stage1 output file segmenter
* @param $ofs3 stage3 output file segmenter
* @param $old_table original table
* @param $new_table new table
*/
public static function diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table_target = null, $new_table_target = null)
{
self::create_tables($ofs1, $old_schema, $new_schema, $old_table_target, $new_table_target);
// were specific tables passed?
if ($old_table_target !== null || $new_table_target !== null) {
$old_table = $old_table_target;
$new_table = $new_table_target;
if ($old_table && $new_table) {
mssql10_diff_tables::update_table_columns($ofs1, $ofs3, $old_table, $new_schema, $new_table);
mssql10_diff_tables::add_alter_statistics($ofs1, $old_table, $new_schema, $new_table);
}
} else {
foreach (dbx::get_tables($new_schema) as $new_table) {
if (!$old_schema) {
// old_schema not defined
continue;
}
$old_table = dbx::get_table($old_schema, $new_table['name']);
dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
if (!$old_table) {
// old_table not defined
continue;
}
mssql10_diff_tables::update_table_columns($ofs1, $ofs3, $old_table, $new_schema, $new_table);
mssql10_diff_tables::add_alter_statistics($ofs1, $old_table, $new_schema, $new_table);
}
}
}
/**
* Outputs commands for addition, removal and modifications of
* table columns.
*
* @param $ofs1 stage1 output file segmenter
* @param $ofs3 stage3 output file segmenter
* @param $old_table original table
* @param $new_table new table
*/
private static function update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table)
{
// README: This function differs substantially from its siblings in pgsql8 or mssql10
// The reason for this is two-fold.
//
// First, for every ALTER TABLE, mysql actually rebuilds the whole table. Thus, it is drastically
// more efficient if we can pack as much into a single ALTER TABLE as possible. Secondly, unlike
// other RDBMS's, MySQL requires that you completely redefine a column if you (a) rename it,
// (b) change its NULL/NOT NULL attribute, (c) change its type, or (d) add or remove its AUTO_INCREMENT
// attribute. This means that just about 75% of the changes that could happen between two versions
// of column require a column redefine rather than granular alteration. Therefore, it doesn't make
// much sense to have 3 redefines of a column in a single ALTER TABLE, which is what happens if you
// port over the pgsql8 or mssql10 versions of this function.
//
// For these reasons, the mysql5 implementation of this function is optimized for making as few
// alterations to each column as possible.
// arbitrary sql
$extra = array('BEFORE1' => array(), 'AFTER1' => array(), 'BEFORE3' => array(), 'AFTER3' => array());
// each entry is keyed by column name, and has a 'command' key, which may be one of
// nothing: do nothing
// drop: drop this column
// change: rename & redefine
// create: create this column
// modify: redefine without rename
// the 'defaults' key is whether to give the column a DEFAULT clause if it is NOT NULL
// the 'nulls' key is whether to include NULL / NOT NULL in the column definition
$commands = array('1' => array(), '3' => array());
$defaults = array('set' => array(), 'drop' => array());
foreach (dbx::get_table_columns($old_table) as $old_column) {
if (!mysql5_table::contains_column($new_table, $old_column['name'])) {
if (!dbsteward::$ignore_oldnames && ($renamed_column_name = mysql5_table::column_name_by_old_name($new_table, $old_column['name'])) !== false) {
continue;
} else {
// echo "NOTICE: add_drop_table_columns() " . $new_table['name'] . " does not contain " . $old_column['name'] . "\n";
$commands['3'][(string) $old_column['name']] = array('command' => 'drop', 'column' => $old_column);
}
}
}
$new_columns = dbx::get_table_columns($new_table);
foreach ($new_columns as $col_index => $new_column) {
$cmd1 = array('command' => 'nothing', 'column' => $new_column, 'defaults' => mysql5_diff::$add_defaults, 'nulls' => TRUE, 'auto_increment' => FALSE);
if (!mysql5_table::contains_column($old_table, $new_column['name'], TRUE)) {
// column not present in old table, is either renamed or new
if (!dbsteward::$ignore_oldnames && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) {
// renamed
$cmd1['command'] = 'change';
$cmd1['old'] = $new_column['oldColumnName'];
} else {
// new
$cmd1['command'] = 'create';
if ($col_index == 0) {
$cmd1['first'] = TRUE;
} else {
$cmd1['after'] = $new_columns[$col_index - 1]['name'];
}
// some columns need filled with values before any new constraints can be applied
// this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute
$db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
if ($db_doc_new_schema) {
$db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']);
if ($db_doc_new_table) {
$db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']);
if ($db_doc_new_column) {
if (isset($db_doc_new_column['beforeAddStage1'])) {
$extras['BEFORE1'][] = trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition";
}
if (isset($db_doc_new_column['afterAddStage1'])) {
$extras['AFTER1'][] = trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition";
}
if (isset($db_doc_new_column['beforeAddStage3'])) {
$extras['BEFORE3'][] = trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition";
}
if (isset($db_doc_new_column['afterAddStage3'])) {
$extras['AFTER3'][] = trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition";
}
} else {
throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found");
}
} else {
throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found");
}
} else {
throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found");
}
}
} else {
if ($old_column = dbx::get_table_column($old_table, $new_column['name'])) {
$old_column_type = mysql5_column::column_type(dbsteward::$old_database, $old_schema, $old_table, $old_column);
$new_column_type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column);
$old_default = isset($old_column['default']) ? (string) $old_column['default'] : '';
$new_default = isset($new_column['default']) ? (string) $new_column['default'] : '';
$auto_increment_added = !mysql5_column::is_auto_increment($old_column['type']) && mysql5_column::is_auto_increment($new_column['type']);
$auto_increment_removed = mysql5_column::is_auto_increment($old_column['type']) && !mysql5_column::is_auto_increment($new_column['type']);
$auto_increment_changed = $auto_increment_added || $auto_increment_removed;
$type_changed = strcasecmp($old_column_type, $new_column_type) !== 0 || $auto_increment_changed;
$default_changed = strcasecmp($old_default, $new_default) !== 0;
$nullable_changed = strcasecmp($old_column['null'] ?: 'true', $new_column['null'] ?: 'true') !== 0;
$cmd1['command'] = 'nothing';
if ($type_changed || $nullable_changed) {
$cmd1['command'] = 'modify';
if ($default_changed && !$new_default) {
$cmd1['defaults'] = FALSE;
}
if ($auto_increment_added) {
$cmd1['auto_increment'] = TRUE;
}
} elseif ($default_changed) {
if (strlen($new_default) > 0) {
if (mysql5_column::is_timestamp($new_column)) {
// timestamps get special treatment
$cmd1['command'] = 'modify';
} else {
$defaults['set'][] = $new_column;
}
} else {
$defaults['drop'][] = $new_column;
}
}
}
}
$commands['1'][(string) $new_column['name']] = $cmd1;
}
// end foreach column
$table_name = mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']);
$get_command_sql = function ($command) use(&$new_schema, &$new_table) {
if ($command['command'] == 'nothing') {
return NULL;
}
if ($command['command'] == 'drop') {
$name = mysql5::get_quoted_column_name($command['column']['name']);
return "DROP COLUMN {$name}";
}
$defn = mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $command['column'], $command['defaults'], $command['nulls'], $command['auto_increment']);
if ($command['command'] == 'change') {
$old = mysql5::get_quoted_column_name($command['old']);
return "CHANGE COLUMN {$old} {$defn}";
}
if ($command['command'] == 'create') {
if (array_key_exists('first', $command)) {
return "ADD COLUMN {$defn} FIRST";
} elseif (array_key_exists('after', $command)) {
$col = mysql5::get_quoted_column_name($command['after']);
return "ADD COLUMN {$defn} AFTER {$col}";
} else {
return "ADD COLUMN {$defn}";
}
}
if ($command['command'] == 'modify') {
return "MODIFY COLUMN {$defn}";
}
throw new Exception("Invalid column diff command '{$command['command']}'");
};
// end get_command_sql()
// pre-stage SQL
foreach ($extra['BEFORE1'] as $sql) {
$ofs1->write($sql . "\n\n");
}
foreach ($extra['BEFORE3'] as $sql) {
$ofs3->write($sql . "\n\n");
}
// output stage 1 sql
$stage1_commands = array();
foreach ($commands['1'] as $column_name => $command) {
$stage1_commands[] = $get_command_sql($command);
}
// we can also add SET DEFAULTs in here
foreach ($defaults['set'] as $column) {
$name = mysql5::get_quoted_column_name($column['name']);
if (strlen($column['default']) > 0) {
$default = (string) $column['default'];
} else {
$type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $column);
$default = mysql5_column::get_default_value($type);
}
$stage1_commands[] = "ALTER COLUMN {$name} SET DEFAULT {$default}";
}
foreach ($defaults['drop'] as $column) {
$name = mysql5::get_quoted_column_name($column['name']);
$stage1_commands[] = "ALTER COLUMN {$name} DROP DEFAULT";
}
$stage1_commands = array_filter($stage1_commands);
if (count($stage1_commands) > 0) {
$sql = "ALTER TABLE {$table_name}\n ";
$sql .= implode(",\n ", $stage1_commands);
$sql .= ";\n\n";
$ofs1->write($sql);
}
// output stage 3 sql
$stage3_commands = array();
foreach ($commands['3'] as $column_name => $command) {
$stage3_commands[] = $get_command_sql($command);
}
$stage3_commands = array_filter($stage3_commands);
if (count($stage3_commands) > 0) {
$sql = "ALTER TABLE {$table_name}\n ";
$sql .= implode(",\n ", $stage3_commands);
$sql .= ";\n\n";
$ofs3->write($sql);
}
// post-stage SQL
foreach ($extra['AFTER1'] as $sql) {
$ofs1->write($sql . "\n\n");
}
foreach ($extra['AFTER3'] as $sql) {
$ofs3->write($sql . "\n\n");
}
}
/**
* Updates objects in schemas.
*
* @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 (mysql5_diff::$new_table_dependency != NULL && count(mysql5_diff::$new_table_dependency) > 0) {
for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
// go in reverse when in delete mode
if ($delete_mode) {
$item = mysql5_diff::$new_table_dependency[count(mysql5_diff::$new_table_dependency) - 1 - $i];
} else {
$item = mysql5_diff::$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']);
}
$ofs->write(mysql5_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) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mysql5_diff_tables::diff_data($ofs, $old_schema, $new_schema);
}
}
}
/**
* Parses GRANT and REVOKE commands
*
* @param database database
* @param command REVOKE command
*
*/
public static function parse($database, $command)
{
$command = sql_parser::remove_last_semicolon($command);
if (preg_match(self::PATTERN_GRANT_REVOKE, $command, $matches) > 0) {
if (count($matches) != 5) {
throw new exception("GRANT/REVOKE definition preg exploded into " . count($matches) . ", panic!");
}
$action = strtoupper($matches[1]);
switch ($action) {
case 'GRANT':
case 'REVOKE':
break;
default:
throw new exception("permission action " . $action . " is unknown, panic!");
break;
}
$operations = preg_split("/[\\,\\s]+/", $matches[2], -1, PREG_SPLIT_NO_EMPTY);
if (!is_array($operations)) {
$permission = array($operations);
}
for ($i = 0; $i < count($operations); $i++) {
$operations[$i] = strtoupper($operations[$i]);
switch ($operations[$i]) {
case 'ALL':
case 'SELECT':
case 'INSERT':
case 'UPDATE':
case 'DELETE':
case 'USAGE':
case 'REFERENCES':
case 'TRIGGER':
break;
default:
var_dump($operations);
throw new exception("the operation " . $operations[$i] . " is unknown, panic!");
break;
}
}
$object = $matches[3];
$chunks = preg_split("/[\\s]+/", $object, -1, PREG_SPLIT_NO_EMPTY);
if (count($chunks) == 1) {
// if there is no white space separating this bit
// then let postgresql decide what it is when the grant is run
$object_type = '';
$object_name = $chunks[0];
} else {
if (count($chunks) == 2) {
// SEQUENCE schema.table_table_id_seq
// TABLE schema.table
$object_type = $chunks[0];
$object_name = $chunks[1];
// if it's a schema, don't try to explode / default the schema prefix
if (strcasecmp($object_type, 'SCHEMA') == 0) {
$schema =& dbx::get_schema($database, $object_name);
} else {
$object_name = sql_parser::get_schema_name($object_name, $database) . '.' . sql_parser::get_object_name($object_name);
$schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database));
}
if ($schema == null) {
throw new exception("Failed to find schema for grant/revoke: " . sql_parser::get_schema_name($object_name, $database));
}
} else {
throw new exception("object definition exploded into " . count($chunks) . " chunks, panic!");
}
}
$role = $matches[4];
// find the node_object, swtich'd on $object_type
// based on http://www.postgresql.org/docs/8.4/static/sql-grant.html
// empty object_type should be considered a TABLE GRANT/REVOKE
if (strlen($object_type) == 0) {
$object_type = 'TABLE';
}
/*
var_dump($command);
var_dump(sql_parser::get_schema_name($object_name, $database));
var_dump(sql_parser::get_object_name($object_name));
/**/
switch (strtoupper($object_type)) {
case 'SCHEMA':
$node_object =& dbx::get_schema($database, $object_name);
break;
case 'SEQUENCE':
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database));
$node_object =& dbx::get_sequence($node_schema, sql_parser::get_object_name($object_name));
break;
case 'TABLE':
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database));
$node_object =& dbx::get_table($node_schema, sql_parser::get_object_name($object_name));
break;
default:
throw new exception("unknown object_type " . $object_type . " encountered, panic!");
break;
}
dbx::set_permission($node_object, $action, $operations, $role);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
