private void CreateIndex(ISagaData sagaData, AdoNetUnitOfWorkScope scope, IEnumerable <KeyValuePair <string, string> > propertiesToIndex)
{
var dialect = scope.Dialect;
var connection = scope.Connection;
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value ?? "",
PropertyNameParameter = string.Format("n{0}", i),
PropertyValueParameter = string.Format("v{0}", i)
})
.ToList();
// lastly, generate new index
using (var command = connection.CreateCommand())
{
// generate batch insert with SQL for each entry in the index
var inserts = parameters
.Select(a => string.Format(
@"insert into {0} ({1}, {2}, {3}, {4}) values ({5}, {6}, {7}, {8})",
dialect.QuoteForTableName(sagaIndexTableName),
dialect.QuoteForColumnName(SAGAINDEX_TYPE_COLUMN),
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN),
dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN),
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN),
dialect.EscapeParameter(SAGAINDEX_TYPE_COLUMN),
dialect.EscapeParameter(a.PropertyNameParameter),
dialect.EscapeParameter(a.PropertyValueParameter),
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN)
));
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.AddParameter(dialect.EscapeParameter(parameter.PropertyNameParameter), DbType.String, parameter.PropertyName);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValueParameter), DbType.String, parameter.PropertyValue);
}
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_TYPE_COLUMN), DbType.String, sagaTypeName);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
public AdoNetUnitOfWorkScope GetScope()
{
var result = new AdoNetUnitOfWorkScope(this, _factory.Dialect, Connection);
if (_autodispose) {
result.OnDispose +=
() =>
{
if (!_aborted) Commit();
this.Dispose();
};
}
return result;
}
private void DeclareIndex(ISagaData sagaData, AdoNetUnitOfWorkScope scope, IDictionary <string, object> propertiesToIndex)
{
var dialect = scope.Dialect;
if (dialect.SupportsOnConflictClause && dialect.SupportsReturningClause && dialect.SupportsTableExpressions)
{
DeclareIndexUsingTableExpressions(sagaData, scope, propertiesToIndex);
}
else if (dialect.SupportsOnConflictClause && dialect.SupportsReturningClause)
{
DeclareIndexUsingReturningClause(sagaData, scope, propertiesToIndex);
}
else
{
DeclareIndexUnoptimized(sagaData, scope, propertiesToIndex);
}
}
public AdoNetUnitOfWorkScope GetScope()
{
var result = new AdoNetUnitOfWorkScope(this, _factory.Dialect, Connection);
if (_autodispose)
{
result.OnDispose +=
() =>
{
if (!_aborted)
{
Commit();
}
this.Dispose();
};
}
return(result);
}
private void DeclareIndexUnoptimized(ISagaData sagaData, AdoNetUnitOfWorkScope scope, IDictionary <string, object> propertiesToIndex)
{
var connection = scope.Connection;
var dialect = scope.Dialect;
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var idxTbl = dialect.QuoteForTableName(sagaIndexTableName);
var idCol = dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN);
var keyCol = dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN);
var valueCol = dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN);
var valuesCol = dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN);
var idParam = dialect.EscapeParameter(SAGAINDEX_ID_COLUMN);
var existingKeys = Enumerable.Empty <string>();
// Let's fetch existing keys..
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"SELECT {1} FROM {0} WHERE {2} = {3};",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 1
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 2
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN) //< 3
);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
using (var reader = command.ExecuteReader())
{
existingKeys = reader.AsEnumerable <string>(SAGAINDEX_KEY_COLUMN).ToArray();
}
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
// For each exisring key, update it's value..
foreach (var key in existingKeys.Where(k => propertiesToIndex.Any(p => p.Key == k)))
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"UPDATE {0} SET {1} = {2}, {3} = {4} " +
"WHERE {5} = {6} AND {7} = {8};",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN), //< 1
dialect.EscapeParameter(SAGAINDEX_VALUE_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_VALUES_COLUMN), //< 3
dialect.EscapeParameter(SAGAINDEX_VALUES_COLUMN), //< 4
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 5
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), //< 6
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 7
dialect.EscapeParameter(SAGAINDEX_KEY_COLUMN) //< 8
);
var value = GetIndexValue(propertiesToIndex[key]);
var values = ArraysEnabledFor(dialect)
? (object)GetIndexValues(propertiesToIndex[key])?.ToArray()
: GetConcatenatedIndexValues(GetIndexValues(propertiesToIndex[key]));
var valuesDbType = ArraysEnabledFor(dialect) ? DbType.Object : DbType.String;
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_KEY_COLUMN), DbType.String, key);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_VALUE_COLUMN), DbType.String, value);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_VALUES_COLUMN), valuesDbType, values);
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
var removedKeys = existingKeys.Where(x => !propertiesToIndex.ContainsKey(x)).ToArray();
if (removedKeys.Length > 0)
{
// Remove no longer needed keys..
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"DELETE FROM {0} WHERE {1} = {2} AND {3} IN ({4});",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 1
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 3
string.Join(", ", existingKeys.Select((x, i) => dialect.EscapeParameter($"k{i}")))
);
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
for (int i = 0; i < existingKeys.Count(); i++)
{
command.AddParameter(dialect.EscapeParameter($"k{i}"), DbType.StringFixedLength, existingKeys.ElementAt(i).Trim());
}
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
var parameters = propertiesToIndex
.Where(x => !existingKeys.Contains(x.Key))
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value,
PropertyNameParameter = string.Format("n{0}", i),
PropertyValueParameter = string.Format("v{0}", i),
PropertyValuesParameter = string.Format("vs{0}", i)
})
.ToList();
if (parameters.Count > 0)
{
// Insert new keys..
using (var command = connection.CreateCommand())
{
var tuples = parameters.Select(p => string.Format("({0}, {1}, {2}, {3})",
idParam,
dialect.EscapeParameter(p.PropertyNameParameter),
dialect.EscapeParameter(p.PropertyValueParameter),
dialect.EscapeParameter(p.PropertyValuesParameter)
));
command.CommandText = string.Format(
"INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES {5};",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 1
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN), //< 3
dialect.QuoteForColumnName(SAGAINDEX_VALUES_COLUMN), //< 4
string.Join(", ", tuples) //< 5
);
foreach (var parameter in parameters)
{
var value = GetIndexValue(parameter.PropertyValue);
var values = ArraysEnabledFor(dialect)
? (object)GetIndexValues(parameter.PropertyValue)?.ToArray()
: GetConcatenatedIndexValues(GetIndexValues(parameter.PropertyValue));
var valuesDbType = ArraysEnabledFor(dialect) ? DbType.Object : DbType.String;
command.AddParameter(dialect.EscapeParameter(parameter.PropertyNameParameter), DbType.String, parameter.PropertyName);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValueParameter), DbType.String, value);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValuesParameter), valuesDbType, values);
}
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
}
private void DeclareIndexUsingReturningClause(ISagaData sagaData, AdoNetUnitOfWorkScope scope, IDictionary <string, object> propertiesToIndex)
{
var dialect = scope.Dialect;
var connection = scope.Connection;
var existingKeys = Enumerable.Empty <string>();
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value,
PropertyNameParameter = string.Format("n{0}", i),
PropertyValueParameter = string.Format("v{0}", i),
PropertyValuesParameter = string.Format("vs{0}", i)
})
.ToList();
var tuples = parameters
.Select(p => string.Format("({0}, {1}, {2}, {3})",
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN),
dialect.EscapeParameter(p.PropertyNameParameter),
dialect.EscapeParameter(p.PropertyValueParameter),
dialect.EscapeParameter(p.PropertyValuesParameter)
));
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES {5} " +
"ON CONFLICT ({1}, {2}) DO UPDATE SET {3} = excluded.{3}, {4} = excluded.{4} " +
"RETURNING {2};",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 1
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN), //< 3
dialect.QuoteForColumnName(SAGAINDEX_VALUES_COLUMN), //< 4
string.Join(", ", tuples) //< 5
);
foreach (var parameter in parameters)
{
var value = GetIndexValue(parameter.PropertyValue);
var values = value == null ? null : ArraysEnabledFor(dialect)
? (object)GetIndexValues(parameter.PropertyValue)?.ToArray()
: GetConcatenatedIndexValues(GetIndexValues(parameter.PropertyValue));
var valuesDbType = ArraysEnabledFor(dialect) ? DbType.Object : DbType.String;
command.AddParameter(dialect.EscapeParameter(parameter.PropertyNameParameter), DbType.String, parameter.PropertyName);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValueParameter), DbType.String, value);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValuesParameter), valuesDbType, values);
}
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
using (var reader = command.ExecuteReader())
{
existingKeys = reader.AsEnumerable <string>(SAGAINDEX_KEY_COLUMN).ToArray();
}
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
var idx = 0;
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"DELETE FROM {0} " +
"WHERE {1} = {2} AND {3} NOT IN ({4});",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 1
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 3
string.Join(", ", existingKeys.Select(k => dialect.EscapeParameter($"k{idx++}")))
);
for (int i = 0; i < existingKeys.Count(); i++)
{
command.AddParameter(dialect.EscapeParameter($"k{i}"), DbType.StringFixedLength, existingKeys.ElementAt(i).Trim());
}
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
private void DeclareIndexUsingTableExpressions(ISagaData sagaData, AdoNetUnitOfWorkScope scope, IDictionary <string, object> propertiesToIndex)
{
var dialect = scope.Dialect;
var connection = scope.Connection;
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value,
PropertyNameParameter = string.Format("n{0}", i),
PropertyValueParameter = string.Format("v{0}", i),
PropertyValuesParameter = string.Format("vs{0}", i)
})
.ToList();
var tuples = parameters
.Select(p => string.Format("({0}, {1}, {2}, {3})",
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN),
dialect.EscapeParameter(p.PropertyNameParameter),
dialect.EscapeParameter(p.PropertyValueParameter),
dialect.EscapeParameter(p.PropertyValuesParameter)
));
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(
"WITH existing AS (" +
"INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES {6} " +
"ON CONFLICT ({1}, {2}) DO UPDATE SET {3} = excluded.{3}, {4} = excluded.{4} " +
"RETURNING {2}) " +
"DELETE FROM {0} " +
"WHERE {1} = {5} AND {2} NOT IN (SELECT {2} FROM existing);",
dialect.QuoteForTableName(sagaIndexTableName), //< 0
dialect.QuoteForColumnName(SAGAINDEX_ID_COLUMN), //< 1
dialect.QuoteForColumnName(SAGAINDEX_KEY_COLUMN), //< 2
dialect.QuoteForColumnName(SAGAINDEX_VALUE_COLUMN), //< 3
dialect.QuoteForColumnName(SAGAINDEX_VALUES_COLUMN), //< 4
dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), //< 5
string.Join(", ", tuples) //< 6
);
foreach (var parameter in parameters)
{
var value = GetIndexValue(parameter.PropertyValue);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyNameParameter), DbType.String, parameter.PropertyName);
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValueParameter), DbType.String, value);
var values = ArraysEnabledFor(dialect)
? (object)GetIndexValues(parameter.PropertyValue)?.ToArray()
: GetConcatenatedIndexValues(GetIndexValues(parameter.PropertyValue));
var dbtype = ArraysEnabledFor(dialect) ? DbType.Object : DbType.String;
command.AddParameter(dialect.EscapeParameter(parameter.PropertyValuesParameter), dbtype, values);
}
command.AddParameter(dialect.EscapeParameter(SAGAINDEX_ID_COLUMN), DbType.Guid, sagaData.Id);
try
{
command.ExecuteNonQuery();
}
catch (DbException exception)
{
throw new OptimisticLockingException(sagaData, exception);
}
}
}
