/** * 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; }