public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated)
{
var cmds = new List<InsertDelete>();
cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID));
if (archiveTable != null) {
cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID));
}
var connStr = buildConnString(dbName);
var rowCounts = new RowCounts(0, 0);
using (var conn = new OleDbConnection(connStr)) {
conn.Open();
var trans = conn.BeginTransaction();
foreach (var id in cmds) {
id.delete.Transaction = trans;
id.delete.Connection = conn;
id.delete.CommandTimeout = Config.QueryTimeout;
logger.Log(id.delete.CommandText, LogLevel.Trace);
int deleted = id.delete.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info);
id.insert.Transaction = trans;
id.insert.Connection = conn;
id.insert.CommandTimeout = Config.QueryTimeout;
logger.Log(id.insert.CommandText, LogLevel.Trace);
int inserted = id.insert.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info);
rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted);
}
trans.Commit();
}
return rowCounts;
}
public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated)
{
var cmds = new List <InsertDelete>();
cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID));
if (archiveTable != null)
{
cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID));
}
var connStr = buildConnString(dbName);
var rowCounts = new RowCounts(0, 0);
using (var conn = new OleDbConnection(connStr)) {
conn.Open();
var trans = conn.BeginTransaction();
foreach (var id in cmds)
{
id.delete.Transaction = trans;
id.delete.Connection = conn;
id.delete.CommandTimeout = Config.QueryTimeout;
logger.Log(id.delete.CommandText, LogLevel.Trace);
int deleted = id.delete.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info);
id.insert.Transaction = trans;
id.insert.Connection = conn;
id.insert.CommandTimeout = Config.QueryTimeout;
logger.Log(id.insert.CommandText, LogLevel.Trace);
int inserted = id.insert.ExecuteNonQuery();
logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info);
rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted);
}
trans.Commit();
}
return(rowCounts);
}
/// <summary>
/// Apply changes for the change tables we captured
/// </summary>
/// <param name="tables">Change tables we captured</param>
/// <param name="CTID">Change tracking id</param>
/// <param name="isConsolidated">Whether changes are consolidated</param>
/// <returns></returns>
private RowCounts ApplyChanges(List <ChangeTable> tables, Int64 CTID, bool isConsolidated)
{
var hasArchive = ValidTablesAndArchives(tables, CTID);
var actions = new List <Action>();
var counts = new ConcurrentDictionary <string, RowCounts>();
foreach (var tableArchive in hasArchive)
{
KeyValuePair <TableConf, TableConf> tLocal = tableArchive;
Action act = () => {
try {
logger.Log(new { message = "Applying changes", Table = tLocal.Key.Name + (tLocal.Value == null ? "" : " (and archive)") }, LogLevel.Debug);
var sw = Stopwatch.StartNew();
var rc = destDataUtils.ApplyTableChanges(tLocal.Key, tLocal.Value, Config.SlaveDB, CTID, Config.SlaveCTDB, isConsolidated);
counts[tLocal.Key.Name] = rc;
logger.Log(new { message = "ApplyTableChanges : " + sw.Elapsed, Table = tLocal.Key.Name }, LogLevel.Trace);
} catch (Exception e) {
HandleException(e, tLocal.Key);
}
};
actions.Add(act);
}
logger.Log("Parallel invocation of " + actions.Count + " table change applies", LogLevel.Trace);
var options = new ParallelOptions();
options.MaxDegreeOfParallelism = Config.MaxThreads;
Parallel.Invoke(options, actions.ToArray());
RowCounts total = counts.Values.Aggregate(new RowCounts(0, 0), (a, b) => new RowCounts(a.Inserted + b.Inserted, a.Deleted + b.Deleted));
return(total);
}
private void RecordRowCounts(RowCounts actual, ChangeTrackingBatch ctb)
{
var expected = sourceDataUtils.GetExpectedRowCounts(Config.RelayDB, ctb.CTID);
logger.Log("Expected row counts: " + expected + " | actual: " + actual, LogLevel.Info);
double diff = expected - actual.Inserted;
double mismatch;
if (expected == 0)
{
if (actual.Inserted == 0)
{
mismatch = 0.0;
}
else
{
logger.Log("Expected 0 rows, got " + actual.Inserted + " rows inserted on slave.", LogLevel.Error);
return;
}
}
else
{
mismatch = diff / expected;
}
int percentDiff = (int)(mismatch * 100);
string key = string.Format("db.mssql_changetracking_counters.RecordCountMismatchProd{0}.{1}", Config.Slave.Replace('.', '_'), Config.SlaveDB);
logger.Increment(key, percentDiff);
}
/// <summary>
/// Runs a single change tracking batch
/// </summary>
/// <param name="ctb">Change tracking batch object to work on</param>
private void RunSingleBatch(ChangeTrackingBatch ctb)
{
Stopwatch sw;
logger.Log("Applying schema changes ", LogLevel.Info);
ApplySchemaChangesAndWrite(ctb);
//marking this field so that all completed slave batches will have the same values
sourceDataUtils.WriteBitWise(Config.RelayDB, ctb.CTID, Convert.ToInt32(SyncBitWise.ConsolidateBatches), AgentType.Slave);
logger.Log("Populating table list", LogLevel.Info);
List <ChangeTable> existingCTTables = PopulateTableList(Config.Tables, Config.RelayDB, new List <ChangeTrackingBatch>()
{
ctb
});
logger.Log("Capturing field lists", LogLevel.Info);
SetFieldListsSlave(Config.RelayDB, Config.Tables, ctb, existingCTTables);
if ((ctb.SyncBitWise & Convert.ToInt32(SyncBitWise.DownloadChanges)) == 0)
{
logger.Log("Downloading changes", LogLevel.Info);
sw = Stopwatch.StartNew();
CopyChangeTables(Config.Tables, Config.RelayDB, Config.SlaveCTDB, ctb.CTID, existingCTTables);
logger.Log("CopyChangeTables: " + sw.Elapsed, LogLevel.Trace);
sourceDataUtils.WriteBitWise(Config.RelayDB, ctb.CTID, Convert.ToInt32(SyncBitWise.DownloadChanges), AgentType.Slave);
logger.Timing(StepTimingKey("DownloadChanges"), (int)sw.ElapsedMilliseconds);
}
if ((ctb.SyncBitWise & Convert.ToInt32(SyncBitWise.ApplyChanges)) == 0)
{
logger.Log("Applying changes", LogLevel.Info);
sw = Stopwatch.StartNew();
RowCounts total = ApplyChanges(existingCTTables, ctb.CTID, isConsolidated: false);
RecordRowCounts(total, ctb);
logger.Log("ApplyChanges: " + sw.Elapsed, LogLevel.Trace);
sourceDataUtils.WriteBitWise(Config.RelayDB, ctb.CTID, Convert.ToInt32(SyncBitWise.ApplyChanges), AgentType.Slave);
logger.Timing(StepTimingKey("ApplyChanges"), (int)sw.ElapsedMilliseconds);
}
logger.Log("Syncing history tables", LogLevel.Info);
sw = Stopwatch.StartNew();
SyncHistoryTables(Config.SlaveCTDB, existingCTTables, isConsolidated: false);
logger.Log("SyncHistoryTables: " + sw.Elapsed, LogLevel.Trace);
var syncStopTime = DateTime.Now;
sourceDataUtils.MarkBatchComplete(Config.RelayDB, ctb.CTID, syncStopTime, Config.Slave);
string key = String.Format(
"db.mssql_changetracking_counters.DataDurationToSync{0}.{1}",
Config.Slave.Replace('.', '_'),
Config.SlaveDB);
logger.Increment(key, (int)(syncStopTime - ctb.SyncStartTime.Value).TotalMinutes);
logger.Timing(StepTimingKey("SyncHistoryTables"), (int)sw.ElapsedMilliseconds);
}
// internal for testing
internal static RowCounts[][] GetBaseRowCounts(IReadOnlyList <int> baseRowCounts, int generations)
{
var rowCounts = new RowCounts[TableIndexExtensions.Count][];
for (int t = 0; t < rowCounts.Length; t++)
{
rowCounts[t] = new RowCounts[generations];
rowCounts[t][0].AggregateInserts = baseRowCounts[t];
}
return(rowCounts);
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
int rowCounts = 0, colCounts = 0;
//有效行列数 不包含中间的空行
if (isValid)
{
rowCounts = sheet.UsedRange.Rows.Count;
colCounts = sheet.UsedRange.Columns.Count;
}
//空行/列截止
else if (isActive)
{
rowCounts = sheet.UsedRange.CurrentRegion.Rows.Count;
colCounts = sheet.UsedRange.CurrentRegion.Columns.Count;
}
else if (isSingleLine)
{
rowCounts = sheet.get_Range("A65535").get_End(Excel.XlDirection.xlUp).Row;
colCounts = sheet.get_Range("IV1").get_End(Excel.XlDirection.xlToLeft).Column;
}
RowCounts.Set(context, rowCounts);
ColCounts.Set(context, colCounts);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL获取行列总数过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
return(m_Delegate.BeginInvoke(callback, state));
}
public async Task RenderAsync(CancellationToken cancellationToken = default)
{
var orphanedTables = Tables
.Where(t => t.ParentKeys.Empty() && t.ChildKeys.Empty())
.ToList();
var mapper = new OrphansModelMapper();
var orphanedTableViewModels = orphanedTables
.Select(t =>
{
if (!RowCounts.TryGetValue(t.Name, out var rowCount))
{
rowCount = 0;
}
return(mapper.Map(t, rowCount));
})
.ToList();
var templateParameter = new Orphans(orphanedTableViewModels);
var renderedOrphans = await Formatter.RenderTemplateAsync(templateParameter, cancellationToken).ConfigureAwait(false);
var databaseName = !IdentifierDefaults.Database.IsNullOrWhiteSpace()
? IdentifierDefaults.Database + " Database"
: "Database";
var pageTitle = "Orphan Tables · " + databaseName;
var orphansContainer = new Container(renderedOrphans, pageTitle, string.Empty);
var renderedPage = await Formatter.RenderTemplateAsync(orphansContainer, cancellationToken).ConfigureAwait(false);
if (!ExportDirectory.Exists)
{
ExportDirectory.Create();
}
var outputPath = Path.Combine(ExportDirectory.FullName, "orphans.html");
using var writer = File.CreateText(outputPath);
await writer.WriteAsync(renderedPage.AsMemory(), cancellationToken).ConfigureAwait(false);
await writer.FlushAsync().ConfigureAwait(false);
}
public async Task RenderAsync(CancellationToken cancellationToken = default)
{
var mapper = new MainModelMapper();
var tableViewModels = new List <Main.Table>();
foreach (var table in Tables)
{
if (!RowCounts.TryGetValue(table.Name, out var rowCount))
{
rowCount = 0;
}
var renderTable = mapper.Map(table, rowCount);
tableViewModels.Add(renderTable);
}
var tablesVm = new Tables(tableViewModels);
var renderedMain = await Formatter.RenderTemplateAsync(tablesVm, cancellationToken).ConfigureAwait(false);
var databaseName = !IdentifierDefaults.Database.IsNullOrWhiteSpace()
? IdentifierDefaults.Database + " Database"
: "Database";
var pageTitle = "Tables · " + databaseName;
var mainContainer = new Container(renderedMain, pageTitle, string.Empty);
var renderedPage = await Formatter.RenderTemplateAsync(mainContainer, cancellationToken).ConfigureAwait(false);
if (!ExportDirectory.Exists)
{
ExportDirectory.Create();
}
var outputPath = Path.Combine(ExportDirectory.FullName, "tables.html");
using var writer = File.CreateText(outputPath);
await writer.WriteAsync(renderedPage.AsMemory(), cancellationToken).ConfigureAwait(false);
await writer.FlushAsync().ConfigureAwait(false);
}
public async Task RenderAsync(CancellationToken cancellationToken = default)
{
var mapper = new MainModelMapper();
var columns = 0U;
var constraints = 0U;
var indexesCount = 0U;
var tableViewModels = new List <Main.Table>();
foreach (var table in Tables)
{
if (!RowCounts.TryGetValue(table.Name, out var rowCount))
{
rowCount = 0;
}
var renderTable = mapper.Map(table, rowCount);
var uniqueKeyLookup = table.GetUniqueKeyLookup();
var uniqueKeyCount = uniqueKeyLookup.UCount();
var checksLookup = table.GetCheckLookup();
var checksCount = checksLookup.UCount();
var indexesLookup = table.GetIndexLookup();
var indexCount = indexesLookup.UCount();
indexesCount += indexCount;
await table.PrimaryKey.IfSomeAsync(_ => constraints++).ConfigureAwait(false);
constraints += uniqueKeyCount;
constraints += renderTable.ParentsCount;
constraints += checksCount;
columns += renderTable.ColumnCount;
tableViewModels.Add(renderTable);
}
var viewViewModels = Views.Select(mapper.Map).ToList();
columns += (uint)viewViewModels.Sum(v => v.ColumnCount);
var sequenceViewModels = Sequences.Select(mapper.Map).ToList();
var synonymTargets = new SynonymTargets(Tables, Views, Sequences, Synonyms, Routines);
var synonymViewModels = Synonyms.Select(s => mapper.Map(s, synonymTargets)).ToList();
var routineViewModels = Routines.Select(mapper.Map).ToList();
var schemas = Tables.Select(t => t.Name)
.Union(Views.Select(v => v.Name))
.Union(Sequences.Select(s => s.Name))
.Union(Synonyms.Select(s => s.Name))
.Union(Routines.Select(r => r.Name))
.Select(n => n.Schema)
.Where(n => n != null)
.Distinct()
.Where(s => s != null)
.Select(s => s !)
.OrderBy(n => n)
.ToList();
var templateParameter = new Main(
Database.IdentifierDefaults.Database,
DatabaseDisplayVersion ?? string.Empty,
columns,
constraints,
indexesCount,
schemas,
tableViewModels,
viewViewModels,
sequenceViewModels,
synonymViewModels,
routineViewModels
);
var renderedMain = await Formatter.RenderTemplateAsync(templateParameter, cancellationToken).ConfigureAwait(false);
var databaseName = !Database.IdentifierDefaults.Database.IsNullOrWhiteSpace()
? Database.IdentifierDefaults.Database + " Database"
: "Database";
var pageTitle = "Home · " + databaseName;
var mainContainer = new Container(renderedMain, pageTitle, string.Empty);
var renderedPage = await Formatter.RenderTemplateAsync(mainContainer, cancellationToken).ConfigureAwait(false);
if (!ExportDirectory.Exists)
{
ExportDirectory.Create();
}
var outputPath = Path.Combine(ExportDirectory.FullName, "index.html");
using var writer = File.CreateText(outputPath);
await writer.WriteAsync(renderedPage.AsMemory(), cancellationToken).ConfigureAwait(false);
await writer.FlushAsync().ConfigureAwait(false);
}
private void RecordRowCounts(RowCounts actual, ChangeTrackingBatch ctb)
{
var expected = sourceDataUtils.GetExpectedRowCounts(Config.RelayDB, ctb.CTID);
logger.Log("Expected row counts: " + expected + " | actual: " + actual, LogLevel.Info);
double diff = expected - actual.Inserted;
double mismatch;
if (expected == 0) {
if (actual.Inserted == 0) {
mismatch = 0.0;
} else {
logger.Log("Expected 0 rows, got " + actual.Inserted + " rows inserted on slave.", LogLevel.Error);
return;
}
} else {
mismatch = diff / expected;
}
int percentDiff = (int)(mismatch * 100);
string key = string.Format("db.mssql_changetracking_counters.RecordCountMismatchProd{0}.{1}", Config.Slave.Replace('.', '_'), Config.SlaveDB);
logger.Increment(key, percentDiff);
}
public Table Map(IRelationalDatabaseTable table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var tableColumns = table.Columns.Select((c, i) => new { Column = c, Ordinal = i + 1 }).ToList();
var primaryKey = table.PrimaryKey;
var uniqueKeys = table.UniqueKeys.ToList();
var parentKeys = table.ParentKeys.ToList();
var childKeys = table.ChildKeys.ToList();
var checks = table.Checks.ToList();
var triggers = table.Triggers.ToList();
var columns = new List <Table.Column>();
foreach (var tableColumn in tableColumns)
{
var col = tableColumn.Column;
var columnName = col.Name.LocalName;
var qualifiedColumnName = table.Name.ToVisibleName() + "." + columnName;
var isPrimaryKey = primaryKey.Match(pk => pk.Columns.Any(c => c.Name.LocalName == columnName), () => false);
var isUniqueKey = uniqueKeys.Any(uk => uk.Columns.Any(ukc => ukc.Name.LocalName == columnName));
var isParentKey = parentKeys.Any(fk => fk.ChildKey.Columns.Any(fkc => fkc.Name.LocalName == columnName));
var matchingParentKeys = parentKeys.Where(fk => fk.ChildKey.Columns.Any(fkc => fkc.Name.LocalName == columnName)).ToList();
var columnParentKeys = new List <Table.ParentKey>();
foreach (var parentKey in matchingParentKeys)
{
var columnIndexes = parentKey.ChildKey.Columns
.Select((c, i) => c.Name.LocalName == columnName ? i : -1)
.Where(i => i >= 0)
.ToList();
var parentColumnNames = parentKey.ParentKey.Columns
.Where((_, i) => columnIndexes.Contains(i))
.Select(c => c.Name.LocalName)
.ToList();
var childKeyName = parentKey.ChildKey.Name.Match(name => name.LocalName, () => string.Empty);
var columnFks = parentColumnNames.Select(colName =>
new Table.ParentKey(
childKeyName,
parentKey.ParentTable,
colName,
qualifiedColumnName,
RootPath
)).ToList();
columnParentKeys.AddRange(columnFks);
}
var matchingChildKeys = childKeys.Where(ck => ck.ParentKey.Columns.Any(ckc => ckc.Name.LocalName == columnName)).ToList();
var columnChildKeys = new List <Table.ChildKey>();
foreach (var childKey in matchingChildKeys)
{
var columnIndexes = childKey.ParentKey.Columns
.Select((c, i) => c.Name.LocalName == columnName ? i : -1)
.Where(i => i >= 0)
.ToList();
var childColumnNames = childKey.ChildKey.Columns
.Where((_, i) => columnIndexes.Contains(i))
.Select(c => c.Name.LocalName)
.ToList();
var childKeyName = childKey.ChildKey.Name.Match(name => name.LocalName, () => string.Empty);
var columnFks = childColumnNames.Select(colName =>
new Table.ChildKey(
childKeyName,
childKey.ChildTable,
colName,
qualifiedColumnName,
RootPath
)).ToList();
columnChildKeys.AddRange(columnFks);
}
var column = new Table.Column(
columnName,
tableColumn.Ordinal,
tableColumn.Column.IsNullable,
tableColumn.Column.Type.Definition,
tableColumn.Column.DefaultValue,
isPrimaryKey,
isUniqueKey,
isParentKey,
columnChildKeys,
columnParentKeys
);
columns.Add(column);
}
var tableIndexes = table.Indexes.ToList();
var renderIndexes = tableIndexes.Select(index =>
new Table.Index(
index.Name?.LocalName,
index.IsUnique,
index.Columns.Select(c => c.Expression).ToList(),
index.Columns.Select(c => c.Order).ToList(),
index.IncludedColumns.Select(c => c.Name.LocalName).ToList()
)).ToList();
var renderPrimaryKey = primaryKey
.Map(pk => new Table.PrimaryKeyConstraint(
pk.Name.Match(name => name.LocalName, () => string.Empty),
pk.Columns.Select(c => c.Name.LocalName).ToList()
));
var renderUniqueKeys = uniqueKeys
.Select(uk => new Table.UniqueKey(
uk.Name.Match(name => name.LocalName, () => string.Empty),
uk.Columns.Select(c => c.Name.LocalName).ToList()
)).ToList();
var renderParentKeys = parentKeys.Select(pk =>
new Table.ForeignKey(
pk.ChildKey.Name.Match(name => name.LocalName, () => string.Empty),
pk.ChildKey.Columns.Select(c => c.Name.LocalName).ToList(),
pk.ParentTable,
pk.ParentKey.Name.Match(name => name.LocalName, () => string.Empty),
pk.ParentKey.Columns.Select(c => c.Name.LocalName).ToList(),
pk.DeleteAction,
pk.UpdateAction,
RootPath
)).ToList();
var renderChecks = checks.Select(c =>
new Table.CheckConstraint(
c.Name.Match(name => name.LocalName, () => string.Empty),
c.Definition
)).ToList();
var renderTriggers = triggers.Select(tr =>
new Table.Trigger(
table.Name,
tr.Name.LocalName,
tr.Definition,
tr.QueryTiming,
tr.TriggerEvent
)).ToList();
var oneDegreeTables = RelationshipFinder.GetTablesByDegrees(table, 1);
var twoDegreeTables = RelationshipFinder.GetTablesByDegrees(table, 2);
var dotFormatter = new DotFormatter(IdentifierDefaults);
var renderOptions = new DotRenderOptions {
HighlightedTable = table.Name
};
var oneDegreeDot = dotFormatter.RenderTables(oneDegreeTables, RowCounts, renderOptions);
var twoDegreeDot = dotFormatter.RenderTables(twoDegreeTables, RowCounts, renderOptions);
var diagrams = new[]
{
new Table.Diagram(table.Name, "One Degree", oneDegreeDot, true),
new Table.Diagram(table.Name, "Two Degrees", twoDegreeDot, false)
};
if (!RowCounts.TryGetValue(table.Name, out var rowCount))
{
rowCount = 0;
}
return(new Table(
table.Name,
columns,
renderPrimaryKey,
renderUniqueKeys,
renderParentKeys,
renderChecks,
renderIndexes,
renderTriggers,
diagrams,
RootPath,
rowCount
));
}