private void SetupJoin()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add(new object[] { "Dave", "Is a maniac" });
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("SimpleJoin", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50))
{
IsPrimaryKey = true
} });
var lookupCata = Import(tbl);
ExtractionInformation fkEi = _catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(n => n.GetRuntimeName() == "Name");
ColumnInfo pk = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Name");
new JoinInfo(CatalogueRepository, fkEi.ColumnInfo, pk, ExtractionJoinType.Left, null);
var ci = new CatalogueItem(CatalogueRepository, _catalogue, "Name_2");
var ei = new ExtractionInformation(CatalogueRepository, ci, pk, pk.Name)
{
Alias = "Name_2"
};
ei.SaveToDatabase();
}
public void CreateANOVersionTest_IntIdentity()
{
var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest");
//setup the anonymisation database (destination)
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
//Create this table in the scratch database
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("MyTable", new[]
{
new DatabaseColumnRequest("id", "int identity(1,1)", false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10), false)
});
TableInfo ti;
ColumnInfo[] cols;
var cata = Import(tbl, out ti, out cols);
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata);
planManager.TargetDatabase = db;
var nameCol = cols.Single(c => c.GetRuntimeName().Equals("Name"));
//setup test rules for migrator
planManager.Plans[nameCol].Plan = Plan.Drop;
//rules should pass checks
planManager.Check(new ThrowImmediatelyCheckNotifier());
var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager);
engine.Execute();
var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano"));
Assert.IsTrue(anoCatalogue.Exists());
//should only be one (the id column
Assert.AreEqual(1, anoCatalogue.CatalogueItems.Length);
var idColInAnoDatabase = anoCatalogue.CatalogueItems[0].ColumnInfo;
Assert.AreEqual("int", idColInAnoDatabase.Data_type);
db.Drop();
var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count();
var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count();
Assert.AreEqual(exports, imports);
Assert.IsTrue(exports > 0);
}
protected DiscoveredTable CreateDataset <T>(int people, int rows, Random r, out PersonCollection peopleGenerated) where T : IDataGenerator
{
var f = new DataGeneratorFactory();
T instance = f.Create <T>(r);
peopleGenerated = new PersonCollection();
peopleGenerated.GeneratePeople(people, r);
var dt = instance.GetDataTable(peopleGenerated, rows);
return(DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable(typeof(T).Name, dt, null, false, this));
}
public void OverwriteMigrationStrategy_NoPrimaryKey()
{
var from = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("Bob", new[] { new DatabaseColumnRequest("Field", "int") });
var to = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("Frank", new[] { new DatabaseColumnRequest("Field", "int") });
var connection = Mock.Of <IManagedConnection>();
var job = Mock.Of <IDataLoadJob>();
var strategy = new OverwriteMigrationStrategy(connection);
var migrationFieldProcessor = Mock.Of <IMigrationFieldProcessor>();
var ex = Assert.Throws <Exception>(() => new MigrationColumnSet(from, to, migrationFieldProcessor));
Assert.AreEqual("There are no primary keys declared in table Bob", ex.Message);
}
public void TestCheckUpdateTrigger()
{
// set up a test database
const string tableName = "TestTable";
var databaseName = DiscoveredDatabaseICanCreateRandomTablesIn.GetRuntimeName();
var table = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable(tableName, new[] { new DatabaseColumnRequest("Id", "int"), });
var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server;
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand(
"CREATE TRIGGER dbo.[TestTable_OnUpdate] ON [dbo].[" + tableName +
"] AFTER DELETE AS RAISERROR('MESSAGE',16,10)", con);
cmd.ExecuteNonQuery();
}
var dbInfo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(databaseName);
var factory = new TriggerImplementerFactory(dbInfo.Server.DatabaseType);
var triggerImplementer = factory.Create(table);
var isEnabled = triggerImplementer.GetTriggerStatus();
Assert.AreEqual(TriggerStatus.Enabled, isEnabled);
// disable the trigger and test correct reporting
using (var con = new SqlConnection(dbInfo.Server.Builder.ConnectionString))
{
con.Open();
var cmd =
new SqlCommand(
"USE [" + databaseName + "]; DISABLE TRIGGER TestTable_OnUpdate ON [" + databaseName + "]..[" +
tableName + "]", con);
cmd.ExecuteNonQuery();
}
isEnabled = triggerImplementer.GetTriggerStatus();
Assert.AreEqual(TriggerStatus.Disabled, isEnabled);
}
private void SetupCatalogueConfigurationEtc()
{
DataTable dt = new DataTable();
dt.Columns.Add("PrivateID");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Rows.Add(new object[] { _cohortKeysGenerated.Keys.First(), "Dave", "2001-01-01" });
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("TestTable", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) });
CatalogueItem[] cataItems;
_catalogue = Import(tbl, out _tableInfo, out _columnInfos, out cataItems, out _extractionInformations);
ExtractionInformation _privateID = _extractionInformations.First(e => e.GetRuntimeName().Equals("PrivateID"));
_privateID.IsExtractionIdentifier = true;
_privateID.SaveToDatabase();
}
private void SetupLookupTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add(new object[] { "Dave", "Is a maniac" });
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("SimpleLookup", dt, new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) });
var lookupCata = Import(tbl);
ExtractionInformation fkEi = _catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(n => n.GetRuntimeName() == "Name");
ColumnInfo pk = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Name");
ColumnInfo descLine1 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Description");
var cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, true);
cmd.Execute();
}
public void TestLookupCommand(LookupTestCase testCase)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("SendingLocation");
dt.Columns.Add("DischargeLocation");
dt.Columns.Add("Country");
var maintbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("MainDataset", dt);
var mainCata = Import(maintbl);
DataTable dtLookup = new DataTable();
dtLookup.Columns.Add("LocationCode");
dtLookup.Columns.Add("Line1");
dtLookup.Columns.Add("Line2");
dtLookup.Columns.Add("Postcode");
dtLookup.Columns.Add("Country");
var lookuptbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("Lookup", dtLookup);
var lookupCata = Import(lookuptbl);
ExtractionInformation fkEi = mainCata.GetAllExtractionInformation(ExtractionCategory.Any).Single(n => n.GetRuntimeName() == "SendingLocation");
ColumnInfo fk = mainCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "SendingLocation");
ColumnInfo pk = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "LocationCode");
ColumnInfo descLine1 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Line1");
ColumnInfo descLine2 = lookupCata.GetTableInfoList(false).Single().ColumnInfos.Single(n => n.GetRuntimeName() == "Line2");
ExecuteCommandCreateLookup cmd = null;
var sqlBefore = GetSql(mainCata);
switch (testCase)
{
case LookupTestCase.SingleKeySingleDescriptionNoVirtualColumn:
cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, false);
cmd.Execute();
//sql should not have changed because we didn't create an new ExtractionInformation virtual column
Assert.AreEqual(sqlBefore, GetSql(mainCata));
break;
case LookupTestCase.SingleKeySingleDescription:
cmd = new ExecuteCommandCreateLookup(CatalogueRepository, fkEi, descLine1, pk, null, true);
cmd.Execute();
//should have the lookup join and the virtual column _Desc
var sqlAfter = GetSql(mainCata);
Assert.IsTrue(sqlAfter.Contains("JOIN"));
Assert.IsTrue(sqlAfter.Contains("SendingLocation_Desc"));
break;
default:
throw new ArgumentOutOfRangeException("testCase");
}
foreach (var d in CatalogueRepository.GetAllObjects <Lookup>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <LookupCompositeJoinInfo>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <TableInfo>())
{
d.DeleteInDatabase();
}
foreach (var d in CatalogueRepository.GetAllObjects <Catalogue>())
{
d.DeleteInDatabase();
}
maintbl.Drop();
lookuptbl.Drop();
}
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);
}
private ExtractDatasetCommand SetupExtractDatasetCommand(string testTableName, string[] pkExtractionColumns, string[] pkColumnInfos = null, bool withLookup = false, bool withJoin = false)
{
DataTable dt = new DataTable();
dt.Columns.Add("PrivateID");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
if (pkColumnInfos != null)
{
dt.PrimaryKey =
dt.Columns.Cast <DataColumn>().Where(col => pkColumnInfos.Contains(col.ColumnName)).ToArray();
}
dt.Rows.Add(new object[] { _cohortKeysGenerated.Keys.First(), "Dave", "2001-01-01" });
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable(testTableName,
dt,
new[] { new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 50)) });
TableInfo tableInfo;
ColumnInfo[] columnInfos;
CatalogueItem[] cataItems;
ExtractionInformation[] extractionInformations;
_catalogue = Import(tbl, out tableInfo, out columnInfos, out cataItems, out extractionInformations);
ExtractionInformation privateID = extractionInformations.First(e => e.GetRuntimeName().Equals("PrivateID"));
privateID.IsExtractionIdentifier = true;
privateID.SaveToDatabase();
if (withLookup)
{
SetupLookupTable();
}
if (withJoin)
{
SetupJoin();
}
_catalogue.ClearAllInjections();
extractionInformations = _catalogue.GetAllExtractionInformation(ExtractionCategory.Any);
foreach (var pkExtractionColumn in pkExtractionColumns)
{
ExtractionInformation column = extractionInformations.First(e => e.GetRuntimeName().Equals(pkExtractionColumn));
column.IsPrimaryKey = true;
column.SaveToDatabase();
}
ExtractionConfiguration configuration;
IExtractableDataSet extractableDataSet;
Project project;
SetupDataExport(testTableName, _catalogue,
out configuration, out extractableDataSet, out project);
configuration.Cohort_ID = _extractableCohort.ID;
configuration.SaveToDatabase();
return(new ExtractDatasetCommand(configuration, new ExtractableDatasetBundle(extractableDataSet)));
}
