public PreparedCommand PrepareDelete(DocumentMap mapping, Where where, CommandParameterValues parameters, DeleteOptions options = null)
{
options ??= DeleteOptions.Default;
var actualTableName = options.TableName ?? mapping.TableName;
var actualSchemaName = options.SchemaName ?? configuration.GetSchemaNameOrDefault(mapping);
if (!mapping.RelatedDocumentsMappings.Any())
{
return(new PreparedCommand($"DELETE FROM [{actualSchemaName}].[{actualTableName}]{options.Hint??""} {where.GenerateSql()}", parameters, RetriableOperation.Delete, mapping, options.CommandTimeout));
}
var statement = new StringBuilder();
statement.AppendLine("DECLARE @Ids as TABLE (Id nvarchar(400))");
statement.AppendLine();
statement.AppendLine("INSERT INTO @Ids");
statement.AppendLine($"SELECT [{mapping.IdColumn.ColumnName}]");
statement.AppendLine($"FROM [{actualSchemaName}].[{actualTableName}] WITH (ROWLOCK)");
statement.AppendLine(where.GenerateSql());
statement.AppendLine();
statement.AppendLine($"DELETE FROM [{actualSchemaName}].[{actualTableName}] WITH (ROWLOCK) WHERE [{mapping.IdColumn.ColumnName}] in (SELECT Id FROM @Ids)");
foreach (var relMap in mapping.RelatedDocumentsMappings.Select(m => (tableName: m.TableName, schema: configuration.GetSchemaNameOrDefault(m), idColumnName: m.IdColumnName)).Distinct())
{
statement.AppendLine($"DELETE FROM [{relMap.schema}].[{relMap.tableName}] WITH (ROWLOCK) WHERE [{relMap.idColumnName}] in (SELECT Id FROM @Ids)");
}
return(new PreparedCommand(statement.ToString(), parameters, RetriableOperation.Delete, mapping, options.CommandTimeout));
}
public override IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults)
{
if (clauses.Count == 0)
{
throw new InvalidOperationException("Must have at least one 'ON' clause per join");
}
intermediateJoins.Add(new Join(clauses.ToList(), joinSource, type));
clauses = new List <JoinClause>();
joinSource = source;
type = joinType;
var commandParameterValues = new CommandParameterValues(ParamValues, parameterValues);
var combinedParameters = new Parameters(Params, parameters);
var combinedParameterDefaults = new ParameterDefaults(ParamDefaults, parameterDefaults);
ParamValues.Clear();
ParamValues.AddRange(commandParameterValues);
Params.Clear();
Params.AddRange(combinedParameters);
ParamDefaults.Clear();
ParamDefaults.AddRange(combinedParameterDefaults);
return(this);
}
public void ShouldBulkInsert()
{
using var writer = Store.BeginWriteTransaction();
var idMetaData = new SqlMetaData("Id", SqlDbType.NVarChar, 50);
var nameMetadata = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
var referencesMetadata = new SqlMetaData("References", SqlDbType.NVarChar, 50);
var records = new List <SqlDataRecord>();
for (var i = 0; i < 100000; i++)
{
var record = new SqlDataRecord(idMetaData, nameMetadata, referencesMetadata);
record.SetString(0, "MyId-" + i);
record.SetString(1, "Name for " + i);
record.SetString(2, "Some-Other-Doc-" + i);
records.Add(record);
}
var parameters = new CommandParameterValues();
parameters.AddTable("bulkInsertData", new TableValuedParameter("TestSchema.SomeTableInsertData", records));
writer.ExecuteNonQuery("insert into TestSchema.SomeTable ([Id], [Name], [References]) select [Id], [Name], [References] from @bulkInsertData", parameters);
var count = writer.ExecuteScalar <int>("select count(*) from TestSchema.SomeTable");
count.Should().Be(100000);
writer.Commit();
}
void AppendRelatedDocumentStatementsForInsert(
StringBuilder sb,
CommandParameterValues parameters,
DocumentMap mapping,
IReadOnlyList <object> documents)
{
var relatedDocumentData = GetRelatedDocumentTableData(mapping, documents);
foreach (var data in relatedDocumentData.Where(g => g.Related.Length > 0))
{
var relatedVariablePrefix = $"{data.TableName.ToLower()}_";
sb.AppendLine($"INSERT INTO [{data.SchemaName}].[{data.TableName}] ([{data.IdColumnName}], [{data.IdTableColumnName}], [{data.RelatedDocumentIdColumnName}], [{data.RelatedDocumentTableColumnName}]) VALUES");
var related = data.Related;
for (var x = 0; x < related.Length; x++)
{
var parentIdVariable = related[x].parentIdVariable;
var relatedDocumentId = related[x].relatedDocumentId;
var relatedTableName = related[x].relatedTableName;
var relatedVariableName = relatedVariablePrefix + x;
parameters.Add(relatedVariableName, relatedDocumentId);
if (x > 0)
{
sb.Append(",");
}
sb.AppendLine($"(@{parentIdVariable}, '{mapping.TableName}', @{relatedVariableName}, '{relatedTableName}')");
}
}
}
string AppendRelatedDocumentStatementsForUpdate(
string statement,
CommandParameterValues parameters,
DocumentMap mapping,
object document)
{
var relatedDocumentData = GetRelatedDocumentTableData(mapping, new[] { document });
if (relatedDocumentData.Count == 0)
{
return(statement);
}
var sb = new StringBuilder();
sb.AppendLine(statement);
sb.AppendLine();
if (relatedDocumentData.Any(d => d.Related.Any()))
{
sb.AppendLine("DECLARE @references as TABLE (Reference nvarchar(400), ReferenceTable nvarchar(400))");
}
foreach (var data in relatedDocumentData)
{
if (data.Related.Any())
{
var relatedVariablePrefix = $"{data.TableName.ToLower()}_";
sb.AppendLine();
sb.AppendLine("DELETE FROM @references");
sb.AppendLine();
var valueBlocks = data.Related.Select((r, idx) => $"(@{relatedVariablePrefix}{idx}, '{r.relatedTableName}')");
sb.Append("INSERT INTO @references VALUES ");
sb.AppendLine(string.Join(", ", valueBlocks));
sb.AppendLine();
sb.AppendLine($"DELETE FROM [{data.SchemaName}].[{data.TableName}] WHERE [{data.IdColumnName}] = @{IdVariableName}");
sb.AppendLine($" AND [{data.RelatedDocumentIdColumnName}] not in (SELECT Reference FROM @references)");
sb.AppendLine();
sb.AppendLine($"INSERT INTO [{data.SchemaName}].[{data.TableName}] ([{data.IdColumnName}], [{data.IdTableColumnName}], [{data.RelatedDocumentIdColumnName}], [{data.RelatedDocumentTableColumnName}])");
sb.AppendLine($"SELECT @{IdVariableName}, '{mapping.TableName}', Reference, ReferenceTable FROM @references t");
sb.AppendLine($"WHERE NOT EXISTS (SELECT null FROM [{data.SchemaName}].[{data.TableName}] r WHERE r.[{data.IdColumnName}] = @{IdVariableName} AND r.[{data.RelatedDocumentIdColumnName}] = t.Reference )");
for (var x = 0; x < data.Related.Length; x++)
{
parameters.Add(relatedVariablePrefix + x, data.Related[x].relatedDocumentId);
}
}
else
{
sb.AppendLine($"DELETE FROM [{data.SchemaName}].[{data.TableName}] WHERE [{data.IdColumnName}] = @Id");
}
}
return(sb.ToString());
}
public PreparedCommand(string statement, CommandParameterValues parameterValues, RetriableOperation operation = RetriableOperation.None, DocumentMap mapping = null, TimeSpan?commandTimeout = null, CommandBehavior commandBehavior = CommandBehavior.Default)
{
Mapping = mapping;
Statement = statement;
ParameterValues = parameterValues;
Operation = operation;
CommandTimeout = commandTimeout;
CommandBehavior = commandBehavior;
}
public SubquerySourceBuilder(ISelect select,
IReadQueryExecutor readQueryExecutor,
ITableAliasGenerator tableAliasGenerator,
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
CommandParameterValues parameterValues,
Parameters parameters,
ParameterDefaults parameterDefaults)
: base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
{
this.select = select;
}
public VariableCasingFixture()
{
query = null;
parameters = null;
transaction = Substitute.For <IRelationalTransaction>();
transaction.WhenForAnyArgs(c => c.Stream <object>("", Arg.Any <CommandParameterValues>()))
.Do(c =>
{
query = c.Arg <string>();
parameters = c.Arg <CommandParameterValues>();
});
}
public IEnumerable <TResult> Stream <TResult>(string query, CommandParameterValues args, Func <IProjectionMapper, TResult> projectionMapper, TimeSpan?commandTimeout = null)
{
var command = new PreparedCommand(query, args, RetriableOperation.Select, commandBehavior: CommandBehavior.Default, commandTimeout: commandTimeout);
using var reader = ExecuteReader(command);
var mapper = new ProjectionMapper(command, reader, configuration.ReaderStrategies);
while (reader.Read())
{
yield return(projectionMapper(mapper));
}
}
public TableSourceQueryBuilder(string tableOrViewName,
string schemaName,
IReadTransaction readQueryExecutor,
ITableAliasGenerator tableAliasGenerator,
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
CommandParameterValues parameterValues,
Parameters parameters,
ParameterDefaults parameterDefaults)
: base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
{
this.schemaName = schemaName;
this.tableOrViewName = tableOrViewName;
}
IDeleteQueryBuilder <IId> CreateQueryBuilder()
{
return(new DeleteQueryBuilder <IId>(
new UniqueParameterNameGenerator(),
(_, q, p, __) =>
{
query = q.GenerateSql();
parameters = p;
},
Enumerable.Empty <IWhereClause>(),
new CommandParameterValues()
));
}
public async IAsyncEnumerable <TResult> StreamAsync <TResult>(string query, CommandParameterValues args, Func <IProjectionMapper, TResult> projectionMapper, TimeSpan?commandTimeout = null, [EnumeratorCancellation] CancellationToken cancellationToken = default)
{
var command = new PreparedCommand(query, args, RetriableOperation.Select, commandBehavior: CommandBehavior.Default, commandTimeout: commandTimeout);
await using var reader = await ExecuteReaderAsync(command, cancellationToken);
var mapper = new ProjectionMapper(command, reader, configuration.ReaderStrategies);
while (await reader.ReadAsync(cancellationToken))
{
yield return(projectionMapper(mapper));
}
}
public DeleteQueryBuilder(
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
DataModificationQueryBuilder queryBuilder,
IWriteQueryExecutor queryExecutor,
IEnumerable <IWhereClause> whereClauses = null,
CommandParameterValues parameterValues = null)
{
this.uniqueParameterNameGenerator = uniqueParameterNameGenerator;
this.queryBuilder = queryBuilder;
this.queryExecutor = queryExecutor;
this.whereClauses = whereClauses ?? new List <IWhereClause>();
this.parameterValues = parameterValues ?? new CommandParameterValues();
}
public void ShouldPadInParameters(int parameterCount, int expectedPaddingTo)
{
var parameters = new CommandParameterValues();
parameters.Add("ids", Enumerable.Range(0, parameterCount).Select(i => "A"));
var command = new SqlCommand($"SELECT * FROM [Table] WHERE [Id] IN @ids");
parameters.ContributeTo(command, new TypeHandlerRegistry());
var parameterListString = string.Join(", ", Enumerable.Range(0, expectedPaddingTo).Select(i => "@ids_" + (i + 1)));
command.CommandText.Should().Be($"SELECT * FROM [Table] WHERE [Id] IN ({parameterListString})");
}
protected SourceQueryBuilder(IReadQueryExecutor readQueryExecutor,
ITableAliasGenerator tableAliasGenerator,
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
CommandParameterValues parameterValues,
Parameters parameters,
ParameterDefaults parameterDefaults)
{
ReadQueryExecutor = readQueryExecutor;
TableAliasGenerator = tableAliasGenerator;
UniqueParameterNameGenerator = uniqueParameterNameGenerator;
ParamValues = parameterValues;
Params = parameters;
ParamDefaults = parameterDefaults;
}
public void ShouldGenerateDeleteWithUnaryWhereClause()
{
string actual = null;
CommandParameterValues values = null;
transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v));
CreateQueryBuilder <IDocument>("Orders")
.Where("Price", UnarySqlOperand.GreaterThan, 5)
.Delete();
actual.Should().Be(@"DELETE FROM dbo.[Orders]
WHERE ([Price] > @price_0)");
values["price_0"].Should().Be(5);
}
public void ShouldReplaceParametersAcrossLineBreaks()
{
var parameters = new CommandParameterValues();
parameters.Add("someId", new[] { "id1", "id2" });
parameters.Add("someIdentifier", "value");
var command = new SqlCommand($"SELECT * FROM [Table]{Environment.NewLine}WHERE [Id] IN @someId{Environment.NewLine}AND [OtherId] = @someIdentifier");
parameters.ContributeTo(command, new TypeHandlerRegistry());
command.CommandText.Should().Be($"SELECT * FROM [Table]{Environment.NewLine}WHERE [Id] IN (@someId_1, @someId_2){Environment.NewLine}AND [OtherId] = @someIdentifier");
command.Parameters["someId_1"].Value.Should().Be("id1");
command.Parameters["someId_2"].Value.Should().Be("id2");
command.Parameters["someIdentifier"].Value.Should().Be("value");
}
public void ShouldReplaceParametersWithExactMatchSpecialCharacters(string paramName)
{
var parameters = new CommandParameterValues();
parameters.Add("someId", new[] { "id1", "id2" });
parameters.Add(paramName, "value");
var command = new SqlCommand($"SELECT * FROM [Table] WHERE [Id] IN @someId AND [OtherId] = @{paramName}");
parameters.ContributeTo(command, new TypeHandlerRegistry());
command.CommandText.Should().Be($"SELECT * FROM [Table] WHERE [Id] IN (@someId_1, @someId_2) AND [OtherId] = @{paramName}");
command.Parameters["someId_1"].Value.Should().Be("id1");
command.Parameters["someId_2"].Value.Should().Be("id2");
command.Parameters[paramName].Value.Should().Be("value");
}
public void ShouldReplaceParametersWithExactMatchEndOfQuery()
{
var parameters = new CommandParameterValues();
parameters.Add("someId", "value");
parameters.Add("someIdentifier", new[] { "id1", "id2" });
var command = new SqlCommand("SELECT * FROM [Table] WHERE [Id] = @someId AND [OtherId] IN @someIdentifier");
parameters.ContributeTo(command, new TypeHandlerRegistry());
command.CommandText.Should().Be("SELECT * FROM [Table] WHERE [Id] = @someId AND [OtherId] IN (@someIdentifier_1, @someIdentifier_2)");
command.Parameters["someId"].Value.Should().Be("value");
command.Parameters["someIdentifier_1"].Value.Should().Be("id1");
command.Parameters["someIdentifier_2"].Value.Should().Be("id2");
}
public void ShouldGenerateDeleteWithBinaryWhereClause()
{
string actual = null;
CommandParameterValues values = null;
transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v));
CreateQueryBuilder <IDocument>("Orders")
.Where("Price", BinarySqlOperand.Between, 5, 10)
.Delete();
actual.Should().Be(@"DELETE FROM dbo.[Orders]
WHERE ([Price] BETWEEN @startvalue_0 AND @endvalue_1)");
values["startvalue_0"].Should().Be(5);
values["endvalue_1"].Should().Be(10);
}
public void ShouldUseMaxLengthsWhenAvailable()
{
var mapping = new Map();
var parameters = new CommandParameterValues();
parameters["FirstName"] = "Fred";
parameters["LastName"] = "Fred";
var command = new SqlCommand($"SELECT BLAH BLAH");
parameters.ContributeTo(command, new TypeHandlerRegistry(), ((IDocumentMap)mapping).Build());
command.Parameters["FirstName"].Size.Should().Be(128);
command.Parameters["LastName"].Size.Should().Be(256);
}
public QueryBuilder(TSelectBuilder selectBuilder,
IReadQueryExecutor readQueryExecutor,
ITableAliasGenerator tableAliasGenerator,
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
CommandParameterValues paramValues,
Parameters @params,
ParameterDefaults paramDefaults)
{
this.selectBuilder = selectBuilder;
this.readQueryExecutor = readQueryExecutor;
this.tableAliasGenerator = tableAliasGenerator;
this.uniqueParameterNameGenerator = uniqueParameterNameGenerator;
this.paramValues = paramValues;
this.@params = @params;
this.paramDefaults = paramDefaults;
}
public JoinSourceQueryBuilder(IAliasedSelectSource originalSource,
JoinType joinType,
IAliasedSelectSource nextJoin,
IReadQueryExecutor readQueryExecutor,
ITableAliasGenerator tableAliasGenerator,
IUniqueParameterNameGenerator uniqueParameterNameGenerator,
CommandParameterValues parameterValues,
Parameters parameters,
ParameterDefaults parameterDefaults)
: base(readQueryExecutor, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
{
this.originalSource = originalSource;
clauses = new List <JoinClause>();
joinSource = nextJoin;
type = joinType;
}
protected static (IQueryBuilder <Foo> builder, (Parameters parameters, CommandParameterValues paramValues)) NewQueryBuilder(IUniqueParameterNameGenerator uniqueParameterNameGenerator = null)
{
var parameters = new Parameters();
var captures = new CommandParameterValues();
var builder = new QueryBuilder <Foo, TableSelectBuilder>(
new TableSelectBuilder(new SimpleTableSource("Foo")),
Substitute.For <IRelationalTransaction>(),
new TableAliasGenerator(),
uniqueParameterNameGenerator ?? CreateSubstituteParameterNameGenerator(),
captures,
parameters,
new ParameterDefaults()
);
return(builder, (parameters, captures));
}
int GetNextMaxValue()
{
using (var transaction = store.BeginWriteTransaction(IsolationLevel.Serializable))
{
var parameters = new CommandParameterValues
{
{ "collectionName", collectionName },
{ "blockSize", blockSize }
};
parameters.CommandType = CommandType.StoredProcedure;
var result = transaction.ExecuteScalar <int>("GetNextKeyBlock", parameters);
transaction.Commit();
return(result);
}
}
public void ShouldGenerateDeleteWithArrayWhereClause()
{
string actual = null;
CommandParameterValues values = null;
transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v));
CreateQueryBuilder <IDocument>("Orders")
.Where("Price", ArraySqlOperand.In, new [] { 5, 10, 15 })
.Delete();
actual.Should().Be(@"DELETE FROM dbo.[Orders]
WHERE ([Price] IN (@price0_0, @price1_1, @price2_2))");
values["price0_0"].Should().Be("5");
values["price1_1"].Should().Be("10");
values["price2_2"].Should().Be("15");
}
CommandParameterValues GetDocumentParameters(Func <DocumentMap, string> allocateId, object customAssignedId, IReadOnlyList <object> documents, DocumentMap mapping)
{
if (documents.Count == 1)
{
return(GetDocumentParameters(allocateId, customAssignedId, CustomIdAssignmentBehavior.ThrowIfIdAlreadySetToDifferentValue, documents[0], mapping, ""));
}
var parameters = new CommandParameterValues();
for (var x = 0; x < documents.Count; x++)
{
var instanceParameters = GetDocumentParameters(allocateId, customAssignedId, CustomIdAssignmentBehavior.IgnoreCustomIdIfIdAlreadySet, documents[x], mapping, $"{x}__");
parameters.AddRange(instanceParameters);
}
return(parameters);
}
public DeleteQueryBuilderFixture()
{
query = null;
parameters = null;
mappings = Substitute.For <IDocumentMapRegistry>();
mappings.Resolve <object>().Returns(c => new EmptyMap());
mappings.Resolve(Arg.Any <Type>()).Returns(c => new EmptyMap());
queryExecutor = Substitute.For <IWriteQueryExecutor>();
queryExecutor.ExecuteNonQuery(Arg.Any <PreparedCommand>()).Returns(info =>
{
query = info.Arg <PreparedCommand>().Statement;
parameters = info.Arg <PreparedCommand>().ParameterValues;
return(1);
});
}
public void ShouldGenerateDeleteWithParameterisedArrayWhereClause()
{
string actual = null;
CommandParameterValues values = null;
transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v));
CreateQueryBuilder <IDocument>("Orders")
.WhereParameterised("Price", ArraySqlOperand.In, new [] { new Parameter("LowerPrice"), new Parameter("UpperPrice") })
.ParameterValues(new object[] { 5, 10 })
.Delete();
actual.Should().Be(@"DELETE FROM dbo.[Orders]
WHERE ([Price] IN (@lowerprice_0, @upperprice_1))");
values["lowerprice_0"].Should().Be(5);
values["upperprice_1"].Should().Be(10);
}
public void ShouldGenerateDeleteWithParameterisedBinaryWhereClause()
{
string actual = null;
CommandParameterValues values = null;
transaction.ExecuteRawDeleteQuery(Arg.Do <string>(s => actual = s), Arg.Do <CommandParameterValues>(v => values = v));
CreateQueryBuilder <IDocument>("Orders")
.WhereParameterised("Price", BinarySqlOperand.Between, new Parameter("LowerPrice"), new Parameter("UpperPrice"))
.ParameterValues(5, 10)
.Delete();
actual.Should().Be(@"DELETE FROM dbo.[Orders]
WHERE ([Price] BETWEEN @lowerprice_0 AND @upperprice_1)");
values["lowerprice_0"].Should().Be(5);
values["upperprice_1"].Should().Be(10);
}
