public void Execute(ICollection <DatabaseFunction> baseFunctions, ICollection <DatabaseFunction> compareFunctions)
{
bool first = false;
//find new functions (in compare, but not in base)
foreach (var function in compareFunctions)
{
var name = function.Name;
var schema = function.SchemaOwner;
var match = baseFunctions.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match != null)
{
continue;
}
var script = string.Empty;
if (!first)
{
first = true;
//CREATE FUNCTION cannot be combined with other statements in a batch,
//so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
if (_results.Count > 0)
{
script += _writer.RunStatements() + Environment.NewLine;
}
}
script += "-- NEW FUNCTION " + function.Name + Environment.NewLine +
_writer.AddFunction(function);
CreateResult(ResultType.Add, function, script);
}
//find dropped and existing functions
foreach (var function in baseFunctions)
{
var name = function.Name;
var schema = function.SchemaOwner;
var match = compareFunctions.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match == null)
{
CreateResult(ResultType.Delete, function, "-- DROP FUNCTION " + function.Name + Environment.NewLine +
_writer.DropFunction(function));
continue;
}
//functions may or may not have been changed
if (function.Sql == match.Sql)
{
continue; //the same
}
//a sanitized comparison
if (_writer.CompareProcedure(function.Sql, match.Sql))
{
continue;
}
//in Oracle could be a CREATE OR REPLACE
CreateResult(ResultType.Change, function, "-- ALTER FUNCTION " + function.Name + Environment.NewLine +
_writer.DropFunction(function) + Environment.NewLine +
_writer.AddFunction(match));
}
}
public void Execute(ICollection <DatabasePackage> basePackages, ICollection <DatabasePackage> comparePackages)
{
bool first = false;
//find new packages (in compare, but not in base)
foreach (var package in comparePackages)
{
var name = package.Name;
var schema = package.SchemaOwner;
var match = basePackages.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match != null)
{
continue;
}
var script = string.Empty;
if (!first)
{
first = true;
//CREATE PACKAGE cannot be combined with other statements in a batch,
//so be preceeded by and terminate with a "/"
if (_results.Count > 0)
{
script += _writer.RunStatements() + Environment.NewLine;
}
}
script += "-- NEW PACKAGE " + package.Name + Environment.NewLine +
_writer.AddPackage(package);
CreateResult(ResultType.Add, package, script);
}
//find dropped and existing packages
foreach (var package in basePackages)
{
var name = package.Name;
var schema = package.SchemaOwner;
var match = comparePackages.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match == null)
{
CreateResult(ResultType.Delete, package, "-- DROP PACKAGE " + package.Name + Environment.NewLine +
_writer.DropPackage(package));
continue;
}
if (package.Body == match.Body && package.Definition == match.Definition)
{
continue;
}
var script = string.Empty;
if (!first)
{
first = true;
//CREATE PACKAGE cannot be combined with other statements in a batch,
//so be preceeded by and terminate with a "/"
if (_results.Count > 0)
{
script += _writer.RunStatements() + Environment.NewLine;
}
}
//different package
script += "-- ALTER PACKAGE " + package.Name + Environment.NewLine;
//we rely on CREATE OR REPLACE here (no drop!)
script += _writer.AddPackage(match);
CreateResult(ResultType.Delete, package, script);
}
}
public void Execute(IEnumerable <DatabaseView> baseViews, IEnumerable <DatabaseView> compareViews)
{
bool first = false;
//find new views (in compare, but not in base)
foreach (var view in compareViews)
{
var name = view.Name;
var schema = view.SchemaOwner;
var match = baseViews.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match != null)
{
continue;
}
var script = string.Empty;
if (!first)
{
first = true;
//CREATE VIEW cannot be combined with other statements in a batch, so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
if (_results.Count > 0)
{
script += _writer.RunStatements() + Environment.NewLine;
}
}
script += "-- NEW VIEW " + view.Name + Environment.NewLine +
_writer.AddView(view);
CreateResult(ResultType.Add, view, script);
}
//find dropped and existing views
foreach (var view in baseViews)
{
var name = view.Name;
var schema = view.SchemaOwner;
var match = compareViews.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match == null)
{
CreateResult(ResultType.Delete, view,
"-- DROP VIEW " + view.Name + Environment.NewLine +
_writer.DropView(view));
continue;
}
//view may or may not have been changed
//we require the view Sql (otherwise we can't write it)
if (view.Sql == match.Sql)
{
continue; //the same
}
//a sanitized comparison
if (_writer.CompareView(view.Sql, match.Sql))
{
continue;
}
//in Oracle could be a CREATE OR REPLACE
var script = "-- ALTER VIEW " + view.Name + Environment.NewLine +
_writer.DropView(view) + Environment.NewLine +
_writer.AddView(match);
CreateResult(ResultType.Change, view, script);
}
}
public void Execute(ICollection <DatabaseStoredProcedure> baseProcedures, ICollection <DatabaseStoredProcedure> compareProcedures)
{
bool first = false;
//find new sprocs (in compare, but not in base)
foreach (var procedure in compareProcedures)
{
var name = procedure.Name;
var schema = procedure.SchemaOwner;
var match = baseProcedures.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match != null)
{
continue;
}
var script = string.Empty;
if (!first)
{
first = true;
//CREATE PROCEDURE cannot be combined with other statements in a batch,
//so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
if (_results.Count > 0)
{
script += _writer.RunStatements() + Environment.NewLine;
}
}
script += "-- NEW STORED PROCEDURE " + procedure.Name + Environment.NewLine +
_writer.AddProcedure(procedure);
CreateResult(ResultType.Add, procedure, script);
}
//find dropped and existing sprocs
foreach (var procedure in baseProcedures)
{
var name = procedure.Name;
var schema = procedure.SchemaOwner;
var match = compareProcedures.FirstOrDefault(t => t.Name == name && t.SchemaOwner == schema);
if (match == null)
{
CreateResult(ResultType.Delete, procedure,
"-- DROP STORED PROCEDURE " + procedure.Name + Environment.NewLine +
_writer.DropProcedure(procedure));
continue;
}
//sproc may or may not have been changed
if (procedure.Sql == match.Sql)
{
continue; //the same
}
//a sanitized comparison
if (_writer.CompareProcedure(procedure.Sql, match.Sql))
{
continue;
}
//in Oracle could be a CREATE OR REPLACE
CreateResult(ResultType.Change, procedure,
"-- ALTER STORED PROCEDURE " + procedure.Name + Environment.NewLine +
_writer.DropProcedure(procedure) + Environment.NewLine +
_writer.AddProcedure(match));
}
}
