public void ServerDatabaseIsPresentAndCorrect(bool alreadyExists)
{
var server = new ExternalDatabaseServer(CatalogueRepository, "Fiction", null);
server.Server = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Name;
//server.Database = "FictionalDatabase"; Ignored by the extractor!
try
{
var destination = new ExecuteFullExtractionToDatabaseMSSql();
destination.PreInitialize(_projectStub, new ThrowImmediatelyDataLoadEventListener());
destination.PreInitialize(_commandStub, new ThrowImmediatelyDataLoadEventListener());
destination.TargetDatabaseServer = server;
destination.TableNamingPattern = "$d";
if (alreadyExists)
{
destination.DatabaseNamingPattern = Database.GetRuntimeName(); //database that exists
}
else
{
destination.DatabaseNamingPattern = "Fictional$nDatabase"; //database does not exist (but server does)
}
var tomemory = new ToMemoryCheckNotifier(new ThrowImmediatelyCheckNotifier());
destination.Check(tomemory);
Assert.AreEqual(alreadyExists? CheckResult.Warning: CheckResult.Success, tomemory.GetWorst());
}
finally
{
server.DeleteInDatabase();
}
}
public void TestArgumentWithTypeThatIsInterface(bool useInterfaceDeclaration)
{
var pipe = new Pipeline(CatalogueRepository, "p");
var pc = new PipelineComponent(CatalogueRepository, pipe, typeof(BasicDataReleaseDestination), -1,
"c");
var arg = new PipelineComponentArgument(CatalogueRepository, pc);
var server = new ExternalDatabaseServer(CatalogueRepository, "fish", null);
try
{
arg.SetType(useInterfaceDeclaration ? typeof(IExternalDatabaseServer) : typeof(ExternalDatabaseServer));
arg.SetValue(server);
//should have set Value string to the ID of the object
Assert.AreEqual(arg.Value, server.ID.ToString());
arg.SaveToDatabase();
//but as system Type should return the server
Assert.AreEqual(arg.GetValueAsSystemType(), server);
}
finally
{
pipe.DeleteInDatabase();
server.DeleteInDatabase();
}
}
public void ServerDatabaseIsPresentAndCorrectButHasTablesInIt()
{
var server = new ExternalDatabaseServer(CatalogueRepository, "Fiction", null);
server.Server = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Name;
//server.Database = "FictionalDatabase"; Ignored by the extractor!
using (var con = Database.Server.GetConnection())
{
con.Open();
Database.Server.GetCommand("CREATE TABLE Bob(name varchar(10))", con).ExecuteNonQuery();
}
try
{
var destination = new ExecuteFullExtractionToDatabaseMSSql();
destination.PreInitialize(_projectStub, new ThrowImmediatelyDataLoadEventListener());
destination.PreInitialize(_commandStub, new ThrowImmediatelyDataLoadEventListener());
destination.TargetDatabaseServer = server;
destination.TableNamingPattern = "$d";
destination.DatabaseNamingPattern = "FictionalDatabase";
var tomemory = new ToMemoryCheckNotifier(new ThrowImmediatelyCheckNotifier());
destination.Check(tomemory);
Assert.AreEqual(CheckResult.Warning, tomemory.GetWorst());
Database.ExpectTable("Bob").Drop();
}
finally
{
server.DeleteInDatabase();
}
}
public void CreateNewExternalServerAndConfigureItAsDefault()
{
ServerDefaults defaults = new ServerDefaults(CatalogueRepository);
var databaseServer = new ExternalDatabaseServer(CatalogueRepository, "Deleteme", null);
try
{
Assert.AreEqual("Deleteme", databaseServer.Name);
databaseServer.Password = "******"; //automatically encrypts password
Assert.AreNotEqual("nothing", databaseServer.Password); //should not match what we just set it to
Assert.AreEqual("nothing", databaseServer.GetDecryptedPassword()); //should match what we set it to because of explicit call to decrypt
databaseServer.Server = "Bob";
databaseServer.Database = "TEST";
databaseServer.SaveToDatabase();
Catalogue cata = new Catalogue(CatalogueRepository, "TestCatalogueFor_CreateNewExternalServerAndConfigureItAsDefault");
cata.DeleteInDatabase();
}
finally
{
databaseServer.DeleteInDatabase();
}
}
public void Destroy()
{
QueryCachingDatabaseServer.DeleteInDatabase();
if (DiscoveredQueryCachingDatabase.Exists())
{
DiscoveredQueryCachingDatabase.Drop();
}
}
public void TearDown()
{
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.ExpectTable("AliasHandlerTests");
if (tbl.Exists())
{
tbl.Drop();
}
_server.DeleteInDatabase();
}
public void ServerMissingServerName()
{
var server = new ExternalDatabaseServer(CatalogueRepository, "Fiction", null);
try
{
var destination = new ExecuteFullExtractionToDatabaseMSSql();
destination.TargetDatabaseServer = server;
var tomemory = new ToMemoryCheckNotifier();
destination.Check(tomemory);
Assert.AreEqual(CheckResult.Fail, tomemory.Messages[0].Result);
Assert.IsTrue(tomemory.Messages[0].Message.StartsWith("TargetDatabaseServer does not have a .Server specified"));
}
finally
{
server.DeleteInDatabase();
}
}
public void JoinablesWithCache()
{
string queryCachingDatabaseName = To.GetRuntimeName();
_queryCachingDatabase = To;
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
//make aggregate 2 have an additional column (dtCreated)
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase);
scripter.CreateAndPatchDatabase(new QueryCachingPatcher(), new AcceptAllCheckNotifier());
var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName, null);
queryCachingDatabaseServer.SetProperties(_queryCachingDatabase);
//make the builder use the query cache we just set SetUp
builder.CacheServer = queryCachingDatabaseServer;
try
{
var builderForCaching = new CohortQueryBuilder(aggregate2, null, true);
var cacheDt = new DataTable();
using (SqlConnection con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con));
da.Fill(cacheDt);
}
var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer);
cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null, 30));
try
{
Console.WriteLine(builder.SQL);
using (var con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
Assert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{2}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
LEFT Join (
/*Cached:cic_{2}_UnitTestAggregate2*/
select * from [{3}]..[JoinableInceptionQuery_AggregateConfiguration{1}]
){0}
on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi",
expectedTableAlias, //{0}
aggregate2.ID, //{1}
cohortIdentificationConfiguration.ID, //{2}
queryCachingDatabaseName) //{3}
), CollapseWhitespace(builder.SQL));
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
finally
{
queryCachingDatabaseServer.DeleteInDatabase();
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop();
}
}
public void RefreshCohort_WithCaching()
{
ExtractionPipelineUseCase useCase;
IExecuteDatasetExtractionDestination results;
var pipe = new Pipeline(CatalogueRepository, "RefreshPipeWithCaching");
var source = new PipelineComponent(CatalogueRepository, pipe, typeof(CohortIdentificationConfigurationSource), 0);
var args = source.CreateArgumentsForClassIfNotExists <CohortIdentificationConfigurationSource>();
var freezeArg = args.Single(a => a.Name.Equals("FreezeAfterSuccessfulImport"));
freezeArg.SetValue(false);
freezeArg.SaveToDatabase();
var dest = new PipelineComponent(CatalogueRepository, pipe, typeof(BasicCohortDestination), 0);
var argsDest = dest.CreateArgumentsForClassIfNotExists <BasicCohortDestination>();
var allocatorArg = argsDest.Single(a => a.Name.Equals("ReleaseIdentifierAllocator"));
allocatorArg.SetValue(null);
allocatorArg.SaveToDatabase();
pipe.SourcePipelineComponent_ID = source.ID;
pipe.DestinationPipelineComponent_ID = dest.ID;
pipe.SaveToDatabase();
Execute(out useCase, out results);
var oldcohort = _configuration.Cohort;
//Create a query cache
var p = new QueryCachingPatcher();
ExternalDatabaseServer queryCacheServer = new ExternalDatabaseServer(CatalogueRepository, "TestCohortRefreshing_CacheTest", p);
DiscoveredDatabase cachedb = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase("TestCohortRefreshing_CacheTest");
if (cachedb.Exists())
{
cachedb.Drop();
}
new MasterDatabaseScriptExecutor(cachedb).CreateAndPatchDatabase(p, new ThrowImmediatelyCheckNotifier());
queryCacheServer.SetProperties(cachedb);
//Create a Cohort Identification configuration (query) that will identify the cohort
CohortIdentificationConfiguration cic = new CohortIdentificationConfiguration(RepositoryLocator.CatalogueRepository, "RefreshCohort.cs");;
try
{
//make it use the cache
cic.QueryCachingServer_ID = queryCacheServer.ID;
cic.SaveToDatabase();
//give it a single table query to fetch distinct chi from test data
var agg = cic.CreateNewEmptyConfigurationForCatalogue(_catalogue, null);
//add the sub query as the only entry in the cic (in the root container)
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 1);
//make the ExtractionConfiguration refresh cohort query be the cic
_configuration.CohortIdentificationConfiguration_ID = cic.ID;
_configuration.CohortRefreshPipeline_ID = pipe.ID;
_configuration.SaveToDatabase();
//get a refreshing engine
var engine = new CohortRefreshEngine(new ThrowImmediatelyDataLoadEventListener(), _configuration);
engine.Execute();
Assert.NotNull(engine.Request.NewCohortDefinition);
var oldData = oldcohort.GetExternalData();
Assert.AreEqual(oldData.ExternalDescription, engine.Request.NewCohortDefinition.Description);
Assert.AreEqual(oldData.ExternalVersion + 1, engine.Request.NewCohortDefinition.Version);
Assert.AreNotEqual(oldcohort.CountDistinct, engine.Request.CohortCreatedIfAny.CountDistinct);
//now nuke all data in the catalogue so the cic returns nobody (except that the identifiers are cached eh?)
DataAccessPortal.GetInstance().ExpectDatabase(_tableInfo, DataAccessContext.InternalDataProcessing).ExpectTable(_tableInfo.GetRuntimeName()).Truncate();
var toMem = new ToMemoryDataLoadEventListener(false);
//get a new engine
engine = new CohortRefreshEngine(toMem, _configuration);
//execute it
var ex = Assert.Throws <Exception>(() => engine.Execute());
Assert.IsTrue(ex.InnerException.InnerException.Message.Contains("CohortIdentificationCriteria execution resulted in an empty dataset"));
//expected this message to happen
//that it did clear the cache
Assert.AreEqual(1, toMem.EventsReceivedBySender.SelectMany(kvp => kvp.Value).Count(msg => msg.Message.Equals("Clearing Cohort Identifier Cache")));
}
finally
{
//make the ExtractionConfiguration not use the cic query
_configuration.CohortRefreshPipeline_ID = null;
_configuration.CohortIdentificationConfiguration_ID = null;
_configuration.SaveToDatabase();
//delete the cic query
cic.QueryCachingServer_ID = null;
cic.SaveToDatabase();
cic.DeleteInDatabase();
//delete the caching database
queryCacheServer.DeleteInDatabase();
cachedb.Drop();
}
}
public void SQLServerDestination()
{
DiscoveredDatabase dbToExtractTo = null;
var ci = new CatalogueItem(CatalogueRepository, _catalogue, "YearOfBirth");
var columnToTransform = _columnInfos.Single(c => c.GetRuntimeName().Equals("DateOfBirth", StringComparison.CurrentCultureIgnoreCase));
string transform = "YEAR(" + columnToTransform.Name + ")";
var ei = new ExtractionInformation(CatalogueRepository, ci, columnToTransform, transform);
ei.Alias = "YearOfBirth";
ei.ExtractionCategory = ExtractionCategory.Core;
ei.SaveToDatabase();
//make it part of the ExtractionConfiguration
var newColumn = new ExtractableColumn(DataExportRepository, _selectedDataSet.ExtractableDataSet, (ExtractionConfiguration)_selectedDataSet.ExtractionConfiguration, ei, 0, ei.SelectSQL);
newColumn.Alias = ei.Alias;
newColumn.SaveToDatabase();
_extractableColumns.Add(newColumn);
//recreate the extraction command so it gets updated with the new column too.
_request = new ExtractDatasetCommand(_configuration, _extractableCohort, new ExtractableDatasetBundle(_extractableDataSet),
_extractableColumns, new HICProjectSalt(_project),
new ExtractionDirectory(@"C:\temp\", _configuration));
try
{
_configuration.Name = "ExecuteFullExtractionToDatabaseMSSqlDestinationTest";
_configuration.SaveToDatabase();
ExtractionPipelineUseCase execute;
IExecuteDatasetExtractionDestination result;
var dbname = TestDatabaseNames.GetConsistentName(_project.Name + "_" + _project.ProjectNumber);
dbToExtractTo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbname);
if (dbToExtractTo.Exists())
{
dbToExtractTo.Drop();
}
base.Execute(out execute, out result);
var destinationTable = dbToExtractTo.ExpectTable(_expectedTableName);
Assert.IsTrue(destinationTable.Exists());
var dt = destinationTable.GetDataTable();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(_cohortKeysGenerated[_cohortKeysGenerated.Keys.First()].Trim(), dt.Rows[0]["ReleaseID"]);
Assert.AreEqual(new DateTime(2001, 1, 1), dt.Rows[0]["DateOfBirth"]);
Assert.AreEqual(2001, dt.Rows[0]["YearOfBirth"]);
Assert.AreEqual(columnToTransform.Data_type, destinationTable.DiscoverColumn("DateOfBirth").DataType.SQLType);
Assert.AreEqual("int", destinationTable.DiscoverColumn("YearOfBirth").DataType.SQLType);
}
finally
{
if (_extractionServer != null)
{
_extractionServer.DeleteInDatabase();
}
if (dbToExtractTo != null)
{
dbToExtractTo.Drop();
}
}
}
public void TestRemoteDatabaseAttach(DatabaseType dbType, Scenario scenario)
{
var db = GetCleanedServer(dbType);
DataTable dt = new DataTable();
dt.Columns.Add("Fish");
dt.Columns.Add("hic_Heroism");
dt.Rows.Add("123", 11);
var tbl = db.CreateTable("MyTable", dt);
Assert.AreEqual(1, tbl.GetRowCount());
TableInfo ti;
ColumnInfo[] cols;
Import(tbl, out ti, out cols);
//Create a virtual RAW column
if (scenario == Scenario.MissingPreLoadDiscardedColumn || scenario == Scenario.MissingPreLoadDiscardedColumnButSelectStar)
{
new PreLoadDiscardedColumn(CatalogueRepository, ti, "MyMissingCol");
}
var externalServer = new ExternalDatabaseServer(CatalogueRepository, "MyFictionalRemote", null);
externalServer.SetProperties(db);
var attacher = new RemoteDatabaseAttacher();
attacher.Initialize(null, db);
attacher.LoadRawColumnsOnly = scenario == Scenario.AllRawColumns || scenario == Scenario.MissingPreLoadDiscardedColumn;
attacher.RemoteSource = externalServer;
var lm = new LogManager(new ServerDefaults(CatalogueRepository).GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID));
lm.CreateNewLoggingTaskIfNotExists("amagad");
var dli = lm.CreateDataLoadInfo("amagad", "p", "a", "", true);
var job = Mock.Of <IDataLoadJob>(p =>
p.RegularTablesToLoad == new List <ITableInfo> {
ti
} &&
p.LookupTablesToLoad == new List <ITableInfo>() && p.DataLoadInfo == dli);
switch (scenario)
{
case Scenario.AllRawColumns:
break;
case Scenario.AllColumns:
break;
case Scenario.MissingPreLoadDiscardedColumn:
var ex = Assert.Throws <PipelineCrashedException>(() => attacher.Attach(job, new GracefulCancellationToken()));
Assert.AreEqual("Invalid column name 'MyMissingCol'.", (ex.InnerException.InnerException).InnerException.Message);
return;
case Scenario.MissingPreLoadDiscardedColumnButSelectStar:
break;
default:
throw new ArgumentOutOfRangeException("scenario");
}
attacher.Attach(job, new GracefulCancellationToken());
Assert.AreEqual(2, tbl.GetRowCount());
dt = tbl.GetDataTable();
VerifyRowExist(dt, 123, 11);
if (scenario == Scenario.AllRawColumns)
{
VerifyRowExist(dt, 123, DBNull.Value);
}
attacher.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
externalServer.DeleteInDatabase();
}
public void GatherAndShare_ANOTable_Test(bool goViaJson)
{
var anoserver = new ExternalDatabaseServer(CatalogueRepository, "MyGatherAndShareTestANOServer", new ANOStorePatcher());
var anoTable = new ANOTable(CatalogueRepository, anoserver, "ANOMagad", "N");
Assert.AreEqual(anoTable.Server_ID, anoserver.ID);
Gatherer g = new Gatherer(RepositoryLocator);
Assert.IsTrue(g.CanGatherDependencies(anoTable));
var gObj = g.GatherDependencies(anoTable);
//root should be the server
Assert.AreEqual(gObj.Object, anoserver);
Assert.AreEqual(gObj.Children.Single().Object, anoTable);
//get the sharing definitions
var shareManager = new ShareManager(RepositoryLocator);
ShareDefinition defParent = gObj.ToShareDefinition(shareManager, new List <ShareDefinition>());
ShareDefinition defChild = gObj.Children.Single().ToShareDefinition(shareManager, new List <ShareDefinition>(new [] { defParent }));
//make it look like we never had it in the first place
shareManager.GetNewOrExistingExportFor(anoserver).DeleteInDatabase();
shareManager.GetNewOrExistingExportFor(anoTable).DeleteInDatabase();
anoTable.DeleteInDatabase();
anoserver.DeleteInDatabase();
if (goViaJson)
{
var sParent = JsonConvertExtensions.SerializeObject(defParent, RepositoryLocator);
var sChild = JsonConvertExtensions.SerializeObject(defChild, RepositoryLocator);
defParent = (ShareDefinition)JsonConvertExtensions.DeserializeObject(sParent, typeof(ShareDefinition), RepositoryLocator);
defChild = (ShareDefinition)JsonConvertExtensions.DeserializeObject(sChild, typeof(ShareDefinition), RepositoryLocator);
}
var anoserverAfter = new ExternalDatabaseServer(shareManager, defParent);
Assert.IsTrue(anoserverAfter.Exists());
//new instance
Assert.AreNotEqual(anoserverAfter.ID, anoserver.ID);
//same properties
Assert.AreEqual(anoserverAfter.Name, anoserver.Name);
Assert.AreEqual(anoserverAfter.CreatedByAssembly, anoserver.CreatedByAssembly);
Assert.AreEqual(anoserverAfter.Database, anoserver.Database);
Assert.AreEqual(anoserverAfter.DatabaseType, anoserver.DatabaseType);
Assert.AreEqual(anoserverAfter.Username, anoserver.Username);
Assert.AreEqual(anoserverAfter.Password, anoserver.Password);
var anoTableAfter = new ANOTable(shareManager, defChild);
//new instance
Assert.AreNotEqual(anoTableAfter.ID, anoTable.ID);
Assert.AreNotEqual(anoTableAfter.Server_ID, anoTable.Server_ID);
//same properties
Assert.AreEqual(anoTableAfter.NumberOfCharactersToUseInAnonymousRepresentation, anoTable.NumberOfCharactersToUseInAnonymousRepresentation);
Assert.AreEqual(anoTableAfter.Suffix, anoTable.Suffix);
//change a property and save it
anoTableAfter.Suffix = "CAMMELS!";
CatalogueRepository.SaveToDatabase(anoTableAfter);
//anoTableAfter.SaveToDatabase(); <- this decides to go check the ANOTable exists on the server refernced which is immaginary btw >< thats why we have the above line instead
//reimport (this time it should be an update, we import the share definitions and it overrdies our database copy (sharing is UPSERT)
var anoTableAfter2 = new ANOTable(shareManager, defChild);
Assert.AreEqual(anoTableAfter.ID, anoTableAfter2.ID);
Assert.AreEqual("N", anoTableAfter2.Suffix);
Assert.AreEqual(ChangeDescription.DatabaseCopyDifferent, anoTableAfter.HasLocalChanges().Evaluation);
anoTableAfter.DeleteInDatabase();
anoserverAfter.DeleteInDatabase();
foreach (ObjectImport o in RepositoryLocator.CatalogueRepository.GetAllObjects <ObjectImport>())
{
o.DeleteInDatabase();
}
}
public void DropDatabases()
{
queryCacheDatabase.Drop();
externalDatabaseServer.DeleteInDatabase();
}