public async Task StoreEdgeData(ExecutionContext executionContext, Guid providerDefinitionId,
string containerName, string originEntityCode, string correlationId, DateTimeOffset timestamp,
VersionChangeType changeType, IEnumerable <string> edges)
{
try
{
var edgeTableName = $"{SqlStringSanitizer.Sanitize(containerName)}Edges".ToLowerInvariant();
if (await CheckTableExists(executionContext, providerDefinitionId, edgeTableName))
{
var sql = BuildEdgeStoreDataSql(edgeTableName, originEntityCode, correlationId, edges,
out var param);
if (!string.IsNullOrWhiteSpace(sql))
{
_logger.LogDebug($"Sql Server Connector - Store Edge Data - Generated query: {sql}");
var config = await base.GetAuthenticationDetails(executionContext, providerDefinitionId);
await _client.ExecuteCommandAsync(config, sql, param);
}
}
}
catch (Exception e)
{
var message =
$"Could not store edge data into Container '{containerName}' for Connector {providerDefinitionId}";
_logger.LogError(e, message);
throw new StoreDataException(message);
}
}
public virtual IEnumerable <PostgreSqlConnectorCommand> BuildCreateIndexSql(
string tableName,
IEnumerable <string> keys,
ILogger logger,
string indexName = null)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new InvalidOperationException("The Table Name must be provided.");
}
if (logger == null)
{
throw new ArgumentNullException(nameof(logger));
}
var builder = new StringBuilder();
// TODO: Better define INDEX. Proper approach would be to search if it exists, and check the fields. IDX can have more than 1 column to index.
if (string.IsNullOrWhiteSpace(indexName))
{
indexName = $"idx_{SqlStringSanitizer.Sanitize(tableName)}";
}
builder.AppendLine(
$"CREATE INDEX IF NOT EXISTS {indexName} ON {SqlStringSanitizer.Sanitize(tableName)}({string.Join(", ", keys)}); ");
return(new[] { new PostgreSqlConnectorCommand {
Text = builder.ToString()
} });
}
public Table(
string name,
IEnumerable <ConnectionDataType> columns,
IEnumerable <string> keys)
{
Name = SqlStringSanitizer.Sanitize(name);
Columns = new List <ConnectionDataType>(columns).AsReadOnly();
Keys = new List <string>(keys).AsReadOnly();
}
public async Task CreateSchemaAsync(IDictionary <string, object> config, string schemaName)
{
await using var connection = await _client.GetConnection(config);
var sqlQuery = $"CREATE SCHEMA IF NOT EXISTS \"{SqlStringSanitizer.Sanitize(schemaName)}\"";
var cmdDb = new NpgsqlCommand(sqlQuery, connection);
cmdDb.ExecuteNonQuery();
}
public string BuildEdgeStoreDataSql(string containerName, string originEntityCode, string correlationId,
IEnumerable <string> edges, out List <NpgsqlParameter> param)
{
var originParam = new NpgsqlParameter {
ParameterName = "OriginEntityCode", Value = originEntityCode
};
var correlationParam = new NpgsqlParameter {
ParameterName = "CorrelationId", Value = correlationId
};
param = new List <NpgsqlParameter> {
originParam, correlationParam
};
var builder = new StringBuilder();
if (StreamMode == StreamMode.Sync)
{
builder.AppendLine(
$"DELETE FROM {SqlStringSanitizer.Sanitize(containerName)} WHERE OriginEntityCode = @{originParam.ParameterName}; ");
}
var edgeValues = new List <string>();
foreach (var edge in edges)
{
var edgeParam = new NpgsqlParameter {
ParameterName = $"{edgeValues.Count}", Value = edge
};
param.Add(edgeParam);
edgeValues.Add(StreamMode == StreamMode.EventStream
? $"(@OriginEntityCode, @CorrelationId, @{edgeParam.ParameterName})"
: $"(@OriginEntityCode, @{edgeParam.ParameterName})");
}
if (edgeValues.Count <= 0)
{
return(builder.ToString());
}
builder.AppendLine(
StreamMode == StreamMode.EventStream
? $"INSERT INTO {SqlStringSanitizer.Sanitize(containerName)} (OriginEntityCode,CorrelationId,Code) VALUES"
: $"INSERT INTO {SqlStringSanitizer.Sanitize(containerName)} (OriginEntityCode,Code) VALUES");
builder.AppendJoin(", ", edgeValues);
builder.Append(";");
return(builder.ToString());
}
public Container(string containerName, StreamMode mode)
{
PrimaryTable = SqlStringSanitizer.Sanitize(containerName);
var columns = _codeEdgeColumns;
if (mode == StreamMode.EventStream)
{
columns = columns.Union(_eventStreamCodeEdgeColumns).ToArray();
}
Tables = new Dictionary <string, Table>
{
["Codes"] = new Table($"{PrimaryTable}Codes", columns, _codeEdgeColumns.Select(x => x.Name)),
["Edges"] = new Table($"{PrimaryTable}Edges", columns, _codeEdgeColumns.Select(x => x.Name))
};
}
public virtual IEnumerable <PostgreSqlConnectorCommand> BuildCreateContainerSql(
string tableName,
IEnumerable <ConnectionDataType> columns,
IEnumerable <string> keys,
string context,
StreamMode streamMode,
ILogger logger)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new InvalidOperationException("The tableName must be provided.");
}
if (columns == null)
{
throw new InvalidOperationException("The data to specify columns must be provided.");
}
var builder = new StringBuilder();
var trimmedColumns = columns.Where(x => x.Name != "Codes").ToList();
builder.AppendLine($"CREATE TABLE IF NOT EXISTS {SqlStringSanitizer.Sanitize(tableName)}(");
var index = 0;
var count = trimmedColumns.Count;
foreach (var column in trimmedColumns)
{
builder.AppendLine($"{SqlStringSanitizer.Sanitize(column.Name)} text NULL " +
// TODO: appoint PK to valid column for StreamMode Event
$"{(column.Name.ToLower().Equals("originentitycode") && context == "Data" && streamMode == StreamMode.Sync ? "PRIMARY KEY" : "")}" +
$"{(index < count - 1 ? "," : "")} ");
index++;
}
builder.AppendLine(");");
return(new[] { new PostgreSqlConnectorCommand {
Text = builder.ToString()
} });
}
protected virtual PostgreSqlConnectorCommand ComposeDelete(string tableName, IDictionary <string, object> fields)
{
var sqlBuilder = new StringBuilder($"DELETE FROM {SqlStringSanitizer.Sanitize(tableName)} WHERE ");
var clauses = new List <string>();
var parameters = new List <NpgsqlParameter>();
foreach (var entry in fields)
{
var key = SqlStringSanitizer.Sanitize(entry.Key);
clauses.Add($"{key} = @{key}");
parameters.Add(new NpgsqlParameter($"{key}", entry.Value));
}
sqlBuilder.AppendJoin(" AND ", clauses);
sqlBuilder.Append(";");
return(new PostgreSqlConnectorCommand {
Text = sqlBuilder.ToString(), Parameters = parameters
});
}
protected virtual PostgreSqlConnectorCommand ComposeInsert(string tableName, IDictionary <string, object> fields,
string context, StreamMode streamMode)
{
var columns = new List <string>();
var parameters = new List <NpgsqlParameter>();
var updateList = string.Join(",",
(from dataType in fields select $"{SqlStringSanitizer.Sanitize(dataType.Key)} = @{dataType.Key}").ToList());
foreach (var entry in fields)
{
columns.Add($"{SqlStringSanitizer.Sanitize(entry.Key)}");
parameters.Add(new NpgsqlParameter($"{SqlStringSanitizer.Sanitize(entry.Key)}", entry.Value));
}
var sqlBuilder = new StringBuilder($"INSERT INTO {SqlStringSanitizer.Sanitize(tableName)} (");
sqlBuilder.AppendJoin(",", columns);
sqlBuilder.Append(") VALUES (");
sqlBuilder.AppendJoin(",", parameters.Select(x => $"@{x.ParameterName}"));
sqlBuilder.Append(")");
if (context != "Data" || streamMode != StreamMode.Sync)
{
sqlBuilder.Append(";");
return(new PostgreSqlConnectorCommand {
Text = sqlBuilder.ToString(), Parameters = parameters
});
}
sqlBuilder.AppendLine(" ON CONFLICT(OriginEntityCode)");
sqlBuilder.AppendLine(" DO");
sqlBuilder.AppendLine($" UPDATE SET {updateList};");
return(new PostgreSqlConnectorCommand {
Text = sqlBuilder.ToString(), Parameters = parameters
});
}
private string BuildRemoveContainerSql(string tableName)
{
return($"DROP TABLE IF EXISTS {SqlStringSanitizer.Sanitize(tableName)};");
}
private string BuildRenameContainerSql(string oldTableName, string newTableName)
{
return($"ALTER TABLE IF EXISTS {SqlStringSanitizer.Sanitize(oldTableName)} RENAME TO {SqlStringSanitizer.Sanitize(newTableName)};");
}
