in src/PDWScripter/PDWscripter.cs [2484:2744]
private void compareDML(PDWscripter cSource, PDWscripter cTarget, string outFile, Boolean SourceFromFile, FilterSettings FilterSet)
{
StreamWriter outfile = null;
FileStream fs = null;
Console.Write("CompareDML>");
cTarget.cmd.CommandText = @" SELECT c.definition, b.name + '.' + a.name AS ObjectName
FROM
sys.sql_modules c
INNER JOIN sys.objects a ON a.object_id = c.object_id
INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id";
if (outFile != "")
{
fs = new FileStream(outFile, FileMode.Create);
outfile = new StreamWriter(fs);
}
String description = "-- script generated the : " + String.Format("{0:d/M/yyyy HH:mm:ss}", DateTime.Now) + "\r\n";
outfile.WriteLine(description);
String createUseDbTxt = "USE " + cTarget.sourceDb + "\r\nGO\r\n";
outfile.WriteLine(createUseDbTxt);
List<KeyValuePair<String, String>> lstSourceDbObjectDefinitions = new List<KeyValuePair<string, string>>();
List<KeyValuePair<String, String>> lstTargetDbObjectDefinitions = new List<KeyValuePair<string, string>>();
List<KeyValuePair<String, String>> lstCreateOrAlterDbObjectDefinitions;
List<KeyValuePair<String, String>> lstDropDbObjectDefinitions;
if (!SourceFromFile)
{
// ===>>>< source
cSource.cmd.CommandText = @" SELECT c.definition, b.name + '.' + a.name AS ObjectName
FROM
sys.sql_modules c
INNER JOIN sys.objects a ON a.object_id = c.object_id
INNER JOIN sys.schemas b
ON a.schema_id = b.schema_id";
rdr = cSource.cmd.ExecuteReader();
while (rdr.Read())
{
IDataRecord record = (IDataRecord)rdr;
KeyValuePair<String, String> sourceKvpObjNameDef = new KeyValuePair<String, String>(String.Format("{0}", record[1]), String.Format("{0}", record[0]).TrimEnd(new char[] { '\r', '\n', ' ' }));
if (!lstSourceDbObjectDefinitions.Exists(objDef => objDef.Key == sourceKvpObjNameDef.Key))
{
// Object doesn't exist
if (!lstSourceDbObjectDefinitions.Any(objDef => objDef.Value.Contains(sourceKvpObjNameDef.Key)))
{
// Object never used by an other object
lstSourceDbObjectDefinitions.Add(sourceKvpObjNameDef);
}
else
{
// Object already used by an other object, we had it previously to the calling one
int idxCallingObj = lstSourceDbObjectDefinitions.IndexOf(lstSourceDbObjectDefinitions.First(objDef => objDef.Value.Contains(sourceKvpObjNameDef.Key)));
lstSourceDbObjectDefinitions.Insert(idxCallingObj, sourceKvpObjNameDef);
}
}
}
}
else
lstSourceDbObjectDefinitions = DbObjectDefinitions;
// ===>>>< Target
rdr = cTarget.cmd.ExecuteReader();
while (rdr.Read())
{
IDataRecord record = (IDataRecord)rdr;
KeyValuePair<String, String> targetKvpObjNameDef = new KeyValuePair<String, String>(String.Format("{0}", record[1]), String.Format("{0}", record[0]).TrimEnd(new char[] { '\r', '\n', ' ' }));
if (!lstTargetDbObjectDefinitions.Exists(objDef => objDef.Key == targetKvpObjNameDef.Key))
{
// Object doesn't exist
if (!lstTargetDbObjectDefinitions.Any(objDef => objDef.Value.Contains(targetKvpObjNameDef.Key)))
{
// Object never used by an other object
lstTargetDbObjectDefinitions.Add(targetKvpObjNameDef);
}
else
{
// Object already used by an other object, we had it previously to the calling one
int idxCallingObj = lstTargetDbObjectDefinitions.IndexOf(lstTargetDbObjectDefinitions.First(objDef => objDef.Value.Contains(targetKvpObjNameDef.Key)));
lstTargetDbObjectDefinitions.Insert(idxCallingObj, targetKvpObjNameDef);
}
}
}
// ==>> Compare
int toDelete = 0;
lstCreateOrAlterDbObjectDefinitions = new List<KeyValuePair<string, string>>();
lstDropDbObjectDefinitions = new List<KeyValuePair<string, string>>();
List<KeyValuePair<string, string>> lstSourceDbObjectDefinitionsSource;
List<KeyValuePair<string, string>> lstSourceDbObjectDefinitionsTarget;
// apply the filter
switch (FilterSet.Granularity.ToUpper())
{
case "SCHEMA":
// split to retrieve the schema name moduledef.Key.Split((char)'.')[0] -- schema.name
lstSourceDbObjectDefinitionsSource = lstSourceDbObjectDefinitions.FindAll(delegate (KeyValuePair<string, string> moduledef) { return FilterSet.GetSchemas().Contains(moduledef.Key.Split((char)'.')[0]); });
lstSourceDbObjectDefinitionsTarget = lstTargetDbObjectDefinitions.FindAll(delegate (KeyValuePair<string, string> moduledef) { return FilterSet.GetSchemas().Contains(moduledef.Key.Split((char)'.')[0]); });
lstCreateOrAlterDbObjectDefinitions = lstSourceDbObjectDefinitionsSource.Except(lstSourceDbObjectDefinitionsTarget).ToList();
lstDropDbObjectDefinitions = lstSourceDbObjectDefinitionsTarget.Except(lstSourceDbObjectDefinitionsSource).ToList();
break;
case "OBJECTS":
lstSourceDbObjectDefinitionsSource = lstSourceDbObjectDefinitions.FindAll(delegate (KeyValuePair<string, string> moduledef) { return FilterSet.GetSchemaNameObjects().Contains(moduledef.Key); });
lstSourceDbObjectDefinitionsTarget = lstTargetDbObjectDefinitions.FindAll(delegate (KeyValuePair<string, string> moduledef) { return FilterSet.GetSchemaNameObjects().Contains(moduledef.Key); });
lstCreateOrAlterDbObjectDefinitions = lstSourceDbObjectDefinitionsSource.Except(lstSourceDbObjectDefinitionsTarget).ToList();
break;
default:
lstCreateOrAlterDbObjectDefinitions = lstSourceDbObjectDefinitions.Except(lstTargetDbObjectDefinitions).ToList();
lstDropDbObjectDefinitions = lstTargetDbObjectDefinitions.Except(lstSourceDbObjectDefinitions).ToList();
toDelete = lstCreateOrAlterDbObjectDefinitions.Except(lstDropDbObjectDefinitions).ToList().Count();
break;
}
description = "\r\n-- objects scripted - ALTERorCREATE = " + lstCreateOrAlterDbObjectDefinitions.Count().ToString() + " - DROP = " + toDelete.ToString();
Console.Write("\r\n objects scripted - ALTERorCREATE = " + lstCreateOrAlterDbObjectDefinitions.Count().ToString() + " - DROP = " + toDelete.ToString());
outfile.WriteLine(description);
//==> EnumReferencedObjects ToolBar create OrderedParallelQuery ALTER
int nbObjectDefinitions = lstCreateOrAlterDbObjectDefinitions.Count();
int index = 0;
string prevDbObjectDefinition = String.Empty;
if (nbObjectDefinitions > 0)
{
outfile.WriteLine("/* =========== DROP AND/OR CREATE ###==> Begin =================*/\r\n");
}
// we order the create script based on dependencies
List<KeyValuePair<string, string>> lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered = new List<KeyValuePair<string, string>>();
List<KeyValuePair<string, string>> lstCreateOrAlterDbObjectDefinitionsSanitized = new List<KeyValuePair<string, string>>();
foreach (KeyValuePair<String, String> CreateOrAlterDbObjectDefinition in lstCreateOrAlterDbObjectDefinitions)
{
KeyValuePair<String, String> CreateOrAlterDbObjectDefinitionSanitized = new KeyValuePair<String, String>(CreateOrAlterDbObjectDefinition.Key, CreateOrAlterDbObjectDefinition.Value.Replace("[", "").Replace("]", ""));
lstCreateOrAlterDbObjectDefinitionsSanitized.Add(CreateOrAlterDbObjectDefinitionSanitized);
}
foreach (KeyValuePair<String, String> CreateOrAlterDbObjectDefinition in lstCreateOrAlterDbObjectDefinitionsSanitized)
{
if (!lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.Exists(objDef => objDef.Key == CreateOrAlterDbObjectDefinition.Key))
{
// Object doesn't exist
if (!lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.Any(objDef => objDef.Value.Contains(CreateOrAlterDbObjectDefinition.Key)))
{
// Object never used by an other object
lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.Add(CreateOrAlterDbObjectDefinition);
}
else
{
// Object already used by an other object, we had it previously to the calling one
int idxCallingObj = lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.IndexOf(lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.First(objDef => objDef.Value.Contains(CreateOrAlterDbObjectDefinition.Key)));
lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered.Insert(idxCallingObj, CreateOrAlterDbObjectDefinition);
}
}
}
foreach (KeyValuePair<String, String> CreateOrAlterDbObjectDefinitionsSanitized in lstCreateOrAlterDbObjectDefinitionsSanitizedOrdered)
{
// we retrieve the original value
KeyValuePair<String, String> dbObjectDefinition = lstCreateOrAlterDbObjectDefinitions.Find(objdef => objdef.Key == CreateOrAlterDbObjectDefinitionsSanitized.Key);
if (lstTargetDbObjectDefinitions.Exists(objDef => objDef.Key == dbObjectDefinition.Key))
{
index++;
// Add object Drop
string strObjectFullName = dbObjectDefinition.Value.Substring(12, dbObjectDefinition.Value.IndexOf(' ', 13) - 12);
string strObjectType = dbObjectDefinition.Value.Substring(7, 4);
string strDropObjectQuery = "DROP " + strObjectType + " " + strObjectFullName + ";";
if (index != 1)
{
strDropObjectQuery = "GO\r\n" + strDropObjectQuery;
}
outfile.WriteLine(strDropObjectQuery);
}
index++;
// Add object Create
if (index != 1)
{
outfile.WriteLine("GO");
}
outfile.WriteLine(dbObjectDefinition.Value.ToString());
outfile.WriteLine("GO");
Console.Write(".");
}
if (nbObjectDefinitions > 0)
{
outfile.WriteLine("/* =========== DROP AND/OR CREATE ###==> End ===================*/");
outfile.WriteLine("PRINT 'DROP AND/OR CREATE End'\r\n");
}
// Drop
nbObjectDefinitions = lstDropDbObjectDefinitions.Count();
if (nbObjectDefinitions > 0)
{
outfile.WriteLine("/* =========== DROP ###==> Begin =================*/");
outfile.WriteLine("PRINT 'DROP Begin'\r\n");
}
prevDbObjectDefinition = String.Empty;
foreach (KeyValuePair<String, String> dbObjectDefinition in lstDropDbObjectDefinitions)
{
if (!lstSourceDbObjectDefinitions.Exists(objDef => objDef.Key == dbObjectDefinition.Key))
{
index++;
// Add object Drop
string strObjectFullName = dbObjectDefinition.Value.Substring(12, dbObjectDefinition.Value.IndexOf(' ', 13) - 12);
string strObjectType = dbObjectDefinition.Value.Substring(7, 4);
if (index != 1)
{
outfile.WriteLine("GO");
}
string strDropObjectQuery = "DROP " + strObjectType + " " + strObjectFullName + ";";
outfile.WriteLine(strDropObjectQuery);
outfile.WriteLine("");
}
}
if (nbObjectDefinitions > 0)
{
outfile.WriteLine("/* =========== DROP ###==> End ===============*/\r\n");
}
outfile.WriteLine("PRINT 'END'");
if (outFile != "")
{
outfile.Close();
}
rdr.Close();
Console.WriteLine("done");
}