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 IdentifierDumperCheckFails_ServerIsNotADumpServer()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "NationalSecurityNumber");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(10)";
preDiscardedColumn1.SaveToDatabase();
//give it the WRONG server
tableInfoCreated.IdentifierDumpServer_ID = ANOStore_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
try
{
dumper.Check(new ThrowImmediatelyCheckNotifier());
Assert.Fail("Expected it to crash before now");
}
catch (Exception ex)
{
Assert.IsTrue(ex.Message.StartsWith("Exception occurred when trying to find stored procedure sp_createIdentifierDump"));
Assert.IsTrue(ex.InnerException.Message.StartsWith("Connected successfully to server"));
Assert.IsTrue(ex.InnerException.Message.EndsWith(" but did not find the stored procedure sp_createIdentifierDump in the database (Possibly the ExternalDatabaseServer is not an IdentifierDump database?)"));
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public void IdentifierDumperCheckFails_StagingNotCalled()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(50)";
preDiscardedColumn1.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
try
{
dumper.Check(new AcceptAllCheckNotifier());
var ex = Assert.Throws <Exception>(() => dumper.DumpAllIdentifiersInTable(_bulkData.GetDataTable(10)));
Assert.AreEqual("IdentifierDumper STAGING insert (ID_BulkData_STAGING) failed, make sure you have called CreateSTAGINGTable() before trying to Dump identifiers (also you should call DropStagging() when you are done)", ex.Message);
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public override void SetDatabaseObject(IActivateItems activator, PreLoadDiscardedColumn databaseObject)
{
base.SetDatabaseObject(activator, databaseObject);
CommonFunctionality.AddChecks(databaseObject);
CommonFunctionality.StartChecking();
CommonFunctionality.AddToMenu(new SetDumpServerMenuItem(Activator, databaseObject.TableInfo));
}
private PreLoadDiscardedColumn Create(string name, string dataType)
{
var discCol = new PreLoadDiscardedColumn(Activator.RepositoryLocator.CatalogueRepository, _tableInfo, name);
discCol.SqlDataType = dataType;
discCol.SaveToDatabase();
return(discCol);
}
protected override void SetBindings(BinderWithErrorProviderFactory rules, PreLoadDiscardedColumn databaseObject)
{
base.SetBindings(rules, databaseObject);
Bind(tbID, "Text", "ID", p => p.ID);
Bind(tbRuntimeColumnName, "Text", "RuntimeColumnName", p => p.RuntimeColumnName);
Bind(tbSqlDataType, "Text", "SqlDataType", p => p.SqlDataType);
Bind(ddDestination, "SelectedItem", "Destination", p => p.Destination);
}
public void TypeOfPreLoadDiscardedColumn()
{
string methodName = new StackTrace().GetFrame(0).GetMethod().Name;
string tableInfoName = "TableInfoFor_" + methodName;
string preLoadDiscardedColumnName = "PreLoadDiscardedColumnFor_" + methodName;
TableInfo toCleanup = CatalogueRepository.GetAllObjects <TableInfo>().SingleOrDefault(t => t.Name.Equals(tableInfoName));
PreLoadDiscardedColumn toCleanupCol = CatalogueRepository.GetAllObjects <PreLoadDiscardedColumn>()
.SingleOrDefault(c => c.RuntimeColumnName.Equals(preLoadDiscardedColumnName));
//must delete pre load discarded first
if (toCleanupCol != null)
{
toCleanupCol.DeleteInDatabase();
}
if (toCleanup != null)
{
toCleanup.DeleteInDatabase();
}
var lmd = new LoadMetadata(CatalogueRepository);
try
{
var pt = new ProcessTask(CatalogueRepository, lmd, LoadStage.AdjustStaging);
var pta = new ProcessTaskArgument(CatalogueRepository, pt);
pta.SetType(typeof(PreLoadDiscardedColumn));
var tableInfo = new TableInfo(CatalogueRepository, tableInfoName);
PreLoadDiscardedColumn preloadDiscardedColumn = new PreLoadDiscardedColumn(CatalogueRepository, tableInfo, preLoadDiscardedColumnName);
try
{
pta.SetValue(preloadDiscardedColumn);
pta.SaveToDatabase();
var newInstanceOfPTA = CatalogueRepository.GetObjectByID <ProcessTaskArgument>(pta.ID);
Assert.AreEqual(newInstanceOfPTA.Value, pta.Value);
PreLoadDiscardedColumn p1 = (PreLoadDiscardedColumn)pta.GetValueAsSystemType();
PreLoadDiscardedColumn p2 = (PreLoadDiscardedColumn)newInstanceOfPTA.GetValueAsSystemType();
Assert.AreEqual(p1.ID, p2.ID);
}
finally
{
preloadDiscardedColumn.DeleteInDatabase();
tableInfo.DeleteInDatabase();
}
}
finally
{
lmd.DeleteInDatabase();
}
}
private void Add(PreLoadDiscardedColumn discardedColumn, ITableInfo tableInfo, string rawDbName)
{
var snip = new SubstringAutocompleteItem(discardedColumn.GetRuntimeName());
var colName = discardedColumn.GetRuntimeName();
snip.MenuText = colName;
snip.Text = tableInfo.GetQuerySyntaxHelper().EnsureFullyQualified(rawDbName, null, tableInfo.GetRuntimeName(), colName);
snip.Tag = discardedColumn;
snip.ImageIndex = GetIndexFor(discardedColumn, RDMPConcept.ColumnInfo.ToString());
AddUnlessDuplicate(snip);
}
public void IdentifierDumperCheckFails_NoTableOnServerRejectChange()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "NationalSecurityNumber");
try
{
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(10)";
preDiscardedColumn1.SaveToDatabase();
var ex = Assert.Throws <ArgumentException>(() => new IdentifierDumper(tableInfoCreated));
StringAssert.Contains("does not have a listed IdentifierDump ExternalDatabaseServer", ex.Message);
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
}
}
public void Test_GetRAWStageTypeWhenPreLoadDiscardedDilution()
{
TableInfo parent = new TableInfo(CatalogueRepository, "Rokkits");
ColumnInfo column = new ColumnInfo(CatalogueRepository, "MyCol", "varchar(4)", parent);
var discard = new PreLoadDiscardedColumn(CatalogueRepository, parent, "MyCol");
discard.SqlDataType = "varchar(10)";
discard.Destination = DiscardedColumnDestination.Dilute;
discard.SaveToDatabase();
Assert.AreEqual("varchar(4)", column.GetRuntimeDataType(LoadStage.PostLoad));
Assert.AreEqual("varchar(4)", column.GetRuntimeDataType(LoadStage.AdjustStaging));
Assert.AreEqual("varchar(10)", column.GetRuntimeDataType(LoadStage.AdjustRaw));
discard.DeleteInDatabase();
parent.DeleteInDatabase();
}
public void IdentifierDumperCheckFails_NoTableExists()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(50)";
preDiscardedColumn1.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
var existingTable = DataAccessPortal.GetInstance()
.ExpectDatabase(IdentifierDump_ExternalDatabaseServer, DataAccessContext.InternalDataProcessing)
.ExpectTable("ID_BulkData");
if (existingTable.Exists())
{
existingTable.Drop();
}
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
try
{
ToMemoryCheckNotifier notifier = new ToMemoryCheckNotifier(new AcceptAllCheckNotifier());
dumper.Check(notifier);
Assert.IsTrue(notifier.Messages.Any(m =>
m.Result == CheckResult.Warning
&&
m.Message.Contains("Table ID_BulkData was not found")));
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public void IdentifierDumperCheckFails_LieAboutDatatype()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(50)";
preDiscardedColumn1.SaveToDatabase();
try
{
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
//table doesnt exist yet it should work
dumper.Check(new AcceptAllCheckNotifier());
//now it is varbinary
preDiscardedColumn1.SqlDataType = "varbinary(200)";
preDiscardedColumn1.SaveToDatabase();
//get a new dumper because we have changed the pre load discarded column
dumper = new IdentifierDumper(tableInfoCreated);
//table doesnt exist yet it should work
Exception ex = Assert.Throws <Exception>(() => dumper.Check(new ThrowImmediatelyCheckNotifier()));
Assert.IsTrue(ex.Message.Contains("has data type varbinary(200) in the Catalogue but appears as varchar(50) in the actual IdentifierDump"));
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public void Execute()
{
if (_planManager.TargetDatabase == null)
{
throw new Exception("PlanManager has no TargetDatabase set");
}
var memoryRepo = new MemoryCatalogueRepository();
using (_catalogueRepository.BeginNewTransactedConnection())
{
try
{
//for each skipped table
foreach (var skippedTable in _planManager.SkippedTables)
{
//we might have to refactor or port JoinInfos to these tables so we should establish what the parenthood of them was
foreach (ColumnInfo columnInfo in skippedTable.ColumnInfos)
{
GetNewColumnInfoForOld(columnInfo, true);
}
}
//for each table that isn't being skipped
foreach (var oldTableInfo in _planManager.TableInfos.Except(_planManager.SkippedTables))
{
List <DatabaseColumnRequest> columnsToCreate = new List <DatabaseColumnRequest>();
Dictionary <string, ColumnInfo> migratedColumns = new Dictionary <string, ColumnInfo>(StringComparer.CurrentCultureIgnoreCase);
var querybuilderForMigratingTable = new QueryBuilder(null, null);
//for each column we are not skipping (Drop) work out the endpoint datatype (planner knows this)
foreach (ColumnInfo columnInfo in oldTableInfo.ColumnInfos)
{
var columnPlan = _planManager.GetPlanForColumnInfo(columnInfo);
if (columnPlan.Plan != Plan.Drop)
{
//add the column verbatim to the query builder because we know we have to read it from source
querybuilderForMigratingTable.AddColumn(new ColumnInfoToIColumn(memoryRepo, columnInfo));
string colName = columnInfo.GetRuntimeName();
//if it is being ano tabled then give the table name ANO as a prefix
if (columnPlan.Plan == Plan.ANO)
{
colName = "ANO" + colName;
}
migratedColumns.Add(colName, columnInfo);
columnsToCreate.Add(new DatabaseColumnRequest(colName, columnPlan.GetEndpointDataType(), !columnInfo.IsPrimaryKey)
{
IsPrimaryKey = columnInfo.IsPrimaryKey
});
}
}
SelectSQLForMigrations.Add(oldTableInfo, querybuilderForMigratingTable);
//Create the actual table
var tbl = _planManager.TargetDatabase.CreateTable(oldTableInfo.GetRuntimeName(), columnsToCreate.ToArray());
//import the created table
TableInfoImporter importer = new TableInfoImporter(_catalogueRepository, tbl);
importer.DoImport(out var newTableInfo, out var newColumnInfos);
//Audit the parenthood of the TableInfo/ColumnInfos
AuditParenthood(oldTableInfo, newTableInfo);
foreach (ColumnInfo newColumnInfo in newColumnInfos)
{
var oldColumnInfo = migratedColumns[newColumnInfo.GetRuntimeName()];
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
if (columnPlan.Plan == Plan.ANO)
{
newColumnInfo.ANOTable_ID = columnPlan.ANOTable.ID;
newColumnInfo.SaveToDatabase();
}
//if there was a dilution configured we need to setup a virtual DLE load only column of the input type (this ensures RAW has a valid datatype)
if (columnPlan.Plan == Plan.Dilute)
{
//Create a discarded (load only) column with name matching the new columninfo
var discard = new PreLoadDiscardedColumn(_catalogueRepository, newTableInfo, newColumnInfo.GetRuntimeName());
//record that it exists to support dilution and that the data type matches the input (old) ColumnInfo (i.e. not the new data type!)
discard.Destination = DiscardedColumnDestination.Dilute;
discard.SqlDataType = oldColumnInfo.Data_type;
discard.SaveToDatabase();
DilutionOperationsForMigrations.Add(discard, columnPlan.Dilution);
}
AuditParenthood(oldColumnInfo, newColumnInfo);
}
if (DilutionOperationsForMigrations.Any())
{
newTableInfo.IdentifierDumpServer_ID = _planManager.GetIdentifierDumpServer().ID;
newTableInfo.SaveToDatabase();
}
}
NewCatalogue = _planManager.Catalogue.ShallowClone();
NewCatalogue.Name = "ANO" + _planManager.Catalogue.Name;
NewCatalogue.Folder = new CatalogueFolder(NewCatalogue, "\\anonymous" + NewCatalogue.Folder.Path);
NewCatalogue.SaveToDatabase();
AuditParenthood(_planManager.Catalogue, NewCatalogue);
//For each of the old ExtractionInformations (95% of the time that's just a reference to a ColumnInfo e.g. '[People].[Height]' but 5% of the time it's some horrible aliased transform e.g. 'dbo.RunMyCoolFunction([People].[Height]) as BigHeight'
foreach (CatalogueItem oldCatalogueItem in _planManager.Catalogue.CatalogueItems)
{
var oldColumnInfo = oldCatalogueItem.ColumnInfo;
//catalogue item is not connected to any ColumnInfo
if (oldColumnInfo == null)
{
continue;
}
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
//we are not migrating it anyway
if (columnPlan.Plan == Plan.Drop)
{
continue;
}
ColumnInfo newColumnInfo = GetNewColumnInfoForOld(oldColumnInfo);
var newCatalogueItem = oldCatalogueItem.ShallowClone(NewCatalogue);
//and rewire it's ColumnInfo to the cloned child one
newCatalogueItem.ColumnInfo_ID = newColumnInfo.ID;
//If the old CatalogueItem had the same name as it's underlying ColumnInfo then we should use the new one otherwise just copy the old name whatever it was
newCatalogueItem.Name = oldCatalogueItem.Name.Equals(oldColumnInfo.Name) ? newColumnInfo.GetRuntimeName() : oldCatalogueItem.Name;
//add ANO to the front if the underlying column was annoed
if (newColumnInfo.GetRuntimeName().StartsWith("ANO") && !newCatalogueItem.Name.StartsWith("ANO"))
{
newCatalogueItem.Name = "ANO" + newCatalogueItem.Name;
}
newCatalogueItem.SaveToDatabase();
var oldExtractionInformation = oldCatalogueItem.ExtractionInformation;
//if the plan is to make the ColumnInfo extractable
if (columnPlan.ExtractionCategoryIfAny != null)
{
//Create a new ExtractionInformation for the new Catalogue
var newExtractionInformation = new ExtractionInformation(_catalogueRepository, newCatalogueItem, newColumnInfo, newColumnInfo.Name);
newExtractionInformation.ExtractionCategory = columnPlan.ExtractionCategoryIfAny.Value;
newExtractionInformation.SaveToDatabase();
//if it was previously extractable
if (oldExtractionInformation != null)
{
var refactorer = new SelectSQLRefactorer();
//restore the old SQL as it existed in the origin table
newExtractionInformation.SelectSQL = oldExtractionInformation.SelectSQL;
//do a refactor on the old column name for the new column name
refactorer.RefactorColumnName(newExtractionInformation, oldColumnInfo, newColumnInfo.Name, true);
//also refactor any other column names that might be referenced by the transform SQL e.g. it could be a combo column name where forename + surname is the value of the ExtractionInformation
foreach (var kvpOtherCols in _parenthoodDictionary.Where(kvp => kvp.Key is ColumnInfo))
{
//if it's one we have already done, dont do it again
if (Equals(kvpOtherCols.Value, newColumnInfo))
{
continue;
}
//otherwise do a non strict refactoring (don't worry if you don't finda ny references)
refactorer.RefactorColumnName(newExtractionInformation, (ColumnInfo)kvpOtherCols.Key, ((ColumnInfo)(kvpOtherCols.Value)).Name, false);
}
//make the new one exactly as extractable
newExtractionInformation.Order = oldExtractionInformation.Order;
newExtractionInformation.Alias = oldExtractionInformation.Alias;
newExtractionInformation.IsExtractionIdentifier = oldExtractionInformation.IsExtractionIdentifier;
newExtractionInformation.HashOnDataRelease = oldExtractionInformation.HashOnDataRelease;
newExtractionInformation.IsPrimaryKey = oldExtractionInformation.IsPrimaryKey;
newExtractionInformation.SaveToDatabase();
}
AuditParenthood(oldCatalogueItem, newCatalogueItem);
if (oldExtractionInformation != null)
{
AuditParenthood(oldExtractionInformation, newExtractionInformation);
}
}
}
var existingJoinInfos = _catalogueRepository.GetAllObjects <JoinInfo>();
var existingLookups = _catalogueRepository.GetAllObjects <Lookup>();
var existingLookupComposites = _catalogueRepository.GetAllObjects <LookupCompositeJoinInfo>();
//migrate join infos
foreach (JoinInfo joinInfo in _planManager.GetJoinInfosRequiredCatalogue())
{
var newFk = GetNewColumnInfoForOld(joinInfo.ForeignKey);
var newPk = GetNewColumnInfoForOld(joinInfo.PrimaryKey);
//already exists
if (!existingJoinInfos.Any(ej => ej.ForeignKey_ID == newFk.ID && ej.PrimaryKey_ID == newPk.ID))
{
new JoinInfo(_catalogueRepository, newFk, newPk, joinInfo.ExtractionJoinType, joinInfo.Collation); //create it
}
}
//migrate Lookups
foreach (Lookup lookup in _planManager.GetLookupsRequiredCatalogue())
{
//Find the new columns in the ANO table that match the old lookup columns
var newDesc = GetNewColumnInfoForOld(lookup.Description);
var newFk = GetNewColumnInfoForOld(lookup.ForeignKey);
var newPk = GetNewColumnInfoForOld(lookup.PrimaryKey);
//see if we already have a Lookup declared for the NEW columns (unlikely)
Lookup newLookup = existingLookups.SingleOrDefault(l => l.Description_ID == newDesc.ID && l.ForeignKey_ID == newFk.ID);
//create new Lookup that mirrors the old but references the ANO columns instead
if (newLookup == null)
{
newLookup = new Lookup(_catalogueRepository, newDesc, newFk, newPk, lookup.ExtractionJoinType, lookup.Collation);
}
//also mirror any composite (secondary, tertiary join column pairs needed for the Lookup to operate correclty e.g. where TestCode 'HAB1' means 2 different things depending on healthboard)
foreach (LookupCompositeJoinInfo compositeJoin in lookup.GetSupplementalJoins().Cast <LookupCompositeJoinInfo>())
{
var newCompositeFk = GetNewColumnInfoForOld(compositeJoin.ForeignKey);
var newCompositePk = GetNewColumnInfoForOld(compositeJoin.PrimaryKey);
if (!existingLookupComposites.Any(c => c.ForeignKey_ID == newCompositeFk.ID && c.PrimaryKey_ID == newCompositePk.ID))
{
new LookupCompositeJoinInfo(_catalogueRepository, newLookup, newCompositeFk, newCompositePk, compositeJoin.Collation);
}
}
}
//create new data load confguration
LoadMetadata = new LoadMetadata(_catalogueRepository, "Anonymising " + NewCatalogue);
LoadMetadata.EnsureLoggingWorksFor(NewCatalogue);
NewCatalogue.LoadMetadata_ID = LoadMetadata.ID;
NewCatalogue.SaveToDatabase();
if (_planManager.DateColumn != null)
{
LoadProgressIfAny = new LoadProgress(_catalogueRepository, LoadMetadata);
LoadProgressIfAny.OriginDate = _planManager.StartDate;
LoadProgressIfAny.SaveToDatabase();
//date column based migration only works for single TableInfo migrations (see Plan Manager checks)
var qb = SelectSQLForMigrations.Single(kvp => !kvp.Key.IsLookupTable()).Value;
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepo, null,
new[]
{
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " >= @startDate", "After batch start date", "", null),
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " <= @endDate", "Before batch end date", "", null),
}
, FilterContainerOperation.AND);
}
try
{
foreach (QueryBuilder qb in SelectSQLForMigrations.Values)
{
Console.WriteLine(qb.SQL);
}
}
catch (Exception e)
{
throw new Exception("Failed to generate migration SQL", e);
}
_catalogueRepository.EndTransactedConnection(true);
}
catch (Exception ex)
{
_catalogueRepository.EndTransactedConnection(false);
throw new Exception("Failed to create ANO version, transaction rolled back succesfully", ex);
}
}
}
public void DumpAllIdentifiersInTable_Passes()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "surname")
{
Destination = DiscardedColumnDestination.StoreInIdentifiersDump,
SqlDataType = "varchar(20)"
};
preDiscardedColumn1.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
var chiToSurnameDictionary = new Dictionary <string, HashSet <string> >();
try
{
dumper.Check(new AcceptAllCheckNotifier());
DataTable dt = _bulkData.GetDataTable(1000);
Assert.AreEqual(1000, dt.Rows.Count);
Assert.IsTrue(dt.Columns.Contains("surname"));
//for checking the final ID table has the correct values in
foreach (DataRow row in dt.Rows)
{
var chi = row["chi"].ToString();
if (!chiToSurnameDictionary.ContainsKey(chi))
{
chiToSurnameDictionary.Add(chi, new HashSet <string>());
}
chiToSurnameDictionary[chi].Add(row["surname"] as string);
}
dumper.CreateSTAGINGTable();
dumper.DumpAllIdentifiersInTable(dt);
dumper.DropStaging();
//confirm that the surname column is no longer in the pipeline
Assert.IsFalse(dt.Columns.Contains("surname"));
//now look at the ids in the identifier dump and make sure they match what was in the pipeline before we sent it
var server = IdentifierDump_Database.Server;
using (var con = server.GetConnection())
{
con.Open();
var cmd = server.GetCommand("Select * from " + "ID_" + BulkTestsData.BulkDataTable, con);
var r = cmd.ExecuteReader();
//make sure the values in the ID table match the ones we originally had in the pipeline
while (r.Read())
{
if (!chiToSurnameDictionary[r["chi"].ToString()].Any())
{
Assert.IsTrue(r["surname"] == DBNull.Value);
}
else
{
Assert.IsTrue(chiToSurnameDictionary[r["chi"].ToString()].Contains(r["surname"] as string), "Dictionary did not contain expected surname:" + r["surname"]);
}
}
r.Close();
//leave the identifier dump in the way we found it (empty)
var tbl = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable);
if (tbl.Exists())
{
tbl.Drop();
}
tbl = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable + "_Archive");
if (tbl.Exists())
{
tbl.Drop();
}
}
}
finally
{
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
public void DumpAllIdentifiersInTable_UnexpectedColumnFoundInIdentifierDumpTable()
{
var preDiscardedColumn1 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "surname");
preDiscardedColumn1.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn1.SqlDataType = "varchar(20)";
preDiscardedColumn1.SaveToDatabase();
var preDiscardedColumn2 = new PreLoadDiscardedColumn(CatalogueRepository, tableInfoCreated, "forename");
preDiscardedColumn2.Destination = DiscardedColumnDestination.StoreInIdentifiersDump;
preDiscardedColumn2.SqlDataType = "varchar(50)";
preDiscardedColumn2.SaveToDatabase();
//give it the correct server
tableInfoCreated.IdentifierDumpServer_ID = IdentifierDump_ExternalDatabaseServer.ID;
tableInfoCreated.SaveToDatabase();
IdentifierDumper dumper = new IdentifierDumper(tableInfoCreated);
dumper.Check(new AcceptAllCheckNotifier());
DiscoveredTable tableInDump = IdentifierDump_Database.ExpectTable("ID_" + BulkTestsData.BulkDataTable);
Assert.IsTrue(tableInDump.Exists(), "ID table did not exist");
var columnsInDump = tableInDump.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
//works and creates table on server
Assert.Contains("hic_validFrom", columnsInDump);
Assert.Contains("forename", columnsInDump);
Assert.Contains("chi", columnsInDump);
Assert.Contains("surname", columnsInDump);
//now delete it!
preDiscardedColumn2.DeleteInDatabase();
//now create a new dumper and watch it go crazy
IdentifierDumper dumper2 = new IdentifierDumper(tableInfoCreated);
var thrower = new ThrowImmediatelyCheckNotifier();
thrower.ThrowOnWarning = true;
try
{
var ex = Assert.Throws <Exception>(() => dumper2.Check(thrower));
Assert.AreEqual("Column forename was found in the IdentifierDump table ID_BulkData but was not one of the primary keys or a PreLoadDiscardedColumn", ex.Message);
}
finally
{
//Drop all this stuff
var server = IdentifierDump_Database.Server;
using (var con = server.GetConnection())
{
con.Open();
//leave the identifier dump in the way we found it (empty)
var cmdDrop = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable, con);
cmdDrop.ExecuteNonQuery();
var cmdDropArchive = server.GetCommand("DROP TABLE ID_" + BulkTestsData.BulkDataTable + "_Archive", con);
cmdDropArchive.ExecuteNonQuery();
}
preDiscardedColumn1.DeleteInDatabase();
tableInfoCreated.IdentifierDumpServer_ID = null;//reset it back to how it was when we found it
tableInfoCreated.SaveToDatabase();
}
}
private void AddColumnToDump(PreLoadDiscardedColumn column, DbConnection con)
{
using (var cmdAlter = DatabaseCommandHelper.GetCommand("Alter table " + _parent.GetRuntimeName() + " ADD " + column.RuntimeColumnName + " " + column.SqlDataType, con))
cmdAlter.ExecuteNonQuery();
}
