/** * Outputs DDL for differences in functions * * @param $ofs1 stage1 output pointer * @param $ofs3 stage3 output pointer * @param $old_schema original schema * @param $new_schema new schema */ public static function diff_functions($ofs1, $ofs3, $old_schema, $new_schema) { // drop functions that no longer exist in stage 3 if ($old_schema != null) { foreach (dbx::get_functions($old_schema) as $old_function) { if (!mysql5_schema::contains_function($new_schema, mysql5_function::get_declaration($new_schema, $old_function))) { $ofs3->write(mysql5_function::get_drop_sql($old_schema, $old_function) . "\n"); } } } // Add new functions and replace modified functions foreach (dbx::get_functions($new_schema) as $new_function) { $old_function = null; if ($old_schema != null) { $old_function = dbx::get_function($old_schema, $new_function['name'], mysql5_function::get_declaration($new_schema, $new_function)); } if ($old_function == null || !mysql5_function::equals($new_schema, $new_function, $old_function, mysql5_diff::$ignore_function_whitespace)) { $ofs1->write(mysql5_function::get_creation_sql($new_schema, $new_function) . "\n"); } else { if (isset($new_function['forceRedefine']) && strcasecmp($new_function['forceRedefine'], 'true') == 0) { $ofs1->write("-- DBSteward insists on function recreation: {$new_schema['name']}.{$new_function['name']} has forceRedefine set to true\n"); $ofs1->write(mysql5_function::get_creation_sql($new_schema, $new_function) . "\n"); } else { if (mysql5_schema::contains_type($new_schema, $new_function['returns']) && mysql5_schema::contains_type($old_schema, $new_function['returns']) && !mysql5_type::equals(dbx::get_type($old_schema, $new_function['returns']), dbx::get_type($new_schema, $new_function['returns']))) { $ofs1->write("-- Force function re-creation {$new_function['name']} for type: {$new_function['returns']}\n"); $ofs1->write(mysql5_function::get_creation_sql($new_schema, $new_function) . "\n"); } } } } }
public function testValid() { $xml = <<<XML <schema name="test" owner="NOBODY"> <type type="enum" name="enum_a"> <enum name="alpha"/> <enum name="bravo"/> <enum name="charlie"/> </type> </schema> XML; $schema = new SimpleXMLElement($xml); $expected_sql = "-- found enum type enum_a. references to type enum_a will be replaced by ENUM('alpha','bravo','charlie')"; $actual_sql = mysql5_type::get_creation_sql($schema, $schema->type); $this->assertEquals($expected_sql, $actual_sql); $expected_sql = "-- dropping enum type enum_a. references to type enum_a will be replaced with the type 'text'"; $actual_sql = mysql5_type::get_drop_sql($schema, $schema->type); $this->assertEquals($expected_sql, $actual_sql); }
/** * Drop removed types * Add new types * Apply type definition differences, updating the type's tables along the way * * @param $ofs output segementer * @param $old_schema original schema * @param $new_schema new schema */ public static function apply_changes($ofs, $old_schema, $new_schema) { // drop any types that are no longer defined self::drop_types($ofs, $old_schema, $new_schema); // create any types that are new in the new definition self::create_types($ofs, $old_schema, $new_schema); // there is no alter for types // find types that still exist that are different // placehold type data in table columns, and recreate the type foreach (dbx::get_types($new_schema) as $new_type) { // does type exist in old definition ? if ($old_schema == NULL || !mysql5_schema::contains_type($old_schema, $new_type['name'])) { continue; } $old_type = dbx::get_type($old_schema, $new_type['name']); // is there a difference between the old and new type definitions? if (mysql5_type::equals($old_schema, $old_type, $new_schema, $new_type)) { continue; } } }
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 build_schema($db_doc, $ofs, $table_depends) { // schema creation if (static::$use_schema_name_prefix) { dbsteward::info("MySQL schema name prefixing mode turned on"); } else { if (count($db_doc->schema) > 1) { throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on"); } } foreach ($db_doc->schema as $schema) { // database grants foreach ($schema->grant as $grant) { $ofs->write(mysql5_permission::get_permission_sql($db_doc, $schema, $schema, $grant) . "\n"); } // enums foreach ($schema->type as $type) { $ofs->write(mysql5_type::get_creation_sql($schema, $type) . "\n"); } // function definitions foreach ($schema->function as $function) { if (mysql5_function::has_definition($function)) { $ofs->write(mysql5_function::get_creation_sql($schema, $function) . "\n\n"); } // function grants foreach ($function->grant as $grant) { $ofs->write(mysql5_permission::get_permission_sql($db_doc, $schema, $function, $grant) . "\n"); } } $sequences = array(); $triggers = array(); // create defined tables foreach ($schema->table as $table) { // get sequences and triggers needed to make this table work $sequences = array_merge($sequences, mysql5_table::get_sequences_needed($schema, $table)); $triggers = array_merge($triggers, mysql5_table::get_triggers_needed($schema, $table)); // table definition $ofs->write(mysql5_table::get_creation_sql($schema, $table) . "\n\n"); // table indexes // mysql5_diff_indexes::diff_indexes_table($ofs, NULL, NULL, $schema, $table); // table grants if (isset($table->grant)) { foreach ($table->grant as $grant) { $ofs->write(mysql5_permission::get_permission_sql($db_doc, $schema, $table, $grant) . "\n"); } } $ofs->write("\n"); } // sequences contained in the schema + sequences used by serials $sequences = array_merge($sequences, dbx::to_array($schema->sequence)); if (count($sequences) > 0) { $ofs->write(mysql5_sequence::get_shim_creation_sql() . "\n\n"); $ofs->write(mysql5_sequence::get_creation_sql($schema, $sequences) . "\n\n"); // sequence grants foreach ($sequences as $sequence) { foreach ($sequence->grant as $grant) { $ofs->write("-- grant for the {$sequence['name']} sequence applies to ALL sequences\n"); $ofs->write(mysql5_permission::get_permission_sql($db_doc, $schema, $sequence, $grant) . "\n"); } } } // trigger definitions + triggers used by serials $triggers = array_merge($triggers, dbx::to_array($schema->trigger)); $unique_triggers = array(); foreach ($triggers as $trigger) { // only do triggers set to the current sql format if (strcasecmp($trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) { // check that this table/timing/event combo hasn't been defined, because MySQL only // allows one trigger per table per BEFORE/AFTER per action $unique_name = "{$trigger['table']}-{$trigger['when']}-{$trigger['event']}"; if (array_key_exists($unique_name, $unique_triggers)) { throw new Exception("MySQL will not allow trigger {$trigger['name']} to be created because it happens on the same table/timing/event as trigger {$unique_triggers[$unique_name]}"); } $unique_triggers[$unique_name] = $trigger['name']; $ofs->write(mysql5_trigger::get_creation_sql($schema, $trigger) . "\n"); } } } foreach ($db_doc->schema as $schema) { // define table primary keys before foreign keys so unique requirements are always met for FOREIGN KEY constraints foreach ($schema->table as $table) { mysql5_diff_constraints::diff_constraints_table($ofs, NULL, NULL, $schema, $table, 'primaryKey', FALSE); } $ofs->write("\n"); } // foreign key references // use the dependency order to specify foreign keys in an order that will satisfy nested foreign keys and etc for ($i = 0; $i < count($table_depends); $i++) { $dep_schema = $table_depends[$i]['schema']; $table = $table_depends[$i]['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } mysql5_diff_constraints::diff_constraints_table($ofs, NULL, NULL, $dep_schema, $table, 'constraint', FALSE); } $ofs->write("\n"); mysql5_diff_views::create_views_ordered($ofs, null, $db_doc); // view permission grants foreach ($db_doc->schema as $schema) { foreach ($schema->view as $view) { if (isset($view->grant)) { foreach ($view->grant as $grant) { $ofs->write(mysql5_permission::get_permission_sql($db_doc, $schema, $view, $grant) . "\n"); } } } } // @TODO: database configurationParameter support }
public static function column_type($db_doc, $node_schema, $node_table, $node_column, &$foreign = NULL) { // if the column is a foreign key, solve for the foreignKey type if (isset($node_column['foreignTable'])) { $foreign = format_constraint::foreign_key_lookup($db_doc, $node_schema, $node_table, $node_column); $foreign_type = static::un_auto_increment($foreign['column']['type']); if (static::is_serial($foreign_type)) { return static::convert_serial($foreign_type); } return $foreign_type; } // if there's no type specified, that's a problem if (!isset($node_column['type'])) { throw new Exception("column missing type -- " . $table['name'] . "." . $column['name']); } // get the type of the column, ignoring any possible auto-increment flag $type = static::un_auto_increment($node_column['type']); // if the column type matches an enum type, inject the enum declaration here if ($node_type = mysql5_type::get_type_node($db_doc, $node_schema, $type)) { return mysql5_type::get_enum_type_declaration($node_type); } // translate serials to their corresponding int types if (static::is_serial($type)) { return static::convert_serial($type); } // nothing special about this type return $type; }
public static function get_creation_sql($node_schema, $node_function) { $name = static::get_declaration($node_schema, $node_function); $definer = strlen($node_function['owner']) > 0 ? xml_parser::role_enum(dbsteward::$new_database, $node_function['owner']) : 'CURRENT_USER'; // always drop the function first, just to be safe, and to be compatible with pgsql8's CREATE OR REPLACE $sql = static::get_drop_sql($node_schema, $node_function) . "\n"; if (mysql5::$swap_function_delimiters) { $sql .= 'DELIMITER ' . static::ALT_DELIMITER . "\n"; } $function_type = static::is_procedure($node_function) ? 'PROCEDURE' : 'FUNCTION'; $sql .= "CREATE DEFINER = {$definer} {$function_type} {$name} ("; if (isset($node_function->functionParameter)) { $params = array(); foreach ($node_function->functionParameter as $param) { if (isset($param['direction']) && !static::is_procedure($node_function)) { throw new exception("Parameter directions are not supported in MySQL functions"); } if (empty($param['name'])) { throw new exception("Function parameters must have names in MySQL. In function '{$node_function['name']}'"); } $type = $param['type']; if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $type)) { $type = mysql5_type::get_enum_type_declaration($node_type); } $sparam = ''; if (isset($param['direction'])) { $sparam .= (string) $param['direction'] . ' '; } $sparam .= mysql5::get_quoted_function_parameter($param['name']) . ' ' . $type; $params[] = $sparam; } $sql .= implode(', ', $params); } $sql .= ")\n"; // Procedures don't have a return statement if (!static::is_procedure($node_function)) { $returns = $node_function['returns']; if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $returns)) { $returns = mysql5_type::get_enum_type_declaration($node_type); } $sql .= "RETURNS " . $returns . "\n"; } $sql .= "LANGUAGE SQL\n"; list($eval_type, $determinism) = static::get_characteristics((string) $node_function['cachePolicy'], (string) $node_function['mysqlEvalType']); $eval_type = str_replace('_', ' ', $eval_type); $sql .= "{$eval_type}\n{$determinism}\n"; // unlike pgsql8, mysql5 defaults to SECURITY DEFINER, so we need to set it to INVOKER unless explicitly told to leave it DEFINER if (!isset($node_function['securityDefiner']) || strcasecmp($node_function['securityDefiner'], 'false') == 0) { $sql .= "SQL SECURITY INVOKER\n"; } else { $sql .= "SQL SECURITY DEFINER\n"; } if (!empty($node_function['description'])) { $sql .= "COMMENT " . mysql5::quote_string_value($node_function['description']) . "\n"; } $sql .= trim(static::get_definition($node_function)); $sql = rtrim($sql, ';'); if (mysql5::$swap_function_delimiters) { $sql .= static::ALT_DELIMITER . "\nDELIMITER ;"; } else { $sql .= ';'; } return $sql; }