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