Skip to content

Commit 0f9d3c3

Browse files
richardhoptonrichardhopton
committed
Fix for issue ThatRendle#244
I assumed that SQL Ce supports DISTINCT when doing the regex replace - to be fair it won't break anything if it doesn't!
1 parent c027ed7 commit 0f9d3c3

5 files changed

Lines changed: 280 additions & 256 deletions

File tree

‎Simple.Data.SqlCe40/SqlCe40QueryPager.cs‎

Lines changed: 34 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,34 +1,34 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.ComponentModel.Composition;
4-
using System.Linq;
5-
using System.Text;
6-
using System.Text.RegularExpressions;
7-
using Simple.Data.Ado;
8-
9-
namespace Simple.Data.SqlCe40
10-
{
11-
[Export(typeof(IQueryPager))]
12-
public class SqlCe40QueryPager : IQueryPager
13-
{
14-
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
15-
private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*", RegexOptions.IgnoreCase);
16-
17-
public IEnumerable<string> ApplyLimit(string sql, int take)
18-
{
19-
yield return SelectMatch.Replace(sql, match => match.Value + " TOP(" + take + ") ");
20-
}
21-
22-
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
23-
{
24-
if (sql.IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) < 0)
25-
{
26-
var match = ColumnExtract.Match(sql);
27-
var columns = match.Groups[1].Value.Trim();
28-
sql += " ORDER BY " + columns.Split(',').First().Trim();
29-
}
30-
31-
yield return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, skip, take);
32-
}
33-
}
34-
}
1+
using System;
2+
using System.Collections.Generic;
3+
using System.ComponentModel.Composition;
4+
using System.Linq;
5+
using System.Text;
6+
using System.Text.RegularExpressions;
7+
using Simple.Data.Ado;
8+
9+
namespace Simple.Data.SqlCe40
10+
{
11+
[Export(typeof(IQueryPager))]
12+
public class SqlCe40QueryPager : IQueryPager
13+
{
14+
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
15+
private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*(DISTINCT)?", RegexOptions.IgnoreCase);
16+
17+
public IEnumerable<string> ApplyLimit(string sql, int take)
18+
{
19+
yield return SelectMatch.Replace(sql, match => match.Value + " TOP(" + take + ") ");
20+
}
21+
22+
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
23+
{
24+
if (sql.IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) < 0)
25+
{
26+
var match = ColumnExtract.Match(sql);
27+
var columns = match.Groups[1].Value.Trim();
28+
sql += " ORDER BY " + columns.Split(',').First().Trim();
29+
}
30+
31+
yield return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, skip, take);
32+
}
33+
}
34+
}

‎Simple.Data.SqlCe40Test/SqlCe40QueryPagerTest.cs‎

