private static void CreateHistoryTable(RelationalTable baseTable, DwhBuilderConfiguration configuration)
{
var historyTable = baseTable.Schema.AddTable(baseTable.Name + configuration.HistoryTableNamePostfix).SetIsHistoryTable();
var identityColumnName = (configuration.HistoryTableIdentityColumnBase ?? historyTable.Name) + configuration.HistoryTableIdentityColumnPostfix;
historyTable.AddColumn(identityColumnName, true).SetIdentity();
foreach (var column in baseTable.Columns)
{
historyTable.AddColumn(column.Name, false);
}
if (baseTable.PrimaryKeyColumns.Count > 0)
{
var historyFkToBase = historyTable.AddForeignKeyTo(baseTable);
foreach (var basePkColumn in baseTable.PrimaryKeyColumns)
{
historyFkToBase.AddColumnPair(historyTable[basePkColumn.Name], basePkColumn);
}
}
foreach (var baseFk in baseTable.ForeignKeys)
{
var historyFk = historyTable.AddForeignKeyTo(baseFk.TargetTable);
foreach (var baseFkPair in baseFk.ColumnPairs)
{
historyFk.AddColumnPair(historyTable[baseFkPair.SourceColumn.Name], baseFkPair.TargetColumn);
}
}
baseTable.AddColumn(configuration.ValidFromColumnName, false);
historyTable.AddColumn(configuration.ValidFromColumnName, false);
historyTable.AddColumn(configuration.ValidToColumnName, false);
}
public void HistoryWithEtlRunInfo()
{
var model = CreateModel();
var configuration = new DwhBuilderConfiguration()
{
HistoryTableNamePostfix = "Hist",
};
model["secondary"]["pet"].SetEtlRunInfoDisabled();
model["dbo"]["people"].SetHasHistoryTable();
RelationalModelExtender.Extend(model, configuration);
Assert.AreEqual(3 + 4 + 1, model["dbo"]["people"].Columns.Count);
Assert.IsNotNull(model["dbo"]["people"][configuration.EtlRunInsertColumnName]);
Assert.IsNotNull(model["dbo"]["people"][configuration.EtlRunUpdateColumnName]);
Assert.IsNotNull(model["dbo"]["people"][configuration.EtlRunFromColumnName]);
Assert.IsNotNull(model["dbo"]["people"][configuration.EtlRunToColumnName]);
Assert.IsNotNull(model["dbo"]["people"][configuration.ValidFromColumnName]);
Assert.AreEqual(1 + 3 + 4 + 2, model["dbo"]["peopleHist"].Columns.Count);
Assert.AreEqual("peopleHistID", model["dbo"]["peopleHist"].Columns[0].Name);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.EtlRunInsertColumnName]);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.EtlRunUpdateColumnName]);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.EtlRunFromColumnName]);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.EtlRunToColumnName]);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.ValidFromColumnName]);
Assert.IsNotNull(model["dbo"]["peopleHist"][configuration.ValidToColumnName]);
Assert.AreEqual(3, model["secondary"]["pet"].Columns.Count);
}
private static SqlTable CreateHistoryTable(SqlTable baseTable, DwhBuilderConfiguration configuration)
{
var historyTable = new SqlTable(baseTable.SchemaAndTableName.Schema, baseTable.SchemaAndTableName.TableName + configuration.HistoryTableNamePostfix);
baseTable.DatabaseDefinition.AddTable(historyTable);
var identityColumnName = (configuration.HistoryTableIdentityColumnBase ?? historyTable.SchemaAndTableName.TableName) + configuration.HistoryTableIdentityColumnPostfix;
historyTable.AddInt(identityColumnName).SetIdentity().SetPK();
// step #1: copy all columns (including foreign keys)
foreach (var column in baseTable.Columns)
{
var historyColumn = new SqlColumn();
column.CopyTo(historyColumn);
historyTable.Columns.Add(column.Name, historyColumn);
historyColumn.Table = historyTable;
}
var baseTablePk = baseTable.Properties.OfType <PrimaryKey>().FirstOrDefault();
var historyFkToBase = new ForeignKey(historyTable, baseTable, "FK_" + historyTable.SchemaAndTableName.SchemaAndName + "__ToBase");
foreach (var basePkColumn in baseTablePk.SqlColumns)
{
historyFkToBase.ForeignKeyColumns.Add(
new ForeignKeyColumnMap(historyTable.Columns[basePkColumn.SqlColumn.Name], basePkColumn.SqlColumn));
}
historyTable.Properties.Add(historyFkToBase);
// step #2: copy foreign key properties (columns were already copied in step #1)
// only those foreign keys are copied to the history table where each column exists in the history table
var baseForeignKeys = baseTable.Properties.OfType <ForeignKey>()
.ToList();
foreach (var baseFk in baseForeignKeys)
{
var historyFk = new ForeignKey(historyTable, baseFk.ReferredTable, null);
historyTable.Properties.Add(historyFk);
foreach (var fkCol in baseFk.ForeignKeyColumns)
{
var fkColumn = historyTable.Columns[fkCol.ForeignKeyColumn.Name];
historyFk.ForeignKeyColumns.Add(new ForeignKeyColumnMap(fkColumn, fkCol.ReferredColumn));
}
foreach (var prop in baseFk.SqlEngineVersionSpecificProperties)
{
historyFk.SqlEngineVersionSpecificProperties.Add(new SqlEngineVersionSpecificProperty(prop.Version, prop.Name, prop.Value));
}
}
baseTable.AddDateTimeOffset(configuration.ValidFromColumnName, 7, configuration.InfinitePastDateTime == null && !configuration.UseEtlRunIdForDefaultValidFrom);
historyTable.AddDateTimeOffset(configuration.ValidFromColumnName, 7, configuration.InfinitePastDateTime == null && !configuration.UseEtlRunIdForDefaultValidFrom);
historyTable.AddDateTimeOffset(configuration.ValidToColumnName, 7, configuration.InfiniteFutureDateTime == null);
return(historyTable);
}
public override void Execute()
{
DatabaseDeclaration.GetTable("dbo", "Company").HasHistoryTable();
var configuration = new DwhBuilderConfiguration();
var model = DwhDataDefinitionToRelationalModelConverter.Convert(DatabaseDeclaration, "dbo");
DataDefinitionExtenderMsSql2016.Extend(DatabaseDeclaration, configuration);
RelationalModelExtender.Extend(model, configuration);
CreateDatabase(DatabaseDeclaration);
Init(configuration, model);
Update(configuration, model);
}
public static void Extend(DatabaseDeclaration declaration, DwhBuilderConfiguration configuration)
{
if (configuration.UseEtlRunInfo)
{
var etlRunTable = new SqlTable(declaration.DefaultSchema, configuration.EtlRunTableName);
declaration.AddTable(etlRunTable);
etlRunTable.AddDateTime2("StartedOn", 7, false).SetPK();
etlRunTable.AddNVarChar("Name", 200, false);
etlRunTable.AddNVarChar("MachineName", 200, false);
etlRunTable.AddNVarChar("UserName", 200, false);
etlRunTable.AddDateTime2("FinishedOn", 7, true);
etlRunTable.AddNVarChar("Result", 20, true);
declaration.AddAutoNaming(new List <SqlTable> {
etlRunTable
});
foreach (var baseTable in declaration.GetTables())
{
if (baseTable.HasProperty <EtlRunInfoDisabledProperty>() || baseTable == etlRunTable)
{
continue;
}
baseTable.AddDateTime2(configuration.EtlRunInsertColumnName, 7, false).SetForeignKeyToTable(etlRunTable.SchemaAndTableName);
baseTable.AddDateTime2(configuration.EtlRunUpdateColumnName, 7, false).SetForeignKeyToTable(etlRunTable.SchemaAndTableName);
baseTable.AddDateTime2(configuration.EtlRunFromColumnName, 7, false).SetForeignKeyToTable(etlRunTable.SchemaAndTableName);
baseTable.AddDateTime2(configuration.EtlRunToColumnName, 7, true).SetForeignKeyToTable(etlRunTable.SchemaAndTableName);
}
}
var baseTablesWithHistory = declaration.GetTables()
.Where(x => x.HasProperty <HasHistoryTableProperty>() &&
x.SchemaAndTableName.TableName != configuration.EtlRunTableName)
.ToList();
var historyTables = new List <SqlTable>();
foreach (var baseTable in baseTablesWithHistory)
{
var historyTable = CreateHistoryTable(baseTable, configuration);
historyTables.Add(historyTable);
}
declaration.AddAutoNaming(historyTables);
}
public void EtlRunInfo()
{
var model = new TestModel();
var configuration = new DwhBuilderConfiguration();
model.GetTable("Secondary", "Pet").EtlRunInfoDisabled();
DataDefinitionExtenderMsSql2016.Extend(model, configuration);
var etlRunTable = model.GetTable("dbo", "_EtlRun");
Assert.IsNotNull(etlRunTable);
Assert.AreEqual(6, etlRunTable.Columns.Count);
Assert.AreEqual(3 + 4, model.GetTable("dbo", "People").Columns.Count);
Assert.AreEqual(3, model.GetTable("Secondary", "Pet").Columns.Count);
}
public static T Extend <T>(T model, DwhBuilderConfiguration configuration)
where T : RelationalModel
{
if (configuration.UseEtlRunInfo)
{
var etlRunTable = model.DefaultSchema.AddTable(configuration.EtlRunTableName).SetEtlRunInfo();
etlRunTable.AddColumn("StartedOn", false);
etlRunTable.AddColumn("Name", false);
etlRunTable.AddColumn("MachineName", false);
etlRunTable.AddColumn("UserName", false);
etlRunTable.AddColumn("FinishedOn", false);
etlRunTable.AddColumn("Result", false);
foreach (var schema in model.Schemas)
{
foreach (var baseTable in schema.Tables)
{
if (baseTable.GetEtlRunInfoDisabled() || baseTable == etlRunTable)
{
continue;
}
var c1 = baseTable.AddColumn(configuration.EtlRunInsertColumnName, false).SetUsedByEtlRunInfo();
var c2 = baseTable.AddColumn(configuration.EtlRunUpdateColumnName, false).SetUsedByEtlRunInfo();
var c3 = baseTable.AddColumn(configuration.EtlRunFromColumnName, false).SetUsedByEtlRunInfo();
var c4 = baseTable.AddColumn(configuration.EtlRunToColumnName, false).SetUsedByEtlRunInfo();
baseTable.AddForeignKeyTo(etlRunTable).AddColumnPair(c1, etlRunTable["StartedOn"]);
baseTable.AddForeignKeyTo(etlRunTable).AddColumnPair(c2, etlRunTable["StartedOn"]);
baseTable.AddForeignKeyTo(etlRunTable).AddColumnPair(c3, etlRunTable["StartedOn"]);
baseTable.AddForeignKeyTo(etlRunTable).AddColumnPair(c4, etlRunTable["StartedOn"]);
}
}
}
var baseTablesWithHistory = model.Schemas.SelectMany(x => x.Tables)
.Where(x => x.GetHasHistoryTable() && !x.GetIsEtlRunInfo());
foreach (var baseTable in baseTablesWithHistory)
{
CreateHistoryTable(baseTable, configuration);
}
return(model);
}
public void EtlRunInfo()
{
var model = CreateModel();
var configuration = new DwhBuilderConfiguration();
model["secondary"]["pet"].SetEtlRunInfoDisabled();
RelationalModelExtender.Extend(model, configuration);
var etlRunTable = model["dbo"]["_EtlRun"];
Assert.IsNotNull(etlRunTable);
Assert.AreEqual(6, etlRunTable.Columns.Count);
Assert.AreEqual(3 + 4, model["dbo"]["people"].Columns.Count);
Assert.AreEqual(3, model["secondary"]["pet"].Columns.Count);
}
public void HistoryWithoutEtlRunInfo()
{
var model = new TestModel();
var configuration = new DwhBuilderConfiguration()
{
UseEtlRunInfo = false,
HistoryTableNamePostfix = "Hist",
};
model.GetTable("dbo", "People").HasHistoryTable();
DataDefinitionExtenderMsSql2016.Extend(model, configuration);
Assert.AreEqual(3 + 1, model.GetTable("dbo", "People").Columns.Count);
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.ValidFromColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.ValidToColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunInsertColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunUpdateColumnName));
Assert.AreEqual(1 + 3 + 2, model.GetTable("dbo", "PeopleHist").Columns.Count);
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey("PeopleHistID"));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.ValidFromColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.ValidToColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunInsertColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunUpdateColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunFromColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunToColumnName));
Assert.AreEqual(3, model.GetTable("Secondary", "Pet").Columns.Count);
Assert.IsNotNull(model.GetTable("dbo", "PeopleHist").Properties.OfType <PrimaryKey>().First().SqlColumns[0].SqlColumn.Properties.OfType <Identity>().FirstOrDefault());
Assert.AreEqual(1, model.GetTable("dbo", "PeopleHist").Properties.OfType <PrimaryKey>().First().SqlColumns.Count);
Assert.AreEqual("PeopleHistID", model.GetTable("dbo", "PeopleHist").Properties.OfType <PrimaryKey>().First().SqlColumns[0].SqlColumn.Name);
Assert.AreEqual(model.GetTable("dbo", "People"), model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().First().ReferredTable);
Assert.AreEqual(model.GetTable("dbo", "PeopleHist")["Id"], model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().First().ForeignKeyColumns[0].ForeignKeyColumn);
Assert.AreEqual(model.GetTable("dbo", "People")["Id"], model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().First().ForeignKeyColumns[0].ReferredColumn);
Assert.AreEqual(model.GetTable("Secondary", "Pet"), model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().Skip(1).First().ReferredTable);
Assert.AreEqual(model.GetTable("dbo", "PeopleHist")["FavoritePetId"], model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().Skip(1).First().ForeignKeyColumns[0].ForeignKeyColumn);
Assert.AreEqual(model.GetTable("Secondary", "Pet")["Id"], model.GetTable("dbo", "PeopleHist").Properties.OfType <ForeignKey>().Skip(1).First().ForeignKeyColumns[0].ReferredColumn);
}
public void HistoryWithoutEtlRunInfo()
{
var model = CreateModel();
var configuration = new DwhBuilderConfiguration()
{
UseEtlRunInfo = false,
HistoryTableNamePostfix = "Hist",
};
model["dbo"]["people"].SetHasHistoryTable();
RelationalModelExtender.Extend(model, configuration);
Assert.AreEqual(3 + 1, model["dbo"]["people"].Columns.Count);
Assert.AreEqual(configuration.ValidFromColumnName, model["dbo"]["people"].Columns[3].Name);
Assert.AreEqual(1 + 3 + 2, model["dbo"]["peopleHist"].Columns.Count);
Assert.AreEqual("peopleHistID", model["dbo"]["peopleHist"].Columns[0].Name);
Assert.AreEqual(configuration.ValidFromColumnName, model["dbo"]["peopleHist"].Columns[4].Name);
Assert.AreEqual(configuration.ValidToColumnName, model["dbo"]["peopleHist"].Columns[5].Name);
Assert.AreEqual(3, model["secondary"]["pet"].Columns.Count);
Assert.IsTrue(model["dbo"]["peopleHist"].AnyPrimaryKeyColumnIsIdentity);
Assert.AreEqual(1, model["dbo"]["peopleHist"].PrimaryKeyColumns.Count);
Assert.AreEqual("peopleHistID", model["dbo"]["peopleHist"].PrimaryKeyColumns[0].Name);
Assert.AreEqual(1 + 3 + 2, model["dbo"]["peopleHist"].Columns.Count);
Assert.IsTrue(model["dbo"]["peopleHist"]["PeopleHistID"] != null);
Assert.IsTrue(model["dbo"]["peopleHist"][configuration.ValidFromColumnName] != null);
Assert.IsTrue(model["dbo"]["peopleHist"][configuration.ValidToColumnName] != null);
Assert.AreEqual(model["dbo"]["people"], model["dbo"]["peopleHist"].ForeignKeys[0].TargetTable);
Assert.AreEqual(model["dbo"]["peopleHist"]["id"], model["dbo"]["peopleHist"].ForeignKeys[0].ColumnPairs[0].SourceColumn);
Assert.AreEqual(model["dbo"]["people"]["id"], model["dbo"]["peopleHist"].ForeignKeys[0].ColumnPairs[0].TargetColumn);
Assert.AreEqual(model["secondary"]["PET"], model["dbo"]["peopleHist"].ForeignKeys[1].TargetTable);
Assert.AreEqual(model["dbo"]["peopleHist"]["favoritepetid"], model["dbo"]["peopleHist"].ForeignKeys[1].ColumnPairs[0].SourceColumn);
Assert.AreEqual(model["secondary"]["PET"]["id"], model["dbo"]["peopleHist"].ForeignKeys[1].ColumnPairs[0].TargetColumn);
}
public void HistoryWithEtlRunInfo()
{
var model = new TestModel();
var configuration = new DwhBuilderConfiguration()
{
HistoryTableNamePostfix = "Hist",
};
model.GetTable("Secondary", "Pet").EtlRunInfoDisabled();
model.GetTable("dbo", "People").HasHistoryTable();
DataDefinitionExtenderMsSql2016.Extend(model, configuration);
var etlRunTable = model.GetTable("dbo", "_EtlRun");
Assert.IsNotNull(etlRunTable);
Assert.AreEqual(3 + 4 + 1, model.GetTable("dbo", "People").Columns.Count);
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunInsertColumnName));
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunUpdateColumnName));
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunFromColumnName));
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.EtlRunToColumnName));
Assert.IsTrue(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.ValidFromColumnName));
Assert.IsFalse(model.GetTable("dbo", "People").Columns.ContainsKey(configuration.ValidToColumnName));
Assert.AreEqual(1 + 3 + 4 + 2, model.GetTable("dbo", "PeopleHist").Columns.Count);
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey("PeopleHistID"));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.EtlRunInsertColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.EtlRunUpdateColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.EtlRunFromColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.EtlRunToColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.ValidFromColumnName));
Assert.IsTrue(model.GetTable("dbo", "PeopleHist").Columns.ContainsKey(configuration.ValidToColumnName));
Assert.AreEqual(3, model.GetTable("Secondary", "Pet").Columns.Count);
}
private void Init(DwhBuilderConfiguration configuration, RelationalModel model)
{
var builder = new MsSqlDwhBuilder(PluginTopic, "run#1", EtlRunId1)
{
Configuration = configuration,
ConnectionString = TestConnectionString,
Model = model,
};
builder.AddTables(model["dbo"]["People"])
.InputIsCustomProcess(CreatePeople1)
.SetValidFromToDefault()
.SetValidFromToRecordTimestampIfAvailable()
.AddMutators(PeopleMutators)
.RemoveExistingRows(b => b
.MatchByPrimaryKey()
.CompareAllColumnsButValidity())
.DisableConstraintCheck()
.BaseIsCurrentFinalizer(b => b
.MatchByPrimaryKey());
builder.AddTables(model["sec"]["Pet"])
.InputIsCustomProcess(CreatePet1)
.SetValidFromToDefault()
.SetValidFromToRecordTimestampIfAvailable()
.AddMutators(PetMutators)
.RemoveExistingRows(b => b
.MatchByPrimaryKey()
.CompareAllColumnsButValidity())
.DisableConstraintCheck()
.BaseIsCurrentFinalizer(b => b
.MatchByPrimaryKey());
var process = builder.Build();
Context.ExecuteOne(true, process);
var result = ReadRows("dbo", "People");
Assert.AreEqual(5, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 0, ["Name"] = "A", ["FavoritePetId"] = 2, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 1, ["Name"] = "B", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 2, ["Name"] = "C", ["FavoritePetId"] = 3, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 3, ["Name"] = "D", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 4, ["Name"] = "E", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0))
}
});
result = ReadRows("dbo", "People_hist");
Assert.AreEqual(5, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["People_histID"] = 1, ["Id"] = 0, ["Name"] = "A", ["FavoritePetId"] = 2, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["People_histID"] = 2, ["Id"] = 1, ["Name"] = "B", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["People_histID"] = 3, ["Id"] = 2, ["Name"] = "C", ["FavoritePetId"] = 3, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["People_histID"] = 4, ["Id"] = 3, ["Name"] = "D", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["People_histID"] = 5, ["Id"] = 4, ["Name"] = "E", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(2000, 1, 1, 1, 1, 1, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
}
});
result = ReadRows("sec", "Pet");
Assert.AreEqual(3, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 1, ["Name"] = "pet#1", ["OwnerPeopleId"] = 0, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 2, ["Name"] = "pet#2", ["OwnerPeopleId"] = 0, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 3, ["Name"] = "pet#3", ["OwnerPeopleId"] = 2, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0)
}
});
result = ReadRows("dbo", "_temp_People");
Assert.AreEqual(5, result.Count);
result = ReadRows("sec", "_temp_Pet");
Assert.AreEqual(3, result.Count);
}
private void Init(DwhBuilderConfiguration configuration, RelationalModel model)
{
var builder = new MsSqlDwhBuilder(PluginTopic, "run#1", EtlRunId1)
{
Configuration = configuration,
ConnectionString = TestConnectionString,
Model = model,
};
// BaseIsCurrentFinalizer + HasHistoryTable enabled
builder.AddTables(model["dbo"]["Company"])
.InputIsCustomProcess(CreateCompany1)
.SetValidFromToDefault()
.RemoveExistingRows(b => b
.MatchByPrimaryKey()
.CompareAllColumnsButValidity()
.AutoValidityIfValueChanged())
.DisableConstraintCheck()
.BaseIsCurrentFinalizer(b => b
.MatchByPrimaryKey());
var process = builder.Build();
Context.ExecuteOne(true, process);
var result = ReadRows("dbo", "Company");
Assert.AreEqual(4, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 1, ["Name"] = "A", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 2, ["Name"] = "B", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 3, ["Name"] = "C", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 4, ["Name"] = "D", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
}
});
result = ReadRows("dbo", "Company_hist");
Assert.AreEqual(4, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Company_histID"] = 1, ["Id"] = 1, ["Name"] = "A", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Company_histID"] = 2, ["Id"] = 2, ["Name"] = "B", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Company_histID"] = 3, ["Id"] = 3, ["Name"] = "C", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Company_histID"] = 4, ["Id"] = 4, ["Name"] = "D", ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["_ValidFrom"] = new DateTimeOffset(new DateTime(1900, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0)), ["_ValidTo"] = new DateTimeOffset(new DateTime(2500, 1, 1, 0, 0, 0, 0), new TimeSpan(0, 0, 0, 0, 0))
}
});
result = ReadRows("dbo", "_temp_Company");
Assert.AreEqual(4, result.Count);
}
private void Update(DwhBuilderConfiguration configuration, RelationalModel model)
{
var builder = new MsSqlDwhBuilder(PluginTopic, "run#2", EtlRunId2)
{
Configuration = configuration,
ConnectionString = TestConnectionString,
Model = model,
};
builder.AddTables(model["dbo"]["People"])
.InputIsCustomProcess(CreatePeople2)
.AddMutators(PeopleMutators)
.RemoveExistingRows(b => b
.MatchByPrimaryKey()
.CompareAllColumnsButValidity())
.DisableConstraintCheck()
.BaseIsCurrentFinalizer(b => b
.MatchByPrimaryKey());
builder.AddTables(model["sec"]["Pet"])
.InputIsCustomProcess(CreatePet2)
.AddMutators(PetMutators)
.RemoveExistingRows(b => b
.MatchByPrimaryKey()
.CompareAllColumnsButValidity())
.DisableConstraintCheck()
.BaseIsCurrentFinalizer(b => b
.MatchByPrimaryKey());
var process = builder.Build();
Context.ExecuteOne(true, process);
var result = ReadRows("dbo", "People");
Assert.AreEqual(5, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 0, ["Name"] = "A", ["FavoritePetId"] = 2, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 1, ["Name"] = "Bx", ["LastChangedOn"] = new DateTime(2010, 1, 1, 1, 1, 1, 0), ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2022, 2, 2, 2, 2, 2, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 2, ["Name"] = "C", ["FavoritePetId"] = 3, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 3, ["Name"] = "Dx", ["LastChangedOn"] = new DateTime(2010, 1, 1, 1, 1, 1, 0), ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2022, 2, 2, 2, 2, 2, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 4, ["Name"] = "E", ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0), ["EtlRunInsert"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunUpdate"] = new DateTime(2001, 1, 1, 1, 1, 1, 0), ["EtlRunFrom"] = new DateTime(2001, 1, 1, 1, 1, 1, 0)
}
});
result = ReadRows("sec", "Pet");
Assert.AreEqual(4, result.Count);
Assert.That.ExactMatch(result, new List <CaseInsensitiveStringKeyDictionary <object> >()
{
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 1, ["Name"] = "pet#1", ["OwnerPeopleId"] = 0, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 2, ["Name"] = "pet#2x", ["OwnerPeopleId"] = 0, ["LastChangedOn"] = new DateTime(2010, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 3, ["Name"] = "pet#3", ["OwnerPeopleId"] = 2, ["LastChangedOn"] = new DateTime(2000, 1, 1, 1, 1, 1, 0)
},
new CaseInsensitiveStringKeyDictionary <object>()
{
["Id"] = 4, ["Name"] = "pet#4x", ["OwnerPeopleId"] = 0, ["LastChangedOn"] = new DateTime(2010, 1, 1, 1, 1, 1, 0)
}
});
result = ReadRows("dbo", "_temp_People");
Assert.AreEqual(2, result.Count);
result = ReadRows("sec", "_temp_Pet");
Assert.AreEqual(2, result.Count);
}
