private DataTableUploadDestination PrepareDestination(IDataLoadEventListener listener, DataTable toProcess)
{
//see if the user has entered an extraction server/database
if (TargetDatabaseServer == null)
{
throw new Exception("TargetDatabaseServer (the place you want to extract the project data to) property has not been set!");
}
try
{
if (!_destinationDatabase.Exists())
{
_destinationDatabase.Create();
}
if (_request is ExtractGlobalsCommand)
{
return(null);
}
var tblName = _toProcess.TableName;
//See if table already exists on the server (likely to cause problems including duplication, schema changes in configuration etc)
if (_destinationDatabase.ExpectTable(tblName).Exists())
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning,
"A table called " + tblName + " already exists on server " + TargetDatabaseServer +
", data load might crash if it is populated and/or has an incompatible schema"));
}
else
{
_tableDidNotExistAtStartOfLoad = true;
}
}
catch (Exception e)
{
//Probably the database didn't exist or the credentials were wrong or something
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to inspect destination for already existing datatables", e));
}
_destination = new DataTableUploadDestination();
PrimeDestinationTypesBasedOnCatalogueTypes(toProcess);
_destination.AllowResizingColumnsAtUploadTime = true;
_destination.AlterTimeout = AlterTimeout;
_destination.PreInitialize(_destinationDatabase, listener);
return(_destination);
}
public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration, AggregateOperation operation, out string sql)
{
var syntax = _database.Server.GetQuerySyntaxHelper();
var mgrTable = _database.ExpectTable(ResultsManagerTable);
using (var con = _server.GetConnection())
{
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand(
$@"Select
{syntax.EnsureWrapped("TableName")},
{syntax.EnsureWrapped("SqlExecuted")} from {mgrTable.GetFullyQualifiedName()}
WHERE {syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID}
AND {syntax.EnsureWrapped("Operation")} = '{operation}'", con))
{
using (var r = cmd.ExecuteReader())
if (r.Read())
{
string tableName = r["TableName"].ToString();
sql = r["SqlExecuted"] as string;
return(_database.ExpectTable(tableName));
}
}
}
sql = null;
return(null);
}
private void DeleteFullyNullRecords(string sourceTableName, DiscoveredDatabase dbInfo, IDataLoadJob job)
{
try
{
var cols = dbInfo.ExpectTable(sourceTableName).DiscoverColumns();
using (var con = dbInfo.Server.GetConnection())
{
con.Open();
using (var cmd = dbInfo.Server.GetCommand(
//Magical code that nukes blank/null rows - where all rows are blank/null
string.Format(@"delete from {0} WHERE {1}",
sourceTableName,
string.Join(" AND ",
cols.Select(c => "(" + c + " IS NULL OR " + c + "=''" + ")"))), con))
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "About to delete fully null records using SQL:" + cmd.CommandText));
cmd.CommandTimeout = 500000;
int affectedRows = cmd.ExecuteNonQuery();
if (affectedRows != 0)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Deleted " + affectedRows + " fully blank/null rows from RAW database"));
}
}
}
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Could not delete fully null records, this will not prevent the data load ocurring", e));
}
}
private void DropBadView(DiscoveredDatabase db, bool ignoreFailure)
{
using (var con = db.Server.GetConnection())
{
con.Open();
var cmd = db.Server.GetCommand($"DROP VIEW {GetBadTableName(db)}", con);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
if (!ignoreFailure)
{
throw;
}
Console.WriteLine("Drop view failed, this is expected, since FAnsi won't see this dodgy table name we can't drop it as normal before tests");
}
}
//the table that the view reads from
var abc = db.ExpectTable("ABC");
if (abc.Exists())
{
abc.Drop();
}
}
private void SetVersion(DiscoveredDatabase db, string name, string version)
{
var versionTable = db.ExpectTable(RoundhouseVersionTable, RoundhouseSchemaName);
versionTable.Truncate();
//repository_path version entry_date modified_date entered_by
//Patching 2.6.0.1 2018-02-05 08:26:54.000 2018-02-05 08:26:54.000 DUNDEE\TZNind
using (var con = db.Server.GetConnection())
{
con.Open();
var sql = "INSERT INTO " + versionTable.GetFullyQualifiedName() +
"(repository_path,version,entry_date,modified_date,entered_by) VALUES (@repository_path,@version,@entry_date,@modified_date,@entered_by)";
var cmd = db.Server.GetCommand(sql, con);
var dt = DateTime.Now;
db.Server.AddParameterWithValueToCommand("@repository_path", cmd, name);
db.Server.AddParameterWithValueToCommand("@version", cmd, version);
db.Server.AddParameterWithValueToCommand("@entry_date", cmd, dt);
db.Server.AddParameterWithValueToCommand("@modified_date", cmd, dt);
db.Server.AddParameterWithValueToCommand("@entered_by", cmd, Environment.UserName);
cmd.ExecuteNonQuery();
}
}
protected override void SetUp()
{
base.SetUp();
var workingDir = new DirectoryInfo(TestContext.CurrentContext.TestDirectory);
parentDir = workingDir.CreateSubdirectory("FlatFileAttacherTests");
DirectoryInfo toCleanup = parentDir.GetDirectories().SingleOrDefault(d => d.Name.Equals("Test_CSV_Attachment"));
if (toCleanup != null)
{
toCleanup.Delete(true);
}
LoadDirectory = LoadDirectory.CreateDirectoryStructure(parentDir, "Test_CSV_Attachment");
// create a separate builder for setting an initial catalog on (need to figure out how best to stop child classes changing ServerICan... as this then causes TearDown to fail)
_database = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using (var con = _database.Server.GetConnection())
{
con.Open();
var cmdCreateTable = _database.Server.GetCommand("CREATE Table " + _database.GetRuntimeName() + "..Bob([name] [varchar](500),[name2] [varchar](500))", con);
cmdCreateTable.ExecuteNonQuery();
}
_table = _database.ExpectTable("Bob");
}
private void ConfigureAndExecutePipeline1OnPipelineExecutionFinishedsuccessfully(object sender, PipelineEngineEventArgs args)
{
//pipeline executed successfully
if (_alsoForwardEngineerCatalogue)
{
string targetTable = null;
try
{
var dest = (DataTableUploadDestination)args.PipelineEngine.DestinationObject;
targetTable = dest.TargetTableName;
var table = _database.ExpectTable(targetTable);
var ui = new ConfigureCatalogueExtractabilityUI(_activator, new TableInfoImporter(_repositoryLocator.CatalogueRepository, table), "File '" + _file.FullName + "'", _projectSpecific);
ui.ShowDialog();
var cata = CatalogueCreatedIfAny = ui.CatalogueCreatedIfAny;
if (cata != null)
{
MessageBox.Show("Catalogue " + cata.Name + " successfully created");
}
else
{
MessageBox.Show("User cancelled Catalogue creation, data has been loaded and TableInfo/ColumnInfos exist in Data Catalogue but there will be no Catalogue");
}
this.ParentForm.Close();
}
catch (Exception e)
{
ExceptionViewer.Show("Failed to import TableInfo/Forward Engineer Catalogue from " + _database.ToString() + "(Table was " + (targetTable ?? "Null!") + ")" + " - see Exception for details", e);
}
}
}
public void SetUp(DiscoveredServer server)
{
_server = server;
var databaseToLoadName = "HICPipelineTests";
// Create the databases
server.ExpectDatabase(databaseToLoadName).Create(true);
server.ChangeDatabase(databaseToLoadName);
// Create the dataset table
DatabaseToLoad = server.ExpectDatabase(databaseToLoadName);
using (var con = DatabaseToLoad.Server.GetConnection())
{
con.Open();
const string createDatasetTableQuery =
"CREATE TABLE TestData ([Col1] [int], [hic_dataLoadRunID] [int] NULL, [hic_validFrom] [datetime] NULL, CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([Col1] ASC))";
const string addValidFromDefault =
"ALTER TABLE TestData ADD CONSTRAINT [DF_TestData__hic_validFrom] DEFAULT (getdate()) FOR [hic_validFrom]";
using (var cmd = DatabaseCommandHelper.GetCommand(createDatasetTableQuery, con))
cmd.ExecuteNonQuery();
using (var cmd = DatabaseCommandHelper.GetCommand(addValidFromDefault, con))
cmd.ExecuteNonQuery();
}
// Ensure the dataset table has been created
var datasetTable = DatabaseToLoad.ExpectTable("TestData");
Assert.IsTrue(datasetTable.Exists());
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dillutionColumns)
{
if (!sourceTable.Exists())
{
throw new Exception("Table " + sourceTable + " does not exist on " + srcDatabaseInfo);
}
//new table will start with the same name as the as the old scripted one
DiscoveredTable newTable = destDatabaseInfo.ExpectTable(destTableName);
var sql = sourceTable.ScriptTableCreation(allowNulls, allowNulls, false /*False because we want to drop these columns entirely not just flip to int*/, newTable);
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
var cmd = destDatabaseInfo.Server.GetCommand(sql, con);
cmd.ExecuteNonQuery();
}
if (!newTable.Exists())
{
throw new Exception("Table '" + newTable + "' not found in " + destDatabaseInfo + " despite running table creation SQL!");
}
foreach (DiscoveredColumn column in newTable.DiscoverColumns())
{
bool drop = false;
var colName = column.GetRuntimeName();
if (column.IsAutoIncrement)
{
drop = true;
}
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//drop the data load run ID field and validFrom fields, we don't need them in STAGING or RAW, it will be hard coded in the MERGE migration with a fixed value anyway.
if (colName.Equals(SpecialFieldNames.DataLoadRunID) || colName.Equals(SpecialFieldNames.ValidFrom))
{
drop = true;
}
var dillution = dillutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dillution != null)
{
column.DataType.AlterTypeTo(dillution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration, AggregateOperation operation)
{
using (var con = _server.GetConnection())
{
con.Open();
var r = DatabaseCommandHelper.GetCommand("Select TableName from CachedAggregateConfigurationResults WHERE AggregateConfiguration_ID = " + configuration.ID + " AND Operation = '" + operation + "'", con).ExecuteReader();
if (r.Read())
{
string tableName = r["TableName"].ToString();
return(_database.ExpectTable(tableName));
}
}
return(null);
}
public void RemoveTableFromDatabase(string tableName, DiscoveredDatabase dbInfo)
{
if (!IsNukable(dbInfo))
{
throw new Exception("This method nukes a table in a database! for obvious reasons this is only allowed on databases with a suffix _STAGING/_RAW");
}
dbInfo.ExpectTable(tableName).Drop();
}
public ExitCodeType Mutilate(IDataLoadJob job)
{
var tbl = _database.ExpectTable(ColumnToResolveOn.TableInfo.GetRuntimeName(_loadStage, job.Configuration.DatabaseNamer));
var pks = ColumnToResolveOn.TableInfo.ColumnInfos.Where(ci => ci.IsPrimaryKey).ToArray();
DeleteRows(tbl, pks, job);
return(ExitCodeType.Success);
}
private void CheckTableHasColumnInfosAndPrimaryKeys(DiscoveredDatabase live, TableInfo tableInfo, out ColumnInfo[] columnInfos, out ColumnInfo[] columnInfosWhichArePrimaryKeys, ICheckNotifier notifier)
{
columnInfos = tableInfo.ColumnInfos.ToArray();
columnInfosWhichArePrimaryKeys = columnInfos.Where(col => col.IsPrimaryKey).ToArray();
//confirm there are at least 1
if (!columnInfos.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + tableInfo.Name + " has no columninfos",
CheckResult.Fail, null));
}
if (!columnInfosWhichArePrimaryKeys.Any())
{
notifier.OnCheckPerformed(new CheckEventArgs(
"TableInfo " + tableInfo.Name + " has no IsPrimaryKey columns", CheckResult.Fail, null));
}
var primaryKeys = live.ExpectTable(tableInfo.GetRuntimeName(), tableInfo.Schema).DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray();
if (primaryKeys.Any(k => k.IsAutoIncrement))
{
notifier.OnCheckPerformed(new CheckEventArgs("AutoIncrement columns " + string.Join(",", primaryKeys.Where(k => k.IsAutoIncrement)) + " are not allowed as Primary Keys for your table because there is no way to differentiate new data being loaded from duplicate old data being loaded (the entire purpose of the RDMP DLE)", CheckResult.Fail));
}
//confirm primary keys match underlying table
//sort pks alphabetically and confirm they match the underlying live system table
string[] actualPks = primaryKeys.Select(c => c.GetRuntimeName()).OrderBy(s => s).ToArray();
string[] pksWeExpect = columnInfosWhichArePrimaryKeys.Select(c => c.GetRuntimeName()).OrderBy(s => s).ToArray();
if (actualPks.Length != pksWeExpect.Length)
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Primary keys in Catalogue for database table " + tableInfo.GetRuntimeName() +
" does not match Catalogue entry (difference in number of keys)", CheckResult.Fail, null));
}
else
{
for (int i = 0; i < pksWeExpect.Length; i++)
{
if (!pksWeExpect[i].Equals(actualPks[i]))
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Mismatch between primary key defined in Catalogue " + pksWeExpect[i] + " and one found in live table " + tableInfo.GetRuntimeName(), CheckResult.Fail, null));
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs(
"Found primary key " + pksWeExpect[i] + " in LIVE table " + tableInfo.GetRuntimeName(), CheckResult.Success, null));
}
}
}
}
private void CheckTableInfo(TableInfo tableInfo, ICheckNotifier notifier)
{
//get all columns
ColumnInfo[] columnInfos;
ColumnInfo[] columnInfosWhichArePrimaryKeys;
//check whether the live database and staging databases have appropriate columns and triggers etc on them
DiscoveredDatabase staging = _databaseConfiguration.DeployInfo[LoadBubble.Staging];
DiscoveredDatabase live = DataAccessPortal.GetInstance().ExpectDatabase(tableInfo, DataAccessContext.DataLoad);
var liveTable = live.ExpectTable(tableInfo.GetRuntimeName(LoadBubble.Live, _databaseConfiguration.DatabaseNamer), tableInfo.Schema);
var liveCols = liveTable.DiscoverColumns();
CheckTableInfoSynchronization(tableInfo, notifier);
CheckTableHasColumnInfosAndPrimaryKeys(live, tableInfo, out columnInfos, out columnInfosWhichArePrimaryKeys, notifier);
try
{
//if trigger is created as part of this check then it is likely to have resulted in changes to the underlying table (e.g. added hic_validFrom field) in which case we should resynch the TableInfo to pickup these new columns
bool runSynchronizationAgain;
CheckTriggerIntact(liveTable, notifier, out runSynchronizationAgain);
if (runSynchronizationAgain)
{
CheckTableInfoSynchronization(tableInfo, notifier);
}
if (!_databaseConfiguration.RequiresStagingTableCreation)
{
//Important:
//Regarding this if block: None of the current loading systems in RDMP have RequiresStagingTableCreation as false but in theory you could build some kind of multi threaded horror
//which had multiple simultaneous loads all populating a single STAGING bubble therefore this code is left in the codebase, it probably works ok but you will need a
//fair bit of work if you want to realise such a monstrosity (for one cleanup on each run probably cleans up STAGING on exit)
//live can have additional hic_ columns which do not appear in staging (lookups cannot)
var stagingTable = staging.ExpectTable(tableInfo.GetRuntimeName(LoadBubble.Staging, _databaseConfiguration.DatabaseNamer));
if (!stagingTable.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("RequiresStagingTableCreation is false but staging does not exist, this flag should indicate that you anticipate STAGING to be already setup before you kick off the load", CheckResult.Fail));
}
var stagingCols = stagingTable.DiscoverColumns();
ConfirmStagingAndLiveHaveSameColumns(tableInfo.GetRuntimeName(), stagingCols, liveCols, false, notifier);
CheckStagingToLiveMigrationForTable(stagingTable, stagingCols, liveTable, liveCols, notifier);
}
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Load Checks Crashed " + tableInfo, CheckResult.Fail, e));
}
}
public void RemoveTablesFromDatabase(IEnumerable <string> tableNames, DiscoveredDatabase dbInfo)
{
if (!IsNukable(dbInfo))
{
throw new Exception("This method loops through every table in a database and nukes it! for obvious reasons this is only allowed on databases with a suffix _STAGING/_RAW");
}
foreach (var tableName in tableNames)
{
dbInfo.ExpectTable(tableName).Drop();
}
}
/// <summary>
/// Creates the <see cref="BulkDataTable"/> in the <see cref="BulkDataDatabase"/> and uploads test data. Use <see cref="ImportAsCatalogue"/> to get
/// rdmp metadata objects pointing at the table.
/// </summary>
public void SetupTestData()
{
//make sure database exists
if (!BulkDataDatabase.Exists())
{
BulkDataDatabase.Create();
}
//generate some people
var people = new PersonCollection();
people.GeneratePeople(5000, r);
//generate the test data
var dt = _dataGenerator.GetDataTable(people, ExpectedNumberOfRowsInTestData);
var tbl = BulkDataDatabase.ExpectTable(BulkDataTable);
if (tbl.Exists())
{
tbl.Drop();
}
//create the table but make sure the chi is a primary key and the correct data type and that we have a sensible primary key
Table = BulkDataDatabase.CreateTable(BulkDataTable, dt, new DatabaseColumnRequest[] {
new DatabaseColumnRequest("chi", new DatabaseTypeRequest(typeof(string), 10))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("dtCreated", new DatabaseTypeRequest(typeof(DateTime)))
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("hb_extract", new DatabaseTypeRequest(typeof(string), 1))
{
IsPrimaryKey = true
}
});
}
public UIDMapping[] LoadMappingsForProject(int projectNumber)
{
var table = _database.ExpectTable(_tableName);
using (var conn = GetConnection())
{
conn.Open();
var cmd =
DatabaseCommandHelper.GetCommand(
"SELECT * FROM " + table.GetFullyQualifiedName() + " WHERE ProjectNumber = @ProjectNumber", conn);
DatabaseCommandHelper.AddParameterWithValueToCommand("@ProjectNumber", cmd, projectNumber);
var reader = cmd.ExecuteReader();
var mappings = new List <UIDMapping>();
while (reader.Read())
{
var mappingFromDatabase = HydrateMapping(reader);
mappings.Add(mappingFromDatabase);
}
return(mappings.ToArray());
}
}
public void CreateTable(DatabaseType dbType)
{
_database = GetCleanedServer(dbType);
_table = _database.CreateTable("TriggerTests", new DatabaseColumnRequest[] {
new DatabaseColumnRequest("name", new DatabaseTypeRequest(typeof(string), 30))
{
AllowNulls = false
},
new DatabaseColumnRequest("bubbles", new DatabaseTypeRequest(typeof(int))),
});
_archiveTable = _database.ExpectTable("TriggerTests_Archive");
}
public SqlBulkInsertDestination(DiscoveredDatabase dbInfo, string tableName, IEnumerable <string> columnNamesToIgnore)
{
_dbInfo = dbInfo;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("Parameter tableName is not specified for SqlBulkInsertDestination");
}
Table = _dbInfo.Server.GetQuerySyntaxHelper().EnsureWrapped(tableName);
_table = _dbInfo.ExpectTable(tableName);
_columnNamesToIgnore = columnNamesToIgnore.ToList();
_taskBeingPerformed = "Bulk insert into " + Table + "(server=" + _dbInfo.Server + ",database=" + _dbInfo.GetRuntimeName() + ")";
}
public static Version GetVersionFromDatabase(DiscoveredDatabase database)
{
var tbl = database.ExpectTable(MasterDatabaseScriptExecutor.RoundhouseVersionTable,
MasterDatabaseScriptExecutor.GetRoundhouseSchemaName(database));
//versions in the database (should only be 1)
var versions = tbl.GetDataTable().Rows.Cast <DataRow>().Select(r =>
r["version"] == DBNull.Value ? new Version(0, 0, 0, 0) : new Version(r["version"].ToString()))
.ToArray();
if (versions.Length == 0)
{
return(new Version(0, 0, 0, 0));
}
return(versions.Max());
}
public void CreateRAWTablesInDatabase(DiscoveredDatabase rawDb, IDataLoadJob job)
{
var namer = job.Configuration.DatabaseNamer;
foreach (TableInfo tableInfo in job.RegularTablesToLoad)
{
var liveTable = tableInfo.Discover(DataAccessContext.DataLoad);
var rawTableName = namer.GetName(liveTable.GetRuntimeName(), LoadBubble.Raw);
var rawTable = rawDb.ExpectTable(rawTableName);
if (rawTable.Exists())
{
rawTable.Drop();
}
var discardedColumns = tableInfo.PreLoadDiscardedColumns.Where(c => c.Destination == DiscardedColumnDestination.Dilute).ToArray();
var clone = new TableInfoCloneOperation(null, null, LoadBubble.Raw);
clone.CloneTable(liveTable.Database, rawDb, tableInfo.Discover(DataAccessContext.DataLoad), rawTableName, true, true, true, discardedColumns);
string[] existingColumns = tableInfo.ColumnInfos.Select(c => c.GetRuntimeName(LoadStage.AdjustRaw)).ToArray();
foreach (PreLoadDiscardedColumn preLoadDiscardedColumn in tableInfo.PreLoadDiscardedColumns)
{
//this column does not get dropped so will be in live TableInfo
if (preLoadDiscardedColumn.Destination == DiscardedColumnDestination.Dilute)
{
continue;
}
if (existingColumns.Any(e => e.Equals(preLoadDiscardedColumn.GetRuntimeName(LoadStage.AdjustRaw))))
{
throw new Exception("There is a column called " + preLoadDiscardedColumn.GetRuntimeName(LoadStage.AdjustRaw) + " as both a PreLoadDiscardedColumn and in the TableInfo (live table), you should either drop the column from the live table or remove it as a PreLoadDiscarded column");
}
//add all the preload discarded columns because they could be routed to ANO store or sent to oblivion
AddColumnToTable(rawTable, preLoadDiscardedColumn.RuntimeColumnName, preLoadDiscardedColumn.SqlDataType, job);
}
_rawTables.Add(rawTable);
}
}
private void FireMutilate(ITableInfo tableInfo, IDataLoadJob job)
{
var tbl = DbInfo.ExpectTable(tableInfo.GetRuntimeName(_loadStage, job.Configuration.DatabaseNamer));
if (!tbl.Exists())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Expected table " + tbl + " did not exist in RAW"));
}
else
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to run " + GetType() + " mutilation on table " + tbl));
Stopwatch sw = new Stopwatch();
sw.Start();
MutilateTable(job, tableInfo, tbl);
sw.Stop();
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, GetType() + " mutilation on table " + tbl + " completed after " + sw.ElapsedMilliseconds + " ms"));
}
}
protected void AssessMissingAndIgnoredColumns(DataTable chunk, IDataLoadEventListener job)
{
DiscoveredColumn[] listColumns = _dbInfo.ExpectTable(Table).DiscoverColumns();
bool problemsWithColumnSets = false;
foreach (DataColumn colInSource in chunk.Columns)
{
if (!listColumns.Any(c => c.GetRuntimeName().Equals(colInSource.ColumnName, StringComparison.CurrentCultureIgnoreCase)))//there is something wicked this way coming, down the pipeline but not in the target table
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error,
"Column " + colInSource.ColumnName + " appears in pipeline but not destination table (" + Table + ") which is on (Database=" + _dbInfo.GetRuntimeName() + ",Server=" + _dbInfo.Server + ")"));
problemsWithColumnSets = true;
}
}
foreach (DiscoveredColumn columnInDestination in listColumns)
{
if (columnInDestination.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID) ||
columnInDestination.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom))
{
//its fine if validFrom/DataLoadRunID columns are missing
continue;//its fine
}
else
if (!chunk.Columns.Contains(columnInDestination.GetRuntimeName())) //its not fine if there are other columns missing (at the very least we should warn the user.
{
bool isBigProblem = !SpecialFieldNames.IsHicPrefixed(columnInDestination);
job.OnNotify(this,
new NotifyEventArgs(isBigProblem?ProgressEventType.Error:ProgressEventType.Warning, //hic_ columns could be ok if missing so only warning, otherwise go error
"Column " + columnInDestination.GetRuntimeName() + " appears in destination table (" + Table + ") but is not in the pipeline (will probably be left as NULL)"));
if (isBigProblem)
{
problemsWithColumnSets = true;
}
}
}
if (problemsWithColumnSets)
{
throw new Exception("There was a mismatch between the columns in the pipeline and the destination table, check earlier progress messages for details on the missing columns");
}
}
protected override void SetUp()
{
base.SetUp();
_database = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
_server = _database.Server;
using (var con = _server.GetConnection())
{
con.Open();
_server.GetCommand("CREATE TABLE " + TABLE_NAME + "(Name varchar(10), Address varchar(500))", con).ExecuteNonQuery();
}
var tbl = _database.ExpectTable("TableInfoSynchronizerTests");
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, tbl);
importer.DoImport(out tableInfoCreated, out columnInfosCreated);
}
/// <summary>
/// Creates a table HospitalAdmissions with no filters
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <returns></returns>
private AggregateConfiguration SetupAggregateConfiguration(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic)
{
var existingTbl = db.ExpectTable("HospitalAdmissions");
var tbl = existingTbl.Exists() ? existingTbl : CreateDataset <HospitalAdmissions>(db, people, 10000, r);
var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis);
var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase));
chi.IsExtractionIdentifier = true;
chi.SaveToDatabase();
var ac = new AggregateConfiguration(CatalogueRepository, cata, "Hospitalised after NA");
ac.AddDimension(chi);
ac.CountSQL = null;
cic.EnsureNamingConvention(ac);
return(ac);
}
public IList <MigrationColumnSet> CreateMigrationColumnSetFromTableInfos(List <ITableInfo> tableInfos, List <ITableInfo> lookupTableInfos, IMigrationFieldProcessor migrationFieldProcessor)
{
//treat null values as empty
tableInfos = tableInfos ?? new List <ITableInfo>();
lookupTableInfos = lookupTableInfos ?? new List <ITableInfo>();
var columnSet = new List <MigrationColumnSet>();
foreach (var tableInfo in tableInfos.Union(lookupTableInfos))
{
var fromTableName = tableInfo.GetRuntimeName(_fromBubble, _namer);
var toTableName = tableInfo.GetRuntimeName(_toBubble, _namer);
DiscoveredTable fromTable = _fromDatabaseInfo.ExpectTable(fromTableName); //Staging doesn't have schema e.g. even if live schema is not dbo STAGING will be
DiscoveredTable toTable = DataAccessPortal.GetInstance()
.ExpectDatabase(tableInfo, DataAccessContext.DataLoad)
.ExpectTable(toTableName, tableInfo.Schema);
if (!fromTable.Exists())
{
if (lookupTableInfos.Contains(tableInfo))//its a lookup table which doesn't exist in from (Staging) - nevermind
{
continue;
}
else
{
throw new Exception("Table " + fromTableName + " was not found on on server " + _fromDatabaseInfo.Server + " (Database " + _fromDatabaseInfo + ")"); //its not a lookup table if it isn't in STAGING thats a problem!
}
}
columnSet.Add(new MigrationColumnSet(fromTable, toTable, migrationFieldProcessor));
}
var sorter = new RelationshipTopologicalSort(columnSet.Select(c => c.DestinationTable));
columnSet = columnSet.OrderBy(s => ((ReadOnlyCollection <DiscoveredTable>)sorter.Order).IndexOf(s.DestinationTable)).ToList();
return(columnSet);
}
public void SynchronizationTests_ColumnDropped(bool acceptChanges)
{
Assert.AreEqual(TABLE_NAME, tableInfoCreated.GetRuntimeName());
var table = _database.ExpectTable(TABLE_NAME);
var colToDrop = table.DiscoverColumn("Address");
table.DropColumn(colToDrop);
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
if (acceptChanges)
{
//accept changes should result in a synchronized table
Assert.AreEqual(true, synchronizer.Synchronize(new AcceptAllCheckNotifier()));
Assert.AreEqual(1, tableInfoCreated.ColumnInfos.Length);//should only be 1 remaining
}
else
{
var ex = Assert.Throws <Exception>(() => synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
Assert.AreEqual("The ColumnInfo Address no longer appears in the live table.", ex.Message);
}
}
private void CheckTablesDoNotExistOnStaging(IEnumerable <ITableInfo> allTableInfos)
{
DiscoveredDatabase stagingDbInfo = _databaseConfiguration.DeployInfo[LoadBubble.Staging];
var alreadyExistingTableInfosThatShouldntBeThere = new List <string>();
var tableNames = allTableInfos.Select(info => info.GetRuntimeName(LoadBubble.Staging, _databaseConfiguration.DatabaseNamer));
foreach (string tableName in tableNames)
{
if (stagingDbInfo.ExpectTable(tableName).Exists())
{
alreadyExistingTableInfosThatShouldntBeThere.Add(tableName);
}
}
if (alreadyExistingTableInfosThatShouldntBeThere.Any())
{
bool nukeTables;
nukeTables = _notifier.OnCheckPerformed(new CheckEventArgs(
"The following tables: '" +
alreadyExistingTableInfosThatShouldntBeThere.Aggregate("", (s, n) => s + n + ",") +
"' exists in the Staging database (" + stagingDbInfo.GetRuntimeName() +
") but the database load configuration requires that tables are created during the load process",
CheckResult.Fail, null, "Drop the tables"));
if (nukeTables)
{
RemoveTablesFromDatabase(alreadyExistingTableInfosThatShouldntBeThere, stagingDbInfo);
}
}
else
{
_notifier.OnCheckPerformed(new CheckEventArgs("Staging table is clear", CheckResult.Success, null));
}
}
private void OnPipelineCompleted(object sender, PipelineEngineEventArgs args, DiscoveredDatabase db)
{
var engine = args.PipelineEngine;
//todo figure out what it created
var dest = engine.DestinationObject as DataTableUploadDestination;
if (dest == null)
{
throw new Exception($"Destination of engine was unexpectedly not a DataTableUploadDestination despite use case {nameof(UploadFileUseCase)}");
}
if (string.IsNullOrWhiteSpace(dest.TargetTableName))
{
throw new Exception($"Destination of engine failed to populate {dest.TargetTableName}");
}
var tbl = db.ExpectTable(dest.TargetTableName);
if (!tbl.Exists())
{
throw new Exception($"Destination of engine claimed to have created {tbl.GetFullyQualifiedName()} but it did not exist");
}
var importer = new TableInfoImporter(BasicActivator.RepositoryLocator.CatalogueRepository, tbl);
importer.DoImport(out var ti, out _);
var cata = BasicActivator.CreateAndConfigureCatalogue(ti, null, $"Import of file '{File.FullName}' by {Environment.UserName} on {DateTime.Now}", ProjectSpecific, TargetFolder);
if (cata != null)
{
Publish(cata);
Emphasise(cata);
}
}
public void EndToEndTest()
{
var cohortDatabaseNameWillBe = TestDatabaseNames.GetConsistentName("TbvCohort");
_discoveredCohortDatabase = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(cohortDatabaseNameWillBe);
//cleanup
if (_discoveredCohortDatabase.Exists())
{
_discoveredCohortDatabase.Drop();
}
//create a normal catalogue
CreateANormalCatalogue();
//create a cohort database using wizard
CreateNewCohortDatabaseWizard cohortDatabaseWizard = new CreateNewCohortDatabaseWizard(_discoveredCohortDatabase, CatalogueRepository, DataExportRepository, false);
_externalCohortTable = cohortDatabaseWizard.CreateDatabase(
new PrivateIdentifierPrototype(_nonTvfExtractionIdentifier)
, new ThrowImmediatelyCheckNotifier());
//create a table valued function
CreateTvfCatalogue(cohortDatabaseNameWillBe);
//Test 1
TestThatQueryBuilderWithoutParametersBeingSetThrowsQueryBuildingException();
PopulateCohortDatabaseWithRecordsFromNonTvfCatalogue();
//Test 2
TestWithParameterValueThatRowsAreReturned();
//Test 3
TestUsingTvfForAggregates();
//Test 4
TestAddingTvfToCIC();
//Test 5
TestDataExportOfTvf();
//tear down
DataExportRepository.GetAllObjects <ExtractableCohort>().Single().DeleteInDatabase();
_externalCohortTable.DeleteInDatabase();
_database.ExpectTable("NonTVFTable").Drop();
_database.ExpectTableValuedFunction("GetTopXRandom").Drop();
//delete global parameter
((AnyTableSqlParameter)_aggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_aggregate.DeleteInDatabase();
((AnyTableSqlParameter)_cicAggregate.GetAllParameters().Single()).DeleteInDatabase();
//delete aggregate
_cicAggregate.DeleteInDatabase();
//get rid of the cohort identification configuration
_cic.DeleteInDatabase();
_pipe.DeleteInDatabase();
//get rid of the cohort database
_discoveredCohortDatabase.Drop();
_nonTvfCatalogue.DeleteInDatabase();
_nonTvfTableInfo.DeleteInDatabase();
_tvfCatalogue.DeleteInDatabase();
_tvfTableInfo.DeleteInDatabase();
}
