public void SubstituteANOIdentifiers_PreviewWithoutPush()
{
var anoTable = GetANOTable();
anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 0;
anoTable.NumberOfIntegersToUseInAnonymousRepresentation = 10;
DiscoveredTable ANOtable = ANOStore_Database.ExpectTable(anoTable.TableName);
//should not exist yet
Assert.False(ANOtable.Exists());
DataTable dt = new DataTable();
dt.Columns.Add("CHI");
dt.Columns.Add("ANOCHI");
dt.Rows.Add("0101010101", DBNull.Value);
ANOTransformer transformer = new ANOTransformer(anoTable, new ThrowImmediatelyDataLoadEventListener());
transformer.Transform(dt, dt.Columns["CHI"], dt.Columns["ANOCHI"], true);
Assert.IsTrue(_anochiPattern.IsMatch((string)dt.Rows[0][1]));//should be 10 digits and then _A
//still not exist yet
Assert.False(ANOtable.Exists());
anoTable.DeleteInDatabase();
}
private void CreateTableIfNotExists()
{
try
{
//create the database if it doesn't exist
if (!_table.Database.Exists())
{
_table.Database.Create();
}
//create the table if it doesn't exist
if (!_table.Exists())
{
_logger.Info("Guid mapping table does not exist, creating it now");
_table.Database.CreateTable(_table.GetRuntimeName(),
new[]
{
new DatabaseColumnRequest(_options.SwapColumnName, new DatabaseTypeRequest(typeof(string), 10), false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest(_options.ReplacementColumnName, new DatabaseTypeRequest(typeof(string), 255), false)
}
);
}
if (_table.Exists())
{
_logger.Info("Guid mapping table exist (" + _table + ")");
}
else
{
throw new Exception("Table creation did not result in table existing!");
}
_logger.Info("Checking for column " + _options.SwapColumnName);
_swapColumnLength = _table.DiscoverColumn(_options.SwapColumnName).DataType.GetLengthIfString();
_logger.Info("Checking for column " + _options.ReplacementColumnName);
_table.DiscoverColumn(_options.ReplacementColumnName);
}
catch (Exception e)
{
var sb = new StringBuilder();
if (_table != null)
{
sb.AppendLine("Server:" + _table.Database.Server.Name);
sb.AppendLine("Database:" + _table.Database.GetRuntimeName());
sb.AppendLine("Username:"******"Table:" + _table.GetFullyQualifiedName());
}
throw new Exception("Error creating/checking Guid substitution table on:" + Environment.NewLine + sb, e);
}
}
protected AlterTableCommandExecution(IBasicActivateItems activator, ITableInfo tableInfo) : base(activator)
{
TableInfo = tableInfo;
try
{
Table = TableInfo.Discover(DataAccessContext.InternalDataProcessing);
}
catch (Exception)
{
SetImpossible("Could not resolve Server/Table connection details");
return;
}
if (!Table.Exists())
{
SetImpossible("Table does not exist");
return;
}
if (Table.TableType != TableType.Table)
{
SetImpossible("Table is a " + Table.TableType);
return;
}
}
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);
}
}
}
/// <summary>
/// Creates the <paramref name="tableTemplate"/> in the database location <paramref name="expectedTable"/>.
/// </summary>
/// <param name="expectedTable"></param>
/// <param name="tableTemplate"></param>
public void CreateTable(DiscoveredTable expectedTable, ImageTableTemplate tableTemplate)
{
expectedTable.Database.CreateTable(expectedTable.GetRuntimeName(), tableTemplate.GetColumns(expectedTable.Database.Server.DatabaseType));
if (!expectedTable.Exists())
{
throw new Exception("Table did not exist after issuing create statement!");
}
}
public override void Setup(IMappingTableOptions options)
{
_options = options;
_swapTable = options.Discover();
_server = _swapTable.Database.Server;
if (!_swapTable.Exists())
{
throw new ArgumentException($"Swap table '{_swapTable.GetFullyQualifiedName()}' did not exist on server '{_server}'");
}
}
public bool DiscoverExistence(DataAccessContext context, out string reason)
{
if (_table.Exists())
{
reason = null;
return(true);
}
reason = "Table " + _table + " did not exist";
return(false);
}
private void ConfirmTableDeletion(DiscoveredTable expectTable)
{
if (expectTable.Exists())
{
var confirm = MessageBox.Show(String.Format("A table named {0} has been created as part of this import. Do you want to keep it?", expectTable.GetFullyQualifiedName()),
"Confirm", MessageBoxButtons.YesNo);
if (confirm == DialogResult.No)
{
expectTable.Drop();
}
}
}
void ui_PipelineExecutionFinishedsuccessfully(object sender, PipelineEngineEventArgs args)
{
if (!_table.Exists())
{
throw new Exception("Pipeline execute succesfully but the expected table '" + _table + "' did not exist");
}
var importer = new TableInfoImporter(BasicActivator.RepositoryLocator.CatalogueRepository, _table);
importer.DoImport(out var ti, out _);
BasicActivator.CreateAndConfigureCatalogue(ti, null, "Execution of '" + _aggregateConfiguration + "' (AggregateConfiguration ID =" + _aggregateConfiguration.ID + ")", ProjectSpecific, TargetFolder);
}
void ui_PipelineExecutionFinishedsuccessfully(object sender, PipelineEngineEventArgs args)
{
if (!_table.Exists())
{
throw new Exception("Pipeline execute succesfully but the expected table '" + _table + "' did not exist");
}
var importer = new TableInfoImporter(Activator.RepositoryLocator.CatalogueRepository, _table);
var createCatalogue = new ConfigureCatalogueExtractabilityUI(Activator, importer, "Execution of '" + _aggregateConfiguration + "' (AggregateConfiguration ID =" + _aggregateConfiguration.ID + ")", _projectSpecific);
createCatalogue.ShowDialog();
}
protected AlterTableCommandExecution(IBasicActivateItems activator, TableInfo tableInfo) : base(activator)
{
TableInfo = tableInfo;
Table = TableInfo.Discover(DataAccessContext.InternalDataProcessing);
if (!Table.Exists())
{
SetImpossible("Table does not exist");
return;
}
if (Table.TableType != TableType.Table)
{
SetImpossible("Table is a " + Table.TableType);
return;
}
}
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
{
var from = tableInfo.Discover(DataAccessContext.DataLoad);
//create a RAW table schema called TableName_Isolation
var cloner = new TableInfoCloneOperation(null, null, LoadBubble.Live);
cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);
if (!toCreate.Exists())
{
throw new Exception(string.Format("Table '{0}' did not exist after issuing create command", toCreate));
}
//Add the data load run id
toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
}
private void CreateIsolationTable(DiscoveredTable toCreate, TableInfo tableInfo)
{
var from = tableInfo.Discover(DataAccessContext.DataLoad);
//create a RAW table schema called TableName_Isolation
var cloner = new TableInfoCloneOperation(new HICDatabaseConfiguration(toCreate.Database.Server), tableInfo, LoadBubble.Live, _job ?? (IDataLoadEventListener) new ThrowImmediatelyDataLoadEventListener());
cloner.CloneTable(from.Database, toCreate.Database, from, toCreate.GetRuntimeName(), true, true, true, tableInfo.PreLoadDiscardedColumns);
if (!toCreate.Exists())
{
throw new Exception($"Table '{toCreate}' did not exist after issuing create command");
}
//Add the data load run id
toCreate.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), false, 10);
}
private void CheckCohortDatabaseHasCorrectTables(ICheckNotifier notifier)
{
try
{
var database = DataAccessPortal.GetInstance().ExpectDatabase(this, DataAccessContext.DataExport);
DiscoveredTable cohortTable = database.ExpectTable(TableName);
if (cohortTable.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Found table " + cohortTable + " in database " + Database, CheckResult.Success, null));
var columns = cohortTable.DiscoverColumns();
ComplainIfColumnMissing(TableName, columns, PrivateIdentifierField, notifier);
ComplainIfColumnMissing(TableName, columns, ReleaseIdentifierField, notifier);
ComplainIfColumnMissing(TableName, columns, DefinitionTableForeignKeyField, notifier);
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + TableName + " in database " + Database, CheckResult.Fail, null));
}
DiscoveredTable foundCohortDefinitionTable = database.ExpectTable(DefinitionTableName);
if (foundCohortDefinitionTable.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Found table " + DefinitionTableName + " in database " + Database, CheckResult.Success, null));
var cols = foundCohortDefinitionTable.DiscoverColumns();
foreach (string requiredField in ExternalCohortTable.CohortDefinitionTable_RequiredFields)
{
ComplainIfColumnMissing(DefinitionTableName, cols, requiredField, notifier);
}
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not find table " + DefinitionTableName + " in database " + Database, CheckResult.Fail, null));
}
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Could not check table intactness for ExternalCohortTable '" + Name + "'", CheckResult.Fail, e));
}
}
public override void WriteItems(DataTable items)
{
StripWhiteSpace(items);
items.TableName = _reportName;
if (!_tbl.Exists())
{
_tbl.Database.CreateTable(_tbl.GetRuntimeName(), items);
}
else
{
using (var insert = _tbl.BeginBulkInsert())
{
insert.Upload(items);
}
}
}
public DatabaseDestination(IsIdentifiableAbstractOptions options, string reportName)
: base(options)
{
var targetDatabase = new DiscoveredServer(options.DestinationConnectionString, options.DestinationDatabaseType).GetCurrentDatabase();
if (!targetDatabase.Exists())
{
throw new Exception("Destination database did not exist");
}
_tbl = targetDatabase.ExpectTable(reportName);
if (_tbl.Exists())
{
_tbl.Drop();
}
_reportName = reportName;
}
public void DiscoverState()
{
_unplannedChildren.Clear();
//assume no children exist
foreach (var anticipatedChild in _anticipatedChildren)
{
anticipatedChild.State = LoadDiagramState.NotFound;
}
//we dont exist either!
if (!Table.Exists())
{
State = LoadDiagramState.NotFound;
return;
}
//we do exist
State = LoadDiagramState.Found;
//discover children and marry them up to planned/ new unplanned ones
foreach (var discoveredColumn in Table.DiscoverColumns())
{
var match = _anticipatedChildren.SingleOrDefault(c => c.ColumnName.Equals(discoveredColumn.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase));
if (match != null)
{
match.SetState(discoveredColumn);
}
else
{
_unplannedChildren.Add(discoveredColumn); //unplanned column
}
}
//any NotFound or Different etc cols or any unplanned children
if (_anticipatedChildren.Any(c => c.State > LoadDiagramState.Found) || _unplannedChildren.Any())
{
State = LoadDiagramState.Different;//elevate our state to Different
}
}
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 Add(DiscoveredTable discoveredTable)
{
if (items.Any(i => i.Tag.Equals(discoveredTable)))
{
return;
}
var snip = new SubstringAutocompleteItem(discoveredTable.GetRuntimeName());
snip.MenuText = discoveredTable.GetRuntimeName(); //name of table
snip.Text = discoveredTable.GetFullyQualifiedName(); //full SQL
snip.Tag = discoveredTable; //record object for future reference
snip.ImageIndex = GetIndexFor(discoveredTable, RDMPConcept.TableInfo.ToString());
AddUnlessDuplicate(snip);
DiscoveredColumn[] columns = null;
try
{
if (discoveredTable.Exists())
{
columns = discoveredTable.DiscoverColumns();
}
}
catch (Exception)
{
//couldn't load nevermind
}
if (columns != null)
{
foreach (var col in columns)
{
Add(col);
}
}
}
public void DropTable()
{
//don't try to cleanup if there was Assert.Inconclusive because the server was inaccessible
if (_database == null)
{
return;
}
if (!_table.Exists())
{
return;
}
string problemsDroppingTrigger, thingsThatWorkedDroppingTrigger;
GetImplementer().DropTrigger(out problemsDroppingTrigger, out thingsThatWorkedDroppingTrigger);
if (_archiveTable.Exists())
{
_archiveTable.Drop();
}
_table.Drop();
}
public override ExitCodeType Attach(IDataLoadJob job, GracefulCancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(TableName) && TableToLoad != null)
{
var allTables = job.RegularTablesToLoad.Union(job.LookupTablesToLoad).Distinct().ToArray();
if (!allTables.Contains(TableToLoad))
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, $"FlatFileAttacher TableToLoad was '{TableToLoad}' (ID={TableToLoad.ID}) but that table was not one of the tables in the load:{string.Join(",", allTables.Select(t=>"'" + t.Name + "'"))}"));
}
TableName = TableToLoad.GetRuntimeName(LoadBubble.Raw, job.Configuration.DatabaseNamer);
}
if (TableName != null)
{
TableName = TableName.Trim();
}
Stopwatch timer = new Stopwatch();
timer.Start();
if (string.IsNullOrWhiteSpace(TableName))
{
throw new ArgumentNullException("TableName has not been set, set it in the DataCatalogue");
}
DiscoveredTable table = _dbInfo.ExpectTable(TableName);
//table didnt exist!
if (!table.Exists())
{
if (!_dbInfo.DiscoverTables(false).Any())//maybe no tables existed
{
throw new FlatFileLoadException("Raw database had 0 tables we could load");
}
else//no there are tables just not the one we were looking for
{
throw new FlatFileLoadException("RAW database did not have a table called:" + TableName);
}
}
//load the flat file
var filepattern = FilePattern ?? "*";
var filesToLoad = LoadDirectory.ForLoading.EnumerateFiles(filepattern).OrderBy(a => a.Name, StringComparer.InvariantCultureIgnoreCase).ToList();
if (!filesToLoad.Any())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Did not find any files matching pattern " + filepattern + " in forLoading directory"));
if (SendLoadNotRequiredIfFileNotFound)
{
return(ExitCodeType.OperationNotRequired);
}
return(ExitCodeType.Success);
}
foreach (var fileToLoad in filesToLoad)
{
LoadFile(table, fileToLoad, _dbInfo, timer, job, cancellationToken);
}
timer.Stop();
return(ExitCodeType.Success);
}
public void CloneTable(DiscoveredDatabase srcDatabaseInfo, DiscoveredDatabase destDatabaseInfo, DiscoveredTable sourceTable, string destTableName, bool dropHICColumns, bool dropIdentityColumns, bool allowNulls, PreLoadDiscardedColumn[] dilutionColumns)
{
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);
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Creating table with SQL:" + sql));
using (var con = destDatabaseInfo.Server.GetConnection())
{
con.Open();
using (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;
}
//drop hic_ columns
if (SpecialFieldNames.IsHicPrefixed(colName) && dropHICColumns)
{
drop = true;
}
//if the ColumnInfo is explicitly marked to be ignored
if (_tableInfo.ColumnInfos.Any(c => c.IgnoreInLoads && c.GetRuntimeName(_copyToBubble.ToLoadStage()).Equals(colName)))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is marked IgnoreInLoads"));
drop = true;
}
//also drop any columns we have specifically been told to ignore in the DLE configuration
if (_hicDatabaseConfiguration.IgnoreColumns != null && _hicDatabaseConfiguration.IgnoreColumns.IsMatch(colName))
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"{colName} will be dropped because it is matches the gloabl ignores pattern ({_hicDatabaseConfiguration.IgnoreColumns})"));
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 dilution = dilutionColumns.SingleOrDefault(c => c.GetRuntimeName().Equals(colName));
if (dilution != null)
{
_listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Altering diluted column {colName} to {dilution.Data_type}"));
column.DataType.AlterTypeTo(dilution.Data_type);
}
if (drop)
{
newTable.DropColumn(column);
}
}
}
///<inheritdoc/>
public void Check(ICheckNotifier notifier)
{
if (_table.Exists() && _archiveTable.Exists())
{
string[] liveCols = _table.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
string[] archiveCols = _archiveTable.DiscoverColumns().Select(c => c.GetRuntimeName()).ToArray();
var passed = CheckColumnOrderInTablesAndArchiveMatch(liveCols, archiveCols, notifier);
if (!passed)
{
return;
}
}
var factory = new TriggerImplementerFactory(_server.DatabaseType);
var implementer = factory.Create(_table);
bool present;
var primaryKeys = _table.DiscoverColumns().Where(c => c.IsPrimaryKey).ToArray();
//we don't know the primary keys
if (!primaryKeys.Any())
{
try
{
//see if it exists
present = implementer.GetTriggerStatus() == TriggerStatus.Enabled;
}
catch (TriggerMissingException)
{
//clearly it doesnt exist
present = false;
}
}
else
{
try
{
//we do know the primary keys
present = implementer.CheckUpdateTriggerIsEnabledAndHasExpectedBody();
}
catch (IrreconcilableColumnDifferencesInArchiveException e)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"Archive table for table " + _table +
" is corrupt, see inner Exception for specific errors", CheckResult.Fail, e));
return;
}
catch (Exception e)
{
NotifyFail(e, notifier, implementer);
return;
}
}
if (present)
{
notifier.OnCheckPerformed(new CheckEventArgs("Trigger presence/intactness for table " + _table + " matched expected presence", CheckResult.Success, null));
}
else
{
NotifyFail(null, notifier, implementer); //try creating it
}
}
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();
}
}
public void Dispose(IDataLoadEventListener listener, Exception pipelineFailureExceptionIfAny)
{
try
{
if (_managedConnection != null)
{
//if there was an error
if (pipelineFailureExceptionIfAny != null)
{
_managedConnection.ManagedTransaction.AbandonAndCloseConnection();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction rolled back sucessfully"));
if (_bulkcopy != null)
{
_bulkcopy.Dispose();
}
}
else
{
_managedConnection.ManagedTransaction.CommitAndCloseConnection();
if (_bulkcopy != null)
{
_bulkcopy.Dispose();
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Transaction committed sucessfully"));
}
}
}
catch (Exception e)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Commit failed on transaction (probably there was a previous error?)", e));
}
//if we have a primary key to create
if (pipelineFailureExceptionIfAny == null && _primaryKey != null && _primaryKey.Any() && discoveredTable != null && discoveredTable.Exists())
{
//Find the columns in the destination
var allColumns = discoveredTable.DiscoverColumns();
//if there are not yet any primary keys
if (allColumns.All(c => !c.IsPrimaryKey))
{
//find the columns the user decorated in his DataTable
DiscoveredColumn[] pkColumnsToCreate = allColumns.Where(c => _primaryKey.Any(pk => pk.Equals(c.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase))).ToArray();
//make sure we found all of them
if (pkColumnsToCreate.Length != _primaryKey.Count)
{
throw new Exception("Could not find primary key column(s) " + string.Join(",", _primaryKey) + " in table " + discoveredTable);
}
//create the primary key to match user provided columns
discoveredTable.CreatePrimaryKey(AlterTimeout, pkColumnsToCreate);
}
}
EndAuditIfExists();
}
public virtual string CreateTrigger(ICheckNotifier notifier, int timeout = 30)
{
if (!_primaryKeys.Any())
{
throw new TriggerException("There must be at least 1 primary key");
}
//if _Archive exists skip creating it
bool skipCreatingArchive = _archiveTable.Exists();
//check _Archive does not already exist
foreach (string forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })
{
if (_columns.Any(c => c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))
{
throw new TriggerException("Table " + _table + " already contains a column called " + forbiddenColumnName + " this column is reserved for Archiving");
}
}
bool b_mustCreate_validFrom = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));
bool b_mustCreate_dataloadRunId = !_columns.Any(c => c.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) && _createDataLoadRunIdAlso;
//forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table
if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)
{
throw new TriggerException("Cannot create trigger because table contains " + SpecialFieldNames.ValidFrom + " but not " + SpecialFieldNames.DataLoadRunID + " (ID must be placed before valid from in column order)");
}
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_dataloadRunId)
{
_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true, timeout);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var dateTimeDatatype = syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);
//must add validFrom outside of transaction if we want SMO to pick it up
if (b_mustCreate_validFrom)
{
_table.AddColumn(SpecialFieldNames.ValidFrom, string.Format(" {0} DEFAULT {1}", dateTimeDatatype, nowFunction), true, timeout);
}
//if we created columns we need to update _column
if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom)
{
_columns = _table.DiscoverColumns();
}
string sql = WorkOutArchiveTableCreationSQL();
if (!skipCreatingArchive)
{
using (var con = _server.GetConnection())
{
con.Open();
var cmdCreateArchive = _server.GetCommand(sql, con);
cmdCreateArchive.ExecuteNonQuery();
_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true, timeout);
_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true, timeout);
_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true, timeout);
}
}
return(sql);
}
public DataTable ProcessPipelineData(DataTable toProcess, IDataLoadEventListener listener, GracefulCancellationToken cancellationToken)
{
if (toProcess == null)
{
return(null);
}
IDatabaseColumnRequestAdjuster adjuster = null;
if (Adjuster != null)
{
var constructor = new ObjectConstructor();
adjuster = (IDatabaseColumnRequestAdjuster)constructor.Construct(Adjuster);
}
//work out the table name for the table we are going to create
if (TargetTableName == null)
{
if (string.IsNullOrWhiteSpace(toProcess.TableName))
{
throw new Exception("Chunk did not have a TableName, did not know what to call the newly created table");
}
TargetTableName = QuerySyntaxHelper.MakeHeaderNameSane(toProcess.TableName);
}
ClearPrimaryKeyFromDataTableAndExplicitWriteTypes(toProcess);
StartAuditIfExists(TargetTableName);
if (_loggingDatabaseListener != null)
{
listener = new ForkDataLoadEventListener(listener, _loggingDatabaseListener);
}
EnsureTableHasDataInIt(toProcess);
bool createdTable = false;
if (_firstTime)
{
bool tableAlreadyExistsButEmpty = false;
if (!_database.Exists())
{
throw new Exception("Database " + _database + " does not exist");
}
discoveredTable = _database.ExpectTable(TargetTableName);
//table already exists
if (discoveredTable.Exists())
{
tableAlreadyExistsButEmpty = true;
if (!AllowLoadingPopulatedTables)
{
if (discoveredTable.IsEmpty())
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Found table " + TargetTableName + " already, normally this would forbid you from loading it (data duplication / no primary key etc) but it is empty so we are happy to load it, it will not be created"));
}
else
{
throw new Exception("There is already a table called " + TargetTableName + " at the destination " + _database);
}
}
if (AllowResizingColumnsAtUploadTime)
{
_dataTypeDictionary = discoveredTable.DiscoverColumns().ToDictionary(k => k.GetRuntimeName(), v => v.GetDataTypeComputer(), StringComparer.CurrentCultureIgnoreCase);
}
}
else
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that the table name " + TargetTableName + " is unique at destination " + _database));
}
//create connection to destination
if (!tableAlreadyExistsButEmpty)
{
createdTable = true;
if (AllowResizingColumnsAtUploadTime)
{
_database.CreateTable(out _dataTypeDictionary, TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
else
{
_database.CreateTable(TargetTableName, toProcess, ExplicitTypes.ToArray(), true, adjuster);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Created table " + TargetTableName + " successfully."));
}
_managedConnection = _server.BeginNewTransactedConnection();
_bulkcopy = discoveredTable.BeginBulkInsert(_managedConnection.ManagedTransaction);
if (Culture != null)
{
_bulkcopy.DateTimeDecider.Culture = Culture;
}
_firstTime = false;
}
try
{
if (AllowResizingColumnsAtUploadTime && !createdTable)
{
ResizeColumnsIfRequired(toProcess, listener);
}
//push the data
swTimeSpentWritting.Start();
_affectedRows += _bulkcopy.Upload(toProcess);
swTimeSpentWritting.Stop();
listener.OnProgress(this, new ProgressEventArgs("Uploading to " + TargetTableName, new ProgressMeasurement(_affectedRows, ProgressType.Records), swTimeSpentWritting.Elapsed));
}
catch (Exception e)
{
_managedConnection.ManagedTransaction.AbandonAndCloseConnection();
if (LoggingServer != null)
{
_dataLoadInfo.LogFatalError(GetType().Name, ExceptionHelper.ExceptionToListOfInnerMessages(e, true));
}
throw new Exception("Failed to write rows (in transaction) to table " + TargetTableName, e);
}
return(null);
}
