public static async Task <ReplicationMetaData> GetPreviousReplicationMetaDataAsync(SqlDatabaseConnection conn,
string jobId,
ReplicationTable table)
{
try
{
ReplicationMetaData replicationMetaData = null;
// ensure replication metadata table
await EnsureTableAsync(conn, table);
// check if metadata exists
await conn.OpenAsync();
var query = string.Format(GetMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(Constants.ReplicationMetaDataJobId),
jobId);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
// metadata exists
var request = JsonConvert.DeserializeObject <PrepareWriteRequest>(
reader[Constants.ReplicationMetaDataRequest].ToString());
var shapeName = reader[Constants.ReplicationMetaDataReplicatedShapeName]
.ToString();
var shapeId = reader[Constants.ReplicationMetaDataReplicatedShapeId]
.ToString();
var timestamp = DateTime.Parse(reader[Constants.ReplicationMetaDataTimestamp]
.ToString());
replicationMetaData = new ReplicationMetaData
{
Request = request,
ReplicatedShapeName = shapeName,
ReplicatedShapeId = shapeId,
Timestamp = timestamp
};
}
await conn.CloseAsync();
return(replicationMetaData);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
}
public static async Task UpsertReplicationMetaDataAsync(IConnectionFactory connFactory, ReplicationTable table,
ReplicationMetaData metaData)
{
var conn = connFactory.GetConnection();
try
{
await conn.OpenAsync();
// try to insert
var cmd = connFactory.GetCommand(
string.Format(InsertMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
metaData.Request.DataVersions.JobId,
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
),
conn);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
try
{
// update if it failed
var cmd = connFactory.GetCommand(
string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp,
metaData.Request.DataVersions.JobId
),
conn);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception exception)
{
Logger.Error(e, $"Error Insert: {e.Message}");
Logger.Error(exception, $"Error Update: {exception.Message}");
throw;
}
finally
{
await conn.CloseAsync();
}
}
finally
{
await conn.CloseAsync();
}
}
public static async Task <ReplicationMetaData> GetPreviousReplicationMetaDataAsync(IConnectionFactory connFactory,
string jobId,
ReplicationTable table)
{
try
{
ReplicationMetaData replicationMetaData = null;
// ensure replication metadata table
await EnsureTableAsync(connFactory, table);
// check if metadata exists
var conn = connFactory.GetConnection();
await conn.OpenAsync();
var cmd = connFactory.GetCommand(
string.Format(GetMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
Utility.Utility.GetSafeName(Constants.ReplicationMetaDataJobId),
jobId),
conn);
var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows())
{
// metadata exists
await reader.ReadAsync();
var request = JsonConvert.DeserializeObject <PrepareWriteRequest>(
reader.GetValueById(Constants.ReplicationMetaDataRequest).ToString());
var shapeName = reader.GetValueById(Constants.ReplicationMetaDataReplicatedShapeName)
.ToString();
var shapeId = reader.GetValueById(Constants.ReplicationMetaDataReplicatedShapeId)
.ToString();
var timestamp = DateTime.Parse(reader.GetValueById(Constants.ReplicationMetaDataTimestamp)
.ToString());
replicationMetaData = new ReplicationMetaData
{
Request = request,
ReplicatedShapeName = shapeName,
ReplicatedShapeId = shapeId,
Timestamp = timestamp
};
}
await conn.CloseAsync();
return(replicationMetaData);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
}
public static async Task UpsertReplicationMetaDataAsync(IConnectionFactory connFactory, ReplicationTable table,
ReplicationMetaData metaData)
{
var conn = connFactory.GetConnection();
try
{
await conn.OpenAsync();
if (!await RecordExistsAsync(connFactory, table, metaData.Request.DataVersions.JobId))
{
// insert if not found
var cmd = connFactory.GetCommand(
string.Format(InsertMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
metaData.Request.DataVersions.JobId,
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
),
conn);
await cmd.ExecuteNonQueryAsync();
}
else
{
// update if found
var cmd = connFactory.GetCommand(
string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp,
metaData.Request.DataVersions.JobId
),
conn);
await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception e)
{
Logger.Error(e, $"Error Upsert Replication Metadata: {e.Message}");
throw;
}
finally
{
await conn.CloseAsync();
}
}
public static async Task <ReplicationMetaData> GetPreviousReplicationMetaDataAsync(
IClientFactory clientFactory,
string jobId,
ReplicationTable table)
{
var client = clientFactory.GetClient();
string query = string.Format(GetMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
Utility.Utility.GetSafeName(Constants.ReplicationMetaDataJobId),
jobId);
try
{
ReplicationMetaData replicationMetaData = null;
// ensure replication metadata table
await EnsureTableAsync(clientFactory, table);
// check if metadata exists
var bqReader = await client.ExecuteReaderAsync(query);
foreach (var row in bqReader)
{
var request = JsonConvert.DeserializeObject <PrepareWriteRequest>(
row[Constants.ReplicationMetaDataRequest].ToString());
var shapeName = row[Constants.ReplicationMetaDataReplicatedShapeName].ToString();
var shapeId = row[Constants.ReplicationMetaDataReplicatedShapeId].ToString();
var timestamp = DateTime.Parse(row[Constants.ReplicationMetaDataTimestamp].ToString());
replicationMetaData = new ReplicationMetaData
{
Request = request,
ReplicatedShapeName = shapeName,
ReplicatedShapeId = shapeId,
Timestamp = timestamp
};
}
return(replicationMetaData);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
finally
{
//noop
}
}
public static async Task <ReplicationMetaData> GetPreviousReplicationMetaDataAsync(
ISessionFactory sessionFactory,
string jobId,
ReplicationTable table)
{
try
{
ReplicationMetaData replicationMetaData = null;
// ensure replication metadata table
await EnsureTableAsync(sessionFactory, table);
// check if metadata exists
var session = sessionFactory.GetSession();
var rows = await session.Execute(string.Format(GetMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
Utility.Utility.GetSafeName(Constants.ReplicationMetaDataJobId, '"'),
jobId));
foreach (var row in rows)
{
var request = JsonConvert.DeserializeObject <PrepareWriteRequest>(row[Constants.ReplicationMetaDataRequest].ToString());
var shapeName = row[Constants.ReplicationMetaDataReplicatedShapeName].ToString();
var shapeId = row[Constants.ReplicationMetaDataReplicatedShapeId].ToString();
var timestamp = DateTime.Parse(row[Constants.ReplicationMetaDataTimestamp].ToString());
replicationMetaData = new ReplicationMetaData
{
Request = request,
ReplicatedShapeName = shapeName,
ReplicatedShapeId = shapeId,
Timestamp = timestamp
};
}
return(replicationMetaData);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
}
public static async Task UpsertReplicationMetaDataAsync(ISessionFactory sessionFactory, ReplicationTable table,
ReplicationMetaData metaData)
{
var session = sessionFactory.GetSession();
try
{
await session.Execute(string.Format(InsertMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
metaData.Request.DataVersions.JobId,
// JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
JsonConvert.SerializeObject(metaData.Request),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
));
}
catch (Exception e)
{
try
{
session.Execute(string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
JsonConvert.SerializeObject(metaData.Request),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp,
metaData.Request.DataVersions.JobId
));
// update if it failed
}
catch (Exception exception)
{
Logger.Error(e, $"Error Insert: {e.Message}");
Logger.Error(exception, $"Error Update: {exception.Message}");
throw;
}
}
}
public static async Task ReconcileReplicationJobAsync(IClientFactory clientFactory, PrepareWriteRequest request)
{
// get request settings
var replicationSettings =
JsonConvert.DeserializeObject <ConfigureReplicationFormData>(request.Replication.SettingsJson);
var safeSchemaName = clientFactory.GetClient().GetDefaultDatabase();
var safeGoldenTableName =
replicationSettings.GoldenTableName;
var safeVersionTableName =
replicationSettings.VersionTableName;
var metaDataTable = new ReplicationTable
{
SchemaName = safeSchemaName,
TableName = Constants.ReplicationMetaDataTableName,
Columns = Constants.ReplicationMetaDataColumns
};
var goldenTable = GetGoldenReplicationTable(request.Schema, safeSchemaName, safeGoldenTableName);
var versionTable = GetVersionReplicationTable(request.Schema, safeSchemaName, safeVersionTableName);
Logger.Info(
$"SchemaName: {safeSchemaName} Golden Table: {safeGoldenTableName} Version Table: {safeVersionTableName} job: {request.DataVersions.JobId}");
// get previous metadata
Logger.Info($"Getting previous metadata job: {request.DataVersions.JobId}");
var previousMetaData = await GetPreviousReplicationMetaDataAsync(clientFactory, request.DataVersions.JobId, metaDataTable);
Logger.Info($"Got previous metadata job: {request.DataVersions.JobId}");
// create current metadata
Logger.Info($"Generating current metadata job: {request.DataVersions.JobId}");
var metaData = new ReplicationMetaData
{
ReplicatedShapeId = request.Schema.Id,
ReplicatedShapeName = request.Schema.Name,
Timestamp = DateTime.Now,
Request = request
};
Logger.Info($"Generated current metadata job: {request.DataVersions.JobId}");
// check if changes are needed
if (previousMetaData == null)
{
Logger.Info($"No Previous metadata creating tables job: {request.DataVersions.JobId}");
await EnsureTableAsync(clientFactory, goldenTable);
await EnsureTableAsync(clientFactory, versionTable);
Logger.Info($"Created tables job: {request.DataVersions.JobId}");
}
else
{
var dropGoldenReason = "";
var dropVersionReason = "";
var previousReplicationSettings =
JsonConvert.DeserializeObject <ConfigureReplicationFormData>(previousMetaData.Request.Replication
.SettingsJson);
var previousGoldenTable = ConvertSchemaToReplicationTable(previousMetaData.Request.Schema, safeSchemaName, previousReplicationSettings.GoldenTableName);
var previousVersionTable = ConvertSchemaToReplicationTable(previousMetaData.Request.Schema, safeSchemaName, previousReplicationSettings.VersionTableName);
// check if golden table name changed
if (previousReplicationSettings.GoldenTableName != replicationSettings.GoldenTableName)
{
dropGoldenReason = GoldenNameChange;
}
// check if version table name changed
if (previousReplicationSettings.VersionTableName != replicationSettings.VersionTableName)
{
dropVersionReason = VersionNameChange;
}
// check if job data version changed
if (metaData.Request.DataVersions.JobDataVersion > previousMetaData.Request.DataVersions.JobDataVersion)
{
dropGoldenReason = JobDataVersionChange;
dropVersionReason = JobDataVersionChange;
}
// check if shape data version changed
if (metaData.Request.DataVersions.ShapeDataVersion >
previousMetaData.Request.DataVersions.ShapeDataVersion)
{
dropGoldenReason = ShapeDataVersionChange;
dropVersionReason = ShapeDataVersionChange;
}
// drop previous golden table
if (dropGoldenReason != "")
{
Logger.Info($"Dropping golden table: {dropGoldenReason}");
await DropTableAsync(clientFactory, previousGoldenTable);
await EnsureTableAsync(clientFactory, goldenTable);
}
// drop previous version table
if (dropVersionReason != "")
{
Logger.Info($"Dropping version table: {dropVersionReason}");
await DropTableAsync(clientFactory, previousVersionTable);
await EnsureTableAsync(clientFactory, versionTable);
}
}
// save new metadata
Logger.Info($"Updating metadata job: {request.DataVersions.JobId}");
await UpsertReplicationMetaDataAsync(clientFactory, metaDataTable, metaData);
Logger.Info($"Updated metadata job: {request.DataVersions.JobId}");
}
public static async Task UpsertReplicationMetaDataAsync(IClientFactory clientFactory, ReplicationTable table,
ReplicationMetaData metaData)
{
var client = clientFactory.GetClient();
try
{
if (!await RecordExistsAsync(clientFactory, table, metaData.Request.DataVersions.JobId))
{
var query = string.Format(InsertMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
metaData.Request.DataVersions.JobId,
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
);
await client.ExecuteReaderAsync(query);
}
else
{
// update if found
var query = string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
metaData.Request.DataVersions.JobId,
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
);
await client.ExecuteReaderAsync(query);
}
}
catch (Exception e)
{
try
{
// update if it failed
var query = string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
JsonConvert.SerializeObject(metaData.Request).Replace("\\", "\\\\"),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp,
metaData.Request.DataVersions.JobId
);
await client.ExecuteReaderAsync(query);
}
catch (Exception exception)
{
Logger.Error(e, $"Error Insert: {e.Message}");
Logger.Error(exception, $"Error Update: {exception.Message}");
throw;
}
finally
{
//noop
}
}
finally
{
//noop
}
}
public static async Task UpsertReplicationMetaDataAsync(IConnectionFactory connFactory, ReplicationTable table, ReplicationMetaData metaData)
{
return;
// var conn = connFactory.GetConnection();
// await conn.OpenAsync();
//
// try
// {
// if (await RecordExistsAsync(connFactory, table, metaData.Request.DataVersions.JobId))
// {
// // update if it failed
// var query = string.Format(UpdateMetaDataQuery,
// Utility.Utility.GetSafeName(table.SchemaName),
// Utility.Utility.GetSafeName(table.TableName),
// JsonConvert.SerializeObject(metaData.Request),
// metaData.ReplicatedShapeId,
// metaData.ReplicatedShapeName,
// metaData.Timestamp,
// metaData.Request.DataVersions.JobId
// );
// Logger.Debug(query);
// var cmd = connFactory.GetCommand(
// query,
// conn);
//
// await cmd.ExecuteNonQueryAsync();
// }
// else
// {
// // try to insert
// var query = string.Format(InsertMetaDataQuery,
// Utility.Utility.GetSafeName(table.SchemaName),
// Utility.Utility.GetSafeName(table.TableName),
// metaData.Request.DataVersions.JobId,
// JsonConvert.SerializeObject(metaData.Request),
// metaData.ReplicatedShapeId,
// metaData.ReplicatedShapeName,
// metaData.Timestamp
// );
// Logger.Debug(query);
// var cmd = connFactory.GetCommand(
// query,
// conn);
//
// await cmd.ExecuteNonQueryAsync();
// }
// }
// catch (Exception e)
// {
// Logger.Error($"Error: {e.Message}");
// throw;
// }
//
// await conn.CloseAsync();
}
public static async Task UpsertReplicationMetaDataAsync(SqlDatabaseConnection conn, ReplicationTable table, ReplicationMetaData metaData)
{
if (await RecordExistsAsync(conn, table, metaData.Request.DataVersions.JobId))
{
try
{
// update
await conn.OpenAsync();
var query = string.Format(UpdateMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
JsonConvert.SerializeObject(metaData.Request),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp,
metaData.Request.DataVersions.JobId
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Logger.Error(e, $"Error Update: {e.Message}");
throw;
}
finally
{
await conn.CloseAsync();
}
}
else
{
try
{
// insert
await conn.OpenAsync();
var query = string.Format(InsertMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
metaData.Request.DataVersions.JobId,
JsonConvert.SerializeObject(metaData.Request),
metaData.ReplicatedShapeId,
metaData.ReplicatedShapeName,
metaData.Timestamp
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Logger.Error(e, $"Error Insert: {e.Message}");
throw;
}
finally
{
await conn.CloseAsync();
}
}
}