/**
* in MSSQL indexes must contain column references, value expressions are not allowed
*
*/
public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql)
{
$dimension_list = '';
$add_column_sql = '';
// in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql
$i = 0;
foreach ($node_index->indexDimension as $dimension) {
$i++;
$dimension_name = (string) $dimension;
if (mssql10_table::contains_column($node_table, $dimension_name)) {
// dimension is an explicit column
// check unique index indexDimensions for nulled columns
// mssql index constraint engine will not ignore null values for nullable columns
if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
$node_column = dbx::get_table_column($node_table, $dimension_name);
if (mssql10_column::null_allowed($node_table, $node_column)) {
dbsteward::error("dimension_name = " . $dimension_name);
//var_dump($node_column);
throw new exception("nulled column index found");
}
}
} else {
// not an explicit column, so create one
$dimension_name = $node_index['name'] . '_' . $i;
$add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . " ADD " . $dimension_name . " AS " . (string) $dimension . ";\n";
}
$dimension_list .= $dimension_name . ', ';
}
$dimension_list = substr($dimension_list, 0, -2);
return $dimension_list;
}
public function build_schema($db_doc, $ofs, $table_depends)
{
// explicitly create the ROLE_APPLICATION
// webservers connect as a user granted this role
$ofs->write("CREATE ROLE " . $db_doc->database->role->application . ";\n");
// schema creation
foreach ($db_doc->schema as $schema) {
$ofs->write(mssql10_schema::get_creation_sql($schema));
// schema grants
if (isset($schema->grant)) {
foreach ($schema->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $schema, $grant) . "\n");
}
}
}
// types: enumerated list, etc
foreach ($db_doc->schema as $schema) {
foreach ($schema->type as $type) {
$ofs->write(mssql10_type::get_creation_sql($schema, $type) . "\n");
}
}
// function definitions
foreach ($db_doc->schema as $schema) {
foreach ($schema->function as $function) {
if (mssql10_function::has_definition($function)) {
$ofs->write(mssql10_function::get_creation_sql($schema, $function));
}
}
}
$ofs->write("\n");
// table structure creation
foreach ($db_doc->schema as $schema) {
// create defined tables
foreach ($schema->table as $table) {
// table definition
$ofs->write(mssql10_table::get_creation_sql($schema, $table) . "\n");
// table indexes
mssql10_diff_indexes::diff_indexes_table($ofs, NULL, NULL, $schema, $table);
// table grants
if (isset($table->grant)) {
foreach ($table->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $table, $grant) . "\n");
}
}
$ofs->write("\n");
}
// sequences contained in the schema
if (isset($schema->sequence)) {
foreach ($schema->sequence as $sequence) {
$ofs->write(mssql10_bit_table::get_creation_sql($schema, $sequence) . "\n");
// sequence permission grants
if (isset($sequence->grant)) {
foreach ($sequence->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $sequence, $grant) . "\n");
}
}
}
}
}
$ofs->write("\n");
// define table primary keys before foreign keys so unique requirements are always met for FOREIGN KEY constraints
foreach ($db_doc->schema as $schema) {
foreach ($schema->table as $table) {
mssql10_diff_tables::diff_constraints_table($ofs, NULL, NULL, $schema, $table, 'primaryKey', FALSE);
}
}
$ofs->write("\n");
// foreign key references
// use the dependency order to specify foreign keys in an order that will satisfy nested foreign keys and etc
for ($i = 0; $i < count($table_depends); $i++) {
$schema = $table_depends[$i]['schema'];
$table = $table_depends[$i]['table'];
if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
mssql10_diff_tables::diff_constraints_table($ofs, NULL, NULL, $schema, $table, 'constraint', FALSE);
}
$ofs->write("\n");
// trigger definitions
foreach ($db_doc->schema as $schema) {
foreach ($schema->trigger as $trigger) {
// only do triggers set to the current sql format
if (strcasecmp($trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
$ofs->write(mssql10_trigger::get_creation_sql($schema, $trigger));
}
}
}
$ofs->write("\n");
// view creation
foreach ($db_doc->schema as $schema) {
foreach ($schema->view as $view) {
$ofs->write(mssql10_view::get_creation_sql($schema, $view));
// view permission grants
if (isset($view->grant)) {
foreach ($view->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $view, $grant) . "\n");
}
}
}
}
$ofs->write("\n");
// @TODO: database configurationParameter support needed ?
}
/**
* Returns list of constraints that should be added.
*
* @param old_table original table
* @param new_table new table
* @param type whether primary keys should be processed or other constraints should be processed
*
* @return list of constraints that should be added
*/
protected static function get_new_constraints($old_schema, $old_table, $new_schema, $new_table, $type)
{
$list = array();
if ($new_table != NULL) {
if ($old_table == NULL) {
foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, $type) as $constraint) {
$list[] = $constraint;
}
} else {
foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, $type) as $constraint) {
$old_constraint = mssql10_constraint::get_table_constraint(dbsteward::$old_database, $old_schema, $old_table, $constraint['name']);
if (!mssql10_table::contains_constraint(dbsteward::$old_database, $old_schema, $old_table, $constraint['name']) || !mssql10_table::constraint_equals($old_constraint, $constraint)) {
$list[] = $constraint;
}
}
}
}
return $list;
}