Lines changed: 63 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -1,51 +1,63 @@
1-
using System.Text.RegularExpressions;
2-
using NUnit.Framework;
3-
using Simple.Data.SqlCe40;
4-
using System.Linq;
5-
6-
namespace Simple.Data.SqlCe40Test
7-
{
8-
[TestFixture]
9-
public class SqlCe40QueryPagerTest
10-
{
11-
static readonly Regex Normalize = new Regex(@"\s+", RegexOptions.Multiline);
12-
13-
[Test]
14-
public void ShouldApplyLimitUsingTop()
15-
{
16-
var sql = "select a,b,c from d where a = 1 order by c";
17-
var expected = new[] { "select top(5) a,b,c from d where a = 1 order by c" };
18-
19-
var pagedSql = new SqlCe40QueryPager().ApplyLimit(sql, 5);
20-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
21-
22-
Assert.IsTrue(expected.SequenceEqual(modified));
23-
}
24-
25-
[Test]
26-
public void ShouldApplyPagingUsingOrderBy()
27-
{
28-
var sql = "select a,b,c from d where a = 1 order by c";
29-
var expected = new[]{
30-
"select a,b,c from d where a = 1 order by c offset 5 rows fetch next 10 rows only"};
31-
32-
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 5, 10);
33-
var modified = pagedSql.Select(x=> Normalize.Replace(x, " ").ToLowerInvariant());
34-
35-
Assert.IsTrue(expected.SequenceEqual(modified));
36-
}
37-
38-
[Test]
39-
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
40-
{
41-
var sql = "select a,b,c from d where a = 1";
42-
var expected = new[]{
43-
"select a,b,c from d where a = 1 order by a offset 10 rows fetch next 20 rows only"};
44-
45-
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 10, 20);
46-
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
47-
48-
Assert.IsTrue(expected.SequenceEqual(modified));
49-
}
50-
}
51-
}
1+
using System.Text.RegularExpressions;
2+
using NUnit.Framework;
3+
using Simple.Data.SqlCe40;
4+
using System.Linq;
5+
6+
namespace Simple.Data.SqlCe40Test
7+
{
8+
[TestFixture]
9+
public class SqlCe40QueryPagerTest
10+
{
11+
static readonly Regex Normalize = new Regex(@"\s+", RegexOptions.Multiline);
12+
13+
[Test]
14+
public void ShouldApplyLimitUsingTop()
15+
{
16+
var sql = "select a,b,c from d where a = 1 order by c";
17+
var expected = new[] { "select top(5) a,b,c from d where a = 1 order by c" };
18+
19+
var pagedSql = new SqlCe40QueryPager().ApplyLimit(sql, 5);
20+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
21+
22+
Assert.IsTrue(expected.SequenceEqual(modified));
23+
}
24+
25+
[Test]
26+
public void ShouldApplyLimitUsingTopWithDistinct()
27+
{
28+
var sql = "select distinct a,b,c from d where a = 1 order by c";
29+
var expected = new[] { "select distinct top(5) a,b,c from d where a = 1 order by c" };
30+
31+
var pagedSql = new SqlCe40QueryPager().ApplyLimit(sql, 5);
32+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
33+
34+
Assert.IsTrue(expected.SequenceEqual(modified));
35+
}
36+
37+
[Test]
38+
public void ShouldApplyPagingUsingOrderBy()
39+
{
40+
var sql = "select a,b,c from d where a = 1 order by c";
41+
var expected = new[]{
42+
"select a,b,c from d where a = 1 order by c offset 5 rows fetch next 10 rows only"};
43+
44+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 5, 10);
45+
var modified = pagedSql.Select(x=> Normalize.Replace(x, " ").ToLowerInvariant());
46+
47+
Assert.IsTrue(expected.SequenceEqual(modified));
48+
}
49+
50+
[Test]
51+
public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
52+
{
53+
var sql = "select a,b,c from d where a = 1";
54+
var expected = new[]{
55+
"select a,b,c from d where a = 1 order by a offset 10 rows fetch next 20 rows only"};
56+
57+
var pagedSql = new SqlCe40QueryPager().ApplyPaging(sql, new string[0], 10, 20);
58+
var modified = pagedSql.Select(x => Normalize.Replace(x, " ").ToLowerInvariant());
59+
60+
Assert.IsTrue(expected.SequenceEqual(modified));
61+
}
62+
}
63+
}

‎Simple.Data.SqlServer/SqlCommandOptimizer.cs‎

