File: SqlBuilder.cs

package info (click to toggle)
mono 4.6.2.7+dfsg-1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 778,148 kB
  • ctags: 914,052
  • sloc: cs: 5,779,509; xml: 2,773,713; ansic: 432,645; sh: 14,749; makefile: 12,361; perl: 2,488; python: 1,434; cpp: 849; asm: 531; sql: 95; sed: 16; php: 1
file content (499 lines) | stat: -rw-r--r-- 23,911 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
#region MIT license
// 
// MIT license
//
// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
// 
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.
// 
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

using DbLinq.Data.Linq.Sql;
using DbLinq.Data.Linq.Sugar.ExpressionMutator;
using DbLinq.Data.Linq.Sugar.Expressions;

using DbLinq.Factory;
using DbLinq.Util;

namespace DbLinq.Data.Linq.Sugar.Implementation
{
    internal class SqlBuilder : ISqlBuilder
    {
        public IExpressionQualifier ExpressionQualifier { get; set; }

        public SqlBuilder()
        {
            ExpressionQualifier = ObjectFactory.Get<IExpressionQualifier>();
        }

        /// <summary>
        /// Builds a SQL string, based on a QueryContext
        /// The build indirectly depends on ISqlProvider which provides all SQL Parts.
        /// </summary>
        /// <param name="expressionQuery"></param>
        /// <param name="queryContext"></param>
        /// <returns></returns>
        public SqlStatement BuildSelect(ExpressionQuery expressionQuery, QueryContext queryContext)
        {
            return Build(expressionQuery.Select, queryContext);
        }

        /// <summary>
        /// Returns a list of sorted tables, given a select expression.
        /// The tables are sorted by dependency: independent tables first, dependent tables next
        /// </summary>
        /// <param name="selectExpression"></param>
        /// <returns></returns>
        protected IList<TableExpression> GetSortedTables(SelectExpression selectExpression)
        {
            var tables = new List<TableExpression>();
            foreach (var table in selectExpression.Tables)
            {
                // the rules are:
                // a table climbs up to 0 until we find the table it depends on
                // we keep the index and insert on it
                // we place joining tables under joined tables
                int tableIndex;
                for (tableIndex = tables.Count; tableIndex > 0; tableIndex--)
                {
                    // above us, the joined table? Stop now
                    if (tables[tableIndex - 1] == table.JoinedTable)
                        break;
                    // if the current table is joining and we have a non-joining table above, we stop here too
                    if (table.JoinExpression != null && tables[tableIndex - 1].JoinExpression == null)
                        break;
                }
                tables.Insert(tableIndex, table);
            }
            return tables;
        }

        /// <summary>
        /// Main SQL builder
        /// </summary>
        /// <param name="selectExpression"></param>
        /// <param name="queryContext"></param>
        /// <returns></returns>
        public SqlStatement Build(SelectExpression selectExpression, QueryContext queryContext)
        {
            var translator = GetTranslator(queryContext.DataContext.Vendor.SqlProvider);
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            selectExpression = translator.OuterExpression(selectExpression);

            // A scope usually has:
            // - a SELECT: the operation creating a CLR object with data coming from SQL tier
            // - a FROM: list of tables
            // - a WHERE: list of conditions
            // - a GROUP BY: grouping by selected columns
            // - a ORDER BY: sort
            var select = BuildSelect(selectExpression, queryContext);
            if (select.ToString() == string.Empty)
            {
                SubSelectExpression subselect = null;
                if (selectExpression.Tables.Count == 1)
                    subselect = selectExpression.Tables[0] as SubSelectExpression;
                if(subselect != null)
                    return sqlProvider.GetParenthesis(Build(subselect.Select, queryContext));
            }

            // TODO: the following might be wrong (at least this might be the wrong place to do this
            if (select.ToString() == string.Empty)
                select = new SqlStatement("SELECT " + sqlProvider.GetLiteral(null) + " AS " + sqlProvider.GetSafeName("Empty"));

            var tables = GetSortedTables(selectExpression);
            var from = BuildFrom(tables, queryContext);
            var join = BuildJoin(tables, queryContext);
            var where = BuildWhere(tables, selectExpression.Where, queryContext);
            var groupBy = BuildGroupBy(selectExpression.Group, queryContext);
            var having = BuildHaving(selectExpression.Where, queryContext);
            var orderBy = BuildOrderBy(selectExpression.OrderBy, queryContext);
            select = Join(queryContext, select, from, join, where, groupBy, having, orderBy);
            select = BuildLimit(selectExpression, select, queryContext);

            if (selectExpression.NextSelectExpression != null)
            {
                var nextLiteralSelect = Build(selectExpression.NextSelectExpression, queryContext);
                select = queryContext.DataContext.Vendor.SqlProvider.GetLiteral(
                    selectExpression.NextSelectExpressionOperator,
                    select, nextLiteralSelect);
            }

            return select;
        }

