public static async Task DeleteRecordAsync(IConnectionFactory connFactory, ReplicationTable table,
string primaryKeyValue)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
try
{
var cmd = connFactory.GetCommand(string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey).ColumnName),
primaryKeyValue
),
conn);
// check if table exists
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
finally
{
await conn.CloseAsync();
}
}
public static async Task DropTableAsync(IConnectionFactory connFactory, ReplicationTable table)
{
var conn = connFactory.GetConnection();
try
{
await conn.OpenAsync();
var cmd = connFactory.GetCommand(
string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"')
),
conn);
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
Logger.Error(e, e.Message);
}
finally
{
await conn.CloseAsync();
}
}
public static async Task DeleteRecordAsync(SqlDatabaseConnection conn, ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
//TODO: first find record line number and delete it from the replication file
var query = string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true)
.ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// check if table exists
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
// private static readonly string EnsureTableQuery = @"SELECT * FROM {0}.{1}";
public static async Task EnsureTableAsync(ISessionFactory sessionFactory, ReplicationTable table)
{
var session = sessionFactory.GetSession();
try
{
Logger.Info($"Creating Schema... {table.SchemaName}");
await session.Execute($"CREATE KEYSPACE IF NOT EXISTS {table.SchemaName} WITH REPLICATION = {{'class' : 'SimpleStrategy', 'replication_factor' : 1}};");
var rows = await session.Execute(string.Format(EnsureTableQuery, table.SchemaName, table.TableName));
Logger.Info($"Creating Table: {string.Format(EnsureTableQuery, table.SchemaName, table.TableName)}");
// check if table exists
var count = (long)rows.First()["count"];
if (count == 0)
{
// create table
var querySb = new StringBuilder($@"CREATE TABLE IF NOT EXISTS
{Utility.Utility.GetSafeName(table.SchemaName, '"')}.{Utility.Utility.GetSafeName(table.TableName, '"')}(");
var primaryKeySb = new StringBuilder("(");
var hasPrimaryKey = false;
//get all tables which are pk, put them together like a normal cassandra upload
var pkColumnList = new List <string> {
};
foreach (var column in table.Columns)
{
querySb.Append(
// $"{Utility.Utility.GetSafeName(column.ColumnName)} {column.DataType}{(column.PrimaryKey ? " PRIMARY KEY" : "")},");
$"{Utility.Utility.GetSafeName(column.ColumnName)} {column.DataType},");
if (column.PrimaryKey)
{
pkColumnList.Add(column.ColumnName);
// primaryKeySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
hasPrimaryKey = true;
}
}
if (pkColumnList.Count > 0)
{
querySb.Append($"PRIMARY KEY (\"{string.Join("\",\"", pkColumnList)}\"),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Info($"Creating Table: {query}");
await session.Execute(query);
}
}
finally
{
//noop
}
}
public static async Task DropTableAsync(ISessionFactory sessionFactory, ReplicationTable table)
{
var session = sessionFactory.GetSession();
var rows = await session.Execute(string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"')
));
}
public static async Task <Dictionary <string, object> > GetRecordAsync(IConnectionFactory connFactory,
ReplicationTable table,
string primaryKeyValue)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
try
{
var cmd = connFactory.GetCommand(string.Format(GetRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey).ColumnName),
primaryKeyValue
),
conn);
var reader = await cmd.ExecuteReaderAsync();
Dictionary <string, object> recordMap = null;
// check if record exists
if (reader.HasRows())
{
await reader.ReadAsync();
recordMap = new Dictionary <string, object>();
foreach (var column in table.Columns)
{
try
{
recordMap[column.ColumnName] = reader.GetValueById(column.ColumnName);
}
catch (Exception e)
{
Logger.Error(e, $"No column with column name: {column.ColumnName}");
Logger.Error(e, e.Message);
recordMap[column.ColumnName] = null;
}
}
}
return(recordMap);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
finally
{
await conn.CloseAsync();
}
}
// private static readonly string EnsureTableQuery = @"SELECT * FROM {0}.{1}";
public static async Task EnsureTableAsync(IConnectionFactory connFactory, ReplicationTable table)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
Logger.Info($"Creating Schema... {table.SchemaName}");
var cmd = connFactory.GetCommand($"CREATE SCHEMA IF NOT EXISTS {table.SchemaName}", conn);
await cmd.ExecuteNonQueryAsync();
// cmd = connFactory.GetCommand(string.Format(EnsureTableQuery, table.SchemaName, table.TableName), conn);
//
// Logger.Debug($"Creating Table: {string.Format(EnsureTableQuery, table.SchemaName, table.TableName)}");
// create table
var querySb = new StringBuilder($@"CREATE TABLE IF NOT EXISTS
{Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)}(");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in table.Columns)
{
querySb.Append(
$"{Utility.Utility.GetSafeName(column.ColumnName)} {column.DataType}{(column.PrimaryKey ? "" : "")},");
if (column.PrimaryKey)
{
primaryKeySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
var query = querySb.ToString();
Logger.Debug($"Creating Table: {query}");
await conn.OpenAsync();
cmd = connFactory.GetCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public static async Task DeleteRecordAsync(ISessionFactory sessionFactory, ReplicationTable table,
string primaryKeyValue)
{
var session = sessionFactory.GetSession();
await session.Execute(string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName, '"'),
primaryKeyValue));
}
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 ReplicatorSink(LightningPersistence lmdb, string targetReplicaId,
KvTable kvTable, ReplicationTable replicationTable,
ReplicationConfig replicationConfig, CancellationToken cancellationToken,
Action <WriteTransaction, string, ulong?> updateClock)
{
_lmdb = lmdb;
_targetReplicaId = targetReplicaId;
_kvTable = kvTable;
_replicationTable = replicationTable;
_replicationConfig = replicationConfig;
_cancellationToken = cancellationToken;
_updateClock = updateClock;
}
public ReplicatorSlave(LightningPersistence lmdb, string ownReplicaId,
KvTable kvTable, ReplicationTable replicationTable, WriteLogTable wlTable,
ReplicationConfig replicationConfig, Action <WriteTransaction, string, ulong?> updateClock) // TODO: Add ACKs streaming
{
_lmdb = lmdb;
_ownReplicaId = ownReplicaId;
_kvTable = kvTable;
_replicationTable = replicationTable;
_wlTable = wlTable;
_replicationConfig = replicationConfig;
_updateClock = updateClock;
_replicationSources = new ConcurrentDictionary <string, ReplicatorSource>();
_cts = new CancellationTokenSource();
}
public static async Task <Dictionary <string, object> > GetRecordAsync(SqlDatabaseConnection conn,
ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(GetRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = await cmd.ExecuteReaderAsync();
Dictionary <string, object> recordMap = null;
// check if record exists
if (reader.HasRows)
{
await reader.ReadAsync();
recordMap = new Dictionary <string, object>();
foreach (var column in table.Columns)
{
try
{
recordMap[column.ColumnName] = reader[column.ColumnName];
}
catch (Exception e)
{
Logger.Error(e, $"No column with column name: {column.ColumnName}");
Logger.Error(e, e.Message);
recordMap[column.ColumnName] = null;
}
}
}
await conn.CloseAsync();
return(recordMap);
}
public static async Task DropTableAsync(IConnectionFactory connFactory, ReplicationTable table)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
var cmd = connFactory.GetCommand(
string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName)
),
conn);
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public static async Task EnsureTableAsync(IClientFactory clientFactory, ReplicationTable table)
{
var client = clientFactory.GetClient();
var db = client.GetDefaultDatabase();
try
{
Logger.Info($"Creating Table... {table.SchemaName}.{table.TableName}");
string bq_query = string.Format(EnsureTableQuery, db,
Utility.Utility.GetSafeString(table.SchemaName, "\'", "\\\'"),
Utility.Utility.GetSafeString(table.TableName, "\'", "\\\'"));
await client.ExecuteReaderAsync(bq_query);
var results = await client.ExecuteReaderAsync(bq_query);
foreach (var row in results)
{
var bq_count = (long)row["c"];
if (bq_count == 0)
{
// create table
var querySb = new StringBuilder($@"CREATE TABLE IF NOT EXISTS
{Utility.Utility.GetSafeName(table.SchemaName, '`', true)}.{Utility.Utility.GetSafeName(table.TableName, '`', true)}(");
foreach (var column in table.Columns)
{
querySb.Append(
$"{Utility.Utility.GetSafeName(column.ColumnName, '`', true)} {column.DataType},");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Info($"Creating Table: {query}");
await client.ExecuteReaderAsync(query);
}
}
}
catch (Exception e)
{
throw;
}
finally
{
}
}
// private static readonly string EnsureTableQuery = @"SELECT * FROM {0}.{1}";
public static async Task EnsureTableAsync(SqlDatabaseConnection conn, ReplicationTable table)
{
// create table
var querySb = new StringBuilder($@"CREATE TABLE IF NOT EXISTS
{Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)}(");
var primaryKeySb = new StringBuilder("PRIMARY KEY (");
var hasPrimaryKey = false;
foreach (var column in table.Columns)
{
querySb.Append(
$"{Utility.Utility.GetSafeName(column.ColumnName)} {column.DataType}{(column.PrimaryKey ? " NOT NULL UNIQUE" : "")},");
if (column.PrimaryKey)
{
primaryKeySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
hasPrimaryKey = true;
}
}
if (hasPrimaryKey)
{
primaryKeySb.Length--;
primaryKeySb.Append(")");
querySb.Append($"{primaryKeySb});");
}
else
{
querySb.Length--;
querySb.Append(");");
}
await conn.OpenAsync();
var query = querySb.ToString();
Logger.Debug($"Creating Table: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
/// <summary>
/// Adds and removes records to replication db
/// Adds and updates available shapes
/// </summary>
/// <param name="connFactory"></param>
/// <param name="schema"></param>
/// <param name="record"></param>
/// <param name="config"></param>
/// <param name="responseStream"></param>
/// <returns>Error message string</returns>
public static async Task <string> WriteRecordAsync(IConnectionFactory connectionFactory, Schema schema, Record record, ConfigureReplicationFormData config, IServerStreamWriter <RecordAck> responseStream)
{
Logger.Debug($"Starting {record.RecordId}");
Stopwatch timer = Stopwatch.StartNew();
try
{
Logger.Debug(JsonConvert.SerializeObject(record, Formatting.Indented));
await WriteSemaphoreSlim.WaitAsync();
string safeSchemaName = config.SchemaName;
string safeGoldenTableName = config.GoldenTableName;
string safeVersionTableName = config.VersionTableName;
ReplicationTable goldenTable = GetGoldenReplicationTable(schema, safeSchemaName, safeGoldenTableName);
ReplicationTable versionTable = GetVersionReplicationTable(schema, safeSchemaName, safeVersionTableName);
List <string> recordVersionIds = record.Versions.Select(r => r.RecordId).ToList();
// GetNamed Record Data
// TODO: Finish
return(null);
}
catch (Exception ex)
{
Logger.Error(ex, $"Error writing record: {ex.Message}");
RecordAck ack = new RecordAck
{
CorrelationId = record.CorrelationId,
Error = ex.Message
};
await responseStream.WriteAsync(ack);
timer.Stop();
return(ex.Message);
}
finally
{
Logger.Debug($"Stopped {record.RecordId}. Time: {timer.ElapsedMilliseconds}");
WriteSemaphoreSlim.Release();
}
}
public static async Task DropTableAsync(IClientFactory clientFactory, ReplicationTable table)
{
var client = clientFactory.GetClient();
try
{
string query = string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`', true),
Utility.Utility.GetSafeName(table.TableName, '`', true)
);
client.ExecuteReaderAsync(query);
}
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 DeleteRecordAsync(IConnectionFactory connFactory, ReplicationTable table,
string primaryKeyValue)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
var cmd = connFactory.GetCommand(string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName, '"'),
primaryKeyValue
),
conn);
// check if table exists
await cmd.ExecuteNonQueryAsync();
await conn.CloseAsync();
}
public static async Task DeleteRecordAsync(IClientFactory clientFactory, ReplicationTable table,
string primaryKeyValue)
{
var client = clientFactory.GetClient();
var query = string.Format(DeleteRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`'),
Utility.Utility.GetSafeName(table.TableName, '`'),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName, '`', true),
primaryKeyValue
);
try
{
await client.ExecuteReaderAsync(query);
}
finally
{
//noop
}
}
public static async Task DropTableAsync(SqlDatabaseConnection conn, ReplicationTable table)
{
await conn.OpenAsync();
// TODO: delete file from disk
var query = string.Format(DropTableQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName)
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
cmd.ExecuteNonQuery();
await conn.CloseAsync();
}
public static async Task <Dictionary <string, object> > GetRecordAsync(ISessionFactory sessionFactory,
ReplicationTable table,
string primaryKeyValue)
{
var session = sessionFactory.GetSession();
var rows = await session.Execute(string.Format(GetRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName, '"'),
Utility.Utility.GetSafeName(table.TableName, '"'),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName, '"'),
primaryKeyValue));
Dictionary <string, object> recordMap = null;
// check if record exists
foreach (var row in rows)
{
recordMap = new Dictionary <string, object>();
foreach (var column in table.Columns)
{
try
{
recordMap[column.ColumnName] = row[column.ColumnName];
}
catch (Exception e)
{
Logger.Error(e, $"No column with column name: {column.ColumnName}");
Logger.Error(e, e.Message);
recordMap[column.ColumnName] = null;
}
}
}
return(recordMap);
}
public static ReplicationTable ConvertSchemaToReplicationTable(Schema schema, string schemaName,
string tableName)
{
var table = new ReplicationTable
{
SchemaName = schemaName,
TableName = tableName,
Columns = new List <ReplicationColumn>()
};
foreach (var property in schema.Properties)
{
var column = new ReplicationColumn
{
ColumnName = property.Name,
DataType = string.IsNullOrWhiteSpace(property.TypeAtSource)? GetType(property.Type): property.TypeAtSource,
PrimaryKey = false
};
table.Columns.Add(column);
}
return(table);
}
public static ReplicationTable ConvertSchemaToReplicationTable(Schema schema, string schemaName,
string tableName)
{
var table = new ReplicationTable
{
SchemaName = schemaName,
TableName = tableName,
Columns = new List <ReplicationColumn>()
};
foreach (var property in schema.Properties)
{
var column = new ReplicationColumn
{
ColumnName = property.Name,
DataType = $"VARCHAR({int.MaxValue})",
PrimaryKey = false
};
table.Columns.Add(column);
}
return(table);
}
public static async Task UpsertRecordAsync(IConnectionFactory connFactory,
ReplicationTable table,
Dictionary <string, object> recordMap)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
try
{
// try to insert
var querySb =
new StringBuilder(
$"INSERT INTO {Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)}(");
foreach (var column in table.Columns)
{
querySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in table.Columns)
{
if (recordMap.ContainsKey(column.ColumnName))
{
var rawValue = recordMap[column.ColumnName];
if (rawValue == null || string.IsNullOrWhiteSpace(rawValue.ToString()))
{
querySb.Append($"NULL,");
}
else
{
if (column.Serialize)
{
rawValue = JsonConvert.SerializeObject(rawValue);
}
querySb.Append($"'{Utility.Utility.GetSafeString(rawValue.ToString())}',");
}
}
else
{
querySb.Append($"NULL,");
}
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
var cmd = connFactory.GetCommand(query, conn);
await cmd.ExecuteNonQueryAsync();
// var primaryKey = table.Columns.Find(c => c.IsKey);
// var primaryValue = recordMap[primaryKey.ColumnName];
// if (await RecordExistsAsync(connFactory, table, primaryValue.ToString()))
// {
// // update if it failed
// var querySb =
// new StringBuilder(
// $"UPDATE {Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)} SET ");
// foreach (var column in table.Columns)
// {
// if (!column.PrimaryKey)
// {
// if (recordMap.ContainsKey(column.ColumnName))
// {
// var rawValue = recordMap[column.ColumnName];
// if (rawValue == null || string.IsNullOrWhiteSpace(rawValue.ToString()))
// {
// querySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)}=NULL,");
// }
// else
// {
// if (column.Serialize)
// {
// rawValue = JsonConvert.SerializeObject(rawValue);
// }
//
// querySb.Append(
// $"{Utility.Utility.GetSafeName(column.ColumnName)}='{Utility.Utility.GetSafeString(rawValue.ToString())}',");
// }
// }
// else
// {
// querySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)}=NULL,");
// }
// }
// }
//
// querySb.Length--;
//
// if (primaryKey.Serialize)
// {
// primaryValue = JsonConvert.SerializeObject(primaryValue);
// }
//
// querySb.Append($" WHERE {Utility.Utility.GetSafeName(primaryKey.ColumnName)}='{Utility.Utility.GetSafeString(primaryValue.ToString())}'");
//
// var query = querySb.ToString();
//
// Logger.Debug($"Update record query: {query}");
//
// var cmd = connFactory.GetCommand(query, conn);
//
// await cmd.ExecuteNonQueryAsync();
// }
// else
// {
// // try to insert
// var querySb =
// new StringBuilder(
// $"INSERT INTO {Utility.Utility.GetSafeName(table.SchemaName)}.{Utility.Utility.GetSafeName(table.TableName)}(");
// foreach (var column in table.Columns)
// {
// querySb.Append($"{Utility.Utility.GetSafeName(column.ColumnName)},");
// }
//
// querySb.Length--;
// querySb.Append(") VALUES (");
//
// foreach (var column in table.Columns)
// {
// if (recordMap.ContainsKey(column.ColumnName))
// {
// var rawValue = recordMap[column.ColumnName];
// if (rawValue == null || string.IsNullOrWhiteSpace(rawValue.ToString()))
// {
// querySb.Append($"NULL,");
// }
// else
// {
// if (column.Serialize)
// {
// rawValue = JsonConvert.SerializeObject(rawValue);
// }
//
// querySb.Append($"'{Utility.Utility.GetSafeString(rawValue.ToString())}',");
// }
// }
// else
// {
// querySb.Append($"NULL,");
// }
// }
//
// querySb.Length--;
// querySb.Append(");");
//
// var query = querySb.ToString();
//
// Logger.Debug($"Insert record query: {query}");
//
// var cmd = connFactory.GetCommand(query, conn);
//
// await cmd.ExecuteNonQueryAsync();
// }
}
catch (Exception e)
{
Logger.Error(e, $"Error: {e.Message}");
throw;
}
await conn.CloseAsync();
}
public static async Task CreateTableAsync(IConnectionFactory connFactory, ReplicationTable table)
{
}
public static async Task <bool> RecordExistsAsync(IClientFactory clientFactory, ReplicationTable table,
string primaryKeyValue)
{
var client = clientFactory.GetClient();
try
{
var query = string.Format(RecordExistsQuery,
Utility.Utility.GetSafeName(table.SchemaName, '`', true),
Utility.Utility.GetSafeName(table.TableName, '`', true),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName, '`', true),
primaryKeyValue
);
// check if record exists
var results = await client.ExecuteReaderAsync(query);
var count = (long)0;
foreach (var row in results)
{
count = (long)row["c"];
}
return(count != 0);
}
finally
{
//noop
}
}
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 <bool> RecordExistsAsync(IConnectionFactory connFactory, ReplicationTable table,
string primaryKeyValue)
{
var conn = connFactory.GetConnection();
await conn.OpenAsync();
try
{
var cmd = connFactory.GetCommand(string.Format(RecordExistsQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey).ColumnName),
primaryKeyValue
),
conn);
// check if record exists
var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var count = (decimal)reader.GetValueById("c");
return(count != 0);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
finally
{
await conn.CloseAsync();
}
}
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}");
}
