public static function get_permission_options_sql($node_permission)
{
if (!empty($node_permission['with'])) {
// @TODO: Support MAX_*_PER_HOUR grant options
if (strcasecmp($node_permission['with'], 'grant') != 0) {
dbsteward::warning("Ignoring WITH option '{$node_permission['with']}' because MySQL only supports WITH GRANT OPTION.");
} else {
return " WITH GRANT OPTION";
}
}
}
/**
* Returns full definition of the column.
*
* @param add_defaults whether default value should be added in case NOT
* NULL constraint is specified but no default value is set
*
* @return full definition of the column
*/
public static function get_full_definition($db_doc, $node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_auto_increment = false)
{
// ignore AUTO_INCREMENT flags for now
$is_auto_increment = static::is_auto_increment($node_column['type']);
$orig_type = (string) $node_column['type'];
$node_column['type'] = static::un_auto_increment($node_column['type']);
$column_type = static::column_type($db_doc, $node_schema, $node_table, $node_column);
$definition = mysql5::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
$nullable = static::null_allowed($node_table, $node_column);
$is_timestamp = static::is_timestamp($node_column);
if ($include_null_definition) {
if ($nullable) {
if ($is_timestamp) {
$definition .= " NULL";
}
} else {
$definition .= " NOT NULL";
}
}
if ($include_auto_increment && $is_auto_increment) {
$definition .= " AUTO_INCREMENT";
}
if (strlen($node_column['default']) > 0) {
if (static::is_serial($node_column['type'])) {
$note = "Ignoring default '{$node_column['default']}' on {$node_schema['name']}.{$node_table['name']}.{$node_column['name']} because it is a serial type";
dbsteward::warning($note . "\n");
} else {
$definition .= " DEFAULT " . $node_column['default'];
}
} else {
if ($add_defaults && $is_timestamp) {
if ($nullable) {
$definition .= " DEFAULT NULL";
} else {
$definition .= " DEFAULT CURRENT_TIMESTAMP";
}
} else {
if (!$nullable && $add_defaults) {
$default_col_value = self::get_default_value($node_column['type']);
if ($default_col_value != null) {
$definition .= " DEFAULT " . $default_col_value;
}
}
}
}
if (strlen($node_column['description']) > 0) {
$definition .= " COMMENT " . mysql5::quote_string_value($node_column['description']);
}
// restore the original type of the column
$node_column['type'] = $orig_type;
return $definition;
}
/**
* @NOTICE: sql_format specific!
* Compare dbsteward::$old_database to dbsteward::$new_database
* Generate DDL / DML / DCL statements to upgrade old to new
*
* Changes are outputted to output_file_segementer members of this class
*
* @param object $stage1_ofs stage 1 output file segmentor
* @param object $stage2_ofs stage 2 output file segmentor
* @param object $stage3_ofs stage 3 output file segmentor
* @param object $stage4_ofs stage 4 output file segmentor
* @return void
*/
public static function diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs)
{
if (mysql5_diff::$as_transaction) {
dbsteward::warning("Most MySQL DDL implicitly commits transactions, so using them is pointless.");
}
// start with pre-upgrade sql statements that prepare the database to take on its changes
dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1BEFORE');
dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2BEFORE');
dbsteward::info("Revoke Permissions");
self::revoke_permissions($stage1_ofs, $stage3_ofs);
dbsteward::info("Update Structure");
self::update_structure($stage1_ofs, $stage3_ofs, self::$new_table_dependency);
dbsteward::info("Update Permissions");
self::update_permissions($stage1_ofs, $stage3_ofs);
// self::update_database_config_parameters($stage1_ofs);
dbsteward::info("Update Data");
self::update_data($stage2_ofs, TRUE);
self::update_data($stage4_ofs, FALSE);
// append any literal SQL in new not in old at the end of data stage 1
$old_sql = dbx::get_sql(dbsteward::$old_database);
$new_sql = dbx::get_sql(dbsteward::$new_database);
for ($n = 0; $n < count($new_sql); $n++) {
if (isset($new_sql[$n]['stage'])) {
// ignore upgrade staged sql elements
continue;
}
// is this new statement in the old database?
$found = FALSE;
for ($o = 0; $o < count($old_sql); $o++) {
if (isset($old_sql[$o]['stage'])) {
// ignore upgrade staged sql elements
continue;
}
if (strcmp($new_sql[$n], $old_sql[$o]) == 0) {
$found = TRUE;
}
}
if (!$found) {
$stage2_ofs->write($new_sql[$n] . "\n");
}
}
// append stage defined sql statements to appropriate stage file
dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1');
dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2');
dbx::build_staged_sql(dbsteward::$new_database, $stage3_ofs, 'STAGE3');
dbx::build_staged_sql(dbsteward::$new_database, $stage4_ofs, 'STAGE4');
}
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_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;
}
public static function get_using_option_sql($using)
{
$using = strtoupper((string) $using);
switch ($using) {
case 'HASH':
case 'BTREE':
return $using;
break;
default:
dbsteward::warning("MySQL does not support the {$using} index type, defaulting to BTREE");
return 'BTREE';
break;
}
}
public static function role_enum($db_doc, $role)
{
if (!is_object($db_doc)) {
var_dump($db_doc);
throw new exception("invalid db_doc passed");
}
switch ($role) {
// PUBLIC is accepted as a special placeholder for public
case 'PUBLIC':
if (strcasecmp(dbsteward::get_sql_format(), 'mysql5') == 0) {
// MySQL doesn't have a "public" role, and will attempt to create the user "PUBLIC"
// instead, warn and alias to ROLE_APPLICATION
$role = dbsteward::string_cast($db_doc->database->role->application);
dbsteward::warning("Warning: MySQL doesn't support the PUBLIC role, using ROLE_APPLICATION ('{$role}') instead.");
} else {
$role = 'PUBLIC';
}
break;
case 'ROLE_APPLICATION':
$role = dbsteward::string_cast($db_doc->database->role->application);
break;
case 'ROLE_OWNER':
$role = dbsteward::string_cast($db_doc->database->role->owner);
break;
case 'ROLE_SLONY':
$role = dbsteward::string_cast($db_doc->database->role->replication);
break;
default:
if (isset($db_doc->database->role->customRole)) {
$custom_roles = preg_split("/[\\,\\s]+/", $db_doc->database->role->customRole, -1, PREG_SPLIT_NO_EMPTY);
for ($i = 0; $i < count($custom_roles); $i++) {
if (strcasecmp($role, $custom_roles[$i]) == 0) {
return $custom_roles[$i];
}
}
if (!dbsteward::$ignore_custom_roles) {
throw new exception("Failed to confirm custom role: " . $role);
} else {
// this is cleverville:
// without having to modify the 450000 calls to role_enum
// return role->owner when a role macro is not found and there is no custom role called $role
$owner = $db_doc->database->role->owner;
dbsteward::warning("Warning: Ignoring custom roles. Role '{$role}' is being overridden by ROLE_OWNER ('{$owner}').");
return $owner;
}
}
throw new exception("Unknown role enumeration: " . $role);
break;
}
return $role;
}
public static function update_table_options($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table)
{
if (strcasecmp(dbsteward::get_sql_format(), 'mssql10') === 0) {
dbsteward::warning("mssql10 tableOptions are not implemented yet");
return;
}
if ($new_schema && $new_table) {
$alter_options = array();
$create_options = array();
$drop_options = array();
$old_options = format_table::get_table_options($old_schema, $old_table);
$new_options = format_table::get_table_options($new_schema, $new_table);
// dropped options are those present in the old table, but not in the new
$drop_options = array_diff_key($old_options, $new_options);
// added options are those present in the new table but not in the old
$create_options = array_diff_key($new_options, $old_options);
// altered options are those present in both but with different values
$alter_options = array_intersect_ukey($new_options, $old_options, function ($new_key, $old_key) use($new_options, $old_options) {
if ($new_key == $old_key && strcasecmp($new_options[$new_key], $old_options[$old_key]) !== 0) {
return 0;
} else {
return -1;
}
});
static::apply_table_options_diff($ofs1, $ofs3, $new_schema, $new_table, $alter_options, $create_options, $drop_options);
}
}
/**
* Given an (optional) target sql format, and (optional) requested sql format,
* determine what sql format to use.
*
* The logic below should be fairly straight-forward, but here's an English version:
* * The "target" format is what the XML says it's targeted for
* * The "requested" format is what the user requested on the command line
* * If both are present and agree, there are no problems
* * If both are present and disagree, warn the user and go with the requested
* * If one is missing, go with the given one
* * If both are missing, go with dbsteward::DEFAULT_SQL_FORMAT
*
* @param string $target
* @param string $requested
* @return string
*/
public static function reconcile_sql_format($target, $requested)
{
if ($target !== FALSE) {
if ($requested !== FALSE) {
if (strcasecmp($target, $requested) == 0) {
$use_sql_format = $target;
} else {
dbsteward::warning("WARNING: XML is targeted for {$target}, but you are forcing {$requested}. Things will probably break!");
$use_sql_format = $requested;
}
} else {
// not forcing a sql_format, use target
dbsteward::notice("XML file(s) are targeted for sqlformat={$target}");
$use_sql_format = $target;
}
} elseif ($requested !== FALSE) {
$use_sql_format = $requested;
} else {
$use_sql_format = dbsteward::DEFAULT_SQL_FORMAT;
}
return $use_sql_format;
}
public static function extract_schema($host, $port, $database, $user, $password)
{
$databases = explode(',', $database);
dbsteward::notice("Connecting to mysql5 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
// if not supplied, ask for the password
if ($password === FALSE) {
echo "Password: "******"Analyzing database {$database}");
$db->use_database($database);
$node_schema = $doc->addChild('schema');
$node_schema['name'] = $database;
$node_schema['owner'] = 'ROLE_OWNER';
// extract global and schema permissions under the public schema
foreach ($db->get_global_grants($user) as $db_grant) {
$node_grant = $node_schema->addChild('grant');
// There are 28 permissions encompassed by the GRANT ALL statement
$node_grant['operation'] = $db_grant->num_ops == 28 ? 'ALL' : $db_grant->operations;
$node_grant['role'] = self::translate_role_name($user, $doc);
if ($db_grant->is_grantable) {
$node_grant['with'] = 'GRANT';
}
}
$enum_types = array();
$enum_type = function ($obj, $mem, $values) use(&$enum_types) {
// if that set of values is defined by a previous enum, use that
foreach ($enum_types as $name => $enum) {
if ($enum === $values) {
return $name;
}
}
// otherwise, make a new one
$name = "enum_" . md5(implode('_', $values));
$enum_types[$name] = $values;
return $name;
};
foreach ($db->get_tables() as $db_table) {
dbsteward::info("Analyze table options/partitions " . $db_table->table_name);
$node_table = $node_schema->addChild('table');
$node_table['name'] = $db_table->table_name;
$node_table['owner'] = 'ROLE_OWNER';
// because mysql doesn't have object owners
$node_table['description'] = $db_table->table_comment;
$node_table['primaryKey'] = '';
if (stripos($db_table->create_options, 'partitioned') !== FALSE && ($partition_info = $db->get_partition_info($db_table))) {
$node_partition = $node_table->addChild('tablePartition');
$node_partition['sqlFormat'] = 'mysql5';
$node_partition['type'] = $partition_info->type;
switch ($partition_info->type) {
case 'HASH':
case 'LINEAR HASH':
$opt = $node_partition->addChild('tablePartitionOption');
$opt->addAttribute('name', 'expression');
$opt->addAttribute('value', $partition_info->expression);
$opt = $node_partition->addChild('tablePartitionOption');
$opt->addAttribute('name', 'number');
$opt->addAttribute('value', $partition_info->number);
break;
case 'KEY':
case 'LINEAR KEY':
$opt = $node_partition->addChild('tablePartitionOption');
$opt->addAttribute('name', 'columns');
$opt->addAttribute('value', $partition_info->columns);
$opt = $node_partition->addChild('tablePartitionOption');
$opt->addAttribute('name', 'number');
$opt->addAttribute('value', $partition_info->number);
break;
case 'LIST':
case 'RANGE':
case 'RANGE COLUMNS':
$opt = $node_partition->addChild('tablePartitionOption');
$opt->addAttribute('name', $partition_info->type == 'RANGE COLUMNS' ? 'columns' : 'expression');
$opt->addAttribute('value', $partition_info->expression);
foreach ($partition_info->segments as $segment) {
$node_seg = $node_partition->addChild('tablePartitionSegment');
$node_seg->addAttribute('name', $segment->name);
$node_seg->addAttribute('value', $segment->value);
}
break;
}
}
foreach ($db->get_table_options($db_table) as $name => $value) {
if (strcasecmp($name, 'auto_increment') === 0 && !static::$use_auto_increment_table_options) {
// don't extract auto_increment tableOptions if we're not using them
continue;
}
$node_option = $node_table->addChild('tableOption');
$node_option['sqlFormat'] = 'mysql5';
$node_option['name'] = $name;
$node_option['value'] = $value;
}
dbsteward::info("Analyze table columns " . $db_table->table_name);
foreach ($db->get_columns($db_table) as $db_column) {
$node_column = $node_table->addChild('column');
$node_column['name'] = $db_column->column_name;
if (!empty($db_column->column_comment)) {
$node_column['description'] = $db_column->column_comment;
}
// returns FALSE if not serial, int/bigint if it is
$type = $db->is_serial_column($db_table, $db_column);
if (!$type) {
$type = $db_column->column_type;
if (stripos($type, 'enum') === 0) {
$values = $db->parse_enum_values($db_column->column_type);
$type = $enum_type($db_table->table_name, $db_column->column_name, $values);
}
if ($db_column->is_auto_increment) {
$type .= ' AUTO_INCREMENT';
}
}
if ($db_column->is_auto_update) {
$type .= ' ON UPDATE CURRENT_TIMESTAMP';
}
$node_column['type'] = $type;
// @TODO: if there are serial sequences/triggers for the column then convert to serial
if ($db_column->column_default !== NULL) {
$node_column['default'] = mysql5::escape_default_value($db_column->column_default);
} elseif (strcasecmp($db_column->is_nullable, 'YES') === 0) {
$node_column['default'] = 'NULL';
}
$node_column['null'] = strcasecmp($db_column->is_nullable, 'YES') === 0 ? 'true' : 'false';
}
// get all plain and unique indexes
dbsteward::info("Analyze table indexes " . $db_table->table_name);
foreach ($db->get_indices($db_table) as $db_index) {
// don't process primary key indexes here
if (strcasecmp($db_index->index_name, 'PRIMARY') === 0) {
continue;
}
// implement unique indexes on a single column as unique column, but only if the index name is the column name
if ($db_index->unique && count($db_index->columns) == 1 && strcasecmp($db_index->columns[0], $db_index->index_name) === 0) {
$column = $db_index->columns[0];
$node_column = dbx::get_table_column($node_table, $column);
if (!$node_column) {
throw new Exception("Unexpected: Could not find column node {$column} for unique index {$db_index->index_name}");
} else {
$node_column = $node_column[0];
}
$node_column['unique'] = 'true';
} else {
$node_index = $node_table->addChild('index');
$node_index['name'] = $db_index->index_name;
$node_index['using'] = strtolower($db_index->index_type);
$node_index['unique'] = $db_index->unique ? 'true' : 'false';
$i = 1;
foreach ($db_index->columns as $column_name) {
$node_index->addChild('indexDimension', $column_name)->addAttribute('name', $column_name . '_' . $i++);
}
}
}
// get all primary/foreign keys
dbsteward::info("Analyze table constraints " . $db_table->table_name);
foreach ($db->get_constraints($db_table) as $db_constraint) {
if (strcasecmp($db_constraint->constraint_type, 'primary key') === 0) {
$node_table['primaryKey'] = implode(',', $db_constraint->columns);
} elseif (strcasecmp($db_constraint->constraint_type, 'foreign key') === 0) {
// mysql sees foreign keys as indexes pointing at indexes.
// it's therefore possible for a compound index to point at a compound index
if (!$db_constraint->referenced_columns || !$db_constraint->referenced_table_name) {
throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} does not refer to any foreign columns");
}
if (count($db_constraint->referenced_columns) == 1 && count($db_constraint->columns) == 1) {
// not a compound index, define the FK inline in the column
$column = $db_constraint->columns[0];
$ref_column = $db_constraint->referenced_columns[0];
$node_column = dbx::get_table_column($node_table, $column);
if (!$node_column) {
throw new Exception("Unexpected: Could not find column node {$column} for foreign key constraint {$db_constraint->constraint_name}");
}
$node_column['foreignSchema'] = $db_constraint->referenced_table_schema;
$node_column['foreignTable'] = $db_constraint->referenced_table_name;
$node_column['foreignColumn'] = $ref_column;
unset($node_column['type']);
// inferred from referenced column
$node_column['foreignKeyName'] = $db_constraint->constraint_name;
// RESTRICT is the default, leave it implicit if possible
if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
$node_column['foreignOnDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
}
if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
$node_column['foreignOnUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
}
} elseif (count($db_constraint->referenced_columns) > 1 && count($db_constraint->referenced_columns) == count($db_constraint->columns)) {
$node_fkey = $node_table->addChild('foreignKey');
$node_fkey['columns'] = implode(', ', $db_constraint->columns);
$node_fkey['foreignSchema'] = $db_constraint->referenced_table_schema;
$node_fkey['foreignTable'] = $db_constraint->referenced_table_name;
$node_fkey['foreignColumns'] = implode(', ', $db_constraint->referenced_columns);
$node_fkey['constraintName'] = $db_constraint->constraint_name;
// RESTRICT is the default, leave it implicit if possible
if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
$node_fkey['onDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
}
if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
$node_fkey['onUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
}
} else {
var_dump($db_constraint);
throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} has mismatched columns");
}
} elseif (strcasecmp($db_constraint->constraint_type, 'unique') === 0) {
dbsteward::warning("Ignoring UNIQUE constraint '{$db_constraint->constraint_name}' because they are implemented as indices");
} elseif (strcasecmp($db_constraint->constraint_type, 'check') === 0) {
// @TODO: implement CHECK constraints
} else {
throw new exception("unknown constraint_type {$db_constraint->constraint_type}");
}
}
foreach ($db->get_table_grants($db_table, $user) as $db_grant) {
dbsteward::info("Analyze table permissions " . $db_table->table_name);
$node_grant = $node_table->addChild('grant');
$node_grant['operation'] = $db_grant->operations;
$node_grant['role'] = self::translate_role_name($user, $doc);
if ($db_grant->is_grantable) {
$node_grant['with'] = 'GRANT';
}
}
}
foreach ($db->get_sequences() as $db_seq) {
$node_seq = $node_schema->addChild('sequence');
$node_seq['name'] = $db_seq->name;
$node_seq['owner'] = 'ROLE_OWNER';
$node_seq['start'] = $db_seq->start_value;
$node_seq['min'] = $db_seq->min_value;
$node_seq['max'] = $db_seq->max_value;
$node_seq['inc'] = $db_seq->increment;
$node_seq['cycle'] = $db_seq->cycle ? 'true' : 'false';
// the sequences table is a special case, since it's not picked up in the tables loop
$seq_table = $db->get_table(mysql5_sequence::TABLE_NAME);
foreach ($db->get_table_grants($seq_table, $user) as $db_grant) {
$node_grant = $node_seq->addChild('grant');
$node_grant['operation'] = $db_grant->operations;
$node_grant['role'] = self::translate_role_name($doc, $user);
if ($db_grant->is_grantable) {
$node_grant['with'] = 'GRANT';
}
}
}
foreach ($db->get_functions() as $db_function) {
dbsteward::info("Analyze function " . $db_function->routine_name);
$node_fn = $node_schema->addChild('function');
$node_fn['name'] = $db_function->routine_name;
$node_fn['owner'] = 'ROLE_OWNER';
$node_fn['returns'] = $type = $db_function->dtd_identifier;
if (strcasecmp($type, 'enum') === 0) {
$node_fn['returns'] = $enum_type($db_function->routine_name, 'returns', $db->parse_enum_values($db_function->dtd_identifier));
}
$node_fn['description'] = $db_function->routine_comment;
if (isset($db_function->procedure) && $db_function->procedure) {
$node_fn['procedure'] = 'true';
}
// $node_fn['procedure'] = 'false';
$eval_type = $db_function->sql_data_access;
// srsly mysql? is_deterministic varchar(3) not null default '', contains YES or NO
$determinism = strcasecmp($db_function->is_deterministic, 'YES') === 0 ? 'DETERMINISTIC' : 'NOT DETERMINISTIC';
$node_fn['cachePolicy'] = mysql5_function::get_cache_policy_from_characteristics($determinism, $eval_type);
$node_fn['mysqlEvalType'] = str_replace(' ', '_', $eval_type);
// INVOKER is the default, leave it implicit when possible
if (strcasecmp($db_function->security_type, 'definer') === 0) {
$node_fn['securityDefiner'] = 'true';
}
foreach ($db_function->parameters as $param) {
$node_param = $node_fn->addChild('functionParameter');
// not supported in mysql functions, even though it's provided?
// $node_param['direction'] = strtoupper($param->parameter_mode);
$node_param['name'] = $param->parameter_name;
$node_param['type'] = $type = $param->dtd_identifier;
if (strcasecmp($type, 'enum') === 0) {
$node_param['type'] = $enum_type($db_function->routine_name, $param->parameter_name, $db->parse_enum_values($param->dtd_identifier));
}
if (isset($param->direction)) {
$node_param['direction'] = $param->direction;
}
}
$node_def = $node_fn->addChild('functionDefinition', $db_function->routine_definition);
$node_def['language'] = 'sql';
$node_def['sqlFormat'] = 'mysql5';
}
foreach ($db->get_triggers() as $db_trigger) {
dbsteward::info("Analyze trigger " . $db_trigger->name);
$node_trigger = $node_schema->addChild('trigger');
foreach ((array) $db_trigger as $k => $v) {
$node_trigger->addAttribute($k, $v);
}
$node_trigger->addAttribute('sqlFormat', 'mysql5');
}
foreach ($db->get_views() as $db_view) {
dbsteward::info("Analyze view " . $db_view->view_name);
if (!empty($db_view->view_name) && empty($db_view->view_query)) {
throw new Exception("Found a view in the database with an empty query. User '{$user}' problaby doesn't have SELECT permissions on tables referenced by the view.");
}
$node_view = $node_schema->addChild('view');
$node_view['name'] = $db_view->view_name;
$node_view['owner'] = 'ROLE_OWNER';
$node_view->addChild('viewQuery', $db_view->view_query)->addAttribute('sqlFormat', 'mysql5');
}
foreach ($enum_types as $name => $values) {
$node_type = $node_schema->addChild('type');
$node_type['type'] = 'enum';
$node_type['name'] = $name;
foreach ($values as $v) {
$node_type->addChild('enum')->addAttribute('name', $v);
}
}
}
xml_parser::validate_xml($doc->asXML());
return xml_parser::format_xml($doc->saveXML());
}
public static function slony_replica_set_contains_table_column_serial_sequence($db_doc, $replica_set, $schema, $table, $column)
{
// is it a serial column and therefore an implicit sequence to replicate?
if (preg_match(pgsql8::PATTERN_SERIAL_COLUMN, $column['type']) > 0) {
if (isset($column['slonyId']) && strlen($column['slonyId']) > 0) {
if (strcasecmp('IGNORE_REQUIRED', $column['slonyId']) == 0) {
// the slonyId IGNORE_REQUIRED magic value allows for slonyId's to be required
// but also allow for some table columns to not be replicated even with the flag on
return FALSE;
} else {
if (!is_numeric(dbsteward::string_cast($column['slonyId']))) {
throw new exception("serial column " . $column['name'] . " slonyId " . $column['slonyId'] . " is not numeric");
} else {
if (strcmp($replica_set['id'], $column['slonySetId']) == 0) {
// this sequence is for the intended replica set
return TRUE;
} else {
if (!isset($column['slonySetId'])) {
if (dbsteward::$require_slony_set_id) {
throw new exception($schema['name'] . '.' . $table['name'] . '.' . $column['name'] . " has a slonyId without a slonySetId, and --requiremissing slonyId and slonyIds are required");
}
// this column has no replica set
$first_replica_set = static::get_slony_replica_set_natural_first($db_doc);
if (strcmp($replica_set['id'], $first_replica_set['id']) == 0) {
// but the $replica_set passed is the FIRST NATURAL ORDER replica set
// so yes this column pertains to this replica_set
return TRUE;
}
}
}
}
}
} else {
dbsteward::warning("Warning: " . str_pad($schema['name'] . '.' . $table['name'] . '.' . $column['name'], 44) . " serial column missing slonyId\t" . self::get_slony_next_id_dialogue($db_doc));
if (dbsteward::$require_slony_id) {
throw new exception($schema['name'] . '.' . $table['name'] . '.' . $column['name'] . " serial column missing slonyId and slonyIds are required");
}
}
} else {
if (isset($column['slonyId'])) {
throw new exception($schema['name'] . '.' . $table['name'] . " non-serial column " . $column['name'] . " has slonyId specified. I do not understand");
}
}
return FALSE;
}
public static function &get_function(&$node_schema, $name, $declaration = NULL, $create_if_not_exist = FALSE)
{
$node_function = NULL;
$nodes = $node_schema->xpath("function[@name='" . $name . "']");
// filter out versions of functon in languages that are not relevant to the current format being processed
$filtered_nodes = array();
foreach ($nodes as $node) {
if (format_function::has_definition($node)) {
$filtered_nodes[] = $node;
}
}
$nodes = $filtered_nodes;
// TODO: this logic is buggy and needs fixed
if (count($nodes) == 0) {
if ($create_if_not_exist) {
// function not found, caller wants the function created in the db
$node_function = $node_schema->addChild('function');
$node_function->addAttribute('name', $name);
}
} else {
if (count($nodes) > 1) {
if (strlen($declaration) == 0) {
throw new exception("more than one match for function " . $name . " and declaration is blank");
}
foreach ($nodes as $node) {
if (strcasecmp(pgsql8_function::get_declaration($node_schema, $node), $declaration) == 0) {
if ($node_function == NULL) {
$node_function = $node;
} else {
throw new exception("more than one function match " . $name . " matches passed declaration: " . $declaration);
}
}
}
if ($node_function == NULL) {
//@DEBUG: use this to make sure function declaration comparisons are working properly
dbsteward::warning("NOTICE: no functions named " . $name . " match passed declaration: " . $declaration);
}
} else {
$node_function = $nodes[0];
}
}
return $node_function;
}
/**
* Adds commands for modification of columns to the list of
* commands.
*
* @param commands list of commands
* @param old_table original table
* @param new_table new table
* @param drop_defaults_columns list for storing columns for which default value should be dropped
*/
private static function add_modify_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns)
{
foreach (dbx::get_table_columns($new_table) as $new_column) {
if (!mssql10_table::contains_column($old_table, $new_column['name'])) {
continue;
}
if (!dbsteward::$ignore_oldnames && mssql10_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) {
// oldColumnName renamed column ? skip definition diffing on it, it is being renamed
continue;
}
$quoted_table_name = mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']);
$old_column = dbx::get_table_column($old_table, $new_column['name']);
$new_column_name = mssql10::get_quoted_column_name($new_column['name']);
$old_column_type = null;
if ($old_column) {
$old_column_type = mssql10_column::column_type(dbsteward::$old_database, $new_schema, $old_table, $old_column, $foreign);
}
$new_column_type = mssql10_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign);
if (strcmp($old_column_type, $new_column_type) != 0) {
// ALTER TYPE .. USING support by looking up the new type in the xml definition
$type_using = '';
$type_using_comment = '';
if (isset($new_column['convertUsing'])) {
$type_using = ' USING ' . $new_column['convertUsing'] . ' ';
$type_using_comment = '- found XML convertUsing: ' . $new_column['convertUsing'] . ' ';
}
// if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum
if (mssql10_column::enum_type_check(dbsteward::$new_database, $new_schema, $new_table, $new_column, $drop_sql, $add_sql)) {
// enum types rewritten as varchar(255)
$new_column_type = 'varchar(255)';
$commands[] = array('stage' => 'AFTER1', 'command' => $drop_sql);
$commands[] = array('stage' => 'AFTER1', 'command' => $add_sql);
}
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . $type_using . " /* TYPE change - table: " . $new_table['name'] . " original: " . $old_column_type . " new: " . $new_column_type . ' ' . $type_using_comment . '*/');
}
$old_default = isset($old_column['default']) ? $old_column['default'] : '';
$new_default = isset($new_column['default']) ? $new_column['default'] : '';
// has the default has changed?
if (strcmp($old_default, $new_default) != 0) {
// in MSSQL, violating the SQL standard,
// inline column default definitions are translated to be table constraints
// the constraint name is somewhat predictable, but not always.
// some versions apped random numebrs when implicitly creating the constraint
// was there a constraint before?
if (strlen($old_default) > 0) {
$commands[] = array('stage' => 'BEFORE1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' DROP CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $old_column['name'] . ';');
}
// is there now a default constraint?
if (strlen($new_default) > 0) {
$commands[] = array('stage' => 'AFTER1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' ADD CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $new_column['name'] . ' DEFAULT ' . $new_default . ' FOR ' . $new_column_name . ';');
}
}
if (strcasecmp($old_column['null'], $new_column['null']) != 0) {
if (mssql10_column::null_allowed($new_table, $new_column)) {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NULL");
} else {
if (mssql10_diff::$add_defaults) {
$default_value = mssql10_column::get_default_value($new_column_type);
if ($default_value != NULL) {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $default_value);
$drop_defaults_columns[] = $new_column;
}
}
// if the default value is defined in the dbsteward XML
// set the value of the column to the default in end of stage 1 so that NOT NULL can be applied in stage 3
// this way custom <sql> tags can be avoided for upgrade generation if defaults are specified
if (strlen($new_column['default']) > 0) {
$commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " SET " . $new_column_name . " = " . $new_column['default'] . " WHERE " . $new_column_name . " IS NULL; -- has_default_now: make modified column that is null the default value before NOT NULL hits");
}
// before altering column, remove any constraint that would stop us from doing so
foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) {
if (preg_match('/' . $new_column['name'] . '[\\s,=)]/', $constraint['definition']) > 0) {
$commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_drop_sql_change_statement($constraint));
}
}
$commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NOT NULL");
// add the constraint back on
foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) {
if (preg_match('/' . $new_column['name'] . '[\\s,=\\)]/', $constraint['definition']) > 0) {
$commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_sql_change_statement($constraint));
}
}
}
}
// for identity()'d columns (serial in dbsteward definition) in mssql that are to no longer be
// we must recreate the table with out the identity attribute
if (preg_match('/int\\sidentity.*$/', $old_column['type']) > 0 && ($new_column['type'] == 'int' || $new_column['type'] == 'bigint')) {
dbsteward::warning("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " requires rebuild to drop identity property on " . $new_column['name']);
// create a "deep copy" of the table so column and rows
// references are not altered in the original old DOM
$table_for_modifying_xml = $new_table->asXML();
$table_for_modifying = simplexml_load_string($table_for_modifying_xml);
// @NOTICE: we do this because of by reference nature of get_table_column()
// any subsequent references to old table's pkey column(s) would show the type as int/bigint and not the identity() definition
// get the column then modify the type to remove the identity
$old_id_pkey_col = dbx::get_table_column($table_for_modifying, $old_column['name']);
$table_for_modifying['name'] = 'tmp_identity_drop_' . $table_for_modifying['name'];
if (preg_match('/^int/', $old_column['type']) > 0) {
$old_id_pkey_col['type'] = 'int';
} else {
$old_id_pkey_col['type'] = 'bigint';
}
// see FS#25730 - dbsteward not properly upgrading serial to int
// http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/
// section start comment
$identity_transition_commands = array('-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild is necessary');
// get the creation sql for a temporary table
$identity_transition_commands[] = mssql10_table::get_creation_sql($new_schema, $table_for_modifying);
// copy over all the old data into new data, it's the only way
$identity_transition_commands[] = "IF EXISTS(SELECT * FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']) . ")\n EXEC('INSERT INTO " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($table_for_modifying['name']) . " ( " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . ")\n SELECT " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . "\n FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " WITH (HOLDLOCK TABLOCKX)');";
// drop FKEYs other tables have to the table
$other_tables_foreign_keying_constraints = dbx::get_tables_foreign_keying_to_table(dbsteward::$new_database, mssql10_diff::$new_table_dependency, $new_schema, $new_table);
dbsteward::info("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " rebuild has " . count($other_tables_foreign_keying_constraints) . " foreign key references to drop and reapply");
foreach ($other_tables_foreign_keying_constraints as $constraint) {
$identity_transition_commands[] = mssql10_table::get_constraint_drop_sql($constraint);
}
// drop the old table
$identity_transition_commands[] = "DROP TABLE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . ";";
// rename temporary table to original name
// NOTE: sp_rename only takes an identifier for the new name, if you schema qualify the new name it will get doubled on the table name
$identity_transition_commands[] = "EXECUTE sp_rename '" . $new_schema['name'] . "." . $table_for_modifying['name'] . "', '" . $new_table['name'] . "', 'OBJECT';";
// mssql10_table:::get_creation_sql() only creates the table
// now that it has been renamed, recreate the table's indexes keys and triggers
$tc_buffer = fopen("php://memory", "rw");
$tc_ofs = new output_file_segmenter('identity_transition_command', 1, $tc_buffer, 'identity_transition_command_buffer');
mssql10_diff_indexes::diff_indexes_table($tc_ofs, NULL, NULL, $new_schema, $new_table);
mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'primaryKey', FALSE);
mssql10_diff_triggers::diff_triggers_table($tc_ofs, NULL, NULL, $new_schema, $new_table);
mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'constraint', FALSE);
rewind($tc_buffer);
while (($tc_line = fgets($tc_buffer, 4096)) !== false) {
$identity_transition_commands[] = $tc_line;
}
unset($tc_ofs);
// restore FKEYs other tables have to the table
foreach ($other_tables_foreign_keying_constraints as $constraint) {
$identity_transition_commands[] = mssql10_table::get_constraint_sql($constraint);
}
// section end comment
$identity_transition_commands[] = '-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild end' . "\n";
// put all of the identity_transition_commands into the command list as BEFORE3's
// this will make the identity column changes occur at the beginning of stage 3
foreach ($identity_transition_commands as $itc) {
$commands[] = array('stage' => 'BEFORE3', 'command' => $itc);
}
}
}
}
/**
* returns if quote_name is true then returns quoted name otherwise returns the original name
*
* @param name name
* @param quote_name whether the name should be quoted
*
* @return string
*/
public static function get_quoted_name($name, $quoted, $quote_char)
{
$quoted = $quoted || dbsteward::$quote_all_names;
if (!$quoted) {
if (static::is_illegal_identifier($name)) {
if (dbsteward::$quote_illegal_identifiers) {
dbsteward::warning("Warning: Quoting illegal identifier '{$name}'");
$quoted = true;
} else {
throw new Exception("Illegal identifier: '{$name}' - turn on quoting of illegal identifiers with --quoteillegalnames");
}
} elseif (static::is_identifier_blacklisted($name)) {
if (dbsteward::$quote_reserved_identifiers) {
dbsteward::warning("Warning: Quoting reserved identifier '{$name}'");
$quoted = true;
} else {
throw new Exception("Reserved identifier: '{$name}' - turn on quoting of reserved identifiers with --quotereservednames");
}
}
}
if ($quoted) {
return $quote_char . $name . $quote_char;
} else {
return $name;
}
}
/**
* 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_drop_sql($node_schema, $node_function)
{
if (!static::has_definition($node_function)) {
$note = "Not dropping function '{$node_function['name']}' - no definitions for mysql5";
dbsteward::warning($note);
return "-- {$note}\n";
}
$function_type = static::is_procedure($node_function) ? 'PROCEDURE' : 'FUNCTION';
return "DROP {$function_type} IF EXISTS " . mysql5::get_fully_qualified_object_name($node_schema['name'], $node_function['name'], 'function') . ";";
}