public static function get_multiple_create_bits($node_schema, $node_table, $constraints) { $bits = array(); foreach ($constraints as $constraint) { $bits[] = mysql5_constraint::get_constraint_sql($constraint, FALSE); if (strcasecmp($constraint['type'], 'PRIMARY KEY') == 0) { // we're adding the PK constraint, so we need to add AUTO_INCREMENT on any affected columns immediately after! $columns = mysql5_table::primary_key_columns($node_table); foreach ($columns as $col) { $node_column = dbx::get_table_column($node_table, $col); if (mysql5_column::is_auto_increment($node_column['type'])) { $bits[] = "MODIFY " . mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $node_column, FALSE, TRUE, TRUE); break; // there can only be one AI column per table } } } } return $bits; }
/** * 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"); } }
/** Convert from arbitrary type notations to mysql5 specific type representations */ protected static function mysql5_type_convert($type, $value = null) { if ($is_ai = mysql5_column::is_auto_increment($type)) { $type = mysql5_column::un_auto_increment($type); } // when used in an index, varchars can only have a max of 3500 bytes // so when converting types, we don't know if it might be in an index, // so we play it safe if (substr($type, -2) == '[]') { $type = 'varchar(3500)'; } switch (strtolower($type)) { case 'bool': case 'boolean': // $type = 'tinyint'; if ($value) { switch (strtolower($value)) { case "'t'": case 'true': case '1': $value = '1'; break; case "'f'": case 'false': case '0': $value = '0'; break; default: throw new Exception("Unknown column type boolean default {$value}"); break; } } break; // boolean // boolean case 'inet': $type = 'varchar(16)'; break; case 'int': case 'integer': $type = 'int(11)'; break; case 'interval': $type = 'varchar(3500)'; break; case 'character varying': case 'varchar': $type = 'varchar(3500)'; break; // mysql's timezone support is attrocious. // see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html // mysql's timezone support is attrocious. // see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html case 'timestamp without timezone': case 'timestamp with timezone': case 'timestamp without time zone': case 'timestamp with time zone': $type = 'timestamp'; break; case 'time with timezone': case 'time with time zone': $type = 'time'; break; case 'serial': case 'bigserial': // emulated with triggers and sequences later on in the process // mysql5 interprets the 'serial' type as "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE" // which is dumb compared to the emulation with triggers/sequences to act more like pgsql's break; case 'uuid': // 8 digits, 3 x 4 digits, 12 digits = 32 digits + 4 hyphens = 36 chars $type = 'varchar(40)'; break; } // character varying(N) => varchar(N) // $type = preg_replace('/character varying\((.+)\)/i','varchar($1)',$type); // mysql doesn't understand epoch if (isset($value) && strcasecmp($value, "'epoch'") == 0) { // 00:00:00 is reserved for the "zero" value of a timestamp field. 01 is the closest we can get. $value = "'1970-01-01 00:00:01'"; } if ($is_ai) { $type = (string) $type . " AUTO_INCREMENT"; } return array($type, $value); }
public function testAutoIncrement() { $xml = <<<XML <dbsteward> <schema name="public" owner="NOBODY"> <table name="test" primaryKey="id" owner="NOBODY"> <column name="s1" type="int auto_increment"/> </table> </schema> </dbsteward> XML; $dbs = new SimpleXMLElement($xml); $col = $dbs->schema->table->column; $this->assertTrue(mysql5_column::is_auto_increment($col['type'])); $this->assertEquals("int", mysql5_column::un_auto_increment($col['type'])); $this->assertEquals("int", mysql5_column::column_type($dbs, $dbs->schema, $dbs->schema->table, $col)); $this->assertEquals("`s1` int AUTO_INCREMENT", mysql5_column::get_full_definition($dbs, $dbs->schema, $dbs->schema->table, $col, true, true, true)); $this->assertEquals("`s1` int", mysql5_column::get_full_definition($dbs, $dbs->schema, $dbs->schema->table, $col, true, true, false)); }