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