Skip to content

Commit 45865c6

Browse files
ThatRendleThatRendle
committed
Initial support for arbitrary functions in column references
1 parent 8ec4684 commit 45865c6

20 files changed

Lines changed: 227 additions & 25 deletions

‎Simple.Data.Ado/AdoAdapter.cs‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -149,6 +149,13 @@ public override IEnumerable<IEnumerable<IDictionary<string,object>>> RunQueries(
149149
}
150150
}
151151

152+
public override bool IsExpressionFunction(string functionName, params object[] args)
153+
{
154+
return functionName.Equals("like", StringComparison.OrdinalIgnoreCase)
155+
&& args.Length == 1
156+
&& args[0] is string;
157+
}
158+
152159
public override IObservable<IDictionary<string, object>> RunQueryAsObservable(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
153160
{
154161
var connection = _connectionProvider.CreateConnection();

‎Simple.Data.Ado/CommandBuilder.cs‎

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,10 +19,15 @@ class CommandBuilder : ICommandBuilder
1919

2020
public string Joins { get; set; }
2121

22-
public CommandBuilder(ISchemaProvider schemaProvider)
22+
public CommandBuilder(ISchemaProvider schemaProvider) : this(schemaProvider, -1)
23+
{
24+
}
25+
26+
public CommandBuilder(ISchemaProvider schemaProvider, int bulkIndex)
2327
{
2428
_text = new StringBuilder();
2529
_schemaProvider = schemaProvider;
30+
_parameterSuffix = (bulkIndex >= 0) ? "_c" + bulkIndex : string.Empty;
2631
}
2732

2833
public CommandBuilder(string text, ISchemaProvider schemaProvider, int bulkIndex)
@@ -32,6 +37,14 @@ public CommandBuilder(string text, ISchemaProvider schemaProvider, int bulkIndex
3237
_parameterSuffix = (bulkIndex >= 0) ? "_c" + bulkIndex : string.Empty;
3338
}
3439

40+
public ParameterTemplate AddParameter(object value)
41+
{
42+
string name = _schemaProvider.NameParameter("p" + Interlocked.Increment(ref _number) + _parameterSuffix);
43+
var parameterTemplate = new ParameterTemplate(name, value);
44+
_parameters.Add(parameterTemplate, value);
45+
return parameterTemplate;
46+
}
47+
3548
public ParameterTemplate AddParameter(object value, Column column)
3649
{
3750
string name = _schemaProvider.NameParameter("p" + Interlocked.Increment(ref _number) + _parameterSuffix);

‎Simple.Data.Ado/CommandTemplate.cs‎

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -41,11 +41,22 @@ public IDbCommand GetDbCommand(IDbConnection connection, IEnumerable<object> par
4141

4242
private IEnumerable<IDbDataParameter> CreateParameters(IDbCommand command, IEnumerable<object> parameterValues)
4343
{
44-
if (!parameterValues.Any(pv => pv != null)) return Enumerable.Empty<IDbDataParameter>();
44+
var fixedParameters = _parameters.Where(pt => pt.Type == ParameterType.FixedValue).ToArray();
45+
if ((!parameterValues.Any(pv => pv != null)) && fixedParameters.Length == 0) yield break;
4546

46-
return parameterValues.Any(o => o is IEnumerable && !(o is string)) || parameterValues.Any(o => o is IRange)
47-
? parameterValues.SelectMany((v,i) => CreateParameters(command, _parameters[i], v))
48-
: parameterValues.Select((v, i) => CreateParameter(command, _parameters[i], v));
47+
foreach (var fixedParameter in fixedParameters)
48+
{
49+
yield return CreateParameter(command, fixedParameter, fixedParameter.FixedValue);
50+
}
51+
52+
var columnParameters = _parameters.Where(pt => pt.Type != ParameterType.FixedValue).ToArray();
53+
54+
foreach (var parameter in parameterValues.Any(o => o is IEnumerable && !(o is string)) || parameterValues.Any(o => o is IRange)
55+
? parameterValues.SelectMany((v, i) => CreateParameters(command, columnParameters[i], v))
56+
: parameterValues.Select((v, i) => CreateParameter(command, columnParameters[i], v)))
57+
{
58+
yield return parameter;
59+
}
4960
}
5061

5162
private static IEnumerable<IDbDataParameter> CreateParameters(IDbCommand command, ParameterTemplate parameterTemplate, object value)

‎Simple.Data.Ado/ExpressionFormatter.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ public ExpressionFormatter(ICommandBuilder commandBuilder, DatabaseSchema schema
1515
{
1616
_commandBuilder = commandBuilder;
1717
_schema = schema;
18-
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema);
18+
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema, _commandBuilder);
1919
}
2020

2121
protected override string FormatObject(object value, object otherOperand)

‎Simple.Data.Ado/ICommandBuilder.cs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@ namespace Simple.Data.Ado
77

88
public interface ICommandBuilder
99
{
10+
ParameterTemplate AddParameter(object value);
1011
ParameterTemplate AddParameter(object value, Column column);
1112
void Append(string text);
1213
IDbCommand GetCommand(IDbConnection connection);

‎Simple.Data.Ado/ParameterTemplate.cs‎

Lines changed: 34 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,17 +7,30 @@ namespace Simple.Data.Ado
77

88
public class ParameterTemplate : IEquatable<ParameterTemplate>
99
{
10+
private readonly object _fixedValue;
1011
private readonly string _name;
1112
private readonly DbType _dbType;
1213
private readonly int _maxLength;
1314
private readonly Column _column;
15+
private readonly ParameterType _type;
16+
17+
public ParameterTemplate(string name, object fixedValue) : this(name, null)
18+
{
19+
_fixedValue = fixedValue;
20+
_type = ParameterType.FixedValue;
21+
}
1422

1523
public ParameterTemplate(string name, Column column)
1624
{
1725
if (name == null) throw new ArgumentNullException("name");
1826
_name = name;
1927
_column = column;
20-
if (column == null) return;
28+
if (column == null)
29+
{
30+
_type = ParameterType.Other;
31+
return;
32+
}
33+
_type = ParameterType.Column;
2134
_dbType = column.DbType;
2235
_maxLength = column.MaxLength;
2336
}
@@ -28,6 +41,12 @@ public ParameterTemplate(string name, DbType dbType, int maxLength)
2841
_name = name;
2942
_dbType = dbType;
3043
_maxLength = maxLength;
44+
_type = ParameterType.Other;
45+
}
46+
47+
internal ParameterType Type
48+
{
49+
get { return _type; }
3150
}
3251

3352
public int MaxLength
@@ -50,6 +69,13 @@ public Column Column
5069
get { return _column; }
5170
}
5271

72+
public object FixedValue
73+
{
74+
get {
75+
return _fixedValue;
76+
}
77+
}
78+
5379
public ParameterTemplate Rename(string newName)
5480
{
5581
return _column == null
@@ -85,4 +111,11 @@ public override string ToString()
85111
return Name;
86112
}
87113
}
114+
115+
enum ParameterType
116+
{
117+
Column,
118+
FixedValue,
119+
Other
120+
}
88121
}

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@ namespace Simple.Data.Ado
99
public class QueryBuilder
1010
{
1111
private readonly IFunctionNameConverter _functionNameConverter = new FunctionNameConverter();
12-
private readonly SimpleReferenceFormatter _simpleReferenceFormatter;
12+
private SimpleReferenceFormatter _simpleReferenceFormatter;
1313
private readonly AdoAdapter _adoAdapter;
1414
private readonly int _bulkIndex;
1515
private readonly DatabaseSchema _schema;
@@ -32,7 +32,8 @@ public QueryBuilder(AdoAdapter adoAdapter, int bulkIndex)
3232
_adoAdapter = adoAdapter;
3333
_bulkIndex = bulkIndex;
3434
_schema = _adoAdapter.GetSchema();
35-
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema);
35+
_commandBuilder = new CommandBuilder(_schema.SchemaProvider, _bulkIndex);
36+
_simpleReferenceFormatter = new SimpleReferenceFormatter(_schema, _commandBuilder);
3637
}
3738

