/**
* 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;
}
/**
* 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;
}
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;
}
public static function escape_default_value($value)
{
// mysql accepts any value as quoted, except for CURRENT_TIMESTAMP
if (strcasecmp($value, 'CURRENT_TIMESTAMP') === 0) {
return strtoupper($value);
}
// if $value is numeric, it doesn't need to be quoted, although it can be
// if we do, though, diffing barfs because "'1'" !== "1"
if (is_numeric($value)) {
return $value;
}
return mysql5::quote_string_value($value);
}
public static function get_creation_sql($node_schema, $node_function)
{
$name = static::get_declaration($node_schema, $node_function);
$definer = strlen($node_function['owner']) > 0 ? xml_parser::role_enum(dbsteward::$new_database, $node_function['owner']) : 'CURRENT_USER';
// always drop the function first, just to be safe, and to be compatible with pgsql8's CREATE OR REPLACE
$sql = static::get_drop_sql($node_schema, $node_function) . "\n";
if (mysql5::$swap_function_delimiters) {
$sql .= 'DELIMITER ' . static::ALT_DELIMITER . "\n";
}
$function_type = static::is_procedure($node_function) ? 'PROCEDURE' : 'FUNCTION';
$sql .= "CREATE DEFINER = {$definer} {$function_type} {$name} (";
if (isset($node_function->functionParameter)) {
$params = array();
foreach ($node_function->functionParameter as $param) {
if (isset($param['direction']) && !static::is_procedure($node_function)) {
throw new exception("Parameter directions are not supported in MySQL functions");
}
if (empty($param['name'])) {
throw new exception("Function parameters must have names in MySQL. In function '{$node_function['name']}'");
}
$type = $param['type'];
if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $type)) {
$type = mysql5_type::get_enum_type_declaration($node_type);
}
$sparam = '';
if (isset($param['direction'])) {
$sparam .= (string) $param['direction'] . ' ';
}
$sparam .= mysql5::get_quoted_function_parameter($param['name']) . ' ' . $type;
$params[] = $sparam;
}
$sql .= implode(', ', $params);
}
$sql .= ")\n";
// Procedures don't have a return statement
if (!static::is_procedure($node_function)) {
$returns = $node_function['returns'];
if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $returns)) {
$returns = mysql5_type::get_enum_type_declaration($node_type);
}
$sql .= "RETURNS " . $returns . "\n";
}
$sql .= "LANGUAGE SQL\n";
list($eval_type, $determinism) = static::get_characteristics((string) $node_function['cachePolicy'], (string) $node_function['mysqlEvalType']);
$eval_type = str_replace('_', ' ', $eval_type);
$sql .= "{$eval_type}\n{$determinism}\n";
// unlike pgsql8, mysql5 defaults to SECURITY DEFINER, so we need to set it to INVOKER unless explicitly told to leave it DEFINER
if (!isset($node_function['securityDefiner']) || strcasecmp($node_function['securityDefiner'], 'false') == 0) {
$sql .= "SQL SECURITY INVOKER\n";
} else {
$sql .= "SQL SECURITY DEFINER\n";
}
if (!empty($node_function['description'])) {
$sql .= "COMMENT " . mysql5::quote_string_value($node_function['description']) . "\n";
}
$sql .= trim(static::get_definition($node_function));
$sql = rtrim($sql, ';');
if (mysql5::$swap_function_delimiters) {
$sql .= static::ALT_DELIMITER . "\nDELIMITER ;";
} else {
$sql .= ';';
}
return $sql;
}
