/**
* This works differently than sql99_diff_indexes::diff_indexes_table because of the way
* mysql5 handles indexes on foreign keys. If we DROP then CREATE an index being used in an FK
* in separate statements, like we do for other formats, MySQL errors because an FK constraint
* relies on that index. If you DROP and CREATE in the same statement, though, MySQL is happy.
* So, just mash all the index changes for each table into a single ALTER TABLE and be done with it.
*/
public static function diff_indexes_table($ofs, $old_schema, $old_table, $new_schema, $new_table)
{
$bits = self::diff_indexes_table_bits($old_schema, $old_table, $new_schema, $new_table);
if (!empty($bits)) {
$ofs->write('ALTER TABLE ' . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']) . "\n " . implode(",\n ", $bits) . ";\n\n");
}
}
public static function get_drop_sql($node_schema, $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";
}
$events = self::get_events($node_trigger);
if (count($events) == 1) {
return "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name']) . ";\n";
} else {
$ddl = "";
foreach ($events as $event) {
if ($event = self::validate_event($event)) {
$ddl .= "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . "_{$event}") . ";\n";
}
}
return $ddl;
}
}
public static function get_permission_sql($db_doc, $node_schema, $node_object, $node_permission, $action = 'grant')
{
if (strcasecmp($node_permission->getName(), 'grant') != 0 && strcasecmp($node_permission->getName(), 'revoke') != 0) {
throw new exception("Cannot extract permission rights from node that is not grant or revoke");
}
if (!isset($node_permission['operation']) || strlen($node_permission['operation']) == 0) {
throw new exception("node_permission operation definition is empty");
}
$object_name = '';
$object_type = strtoupper($node_object->getName());
$privileges = array_map(function ($p) use($object_type) {
return mysql5_permission::get_real_privilege($p, $object_type);
}, static::get_permission_privileges($node_permission));
$roles = static::get_permission_roles($db_doc, $node_permission);
$with = static::get_permission_options_sql($node_permission);
switch ($object_type) {
case 'SCHEMA':
// all tables on current database, because no schemas
$object_name = '*';
break;
case 'VIEW':
return "-- Ignoring permissions on view '{$node_object['name']}' because MySQL uses SQL SECURITY DEFINER semantics\n";
case 'TABLE':
$object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_object['name']);
break;
case 'FUNCTION':
$object_name = "FUNCTION " . mysql5::get_fully_qualified_object_name($node_schema['name'], $node_object['name'], 'function');
break;
case 'SEQUENCE':
// sequences exist as rows in a table for mysql
$object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], mysql5_sequence::TABLE_NAME);
break;
default:
throw new exception("unknown object type encountered: " . $object_type);
}
$sql = static::get_sql(strtoupper($action), $object_name, $privileges, array_map('mysql5::get_quoted_object_name', $roles), $with) . "\n";
return $sql;
}
public static function get_drop_sql($node_schema, $node_view)
{
return "DROP VIEW IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']) . ";";
}
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");
}
}
}
}
}
}
public static function get_constraint_drop_sql($constraint, $with_alter_table = TRUE)
{
if (!is_array($constraint)) {
throw new exception("constraint is not an array?");
}
if (strlen($constraint['table_name']) == 0) {
var_dump(array_keys($constraint));
throw new exception("table_name is blank");
}
// because MySQL refuses to have consistent syntax
switch (strtoupper($constraint['type'])) {
case 'CHECK':
// @TODO: Implement compatibility
dbsteward::warning("Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint");
return "-- Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint";
break;
case 'UNIQUE':
$drop = "INDEX " . mysql5::get_quoted_object_name($constraint['name']);
break;
case 'PRIMARY KEY':
$drop = "PRIMARY KEY";
break;
case 'FOREIGN KEY':
$drop = "FOREIGN KEY " . mysql5::get_quoted_object_name($constraint['name']);
break;
case 'KEY':
$drop = "KEY " . mysql5::get_quoted_object_name($constraint['name']);
break;
default:
// we shouldn't actually ever get here.
throw new Exception("Unimplemented MySQL constraint {$constraint['type']}");
}
$sql = '';
if ($with_alter_table) {
$sql .= "ALTER TABLE " . mysql5::get_fully_qualified_table_name($constraint['schema_name'], $constraint['table_name']) . " ";
}
$sql .= "DROP {$drop}";
if ($with_alter_table) {
$sql .= ";";
}
return $sql;
}
private static function get_recreate_table_sql($schema, $table)
{
$fq_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']);
$fq_tmp_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name'] . '_DBSTEWARD_MIGRATION');
// utilize MySQL's CREATE TABLE ... SELECT syntax for cleaner recreation
// see: http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html
$sql = "CREATE TABLE {$fq_tmp_name}";
$opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($schema, $table));
if (!empty($opt_sql)) {
$sql .= "\n" . $opt_sql;
}
if (strlen($table['description']) > 0) {
$sql .= "\nCOMMENT " . mysql5::quote_string_value($table['description']);
}
$sql .= "\nSELECT * FROM {$fq_name};\n";
$sql .= "DROP TABLE {$fq_name};\n";
$sql .= "RENAME TABLE {$fq_tmp_name} TO {$fq_name};";
return $sql;
}
protected static function get_dimension_list($node_schema, $node_table, $node_index)
{
$dimensions = array();
foreach ($node_index->indexDimension as $dimension) {
// mysql only supports indexed columns, not indexed expressions like in pgsql or mssql
if (!mysql5_table::contains_column($node_table, $dimension)) {
throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'");
}
$dimensions[] = mysql5::get_quoted_column_name($dimension);
}
return $dimensions;
}
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");
}
/**
* Retrieves an associative array of table options defined for a table
* @param SimpleXMLElement $node_schema The schema
* @param SimpleXMLElement $node_table The table
* @return array Option name => option value
*/
public static function get_table_options($node_schema, $node_table)
{
$opts = parent::get_table_options($node_schema, $node_table);
if (!mysql5::$use_auto_increment_table_options && array_key_exists('auto_increment', $opts)) {
dbsteward::warning('WARNING: Ignoring auto_increment tableOption on table ' . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']));
dbsteward::warning(' Setting the auto_increment value is unreliable. If you want to use it, pass the --useautoincrementoptions commandline flag');
unset($opts['auto_increment']);
}
return $opts;
}
public static function get_multiple_create_sql($node_schema, $node_table, $constraints)
{
if (count($constraints) == 0) {
return '';
}
$bits = self::get_multiple_create_bits($node_schema, $node_table, $constraints);
$table = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
return "ALTER TABLE {$table}\n " . implode(",\n ", $bits) . ";\n";
}