3839
public ICommandBuilder Build(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
@@ -71,7 +72,7 @@ private void SetQueryContext(SimpleQuery query)
7172

7273
_tableName = ObjectName.Parse(query.TableName.Split('.').Last());
7374
_table = _schema.FindTable(_tableName);
74-
_commandBuilder = new CommandBuilder(GetSelectClause(_tableName), _schema.SchemaProvider, _bulkIndex);
75+
_commandBuilder.SetText(GetSelectClause(_tableName));
7576
}
7677

7778
private void HandleJoins()

‎Simple.Data.Ado/SimpleReferenceFormatter.cs‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,16 +1,20 @@
11
namespace Simple.Data.Ado
22
{
33
using System;
4+
using System.Collections.Generic;
5+
using System.Text;
46
using Schema;
57

68
class SimpleReferenceFormatter
79
{
810
private readonly IFunctionNameConverter _functionNameConverter = new FunctionNameConverter();
911
private readonly DatabaseSchema _schema;
12+
private readonly ICommandBuilder _commandBuilder;
1013

11-
public SimpleReferenceFormatter(DatabaseSchema schema)
14+
public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder)
1215
{
1316
_schema = schema;
17+
_commandBuilder = commandBuilder;
1418
}
1519

1620
public string FormatColumnClause(SimpleReference reference)
@@ -65,13 +69,25 @@ private string TryFormatAsFunctionReference(FunctionReference functionReference)
6569

6670
var sqlName = _functionNameConverter.ConvertToSqlName(functionReference.Name);
6771
return functionReference.Alias == null
68-
? string.Format("{0}({1})", sqlName,
69-
FormatColumnClause(functionReference.Argument))
72+
? string.Format("{0}({1}{2})", sqlName,
73+
FormatColumnClause(functionReference.Argument),
74+
FormatAdditionalArguments(functionReference.AdditionalArguments))
7075
: string.Format("{0}({1}) AS {2}", sqlName,
7176
FormatColumnClause(functionReference.Argument),
7277
_schema.QuoteObjectName(functionReference.Alias));
7378
}
7479

