Added Query paging support to SqlCe40 provider

ThatRendle · ThatRendle · commit 7e7432ef8387 · 2011-04-30T15:38:57.000+01:00
diff --git a/Simple.Data.SqlCe40/Simple.Data.SqlCe40.csproj b/Simple.Data.SqlCe40/Simple.Data.SqlCe40.csproj
@@ -55,6 +55,7 @@
       <DependentUpon>Resources.resx</DependentUpon>
     </Compile>
     <Compile Include="SqlCe40ConnectionProvider.cs" />
+    <Compile Include="SqlCe40QueryPager.cs" />
     <Compile Include="SqlCe40SchemaProvider.cs" />
     <Compile Include="SqlTypeResolver.cs" />
   </ItemGroup>
diff --git a/Simple.Data.SqlCe40/SqlCe40QueryPager.cs b/Simple.Data.SqlCe40/SqlCe40QueryPager.cs
@@ -0,0 +1,44 @@
+﻿using System;
+using System.Collections.Generic;
+using System.ComponentModel.Composition;
+using System.Linq;
+using System.Text;
+using System.Text.RegularExpressions;
+using Simple.Data.Ado;
+
+namespace Simple.Data.SqlCe40
+{
+    [Export(typeof(IQueryPager))]
+    public class SqlCe40QueryPager : IQueryPager
+    {
+        private static readonly Regex ColumnExtract = new Regex(@"SELECT\s*(.*)\s*(FROM.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
+        
+        public string ApplyPaging(string sql, string skipParameterName, string takeParameterName)
+        {
+            if (sql.IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) < 0)
+            {
+                var match = ColumnExtract.Match(sql);
+                var columns = match.Groups[1].Value.Trim();
+                sql += " ORDER BY " + columns.Split(',').First().Trim();
+            }
+
+            return string.Format("{0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", sql, skipParameterName, takeParameterName);
+        }
+
+        private static string ExtractOrderBy(string columns, ref string fromEtc)
+        {
+            string orderBy;
+            int index = fromEtc.IndexOf("ORDER BY", StringComparison.InvariantCultureIgnoreCase);
+            if (index > -1)
+            {
+                orderBy = fromEtc.Substring(index).Trim();
+                fromEtc = fromEtc.Remove(index).Trim();
+            }
+            else
+            {
+                orderBy = "ORDER BY " + columns.Split(',').First().Trim();
+            }
+            return orderBy;
+        }
+    }
+}
diff --git a/Simple.Data.SqlCe40Test/QueryTest.cs b/Simple.Data.SqlCe40Test/QueryTest.cs
@@ -0,0 +1,53 @@
+﻿using System.IO;
+using System.Reflection;
+using NUnit.Framework;
+
+namespace Simple.Data.SqlCe40Test
+{
+    [TestFixture]
+    public class QueryTest
+    {
+        private static readonly string DatabasePath = Path.Combine(
+            Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase.Substring(8)),
+            "TestDatabase.sdf");
+
+        [Test]
+        public void ShouldSelectFromOneToTen()
+        {
+            var db = Database.Opener.OpenFile(DatabasePath);
+            var query = db.PagingTest.QueryById(1.to(100)).Take(10);
+            int index = 1;
+            foreach (var row in query)
+            {
+                Assert.AreEqual(index, row.Id);
+                index++;
+            }
+        }
+
+        [Test]
+        public void ShouldSelectFromElevenToTwenty()
+        {
+            var db = Database.Opener.OpenFile(DatabasePath);
+            var query = db.PagingTest.QueryById(1.to(100)).Skip(10).Take(10);
+            int index = 11;
+            foreach (var row in query)
+            {
+                Assert.AreEqual(index, row.Id);
+                index++;
+            }
+        }
+
+        [Test]
+        public void ShouldSelectFromOneHundredToNinetyOne()
+        {
+            var db = Database.Opener.OpenFile(DatabasePath);
+            var query = db.PagingTest.QueryById(1.to(100)).OrderByDescending(db.PagingTest.Id).Skip(0).Take(10);
+            int index = 100;
+            foreach (var row in query)
+            {
+                Assert.AreEqual(index, row.Id);
+                index--;
+            }
+        }
+    }
+}
diff --git a/Simple.Data.SqlCe40Test/Simple.Data.SqlCe40Test.csproj b/Simple.Data.SqlCe40Test/Simple.Data.SqlCe40Test.csproj
@@ -77,6 +77,8 @@
     <Compile Include="NorthwindTests.cs" />
     <Compile Include="OrderDetailTests.cs" />
     <Compile Include="Properties\AssemblyInfo.cs" />
+    <Compile Include="QueryTest.cs" />
+    <Compile Include="SqlCe40QueryPagerTest.cs" />
     <Compile Include="SchemaTests\DatabaseSchemaTests.cs" />
     <Compile Include="User.cs" />
   </ItemGroup>
diff --git a/Simple.Data.SqlCe40Test/SqlCe40QueryPagerTest.cs b/Simple.Data.SqlCe40Test/SqlCe40QueryPagerTest.cs
@@ -0,0 +1,38 @@
+using System.Text.RegularExpressions;
+using NUnit.Framework;
+using Simple.Data.SqlCe40;
+
+namespace Simple.Data.SqlCe40Test
+{
+    [TestFixture]
+    public class SqlCe40QueryPagerTest
+    {
+        static readonly Regex Normalize = new Regex(@"\s+", RegexOptions.Multiline);
+
+        [Test]
+        public void ShouldApplyPagingUsingOrderBy()
+        {
+            const string sql = "select a,b,c from d where a = 1 order by c";
+            const string expected =
+                "select a,b,c from d where a = 1 order by c offset @skip rows fetch next @take rows only";
+
+            var modified = new SqlCe40QueryPager().ApplyPaging(sql, "@skip", "@take");
+            modified = Normalize.Replace(modified, " ").ToLowerInvariant();
+
+            Assert.AreEqual(expected, modified);
+        }
+
+        [Test]
+        public void ShouldApplyPagingUsingOrderByFirstColumnIfNotAlreadyOrdered()
+        {
+            const string sql = "select a,b,c from d where a = 1";
+            const string expected =
+                "select a,b,c from d where a = 1 order by a offset @skip rows fetch next @take rows only";
+
+            var modified = new SqlCe40QueryPager().ApplyPaging(sql, "@skip", "@take");
+            modified = Normalize.Replace(modified, " ").ToLowerInvariant();
+
+            Assert.AreEqual(expected, modified);
+        }
+    }
+}
diff --git a/Simple.Data.SqlCe40Test/TestDatabase.sdf b/Simple.Data.SqlCe40Test/TestDatabase.sdf
diff --git a/Simple.Data.SqlCe40Test/TestDatabase.sdf.bak b/Simple.Data.SqlCe40Test/TestDatabase.sdf.bak
