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