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); } } }
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); } } }
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); } } } } } }