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']);
}
