public override void DropDatabase(DiscoveredDatabase database)
{
var master = database.Server.ExpectDatabase("postgres");
NpgsqlConnection.ClearAllPools();
using (var con = (NpgsqlConnection)master.Server.GetConnection())
{
con.Open();
// https://dba.stackexchange.com/a/11895
using (var cmd = new NpgsqlCommand($"UPDATE pg_database SET datallowconn = 'false' WHERE datname = '{database.GetRuntimeName()}';", con))
cmd.ExecuteNonQuery();
using (var cmd = new NpgsqlCommand($@"SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '{database.GetRuntimeName()}';"
, con))
cmd.ExecuteNonQuery();
using (var cmd = new NpgsqlCommand("DROP DATABASE \"" + database.GetRuntimeName() + "\"", con))
cmd.ExecuteNonQuery();
}
NpgsqlConnection.ClearAllPools();
}
private void AddReplacement(Dictionary <string, string> replacementStrings, string sourceDb, string sourceTable, string col, IQuerySyntaxHelper sourceSyntax, IQuerySyntaxHelper destinationSyntax)
{
replacementStrings.Add(
sourceSyntax.EnsureFullyQualified(sourceDb, null, sourceTable, col),
destinationSyntax.EnsureFullyQualified(_tempDb.GetRuntimeName(), null, sourceTable, col)
);
}
private void GetInsertData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
{
var memoryRepository = new MemoryCatalogueRepository();
var sytnaxHelper = server.GetQuerySyntaxHelper();
string tableName = _tableInfo.Name;
string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");
var whereStatement = "";
foreach (ColumnInfo pk in _pks)
{
whereStatement += string.Format("{0}.{1} = {2}.{1} AND ", tableName, pk.GetRuntimeName(), archiveTableName);
}
var qb = new QueryBuilder(null, null, new[] { _tableInfo });
qb.TopX = _batchSize;
qb.AddColumnRange(_tableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());
//where
var filter1 = new SpontaneouslyInventedFilter(memoryRepository, null, SpecialFieldNames.DataLoadRunID + " = " + _dataLoadRunID, "DataLoadRunID matches", null, null);
var filter2 =
new SpontaneouslyInventedFilter(memoryRepository, null,
string.Format(@" not exists (
select 1 from {0} where {1} {2} < {3}
)", archiveTableName, whereStatement, SpecialFieldNames.DataLoadRunID, _dataLoadRunID),
"Record doesn't exist in archive", null, null);
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { filter1, filter2 }, FilterContainerOperation.AND);
Inserts = new DataTable();
FillTableWithQueryIfUserConsents(Inserts, qb.SQL, checkNotifier, server);
}
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");
}
public override void DropDatabase(DiscoveredDatabase database)
{
bool userIsCurrentlyInDatabase = database.Server.GetCurrentDatabase().GetRuntimeName().Equals(database.GetRuntimeName());
var serverConnectionBuilder = new SqlConnectionStringBuilder(database.Server.Builder.ConnectionString);
if (userIsCurrentlyInDatabase)
{
serverConnectionBuilder.InitialCatalog = "master";
}
// Create a new server so we don't mutate database.Server and cause a whole lot of side-effects in other code, e.g. attachers
var server = new DiscoveredServer(serverConnectionBuilder);
var databaseToDrop = database.GetRuntimeName();
string sql = "ALTER DATABASE [" + databaseToDrop + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + Environment.NewLine;
sql += "DROP DATABASE [" + databaseToDrop + "]";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
SqlConnection.ClearAllPools();
}
protected override ReleaseAudit GetChunkImpl(IDataLoadEventListener listener, GracefulCancellationToken cancellationToken)
{
DirectoryInfo sourceFolder = GetSourceFolder();
Debug.Assert(sourceFolder != null, "sourceFolder != null");
var dbOutputFolder = sourceFolder.CreateSubdirectory(ExtractionDirectory.MASTER_DATA_FOLDER_NAME);
var releaseAudit = new ReleaseAudit()
{
SourceGlobalFolder = PrepareSourceGlobalFolder()
};
if (_database != null)
{
_database.Detach();
var databaseName = _database.GetRuntimeName();
File.Copy(Path.Combine(_dataPathMap.FullName, databaseName + ".mdf"), Path.Combine(dbOutputFolder.FullName, databaseName + ".mdf"));
File.Copy(Path.Combine(_dataPathMap.FullName, databaseName + "_log.ldf"), Path.Combine(dbOutputFolder.FullName, databaseName + "_log.ldf"));
File.Delete(Path.Combine(_dataPathMap.FullName, databaseName + ".mdf"));
File.Delete(Path.Combine(_dataPathMap.FullName, databaseName + "_log.ldf"));
}
return(releaseAudit);
}
private PatchingUI(DiscoveredDatabase database, ITableRepository repository, Version databaseVersion, IPatcher patcher, Patch[] patchesInDatabase, SortedDictionary <string, Patch> allPatchesInAssembly)
{
_database = database;
_repository = repository;
_databaseVersion = databaseVersion;
_patcher = patcher;
InitializeComponent();
if (LicenseManager.UsageMode == LicenseUsageMode.Designtime)
{
return;
}
_hostAssemblyVersion = new Version(FileVersionInfo.GetVersionInfo(_patcher.GetDbAssembly().Location).FileVersion);
_patchesInDatabase = patchesInDatabase;
_allPatchesInAssembly = allPatchesInAssembly;
string name = patcher.Name + " v" + patcher.GetDbAssembly().GetName().Version.ToString(3);
int numberOfPatchesToApply = _allPatchesInAssembly.Values.Except(_patchesInDatabase).Count();
tbPatch.Text = $"{name} ({numberOfPatchesToApply} Patch{(numberOfPatchesToApply > 1 ? "es":"")})";
if (_database == null)
{
tbDatabase.Text = "Form loaded without a specific database to target!";
tbDatabase.ForeColor = Color.Red;
}
else
{
tbDatabase.Text = string.Format("{0}, Version:{1}", _database.GetRuntimeName(), repository.GetVersion());
}
}
private PatchingUI(DiscoveredDatabase database, ITableRepository repository, IPatcher patcher)
{
_database = database;
_repository = repository;
_patcher = patcher;
InitializeComponent();
if (LicenseManager.UsageMode == LicenseUsageMode.Designtime)
{
return;
}
string name = patcher.Name + " v" + patcher.GetDbAssembly().GetName().Version.ToString(3);
tbPatch.Text = $"{name}";
if (_database == null)
{
tbDatabase.Text = "Form loaded without a specific database to target!";
tbDatabase.ForeColor = Color.Red;
}
else
{
tbDatabase.Text = string.Format("{0}, Version:{1}", _database.GetRuntimeName(), repository.GetVersion());
}
}
private void CreateANormalCatalogue()
{
var svr = _database.Server;
using (var con = svr.GetConnection())
{
con.Open();
svr.GetCommand("CREATE TABLE NonTVFTable ( chi varchar(10))", con).ExecuteNonQuery();
svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0101010101')", con).ExecuteNonQuery();
svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0202020202')", con).ExecuteNonQuery();
svr.GetCommand("INSERT INTO NonTVFTable VALUES ('0303030303')", con).ExecuteNonQuery();
}
var importer = new TableInfoImporter(CatalogueRepository, svr.Name,
_database.GetRuntimeName(), "NonTVFTable",
DatabaseType.MicrosoftSQLServer, _database.Server.ExplicitUsernameIfAny, _database.Server.ExplicitPasswordIfAny);
importer.DoImport(out var tbl, out var cols);
var engineer = new ForwardEngineerCatalogue(tbl, cols, true);
engineer.ExecuteForwardEngineering(out var cata, out var cis, out var eis);
_nonTvfExtractionIdentifier = eis.Single();
_nonTvfExtractionIdentifier.IsExtractionIdentifier = true;
_nonTvfExtractionIdentifier.SaveToDatabase();
_nonTvfCatalogue = cata;
_nonTvfTableInfo = tbl;
}
private void LoadFile(DiscoveredTable tableToLoad, FileInfo fileToLoad, DiscoveredDatabase dbInfo, Stopwatch timer, IDataLoadJob job)
{
using (var con = dbInfo.Server.GetConnection())
{
DataTable dt = tableToLoad.GetDataTable(0);
using (var insert = tableToLoad.BeginBulkInsert(Culture))
{
// setup bulk insert it into destination
insert.Timeout = 500000;
//bulk insert ito destination
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to open file " + fileToLoad.FullName));
OpenFile(fileToLoad, job);
//confirm the validity of the headers
ConfirmFlatFileHeadersAgainstDataTable(dt, job);
con.Open();
//now we will read data out of the file in batches
int batchNumber = 1;
int maxBatchSize = 10000;
int recordsCreatedSoFar = 0;
try
{
//while there is data to be loaded into table
while (IterativelyBatchLoadDataIntoDataTable(dt, maxBatchSize) != 0)
{
DropEmptyColumns(dt);
ConfirmFitToDestination(dt, tableToLoad, job);
try
{
recordsCreatedSoFar += insert.Upload(dt);
dt.Rows.Clear(); //very important otherwise we add more to the end of the table but still insert last batches records resulting in exponentially multiplying upload sizes of duplicate records!
job.OnProgress(this,
new ProgressEventArgs(dbInfo.GetRuntimeName(),
new ProgressMeasurement(recordsCreatedSoFar, ProgressType.Records), timer.Elapsed));
}
catch (Exception e)
{
throw new Exception("Error processing batch number " + batchNumber + " (of batch size " + maxBatchSize + ")", e);
}
}
}
catch (Exception e)
{
throw new FlatFileLoadException("Error processing file " + fileToLoad, e);
}
finally
{
CloseFile();
}
}
}
}
public override DirectoryInfo Detach(DiscoveredDatabase database)
{
const string GetDefaultSQLServerDatabaseDirectory = @"SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'master' AND type = 0";
string dataFolder;
// Create a new server so we don't mutate database.Server and cause a whole lot of side-effects in other code, e.g. attachers
var server = database.Server;
var databaseToDetach = database.GetWrappedName();
// set in simple recovery and truncate all logs!
string sql = "ALTER DATABASE " + databaseToDetach + " SET RECOVERY SIMPLE; " + Environment.NewLine +
"DBCC SHRINKFILE (" + databaseToDetach + ", 1)";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
// other operations must be done on master
server.ChangeDatabase("master");
// set single user before detaching
sql = "ALTER DATABASE " + databaseToDetach + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
var dbLiteralName = database.Server.GetQuerySyntaxHelper().Escape(database.GetRuntimeName());
// detach!
sql = @"EXEC sys.sp_detach_db '" + dbLiteralName + "';";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
// get data-files path from SQL Server
using (var connection = (SqlConnection)server.GetConnection())
{
connection.Open();
using (var cmd = new SqlCommand(GetDefaultSQLServerDatabaseDirectory, connection))
dataFolder = (string)cmd.ExecuteScalar();
}
return(dataFolder == null ? null : new DirectoryInfo(dataFolder));
}
public override void DropDatabase(DiscoveredDatabase database)
{
using (var con = (MySqlConnection)database.Server.GetConnection())
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand("DROP DATABASE `" + database.GetRuntimeName() + "`", con))
cmd.ExecuteNonQuery();
}
}
public override void DropDatabase(DiscoveredDatabase database)
{
using (var con = (OracleConnection)database.Server.GetConnection())
{
con.Open();
using (var cmd = new OracleCommand("DROP USER \"" + database.GetRuntimeName() + "\" CASCADE ", con))
cmd.ExecuteNonQuery();
}
}
private string GetBadTableName(DiscoveredDatabase db)
{
switch (db.Server.DatabaseType)
{
case DatabaseType.MicrosoftSQLServer:
return("[BB (ff)]");
case DatabaseType.MySql:
return("`BB (ff)`");
case DatabaseType.Oracle:
return(db.GetRuntimeName() + ".\"BB (ff)\"");
case DatabaseType.PostgreSql:
return('"' + db.GetRuntimeName() + "\".public.\"BB (ff)\"");
default:
throw new ArgumentOutOfRangeException(nameof(db.Server.DatabaseType), db.Server.DatabaseType, null);
}
}
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();
}
}
public LoadDiagramDatabaseNode(LoadBubble bubble, DiscoveredDatabase database, TableInfo[] loadTables, HICDatabaseConfiguration config)
{
_bubble = bubble;
Database = database;
_loadTables = loadTables;
_config = config;
DatabaseName = Database.GetRuntimeName();
_anticipatedChildren.AddRange(_loadTables.Select(t => new LoadDiagramTableNode(this, t, _bubble, _config)));
}
//Constructor
internal StandardDatabaseHelper(DiscoveredDatabase liveDatabase, INameDatabasesAndTablesDuringLoads namer, DiscoveredServer rawServer)
{
DatabaseNamer = namer;
foreach (LoadBubble stage in new[] { LoadBubble.Raw, LoadBubble.Staging, LoadBubble.Live, })
{
var stageName = DatabaseNamer.GetDatabaseName(liveDatabase.GetRuntimeName(), stage);
DatabaseInfoList.Add(stage, stage == LoadBubble.Raw ? rawServer.ExpectDatabase(stageName) : liveDatabase.Server.ExpectDatabase(stageName));
}
}
/// <summary>
/// Sets server,database,username and password properties based on the supplied DiscoveredDatabase (which doesn't have to actually exist). This method also optionally calls
/// SaveToDatabase which commits the changes to the Catalogue Repository
/// </summary>
/// <param name="discoveredDatabase"></param>
/// <param name="save">true if you want to call SaveToDatabase after setting the properties</param>
public void SetProperties(DiscoveredDatabase discoveredDatabase, bool save = true)
{
Server = discoveredDatabase.Server.Name;
Database = discoveredDatabase.GetRuntimeName();
Username = discoveredDatabase.Server.ExplicitUsernameIfAny;
Password = discoveredDatabase.Server.ExplicitPasswordIfAny;
DatabaseType = discoveredDatabase.Server.DatabaseType;
if (save)
{
SaveToDatabase();
}
}
/// <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="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 = null, bool justDropTablesIfPossible = false)
{
if (dbnName == null)
{
dbnName = DiscoveredDatabaseICanCreateRandomTablesIn.GetRuntimeName();
}
DiscoveredServer wc1;
DiscoveredDatabase wc2;
var toReturn = GetCleanedServer(type, dbnName, out wc1, out wc2, justDropTablesIfPossible);
forCleanup.Add(toReturn);
return(toReturn);
}
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;
}
}
}
/// <summary>
/// Sets up the databases <see cref="From"/> and <see cref="To"/> on the test database server of the given
/// <paramref name="dbType"/>. This method is automatically called with <see cref="DatabaseType.MicrosoftSQLServer"/>
/// in <see cref="OneTimeSetUp()"/> (nunit automatically fires it).
/// </summary>
/// <param name="dbType"></param>
protected void SetupFromTo(DatabaseType dbType)
{
To = GetCleanedServer(dbType);
From = To.Server.ExpectDatabase(To.GetRuntimeName() + Suffix);
// ensure the test staging and live databases are empty
if (!From.Exists())
{
From.Create();
}
else
{
DeleteTables(From);
}
}
public override void CreateBackup(DiscoveredDatabase discoveredDatabase, string backupName)
{
var server = discoveredDatabase.Server;
using (var con = server.GetConnection())
{
con.Open();
string sql = string.Format(
"BACKUP DATABASE {0} TO DISK = '{0}.bak' WITH INIT , NOUNLOAD , NAME = N'{1}', NOSKIP , STATS = 10, NOFORMAT",
discoveredDatabase.GetRuntimeName(), backupName);
using (var cmd = server.GetCommand(sql, con))
cmd.ExecuteNonQuery();
}
}
public void Create(CatalogueRepository repository, DiscoveredDatabase database,
ILoadDirectory directory)
{
TableInfo = new TableInfo(repository, "TestData")
{
Server = database.Server.Name,
Database = database.GetRuntimeName()
};
TableInfo.SaveToDatabase();
if (!string.IsNullOrWhiteSpace(database.Server.ExplicitUsernameIfAny))
{
Credentials = new DataAccessCredentialsFactory(repository).Create(TableInfo,
database.Server.ExplicitUsernameIfAny, database.Server.ExplicitPasswordIfAny,
DataAccessContext.Any);
}
ColumnInfo = new ColumnInfo(repository, "Col1", "int", TableInfo)
{
IsPrimaryKey = true
};
ColumnInfo.SaveToDatabase();
LoadMetadata = new LoadMetadata(repository, "HICLoadPipelineTests")
{
LocationOfFlatFiles = directory.RootPath.FullName
};
LoadMetadata.SaveToDatabase();
Catalogue = new Catalogue(repository, "HICLoadPipelineTests")
{
LoggingDataTask = "Test",
LoadMetadata_ID = LoadMetadata.ID
};
Catalogue.SaveToDatabase();
var catalogueItem = new CatalogueItem(repository, Catalogue, "Test");
catalogueItem.SetColumnInfo(ColumnInfo);
SetupLoadProcessTasks(repository);
}
private bool IsNukable(DiscoveredDatabase dbInfo)
{
return(dbInfo.GetRuntimeName().EndsWith("_STAGING", StringComparison.CurrentCultureIgnoreCase) || dbInfo.GetRuntimeName().EndsWith("_RAW", StringComparison.CurrentCultureIgnoreCase));
}
private void GenerateSQLPreview()
{
if (VisualStudioDesignMode)
{
return;
}
QueryPreview.ReadOnly = false;
try
{
string toShow = "";
DiscoveredDatabase location = _extractableCohort.GetDatabaseServer();
//tell user about connection string (currently we don't support usernames/passwords so it's fine
toShow += "/*Cohort is stored in Server " + location.Server.Name + " Database " + location.GetRuntimeName() + "*/" + Environment.NewLine;
toShow += Environment.NewLine;
IExternalCohortTable externalCohortTable = _extractableCohort.ExternalCohortTable;
string sql = "SELECT * FROM " + externalCohortTable.TableName +
Environment.NewLine
+ " WHERE " + _extractableCohort.WhereSQL();
toShow += Environment.NewLine;
toShow += Environment.NewLine + "/*SQL to view cohort:*/" + Environment.NewLine;
toShow += sql;
QueryPreview.Text = toShow;
}
catch (Exception ex)
{
QueryPreview.Text = ExceptionHelper.ExceptionToListOfInnerMessages(ex, true);
}
finally
{
QueryPreview.ReadOnly = true;
}
}
public void TestGettingAggregateJustFromConfig_DistinctCHISelect()
{
CachedAggregateConfigurationResultsManager manager = new CachedAggregateConfigurationResultsManager(externalDatabaseServer);
cohortIdentificationConfiguration.QueryCachingServer_ID = externalDatabaseServer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.CreateRootContainerIfNotExists();
cohortIdentificationConfiguration.RootCohortAggregateContainer.AddChild(aggregate1, 0);
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
var server = queryCacheDatabase.Server;
using (var con = server.GetConnection())
{
con.Open();
var da = server.GetDataAdapter(builder.SQL, con);
var dt = new DataTable();
da.Fill(dt);
manager.CommitResults(new CacheCommitIdentifierList(aggregate1,
string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]", cohortIdentificationConfiguration.ID), dt, _chiColumnSpecification, 30));
}
CohortQueryBuilder builderCached = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*Cached:cic_{0}_UnitTestAggregate1*/
select * from [" + queryCacheDatabase.GetRuntimeName() + "]..[IndexedExtractionIdentifierList_AggregateConfiguration" + aggregate1.ID + @"]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builderCached.SQL));
}
finally
{
cohortIdentificationConfiguration.RootCohortAggregateContainer.RemoveChild(aggregate1);
}
}
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));
}
}
/// <summary>
/// Returns the fully qualified RAW name of the column factoring in namer e.g. [ab213_ImagingRAW]..[StudyTable].[MyCol]
/// </summary>
/// <param name="col"></param>
/// <returns></returns>
private string GetRAWColumnNameFullyQualified(ColumnInfo col)
{
return(_syntaxHelper.EnsureFullyQualified(_raw.GetRuntimeName(), null, col.TableInfo.GetRuntimeName(LoadBubble.Raw, _namer), col.GetRuntimeName(LoadStage.AdjustRaw)));
}
internal void Create(DiscoveredDatabase db, ICheckNotifier notifier, PlatformDatabaseCreationOptions options)
{
if (db.Exists())
{
if (options.DropDatabases)
{
db.Drop();
}
else
{
throw new Exception("Database " + db.GetRuntimeName() + " already exists and allowDrop option was not specified");
}
}
notifier.OnCheckPerformed(new CheckEventArgs("About to create " + db.GetRuntimeName(), CheckResult.Success));
//create a new database for the datasets
db.Create();
notifier.OnCheckPerformed(new CheckEventArgs("Succesfully created " + db.GetRuntimeName(), CheckResult.Success));
//fixed seed so everyone gets the same datasets
var r = new Random(options.Seed);
notifier.OnCheckPerformed(new CheckEventArgs("Generating people", CheckResult.Success));
//people
var people = new PersonCollection();
people.GeneratePeople(options.NumberOfPeople, r);
//datasets
var biochem = ImportCatalogue(Create <Biochemistry>(db, people, r, notifier, options.NumberOfRowsPerDataset, "chi", "Healthboard", "SampleDate", "TestCode"));
var demography = ImportCatalogue(Create <Demography>(db, people, r, notifier, options.NumberOfRowsPerDataset, "chi", "dtCreated", "hb_extract"));
var prescribing = ImportCatalogue(Create <Prescribing>(db, people, r, notifier, options.NumberOfRowsPerDataset, "chi", "PrescribedDate", "Name")); //<- this is slooo!
var admissions = ImportCatalogue(Create <HospitalAdmissions>(db, people, r, notifier, options.NumberOfRowsPerDataset, "chi", "AdmissionDate"));
var carotid = Create <CarotidArteryScan>(db, people, r, notifier, options.NumberOfRowsPerDataset, "RECORD_NUMBER");
//the following should not be extractable
ForExtractionInformations(demography,
e => e.DeleteInDatabase(),
"chi_num_of_curr_record",
"surname",
"forename",
"current_address_L1",
"current_address_L2",
"current_address_L3",
"current_address_L4",
"birth_surname",
"previous_surname",
"midname",
"alt_forename",
"other_initials",
"previous_address_L1",
"previous_address_L2",
"previous_address_L3",
"previous_address_L4",
"previous_postcode",
"date_address_changed",
"adr",
"previous_gp_accept_date",
"hic_dataLoadRunID");
//the following should be special approval only
ForExtractionInformations(demography,
e => {
e.ExtractionCategory = ExtractionCategory.SpecialApprovalRequired;
e.SaveToDatabase();
},
"current_postcode",
"current_gp",
"previous_gp",
"date_of_birth");
CreateAdmissionsViews(db);
var vConditions = ImportCatalogue(db.ExpectTable("vConditions"));
var vOperations = ImportCatalogue(db.ExpectTable("vOperations"));
CreateGraph(biochem, "Test Codes", "TestCode", false, null);
CreateGraph(biochem, "Test Codes By Date", "SampleDate", true, "TestCode");
CreateFilter(biochem, "Creatinine", "TestCode", "TestCode like '%CRE%'", @"Serum creatinine is a blood measurement. It is an indicator of renal health.");
CreateFilter(biochem, "Test Code", "TestCode", "TestCode like @code", "Filters any test code set");
CreateExtractionInformation(demography, "Age", "date_of_birth", "FLOOR(DATEDIFF(DAY, date_of_birth, GETDATE()) / 365.25) As Age");
var fAge = CreateFilter(demography, "Older at least x years", "Age", "FLOOR(DATEDIFF(DAY, date_of_birth, GETDATE()) / 365.25) >= @age", "Patients age is greater than or equal to the provided @age");
SetParameter(fAge, "@age", "int", "16");
CreateGraph(demography, "Patient Ages", "Age", false, null);
CreateGraph(prescribing, "Approved Name", "ApprovedName", false, null);
CreateGraph(prescribing, "Approved Name Over Time", "PrescribedDate", true, "ApprovedName");
CreateGraph(prescribing, "Bnf", "FormattedBnfCode", false, null);
CreateGraph(prescribing, "Bnf Over Time", "PrescribedDate", true, "FormattedBnfCode");
CreateFilter(
CreateGraph(vConditions, "Conditions frequency", "Field", false, "Condition"),
"Common Conditions Only",
@"(Condition in
(select top 40 Condition from vConditions c
WHERE Condition <> 'NULL' AND Condition <> 'Nul'
group by Condition order by count(*) desc))");
CreateFilter(
CreateGraph(vOperations, "Operation frequency", "Field", false, "Operation"),
"Common Operation Only",
@"(Operation in
(select top 40 Operation from vOperations c
WHERE Operation <> 'NULL' AND Operation <> 'Nul'
group by Operation order by count(*) desc))");
//group these all into the same folder
admissions.Folder = new CatalogueFolder(admissions, @"\admissions");
admissions.SaveToDatabase();
vConditions.Folder = new CatalogueFolder(vConditions, @"\admissions");
vConditions.SaveToDatabase();
vOperations.Folder = new CatalogueFolder(vOperations, @"\admissions");
vOperations.SaveToDatabase();
//Create cohort store database
var wizard = new CreateNewCohortDatabaseWizard(db, _repos.CatalogueRepository, _repos.DataExportRepository, false);
var externalCohortTable = wizard.CreateDatabase(new PrivateIdentifierPrototype("chi", "varchar(10)"), new ThrowImmediatelyCheckNotifier());
//Find the pipeline for committing cohorts
var cohortCreationPipeline = _repos.CatalogueRepository.GetAllObjects <Pipeline>().FirstOrDefault(p => p?.Source?.Class == typeof(CohortIdentificationConfigurationSource).FullName);
if (cohortCreationPipeline == null)
{
throw new Exception("Could not find a cohort committing pipeline");
}
//A cohort creation query
var f = CreateFilter(vConditions, "Lung Cancer Condition", "Condition", "Condition like 'C349'", "ICD-10-CM Diagnosis Code C34.9 Malignant neoplasm of unspecified part of bronchus or lung");
var cic = CreateCohortIdentificationConfiguration((ExtractionFilter)f);
var cohort = CommitCohortToNewProject(cic, externalCohortTable, cohortCreationPipeline, "Lung Cancer Project", "P1 Lung Cancer Patients", 123, out Project project);
var cohortTable = cohort.ExternalCohortTable.DiscoverCohortTable();
using (var con = cohortTable.Database.Server.GetConnection())
{
con.Open();
//delete half the records (so we can simulate cohort refresh)
var cmd = cohortTable.Database.Server.GetCommand(string.Format("DELETE TOP (10) PERCENT from {0}", cohortTable.GetFullyQualifiedName()), con);
cmd.ExecuteNonQuery();
}
var ec1 = CreateExtractionConfiguration(project, cohort, "First Extraction (2016 - project 123)", true, notifier, biochem, prescribing, demography);
var ec2 = CreateExtractionConfiguration(project, cohort, "Project 123 - 2017 Refresh", true, notifier, biochem, prescribing, demography, admissions);
var ec3 = CreateExtractionConfiguration(project, cohort, "Project 123 - 2018 Refresh", true, notifier, biochem, prescribing, demography, admissions);
ReleaseAllConfigurations(notifier, ec1, ec2, ec3);
}
public ExternalCohortTable CreateDatabase(PrivateIdentifierPrototype privateIdentifierPrototype, ICheckNotifier notifier)
{
if (!_targetDatabase.Exists())
{
notifier.OnCheckPerformed(new CheckEventArgs("Did not find database " + _targetDatabase + " on server so creating it", CheckResult.Success));
_targetDatabase.Create();
}
try
{
var definitionTable = _targetDatabase.CreateTable("CohortDefinition", new[]
{
new DatabaseColumnRequest("id", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false, IsAutoIncrement = true, IsPrimaryKey = true
},
new DatabaseColumnRequest("projectNumber", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("version", new DatabaseTypeRequest(typeof(int)))
{
AllowNulls = false
},
new DatabaseColumnRequest("description", new DatabaseTypeRequest(typeof(string), 3000))
{
AllowNulls = false
},
new DatabaseColumnRequest("dtCreated", new DatabaseTypeRequest(typeof(DateTime)))
{
AllowNulls = false, Default = MandatoryScalarFunctions.GetTodaysDate
}
});
var idColumn = definitionTable.DiscoverColumn("id");
var foreignKey = new DatabaseColumnRequest(_definitionTableForeignKeyField, new DatabaseTypeRequest(typeof(int)), false)
{
IsPrimaryKey = true
};
var cohortTable = _targetDatabase.CreateTable("Cohort", new []
{
new DatabaseColumnRequest(privateIdentifierPrototype.RuntimeName, privateIdentifierPrototype.DataType, false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest(_releaseIdentifierFieldName, new DatabaseTypeRequest(typeof(string), 300))
{
AllowNulls = AllowNullReleaseIdentifiers
},
foreignKey
}
,
//foreign key between id and cohortDefinition_id
new Dictionary <DatabaseColumnRequest, DiscoveredColumn>()
{
{ foreignKey, idColumn }
}, true);
notifier.OnCheckPerformed(new CheckEventArgs("About to create pointer to the source", CheckResult.Success));
var pointer = new ExternalCohortTable(_dataExportRepository, "TestExternalCohort", _targetDatabase.Server.DatabaseType)
{
DatabaseType = _targetDatabase.Server.DatabaseType,
Server = _targetDatabase.Server.Name,
Database = _targetDatabase.GetRuntimeName(),
Username = _targetDatabase.Server.ExplicitUsernameIfAny,
Password = _targetDatabase.Server.ExplicitPasswordIfAny,
Name = _targetDatabase.GetRuntimeName(),
TableName = cohortTable.GetRuntimeName(),
PrivateIdentifierField = privateIdentifierPrototype.RuntimeName,
ReleaseIdentifierField = _releaseIdentifierFieldName,
DefinitionTableForeignKeyField = _definitionTableForeignKeyField,
DefinitionTableName = definitionTable.GetRuntimeName()
};
pointer.SaveToDatabase();
notifier.OnCheckPerformed(new CheckEventArgs("successfully created reference to cohort source in data export manager", CheckResult.Success));
notifier.OnCheckPerformed(new CheckEventArgs("About to run post creation checks", CheckResult.Success));
pointer.Check(notifier);
notifier.OnCheckPerformed(new CheckEventArgs("Finished", CheckResult.Success));
return(pointer);
}
catch (Exception e)
{
notifier.OnCheckPerformed(
new CheckEventArgs("Entire setup failed with exception (double click to find out why)",
CheckResult.Fail, e));
return(null);
}
}
