public void CreateTablesInStage(DatabaseCloner cloner, LoadBubble stage)
{
foreach (TableInfo regularTableInfo in RegularTablesToLoad)
{
cloner.CreateTablesInDatabaseFromCatalogueInfo(_listener, regularTableInfo, stage);
}
foreach (TableInfo lookupTableInfo in LookupTablesToLoad)
{
cloner.CreateTablesInDatabaseFromCatalogueInfo(_listener, lookupTableInfo, stage);
}
PushForDisposal(cloner);
}
private void CreateRawDatabaseIfRequired(IDataLoadJob job)
{
// Ask the runtime process host if we need to create the RAW database
if (!MustCreateRawDatabase())
{
return;
}
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Determined that we must create the RAW database tables..."));
var cloner = new DatabaseCloner(_databaseConfiguration);
cloner.CreateDatabaseForStage(LoadBubble.Raw);
job.CreateTablesInStage(cloner, LoadBubble.Raw);
}
// This no longer copies between servers, but the original test didn't guarantee that would happen anyway
public void CloneDatabaseAndTable()
{
string testLiveDatabaseName = TestDatabaseNames.GetConsistentName("TEST");
var testDb = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName);
var raw = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName + "_RAW");
foreach (DiscoveredDatabase db in new[] { raw, testDb })
{
if (db.Exists())
{
foreach (DiscoveredTable table in db.DiscoverTables(true))
{
table.Drop();
}
db.Drop();
}
}
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.CreateDatabase(testLiveDatabaseName);
Assert.IsTrue(testDb.Exists());
testDb.CreateTable("Table_1", new[] { new DatabaseColumnRequest("Id", "int") });
//clone the builder
var builder = new SqlConnectionStringBuilder(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.Builder.ConnectionString)
{
InitialCatalog = testLiveDatabaseName
};
var dbConfiguration = new HICDatabaseConfiguration(new DiscoveredServer(builder), null, new ServerDefaults(CatalogueRepository));
var cloner = new DatabaseCloner(dbConfiguration);
try
{
var cloneDb = cloner.CreateDatabaseForStage(LoadBubble.Raw);
//confirm database appeared
Assert.IsTrue(DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(testLiveDatabaseName + "_RAW").Exists());
//now create a catalogue and wire it SetUp to the table TEST on the test database server
Catalogue cata = SetupATestCatalogue(builder, testLiveDatabaseName, "Table_1");
//now clone the catalogue data structures to MachineName
foreach (TableInfo tableInfo in cata.GetTableInfoList(false))
{
cloner.CreateTablesInDatabaseFromCatalogueInfo(new ThrowImmediatelyDataLoadEventListener(), tableInfo, LoadBubble.Raw);
}
Assert.IsTrue(raw.Exists());
Assert.IsTrue(raw.ExpectTable("Table_1").Exists());
}
finally
{
cloner.LoadCompletedSoDispose(ExitCodeType.Success, new ThrowImmediatelyDataLoadEventListener());
while (toCleanUp.Count > 0)
{
try
{
toCleanUp.Pop().DeleteInDatabase();
}
catch (Exception e)
{
//always clean SetUp everything
Console.WriteLine(e);
}
}
}
}
public void CreateTablesInStage(DatabaseCloner cloner, LoadBubble stage)
{
}
private void CreateStagingTables(IDataLoadJob job)
{
var cloner = new DatabaseCloner(_databaseConfiguration);
job.CreateTablesInStage(cloner, LoadBubble.Staging);
}
public override ExitCodeType Run(IDataLoadJob job, GracefulCancellationToken cancellationToken)
{
if (Skip(job))
{
return(ExitCodeType.Error);
}
var configuration = job.Configuration;
var namer = configuration.DatabaseNamer;
DiscoveredServer server = job.LoadMetadata.GetDistinctLiveDatabaseServer();
//Drop any STAGING tables that already exist
foreach (var table in job.RegularTablesToLoad)
{
string stagingDbName = table.GetDatabaseRuntimeName(LoadStage.AdjustStaging, namer);
string stagingTableName = table.GetRuntimeName(LoadStage.AdjustStaging, namer);
var stagingDb = server.ExpectDatabase(stagingDbName);
var stagingTable = stagingDb.ExpectTable(stagingTableName);
if (stagingDb.Exists())
{
if (stagingTable.Exists())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, $"Dropping existing STAGING table remnant {stagingTable.GetFullyQualifiedName()}"));
stagingTable.Drop();
}
}
}
//Now create STAGING tables (empty)
var cloner = new DatabaseCloner(configuration);
job.CreateTablesInStage(cloner, LoadBubble.Staging);
using (DbConnection con = server.GetConnection())
{
con.Open();
Stopwatch sw = Stopwatch.StartNew();
foreach (TableInfo table in job.RegularTablesToLoad)
{
string fromDb = table.GetDatabaseRuntimeName(LoadStage.AdjustRaw, namer);
string toDb = table.GetDatabaseRuntimeName(LoadStage.AdjustStaging, namer);
string fromTable = table.GetRuntimeName(LoadStage.AdjustRaw, namer);
string toTable = table.GetRuntimeName(LoadStage.AdjustStaging, namer);
IQuerySyntaxHelper syntaxHelper = table.GetQuerySyntaxHelper();
var fromCols = server.ExpectDatabase(fromDb).ExpectTable(fromTable).DiscoverColumns();
var toCols = server.ExpectDatabase(toDb).ExpectTable(toTable).DiscoverColumns();
//Migrate only columns that appear in both tables
var commonColumns = fromCols.Select(f => f.GetRuntimeName()).Intersect(toCols.Select(t => t.GetRuntimeName())).ToArray();
string sql = string.Format(@"INSERT INTO {1}({2}) SELECT DISTINCT {2} FROM {0}",
syntaxHelper.EnsureFullyQualified(fromDb, null, fromTable),
syntaxHelper.EnsureFullyQualified(toDb, null, toTable),
string.Join(",", commonColumns.Select(c => syntaxHelper.EnsureWrapped(c))));
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to send SQL:" + sql));
DbCommand cmd = server.GetCommand(sql, con);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to migrate rows", ex));
throw;
}
}
sw.Stop();
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Migrated all rows using INSERT INTO in " + sw.ElapsedMilliseconds + "ms"));
}
return(ExitCodeType.Success);
}
