5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
28 using System.Collections.Generic;
\r
29 using System.Diagnostics;
\r
31 using System.Linq.Expressions;
\r
32 using System.Text.RegularExpressions;
\r
34 using DbLinq.Data.Linq.Sugar.ExpressionMutator;
\r
35 using DbLinq.Data.Linq.Sugar.Expressions;
\r
36 using DbLinq.Factory;
\r
39 namespace DbLinq.Data.Linq.Sugar.Implementation
\r
42 /// Full query builder, with cache management
\r
43 /// 1. Parses Linq Expression
\r
44 /// 2. Generates SQL
\r
46 internal partial class QueryBuilder : IQueryBuilder
\r
48 public IExpressionLanguageParser ExpressionLanguageParser { get; set; }
\r
49 public IExpressionDispatcher ExpressionDispatcher { get; set; }
\r
50 public IPrequelAnalyzer PrequelAnalyzer { get; set; }
\r
51 public IExpressionOptimizer ExpressionOptimizer { get; set; }
\r
52 public ISpecialExpressionTranslator SpecialExpressionTranslator { get; set; }
\r
53 public ISqlBuilder SqlBuilder { get; set; }
\r
55 public QueryBuilder()
\r
57 ExpressionLanguageParser = ObjectFactory.Get<IExpressionLanguageParser>();
\r
58 ExpressionDispatcher = ObjectFactory.Get<IExpressionDispatcher>();
\r
59 PrequelAnalyzer = ObjectFactory.Get<IPrequelAnalyzer>();
\r
60 ExpressionOptimizer = ObjectFactory.Get<IExpressionOptimizer>();
\r
61 SpecialExpressionTranslator = ObjectFactory.Get<ISpecialExpressionTranslator>();
\r
62 SqlBuilder = ObjectFactory.Get<ISqlBuilder>();
\r
66 /// Builds the ExpressionQuery:
\r
67 /// - parses Expressions and builds row creator
\r
68 /// - checks names unicity
\r
70 /// <param name="expressions"></param>
\r
71 /// <param name="queryContext"></param>
\r
72 /// <returns></returns>
\r
73 protected virtual ExpressionQuery BuildExpressionQuery(ExpressionChain expressions, QueryContext queryContext)
\r
75 var builderContext = new BuilderContext(queryContext);
\r
76 BuildExpressionQuery(expressions, builderContext);
\r
77 CheckTablesAlias(builderContext);
\r
78 CheckParametersAlias(builderContext);
\r
79 return builderContext.ExpressionQuery;
\r
83 /// Finds all registered tables or columns with the given name.
\r
84 /// We exclude parameter because they won't be prefixed/suffixed the same way (well, that's a guess, I hope it's a good one)
\r
86 /// <param name="name"></param>
\r
87 /// <param name="builderContext"></param>
\r
88 /// <returns></returns>
\r
89 protected virtual IList<Expression> FindExpressionsByName(string name, BuilderContext builderContext)
\r
91 var expressions = new List<Expression>();
\r
92 expressions.AddRange((from t in builderContext.EnumerateAllTables() where t.Alias == name select (Expression)t).Distinct());
\r
93 expressions.AddRange(from c in builderContext.EnumerateScopeColumns() where c.Alias == name select (Expression)c);
\r
97 protected virtual string MakeName(string aliasBase, int index, string anonymousBase, BuilderContext builderContext)
\r
99 if (string.IsNullOrEmpty(aliasBase))
\r
100 aliasBase = anonymousBase;
\r
101 return string.Format("{0}{1}", aliasBase, index);
\r
104 protected virtual string MakeTableName(string aliasBase, int index, BuilderContext builderContext)
\r
106 return MakeName(aliasBase, index, "t", builderContext);
\r
109 protected virtual string MakeParameterName(string aliasBase, int index, BuilderContext builderContext)
\r
111 return MakeName(aliasBase, index, "p", builderContext);
\r
115 /// Give all non-aliased tables a name
\r
117 /// <param name="builderContext"></param>
\r
118 protected virtual void CheckTablesAlias(BuilderContext builderContext)
\r
120 var tables = builderContext.EnumerateAllTables().Distinct().ToList();
\r
121 // just to be nice: if we have only one table involved, there's no need to alias it
\r
122 if (tables.Count == 1)
\r
124 tables[0].Alias = null;
\r
128 foreach (var tableExpression in tables)
\r
130 // if no alias, or duplicate alias
\r
131 if (string.IsNullOrEmpty(tableExpression.Alias) ||
\r
132 FindExpressionsByName(tableExpression.Alias, builderContext).Count > 1)
\r
134 int anonymousIndex = 0;
\r
135 var aliasBase = tableExpression.Alias;
\r
136 // we try to assign one until we have a unique alias
\r
139 tableExpression.Alias = MakeTableName(aliasBase, ++anonymousIndex, builderContext);
\r
140 } while (FindExpressionsByName(tableExpression.Alias, builderContext).Count != 1);
\r
146 protected virtual IList<InputParameterExpression> FindParametersByName(string name, BuilderContext builderContext)
\r
148 return (from p in builderContext.ExpressionQuery.Parameters where p.Alias == name select p).ToList();
\r
152 /// Gives anonymous parameters a name and checks for names unicity
\r
153 /// The fact of giving a nice name just helps for readability
\r
155 /// <param name="builderContext"></param>
\r
156 protected virtual void CheckParametersAlias(BuilderContext builderContext)
\r
158 foreach (var externalParameterExpression in builderContext.ExpressionQuery.Parameters)
\r
160 if (string.IsNullOrEmpty(externalParameterExpression.Alias)
\r
161 || FindParametersByName(externalParameterExpression.Alias, builderContext).Count > 1)
\r
163 int anonymousIndex = 0;
\r
164 var aliasBase = externalParameterExpression.Alias;
\r
165 // we try to assign one until we have a unique alias
\r
168 externalParameterExpression.Alias = MakeTableName(aliasBase, ++anonymousIndex, builderContext);
\r
169 } while (FindParametersByName(externalParameterExpression.Alias, builderContext).Count > 1);
\r
175 /// Builds and chains the provided Expressions
\r
177 /// <param name="expressions"></param>
\r
178 /// <param name="builderContext"></param>
\r
179 protected virtual void BuildExpressionQuery(ExpressionChain expressions, BuilderContext builderContext)
\r
181 var previousExpression = ExpressionDispatcher.CreateTableExpression(expressions.Expressions[0], builderContext);
\r
182 previousExpression = BuildExpressionQuery(expressions, previousExpression, builderContext);
\r
183 BuildOffsetsAndLimits(builderContext);
\r
184 // then prepare Parts for SQL translation
\r
185 PrepareSqlOperands(builderContext);
\r
186 // now, we optimize anything we can
\r
187 OptimizeQuery(builderContext);
\r
188 // finally, compile our object creation method
\r
189 CompileRowCreator(builderContext);
\r
190 // in the very end, we keep the SELECT clause
\r
191 builderContext.ExpressionQuery.Select = builderContext.CurrentSelect;
\r
195 /// Builds the ExpressionQuery main Expression, given a Table (or projection) expression
\r
197 /// <param name="expressions"></param>
\r
198 /// <param name="tableExpression"></param>
\r
199 /// <param name="builderContext"></param>
\r
200 /// <returns></returns>
\r
201 protected Expression BuildExpressionQuery(ExpressionChain expressions, Expression tableExpression, BuilderContext builderContext)
\r
203 tableExpression = ExpressionDispatcher.Analyze(expressions, tableExpression, builderContext);
\r
204 ExpressionDispatcher.BuildSelect(tableExpression, builderContext);
\r
205 return tableExpression;
\r
208 public virtual SelectExpression BuildSelectExpression(ExpressionChain expressions, Expression tableExpression, BuilderContext builderContext)
\r
210 BuildExpressionQuery(expressions, tableExpression, builderContext);
\r
211 return builderContext.CurrentSelect;
\r
215 /// This is a hint for SQL generations
\r
217 /// <param name="builderContext"></param>
\r
218 protected virtual void BuildOffsetsAndLimits(BuilderContext builderContext)
\r
220 foreach (var selectExpression in builderContext.SelectExpressions)
\r
222 if (selectExpression.Offset != null && selectExpression.Limit != null)
\r
224 selectExpression.OffsetAndLimit = Expression.Add(selectExpression.Offset, selectExpression.Limit);
\r
230 /// Builds the delegate to create a row
\r
232 /// <param name="builderContext"></param>
\r
233 protected virtual void CompileRowCreator(BuilderContext builderContext)
\r
235 var reader = builderContext.CurrentSelect.Reader;
\r
236 reader = (LambdaExpression)SpecialExpressionTranslator.Translate(reader);
\r
237 reader = (LambdaExpression)ExpressionOptimizer.Optimize(reader, builderContext);
\r
238 builderContext.ExpressionQuery.RowObjectCreator = reader.Compile();
\r
242 /// Prepares SELECT operands to help SQL transaltion
\r
244 /// <param name="builderContext"></param>
\r
245 protected virtual void PrepareSqlOperands(BuilderContext builderContext)
\r
247 ProcessExpressions(PrequelAnalyzer.Analyze, true, builderContext);
\r
251 /// Processes all expressions in query, with the option to process only SQL targetting expressions
\r
252 /// This method is generic, it receives a delegate which does the real processing
\r
254 /// <param name="processor"></param>
\r
255 /// <param name="processOnlySqlParts"></param>
\r
256 /// <param name="builderContext"></param>
\r
257 protected virtual void ProcessExpressions(Func<Expression, BuilderContext, Expression> processor,
\r
258 bool processOnlySqlParts, BuilderContext builderContext)
\r
260 for (int scopeExpressionIndex = 0; scopeExpressionIndex < builderContext.SelectExpressions.Count; scopeExpressionIndex++)
\r
262 // no need to process the select itself here, all ScopeExpressions that are operands are processed as operands
\r
263 // and the main ScopeExpression (the SELECT) is processed below
\r
264 var scopeExpression = builderContext.SelectExpressions[scopeExpressionIndex];
\r
267 List<int> whereToRemove = new List<int>(); // List of where clausole evaluating TRUE (could be ignored and so removed)
\r
268 bool falseWhere = false; // true when the full where evaluate to FALSE
\r
269 for (int whereIndex = 0; whereIndex < scopeExpression.Where.Count; whereIndex++)
\r
271 Expression whereClausole = processor(scopeExpression.Where[whereIndex], builderContext);
\r
272 ConstantExpression constantWhereClausole = whereClausole as ConstantExpression;
\r
273 if (constantWhereClausole != null)
\r
275 if (constantWhereClausole.Value.Equals(false))
\r
280 else if (constantWhereClausole.Value.Equals(true))
\r
282 whereToRemove.Add(whereIndex);
\r
286 scopeExpression.Where[whereIndex] = whereClausole;
\r
288 if (scopeExpression.Where.Count > 0)
\r
292 scopeExpression.Where.Clear();
\r
293 scopeExpression.Where.Add(Expression.Equal(Expression.Constant(true), Expression.Constant(false)));
\r
296 foreach (int whereIndex in whereToRemove)
\r
297 scopeExpression.Where.RemoveAt(whereIndex);
\r
301 if (scopeExpression.Offset != null)
\r
302 scopeExpression.Offset = processor(scopeExpression.Offset, builderContext);
\r
303 if (scopeExpression.Limit != null)
\r
304 scopeExpression.Limit = processor(scopeExpression.Limit, builderContext);
\r
305 if (scopeExpression.OffsetAndLimit != null)
\r
306 scopeExpression.OffsetAndLimit = processor(scopeExpression.OffsetAndLimit, builderContext);
\r
308 builderContext.SelectExpressions[scopeExpressionIndex] = scopeExpression;
\r
310 // now process the main SELECT
\r
311 if (processOnlySqlParts)
\r
313 // if we process only the SQL Parts, these are the operands
\r
314 var newOperands = new List<Expression>();
\r
315 foreach (var operand in builderContext.CurrentSelect.Operands)
\r
316 newOperands.Add(processor(operand, builderContext));
\r
317 builderContext.CurrentSelect = builderContext.CurrentSelect.ChangeOperands(newOperands);
\r
321 // the output parameters and result builder
\r
322 builderContext.CurrentSelect = (SelectExpression)processor(builderContext.CurrentSelect, builderContext);
\r
327 /// Optimizes the query by optimizing subexpressions, and preparsing constant expressions
\r
329 /// <param name="builderContext"></param>
\r
330 protected virtual void OptimizeQuery(BuilderContext builderContext)
\r
332 ProcessExpressions(ExpressionOptimizer.Optimize, false, builderContext);
\r
335 protected virtual SelectQuery BuildSqlQuery(ExpressionQuery expressionQuery, QueryContext queryContext)
\r
337 var sql = SqlBuilder.BuildSelect(expressionQuery, queryContext);
\r
338 var sqlQuery = new SelectQuery(queryContext.DataContext, sql, expressionQuery.Parameters, expressionQuery.RowObjectCreator, expressionQuery.Select.ExecuteMethodName);
\r
342 private static IQueryCache queryCache;
\r
343 protected IQueryCache QueryCache
\r
347 if (queryCache == null)
\r
348 queryCache = ObjectFactory.Get<IQueryCache>();
\r
353 protected virtual SelectQuery GetFromSelectCache(ExpressionChain expressions)
\r
355 var cache = QueryCache;
\r
356 return cache.GetFromSelectCache(expressions);
\r
359 protected virtual void SetInSelectCache(ExpressionChain expressions, SelectQuery sqlSelectQuery)
\r
361 var cache = QueryCache;
\r
362 cache.SetInSelectCache(expressions, sqlSelectQuery);
\r
365 protected virtual Delegate GetFromTableReaderCache(Type tableType, IList<string> columns)
\r
367 var cache = QueryCache;
\r
368 return cache.GetFromTableReaderCache(tableType, columns);
\r
371 protected virtual void SetInTableReaderCache(Type tableType, IList<string> columns, Delegate tableReader)
\r
373 var cache = queryCache;
\r
374 cache.SetInTableReaderCache(tableType, columns, tableReader);
\r
378 /// Main entry point for the class. Builds or retrive from cache a SQL query corresponding to given Expressions
\r
380 /// <param name="expressions"></param>
\r
381 /// <param name="queryContext"></param>
\r
382 /// <returns></returns>
\r
383 public SelectQuery GetSelectQuery(ExpressionChain expressions, QueryContext queryContext)
\r
385 SelectQuery query = null;
\r
386 if (queryContext.DataContext.QueryCacheEnabled)
\r
388 query = GetFromSelectCache(expressions);
\r
392 Profiler.At("START: GetSelectQuery(), building Expression query");
\r
393 var expressionsQuery = BuildExpressionQuery(expressions, queryContext);
\r
394 Profiler.At("END: GetSelectQuery(), building Expression query");
\r
396 Profiler.At("START: GetSelectQuery(), building Sql query");
\r
397 query = BuildSqlQuery(expressionsQuery, queryContext);
\r
398 Profiler.At("END: GetSelectQuery(), building Sql query");
\r
400 if (queryContext.DataContext.QueryCacheEnabled)
\r
402 SetInSelectCache(expressions, query);
\r
405 else if (query.InputParameters.Count > 0)
\r
407 Profiler.At("START: GetSelectQuery(), building Expression parameters of cached query");
\r
408 var parameters = BuildExpressionParameters(expressions, queryContext);
\r
409 query = new SelectQuery(queryContext.DataContext, query.Sql, parameters, query.RowObjectCreator, query.ExecuteMethodName);
\r
410 Profiler.At("END: GetSelectQuery(), building Expression parameters of cached query");
\r
415 IList<InputParameterExpression> BuildExpressionParameters(ExpressionChain expressions, QueryContext queryContext)
\r
417 var builderContext = new BuilderContext(queryContext);
\r
418 var previousExpression = ExpressionDispatcher.CreateTableExpression(expressions.Expressions[0], builderContext);
\r
419 previousExpression = BuildExpressionQuery(expressions, previousExpression, builderContext);
\r
420 BuildOffsetsAndLimits(builderContext);
\r
421 // then prepare Parts for SQL translation
\r
422 PrepareSqlOperands(builderContext);
\r
423 return builderContext.ExpressionQuery.Parameters;
\r
427 /// Returns a Delegate to create a row for a given IDataRecord
\r
428 /// The Delegate is Func<IDataRecord,MappingContext,"tableType">
\r
430 /// <param name="tableType">The table type (must be managed by DataContext)</param>
\r
431 /// <param name="parameters"></param>
\r
432 /// <param name="queryContext"></param>
\r
433 /// <returns></returns>
\r
434 public virtual Delegate GetTableReader(Type tableType, IList<string> parameters, QueryContext queryContext)
\r
436 Delegate reader = null;
\r
437 if (queryContext.DataContext.QueryCacheEnabled)
\r
439 reader = GetFromTableReaderCache(tableType, parameters);
\r
441 if (reader == null)
\r
443 var lambda = ExpressionDispatcher.BuildTableReader(tableType, parameters,
\r
444 new BuilderContext(queryContext));
\r
445 reader = lambda.Compile();
\r
446 if (queryContext.DataContext.QueryCacheEnabled)
\r
448 SetInTableReaderCache(tableType, parameters, reader);
\r
454 private static readonly Regex parameterIdentifierEx = new Regex(@"\{(?<var>[\d.]+)\}", RegexOptions.Singleline | RegexOptions.ExplicitCapture | RegexOptions.Compiled);
\r
457 /// Converts a direct SQL query to a safe query with named parameters
\r
459 /// <param name="sql">Raw SQL query</param>
\r
460 /// <param name="queryContext"></param>
\r
461 /// <returns></returns>
\r
462 public virtual DirectQuery GetDirectQuery(string sql, QueryContext queryContext)
\r
465 var safeSql = queryContext.DataContext.Vendor.SqlProvider.GetSafeQuery(sql);
\r
466 var parameters = new List<string>();
\r
467 var parameterizedSql = parameterIdentifierEx.Replace(safeSql, delegate(Match e)
\r
469 var field = e.Groups[1].Value;
\r
470 var parameterIndex = int.Parse(field);
\r
471 while (parameters.Count <= parameterIndex)
\r
472 parameters.Add(string.Empty);
\r
473 var literalParameterName =
\r
474 queryContext.DataContext.Vendor.SqlProvider.GetParameterName(string.Format("p{0}", parameterIndex));
\r
475 parameters[parameterIndex] = literalParameterName;
\r
476 return literalParameterName;
\r
478 return new DirectQuery(queryContext.DataContext, parameterizedSql, parameters);
\r