|
public static Combine ( Spacing spacing ) : |
||
| spacing | Spacing | |
| return | ||
private static SqlPreCommand AlterTableAddColumnDefault(ITable table, IColumn column, Replacements rep)
{
bool temporalDefault = !column.Nullable && !column.Identity && column.Default == null;
if (!temporalDefault)
{
return(SqlBuilder.AlterTableAddColumn(table, column));
}
try
{
var defaultValue = GetDefaultValue(table, column, rep);
if (defaultValue == "force")
{
return(SqlBuilder.AlterTableAddColumn(table, column));
}
column.Default = defaultValue;
return(SqlPreCommand.Combine(Spacing.Simple,
SqlBuilder.AlterTableAddColumn(table, column),
SqlBuilder.DropDefaultConstraint(table.Name, column.Name)));
}
finally
{
column.Default = null;
}
}
public static SqlPreCommand?CreateTablesScript()
{
var sqlBuilder = Connector.Current.SqlBuilder;
Schema s = Schema.Current;
List <ITable> tables = s.GetDatabaseTables().Where(t => !s.IsExternalDatabase(t.Name.Schema.Database)).ToList();
SqlPreCommand?createTables = tables.Select(t => sqlBuilder.CreateTableSql(t)).Combine(Spacing.Double)?.PlainSqlCommand();
SqlPreCommand?foreignKeys = tables.Select(sqlBuilder.AlterTableForeignKeys).Combine(Spacing.Double)?.PlainSqlCommand();
SqlPreCommand?indices = tables.Select(t =>
{
var allIndexes = t.GeneratAllIndexes().Where(a => !(a is PrimaryKeyIndex));;
var mainIndices = allIndexes.Select(ix => sqlBuilder.CreateIndex(ix, checkUnique: null)).Combine(Spacing.Simple);
var historyIndices = t.SystemVersioned == null ? null :
allIndexes.Where(a => a.GetType() == typeof(TableIndex)).Select(mix => sqlBuilder.CreateIndexBasic(mix, forHistoryTable: true)).Combine(Spacing.Simple);
return(SqlPreCommand.Combine(Spacing.Double, mainIndices, historyIndices));
}).NotNull().Combine(Spacing.Double)?.PlainSqlCommand();
return(SqlPreCommand.Combine(Spacing.Triple, createTables, foreignKeys, indices));
}
public static SqlPreCommand SnapshotIsolation(Replacements replacements)
{
if (replacements.SchemaOnly)
{
return(null);
}
var list = Schema.Current.DatabaseNames().Select(a => a?.ToString()).ToList();
if (list.Contains(null))
{
list.Remove(null);
list.Add(Connector.Current.DatabaseName());
}
var results = Database.View <SysDatabases>()
.Where(d => list.Contains(d.name))
.Select(d => new { d.name, d.snapshot_isolation_state, d.is_read_committed_snapshot_on }).ToList();
var cmd = replacements.WithReplacedDatabaseName().Using(_ => results.Select((a, i) =>
SqlPreCommand.Combine(Spacing.Simple,
!a.snapshot_isolation_state || !a.is_read_committed_snapshot_on ? DisconnectUsers(a.name, "SPID" + i) : null,
!a.snapshot_isolation_state ? SqlBuilder.SetSnapshotIsolation(a.name, true) : null,
!a.is_read_committed_snapshot_on ? SqlBuilder.MakeSnapshotIsolationDefault(a.name, true) : null)).Combine(Spacing.Double));
if (cmd == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Double,
new SqlPreCommandSimple("use master -- Start Snapshot"),
cmd,
new SqlPreCommandSimple("use {0} -- Stop Snapshot".FormatWith(Connector.Current.DatabaseName()))));
}
public static SqlPreCommand SnapshotIsolation()
{
if (!Connector.Current.AllowsSetSnapshotIsolation)
{
return(null);
}
var list = Schema.Current.DatabaseNames().Select(a => a?.ToString()).ToList();
if (list.Contains(null))
{
list.Remove(null);
list.Add(Connector.Current.DatabaseName());
}
var cmd = list.Select(a =>
SqlPreCommand.Combine(Spacing.Simple,
//DisconnectUsers(a.name, "SPID" + i) : null,
SqlBuilder.SetSingleUser(a),
SqlBuilder.SetSnapshotIsolation(a, true),
SqlBuilder.MakeSnapshotIsolationDefault(a, true),
SqlBuilder.SetMultiUser(a))
).Combine(Spacing.Double);
return(cmd);
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (index is PrimaryClusteredIndex)
{
return(new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith(
index.Table.Name,
index.IndexName,
columns)));
}
if (index is UniqueIndex uIndex && uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql));
}
public static SqlPreCommand?SnapshotIsolation()
{
if (!Connector.Current.AllowsSetSnapshotIsolation)
{
return(null);
}
var list = Schema.Current.DatabaseNames().Select(a => a?.ToString()).ToList();
if (list.Contains(null))
{
list.Remove(null);
list.Add(Connector.Current.DatabaseName());
}
var cmd = list.NotNull()
.Where(db => !SnapshotIsolationEnabled(db))
.Select(db => SqlPreCommand.Combine(Spacing.Simple,
SqlBuilder.SetSingleUser(db),
SqlBuilder.SetSnapshotIsolation(db, true),
SqlBuilder.MakeSnapshotIsolationDefault(db, true),
SqlBuilder.SetMultiUser(db))
).Combine(Spacing.Double);
return(cmd);
}
public static SqlPreCommand?SnapshotIsolation()
{
var connector = Connector.Current;
if (!connector.AllowsSetSnapshotIsolation)
{
return(null);
}
var list = connector.Schema.DatabaseNames().Select(a => a?.ToString()).ToList();
if (list.Contains(null))
{
list.Remove(null);
list.Add(connector.DatabaseName());
}
var sqlBuilder = connector.SqlBuilder;
var cmd = list.NotNull()
.Select(dbn => new DatabaseName(null, dbn, connector.Schema.Settings.IsPostgres))
.Where(db => !SnapshotIsolationEnabled(db))
.Select(db => SqlPreCommand.Combine(Spacing.Simple,
sqlBuilder.SetSingleUser(db),
sqlBuilder.SetSnapshotIsolation(db, true),
sqlBuilder.MakeSnapshotIsolationDefault(db, true),
sqlBuilder.SetMultiUser(db))
).Combine(Spacing.Double);
return(cmd);
}
public static SqlPreCommand RemoveAllScript(DatabaseName databaseName)
{
var schemas = SqlBuilder.SystemSchemas.ToString(a => "'" + a + "'", ", ");
return(SqlPreCommand.Combine(Spacing.Double,
new SqlPreCommandSimple(Use(databaseName, RemoveAllProceduresScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllViewsScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllConstraintsScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllTablesScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllSchemasScript.FormatWith(schemas)))
));
}
public static SqlPreCommand RenameOrMove(DiffTable oldTable, ITable newTable)
{
if (object.Equals(oldTable.Name.Schema.Database, newTable.Name.Schema.Database))
{
return(RenameOrChangeSchema(oldTable.Name, newTable.Name));
}
return(SqlPreCommand.Combine(Spacing.Simple,
CreateTableSql(newTable),
MoveRows(oldTable.Name, newTable.Name, newTable.Columns.Keys),
DropTable(oldTable)) !);
}
public static SqlPreCommand CreateTablesScript()
{
List <ITable> tables = Schema.Current.GetDatabaseTables().ToList();
SqlPreCommand createTables = tables.Select(SqlBuilder.CreateTableSql).Combine(Spacing.Double).PlainSqlCommand();
SqlPreCommand foreignKeys = tables.Select(SqlBuilder.AlterTableForeignKeys).Combine(Spacing.Double).PlainSqlCommand();
SqlPreCommand indices = tables.Select(SqlBuilder.CreateAllIndices).NotNull().Combine(Spacing.Double).PlainSqlCommand();
return(SqlPreCommand.Combine(Spacing.Triple, createTables, foreignKeys, indices));
}
public static SqlPreCommand RemoveAllScript(DatabaseName databaseName)
{
var schemas = SqlBuilder.SystemSchemas.ToString(a => "'" + a + "'", ", ");
return(SqlPreCommand.Combine(Spacing.Double,
new SqlPreCommandSimple(Use(databaseName, RemoveAllProceduresScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllViewsScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllConstraintsScript)),
Connector.Current.SupportsTemporalTables ? new SqlPreCommandSimple(Use(databaseName, StopSystemVersioning)) : null,
new SqlPreCommandSimple(Use(databaseName, RemoveAllTablesScript)),
new SqlPreCommandSimple(Use(databaseName, RemoveAllSchemasScript.FormatWith(schemas)))
));
}
public static SqlPreCommand?RemoveDuplicatedIndices()
{
var isPostgres = Schema.Current.Settings.IsPostgres;
var sqlBuilder = Connector.Current.SqlBuilder;
var plainData = (from s in Database.View <SysSchemas>()
from t in s.Tables()
from ix in t.Indices()
from ic in ix.IndexColumns()
from c in t.Columns()
where ic.column_id == c.column_id
select new
{
table = new ObjectName(new SchemaName(null, s.name, isPostgres), t.name, isPostgres),
index = ix.name,
ix.is_unique,
column = c.name,
ic.is_descending_key,
ic.is_included_column,
ic.index_column_id
}).ToList();
var tables = plainData.AgGroupToDictionary(a => a.table,
gr => gr.AgGroupToDictionary(a => new { a.index, a.is_unique },
gr2 => gr2.OrderBy(a => a.index_column_id)
.Select(a => a.column + (a.is_included_column ? "(K)" : "(I)") + (a.is_descending_key ? "(D)" : "(A)"))
.ToString("|")));
var result = tables.SelectMany(t =>
t.Value.GroupBy(a => a.Value, a => a.Key)
.Where(gr => gr.Count() > 1)
.Select(gr =>
{
var best = gr.OrderByDescending(a => a.is_unique).ThenByDescending(a => a.index !/*CSBUG*/.StartsWith("IX")).ThenByDescending(a => a.index).First();
return(gr.Where(g => g != best)
.Select(g => sqlBuilder.DropIndex(t.Key !, g.index !))
.PreAnd(new SqlPreCommandSimple("-- DUPLICATIONS OF {0}".FormatWith(best.index))).Combine(Spacing.Simple));
})
).Combine(Spacing.Double);
if (result == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Double,
new SqlPreCommandSimple("use {0}".FormatWith(Connector.Current.DatabaseName())),
result));
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (!(index is UniqueIndex))
{
return(new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith(
index.IndexName,
index.Table.Name,
columns)));
}
else
{
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return(new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns)));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql));
}
else
{
return(new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where)));
}
}
}
public static SqlPreCommand TotalSynchronizeScript(bool interactive = true, bool schemaOnly = false)
{
var command = Schema.Current.SynchronizationScript(interactive, schemaOnly);
if (command == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Double,
new SqlPreCommandSimple(SynchronizerMessage.StartOfSyncScriptGeneratedOn0.NiceToString().FormatWith(DateTime.Now)),
new SqlPreCommandSimple("use {0}".FormatWith(Connector.Current.DatabaseName())),
command,
new SqlPreCommandSimple(SynchronizerMessage.EndOfSyncScript.NiceToString())));
}
public static SqlPreCommand MoveRows(ObjectName oldTable, ObjectName newTable, IEnumerable <string> columnNames)
{
SqlPreCommandSimple command = new SqlPreCommandSimple(
@"INSERT INTO {0} ({2})
SELECT {3}
FROM {1} as [table]".FormatWith(
newTable,
oldTable,
columnNames.ToString(a => a.SqlEscape(), ", "),
columnNames.ToString(a => "[table]." + a.SqlEscape(), ", ")));
return(SqlPreCommand.Combine(Spacing.Simple,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} ON".FormatWith(newTable)),
command,
new SqlPreCommandSimple("SET IDENTITY_INSERT {0} OFF".FormatWith(newTable))));
}
public static SqlPreCommand AlterTableAlterColumn(ITable table, IColumn column, string?defaultConstraintName = null, ObjectName?forceTableName = null)
{
var alterColumn = new SqlPreCommandSimple("ALTER TABLE {0} ALTER COLUMN {1}".FormatWith(forceTableName ?? table.Name, CreateColumn(column, null, isChange: true)));
if (column.Default == null)
{
return(alterColumn);
}
var defCons = GetDefaultConstaint(table, column) !;
return(SqlPreCommand.Combine(Spacing.Simple,
AlterTableDropConstraint(table.Name, defaultConstraintName ?? defCons.Name),
alterColumn,
AlterTableAddDefaultConstraint(table.Name, defCons)
) !);
}
public static SqlPreCommand RenameOrChangeSchema(ObjectName oldTableName, ObjectName newTableName)
{
if (!object.Equals(oldTableName.Schema.Database, newTableName.Schema.Database))
{
throw new InvalidOperationException("Different database");
}
if (object.Equals(oldTableName.Schema, newTableName.Schema))
{
return(RenameTable(oldTableName, newTableName.Name));
}
var oldNewSchema = oldTableName.OnSchema(newTableName.Schema);
return(SqlPreCommand.Combine(Spacing.Simple,
AlterSchema(oldTableName, newTableName.Schema),
oldNewSchema.Equals(newTableName) ? null : RenameTable(oldNewSchema, newTableName.Name)) !);
}
private static SqlPreCommand SyncEnums(Schema schema, Table table, Dictionary <string, Entity> current, Dictionary <string, Entity> should)
{
var deletes = Synchronizer.SynchronizeScript(
should,
current,
null,
(str, c) => table.DeleteSqlSync(c, comment: c.toStr),
null, Spacing.Double);
var moves = Synchronizer.SynchronizeScript(
should,
current,
null,
null,
(str, s, c) =>
{
if (s.id == c.id)
{
return(table.UpdateSqlSync(c, comment: c.toStr));
}
var insert = table.InsertSqlSync(s);
var move = (from t in schema.GetDatabaseTables()
from col in t.Columns.Values
where col.ReferenceTable == table
select new SqlPreCommandSimple("UPDATE {0} SET {1} = {2} WHERE {1} = {3} -- {4} re-indexed"
.FormatWith(t.Name, col.Name, s.Id, c.Id, c.toStr)))
.Combine(Spacing.Simple);
var delete = table.DeleteSqlSync(c, comment: c.toStr);
return(SqlPreCommand.Combine(Spacing.Simple, insert, move, delete));
}, Spacing.Double);
var creates = Synchronizer.SynchronizeScript(
should,
current,
(str, s) => table.InsertSqlSync(s),
null,
null, Spacing.Double);
return(SqlPreCommand.Combine(Spacing.Double, deletes, moves, creates));
}
private static SqlPreCommand AlterTableAddColumnDefault(ITable table, IColumn column, Replacements rep)
{
bool temporalDefault = !column.Nullable && !column.Identity && column.Default == null;
if (!temporalDefault)
{
return(SqlBuilder.AlterTableAddColumn(table, column));
}
string defaultValue = rep.Interactive ? SafeConsole.AskString("Default value for '{0}.{1}'? (or press enter) ".FormatWith(table.Name.Name, column.Name), stringValidator: str => null) : "";
if (defaultValue == "null")
{
return(SqlBuilder.AlterTableAddColumn(table, column));
}
try
{
column.Default = defaultValue;
if (column.Default.HasText() && SqlBuilder.IsString(column.SqlDbType) && !column.Default.Contains("'"))
{
column.Default = "'" + column.Default + "'";
}
if (string.IsNullOrEmpty(column.Default))
{
column.Default = SqlBuilder.IsNumber(column.SqlDbType) ? "0" :
SqlBuilder.IsString(column.SqlDbType) ? "''" :
SqlBuilder.IsDate(column.SqlDbType) ? "GetDate()" :
"?";
}
return(SqlPreCommand.Combine(Spacing.Simple,
SqlBuilder.AlterTableAddColumn(table, column),
SqlBuilder.DropDefaultConstraint(table.Name, column.Name)));
}
finally
{
column.Default = null;
}
}
public static SqlPreCommand CreateIndex(Index index, Replacements?checkUnique)
{
if (index is PrimaryClusteredIndex)
{
var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
return(new SqlPreCommandSimple($"ALTER TABLE {index.Table.Name} ADD CONSTRAINT {index.IndexName} PRIMARY KEY CLUSTERED({columns})"));
}
if (index is UniqueIndex uIndex)
{
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
var columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
SqlPreCommandSimple viewSql = new SqlPreCommandSimple($"CREATE VIEW {viewName} WITH SCHEMABINDING AS SELECT {columns} FROM {uIndex.Table.Name.ToString()} WHERE {uIndex.Where}")
{
GoBefore = true, GoAfter = true
};
SqlPreCommandSimple indexSql = new SqlPreCommandSimple($"CREATE UNIQUE CLUSTERED INDEX {uIndex.IndexName} ON {viewName}({columns})");
return(SqlPreCommand.Combine(Spacing.Simple,
checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null,
viewSql,
indexSql) !);
}
else
{
return(SqlPreCommand.Combine(Spacing.Double,
checkUnique != null ? RemoveDuplicatesIfNecessary(uIndex, checkUnique) : null,
CreateIndexBasic(index, false)) !);
}
}
else
{
return(CreateIndexBasic(index, forHistoryTable: false));
}
}
public static SqlPreCommand RenameOrMove(DiffTable oldTable, ITable newTable)
{
if (object.Equals(oldTable.Name.Schema, newTable.Name.Schema))
{
return(RenameTable(oldTable.Name, newTable.Name.Name));
}
if (object.Equals(oldTable.Name.Schema.Database, newTable.Name.Schema.Database))
{
var oldNewSchema = oldTable.Name.OnSchema(newTable.Name.Schema);
return(SqlPreCommand.Combine(Spacing.Simple,
AlterSchema(oldTable.Name, newTable.Name.Schema),
oldNewSchema.Equals(newTable.Name) ? null : RenameTable(oldNewSchema, newTable.Name.Name)));
}
return(SqlPreCommand.Combine(Spacing.Simple,
CreateTableSql(newTable),
MoveRows(oldTable.Name, newTable.Name, newTable.Columns.Keys),
DropTable(oldTable.Name)));
}
internal static SqlPreCommand CopyData(ITable newTable, DiffTable oldTable, Replacements rep)
{
var selectColumns = newTable.Columns
.Select(col => oldTable.Columns.TryGetC(col.Key)?.Name ?? GetDefaultValue(newTable, col.Value, rep))
.ToString(", ");
var insertSelect = new SqlPreCommandSimple(
$@"INSERT INTO {newTable.Name} ({newTable.Columns.Values.ToString(a => a.Name, ", ")})
SELECT {selectColumns}
FROM {oldTable.Name}");
if (!newTable.PrimaryKey.Identity)
{
return(insertSelect);
}
return(SqlPreCommand.Combine(Spacing.Simple,
SqlBuilder.SetIdentityInsert(newTable.Name, true),
insertSelect,
SqlBuilder.SetIdentityInsert(newTable.Name, false)
));
}
public static SqlPreCommand Combine(this IEnumerable <SqlPreCommand> preCommands, Spacing spacing)
{
return(SqlPreCommand.Combine(spacing, preCommands.ToArray()));
}
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements)
{
Dictionary <string, ITable> model = Schema.Current.GetDatabaseTables().ToDictionaryEx(a => a.Name.ToString(), "schema tables");
HashSet <SchemaName> modelSchemas = Schema.Current.GetDatabaseTables().Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet();
Dictionary <string, DiffTable> database = DefaultGetDatabaseDescription(Schema.Current.DatabaseNames());
HashSet <SchemaName> databaseSchemas = DefaultGetSchemas(Schema.Current.DatabaseNames());
if (SimplifyDiffTables != null)
{
SimplifyDiffTables(database);
}
replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables);
database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables);
Dictionary <ITable, Dictionary <string, Index> > modelIndices = model.Values
.ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name)));
model.JoinDictionaryForeach(database, (tn, tab, diff) =>
{
var key = Replacements.KeyColumnsForTable(tn);
replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key);
diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key);
diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]);
});
Func <ObjectName, ObjectName> ChangeName = (ObjectName objectName) =>
{
string name = replacements.Apply(Replacements.KeyTables, objectName.ToString());
return(model.TryGetC(name)?.Name ?? objectName);
};
Func <ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName =>
{
var dropFks = (from t in database.Values
from c in t.Columns.Values
where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName)
select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple);
if (dropFks == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks));
};
using (replacements.WithReplacedDatabaseName())
{
SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
(_, newSN) => SqlBuilder.CreateSchema(newSN),
null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN),
Spacing.Double);
//use database without replacements to just remove indexes
SqlPreCommand dropStatistics =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats),
(tn, tab, dif) =>
{
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
return(SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList()));
},
Spacing.Double);
SqlPreCommand dropIndices =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple),
(tn, tab, dif) =>
{
Dictionary <string, Index> modelIxs = modelIndices[tab];
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
null,
(i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null,
Spacing.Simple);
return(changes);
},
Spacing.Double);
SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript(
model,
database,
null,
(tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null)
.Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple),
(tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
null,
(cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null,
(cn, colModel, colDb) => colDb.ForeignKey == null ? null :
colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ?
SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) :
null, Spacing.Simple),
dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)),
Spacing.Double);
SqlPreCommand tables =
Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.CreateTableSql(tab),
(tn, dif) => SqlBuilder.DropTable(dif.Name),
(tn, tab, dif) =>
SqlPreCommand.Combine(Spacing.Simple,
!object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, tabCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
AlterTableAddColumnDefault(tab, tabCol, replacements)),
(cn, difCol) => SqlPreCommandSimple.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null,
SqlBuilder.AlterTableDropColumn(tab, cn)),
(cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple,
difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name),
difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple,
tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
SqlBuilder.AlterTableAlterColumn(tab, tabCol),
tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null),
difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null,
tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default) : null),
UpdateByFkChange(tn, difCol, tabCol, ChangeName)),
Spacing.Simple)),
Spacing.Double);
if (tables != null)
{
tables.GoAfter = true;
}
var tableReplacements = replacements.TryGetC(Replacements.KeyTables);
if (tableReplacements != null)
{
replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse();
}
SqlPreCommand syncEnums;
try
{
syncEnums = SynchronizeEnumsScript(replacements);
}
catch (Exception e)
{
syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message);
}
SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.AlterTableForeignKeys(tab),
null,
(tn, tab, dif) => Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null :
SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable),
null,
(cn, colModel, coldb) =>
{
if (colModel.ReferenceTable == null || colModel.AvoidForeignKey)
{
return(null);
}
if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable)))
{
return(SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable));
}
var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name);
return(SqlPreCommand.Combine(Spacing.Simple,
name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null,
(coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null));
},
Spacing.Simple),
Spacing.Double);
bool?createMissingFreeIndexes = null;
SqlPreCommand addIndices =
Synchronizer.SynchronizeScript(model, database,
(tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple),
null,
(tn, tab, dif) =>
{
var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn));
Func <IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name);
Dictionary <string, Index> modelIxs = modelIndices[tab];
var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
(i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null,
null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) :
mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null,
Spacing.Simple);
return(SqlPreCommand.Combine(Spacing.Simple, controlledIndexes));
}, Spacing.Double);
SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
null,
(_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN),
Spacing.Double);
return(SqlPreCommand.Combine(Spacing.Triple, createSchemas, dropStatistics, dropIndices, dropForeignKeys, tables, syncEnums, addForeingKeys, addIndices, dropSchemas));
}
}
public static SqlPreCommand SynchronizeTablesScript(Replacements replacements)
{
Schema s = Schema.Current;
Dictionary <string, ITable> model = s.GetDatabaseTables().Where(t => !s.IsExternalDatabase(t.Name.Schema.Database)).ToDictionaryEx(a => a.Name.ToString(), "schema tables");
HashSet <SchemaName> modelSchemas = model.Values.Select(a => a.Name.Schema).Where(a => !SqlBuilder.SystemSchemas.Contains(a.Name)).ToHashSet();
Dictionary <string, DiffTable> database = DefaultGetDatabaseDescription(s.DatabaseNames());
HashSet <SchemaName> databaseSchemas = DefaultGetSchemas(s.DatabaseNames());
SimplifyDiffTables?.Invoke(database);
replacements.AskForReplacements(database.Keys.ToHashSet(), model.Keys.ToHashSet(), Replacements.KeyTables);
database = replacements.ApplyReplacementsToOld(database, Replacements.KeyTables);
Dictionary <ITable, Dictionary <string, Index> > modelIndices = model.Values
.ToDictionary(t => t, t => t.GeneratAllIndexes().ToDictionaryEx(a => a.IndexName, "Indexes for {0}".FormatWith(t.Name)));
//To --> From
Dictionary <ObjectName, ObjectName> copyDataFrom = new Dictionary <ObjectName, ObjectName>();
//A -> A_temp
Dictionary <ObjectName, ObjectName> preRenames = new Dictionary <ObjectName, ObjectName>();
model.JoinDictionaryForeach(database, (tn, tab, diff) =>
{
var key = Replacements.KeyColumnsForTable(tn);
replacements.AskForReplacements(diff.Columns.Keys.ToHashSet(), tab.Columns.Keys.ToHashSet(), key);
diff.Columns = replacements.ApplyReplacementsToOld(diff.Columns, key);
diff.Indices = ApplyIndexAutoReplacements(diff, tab, modelIndices[tab]);
var diffPk = diff.Columns.TryGetC(tab.PrimaryKey.Name);
if (diffPk != null && tab.PrimaryKey.Identity != diffPk.Identity)
{
if (tab.Name.Equals(diff.Name))
{
var tempName = new ObjectName(diff.Name.Schema, diff.Name.Name + "_old");
preRenames.Add(diff.Name, tempName);
copyDataFrom.Add(tab.Name, tempName);
if (replacements.Interactive)
{
SafeConsole.WriteLineColor(ConsoleColor.Yellow, $@"Column {diffPk.Name} in {diff.Name} is now Identity={tab.PrimaryKey.Identity}.");
Console.WriteLine($@"Changing a Primary Key is not supported by SQL Server so the script will...:
1. Rename {diff.Name} table to {tempName}
2. Create a new table {diff.Name}
3. Copy data from {tempName} to {tab.Name}.
4. Drop {tempName}
");
}
}
else
{
copyDataFrom.Add(tab.Name, diff.Name);
if (replacements.Interactive)
{
SafeConsole.WriteLineColor(ConsoleColor.Yellow, $@"Column {diffPk.Name} in {diff.Name} is now Identity={tab.PrimaryKey.Identity}.");
Console.WriteLine($@"Changing a Primary Key is not supported by SQL Server so the script will...:
1. Create a new table {tab.Name}
2. Copy data from {diff.Name} to {tab.Name}.
3. Drop {diff.Name}
");
}
}
}
});
var columnsByFKTarget = database.Values.SelectMany(a => a.Columns.Values).Where(a => a.ForeignKey != null).GroupToDictionary(a => a.ForeignKey.TargetTable);
foreach (var pr in preRenames)
{
var diff = database[pr.Key.ToString()];
diff.Name = pr.Value;
foreach (var col in columnsByFKTarget.TryGetC(pr.Key).EmptyIfNull())
{
col.ForeignKey.TargetTable = pr.Value;
}
database.Add(pr.Value.ToString(), diff);
database.Remove(pr.Key.ToString());
}
Func <ObjectName, ObjectName> ChangeName = (ObjectName objectName) =>
{
string name = replacements.Apply(Replacements.KeyTables, objectName.ToString());
return(model.TryGetC(name)?.Name ?? objectName);
};
Func <ObjectName, SqlPreCommand> DeleteAllForeignKey = tableName =>
{
var dropFks = (from t in database.Values
from c in t.Columns.Values
where c.ForeignKey != null && c.ForeignKey.TargetTable.Equals(tableName)
select SqlBuilder.AlterTableDropConstraint(t.Name, c.ForeignKey.Name)).Combine(Spacing.Simple);
if (dropFks == null)
{
return(null);
}
return(SqlPreCommand.Combine(Spacing.Simple, new SqlPreCommandSimple("---In order to remove the PK of " + tableName.Name), dropFks));
};
using (replacements.WithReplacedDatabaseName())
{
SqlPreCommand preRenameTables = preRenames.Select(a => SqlBuilder.RenameTable(a.Key, a.Value.Name)).Combine(Spacing.Double);
if (preRenameTables != null)
{
preRenameTables.GoAfter = true;
}
SqlPreCommand createSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
(_, newSN) => SqlBuilder.CreateSchema(newSN),
null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.CreateSchema(newSN),
Spacing.Double);
//use database without replacements to just remove indexes
SqlPreCommand dropStatistics =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => SqlBuilder.DropStatistics(tn, dif.Stats),
(tn, tab, dif) =>
{
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
return(SqlBuilder.DropStatistics(tn, dif.Stats.Where(a => a.Columns.Any(removedColums.Contains)).ToList()));
},
Spacing.Double);
SqlPreCommand dropIndices =
Synchronizer.SynchronizeScript(model, database,
null,
(tn, dif) => dif.Indices.Values.Where(ix => !ix.IsPrimary).Select(ix => SqlBuilder.DropIndex(dif.Name, ix)).Combine(Spacing.Simple),
(tn, tab, dif) =>
{
Dictionary <string, Index> modelIxs = modelIndices[tab];
var removedColums = dif.Columns.Keys.Except(tab.Columns.Keys).ToHashSet();
var changes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
null,
(i, dix) => dix.Columns.Any(removedColums.Contains) || dix.IsControlledIndex ? SqlBuilder.DropIndex(dif.Name, dix) : null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlPreCommand.Combine(Spacing.Double, dix.IsPrimary ? DeleteAllForeignKey(dif.Name) : null, SqlBuilder.DropIndex(dif.Name, dix)) : null,
Spacing.Simple);
return(changes);
},
Spacing.Double);
SqlPreCommand dropForeignKeys = Synchronizer.SynchronizeScript(
model,
database,
null,
(tn, dif) => dif.Columns.Values.Select(c => c.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, c.ForeignKey.Name) : null)
.Concat(dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name))).Combine(Spacing.Simple),
(tn, tab, dif) => SqlPreCommand.Combine(Spacing.Simple,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
null,
(cn, colDb) => colDb.ForeignKey != null ? SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) : null,
(cn, colModel, colDb) => colDb.ForeignKey == null ? null :
colModel.ReferenceTable == null || colModel.AvoidForeignKey || !colModel.ReferenceTable.Name.Equals(ChangeName(colDb.ForeignKey.TargetTable)) ?
SqlBuilder.AlterTableDropConstraint(dif.Name, colDb.ForeignKey.Name) :
null, Spacing.Simple),
dif.MultiForeignKeys.Select(fk => SqlBuilder.AlterTableDropConstraint(dif.Name, fk.Name)).Combine(Spacing.Simple)),
Spacing.Double);
SqlPreCommand preRenamePks = preRenames.Select(a => SqlBuilder.DropPrimaryKeyConstraint(a.Value)).Combine(Spacing.Double);
SqlPreCommand tables =
Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlPreCommand.Combine(Spacing.Double,
SqlBuilder.CreateTableSql(tab),
copyDataFrom.ContainsKey(tab.Name) ? CopyData(tab, database.GetOrThrow(copyDataFrom.GetOrThrow(tab.Name).ToString()), replacements).Do(a => a.GoBefore = true) : null
),
(tn, dif) => SqlBuilder.DropTable(dif.Name),
(tn, tab, dif) =>
SqlPreCommand.Combine(Spacing.Simple,
!object.Equals(dif.Name, tab.Name) ? SqlBuilder.RenameOrMove(dif, tab) : null,
Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, tabCol) => SqlPreCommand.Combine(Spacing.Simple,
tabCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
AlterTableAddColumnDefault(tab, tabCol, replacements)),
(cn, difCol) => SqlPreCommand.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, difCol.Name) : null,
SqlBuilder.AlterTableDropColumn(tab, cn)),
(cn, tabCol, difCol) => SqlPreCommand.Combine(Spacing.Simple,
difCol.Name == tabCol.Name ? null : SqlBuilder.RenameColumn(tab, difCol.Name, tabCol.Name),
difCol.ColumnEquals(tabCol, ignorePrimaryKey: true) ? null : SqlPreCommand.Combine(Spacing.Simple,
tabCol.PrimaryKey && !difCol.PrimaryKey && dif.PrimaryKeyName != null ? SqlBuilder.DropPrimaryKeyConstraint(tab.Name) : null,
SqlBuilder.AlterTableAlterColumn(tab, tabCol),
tabCol.SqlDbType == SqlDbType.NVarChar && difCol.SqlDbType == SqlDbType.NChar ? SqlBuilder.UpdateTrim(tab, tabCol) : null),
difCol.DefaultEquals(tabCol) ? null : SqlPreCommand.Combine(Spacing.Simple,
difCol.Default != null ? SqlBuilder.DropDefaultConstraint(tab.Name, tabCol.Name) : null,
tabCol.Default != null ? SqlBuilder.AddDefaultConstraint(tab.Name, tabCol.Name, tabCol.Default, tabCol.SqlDbType) : null),
UpdateByFkChange(tn, difCol, tabCol, ChangeName, copyDataFrom)),
Spacing.Simple)),
Spacing.Double);
if (tables != null)
{
tables.GoAfter = true;
}
var tableReplacements = replacements.TryGetC(Replacements.KeyTables);
if (tableReplacements != null)
{
replacements[Replacements.KeyTablesInverse] = tableReplacements.Inverse();
}
SqlPreCommand syncEnums;
try
{
syncEnums = SynchronizeEnumsScript(replacements);
}
catch (Exception e)
{
syncEnums = new SqlPreCommandSimple("-- Exception synchronizing enums: " + e.Message);
}
SqlPreCommand addForeingKeys = Synchronizer.SynchronizeScript(
model,
database,
(tn, tab) => SqlBuilder.AlterTableForeignKeys(tab),
null,
(tn, tab, dif) => Synchronizer.SynchronizeScript(
tab.Columns,
dif.Columns,
(cn, colModel) => colModel.ReferenceTable == null || colModel.AvoidForeignKey ? null :
SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable),
null,
(cn, colModel, coldb) =>
{
if (colModel.ReferenceTable == null || colModel.AvoidForeignKey)
{
return(null);
}
if (coldb.ForeignKey == null || !colModel.ReferenceTable.Name.Equals(ChangeName(coldb.ForeignKey.TargetTable)))
{
return(SqlBuilder.AlterTableAddConstraintForeignKey(tab, colModel.Name, colModel.ReferenceTable));
}
var name = SqlBuilder.ForeignKeyName(tab.Name.Name, colModel.Name);
return(SqlPreCommand.Combine(Spacing.Simple,
name != coldb.ForeignKey.Name.Name ? SqlBuilder.RenameForeignKey(coldb.ForeignKey.Name, name) : null,
(coldb.ForeignKey.IsDisabled || coldb.ForeignKey.IsNotTrusted) && !replacements.SchemaOnly ? SqlBuilder.EnableForeignKey(tab.Name, name) : null));
},
Spacing.Simple),
Spacing.Double);
bool?createMissingFreeIndexes = null;
SqlPreCommand addIndices =
Synchronizer.SynchronizeScript(model, database,
(tn, tab) => modelIndices[tab].Values.Where(a => !(a is PrimaryClusteredIndex)).Select(SqlBuilder.CreateIndex).Combine(Spacing.Simple),
null,
(tn, tab, dif) =>
{
var columnReplacements = replacements.TryGetC(Replacements.KeyColumnsForTable(tn));
Func <IColumn, bool> isNew = c => !dif.Columns.ContainsKey(columnReplacements?.TryGetC(c.Name) ?? c.Name);
Dictionary <string, Index> modelIxs = modelIndices[tab];
var controlledIndexes = Synchronizer.SynchronizeScript(modelIxs, dif.Indices,
(i, mix) => mix is UniqueIndex || mix.Columns.Any(isNew) || SafeConsole.Ask(ref createMissingFreeIndexes, "Create missing non-unique index {0} in {1}?".FormatWith(mix.IndexName, tab.Name)) ? SqlBuilder.CreateIndex(mix) : null,
null,
(i, mix, dix) => !dix.IndexEquals(dif, mix) ? SqlBuilder.CreateIndex(mix) :
mix.IndexName != dix.IndexName ? SqlBuilder.RenameIndex(tab, dix.IndexName, mix.IndexName) : null,
Spacing.Simple);
return(SqlPreCommand.Combine(Spacing.Simple, controlledIndexes));
}, Spacing.Double);
SqlPreCommand dropSchemas = Synchronizer.SynchronizeScriptReplacing(replacements, "Schemas",
modelSchemas.ToDictionary(a => a.ToString()),
databaseSchemas.ToDictionary(a => a.ToString()),
null,
(_, oldSN) => DropSchema(oldSN) ? SqlBuilder.DropSchema(oldSN) : null,
(_, newSN, oldSN) => newSN.Equals(oldSN) ? null : SqlBuilder.DropSchema(oldSN),
Spacing.Double);
return(SqlPreCommand.Combine(Spacing.Triple, preRenameTables, createSchemas, dropStatistics, dropIndices, dropForeignKeys, preRenamePks, tables, syncEnums, addForeingKeys, addIndices, dropSchemas));
}
}
static SqlPreCommand SynchronizeEnumsScript(Replacements replacements)
{
Schema schema = Schema.Current;
List <SqlPreCommand> commands = new List <SqlPreCommand>();
foreach (var table in schema.Tables.Values)
{
Type enumType = EnumEntity.Extract(table.Type);
if (enumType != null)
{
IEnumerable <Entity> should = EnumEntity.GetEntities(enumType);
Dictionary <string, Entity> shouldByName = should.ToDictionary(a => a.ToString());
List <Entity> current = Administrator.TryRetrieveAll(table.Type, replacements);
Dictionary <string, Entity> currentByName = current.ToDictionaryEx(a => a.toStr, table.Name.Name);
string key = Replacements.KeyEnumsForTable(table.Name.Name);
replacements.AskForReplacements(currentByName.Keys.ToHashSet(), shouldByName.Keys.ToHashSet(), key);
currentByName = replacements.ApplyReplacementsToOld(currentByName, key);
var mix = shouldByName.JoinDictionary(currentByName, (n, s, c) => new { s, c }).Where(a => a.Value.s.id != a.Value.c.id).ToDictionary();
HashSet <PrimaryKey> usedIds = current.Select(a => a.Id).ToHashSet();
Dictionary <string, Entity> middleByName = mix.Where(kvp => usedIds.Contains(kvp.Value.s.Id)).ToDictionary(kvp => kvp.Key, kvp => Clone(kvp.Value.c));
if (middleByName.Any())
{
var moveToAux = SyncEnums(schema, table,
currentByName.Where(a => middleByName.ContainsKey(a.Key)).ToDictionary(),
middleByName);
if (moveToAux != null)
{
commands.Add(moveToAux);
}
}
var com = SyncEnums(schema, table,
currentByName.Where(a => !middleByName.ContainsKey(a.Key)).ToDictionary(),
shouldByName.Where(a => !middleByName.ContainsKey(a.Key)).ToDictionary());
if (com != null)
{
commands.Add(com);
}
if (middleByName.Any())
{
var backFromAux = SyncEnums(schema, table,
middleByName,
shouldByName.Where(a => middleByName.ContainsKey(a.Key)).ToDictionary());
if (backFromAux != null)
{
commands.Add(backFromAux);
}
}
}
}
return(SqlPreCommand.Combine(Spacing.Double, commands.ToArray()));
}
