/// <summary> /// The compare table collections. /// </summary> /// <param name="first"> /// The first. /// </param> /// <param name="second"> /// The second. /// </param> /// <returns> /// The <see cref="ApplicationTableCollectionDifference"/>. /// </returns> public static ApplicationTableCollectionDifference CompareTableCollections( List <Table> first, List <Table> second) { var retval = new ApplicationTableCollectionDifference(); // Compare List of Tables var firstTableNameList = new HashSet <string>(first.Select(t => t.TableName.ToUpper())); retval.FirstExtraTables = second.Where(s => !firstTableNameList.Contains(s.TableName.ToUpper())).ToList(); var secondTableNameList = new HashSet <string>(second.Select(t => t.TableName.ToUpper())); retval.FirstMissingTables = first.Where(s => !secondTableNameList.Contains(s.TableName.ToUpper())).ToList(); // Compare Columns in tables retval.ProblemTables = new List <ApplicationTableDifference>(); var firstValidTables = first.Where(s => secondTableNameList.Contains(s.TableName.ToUpper())).ToList(); foreach (var firstTable in firstValidTables) { var secondTable = second.FirstOrDefault(u => u.TableName.ToUpper() == firstTable.TableName.ToUpper()); if (secondTable != null) { var diff = ApplicationTableDifference.CompareTableColumns(firstTable, secondTable); if (diff != null) { retval.ProblemTables.Add(diff); } } } return(retval); }
/// <summary> /// The compare table columns. /// </summary> /// <param name="firstTable"> /// The first table. /// </param> /// <param name="secondTable"> /// The second table. /// </param> /// <returns> /// The <see cref="ApplicationTableDifference"/>. /// </returns> public static ApplicationTableDifference CompareTableColumns(Table firstTable, Table secondTable) { var retval = new ApplicationTableDifference(); var secondTableColumnNameList = new HashSet <string>(secondTable.Columns.Select(u => u.ColumnName.ToUpper())); retval.FirstExtraColumns = firstTable.Columns .Where(c => !secondTableColumnNameList.Contains(c.ColumnName.ToUpper())).ToList(); var firstTableColumnNameList = new HashSet <string>(firstTable.Columns.Select(u => u.ColumnName.ToUpper())); retval.FirstMissingColumns = secondTable.Columns .Where(c => !firstTableColumnNameList.Contains(c.ColumnName.ToUpper())).ToList(); var secondTableRelationshipTableNames = new HashSet <string>(secondTable.RelationShips.Select(u => u.TableName.ToUpper())); retval.FirstExtraRelationships = firstTable.RelationShips .Where(u => !secondTableRelationshipTableNames.Contains(u.TableName)).ToList(); var firstTableRelationshipTableNames = new HashSet <string>(firstTable.RelationShips.Select(u => u.TableName.ToUpper())); retval.FirstMissingRelationships = secondTable.RelationShips .Where(u => !firstTableRelationshipTableNames.Contains(u.TableName)).ToList(); retval.ColumnDataTypeDiffs = new List <ColumnDataTypeDifference>(); foreach (var column in firstTable.Columns) { var secondColumn = secondTable.Columns.FirstOrDefault(u => u.ColumnName.ToUpper() == column.ColumnName.ToUpper()); if (secondColumn != null && column.CSharpDataType != secondColumn.CSharpDataType) { retval.ColumnDataTypeDiffs.Add( new ColumnDataTypeDifference { FirstColumn = column, SecondColumn = secondColumn, FirstColumnName = column.ColumnName, FirstColumnDataType = column.CSharpDataType, SecondColumnDataType = secondColumn.CSharpDataType }); } } if (retval.IsBroken) { retval.ProblemTable = firstTable; return(retval); } return(null); }
private static void FindRelationshipDifferences(Table firstTable, Table secondTable, ApplicationTableDifference retval) { // Relationships var secondTableRelationshipTableNames = new HashSet <string>(secondTable.Relationships.Select(u => u.ReferencedTableName.ToUpper())); retval.FirstExtraRelationships.AddRange(firstTable.Relationships .Where(u => !secondTableRelationshipTableNames.Contains(u.ReferencedTableName.ToUpper())).ToList()); var firstTableRelationshipTableNames = new HashSet <string>(firstTable.Relationships.Select(u => u.ReferencedTableName.ToUpper())); retval.FirstMissingRelationships.AddRange(secondTable.Relationships .Where(u => !firstTableRelationshipTableNames.Contains(u.ReferencedTableName.ToUpper())).ToList()); secondTableRelationshipTableNames = new HashSet <string>(secondTable.Relationships.Select(u => u.RelationshipName.ToUpper())); retval.FirstExtraRelationships.AddRange(firstTable.Relationships .Where(u => !secondTableRelationshipTableNames.Contains(u.RelationshipName.ToUpper())).ToList()); firstTableRelationshipTableNames = new HashSet <string>(firstTable.Relationships.Select(u => u.RelationshipName.ToUpper())); retval.FirstMissingRelationships.AddRange(secondTable.Relationships .Where(u => !firstTableRelationshipTableNames.Contains(u.RelationshipName.ToUpper())).ToList()); secondTableRelationshipTableNames = new HashSet <string>(secondTable.Relationships.Select(u => u.ColumnName.ToUpper())); retval.FirstExtraRelationships.AddRange(firstTable.Relationships .Where(u => !secondTableRelationshipTableNames.Contains(u.ColumnName.ToUpper())).ToList()); firstTableRelationshipTableNames = new HashSet <string>(firstTable.Relationships.Select(u => u.ColumnName.ToUpper())); retval.FirstMissingRelationships.AddRange(secondTable.Relationships .Where(u => !firstTableRelationshipTableNames.Contains(u.ColumnName.ToUpper())).ToList()); secondTableRelationshipTableNames = new HashSet <string>(secondTable.Relationships.Select(u => u.ReferencedColumnName.ToUpper())); retval.FirstExtraRelationships.AddRange(firstTable.Relationships .Where(u => !secondTableRelationshipTableNames.Contains(u.ReferencedColumnName.ToUpper())).ToList()); firstTableRelationshipTableNames = new HashSet <string>(firstTable.Relationships.Select(u => u.ReferencedColumnName.ToUpper())); retval.FirstMissingRelationships.AddRange(secondTable.Relationships .Where(u => !firstTableRelationshipTableNames.Contains(u.ReferencedColumnName.ToUpper())).ToList()); retval.FirstExtraRelationships.GroupBy(a => a.RelationshipName); retval.FirstMissingRelationships.GroupBy(a => a.RelationshipName); //Check for multiplicaity changes foreach (var firstTableRelationship in firstTable.Relationships) { //Checking on DependencyRelationShip as a self referencing table has both a parent and child relationship if (secondTable.Relationships.Any(r => r.RelationshipName == firstTableRelationship.RelationshipName && r.DependencyRelationShip == firstTableRelationship.DependencyRelationShip && (r.Multiplicity != firstTableRelationship.Multiplicity || r.ReferencedMultiplicity != firstTableRelationship.ReferencedMultiplicity))) { var secondTableRelationships = secondTable.Relationships.Where(r => r.RelationshipName == firstTableRelationship.RelationshipName && firstTableRelationship.DependencyRelationShip == firstTableRelationship.DependencyRelationShip); foreach (var secondTableRelationship in secondTableRelationships) { if (secondTableRelationship != null) { firstTableRelationship.Multiplicity = secondTableRelationship.Multiplicity; firstTableRelationship.ReferencedMultiplicity = secondTableRelationship.ReferencedMultiplicity; retval.UpdatedRelationships.Add(firstTableRelationship); } } } } }
/// <summary> /// The refresh indexes. /// </summary> /// <param name="firstTable"> /// The first table. /// </param> /// <param name="secondTable"> /// The second table. /// </param> /// <param name="retval"> /// The retval. /// </param> private static void RefreshIndexes(Table firstTable, Table secondTable, ApplicationTableDifference retval) { // Indexes var secondTableIndexTableNames = new HashSet <string>(secondTable.Indexes.Select(u => u.Name.ToUpper())); retval.ExtraIndexes = firstTable.Indexes.Where(u => !secondTableIndexTableNames.Contains(u.Name.ToUpper())) .ToList(); var firstTableIndexTableNames = new HashSet <string>(firstTable.Indexes.Select(u => u.Name.ToUpper())); retval.MissingIndexes = secondTable.Indexes .Where(u => !firstTableIndexTableNames.Contains(u.Name.ToUpper())).ToList(); if (retval.ExtraIndexes.Count == 0 && retval.MissingIndexes.Count == 0) { foreach (Index index in firstTable.Indexes) { Index secondIndex = secondTable.Indexes.FirstOrDefault(i => i.Name.ToUpper() == index.Name.ToUpper()); if (secondIndex != null) { if (index.IsUnique != secondIndex.IsUnique || index.Name.ToUpper() != secondIndex.Name.ToUpper() || index.IndexType != secondIndex.IndexType) { retval.ProblemIndexes.Add(index); } else { var secondIndexColumnNames = new HashSet <string>(secondIndex.Columns.Select(u => u.ToUpper())); var extraIndexColumns = index.Columns .Where(u => !secondIndexColumnNames.Contains(u.ToUpper())).ToList(); if (extraIndexColumns.Count > 0) { retval.ProblemIndexes.Add(index); } else { var firstIndexColumnNames = new HashSet <string>(index.Columns.Select(u => u.ToUpper())); var missingIndexColumns = secondIndex.Columns .Where(u => !firstIndexColumnNames.Contains(u.ToUpper())).ToList(); if (missingIndexColumns.Count > 0) { retval.ProblemIndexes.Add(index); } else { foreach (string column in index.Columns) { var secondColumn = secondIndex.Columns.FirstOrDefault(c => c.ToUpper() == column.ToUpper()); if (column.ToUpper() != secondColumn.ToUpper()) { retval.ProblemIndexes.Add(index); } } } } } } } } }
/// <summary> /// The compare table columns. /// </summary> /// <param name="firstTable"> /// The first table. /// </param> /// <param name="secondTable"> /// The second table. /// </param> /// <returns> /// The <see cref="ApplicationTableDifference"/>. /// </returns> public ApplicationTableDifference CompareTableColumns(Table firstTable, Table secondTable) { this.ProblemIndexes = new List <Index>(); var retval = new ApplicationTableDifference(); // Extra Columns var secondTableColumnNameList = new HashSet <string>(secondTable.Columns.Select(u => u.ColumnName.ToUpper())); retval.FirstExtraColumns = firstTable.Columns .Where(c => !secondTableColumnNameList.Contains(c.ColumnName.ToUpper())).ToList(); var firstTableColumnNameList = new HashSet <string>(firstTable.Columns.Select(u => u.ColumnName.ToUpper())); retval.FirstMissingColumns = secondTable.Columns .Where(c => !firstTableColumnNameList.Contains(c.ColumnName.ToUpper())).ToList(); FindRelationshipDifferences(firstTable, secondTable, retval); RefreshIndexes(firstTable, secondTable, retval); retval.ColumnDataTypeDiffs = new List <ColumnDataTypeDifference>(); foreach (var column in firstTable.Columns) { var secondColumn = secondTable.Columns.FirstOrDefault(u => u.ColumnName.ToUpper() == column.ColumnName.ToUpper()); if (secondColumn != null && (column.DomainDataType != secondColumn.DomainDataType || column.ColumnOrder != secondColumn.ColumnOrder || column.IsPrimaryKey != secondColumn.IsPrimaryKey || column.IsRequired != secondColumn.IsRequired || column.Length != secondColumn.Length || column.Precision != secondColumn.Precision || column.Scale != secondColumn.Scale || column.IsIdentity != secondColumn.IsIdentity || column.IdentityIncrement != secondColumn.IdentityIncrement || column.IdentitySeed != secondColumn.IdentitySeed )) { retval.ColumnDataTypeDiffs.Add( new ColumnDataTypeDifference { FirstColumn = column, SecondColumn = secondColumn, FirstColumnName = column.ColumnName, FirstColumnDataType = column.DomainDataType, SecondColumnDataType = secondColumn.DomainDataType, ColumnOrderIsDifferent = column.ColumnOrder != secondColumn.ColumnOrder, PrimaryKeyIsDifferent = column.IsPrimaryKey != secondColumn.IsPrimaryKey, IsRequiredDifferent = column.IsRequired != secondColumn.IsRequired, LengthIsDifferent = column.Length != secondColumn.Length, PrecisionIsDifferent = column.Precision != secondColumn.Precision, ScaleIsDifferent = column.Scale != secondColumn.Scale, //IsIdentity = column.IsIdentity != secondColumn.IsIdentity //IdentityIncrement = column.IdentityIncrement != secondColumn.IdentityIncrement //IdentitySeed = column.IdentitySeed != secondColumn.IdentitySeed }); } } if (retval.IsBroken) { // retval.ProblemTable = firstTable; retval.ProblemTable = secondTable; return(retval); } return(null); }