Merge pull request ThatRendle#193 from SimonH/master

ThatRendle · ThatRendle · commit bfb4fbfbece2 · 2012-06-19T04:31:28.000-07:00
Custom Query builders and update locking
diff --git a/Simple.Data.Ado/AdoAdapterQueryRunner.cs b/Simple.Data.Ado/AdoAdapterQueryRunner.cs
@@ -94,6 +94,7 @@ out IEnumerable<SimpleQueryClauseBase>
                 .ClearTake()
                 .ClearOrderBy()
                 .ClearWith()
+                .ClearForUpdate()
                 .ReplaceSelect(new CountSpecialReference());
             var unhandledClausesList = new List<IEnumerable<SimpleQueryClauseBase>>
                                            {
diff --git a/Simple.Data.Ado/CommandBuilder.cs b/Simple.Data.Ado/CommandBuilder.cs
@@ -9,7 +9,7 @@
 
 namespace Simple.Data.Ado
 {
-    class CommandBuilder : ICommandBuilder
+    public class CommandBuilder : ICommandBuilder
     {
         private int _number;
         private Func<IDbCommand, IDbParameterFactory> _getParameterFactory;
diff --git a/Simple.Data.Ado/ICustomQueryBuilder.cs b/Simple.Data.Ado/ICustomQueryBuilder.cs
@@ -0,0 +1,9 @@
+﻿using System.Collections.Generic;
+
+namespace Simple.Data.Ado
+{
+    public interface ICustomQueryBuilder
+    {
+        ICommandBuilder Build(AdoAdapter adapter, int bulkIndex, SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses);
+    }
+}
diff --git a/Simple.Data.Ado/Joiner.cs b/Simple.Data.Ado/Joiner.cs
@@ -8,7 +8,7 @@
 
 namespace Simple.Data.Ado
 {
-    class Joiner
+    public class Joiner
     {
         private readonly JoinType _joinType;
         private readonly DatabaseSchema _schema;
diff --git a/Simple.Data.Ado/QueryBuilder.cs b/Simple.Data.Ado/QueryBuilder.cs
diff --git a/Simple.Data.Ado/QueryBuilderBase.cs b/Simple.Data.Ado/QueryBuilderBase.cs
diff --git a/Simple.Data.Ado/Simple.Data.Ado.csproj b/Simple.Data.Ado/Simple.Data.Ado.csproj
@@ -98,6 +98,7 @@
     <Compile Include="ICommandBuilder.cs" />
     <Compile Include="IConnectionProvider.cs" />
     <Compile Include="ICustomInserter.cs" />
+    <Compile Include="ICustomQueryBuilder.cs" />
     <Compile Include="IDbParameterFactory.cs" />
     <Compile Include="IExpressionFormatter.cs" />
     <Compile Include="IFunctionNameConverter.cs" />
@@ -121,6 +122,7 @@
     </Compile>
     <Compile Include="ProviderHelper.cs" />
     <Compile Include="QueryBuilder.cs" />
+    <Compile Include="QueryBuilderBase.cs" />
     <Compile Include="SimpleReferenceFormatter.cs" />
     <Compile Include="SchemaResolutionException.cs" />
     <Compile Include="Schema\AmbiguousObjectNameException.cs" />
diff --git a/Simple.Data.Ado/SimpleReferenceFormatter.cs b/Simple.Data.Ado/SimpleReferenceFormatter.cs
@@ -5,16 +5,22 @@ namespace Simple.Data.Ado
     using System.Text;
     using Schema;
 
-    class SimpleReferenceFormatter
+    public class SimpleReferenceFormatter
     {
-        private readonly IFunctionNameConverter _functionNameConverter = new FunctionNameConverter();
+        private readonly IFunctionNameConverter _functionNameConverter;
         private readonly DatabaseSchema _schema;
         private readonly ICommandBuilder _commandBuilder;
 
-        public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder)
+        public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder) : this(schema, commandBuilder, null)
+        {
+
+        }
+
+        public SimpleReferenceFormatter(DatabaseSchema schema, ICommandBuilder commandBuilder, IFunctionNameConverter functionNameConverter)
         {
             _schema = schema;
             _commandBuilder = commandBuilder;
+            _functionNameConverter = functionNameConverter ?? new FunctionNameConverter();
         }
 
         public string FormatColumnClause(SimpleReference reference)
diff --git a/Simple.Data.SqlServer/Simple.Data.SqlServer.csproj b/Simple.Data.SqlServer/Simple.Data.SqlServer.csproj
@@ -59,12 +59,14 @@
       <DependentUpon>Resources.resx</DependentUpon>
     </Compile>
     <Compile Include="SqlBulkInserter.cs" />
+    <Compile Include="SqlCustomQueryBuilder.cs" />
     <Compile Include="SqlDbParameterFactory.cs" />
     <Compile Include="SqlColumn.cs" />
     <Compile Include="SqlCommandOptimizer.cs" />
     <Compile Include="SqlConnectionProvider.cs" />
     <Compile Include="SqlDbTypeEx.cs" />
     <Compile Include="SqlObservableQueryRunner.cs" />
+    <Compile Include="SqlQueryBuilder.cs" />
     <Compile Include="SqlQueryPager.cs" />
     <Compile Include="SqlSchemaProvider.cs" />
     <Compile Include="SqlTypeResolver.cs" />
diff --git a/Simple.Data.SqlServer/SqlCustomQueryBuilder.cs b/Simple.Data.SqlServer/SqlCustomQueryBuilder.cs
@@ -0,0 +1,16 @@
+﻿using System.Collections.Generic;
+using System.ComponentModel.Composition;
+using Simple.Data.Ado;
+
+namespace Simple.Data.SqlServer
+{
+
+    [Export(typeof(ICustomQueryBuilder))]
+    public class SqlCustomQueryBuilder : ICustomQueryBuilder
+    {
+        public ICommandBuilder Build(AdoAdapter adapter, int bulkIndex, SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
+        {
+            return new SqlQueryBuilder(adapter, bulkIndex).Build(query, out unhandledClauses);
+        }
+    }
+}
diff --git a/Simple.Data.SqlServer/SqlQueryBuilder.cs b/Simple.Data.SqlServer/SqlQueryBuilder.cs
@@ -0,0 +1,48 @@
+﻿using System.Collections.Generic;
+using System.Linq;
+using Simple.Data.Ado;
+
+namespace Simple.Data.SqlServer
+{
+    public class SqlQueryBuilder : QueryBuilderBase
+    {
+        private List<SimpleQueryClauseBase> _unhandledClauses;
+
+        public SqlQueryBuilder(AdoAdapter adapter, int bulkIndex)
+            : base(adapter, bulkIndex)
+        {
+        }
+
+        public override ICommandBuilder Build(SimpleQuery query, out IEnumerable<SimpleQueryClauseBase> unhandledClauses)
+        {
+            _unhandledClauses = new List<SimpleQueryClauseBase>();
+            SetQueryContext(query);
+
+            HandleJoins();
+            HandleQueryCriteria();
+            HandleGrouping();
+            HandleHavingCriteria();
+            HandleOrderBy();
+
+            unhandledClauses = _unhandledClauses;
+            return _commandBuilder;
+        }
+
+        protected override string GetSelectClause(ObjectName tableName)
+        {
+            var select = base.GetSelectClause(tableName);
+            var forUpdateClause = _query.Clauses.OfType<ForUpdateClause>().FirstOrDefault();
+            if (forUpdateClause != null)
+            {
+                var forUpdate = " WITH (UPDLOCK, ROWLOCK";
+                if (forUpdateClause.SkipLockedRows)
+                {
+                    forUpdate += ", READPAST";
+                }
+                forUpdate += ")";
+                select += forUpdate;
+            }
+            return select;
+        }
+    }
+}
diff --git a/Simple.Data.SqlTest/QueryTest.cs b/Simple.Data.SqlTest/QueryTest.cs
@@ -431,5 +431,21 @@ public void CanFetchMoreThanOneHundredRows()
 
             Assert.GreaterOrEqual(customers.Count, 200);
         }
+
+        [Test]
+        public void QueryWithForUpdateFalseShouldReturnCorrectResult()
+        {
+            var db = DatabaseHelper.Open();
+            var actual = db.Users.QueryById(1).Select(db.Users.Name).ForUpdate(false).First();
+            Assert.AreEqual("Bob", actual.Name);
+        }
+
+        [Test]
+        public void QueryWithForUpdateTrueShouldReturnCorrectResult()
+        {
+            var db = DatabaseHelper.Open();
+            var actual = db.Users.QueryById(1).Select(db.Users.Name).ForUpdate(true).First();
+            Assert.AreEqual("Bob", actual.Name);
+        }
     }
 }
diff --git a/Simple.Data.UnitTest/SimpleQueryTest.cs b/Simple.Data.UnitTest/SimpleQueryTest.cs
@@ -168,5 +168,38 @@ public void JoinOnShouldSetAJoin()
             Assert.AreEqual(quux.foo_id, join.JoinExpression.RightOperand);
             Assert.AreEqual(SimpleExpressionType.Equal, join.JoinExpression.Type);
         }
+
+        [Test]
+        public void ForUpdateShouldAddAClause()
+        {
+            var query = new SimpleQuery(null, "foo").ForUpdate(true);
+            Assert.AreEqual(1, query.Clauses.OfType<ForUpdateClause>().Count());
+            var forUpdate = query.Clauses.OfType<ForUpdateClause>().Single();
+            Assert.IsTrue(forUpdate.SkipLockedRows);
+        }
+
+        [Test]
+        public void SubsequentCallsToForUpdateShouldReplaceClause()
+        {
+            var query = new SimpleQuery(null, "foo").ForUpdate(false);
+            Assert.AreEqual(1, query.Clauses.OfType<ForUpdateClause>().Count());
+            var forUpdate = query.Clauses.OfType<ForUpdateClause>().Single();
+            Assert.IsFalse(forUpdate.SkipLockedRows);
+            query = query.ForUpdate(true);
+            Assert.AreEqual(1, query.Clauses.OfType<ForUpdateClause>().Count());
+            forUpdate = query.Clauses.OfType<ForUpdateClause>().Single();
+            Assert.IsTrue(forUpdate.SkipLockedRows);
+        }
+
+        [Test]
+        public void ClearForUpdateRemovesClause()
+        {
+            var query = new SimpleQuery(null, "foo").ForUpdate(false);
+            Assert.AreEqual(1, query.Clauses.OfType<ForUpdateClause>().Count());
+            var forUpdate = query.Clauses.OfType<ForUpdateClause>().Single();
+            Assert.IsFalse(forUpdate.SkipLockedRows);
+            query = query.ClearForUpdate();
+            Assert.AreEqual(0, query.Clauses.OfType<ForUpdateClause>().Count());
+        }
     }
 }
