Пример #1
0
        public static void CreateDiffScript(IRepository sourceRepository, IRepository targetRepository,IGenerator generator, bool includeTargetDrops)
        {
            List<string> sourceTables = sourceRepository.GetAllTableNames();
            List<string> targetTables = targetRepository.GetAllTableNames();

            // Script each table not in the target
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateTableCreate(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GeneratePrimaryKeys(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                List<string> tableIndexes = sourceRepository.GetIndexesFromTable(tableName).Select(i => i.IndexName).Distinct().ToList();
                foreach (var index in tableIndexes)
                {
                    generator.GenerateIndexScript(tableName, index);
                }
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateForeignKeys(tableName);
            }

            // Drop each table in the target but not the source
            if (includeTargetDrops)
            {
                foreach (string tableName in targetTables.Except(sourceTables))
                {
                    generator.GenerateTableDrop(tableName);
                }
            }

            //For each table both in target and source
            foreach (string tableName in sourceTables.Intersect(targetTables))
            {
                // Check columns for the table: Dropped, added or changed ?
                IEnumerable<Column> sourceColumns = from c in sourceRepository.GetColumnsFromTable()
                                    where c.TableName == tableName
                                    select c;
                IEnumerable<Column> targetColumns = from c in targetRepository.GetColumnsFromTable()
                                    where c.TableName == tableName
                                    select c;

                // Added columns
                foreach (var column in sourceColumns.Except(targetColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnAddScript(column);
                }
                // Same columns, check for changes
                foreach (var sourceColumn in sourceColumns.Intersect(targetColumns, new ColumnComparer()))
                {
                    bool altered = false;
                    // Check if they have any differences:
                    var targetColumn = (from c in targetColumns
                                        where c.TableName == sourceColumn.TableName && c.ColumnName == sourceColumn.ColumnName
                                        select c).Single();
                    if (sourceColumn.IsNullable != targetColumn.IsNullable)
                        altered = true;
                    if (sourceColumn.NumericPrecision != targetColumn.NumericPrecision)
                        altered = true;
                    if (sourceColumn.NumericScale != targetColumn.NumericScale)
                        altered = true;
                    if (sourceColumn.AutoIncrementBy != targetColumn.AutoIncrementBy)
                        altered = true;
                    if (sourceColumn.CharacterMaxLength != targetColumn.CharacterMaxLength)
                        altered = true;
                    if (sourceColumn.DataType != targetColumn.DataType)
                        altered = true;

                    if (altered)
                        generator.GenerateColumnAlterScript(sourceColumn);

                    // Changed defaults is special case
                    if (!targetColumn.ColumnHasDefault && sourceColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                    if (!sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnDropDefaultScript(sourceColumn);
                    }
                    // If both columns have defaults, but they are different
                    if ((sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault) && (sourceColumn.ColumnDefault != targetColumn.ColumnDefault))
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                }

                //Check primary keys
                List<PrimaryKey> sourcePK = sourceRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
                List<PrimaryKey> targetPK = targetRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();

                // Add the PK
                if (targetPK.Count == 0 && sourcePK.Count > 0)
                {
                    generator.GeneratePrimaryKeys(tableName);
                }

                // Do we have the same columns, if not, drop and create.
                if (sourcePK.Count > 0 && targetPK.Count > 0)
                {
                    if (sourcePK.Count == targetPK.Count)
                    {
                        //Compare columns
                        for (int i = 0; i < sourcePK.Count; i++)
                        {
                            if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
                            {
                                generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
                                generator.GeneratePrimaryKeys(tableName);
                                break;
                            }
                        }
                    }
                    // Not same column count, just drop and create
                    else
                    {
                        generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
                        generator.GeneratePrimaryKeys(tableName);
                    }
                }

                // Check indexes
                List<Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
                List<Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);

                // Check added indexes (by name only)
                foreach (var index in sourceIXs)
                {
                    var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
                    if (targetIX.Count() == 0)
                    {
                        generator.GenerateIndexScript(index.TableName, index.IndexName);
                    }
                }

                // Check foreign keys
                List<Constraint> sourceFKs = sourceRepository.GetAllForeignKeys(tableName);
                List<Constraint> targetFKs = targetRepository.GetAllForeignKeys(tableName);

                // Check added foreign keys (by name only)
                foreach (var fk in sourceFKs)
                {
                    Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (targetFK == null)
                    {
                        generator.GenerateForeignKey(fk);
                    }
                }
                // Check deleted FKs (by name only)
                foreach (var fk in targetFKs)
                {
                    Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (sourceFK == null)
                    {
                        generator.GenerateForeignKeyDrop(fk);
                    }
                }

                // Check deleted indexes (by name only)
                foreach (var index in targetIXs)
                {
                    var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
                    if (sourceIX.Count() == 0)
                    {
                        generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
                    }
                }

                // Dropped columns
                foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnDropScript(column);
                }

            }
        }
Пример #2
0
        public static void CreateDiffScript(IRepository sourceRepository, IRepository targetRepository, IGenerator generator, bool includeTargetDrops)
        {
            List <string> sourceTables = sourceRepository.GetAllTableNames();
            List <string> targetTables = targetRepository.GetAllTableNames();

            // Script each table not in the target
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateTableCreate(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GeneratePrimaryKeys(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                List <string> tableIndexes = sourceRepository.GetIndexesFromTable(tableName).Select(i => i.IndexName).Distinct().ToList();
                foreach (var index in tableIndexes)
                {
                    generator.GenerateIndexScript(tableName, index);
                }
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateForeignKeys(tableName);
            }

            // Drop each table in the target but not the source
            if (includeTargetDrops)
            {
                foreach (string tableName in targetTables.Except(sourceTables))
                {
                    generator.GenerateTableDrop(tableName);
                }
            }

            //For each table both in target and source
            foreach (string tableName in sourceTables.Intersect(targetTables))
            {
                // Check columns for the table: Dropped, added or changed ?
                IEnumerable <Column> sourceColumns = from c in sourceRepository.GetAllColumns()
                                                     where c.TableName == tableName
                                                     select c;
                IEnumerable <Column> targetColumns = from c in targetRepository.GetAllColumns()
                                                     where c.TableName == tableName
                                                     select c;

                // Added columns
                foreach (var column in sourceColumns.Except(targetColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnAddScript(column);
                }
                // Same columns, check for changes
                foreach (var sourceColumn in sourceColumns.Intersect(targetColumns, new ColumnComparer()))
                {
                    bool altered = false;
                    // Check if they have any differences:
                    var targetColumn = (from c in targetColumns
                                        where c.TableName == sourceColumn.TableName && c.ColumnName == sourceColumn.ColumnName
                                        select c).Single();
                    if (sourceColumn.IsNullable != targetColumn.IsNullable)
                    {
                        altered = true;
                    }
                    if (sourceColumn.NumericPrecision != targetColumn.NumericPrecision)
                    {
                        altered = true;
                    }
                    if (sourceColumn.NumericScale != targetColumn.NumericScale)
                    {
                        altered = true;
                    }
                    if (sourceColumn.AutoIncrementBy != targetColumn.AutoIncrementBy)
                    {
                        altered = true;
                    }
                    if (sourceColumn.CharacterMaxLength != targetColumn.CharacterMaxLength)
                    {
                        altered = true;
                    }
                    if (sourceColumn.DataType != targetColumn.DataType)
                    {
                        altered = true;
                    }

                    if (altered)
                    {
                        generator.GenerateColumnAlterScript(sourceColumn);
                    }

                    // Changed defaults is special case
                    if (!targetColumn.ColumnHasDefault && sourceColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                    if (!sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnDropDefaultScript(sourceColumn);
                    }
                    // If both columns have defaults, but they are different
                    if ((sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault) && (sourceColumn.ColumnDefault != targetColumn.ColumnDefault))
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                }

                //Check primary keys
                List <PrimaryKey> sourcePK = sourceRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
                List <PrimaryKey> targetPK = targetRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();

                // Add the PK
                if (targetPK.Count == 0 && sourcePK.Count > 0)
                {
                    generator.GeneratePrimaryKeys(tableName);
                }

                // Do we have the same columns, if not, drop and create.
                if (sourcePK.Count > 0 && targetPK.Count > 0)
                {
                    if (sourcePK.Count == targetPK.Count)
                    {
                        //Compare columns
                        for (int i = 0; i < sourcePK.Count; i++)
                        {
                            if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
                            {
                                generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
                                generator.GeneratePrimaryKeys(tableName);
                                break;
                            }
                        }
                    }
                    // Not same column count, just drop and create
                    else
                    {
                        generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
                        generator.GeneratePrimaryKeys(tableName);
                    }
                }

                // Check indexes
                List <Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
                List <Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);

                // Check added indexes (by name only)
                foreach (var index in sourceIXs)
                {
                    var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
                    if (targetIX.Count() == 0)
                    {
                        generator.GenerateIndexScript(index.TableName, index.IndexName);
                    }
                }

                // Check foreign keys
                List <Constraint> sourceFKs = sourceRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();
                List <Constraint> targetFKs = targetRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();

                // Check added foreign keys (by name only)
                foreach (var fk in sourceFKs)
                {
                    Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (targetFK == null)
                    {
                        generator.GenerateForeignKey(fk);
                    }
                }
                // Check deleted FKs (by name only)
                foreach (var fk in targetFKs)
                {
                    Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (sourceFK == null)
                    {
                        generator.GenerateForeignKeyDrop(fk);
                    }
                }

                // Check deleted indexes (by name only)
                foreach (var index in targetIXs)
                {
                    var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
                    if (sourceIX.Count() == 0)
                    {
                        generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
                    }
                }

                // Dropped columns
                foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnDropScript(column);
                }
            }
        }
Пример #3
0
        private static void AddIndexes(IRepository repository, List<string> dcLines, string n, bool cs)
        {

            for (int y = 0; y < dcLines.Count; y++)
            {
                string attr = "<Global.System.Data.Linq.Mapping.TableAttribute(";
                if (cs)
                    attr = "[global::System.Data.Linq.Mapping.TableAttribute(";
                if (dcLines[y].StartsWith(n + attr))
                {
                    string tableName = string.Empty;
                    // if the Name attribute is used, that is the table name, otherwise use class name
                    string[] names = dcLines[y].Split('"');
                    if (names.Count() > 1)
                        tableName = names[1];
                    string[] words = dcLines[y + 1].Split(' ');
                    if (words.Count() > 3)
                    {
                        if (string.IsNullOrEmpty(tableName))
                            tableName = words[3];
                        List<Index> indexList = repository.GetIndexesFromTable(tableName);
                        List<PrimaryKey> pkList = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == tableName).ToList();
                        //If there are indexes, add them
                        if (indexList.Count > 0)
                        {
                            IEnumerable<string> uniqueIndexNameList = indexList.Select(ind => ind.IndexName).Distinct();
                            foreach (string uniqueIndexName in uniqueIndexNameList)
                            {
                                string colList = string.Empty;
                                IOrderedEnumerable<Index> indexesByName = from ind in indexList
                                                                          where ind.IndexName == uniqueIndexName
                                                                          orderby ind.OrdinalPosition
                                                                          select ind;

                                // Check if a Unique index overlaps an existing primary key
                                // If that is the case, do not add the duplicate index
                                // as this will cause LINQ to SQL to crash 
                                // when doing updates with rowversion columns
                                var ixList = indexesByName.ToList();
                                if (ixList.Count > 0 && ixList[0].Unique)
                                {
                                    int i = 0;
                                    foreach (var pk in pkList)
                                    {
                                        if (ixList.Count > i)
                                        {
                                            if (pk.ColumnName != ixList[i].ColumnName)
                                            {
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                        i++;
                                    }
                                    if (i > 0)
                                        continue;
                                }


                                bool unique = false;
                                var idx = indexesByName.First();
                                if (idx.Unique)
                                {
                                    unique = true;
                                }

                                foreach (Index col in indexesByName)
                                {
                                    colList += string.Format("{0} {1}, ", ToUpperFirst(col.ColumnName.Replace(" ", string.Empty)), col.SortOrder.ToString());
                                }
                                colList = colList.Remove(colList.Length - 2, 2);
                                string indexAttr = "<Index(Name:=\"{0}\", Columns:=\"{1}\", IsUnique:={2})> _";
                                if (cs)
                                    indexAttr = "[Index(Name=\"{0}\", Columns=\"{1}\", IsUnique={2})]";
                                string falseString = "False";
                                if (cs)
                                    falseString = "false";
                                string trueString = "True";
                                if (cs)
                                    trueString = "true";

                                dcLines[y - 1] += Environment.NewLine + n + string.Format(indexAttr, idx.IndexName, colList, unique ? trueString : falseString);
                            }
                        }
                    }
                }
            }
        }
        private static void AddIndexes(IRepository repository, List <string> dcLines, string n, bool cs)
        {
            for (int y = 0; y < dcLines.Count; y++)
            {
                string attr = "<Global.System.Data.Linq.Mapping.TableAttribute(";
                if (cs)
                {
                    attr = "[global::System.Data.Linq.Mapping.TableAttribute(";
                }
                if (dcLines[y].StartsWith(n + attr))
                {
                    string tableName = string.Empty;
                    // if the Name attribute is used, that is the table name, otherwise use class name
                    string[] names = dcLines[y].Split('"');
                    if (names.Count() > 1)
                    {
                        tableName = names[1];
                    }
                    string[] words = dcLines[y + 1].Split(' ');
                    if (words.Count() > 3)
                    {
                        if (string.IsNullOrEmpty(tableName))
                        {
                            tableName = words[3];
                        }
                        List <Index>      indexList = repository.GetIndexesFromTable(tableName);
                        List <PrimaryKey> pkList    = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == tableName).ToList();
                        //If there are indexes, add them
                        if (indexList.Count > 0)
                        {
                            IEnumerable <string> uniqueIndexNameList = indexList.Select(ind => ind.IndexName).Distinct();
                            foreach (string uniqueIndexName in uniqueIndexNameList)
                            {
                                string colList = string.Empty;
                                IOrderedEnumerable <Index> indexesByName = from ind in indexList
                                                                           where ind.IndexName == uniqueIndexName
                                                                           orderby ind.OrdinalPosition
                                                                           select ind;

                                // Check if a Unique index overlaps an existing primary key
                                // If that is the case, do not add the duplicate index
                                // as this will cause LINQ to SQL to crash
                                // when doing updates with rowversion columns
                                var ixList = indexesByName.ToList();
                                if (ixList.Count > 0 && ixList[0].Unique)
                                {
                                    int i = 0;
                                    foreach (var pk in pkList)
                                    {
                                        if (ixList.Count > i)
                                        {
                                            if (pk.ColumnName != ixList[i].ColumnName)
                                            {
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                        i++;
                                    }
                                    if (i > 0)
                                    {
                                        continue;
                                    }
                                }


                                bool unique = false;
                                var  idx    = indexesByName.First();
                                if (idx.Unique)
                                {
                                    unique = true;
                                }

                                foreach (Index col in indexesByName)
                                {
                                    colList += string.Format("{0} {1}, ", ToUpperFirst(col.ColumnName.Replace(" ", string.Empty)), col.SortOrder.ToString());
                                }
                                colList = colList.Remove(colList.Length - 2, 2);
                                string indexAttr = "<Index(Name:=\"{0}\", Columns:=\"{1}\", IsUnique:={2})> _";
                                if (cs)
                                {
                                    indexAttr = "[Index(Name=\"{0}\", Columns=\"{1}\", IsUnique={2})]";
                                }
                                string falseString = "False";
                                if (cs)
                                {
                                    falseString = "false";
                                }
                                string trueString = "True";
                                if (cs)
                                {
                                    trueString = "true";
                                }

                                dcLines[y - 1] += Environment.NewLine + n + string.Format(indexAttr, idx.IndexName, colList, unique ? trueString : falseString);
                            }
                        }
                    }
                }
            }
        }