Skip to content

Commit 54581ae

Browse files
ThatRendleThatRendle
committed
Implemented proper, join-aware paging in SQL Server
1 parent 5bcbf2e commit 54581ae

11 files changed

Lines changed: 216 additions & 172 deletions

File tree

‎Simple.Data.Ado.Test/ProviderHelperTest.cs‎

Lines changed: 106 additions & 106 deletions
Original file line numberDiff line numberDiff line change
@@ -1,118 +1,118 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.Linq;
4-
using System.Text;
5-
using NUnit.Framework;
6-
using System.Data;
7-
using Simple.Data.Ado.Schema;
8-
using System.ComponentModel.Composition;
9-
10-
namespace Simple.Data.Ado.Test
11-
{
12-
[TestFixture]
13-
public class ProviderHelperTest
14-
{
15-
[Test]
16-
public void ShouldNotRequestExportableTypeFromServiceProvider()
17-
{
18-
var helper = new ProviderHelper();
19-
var connectionProvider = new StubConnectionAndServiceProvider();
20-
var actual = helper.GetCustomProvider<ITestInterface>(connectionProvider);
21-
Assert.IsNull(connectionProvider.RequestedServiceType);
22-
}
23-
24-
[Test]
25-
public void ShouldRequestNonExportedTypeFromServiceProvider()
26-
{
27-
var helper = new ProviderHelper();
28-
var connectionProvider = new StubConnectionAndServiceProvider();
29-
var actual = helper.GetCustomProvider<IQueryPager>(connectionProvider);
30-
Assert.AreEqual(typeof(IQueryPager), connectionProvider.RequestedServiceType);
31-
}
32-
33-
[Test]
34-
public void ShouldReturnNonExportedTypeFromServiceProvider()
35-
{
36-
var helper = new ProviderHelper();
37-
var connectionProvider = new StubConnectionAndServiceProvider();
38-
var actual = helper.GetCustomProvider<IQueryPager>(connectionProvider);
39-
Assert.IsInstanceOf(typeof(IQueryPager), actual);
40-
}
41-
42-
[Test]
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
using NUnit.Framework;
6+
using System.Data;
7+
using Simple.Data.Ado.Schema;
8+
using System.ComponentModel.Composition;
9+
10+
namespace Simple.Data.Ado.Test
11+
{
12+
[TestFixture]
13+
public class ProviderHelperTest
14+
{
15+
[Test]
16+
public void ShouldNotRequestExportableTypeFromServiceProvider()
17+
{
18+
var helper = new ProviderHelper();
19+
var connectionProvider = new StubConnectionAndServiceProvider();
20+
var actual = helper.GetCustomProvider<ITestInterface>(connectionProvider);
21+
Assert.IsNull(connectionProvider.RequestedServiceType);
22+
}
23+
24+
[Test]
25+
public void ShouldRequestNonExportedTypeFromServiceProvider()
26+
{
27+
var helper = new ProviderHelper();
28+
var connectionProvider = new StubConnectionAndServiceProvider();
29+
var actual = helper.GetCustomProvider<IQueryPager>(connectionProvider);
30+
Assert.AreEqual(typeof(IQueryPager), connectionProvider.RequestedServiceType);
31+
}
32+
33+
[Test]
34+
public void ShouldReturnNonExportedTypeFromServiceProvider()
35+
{
36+
var helper = new ProviderHelper();
37+
var connectionProvider = new StubConnectionAndServiceProvider();
38+
var actual = helper.GetCustomProvider<IQueryPager>(connectionProvider);
39+
Assert.IsInstanceOf(typeof(IQueryPager), actual);
40+
}
41+
42+
[Test]
4343
public void ShouldFindProviderUsingAssemblyAttribute()
4444
{
4545
IConnectionProvider provider;
4646
Assert.True(ProviderHelper.TryLoadAssemblyUsingAttribute("Test", null, out provider));
4747
Assert.IsNotNull(provider);
4848
Assert.IsInstanceOf<StubConnectionProvider>(provider);
49-
}
50-
51-
public class StubConnectionAndServiceProvider : IConnectionProvider, IServiceProvider
52-
{
53-
public void SetConnectionString(string connectionString)
54-
{
55-
throw new NotImplementedException();
56-
}
57-
58-
public IDbConnection CreateConnection()
59-
{
60-
throw new NotImplementedException();
61-
}
62-
63-
public ISchemaProvider GetSchemaProvider()
64-
{
65-
throw new NotImplementedException();
66-
}
67-
68-
public string ConnectionString
69-
{
70-
get { throw new NotImplementedException(); }
71-
}
72-
73-
public bool SupportsCompoundStatements
74-
{
75-
get { throw new NotImplementedException(); }
76-
}
77-
78-
public string GetIdentityFunction()
79-
{
80-
throw new NotImplementedException();
81-
}
82-
83-
public bool SupportsStoredProcedures
84-
{
85-
get { throw new NotImplementedException(); }
86-
}
87-
88-
public IProcedureExecutor GetProcedureExecutor(AdoAdapter adapter, ObjectName procedureName)
89-
{
90-
throw new NotImplementedException();
91-
}
92-
93-
public Type RequestedServiceType { get; private set; }
94-
public Object GetService(Type serviceType)
95-
{
96-
this.RequestedServiceType = serviceType;
97-
return new StubQueryPager();
98-
}
99-
}
100-
101-
public class StubQueryPager : IQueryPager
49+
}
50+
51+
public class StubConnectionAndServiceProvider : IConnectionProvider, IServiceProvider
52+
{
53+
public void SetConnectionString(string connectionString)
54+
{
55+
throw new NotImplementedException();
56+
}
57+
58+
public IDbConnection CreateConnection()
59+
{
60+
throw new NotImplementedException();
61+
}
62+
63+
public ISchemaProvider GetSchemaProvider()
64+
{
65+
throw new NotImplementedException();
66+
}
67+
68+
public string ConnectionString
69+
{
70+
get { throw new NotImplementedException(); }
71+
}
72+
73+
public bool SupportsCompoundStatements
74+
{
75+
get { throw new NotImplementedException(); }
76+
}
77+
78+
public string GetIdentityFunction()
79+
{
80+
throw new NotImplementedException();
81+
}
82+
83+
public bool SupportsStoredProcedures
84+
{
85+
get { throw new NotImplementedException(); }
86+
}
87+
88+
public IProcedureExecutor GetProcedureExecutor(AdoAdapter adapter, ObjectName procedureName)
89+
{
90+
throw new NotImplementedException();
91+
}
92+
93+
public Type RequestedServiceType { get; private set; }
94+
public Object GetService(Type serviceType)
95+
{
96+
this.RequestedServiceType = serviceType;
97+
return new StubQueryPager();
98+
}
99+
}
100+
101+
public class StubQueryPager : IQueryPager
102102
{
103103
public IEnumerable<string> ApplyLimit(string sql, int take)
104104
{
105105
throw new NotImplementedException();
106106
}
107107

108-
public IEnumerable<string> ApplyPaging(string sql, int skip, int take)
109-
{
110-
throw new NotImplementedException();
111-
}
112-
}
113-
114-
public interface ITestInterface { }
115-
[Export(typeof(ITestInterface))]
116-
public class TestClass : ITestInterface { }
117-
}
118-
}
108+
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
109+
{
110+
throw new NotImplementedException();
111+
}
112+
}
113+
114+
public interface ITestInterface { }
115+
[Export(typeof(ITestInterface))]
116+
public class TestClass : ITestInterface { }
117+
}
118+
}

