Skip to content

Commit 02c3c77

Browse files
ThatRendleThatRendle
committed
Added Column Lists, Count and Exists/Any to Queries
1 parent 0fa4368 commit 02c3c77

File tree

13 files changed

+291
-27
lines changed

13 files changed

+291
-27
lines changed

‎.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,4 +14,5 @@ TestResults/*
1414
*.dbmdl
1515
Releases/2*
1616
*.pidb
17+
Simple.Data.sln.docstates
1718
test-results

‎Simple.Data.Ado/Joiner.cs‎

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,17 @@ public string GetJoinClauses(ObjectName mainTableName, SimpleExpression expressi
3636
return string.Join(" ", tablePairs.Select(tp => _done[tp.Item2]));
3737
}
3838

39+
public string GetJoinClauses(ObjectName mainTableName, SimpleExpression expression, IEnumerable<DynamicReference> references)
40+
{
41+
_done.AddOrUpdate(mainTableName, string.Empty, (s, o) => string.Empty);
42+
var tablePairs = GetTableNames(expression, mainTableName.Schema).Concat(GetTableNames(references, mainTableName.Schema)).Distinct();
43+
foreach (var tablePair in tablePairs)
44+
{
45+
AddJoin(tablePair.Item1, tablePair.Item2);
46+
}
47+
return string.Join(" ", tablePairs.Select(tp => _done[tp.Item2]));
48+
}
49+
3950
private void AddJoin(ObjectName table1Name, ObjectName table2Name)
4051
{
4152
var table1 = _schema.FindTable(table1Name);
@@ -78,13 +89,17 @@ private string JoinKeyword
7889
get { return _joinType == JoinType.Inner ? string.Empty : "LEFT"; }
7990
}
8091

81-
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(SimpleExpression expression, string schema)
92+
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(IEnumerable<DynamicReference> references, string schema)
8293
{
83-
return GetReferencesFromExpression(expression)
84-
.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))
94+
return references.SelectMany(r => DynamicReferenceToTuplePairs(r, schema))
8595
.TupleSelect((table1, table2) => Tuple.Create(new ObjectName(schema, table1), new ObjectName(schema, table2)))
8696
.Distinct();
8797
}
98+
99+
private static IEnumerable<Tuple<ObjectName,ObjectName>> GetTableNames(SimpleExpression expression, string schema)
100+
{
101+
return expression == null ? Enumerable.Empty<Tuple<ObjectName, ObjectName>>() : GetTableNames(GetReferencesFromExpression(expression), schema);
102+
}
88103

89104
private static IEnumerable<Tuple<string,string>> DynamicReferenceToTuplePairs(DynamicReference reference, string schema)
90105
{

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 34 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ public ICommandBuilder Build(SimpleQuery query)
2626
{
2727
SetQueryContext(query);
2828

29+
HandleJoins();
2930
HandleQueryCriteria();
3031
HandleOrderBy();
3132
HandlePaging();
@@ -41,15 +42,21 @@ private void SetQueryContext(SimpleQuery query)
4142
_commandBuilder = new CommandBuilder(GetSelectClause(_tableName), _schema.SchemaProvider);
4243
}
4344

44-
private void HandleQueryCriteria()
45+
private void HandleJoins()
4546
{
46-
if (_query.Criteria == null) return;
47+
if (_query.Criteria == null
48+
&& (_query.Columns.Where(r => !(r is CountSpecialReference)).Count() == 0)) return;
4749

48-
var joins = new Joiner(JoinType.Inner, _schema).GetJoinClauses(_tableName, _query.Criteria);
50+
var joins = new Joiner(JoinType.Inner, _schema).GetJoinClauses(_tableName, _query.Criteria, _query.Columns.Where(r => !(r is CountSpecialReference)));
4951
if (!string.IsNullOrWhiteSpace(joins))
5052
{
5153
_commandBuilder.Append(" " + joins);
5254
}
55+
}
56+
57+
private void HandleQueryCriteria()
58+
{
59+
if (_query.Criteria == null) return;
5360
_commandBuilder.Append(" WHERE " + new ExpressionFormatter(_commandBuilder, _schema).Format(_query.Criteria));
5461
}
5562

@@ -88,8 +95,31 @@ private string GetSelectClause(ObjectName tableName)
8895
{
8996
var table = _schema.FindTable(tableName);
9097
return string.Format("select {0} from {1}",
91-
string.Join(",", table.Columns.Select(c => string.Format("{0}.{1}", table.QualifiedName, c.QuotedName))),
98+
GetColumnsClause(table),
9299
table.QualifiedName);
93100
}
101+
102+
private string GetColumnsClause(Table table)
103+
{
104+
return _query.Columns.Count() == 1 && _query.Columns.Single() is CountSpecialReference
105+
?
106+
"COUNT(*)"
107+
:
108+
string.Join(",", GetColumnsToSelect(table).Select(c => string.Format("{0}.{1}", c.Item1.QualifiedName, c.Item2.QuotedName)));
109+
}
110+
111+
private IEnumerable<Tuple<Table,Column>> GetColumnsToSelect(Table table)
112+
{
113+
if (_query.Columns.Any())
114+
{
115+
return from c in _query.Columns
116+
let t = _schema.FindTable(c.GetOwner().GetName())
117+
select Tuple.Create(t, t.FindColumn(c.GetName()));
118+
}
119+
else
120+
{
121+
return table.Columns.Select(c => Tuple.Create(table, c));
122+
}
123+
}
94124
}
95125
}

‎Simple.Data.BehaviourTest/QueryTest.cs‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
using NUnit.Framework;
6+
using Simple.Data.Mocking.Ado;
7+
8+
namespace Simple.Data.IntegrationTest
9+
{
10+
[TestFixture]
11+
public class QueryTest : DatabaseIntegrationContext
12+
{
13+
protected override void SetSchema(MockSchemaProvider schemaProvider)
14+
{
15+
schemaProvider.SetTables(new[] {"dbo", "Users", "BASE TABLE"},
16+
new[] {"dbo", "UserBio", "BASE TABLE"});
17+
18+
schemaProvider.SetColumns(new object[] {"dbo", "Users", "Id", true},
19+
new[] {"dbo", "Users", "Name"},
20+
new[] {"dbo", "Users", "Password"},
21+
new[] {"dbo", "Users", "Age"},
22+
new[] {"dbo", "UserBio", "UserId"},
23+
new[] {"dbo", "UserBio", "Text"});
24+
25+
schemaProvider.SetPrimaryKeys(new object[] { "dbo", "Users", "Id", 0 });
26+
schemaProvider.SetForeignKeys(new object[] { "FK_Users_UserBio", "dbo", "UserBio", "UserId", "dbo", "Users", "Id", 0 });
27+
}
28+
29+
[Test]
30+
public void SpecifyingColumnsShouldRestrictSelect()
31+
{
32+
_db.Users.All()
33+
.Select(_db.Users.Name, _db.Users.Password)
34+
.ToList();
35+
GeneratedSqlIs("select [dbo].[users].[name],[dbo].[users].[password] from [dbo].[users]");
36+
}
37+
38+
[Test]
39+
public void SpecifyingColumnsFromOtherTablesShouldAddJoin()
40+
{
41+
_db.Users.All()
42+
.Select(_db.Users.Name, _db.Users.Password, _db.Users.UserBio.Text)
43+
.ToList();
44+
GeneratedSqlIs(
45+
"select [dbo].[users].[name],[dbo].[users].[password],[dbo].[userbio].[text] from [dbo].[users]" +
46+
" join [dbo].[userbio] on ([dbo].[users].[id] = [dbo].[userbio].[userid])");
47+
}
48+
49+
[Test]
50+
public void SpecifyingCountShouldSelectCount()
51+
{
52+
try
53+
{
54+
_db.Users.All().Count();
55+
}
56+
catch (InvalidOperationException)
57+
{
58+
// This won't work on Mock provider, but the SQL should be generated OK
59+
}
60+
61+
GeneratedSqlIs("select count(*) from [dbo].[users]");
62+
}
63+
64+
[Test]
65+
public void SpecifyingExistsShouldSelectCount()
66+
{
67+
try
68+
{
69+
_db.Users.All().Exists();
70+
}
71+
catch (InvalidOperationException)
72+
{
73+
// This won't work on Mock provider, but the SQL should be generated OK
74+
}
75+
76+
GeneratedSqlIs("select count(*) from [dbo].[users]");
77+
}
78+
79+
[Test]
80+
public void SpecifyingAnyShouldSelectCount()
81+
{
82+
try
83+
{
84+
_db.Users.All().Any();
85+
}
86+
catch (InvalidOperationException)
87+
{
88+
// This won't work on Mock provider, but the SQL should be generated OK
89+
}
90+
91+
GeneratedSqlIs("select count(*) from [dbo].[users]");
92+
}
93+
}
94+
}

‎Simple.Data.BehaviourTest/Simple.Data.BehaviourTest.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@
6161
<Compile Include="ExceptionsTesting.cs" />
6262
<Compile Include="NaturalNamingTest.cs" />
6363
<Compile Include="ProcedureTest.cs" />
64+
<Compile Include="QueryTest.cs" />
6465
<Compile Include="RangeAndArrayFindTest.cs" />
6566
<Compile Include="TransactionTest.cs" />
6667
<Compile Include="DatabaseTest.cs" />

‎Simple.Data.SqlTest/QueryTest.cs‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,20 @@ namespace Simple.Data.SqlTest
99
[TestFixture]
1010
public class QueryTest
1111
{
12+
[Test]
13+
public void CountWithNoCriteriaShouldSelectThree()
14+
{
15+
var db = DatabaseHelper.Open();
16+
Assert.AreEqual(3, db.Users.Count());
17+
}
18+
19+
[Test]
20+
public void CountWithCriteriaShouldSelectTwo()
21+
{
22+
var db = DatabaseHelper.Open();
23+
Assert.AreEqual(2, db.Users.Count(db.Users.Age > 30));
24+
}
25+
1226
[Test]
1327
public void ShouldSelectFromOneToTen()
1428
{

‎Simple.Data.sln.docstates‎

2 KB
Binary file not shown.

‎Simple.Data/Commands/CommandFactory.cs‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
1-
using System.Collections.Generic;
1+
using System.Collections.Concurrent;
2+
using System.Collections.Generic;
23
using System.Linq;
34

45
namespace Simple.Data.Commands
@@ -19,11 +20,14 @@ class CommandFactory
1920
new DeleteByCommand(),
2021
new DeleteAllCommand(),
2122
new QueryByCommand(),
23+
new CountCommand(),
2224
};
2325

26+
private static readonly ConcurrentDictionary<string, ICommand> Cache = new ConcurrentDictionary<string, ICommand>();
27+
2428
public static ICommand GetCommandFor(string method)
2529
{
26-
return Commands.SingleOrDefault(command => command.IsCommandFor(method));
30+
return Cache.GetOrAdd(method, m => Commands.SingleOrDefault(command => command.IsCommandFor(method)));
2731
}
2832
}
2933
}

‎Simple.Data/Commands/CountCommand.cs‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Dynamic;
4+
using System.Linq;
5+
using System.Text;
6+
7+
namespace Simple.Data.Commands
8+
{
9+
class CountCommand : ICommand
10+
{
11+
/// <summary>
12+
/// Determines whether the instance is able to handle the specified method.
13+
/// </summary>
14+
/// <param name="method">The method name.</param>
15+
/// <returns>
16+
/// <c>true</c> if the instance is able to handle the specified method; otherwise, <c>false</c>.
17+
/// </returns>
18+
public bool IsCommandFor(string method)
19+
{
20+
return method.StartsWith("count", StringComparison.InvariantCultureIgnoreCase);
21+
}
22+
23+
/// <summary>
24+
/// Executes the command.
25+
/// </summary>
26+
/// <param name="dataStrategy">The data strategy.</param>
27+
/// <param name="table"></param>
28+
/// <param name="binder">The binder from the <see cref="DynamicTable"/> method invocation.</param>
29+
/// <param name="args">The arguments from the <see cref="DynamicTable"/> method invocation.</param>
30+
/// <returns></returns>
31+
public object Execute(DataStrategy dataStrategy, DynamicTable table, InvokeMemberBinder binder, object[] args)
32+
{
33+
if (args.Length == 1 && args[0] is SimpleExpression)
34+
{
35+
return new SimpleQuery(dataStrategy.GetAdapter(), table.GetQualifiedName()).Where((SimpleExpression)args[0]).Count();
36+
}
37+
38+
return new SimpleQuery(dataStrategy.GetAdapter(), table.GetQualifiedName()).Count();
39+
}
40+
41+
public Func<object[], object> CreateDelegate(DataStrategy dataStrategy, DynamicTable table, InvokeMemberBinder binder, object[] args)
42+
{
43+
throw new NotImplementedException();
44+
}
45+
}
46+
}

‎Simple.Data/CountSpecialReference.cs‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
6+
namespace Simple.Data
7+
{
8+
public class CountSpecialReference : DynamicReference
9+
{
10+
public CountSpecialReference() : base("COUNT")
11+
{
12+
13+
}
14+
}
15+
}

0 commit comments

Comments
 (0)