diff --git a/Simple.Data/ForUpdateClause.cs b/Simple.Data/ForUpdateClause.cs
@@ -0,0 +1,12 @@
+﻿namespace Simple.Data
+{
+    public class ForUpdateClause : SimpleQueryClauseBase
+    {
+        public bool SkipLockedRows { get; private set; }
+
+        public ForUpdateClause(bool skipLockedRows)
+        {
+            SkipLockedRows = skipLockedRows;
+        }
+    }
+}
diff --git a/Simple.Data/Simple.Data.csproj b/Simple.Data/Simple.Data.csproj
@@ -88,6 +88,7 @@
     <Compile Include="DatabaseRunner.cs" />
     <Compile Include="DictionaryCloner.cs" />
     <Compile Include="ErrorCallback.cs" />
+    <Compile Include="ForUpdateClause.cs" />
     <Compile Include="FunctionSignature.cs" />
     <Compile Include="InMemoryAdapter.cs" />
     <Compile Include="InMemoryAdapterIAdapterWithTransactions.cs" />
diff --git a/Simple.Data/SimpleQuery.cs b/Simple.Data/SimpleQuery.cs
@@ -150,6 +150,25 @@ public SimpleQuery ReplaceSelect(IEnumerable<SimpleReference> columns)
             return new SimpleQuery(this, _clauses.Where(c => !(c is SelectClause)).Append(new SelectClause(columns)).ToArray());
         }
 
