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 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; }
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 and returns SQL for creation of the table. * * @return created SQL command */ public static function get_creation_sql($node_schema, $node_table) { if ($node_schema->getName() != 'schema') { throw new exception("node_schema object element name is not schema. check stack for offending caller"); } if ($node_table->getName() != 'table') { throw new exception("node_table object element name is not table. check stack for offending caller"); } if (strlen($node_table['inherits']) > 0) { //@TODO: implement compatibility with pgsql table inheritance dbsteward::error("Skipping table '{$node_table['name']}' because MySQL does not support table inheritance"); return "-- Skipping table '{$node_table['name']}' because MySQL does not support table inheritance"; } $table_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']); $sql = "CREATE TABLE {$table_name} (\n"; $cols = array(); foreach ($node_table->column as $column) { $cols[] = mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $column, false); } $part_sql = static::get_partition_sql($node_schema, $node_table); $sql .= " " . implode(",\n ", $cols) . "\n)"; $opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($node_schema, $node_table)); if (!empty($opt_sql)) { $sql .= "\n" . $opt_sql; } if (strlen($node_table['description']) > 0) { $sql .= "\nCOMMENT " . mysql5::quote_string_value($node_table['description']); } if (!empty($part_sql)) { $sql .= "\n" . $part_sql; } $sql .= ';'; // @TODO: implement column statistics // @TODO: table ownership with $node_table['owner'] ? return $sql; }
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"); } } } } } }
private static function get_recreate_table_sql($schema, $table) { $fq_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']); $fq_tmp_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name'] . '_DBSTEWARD_MIGRATION'); // utilize MySQL's CREATE TABLE ... SELECT syntax for cleaner recreation // see: http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html $sql = "CREATE TABLE {$fq_tmp_name}"; $opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($schema, $table)); if (!empty($opt_sql)) { $sql .= "\n" . $opt_sql; } if (strlen($table['description']) > 0) { $sql .= "\nCOMMENT " . mysql5::quote_string_value($table['description']); } $sql .= "\nSELECT * FROM {$fq_name};\n"; $sql .= "DROP TABLE {$fq_name};\n"; $sql .= "RENAME TABLE {$fq_tmp_name} TO {$fq_name};"; return $sql; }
protected static function get_dimension_list($node_schema, $node_table, $node_index) { $dimensions = array(); foreach ($node_index->indexDimension as $dimension) { // mysql only supports indexed columns, not indexed expressions like in pgsql or mssql if (!mysql5_table::contains_column($node_table, $dimension)) { throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'"); } $dimensions[] = mysql5::get_quoted_column_name($dimension); } return $dimensions; }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $limit_to_tables_count = count(dbsteward::$limit_to_tables); foreach ($tables as $dep_table) { $schema = $dep_table['schema']; $table = $dep_table['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(mysql5_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $table_primary_keys = mysql5_table::primary_key_columns($table); $table_column_names = dbx::to_array($table->column, 'name'); $node_rows =& dbx::get_table_rows($table); // the <rows> element $data_column_names = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column if (count($table_primary_keys) == 1 && in_array($table_primary_keys[0], $data_column_names)) { $pk_column_name = $table_primary_keys[0]; $node_pk_column = dbx::get_table_column($table, $pk_column_name); if ($node_pk_column == NULL) { throw new exception("Failed to find primary key column '" . $pk_column_name . "' for " . $schema['name'] . "." . $table['name']); } // only set the pkey to MAX() if the primary key column is also a serial/bigserial and if serialStart is not defined if (mysql5_column::is_serial($node_pk_column['type']) && !isset($node_pk_column['serialStart'])) { $fqtn = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']); $qcol = mysql5::get_quoted_column_name($pk_column_name); $setval = mysql5_sequence::get_setval_call(mysql5_column::get_serial_sequence_name($schema, $table, $node_pk_column), "MAX({$qcol})", "TRUE"); $sql = "SELECT {$setval} FROM {$fqtn};\n"; $ofs->write($sql); } } // unlike the pg class, we cannot just set identity column start values here with setval without inserting a row // check if primary key is a column of this table - FS#17481 if (count(array_diff($table_primary_keys, $table_column_names)) != 0) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
public function testListRangePartitionSql() { $xml = <<<XML <schema name="public" owner="NOBODY"> <table name="test" primaryKey="id" owner="NOBODY"> <column name="id" type="int auto_increment"/> <column name="foo" type="int"/> <tablePartition type="LIST"> <tablePartitionOption name="column" value="id"/> <tablePartitionSegment name="p0" value="1, 2, 3"/> <tablePartitionSegment name="p1" value="4, 5, 6"/> </tablePartition> </table> </schema> XML; $schema = simplexml_load_string($xml); $table = $schema->table; $get_sql = function () use(&$schema, &$table) { return mysql5_table::get_partition_sql($schema, $table); }; $this->assertEquals("PARTITION BY LIST (`id`) (\n PARTITION `p0` VALUES IN (1, 2, 3),\n PARTITION `p1` VALUES IN (4, 5, 6)\n)", $get_sql()); $table->tablePartition['type'] = 'RANGE'; $table->tablePartition->tablePartitionSegment[0]['value'] = '4'; $table->tablePartition->tablePartitionSegment[1]['value'] = '6'; $p2 = $table->tablePartition->addChild('tablePartitionSegment'); $p2['name'] = 'p2'; $p2['value'] = 'MAXVALUE'; $this->assertEquals("PARTITION BY RANGE (`id`) (\n PARTITION `p0` VALUES LESS THAN (4),\n PARTITION `p1` VALUES LESS THAN (6),\n PARTITION `p2` VALUES LESS THAN (MAXVALUE)\n)", $get_sql()); $table->tablePartition['type'] = 'RANGE COLUMNS'; $table->tablePartition->tablePartitionOption[0]['name'] = 'columns'; $table->tablePartition->tablePartitionOption[0]['value'] = 'id,foo'; $table->tablePartition->tablePartitionSegment[0]['value'] = '4,10'; $table->tablePartition->tablePartitionSegment[1]['value'] = '6,20'; $table->tablePartition->tablePartitionSegment[2]['value'] = 'MAXVALUE,MAXVALUE'; $this->assertEquals("PARTITION BY RANGE COLUMNS (`id`, `foo`) (\n PARTITION `p0` VALUES LESS THAN (4,10),\n PARTITION `p1` VALUES LESS THAN (6,20),\n PARTITION `p2` VALUES LESS THAN (MAXVALUE,MAXVALUE)\n)", $get_sql()); }
public function testEmptyName() { $xml = <<<XML <schema name="public" owner="NOBODY"> <table name="test" primaryKey="a" owner="NOBODY"> <tableOption sqlFormat="mysql5" name="" value="5"/> <column name="a" type="int"/> </table> </schema> XML; $schema = new SimpleXMLElement($xml); try { mysql5_table::get_table_options_sql(mysql5_table::get_table_options($schema, $schema->table)); } catch (Exception $ex) { if (strcasecmp($ex->getMessage(), "tableOption of table public.test cannot have an empty name") !== 0) { throw $ex; } return; } $this->fail("Was expecting empty tableOption name exception, got nothing"); }
public function testExtractRangeColumns() { $sql = <<<SQL CREATE TABLE range_test (id int, foo int, PRIMARY KEY (id, foo)) PARTITION BY RANGE COLUMNS (id, foo) ( PARTITION p0 VALUES LESS THAN (10, 20), PARTITION p1 VALUES LESS THAN (20, 30), PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE) ); SQL; $schema = $this->extract($sql); $partition = $schema->table->tablePartition; $this->assertNotEmpty($partition); $this->assertEquals('RANGE COLUMNS', (string) $partition['type']); $opts = mysql5_table::get_partition_options($schema->table['name'], $partition); $this->assertEquals('id,foo', $opts['columns']); $this->assertEquals(3, count($partition->tablePartitionSegment)); $this->assertEquals('p0', (string) $partition->tablePartitionSegment[0]['name']); $this->assertEquals('10,20', (string) $partition->tablePartitionSegment[0]['value']); $this->assertEquals('p1', (string) $partition->tablePartitionSegment[1]['name']); $this->assertEquals('20,30', (string) $partition->tablePartitionSegment[1]['value']); $this->assertEquals('p2', (string) $partition->tablePartitionSegment[2]['name']); $this->assertEquals('MAXVALUE,MAXVALUE', (string) $partition->tablePartitionSegment[2]['value']); }