예제 #1
0
        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)
                                         ));
        }
예제 #2
0
        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));
            }
        }
예제 #3
0
        public static string GetDefaultValue(ITable table, IColumn column, Replacements rep)
        {
            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 == "force")
            {
                return(defaultValue);
            }

            if (defaultValue.HasText() && SqlBuilder.IsString(column.SqlDbType) && !defaultValue.Contains("'"))
            {
                defaultValue = "'" + defaultValue + "'";
            }

            if (string.IsNullOrEmpty(defaultValue))
            {
                defaultValue = SqlBuilder.IsNumber(column.SqlDbType) ? "0" :
                               SqlBuilder.IsString(column.SqlDbType) ? "''" :
                               SqlBuilder.IsDate(column.SqlDbType) ? "GetDate()" :
                               "?";
            }

            return(defaultValue);
        }
예제 #4
0
 public static List <T> TryRetrieveAll <T>(Replacements replacements)
     where T : Entity
 {
     return(TryRetrieveAll(typeof(T), replacements).Cast <T>().ToList());
 }
예제 #5
0
        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()));
        }
예제 #6
0
        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;
            }
        }
예제 #7
0
        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));
            }
        }
예제 #8
0
        public static SqlPreCommand?RemoveDuplicatesIfNecessary(UniqueIndex uniqueIndex, Replacements rep)
        {
            try
            {
                var primaryKey = uniqueIndex.Table.Columns.Values.Where(a => a.PrimaryKey).Only();

                if (primaryKey == null)
                {
                    return(null);
                }


                int count = DuplicateCount(uniqueIndex, rep);

                if (count == 0)
                {
                    return(null);
                }

                var columns = uniqueIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ");

                if (rep.Interactive)
                {
                    if (SafeConsole.Ask($"There are {count} rows in {uniqueIndex.Table.Name} with the same {columns}. Generate DELETE duplicates script?"))
                    {
                        return(RemoveDuplicates(uniqueIndex, primaryKey, columns, commentedOut: false));
                    }

                    return(null);
                }
                else
                {
                    return(RemoveDuplicates(uniqueIndex, primaryKey, columns, commentedOut: true));
                }
            }
            catch (Exception)
            {
                return(new SqlPreCommandSimple($"-- Impossible to determine duplicates in new index {uniqueIndex.IndexName}"));
            }
        }
예제 #9
0
        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;
            }
        }
예제 #10
0
        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));
            }
        }
예제 #11
0
        public static SqlPreCommand RemoveDuplicatesIfNecessary(UniqueIndex uniqueIndex, Replacements rep)
        {
            try
            {
                var primaryKey = uniqueIndex.Table.Columns.Values.Where(a => a.PrimaryKey).Only();

                if (primaryKey == null)
                {
                    return(null);
                }


                int count = DuplicateCount(uniqueIndex, rep);

                if (count == 0)
                {
                    return(null);
                }

                var columns = uniqueIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ");


                if (SafeConsole.Ask($"There are {count} rows in {uniqueIndex.Table.Name} with the same {columns}. Generate DELETE duplicates script?"))
                {
                    return(new SqlPreCommandSimple($@"DELETE {uniqueIndex.Table.Name} 
WHERE {primaryKey.Name} NOT IN
(
    SELECT MIN({primaryKey.Name})
    FROM {uniqueIndex.Table.Name}
    GROUP BY {columns}
)"));
                }

                return(null);
            }
            catch (Exception e)
            {
                return(new SqlPreCommandSimple($"-- Impossible to determine duplicates in new index {uniqueIndex.IndexName}"));
            }
        }