private void RunSQL(DiscoveredDatabase db, string sql, string filename) { using (var con = db.Server.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(sql, con); string insert = @" INSERT INTO [RoundhousE].[ScriptsRun] ([script_name], [text_of_script], [text_hash], [one_time_script], [entry_date], [modified_date], [entered_by]) VALUES (@script_name, @text_of_script, @text_hash, @one_time_script, @entry_date, @modified_date, @entered_by) "; DateTime dt = DateTime.Now; var cmd2 = db.Server.GetCommand(insert, con); db.Server.AddParameterWithValueToCommand("@script_name", cmd2, filename); db.Server.AddParameterWithValueToCommand("@text_of_script", cmd2, sql); db.Server.AddParameterWithValueToCommand("@text_hash", cmd2, CalculateHash(sql)); db.Server.AddParameterWithValueToCommand("@one_time_script", cmd2, 1); db.Server.AddParameterWithValueToCommand("@entry_date", cmd2, dt); db.Server.AddParameterWithValueToCommand("@modified_date", cmd2, dt); db.Server.AddParameterWithValueToCommand("@entered_by", cmd2, Environment.UserName); cmd2.ExecuteNonQuery(); } }
public void TestRoundDateToMiddleOfQuarter(string input, string expectedDilute) { var tbl = Mock.Of <ITableInfo>(m => m.GetRuntimeName(LoadStage.AdjustStaging, null) == "DateRoundingTests"); var col = Mock.Of <IPreLoadDiscardedColumn>(c => c.TableInfo == tbl && c.GetRuntimeName() == "TestField"); var o = new RoundDateToMiddleOfQuarter(); o.ColumnToDilute = col; var sql = o.GetMutilationSql(null); var server = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer).Server; using (var con = server.BeginNewTransactedConnection()) { try { string insert = input != null ? "'" + input + "'" : "NULL"; server.GetCommand(@"CREATE TABLE DateRoundingTests(TestField datetime) INSERT INTO DateRoundingTests VALUES (" + insert + ")", con).ExecuteNonQuery(); UsefulStuff.ExecuteBatchNonQuery(sql, con.Connection, con.Transaction); var result = server.GetCommand("SELECT * from DateRoundingTests", con).ExecuteScalar(); if (expectedDilute == null) { Assert.AreEqual(DBNull.Value, result); } else { Assert.AreEqual(DateTime.Parse(expectedDilute), result); } } finally { con.ManagedTransaction.AbandonAndCloseConnection(); } } }
public void TestExcludeRight3OfUKPostcodes(string input, string expectedDilute) { var tbl = Mock.Of <ITableInfo>(t => t.GetRuntimeName(LoadStage.AdjustStaging, null) == "ExcludeRight3OfPostcodes"); var col = Mock.Of <IPreLoadDiscardedColumn>(c => c.TableInfo == tbl && c.GetRuntimeName() == "TestField"); var o = new ExcludeRight3OfUKPostcodes(); o.ColumnToDilute = col; var sql = o.GetMutilationSql(null); var server = DiscoveredDatabaseICanCreateRandomTablesIn.Server; using (var con = server.BeginNewTransactedConnection()) { try { string insert = input != null ? "'" + input + "'" : "NULL"; server.GetCommand(@"CREATE TABLE ExcludeRight3OfPostcodes(TestField varchar(15)) INSERT INTO ExcludeRight3OfPostcodes VALUES (" + insert + ")", con).ExecuteNonQuery(); UsefulStuff.ExecuteBatchNonQuery(sql, con.Connection, con.Transaction); var result = server.GetCommand("SELECT * from ExcludeRight3OfPostcodes", con).ExecuteScalar(); if (expectedDilute == null) { Assert.AreEqual(DBNull.Value, result); } else { Assert.AreEqual(expectedDilute, result); } } finally { con.ManagedTransaction.AbandonAndCloseConnection(); } } }
private void RunSQL(KeyValuePair <string, Patch> kvp) { using (var con = Database.Server.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(kvp.Value.GetScriptBody(), con, null, DiscoveredServerHelper.CreateDatabaseTimeoutInSeconds); } var now = DateTime.Now; Database.ExpectTable(RoundhouseScriptsRunTable, RoundhouseSchemaName) .Insert(new Dictionary <string, object>() { { "script_name", kvp.Key }, { "text_of_script", kvp.Value.EntireScript }, { "text_hash", CalculateHash(kvp.Value.EntireScript) }, { "entry_date", now }, { "modified_date", now }, { "entered_by", Environment.UserName }, }); SetVersion(kvp.Key, kvp.Value.DatabaseVersionNumber.ToString()); }
protected override void SetUp() { base.SetUp(); string sql = @"CREATE TABLE [dbo].[Tests]( [chi] [varchar](10) NULL, [Date] [datetime] NULL, [hb_extract] [varchar](1) NULL, [TestId] [int] NOT NULL, CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED ( [TestId] ASC ) ) GO CREATE TABLE [dbo].[Results]( [TestId] [int] NOT NULL, [Measure] [varchar](10) NOT NULL, [Value] [int] NULL, CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED ( [TestId] ASC, [Measure] ASC ) ) GO ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_Tests] FOREIGN KEY([TestId]) REFERENCES [dbo].[Tests] ([TestId]) GO"; var server = From.Server; using (var con = server.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(sql, con); } var importer1 = new TableInfoImporter(CatalogueRepository, From.ExpectTable("Tests")); var importer2 = new TableInfoImporter(CatalogueRepository, From.ExpectTable("Results")); importer1.DoImport(out t1, out c1); importer2.DoImport(out t2, out c2); var engineer1 = new ForwardEngineerCatalogue(t1, c1, true); var engineer2 = new ForwardEngineerCatalogue(t2, c2, true); engineer1.ExecuteForwardEngineering(out cata1, out cataItems1, out eis1); engineer2.ExecuteForwardEngineering(out cata2, out cataItems2, out eis2); new JoinInfo(CatalogueRepository, c1.Single(e => e.GetRuntimeName().Equals("TestId")), c2.Single(e => e.GetRuntimeName().Equals("TestId")), ExtractionJoinType.Left, null); _anoTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOTes", "T"); _anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 10; _anoTable.SaveToDatabase(); _anoTable.PushToANOServerAsNewTable("int", new ThrowImmediatelyCheckNotifier()); _comboCata = new Catalogue(CatalogueRepository, "Combo Catalogue"); //pk var ciTestId = new CatalogueItem(CatalogueRepository, _comboCata, "TestId"); var colTestId = c1.Single(c => c.GetRuntimeName().Equals("TestId")); ciTestId.ColumnInfo_ID = colTestId.ID; ciTestId.SaveToDatabase(); var eiTestId = new ExtractionInformation(CatalogueRepository, ciTestId, colTestId, colTestId.Name); //Measure var ciMeasure = new CatalogueItem(CatalogueRepository, _comboCata, "Measuree"); var colMeasure = c2.Single(c => c.GetRuntimeName().Equals("Measure")); ciMeasure.ColumnInfo_ID = colMeasure.ID; ciMeasure.SaveToDatabase(); var eiMeasure = new ExtractionInformation(CatalogueRepository, ciMeasure, colMeasure, colMeasure.Name); //Date var ciDate = new CatalogueItem(CatalogueRepository, _comboCata, "Dat"); var colDate = c1.Single(c => c.GetRuntimeName().Equals("Date")); ciDate.ColumnInfo_ID = colDate.ID; ciDate.SaveToDatabase(); var eiDate = new ExtractionInformation(CatalogueRepository, ciDate, colDate, colDate.Name); _destinationDatabase = To; }
private void CreateCohortDatabase() { //Where {0} is the name of the Cohort database //Where {1} is either CHI or ANOCHI depending on whether anonymisation is enabled on the target Catalogue //Where {2} is either 10 or 13 -- the column length of either CHI or ANOCHI var database = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(CohortDatabaseName); if (database.Exists()) { database.DiscoverTables(false).ToList().ForEach(t => t.Drop()); database.Drop(); } string sql = string.Format(@" CREATE DATABASE {0} GO USE {0} CREATE TABLE [dbo].[Cohort]( [PrivateID] [varchar](10) NOT NULL, [ReleaseID] [varchar](10) NULL, [cohortDefinition_id] [int] NOT NULL, CONSTRAINT [PK_Cohort] PRIMARY KEY CLUSTERED ( [PrivateID] ASC, [cohortDefinition_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[CohortDefinition]( [id] [int] IDENTITY(1,1) NOT NULL, [projectNumber] [int] NOT NULL, [version] [int] NOT NULL, [description] [varchar](4000) NOT NULL, [dtCreated] [date] NOT NULL, CONSTRAINT [PK_CohortDefinition] PRIMARY KEY NONCLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CohortDefinition] ADD CONSTRAINT [DF_CohortDefinition_dtCreated] DEFAULT (getdate()) FOR [dtCreated] GO ALTER TABLE [dbo].[Cohort] WITH CHECK ADD CONSTRAINT [FK_Cohort_CohortDefinition] FOREIGN KEY([cohortDefinition_id]) REFERENCES [dbo].[CohortDefinition] ([id]) GO ALTER TABLE [dbo].[Cohort] CHECK CONSTRAINT [FK_Cohort_CohortDefinition] GO ", //{0} CohortDatabaseName); using (var con = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(sql, con, timeout: 15); con.Close(); } }