‎Simple.Data.Ado/AdoAdapterQueryRunner.cs‎

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -151,7 +151,7 @@ private ICommandBuilder[] GetPagedQueryCommandBuilders(ref SimpleQuery query, In
151151
}
152152
else
153153
{
154-
ApplyPaging(commandBuilders, mainCommandBuilder, skipClause, takeClause, query.Clauses.OfType<WithClause>().Any(), queryPager);
154+
ApplyPaging(query, commandBuilders, mainCommandBuilder, skipClause, takeClause, query.Clauses.OfType<WithClause>().Any(), queryPager);
155155
}
156156
}
157157
return commandBuilders.ToArray();
@@ -168,7 +168,7 @@ private void DeferPaging(ref SimpleQuery query, ICommandBuilder mainCommandBuild
168168
commandBuilders.Add(commandBuilder);
169169
}
170170

171-
private void ApplyPaging(List<ICommandBuilder> commandBuilders, ICommandBuilder mainCommandBuilder, SkipClause skipClause, TakeClause takeClause, bool hasWithClause, IQueryPager queryPager)
171+
private void ApplyPaging(SimpleQuery query, List<ICommandBuilder> commandBuilders, ICommandBuilder mainCommandBuilder, SkipClause skipClause, TakeClause takeClause, bool hasWithClause, IQueryPager queryPager)
172172
{
173173
const int maxInt = 2147483646;
174174

@@ -179,9 +179,17 @@ private void ApplyPaging(List<ICommandBuilder> commandBuilders, ICommandBuilder
179179
}
180180
else
181181
{
182+
var table = _adapter.GetSchema().FindTable(query.TableName);
183+
if (table.PrimaryKey == null || table.PrimaryKey.Length == 0)
184+
{
185+
throw new AdoAdapterException("Cannot apply paging to a table with no primary key.");
186+
}
187+
var keys = table.PrimaryKey.AsEnumerable()
188+
.Select(k => string.Format("{0}.{1}", table.QualifiedName, _adapter.GetSchema().QuoteObjectName(k)))
189+
.ToArray();
182190
int skip = skipClause == null ? 0 : skipClause.Count;
183191
int take = takeClause == null ? maxInt : takeClause.Count;
184-
commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, skip, take);
192+
commandTexts = queryPager.ApplyPaging(mainCommandBuilder.Text, keys, skip, take);
185193
}
186194

