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