public void Dilution_WithNamer_Test()
{
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
DataTable dt = new DataTable();
dt.Columns.Add("Bob");
dt.Rows.Add(new[] { "Fish" });
var tbl = db.CreateTable("DilutionNamerTest", dt);
Import(tbl, out var ti, out var cols);
tbl.Rename("AAAA");
var namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "AAAA");
var discarded = new PreLoadDiscardedColumn(CatalogueRepository, ti, "Bob");
discarded.SqlDataType = "varchar(10)";
discarded.Destination = DiscardedColumnDestination.Dilute;
discarded.SaveToDatabase();
var dilution = new Dilution();
dilution.ColumnToDilute = discarded;
dilution.Operation = typeof(CrushToBitFlag);
dilution.Initialize(db, LoadStage.AdjustStaging);
dilution.Check(new ThrowImmediatelyCheckNotifier());
var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(db.Server, namer), ti);
dilution.Mutilate(job);
}
public void Test_IsolateSingleTableWithSchema_Duplication(string schemaExpression, bool includeQualifiers)
{
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("A");
dt.Columns.Add("B");
dt.Rows.Add("Fish", 1);
dt.Rows.Add("Fish", 2);
dt.Rows.Add("Fish", 3);
dt.Rows.Add("Frank", 2);
dt.Rows.Add("Candy", 2);
var tbl = db.CreateTable("MyCoolTable2", dt);
//import the table and make A look like a primary key to the metadata layer (and A would be pk in LIVE but not in RAW ofc)
Import(tbl, out var tableInfoCreated, out var columnInfosCreated);
var syntax = db.Server.GetQuerySyntaxHelper();
tableInfoCreated.Name =
(includeQualifiers ? syntax.EnsureWrapped(db.GetRuntimeName()) : db.GetRuntimeName())
+ schemaExpression +
(includeQualifiers ? syntax.EnsureWrapped(tbl.GetRuntimeName()) : tbl.GetRuntimeName());
tableInfoCreated.SaveToDatabase();
foreach (ColumnInfo column in columnInfosCreated)
{
column.Name = tableInfoCreated.Name + "." +
(includeQualifiers ? syntax.EnsureWrapped(column.GetRuntimeName()) : column.GetRuntimeName());
column.SaveToDatabase();
}
//lie about the primary key status
var a = columnInfosCreated.Single(c => c.GetRuntimeName().Equals("A"));
a.IsPrimaryKey = true;
a.SaveToDatabase();
var mutilator = GetMutilator(db, tableInfoCreated);
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "MyCoolTable2"));
var job = new ThrowImmediatelyDataLoadJob(config, tableInfoCreated);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
dt = tbl.GetDataTable();
Assert.AreEqual(2, dt.Rows.Count);
var dtIsolation = tbl.Database.ExpectTable("MyCoolTable2_Isolation").GetDataTable();
Assert.AreEqual(3, dtIsolation.Rows.Count);
}
public void TestTableInfo(bool usenamer)
{
string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
var sw = new StreamWriter(filename);
sw.WriteLine("name,name2");
sw.WriteLine("Bob,Munchousain");
sw.WriteLine("Franky,Hollyw9ood");
sw.Flush();
sw.Close();
sw.Dispose();
TableInfo ti;
ColumnInfo[] cols;
Import(_table, out ti, out cols);
var attacher = new AnySeparatorFileAttacher();
attacher.Initialize(LoadDirectory, _database);
attacher.Separator = ",";
attacher.FilePattern = "bob*";
attacher.TableToLoad = ti;
INameDatabasesAndTablesDuringLoads namer = null;
if (usenamer)
{
_table.Rename("AAA");
namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(_database, "AAA");
}
var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, namer), ti);
var exitCode = attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
using (var con = _database.Server.GetConnection())
{
con.Open();
var r = _database.Server.GetCommand("Select name,name2 from " + _table.GetRuntimeName(), con).ExecuteReader();
Assert.IsTrue(r.Read());
Assert.AreEqual("Bob", r["name"]);
Assert.AreEqual("Munchousain", r["name2"]);
Assert.IsTrue(r.Read());
Assert.AreEqual("Franky", r["name"]);
Assert.AreEqual("Hollyw9ood", r["name2"]);
}
attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
File.Delete(filename);
}
public void ExecuteSqlFileRuntimeTask_ValidID_CustomNamer(DatabaseType dbType)
{
var dt = new DataTable();
dt.Columns.Add("Lawl");
dt.Rows.Add(new object[] { 2 });
var db = GetCleanedServer(dbType, true);
var tbl = db.CreateTable("Fish", dt);
var tableName = "AAAAAAA";
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
FileInfo f = new FileInfo(Path.Combine(TestContext.CurrentContext.TestDirectory, "Bob.sql"));
File.WriteAllText(f.FullName, @"UPDATE {T:" + ti.ID + "} Set {C:" + cols[0].ID + "} = 1");
tbl.Rename(tableName);
//we renamed the table to simulate RAW, confirm TableInfo doesn't think it exists
Assert.IsFalse(ti.Discover(DataAccessContext.InternalDataProcessing).Exists());
var pt = Mock.Of <IProcessTask>(x => x.Path == f.FullName);
var dir = LoadDirectory.CreateDirectoryStructure(new DirectoryInfo(TestContext.CurrentContext.TestDirectory), "ExecuteSqlFileRuntimeTaskTests", true);
var task = new ExecuteSqlFileRuntimeTask(pt, new RuntimeArgumentCollection(new IArgument[0], new StageArgs(LoadStage.AdjustRaw, db, dir)));
task.Check(new ThrowImmediatelyCheckNotifier());
//create a namer that tells the user
var namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, tableName);
HICDatabaseConfiguration configuration = new HICDatabaseConfiguration(db.Server, namer);
IDataLoadJob job = Mock.Of <IDataLoadJob>(x =>
x.RegularTablesToLoad == new List <ITableInfo> {
ti
} &&
x.LookupTablesToLoad == new List <ITableInfo>() &&
x.Configuration == configuration);
task.Run(job, new GracefulCancellationToken());
Assert.AreEqual(1, tbl.GetDataTable().Rows[0][0]);
tbl.Drop();
}
public void TestPreExecutionChecker_TablesDontExist()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("Imaginary");
Assert.IsFalse(tbl.Exists());
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl);
var checker = new PreExecutionChecker(lmd, new HICDatabaseConfiguration(DiscoveredDatabaseICanCreateRandomTablesIn.Server));
var ex = Assert.Throws <Exception>(() => checker.Check(new ThrowImmediatelyCheckNotifier()));
StringAssert.IsMatch("Table '.*Imaginary.*' does not exist", ex.Message);
}
public void TestPreExecutionChecker_TablesDontExist()
{
var db = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
var tbl = db.ExpectTable("Imaginary");
Assert.IsFalse(tbl.Exists());
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl);
var checker = new PreExecutionChecker(lmd, new HICDatabaseConfiguration(db.Server));
var ex = Assert.Throws <Exception>(() => checker.Check(new ThrowImmediatelyCheckNotifier()));
StringAssert.IsMatch("Table '.*Imaginary.*' does not exist", ex.Message);
}
public void SafePrimaryKeyCollisionResolverMutilationTests_WithDatabaseNamer_RecordsDeleted(DatabaseType dbType)
{
var db = GetCleanedServer(dbType, true);
DataTable dt = new DataTable();
dt.Columns.Add("PK");
dt.Columns.Add("ResolveOn");
dt.Columns.Add("AnotherCol");
dt.Rows.Add(1, null, "cat");
dt.Rows.Add(1, "fish", "flop");
dt.Rows.Add(2, "fish", "flop");
dt.Rows.Add(3, "dave", "franl");
var tbl = db.CreateTable("MyTable", dt);
TableInfo ti;
ColumnInfo[] cis;
Import(tbl, out ti, out cis);
tbl.Rename("AAAA");
var namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "AAAA");
var pk = cis.Single(c => c.GetRuntimeName().Equals("PK"));
pk.IsPrimaryKey = true;
pk.SaveToDatabase();
var resolveOn = cis.Single(c => c.GetRuntimeName().Equals("ResolveOn"));
var mutilation = new SafePrimaryKeyCollisionResolverMutilation();
mutilation.ColumnToResolveOn = resolveOn;
mutilation.PreferLargerValues = true;
mutilation.PreferNulls = true;
mutilation.Initialize(db, LoadStage.AdjustRaw);
mutilation.Mutilate(new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(db.Server, namer), ti));
Assert.AreEqual(3, tbl.GetRowCount());
var result = tbl.GetDataTable();
//if you prefer nulls you shouldn't want this one
Assert.AreEqual(0, result.Rows.Cast <DataRow>().Count(r => (int)r["PK"] == 1 && r["ResolveOn"] as string == "fish" && r["AnotherCol"] as string == "flop"));
//if you prefer nulls you should have this one
Assert.AreEqual(1, result.Rows.Cast <DataRow>().Count(r => (int)r["PK"] == 1 && r["ResolveOn"] == DBNull.Value && r["AnotherCol"] as string == "cat"));
}
public void Test_IsolateSingleTable_Dupliction(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("A");
dt.Columns.Add("B");
dt.Rows.Add("Fish", 1);
dt.Rows.Add("Fish", 2);
dt.Rows.Add("Fish", 3);
dt.Rows.Add("Frank", 2);
dt.Rows.Add("Candy", 2);
var tbl = db.CreateTable("MyCoolTable2", dt);
//import the table and make A look like a primary key to the metadata layer (and A would be pk in LIVE but not in RAW ofc)
TableInfo tableInfoCreated;
ColumnInfo[] columnInfosCreated;
Import(tbl, out tableInfoCreated, out columnInfosCreated);
//lie abot the primary key status
var a = columnInfosCreated.Single(c => c.GetRuntimeName().Equals("A"));
a.IsPrimaryKey = true;
a.SaveToDatabase();
var mutilator = GetMutilator(db, tableInfoCreated);
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "MyCoolTable2"));
var job = new ThrowImmediatelyDataLoadJob(config, tableInfoCreated);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
dt = tbl.GetDataTable();
Assert.AreEqual(2, dt.Rows.Count);
var dtIsolation = tbl.Database.ExpectTable("MyCoolTable2_Isolation").GetDataTable();
Assert.AreEqual(3, dtIsolation.Rows.Count);
}
public void TestPreExecutionChecker_TableIsTableValuedFunction()
{
TestableTableValuedFunction f = new TestableTableValuedFunction();
f.Create(DiscoveredDatabaseICanCreateRandomTablesIn, CatalogueRepository);
var tbl = f.TableInfoCreated.Discover(DataAccessContext.DataLoad);
Assert.IsTrue(tbl.Exists());
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(f.TableInfoCreated);
var checker = new PreExecutionChecker(lmd, new HICDatabaseConfiguration(DiscoveredDatabaseICanCreateRandomTablesIn.Server));
var ex = Assert.Throws <Exception>(() => checker.Check(new ThrowImmediatelyCheckNotifier()));
StringAssert.IsMatch("Table '.*MyAwesomeFunction.*' is a TableValuedFunction", ex.Message);
}
private void RunAttachStageWithNormalJob(RemoteTableAttacher attacher, DiscoveredDatabase db)
{
//the table to get data from
attacher.RemoteTableName = "table1";
attacher.RAWTableName = "table2";
attacher.Check(new ThrowImmediatelyCheckNotifier());
attacher.Initialize(null, db);
using (var dt = new DataTable())
{
dt.Columns.Add("Col1");
dt.Rows.Add("fff");
var tbl1 = db.CreateTable("table1", dt);
var tbl2 = db.CreateTable("table2", new [] { new DatabaseColumnRequest("Col1", new DatabaseTypeRequest(typeof(string), 5)) });
Assert.AreEqual(1, tbl1.GetRowCount());
Assert.AreEqual(0, tbl2.GetRowCount());
var logManager = new LogManager(new DiscoveredServer(UnitTestLoggingConnectionString));
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl2);
Mock.Get(lmd).Setup(p => p.CatalogueRepository).Returns(CatalogueRepository);
logManager.CreateNewLoggingTaskIfNotExists(lmd.GetDistinctLoggingTask());
var dbConfiguration = new HICDatabaseConfiguration(lmd, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "table2"));
var job = new DataLoadJob(RepositoryLocator, "test job", logManager, lmd, new TestLoadDirectory(), new ThrowImmediatelyDataLoadEventListener(), dbConfiguration);
job.StartLogging();
attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(1, tbl1.GetRowCount());
Assert.AreEqual(1, tbl2.GetRowCount());
}
}
private void RunAttachStageWithLoadProgressJob(RemoteTableAttacher attacher, DiscoveredDatabase db,
bool mismatchProgress)
{
var syntax = db.Server.GetQuerySyntaxHelper();
//the table to get data from
attacher.RemoteSelectSQL = $"SELECT * FROM table1 WHERE {syntax.EnsureWrapped("DateCol")} >= @startDate AND {syntax.EnsureWrapped("DateCol")} <= @endDate";
attacher.RAWTableName = "table2";
attacher.Check(new ThrowImmediatelyCheckNotifier());
attacher.Initialize(null, db);
using (var dt = new DataTable())
{
dt.Columns.Add("Col1");
dt.Columns.Add("DateCol");
dt.Rows.Add("fff", new DateTime(2000, 1, 1));
dt.Rows.Add("fff", new DateTime(2001, 1, 1));
dt.Rows.Add("fff", new DateTime(2002, 1, 1));
var tbl1 = db.CreateTable("table1", dt);
var tbl2 = db.CreateTable("table2", new []
{
new DatabaseColumnRequest("Col1", new DatabaseTypeRequest(typeof(string), 5)),
new DatabaseColumnRequest("DateCol", new DatabaseTypeRequest(typeof(DateTime)))
});
Assert.AreEqual(3, tbl1.GetRowCount());
Assert.AreEqual(0, tbl2.GetRowCount());
var logManager = new LogManager(new DiscoveredServer(UnitTestLoggingConnectionString));
var lmd = RdmpMockFactory.Mock_LoadMetadataLoadingTable(tbl2);
Mock.Get(lmd).Setup(p => p.CatalogueRepository).Returns(CatalogueRepository);
logManager.CreateNewLoggingTaskIfNotExists(lmd.GetDistinctLoggingTask());
var lp = new LoadProgress(CatalogueRepository, new LoadMetadata(CatalogueRepository, "ffffff"));
lp.OriginDate = new DateTime(2001, 1, 1);
attacher.Progress = lp;
attacher.ProgressUpdateStrategy = new DataLoadProgressUpdateInfo()
{
Strategy = DataLoadProgressUpdateStrategy.DoNothing
};
var dbConfiguration = new HICDatabaseConfiguration(lmd, RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "table2"));
var job = new ScheduledDataLoadJob(RepositoryLocator, "test job", logManager, lmd, new TestLoadDirectory(), new ThrowImmediatelyDataLoadEventListener(), dbConfiguration);
job.LoadProgress = mismatchProgress
? new LoadProgress(CatalogueRepository, new LoadMetadata(CatalogueRepository, "ffsdf"))
: lp;
job.DatesToRetrieve = new List <DateTime> {
new DateTime(2001, 01, 01)
};
job.StartLogging();
attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(3, tbl1.GetRowCount());
Assert.AreEqual(mismatchProgress ? 0 : 1, tbl2.GetRowCount());
}
}
public void TestCoalescer_RampantNullness(DatabaseType type, bool useCustomNamer)
{
var db = GetCleanedServer(type, "TestCoalescer");
int batchCount = 1000;
DataTable dt = new DataTable("TestCoalescer_RampantNullness");
dt.Columns.Add("pk");
dt.Columns.Add("f1");
dt.Columns.Add("f2");
dt.Columns.Add("f3");
dt.Columns.Add("f4");
Random r = new Random();
for (int i = 0; i < batchCount; i++)
{
int randInt = r.Next(250);
int randCompleteness = r.Next(4);
dt.Rows.Add(new object[] { randInt, randInt, randInt, randInt, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, randInt, randInt });
if (randCompleteness >= 1)
{
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, randInt, randInt, randInt });
}
if (randCompleteness >= 2)
{
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, DBNull.Value, randInt, randInt });
}
if (randCompleteness >= 3)
{
dt.Rows.Add(new object[] { randInt, randInt, randInt, DBNull.Value, DBNull.Value });
dt.Rows.Add(new object[] { randInt, randInt, randInt, DBNull.Value, randInt });
dt.Rows.Add(new object[] { randInt, randInt, randInt, randInt, DBNull.Value });
dt.Rows.Add(new object[] { randInt, DBNull.Value, DBNull.Value, DBNull.Value, DBNull.Value });
}
}
var tbl = db.CreateTable(dt.TableName, dt);
var importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out var tableInfo, out var colInfos);
//lie about what hte primary key is because this component is designed to run in the RAW environment and we are simulating a LIVE TableInfo (correctly)
var pk = colInfos.Single(c => c.GetRuntimeName().Equals("pk"));
pk.IsPrimaryKey = true;
pk.SaveToDatabase();
INameDatabasesAndTablesDuringLoads namer = null;
if (useCustomNamer)
{
tbl.Rename("AAAA");
namer = RdmpMockFactory.Mock_INameDatabasesAndTablesDuringLoads(db, "AAAA");
}
HICDatabaseConfiguration configuration = new HICDatabaseConfiguration(db.Server, namer);
var coalescer = new Coalescer();
coalescer.TableRegexPattern = new Regex(".*");
coalescer.CreateIndex = true;
coalescer.Initialize(db, LoadStage.AdjustRaw);
var job = new ThrowImmediatelyDataLoadJob(configuration, tableInfo);
coalescer.Mutilate(job);
var dt2 = tbl.GetDataTable();
foreach (DataRow row in dt2.Rows)
{
Assert.AreNotEqual(DBNull.Value, row["f1"]);
Assert.AreNotEqual(DBNull.Value, row["f2"]);
Assert.AreNotEqual(DBNull.Value, row["f3"]);
Assert.AreNotEqual(DBNull.Value, row["f4"]);
}
db.Drop();
}
