580b3b8d-cc02-4a68-8fbd-d1ec374a25f2trueD0001-sql-11.sdt.localjazz dev master ProdtrueNewtonsoft.JsonNewtonsoft.Json.ConvertersNewtonsoft.Json.LinqNewtonsoft.Json.SchemaNewtonsoft.Json.SerializationSystemSystem.ComponentModelSystem.DiagnosticsSystem.Diagnostics.TracingSystem.IO.CompressionSystem.Linq.Dynamic
// This link provides a good explanation of the Dynamic Query used in the DataTableJoins
// https://ecs.syr.edu/faculty/fawcett/handouts/CoreTechnologies/CSharp/samples/CSharpSamples/LinqSamples/DynamicQuery/Dynamic%20Expressions.html
//
#region Options
public static int TopRows = 1000; //int.MaxValue;
public static int TableDumpDepth = int.MaxValue; //2;
public static bool ShowTableQuery = false;
public static bool ShowTablesToRemove = false;
public static bool ShowTableNames = false;
public static bool ShowTableList = false;
public static bool ShowMissingUpdate = true;
public static bool ShowMissingLegacy = false;
#endregion
private Connections connections = new Connections
{
//Legacy = new ServerInstance("E0001-SQL-11.sdt.local", "StrataSphere"),
//Update = new ServerInstance("D0011-SQL-01.sdt.local", "StrataSphere")
Legacy = new ServerInstance("D0011-SQL-01.sdt.local", "jazz tst Southern Illinois 20190918.1"),
Update = new ServerInstance("D0011-SQL-01.sdt.local", "jazz tst Southern Illinois 20200331.1")
//Legacy = new ServerInstance("D0001-SQL-11.sdt.local", "jazz tst master prod 20190708.1"),
//Update = new ServerInstance("D0001-SQL-11.sdt.local", "jazz tst master prod")
};
// Hints:
// 1) if you pass a null for the table list it will include all tables in the schema
// 2) if you pass a table name like "C%"' it will only include all tables that start with C
private List SchemasAndTablesToReview = new List {
new TablesAndColumns("cci", new[] {"%QVILineItemOpportunity%"}),
//new TablesAndColumns("clientdss", new [] { "FactPatientEncounterSummary" }),
//new TablesAndColumns("dss", new [] {"%CaseType%"}, new[] {"CaseTypeDRGMapping","CaseTypeFamilyMapping","FactCaseTypeMapping"}),
//new TablesAndColumns("fw", new []{ "DimAgeCohort"})
};
void Main()
{
var tables = GetTables().ToList();
// Using 'D|' at the beginning of a column name implies that the sort order is descending for that column
tables.ForEach(table =>
{
switch (table.SchemaName)
{
case "cci":
switch (table.TableName)
{
case "ChargeCodeConfig":
table.Init(
new[] { "ChargeCodeID", "CostDriver" },
keyColumns: new[] { "ChargeCodeID", "CostDriver" }
);
break;
case "ChargeCodeCostDetail":
table.Init(
new[] { "EntityID", "ChargeCodeID" },
keyColumns: new[] { "EntityID", "ChargeCodeID" }
);
break;
case "Configuration":
table.Init(new[] { "D|FiscalYearID" }, new[] { "InitiativeIsSimplifiedWorkflow" }, new[] { "FiscalYearId" });
break;
case "InitiativeWorkflowHistory":
table.Init(new[] { "FromStep", "ToStep" });
break;
case "VariationEncounterSavingInfo":
table.Init(new[] { "EncounterRecordNumber" });
break;
case "FactOpportunityMetric":
table.Init(new[] { "FiscalYearID", "FiscalMonthID" });
break;
case "FactOpportunitySavings":
table.Init(new[] { "FiscalyYearID", "FiscalMonthID" });
break;
case "Initiative":
table.Init(new[] { "Name" });
break;
case "SystemSetting":
table.Excludes(new[] { "IsEncrypted" });
break;
case "QVILineItemOpportunity":
table.KeyColumns(new[] {"BreakdownID", "QVILineItemID", "QualityVariationEventID"});
break;
case "VariationOpportunity":
table.Init(
new[] { "CaseTypeEntityId", "CostDriver" },
keyColumns: new[] { "CostDriver", "CastTypeEntityId" }
);
break;
};
break;
case "clientdss":
switch (table.TableName)
{
case "FactPatientEncounterSummary":
table.Init(
new[] { "EncounterRecordNumber" },
new[] { "MikeQAtest1ID", "CustomJesseValidatingAllOthers", "CustomNewCalculatedSystemFieldasdf" },
where: "OBJ.MSDRGCaseTypeFamilyID <> 0 OR OBJ.APRDRGCaseTypeFamilyID <> 0");
break;
};
break;
case "dss":
switch (table.TableName)
{
case "CaseTypeDRGMapping":
table.Init(new[] { "DRGID" }, keyColumns: new[] { "DRGID" });
break;
case "CaseTypeEntity":
table.Init(new[] { "EntityID", "CaseTypeEntityID" },
keyColumns: new[] { "EntityID", "CaseTypeEntityID" });
break;
case "CaseTypeFamily":
table.Init(new[] { "Name", "CaseTypeFamilyGlobalID", "CaseTypeFamilyID" },
keyColumns: new[] { "Name", "CaseTypeFamilyGlobalID" });
break;
case "CaseTypeFamilyMappingAPRDRG":
table.Init(new[] { "APRDRGCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" },
keyColumns: new[] { "APRDRGCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" });
break;
case "CaseTypeFamilyMappingCPT":
table.Init(new[] { "CPTCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" },
keyColumns: new[] { "CPTCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" });
break;
case "CaseTypeFamilyMappingICD10":
table.Init(new[] { "ICD10Code", "MSDRGCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" },
keyColumns: new[] { "ICD10Code", "MSDRGCode", "CaseTypeFamilyVersionID", "CaseTypeFamilyID" });
break;
case "CaseTypeFamilyVersion":
table.Init(new[] { "CaseTypeFamilyVersionID" },
keyColumns: new[] { "CaseTypeFamilyVersionID" });
break;
case "DimCaseType":
table.Init(new[] { "CaseTypeFamilyID", "CaseTypeId", "Name" }, jsonColumns: new[] { "CaseTypeConfigJSON" });
break;
};
break;
};
});
if (ShowTableNames) tables.Select(t => t.SchemaTable).Dump(nameof(ShowTableNames));
if (ShowTableList) tables.Dump(nameof(ShowTableList));
DoTheComparisons(connections, tables);
}
// Define other methods and classes here
#region Gather Table Information
public string GetTableQuery()
{
var tableSchemas = string.Join(", ", SchemasAndTablesToReview.Select(sattr => $"'{sattr.SchemaName}'"));
var tableList = string.Join("\r\n", SchemasAndTablesToReview.ConvertAll(sattr =>
{
if (sattr.TableNames?.Any(cn => cn == "*") ?? true)
{
return $"\r\nWHEN c.TABLE_SCHEMA = '{sattr.SchemaName}' THEN 1";
}
else
{
var result = "";
if (sattr.TableNames.Any(tn => tn.Contains("%")))
{
result += sattr.TableNames.Where(tn => tn.Contains("%"))
.Aggregate("", (x, y) => $"{x}\r\nWHEN c.TABLE_SCHEMA = '{sattr.SchemaName}' AND c.TABLE_NAME LIKE '{y}' THEN 1");
}
if (sattr.TableNames.Any(tn => !tn.Contains("%")))
{
result += $"\r\nWHEN c.TABLE_SCHEMA = '{sattr.SchemaName}' AND c.TABLE_NAME IN ({string.Join(", ", sattr.TableNames.Where(tn => !tn.Contains("%")).Select(tn => $"'{tn}'"))}) THEN 1";
}
return result;
}
}));
return $@"SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
, CAST(CASE WHEN ccu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON tc.TABLE_SCHEMA = c.TABLE_SCHEMA AND tc.TABLE_NAME = c.TABLE_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA AND ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND ccu.COLUMN_NAME = c.COLUMN_NAME
WHERE c.TABLE_SCHEMA in ({tableSchemas})
AND (CASE {tableList}
ELSE 0
END = 1)
AND c.DATA_TYPE NOT IN ('datetime','uniqueidentifier', 'timestamp')
AND NOT (c.COLUMN_NAME = 'RowID' and CAST(CASE WHEN ccu.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END AS BIT) = 1)
AND tc.CONSTRAINT_TYPE = 'Primary Key'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION";
}
public IList
GetTables()
{
var tableQuery = GetTableQuery();
if (ShowTableQuery) tableQuery.Dump(nameof(ShowTableQuery));
var tablesToRemove = SchemasAndTablesToReview.Where(sattr => sattr.ExcludeTables.Any()).SelectMany(sattr => sattr.ExcludeTables.Select(et => new { sattr.SchemaName, ExcludeTable = et }));
var tables = ExecuteQueryDynamic(tableQuery).ToList();
if (tablesToRemove.Any())
{
if (ShowTablesToRemove)
tablesToRemove.Dump(nameof(ShowTablesToRemove));
tables.RemoveAll(x => tablesToRemove.Any(sattr => sattr.SchemaName == x.TABLE_SCHEMA && sattr.ExcludeTable == x.TABLE_NAME));
}
return tables
.GroupBy(x => new { x.TABLE_SCHEMA, x.TABLE_NAME },
(g, d) => new Table(g.TABLE_SCHEMA, g.TABLE_NAME,
d.Select(y => new Column(y.COLUMN_NAME, y.DATA_TYPE, y.IsPrimaryKey)).ToList()))
.ToList();
}
public class Table
{
public string SchemaName { get; set; }
public string TableName { get; set; }
public IList Columns { get; set; } = new List();
public IList OrderBy { get; set; } = new List();
public IList Exclude { get; set; } = new List();
public IList KeyColumn { get; set; } = new List();
public IList Json { get; set; } = new List();
public string Where { get; set; } = "";
internal string SchemaTable => $"{SchemaName}.{TableName}";
public string SqlSelect => $"select TOP({TopRows}) \r\n\t{SelectColumns}\r\nfrom {SchemaTable} OBJ{Where}{OrderByStatement}";
internal string SelectColumns => !Columns?.Any() ?? true ? "*" : string.Join(",\r\n\t", Columns.Where(c => !Exclude.ToList().Contains(c.ColumnName)).Select(c => c.ColumnName));
internal string OrderByStatement => !OrderBy?.Any() ?? true ? "" : "\r\nOrder By " + string.Join(", ", OrderBy.Select(ob => $"{ob.ColumnName}{(ob.IsDescending ? " DESC" : "")}"));
public Table(string schemaName, string tableName)
{
SchemaName = schemaName;
TableName = tableName;
Columns = new List();
OrderBy = new List();
}
public Table(string schemaName, string tableName, IEnumerable columns)
: this(schemaName, tableName)
{
Columns = columns.ToList();
}
public Table(string schemaName, string tableName, IEnumerable columns, IEnumerable orderBy)
: this(schemaName, tableName, columns)
{
OrderBy = orderBy.ToList();
}
///
///
///
///
/// Using 'D|' at the beginning of a column name implies that the sort order is descending for that column
///
public void Init(IEnumerable orderBys = null, IEnumerable excludes = null, IEnumerable keyColumns = null, IEnumerable jsonColumns = null, string where = "")
{
Init(orderBys?.Distinct().Select(o => new OrderBy(o.Split('|').Last(), o.StartsWith("D|", StringComparison.CurrentCultureIgnoreCase))) ?? null,
excludes?.Distinct(), keyColumns?.Distinct(), jsonColumns?.Distinct(), where);
}
///
///
///
///
/// Using 'D|' at the beginning of a column name implies that the sort order is descending for that column
///
public void Init(IEnumerable orderBys = null, IEnumerable excludes = null, IEnumerable keyColumns = null, IEnumerable jsonColumns = null, string where = "")
{
if (orderBys != null)
OrderBys(orderBys.Where(x => Columns.Select(c => c.ColumnName).Contains(x.ColumnName)));
if (excludes != null)
Excludes(excludes.Where(x => Columns.Select(c => c.ColumnName).Contains(x)));
if (keyColumns != null)
KeyColumns(keyColumns.Where(x => Columns.Select(c => c.ColumnName).Contains(x)));
if (jsonColumns != null)
JsonColumns(jsonColumns.Where(x => Columns.Select(c => c.ColumnName).Contains(x)));
if (!string.IsNullOrEmpty(where))
where = $"\r\nwhere {where}";
}
///
/// Using 'D|' at the beginning of a column name implies that the sort order is descending for that column
///
public void OrderBys(IEnumerable orderBys)
{
OrderBy.Clear();
((List)OrderBy).AddRange(orderBys.Where(o => Columns.Select(c => c.ColumnName).Contains(o.ColumnName, StringComparer.CurrentCultureIgnoreCase)));
}
public void Excludes(IEnumerable excludes)
{
Exclude.Clear();
((List)Exclude).AddRange(excludes.Where(e => Columns.Select(c => c.ColumnName).Contains(e, StringComparer.CurrentCultureIgnoreCase)));
}
public void KeyColumns(IEnumerable keyColumns)
{
KeyColumn.Clear();
((List)KeyColumn).AddRange(keyColumns.Where(kc => Columns.Select(c => c.ColumnName).Contains(kc, StringComparer.CurrentCultureIgnoreCase)));
KeyColumn.Reverse().ToList().ForEach(kc => {
var col = Columns.Single(c => c.ColumnName == kc);
Columns.Remove(col);
Columns.Insert(0, col);
});
}
public void JsonColumns(IEnumerable jsonColumns)
{
Json.Clear();
((List)Json).AddRange(jsonColumns.Where(jc => Columns.Select(c => c.ColumnName).Contains(jc, StringComparer.CurrentCultureIgnoreCase)));
}
}
public class Column
{
public string ColumnName { get; set; }
public string DataType { get; set; }
public bool IsPrimaryKey { get; set; }
public Column(string columnName, string dataType = "", bool isPrimaryKey = false)
{
ColumnName = columnName;
DataType = dataType;
IsPrimaryKey = isPrimaryKey;
}
}
public class OrderBy
{
public string ColumnName { get; set; }
public bool IsDescending { get; set; } = false;
public OrderBy(string columnName, bool isDescending = false)
{
ColumnName = columnName;
IsDescending = isDescending;
}
}
public class TablesAndColumns
{
public string SchemaName { get; set; }
public IEnumerable TableNames { get; set; }
public IEnumerable ExcludeTables { get; set; }
public TablesAndColumns(string schemaName, IEnumerable tables = null, IEnumerable excludeTables = null)
{
SchemaName = schemaName;
TableNames = tables ?? new string[] { "*" };
ExcludeTables = excludeTables ?? new string[] { };
}
}
public class OrderByInformation
{
public string TableName { get; set; }
public IEnumerable OrderBy { get; set; }
public OrderByInformation(string tableName, IEnumerable columns)
{
TableName = tableName;
OrderBy = columns
.Select(c =>
new OrderBy(c.StartsWith("D|") ? c.Substring(2) : c,
c.StartsWith("D|")));
}
}
public class ColumnsToExclude
{
public string TableName { get; set; }
public IEnumerable ColumnNames { get; set; }
public ColumnsToExclude(string tableName, IEnumerable columns)
{
TableName = tableName;
ColumnNames = columns;
}
}
public class JsonColumns
{
public string TableName { get; set; }
public IEnumerable ColumnNames { get; set; }
public JsonColumns(string tableName, IEnumerable columns)
{
TableName = tableName;
ColumnNames = columns;
}
}
#endregion
#region Do the comparisons
public void DoTheComparisons(Connections connections, IEnumerable
tables)
{
foreach (var table in tables)
{
var sql = table.SqlSelect;
var dataLegacy = GetData(connections.Legacy, sql, table.TableName).AsEnumerable().ToList();
var dataUpdate = GetData(connections.Update, sql, table.TableName).AsEnumerable().ToList();
var compare = new List();
var jsonColumns = table.Json.ToList() ?? null;
var keyColumns = table.KeyColumn.ToList() ?? null;
var idx = 0;
dataLegacy
.ForEach(legacyRow =>
{
DataRow updateRow = default(DataRow);
if (table.KeyColumn.Any())
{
var keys = string.Join("|", table.KeyColumn.Select(kc => $"{legacyRow.Field