public static ReplicatorUsingCDC GetInstance(CDCReplicatorOptions options, string sourceCon, string partitionCon)
{
_instance.Value._sourceCon = sourceCon;
_instance.Value._partitionCon = partitionCon;
_instance.Value._options = options;
_instance.Value._sqlServerDriver = new SqlServerDriver(_instance.Value._sourceCon);
return(_instance.Value);
}
public async Task UpgradeAsync(string DbSchema, CDCReplicatorOptions option)
{
Console.WriteLine("Installing version " + _currentVersion + "...");
await this.IsAgentRunning();
string[] filenames = _assembly.GetManifestResourceNames();
string versionScriptPath = null;
if (filenames.Contains($"xAudit.CDC.Scripts.Versions.{_currentVersion}.sql"))
{
versionScriptPath = $"xAudit.CDC.Scripts.Versions.{_currentVersion}.sql";
}
else
{
var regex = new Regex(@"\d+\.\d+\.\d+");
var allVersions = filenames.Where(x => x.StartsWith("xAudit.CDC.Scripts.Versions")).Select(x => (Version)regex.Match(x).Value).ToArray();
Version previousVersion = _currentVersion.FindImmediatePrevious(allVersions);
versionScriptPath = $"xAudit.CDC.Scripts.Versions.{previousVersion}.sql";
Console.WriteLine("script not found for current version. Instead executing script from previous version " + previousVersion);
}
using (var transaction = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.ReadCommitted
},
TransactionScopeAsyncFlowOption.Enabled))
{
//execute cleanup query. Delete all SP, UDF etc belongs to current version
StringBuilder query = new StringBuilder();
using (Stream stream = _assembly.GetManifestResourceStream("xAudit.CDC.Scripts.cleanup.sql"))
{
using (StreamReader reader = new StreamReader(stream))
{
query.Append(await reader.ReadToEndAsync());
}
}
query = query.Replace("xAudit", DbSchema);
await _sqlServerDriver.ExecuteTextAsync(query.ToString(), null);
//execute current version scripts. Create all SP, UDF etc belongs to current version
query = query.Clear();
using (Stream stream = _assembly.GetManifestResourceStream(versionScriptPath))
{
using (StreamReader reader = new StreamReader(stream))
{
query.Append(await reader.ReadToEndAsync());
}
}
await _sqlServerDriver.ExecuteScriptAsync(query.ToString());
await this.AddVersion(option, DbSchema);
transaction.Complete();
}
}
private async Task AddVersion(CDCReplicatorOptions option, string dbSchemaName)
{
DbParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@VERSION", this._currentVersion.ToString()),
new SqlParameter("@MACHINE", Environment.MachineName),
new SqlParameter("@MAJOR", this._currentVersion.Major),
new SqlParameter("@MINOR", this._currentVersion.Minor),
new SqlParameter("@PATCH", this._currentVersion.Patch),
new SqlParameter("@PROCESSID", Process.GetCurrentProcess().Id),
new SqlParameter("@TOTALTABLES", option.Tables == null?(object)DBNull.Value:option.Tables.Count),
new SqlParameter("@TRACKSCHEMACHANGES", option.TrackSchemaChanges),
new SqlParameter("@INSTANCENAME", option.InstanceName),
};
await _sqlServerDriver.ExecuteNonQueryAsync(dbSchemaName + ".INSERT_NEW_VERSION", parameters);
Console.WriteLine("version " + this._currentVersion + " installed");
}
/// <summary>
/// Will try to enable cdc on the given tables if not already enabled
/// </summary>
/// <param name="tables">The list of tables under appropriate schema names</param>
/// <returns>Return the list of tables which got failed to be enabled for cdc</returns>
private async Task <AuditTableCollection> CheckAndApplyOnTables(string dbSchemaName, CDCReplicatorOptions option)
{
var ds = await _sqlServerDriver.GetDataSetAsync(dbSchemaName + ".GET_TRACKED_TABLES@2", null);
if (ds == null)
{
return(option.Tables);
}
HashSet <string> changedTables = new HashSet <string>();
HashSet <string> activeTables = new HashSet <string>();
HashSet <string> inputTables = AuditTableCollectionHelper.ToHashSet(option.Tables);
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
Console.WriteLine("\nThe following active tables have changed since the last run");
Console.WriteLine("---------------------------------------------------------");
foreach (DataRow row in ds.Tables[0].Rows)
{
var schema = Convert.ToString(row["SCHEMA"]);
var table = Convert.ToString(row["TABLE"]);
var tableName = schema + "." + table;
if (option.TrackSchemaChanges)
{
changedTables.Add(tableName);
}
log(tableName, Convert.ToString(row["COLUMN"]), Convert.ToString(row["CHANGE"]));
}
if (!option.TrackSchemaChanges)
{
Console.WriteLine("Warning! Tracking schema changes are disabled for this run");
}
}
if (ds.Tables[1] != null)
{
foreach (DataRow row in ds.Tables[1].Rows)
{
var schema = Convert.ToString(row["SOURCE_SCHEMA"]);
var table = Convert.ToString(row["SOURCE_TABLE"]);
var tableName = schema + "." + table;
activeTables.Add(tableName);
}
}
this.SegregateTables(inputTables, activeTables, changedTables, out HashSet <string> recreate, out HashSet <string> disable, out HashSet <string> enable);
await this.Enable(enable, option.InstanceName, option.ForceMerge);
await this.Disable(disable, option.InstanceName);
await this.Reenable(recreate, option.InstanceName, option.ForceMerge);
return(option.Tables);
//local functions
void log(string instance, string column, string change)
{
switch (change)
{
case "-":
Console.ForegroundColor = ConsoleColor.Red;
break;
case "+":
Console.ForegroundColor = ConsoleColor.Green;
break;
default:
Console.ForegroundColor = ConsoleColor.Yellow;
break;
}
;
Console.Write($"[{change}]");
Console.ForegroundColor = ConsoleColor.Gray;
Console.WriteLine($"{instance}--> {column}");
Console.ResetColor();
}
}