+        /// <summary>
+        /// Alters the query to lock the rows for update. 
+        /// </summary>
+        /// <param name="skipLockedRows">Indicates whether the selection should skip rows already locked</param>
+        /// <returns>A new <see cref="SimpleQuery"/> which will perform locking on the selected rows</returns>
+        public SimpleQuery ForUpdate(bool skipLockedRows)
+        {
+            return new SimpleQuery(this, _clauses.Where(c => !(c is ForUpdateClause)).Append(new ForUpdateClause(skipLockedRows)).ToArray());
+        }
+
+        /// <summary>
+        /// Removes any specified ForUpdate from the Query
+        /// </summary>
+        /// <returns>A new <see cref="SimpleQuery"/> with any specified ForUpdate removed</returns>
+        public SimpleQuery ClearForUpdate()
+        {
+            return new SimpleQuery(this, _clauses.Where(c => !(c is ForUpdateClause)).ToArray());
+        }
+
         private void ThrowIfThereIsAlreadyASelectClause()
         {
             if (_clauses.OfType<SelectClause>().Any())

Original file line number	Diff line number	Diff line change
`@@ -94,6 +94,7 @@ out IEnumerable<SimpleQueryClauseBase>`
`94`	`94`	`.ClearTake()`
`95`	`95`	`.ClearOrderBy()`
`96`	`96`	`.ClearWith()`
	`97`	`+ .ClearForUpdate()`
`97`	`98`	`.ReplaceSelect(new CountSpecialReference());`
`98`	`99`	`var unhandledClausesList = new List<IEnumerable<SimpleQueryClauseBase>>`
`99`	`100`	`{`
Original file line number	Diff line number	Diff line change
`@@ -9,7 +9,7 @@`
`9`	`9`
`10`	`10`	`namespace Simple.Data.Ado`
`11`	`11`	`{`
`12`		`- class CommandBuilder : ICommandBuilder`
	`12`	`+ public class CommandBuilder : ICommandBuilder`
`13`	`13`	`{`
`14`	`14`	`private int _number;`
`15`	`15`	`private Func<IDbCommand, IDbParameterFactory> _getParameterFactory;`
Original file line number	Diff line number	Diff line change
`@@ -8,7 +8,7 @@`
`8`	`8`
`9`	`9`	`namespace Simple.Data.Ado`
`10`	`10`	`{`
`11`		`- class Joiner`
	`11`	`+ public class Joiner`
`12`	`12`	`{`
`13`	`13`	`private readonly JoinType _joinType;`
`14`	`14`	`private readonly DatabaseSchema _schema;`