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