private static SchemaComparisonResult CompareStoredProcedures(Database source, Database target, bool strict)
{
SchemaComparisonResult result = new SchemaComparisonResult();
var sourceObjectsMissingInTarget = source.StoredProcedures.Where(x => !target.StoredProcedures.Any(y => y.Name == x.Name && y.Type == x.Type));
if (sourceObjectsMissingInTarget.Count() > 0)
{
result.Inequalities.AddRange(sourceObjectsMissingInTarget.Select(x => new SchemaComparisonInequality
{
ObjectName = x.Name,
ObjectType = x.Type,
InequalityType = SchemaComparisonInequalityTypes.MissingInTarget
}));
}
if (strict)
{
var targetObjectsMissingInSource = target.StoredProcedures.Where(x => !source.StoredProcedures.Any(y => y.Name == x.Name && y.Type == x.Type));
if (targetObjectsMissingInSource.Count() > 0)
{
result.Inequalities.AddRange(targetObjectsMissingInSource.Select(x => new SchemaComparisonInequality
{
ObjectName = x.Name,
ObjectType = x.Type,
InequalityType = SchemaComparisonInequalityTypes.MissingInSource
}));
}
}
return result;
}
public static SchemaComparisonResult Compare(this Database source, Database target, bool strict = false)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
if (target == null)
{
throw new ArgumentNullException("target");
}
SchemaComparisonResult result = new SchemaComparisonResult();
result.Merge(CompareTables(source, target, strict));
result.Merge(CompareFunctions(source, target, strict));
result.Merge(CompareStoredProcedures(source, target, strict));
return result;
}
private static SchemaComparisonResult CompareTables(Database source, Database target, bool strict)
{
SchemaComparisonResult result = new SchemaComparisonResult();
var sourceTablesMissingInTarget = source.Tables.Where(x => !target.Tables.Any(y => y.Name == x.Name));
if (sourceTablesMissingInTarget.Count() > 0)
{
result.Inequalities.AddRange(sourceTablesMissingInTarget.Select(x => new SchemaComparisonInequality
{
ObjectName = x.Name,
ObjectType = x.Type,
InequalityType = SchemaComparisonInequalityTypes.MissingInTarget
}));
}
if (strict)
{
var targetTablesMissingInSource = source.Tables.Where(x => !target.Tables.Any(y => y.Name == x.Name));
if (targetTablesMissingInSource.Count() > 0)
{
result.Inequalities.AddRange(targetTablesMissingInSource.Select(x => new SchemaComparisonInequality
{
ObjectName = x.Name,
ObjectType = x.Type,
InequalityType = SchemaComparisonInequalityTypes.MissingInSource
}));
}
}
var commonTables = source.Tables.Where(x => target.Tables.Exists(y => y.Name == x.Name));
foreach (var table in commonTables)
{
var targetTable = target.Tables.FirstOrDefault(x => x.Name == table.Name);
if (targetTable == null)
{
continue; //Shouldnt be possible
}
result.Merge(CompareTable(table, targetTable, strict));
}
return result;
}
/// <summary>
/// Converts information from sql schema discovery queries and turns it into serializable DatabaseSchema objects.
/// </summary>
private static Database ProcessResults(SchemaDiscoveryResult results)
{
if (results == null)
{
return new Database();
}
Database databaseSchema = new Database(results.DatabaseName);
//Tables
foreach (var tableResult in results.Objects.Where(x => x.Type == SchemaObjectTypeStrings.UserTable))
{
var tableSchema = new Table(tableResult.Name);
//columns
tableSchema.Objects.AddRange(
results.Columns.Where(x => x.TableName == tableResult.Name).Select(x => new TableColumn(x.ColumnName))
);
var tableObjects = results.Objects.Where(x => x.ParentName == tableResult.Name).ToList();
var tableIndexes = results.Indexes.Where(x => x.TableName == tableResult.Name).ToList();
//primary keys
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.PrimaryKeyConstraint).Select(x => new PrimaryKeyConstraint(x.Name))
);
//foreign keys
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.ForeignKeyConstraint).Select(x => new ForeignKeyConstraint(x.Name))
);
//default contstraints
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.DefaultConstraint).Select(x => new DefaultConstraint(x.Name))
);
//unique contstraints
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.UniqueConstraint).Select(x => new UniqueConstraint(x.Name))
);
//check constraints
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.CheckConstraint).Select(x => new CheckConstraint(x.Name))
);
//triggers
tableSchema.Objects.AddRange(
tableObjects.Where(x => x.Type == SchemaObjectTypeStrings.SqlTrigger).Select(x => new Trigger(x.Name))
);
//indexes
tableSchema.Objects.AddRange(
tableIndexes.Select(x => new TableIndex(x.IndexName))
);
databaseSchema.Tables.Add(tableSchema);
}
//Stored Procedures
databaseSchema.StoredProcedures.AddRange(
results.Objects.Where(x => x.Type == SchemaObjectTypeStrings.SqlStoredProcedure).Select(x => new StoredProcedure(x.Name))
);
//Functions
databaseSchema.Functions.AddRange(
results.Objects.Where(x => x.Type == SchemaObjectTypeStrings.SqlScalarFunction).Select(x => new ScalarValuedFunction(x.Name))
);
databaseSchema.Functions.AddRange(
results.Objects.Where(x => x.Type == SchemaObjectTypeStrings.SqlTableValuedFunction).Select(x => new TableValuedFunction(x.Name))
);
return databaseSchema;
}