public void TestGenerateSqlForThreeLevelJoinPath_TimePeriodIsGrandparent()
{
ThreeTableSetupWhereTimePeriodIsGrandparent();
var ciTimePeriodicity = CatalogueRepository.GetAllObjects <ColumnInfo>().SingleOrDefault(c => c.GetRuntimeName().Equals("HeaderDate"));
if (ciTimePeriodicity == null)
{
throw new InvalidOperationException("Could not find TimePeriodicity column");
}
var sqlHelper = new BackfillSqlHelper(ciTimePeriodicity, From, To);
var tiHeader = CatalogueRepository.GetAllObjects <TableInfo>().Single(t => t.GetRuntimeName().Equals("Headers"));
var tiSamples = CatalogueRepository.GetAllObjects <TableInfo>().Single(t => t.GetRuntimeName().Equals("Samples"));
var tiResults = CatalogueRepository.GetAllObjects <TableInfo>().Single(t => t.GetRuntimeName().Equals("Results"));
var joinInfos = CatalogueRepository.GetAllObjects <JoinInfo>();
var joinPath = new List <JoinInfo>
{
joinInfos.Single(info => info.PrimaryKey.TableInfo_ID == tiHeader.ID),
joinInfos.Single(info => info.PrimaryKey.TableInfo_ID == tiSamples.ID)
};
var sql = sqlHelper.CreateSqlForJoinToTimePeriodicityTable("CurrentTable", tiResults, "TimePeriodicityTable", From, joinPath);
Assert.AreEqual(string.Format(@"SELECT CurrentTable.*, TimePeriodicityTable.HeaderDate AS TimePeriodicityField
FROM [{0}]..[Results] CurrentTable
LEFT JOIN [{0}]..[Samples] j1 ON j1.ID = CurrentTable.SampleID
LEFT JOIN [{0}]..[Headers] TimePeriodicityTable ON TimePeriodicityTable.ID = j1.HeaderID",
From.GetRuntimeName()), sql);
}
public ExitCodeType Mutilate(IDataLoadJob listener)
{
if (TimePeriodicityField == null)
{
throw new InvalidOperationException("TimePeriodicityField has not been set.");
}
var liveDatabaseInfo = GetLiveDatabaseInfo();
if (TestContext)
{
// If we are operating inside a test, the client is responsible for providing a TableNamingScheme
if (TableNamingScheme == null)
{
throw new InvalidOperationException("Executing within test context but no TableNamingScheme has been provided");
}
}
else
{
// If we are not operating inside a Test, hardwire the TableNamingScheme
TableNamingScheme = new FixedStagingDatabaseNamer(liveDatabaseInfo.GetRuntimeName());
}
// create invariant helpers
_sqlHelper = new BackfillSqlHelper(TimePeriodicityField, _dbInfo, liveDatabaseInfo);
_migrationConfiguration = new MigrationConfiguration(liveDatabaseInfo, LoadBubble.Live, LoadBubble.Staging, TableNamingScheme);
// starting with the TimePeriodicity table, we descend the join relationships to the leaf tables then ascend back up to the TimePeriodicity table
// at each step we determine the effective date of the record by joining back to the TimePeriodicity table
// this allows us to remove updates that are older than the corresponding record in live
// - however we don't remove rows that still have children, hence the recursion from leaves upwards
// -- a record may be an 'old update', but have a child for insertion (i.e. the child is not in live), in this case the parent must remain in staging despite it being 'old'
// - 'old updates' that are not deleted (because they have new descendants) must have their own data updated to reflect what is in live if there is a difference between the two, otherwise we may overwrite live with stale data
//
_tiWithTimeColumn = TimePeriodicityField.TableInfo;
ProcessOldUpdatesInTable(_tiWithTimeColumn, new List <JoinInfo>());
// Having processed all descendants of the TimePeriodicity table, we now recursively ascend back up through its predecessors to the top of the join tree
// Doing effectively the same thing, removing items that are older than the corresponding live items that do not also have new descendants and updating staging rows with live data where required.
ProcessPredecessors(_tiWithTimeColumn, new List <JoinInfo>());
return(ExitCodeType.Success);
}
