protected void DeleteTables(DiscoveredDatabase database)
{
var syntax = database.Server.GetQuerySyntaxHelper();
if (database.Server.DatabaseType == DatabaseType.MicrosoftSQLServer)
{
using (var con = database.Server.GetConnection())
{
con.Open();
foreach (var t in database.DiscoverTables(false))
{
//disable system versioning on any temporal tables otherwise drop fails
try
{
t.Database.Server.GetCommand(
$@"IF OBJECTPROPERTY(OBJECT_ID('{syntax.EnsureWrapped(t.GetRuntimeName())}'), 'TableTemporalType') = 2
ALTER TABLE {t.GetFullyQualifiedName()} SET (SYSTEM_VERSIONING = OFF)", con).ExecuteNonQuery();
}
catch (Exception)
{
TestContext.Out.WriteLine(
$"Failed to generate disable System Versioning check for table {t} (nevermind)");
}
}
}
}
var tables = new RelationshipTopologicalSort(database.DiscoverTables(true));
foreach (var t in tables.Order.Reverse())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
foreach (var t in database.DiscoverTableValuedFunctions())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
}
protected void CheckTablesExist(ICheckNotifier notifier)
{
try
{
if (!_remoteDatabase.Exists())
throw new Exception("Database " + _remoteDatabase + " did not exist on the remote server");
//still worthwhile doing this incase we cannot connect to the server
var tables = _remoteDatabase.DiscoverTables(true).Select(t => t.GetRuntimeName()).ToArray();
//overrides table level checks
if (!string.IsNullOrWhiteSpace(RemoteSelectSQL))
return;
//user has just picked a table to copy exactly so we can precheck for it
if (tables.Contains(RemoteTableName))
notifier.OnCheckPerformed(new CheckEventArgs(
"successfully found table " + RemoteTableName + " on server " + _remoteDatabase.Server + " on database " +
_remoteDatabase,
CheckResult.Success, null));
else
notifier.OnCheckPerformed(new CheckEventArgs(
"Could not find table called '" + RemoteTableName + "' on server " + _remoteDatabase.Server + " on database " +
_remoteDatabase +Environment.NewLine+"(The following tables were found:"+string.Join(",",tables)+")",
CheckResult.Fail, null));
}
catch (Exception e)
{
notifier.OnCheckPerformed(new CheckEventArgs("Problem occurred when trying to enumerate tables on server " + _remoteDatabase.Server + " on database " +_remoteDatabase, CheckResult.Fail, e));
}
}
protected void DeleteTables(DiscoveredDatabase database)
{
var tables = new RelationshipTopologicalSort(database.DiscoverTables(true));
foreach (var t in tables.Order.Reverse())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
foreach (var t in database.DiscoverTableValuedFunctions())
{
try
{
t.Drop();
}
catch (Exception ex)
{
throw new Exception($"Failed to drop table '{t.GetFullyQualifiedName()} during cleanup", ex);
}
}
}
private void SetTargetDatabase(DiscoveredDatabase database)
{
_table = database.DiscoverTables(false).FirstOrDefault();
if (_table == null)
{
SetImpossible("Database was empty");
}
}
/// <summary>
/// Gets an empty database on the test server of the appropriate DBMS
/// </summary>
/// <param name="type">The DBMS you want a server of (a valid connection string must exist in TestDatabases.txt)</param>
/// <param name="dbnName">null for default test database name (recommended unless you are testing moving data from one database to another on the same test server)</param>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="justDropTablesIfPossible">Determines behaviour when the test database already exists. False to drop and recreate it. True to just drop tables (faster)</param>
/// <returns></returns>
protected DiscoveredDatabase GetCleanedServer(DatabaseType type, string dbnName, out DiscoveredServer server, out DiscoveredDatabase database, bool justDropTablesIfPossible = false)
{
switch (type)
{
case DatabaseType.MicrosoftSQLServer:
server = new DiscoveredServer(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Builder);
break;
case DatabaseType.MySql:
server = _discoveredMySqlServer == null ? null : new DiscoveredServer(_discoveredMySqlServer.Builder);
break;
case DatabaseType.Oracle:
server = _discoveredOracleServer == null ? null : new DiscoveredServer(_discoveredOracleServer.Builder);
break;
default:
throw new ArgumentOutOfRangeException("type");
}
if (server == null)
{
Assert.Inconclusive();
}
//the microsoft one should exist! others are optional
if (!server.Exists() && type != DatabaseType.MicrosoftSQLServer)
{
Assert.Inconclusive();
}
server.TestConnection();
database = server.ExpectDatabase(dbnName);
if (justDropTablesIfPossible && database.Exists())
{
foreach (var t in database.DiscoverTables(true))
{
t.Drop();
}
foreach (var t in database.DiscoverTableValuedFunctions())
{
t.Drop();
}
}
else
{
database.Create(true);
}
server.ChangeDatabase(dbnName);
Assert.IsTrue(database.Exists());
return(database);
}
public ExitCodeType Mutilate(IDataLoadJob job)
{
if (ConditionsToTerminateUnder == PrematureLoadEndCondition.Always)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "ConditionsToTerminateUnder is " + ConditionsToTerminateUnder + " so terminating load with " + ExitCodeToReturnIfConditionMet));
return(ExitCodeToReturnIfConditionMet);
}
if (ConditionsToTerminateUnder == PrematureLoadEndCondition.NoRecordsInAnyTablesInDatabase)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to inspect what tables have rows in them in database " + _databaseInfo.GetRuntimeName()));
foreach (var t in _databaseInfo.DiscoverTables(false))
{
int rowCount = t.GetRowCount();
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Found table " + t.GetRuntimeName() + " with row count " + rowCount));
if (rowCount > 0)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Found at least 1 record in 1 table so condition " + ConditionsToTerminateUnder + " is not met. Therefore returning Success so the load can continue normally."));
return(ExitCodeType.Success);
}
}
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "No tables had any rows in them so returning " + ExitCodeToReturnIfConditionMet + " which should terminate the load here"));
return(ExitCodeToReturnIfConditionMet);
}
if (ConditionsToTerminateUnder == PrematureLoadEndCondition.NoFilesInForLoading)
{
var dataLoadJob = job as IDataLoadJob;
if (dataLoadJob == null)
{
throw new Exception("IDataLoadEventListener " + job + " was not an IDataLoadJob (very unexpected)");
}
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to check ForLoading directory for files, the directory is:" + dataLoadJob.LoadDirectory.ForLoading.FullName));
var files = dataLoadJob.LoadDirectory.ForLoading.GetFiles();
if (!files.Any())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "No files in ForLoading so returning " + ExitCodeToReturnIfConditionMet + " which should terminate the load here"));
return(ExitCodeToReturnIfConditionMet);
}
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Found " + files.Length + " files in ForLoading so not terminating (" + string.Join(",", files.Select(f => f.Name)) + ")"));
//There were
return(ExitCodeType.Success);
}
throw new Exception("Didn't know how to handle condition:" + ConditionsToTerminateUnder);
}
public void Migrate(IDataLoadJob job, GracefulCancellationToken cancellationToken)
{
if (_sourceDbInfo.DiscoverTables(false).All(t => t.IsEmpty()))
{
throw new Exception("The source database '" + _sourceDbInfo.GetRuntimeName() + "' on " + _sourceDbInfo.Server.Name + " is empty. There is nothing to migrate.");
}
using (var managedConnectionToDestination = _destinationDbInfo.Server.BeginNewTransactedConnection())
{
try
{
// This will eventually be provided by factory/externally based on LoadMetadata (only one strategy for now)
_migrationStrategy = new OverwriteMigrationStrategy(managedConnectionToDestination);
_migrationStrategy.TableMigrationCompleteHandler += (name, inserts, updates) =>
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Migrate table " + name + " from STAGING to " + _destinationDbInfo.GetRuntimeName() + ": " + inserts + " inserts, " + updates + " updates"));
//migrate all tables (both lookups and live tables in the same way)
var dataColsToMigrate = _migrationConfig.CreateMigrationColumnSetFromTableInfos(job.RegularTablesToLoad, job.LookupTablesToLoad,
new StagingToLiveMigrationFieldProcessor(
_databaseConfiguration.UpdateButDoNotDiff,
_databaseConfiguration.IgnoreColumns,
job.GetAllColumns().Where(c => c.IgnoreInLoads).ToArray())
{
NoBackupTrigger = job.LoadMetadata.IgnoreTrigger
});
// Migrate the data columns
_migrationStrategy.Execute(job, dataColsToMigrate, job.DataLoadInfo, cancellationToken);
managedConnectionToDestination.ManagedTransaction.CommitAndCloseConnection();
job.DataLoadInfo.CloseAndMarkComplete();
}
catch (OperationCanceledException)
{
managedConnectionToDestination.ManagedTransaction.AbandonAndCloseConnection();
}
catch (Exception ex)
{
try
{
managedConnectionToDestination.ManagedTransaction.AbandonAndCloseConnection();
}
catch (Exception)
{
throw new Exception("Failed to rollback after exception, see inner exception for details of original problem", ex);
}
throw;
}
}
}
public void CreateStaging(DiscoveredServer liveServer)
{
_stagingDatabase = liveServer.ExpectDatabase(GetDatabaseName(null, LoadBubble.Staging));
if (!_stagingDatabase.Exists())
{
_stagingDatabase.Create();
}
//get rid of any old data from previous load
foreach (var t in _stagingDatabase.DiscoverTables(false))
{
t.Truncate();
}
}
public void DiscoverState()
{
_unplannedChildren.Clear();
if (!Database.Exists())
{
State = LoadDiagramState.NotFound;
foreach (var plannedChild in _anticipatedChildren)
{
plannedChild.SetStateNotFound();
}
return;
}
//database does exist
State = LoadDiagramState.Found;
//so check the children (tables) for state
foreach (var plannedChild in _anticipatedChildren)
{
plannedChild.DiscoverState();
}
//also discover any unplanned tables if not live
if (_bubble != LoadBubble.Live)
{
foreach (DiscoveredTable discoveredTable in Database.DiscoverTables(true))
{
//it's an anticipated one
if (_anticipatedChildren.Any(c => c.TableName.Equals(discoveredTable.GetRuntimeName(), StringComparison.CurrentCultureIgnoreCase)))
{
continue;
}
//it's unplanned (maybe user created it as part of his load script or something)
_unplannedChildren.Add(new UnplannedTable(discoveredTable));
}
}
}
public void Check(ICheckNotifier notifier)
{
var columnsToDump = TableInfo.PreLoadDiscardedColumns;
var duplicates = columnsToDump.GroupBy(k => k.GetRuntimeName()).Where(c => c.Count() > 1).ToArray();
foreach (var duplicate in duplicates)
{
notifier.OnCheckPerformed(
new CheckEventArgs(
"There are " + duplicate.Count() + " PreLoadDiscardedColumns called '" + duplicate.Key + "' for TableInfo '" +
TableInfo + "'", CheckResult.Fail));
}
//columns that exist in live but are supposedly dropped during load
var liveColumns = TableInfo.ColumnInfos.ToArray();
foreach (var preLoadDiscardedColumn in columnsToDump)
{
var match = liveColumns.FirstOrDefault(c => c.GetRuntimeName().Equals(preLoadDiscardedColumn.GetRuntimeName()));
if (match != null)
{
if (preLoadDiscardedColumn.Destination != DiscardedColumnDestination.Dilute)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + TableInfo + " declares both a PreLoadDiscardedColumn '" + preLoadDiscardedColumn + "' and a ColumnInfo with the same name", CheckResult.Fail));
return;
}
if (match.IsPrimaryKey && preLoadDiscardedColumn.Destination == DiscardedColumnDestination.Dilute)
{
notifier.OnCheckPerformed(new CheckEventArgs("TableInfo " + TableInfo + " declares a PreLoadDiscardedColumn '" + preLoadDiscardedColumn + "' but there is a matching ColumnInfo of the same name which IsPrimaryKey", CheckResult.Fail));
return;
}
}
}
if (!HasAtLeastOneColumnToStoreInDump)
{
notifier.OnCheckPerformed(new CheckEventArgs("No columns require dumping from TableInfo " + _tableInfo + " so checking is not needed", CheckResult.Success, null));
return;
}
var tables = _dumpDatabase.DiscoverTables(false);
bool stagingTableFound = tables.Any(t => t.GetRuntimeName().Equals(GetStagingRuntimeName()));
ConfirmDependencies(_dumpDatabase, notifier);
//detect ongoing loads/dirty cleanup
if (stagingTableFound)
{
bool shouldDrop = notifier.OnCheckPerformed(new CheckEventArgs("STAGING table found " + GetStagingRuntimeName() + " in ANO database",
CheckResult.Fail, null, "Drop table " + GetStagingRuntimeName()));
if (shouldDrop)
{
DropStaging();
}
}
else
{
notifier.OnCheckPerformed(new CheckEventArgs("Confirmed absence of Table " + GetStagingRuntimeName() + "(this will be created during load)", CheckResult.Success, null));
}
//confirm that there is a ColumnInfo for every Dilute column
var columnInfos = _tableInfo.ColumnInfos.ToArray();
foreach (var dilutedColumn in ColumnsToRouteToSomewhereElse.Where(c => c.Destination == DiscardedColumnDestination.Dilute))
{
if (!columnInfos.Any(c => c.GetRuntimeName().Equals(dilutedColumn.RuntimeColumnName)))
{
notifier.OnCheckPerformed(new CheckEventArgs("PreLoadDiscardedColumn called " + dilutedColumn.GetRuntimeName() +
" is marked for Dilution but does not appear in the TableInfo object's ColumnInfo collection. Diluted columns must appear both in the LIVE database (in diluted state) and in IdentifierDump (in pristene state) which means that for every PreLoadDiscardedColumn which has the destination Dilution, there must be a ColumnInfo with the same name in LIVE", CheckResult.Fail, null));
}
}
//if there are any columns due to be stored in the Identifier dump
if (ColumnsToRouteToSomewhereElse.Any(c => c.GoesIntoIdentifierDump()))
{
//see if table exists
IdentifierDumperSynchronizer synchronizer = new IdentifierDumperSynchronizer(this, _externalDatabaseServer);
synchronizer.Synchronize(notifier);
//make sure there is a backup trigger enabled on the Identifier dump so that we version updates
TriggerChecks triggerChecker = new TriggerChecks(_dumpDatabase.ExpectTable(GetRuntimeName())); // primary keys - ignoring transforms for ANO
triggerChecker.Check(notifier);
}
}
protected override void RunSpecificChecks(ICheckNotifier notifier, bool isRunTime)
{
if (!_releaseData.ReleaseGlobals || _releaseData.ReleaseState == ReleaseState.DoingPatch)
{
notifier.OnCheckPerformed(new CheckEventArgs("You cannot untick globals or release a subset of datasets when releasing from a DB", CheckResult.Fail));
}
var foundConnection = String.Empty;
var tables = new List <string>();
foreach (var cumulativeResult in _releaseData.ConfigurationsForRelease.SelectMany(x => x.Key.CumulativeExtractionResults))
{
if (cumulativeResult.DestinationDescription.Split('|').Length != 3)
{
throw new Exception("The extraction did not generate a description that can be parsed. " +
"Have you extracted to a mix of CSVs and DB tables?");
}
string candidate = cumulativeResult.DestinationDescription.Split('|')[0] + "|" +
cumulativeResult.DestinationDescription.Split('|')[1];
tables.Add(cumulativeResult.DestinationDescription.Split('|')[2]);
if (String.IsNullOrEmpty(foundConnection)) // the first time we use the candidate as our connection...
{
foundConnection = candidate;
}
if (foundConnection != candidate) // ...then we check that all other candidates point to the same DB
{
throw new Exception("You are trying to extract from multiple servers or databases. This is not allowed! " +
"Please re-run the extracts against the same database.");
}
foreach (var supplementalResult in cumulativeResult.SupplementalExtractionResults
.Where(x => x.IsReferenceTo(typeof(SupportingSQLTable)) ||
x.IsReferenceTo(typeof(TableInfo))))
{
if (supplementalResult.DestinationDescription.Split('|').Length != 3)
{
throw new Exception("The extraction did not generate a description that can be parsed. " +
"Have you extracted to a mix of CSVs and DB tables?");
}
candidate = supplementalResult.DestinationDescription.Split('|')[0] + "|" +
supplementalResult.DestinationDescription.Split('|')[1];
tables.Add(supplementalResult.DestinationDescription.Split('|')[2]);
if (foundConnection != candidate) // ...then we check that all other candidates point to the same DB
{
throw new Exception("You are trying to extract from multiple servers or databases. This is not allowed! " +
"Please re-run the extracts against the same database.");
}
}
}
foreach (var globalResult in _releaseData.ConfigurationsForRelease.SelectMany(x => x.Key.SupplementalExtractionResults)
.Where(x => x.IsReferenceTo(typeof(SupportingSQLTable)) ||
x.IsReferenceTo(typeof(TableInfo))))
{
if (globalResult.DestinationDescription.Split('|').Length != 3)
{
throw new Exception("The extraction did not generate a description that can be parsed. " +
"Have you extracted the Globals to CSVs rather than DB tables?");
}
string candidate = globalResult.DestinationDescription.Split('|')[0] + "|" +
globalResult.DestinationDescription.Split('|')[1];
tables.Add(globalResult.DestinationDescription.Split('|')[2]);
if (String.IsNullOrEmpty(foundConnection)) // the first time we use the candidate as our connection...
{
foundConnection = candidate;
}
if (foundConnection != candidate) // ...then we check that all other candidates point to the same DB
{
throw new Exception("You are trying to extract from multiple servers or databases. This is not allowed! " +
"Please re-run the extracts against the same database.");
}
}
var externalServerId = int.Parse(foundConnection.Split('|')[0]);
var dbName = foundConnection.Split('|')[1];
var externalServer = _catalogueRepository.GetObjectByID <ExternalDatabaseServer>(externalServerId);
if (!String.IsNullOrWhiteSpace(externalServer.MappedDataPath))
{
_dataPathMap = new DirectoryInfo(externalServer.MappedDataPath);
}
else
{
throw new Exception("The selected Server (" + externalServer.Name + ") must have a Data Path in order to be used as an extraction destination.");
}
var server = DataAccessPortal.GetInstance().ExpectServer(externalServer, DataAccessContext.DataExport, setInitialDatabase: false);
_database = server.ExpectDatabase(dbName);
if (!_database.Exists())
{
throw new Exception("Database " + _database + " does not exist!");
}
foreach (var table in tables)
{
var foundTable = _database.ExpectTable(table);
if (!foundTable.Exists())
{
throw new Exception("Table " + table + " does not exist!");
}
}
var spuriousTables = _database.DiscoverTables(false).Where(t => !tables.Contains(t.GetRuntimeName())).ToList();
if (spuriousTables.Any() && !notifier.OnCheckPerformed(new CheckEventArgs("Spurious table(s): " + String.Join(",", spuriousTables) + " found in the DB." +
"These WILL BE released, you may want to check them before proceeding.",
CheckResult.Warning,
null,
"Are you sure you want to continue the release process?")))
{
if (!isRunTime)
{
throw new Exception("Release aborted by user.");
}
}
DirectoryInfo sourceFolder = GetSourceFolder();
var dbOutputFolder = sourceFolder.CreateSubdirectory(ExtractionDirectory.MASTER_DATA_FOLDER_NAME);
var databaseName = _database.GetRuntimeName();
if (File.Exists(Path.Combine(dbOutputFolder.FullName, databaseName + ".mdf")) ||
File.Exists(Path.Combine(dbOutputFolder.FullName, databaseName + "_log.ldf")))
{
if (notifier.OnCheckPerformed(new CheckEventArgs(String.Format("It seems that database {0} was already detached previously into {1} " +
"but not released or cleaned from the extraction folder", databaseName, dbOutputFolder.FullName),
CheckResult.Warning,
null,
"Do you want to delete it? You should check the contents first. Clicking 'No' will abort the Release.")))
{
File.Delete(Path.Combine(dbOutputFolder.FullName, databaseName + ".mdf"));
File.Delete(Path.Combine(dbOutputFolder.FullName, databaseName + "_log.ldf"));
notifier.OnCheckPerformed(new CheckEventArgs("Cleaned non-empty existing db output folder folder: " + dbOutputFolder.FullName,
CheckResult.Success));
}
else
{
if (!isRunTime)
{
throw new Exception("Release aborted by user.");
}
}
}
}
public void PreInitialize(DiscoveredDatabase value, IDataLoadEventListener listener)
{
_tableNamesAtDestination = value.DiscoverTables(true).Select(t => t.GetRuntimeName()).ToArray();
}
