public string Sql(TableId table) => new[] { table.Schema, table.Table }.NotNull().Join(".", QuoteIfRequired);
public static string Sql(this ICommonDb db, TableId table) => table.Schema.HasValue() ? $"{db.Sql(table.Schema)}.{db.Sql(table.Table)}" : $"{db.Sql(table.Table)}";
async Task <ByteSize> LoadBLobData(SyncTableCfg tableCfg, ILogger log, string loadId, string sourceSql, object maxTs, TableId loadTable) { var path = StringPath.Relative("sync", tableCfg.Name, loadId); var copyTask = Source.CopyTo(path, sourceSql, tableCfg, maxTs); var loadedFiles = new KeyedCollection <StringPath, FileListItem>(f => f.Path); while (true) // load as the files are created { if (copyTask.IsFaulted) { break; } var toLoad = (await Store.List(path).SelectManyList()) .Where(f => !loadedFiles.ContainsKey(f.Path)).ToArray(); if (toLoad.None()) { if (copyTask.IsCompleted) { break; } await 5.Seconds().Delay(); continue; } log.Debug("Sync {Table} - loading: {Files}", tableCfg.Name, toLoad.Join("|", l => l.Path.ToString())); await Dest.LoadFrom(toLoad.Select(f => f.Path), loadTable); loadedFiles.AddRange(toLoad); await toLoad.BlockAction(f => Store.Delete(f.Path, log), parallel : 8); } log.Information("Sync {Table} - copied {Files} files ({Size})", tableCfg.Name, loadedFiles.Count, loadedFiles.Sum(f => f.Bytes).Bytes().Humanize("#,#")); return(loadedFiles.Sum(f => f.Bytes).Bytes()); }
/// <summary>Legacy bulk copy, tmp table switching version</summary> public async Task UpdateTable(SyncTableCfg tableCfg, ILogger log, CancellationToken cancel, bool fullLoad = false, int limit = 0, SyncMode mode = SyncMode.Blob) { var sw = Stopwatch.StartNew(); await Dest.Init(Store.ContainerUrl); var sourceSql = tableCfg.Sql ?? $"select * from {Source.DefaultSchema}.tableCfg.Name"; var destTable = new TableId(Dest.DefaultSchema, tableCfg.Name); var destSchema = await Dest.Schema(destTable); var destExists = destSchema != null; var syncType = fullLoad || destSchema == null ? SyncType.Full : tableCfg.SyncType; if (syncType != SyncType.Full && destSchema?.Columns.Any(c => c.ColumnName.Equals(tableCfg.TsCol, StringComparison.InvariantCultureIgnoreCase)) == false) { syncType = SyncType.Full; } if (syncType.IsIncremental() && tableCfg.TsCol.NullOrEmpty()) { throw new InvalidOperationException("table configured for incremental, but no ts column was found"); } var maxTs = syncType.IsIncremental() ? await Dest.Conn.ExecuteScalar <object>(nameof(UpdateTable), $"select max({Dest.Sql(tableCfg.TsCol)}) from {Dest.Sql(destTable)}") : null; // start reading and get schema. if we are blowwing, do this to get the schema without loading any rows using var reader = await Source.Read(sourceSql, tableCfg, maxTs, mode == SyncMode.Blob? 0 : limit); var querySchema = reader.Schema(); destSchema ??= querySchema; // if there is no final destination schema, then it should match the source // apply overrides to dest schema destSchema = new TableSchema(destSchema.Columns.Select(c => { var cfg = tableCfg.Cols[c.ColumnName]; return(new ColumnSchema(c.ColumnName, c.DataType) { ProviderTypeExpression = cfg?.SqlType, Key = cfg?.Id, AllowDBNull = cfg?.Null }); })); // create table if not exists if (!destExists) { await CreateDestTable(destTable, tableCfg, destSchema, log); } // prepare tmp table if required var tmpTable = destTable.WithTable($"{destTable.Table}_tmp"); var loadTable = destExists ? tmpTable : destTable; if (loadTable == tmpTable) { await CreateTmpTable(tmpTable, querySchema); } // copy data var newRows = 0L; var newBytes = 0.Bytes(); var loadId = DateTime.UtcNow.FileSafeTimestamp(); if (mode == SyncMode.Blob) { newBytes += await LoadBLobData(tableCfg, log, loadId, sourceSql, maxTs, loadTable); } else { newRows = await Dest.BulkCopy(reader, loadTable, log, cancel); log.Debug("Sync {Table} - loaded {Rows} into {LoadTable} ({SyncType})", tableCfg.Name, newRows, loadTable, syncType); } // if we loaded in to temp table, work out best way to switch this in without downtime if (loadTable == tmpTable) { if (newRows == 0 && newBytes == 0.Bytes()) { await Dest.DropTable(tmpTable); // no new rows, nothing to do } else if (syncType.IsIncremental() || tableCfg.ManualSchema) // incremental load, or manual schema. Move the rows into the desitntion table { var cols = destSchema.Columns; var mergeRes = await Dest.Merge(destTable, tmpTable, tableCfg.IdCols, cols); log.Debug("Sync {Table} - merged {Records} from {TempTable}", tableCfg.Name, mergeRes, tmpTable); await Dest.DropTable(tmpTable); } else { // there may be moments where the table dissapears.I removed the transaction to get past this error: BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. //using (var trans = await Dest.Conn.Conn.BeginTransactionAsync(IsolationLevel.ReadUncommitted, cancel)) { await Dest.DropTable(destTable); await Dest.RenameTable(tmpTable, destTable); /*await trans.CommitAsync(); * }*/ log.Debug("Sync {Table} - switch out temp table {TempTable}", tableCfg.Name, tmpTable); } } log.Information("Sync {Table} - completed loading {Size} in {Duration}", tableCfg.Name, newBytes > 0.Bytes() ? newBytes.Humanize("#,#") : newRows.ToString("#,#"), sw.Elapsed.HumanizeShort()); }
public async Task UpdateTable(SyncTableCfg tableCfg, ILogger log, bool fullLoad = false, int limit = 0) { var sw = Stopwatch.StartNew(); var sourceTable = new TableId(Source.DefaultSchema, tableCfg.Name); var destTable = new TableId(Dest.DefaultSchema, tableCfg.Name); var destSchema = await Dest.Schema(destTable); var destExists = destSchema != null; var syncType = fullLoad || destSchema == null ? SyncType.Full : tableCfg.SyncType; if (syncType != SyncType.Full && destSchema?.Columns.Any(c => c.ColumnName.Equals(tableCfg.TsCol, StringComparison.InvariantCultureIgnoreCase)) == false) { syncType = SyncType.Full; } if (syncType.IsIncremental() && tableCfg.TsCol.NullOrEmpty()) { throw new InvalidOperationException("table configured for incremental, but no ts column was found"); } var maxTs = syncType.IsIncremental() ? await Dest.Connection.ExecuteScalar <object>(nameof(UpdateTable), $"select max({Dest.Sql(tableCfg.TsCol)}) from {Dest.Sql(destTable)}") : null; // start reading and get schema using var reader = await Source.Read(sourceTable, tableCfg, maxTs, limit); var querySchema = reader.Schema(); destSchema ??= querySchema; // if there is no final destination schema, then it should match the source // apply overrides to dest schema destSchema = new TableSchema(destSchema.Columns.Select(c => { var cfg = tableCfg.Cols[c.ColumnName]; return(new ColumnSchema(c.ColumnName, c.DataType) { ProviderTypeExpression = cfg?.TypeOverride, Key = cfg?.Id, AllowDBNull = cfg?.Null }); })); // create table if not exists if (!destExists) { await CreateDestTable(destTable, tableCfg, destSchema, log); } // prepare tmp table if required var tmpTable = destTable.WithTable($"{destTable.Table}_tmp"); var loadTable = destExists ? tmpTable : destTable; if (loadTable == tmpTable) { await CreateTmpTable(tmpTable, querySchema); } // copy data var newRows = await Dest.BulkCopy(reader, loadTable, log); log.Debug("{Table} - loaded {Rows} into {LoadTable} ({SyncType})", tableCfg.Name, newRows, loadTable, syncType); // if we loaded in to temp table, work out best way to switch this in without downtime if (loadTable == tmpTable) { if (newRows == 0) { await Dest.DropTable(tmpTable); // no new rows, nothing to do } else if (syncType.IsIncremental() || tableCfg.ManualSchema) // incremental load, or manual schema. Move the rows into the desitntion table { var cols = destSchema.Columns; var mergeRes = await Dest.Merge(destTable, tmpTable, tableCfg.IdCol, cols); log.Debug("{Table} - merged {Records} from {TempTable}", tableCfg.Name, mergeRes, tmpTable); await Dest.DropTable(tmpTable); } else { using (var trans = Dest.Connection.Conn.BeginTransaction()) { await Dest.DropTable(destTable, trans); await Dest.RenameTable(tmpTable, destTable, trans); await trans.CommitAsync(); log.Debug("{Table} - switch out temp table {TempTable}", tableCfg.Name, tmpTable); } } } log.Information("{Table} - completed loading {Rows} in {Duration}", tableCfg.Name, newRows, sw.Elapsed.HumanizeShort()); }