/**
* 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");
}
$table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']);
$sql = "CREATE TABLE " . $table_name . " (\n";
foreach (dbx::get_table_columns($node_table) as $column) {
$sql .= "\t" . mssql10_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $column, FALSE) . ",\n";
}
$sql = substr($sql, 0, strlen($sql) - 2);
$sql .= "\n)";
if (isset($node_table['inherits']) && strlen($node_table['inherits']) > 0) {
//@TODO: this does not look like it is supported
}
$sql .= ";";
// @IMPLEMENT: $table['description'] specifier ?
foreach (dbx::get_table_columns($node_table) as $column) {
if (isset($column['statistics'])) {
$sql .= "\nALTER TABLE ONLY " . $table_name . " ALTER COLUMN " . mssql10::get_quoted_column_name($column['name']) . " SET STATISTICS " . $column['statistics'] . ";\n";
}
// @IMPLEMENT: $column['description'] specifier ?
// if the column type is a defined enum, add a check constraint to enforce the pseudo-enum
if (mssql10_column::enum_type_check(dbsteward::$new_database, $node_schema, $node_table, $column, $drop_sql, $add_sql)) {
$sql .= $add_sql;
}
}
// @IMPLMENT table ownership with $node_table['owner'] ?
return $sql;
}
public static function get_drop_check_sql($node_schema, $node_table, $column, $node_type)
{
if (!is_object($node_type)) {
var_dump($node_type);
throw new exception("node_type is not an object");
}
if (!isset($node_type->enum)) {
throw new exception("no enums defined for type " . $node_type['name']);
}
$enum_values = mssql10_type::get_enum_values($node_type);
$table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']);
$column_name = mssql10::get_quoted_column_name($column['name']);
$constraint_name = pgsql8_index::index_name($node_table['name'], $column['name'], '_check_enum');
$enum_list = "'" . implode("','", $enum_values) . "'";
$ddl = "ALTER TABLE " . $table_name . "\n";
$ddl .= "\tDROP CONSTRAINT " . $constraint_name . ";\n";
return $ddl;
}
/**
* 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)
{
$column_type = mssql10_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign);
$definition = mssql10::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
if (strlen($node_column['default']) > 0) {
$definition .= " DEFAULT " . $node_column['default'];
} else {
if (!mssql10_column::null_allowed($node_table, $node_column) && $add_defaults) {
$default_col_value = mssql10_column::get_default_value($node_column['type']);
if ($default_col_value != NULL) {
$definition .= " DEFAULT " . $default_col_value;
}
}
}
if ($include_null_definition && !mssql10_column::null_allowed($node_table, $node_column)) {
$definition .= " NOT NULL";
}
return $definition;
}
protected static function get_data_row_update($node_schema, $node_table, $old_data_row_columns, $old_data_row, $new_data_row_columns, $new_data_row, $changed_columns)
{
if (count($changed_columns) == 0) {
throw new exception("empty changed_columns passed");
}
// what columns from new_data_row are different in old_data_row?
// those are the ones to push through the update statement to make the database current
$old_columns = '';
$update_columns = '';
foreach ($changed_columns as $changed_column) {
if (!isset($changed_column['old_col'])) {
$old_columns .= 'NOTDEFINED, ';
} else {
$old_col_value = mssql10::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['old_col']);
$old_columns .= $changed_column['name'] . ' = ' . $old_col_value . ', ';
}
$update_col_name = mssql10::get_quoted_column_name($changed_column['name']);
$update_col_value = mssql10::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['new_col']);
$update_columns .= $update_col_name . ' = ' . $update_col_value . ', ';
}
// if the computed update_columns expression is < 5 chars, complain
if (strlen($update_columns) < 5) {
var_dump($update_columns);
throw new exception(sprintf("%s.%s update_columns is < 5 chars, unexpected", $node_schema['name'], $node_table['name']));
}
// kill trailing ', '
$update_columns = substr($update_columns, 0, -2);
$old_columns = substr($old_columns, 0, -2);
// use multiline comments here, so when data has newlines they can be preserved, but upgrade scripts don't catch on fire
$sql = sprintf("UPDATE %s.%s SET %s WHERE (%s); /* old values: %s */\n", mssql10::get_quoted_schema_name($node_schema['name']), mssql10::get_quoted_table_name($node_table['name']), $update_columns, dbx::primary_key_expression(dbsteward::$new_database, $node_schema, $node_table, $new_data_row_columns, $new_data_row), $old_columns);
return $sql;
}
