/**
  * 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;
 }
Example #4
0
 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']) . ";";
 }
Example #5
0
 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;
 }
Example #8
0
 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;
 }
Example #9
0
 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");
 }
Example #10
0
 /**
  * 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";
 }