80+
private string FormatAdditionalArguments(IEnumerable<object> additionalArguments)
81+
{
82+
StringBuilder builder = null;
83+
foreach (var additionalArgument in additionalArguments)
84+
{
85+
if (builder == null) builder = new StringBuilder();
86+
builder.AppendFormat(",{0}", _commandBuilder.AddParameter(additionalArgument));
87+
}
88+
return builder != null ? builder.ToString() : string.Empty;
89+
}
90+
7591
private string TryFormatAsObjectReference(ObjectReference objectReference)
7692
{
7793
if (ReferenceEquals(objectReference, null)) return null;

‎Simple.Data.Ado/TypeHelper.cs‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,8 @@
33

44
namespace Simple.Data.Ado
55
{
6+
using System.Data;
7+
68
static class TypeHelper
79
{
810
private static readonly HashSet<Type> BaseTypes = new HashSet<Type>

‎Simple.Data.BehaviourTest/Query/FunctionTest.cs‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
6+
namespace Simple.Data.IntegrationTest.Query
7+
{
8+
using Mocking.Ado;
9+
using NUnit.Framework;
10+
11+
[TestFixture]
12+
public class FunctionTest : DatabaseIntegrationContext
13+
{
14+
protected override void SetSchema(MockSchemaProvider schemaProvider)
15+
{
16+
schemaProvider.SetTables(new[] { "dbo", "Users", "BASE TABLE" });
17+
18+
schemaProvider.SetColumns(new[] { "dbo", "Users", "Name" },
19+
new[] { "dbo", "Users", "Password" });
20+
}
21+
22+
[Test]
23+
public void SubstringIsEnteredCorrectlyInFindAll()
24+
{
25+
const string expected = @"select [dbo].[users].[name],[dbo].[users].[password] from [dbo].[users] where substring([dbo].[users].[name],@p1,@p2) = @p3";
26+
27+
EatException<InvalidOperationException>(() =>
28+
_db.Users.FindAll(_db.Users.Name.Substring(0, 1) == "A").ToList());
29+
30+
GeneratedSqlIs(expected);
31+
Parameter(0).Is(0);
32+
Parameter(1).Is(1);
33+
Parameter(2).Is("A");
34+
}
35+
36+
[Test]
37+
public void SubstringIsEnteredCorrectlyInFindOne()
38+
{
39+
const string expected = @"select [dbo].[users].* from [dbo].[users] where substring([dbo].[users].[name],@p1,@p2) = @p3";
40+
41+
_db.Users.Find(_db.Users.Name.Substring(0, 1) == "A");
42+
43+
GeneratedSqlIs(expected);
44+
Parameter(0).Is(0);
45+
Parameter(1).Is(1);
46+
Parameter(2).Is("A");
47+
}
48+
}
49+
}

0 commit comments

Comments
 (0)