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