private void DeleteEntriesHavingNoChildren(ITableInfo tiCurrent, List <JoinInfo> joinPathToTimeTable, List <JoinInfo> joinsToProcess, MigrationColumnSetQueryHelper mcsQueryHelper)
{
// If there are no joins then we should delete any old updates at this level
string deleteSql;
if (!joinsToProcess.Any())
{
deleteSql = "WITH " + GetCurrentOldEntriesSQL(tiCurrent, joinPathToTimeTable) + ", EntriesToDelete AS (SELECT * FROM CurrentOldEntries)";
}
else
{
// Join on children so we can detect childless rows and delete them
var joins = new List <string>();
var wheres = new List <string>();
// create sql for child joins
foreach (var childJoin in joinsToProcess)
{
var childTable = childJoin.ForeignKey.TableInfo;
joins.Add(string.Format("LEFT JOIN {0} {1} ON CurrentOldEntries.{2} = {1}.{3}",
"[" + _dbInfo.GetRuntimeName() + "]..[" + childTable.GetRuntimeName() + "]",
childTable.GetRuntimeName(),
childJoin.PrimaryKey.GetRuntimeName(),
childJoin.ForeignKey.GetRuntimeName()
));
wheres.Add(childTable.GetRuntimeName() + "." + childJoin.ForeignKey.GetRuntimeName() + " IS NULL");
}
deleteSql = "WITH " + GetCurrentOldEntriesSQL(tiCurrent, joinPathToTimeTable) +
", EntriesToDelete AS (SELECT DISTINCT CurrentOldEntries.* FROM CurrentOldEntries " + string.Join(" ", joins) +
" WHERE " +
string.Join(" AND ", wheres) + ")";
}
deleteSql += string.Format(@"
DELETE CurrentTable
FROM {0} CurrentTable
RIGHT JOIN EntriesToDelete {1}",
"[" + _dbInfo.GetRuntimeName() + "]..[" + tiCurrent.GetRuntimeName() + "]",
mcsQueryHelper.BuildJoinClause("EntriesToDelete", "CurrentTable"));
using (var connection = (SqlConnection)_dbInfo.Server.GetConnection())
{
connection.Open();
var cmd = new SqlCommand(deleteSql, connection);
cmd.ExecuteNonQuery();
}
}
private void UpdateOldParentsThatHaveNewChildren(ITableInfo tiCurrent, List <JoinInfo> joinPathToTimeTable, ReverseMigrationQueryHelper queryHelper, MigrationColumnSetQueryHelper mcsQueryHelper)
{
var update = string.Format(@"WITH
{0}
UPDATE CurrentTable
SET {1}
FROM
LiveDataForUpdating LEFT JOIN {2} AS CurrentTable {3}",
GetLiveDataToUpdateStaging(tiCurrent, joinPathToTimeTable),
queryHelper.BuildUpdateClauseForRow("LiveDataForUpdating", "CurrentTable"),
"[" + _dbInfo.GetRuntimeName() + "]..[" + tiCurrent.GetRuntimeName() + "]",
mcsQueryHelper.BuildJoinClause("LiveDataForUpdating", "CurrentTable"));
using (var connection = (SqlConnection)_dbInfo.Server.GetConnection())
{
connection.Open();
var cmd = new SqlCommand(update, connection);
cmd.ExecuteNonQuery();
}
}