Lines changed: 23 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,23 +1,23 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.Linq;
4-
using System.Text;
5-
6-
namespace Simple.Data.SqlServer
7-
{
8-
using System.ComponentModel.Composition;
9-
using System.Data.SqlClient;
10-
using System.Text.RegularExpressions;
11-
using Ado;
12-
13-
[Export(typeof(CommandOptimizer))]
14-
public class SqlCommandOptimizer : CommandOptimizer
15-
{
16-
public override System.Data.IDbCommand OptimizeFindOne(System.Data.IDbCommand command)
17-
{
18-
command.CommandText = Regex.Replace(command.CommandText, "^SELECT ", "SELECT TOP 1 ",
19-
RegexOptions.IgnoreCase);
20-
return command;
21-
}
22-
}
23-
}
1+
using System;
2+
using System.Collections.Generic;
3+
using System.Linq;
4+
using System.Text;
5+
6+
namespace Simple.Data.SqlServer
7+
{
8+
using System.ComponentModel.Composition;
9+
using System.Data.SqlClient;
10+
using System.Text.RegularExpressions;
11+
using Ado;
12+
13+
[Export(typeof(CommandOptimizer))]
14+
public class SqlCommandOptimizer : CommandOptimizer
15+
{
16+
public override System.Data.IDbCommand OptimizeFindOne(System.Data.IDbCommand command)
17+
{
18+
command.CommandText = Regex.Replace(command.CommandText, @"^SELECT\s*(DISTINCT)?", "SELECT $1 TOP 1 ",
19+
RegexOptions.IgnoreCase);
20+
return command;
21+
}
22+
}
23+
}

‎Simple.Data.SqlServer/SqlQueryPager.cs‎

