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