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 TestToCSVConverter(bool prefixWithWorkbookName)
{
var loc = _fileLocations[TestFile];
ExcelToCSVFilesConverter converter = new ExcelToCSVFilesConverter();
converter.ExcelFilePattern = loc.Name;
converter.PrefixWithWorkbookName = prefixWithWorkbookName;
var mockProjDir = Mock.Of <ILoadDirectory>(p => p.ForLoading == loc.Directory);
var j = new ThrowImmediatelyDataLoadJob();
j.LoadDirectory = mockProjDir;
converter.Fetch(j, new GracefulCancellationToken());
var file = prefixWithWorkbookName ? loc.Directory.GetFiles("Book1_Sheet1.csv").Single(): loc.Directory.GetFiles("Sheet1.csv").Single();
Assert.IsTrue(file.Exists);
var contents = File.ReadAllText(file.FullName);
Assert.AreEqual(
@"Participant,Score,IsEvil,DateField,DoubleField,MixedField
Bob,3,yes,2001-01-01,0.1,10:30:00
Frank,1.1,no,2001-01-01 10:30:00,0.51,11:30:00
Hank,2.1,no,2002-01-01 11:30:00,0.22,0.1
Shanker,2,yes,2003-01-01 01:30:00,0.10,0.51
Bobboy,2,maybe,2015-09-18,15:09:00,00:03:56", contents.Trim(new[] { ',', '\r', '\n', ' ', '\t' }));
file.Delete();
}
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 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 ExecuteSqlRuntimeTask_InvalidID(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);
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
string sql = @"UPDATE {T:0} Set {C:0} = 1";
IRuntimeTask task;
IProcessTask pt;
var dir = LoadDirectory.CreateDirectoryStructure(new DirectoryInfo(TestContext.CurrentContext.TestDirectory), "ExecuteSqlFileRuntimeTaskTests", true);
var sqlArg = new IArgument[] { Mock.Of <IArgument>(x =>
x.Name == "Sql" &&
x.Value == sql &&
x.GetValueAsSystemType() == sql) };
var args = new RuntimeArgumentCollection(sqlArg, new StageArgs(LoadStage.AdjustRaw, db, dir));
pt = Mock.Of <IProcessTask>(x =>
x.Path == typeof(ExecuteSqlMutilation).FullName &&
x.GetAllArguments() == sqlArg
);
task = new MutilateDataTablesRuntimeTask(pt, args, CatalogueRepository.MEF);
task.Check(new ThrowImmediatelyCheckNotifier());
HICDatabaseConfiguration configuration = new HICDatabaseConfiguration(db.Server);
var job = new ThrowImmediatelyDataLoadJob();
job.RegularTablesToLoad = new List <ITableInfo> {
ti
};
job.LookupTablesToLoad = new List <ITableInfo>();
job.Configuration = configuration;
var ex = Assert.Throws <Exception>(() => task.Run(job, new GracefulCancellationToken()));
StringAssert.Contains("Mutilate failed", ex.Message);
StringAssert.Contains("Failed to find a TableInfo in the load with ID 0", ex.InnerException.Message);
task.LoadCompletedSoDispose(Core.DataLoad.ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
}
public void VarcharMaxer_BadTableNames(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
var tbl = db.CreateTable("Fi ; '`sh", new[]
{
new DatabaseColumnRequest("Da' ,,;ve", new DatabaseTypeRequest(typeof(string), 100)),
new DatabaseColumnRequest("Frrrrr ##' ank", new DatabaseTypeRequest(typeof(int)))
});
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
var maxer = new TableVarcharMaxer();
maxer.TableRegexPattern = new Regex(".*");
maxer.DestinationType = db.Server.GetQuerySyntaxHelper().TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(string), int.MaxValue));
maxer.Initialize(db, LoadStage.AdjustRaw);
maxer.Check(new ThrowImmediatelyCheckNotifier()
{
ThrowOnWarning = true
});
var job = new ThrowImmediatelyDataLoadJob();
job.RegularTablesToLoad = new List <ITableInfo>()
{
ti
};
job.Configuration = new HICDatabaseConfiguration(db.Server, null, null, null);
maxer.Mutilate(job);
switch (dbType)
{
case DatabaseType.MicrosoftSQLServer:
Assert.AreEqual("varchar(max)", tbl.DiscoverColumn("Da' ,,;ve").DataType.SQLType);
break;
case DatabaseType.MySql:
Assert.AreEqual("text", tbl.DiscoverColumn("Da' ,,;ve").DataType.SQLType);
break;
case DatabaseType.Oracle:
Assert.AreEqual("varchar(max)", tbl.DiscoverColumn("Da' ,,;ve").DataType.SQLType);
break;
default:
throw new ArgumentOutOfRangeException("dbType");
}
}
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 Test_FlatFileAttcher_IgnoreColumns()
{
string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
var sw = new StreamWriter(filename);
sw.WriteLine("name,name2,address");
sw.WriteLine("Bob,Munchousain,\"67, franklin\"");
sw.WriteLine("Franky,Hollyw9ood,32 dodgery");
sw.Flush();
sw.Close();
sw.Dispose();
Import(_table, out var ti, out var cols);
var attacher = new AnySeparatorFileAttacher();
attacher.Initialize(LoadDirectory, _database);
attacher.Separator = ",";
attacher.FilePattern = "bob*";
attacher.TableToLoad = ti;
attacher.IgnoreColumns = "address";
var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, null), 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 Test_FlatFileAttcher_AmbiguousDates(DatabaseType type, string val, string attacherCulture, string threadCulture)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo(threadCulture);
string filename = Path.Combine(LoadDirectory.ForLoading.FullName, "bob.csv");
var sw = new StreamWriter(filename);
sw.WriteLine("dob");
sw.WriteLine(val);
sw.Flush();
sw.Close();
sw.Dispose();
var db = GetCleanedServer(type);
var tbl =
db.CreateTable("AmbiguousDatesTestTable",
new [] { new DatabaseColumnRequest("dob", new DatabaseTypeRequest(typeof(DateTime))) }
);
Import(tbl, out TableInfo ti, out _);
var attacher = new AnySeparatorFileAttacher();
attacher.Separator = ",";
attacher.FilePattern = "bob*";
attacher.TableName = tbl.GetRuntimeName();
attacher.Culture = new CultureInfo(attacherCulture);
attacher.Initialize(LoadDirectory, db);
var job = new ThrowImmediatelyDataLoadJob(new HICDatabaseConfiguration(_database.Server, null), ti);
var exitCode = attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
Assert.AreEqual(new DateTime(2001, 1, 27), tbl.GetDataTable().Rows[0][0]);
File.Delete(filename);
tbl.Drop();
}
public void Test_TableToLoad_IDNotInLoadMetadata()
{
var source = new AnySeparatorFileAttacher();
var tiInLoad = new TableInfo(CatalogueRepository, "TableInLoad");
var tiNotInLoad = new TableInfo(CatalogueRepository, "TableNotInLoad");
source.TableToLoad = tiNotInLoad;
var job = new ThrowImmediatelyDataLoadJob(new ThrowImmediatelyDataLoadEventListener()
{
ThrowOnWarning = true
});
job.RegularTablesToLoad = new System.Collections.Generic.List <ITableInfo>(new [] { tiInLoad });
var ex = Assert.Throws <Exception>(() => source.Attach(job, new GracefulCancellationToken()));
StringAssert.IsMatch("FlatFileAttacher TableToLoad was 'TableNotInLoad' \\(ID=\\d+\\) but that table was not one of the tables in the load:'TableInLoad'", ex.Message);
}
private void TestConversionFor(string targetFile, string fileExtensionToConvert, int expectedNumberOfSheets, LoadDirectory directory)
{
FileInfo f = new FileInfo(targetFile);
try
{
Assert.IsTrue(f.Exists);
Assert.IsTrue(f.Length > 100);
ExcelToCSVFilesConverter converter = new ExcelToCSVFilesConverter();
var job = new ThrowImmediatelyDataLoadJob(new ThrowImmediatelyDataLoadEventListener()
{
ThrowOnWarning = true, WriteToConsole = true
});
job.LoadDirectory = directory;
converter.ExcelFilePattern = fileExtensionToConvert;
converter.Fetch(job, new GracefulCancellationToken());
FileInfo[] filesCreated = directory.ForLoading.GetFiles("*.csv");
Assert.AreEqual(expectedNumberOfSheets, filesCreated.Length);
foreach (FileInfo fileCreated in filesCreated)
{
Assert.IsTrue(Regex.IsMatch(fileCreated.Name, "Sheet[0-9].csv"));
Assert.GreaterOrEqual(fileCreated.Length, 100);
fileCreated.Delete();
}
}
finally
{
f.Delete();
}
}
public void Test_IsolateTables_NoRecordsLeftBehind(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* A -> X,A,FF
* ⬍ (collision on Pk X means we migrate A but we must make sure to ship Y too or it will be an orphan)
* X,A,GG
* Y,A,HH ('good' record but must be isolated because referenced foreign key A is going away).
*
**********************************/
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol2");
dt2.Columns.Add("OtherCol3");
dt2.Rows.Add("X", "A", "FF", DBNull.Value); //these are colliding (on pk 'X')
dt2.Rows.Add("X", "A", "GG", DBNull.Value);
dt2.Rows.Add("Y", "A", "HH", DBNull.Value); //must not be left behind
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
Assert.DoesNotThrow(() => mutilator.Mutilate(job));
//parent should now have 0...
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtParent.Rows.Count);
//isolation should have 1
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(1, dtParentIsolation.Rows.Count);
//child table should also be empty
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtChild.Rows.Count);
//child isolation table should have 3 (both bad records and the good record that would otherwise be an orphan in live)
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(3, dtChildIsolation.Rows.Count);
}
public void Test_IsolateTwoTables_Dupliction(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("SeriesInstanceUID");
dt.Columns.Add("Seriesly");
dt.Rows.Add("1.2.3", 1); //collision with children (see d2)
dt.Rows.Add("1.2.3", 2);
dt.Rows.Add("2.3.4", 3); //collision with no children
dt.Rows.Add("2.3.4", 4);
dt.Rows.Add("5.2.1", 2); //unique
dt.Rows.Add("9.9.9", 2); // not a collision but should be deleted because of child collision
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("SeriesInstanceUID");
dt2.Columns.Add("SOPInstanceUID");
dt2.Columns.Add("PatientName");
dt2.Rows.Add("1.2.3", "1.2.1", "Frank");
//these are not a collision because SOPInstanceUID is the pk for this table but they should also be isolated because their series contains duplication
dt2.Rows.Add("1.2.3", "1.2.2", "Dave");
dt2.Rows.Add("5.2.1", "1.1.1", "jjj"); //some more normal records in child
dt2.Rows.Add("5.2.1", "1.1.2", "fff");
dt2.Rows.Add("5.2.1", "1.1.3", "kkk");
dt2.Rows.Add("9.9.9", "1.1.5", "zkk"); //collision on SOPInstanceUID ("1.1.5")
dt2.Rows.Add("9.9.9", "1.1.5", "zzb");
var tblParent = db.CreateTable("Parent", dt, new[]
{
new DatabaseColumnRequest("SeriesInstanceUID", new DatabaseTypeRequest(typeof(string))),
});
var tblChild = db.CreateTable("Child", dt2, new []
{
new DatabaseColumnRequest("SeriesInstanceUID", new DatabaseTypeRequest(typeof(string))),
new DatabaseColumnRequest("SOPInstanceUID", new DatabaseTypeRequest(typeof(string)))
});
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie abot the primary key statuses
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("SeriesInstanceUID"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("SOPInstanceUID"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//checking should fail because it doesn't know how to join tables
var ex = Assert.Throws <Exception>(() => mutilator.Check(new AcceptAllCheckNotifier()));
StringAssert.Contains("join", ex.Message); //should be complaining about missing join infos
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("SeriesInstanceUID")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("SeriesInstanceUID")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now only have "5.2.1"
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(1, dtParent.Rows.Count);
//isolation should have 5 ("1.2.3", "2.3.4" and "9.9.9")
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(5, dtParentIsolation.Rows.Count);
//child table should now only have 3 ("1.1.1", "1.1.2" and "1.1.3")
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(3, dtChild.Rows.Count);
//child isolation table should have 4:
/*
* "1.2.3","1.2.1","Frank"
* "1.2.3","1.2.2","Dave"
* "9.9.9", "1.1.5", "zkk"
* "9.9.9", "1.1.5", "zzb"
*/
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(4, dtChildIsolation.Rows.Count);
}
public void Test_IsolateTwoTables_MutipleConflictingColumns(DatabaseType dbType, bool whitespace)
{
var db = GetCleanedServer(dbType);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1); //these are colliding on pk "A" with different values of "OtherCol"
dt.Rows.Add(whitespace? "A " :"A", 2);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol2");
dt2.Columns.Add("OtherCol3");
dt2.Rows.Add(whitespace ? "X ": "X", "A", "FF", DBNull.Value); //these are colliding on pk "X" with different values of "OtherCol2"
dt2.Rows.Add("X", whitespace ? "A " :"A", "GG", DBNull.Value);
dt2.Rows.Add(whitespace ? "X ": "X", "A", "FF", "HH"); //these are colliding on pk "X" with different values of "OtherCol2"
dt2.Rows.Add("X", whitespace ? "A " :"A", "GG", "HH");
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now be empty
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtParent.Rows.Count);
//isolation should have 2
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(2, dtParentIsolation.Rows.Count);
//child table should also be empty
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtChild.Rows.Count);
//child isolation table should have 4:
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(4, dtChildIsolation.Rows.Count);
}
public void Test_IsolateTwoTables_IntKeys(DatabaseType dbType)
{
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* 4 -> 8,4,1
* ⬍ (collision causes pk 4 to be migrated)
* 8,4,2
* 5 -> 9,5,1 (good record with no collisions anywhere)
**********************************/
var db = GetCleanedServer(dbType);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add(4, 1);
dt.Rows.Add(5, 2);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol");
dt2.Rows.Add(8, 4, 1); //these are colliding on pk 8 which will ship full hierarchy of parent pk 4 to the isolation table
dt2.Rows.Add(8, 4, 2);
dt2.Rows.Add(9, 5, 1); //good record with no collisions, should not be deleted!
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//make sure FAnsi made an int column
Assert.AreEqual(typeof(int), tblParent.DiscoverColumn("Pk").GetGuesser().Guess.CSharpType);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now have 1
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(1, dtParent.Rows.Count);
//isolation should have 1
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(1, dtParentIsolation.Rows.Count);
//child table should have the good 1
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(1, dtChild.Rows.Count);
//child isolation table should have 2:
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(2, dtChildIsolation.Rows.Count);
}
public void TestMerge(DatabaseType databaseType)
{
//microsoft one gets called for free in test setup (see base class)
if (databaseType != DatabaseType.MicrosoftSQLServer)
{
SetupFromTo(databaseType);
}
var dt = new DataTable();
var colName = new DataColumn("Name", typeof(string));
var colAge = new DataColumn("Age", typeof(int));
dt.Columns.Add(colName);
dt.Columns.Add(colAge);
dt.Columns.Add("Postcode", typeof(string));
//Data in live awaiting toTbl be updated
dt.Rows.Add(new object[] { "Dave", 18, "DD3 1AB" });
dt.Rows.Add(new object[] { "Dave", 25, "DD1 1XS" });
dt.Rows.Add(new object[] { "Mango", 32, DBNull.Value });
dt.Rows.Add(new object[] { "Filli", 32, "DD3 78L" });
dt.Rows.Add(new object[] { "Mandrake", 32, DBNull.Value });
dt.PrimaryKey = new[] { colName, colAge };
var toTbl = To.CreateTable("ToTable", dt);
Assert.IsTrue(toTbl.DiscoverColumn("Name").IsPrimaryKey);
Assert.IsTrue(toTbl.DiscoverColumn("Age").IsPrimaryKey);
Assert.IsFalse(toTbl.DiscoverColumn("Postcode").IsPrimaryKey);
dt.Rows.Clear();
//new data being loaded
dt.Rows.Add(new object[] { "Dave", 25, "DD1 1PS" }); //update toTbl change postcode toTbl "DD1 1PS"
dt.Rows.Add(new object[] { "Chutney", 32, DBNull.Value }); //new insert Chutney
dt.Rows.Add(new object[] { "Mango", 32, DBNull.Value }); //ignored because already present in dataset
dt.Rows.Add(new object[] { "Filli", 32, DBNull.Value }); //update from "DD3 78L" null
dt.Rows.Add(new object[] { "Mandrake", 32, "DD1 1PS" }); //update from null toTbl "DD1 1PS"
dt.Rows.Add(new object[] { "Mandrake", 31, "DD1 1PS" }); // insert because Age is unique (and part of pk)
var fromTbl = From.CreateTable(DatabaseName + "_ToTable_STAGING", dt);
//import the toTbl table as a TableInfo
TableInfo ti;
ColumnInfo[] cis;
var cata = Import(toTbl, out ti, out cis);
//put the backup trigger on the live table (this will also create the needed hic_ columns etc)
var triggerImplementer = new TriggerImplementerFactory(databaseType).Create(toTbl);
triggerImplementer.CreateTrigger(new ThrowImmediatelyCheckNotifier());
var configuration = new MigrationConfiguration(From, LoadBubble.Staging, LoadBubble.Live,
new FixedStagingDatabaseNamer(toTbl.Database.GetRuntimeName(), fromTbl.Database.GetRuntimeName()));
var lmd = new LoadMetadata(CatalogueRepository);
cata.LoadMetadata_ID = lmd.ID;
cata.SaveToDatabase();
var migrationHost = new MigrationHost(From, To, configuration, new HICDatabaseConfiguration(lmd));
//set SetUp a logging task
var logServer = new ServerDefaults(CatalogueRepository).GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID);
var logManager = new LogManager(logServer);
logManager.CreateNewLoggingTaskIfNotExists("CrossDatabaseMergeCommandTest");
var dli = logManager.CreateDataLoadInfo("CrossDatabaseMergeCommandTest", "tests", "running test", "", true);
var job = new ThrowImmediatelyDataLoadJob();
job.DataLoadInfo = dli;
job.RegularTablesToLoad = new List <ITableInfo>(new[] { ti });
migrationHost.Migrate(job, new GracefulCancellationToken());
var resultantDt = toTbl.GetDataTable();
Assert.AreEqual(7, resultantDt.Rows.Count);
AssertRowEquals(resultantDt, "Dave", 25, "DD1 1PS");
AssertRowEquals(resultantDt, "Chutney", 32, DBNull.Value);
AssertRowEquals(resultantDt, "Mango", 32, DBNull.Value);
AssertRowEquals(resultantDt, "Filli", 32, DBNull.Value);
AssertRowEquals(resultantDt, "Mandrake", 32, "DD1 1PS");
AssertRowEquals(resultantDt, "Mandrake", 31, "DD1 1PS");
AssertRowEquals(resultantDt, "Dave", 18, "DD3 1AB");
var archival = logManager.GetArchivalDataLoadInfos("CrossDatabaseMergeCommandTest", new CancellationToken());
var log = archival.First();
Assert.AreEqual(dli.ID, log.ID);
Assert.AreEqual(2, log.TableLoadInfos.Single().Inserts);
Assert.AreEqual(3, log.TableLoadInfos.Single().Updates);
}
public void Test_IsolateTwoTables_MultipleCollidingChildren(DatabaseType dbType)
{
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* A -> X,A,1
* ⬍ (collision causes A delete)
* X,A,2
* -> Y,A,1
* ⬍ (collision causes repeated attempt to delete A)
* Y,A,2
*
**********************************/
var db = GetCleanedServer(dbType);
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol");
dt2.Rows.Add("X", "A", 1); //these are colliding on pk "X" which will ship A to the isolation table
dt2.Rows.Add("X", "A", 2);
dt2.Rows.Add("Y", "A", 2); //these are colliding on pk "Y" but also reference A (which has already been shipped to isolation)
dt2.Rows.Add("Y", "A", 1);
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now have 0...
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtParent.Rows.Count);
//isolation should have 1
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(1, dtParentIsolation.Rows.Count);
//child table should also be empty
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtChild.Rows.Count);
//child isolation table should have 4:
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(4, dtChildIsolation.Rows.Count);
}
public void Test_IsolateTables_Orphans(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* A <> X,B,FF
* ⬍ (collision on pk X causes lookup of Fk B which does not exist in Parent)
* X,B,GG
*
**********************************/
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1); //these are colliding on pk "A" with different values of "OtherCol"
dt.Rows.Add("A", 2);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol2");
dt2.Columns.Add("OtherCol3");
dt2.Rows.Add("X", "B", "FF", DBNull.Value); //these are colliding (on pk 'X') and also orphans (B does not appear in parent table dt)
dt2.Rows.Add("X", "B", "GG", DBNull.Value);
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
var ex = Assert.Throws <Exception>(() => mutilator.Mutilate(job));
Assert.AreEqual("Primary key value not found for X", ex.Message);
}
public void Test_IsolateTables_NullForeignKey(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* A <> X,A,FF
* ⬍ (collision on pk X causes lookup of A but then we throw because the second record has no Fk listed)
* X,NULL,GG
*
**********************************/
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1); //these are colliding on pk "A" with different values of "OtherCol"
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol2");
dt2.Columns.Add("OtherCol3");
dt2.Rows.Add("X", "A", "FF", DBNull.Value); //these are colliding (on pk 'X'). "A" exists but the null value in the other record is a problem
dt2.Rows.Add("X", DBNull.Value, "GG", DBNull.Value);
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now have 0...
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtParent.Rows.Count);
//isolation should have 1 (A)
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(1, dtParentIsolation.Rows.Count);
AssertContains(dtParentIsolation, "A", true, 0);
//child table should have the null record only
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(1, dtChild.Rows.Count);
AssertContains(dtChild, "X", DBNull.Value, "GG", DBNull.Value);
//child isolation table should have 1 record (the X,A,FF)
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(1, dtChildIsolation.Rows.Count);
AssertContains(dtChildIsolation, "X", "A", "FF", DBNull.Value, 0);
}
public void Test_IsolateTables_AmbiguousFk(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
/***************************************
* Parent(Pk) Child (Pk2,Fk,OtherCol)
* A -> X,A,FF
* ⬍ (collision on pk X is a big problem because they list different fks!)
* B -> X,B,GG
* Y,B,AA (good record, if we have to isolate both A and B we had better isolate this too)
**********************************/
//Create a table in 'RAW' (has no constraints)
var dt = new DataTable();
dt.Columns.Add("Pk");
dt.Columns.Add("OtherCol");
dt.Rows.Add("A", 1);
dt.Rows.Add("B", 2);
//Create a table in 'RAW' (has no constraints)
var dt2 = new DataTable();
dt2.Columns.Add("Pk2");
dt2.Columns.Add("Fk");
dt2.Columns.Add("OtherCol2");
dt2.Columns.Add("OtherCol3");
dt2.Rows.Add("X", "A", "FF", DBNull.Value); //these are colliding (on pk 'X') but list two different (but existing) pks!
dt2.Rows.Add("X", "B", "GG", DBNull.Value);
dt2.Rows.Add("Y", "B", "AA", DBNull.Value); //good record but has to be isolated because it is child of B which is involved in the above collision
var tblParent = db.CreateTable("Parent", dt);
var tblChild = db.CreateTable("Child", dt2);
//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 parentTableInfo;
ColumnInfo[] parentColumnInfosCreated;
TableInfo childTableInfo;
ColumnInfo[] childColumnInfosCreated;
Import(tblParent, out parentTableInfo, out parentColumnInfosCreated);
Import(tblChild, out childTableInfo, out childColumnInfosCreated);
//make sure RDMP knows joins start with this table
parentTableInfo.IsPrimaryExtractionTable = true;
parentTableInfo.SaveToDatabase();
//lie about the primary key statuses (to simulate live)
var seriesInstanceUIdCol =
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk"));
seriesInstanceUIdCol.IsPrimaryKey = true;
seriesInstanceUIdCol.SaveToDatabase();
var sopInstanceUIdCol = childColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk2"));
sopInstanceUIdCol.IsPrimaryKey = true;
sopInstanceUIdCol.SaveToDatabase();
//Create a new mutilator for these two tables
var mutilator = GetMutilator(db, parentTableInfo, childTableInfo);
//tell RDMP about how to join tables
new JoinInfo(CatalogueRepository, childColumnInfosCreated.Single(
c => c.GetRuntimeName().Equals("Fk")),
parentColumnInfosCreated.Single(c => c.GetRuntimeName().Equals("Pk")),
ExtractionJoinType.Right, null);
//now that we have a join it should pass checks
mutilator.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(db.Server, new ReturnSameString());
var job = new ThrowImmediatelyDataLoadJob(config, parentTableInfo, childTableInfo);
mutilator.Initialize(db, LoadStage.AdjustRaw);
mutilator.Mutilate(job);
//parent should now have 0...
var dtParent = parentTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtParent.Rows.Count);
//isolation should have 2 (A and B)
var dtParentIsolation = db.ExpectTable("Parent_Isolation").GetDataTable();
Assert.AreEqual(2, dtParentIsolation.Rows.Count);
//child table should also be empty
var dtChild = childTableInfo.Discover(DataAccessContext.InternalDataProcessing).GetDataTable();
Assert.AreEqual(0, dtChild.Rows.Count);
//child isolation table should have 3 (both bad records and the good record that would otherwise be an orphan in live)
var dtChildIsolation = db.ExpectTable("Child_Isolation").GetDataTable();
Assert.AreEqual(3, dtChildIsolation.Rows.Count);
}
public void CopyFiles()
{
var sourceDir = new DirectoryInfo(TestContext.CurrentContext.TestDirectory).CreateSubdirectory("subdir");
var targetDir = new DirectoryInfo(TestContext.CurrentContext.TestDirectory).CreateSubdirectory("loaddir");
//make sure target is empty
foreach (var f in targetDir.GetFiles())
{
f.Delete();
}
var originpath = Path.Combine(sourceDir.FullName, "myFile.txt");
File.WriteAllText(originpath, "fish");
var job = new ThrowImmediatelyDataLoadJob();
var mockProjectDirectory = Mock.Of <ILoadDirectory>(p => p.ForLoading == targetDir);
job.LoadDirectory = mockProjectDirectory;
//Create the provider
var provider = new ImportFilesDataProvider();
//it doesn't know what to load yet
Assert.Throws <Exception>(() => provider.Check(new ThrowImmediatelyCheckNotifier()));
//now it does
provider.DirectoryPath = sourceDir.FullName;
//but it doesn't have a file pattern
Assert.Throws <Exception>(() => provider.Check(new ThrowImmediatelyCheckNotifier()));
//now it does but its not a matching one
provider.FilePattern = "cannonballs.bat";
//either way it passes checking
Assert.DoesNotThrow(() => provider.Check(new ThrowImmediatelyCheckNotifier()));
//execute the provider
provider.Fetch(job, new GracefulCancellationToken());
//destination is empty because nothing matched
Assert.IsEmpty(targetDir.GetFiles());
//give it correct pattern
provider.FilePattern = "*.txt";
//execute the provider
provider.Fetch(job, new GracefulCancellationToken());
//both files should exist
Assert.AreEqual(1, targetDir.GetFiles().Count());
Assert.AreEqual(1, sourceDir.GetFiles().Count());
//simulate load failure
provider.LoadCompletedSoDispose(ExitCodeType.Abort, new ThrowImmediatelyDataLoadJob());
//both files should exist
Assert.AreEqual(1, targetDir.GetFiles().Count());
Assert.AreEqual(1, sourceDir.GetFiles().Count());
//simulate load success
provider.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadJob());
//both files should exist because Delete on success is false
Assert.AreEqual(1, targetDir.GetFiles().Count());
Assert.AreEqual(1, sourceDir.GetFiles().Count());
//change behaviour to delete on successful data loads
provider.DeleteFilesOnsuccessfulLoad = true;
//simulate load failure
provider.LoadCompletedSoDispose(ExitCodeType.Error, new ThrowImmediatelyDataLoadJob());
//both files should exist
Assert.AreEqual(1, targetDir.GetFiles().Count());
Assert.AreEqual(1, sourceDir.GetFiles().Count());
//simulate load success
provider.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadJob());
//only forLoading file should exist (in real life that one would be handled by archivng already)
Assert.AreEqual(1, targetDir.GetFiles().Count());
Assert.AreEqual(0, sourceDir.GetFiles().Count());
}
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();
}
