public void CompositeLookupTest()
{
TableInfo fkTable = null;
TableInfo pkTable = null;
ColumnInfo desc = null;
ColumnInfo fk = null;
ColumnInfo pk = null;
ColumnInfo fk2 = null;
ColumnInfo pk2 = null;
Lookup lookup = null;
LookupCompositeJoinInfo composite = null;
try
{
//table 1 - the dataset table, it has 2 foreign keys e.g. TestCode, Healthboard
fkTable = new TableInfo(CatalogueRepository, "UnitTest_Biochemistry");
fk = new ColumnInfo(CatalogueRepository, "UnitTest_BCTestCode", "int", fkTable);
fk2 = new ColumnInfo(CatalogueRepository, "UnitTest_BCHealthBoard", "int", fkTable);
//table 2 - the lookup table, it has 2 primary keys e.g. TestCode,Healthboard and 1 description e.g. TestDescription (the Healthboard makes it a composite JOIN which allows for the same TestCode being mapped to a different discription in Tayside vs Fife (healthboard)
pkTable = new TableInfo(CatalogueRepository, "UnitTest_BiochemistryLookup");
pk = new ColumnInfo(CatalogueRepository, "UnitTest_TestCode", "int", pkTable);
pk2 = new ColumnInfo(CatalogueRepository, "UnitTest_Healthboard", "int", pkTable);
desc = new ColumnInfo(CatalogueRepository, "UnitTest_TestDescription", "int", pkTable);
lookup = new Lookup(CatalogueRepository, desc, fk, pk, ExtractionJoinType.Left, null);
Assert.AreEqual(lookup.PrimaryKey.Name, pk.Name);
Assert.AreEqual(lookup.PrimaryKey.ID, pk.ID);
Assert.AreEqual(lookup.ForeignKey.Name, fk.Name);
Assert.AreEqual(lookup.ForeignKey.ID, fk.ID);
Assert.AreEqual(lookup.Description.Name, desc.Name);
Assert.AreEqual(lookup.Description.ID, desc.ID);
//Create the composite lookup
composite = new LookupCompositeJoinInfo(CatalogueRepository, lookup, fk2, pk2);
Assert.AreEqual(composite.OriginalLookup_ID, lookup.ID);
Assert.AreEqual(composite.PrimaryKey.ID, pk2.ID);
Assert.AreEqual(composite.PrimaryKey_ID, pk2.ID);
Assert.AreEqual(composite.PrimaryKey.Name, pk2.Name);
Assert.AreEqual(composite.ForeignKey.ID, fk2.ID);
Assert.AreEqual(composite.ForeignKey_ID, fk2.ID);
Assert.AreEqual(composite.ForeignKey.Name, fk2.Name);
//get a fresh copy out of memory now that we have created the Lookup composite key, confirm the integrity of that relationship
Assert.AreEqual(lookup.GetSupplementalJoins().Count(), 1);
Assert.AreEqual(lookup.GetSupplementalJoins().Cast <LookupCompositeJoinInfo>().First().ID, composite.ID);
composite.DeleteInDatabase();
composite = null;
Assert.AreEqual(lookup.GetSupplementalJoins().Count(), 0);
}
catch (Exception ex)
{
Console.Write(ex.ToString());
throw;
}
finally
{
//cleanup
if (composite != null)
{
composite.DeleteInDatabase();
}
lookup.DeleteInDatabase();
desc.DeleteInDatabase();
fk.DeleteInDatabase();
pk.DeleteInDatabase();
fk2.DeleteInDatabase();
pk2.DeleteInDatabase();
fkTable.DeleteInDatabase();
pkTable.DeleteInDatabase();
}
}
public void CompositeLookupTest_SQL()
{
//this only works for MSSQL Servers
if (CatalogueRepository.DiscoveredServer.DatabaseType != DatabaseType.MicrosoftSQLServer)
{
Assert.Ignore("This test only targets Microsft SQL Servers");
}
TableInfo fkTable = null;
TableInfo pkTable = null;
ColumnInfo desc = null;
ColumnInfo fk = null;
ColumnInfo pk = null;
ColumnInfo fk2 = null;
ColumnInfo pk2 = null;
Lookup lookup = null;
LookupCompositeJoinInfo composite = null;
try
{
//table 1 - the dataset table, it has 2 foreign keys e.g. TestCode, Healthboard
fkTable = new TableInfo(CatalogueRepository, "UnitTest_Biochemistry");
fk = new ColumnInfo(CatalogueRepository, "UnitTest_BCTestCode", "int", fkTable);
fk2 = new ColumnInfo(CatalogueRepository, "UnitTest_BCHealthBoard", "int", fkTable);
//table 2 - the lookup table, it has 2 primary keys e.g. TestCode,Healthboard and 1 description e.g. TestDescription (the Healthboard makes it a composite JOIN which allows for the same TestCode being mapped to a different discription in Tayside vs Fife (healthboard)
pkTable = new TableInfo(CatalogueRepository, "UnitTest_BiochemistryLookup");
pk = new ColumnInfo(CatalogueRepository, "UnitTest_TestCode", "int", pkTable);
pk2 = new ColumnInfo(CatalogueRepository, "UnitTest_Healthboard", "int", pkTable);
desc = new ColumnInfo(CatalogueRepository, "UnitTest_TestDescription", "int", pkTable);
lookup = new Lookup(CatalogueRepository, desc, fk, pk, ExtractionJoinType.Left, null);
string joinSQL = JoinHelper.GetJoinSQL(lookup);
Assert.AreEqual(joinSQL, "UnitTest_Biochemistry Left JOIN UnitTest_BiochemistryLookup ON UnitTest_BCTestCode = UnitTest_TestCode");
//Create the composite lookup
composite = new LookupCompositeJoinInfo(CatalogueRepository, lookup, fk2, pk2);
string joinSQL_AfterAddingCompositeKey = JoinHelper.GetJoinSQL(lookup);
Assert.AreEqual(joinSQL_AfterAddingCompositeKey, "UnitTest_Biochemistry Left JOIN UnitTest_BiochemistryLookup ON UnitTest_BCTestCode = UnitTest_TestCode AND UnitTest_BCHealthBoard = UnitTest_Healthboard");
}
catch (Exception ex)
{
Console.Write(ex.ToString());
throw ex;
}
finally
{
//cleanup
if (composite != null)
{
composite.DeleteInDatabase();
}
lookup.DeleteInDatabase();
desc.DeleteInDatabase();
fk.DeleteInDatabase();
pk.DeleteInDatabase();
fk2.DeleteInDatabase();
pk2.DeleteInDatabase();
fkTable.DeleteInDatabase();
pkTable.DeleteInDatabase();
}
}
public void CreateANOVersionTest_LookupsAndExtractionInformations()
{
var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest");
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
bulk.SetupTestData();
bulk.ImportAsCatalogue();
//Create a lookup table on the server
var lookupTbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("z_sexLookup", new[]
{
new DatabaseColumnRequest("Code", "varchar(1)")
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("hb_Code", "varchar(1)")
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Description", "varchar(100)")
});
//import a reference to the table
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, lookupTbl);
ColumnInfo[] lookupColumnInfos;
TableInfo lookupTableInfo;
importer.DoImport(out lookupTableInfo, out lookupColumnInfos);
//Create a Lookup reference
var ciSex = bulk.catalogue.CatalogueItems.Single(c => c.Name == "sex");
var ciHb = bulk.catalogue.CatalogueItems.Single(c => c.Name == "hb_extract");
var eiChi = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "chi");
eiChi.IsExtractionIdentifier = true;
eiChi.SaveToDatabase();
var eiCentury = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "century");
eiCentury.HashOnDataRelease = true;
eiCentury.ExtractionCategory = ExtractionCategory.Internal;
eiCentury.SaveToDatabase();
//add a transform
var eiPostcode = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "current_postcode");
eiPostcode.SelectSQL = string.Format("LEFT(10,{0}.[current_postcode])", eiPostcode.ColumnInfo.TableInfo.Name);
eiPostcode.Alias = "MyMutilatedColumn";
eiPostcode.SaveToDatabase();
//add a combo transform
var ciComboCol = new CatalogueItem(CatalogueRepository, bulk.catalogue, "ComboColumn");
var colForename = bulk.columnInfos.Single(c => c.GetRuntimeName() == "forename");
var colSurname = bulk.columnInfos.Single(c => c.GetRuntimeName() == "surname");
var eiComboCol = new ExtractionInformation(CatalogueRepository, ciComboCol, colForename, colForename + " + ' ' + " + colSurname);
eiComboCol.Alias = "ComboColumn";
eiComboCol.SaveToDatabase();
var eiDataLoadRunId = bulk.extractionInformations.Single(ei => ei.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID));
eiDataLoadRunId.DeleteInDatabase();
var lookup = new Lookup(CatalogueRepository, lookupColumnInfos[2], ciSex.ColumnInfo, lookupColumnInfos[0], ExtractionJoinType.Left, null);
//now lets make it worse, lets assume the sex code changes per healthboard therefore the join to the lookup requires both fields sex and hb_extract
var compositeLookup = new LookupCompositeJoinInfo(CatalogueRepository, lookup, ciHb.ColumnInfo, lookupColumnInfos[1]);
//now lets make the _Desc field in the original Catalogue
int orderToInsertDescriptionFieldAt = ciSex.ExtractionInformation.Order;
//bump everyone down 1
foreach (var toBumpDown in bulk.catalogue.CatalogueItems.Select(ci => ci.ExtractionInformation).Where(e => e != null && e.Order > orderToInsertDescriptionFieldAt))
{
toBumpDown.Order++;
toBumpDown.SaveToDatabase();
}
var ciDescription = new CatalogueItem(CatalogueRepository, bulk.catalogue, "Sex_Desc");
var eiDescription = new ExtractionInformation(CatalogueRepository, ciDescription, lookupColumnInfos[2], lookupColumnInfos[2].Name);
eiDescription.Alias = "Sex_Desc";
eiDescription.Order = orderToInsertDescriptionFieldAt + 1;
eiDescription.ExtractionCategory = ExtractionCategory.Supplemental;
eiDescription.SaveToDatabase();
bulk.catalogue.ClearAllInjections();
//check it worked
QueryBuilder qb = new QueryBuilder(null, null);
qb.AddColumnRange(bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any));
//The query builder should be able to succesfully create SQL
Console.WriteLine(qb.SQL);
//there should be 2 tables involved in the query [z_sexLookup] and [BulkData]
Assert.AreEqual(2, qb.TablesUsedInQuery.Count);
//the query builder should have identified the lookup
Assert.AreEqual(lookup, qb.GetDistinctRequiredLookups().Single());
//////////////////////////////////////////////////////////////////////////////////////The Actual Bit Being Tested////////////////////////////////////////////////////
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue);
planManager.TargetDatabase = db;
//setup test rules for migrator
CreateMigrationRules(planManager, bulk);
//rules should pass checks
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager);
engine.Execute();
//////////////////////////////////////////////////////////////////////////////////////End The Actual Bit Being Tested////////////////////////////////////////////////////
var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano"));
Assert.IsTrue(anoCatalogue.Exists());
//The new Catalogue should have the same number of ExtractionInformations
var eiSource = bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray();
var eiDestination = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray();
Assert.AreEqual(eiSource.Length, eiDestination.Length, "Both the new and the ANO catalogue should have the same number of ExtractionInformations (extractable columns)");
for (int i = 0; i < eiSource.Length; i++)
{
Assert.AreEqual(eiSource[i].Order, eiDestination[i].Order, "ExtractionInformations in the source and destination Catalogue should have the same order");
Assert.AreEqual(eiSource[i].GetRuntimeName(),
eiDestination[i].GetRuntimeName().Replace("ANO", ""), "ExtractionInformations in the source and destination Catalogue should have the same names (excluding ANO prefix)");
Assert.AreEqual(eiSource[i].ExtractionCategory, eiDestination[i].ExtractionCategory, "Old / New ANO ExtractionInformations did not match on ExtractionCategory");
Assert.AreEqual(eiSource[i].IsExtractionIdentifier, eiDestination[i].IsExtractionIdentifier, "Old / New ANO ExtractionInformations did not match on IsExtractionIdentifier");
Assert.AreEqual(eiSource[i].HashOnDataRelease, eiDestination[i].HashOnDataRelease, "Old / New ANO ExtractionInformations did not match on HashOnDataRelease");
Assert.AreEqual(eiSource[i].IsPrimaryKey, eiDestination[i].IsPrimaryKey, "Old / New ANO ExtractionInformations did not match on IsPrimaryKey");
}
//check it worked
QueryBuilder qbdestination = new QueryBuilder(null, null);
qbdestination.AddColumnRange(anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any));
//The query builder should be able to succesfully create SQL
Console.WriteLine(qbdestination.SQL);
var anoEiPostcode = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("MyMutilatedColumn"));
//The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT...
Assert.AreEqual(string.Format("LEFT(10,{0}.[current_postcode])", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiPostcode.SelectSQL);
var anoEiComboCol = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("ComboColumn"));
//The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT...
Assert.AreEqual(string.Format("{0}.[forename] + ' ' + {0}.[surname]", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiComboCol.SelectSQL);
//there should be 2 tables involved in the query [z_sexLookup] and [BulkData]
Assert.AreEqual(2, qbdestination.TablesUsedInQuery.Count);
//the query builder should have identified the lookup but it should be the new one not the old one
Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Count(), "New query builder for ano catalogue did not correctly identify that there was a Lookup");
Assert.AreNotEqual(lookup, qbdestination.GetDistinctRequiredLookups().Single(), "New query builder for ano catalogue identified the OLD Lookup!");
Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins().Count(), "The new Lookup did not have the composite join key (sex/hb_extract)");
Assert.AreNotEqual(compositeLookup, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins(), "New query builder for ano catalogue identified the OLD LookupCompositeJoinInfo!");
db.Drop();
var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count();
var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count();
Assert.AreEqual(exports, imports);
Assert.IsTrue(exports > 0);
}