/**
* 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;
}
/**
* Adds commands for creation of new columns to the list of
* commands.
*
* @param commands list of commands
* @param old_table original table
* @param new_table new table
* @param drop_defaults_columns list for storing columns for which default value should be dropped
*/
private static function add_create_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns)
{
foreach (dbx::get_table_columns($new_table) as $new_column) {
if (!mssql10_table::contains_column($old_table, $new_column['name'])) {
if (!dbsteward::$ignore_oldnames && mssql10_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) {
// oldColumnName renamed column ? rename table column of create new one
$renamed_column_schema_name = $new_schema['name'];
$renamed_column_table_name = $new_table['name'];
$old_column_name = $new_column['oldColumnName'];
$new_column_name = $new_column['name'];
$commands[] = array('stage' => 'BEFORE1', 'command' => "-- column rename from oldColumnName specification\n" . "sp_rename '" . $renamed_column_schema_name . "." . $renamed_column_table_name . "." . $old_column_name . "' , '{$new_column_name}', 'COLUMN' ;");
continue;
}
// notice $include_null_definition is false
// this is because ADD <column definition>s with NOT NULL will fail when there are existing rows
$new_column_type = mssql10_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign);
/* @DIFFTOOL - look for columns of a certain type being added
if ( preg_match('/time|date/i', $new_column_type) > 0 ) {
echo $new_schema . "." . $new_table['name'] . "." . $new_column['name'] . " of type " . $new_column_type . " " . $new_column['default'] . " found\n";
}
/**/
$commands[] = array('stage' => '1', 'command' => "\tADD " . mssql10_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mssql10_diff::$add_defaults, FALSE));
// we put the NOT NULL as an alteration in STAGE3 as data will have been updated in STAGE2
if (!mssql10_column::null_allowed($new_table, $new_column)) {
$commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . mssql10::get_quoted_column_name($new_column['name']) . " " . $new_column_type . " NOT NULL");
// also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3
if (strlen($new_column['default']) > 0) {
$commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " SET " . mssql10::get_quoted_column_name($new_column['name']) . " = DEFAULT" . " WHERE " . mssql10::get_quoted_column_name($new_column['name']) . " IS NULL;");
}
}
// 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, $new_schema, $new_table, $new_column, $drop_sql, $add_sql)) {
$commands[] = array('stage' => 'AFTER1', 'command' => $add_sql);
}
if (mssql10_diff::$add_defaults && !mssql10_column::null_allowed($new_table, $new_column)) {
$drop_defaults_columns[] = $new_column;
}
// some columns need filled with values before any new constraints can be applied
// this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute
$db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
if ($db_doc_new_schema) {
$db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']);
if ($db_doc_new_table) {
$db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']);
if ($db_doc_new_column) {
if (isset($db_doc_new_column['beforeAddStage1'])) {
$commands[] = array('stage' => 'BEFORE1', 'command' => trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition");
}
if (isset($db_doc_new_column['afterAddStage1'])) {
$commands[] = array('stage' => 'AFTER1', 'command' => trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition");
}
if (isset($db_doc_new_column['beforeAddStage3'])) {
$commands[] = array('stage' => 'BEFORE3', 'command' => trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition");
}
if (isset($db_doc_new_column['afterAddStage3'])) {
$commands[] = array('stage' => 'AFTER3', 'command' => trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition");
}
} else {
throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found");
}
} else {
throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found");
}
} else {
throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found");
}
}
}
}
