public DatabaseColumnRequestUI(DatabaseColumnRequest column)
{
_column = column;
InitializeComponent();
lblColumnName.Text = column.ColumnName;
ddManagedType.DataSource = DatabaseTypeRequest.PreferenceOrder;
var request = column.TypeRequested;
if (request != null)
{
if (request.CSharpType != null)
ddManagedType.SelectedItem = request.CSharpType;
if (request.Size != null)
nBeforeDecimal.Value = request.Size.NumbersBeforeDecimalPlace;
if (request.Size != null)
nAfterDecimal.Value = request.Size.NumbersAfterDecimalPlace;
if (request.Width.HasValue)
nLength.Value = request.Width.Value;
}
tbExplicitDbType.Text = column.ExplicitDbType;
bLoaded = true;
}
public CacheCommitIdentifierList(AggregateConfiguration configuration, string sql, DataTable results, DatabaseColumnRequest identifierColumn, int timeout)
: base(AggregateOperation.IndexedExtractionIdentifierList, configuration, sql, results, timeout, new [] { identifierColumn })
{
//advise them if they are trying to cache an identifier list but the DataTable has more than 1 column
if (results.Columns.Count != 1)
{
throw new NotSupportedException("The DataTable did not have exactly 1 column (it had " + results.Columns.Count + " columns). This makes it incompatible with committing to the Cache as an IdentifierList");
}
//advise them if they are trying to cache a cache query itself!
if (sql.Trim().StartsWith(CachedAggregateConfigurationResultsManager.CachingPrefix))
{
throw new NotSupportedException("Sql for the query started with '" + CachedAggregateConfigurationResultsManager.CachingPrefix + "' which implies you ran some SQL code to fetch some stuff from the cache and then committed it back into the cache (obliterating the record of what the originally executed query was). This is referred to as Inception Caching and isn't allowed. Note to developers: this happens if user caches a query then runs the query again (fetching it from the cache) and somehow tries to commit the cache fetch request back into the cache as an overwrite");
}
//throw away nulls
foreach (var r in results.Rows.Cast <DataRow>().ToArray())
{
if (r[0] == null || r[0] == DBNull.Value)
{
results.Rows.Remove(r);
}
}
if (identifierColumn == null)
{
throw new Exception("You must specify the data type of the identifier column, identifierColumn was null");
}
_identifierColumn = identifierColumn;
_identifierColumn.AllowNulls = false;
_identifierColumn.ColumnName = results.Columns[0].ColumnName;
}
public void TestForeignKey_OneColumnKey(DatabaseType dbType, bool cascade)
{
var db = GetTestDatabase(dbType);
var parentTable = db.CreateTable("Table1",
new[]
{
new DatabaseColumnRequest("Id", "int", false)
{
IsAutoIncrement = true,
IsPrimaryKey = true
}
});
var discovered_pkCol = parentTable.DiscoverColumn("Id");
var requested_fkCol = new DatabaseColumnRequest("Parent_Id", "int");
var childTable = db.CreateTable("Child1", new[]
{
requested_fkCol,
new DatabaseColumnRequest("SomeNumber", "int")
}, new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ requested_fkCol, discovered_pkCol }
}, cascade);
var discovered_fkCol = childTable.DiscoverColumn("Parent_Id");
DiscoveredRelationship[] relationships = parentTable.DiscoverRelationships();
Assert.AreEqual(1, relationships.Length);
Assert.AreEqual(parentTable, relationships[0].PrimaryKeyTable);
Assert.AreEqual(childTable, relationships[0].ForeignKeyTable);
Assert.AreEqual(1, relationships[0].Keys.Count);
Assert.AreEqual(parentTable.DiscoverColumns().Single(), relationships[0].Keys.Keys.Single());
Assert.AreEqual(discovered_fkCol, relationships[0].Keys.Values.Single());
Assert.AreEqual(parentTable.DiscoverColumns().Single(), discovered_pkCol);
Assert.AreEqual(relationships[0].Keys[discovered_pkCol], discovered_fkCol);
Assert.AreEqual(cascade ? CascadeRule.Delete:CascadeRule.NoAction, relationships[0].CascadeDelete);
var sort1 = new RelationshipTopologicalSort(new[] { childTable, parentTable });
Assert.AreEqual(sort1.Order[0], parentTable);
Assert.AreEqual(sort1.Order[1], childTable);
var sort2 = new RelationshipTopologicalSort(new[] { parentTable, childTable });
Assert.AreEqual(sort2.Order[0], parentTable);
Assert.AreEqual(sort2.Order[1], childTable);
childTable.Drop();
parentTable.Drop();
}
protected override string GetCreateTableSqlLineForColumn(DatabaseColumnRequest col, string datatype, IQuerySyntaxHelper syntaxHelper)
{
if (col.IsAutoIncrement)
{
return(string.Format("{0} NUMBER {1}", col.ColumnName, syntaxHelper.GetAutoIncrementKeywordIfAny()));
}
return(base.GetCreateTableSqlLineForColumn(col, datatype, syntaxHelper));
}
public void TestForeignKey_TwoColumnKey(DatabaseType dbType)
{
var db = GetTestDatabase(dbType);
var parentTable = db.CreateTable("Table2",
new[]
{
new DatabaseColumnRequest("Id1", "int", false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Id2", "int", false)
{
IsPrimaryKey = true
}
});
var discovered_pkCol1 = parentTable.DiscoverColumn("Id1");
var discovered_pkCol2 = parentTable.DiscoverColumn("Id2");
var requested_fkCol1 = new DatabaseColumnRequest("Parent_Id1", "int");
var requested_fkCol2 = new DatabaseColumnRequest("Parent_Id2", "int");
var childTable = db.CreateTable("Child2", new[]
{
requested_fkCol1,
requested_fkCol2,
}, new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ requested_fkCol1, discovered_pkCol1 },
{ requested_fkCol2, discovered_pkCol2 }
}, true);
var discovered_fkCol1 = childTable.DiscoverColumn("Parent_Id1");
var discovered_fkCol2 = childTable.DiscoverColumn("Parent_Id2");
DiscoveredRelationship[] relationships = parentTable.DiscoverRelationships();
Assert.AreEqual(1, relationships.Length);
Assert.AreEqual(parentTable, relationships[0].PrimaryKeyTable);
Assert.AreEqual(childTable, relationships[0].ForeignKeyTable);
//should be a composite key of Id1 => Parent_Id1 && Id2 => Parent_Id2
Assert.AreEqual(2, relationships[0].Keys.Count);
Assert.AreEqual(discovered_fkCol1, relationships[0].Keys[discovered_pkCol1]);
Assert.AreEqual(discovered_fkCol2, relationships[0].Keys[discovered_pkCol2]);
childTable.Drop();
parentTable.Drop();
}
protected override string GetCreateTableSqlLineForColumn(DatabaseColumnRequest col, string datatype, IQuerySyntaxHelper syntaxHelper)
{
//Collations generally have to be in quotes (unless maybe they are very weird user generated ones?)
return(string.Format("{0} {1} {2} {3} {4} {5}",
syntaxHelper.EnsureWrapped(col.ColumnName),
datatype,
col.Default != MandatoryScalarFunctions.None ? "default " + syntaxHelper.GetScalarFunctionSql(col.Default) : "",
string.IsNullOrWhiteSpace(col.Collation) ? "" : "COLLATE " + '"' + col.Collation.Trim('"') + '"',
col.AllowNulls && !col.IsPrimaryKey ? " NULL" : " NOT NULL",
col.IsAutoIncrement ? syntaxHelper.GetAutoIncrementKeywordIfAny() : ""
));
}
protected override string GetCreateTableSqlLineForColumn(DatabaseColumnRequest col, string datatype, IQuerySyntaxHelper syntaxHelper)
{
//if it is not unicode then that's fine
if (col.TypeRequested == null || !col.TypeRequested.Unicode)
{
return(base.GetCreateTableSqlLineForColumn(col, datatype, syntaxHelper));
}
//MySql unicode is not a data type it's a character set/collation only
return(string.Format("{0} {1} {2} {3} {4} {5} {6}",
syntaxHelper.EnsureWrapped(col.ColumnName),
datatype,
"CHARACTER SET utf8mb4",
col.Default != MandatoryScalarFunctions.None ? "default " + syntaxHelper.GetScalarFunctionSql(col.Default) : "",
"COLLATE " + (col.Collation ?? "utf8mb4_bin"),
col.AllowNulls && !col.IsPrimaryKey ? " NULL" : " NOT NULL",
col.IsAutoIncrement ? syntaxHelper.GetAutoIncrementKeywordIfAny() : ""
));
}
public DatabaseColumnRequestUI(DatabaseColumnRequest column)
{
_column = column;
InitializeComponent();
lblColumnName.Text = column.ColumnName;
ddManagedType.DataSource = DatabaseTypeRequest.PreferenceOrder;
var request = column.TypeRequested;
if (request != null)
{
if (request.CSharpType != null)
{
ddManagedType.SelectedItem = request.CSharpType;
}
if (request.DecimalPlacesBeforeAndAfter != null && request.DecimalPlacesBeforeAndAfter.NumbersBeforeDecimalPlace.HasValue)
{
nBeforeDecimal.Value = request.DecimalPlacesBeforeAndAfter.NumbersBeforeDecimalPlace.Value;
}
if (request.DecimalPlacesBeforeAndAfter != null && request.DecimalPlacesBeforeAndAfter.NumbersAfterDecimalPlace.HasValue)
{
nAfterDecimal.Value = request.DecimalPlacesBeforeAndAfter.NumbersAfterDecimalPlace.Value;
}
if (request.MaxWidthForStrings.HasValue)
{
nLength.Value = request.MaxWidthForStrings.Value;
}
}
tbExplicitDbType.Text = column.ExplicitDbType;
bLoaded = true;
}
/// <summary>
/// Declare that the column of name columnName (which might or might not appear in DataTables being uploaded) should always have the associated database type (e.g. varchar(59))
/// The columnName is Case insensitive. Note that if AllowResizingColumnsAtUploadTime is true then these datatypes are only the starting types and might get changed later to
/// accomodate new data.
/// </summary>
/// <param name="columnName"></param>
/// <param name="explicitType"></param>
/// <param name="columnFlags"></param>
/// <returns>The Column Request that has been added to the array</returns>
public DatabaseColumnRequest AddExplicitWriteType(string columnName, string explicitType, ISupplementalColumnInformation columnFlags = null)
{
DatabaseColumnRequest columnRequest;
if (columnFlags == null)
{
columnRequest = new DatabaseColumnRequest(columnName, explicitType, true);
ExplicitTypes.Add(columnRequest);
return(columnRequest);
}
else
{
columnRequest = new DatabaseColumnRequest(columnName, explicitType, !columnFlags.IsPrimaryKey && !columnFlags.IsAutoIncrement)
{
IsPrimaryKey = columnFlags.IsPrimaryKey,
IsAutoIncrement = columnFlags.IsAutoIncrement,
Collation = columnFlags.Collation
};
ExplicitTypes.Add(columnRequest);
return(columnRequest);
}
}
public void BadNames_DiscoverRelationships(DatabaseType dbType)
{
var db = GetTestDatabase(dbType);
var tbl1 = db.CreateTable(BadTableName, new[]
{
new DatabaseColumnRequest(BadColumnName, new DatabaseTypeRequest(typeof(string), 100))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Frrrrr ##' ank", new DatabaseTypeRequest(typeof(int)))
});
DiscoveredColumn pk = tbl1.DiscoverColumns().Single(c => c.IsPrimaryKey);
DatabaseColumnRequest fk;
var tbl2 = db.CreateTable(new CreateTableArgs(db, BadTableName + "2", null)
{
ExplicitColumnDefinitions = new [] { fk = new DatabaseColumnRequest(BadColumnName + "2", new DatabaseTypeRequest(typeof(string), 100)) },
ForeignKeyPairs = new Dictionary <DatabaseColumnRequest, DiscoveredColumn> {
{ fk, pk }
}
});
var r = tbl1.DiscoverRelationships().Single();
Assert.AreEqual(tbl1, r.PrimaryKeyTable);
Assert.AreEqual(tbl2, r.ForeignKeyTable);
Assert.AreEqual(pk, r.Keys.Single().Key);
Assert.AreEqual(tbl2.DiscoverColumn(BadColumnName + "2"), r.Keys.Single().Value);
tbl2.Drop();
tbl1.Drop();
}
public ExternalCohortTable CreateDatabase(PrivateIdentifierPrototype privateIdentifierPrototype, ICheckNotifier notifier)
{
if (!_targetDatabase.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Did not find database " + _targetDatabase + " on server so creating it", CheckResult.Success));
_targetDatabase.Create();
}
try
{
var definitionTable = _targetDatabase.CreateTable("CohortDefinition", new[]
{
new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false, IsAutoIncrement = true, IsPrimaryKey = true
},
new DatabaseColumnRequest("projectNumber", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("description", new DatabaseTypeRequest(typeof(string), 3000))
{
AllowNulls = false
},
new DatabaseColumnRequest("dtCreated", new DatabaseTypeRequest(typeof(DateTime)))
{
AllowNulls = false, Default = MandatoryScalarFunctions.GetTodaysDate
}
});
var idColumn = definitionTable.DiscoverColumn("id");
var foreignKey = new DatabaseColumnRequest(_definitionTableForeignKeyField, new DatabaseTypeRequest(typeof(int)), false)
{
IsPrimaryKey = true
};
var cohortTable = _targetDatabase.CreateTable("Cohort", new []
{
new DatabaseColumnRequest(privateIdentifierPrototype.RuntimeName, privateIdentifierPrototype.DataType, false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest(_releaseIdentifierFieldName, new DatabaseTypeRequest(typeof(string), 300))
{
AllowNulls = AllowNullReleaseIdentifiers
},
foreignKey
}
,
//foreign key between id and cohortDefinition_id
new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ foreignKey, idColumn }
}, true);
notifier.OnCheckPerformed(new CheckEventArgs("About to create pointer to the source", CheckResult.Success));
var pointer = new ExternalCohortTable(_dataExportRepository, "TestExternalCohort", _targetDatabase.Server.DatabaseType)
{
DatabaseType = _targetDatabase.Server.DatabaseType,
Server = _targetDatabase.Server.Name,
Database = _targetDatabase.GetRuntimeName(),
Username = _targetDatabase.Server.ExplicitUsernameIfAny,
Password = _targetDatabase.Server.ExplicitPasswordIfAny,
Name = _targetDatabase.GetRuntimeName(),
TableName = cohortTable.GetRuntimeName(),
PrivateIdentifierField = privateIdentifierPrototype.RuntimeName,
ReleaseIdentifierField = _releaseIdentifierFieldName,
DefinitionTableForeignKeyField = _definitionTableForeignKeyField,
DefinitionTableName = definitionTable.GetRuntimeName()
};
pointer.SaveToDatabase();
notifier.OnCheckPerformed(new CheckEventArgs("successfully created reference to cohort source in data export manager", CheckResult.Success));
notifier.OnCheckPerformed(new CheckEventArgs("About to run post creation checks", CheckResult.Success));
pointer.Check(notifier);
notifier.OnCheckPerformed(new CheckEventArgs("Finished", CheckResult.Success));
return(pointer);
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs("Entire setup failed with exception (double click to find out why)",
CheckResult.Fail, e));
return(null);
}
}
public void Load(DatabaseType databaseType, TestCase testCase)
{
var defaults = new ServerDefaults(CatalogueRepository);
var logServer = defaults.GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID);
var logManager = new LogManager(logServer);
var db = GetCleanedServer(databaseType);
var raw = db.Server.ExpectDatabase(db.GetRuntimeName() + "_RAW");
if (raw.Exists())
{
raw.Drop();
}
var dt = new DataTable("MyTable");
dt.Columns.Add("Name");
dt.Columns.Add("DateOfBirth");
dt.Columns.Add("FavouriteColour");
dt.Rows.Add("Bob", "2001-01-01", "Pink");
dt.Rows.Add("Frank", "2001-01-01", "Orange");
var nameCol = new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 20), false)
{
IsPrimaryKey = true
};
if (testCase == TestCase.DodgyCollation)
{
if (databaseType == DatabaseType.MicrosoftSQLServer)
{
nameCol.Collation = "Latin1_General_CS_AS_KS_WS";
}
else if (databaseType == DatabaseType.MySql)
{
nameCol.Collation = "latin1_german1_ci";
}
}
DiscoveredTable tbl;
if (testCase == TestCase.WithNonPrimaryKeyIdentityColumn)
{
tbl = db.CreateTable("MyTable", new []
{
new DatabaseColumnRequest("ID", new DatabaseTypeRequest(typeof(int)), false)
{
IsPrimaryKey = false, IsAutoIncrement = true
},
nameCol,
new DatabaseColumnRequest("DateOfBirth", new DatabaseTypeRequest(typeof(DateTime)), false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("FavouriteColour", new DatabaseTypeRequest(typeof(string))),
});
using (var blk = tbl.BeginBulkInsert())
blk.Upload(dt);
Assert.AreEqual(1, tbl.DiscoverColumns().Count(c => c.GetRuntimeName().Equals("ID", StringComparison.CurrentCultureIgnoreCase)), "Table created did not contain ID column");
}
else
if (testCase == TestCase.AllPrimaryKeys)
{
dt.PrimaryKey = dt.Columns.Cast <DataColumn>().ToArray();
tbl = db.CreateTable("MyTable", dt, new [] { nameCol }); //upload the column as is
Assert.IsTrue(tbl.DiscoverColumns().All(c => c.IsPrimaryKey));
}
else
{
tbl = db.CreateTable("MyTable", dt, new[]
{
nameCol,
new DatabaseColumnRequest("DateOfBirth", new DatabaseTypeRequest(typeof(DateTime)), false)
{
IsPrimaryKey = true
}
});
}
Assert.AreEqual(2, tbl.GetRowCount());
//define a new load configuration
var lmd = new LoadMetadata(CatalogueRepository, "MyLoad");
if (testCase == TestCase.NoTrigger)
{
lmd.IgnoreTrigger = true;
lmd.SaveToDatabase();
}
TableInfo ti = Import(tbl, lmd, logManager);
var projectDirectory = SetupLoadDirectory(lmd);
CreateCSVProcessTask(lmd, ti, "*.csv");
//create a text file to load where we update Frank's favourite colour (it's a pk field) and we insert a new record (MrMurder)
File.WriteAllText(
Path.Combine(projectDirectory.ForLoading.FullName, "LoadMe.csv"),
@"Name,DateOfBirth,FavouriteColour
Frank,2001-01-01,Neon
MrMurder,2001-01-01,Yella");
//the checks will probably need to be run as ddl admin because it involves creating _Archive table and trigger the first time
//clean SetUp RAW / STAGING etc and generally accept proposed cleanup operations
var checker = new CheckEntireDataLoadProcess(lmd, new HICDatabaseConfiguration(lmd), new HICLoadConfigurationFlags(), CatalogueRepository.MEF);
checker.Check(new AcceptAllCheckNotifier());
//create a reader
if (testCase == TestCase.LowPrivilegeLoaderAccount)
{
SetupLowPrivilegeUserRightsFor(ti, TestLowPrivilegePermissions.Reader | TestLowPrivilegePermissions.Writer);
SetupLowPrivilegeUserRightsFor(db.Server.ExpectDatabase("DLE_STAGING"), TestLowPrivilegePermissions.All);
}
Assert.AreEqual(testCase != TestCase.NoTrigger, tbl.DiscoverColumns().Select(c => c.GetRuntimeName()).Contains(SpecialFieldNames.DataLoadRunID), $"When running with NoTrigger there shouldn't be any additional columns added to table. Test case was {testCase}");
Assert.AreEqual(testCase != TestCase.NoTrigger, tbl.DiscoverColumns().Select(c => c.GetRuntimeName()).Contains(SpecialFieldNames.ValidFrom), $"When running with NoTrigger there shouldn't be any additional columns added to table. Test case was {testCase}");
var dbConfig = new HICDatabaseConfiguration(lmd, testCase == TestCase.WithCustomTableNamer? new CustomINameDatabasesAndTablesDuringLoads():null);
if (testCase == TestCase.WithCustomTableNamer)
{
new PreExecutionChecker(lmd, dbConfig).Check(new AcceptAllCheckNotifier()); //handles staging database creation etc
}
if (testCase == TestCase.WithDiffColumnIgnoreRegex)
{
dbConfig.UpdateButDoNotDiff = new Regex("^FavouriteColour"); //do not diff FavouriteColour
}
var loadFactory = new HICDataLoadFactory(
lmd,
dbConfig,
new HICLoadConfigurationFlags(),
CatalogueRepository,
logManager
);
try
{
var exe = loadFactory.Create(new ThrowImmediatelyDataLoadEventListener());
var exitCode = exe.Run(
new DataLoadJob(RepositoryLocator, "Go go go!", logManager, lmd, projectDirectory, new ThrowImmediatelyDataLoadEventListener(), dbConfig),
new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
if (testCase == TestCase.AllPrimaryKeys)
{
Assert.AreEqual(4, tbl.GetRowCount()); //Bob, Frank, Frank (with also pk Neon) & MrMurder
Assert.Pass();
}
if (testCase == TestCase.WithDiffColumnIgnoreRegex)
{
Assert.AreEqual(3, tbl.GetRowCount()); //Bob, Frank (original since the diff was skipped), & MrMurder
//frank should be updated to like Neon instead of Orange
Assert.AreEqual(3, tbl.GetRowCount());
var frankOld = tbl.GetDataTable().Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "Frank");
Assert.AreEqual("Orange", frankOld["FavouriteColour"]);
Assert.Pass();
}
//frank should be updated to like Neon instead of Orange
Assert.AreEqual(3, tbl.GetRowCount());
var result = tbl.GetDataTable();
var frank = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "Frank");
Assert.AreEqual("Neon", frank["FavouriteColour"]);
if (testCase != TestCase.NoTrigger)
{
AssertHasDataLoadRunId(frank);
}
//MrMurder is a new person who likes Yella
var mrmurder = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "MrMurder");
Assert.AreEqual("Yella", mrmurder["FavouriteColour"]);
Assert.AreEqual(new DateTime(2001, 01, 01), mrmurder["DateOfBirth"]);
if (testCase != TestCase.NoTrigger)
{
AssertHasDataLoadRunId(mrmurder);
}
//bob should be untouched (same values as before and no dataloadrunID)
var bob = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "Bob");
Assert.AreEqual("Pink", bob["FavouriteColour"]);
Assert.AreEqual(new DateTime(2001, 01, 01), bob["DateOfBirth"]);
if (testCase != TestCase.NoTrigger)
{
Assert.AreEqual(DBNull.Value, bob[SpecialFieldNames.DataLoadRunID]);
//MySql add default of now() on a table will auto populate all the column values with the the now() date while Sql Server will leave them as nulls
if (databaseType == DatabaseType.MicrosoftSQLServer)
{
Assert.AreEqual(DBNull.Value, bob[SpecialFieldNames.ValidFrom]);
}
}
Assert.AreEqual(testCase != TestCase.NoTrigger, tbl.DiscoverColumns().Select(c => c.GetRuntimeName()).Contains(SpecialFieldNames.DataLoadRunID), $"When running with NoTrigger there shouldn't be any additional columns added to table. Test case was {testCase}");
Assert.AreEqual(testCase != TestCase.NoTrigger, tbl.DiscoverColumns().Select(c => c.GetRuntimeName()).Contains(SpecialFieldNames.ValidFrom), $"When running with NoTrigger there shouldn't be any additional columns added to table. Test case was {testCase}");
}
finally
{
Directory.Delete(lmd.LocationOfFlatFiles, true);
foreach (Catalogue c in RepositoryLocator.CatalogueRepository.GetAllObjects <Catalogue>())
{
c.DeleteInDatabase();
}
foreach (TableInfo t in RepositoryLocator.CatalogueRepository.GetAllObjects <TableInfo>())
{
t.DeleteInDatabase();
}
foreach (LoadMetadata l in RepositoryLocator.CatalogueRepository.GetAllObjects <LoadMetadata>())
{
l.DeleteInDatabase();
}
}
if (testCase == TestCase.WithCustomTableNamer)
{
var db2 = db.Server.ExpectDatabase("BB_STAGING");
if (db.Exists())
{
db2.Drop();
}
}
}
public void DLELoadTwoTables(DatabaseType databaseType)
{
//setup the data tables
var defaults = new ServerDefaults(CatalogueRepository);
var logServer = defaults.GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID);
var logManager = new LogManager(logServer);
var db = GetCleanedServer(databaseType);
var dtParent = new DataTable();
dtParent.Columns.Add("ID", typeof(int));
dtParent.Columns.Add("Name");
dtParent.Columns.Add("Height");
dtParent.PrimaryKey = new[] { dtParent.Columns[0] };
dtParent.Rows.Add("1", "Dave", "3.5");
var dtChild = new DataTable();
dtChild.Columns.Add("Parent_ID");
dtChild.Columns.Add("ChildNumber");
dtChild.Columns.Add("Name");
dtChild.Columns.Add("DateOfBirth");
dtChild.Columns.Add("Age");
dtChild.Columns.Add("Height");
dtChild.Rows.Add("1", "1", "Child1", "2001-01-01", "20", "3.5");
dtChild.Rows.Add("1", "2", "Child2", "2002-01-01", "19", "3.4");
dtChild.PrimaryKey = new[] { dtChild.Columns[0], dtChild.Columns[1] };
//create the parent table based on the DataTable
var parentTbl = db.CreateTable("Parent", dtParent);
//go find the primary key column created
var pkParentID = parentTbl.DiscoverColumn("ID");
//forward declare this column as part of pk (will be used to specify foreign key
var fkParentID = new DatabaseColumnRequest("Parent_ID", "int")
{
IsPrimaryKey = true
};
var args = new CreateTableArgs(
db,
"Child",
null,
dtChild,
false,
new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ fkParentID, pkParentID }
},
true);
args.ExplicitColumnDefinitions = new[]
{
fkParentID
};
var childTbl = db.CreateTable(args);
Assert.AreEqual(1, parentTbl.GetRowCount());
Assert.AreEqual(2, childTbl.GetRowCount());
//create a new load
var lmd = new LoadMetadata(CatalogueRepository, "MyLoading2");
TableInfo childTableInfo = Import(childTbl, lmd, logManager);
TableInfo parentTableInfo = Import(parentTbl, lmd, logManager);
var projectDirectory = SetupLoadDirectory(lmd);
CreateCSVProcessTask(lmd, parentTableInfo, "parent.csv");
CreateCSVProcessTask(lmd, childTableInfo, "child.csv");
//create a text file to load where we update Frank's favourite colour (it's a pk field) and we insert a new record (MrMurder)
File.WriteAllText(
Path.Combine(projectDirectory.ForLoading.FullName, "parent.csv"),
@"ID,Name,Height
2,Man2,3.1
1,Dave,3.2");
File.WriteAllText(
Path.Combine(projectDirectory.ForLoading.FullName, "child.csv"),
@"Parent_ID,ChildNumber,Name,DateOfBirth,Age,Height
1,1,UpdC1,2001-01-01,20,3.5
2,1,NewC1,2000-01-01,19,null");
//clean SetUp RAW / STAGING etc and generally accept proposed cleanup operations
var checker = new CheckEntireDataLoadProcess(lmd, new HICDatabaseConfiguration(lmd), new HICLoadConfigurationFlags(), CatalogueRepository.MEF);
checker.Check(new AcceptAllCheckNotifier());
var config = new HICDatabaseConfiguration(lmd);
var loadFactory = new HICDataLoadFactory(
lmd,
config,
new HICLoadConfigurationFlags(),
CatalogueRepository,
logManager
);
try
{
var exe = loadFactory.Create(new ThrowImmediatelyDataLoadEventListener());
var exitCode = exe.Run(
new DataLoadJob(RepositoryLocator, "Go go go!", logManager, lmd, projectDirectory, new ThrowImmediatelyDataLoadEventListener(), config),
new GracefulCancellationToken());
Assert.AreEqual(ExitCodeType.Success, exitCode);
//should now be 2 parents (the original - who was updated) + 1 new one (Man2)
Assert.AreEqual(2, parentTbl.GetRowCount());
var result = parentTbl.GetDataTable();
var dave = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "Dave");
Assert.AreEqual(3.2f, dave["Height"]); //should now be only 3.2 inches high
AssertHasDataLoadRunId(dave);
//should be 3 children (Child1 who gets updated to be called UpdC1) and NewC1
Assert.AreEqual(3, childTbl.GetRowCount());
result = childTbl.GetDataTable();
var updC1 = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "UpdC1");
Assert.AreEqual(1, updC1["Parent_ID"]);
Assert.AreEqual(1, updC1["ChildNumber"]);
AssertHasDataLoadRunId(updC1);
var newC1 = result.Rows.Cast <DataRow>().Single(r => (string)r["Name"] == "NewC1");
Assert.AreEqual(2, newC1["Parent_ID"]);
Assert.AreEqual(1, newC1["ChildNumber"]);
Assert.AreEqual(DBNull.Value, newC1["Height"]); //the "null" in the input file should be DBNull.Value in the final database
AssertHasDataLoadRunId(newC1);
}
finally
{
Directory.Delete(lmd.LocationOfFlatFiles, true);
foreach (Catalogue c in RepositoryLocator.CatalogueRepository.GetAllObjects <Catalogue>())
{
c.DeleteInDatabase();
}
foreach (TableInfo t in RepositoryLocator.CatalogueRepository.GetAllObjects <TableInfo>())
{
t.DeleteInDatabase();
}
foreach (LoadMetadata l in RepositoryLocator.CatalogueRepository.GetAllObjects <LoadMetadata>())
{
l.DeleteInDatabase();
}
}
}
/// <summary>
/// Create a new column based on the <see cref="DatabaseColumnRequest"/> (See <see cref="DicomTypeTranslater.GetNaturalTypeForVr(DicomVR, DicomVM)"/>)
/// </summary>
/// <param name="databaseColumnRequest"></param>
public ImageColumnTemplate(DatabaseColumnRequest databaseColumnRequest)
{
ColumnName = databaseColumnRequest.ColumnName;
AllowNulls = databaseColumnRequest.AllowNulls;
IsPrimaryKey = databaseColumnRequest.IsPrimaryKey;
}