        public SqlStatement Join(QueryContext queryContext, params SqlStatement[] clauses)
        {
            return SqlStatement.Join(queryContext.DataContext.Vendor.SqlProvider.NewLine,
                               (from clause in clauses where clause.ToString() != string.Empty select clause).ToList());
        }

        /// <summary>
        /// The simple part: converts an expression to SQL
        /// This is not used for FROM clause
        /// </summary>
        /// <param name="expression"></param>
        /// <param name="queryContext"></param>
        /// <returns></returns>
        protected virtual SqlStatement BuildExpression(Expression expression, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var currentPrecedence = ExpressionQualifier.GetPrecedence(expression);
            // first convert operands
            var operands = expression.GetOperands();
            var literalOperands = new List<SqlStatement>();
            foreach (var operand in operands)
            {
                var operandPrecedence = ExpressionQualifier.GetPrecedence(operand);
                var literalOperand = BuildExpression(operand, queryContext);
                if (operandPrecedence > currentPrecedence)
                    literalOperand = sqlProvider.GetParenthesis(literalOperand);
                literalOperands.Add(literalOperand);
            }

            // then converts expression
            if (expression is SpecialExpression)
                return sqlProvider.GetLiteral(((SpecialExpression)expression).SpecialNodeType, literalOperands);
            if (expression is EntitySetExpression)
                expression = ((EntitySetExpression)expression).TableExpression;
            if (expression is TableExpression)
            {
                var tableExpression = (TableExpression)expression;
                if (tableExpression.Alias != null) // if we have an alias, use it
                {
                    return sqlProvider.GetColumn(sqlProvider.GetTableAlias(tableExpression.Alias),
                                                 sqlProvider.GetColumns());
                }
                return sqlProvider.GetColumns();
            }
            if (expression is ColumnExpression)
            {
                var columnExpression = (ColumnExpression)expression;
                if (columnExpression.Table.Alias != null)
                {
                    return sqlProvider.GetColumn(sqlProvider.GetTableAlias(columnExpression.Table.Alias),
                                                 columnExpression.Name);
                }
                return sqlProvider.GetColumn(columnExpression.Name);
            }
            if (expression is InputParameterExpression)
            {
                var inputParameterExpression = (InputParameterExpression)expression;
                if (expression.Type.IsArray)
                {
                    int i = 0;
                    List<SqlStatement> inputParameters = new List<SqlStatement>();
                    foreach (object p in (Array)inputParameterExpression.GetValue())
                    {
                        inputParameters.Add(new SqlStatement(new SqlParameterPart(sqlProvider.GetParameterName(inputParameterExpression.Alias + i.ToString()),
                                                          inputParameterExpression.Alias + i.ToString())));
                        ++i;
                    }
                    return new SqlStatement(sqlProvider.GetLiteral(inputParameters.ToArray()));
                }
                return
                    new SqlStatement(new SqlParameterPart(sqlProvider.GetParameterName(inputParameterExpression.Alias),
                                                          inputParameterExpression.Alias));
            }
            if (expression is SelectExpression)
                return Build((SelectExpression)expression, queryContext);
            if (expression is ConstantExpression)
                return sqlProvider.GetLiteral(((ConstantExpression)expression).Value);
            if (expression is GroupExpression)
                return BuildExpression(((GroupExpression)expression).GroupedExpression, queryContext);

            StartIndexOffsetExpression indexExpression = expression as StartIndexOffsetExpression;
            if (indexExpression!=null)
            {
                if (indexExpression.StartsAtOne)
                {
                    literalOperands.Add(BuildExpression(Expression.Constant(1), queryContext));
                    return sqlProvider.GetLiteral(ExpressionType.Add, literalOperands);
                }
                else
                    return literalOperands.First();
            }
            if (expression.NodeType == ExpressionType.Convert || expression.NodeType == ExpressionType.ConvertChecked)
            {
                var unaryExpression = (UnaryExpression)expression;
                var firstOperand = literalOperands.First();
                if (IsConversionRequired(unaryExpression))
                    return sqlProvider.GetLiteralConvert(firstOperand, unaryExpression.Type);
                return firstOperand;
            }
            return sqlProvider.GetLiteral(expression.NodeType, literalOperands);
        }

