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 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 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);
}