public void Check(ICheckNotifier notifier)
{
var columnsToDump = TableInfo.PreLoadDiscardedColumns;
var duplicates = columnsToDump.GroupBy(k => k.GetRuntimeName()).Where(c => c.Count() > 1).ToArray();
foreach (var duplicate in duplicates)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"There are " + duplicate.Count() + " PreLoadDiscardedColumns called '" + duplicate.Key + "' for TableInfo '" +
TableInfo + "'", CheckResult.Fail));
}
//columns that exist in live but are supposedly dropped during load
var liveColumns = TableInfo.ColumnInfos.ToArray();
foreach (var preLoadDiscardedColumn in columnsToDump)
{
var match = liveColumns.FirstOrDefault(c => c.GetRuntimeName().Equals(preLoadDiscardedColumn.GetRuntimeName()));
if (match != null)
{
if (preLoadDiscardedColumn.Destination != DiscardedColumnDestination.Dilute)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + TableInfo + " declares both a PreLoadDiscardedColumn '" + preLoadDiscardedColumn + "' and a ColumnInfo with the same name", CheckResult.Fail));
return;
}
if (match.IsPrimaryKey && preLoadDiscardedColumn.Destination == DiscardedColumnDestination.Dilute)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + TableInfo + " declares a PreLoadDiscardedColumn '" + preLoadDiscardedColumn + "' but there is a matching ColumnInfo of the same name which IsPrimaryKey", CheckResult.Fail));
return;
}
}
}
if (!HasAtLeastOneColumnToStoreInDump)
{
notifier.OnCheckPerformed(new CheckEventArgs("No columns require dumping from TableInfo " + _tableInfo + " so checking is not needed", CheckResult.Success, null));
return;
}
var tables = _dumpDatabase.DiscoverTables(false);
bool stagingTableFound = tables.Any(t => t.GetRuntimeName().Equals(GetStagingRuntimeName()));
ConfirmDependencies(_dumpDatabase, notifier);
//detect ongoing loads/dirty cleanup
if (stagingTableFound)
{
bool shouldDrop = notifier.OnCheckPerformed(new CheckEventArgs("STAGING table found " + GetStagingRuntimeName() + " in ANO database",
CheckResult.Fail, null, "Drop table " + GetStagingRuntimeName()));
if (shouldDrop)
{
DropStaging();
}
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Confirmed absence of Table " + GetStagingRuntimeName() + "(this will be created during load)", CheckResult.Success, null));
}
//confirm that there is a ColumnInfo for every Dilute column
var columnInfos = _tableInfo.ColumnInfos.ToArray();
foreach (var dilutedColumn in ColumnsToRouteToSomewhereElse.Where(c => c.Destination == DiscardedColumnDestination.Dilute))
{
if (!columnInfos.Any(c => c.GetRuntimeName().Equals(dilutedColumn.RuntimeColumnName)))
{
notifier.OnCheckPerformed(new CheckEventArgs("PreLoadDiscardedColumn called " + dilutedColumn.GetRuntimeName() +
" is marked for Dilution but does not appear in the TableInfo object's ColumnInfo collection. Diluted columns must appear both in the LIVE database (in diluted state) and in IdentifierDump (in pristene state) which means that for every PreLoadDiscardedColumn which has the destination Dilution, there must be a ColumnInfo with the same name in LIVE", CheckResult.Fail, null));
}
}
//if there are any columns due to be stored in the Identifier dump
if (ColumnsToRouteToSomewhereElse.Any(c => c.GoesIntoIdentifierDump()))
{
//see if table exists
IdentifierDumperSynchronizer synchronizer = new IdentifierDumperSynchronizer(this, _externalDatabaseServer);
synchronizer.Synchronize(notifier);
//make sure there is a backup trigger enabled on the Identifier dump so that we version updates
TriggerChecks triggerChecker = new TriggerChecks(_dumpDatabase.ExpectTable(GetRuntimeName())); // primary keys - ignoring transforms for ANO
triggerChecker.Check(notifier);
}
}
public void DumpAllIdentifiersInTable(DataTable inDataTable)
{
if (HasAtLeastOneColumnToStoreInDump)
{
//bulk insert into STAGING
using (var con = (SqlConnection)_dumpDatabase.Server.GetConnection())
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
bulkCopy.DestinationTableName = GetStagingRuntimeName();
List <string> uniqueNamesAdded = new List <string>();
//wire up the identifiers
foreach (PreLoadDiscardedColumn column in ColumnsToRouteToSomewhereElse.Where(c => c.GoesIntoIdentifierDump()))
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.RuntimeColumnName, column.RuntimeColumnName));
uniqueNamesAdded.Add(column.RuntimeColumnName);
}
var pks = TableInfo.ColumnInfos.Where(c => c.IsPrimaryKey).ToArray();
//wire up the primary keys
foreach (ColumnInfo pk in pks)
{
var pkName = pk.GetRuntimeName(LoadStage.AdjustRaw);
//if we have not already added it (can be the case if there is a PreLoadDiscardedColumn which is also a primary key e.g. in the case of dilution)
if (uniqueNamesAdded.Contains(pkName))
{
continue;
}
uniqueNamesAdded.Add(pkName);
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(pkName, pkName));
}
try
{
bulkCopy.WriteToServer(inDataTable);
}
catch (Exception e)
{
throw new Exception("IdentifierDumper STAGING insert (" + bulkCopy.DestinationTableName + ") failed, make sure you have called CreateSTAGINGTable() before trying to Dump identifiers (also you should call DropStagging() when you are done)", e);
}
MergeStagingWithLive(pks.Select(col => col.GetRuntimeName(LoadStage.AdjustRaw)).ToArray());
}
HaveDumpedRecords = true;
}
//now drop the columns
foreach (PreLoadDiscardedColumn preLoadDiscardedColumn in ColumnsToRouteToSomewhereElse)
{
if (inDataTable.Columns.Contains(preLoadDiscardedColumn.RuntimeColumnName))
{
if (preLoadDiscardedColumn.Destination != DiscardedColumnDestination.Dilute)
{
inDataTable.Columns.Remove(preLoadDiscardedColumn.RuntimeColumnName);
}
HaveDumpedRecords = true;
}
else
{
throw new Exception("Could not find " + preLoadDiscardedColumn.RuntimeColumnName + " in pipeline column collection");
}
}
}
private void MergeStagingWithLive(string[] pks)
{
using (var con = _dumpDatabase.Server.GetConnection())
{
con.Open();
string[] allColumns =
pks.Select(col => col) //get the primary keys
.Union(
ColumnsToRouteToSomewhereElse.Where(
c => c.GoesIntoIdentifierDump()) //and the columns due to end up in the dump
.Select(dump => dump.RuntimeColumnName))
.ToArray();
//INSERT NEW RECORDS
//"MERGE [Demography]..[GP_ULTRA] AS dest USING [DLE_STAGING]..[Demography_GP_ULTRA_STAGING] AS source ON (source.[gmc] = dest.[gmc] AND source.[gp_code] = dest.[gp_code] AND source.[practice_code] = dest.[practice_code] AND source.[date_into_practice] = dest.[date_into_practice]) WHEN NOT MATCHED BY TARGET THEN INSERT ([notes], [gmc], [gp_code], [gp_cksum], [practice_code], [practice_cksum], [surname], [forename], [initials], [date_into_practice], [date_out_of_practice], hic_dataLoadRunID) VALUES (source.[notes], source.[gmc], source.[gp_code], source.[gp_cksum], source.[practice_code], source.[practice_cksum], source.[surname], source.[forename], source.[initials], source.[date_into_practice], source.[date_out_of_practice], 4718) OUTPUT $action, inserted.*;"
string mergeSql = "MERGE " + Environment.NewLine;
mergeSql += GetRuntimeName() + " AS dest " + Environment.NewLine;
mergeSql += "USING " + GetStagingRuntimeName() + " AS source " + Environment.NewLine;
mergeSql += "ON (" + Environment.NewLine;
mergeSql = pks.Aggregate(mergeSql, (s, n) => s + " source.[" + n + "]=dest.[" + n + "] AND").TrimEnd(new [] { 'A', 'N', 'D', ' ' }) + Environment.NewLine;
mergeSql += ") WHEN NOT MATCHED BY TARGET THEN INSERT (" + Environment.NewLine;
mergeSql = allColumns.Aggregate(mergeSql, (s, n) => s + "[" + n + "],").TrimEnd(new[] { ',', ' ' }) + Environment.NewLine;
mergeSql += ") VALUES (" + Environment.NewLine;
mergeSql = allColumns.Aggregate(mergeSql, (s, n) => s + " source.[" + n + "],").TrimEnd(new[] { ',', ' ' }) + Environment.NewLine;
mergeSql += ");" + Environment.NewLine;
using (DbCommand cmdInsert = _dumpDatabase.Server.GetCommand(mergeSql, con))
{
cmdInsert.CommandTimeout = Timeout;
cmdInsert.ExecuteNonQuery();
}
//PERFORM overwrite with UPDATES
string updateSql = "WITH ToUpdate AS (" + Environment.NewLine;
updateSql += "SELECT stag.* FROM " + GetStagingRuntimeName() + " AS stag" + Environment.NewLine;
updateSql += "LEFT OUTER JOIN " + GetRuntimeName() + " AS prod" + Environment.NewLine;
updateSql += "ON ( " + Environment.NewLine;
updateSql += "/*Primary Keys JOIN*/" + Environment.NewLine;
updateSql = pks.Aggregate(updateSql, (s, n) => s + " stag.[" + n + "]=prod.[" + n + "] AND").TrimEnd(new[] { 'A', 'N', 'D', ' ' }) + Environment.NewLine;
updateSql += ") WHERE" + Environment.NewLine;
updateSql += "/*Primary Keys not null*/" + Environment.NewLine;
updateSql = pks.Aggregate(updateSql, (s, n) => s + " stag.[" + n + "] IS NOT NULL AND").TrimEnd(new[] { 'A', 'N', 'D', ' ' }) + Environment.NewLine;
updateSql += "AND EXISTS (SELECT " + Environment.NewLine;
updateSql += "/*All columns in stag*/" + Environment.NewLine;
updateSql = allColumns.Aggregate(updateSql, (s, n) => s + " stag.[" + n + "],").TrimEnd(new[] { ',', ' ' }) + Environment.NewLine;
updateSql += "EXCEPT SELECT" + Environment.NewLine;
updateSql = allColumns.Aggregate(updateSql, (s, n) => s + " prod.[" + n + "],").TrimEnd(new[] { ',', ' ' }) + Environment.NewLine;
updateSql += "))" + Environment.NewLine;
updateSql += Environment.NewLine;
updateSql += "UPDATE prod SET" + Environment.NewLine;
updateSql = allColumns.Aggregate(updateSql, (s, n) => s + " prod.[" + n + "]=ToUpdate.[" + n + "],").TrimEnd(new[] { ',' }) + Environment.NewLine;
updateSql += "FROM " + GetRuntimeName() + " AS prod " + Environment.NewLine;
updateSql += "INNER JOIN ToUpdate ON " + Environment.NewLine;
updateSql += "(" + Environment.NewLine;
updateSql = pks.Aggregate(updateSql, (s, n) => s + " prod.[" + n + "]=ToUpdate.[" + n + "] AND").TrimEnd(new[] { 'A', 'N', 'D', ' ' }) + Environment.NewLine;
updateSql += ")" + Environment.NewLine;
using (DbCommand updateCommand = _dumpDatabase.Server.GetCommand(updateSql, con))
{
updateCommand.CommandTimeout = Timeout;
updateCommand.ExecuteNonQuery();
}
using (DbCommand cmdtruncateIdentifiersArchive =
_dumpDatabase.Server.GetCommand("TRUNCATE TABLE " + GetStagingRuntimeName(), con))
{
if (!cmdtruncateIdentifiersArchive.CommandText.Contains("_STAGING"))
{
throw new Exception("Were about to run a command that TRUNCATED a non staging table!");
}
//clear the table now
cmdtruncateIdentifiersArchive.ExecuteNonQuery();
}
}
}
