/** * @todo should *all* sequences be dropped in stage3, rather than just the shim? * the only reason we don't is because pgsql8 drops in stage 1... */ public static function diff_sequences($ofs1, $ofs3, $old_schema, $new_schema) { $new_sequences = static::get_sequences($new_schema); if ($old_schema != null) { $old_sequences = static::get_sequences($old_schema); } else { $old_sequences = array(); } if (empty($new_sequences)) { // there are no sequences in the new schema, so if there used to be sequences, // we can just drop the whole shim table if (!empty($old_sequences)) { $ofs3->write(mysql5_sequence::get_shim_drop_sql()); } } else { // there *are* sequences in the new schema, so if there didn't used to be, // we need to add the shim in before adding any sequences if (empty($old_sequences)) { $ofs1->write(mysql5_sequence::get_shim_creation_sql() . "\n\n"); $ofs1->write(mysql5_sequence::get_creation_sql($new_schema, $new_sequences) . "\n"); } else { // there were schemas in the old schema $common_sequences = array(); // only drop sequences not in the new schema $to_drop = array(); foreach ($old_sequences as $old_seq) { if (static::schema_contains_sequence($new_schema, $old_seq['name'], true)) { // if the sequence *is* in the new schema, then it might have changed $common_sequences[(string) $old_seq['name']] = $old_seq; } else { $to_drop[] = $old_seq; } } if (!empty($to_drop)) { $ofs1->write(mysql5_sequence::get_drop_sql($old_schema, $to_drop) . "\n\n"); } // only add sequences not in the old schema $to_insert = array(); foreach ($new_sequences as $new_seq) { if (static::schema_contains_sequence($old_schema, $new_seq['name'])) { // there used to be a sequence named $new_seq['name'] self::diff_single($ofs1, $common_sequences[(string) $new_seq['name']], $new_seq); } elseif (!dbsteward::$ignore_oldnames && !empty($new_seq['oldSequenceName']) && static::schema_contains_sequence($old_schema, $new_seq['oldSequenceName'])) { // there used to be a sequence named $new_seq['oldSequenceName'] self::diff_single($ofs1, $common_sequences[(string) $new_seq['oldSequenceName']], $new_seq); } else { $to_insert[] = $new_seq; } } if (!empty($to_insert)) { $ofs1->write(mysql5_sequence::get_creation_sql($new_schema, $to_insert) . "\n"); } } } }
protected static function drop_old_schemas($ofs) { $drop_sequences = array(); if (is_array(mysql5_diff::$old_table_dependency)) { $deps = mysql5_diff::$old_table_dependency; $processed_schemas = array(); foreach ($deps as $dep) { $old_schema = $dep['schema']; if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) { // this schema is being dropped, drop all children objects in it if (!in_array(trim($old_schema['name']), $processed_schemas)) { // this schema hasn't been processed yet, go ahead and drop views, types, functions, sequences // only do it once per schema foreach ($old_schema->type as $node_type) { $ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n"); } foreach ($old_schema->function as $node_function) { $ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n"); } foreach ($old_schema->sequence as $node_sequence) { $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n"); } $processed_schemas[] = trim($old_schema['name']); } if ($dep['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } // constraints, indexes, triggers will be deleted along with the tables they're attached to // tables will drop themselves later on // $ofs->write(mysql5_table::get_drop_sql($old_schema, $dep['table']) . "\n"); $table_name = mysql5::get_fully_qualified_table_name($dep['schema']['name'], $dep['table']['name']); $ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n"); $ofs->write("-- {$table_name} will be dropped later according to table dependency order\n"); // table sequences need dropped separately foreach (mysql5_table::get_sequences_needed($old_schema, $dep['table']) as $node_sequence) { $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n"); } } } } else { foreach (dbsteward::$old_database->schema as $old_schema) { if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) { foreach ($old_schema->type as $node_type) { $ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n"); } foreach ($old_schema->function as $node_function) { $ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n"); } foreach ($old_schema->sequence as $node_sequence) { $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n"); } foreach ($old_schema->table as $node_table) { // tables will drop themselves later on // $ofs->write(mysql5_table::get_drop_sql($old_schema, $node_table) . "\n"); $table_name = mysql5::get_fully_qualified_table_name($old_schema['name'], $node_table['name']); $ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n"); $ofs->write("-- {$table_name} will be dropped later according to table dependency order\n"); foreach (mysql5_table::get_sequences_needed($old_schema, $node_table) as $node_sequence) { $ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n"); } } } } } }
private function setup_shim() { $this->pdo->exec($sql = mysql5_sequence::get_shim_drop_sql()); // echo $sql . "\n\n"; $this->pdo->exec($sql = mysql5_sequence::get_shim_creation_sql()); // echo $sql . "\n\n"; }
public function testSerials() { $old = <<<XML <schema name="test0" owner="NOBODY"> </schema> XML; $new = <<<XML <schema name="test0" owner="NOBODY"> <table name="table" owner="NOBODY"> <column name="id" type="serial"/> </table> </schema> XML; // shouldn't create any serial sequences $this->common($new, $new, ''); // should create a serial sequence $expected = mysql5_sequence::get_shim_creation_sql(); $expected .= <<<SQL INSERT INTO `__sequences` (`name`, `increment`, `min_value`, `max_value`, `cur_value`, `start_value`, `cycle`) VALUES ('__test0_table_id_serial_seq', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT); SQL; $this->common($old, $new, $expected); // should drop a serial sequence $expected = <<<SQL DROP TABLE IF EXISTS `__sequences`; DROP FUNCTION IF EXISTS `nextval`; DROP FUNCTION IF EXISTS `setval`; DROP FUNCTION IF EXISTS `currval`; DROP FUNCTION IF EXISTS `lastval`; SQL; $this->common($new, $old, $expected); $renamed = <<<XML <schema name="test0" owner="NOBODY"> <table name="table" owner="NOBODY"> <column name="newid" type="serial" oldColumnName="id"/> </table> </schema> XML; // should UPDATE for name $this->common($new, $renamed, "UPDATE `__sequences`\nSET `name` = '__test0_table_newid_serial_seq'\nWHERE `name` = '__test0_table_id_serial_seq';"); }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $limit_to_tables_count = count(dbsteward::$limit_to_tables); foreach ($tables as $dep_table) { $schema = $dep_table['schema']; $table = $dep_table['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(mysql5_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $table_primary_keys = mysql5_table::primary_key_columns($table); $table_column_names = dbx::to_array($table->column, 'name'); $node_rows =& dbx::get_table_rows($table); // the <rows> element $data_column_names = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column if (count($table_primary_keys) == 1 && in_array($table_primary_keys[0], $data_column_names)) { $pk_column_name = $table_primary_keys[0]; $node_pk_column = dbx::get_table_column($table, $pk_column_name); if ($node_pk_column == NULL) { throw new exception("Failed to find primary key column '" . $pk_column_name . "' for " . $schema['name'] . "." . $table['name']); } // only set the pkey to MAX() if the primary key column is also a serial/bigserial and if serialStart is not defined if (mysql5_column::is_serial($node_pk_column['type']) && !isset($node_pk_column['serialStart'])) { $fqtn = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']); $qcol = mysql5::get_quoted_column_name($pk_column_name); $setval = mysql5_sequence::get_setval_call(mysql5_column::get_serial_sequence_name($schema, $table, $node_pk_column), "MAX({$qcol})", "TRUE"); $sql = "SELECT {$setval} FROM {$fqtn};\n"; $ofs->write($sql); } } // unlike the pg class, we cannot just set identity column start values here with setval without inserting a row // check if primary key is a column of this table - FS#17481 if (count(array_diff($table_primary_keys, $table_column_names)) != 0) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
public static function get_serial_start_setval_sql($schema, $table, $column) { $sequence_name = static::get_serial_sequence_name($schema, $table, $column); $setval = mysql5_sequence::get_setval_call($sequence_name, $column['serialStart'], 'TRUE'); return "SELECT {$setval};"; }
public function testDelimiters() { mysql5::$swap_function_delimiters = TRUE; $actual = mysql5_sequence::get_shim_creation_sql(); $actual = trim(preg_replace('/--.*(\\n\\s*)?/', '', $actual)); $expected = <<<SQL CREATE TABLE IF NOT EXISTS `__sequences` ( `name` VARCHAR(100) NOT NULL, `increment` INT(11) unsigned NOT NULL DEFAULT 1, `min_value` INT(11) unsigned NOT NULL DEFAULT 1, `max_value` BIGINT(20) unsigned NOT NULL DEFAULT 18446744073709551615, `cur_value` BIGINT(20) unsigned DEFAULT 1, `start_value` BIGINT(20) unsigned DEFAULT 1, `cycle` BOOLEAN NOT NULL DEFAULT FALSE, `should_advance` BOOLEAN NOT NULL DEFAULT TRUE, PRIMARY KEY (`name`) ) ENGINE = MyISAM; DELIMITER \$_\$ DROP FUNCTION IF EXISTS `currval`\$_\$ CREATE FUNCTION `currval` (`seq_name` varchar(100)) RETURNS BIGINT(20) NOT DETERMINISTIC BEGIN DECLARE val BIGINT(20); IF @__sequences_lastval IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session'; ELSE SELECT `currval` INTO val FROM `__sequences_currvals` WHERE `name` = seq_name; RETURN val; END IF; END\$_\$ DROP FUNCTION IF EXISTS `lastval`\$_\$ CREATE FUNCTION `lastval` () RETURNS BIGINT(20) NOT DETERMINISTIC BEGIN IF @__sequences_lastval IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session'; ELSE RETURN @__sequences_lastval; END IF; END\$_\$ DROP FUNCTION IF EXISTS `nextval`\$_\$ CREATE FUNCTION `nextval` (`seq_name` varchar(100)) RETURNS BIGINT(20) NOT DETERMINISTIC BEGIN DECLARE advance BOOLEAN; CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` ( `name` VARCHAR(100) NOT NULL, `currval` BIGINT(20), PRIMARY KEY (`name`) ); SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name; SELECT `should_advance` INTO advance FROM `__sequences` WHERE `name` = seq_name; IF @__sequences_lastval IS NOT NULL THEN IF advance = TRUE THEN UPDATE `__sequences` SET `cur_value` = IF ( (`cur_value` + `increment`) > `max_value`, IF (`cycle` = TRUE, `min_value`, NULL), `cur_value` + `increment` ) WHERE `name` = seq_name; SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name; ELSE UPDATE `__sequences` SET `should_advance` = TRUE WHERE `name` = seq_name; END IF; REPLACE INTO `__sequences_currvals` (`name`, `currval`) VALUE (seq_name, @__sequences_lastval); END IF; RETURN @__sequences_lastval; END\$_\$ DROP FUNCTION IF EXISTS `setval`\$_\$ CREATE FUNCTION `setval` (`seq_name` varchar(100), `value` bigint(20), `advance` BOOLEAN) RETURNS bigint(20) NOT DETERMINISTIC BEGIN UPDATE `__sequences` SET `cur_value` = value, `should_advance` = advance WHERE `name` = seq_name; IF advance = FALSE THEN CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` ( `name` VARCHAR(100) NOT NULL, `currval` BIGINT(20), PRIMARY KEY (`name`) ); REPLACE INTO `__sequences_currvals` (`name`, `currval`) VALUE (seq_name, value); SET @__sequences_lastval = value; END IF; RETURN value; END\$_\$ DELIMITER ; SQL; $this->assertEquals($expected, $actual); }