187195
commandBuilders.AddRange(

‎Simple.Data.Ado/IQueryPager.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,6 @@ namespace Simple.Data.Ado
55
public interface IQueryPager
66
{
77
IEnumerable<string> ApplyLimit(string sql, int take);
8-
IEnumerable<string> ApplyPaging(string sql, int skip, int take);
8+
IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take);
99
}
1010
}

‎Simple.Data.Ado/Joiner.cs‎

Lines changed: 20 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -121,12 +121,12 @@ private string MakeJoinText(Table rightTable, string alias, ForeignKey foreignKe
121121
builder.AppendFormat(" JOIN {0}", rightTable.QualifiedName);
122122
if (!string.IsNullOrWhiteSpace(alias)) builder.Append(" " + _schema.QuoteObjectName(alias));
123123
builder.Append(" ON (");
124-
builder.Append(FormatJoinExpression(foreignKey, 0, alias));
124+
builder.Append(FormatJoinExpression(foreignKey, 0, rightTable, alias));
125125

126126
for (int i = 1; i < foreignKey.Columns.Length; i++)
127127
{
128128
builder.Append(" AND ");
129-
builder.Append(FormatJoinExpression(foreignKey, i, alias));
129+
builder.Append(FormatJoinExpression(foreignKey, i, rightTable, alias));
130130
}
131131
builder.Append(")");
132132
return builder.ToString();
@@ -167,13 +167,25 @@ private SimpleExpression CreateJoinExpression(ObjectReference table, ForeignKey
167167
return masterObjectReference == detailObjectReference;
168168
}
169169

170-
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex, string alias)
170+
private string FormatJoinExpression(ForeignKey foreignKey, int columnIndex, Table rightTable, string alias)
171171
{
172-
var leftTable = string.IsNullOrWhiteSpace(alias)
173-
? _schema.QuoteObjectName(foreignKey.MasterTable)
174-
: _schema.QuoteObjectName(alias);
175-
return string.Format("{0}.{1} = {2}.{3}", leftTable, _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
176-
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
172+
if (rightTable.ActualName == foreignKey.MasterTable.Name &&
173+
rightTable.Schema == foreignKey.MasterTable.Schema)
174+
{
175+
var rightTableName = string.IsNullOrWhiteSpace(alias)
176+
? _schema.QuoteObjectName(foreignKey.MasterTable)
177+
: _schema.QuoteObjectName(alias);
178+
return string.Format("{0}.{1} = {2}.{3}",
179+
rightTableName, _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
180+
_schema.QuoteObjectName(foreignKey.DetailTable), _schema.QuoteObjectName(foreignKey.Columns[columnIndex])
181+
);
182+
}
183+
184+
var leftTableName = string.IsNullOrWhiteSpace(alias)
185+
? _schema.QuoteObjectName(foreignKey.DetailTable)
186+
: _schema.QuoteObjectName(alias);
187+
return string.Format("{0}.{1} = {2}.{3}", _schema.QuoteObjectName(foreignKey.MasterTable), _schema.QuoteObjectName(foreignKey.UniqueColumns[columnIndex]),
188+
leftTableName, _schema.QuoteObjectName(foreignKey.Columns[columnIndex]));
177189
}
178190

179191
private string JoinKeyword

‎Simple.Data.SqlCe40/SqlCe40QueryPager.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ public IEnumerable<string> ApplyLimit(string sql, int take)
1919
yield return SelectMatch.Replace(sql, match => match.Value + " TOP(" + take + ") ");
2020
}
2121

22-
public IEnumerable<string> ApplyPaging(string sql, int skip, int take)
22+
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
2323
{
2424
if (sql.IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) < 0)
2525
{

‎Simple.Data.SqlCe40Test/SqlCe40QueryPagerTest.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@ public void ShouldApplyPagingUsingOrderBy()
2929
var expected = new[]{
3030
"select a,b,c from d where a = 1 order by c offset 5 rows fetch next 10 rows only"};
3131

32-
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, 5, 10);
32+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 5, 10);
3333
var modified = pagedSql.Select(x=> Normalize.Replace(x, " ").ToLowerInvariant());
3434

3535
Assert.IsTrue(expected.SequenceEqual(modified));
@@ -42,7 +42,7 @@ public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
4242
var expected = new[]{
4343
"select a,b,c from d where a = 1 order by a offset 10 rows fetch next 20 rows only"};
4444

45-
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, 10, 20);
45+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 10, 20);
4646
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
4747

4848
Assert.IsTrue(expected.SequenceEqual(modified));

0 commit comments

Comments
 (0)