        private Expressions.ExpressionTranslator GetTranslator(DbLinq.Vendor.ISqlProvider provider)
        {
            var p = provider as DbLinq.Vendor.Implementation.SqlProvider;
            if (p != null)
                return p.GetTranslator();
            return new ExpressionTranslator();
        }

        /// <summary>
        /// Determines if a SQL conversion is required
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        private bool IsConversionRequired(UnaryExpression expression)
        {
            // obvious (and probably never happens), conversion to the same type
            if (expression.Type == expression.Operand.Type)
                return false;
            // second, nullable to non-nullable for the same type
            if (expression.Type.IsNullable() && !expression.Operand.Type.IsNullable())
            {
                if (expression.Type.GetNullableType() == expression.Operand.Type)
                    return false;
            }
            // third, non-nullable to nullable
            if (!expression.Type.IsNullable() && expression.Operand.Type.IsNullable())
            {
                if (expression.Type == expression.Operand.Type.GetNullableType())
                    return false;
            }
            // found no excuse not to convert? then convert
            return true;
        }

        protected virtual bool MustDeclareAsJoin(IList<TableExpression> tables, TableExpression table)
        {
            // the first table can not be declared as join
            if (table == tables[0])
                return false;
            // we must declare as join, whatever the join is,
            // if some of the registered tables are registered as complex join
            if (tables.Any(t => t.JoinType != TableJoinType.Inner))
                return table.JoinExpression != null;
            return false;
        }

        protected virtual SqlStatement BuildFrom(IList<TableExpression> tables, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var fromClauses = new List<SqlStatement>();
            foreach (var tableExpression in tables)
            {
                if (!MustDeclareAsJoin(tables, tableExpression))
                {
                    if (tableExpression.Alias != null)
                    {
                        string tableAlias;

                        // All subqueries has an alias in FROM
                        SubSelectExpression subquery = tableExpression as SubSelectExpression;
                        if (subquery == null)
                            tableAlias = sqlProvider.GetTableAsAlias(tableExpression.Name, tableExpression.Alias);
                        else
                        {
                            var subqueryStatements = new SqlStatement(Build(subquery.Select, queryContext));
                            tableAlias = sqlProvider.GetSubQueryAsAlias(subqueryStatements.ToString(), tableExpression.Alias);
                        }

                        if ((tableExpression.JoinType & TableJoinType.LeftOuter) != 0)
                            tableAlias = "/* LEFT OUTER */ " + tableAlias;
                        if ((tableExpression.JoinType & TableJoinType.RightOuter) != 0)
                            tableAlias = "/* RIGHT OUTER */ " + tableAlias;
                        fromClauses.Add(tableAlias);
                    }
                    else
                    {
                        fromClauses.Add(sqlProvider.GetTable(tableExpression.Name));
                    }
                }
            }
            return sqlProvider.GetFromClause(fromClauses.ToArray());
        }

        /// <summary>
        /// Builds join clauses
        /// </summary>
        /// <param name="tables"></param>
        /// <param name="queryContext"></param>
        /// <returns></returns>
        protected virtual SqlStatement BuildJoin(IList<TableExpression> tables, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var joinClauses = new List<SqlStatement>();
            foreach (var tableExpression in tables)
            {
                // this is the pending declaration of direct tables
                if (MustDeclareAsJoin(tables, tableExpression))
                {
                    // get constitutive Parts
                    var joinExpression = BuildExpression(tableExpression.JoinExpression, queryContext);
                    var tableAlias = sqlProvider.GetTableAsAlias(tableExpression.Name, tableExpression.Alias);
                    SqlStatement joinClause;
                    switch (tableExpression.JoinType)
                    {
                        case TableJoinType.Inner:
                            joinClause = sqlProvider.GetInnerJoinClause(tableAlias, joinExpression);
                            break;
                        case TableJoinType.LeftOuter:
                            joinClause = sqlProvider.GetLeftOuterJoinClause(tableAlias, joinExpression);
                            break;
                        case TableJoinType.RightOuter:
                            joinClause = sqlProvider.GetRightOuterJoinClause(tableAlias, joinExpression);
                            break;
                        case TableJoinType.FullOuter:
                            throw new NotImplementedException();
                        default:
                            throw new ArgumentOutOfRangeException();
                    }
                    joinClauses.Add(joinClause);
                }
            }
            return sqlProvider.GetJoinClauses(joinClauses.ToArray());
        }

        protected virtual bool IsHavingClause(Expression expression)
        {
            bool isHaving = false;
            expression.Recurse(delegate(Expression e)
                                   {
                                       if (e is GroupExpression)
                                           isHaving = true;
                                       return e;
                                   });
            return isHaving;
        }

