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(); }