/**
* @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);
}
