private void CreateAttacher(ITableInfo t, QueryBuilder qb, LoadMetadata lmd, LoadProgress loadProgressIfAny)
{
var pt = new ProcessTask(Activator.RepositoryLocator.CatalogueRepository, lmd, LoadStage.Mounting);
pt.ProcessTaskType = ProcessTaskType.Attacher;
pt.Name = "Read from " + t;
pt.Path = typeof(RemoteTableAttacher).FullName;
pt.SaveToDatabase();
pt.CreateArgumentsForClassIfNotExists <RemoteTableAttacher>();
pt.SetArgumentValue("RemoteServer", t.Server);
pt.SetArgumentValue("RemoteDatabaseName", t.GetDatabaseRuntimeName(LoadStage.PostLoad));
pt.SetArgumentValue("RemoteTableName", t.GetRuntimeName());
pt.SetArgumentValue("DatabaseType", DatabaseType.MicrosoftSQLServer);
pt.SetArgumentValue("RemoteSelectSQL", qb.SQL);
pt.SetArgumentValue("RAWTableName", t.GetRuntimeName(LoadBubble.Raw));
if (loadProgressIfAny != null)
{
pt.SetArgumentValue("Progress", loadProgressIfAny);
// pt.SetArgumentValue("ProgressUpdateStrategy", DataLoadProgressUpdateStrategy.UseMaxRequestedDay);
pt.SetArgumentValue("LoadNotRequiredIfNoRowsRead", true);
}
/*
*
* public DataLoadProgressUpdateInfo { get; set; }
*/
}
public void SynchronizationTests_NoChanges()
{
Assert.AreEqual(TABLE_NAME, tableInfoCreated.GetRuntimeName());
TableInfoSynchronizer synchronizer = new TableInfoSynchronizer(tableInfoCreated);
Assert.AreEqual(true, synchronizer.Synchronize(new ThrowImmediatelyCheckNotifier()));
}
public void Add(ITableInfo tableInfo, LoadStage loadStage)
{
//we already have it or it is not setup properly
if (items.Any(i => i.Tag.Equals(tableInfo)) || string.IsNullOrWhiteSpace(tableInfo.Database) || string.IsNullOrWhiteSpace(tableInfo.Server))
{
return;
}
var runtimeName = tableInfo.GetRuntimeName(loadStage);
var dbName = tableInfo.GetDatabaseRuntimeName(loadStage);
var syntaxHelper = tableInfo.GetQuerySyntaxHelper();
var fullSql = syntaxHelper.EnsureFullyQualified(dbName, null, runtimeName);
var snip = new SubstringAutocompleteItem(tableInfo.GetRuntimeName());
snip.MenuText = runtimeName; //name of table
snip.Text = fullSql; //full SQL
snip.Tag = tableInfo; //record object for future reference
snip.ImageIndex = GetIndexFor(tableInfo, RDMPConcept.TableInfo.ToString());
foreach (IHasStageSpecificRuntimeName o in tableInfo.GetColumnsAtStage(loadStage))
{
var preDiscarded = o as PreLoadDiscardedColumn;
var columnInfo = o as ColumnInfo;
if (preDiscarded != null)
{
Add(preDiscarded, tableInfo, dbName);
}
else
if (columnInfo != null)
{
Add(columnInfo, tableInfo, dbName, loadStage, syntaxHelper);
}
else
{
throw new Exception("Expected IHasStageSpecificRuntimeName returned by TableInfo.GetColumnsAtStage to return only ColumnInfos and PreLoadDiscardedColumns. It returned a '" + o.GetType().Name + "'");
}
}
AddUnlessDuplicate(snip);
}
private void DeleteEntriesHavingNoChildren(ITableInfo tiCurrent, List <JoinInfo> joinPathToTimeTable, List <JoinInfo> joinsToProcess, MigrationColumnSetQueryHelper mcsQueryHelper)
{
// If there are no joins then we should delete any old updates at this level
string deleteSql;
if (!joinsToProcess.Any())
{
deleteSql = "WITH " + GetCurrentOldEntriesSQL(tiCurrent, joinPathToTimeTable) + ", EntriesToDelete AS (SELECT * FROM CurrentOldEntries)";
}
else
{
// Join on children so we can detect childless rows and delete them
var joins = new List <string>();
var wheres = new List <string>();
// create sql for child joins
foreach (var childJoin in joinsToProcess)
{
var childTable = childJoin.ForeignKey.TableInfo;
joins.Add(string.Format("LEFT JOIN {0} {1} ON CurrentOldEntries.{2} = {1}.{3}",
"[" + _dbInfo.GetRuntimeName() + "]..[" + childTable.GetRuntimeName() + "]",
childTable.GetRuntimeName(),
childJoin.PrimaryKey.GetRuntimeName(),
childJoin.ForeignKey.GetRuntimeName()
));
wheres.Add(childTable.GetRuntimeName() + "." + childJoin.ForeignKey.GetRuntimeName() + " IS NULL");
}
deleteSql = "WITH " + GetCurrentOldEntriesSQL(tiCurrent, joinPathToTimeTable) +
", EntriesToDelete AS (SELECT DISTINCT CurrentOldEntries.* FROM CurrentOldEntries " + string.Join(" ", joins) +
" WHERE " +
string.Join(" AND ", wheres) + ")";
}
deleteSql += string.Format(@"
DELETE CurrentTable
FROM {0} CurrentTable
RIGHT JOIN EntriesToDelete {1}",
"[" + _dbInfo.GetRuntimeName() + "]..[" + tiCurrent.GetRuntimeName() + "]",
mcsQueryHelper.BuildJoinClause("EntriesToDelete", "CurrentTable"));
using (var connection = (SqlConnection)_dbInfo.Server.GetConnection())
{
connection.Open();
var cmd = new SqlCommand(deleteSql, connection);
cmd.ExecuteNonQuery();
}
}
protected void CreateCSVProcessTask(LoadMetadata lmd, ITableInfo ti, string regex)
{
var pt = new ProcessTask(CatalogueRepository, lmd, LoadStage.Mounting);
pt.Path = typeof(AnySeparatorFileAttacher).FullName;
pt.ProcessTaskType = ProcessTaskType.Attacher;
pt.Name = "Load " + ti.GetRuntimeName();
pt.SaveToDatabase();
pt.CreateArgumentsForClassIfNotExists <AnySeparatorFileAttacher>();
pt.SetArgumentValue("FilePattern", regex);
pt.SetArgumentValue("Separator", ",");
pt.SetArgumentValue("TableToLoad", ti);
pt.Check(new ThrowImmediatelyCheckNotifier());
}
public void Add(ColumnInfo columnInfo, ITableInfo tableInfo, string databaseName, LoadStage stage, IQuerySyntaxHelper syntaxHelper)
{
var col = columnInfo.GetRuntimeName(stage);
var table = tableInfo.GetRuntimeName(stage);
var dbName = tableInfo.GetDatabaseRuntimeName(stage);
var snip = new SubstringAutocompleteItem(col);
snip.MenuText = col;
var fullySpecified = syntaxHelper.EnsureFullyQualified(dbName, tableInfo.Schema, table, col);
snip.Text = fullySpecified;
snip.Tag = columnInfo;
snip.ImageIndex = GetIndexFor(columnInfo, RDMPConcept.ColumnInfo.ToString());
AddUnlessDuplicate(snip);
}
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"));
}
}
private void UpdateOldParentsThatHaveNewChildren(ITableInfo tiCurrent, List <JoinInfo> joinPathToTimeTable, ReverseMigrationQueryHelper queryHelper, MigrationColumnSetQueryHelper mcsQueryHelper)
{
var update = string.Format(@"WITH
{0}
UPDATE CurrentTable
SET {1}
FROM
LiveDataForUpdating LEFT JOIN {2} AS CurrentTable {3}",
GetLiveDataToUpdateStaging(tiCurrent, joinPathToTimeTable),
queryHelper.BuildUpdateClauseForRow("LiveDataForUpdating", "CurrentTable"),
"[" + _dbInfo.GetRuntimeName() + "]..[" + tiCurrent.GetRuntimeName() + "]",
mcsQueryHelper.BuildJoinClause("LiveDataForUpdating", "CurrentTable"));
using (var connection = (SqlConnection)_dbInfo.Server.GetConnection())
{
connection.Open();
var cmd = new SqlCommand(update, connection);
cmd.ExecuteNonQuery();
}
}
/// <inheritdoc cref="ExecuteForwardEngineering()"/>
public void ExecuteForwardEngineering(ICatalogue intoExistingCatalogue,out ICatalogue catalogue, out CatalogueItem[] catalogueItems, out ExtractionInformation[] extractionInformations)
{
var repo = _tableInfo.CatalogueRepository;
//if user did not specify an existing catalogue to supplement
if (intoExistingCatalogue == null)
//create a new (empty) catalogue and treat that as the new target
intoExistingCatalogue = new Catalogue(repo, _tableInfo.GetRuntimeName());
catalogue = intoExistingCatalogue;
List<CatalogueItem> catalogueItemsCreated = new List<CatalogueItem>();
List<ExtractionInformation> extractionInformationsCreated = new List<ExtractionInformation>();
int order = 0;
//for each column we will add a new one to the
foreach (ColumnInfo col in _columnInfos)
{
order++;
//create it with the same name
CatalogueItem cataItem = new CatalogueItem(repo, intoExistingCatalogue, col.Name.Substring(col.Name.LastIndexOf(".") + 1).Trim('[', ']', '`','"'));
catalogueItemsCreated.Add(cataItem);
if (_markAllExtractable)
{
var newExtractionInfo = new ExtractionInformation(repo, cataItem, col, col.Name);
newExtractionInfo.Order = order;
newExtractionInfo.SaveToDatabase();
extractionInformationsCreated.Add(newExtractionInfo);
}
else
{
cataItem.ColumnInfo_ID = col.ID;
cataItem.SaveToDatabase();
}
}
extractionInformations = extractionInformationsCreated.ToArray();
catalogueItems = catalogueItemsCreated.ToArray();
}
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);
}
private string GenerateSQL(out ColumnInfo[] pks, out List <IResolveDuplication> resolvers)
{
string sql = "";
string tableNameInRAW = GetTableName();
var cols = _tableInfo.ColumnInfos.ToArray();
pks = cols.Where(col => col.IsPrimaryKey).ToArray();
if (!pks.Any())
{
throw new Exception("TableInfo " + _tableInfo.GetRuntimeName() + " does not have any primary keys defined so cannot resolve primary key collisions");
}
string primaryKeys = pks.Aggregate("", (s, n) => s + _querySyntaxHelper.EnsureWrapped(n.GetRuntimeName(LoadStage.AdjustRaw)) + ",");
primaryKeys = primaryKeys.TrimEnd(new[] { ',' });
sql += "/*Notice how entities are not fully indexed with Database, this is because this code will run on RAW servers, prior to reaching STAGING/LIVE - the place where there are primary keys*/" + Environment.NewLine;
sql += WithCTE + Environment.NewLine;
sql += "AS" + Environment.NewLine;
sql += "(" + Environment.NewLine;
sql += SelectRownum + " OVER(" + Environment.NewLine;
sql += "\t PARTITION BY" + Environment.NewLine;
sql += "\t\t " + primaryKeys + Environment.NewLine;
sql += "\t ORDER BY" + Environment.NewLine;
sql += "\t /*Priority in which order they should be used to resolve duplication of the primary key values, order by:*/" + Environment.NewLine;
resolvers = new List <IResolveDuplication>();
resolvers.AddRange(cols.Where(c => c.DuplicateRecordResolutionOrder != null));
resolvers.AddRange(_tableInfo.PreLoadDiscardedColumns.Where(c => c.DuplicateRecordResolutionOrder != null));
if (!resolvers.Any())
{
throw new Exception("The ColumnInfos of TableInfo " + _tableInfo + " do not have primary key resolution orders configured (do not know which order to use non primary key column values in to resolve collisions). Fix this by right clicking a TableInfo in CatalogueManager and selecting 'Configure Primary Key Collision Resolution'.");
}
//order by the priority of columns
foreach (IResolveDuplication column in resolvers.OrderBy(col => col.DuplicateRecordResolutionOrder))
{
if (column is ColumnInfo && ((ColumnInfo)column).IsPrimaryKey)
{
throw new Exception("Column " + column.GetRuntimeName() + " is flagged as primary key when it also has a DuplicateRecordResolutionOrder, primary keys cannot be used to resolve duplication since they are the hash! Resolve this in the CatalogueManager by right clicking the offending TableInfo " + _tableInfo.GetRuntimeName() + " and editing the resolution order");
}
sql = AppendRelevantOrderBySql(sql, column);
}
//trim the last remaining open bracket
sql = sql.TrimEnd(new[] { ',', '\r', '\n' }) + Environment.NewLine;
sql += ") AS DuplicateCount" + Environment.NewLine;
sql += "FROM " + tableNameInRAW + Environment.NewLine;
sql += ")" + Environment.NewLine;
sql += DeleteBit;
return(sql);
}
/// <summary>
/// Composes the SQL which joins the supplied table back up or down to the TimePeriodicity table, so we can assign the rows an effective load date
/// </summary>
/// <param name="tableAlias"></param>
/// <param name="tableInfo"></param>
/// <param name="timePeriodTableAlias"></param>
/// <param name="dbInfo"></param>
/// <param name="joinPath"></param>
/// <returns></returns>
public string CreateSqlForJoinToTimePeriodicityTable(string tableAlias, ITableInfo tableInfo, string timePeriodTableAlias, DiscoveredDatabase dbInfo, List <JoinInfo> joinPath)
{
if (tableInfo.ID == _timePeriodicityField.TableInfo_ID && joinPath.Count > 0)
{
throw new InvalidOperationException("You have asked for a join where the original table *is* the TimePeriodicityTable but a non-empty join path has been provided. There should be no path when dealing directly with the TimePeriodicity table");
}
// Simple case, there is no join so we are just selecting the row and aliasing the TimePeriodicityField for the provided table
if (!joinPath.Any())
{
return(string.Format(@"SELECT {0}.*, {0}.{1} AS TimePeriodicityField FROM {2} {0}",
tableAlias, _timePeriodicityField.GetRuntimeName(),
"[" + dbInfo.GetRuntimeName() + "]..[" + tableInfo.GetRuntimeName() + "]"));
}
// Ensure that the TimePeriodicityTable is at the end of the path (to make constructing the join a bit easier)
if (joinPath[0].ForeignKey.TableInfo_ID == _tiWithTimeColumn.ID || joinPath[0].PrimaryKey.TableInfo_ID == _tiWithTimeColumn.ID)
{
joinPath.Reverse();
}
if ((joinPath[joinPath.Count - 1].ForeignKey.TableInfo_ID != _tiWithTimeColumn.ID) && (joinPath[joinPath.Count - 1].PrimaryKey.TableInfo_ID != _tiWithTimeColumn.ID))
{
throw new InvalidOperationException("The TimePeriodicity table is not at the beginning or end of the join path.");
}
var sql = string.Format(@"SELECT {0}.*, {1}.{2} AS TimePeriodicityField
FROM {3} {4}",
tableAlias, timePeriodTableAlias, _timePeriodicityField.GetRuntimeName(),
"[" + dbInfo.GetRuntimeName() + "]..[" + tableInfo.GetRuntimeName() + "]",
tableAlias);
// Is our table a parent or child? The join is composed differently.
var ascending = tableInfo.ID == joinPath[0].ForeignKey.TableInfo_ID;
for (var i = 0; i < joinPath.Count; i++)
{
var join = joinPath[i];
if (ascending)
{
var parentTable = join.PrimaryKey.TableInfo;
var childTableAlias = (i == 0) ? tableAlias : "j" + i;
var parentTableAlias = (i == (joinPath.Count - 1)) ? timePeriodTableAlias : "j" + (i + 1);
sql += string.Format(@"
LEFT JOIN {0} {1} ON {1}.{3} = {2}.{4}",
"[" + dbInfo.GetRuntimeName() + "]..[" + parentTable.GetRuntimeName() + "]",
parentTableAlias,
childTableAlias,
join.PrimaryKey.GetRuntimeName(),
join.ForeignKey.GetRuntimeName());
}
else
{
var childTable = join.ForeignKey.TableInfo;
var parentTableAlias = (i == 0) ? tableAlias : "j" + (i + 1);
var childTableAlias = (i == (joinPath.Count - 1)) ? timePeriodTableAlias : "j" + i;
sql += string.Format(@"
LEFT JOIN {0} {1} ON {2}.{3} = {1}.{4}",
"[" + dbInfo.GetRuntimeName() + "]..[" + childTable.GetRuntimeName() + "]",
childTableAlias,
parentTableAlias,
join.PrimaryKey.GetRuntimeName(),
join.ForeignKey.GetRuntimeName());
}
}
return(sql);
}
private string GetStagingRuntimeName()
{
return("ID_" + _tableInfo.GetRuntimeName() + "_STAGING");
}
/// <summary>
///
/// </summary>
/// <exception cref="SynchronizationFailedException">Could not figure out how to resolve a synchronization problem between the TableInfo and the underlying table structure</exception>
/// <param name="notifier">Called every time a fixable problem is detected, method must return true or false. True = apply fix, False = don't - but carry on checking</param>
public bool Synchronize(ICheckNotifier notifier)
{
bool IsSynched = true;
//server exists and is accessible?
try
{
_toSyncTo.TestConnection();
}
catch (Exception e)
{
throw new SynchronizationFailedException("Could not connect to " + _toSyncTo, e);
}
//database exists?
var expectedDatabase = _toSyncTo.ExpectDatabase(_tableToSync.GetDatabaseRuntimeName());
if (!expectedDatabase.Exists())
{
throw new SynchronizationFailedException("Server did not contain a database called " + _tableToSync.GetDatabaseRuntimeName());
}
//identify new columns
DiscoveredColumn[] liveColumns;
DiscoveredTable expectedTable;
if (_tableToSync.IsTableValuedFunction)
{
expectedTable = expectedDatabase.ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException("Database " + expectedDatabase + " did not contain a TABLE VALUED FUNCTION called " + _tableToSync.GetRuntimeName());
}
}
else
{
//table exists?
expectedTable = expectedDatabase.ExpectTable(_tableToSync.GetRuntimeName(), _tableToSync.Schema, _tableToSync.IsView ? TableType.View:TableType.Table);
if (!expectedTable.Exists())
{
throw new SynchronizationFailedException(
$"Database {expectedDatabase} did not contain a {(_tableToSync.IsView ? "view" : "table") } called {_tableToSync.GetRuntimeName()} (make sure you have marked whether it is a table/view and that it exists in your database)");
}
}
try
{
liveColumns = expectedTable.DiscoverColumns();
}
catch (SqlException e)
{
throw new Exception("Failed to enumerate columns in " +
_toSyncTo +
" (we were attempting to synchronize the TableInfo " + _tableToSync + " (ID=" + _tableToSync.ID + "). Check the inner exception for specifics", e);
}
ColumnInfo[] catalogueColumns = _tableToSync.ColumnInfos.ToArray();
IDataAccessCredentials credentialsIfExists = _tableToSync.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
string pwd = null;
string usr = null;
if (credentialsIfExists != null)
{
usr = credentialsIfExists.Username;
pwd = credentialsIfExists.GetDecryptedPassword();
}
ITableInfoImporter importer;
//for importing new stuff
if (_tableToSync.IsTableValuedFunction)
{
importer = new TableValuedFunctionImporter(_repository, (DiscoveredTableValuedFunction)expectedTable);
}
else
{
importer = new TableInfoImporter(_repository, _toSyncTo.Name, _toSyncTo.GetCurrentDatabase().GetRuntimeName(), _tableToSync.GetRuntimeName(), _tableToSync.DatabaseType, username: usr, password: pwd, importFromSchema: _tableToSync.Schema, importTableType: _tableToSync.IsView ? TableType.View:TableType.Table);
}
DiscoveredColumn[] newColumnsInLive =
liveColumns.Where(
live => !catalogueColumns.Any(columnInfo =>
columnInfo.GetRuntimeName()
.Equals(live.GetRuntimeName()))).ToArray();
//there are new columns in the live database that are not in the Catalogue
if (newColumnsInLive.Any())
{
//see if user wants to add missing columns
bool addMissingColumns = notifier.OnCheckPerformed(new CheckEventArgs("The following columns are missing from the TableInfo:" + string.Join(",", newColumnsInLive.Select(c => c.GetRuntimeName())), CheckResult.Fail, null, "The ColumnInfos will be created and added to the TableInfo"));
List <ColumnInfo> added = new List <ColumnInfo>();
if (addMissingColumns)
{
foreach (DiscoveredColumn missingColumn in newColumnsInLive)
{
added.Add(importer.CreateNewColumnInfo(_tableToSync, missingColumn));
}
ForwardEngineerExtractionInformationIfAppropriate(added, notifier);
}
else
{
IsSynched = false;
}
}
//See if we need to delete any ColumnInfos
ColumnInfo[] columnsInCatalogueButSinceDisapeared =
catalogueColumns
.Where(columnInfo => !liveColumns.Any( //there are not any
c => columnInfo.GetRuntimeName().Equals(c.GetRuntimeName())) //columns with the same name between discovery/columninfo
).ToArray();
if (columnsInCatalogueButSinceDisapeared.Any())
{
foreach (var columnInfo in columnsInCatalogueButSinceDisapeared)
{
bool deleteExtraColumnInfos = notifier.OnCheckPerformed(new CheckEventArgs("The ColumnInfo " + columnInfo.GetRuntimeName() + " no longer appears in the live table.", CheckResult.Fail, null, "Delete ColumnInfo " + columnInfo.GetRuntimeName()));
if (deleteExtraColumnInfos)
{
columnInfo.DeleteInDatabase();
}
else
{
IsSynched = false;
}
}
}
_tableToSync.ClearAllInjections();
if (IsSynched)
{
IsSynched = SynchronizeTypes(notifier, liveColumns);
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have primary keys!
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsPrimaryKey");
}
if (IsSynched && !_tableToSync.IsTableValuedFunction)//table valued functions don't have autonum
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "IsAutoIncrement");
}
if (IsSynched)
{
IsSynched = SynchronizeField(liveColumns, _tableToSync.ColumnInfos, notifier, "Collation");
}
if (IsSynched && _tableToSync.IsTableValuedFunction)
{
IsSynched = SynchronizeParameters((TableValuedFunctionImporter)importer, notifier);
}
_tableToSync.ClearAllInjections();
//get list of primary keys from underlying table
return(IsSynched);
}
public override ExitCodeType Run(IDataLoadJob job, GracefulCancellationToken cancellationToken)
{
if (_pipeline != null)
{
throw new Exception("Pipeline already executed once");
}
var contextFactory = new DataFlowPipelineContextFactory <DataTable>();
var context = contextFactory.Create(PipelineUsage.LoadsSingleTableInfo | PipelineUsage.FixedDestination | PipelineUsage.LogsToTableLoadInfo);
//where we are coming from (source)
var sourceConvention = LoadBubble.Raw;
DiscoveredDatabase sourceDatabase = _databaseConfiguration.DeployInfo[sourceConvention];
var sourceTableName = _tableInfo.GetRuntimeName(sourceConvention, _databaseConfiguration.DatabaseNamer);
//What to do if where we are coming from does not have the table existing on it
if (!sourceDatabase.ExpectTable(sourceTableName).Exists())
{
if (_isLookupTable)
{
job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Warning,
"Lookup table " + sourceTableName + " did not exist on RAW so was not migrated to STAGING"));
return(ExitCodeType.Success);
}
else
{
job.OnNotify(this,
new NotifyEventArgs(ProgressEventType.Error,
"Table " + sourceTableName + " did not exist in RAW database " + sourceDatabase +
" when it came time to migrate RAW to STAGING (and the table is not a lookup)"));
}
}
// where we are going to (destination)
// ignore any columns that are marked for discard
var destinationConvention = LoadBubble.Staging;
DiscoveredDatabase destinationDatabase = _databaseConfiguration.DeployInfo[LoadBubble.Staging];
var destinationTableName = _tableInfo.GetRuntimeName(destinationConvention, _databaseConfiguration.DatabaseNamer);
DeleteFullyNullRecords(sourceTableName, sourceDatabase, job);
//audit
ITableLoadInfo tableLoadInfo = job.DataLoadInfo.CreateTableLoadInfo(
"None required, if fails then simply drop Staging database and reload dataset", "STAGING:" + destinationTableName,
new DataSource[] { new DataSource("RAW:" + sourceTableName, DateTime.Now) }, -1);
var syntax = sourceDatabase.Server.GetQuerySyntaxHelper();
//connect to source and open a reader! note that GetReaderForRAW will at this point preserve the state of the database such that any commands e.g. deletes will not have any effect even though ExecutePipeline has not been called!
var source = new DbDataCommandDataFlowSource(
"Select distinct * from " + syntax.EnsureWrapped(sourceTableName),
"Fetch data from " + syntax.EnsureWrapped(sourceTableName),
sourceDatabase.Server.Builder, 50000);
//ignore those that are pre load discarded columns (unless they are dilution in which case they get passed through in a decrepid state instead of dumped entirely - these fields will still bein ANODump in pristene state btw)
var columnNamesToIgnoreForBulkInsert = _tableInfo.PreLoadDiscardedColumns.Where(c => c.Destination != DiscardedColumnDestination.Dilute).Select(column => column.RuntimeColumnName).ToList();
//pass pre load discard
var destination = new SqlBulkInsertDestination(destinationDatabase, destinationTableName, columnNamesToIgnoreForBulkInsert);
//engine that will move data
_pipeline = new DataFlowPipelineEngine <DataTable>(context, source, destination, job);
//add clean strings component
_pipeline.ComponentObjects.Add(new CleanStrings());
//add dropping of preload discard columns
_pipeline.ComponentObjects.Add(new BasicAnonymisationEngine());
_pipeline.Initialize(tableLoadInfo, _tableInfo);
//tell it to move data
_pipeline.ExecutePipeline(cancellationToken);
return(ExitCodeType.Success);
}
public void FetchData(ICheckNotifier checkNotifier)
{
try
{
DiscoveredDatabase database;
DiscoveredServer server;
try
{
database = DataAccessPortal.GetInstance().ExpectDatabase(_tableInfo, DataAccessContext.InternalDataProcessing);
server = database.Server;
}
catch (Exception ex)
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Could not connect to data access point TableInfo " + _tableInfo, CheckResult.Fail, ex));
return;
}
if (database.Exists())
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Verified database exists " + database, CheckResult.Success));
}
var liveTable = _tableInfo.Discover(DataAccessContext.InternalDataProcessing);
var archiveTable = database.ExpectTable(_tableInfo.GetRuntimeName() + "_Archive", liveTable.Schema);
if (liveTable.Exists())
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Verified table exists " + _tableInfo, CheckResult.Success));
}
if (archiveTable.Exists())
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Verified Archive table exists " + archiveTable, CheckResult.Success));
}
else
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Did not find an Archive table called " + archiveTable, CheckResult.Fail));
}
var allCols = _tableInfo.ColumnInfos.ToArray();
var allArchiveCols = archiveTable.DiscoverColumns().ToArray();
_pks = allCols.Where(c => c.IsPrimaryKey).ToArray();
if (_pks.Any())
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Found the following primary keys:" + string.Join(",", _pks.Select(p => p.GetRuntimeName())), CheckResult.Success));
}
else
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Table does not have any ColumnInfos marked with IsPrimaryKey (try synchronizing the TableInfo if you are sure you have some", CheckResult.Fail));
}
_sharedColumns =
allCols.Where( //from all columns take all columns where
c => allArchiveCols.Any(
//there is a column with the same name in the archive columns (ignoring case)
archiveCol => c.GetRuntimeName().Equals(archiveCol.GetRuntimeName(), StringComparison.InvariantCultureIgnoreCase)
//but dont care about differences in these columns (e.g. the actual data load run id will obviously be different!)
&& !SpecialFieldNames.IsHicPrefixed(c)
)).ToArray();
checkNotifier.OnCheckPerformed(new CheckEventArgs("Shared columns between the archive and the live table are " + string.Join(",", _sharedColumns.Select(c => c.GetRuntimeName())), CheckResult.Success));
GetInsertData(server, database, checkNotifier);
GetUpdatetData(server, database, checkNotifier);
}
catch (Exception e)
{
checkNotifier.OnCheckPerformed(new CheckEventArgs("Fatal error trying to fetch data", CheckResult.Fail, e));
}
}