Lines changed: 78 additions & 78 deletions
Original file line numberDiff line numberDiff line change
@@ -1,78 +1,78 @@
1-
using System;
2-
using System.Collections.Generic;
3-
using System.ComponentModel.Composition;
4-
using System.Linq;
5-
using System.Text;
6-
using System.Text.RegularExpressions;
7-
using Simple.Data.Ado;
8-
9-
namespace Simple.Data.SqlServer
10-
{
11-
[Export(typeof(IQueryPager))]
12-
public class SqlQueryPager : IQueryPager
13-
{
14-
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
15-
private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*", RegexOptions.IgnoreCase);
16-
17-
public IEnumerable<string> ApplyLimit(string sql, int take)
18-
{
19-
yield return SelectMatch.Replace(sql, match => match.Value + " TOP " + take + " ");
20-
}
21-
22-
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
23-
{
24-
var builder = new StringBuilder("WITH __Data AS (SELECT ");
25-
26-
var match = ColumnExtract.Match(sql);
27-
var columns = match.Groups[1].Value.Trim();
28-
var fromEtc = match.Groups[2].Value.Trim();
29-
30-
builder.Append(string.Join(",", keys));
31-
32-
var orderBy = ExtractOrderBy(columns, keys, ref fromEtc);
33-
34-
builder.AppendFormat(", ROW_NUMBER() OVER({0}) AS [_#_]", orderBy);
35-
builder.AppendLine();
36-
builder.Append(fromEtc);
37-
builder.AppendLine(")");
38-
builder.AppendFormat("SELECT {0} FROM __Data ", columns);
39-
builder.AppendFormat("JOIN {0} ON ",
40-
keys[0].Substring(0, keys[0].LastIndexOf(".", StringComparison.OrdinalIgnoreCase)));
41-
builder.AppendFormat(string.Join(" ", keys.Select(MakeDataJoin)));
42-
var rest = Regex.Replace(fromEtc, @"^from (\[.*?\]\.\[.*?\])", @"");
43-
builder.Append(rest);
44-
45-
builder.AppendFormat(" AND [_#_] BETWEEN {0} AND {1}", skip + 1, skip + take);
46-
47-
yield return builder.ToString();
48-
}
49-
50-
private static string MakeDataJoin(string key)
51-
{
52-
return key + " = __Data" + key.Substring(key.LastIndexOf(".", StringComparison.OrdinalIgnoreCase));
53-
}
54-
55-
private static string DequalifyColumns(string original)
56-
{
57-
var q = from part in original.Split(',')
58-
select part.Substring(Math.Max(part.LastIndexOf('.') + 1, part.LastIndexOf('[')));
59-
return string.Join(",", q);
60-
}
61-
62-
private static string ExtractOrderBy(string columns, string[] keys, ref string fromEtc)
63-
{
64-
string orderBy;
65-
int index = fromEtc.IndexOf("ORDER BY", StringComparison.InvariantCultureIgnoreCase);
66-
if (index > -1)
67-
{
68-
orderBy = fromEtc.Substring(index).Trim();
69-
fromEtc = fromEtc.Remove(index).Trim();
70-
}
71-
else
72-
{
73-
orderBy = "ORDER BY " + string.Join(", ", keys);
74-
}
75-
return orderBy;
76-
}
77-
}
78-
}
1+
using System;
2+
using System.Collections.Generic;
3+
using System.ComponentModel.Composition;
4+
using System.Linq;
5+
using System.Text;
6+
using System.Text.RegularExpressions;
7+
using Simple.Data.Ado;
8+
9+
namespace Simple.Data.SqlServer
10+
{
11+
[Export(typeof(IQueryPager))]
12+
public class SqlQueryPager : IQueryPager
13+
{
14+
private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
15+
private static readonly Regex SelectMatch = new Regex(@"^SELECT\s*(DISTINCT)?", RegexOptions.IgnoreCase);
16+
17+
public IEnumerable<string> ApplyLimit(string sql, int take)
18+
{
19+
yield return SelectMatch.Replace(sql, match => match.Value + " TOP " + take + " ");
20+
}
21+
22+
public IEnumerable<string> ApplyPaging(string sql, string[] keys, int skip, int take)
23+
{
24+
var builder = new StringBuilder("WITH __Data AS (SELECT ");
25+
26+
var match = ColumnExtract.Match(sql);
27+
var columns = match.Groups[1].Value.Trim();
28+
var fromEtc = match.Groups[2].Value.Trim();
29+
30+
builder.Append(string.Join(",", keys));
31+
32+
var orderBy = ExtractOrderBy(columns, keys, ref fromEtc);
33+
34+
builder.AppendFormat(", ROW_NUMBER() OVER({0}) AS [_#_]", orderBy);
35+
builder.AppendLine();
36+
builder.Append(fromEtc);
37+
builder.AppendLine(")");
38+
builder.AppendFormat("SELECT {0} FROM __Data ", columns);
39+
builder.AppendFormat("JOIN {0} ON ",
40+
keys[0].Substring(0, keys[0].LastIndexOf(".", StringComparison.OrdinalIgnoreCase)));
41+
builder.AppendFormat(string.Join(" ", keys.Select(MakeDataJoin)));
42+
var rest = Regex.Replace(fromEtc, @"^from (\[.*?\]\.\[.*?\])", @"");
43+
builder.Append(rest);
44+
45+
builder.AppendFormat(" AND [_#_] BETWEEN {0} AND {1}", skip + 1, skip + take);
46+
47+
yield return builder.ToString();
48+
}
49+
50+
private static string MakeDataJoin(string key)
51+
{
52+
return key + " = __Data" + key.Substring(key.LastIndexOf(".", StringComparison.OrdinalIgnoreCase));
53+
}
54+
55+
private static string DequalifyColumns(string original)
56+
{
57+
var q = from part in original.Split(',')
58+
select part.Substring(Math.Max(part.LastIndexOf('.') + 1, part.LastIndexOf('[')));
59+
return string.Join(",", q);
60+
}
61+
62+
private static string ExtractOrderBy(string columns, string[] keys, ref string fromEtc)
63+
{
64+
string orderBy;
65+
int index = fromEtc.IndexOf("ORDER BY", StringComparison.InvariantCultureIgnoreCase);
66+
if (index > -1)
67+
{
68+
orderBy = fromEtc.Substring(index).Trim();
69+
fromEtc = fromEtc.Remove(index).Trim();
70+
}
71+
else
72+
{
73+
orderBy = "ORDER BY " + string.Join(", ", keys);
74+
}
75+
return orderBy;
76+
}
77+
}
78+
}

0 commit comments

Comments
 (0)