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();
}
public static async Task <bool> RecordExistsAsync(SqlDatabaseConnection conn, ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(RecordExistsQuery,
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
};
// check if record exists
var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var count = (long)reader["c"];
await conn.CloseAsync();
return(count != 0);
}
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 <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);
}
// 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();
}
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 UpsertRecordAsync(SqlDatabaseConnection conn,
ReplicationTable table,
Dictionary <string, object> recordMap)
{
var primaryKey = table.Columns.Find(c => c.PrimaryKey);
var primaryValue = recordMap[primaryKey.ColumnName];
if (await RecordExistsAsync(conn, table, primaryValue.ToString()))
{
// update data
try
{
Logger.Debug($"Updating record: {primaryValue}");
await conn.OpenAsync();
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 = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
await conn.CloseAsync();
Logger.Error(e, $"Error Update: {e.Message}");
throw;
}
finally
{
await conn.CloseAsync();
}
}
else
{
// insert data
try
{
Logger.Debug($"Inserting record: {primaryValue}");
await conn.OpenAsync();
// 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 = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
await cmd.ExecuteNonQueryAsync();
}
catch (Exception e)
{
await conn.CloseAsync();
Logger.Error(e, $"Error Insert: {e.Message}");
throw;
}
finally
{
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();
}
}
}
