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