/**
* Given a type name like `schema`.`enum_name`, find the type node in schema 'schema' with name 'enum_name'
* @return SimpleXMLElement
*/
public static function get_type_node($db_doc, $node_schema, $name_ref)
{
if (preg_match('/(?:["`]?(\\w+)["`]?\\.)?["`]?(.+)["`]?/', $name_ref, $matches) > 0) {
$schema_ref = $matches[1];
$type_name = $matches[2];
// if we found a schema name in the name reference, then attempt to override the given node_schema with the named one
if (!$schema_ref) {
if (!$node_schema) {
throw new Exception("No schema node given and no schema name found in type name reference '{$name_ref}'");
}
} else {
$node_schema = dbx::get_schema($db_doc, $schema_ref);
if (!$node_schema) {
throw new Exception("Could not find schema '{$schema_ref}', given by type name reference '{$name_ref}'");
}
}
$node_type = dbx::get_type($node_schema, $type_name);
if (!$node_type) {
// we did not find the given type - this is not exceptional because we might just be testing to see if it exists
return NULL;
}
// if we got this far, we found the referenced type node
return $node_type;
}
throw new Exception("Unrecognizable type name reference: '{$name_ref}'");
}
/**
* 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 (!pgsql8_schema::contains_function($new_schema, pgsql8_function::get_declaration($new_schema, $old_function, FALSE))) {
$ofs3->write(pgsql8_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'], pgsql8_function::get_declaration($new_schema, $new_function));
}
if ($old_function == null || !pgsql8_function::equals($new_schema, $new_function, $old_function, pgsql8_diff::$ignore_function_whitespace)) {
$ofs1->write(pgsql8_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(pgsql8_function::get_creation_sql($new_schema, $new_function) . "\n");
} else {
if (pgsql8_schema::contains_type($new_schema, $new_function['returns']) && pgsql8_schema::contains_type($old_schema, $new_function['returns']) && !pgsql8_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(pgsql8_function::get_creation_sql($new_schema, $new_function) . "\n");
}
}
}
}
}
/**
* in MSSQL indexes must contain column references, value expressions are not allowed
*
*/
public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql)
{
$dimension_list = '';
$add_column_sql = '';
// in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql
$i = 0;
foreach ($node_index->indexDimension as $dimension) {
$i++;
$dimension_name = (string) $dimension;
if (mssql10_table::contains_column($node_table, $dimension_name)) {
// dimension is an explicit column
// check unique index indexDimensions for nulled columns
// mssql index constraint engine will not ignore null values for nullable columns
if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
$node_column = dbx::get_table_column($node_table, $dimension_name);
if (mssql10_column::null_allowed($node_table, $node_column)) {
dbsteward::error("dimension_name = " . $dimension_name);
//var_dump($node_column);
throw new exception("nulled column index found");
}
}
} else {
// not an explicit column, so create one
$dimension_name = $node_index['name'] . '_' . $i;
$add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . " ADD " . $dimension_name . " AS " . (string) $dimension . ";\n";
}
$dimension_list .= $dimension_name . ', ';
}
$dimension_list = substr($dimension_list, 0, -2);
return $dimension_list;
}
public static function has_permission($node_object, $node_permission)
{
$permission_operations = pgsql8_permission::get_permission_operations($node_permission);
// for each of the rights the node_permission provides
foreach ($permission_operations as $permission_operation) {
// look at each of the permissions on the node_object and see if the right is given
foreach (dbx::get_permissions($node_object) as $node_object_permission) {
if (strcasecmp($node_object_permission->getName(), $node_permission->getName()) == 0) {
if (strcasecmp($node_object_permission['role'], $node_permission['role']) == 0) {
// if this node_object_permission of node_object provides the right
// the permission is confirmed provided in the object already
if (in_array($permission_operation, pgsql8_permission::get_permission_operations($node_object_permission))) {
// so move on to the next permission_operation
continue 2;
}
}
}
}
// if we get here if the right is not found in the in_array comparison for the object
dbsteward::warning("permission_operation " . $permission_operation . " not found in " . $node_object['name'] . " permissions for " . $node_permission['role']);
return false;
}
// if we get here then all rights were found to be provided in the object already
// so the answer to has_permission? is yes
return true;
}
/**
* Parses CREATE TRIGGER command.
*
* @param database database
* @param command CREATE TRIGGER command
*
* @throws ParserException Thrown if problem occured while parsing the
* command.
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN, trim($command), $matches) > 0) {
$trigger_name = trim($matches[1]);
$when = $matches[2];
$events = array();
if (strlen($matches[3]) > 0) {
$events[] = $matches[3];
}
if (strlen($matches[4]) > 0) {
$events[] = $matches[4];
}
if (strlen($matches[5]) > 0) {
$events[] = $matches[5];
}
$table_name = trim($matches[6]);
$fireOn = $matches[7];
$procedure = $matches[8];
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database));
$node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name));
if ($node_table == null) {
throw new exception("Failed to find trigger table " . $trigger->get_table_name());
}
$node_trigger =& dbx::get_table_trigger($node_schema, $node_table, $trigger_name, true);
dbx::set_attribute($node_trigger, 'when', strcasecmp('BEFORE', $when) == 0 ? 'BEFORE' : 'AFTER');
dbx::set_attribute($node_trigger, 'forEach', strcasecmp('ROW', $when) == 0 ? 'ROW' : 'STATEMENT');
dbx::set_attribute($node_trigger, 'function', trim($procedure));
dbx::set_attribute($node_trigger, 'event', implode(', ', $events));
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Parses CREATE TYPE command.
*
* @param database database
* @param command CREATE TYPE command
*/
public static function parse($database, $command)
{
$line = $command;
// CREATE PROCEDURAL LANGUAGE plpgsql;
// CREATE [ PROCEDURAL ] LANGUAGE name
// CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
// HANDLER call_handler [ VALIDATOR valfunction ]
if (preg_match(self::PATTERN_CREATE_LANGUAGE, $line, $matches) > 0) {
// simplify parsing by killing last semicolon
$line = sql_parser::remove_last_semicolon($line);
// break up the command by whitespace
$chunks = preg_split('/[\\s]+/', $line, -1, PREG_SPLIT_NO_EMPTY);
// shift the LANGUAGE keyword off
array_shift($chunks);
// shift the language name off
$language_name = array_shift($chunks);
// create language entry
$language =& dbx::get_language($database, $language_name, true);
// grab the language modifiers
while (strcasecmp('LANGUAGE', $chunks[0]) != 0) {
if (strcasecmp('CREATE', $chunks[0]) == 0) {
// expected first CREATE lead doesn't modify anything
} else {
if (strcasecmp('TRUSTED', $chunks[0]) == 0) {
dbx::set_attribute($language, 'trusted', $chunks[0]);
} else {
if (strcasecmp('PROCEDURAL', $chunks[0]) == 0) {
dbx::set_attribute($language, 'procedural', $chunks[0]);
} else {
throw new exception("unknown CREATE LANGUAGE modifier: " . $chunks[0]);
}
}
}
// shift the lead chunk off now that it has been interpreted
array_shift($chunks);
}
// if there are chunks left, figure out what optional parameteres they are and save them in the language object
// make sure it's not the trailing ;, we don't care
while (count($chunks) > 0 && trim(implode(' ', $chunks)) != ';') {
if (strcasecmp('HANDLER', $chunks[0]) == 0) {
dbx::set_attribute($language, 'handler', $chunks[1]);
} else {
if (strcasecmp('VALIDATOR', $chunks[0]) == 0) {
dbx::set_attribute($language, 'validator', $chunks[1]);
} else {
throw new exception("unknown CREATE LANGUAGE callback: " . $chunks[0]);
}
}
// shift the lead chunk and its value off now that it has been interpreted
array_shift($chunks);
array_shift($chunks);
}
} else {
throw new exception("Cannot parse command: " . $line);
}
}
/**
* Outputs commands for dropping languages.
*
* @param $ofs output file segmenter
* @param $old_database original database
* @param $new_database new database
*/
private static function drop_languages($ofs)
{
if (dbsteward::$old_database != null) {
foreach (dbx::get_languages(dbsteward::$old_database) as $language) {
if (dbx::get_language(dbsteward::$new_database, $language['name']) == null) {
$ofs->write(pgsql8_language::get_drop_sql($language) . "\n");
}
}
}
}
/**
* Outputs commands for dropping types.
*
* @param $ofs output file pointer
* @param $old_schema original schema
* @param $new_schema new schema
*/
private static function drop_types($ofs, $old_schema, $new_schema)
{
if ($old_schema != NULL) {
foreach (dbx::get_types($old_schema) as $type) {
if (!mssql10_schema::contains_type($new_schema, $type['name'])) {
$ofs->write(mssql10_type::get_drop_sql($new_schema, $type) . "\n");
}
}
}
}
/**
* Creates and returns SQL for dropping the trigger.
*
* @return created SQL
*/
public static function get_drop_sql($node_schema, $node_trigger)
{
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == null) {
throw new exception("Failed to find trigger table " . $node_trigger['table'] . " in schema node " . $node_schema['name']);
}
$table_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_table['name']);
$ddl = "DROP TRIGGER " . pgsql8::get_quoted_object_name($node_trigger['name']) . " ON " . $table_name . ";\n";
return $ddl;
}
/**
* Creates new schemas (not the objects inside the schemas)
*
* @param object $ofs output file pointer
* @return void
*/
protected static function create_new_schemas($ofs)
{
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == null) {
dbsteward::info("Create New Schema " . $new_schema['name']);
pgsql8::set_context_replica_set_id($new_schema);
$ofs->write(format_schema::get_creation_sql($new_schema));
}
}
}
private static function drop_types($ofs, $old_schema, $new_schema)
{
if ($old_schema != NULL) {
foreach (dbx::get_types($old_schema) as $type) {
if (!format_schema::contains_type($new_schema, $type['name'])) {
$ofs->write(format_type::get_drop_sql($new_schema, $type) . "\n");
// $ofs->write(mysql5_type::get_type_demotion_sql($new_schema, $type));
}
}
}
}
/**
* Drop all missing or modified views
*
* @param $ofs output file segmenter
* @param $old_schema original schema
* @param $new_schema new schema
*/
public static function drop_views($ofs, $old_schema, $new_schema)
{
if ($old_schema != NULL) {
foreach (dbx::get_views($old_schema) as $old_view) {
$new_view = dbx::get_view($new_schema, $old_view['name']);
if ($new_view == NULL || self::is_view_modified($old_view, $new_view)) {
$ofs->write(mssql10_view::get_drop_sql($old_schema, $old_view) . "\n");
}
}
}
}
/**
* Returns true if schema contains function with given $declaration, otherwise false.
*
* @param $declaration declaration of the function
*
* @return true if schema contains function with given $declaration, otherwise false
*/
public function contains_function($node_schema, $declaration)
{
$found = FALSE;
foreach (dbx::get_functions($node_schema) as $node_function) {
if (strcasecmp(mssql10_function::get_declaration($node_schema, $node_function), $declaration) == 0) {
$found = TRUE;
break;
}
}
return $found;
}
/**
* Returns schema name from optionally schema qualified name.
*
* @param name optionally schema qualified name
* @param database database
*
* @return name of the schema
*/
public static function get_schema_name($name, $database)
{
$pos = strpos($name, '.');
if ($pos === FALSE) {
$default_schema = dbx::get_default_schema();
$result = $default_schema['name'];
} else {
$result = substr($name, 0, $pos);
}
return self::quoted_name_strip($result);
}
public static function get_creation_sql($node_schema, $node_trigger)
{
$events = self::get_events($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";
}
if (empty($node_trigger['function'])) {
throw new Exception("No trigger body defined for trigger '{$node_trigger['name']}'");
}
if (!($when = self::validate_when($node_trigger['when']))) {
throw new Exception("Invalid WHEN clause for trigger '{$node_trigger['name']}': '{$node_trigger['when']}'");
}
$notes = "";
if (count($events) == 0) {
throw new Exception("No events were given for trigger {$node_trigger['name']}");
} elseif (count($events) > 1) {
$notes .= "-- You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time\n";
$notes .= "-- generating separate triggers for each event\n";
dbsteward::warning("You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time");
dbsteward::warning(" generating separate triggers for each event");
}
if (!empty($node_trigger['forEach']) && strcasecmp($node_trigger['forEach'], 'row')) {
dbsteward::error($notes .= "-- You specified a forEach value of {$node_trigger['forEach']} on trigger {$node_trigger['name']} but MySQL only supports ROW - ignoring\n");
}
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == null) {
throw new exception("Failed to find trigger's table '{$node_trigger['table']}' in schema node '{$node_schema['name']}'");
}
$table_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
// always drop triggers before creating them
$ddl = static::get_drop_sql($node_schema, $node_trigger);
$single = count($events) == 1;
foreach ($events as $event) {
if (!($event = self::validate_event($event))) {
throw new Exception("Invalid event on trigger '{$node_trigger['name']}': '{$event}'");
}
// @TODO: use something like get_fully_qualified_object_name
$trigger_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . ($single ? '' : "_{$event}"));
$trigger_fn = trim($node_trigger['function']);
if (substr($trigger_fn, -1) != ';') {
$trigger_fn .= ';';
}
$ddl .= <<<SQL
CREATE TRIGGER {$trigger_name} {$when} {$event} ON {$table_name}
FOR EACH ROW {$trigger_fn}
SQL;
}
return $notes . $ddl;
}
protected static function schema_contains_trigger($schema, $trigger)
{
if (parent::schema_contains_trigger($schema, $trigger)) {
return TRUE;
}
foreach (dbx::get_tables($schema) as $table) {
foreach (mysql5_table::get_triggers_needed($schema, $table) as $table_trigger) {
if (strcasecmp($table_trigger['name'], $trigger['name']) === 0) {
return TRUE;
}
}
}
return FALSE;
}
private function _testDbxForeignKey()
{
$doc = simplexml_load_string($this->xml);
$schema = $doc->schema;
$table1 = $schema->table[0];
$table1_t1id = $table1->column[0];
$table1_t2id = $table1->column[1];
$table2 = $schema->table[1];
$table2_t2id = $table2->column[0];
dbx::foreign_key($doc, $schema, $table1, $table1_t2id, $foreign);
$this->assertEquals($schema, $foreign['schema']);
$this->assertEquals($table2, $foreign['table']);
$this->assertEquals($table2_t2id, $foreign['column']);
}
/**
* Parses GRANT and REVOKE commands
*
* @param database database
* @param command REVOKE command
*/
public static function parse($database, $command)
{
$command = sql_parser::remove_last_semicolon($command);
if (preg_match(self::PATTERN_CONFIG_PARAMETER, $command, $matches) > 0) {
if (count($matches) != 3) {
var_dump($matches);
throw new exception("Database configuration parameter call preg exploded into " . count($matches) . ", panic!");
}
// just do what the call does push around the name -> value
$configuration_parameter =& dbx::get_configuration_parameter($database, $matches[1], true);
dbx::set_attribute($configuration_parameter, 'value', $matches[2]);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Parses ALTER SCHEMA command.
*
* @param database database
* @param command ALTER SCHEMA command
*
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) {
$line = $command;
$schema_name = trim($matches[1]);
$owner_name = trim($matches[2]);
$schema =& dbx::get_schema($database, $schema_name);
if ($schema == null) {
throw new exception("failed to find schema " . $schema_name . " for alter owner statement");
}
dbx::set_attribute($schema, 'owner', $owner_name);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Parses ALTER LANGUAGE command.
*
* @param database database
* @param command ALTER LANGUAGE command
*
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN, $command, $matches) > 0) {
$line = $command;
$language_name = trim($matches[1]);
$owner_name = trim($matches[2]);
$language =& dbx::get_language($database, $language_name);
if ($language == null) {
throw new exception("Language " . $language_name . " not found. Is the create for it missing or after the ALTER LANGUAGE statement ?");
}
dbx::set_attribute($language, 'owner', $owner_name);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Parses CREATE SCHEMA command.
*
* @param database database
* @param command CREATE SCHEMA command
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN_CREATE_SCHEMA, $command, $matches) > 0) {
$node_schema = dbx::get_schema($database, $matches[1], true);
if (isset($matches[2])) {
dbx::set_attribute($node_schema, 'authorization', $matches[2]);
}
} else {
if (preg_match(self::PATTERN_CREATE_SCHEMA_AUTHORIZATION, $command, $matches) > 0) {
$node_schema = dbx::get_schema($database, $matches[1], true);
dbx::set_attribute($node_schema, 'authorization', $node_schema['name']);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
}
/**
* Parses CREATE VIEW command.
*
* @param database database
* @param command CREATE VIEW command
*/
public static function parse($database, $command)
{
if (preg_match(self::PATTERN, trim($command), $matches) > 0) {
$view_name = $matches[1];
$column_names = $matches[2];
$query = $matches[3];
if (strlen($view_name) == 0 || strlen($query) == 0) {
throw new exception("Cannot parse command: " . $command);
}
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($view_name, $database));
$node_view =& dbx::get_view($node_schema, sql_parser::get_object_name($view_name, $database), true);
$node_view->addChild('viewQuery', $query);
} else {
throw new exception("Cannot parse command: " . $command);
}
}
/**
* Returns list of indexes that should be dropped.
*
* @param old_table original table
* @param new_table new table
*
* @return list of indexes that should be dropped
*
* @todo Indexes that are depending on a removed field should not be added
* to drop because they are already removed.
*/
public static function get_drop_indexes($old_schema, $old_table, $new_schema, $new_table)
{
$list = array();
if ($new_table != null && $old_table != null) {
foreach (format_index::get_table_indexes($old_schema, $old_table) as $old_index) {
$new_index = dbx::get_table_index($new_schema, $new_table, $old_index['name']);
if (!format_table::contains_index($new_schema, $new_table, $old_index['name'])) {
$list[] = $old_index;
} else {
if (!format_index::equals($new_index, $old_index)) {
$list[] = $old_index;
}
}
}
}
return $list;
}
private static function schema_contains_sequence($schema, $sequence_name, $include_oldnames = FALSE)
{
if (mysql5_schema::contains_sequence($schema, $sequence_name)) {
return TRUE;
}
foreach (dbx::get_tables($schema) as $table) {
foreach (mysql5_table::get_sequences_needed($schema, $table) as $sequence) {
if (strcasecmp($sequence['name'], $sequence_name) === 0) {
return TRUE;
}
if ($include_oldnames && !dbsteward::$ignore_oldnames && !empty($sequence['oldSequenceName']) && strcasecmp($sequence['oldSequenceName'], $sequence_name) === 0) {
return TRUE;
}
}
}
return FALSE;
}
/**
* Modify sequence values if they have changed
*
* @param $ofs output file pointer
* @param $old_schema original schema
* @param $new_schema new schema
*/
private static function add_modified_sequences($ofs, $old_schema, $new_schema)
{
foreach (dbx::get_sequences($new_schema) as $new_sequence) {
if ($old_schema != null && pgsql8_schema::contains_sequence($old_schema, $new_sequence['name'])) {
$old_sequence = dbx::get_sequence($old_schema, $new_sequence['name']);
$sql = '';
if ($new_sequence['inc'] != null && strcasecmp($new_sequence['inc'], $old_sequence['inc']) != 0) {
$sql .= "\n\tINCREMENT BY " . $new_sequence['inc'];
}
if ($new_sequence['min'] == null && $old_sequence['min'] != null) {
$sql .= "\n\tNO MINVALUE";
} else {
if ($new_sequence['min'] != null && strcasecmp($new_sequence['min'], $old_sequence['min']) != 0) {
$sql .= "\n\tMINVALUE " . $new_sequence['min'];
}
}
if ($new_sequence['max'] == null && $old_sequence['max'] != null) {
$sql .= "\n\tNO MAXVALUE";
} else {
if ($new_sequence['max'] != null && strcasecmp($new_sequence['max'], $old_sequence['max']) != 0) {
$sql .= "\n\tMAXVALUE " . $new_sequence['max'];
}
}
if (!pgsql8_diff::$ignore_start_with) {
if ($new_sequence['start'] != null && strcasecmp($new_sequence['start'], $old_sequence['start']) != 0) {
$sql .= "\n\tRESTART WITH " . $new_sequence['start'];
}
}
if ($new_sequence['cache'] != null && strcasecmp($new_sequence['cache'], $old_sequence['cache']) != 0) {
$sql .= "\n\tCACHE " . $new_sequence['cache'];
}
if ($old_sequence['cycle'] && !$new_sequence['cycle']) {
$sql .= "\n\tNO CYCLE";
} else {
if (!$old_sequence['cycle'] && $new_sequence['cycle']) {
$sql .= "\n\tCYCLE";
}
}
if (strlen($sql) > 0) {
$ofs->write("ALTER SEQUENCE " . pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_sequence['name']) . $sql . ";\n");
}
}
}
}
/**
* Outputs DDL for differences in sequences
* @NOTICE note that sequences are created/handled for MSSQL by mssql10_bit_table
*
* @param $ofs output file pointer
* @param $old_schema original schema
* @param $new_schema new schema
*/
public static function diff_sequences($ofs, $old_schema, $new_schema)
{
// Drop sequences that do not exist in new schema
if ($old_schema != NULL) {
foreach (dbx::get_sequences($old_schema) as $sequence) {
if (!mssql10_schema::contains_sequence($new_schema, $sequence['name'])) {
$ofs->write(mssql10_bit_table::get_drop_sql($old_schema, $sequence) . "\n");
}
}
}
// Add new sequences
foreach (dbx::get_sequences($new_schema) as $sequence) {
if ($old_schema == NULL || !mssql10_schema::contains_sequence($old_schema, $sequence['name'])) {
$ofs->write(mssql10_bit_table::get_creation_sql($new_schema, $sequence) . "\n");
}
}
// Alter modified sequences
self::add_modified_sequences($ofs, $old_schema, $new_schema);
}
public static function get_multiple_create_bits($node_schema, $node_table, $constraints)
{
$bits = array();
foreach ($constraints as $constraint) {
$bits[] = mysql5_constraint::get_constraint_sql($constraint, FALSE);
if (strcasecmp($constraint['type'], 'PRIMARY KEY') == 0) {
// we're adding the PK constraint, so we need to add AUTO_INCREMENT on any affected columns immediately after!
$columns = mysql5_table::primary_key_columns($node_table);
foreach ($columns as $col) {
$node_column = dbx::get_table_column($node_table, $col);
if (mysql5_column::is_auto_increment($node_column['type'])) {
$bits[] = "MODIFY " . mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $node_column, FALSE, TRUE, TRUE);
break;
// there can only be one AI column per table
}
}
}
}
return $bits;
}
/**
* Creates declaration string for the function. The string consists
* of function name, '(', list of argument types separated by ',' and ')'.
*
* @param $node_function
*/
public static function get_declaration($node_schema, $node_function)
{
$r = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_function_name($node_function['name']) . '(';
$parameters = dbx::get_function_parameters($node_function);
foreach ($parameters as $parameter) {
$arg = '';
if (strlen($parameter['name']) > 0) {
$arg .= '@' . $parameter['name'] . ' ';
}
$arg .= $parameter['type'];
if (isset($parameter['direction'])) {
$arg .= ' ' . $parameter['direction'];
}
$r .= $arg . ', ';
}
if (count($parameters) > 0) {
$r = substr($r, 0, -2);
}
$r .= ')';
return $r;
}
/**
* 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 (!mssql10_schema::contains_function($new_schema, mssql10_function::get_declaration($new_schema, $old_function))) {
$ofs3->write(mssql10_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'], mssql10_function::get_declaration($new_schema, $new_function));
}
if ($old_function == NULL) {
$ofs1->write(mssql10_function::get_creation_sql($new_schema, $new_function) . "\n");
} else {
if (!mssql10_function::equals($new_schema, $new_function, $old_function, mssql10_diff::$ignore_function_whitespace)) {
// functions are not equal, old_function is not null, it previously existed
// for MSSQL, there is no CREATE OR REPLACE FUNCTION, so drop the function explicitly
$ofs1->write(mssql10_function::get_drop_sql($old_schema, $old_function) . "\n");
$ofs1->write(mssql10_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(mssql10_function::get_creation_sql($new_schema, $new_function) . "\n");
} else {
if (mssql10_schema::contains_type($new_schema, $new_function['returns']) && mssql10_schema::contains_type($old_schema, $new_function['returns']) && !mssql10_type::equals(dbx::get_type($old_schema, $new_function['returns']), dbx::get_type($new_schema, $new_function['returns']))) {
$ofs1->write("-- dbstward insisting on function re-creation " . $new_function['name'] . " for type " . $new_function['returns'] . " definition change\n");
$ofs1->write(mssql10_function::get_drop_sql($old_schema, $old_function) . "\n");
$ofs1->write(mssql10_function::get_creation_sql($new_schema, $new_function) . "\n");
}
}
}
}
}
}
/**
* Parses CREATE INDEX command.
*
* @param database database
* @param command CREATE INDEX command
*/
public static function parse($database, $command)
{
if (preg_match(self::CREATE_PATTERN, trim($command), $matches) > 0) {
$unique_value = strlen(trim($matches[1])) > 0 ? 'true' : 'false';
$index_name = $matches[2];
$table_name = $matches[3];
$using = trim($matches[4]);
if ($index_name == null || $table_name == null || $using == null) {
throw new exception("Cannot parse command: " . $command);
}
$node_schema =& dbx::get_schema($database, sql_parser::get_schema_name(trim($table_name), $database));
$node_table =& dbx::get_table($node_schema, sql_parser::get_object_name(trim($table_name)));
if ($node_table == null) {
throw new exception("Failed to find table: " . $table_name);
}
$node_index =& dbx::create_table_index($node_table, $index_name);
dbx::set_attribute($node_index, 'using', $using);
dbx::set_attribute($node_index, 'unique', $unique_value);
} else {
throw new exception("Cannot parse command: " . $command);
}
}