        protected virtual SqlStatement BuildWhere(IList<TableExpression> tables, IList<Expression> wheres, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var whereClauses = new List<SqlStatement>();
            foreach (var tableExpression in tables)
            {
                if (!MustDeclareAsJoin(tables, tableExpression) && tableExpression.JoinExpression != null)
                    whereClauses.Add(BuildExpression(tableExpression.JoinExpression, queryContext));
            }
            foreach (var whereExpression in wheres)
            {
                if (!IsHavingClause(whereExpression))
                    whereClauses.Add(BuildExpression(whereExpression, queryContext));
            }
            return sqlProvider.GetWhereClause(whereClauses.ToArray());
        }

        protected virtual SqlStatement BuildHaving(IList<Expression> wheres, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var havingClauses = new List<SqlStatement>();
            foreach (var whereExpression in wheres)
            {
                if (IsHavingClause(whereExpression))
                    havingClauses.Add(BuildExpression(whereExpression, queryContext));
            }
            return sqlProvider.GetHavingClause(havingClauses.ToArray());
        }

        protected virtual SqlStatement GetGroupByClause(ColumnExpression columnExpression, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            if (columnExpression.Table.Alias != null)
            {
                return sqlProvider.GetColumn(sqlProvider.GetTableAlias(columnExpression.Table.Alias),
                                             columnExpression.Name);
            }
            return sqlProvider.GetColumn(columnExpression.Name);
        }

        protected virtual SqlStatement BuildGroupBy(IList<GroupExpression> groupByExpressions, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var groupByClauses = new List<SqlStatement>();
            foreach (var groupByExpression in groupByExpressions)
            {
                foreach (var operand in groupByExpression.Clauses)
                {
                    var columnOperand = operand as ColumnExpression;
                    if (columnOperand == null)
                        throw Error.BadArgument("S0201: Groupby argument must be a ColumnExpression");
                    groupByClauses.Add(GetGroupByClause(columnOperand, queryContext));
                }
            }
            return sqlProvider.GetGroupByClause(groupByClauses.ToArray());
        }

        protected virtual SqlStatement BuildOrderBy(IList<OrderByExpression> orderByExpressions, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var orderByClauses = new List<SqlStatement>();
            foreach (var clause in orderByExpressions)
            {
                orderByClauses.Add(sqlProvider.GetOrderByColumn(BuildExpression(clause.ColumnExpression, queryContext),
                                                                clause.Descending));
            }
            return sqlProvider.GetOrderByClause(orderByClauses.ToArray());
        }

        protected virtual SqlStatement BuildSelect(Expression select, QueryContext queryContext)
        {
            var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
            var selectClauses = new List<SqlStatement>();
            foreach (var selectExpression in select.GetOperands())
            {
                var expressionString = BuildExpression(selectExpression, queryContext);
                if (selectExpression is SelectExpression)
                    selectClauses.Add(sqlProvider.GetParenthesis(expressionString));
                else
                    selectClauses.Add(expressionString);
            }
            SelectExpression selectExp = select as SelectExpression;
            if (selectExp != null)
            {
                if (selectExp.Group.Count == 1 && selectExp.Group[0].GroupedExpression == selectExp.Group[0].KeyExpression)
                {
                    // this is a select DISTINCT expression
                    // TODO: better handle selected columns on DISTINCT: I suspect this will not work in some cases
                    if (selectClauses.Count == 0)
                    {
                        selectClauses.Add(sqlProvider.GetColumns());
                    }
                    return sqlProvider.GetSelectDistinctClause(selectClauses.ToArray());
                }
            }
            return sqlProvider.GetSelectClause(selectClauses.ToArray());
        }

        protected virtual SqlStatement BuildLimit(SelectExpression select, SqlStatement literalSelect, QueryContext queryContext)
        {
            if (select.Limit != null)
            {
                var literalLimit = BuildExpression(select.Limit, queryContext);
                if (select.Offset != null)
                {
                    var literalOffset = BuildExpression(select.Offset, queryContext);
                    var literalOffsetAndLimit = BuildExpression(select.OffsetAndLimit, queryContext);
                    return queryContext.DataContext.Vendor.SqlProvider.GetLiteralLimit(literalSelect, literalLimit,
                                                                                       literalOffset,
                                                                                       literalOffsetAndLimit);
                }
                return queryContext.DataContext.Vendor.SqlProvider.GetLiteralLimit(literalSelect, literalLimit);
            }
            return literalSelect;
        }
    }
}