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