|
0
|
1 using System;
|
|
|
2 using System.Collections;
|
|
|
3 using System.Collections.Generic;
|
|
|
4 using System.Diagnostics;
|
|
|
5 using System.IO;
|
|
|
6 using System.Linq;
|
|
|
7 using System.Text;
|
|
|
8 using System.Threading;
|
|
|
9
|
|
|
10 using JetBrains.Annotations;
|
|
|
11
|
|
|
12 namespace BLToolkit.Data.Sql
|
|
|
13 {
|
|
|
14 using Reflection;
|
|
|
15
|
|
|
16 using FJoin = SqlQuery.FromClause.Join;
|
|
|
17
|
|
|
18 [DebuggerDisplay("SQL = {SqlText}")]
|
|
|
19 public class SqlQuery : ISqlTableSource
|
|
|
20 {
|
|
|
21 #region Init
|
|
|
22
|
|
|
23 static readonly Dictionary<string,object> _reservedWords = new Dictionary<string,object>();
|
|
|
24
|
|
|
25 static SqlQuery()
|
|
|
26 {
|
|
|
27 using (var stream = typeof(SqlQuery).Assembly.GetManifestResourceStream(typeof(SqlQuery), "ReservedWords.txt"))
|
|
|
28 using (var reader = new StreamReader(stream))
|
|
|
29 {
|
|
|
30 /*
|
|
|
31 var words = reader.ReadToEnd().Replace(' ', '\n').Replace('\t', '\n').Split('\n');
|
|
|
32 var q = from w in words where w.Length > 0 orderby w select w;
|
|
|
33
|
|
|
34 var text = string.Join("\n", q.Distinct().ToArray());
|
|
|
35 */
|
|
|
36
|
|
|
37 string s;
|
|
|
38 while ((s = reader.ReadLine()) != null)
|
|
|
39 _reservedWords.Add(s, s);
|
|
|
40 }
|
|
|
41 }
|
|
|
42
|
|
|
43 public SqlQuery()
|
|
|
44 {
|
|
|
45 SourceID = Interlocked.Increment(ref SourceIDCounter);
|
|
|
46
|
|
|
47 _select = new SelectClause (this);
|
|
|
48 _from = new FromClause (this);
|
|
|
49 _where = new WhereClause (this);
|
|
|
50 _groupBy = new GroupByClause(this);
|
|
|
51 _having = new WhereClause (this);
|
|
|
52 _orderBy = new OrderByClause(this);
|
|
|
53 }
|
|
|
54
|
|
|
55 internal SqlQuery(int id)
|
|
|
56 {
|
|
|
57 SourceID = id;
|
|
|
58 }
|
|
|
59
|
|
|
60 internal void Init(
|
|
|
61 InsertClause insert,
|
|
|
62 UpdateClause update,
|
|
|
63 DeleteClause delete,
|
|
|
64 SelectClause select,
|
|
|
65 FromClause from,
|
|
|
66 WhereClause where,
|
|
|
67 GroupByClause groupBy,
|
|
|
68 WhereClause having,
|
|
|
69 OrderByClause orderBy,
|
|
|
70 List<Union> unions,
|
|
|
71 SqlQuery parentSql,
|
|
|
72 bool parameterDependent,
|
|
|
73 List<SqlParameter> parameters)
|
|
|
74 {
|
|
|
75 _insert = insert;
|
|
|
76 _update = update;
|
|
|
77 _delete = delete;
|
|
|
78 _select = select;
|
|
|
79 _from = from;
|
|
|
80 _where = where;
|
|
|
81 _groupBy = groupBy;
|
|
|
82 _having = having;
|
|
|
83 _orderBy = orderBy;
|
|
|
84 _unions = unions;
|
|
|
85 ParentSql = parentSql;
|
|
|
86 IsParameterDependent = parameterDependent;
|
|
|
87 _parameters.AddRange(parameters);
|
|
|
88
|
|
|
89 foreach (var col in select.Columns)
|
|
|
90 col.Parent = this;
|
|
|
91
|
|
|
92 _select. SetSqlQuery(this);
|
|
|
93 _from. SetSqlQuery(this);
|
|
|
94 _where. SetSqlQuery(this);
|
|
|
95 _groupBy.SetSqlQuery(this);
|
|
|
96 _having. SetSqlQuery(this);
|
|
|
97 _orderBy.SetSqlQuery(this);
|
|
|
98 }
|
|
|
99
|
|
|
100 readonly List<SqlParameter> _parameters = new List<SqlParameter>();
|
|
|
101 public List<SqlParameter> Parameters
|
|
|
102 {
|
|
|
103 get { return _parameters; }
|
|
|
104 }
|
|
|
105
|
|
|
106 private List<object> _properties;
|
|
|
107 public List<object> Properties
|
|
|
108 {
|
|
|
109 get { return _properties ?? (_properties = new List<object>()); }
|
|
|
110 }
|
|
|
111
|
|
|
112 public bool IsParameterDependent { get; set; }
|
|
|
113 public SqlQuery ParentSql { get; set; }
|
|
|
114
|
|
|
115 public bool IsSimple
|
|
|
116 {
|
|
|
117 get { return !Select.HasModifier && Where.IsEmpty && GroupBy.IsEmpty && Having.IsEmpty && OrderBy.IsEmpty; }
|
|
|
118 }
|
|
|
119
|
|
|
120 private QueryType _queryType = QueryType.Select;
|
|
|
121 public QueryType QueryType
|
|
|
122 {
|
|
|
123 get { return _queryType; }
|
|
|
124 set { _queryType = value; }
|
|
|
125 }
|
|
|
126
|
|
|
127 public bool IsSelect { get { return _queryType == QueryType.Select; } }
|
|
|
128 public bool IsDelete { get { return _queryType == QueryType.Delete; } }
|
|
|
129 public bool IsInsertOrUpdate { get { return _queryType == QueryType.InsertOrUpdate; } }
|
|
|
130 public bool IsInsert { get { return _queryType == QueryType.Insert || _queryType == QueryType.InsertOrUpdate; } }
|
|
|
131 public bool IsUpdate { get { return _queryType == QueryType.Update || _queryType == QueryType.InsertOrUpdate; } }
|
|
|
132
|
|
|
133 #endregion
|
|
|
134
|
|
|
135 #region Column
|
|
|
136
|
|
|
137 public class Column : IEquatable<Column>, ISqlExpression, IChild<SqlQuery>
|
|
|
138 {
|
|
|
139 public Column(SqlQuery parent, ISqlExpression expression, string alias)
|
|
|
140 {
|
|
|
141 if (expression == null) throw new ArgumentNullException("expression");
|
|
|
142
|
|
|
143 Parent = parent;
|
|
|
144 Expression = expression;
|
|
|
145 _alias = alias;
|
|
|
146
|
|
|
147 #if DEBUG
|
|
|
148 _columnNumber = ++_columnCounter;
|
|
|
149 #endif
|
|
|
150 }
|
|
|
151
|
|
|
152 public Column(SqlQuery builder, ISqlExpression expression)
|
|
|
153 : this(builder, expression, null)
|
|
|
154 {
|
|
|
155 }
|
|
|
156
|
|
|
157 #if DEBUG
|
|
|
158 readonly int _columnNumber;
|
|
|
159 static int _columnCounter;
|
|
|
160 #endif
|
|
|
161
|
|
|
162 public ISqlExpression Expression { get; set; }
|
|
|
163
|
|
|
164 internal string _alias;
|
|
|
165 public string Alias
|
|
|
166 {
|
|
|
167 get
|
|
|
168 {
|
|
|
169 if (_alias == null)
|
|
|
170 {
|
|
|
171 if (Expression is SqlField)
|
|
|
172 {
|
|
|
173 var field = (SqlField)Expression;
|
|
|
174 return field.Alias ?? field.PhysicalName;
|
|
|
175 }
|
|
|
176
|
|
|
177 if (Expression is Column)
|
|
|
178 {
|
|
|
179 var col = (Column)Expression;
|
|
|
180 return col.Alias;
|
|
|
181 }
|
|
|
182 }
|
|
|
183
|
|
|
184 return _alias;
|
|
|
185 }
|
|
|
186 set { _alias = value; }
|
|
|
187 }
|
|
|
188
|
|
|
189 public bool Equals(Column other)
|
|
|
190 {
|
|
|
191 return Expression.Equals(other.Expression);
|
|
|
192 }
|
|
|
193
|
|
|
194 #if OVERRIDETOSTRING
|
|
|
195
|
|
|
196 public override string ToString()
|
|
|
197 {
|
|
|
198 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
199 }
|
|
|
200
|
|
|
201 #endif
|
|
|
202
|
|
|
203 #region ISqlExpression Members
|
|
|
204
|
|
|
205 public bool CanBeNull()
|
|
|
206 {
|
|
|
207 return Expression.CanBeNull();
|
|
|
208 }
|
|
|
209
|
|
|
210 public bool Equals(ISqlExpression other, Func<ISqlExpression,ISqlExpression,bool> comparer)
|
|
|
211 {
|
|
|
212 if (this == other)
|
|
|
213 return true;
|
|
|
214
|
|
|
215 return
|
|
|
216 other is Column &&
|
|
|
217 Expression.Equals(((Column)other).Expression, comparer) &&
|
|
|
218 comparer(this, other);
|
|
|
219 }
|
|
|
220
|
|
|
221 public int Precedence
|
|
|
222 {
|
|
|
223 get { return Sql.Precedence.Primary; }
|
|
|
224 }
|
|
|
225
|
|
|
226 public Type SystemType
|
|
|
227 {
|
|
|
228 get { return Expression.SystemType; }
|
|
|
229 }
|
|
|
230
|
|
|
231 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
232 {
|
|
|
233 if (!doClone(this))
|
|
|
234 return this;
|
|
|
235
|
|
|
236 ICloneableElement clone;
|
|
|
237
|
|
|
238 var parent = (SqlQuery)Parent.Clone(objectTree, doClone);
|
|
|
239
|
|
|
240 if (!objectTree.TryGetValue(this, out clone))
|
|
|
241 objectTree.Add(this, clone = new Column(
|
|
|
242 parent,
|
|
|
243 (ISqlExpression)Expression.Clone(objectTree, doClone),
|
|
|
244 _alias));
|
|
|
245
|
|
|
246 return clone;
|
|
|
247 }
|
|
|
248
|
|
|
249 #endregion
|
|
|
250
|
|
|
251 #region IEquatable<ISqlExpression> Members
|
|
|
252
|
|
|
253 bool IEquatable<ISqlExpression>.Equals(ISqlExpression other)
|
|
|
254 {
|
|
|
255 if (this == other)
|
|
|
256 return true;
|
|
|
257
|
|
|
258 return other is Column && Equals((Column)other);
|
|
|
259 }
|
|
|
260
|
|
|
261 #endregion
|
|
|
262
|
|
|
263 #region ISqlExpressionWalkable Members
|
|
|
264
|
|
|
265 [Obsolete]
|
|
|
266 public ISqlExpression Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
267 {
|
|
|
268 if (!(skipColumns && Expression is Column))
|
|
|
269 Expression = Expression.Walk(skipColumns, func);
|
|
|
270
|
|
|
271 return func(this);
|
|
|
272 }
|
|
|
273
|
|
|
274 #endregion
|
|
|
275
|
|
|
276 #region IChild<ISqlTableSource> Members
|
|
|
277
|
|
|
278 string IChild<SqlQuery>.Name
|
|
|
279 {
|
|
|
280 get { return Alias; }
|
|
|
281 }
|
|
|
282
|
|
|
283 public SqlQuery Parent { get; set; }
|
|
|
284
|
|
|
285 #endregion
|
|
|
286
|
|
|
287 #region IQueryElement Members
|
|
|
288
|
|
|
289 public QueryElementType ElementType { get { return QueryElementType.Column; } }
|
|
|
290
|
|
|
291 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
292 {
|
|
|
293 if (dic.ContainsKey(this))
|
|
|
294 return sb.Append("...");
|
|
|
295
|
|
|
296 dic.Add(this, this);
|
|
|
297
|
|
|
298 sb
|
|
|
299 .Append('t')
|
|
|
300 .Append(Parent.SourceID)
|
|
|
301 .Append(".");
|
|
|
302
|
|
|
303 #if DEBUG
|
|
|
304 sb.Append('[').Append(_columnNumber).Append(']');
|
|
|
305 #endif
|
|
|
306
|
|
|
307 if (Expression is SqlQuery)
|
|
|
308 {
|
|
|
309 sb
|
|
|
310 .Append("(\n\t\t");
|
|
|
311 var len = sb.Length;
|
|
|
312 Expression.ToString(sb, dic).Replace("\n", "\n\t\t", len, sb.Length - len);
|
|
|
313 sb.Append("\n\t)");
|
|
|
314 }
|
|
|
315 /*else if (Expression is Column)
|
|
|
316 {
|
|
|
317 var col = (Column)Expression;
|
|
|
318 sb
|
|
|
319 .Append("t")
|
|
|
320 .Append(col.Parent.SourceID)
|
|
|
321 .Append(".")
|
|
|
322 .Append(col.Alias ?? "c" + (col.Parent.Select.Columns.IndexOf(col) + 1));
|
|
|
323 }*/
|
|
|
324 else
|
|
|
325 {
|
|
|
326 Expression.ToString(sb, dic);
|
|
|
327 }
|
|
|
328
|
|
|
329 dic.Remove(this);
|
|
|
330
|
|
|
331 return sb;
|
|
|
332 }
|
|
|
333
|
|
|
334 #endregion
|
|
|
335 }
|
|
|
336
|
|
|
337 #endregion
|
|
|
338
|
|
|
339 #region TableSource
|
|
|
340
|
|
|
341 public class TableSource : ISqlTableSource
|
|
|
342 {
|
|
|
343 public TableSource(ISqlTableSource source, string alias)
|
|
|
344 : this(source, alias, null)
|
|
|
345 {
|
|
|
346 }
|
|
|
347
|
|
|
348 public TableSource(ISqlTableSource source, string alias, params JoinedTable[] joins)
|
|
|
349 {
|
|
|
350 if (source == null) throw new ArgumentNullException("source");
|
|
|
351
|
|
|
352 Source = source;
|
|
|
353 _alias = alias;
|
|
|
354
|
|
|
355 if (joins != null)
|
|
|
356 _joins.AddRange(joins);
|
|
|
357 }
|
|
|
358
|
|
|
359 public TableSource(ISqlTableSource source, string alias, IEnumerable<JoinedTable> joins)
|
|
|
360 {
|
|
|
361 if (source == null) throw new ArgumentNullException("source");
|
|
|
362
|
|
|
363 Source = source;
|
|
|
364 _alias = alias;
|
|
|
365
|
|
|
366 if (joins != null)
|
|
|
367 _joins.AddRange(joins);
|
|
|
368 }
|
|
|
369
|
|
|
370 public ISqlTableSource Source { get; set; }
|
|
|
371 public SqlTableType SqlTableType { get { return Source.SqlTableType; } }
|
|
|
372
|
|
|
373 internal string _alias;
|
|
|
374 public string Alias
|
|
|
375 {
|
|
|
376 get
|
|
|
377 {
|
|
|
378 if (string.IsNullOrEmpty(_alias))
|
|
|
379 {
|
|
|
380 if (Source is TableSource)
|
|
|
381 return (Source as TableSource).Alias;
|
|
|
382
|
|
|
383 if (Source is SqlTable)
|
|
|
384 return ((SqlTable)Source).Alias;
|
|
|
385 }
|
|
|
386
|
|
|
387 return _alias;
|
|
|
388 }
|
|
|
389 set { _alias = value; }
|
|
|
390 }
|
|
|
391
|
|
|
392 public TableSource this[ISqlTableSource table]
|
|
|
393 {
|
|
|
394 get { return this[table, null]; }
|
|
|
395 }
|
|
|
396
|
|
|
397 public TableSource this[ISqlTableSource table, string alias]
|
|
|
398 {
|
|
|
399 get
|
|
|
400 {
|
|
|
401 foreach (var tj in Joins)
|
|
|
402 {
|
|
|
403 var t = CheckTableSource(tj.Table, table, alias);
|
|
|
404
|
|
|
405 if (t != null)
|
|
|
406 return t;
|
|
|
407 }
|
|
|
408
|
|
|
409 return null;
|
|
|
410 }
|
|
|
411 }
|
|
|
412
|
|
|
413 readonly List<JoinedTable> _joins = new List<JoinedTable>();
|
|
|
414 public List<JoinedTable> Joins
|
|
|
415 {
|
|
|
416 get { return _joins; }
|
|
|
417 }
|
|
|
418
|
|
|
419 public void ForEach(Action<TableSource> action, HashSet<SqlQuery> visitedQueries)
|
|
|
420 {
|
|
|
421 action(this);
|
|
|
422 foreach (var join in Joins)
|
|
|
423 join.Table.ForEach(action, visitedQueries);
|
|
|
424
|
|
|
425 if (Source is SqlQuery && visitedQueries.Contains((SqlQuery)Source))
|
|
|
426 ((SqlQuery)Source).ForEachTable(action, visitedQueries);
|
|
|
427 }
|
|
|
428
|
|
|
429 public IEnumerable<ISqlTableSource> GetTables()
|
|
|
430 {
|
|
|
431 yield return Source;
|
|
|
432
|
|
|
433 foreach (var join in Joins)
|
|
|
434 foreach (var table in join.Table.GetTables())
|
|
|
435 yield return table;
|
|
|
436 }
|
|
|
437
|
|
|
438 public int GetJoinNumber()
|
|
|
439 {
|
|
|
440 var n = Joins.Count;
|
|
|
441
|
|
|
442 foreach (var join in Joins)
|
|
|
443 n += join.Table.GetJoinNumber();
|
|
|
444
|
|
|
445 return n;
|
|
|
446 }
|
|
|
447
|
|
|
448 #if OVERRIDETOSTRING
|
|
|
449
|
|
|
450 public override string ToString()
|
|
|
451 {
|
|
|
452 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
453 }
|
|
|
454
|
|
|
455 #endif
|
|
|
456
|
|
|
457 #region IEquatable<ISqlExpression> Members
|
|
|
458
|
|
|
459 bool IEquatable<ISqlExpression>.Equals(ISqlExpression other)
|
|
|
460 {
|
|
|
461 return this == other;
|
|
|
462 }
|
|
|
463
|
|
|
464 #endregion
|
|
|
465
|
|
|
466 #region ISqlExpressionWalkable Members
|
|
|
467
|
|
|
468 [Obsolete]
|
|
|
469 public ISqlExpression Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
470 {
|
|
|
471 Source = (ISqlTableSource)Source.Walk(skipColumns, func);
|
|
|
472
|
|
|
473 foreach (var t in Joins)
|
|
|
474 ((ISqlExpressionWalkable)t).Walk(skipColumns, func);
|
|
|
475
|
|
|
476 return this;
|
|
|
477 }
|
|
|
478
|
|
|
479 #endregion
|
|
|
480
|
|
|
481 #region ISqlTableSource Members
|
|
|
482
|
|
|
483 public int SourceID { get { return Source.SourceID; } }
|
|
|
484 public SqlField All { get { return Source.All; } }
|
|
|
485
|
|
|
486 IList<ISqlExpression> ISqlTableSource.GetKeys(bool allIfEmpty)
|
|
|
487 {
|
|
|
488 return Source.GetKeys(allIfEmpty);
|
|
|
489 }
|
|
|
490
|
|
|
491 #endregion
|
|
|
492
|
|
|
493 #region ICloneableElement Members
|
|
|
494
|
|
|
495 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
496 {
|
|
|
497 if (!doClone(this))
|
|
|
498 return this;
|
|
|
499
|
|
|
500 ICloneableElement clone;
|
|
|
501
|
|
|
502 if (!objectTree.TryGetValue(this, out clone))
|
|
|
503 {
|
|
|
504 var ts = new TableSource((ISqlTableSource)Source.Clone(objectTree, doClone), _alias);
|
|
|
505
|
|
|
506 objectTree.Add(this, clone = ts);
|
|
|
507
|
|
|
508 ts._joins.AddRange(_joins.ConvertAll(jt => (JoinedTable)jt.Clone(objectTree, doClone)));
|
|
|
509 }
|
|
|
510
|
|
|
511 return clone;
|
|
|
512 }
|
|
|
513
|
|
|
514 #endregion
|
|
|
515
|
|
|
516 #region IQueryElement Members
|
|
|
517
|
|
|
518 public QueryElementType ElementType { get { return QueryElementType.TableSource; } }
|
|
|
519
|
|
|
520 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
521 {
|
|
|
522 if (sb.Length > 500)
|
|
|
523 return sb;
|
|
|
524
|
|
|
525 if (dic.ContainsKey(this))
|
|
|
526 return sb.Append("...");
|
|
|
527
|
|
|
528 dic.Add(this, this);
|
|
|
529
|
|
|
530 if (Source is SqlQuery)
|
|
|
531 {
|
|
|
532 sb.Append("(\n\t");
|
|
|
533 var len = sb.Length;
|
|
|
534 Source.ToString(sb, dic).Replace("\n", "\n\t", len, sb.Length - len);
|
|
|
535 sb.Append("\n)");
|
|
|
536 }
|
|
|
537 else
|
|
|
538 Source.ToString(sb, dic);
|
|
|
539
|
|
|
540 sb
|
|
|
541 .Append(" as t")
|
|
|
542 .Append(SourceID);
|
|
|
543
|
|
|
544 foreach (IQueryElement join in Joins)
|
|
|
545 {
|
|
|
546 sb.AppendLine().Append('\t');
|
|
|
547 var len = sb.Length;
|
|
|
548 join.ToString(sb, dic).Replace("\n", "\n\t", len, sb.Length - len);
|
|
|
549 }
|
|
|
550
|
|
|
551 dic.Remove(this);
|
|
|
552
|
|
|
553 return sb;
|
|
|
554 }
|
|
|
555
|
|
|
556 #endregion
|
|
|
557
|
|
|
558 #region ISqlExpression Members
|
|
|
559
|
|
|
560 public bool CanBeNull()
|
|
|
561 {
|
|
|
562 return Source.CanBeNull();
|
|
|
563 }
|
|
|
564
|
|
|
565 public bool Equals(ISqlExpression other, Func<ISqlExpression,ISqlExpression,bool> comparer)
|
|
|
566 {
|
|
|
567 return this == other;
|
|
|
568 }
|
|
|
569
|
|
|
570 public int Precedence { get { return Source.Precedence; } }
|
|
|
571 public Type SystemType { get { return Source.SystemType; } }
|
|
|
572
|
|
|
573 #endregion
|
|
|
574 }
|
|
|
575
|
|
|
576 #endregion
|
|
|
577
|
|
|
578 #region TableJoin
|
|
|
579
|
|
|
580 public enum JoinType
|
|
|
581 {
|
|
|
582 Auto,
|
|
|
583 Inner,
|
|
|
584 Left,
|
|
|
585 CrossApply,
|
|
|
586 OuterApply
|
|
|
587 }
|
|
|
588
|
|
|
589 public class JoinedTable : IQueryElement, ISqlExpressionWalkable, ICloneableElement
|
|
|
590 {
|
|
|
591 public JoinedTable(JoinType joinType, TableSource table, bool isWeak, SearchCondition searchCondition)
|
|
|
592 {
|
|
|
593 JoinType = joinType;
|
|
|
594 Table = table;
|
|
|
595 IsWeak = isWeak;
|
|
|
596 Condition = searchCondition;
|
|
|
597 CanConvertApply = true;
|
|
|
598 }
|
|
|
599
|
|
|
600 public JoinedTable(JoinType joinType, TableSource table, bool isWeak)
|
|
|
601 : this(joinType, table, isWeak, new SearchCondition())
|
|
|
602 {
|
|
|
603 }
|
|
|
604
|
|
|
605 public JoinedTable(JoinType joinType, ISqlTableSource table, string alias, bool isWeak)
|
|
|
606 : this(joinType, new TableSource(table, alias), isWeak)
|
|
|
607 {
|
|
|
608 }
|
|
|
609
|
|
|
610 public JoinType JoinType { get; set; }
|
|
|
611 public TableSource Table { get; set; }
|
|
|
612 public SearchCondition Condition { get; private set; }
|
|
|
613 public bool IsWeak { get; set; }
|
|
|
614 public bool CanConvertApply { get; set; }
|
|
|
615
|
|
|
616 public ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
617 {
|
|
|
618 if (!doClone(this))
|
|
|
619 return this;
|
|
|
620
|
|
|
621 ICloneableElement clone;
|
|
|
622
|
|
|
623 if (!objectTree.TryGetValue(this, out clone))
|
|
|
624 objectTree.Add(this, clone = new JoinedTable(
|
|
|
625 JoinType,
|
|
|
626 (TableSource)Table.Clone(objectTree, doClone),
|
|
|
627 IsWeak,
|
|
|
628 (SearchCondition)Condition.Clone(objectTree, doClone)));
|
|
|
629
|
|
|
630 return clone;
|
|
|
631 }
|
|
|
632
|
|
|
633 #if OVERRIDETOSTRING
|
|
|
634
|
|
|
635 public override string ToString()
|
|
|
636 {
|
|
|
637 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
638 }
|
|
|
639
|
|
|
640 #endif
|
|
|
641
|
|
|
642 #region ISqlExpressionWalkable Members
|
|
|
643
|
|
|
644 [Obsolete]
|
|
|
645 public ISqlExpression Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> action)
|
|
|
646 {
|
|
|
647 Condition = (SearchCondition)((ISqlExpressionWalkable)Condition).Walk(skipColumns, action);
|
|
|
648
|
|
|
649 #pragma warning disable 0618
|
|
|
650 Table.Walk(skipColumns, action);
|
|
|
651 #pragma warning restore 0618
|
|
|
652
|
|
|
653 return null;
|
|
|
654 }
|
|
|
655
|
|
|
656 #endregion
|
|
|
657
|
|
|
658 #region IQueryElement Members
|
|
|
659
|
|
|
660 public QueryElementType ElementType { get { return QueryElementType.JoinedTable; } }
|
|
|
661
|
|
|
662 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
663 {
|
|
|
664 if (dic.ContainsKey(this))
|
|
|
665 return sb.Append("...");
|
|
|
666
|
|
|
667 dic.Add(this, this);
|
|
|
668
|
|
|
669 switch (JoinType)
|
|
|
670 {
|
|
|
671 case JoinType.Inner : sb.Append("INNER JOIN "); break;
|
|
|
672 case JoinType.Left : sb.Append("LEFT JOIN "); break;
|
|
|
673 case JoinType.CrossApply : sb.Append("CROSS APPLY "); break;
|
|
|
674 case JoinType.OuterApply : sb.Append("OUTER APPLY "); break;
|
|
|
675 default : sb.Append("SOME JOIN "); break;
|
|
|
676 }
|
|
|
677
|
|
|
678 ((IQueryElement)Table).ToString(sb, dic);
|
|
|
679 sb.Append(" ON ");
|
|
|
680 ((IQueryElement)Condition).ToString(sb, dic);
|
|
|
681
|
|
|
682 dic.Remove(this);
|
|
|
683
|
|
|
684 return sb;
|
|
|
685 }
|
|
|
686
|
|
|
687 #endregion
|
|
|
688 }
|
|
|
689
|
|
|
690 #endregion
|
|
|
691
|
|
|
692 #region Predicate
|
|
|
693
|
|
|
694 public abstract class Predicate : ISqlPredicate
|
|
|
695 {
|
|
|
696 public enum Operator
|
|
|
697 {
|
|
|
698 Equal, // = Is the operator used to test the equality between two expressions.
|
|
|
699 NotEqual, // <> != Is the operator used to test the condition of two expressions not being equal to each other.
|
|
|
700 Greater, // > Is the operator used to test the condition of one expression being greater than the other.
|
|
|
701 GreaterOrEqual, // >= Is the operator used to test the condition of one expression being greater than or equal to the other expression.
|
|
|
702 NotGreater, // !> Is the operator used to test the condition of one expression not being greater than the other expression.
|
|
|
703 Less, // < Is the operator used to test the condition of one expression being less than the other.
|
|
|
704 LessOrEqual, // <= Is the operator used to test the condition of one expression being less than or equal to the other expression.
|
|
|
705 NotLess // !< Is the operator used to test the condition of one expression not being less than the other expression.
|
|
|
706 }
|
|
|
707
|
|
|
708 public class Expr : Predicate
|
|
|
709 {
|
|
|
710 public Expr([NotNull] ISqlExpression exp1, int precedence)
|
|
|
711 : base(precedence)
|
|
|
712 {
|
|
|
713 if (exp1 == null) throw new ArgumentNullException("exp1");
|
|
|
714
|
|
|
715 Expr1 = exp1;
|
|
|
716 }
|
|
|
717
|
|
|
718 public Expr([NotNull] ISqlExpression exp1)
|
|
|
719 : base(exp1.Precedence)
|
|
|
720 {
|
|
|
721 if (exp1 == null) throw new ArgumentNullException("exp1");
|
|
|
722
|
|
|
723 Expr1 = exp1;
|
|
|
724 }
|
|
|
725
|
|
|
726 public ISqlExpression Expr1 { get; set; }
|
|
|
727
|
|
|
728 [Obsolete]
|
|
|
729 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
730 {
|
|
|
731 Expr1 = Expr1.Walk(skipColumns, func);
|
|
|
732
|
|
|
733 if (Expr1 == null)
|
|
|
734 throw new InvalidOperationException();
|
|
|
735 }
|
|
|
736
|
|
|
737 public override bool CanBeNull()
|
|
|
738 {
|
|
|
739 return Expr1.CanBeNull();
|
|
|
740 }
|
|
|
741
|
|
|
742 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
743 {
|
|
|
744 if (!doClone(this))
|
|
|
745 return this;
|
|
|
746
|
|
|
747 ICloneableElement clone;
|
|
|
748
|
|
|
749 if (!objectTree.TryGetValue(this, out clone))
|
|
|
750 objectTree.Add(this, clone = new Expr((ISqlExpression)Expr1.Clone(objectTree, doClone), Precedence));
|
|
|
751
|
|
|
752 return clone;
|
|
|
753 }
|
|
|
754
|
|
|
755 public override QueryElementType ElementType
|
|
|
756 {
|
|
|
757 get { return QueryElementType.ExprPredicate; }
|
|
|
758 }
|
|
|
759
|
|
|
760 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
761 {
|
|
|
762 Expr1.ToString(sb, dic);
|
|
|
763 }
|
|
|
764 }
|
|
|
765
|
|
|
766 public class NotExpr : Expr
|
|
|
767 {
|
|
|
768 public NotExpr(ISqlExpression exp1, bool isNot, int precedence)
|
|
|
769 : base(exp1, precedence)
|
|
|
770 {
|
|
|
771 IsNot = isNot;
|
|
|
772 }
|
|
|
773
|
|
|
774 public bool IsNot { get; set; }
|
|
|
775
|
|
|
776 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
777 {
|
|
|
778 if (!doClone(this))
|
|
|
779 return this;
|
|
|
780
|
|
|
781 ICloneableElement clone;
|
|
|
782
|
|
|
783 if (!objectTree.TryGetValue(this, out clone))
|
|
|
784 objectTree.Add(this, clone = new NotExpr((ISqlExpression)Expr1.Clone(objectTree, doClone), IsNot, Precedence));
|
|
|
785
|
|
|
786 return clone;
|
|
|
787 }
|
|
|
788
|
|
|
789 public override QueryElementType ElementType
|
|
|
790 {
|
|
|
791 get { return QueryElementType.NotExprPredicate; }
|
|
|
792 }
|
|
|
793
|
|
|
794 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
795 {
|
|
|
796 if (IsNot) sb.Append("NOT (");
|
|
|
797 base.ToString(sb, dic);
|
|
|
798 if (IsNot) sb.Append(")");
|
|
|
799 }
|
|
|
800 }
|
|
|
801
|
|
|
802 // { expression { = | <> | != | > | >= | ! > | < | <= | !< } expression
|
|
|
803 //
|
|
|
804 public class ExprExpr : Expr
|
|
|
805 {
|
|
|
806 public ExprExpr(ISqlExpression exp1, Operator op, ISqlExpression exp2)
|
|
|
807 : base(exp1, Sql.Precedence.Comparison)
|
|
|
808 {
|
|
|
809 this.Operator = op;
|
|
|
810 Expr2 = exp2;
|
|
|
811 }
|
|
|
812
|
|
|
813 public new Operator Operator { get; private set; }
|
|
|
814 public ISqlExpression Expr2 { get; internal set; }
|
|
|
815
|
|
|
816 [Obsolete]
|
|
|
817 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
818 {
|
|
|
819 #pragma warning disable 0618
|
|
|
820 base.Walk(skipColumns, func);
|
|
|
821 #pragma warning restore 0618
|
|
|
822 Expr2 = Expr2.Walk(skipColumns, func);
|
|
|
823 }
|
|
|
824
|
|
|
825 public override bool CanBeNull()
|
|
|
826 {
|
|
|
827 return base.CanBeNull() || Expr2.CanBeNull();
|
|
|
828 }
|
|
|
829
|
|
|
830 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
831 {
|
|
|
832 if (!doClone(this))
|
|
|
833 return this;
|
|
|
834
|
|
|
835 ICloneableElement clone;
|
|
|
836
|
|
|
837 if (!objectTree.TryGetValue(this, out clone))
|
|
|
838 objectTree.Add(this, clone = new ExprExpr(
|
|
|
839 (ISqlExpression)Expr1.Clone(objectTree, doClone), this.Operator, (ISqlExpression)Expr2.Clone(objectTree, doClone)));
|
|
|
840
|
|
|
841 return clone;
|
|
|
842 }
|
|
|
843
|
|
|
844 public override QueryElementType ElementType
|
|
|
845 {
|
|
|
846 get { return QueryElementType.ExprExprPredicate; }
|
|
|
847 }
|
|
|
848
|
|
|
849 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
850 {
|
|
|
851 Expr1.ToString(sb, dic);
|
|
|
852
|
|
|
853 string op;
|
|
|
854
|
|
|
855 switch (this.Operator)
|
|
|
856 {
|
|
|
857 case Operator.Equal : op = "="; break;
|
|
|
858 case Operator.NotEqual : op = "<>"; break;
|
|
|
859 case Operator.Greater : op = ">"; break;
|
|
|
860 case Operator.GreaterOrEqual: op = ">="; break;
|
|
|
861 case Operator.NotGreater : op = "!>"; break;
|
|
|
862 case Operator.Less : op = "<"; break;
|
|
|
863 case Operator.LessOrEqual : op = "<="; break;
|
|
|
864 case Operator.NotLess : op = "!<"; break;
|
|
|
865 default: throw new InvalidOperationException();
|
|
|
866 }
|
|
|
867
|
|
|
868 sb.Append(" ").Append(op).Append(" ");
|
|
|
869
|
|
|
870 Expr2.ToString(sb, dic);
|
|
|
871 }
|
|
|
872 }
|
|
|
873
|
|
|
874 // string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ]
|
|
|
875 //
|
|
|
876 public class Like : NotExpr
|
|
|
877 {
|
|
|
878 public Like(ISqlExpression exp1, bool isNot, ISqlExpression exp2, ISqlExpression escape)
|
|
|
879 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
880 {
|
|
|
881 Expr2 = exp2;
|
|
|
882 Escape = escape;
|
|
|
883 }
|
|
|
884
|
|
|
885 public ISqlExpression Expr2 { get; internal set; }
|
|
|
886 public ISqlExpression Escape { get; internal set; }
|
|
|
887
|
|
|
888 [Obsolete]
|
|
|
889 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
890 {
|
|
|
891 #pragma warning disable 0618
|
|
|
892 base.Walk(skipColumns, func);
|
|
|
893 #pragma warning restore 0618
|
|
|
894 Expr2 = Expr2.Walk(skipColumns, func);
|
|
|
895
|
|
|
896 if (Escape != null)
|
|
|
897 Escape = Escape.Walk(skipColumns, func);
|
|
|
898 }
|
|
|
899
|
|
|
900 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
901 {
|
|
|
902 if (!doClone(this))
|
|
|
903 return this;
|
|
|
904
|
|
|
905 ICloneableElement clone;
|
|
|
906
|
|
|
907 if (!objectTree.TryGetValue(this, out clone))
|
|
|
908 objectTree.Add(this, clone = new Like(
|
|
|
909 (ISqlExpression)Expr1.Clone(objectTree, doClone), IsNot, (ISqlExpression)Expr2.Clone(objectTree, doClone), Escape));
|
|
|
910
|
|
|
911 return clone;
|
|
|
912 }
|
|
|
913
|
|
|
914 public override QueryElementType ElementType
|
|
|
915 {
|
|
|
916 get { return QueryElementType.LikePredicate; }
|
|
|
917 }
|
|
|
918
|
|
|
919 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
920 {
|
|
|
921 Expr1.ToString(sb, dic);
|
|
|
922
|
|
|
923 if (IsNot) sb.Append(" NOT");
|
|
|
924 sb.Append(" LIKE ");
|
|
|
925
|
|
|
926 Expr2.ToString(sb, dic);
|
|
|
927
|
|
|
928 if (Escape != null)
|
|
|
929 {
|
|
|
930 sb.Append(" ESCAPE ");
|
|
|
931 Escape.ToString(sb, dic);
|
|
|
932 }
|
|
|
933 }
|
|
|
934 }
|
|
|
935
|
|
|
936 // expression [ NOT ] BETWEEN expression AND expression
|
|
|
937 //
|
|
|
938 public class Between : NotExpr
|
|
|
939 {
|
|
|
940 public Between(ISqlExpression exp1, bool isNot, ISqlExpression exp2, ISqlExpression exp3)
|
|
|
941 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
942 {
|
|
|
943 Expr2 = exp2;
|
|
|
944 Expr3 = exp3;
|
|
|
945 }
|
|
|
946
|
|
|
947 public ISqlExpression Expr2 { get; internal set; }
|
|
|
948 public ISqlExpression Expr3 { get; internal set; }
|
|
|
949
|
|
|
950 [Obsolete]
|
|
|
951 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
952 {
|
|
|
953 #pragma warning disable 0618
|
|
|
954 base.Walk(skipColumns, func);
|
|
|
955 #pragma warning restore 0618
|
|
|
956 Expr2 = Expr2.Walk(skipColumns, func);
|
|
|
957 Expr3 = Expr3.Walk(skipColumns, func);
|
|
|
958 }
|
|
|
959
|
|
|
960 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
961 {
|
|
|
962 if (!doClone(this))
|
|
|
963 return this;
|
|
|
964
|
|
|
965 ICloneableElement clone;
|
|
|
966
|
|
|
967 if (!objectTree.TryGetValue(this, out clone))
|
|
|
968 objectTree.Add(this, clone = new Between(
|
|
|
969 (ISqlExpression)Expr1.Clone(objectTree, doClone),
|
|
|
970 IsNot,
|
|
|
971 (ISqlExpression)Expr2.Clone(objectTree, doClone),
|
|
|
972 (ISqlExpression)Expr3.Clone(objectTree, doClone)));
|
|
|
973
|
|
|
974 return clone;
|
|
|
975 }
|
|
|
976
|
|
|
977 public override QueryElementType ElementType
|
|
|
978 {
|
|
|
979 get { return QueryElementType.BetweenPredicate; }
|
|
|
980 }
|
|
|
981
|
|
|
982 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
983 {
|
|
|
984 Expr1.ToString(sb, dic);
|
|
|
985
|
|
|
986 if (IsNot) sb.Append(" NOT");
|
|
|
987 sb.Append(" BETWEEN ");
|
|
|
988
|
|
|
989 Expr2.ToString(sb, dic);
|
|
|
990 sb.Append(" AND ");
|
|
|
991 Expr3.ToString(sb, dic);
|
|
|
992 }
|
|
|
993 }
|
|
|
994
|
|
|
995 // expression IS [ NOT ] NULL
|
|
|
996 //
|
|
|
997 public class IsNull : NotExpr
|
|
|
998 {
|
|
|
999 public IsNull(ISqlExpression exp1, bool isNot)
|
|
|
1000 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
1001 {
|
|
|
1002 }
|
|
|
1003
|
|
|
1004 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1005 {
|
|
|
1006 if (!doClone(this))
|
|
|
1007 return this;
|
|
|
1008
|
|
|
1009 ICloneableElement clone;
|
|
|
1010
|
|
|
1011 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1012 objectTree.Add(this, clone = new IsNull((ISqlExpression)Expr1.Clone(objectTree, doClone), IsNot));
|
|
|
1013
|
|
|
1014 return clone;
|
|
|
1015 }
|
|
|
1016
|
|
|
1017 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
1018 {
|
|
|
1019 Expr1.ToString(sb, dic);
|
|
|
1020 sb
|
|
|
1021 .Append(" IS ")
|
|
|
1022 .Append(IsNot ? "NOT " : "")
|
|
|
1023 .Append("NULL");
|
|
|
1024 }
|
|
|
1025
|
|
|
1026 public override QueryElementType ElementType
|
|
|
1027 {
|
|
|
1028 get { return QueryElementType.IsNullPredicate; }
|
|
|
1029 }
|
|
|
1030 }
|
|
|
1031
|
|
|
1032 // expression [ NOT ] IN ( subquery | expression [ ,...n ] )
|
|
|
1033 //
|
|
|
1034 public class InSubQuery : NotExpr
|
|
|
1035 {
|
|
|
1036 public InSubQuery(ISqlExpression exp1, bool isNot, SqlQuery subQuery)
|
|
|
1037 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
1038 {
|
|
|
1039 SubQuery = subQuery;
|
|
|
1040 }
|
|
|
1041
|
|
|
1042 public SqlQuery SubQuery { get; private set; }
|
|
|
1043
|
|
|
1044 [Obsolete]
|
|
|
1045 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
1046 {
|
|
|
1047 #pragma warning disable 0618
|
|
|
1048 base.Walk(skipColumns, func);
|
|
|
1049 #pragma warning restore 0618
|
|
|
1050 SubQuery = (SqlQuery)((ISqlExpression)SubQuery).Walk(skipColumns, func);
|
|
|
1051 }
|
|
|
1052
|
|
|
1053 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1054 {
|
|
|
1055 if (!doClone(this))
|
|
|
1056 return this;
|
|
|
1057
|
|
|
1058 ICloneableElement clone;
|
|
|
1059
|
|
|
1060 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1061 objectTree.Add(this, clone = new InSubQuery(
|
|
|
1062 (ISqlExpression)Expr1.Clone(objectTree, doClone),
|
|
|
1063 IsNot,
|
|
|
1064 (SqlQuery)SubQuery.Clone(objectTree, doClone)));
|
|
|
1065
|
|
|
1066 return clone;
|
|
|
1067 }
|
|
|
1068
|
|
|
1069 public override QueryElementType ElementType
|
|
|
1070 {
|
|
|
1071 get { return QueryElementType.InSubQueryPredicate; }
|
|
|
1072 }
|
|
|
1073
|
|
|
1074 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
1075 {
|
|
|
1076 Expr1.ToString(sb, dic);
|
|
|
1077
|
|
|
1078 if (IsNot) sb.Append(" NOT");
|
|
|
1079 sb.Append(" IN (");
|
|
|
1080
|
|
|
1081 ((IQueryElement)SubQuery).ToString(sb, dic);
|
|
|
1082 sb.Append(")");
|
|
|
1083 }
|
|
|
1084 }
|
|
|
1085
|
|
|
1086 public class InList : NotExpr
|
|
|
1087 {
|
|
|
1088 public InList(ISqlExpression exp1, bool isNot, params ISqlExpression[] values)
|
|
|
1089 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
1090 {
|
|
|
1091 if (values != null && values.Length > 0)
|
|
|
1092 _values.AddRange(values);
|
|
|
1093 }
|
|
|
1094
|
|
|
1095 public InList(ISqlExpression exp1, bool isNot, IEnumerable<ISqlExpression> values)
|
|
|
1096 : base(exp1, isNot, Sql.Precedence.Comparison)
|
|
|
1097 {
|
|
|
1098 if (values != null)
|
|
|
1099 _values.AddRange(values);
|
|
|
1100 }
|
|
|
1101
|
|
|
1102 readonly List<ISqlExpression> _values = new List<ISqlExpression>();
|
|
|
1103 public List<ISqlExpression> Values { get { return _values; } }
|
|
|
1104
|
|
|
1105 [Obsolete]
|
|
|
1106 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> action)
|
|
|
1107 {
|
|
|
1108 #pragma warning disable 0618
|
|
|
1109 base.Walk(skipColumns, action);
|
|
|
1110 #pragma warning restore 0618
|
|
|
1111 for (var i = 0; i < _values.Count; i++)
|
|
|
1112 _values[i] = _values[i].Walk(skipColumns, action);
|
|
|
1113 }
|
|
|
1114
|
|
|
1115 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1116 {
|
|
|
1117 if (!doClone(this))
|
|
|
1118 return this;
|
|
|
1119
|
|
|
1120 ICloneableElement clone;
|
|
|
1121
|
|
|
1122 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1123 {
|
|
|
1124 objectTree.Add(this, clone = new InList(
|
|
|
1125 (ISqlExpression)Expr1.Clone(objectTree, doClone),
|
|
|
1126 IsNot,
|
|
|
1127 _values.ConvertAll(e => (ISqlExpression)e.Clone(objectTree, doClone)).ToArray()));
|
|
|
1128 }
|
|
|
1129
|
|
|
1130 return clone;
|
|
|
1131 }
|
|
|
1132
|
|
|
1133 public override QueryElementType ElementType
|
|
|
1134 {
|
|
|
1135 get { return QueryElementType.InListPredicate; }
|
|
|
1136 }
|
|
|
1137
|
|
|
1138 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
1139 {
|
|
|
1140 Expr1.ToString(sb, dic);
|
|
|
1141
|
|
|
1142 if (IsNot) sb.Append(" NOT");
|
|
|
1143 sb.Append(" IN (");
|
|
|
1144
|
|
|
1145 foreach (var value in Values)
|
|
|
1146 {
|
|
|
1147 value.ToString(sb, dic);
|
|
|
1148 sb.Append(',');
|
|
|
1149 }
|
|
|
1150
|
|
|
1151 if (Values.Count > 0)
|
|
|
1152 sb.Length--;
|
|
|
1153
|
|
|
1154 sb.Append(")");
|
|
|
1155 }
|
|
|
1156 }
|
|
|
1157
|
|
|
1158 // CONTAINS ( { column | * } , '< contains_search_condition >' )
|
|
|
1159 // FREETEXT ( { column | * } , 'freetext_string' )
|
|
|
1160 // expression { = | <> | != | > | >= | !> | < | <= | !< } { ALL | SOME | ANY } ( subquery )
|
|
|
1161 // EXISTS ( subquery )
|
|
|
1162
|
|
|
1163 public class FuncLike : Predicate
|
|
|
1164 {
|
|
|
1165 public FuncLike(SqlFunction func)
|
|
|
1166 : base(func.Precedence)
|
|
|
1167 {
|
|
|
1168 Function = func;
|
|
|
1169 }
|
|
|
1170
|
|
|
1171 public SqlFunction Function { get; private set; }
|
|
|
1172
|
|
|
1173 [Obsolete]
|
|
|
1174 protected override void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
1175 {
|
|
|
1176 Function = (SqlFunction)((ISqlExpression)Function).Walk(skipColumns, func);
|
|
|
1177 }
|
|
|
1178
|
|
|
1179 public override bool CanBeNull()
|
|
|
1180 {
|
|
|
1181 return Function.CanBeNull();
|
|
|
1182 }
|
|
|
1183
|
|
|
1184 protected override ICloneableElement Clone(Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1185 {
|
|
|
1186 if (!doClone(this))
|
|
|
1187 return this;
|
|
|
1188
|
|
|
1189 ICloneableElement clone;
|
|
|
1190
|
|
|
1191 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1192 objectTree.Add(this, clone = new FuncLike((SqlFunction)Function.Clone(objectTree, doClone)));
|
|
|
1193
|
|
|
1194 return clone;
|
|
|
1195 }
|
|
|
1196
|
|
|
1197 public override QueryElementType ElementType
|
|
|
1198 {
|
|
|
1199 get { return QueryElementType.FuncLikePredicate; }
|
|
|
1200 }
|
|
|
1201
|
|
|
1202 protected override void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic)
|
|
|
1203 {
|
|
|
1204 ((IQueryElement)Function).ToString(sb, dic);
|
|
|
1205 }
|
|
|
1206 }
|
|
|
1207
|
|
|
1208 #region Overrides
|
|
|
1209
|
|
|
1210 #if OVERRIDETOSTRING
|
|
|
1211
|
|
|
1212 public override string ToString()
|
|
|
1213 {
|
|
|
1214 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
1215 }
|
|
|
1216
|
|
|
1217 #endif
|
|
|
1218
|
|
|
1219 #endregion
|
|
|
1220
|
|
|
1221 protected Predicate(int precedence)
|
|
|
1222 {
|
|
|
1223 Precedence = precedence;
|
|
|
1224 }
|
|
|
1225
|
|
|
1226 #region IPredicate Members
|
|
|
1227
|
|
|
1228 public int Precedence { get; private set; }
|
|
|
1229
|
|
|
1230 public abstract bool CanBeNull();
|
|
|
1231 protected abstract ICloneableElement Clone (Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone);
|
|
|
1232 [Obsolete]
|
|
|
1233 protected abstract void Walk (bool skipColumns, Func<ISqlExpression,ISqlExpression> action);
|
|
|
1234
|
|
|
1235 [Obsolete]
|
|
|
1236 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
1237 {
|
|
|
1238 Walk(skipColumns, func);
|
|
|
1239 return null;
|
|
|
1240 }
|
|
|
1241
|
|
|
1242 ICloneableElement ICloneableElement.Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1243 {
|
|
|
1244 if (!doClone(this))
|
|
|
1245 return this;
|
|
|
1246
|
|
|
1247 return Clone(objectTree, doClone);
|
|
|
1248 }
|
|
|
1249
|
|
|
1250 #endregion
|
|
|
1251
|
|
|
1252 #region IQueryElement Members
|
|
|
1253
|
|
|
1254 public abstract QueryElementType ElementType { get; }
|
|
|
1255
|
|
|
1256 protected abstract void ToString(StringBuilder sb, Dictionary<IQueryElement, IQueryElement> dic);
|
|
|
1257
|
|
|
1258 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
1259 {
|
|
|
1260 if (dic.ContainsKey(this))
|
|
|
1261 return sb.Append("...");
|
|
|
1262
|
|
|
1263 dic.Add(this, this);
|
|
|
1264 ToString(sb, dic);
|
|
|
1265 dic.Remove(this);
|
|
|
1266
|
|
|
1267 return sb;
|
|
|
1268 }
|
|
|
1269
|
|
|
1270 #endregion
|
|
|
1271 }
|
|
|
1272
|
|
|
1273 #endregion
|
|
|
1274
|
|
|
1275 #region Condition
|
|
|
1276
|
|
|
1277 public class Condition : IQueryElement, ICloneableElement
|
|
|
1278 {
|
|
|
1279 public Condition(bool isNot, ISqlPredicate predicate)
|
|
|
1280 {
|
|
|
1281 IsNot = isNot;
|
|
|
1282 Predicate = predicate;
|
|
|
1283 }
|
|
|
1284
|
|
|
1285 public Condition(bool isNot, ISqlPredicate predicate, bool isOr)
|
|
|
1286 {
|
|
|
1287 IsNot = isNot;
|
|
|
1288 Predicate = predicate;
|
|
|
1289 IsOr = isOr;
|
|
|
1290 }
|
|
|
1291
|
|
|
1292 public bool IsNot { get; set; }
|
|
|
1293 public ISqlPredicate Predicate { get; set; }
|
|
|
1294 public bool IsOr { get; set; }
|
|
|
1295
|
|
|
1296 public int Precedence
|
|
|
1297 {
|
|
|
1298 get
|
|
|
1299 {
|
|
|
1300 return
|
|
|
1301 IsNot ? Sql.Precedence.LogicalNegation :
|
|
|
1302 IsOr ? Sql.Precedence.LogicalDisjunction :
|
|
|
1303 Sql.Precedence.LogicalConjunction;
|
|
|
1304 }
|
|
|
1305 }
|
|
|
1306
|
|
|
1307 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1308 {
|
|
|
1309 if (!doClone(this))
|
|
|
1310 return this;
|
|
|
1311
|
|
|
1312 ICloneableElement clone;
|
|
|
1313
|
|
|
1314 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1315 objectTree.Add(this, clone = new Condition(IsNot, (ISqlPredicate)Predicate.Clone(objectTree, doClone), IsOr));
|
|
|
1316
|
|
|
1317 return clone;
|
|
|
1318 }
|
|
|
1319
|
|
|
1320 public bool CanBeNull()
|
|
|
1321 {
|
|
|
1322 return Predicate.CanBeNull();
|
|
|
1323 }
|
|
|
1324
|
|
|
1325 #if OVERRIDETOSTRING
|
|
|
1326
|
|
|
1327 public override string ToString()
|
|
|
1328 {
|
|
|
1329 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
1330 }
|
|
|
1331
|
|
|
1332 #endif
|
|
|
1333
|
|
|
1334 #region IQueryElement Members
|
|
|
1335
|
|
|
1336 public QueryElementType ElementType { get { return QueryElementType.Condition; } }
|
|
|
1337
|
|
|
1338 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
1339 {
|
|
|
1340 if (dic.ContainsKey(this))
|
|
|
1341 return sb.Append("...");
|
|
|
1342
|
|
|
1343 dic.Add(this, this);
|
|
|
1344
|
|
|
1345 sb.Append('(');
|
|
|
1346
|
|
|
1347 if (IsNot) sb.Append("NOT ");
|
|
|
1348
|
|
|
1349 Predicate.ToString(sb, dic);
|
|
|
1350 sb.Append(')').Append(IsOr ? " OR " : " AND ");
|
|
|
1351
|
|
|
1352 dic.Remove(this);
|
|
|
1353
|
|
|
1354 return sb;
|
|
|
1355 }
|
|
|
1356
|
|
|
1357 #endregion
|
|
|
1358 }
|
|
|
1359
|
|
|
1360 #endregion
|
|
|
1361
|
|
|
1362 #region SearchCondition
|
|
|
1363
|
|
|
1364 public class SearchCondition : ConditionBase<SearchCondition, SearchCondition.Next>, ISqlPredicate, ISqlExpression
|
|
|
1365 {
|
|
|
1366 public SearchCondition()
|
|
|
1367 {
|
|
|
1368 }
|
|
|
1369
|
|
|
1370 public SearchCondition(IEnumerable<Condition> list)
|
|
|
1371 {
|
|
|
1372 _conditions.AddRange(list);
|
|
|
1373 }
|
|
|
1374
|
|
|
1375 public SearchCondition(params Condition[] list)
|
|
|
1376 {
|
|
|
1377 _conditions.AddRange(list);
|
|
|
1378 }
|
|
|
1379
|
|
|
1380 public class Next
|
|
|
1381 {
|
|
|
1382 internal Next(SearchCondition parent)
|
|
|
1383 {
|
|
|
1384 _parent = parent;
|
|
|
1385 }
|
|
|
1386
|
|
|
1387 readonly SearchCondition _parent;
|
|
|
1388
|
|
|
1389 public SearchCondition Or { get { return _parent.SetOr(true); } }
|
|
|
1390 public SearchCondition And { get { return _parent.SetOr(false); } }
|
|
|
1391
|
|
|
1392 public ISqlExpression ToExpr() { return _parent; }
|
|
|
1393 }
|
|
|
1394
|
|
|
1395 readonly List<Condition> _conditions = new List<Condition>();
|
|
|
1396 public List<Condition> Conditions
|
|
|
1397 {
|
|
|
1398 get { return _conditions; }
|
|
|
1399 }
|
|
|
1400
|
|
|
1401 protected override SearchCondition Search
|
|
|
1402 {
|
|
|
1403 get { return this; }
|
|
|
1404 }
|
|
|
1405
|
|
|
1406 protected override Next GetNext()
|
|
|
1407 {
|
|
|
1408 return new Next(this);
|
|
|
1409 }
|
|
|
1410
|
|
|
1411 #region Overrides
|
|
|
1412
|
|
|
1413 #if OVERRIDETOSTRING
|
|
|
1414
|
|
|
1415 public override string ToString()
|
|
|
1416 {
|
|
|
1417 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
1418 }
|
|
|
1419
|
|
|
1420 #endif
|
|
|
1421
|
|
|
1422 #endregion
|
|
|
1423
|
|
|
1424 #region IPredicate Members
|
|
|
1425
|
|
|
1426 public int Precedence
|
|
|
1427 {
|
|
|
1428 get
|
|
|
1429 {
|
|
|
1430 if (_conditions.Count == 0) return Sql.Precedence.Unknown;
|
|
|
1431 if (_conditions.Count == 1) return _conditions[0].Precedence;
|
|
|
1432
|
|
|
1433 return _conditions.Select(_ =>
|
|
|
1434 _.IsNot ? Sql.Precedence.LogicalNegation :
|
|
|
1435 _.IsOr ? Sql.Precedence.LogicalDisjunction :
|
|
|
1436 Sql.Precedence.LogicalConjunction).Min();
|
|
|
1437 }
|
|
|
1438 }
|
|
|
1439
|
|
|
1440 public Type SystemType
|
|
|
1441 {
|
|
|
1442 get { return typeof(bool); }
|
|
|
1443 }
|
|
|
1444
|
|
|
1445 [Obsolete]
|
|
|
1446 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
1447 {
|
|
|
1448 foreach (var condition in Conditions)
|
|
|
1449 condition.Predicate.Walk(skipColumns, func);
|
|
|
1450
|
|
|
1451 return func(this);
|
|
|
1452 }
|
|
|
1453
|
|
|
1454 #endregion
|
|
|
1455
|
|
|
1456 #region IEquatable<ISqlExpression> Members
|
|
|
1457
|
|
|
1458 bool IEquatable<ISqlExpression>.Equals(ISqlExpression other)
|
|
|
1459 {
|
|
|
1460 return this == other;
|
|
|
1461 }
|
|
|
1462
|
|
|
1463 #endregion
|
|
|
1464
|
|
|
1465 #region ISqlExpression Members
|
|
|
1466
|
|
|
1467 public bool CanBeNull()
|
|
|
1468 {
|
|
|
1469 foreach (var c in Conditions)
|
|
|
1470 if (c.CanBeNull())
|
|
|
1471 return true;
|
|
|
1472
|
|
|
1473 return false;
|
|
|
1474 }
|
|
|
1475
|
|
|
1476 public bool Equals(ISqlExpression other, Func<ISqlExpression,ISqlExpression,bool> comparer)
|
|
|
1477 {
|
|
|
1478 return this == other;
|
|
|
1479 }
|
|
|
1480
|
|
|
1481 #endregion
|
|
|
1482
|
|
|
1483 #region ICloneableElement Members
|
|
|
1484
|
|
|
1485 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1486 {
|
|
|
1487 if (!doClone(this))
|
|
|
1488 return this;
|
|
|
1489
|
|
|
1490 ICloneableElement clone;
|
|
|
1491
|
|
|
1492 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1493 {
|
|
|
1494 var sc = new SearchCondition();
|
|
|
1495
|
|
|
1496 objectTree.Add(this, clone = sc);
|
|
|
1497
|
|
|
1498 sc._conditions.AddRange(_conditions.ConvertAll(c => (Condition)c.Clone(objectTree, doClone)));
|
|
|
1499 }
|
|
|
1500
|
|
|
1501 return clone;
|
|
|
1502 }
|
|
|
1503
|
|
|
1504 #endregion
|
|
|
1505
|
|
|
1506 #region IQueryElement Members
|
|
|
1507
|
|
|
1508 public QueryElementType ElementType { get { return QueryElementType.SearchCondition; } }
|
|
|
1509
|
|
|
1510 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
1511 {
|
|
|
1512 if (dic.ContainsKey(this))
|
|
|
1513 return sb.Append("...");
|
|
|
1514
|
|
|
1515 dic.Add(this, this);
|
|
|
1516
|
|
|
1517 foreach (IQueryElement c in Conditions)
|
|
|
1518 c.ToString(sb, dic);
|
|
|
1519
|
|
|
1520 if (Conditions.Count > 0)
|
|
|
1521 sb.Length -= 4;
|
|
|
1522
|
|
|
1523 dic.Remove(this);
|
|
|
1524
|
|
|
1525 return sb;
|
|
|
1526 }
|
|
|
1527
|
|
|
1528 #endregion
|
|
|
1529 }
|
|
|
1530
|
|
|
1531 #endregion
|
|
|
1532
|
|
|
1533 #region ConditionBase
|
|
|
1534
|
|
|
1535 interface IConditionExpr<T>
|
|
|
1536 {
|
|
|
1537 T Expr (ISqlExpression expr);
|
|
|
1538 T Field (SqlField field);
|
|
|
1539 T SubQuery(SqlQuery sqlQuery);
|
|
|
1540 T Value (object value);
|
|
|
1541 }
|
|
|
1542
|
|
|
1543 public abstract class ConditionBase<T1,T2> : IConditionExpr<ConditionBase<T1,T2>.Expr_>
|
|
|
1544 where T1 : ConditionBase<T1,T2>
|
|
|
1545 {
|
|
|
1546 public class Expr_
|
|
|
1547 {
|
|
|
1548 internal Expr_(ConditionBase<T1,T2> condition, bool isNot, ISqlExpression expr)
|
|
|
1549 {
|
|
|
1550 _condition = condition;
|
|
|
1551 _isNot = isNot;
|
|
|
1552 _expr = expr;
|
|
|
1553 }
|
|
|
1554
|
|
|
1555 readonly ConditionBase<T1,T2> _condition;
|
|
|
1556 readonly bool _isNot;
|
|
|
1557 readonly ISqlExpression _expr;
|
|
|
1558
|
|
|
1559 T2 Add(ISqlPredicate predicate)
|
|
|
1560 {
|
|
|
1561 _condition.Search.Conditions.Add(new Condition(_isNot, predicate));
|
|
|
1562 return _condition.GetNext();
|
|
|
1563 }
|
|
|
1564
|
|
|
1565 #region Predicate.ExprExpr
|
|
|
1566
|
|
|
1567 public class Op_ : IConditionExpr<T2>
|
|
|
1568 {
|
|
|
1569 internal Op_(Expr_ expr, Predicate.Operator op)
|
|
|
1570 {
|
|
|
1571 _expr = expr;
|
|
|
1572 _op = op;
|
|
|
1573 }
|
|
|
1574
|
|
|
1575 readonly Expr_ _expr;
|
|
|
1576 readonly Predicate.Operator _op;
|
|
|
1577
|
|
|
1578 public T2 Expr (ISqlExpression expr) { return _expr.Add(new Predicate.ExprExpr(_expr._expr, _op, expr)); }
|
|
|
1579 public T2 Field (SqlField field) { return Expr(field); }
|
|
|
1580 public T2 SubQuery(SqlQuery subQuery) { return Expr(subQuery); }
|
|
|
1581 public T2 Value (object value) { return Expr(new SqlValue(value)); }
|
|
|
1582
|
|
|
1583 public T2 All (SqlQuery subQuery) { return Expr(SqlFunction.CreateAll (subQuery)); }
|
|
|
1584 public T2 Some (SqlQuery subQuery) { return Expr(SqlFunction.CreateSome(subQuery)); }
|
|
|
1585 public T2 Any (SqlQuery subQuery) { return Expr(SqlFunction.CreateAny (subQuery)); }
|
|
|
1586 }
|
|
|
1587
|
|
|
1588 public Op_ Equal { get { return new Op_(this, Predicate.Operator.Equal); } }
|
|
|
1589 public Op_ NotEqual { get { return new Op_(this, Predicate.Operator.NotEqual); } }
|
|
|
1590 public Op_ Greater { get { return new Op_(this, Predicate.Operator.Greater); } }
|
|
|
1591 public Op_ GreaterOrEqual { get { return new Op_(this, Predicate.Operator.GreaterOrEqual); } }
|
|
|
1592 public Op_ NotGreater { get { return new Op_(this, Predicate.Operator.NotGreater); } }
|
|
|
1593 public Op_ Less { get { return new Op_(this, Predicate.Operator.Less); } }
|
|
|
1594 public Op_ LessOrEqual { get { return new Op_(this, Predicate.Operator.LessOrEqual); } }
|
|
|
1595 public Op_ NotLess { get { return new Op_(this, Predicate.Operator.NotLess); } }
|
|
|
1596
|
|
|
1597 #endregion
|
|
|
1598
|
|
|
1599 #region Predicate.Like
|
|
|
1600
|
|
|
1601 public T2 Like(ISqlExpression expression, SqlValue escape) { return Add(new Predicate.Like(_expr, false, expression, escape)); }
|
|
|
1602 public T2 Like(ISqlExpression expression) { return Like(expression, null); }
|
|
|
1603 public T2 Like(string expression, SqlValue escape) { return Like(new SqlValue(expression), escape); }
|
|
|
1604 public T2 Like(string expression) { return Like(new SqlValue(expression), null); }
|
|
|
1605
|
|
|
1606 #endregion
|
|
|
1607
|
|
|
1608 #region Predicate.Between
|
|
|
1609
|
|
|
1610 public T2 Between (ISqlExpression expr1, ISqlExpression expr2) { return Add(new Predicate.Between(_expr, false, expr1, expr2)); }
|
|
|
1611 public T2 NotBetween(ISqlExpression expr1, ISqlExpression expr2) { return Add(new Predicate.Between(_expr, true, expr1, expr2)); }
|
|
|
1612
|
|
|
1613 #endregion
|
|
|
1614
|
|
|
1615 #region Predicate.IsNull
|
|
|
1616
|
|
|
1617 public T2 IsNull { get { return Add(new Predicate.IsNull(_expr, false)); } }
|
|
|
1618 public T2 IsNotNull { get { return Add(new Predicate.IsNull(_expr, true)); } }
|
|
|
1619
|
|
|
1620 #endregion
|
|
|
1621
|
|
|
1622 #region Predicate.In
|
|
|
1623
|
|
|
1624 public T2 In (SqlQuery subQuery) { return Add(new Predicate.InSubQuery(_expr, false, subQuery)); }
|
|
|
1625 public T2 NotIn(SqlQuery subQuery) { return Add(new Predicate.InSubQuery(_expr, true, subQuery)); }
|
|
|
1626
|
|
|
1627 Predicate.InList CreateInList(bool isNot, object[] exprs)
|
|
|
1628 {
|
|
|
1629 var list = new Predicate.InList(_expr, isNot, null);
|
|
|
1630
|
|
|
1631 if (exprs != null && exprs.Length > 0)
|
|
|
1632 {
|
|
|
1633 foreach (var item in exprs)
|
|
|
1634 {
|
|
|
1635 if (item == null || item is SqlValue && ((SqlValue)item).Value == null)
|
|
|
1636 continue;
|
|
|
1637
|
|
|
1638 if (item is ISqlExpression)
|
|
|
1639 list.Values.Add((ISqlExpression)item);
|
|
|
1640 else
|
|
|
1641 list.Values.Add(new SqlValue(item));
|
|
|
1642 }
|
|
|
1643 }
|
|
|
1644
|
|
|
1645 return list;
|
|
|
1646 }
|
|
|
1647
|
|
|
1648 public T2 In (params object[] exprs) { return Add(CreateInList(false, exprs)); }
|
|
|
1649 public T2 NotIn(params object[] exprs) { return Add(CreateInList(true, exprs)); }
|
|
|
1650
|
|
|
1651 #endregion
|
|
|
1652 }
|
|
|
1653
|
|
|
1654 public class Not_ : IConditionExpr<Expr_>
|
|
|
1655 {
|
|
|
1656 internal Not_(ConditionBase<T1,T2> condition)
|
|
|
1657 {
|
|
|
1658 _condition = condition;
|
|
|
1659 }
|
|
|
1660
|
|
|
1661 readonly ConditionBase<T1,T2> _condition;
|
|
|
1662
|
|
|
1663 public Expr_ Expr (ISqlExpression expr) { return new Expr_(_condition, true, expr); }
|
|
|
1664 public Expr_ Field (SqlField field) { return Expr(field); }
|
|
|
1665 public Expr_ SubQuery(SqlQuery subQuery) { return Expr(subQuery); }
|
|
|
1666 public Expr_ Value (object value) { return Expr(new SqlValue(value)); }
|
|
|
1667
|
|
|
1668 public T2 Exists(SqlQuery subQuery)
|
|
|
1669 {
|
|
|
1670 _condition.Search.Conditions.Add(new Condition(true, new Predicate.FuncLike(SqlFunction.CreateExists(subQuery))));
|
|
|
1671 return _condition.GetNext();
|
|
|
1672 }
|
|
|
1673 }
|
|
|
1674
|
|
|
1675 protected abstract SearchCondition Search { get; }
|
|
|
1676 protected abstract T2 GetNext();
|
|
|
1677
|
|
|
1678 protected T1 SetOr(bool value)
|
|
|
1679 {
|
|
|
1680 Search.Conditions[Search.Conditions.Count - 1].IsOr = value;
|
|
|
1681 return (T1)this;
|
|
|
1682 }
|
|
|
1683
|
|
|
1684 public Not_ Not { get { return new Not_(this); } }
|
|
|
1685
|
|
|
1686 public Expr_ Expr (ISqlExpression expr) { return new Expr_(this, false, expr); }
|
|
|
1687 public Expr_ Field (SqlField field) { return Expr(field); }
|
|
|
1688 public Expr_ SubQuery(SqlQuery subQuery) { return Expr(subQuery); }
|
|
|
1689 public Expr_ Value (object value) { return Expr(new SqlValue(value)); }
|
|
|
1690
|
|
|
1691 public T2 Exists(SqlQuery subQuery)
|
|
|
1692 {
|
|
|
1693 Search.Conditions.Add(new Condition(false, new Predicate.FuncLike(SqlFunction.CreateExists(subQuery))));
|
|
|
1694 return GetNext();
|
|
|
1695 }
|
|
|
1696 }
|
|
|
1697
|
|
|
1698 #endregion
|
|
|
1699
|
|
|
1700 #region OrderByItem
|
|
|
1701
|
|
|
1702 public class OrderByItem : IQueryElement, ICloneableElement
|
|
|
1703 {
|
|
|
1704 public OrderByItem(ISqlExpression expression, bool isDescending)
|
|
|
1705 {
|
|
|
1706 Expression = expression;
|
|
|
1707 IsDescending = isDescending;
|
|
|
1708 }
|
|
|
1709
|
|
|
1710 public ISqlExpression Expression { get; internal set; }
|
|
|
1711 public bool IsDescending { get; private set; }
|
|
|
1712
|
|
|
1713 [Obsolete]
|
|
|
1714 internal void Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
1715 {
|
|
|
1716 Expression = Expression.Walk(skipColumns, func);
|
|
|
1717 }
|
|
|
1718
|
|
|
1719 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
1720 {
|
|
|
1721 if (!doClone(this))
|
|
|
1722 return this;
|
|
|
1723
|
|
|
1724 ICloneableElement clone;
|
|
|
1725
|
|
|
1726 if (!objectTree.TryGetValue(this, out clone))
|
|
|
1727 objectTree.Add(this, clone = new OrderByItem((ISqlExpression)Expression.Clone(objectTree, doClone), IsDescending));
|
|
|
1728
|
|
|
1729 return clone;
|
|
|
1730 }
|
|
|
1731
|
|
|
1732 #region Overrides
|
|
|
1733
|
|
|
1734 #if OVERRIDETOSTRING
|
|
|
1735
|
|
|
1736 public override string ToString()
|
|
|
1737 {
|
|
|
1738 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
1739 }
|
|
|
1740
|
|
|
1741 #endif
|
|
|
1742
|
|
|
1743 #endregion
|
|
|
1744
|
|
|
1745 #region IQueryElement Members
|
|
|
1746
|
|
|
1747 public QueryElementType ElementType
|
|
|
1748 {
|
|
|
1749 get { return QueryElementType.OrderByItem; }
|
|
|
1750 }
|
|
|
1751
|
|
|
1752 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
1753 {
|
|
|
1754 Expression.ToString(sb, dic);
|
|
|
1755
|
|
|
1756 if (IsDescending)
|
|
|
1757 sb.Append(" DESC");
|
|
|
1758
|
|
|
1759 return sb;
|
|
|
1760 }
|
|
|
1761
|
|
|
1762 #endregion
|
|
|
1763 }
|
|
|
1764
|
|
|
1765 #endregion
|
|
|
1766
|
|
|
1767 #region ClauseBase
|
|
|
1768
|
|
|
1769 public abstract class ClauseBase
|
|
|
1770 {
|
|
|
1771 protected ClauseBase(SqlQuery sqlQuery)
|
|
|
1772 {
|
|
|
1773 SqlQuery = sqlQuery;
|
|
|
1774 }
|
|
|
1775
|
|
|
1776 public SelectClause Select { get { return SqlQuery.Select; } }
|
|
|
1777 public FromClause From { get { return SqlQuery.From; } }
|
|
|
1778 public WhereClause Where { get { return SqlQuery.Where; } }
|
|
|
1779 public GroupByClause GroupBy { get { return SqlQuery.GroupBy; } }
|
|
|
1780 public WhereClause Having { get { return SqlQuery.Having; } }
|
|
|
1781 public OrderByClause OrderBy { get { return SqlQuery.OrderBy; } }
|
|
|
1782 public SqlQuery End() { return SqlQuery; }
|
|
|
1783
|
|
|
1784 protected internal SqlQuery SqlQuery { get; private set; }
|
|
|
1785
|
|
|
1786 internal void SetSqlQuery(SqlQuery sqlQuery)
|
|
|
1787 {
|
|
|
1788 SqlQuery = sqlQuery;
|
|
|
1789 }
|
|
|
1790 }
|
|
|
1791
|
|
|
1792 public abstract class ClauseBase<T1, T2> : ConditionBase<T1, T2>
|
|
|
1793 where T1 : ClauseBase<T1, T2>
|
|
|
1794 {
|
|
|
1795 protected ClauseBase(SqlQuery sqlQuery)
|
|
|
1796 {
|
|
|
1797 SqlQuery = sqlQuery;
|
|
|
1798 }
|
|
|
1799
|
|
|
1800 public SelectClause Select { get { return SqlQuery.Select; } }
|
|
|
1801 public FromClause From { get { return SqlQuery.From; } }
|
|
|
1802 public GroupByClause GroupBy { get { return SqlQuery.GroupBy; } }
|
|
|
1803 public WhereClause Having { get { return SqlQuery.Having; } }
|
|
|
1804 public OrderByClause OrderBy { get { return SqlQuery.OrderBy; } }
|
|
|
1805 public SqlQuery End() { return SqlQuery; }
|
|
|
1806
|
|
|
1807 protected internal SqlQuery SqlQuery { get; private set; }
|
|
|
1808
|
|
|
1809 internal void SetSqlQuery(SqlQuery sqlQuery)
|
|
|
1810 {
|
|
|
1811 SqlQuery = sqlQuery;
|
|
|
1812 }
|
|
|
1813 }
|
|
|
1814
|
|
|
1815 #endregion
|
|
|
1816
|
|
|
1817 #region SelectClause
|
|
|
1818
|
|
|
1819 public class SelectClause : ClauseBase, IQueryElement, ISqlExpressionWalkable
|
|
|
1820 {
|
|
|
1821 #region Init
|
|
|
1822
|
|
|
1823 internal SelectClause(SqlQuery sqlQuery) : base(sqlQuery)
|
|
|
1824 {
|
|
|
1825 }
|
|
|
1826
|
|
|
1827 internal SelectClause(
|
|
|
1828 SqlQuery sqlQuery,
|
|
|
1829 SelectClause clone,
|
|
|
1830 Dictionary<ICloneableElement,ICloneableElement> objectTree,
|
|
|
1831 Predicate<ICloneableElement> doClone)
|
|
|
1832 : base(sqlQuery)
|
|
|
1833 {
|
|
|
1834 _columns.AddRange(clone._columns.ConvertAll(c => (Column)c.Clone(objectTree, doClone)));
|
|
|
1835
|
|
|
1836 IsDistinct = clone.IsDistinct;
|
|
|
1837 TakeValue = clone.TakeValue == null ? null : (ISqlExpression)clone.TakeValue.Clone(objectTree, doClone);
|
|
|
1838 SkipValue = clone.SkipValue == null ? null : (ISqlExpression)clone.SkipValue.Clone(objectTree, doClone);
|
|
|
1839 }
|
|
|
1840
|
|
|
1841 internal SelectClause(bool isDistinct, ISqlExpression takeValue, ISqlExpression skipValue, IEnumerable<Column> columns)
|
|
|
1842 : base(null)
|
|
|
1843 {
|
|
|
1844 IsDistinct = isDistinct;
|
|
|
1845 TakeValue = takeValue;
|
|
|
1846 SkipValue = skipValue;
|
|
|
1847
|
|
|
1848 _columns.AddRange(columns);
|
|
|
1849 }
|
|
|
1850
|
|
|
1851 #endregion
|
|
|
1852
|
|
|
1853 #region Columns
|
|
|
1854
|
|
|
1855 public SelectClause Field(SqlField field)
|
|
|
1856 {
|
|
|
1857 AddOrGetColumn(new Column(SqlQuery, field));
|
|
|
1858 return this;
|
|
|
1859 }
|
|
|
1860
|
|
|
1861 public SelectClause Field(SqlField field, string alias)
|
|
|
1862 {
|
|
|
1863 AddOrGetColumn(new Column(SqlQuery, field, alias));
|
|
|
1864 return this;
|
|
|
1865 }
|
|
|
1866
|
|
|
1867 public SelectClause SubQuery(SqlQuery subQuery)
|
|
|
1868 {
|
|
|
1869 if (subQuery.ParentSql != null && subQuery.ParentSql != SqlQuery)
|
|
|
1870 throw new ArgumentException("SqlQuery already used as subquery");
|
|
|
1871
|
|
|
1872 subQuery.ParentSql = SqlQuery;
|
|
|
1873
|
|
|
1874 AddOrGetColumn(new Column(SqlQuery, subQuery));
|
|
|
1875 return this;
|
|
|
1876 }
|
|
|
1877
|
|
|
1878 public SelectClause SubQuery(SqlQuery sqlQuery, string alias)
|
|
|
1879 {
|
|
|
1880 if (sqlQuery.ParentSql != null && sqlQuery.ParentSql != SqlQuery)
|
|
|
1881 throw new ArgumentException("SqlQuery already used as subquery");
|
|
|
1882
|
|
|
1883 sqlQuery.ParentSql = SqlQuery;
|
|
|
1884
|
|
|
1885 AddOrGetColumn(new Column(SqlQuery, sqlQuery, alias));
|
|
|
1886 return this;
|
|
|
1887 }
|
|
|
1888
|
|
|
1889 public SelectClause Expr(ISqlExpression expr)
|
|
|
1890 {
|
|
|
1891 AddOrGetColumn(new Column(SqlQuery, expr));
|
|
|
1892 return this;
|
|
|
1893 }
|
|
|
1894
|
|
|
1895 public SelectClause Expr(ISqlExpression expr, string alias)
|
|
|
1896 {
|
|
|
1897 AddOrGetColumn(new Column(SqlQuery, expr, alias));
|
|
|
1898 return this;
|
|
|
1899 }
|
|
|
1900
|
|
|
1901 public SelectClause Expr(string expr, params ISqlExpression[] values)
|
|
|
1902 {
|
|
|
1903 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(null, expr, values)));
|
|
|
1904 return this;
|
|
|
1905 }
|
|
|
1906
|
|
|
1907 public SelectClause Expr(Type systemType, string expr, params ISqlExpression[] values)
|
|
|
1908 {
|
|
|
1909 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(systemType, expr, values)));
|
|
|
1910 return this;
|
|
|
1911 }
|
|
|
1912
|
|
|
1913 public SelectClause Expr(string expr, int priority, params ISqlExpression[] values)
|
|
|
1914 {
|
|
|
1915 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(null, expr, priority, values)));
|
|
|
1916 return this;
|
|
|
1917 }
|
|
|
1918
|
|
|
1919 public SelectClause Expr(Type systemType, string expr, int priority, params ISqlExpression[] values)
|
|
|
1920 {
|
|
|
1921 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(systemType, expr, priority, values)));
|
|
|
1922 return this;
|
|
|
1923 }
|
|
|
1924
|
|
|
1925 public SelectClause Expr(string alias, string expr, int priority, params ISqlExpression[] values)
|
|
|
1926 {
|
|
|
1927 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(null, expr, priority, values)));
|
|
|
1928 return this;
|
|
|
1929 }
|
|
|
1930
|
|
|
1931 public SelectClause Expr(Type systemType, string alias, string expr, int priority, params ISqlExpression[] values)
|
|
|
1932 {
|
|
|
1933 AddOrGetColumn(new Column(SqlQuery, new SqlExpression(systemType, expr, priority, values)));
|
|
|
1934 return this;
|
|
|
1935 }
|
|
|
1936
|
|
|
1937 public SelectClause Expr<T>(ISqlExpression expr1, string operation, ISqlExpression expr2)
|
|
|
1938 {
|
|
|
1939 AddOrGetColumn(new Column(SqlQuery, new SqlBinaryExpression(typeof(T), expr1, operation, expr2)));
|
|
|
1940 return this;
|
|
|
1941 }
|
|
|
1942
|
|
|
1943 public SelectClause Expr<T>(ISqlExpression expr1, string operation, ISqlExpression expr2, int priority)
|
|
|
1944 {
|
|
|
1945 AddOrGetColumn(new Column(SqlQuery, new SqlBinaryExpression(typeof(T), expr1, operation, expr2, priority)));
|
|
|
1946 return this;
|
|
|
1947 }
|
|
|
1948
|
|
|
1949 public SelectClause Expr<T>(string alias, ISqlExpression expr1, string operation, ISqlExpression expr2, int priority)
|
|
|
1950 {
|
|
|
1951 AddOrGetColumn(new Column(SqlQuery, new SqlBinaryExpression(typeof(T), expr1, operation, expr2, priority), alias));
|
|
|
1952 return this;
|
|
|
1953 }
|
|
|
1954
|
|
|
1955 public int Add(ISqlExpression expr)
|
|
|
1956 {
|
|
|
1957 if (expr is Column && ((Column)expr).Parent == SqlQuery)
|
|
|
1958 throw new InvalidOperationException();
|
|
|
1959
|
|
|
1960 return Columns.IndexOf(AddOrGetColumn(new Column(SqlQuery, expr)));
|
|
|
1961 }
|
|
|
1962
|
|
|
1963 public int Add(ISqlExpression expr, string alias)
|
|
|
1964 {
|
|
|
1965 return Columns.IndexOf(AddOrGetColumn(new Column(SqlQuery, expr, alias)));
|
|
|
1966 }
|
|
|
1967
|
|
|
1968 Column AddOrGetColumn(Column col)
|
|
|
1969 {
|
|
|
1970 foreach (var c in Columns)
|
|
|
1971 if (c.Equals(col))
|
|
|
1972 return col;
|
|
|
1973
|
|
|
1974 #if DEBUG
|
|
|
1975
|
|
|
1976 switch (col.Expression.ElementType)
|
|
|
1977 {
|
|
|
1978 case QueryElementType.SqlField :
|
|
|
1979 {
|
|
|
1980 var table = ((SqlField)col.Expression).Table;
|
|
|
1981
|
|
|
1982 //if (SqlQuery.From.GetFromTables().Any(_ => _ == table))
|
|
|
1983 // throw new InvalidOperationException("Wrong field usage.");
|
|
|
1984
|
|
|
1985 break;
|
|
|
1986 }
|
|
|
1987
|
|
|
1988 case QueryElementType.Column :
|
|
|
1989 {
|
|
|
1990 var query = ((Column)col.Expression).Parent;
|
|
|
1991
|
|
|
1992 //if (!SqlQuery.From.GetFromQueries().Any(_ => _ == query))
|
|
|
1993 // throw new InvalidOperationException("Wrong column usage.");
|
|
|
1994
|
|
|
1995 break;
|
|
|
1996 }
|
|
|
1997
|
|
|
1998 case QueryElementType.SqlQuery :
|
|
|
1999 {
|
|
|
2000 if (col.Expression == SqlQuery)
|
|
|
2001 throw new InvalidOperationException("Wrong query usage.");
|
|
|
2002 break;
|
|
|
2003 }
|
|
|
2004 }
|
|
|
2005
|
|
|
2006 #endif
|
|
|
2007
|
|
|
2008 Columns.Add(col);
|
|
|
2009
|
|
|
2010 return col;
|
|
|
2011 }
|
|
|
2012
|
|
|
2013 readonly List<Column> _columns = new List<Column>();
|
|
|
2014 public List<Column> Columns
|
|
|
2015 {
|
|
|
2016 get { return _columns; }
|
|
|
2017 }
|
|
|
2018
|
|
|
2019 #endregion
|
|
|
2020
|
|
|
2021 #region HasModifier
|
|
|
2022
|
|
|
2023 public bool HasModifier
|
|
|
2024 {
|
|
|
2025 get { return IsDistinct || SkipValue != null || TakeValue != null; }
|
|
|
2026 }
|
|
|
2027
|
|
|
2028 #endregion
|
|
|
2029
|
|
|
2030 #region Distinct
|
|
|
2031
|
|
|
2032 public SelectClause Distinct
|
|
|
2033 {
|
|
|
2034 get { IsDistinct = true; return this; }
|
|
|
2035 }
|
|
|
2036
|
|
|
2037 public bool IsDistinct { get; set; }
|
|
|
2038
|
|
|
2039 #endregion
|
|
|
2040
|
|
|
2041 #region Take
|
|
|
2042
|
|
|
2043 public SelectClause Take(int value)
|
|
|
2044 {
|
|
|
2045 TakeValue = new SqlValue(value);
|
|
|
2046 return this;
|
|
|
2047 }
|
|
|
2048
|
|
|
2049 public SelectClause Take(ISqlExpression value)
|
|
|
2050 {
|
|
|
2051 TakeValue = value;
|
|
|
2052 return this;
|
|
|
2053 }
|
|
|
2054
|
|
|
2055 public ISqlExpression TakeValue { get; set; }
|
|
|
2056
|
|
|
2057 #endregion
|
|
|
2058
|
|
|
2059 #region Skip
|
|
|
2060
|
|
|
2061 public SelectClause Skip(int value)
|
|
|
2062 {
|
|
|
2063 SkipValue = new SqlValue(value);
|
|
|
2064 return this;
|
|
|
2065 }
|
|
|
2066
|
|
|
2067 public SelectClause Skip(ISqlExpression value)
|
|
|
2068 {
|
|
|
2069 SkipValue = value;
|
|
|
2070 return this;
|
|
|
2071 }
|
|
|
2072
|
|
|
2073 public ISqlExpression SkipValue { get; set; }
|
|
|
2074
|
|
|
2075 #endregion
|
|
|
2076
|
|
|
2077 #region Overrides
|
|
|
2078
|
|
|
2079 #if OVERRIDETOSTRING
|
|
|
2080
|
|
|
2081 public override string ToString()
|
|
|
2082 {
|
|
|
2083 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2084 }
|
|
|
2085
|
|
|
2086 #endif
|
|
|
2087
|
|
|
2088 #endregion
|
|
|
2089
|
|
|
2090 #region ISqlExpressionWalkable Members
|
|
|
2091
|
|
|
2092 [Obsolete]
|
|
|
2093 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2094 {
|
|
|
2095 for (var i = 0; i < Columns.Count; i++)
|
|
|
2096 {
|
|
|
2097 var col = Columns[i];
|
|
|
2098 #pragma warning disable 0618
|
|
|
2099 var expr = col.Walk(skipColumns, func);
|
|
|
2100 #pragma warning restore 0618
|
|
|
2101
|
|
|
2102 if (expr is Column)
|
|
|
2103 Columns[i] = (Column)expr;
|
|
|
2104 else
|
|
|
2105 Columns[i] = new Column(col.Parent, expr, col.Alias);
|
|
|
2106 }
|
|
|
2107
|
|
|
2108 if (TakeValue != null) TakeValue = TakeValue.Walk(skipColumns, func);
|
|
|
2109 if (SkipValue != null) SkipValue = SkipValue.Walk(skipColumns, func);
|
|
|
2110
|
|
|
2111 return null;
|
|
|
2112 }
|
|
|
2113
|
|
|
2114 #endregion
|
|
|
2115
|
|
|
2116 #region IQueryElement Members
|
|
|
2117
|
|
|
2118 public QueryElementType ElementType { get { return QueryElementType.SelectClause; } }
|
|
|
2119
|
|
|
2120 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2121 {
|
|
|
2122 if (dic.ContainsKey(this))
|
|
|
2123 return sb.Append("...");
|
|
|
2124
|
|
|
2125 dic.Add(this, this);
|
|
|
2126
|
|
|
2127 sb.Append("SELECT ");
|
|
|
2128
|
|
|
2129 if (IsDistinct) sb.Append("DISTINCT ");
|
|
|
2130
|
|
|
2131 if (SkipValue != null)
|
|
|
2132 {
|
|
|
2133 sb.Append("SKIP ");
|
|
|
2134 SkipValue.ToString(sb, dic);
|
|
|
2135 sb.Append(" ");
|
|
|
2136 }
|
|
|
2137
|
|
|
2138 if (TakeValue != null)
|
|
|
2139 {
|
|
|
2140 sb.Append("TAKE ");
|
|
|
2141 TakeValue.ToString(sb, dic);
|
|
|
2142 sb.Append(" ");
|
|
|
2143 }
|
|
|
2144
|
|
|
2145 sb.AppendLine();
|
|
|
2146
|
|
|
2147 if (Columns.Count == 0)
|
|
|
2148 sb.Append("\t*, \n");
|
|
|
2149 else
|
|
|
2150 foreach (var c in Columns)
|
|
|
2151 {
|
|
|
2152 sb.Append("\t");
|
|
|
2153 ((IQueryElement)c).ToString(sb, dic);
|
|
|
2154 sb
|
|
|
2155 .Append(" as ")
|
|
|
2156 .Append(c.Alias ?? "c" + (Columns.IndexOf(c) + 1))
|
|
|
2157 .Append(", \n");
|
|
|
2158 }
|
|
|
2159
|
|
|
2160 sb.Length -= 3;
|
|
|
2161
|
|
|
2162 dic.Remove(this);
|
|
|
2163
|
|
|
2164 return sb;
|
|
|
2165 }
|
|
|
2166
|
|
|
2167 #endregion
|
|
|
2168 }
|
|
|
2169
|
|
|
2170 private SelectClause _select;
|
|
|
2171 public SelectClause Select
|
|
|
2172 {
|
|
|
2173 get { return _select; }
|
|
|
2174 }
|
|
|
2175
|
|
|
2176 #endregion
|
|
|
2177
|
|
|
2178 #region InsertClause
|
|
|
2179
|
|
|
2180 public class SetExpression : IQueryElement, ISqlExpressionWalkable, ICloneableElement
|
|
|
2181 {
|
|
|
2182 public SetExpression(ISqlExpression column, ISqlExpression expression)
|
|
|
2183 {
|
|
|
2184 Column = column;
|
|
|
2185 Expression = expression;
|
|
|
2186
|
|
|
2187 if (expression is SqlParameter)
|
|
|
2188 {
|
|
|
2189 var p = (SqlParameter)expression;
|
|
|
2190
|
|
|
2191 //if (type.IsEnum)
|
|
|
2192 // p.SetEnumConverter(type, mappingSchema);
|
|
|
2193
|
|
|
2194 if (column is SqlField)
|
|
|
2195 {
|
|
|
2196 var field = (SqlField)column;
|
|
|
2197
|
|
|
2198 if (field.MemberMapper != null)
|
|
|
2199 {
|
|
|
2200 if (field.MemberMapper.MapMemberInfo.IsDbTypeSet)
|
|
|
2201 p.DbType = field.MemberMapper.MapMemberInfo.DbType;
|
|
|
2202
|
|
|
2203 if (field.MemberMapper.MapMemberInfo.IsDbSizeSet)
|
|
|
2204 p.DbSize = field.MemberMapper.MapMemberInfo.DbSize;
|
|
|
2205 }
|
|
|
2206 }
|
|
|
2207 }
|
|
|
2208 }
|
|
|
2209
|
|
|
2210 public ISqlExpression Column { get; set; }
|
|
|
2211 public ISqlExpression Expression { get; set; }
|
|
|
2212
|
|
|
2213 #region Overrides
|
|
|
2214
|
|
|
2215 #if OVERRIDETOSTRING
|
|
|
2216
|
|
|
2217 public override string ToString()
|
|
|
2218 {
|
|
|
2219 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2220 }
|
|
|
2221
|
|
|
2222 #endif
|
|
|
2223
|
|
|
2224 #endregion
|
|
|
2225
|
|
|
2226 #region ICloneableElement Members
|
|
|
2227
|
|
|
2228 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
2229 {
|
|
|
2230 if (!doClone(this))
|
|
|
2231 return this;
|
|
|
2232
|
|
|
2233 ICloneableElement clone;
|
|
|
2234
|
|
|
2235 if (!objectTree.TryGetValue(this, out clone))
|
|
|
2236 {
|
|
|
2237 objectTree.Add(this, clone = new SetExpression(
|
|
|
2238 (ISqlExpression)Column. Clone(objectTree, doClone),
|
|
|
2239 (ISqlExpression)Expression.Clone(objectTree, doClone)));
|
|
|
2240 }
|
|
|
2241
|
|
|
2242 return clone;
|
|
|
2243 }
|
|
|
2244
|
|
|
2245 #endregion
|
|
|
2246
|
|
|
2247 #region ISqlExpressionWalkable Members
|
|
|
2248
|
|
|
2249 [Obsolete]
|
|
|
2250 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2251 {
|
|
|
2252 Column = Column. Walk(skipColumns, func);
|
|
|
2253 Expression = Expression.Walk(skipColumns, func);
|
|
|
2254 return null;
|
|
|
2255 }
|
|
|
2256
|
|
|
2257 #endregion
|
|
|
2258
|
|
|
2259 #region IQueryElement Members
|
|
|
2260
|
|
|
2261 public QueryElementType ElementType { get { return QueryElementType.SetExpression; } }
|
|
|
2262
|
|
|
2263 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2264 {
|
|
|
2265 Column.ToString(sb, dic);
|
|
|
2266 sb.Append(" = ");
|
|
|
2267 Expression.ToString(sb, dic);
|
|
|
2268
|
|
|
2269 return sb;
|
|
|
2270 }
|
|
|
2271
|
|
|
2272 #endregion
|
|
|
2273 }
|
|
|
2274
|
|
|
2275 public class InsertClause : IQueryElement, ISqlExpressionWalkable, ICloneableElement
|
|
|
2276 {
|
|
|
2277 public InsertClause()
|
|
|
2278 {
|
|
|
2279 Items = new List<SetExpression>();
|
|
|
2280 }
|
|
|
2281
|
|
|
2282 public List<SetExpression> Items { get; private set; }
|
|
|
2283 public SqlTable Into { get; set; }
|
|
|
2284 public bool WithIdentity { get; set; }
|
|
|
2285
|
|
|
2286 #region Overrides
|
|
|
2287
|
|
|
2288 #if OVERRIDETOSTRING
|
|
|
2289
|
|
|
2290 public override string ToString()
|
|
|
2291 {
|
|
|
2292 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2293 }
|
|
|
2294
|
|
|
2295 #endif
|
|
|
2296
|
|
|
2297 #endregion
|
|
|
2298
|
|
|
2299 #region ICloneableElement Members
|
|
|
2300
|
|
|
2301 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
2302 {
|
|
|
2303 if (!doClone(this))
|
|
|
2304 return this;
|
|
|
2305
|
|
|
2306 var clone = new InsertClause { WithIdentity = WithIdentity };
|
|
|
2307
|
|
|
2308 if (Into != null)
|
|
|
2309 clone.Into = (SqlTable)Into.Clone(objectTree, doClone);
|
|
|
2310
|
|
|
2311 foreach (var item in Items)
|
|
|
2312 clone.Items.Add((SetExpression)item.Clone(objectTree, doClone));
|
|
|
2313
|
|
|
2314 objectTree.Add(this, clone);
|
|
|
2315
|
|
|
2316 return clone;
|
|
|
2317 }
|
|
|
2318
|
|
|
2319 #endregion
|
|
|
2320
|
|
|
2321 #region ISqlExpressionWalkable Members
|
|
|
2322
|
|
|
2323 [Obsolete]
|
|
|
2324 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2325 {
|
|
|
2326 if (Into != null)
|
|
|
2327 ((ISqlExpressionWalkable)Into).Walk(skipColumns, func);
|
|
|
2328
|
|
|
2329 foreach (var t in Items)
|
|
|
2330 ((ISqlExpressionWalkable)t).Walk(skipColumns, func);
|
|
|
2331
|
|
|
2332 return null;
|
|
|
2333 }
|
|
|
2334
|
|
|
2335 #endregion
|
|
|
2336
|
|
|
2337 #region IQueryElement Members
|
|
|
2338
|
|
|
2339 public QueryElementType ElementType { get { return QueryElementType.InsertClause; } }
|
|
|
2340
|
|
|
2341 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2342 {
|
|
|
2343 sb.Append("VALUES ");
|
|
|
2344
|
|
|
2345 if (Into != null)
|
|
|
2346 ((IQueryElement)Into).ToString(sb, dic);
|
|
|
2347
|
|
|
2348 sb.AppendLine();
|
|
|
2349
|
|
|
2350 foreach (var e in Items)
|
|
|
2351 {
|
|
|
2352 sb.Append("\t");
|
|
|
2353 ((IQueryElement)e).ToString(sb, dic);
|
|
|
2354 sb.AppendLine();
|
|
|
2355 }
|
|
|
2356
|
|
|
2357 return sb;
|
|
|
2358 }
|
|
|
2359
|
|
|
2360 #endregion
|
|
|
2361 }
|
|
|
2362
|
|
|
2363 private InsertClause _insert;
|
|
|
2364 public InsertClause Insert
|
|
|
2365 {
|
|
|
2366 get { return _insert ?? (_insert = new InsertClause()); }
|
|
|
2367 }
|
|
|
2368
|
|
|
2369 public void ClearInsert()
|
|
|
2370 {
|
|
|
2371 _insert = null;
|
|
|
2372 }
|
|
|
2373
|
|
|
2374 #endregion
|
|
|
2375
|
|
|
2376 #region UpdateClause
|
|
|
2377
|
|
|
2378 public class UpdateClause : IQueryElement, ISqlExpressionWalkable, ICloneableElement
|
|
|
2379 {
|
|
|
2380 public UpdateClause()
|
|
|
2381 {
|
|
|
2382 Items = new List<SetExpression>();
|
|
|
2383 Keys = new List<SetExpression>();
|
|
|
2384 }
|
|
|
2385
|
|
|
2386 public List<SetExpression> Items { get; private set; }
|
|
|
2387 public List<SetExpression> Keys { get; private set; }
|
|
|
2388 public SqlTable Table { get; set; }
|
|
|
2389
|
|
|
2390 #region Overrides
|
|
|
2391
|
|
|
2392 #if OVERRIDETOSTRING
|
|
|
2393
|
|
|
2394 public override string ToString()
|
|
|
2395 {
|
|
|
2396 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2397 }
|
|
|
2398
|
|
|
2399 #endif
|
|
|
2400
|
|
|
2401 #endregion
|
|
|
2402
|
|
|
2403 #region ICloneableElement Members
|
|
|
2404
|
|
|
2405 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
2406 {
|
|
|
2407 if (!doClone(this))
|
|
|
2408 return this;
|
|
|
2409
|
|
|
2410 var clone = new UpdateClause();
|
|
|
2411
|
|
|
2412 if (Table != null)
|
|
|
2413 clone.Table = (SqlTable)Table.Clone(objectTree, doClone);
|
|
|
2414
|
|
|
2415 foreach (var item in Items)
|
|
|
2416 clone.Items.Add((SetExpression)item.Clone(objectTree, doClone));
|
|
|
2417
|
|
|
2418 foreach (var item in Keys)
|
|
|
2419 clone.Keys.Add((SetExpression)item.Clone(objectTree, doClone));
|
|
|
2420
|
|
|
2421 objectTree.Add(this, clone);
|
|
|
2422
|
|
|
2423 return clone;
|
|
|
2424 }
|
|
|
2425
|
|
|
2426 #endregion
|
|
|
2427
|
|
|
2428 #region ISqlExpressionWalkable Members
|
|
|
2429
|
|
|
2430 [Obsolete]
|
|
|
2431 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2432 {
|
|
|
2433 if (Table != null)
|
|
|
2434 ((ISqlExpressionWalkable)Table).Walk(skipColumns, func);
|
|
|
2435
|
|
|
2436 foreach (var t in Items)
|
|
|
2437 ((ISqlExpressionWalkable)t).Walk(skipColumns, func);
|
|
|
2438
|
|
|
2439 foreach (var t in Keys)
|
|
|
2440 ((ISqlExpressionWalkable)t).Walk(skipColumns, func);
|
|
|
2441
|
|
|
2442 return null;
|
|
|
2443 }
|
|
|
2444
|
|
|
2445 #endregion
|
|
|
2446
|
|
|
2447 #region IQueryElement Members
|
|
|
2448
|
|
|
2449 public QueryElementType ElementType { get { return QueryElementType.UpdateClause; } }
|
|
|
2450
|
|
|
2451 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2452 {
|
|
|
2453 sb.Append("SET ");
|
|
|
2454
|
|
|
2455 if (Table != null)
|
|
|
2456 ((IQueryElement)Table).ToString(sb, dic);
|
|
|
2457
|
|
|
2458 sb.AppendLine();
|
|
|
2459
|
|
|
2460 foreach (var e in Items)
|
|
|
2461 {
|
|
|
2462 sb.Append("\t");
|
|
|
2463 ((IQueryElement)e).ToString(sb, dic);
|
|
|
2464 sb.AppendLine();
|
|
|
2465 }
|
|
|
2466
|
|
|
2467 return sb;
|
|
|
2468 }
|
|
|
2469
|
|
|
2470 #endregion
|
|
|
2471 }
|
|
|
2472
|
|
|
2473 private UpdateClause _update;
|
|
|
2474 public UpdateClause Update
|
|
|
2475 {
|
|
|
2476 get { return _update ?? (_update = new UpdateClause()); }
|
|
|
2477 }
|
|
|
2478
|
|
|
2479 public void ClearUpdate()
|
|
|
2480 {
|
|
|
2481 _update = null;
|
|
|
2482 }
|
|
|
2483
|
|
|
2484 #endregion
|
|
|
2485
|
|
|
2486 #region DeleteClause
|
|
|
2487
|
|
|
2488 public class DeleteClause : IQueryElement, ISqlExpressionWalkable, ICloneableElement
|
|
|
2489 {
|
|
|
2490 public SqlTable Table { get; set; }
|
|
|
2491
|
|
|
2492 #region Overrides
|
|
|
2493
|
|
|
2494 #if OVERRIDETOSTRING
|
|
|
2495
|
|
|
2496 public override string ToString()
|
|
|
2497 {
|
|
|
2498 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2499 }
|
|
|
2500
|
|
|
2501 #endif
|
|
|
2502
|
|
|
2503 #endregion
|
|
|
2504
|
|
|
2505 #region ICloneableElement Members
|
|
|
2506
|
|
|
2507 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
2508 {
|
|
|
2509 if (!doClone(this))
|
|
|
2510 return this;
|
|
|
2511
|
|
|
2512 var clone = new DeleteClause();
|
|
|
2513
|
|
|
2514 if (Table != null)
|
|
|
2515 clone.Table = (SqlTable)Table.Clone(objectTree, doClone);
|
|
|
2516
|
|
|
2517 objectTree.Add(this, clone);
|
|
|
2518
|
|
|
2519 return clone;
|
|
|
2520 }
|
|
|
2521
|
|
|
2522 #endregion
|
|
|
2523
|
|
|
2524 #region ISqlExpressionWalkable Members
|
|
|
2525
|
|
|
2526 [Obsolete]
|
|
|
2527 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2528 {
|
|
|
2529 if (Table != null)
|
|
|
2530 ((ISqlExpressionWalkable)Table).Walk(skipColumns, func);
|
|
|
2531
|
|
|
2532 return null;
|
|
|
2533 }
|
|
|
2534
|
|
|
2535 #endregion
|
|
|
2536
|
|
|
2537 #region IQueryElement Members
|
|
|
2538
|
|
|
2539 public QueryElementType ElementType { get { return QueryElementType.DeleteClause; } }
|
|
|
2540
|
|
|
2541 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2542 {
|
|
|
2543 sb.Append("DELETE FROM ");
|
|
|
2544
|
|
|
2545 if (Table != null)
|
|
|
2546 ((IQueryElement)Table).ToString(sb, dic);
|
|
|
2547
|
|
|
2548 sb.AppendLine();
|
|
|
2549
|
|
|
2550 return sb;
|
|
|
2551 }
|
|
|
2552
|
|
|
2553 #endregion
|
|
|
2554 }
|
|
|
2555
|
|
|
2556 private DeleteClause _delete;
|
|
|
2557 public DeleteClause Delete
|
|
|
2558 {
|
|
|
2559 get { return _delete ?? (_delete = new DeleteClause()); }
|
|
|
2560 }
|
|
|
2561
|
|
|
2562 public void ClearDelete()
|
|
|
2563 {
|
|
|
2564 _delete = null;
|
|
|
2565 }
|
|
|
2566
|
|
|
2567 #endregion
|
|
|
2568
|
|
|
2569 #region FromClause
|
|
|
2570
|
|
|
2571 public class FromClause : ClauseBase, IQueryElement, ISqlExpressionWalkable
|
|
|
2572 {
|
|
|
2573 #region Join
|
|
|
2574
|
|
|
2575 public class Join : ConditionBase<Join,Join.Next>
|
|
|
2576 {
|
|
|
2577 public class Next
|
|
|
2578 {
|
|
|
2579 internal Next(Join parent)
|
|
|
2580 {
|
|
|
2581 _parent = parent;
|
|
|
2582 }
|
|
|
2583
|
|
|
2584 readonly Join _parent;
|
|
|
2585
|
|
|
2586 public Join Or { get { return _parent.SetOr(true); } }
|
|
|
2587 public Join And { get { return _parent.SetOr(false); } }
|
|
|
2588
|
|
|
2589 public static implicit operator Join(Next next)
|
|
|
2590 {
|
|
|
2591 return next._parent;
|
|
|
2592 }
|
|
|
2593 }
|
|
|
2594
|
|
|
2595 protected override SearchCondition Search
|
|
|
2596 {
|
|
|
2597 get { return JoinedTable.Condition; }
|
|
|
2598 }
|
|
|
2599
|
|
|
2600 protected override Next GetNext()
|
|
|
2601 {
|
|
|
2602 return new Next(this);
|
|
|
2603 }
|
|
|
2604
|
|
|
2605 internal Join(JoinType joinType, ISqlTableSource table, string alias, bool isWeak, ICollection<Join> joins)
|
|
|
2606 {
|
|
|
2607 JoinedTable = new JoinedTable(joinType, table, alias, isWeak);
|
|
|
2608
|
|
|
2609 if (joins != null && joins.Count > 0)
|
|
|
2610 foreach (var join in joins)
|
|
|
2611 JoinedTable.Table.Joins.Add(join.JoinedTable);
|
|
|
2612 }
|
|
|
2613
|
|
|
2614 public JoinedTable JoinedTable { get; private set; }
|
|
|
2615 }
|
|
|
2616
|
|
|
2617 #endregion
|
|
|
2618
|
|
|
2619 internal FromClause(SqlQuery sqlQuery) : base(sqlQuery)
|
|
|
2620 {
|
|
|
2621 }
|
|
|
2622
|
|
|
2623 internal FromClause(
|
|
|
2624 SqlQuery sqlQuery,
|
|
|
2625 FromClause clone,
|
|
|
2626 Dictionary<ICloneableElement,ICloneableElement> objectTree,
|
|
|
2627 Predicate<ICloneableElement> doClone)
|
|
|
2628 : base(sqlQuery)
|
|
|
2629 {
|
|
|
2630 _tables.AddRange(clone._tables.ConvertAll(ts => (TableSource)ts.Clone(objectTree, doClone)));
|
|
|
2631 }
|
|
|
2632
|
|
|
2633 internal FromClause(IEnumerable<TableSource> tables)
|
|
|
2634 : base(null)
|
|
|
2635 {
|
|
|
2636 _tables.AddRange(tables);
|
|
|
2637 }
|
|
|
2638
|
|
|
2639 public FromClause Table(ISqlTableSource table, params FJoin[] joins)
|
|
|
2640 {
|
|
|
2641 return Table(table, null, joins);
|
|
|
2642 }
|
|
|
2643
|
|
|
2644 public FromClause Table(ISqlTableSource table, string alias, params FJoin[] joins)
|
|
|
2645 {
|
|
|
2646 var ts = AddOrGetTable(table, alias);
|
|
|
2647
|
|
|
2648 if (joins != null && joins.Length > 0)
|
|
|
2649 foreach (var join in joins)
|
|
|
2650 ts.Joins.Add(join.JoinedTable);
|
|
|
2651
|
|
|
2652 return this;
|
|
|
2653 }
|
|
|
2654
|
|
|
2655 TableSource GetTable(ISqlTableSource table, string alias)
|
|
|
2656 {
|
|
|
2657 foreach (var ts in Tables)
|
|
|
2658 if (ts.Source == table)
|
|
|
2659 if (alias == null || ts.Alias == alias)
|
|
|
2660 return ts;
|
|
|
2661 else
|
|
|
2662 throw new ArgumentException("alias");
|
|
|
2663
|
|
|
2664 return null;
|
|
|
2665 }
|
|
|
2666
|
|
|
2667 TableSource AddOrGetTable(ISqlTableSource table, string alias)
|
|
|
2668 {
|
|
|
2669 var ts = GetTable(table, alias);
|
|
|
2670
|
|
|
2671 if (ts != null)
|
|
|
2672 return ts;
|
|
|
2673
|
|
|
2674 var t = new TableSource(table, alias);
|
|
|
2675
|
|
|
2676 Tables.Add(t);
|
|
|
2677
|
|
|
2678 return t;
|
|
|
2679 }
|
|
|
2680
|
|
|
2681 public TableSource this[ISqlTableSource table]
|
|
|
2682 {
|
|
|
2683 get { return this[table, null]; }
|
|
|
2684 }
|
|
|
2685
|
|
|
2686 public TableSource this[ISqlTableSource table, string alias]
|
|
|
2687 {
|
|
|
2688 get
|
|
|
2689 {
|
|
|
2690 foreach (var ts in Tables)
|
|
|
2691 {
|
|
|
2692 var t = CheckTableSource(ts, table, alias);
|
|
|
2693
|
|
|
2694 if (t != null)
|
|
|
2695 return t;
|
|
|
2696 }
|
|
|
2697
|
|
|
2698 return null;
|
|
|
2699 }
|
|
|
2700 }
|
|
|
2701
|
|
|
2702 public bool IsChild(ISqlTableSource table)
|
|
|
2703 {
|
|
|
2704 foreach (var ts in Tables)
|
|
|
2705 if (ts.Source == table || CheckChild(ts.Joins, table))
|
|
|
2706 return true;
|
|
|
2707 return false;
|
|
|
2708 }
|
|
|
2709
|
|
|
2710 static bool CheckChild(IEnumerable<JoinedTable> joins, ISqlTableSource table)
|
|
|
2711 {
|
|
|
2712 foreach (var j in joins)
|
|
|
2713 if (j.Table.Source == table || CheckChild(j.Table.Joins, table))
|
|
|
2714 return true;
|
|
|
2715 return false;
|
|
|
2716 }
|
|
|
2717
|
|
|
2718 readonly List<TableSource> _tables = new List<TableSource>();
|
|
|
2719 public List<TableSource> Tables
|
|
|
2720 {
|
|
|
2721 get { return _tables; }
|
|
|
2722 }
|
|
|
2723
|
|
|
2724 static IEnumerable<ISqlTableSource> GetJoinTables(TableSource source, QueryElementType elementType)
|
|
|
2725 {
|
|
|
2726 if (source.Source.ElementType == elementType)
|
|
|
2727 yield return source.Source;
|
|
|
2728
|
|
|
2729 foreach (var join in source.Joins)
|
|
|
2730 foreach (var table in GetJoinTables(join.Table, elementType))
|
|
|
2731 yield return table;
|
|
|
2732 }
|
|
|
2733
|
|
|
2734 internal IEnumerable<ISqlTableSource> GetFromTables()
|
|
|
2735 {
|
|
|
2736 return Tables.SelectMany(_ => GetJoinTables(_, QueryElementType.SqlTable));
|
|
|
2737 }
|
|
|
2738
|
|
|
2739 internal IEnumerable<ISqlTableSource> GetFromQueries()
|
|
|
2740 {
|
|
|
2741 return Tables.SelectMany(_ => GetJoinTables(_, QueryElementType.SqlQuery));
|
|
|
2742 }
|
|
|
2743
|
|
|
2744 static TableSource FindTableSource(TableSource source, SqlTable table)
|
|
|
2745 {
|
|
|
2746 if (source.Source == table)
|
|
|
2747 return source;
|
|
|
2748
|
|
|
2749 foreach (var join in source.Joins)
|
|
|
2750 {
|
|
|
2751 var ts = FindTableSource(join.Table, table);
|
|
|
2752 if (ts != null)
|
|
|
2753 return ts;
|
|
|
2754 }
|
|
|
2755
|
|
|
2756 return null;
|
|
|
2757 }
|
|
|
2758
|
|
|
2759 public ISqlTableSource FindTableSource(SqlTable table)
|
|
|
2760 {
|
|
|
2761 foreach (var source in Tables)
|
|
|
2762 {
|
|
|
2763 var ts = FindTableSource(source, table);
|
|
|
2764 if (ts != null)
|
|
|
2765 return ts;
|
|
|
2766 }
|
|
|
2767
|
|
|
2768 return null;
|
|
|
2769 }
|
|
|
2770
|
|
|
2771 #region Overrides
|
|
|
2772
|
|
|
2773 #if OVERRIDETOSTRING
|
|
|
2774
|
|
|
2775 public override string ToString()
|
|
|
2776 {
|
|
|
2777 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2778 }
|
|
|
2779
|
|
|
2780 #endif
|
|
|
2781
|
|
|
2782 #endregion
|
|
|
2783
|
|
|
2784 #region ISqlExpressionWalkable Members
|
|
|
2785
|
|
|
2786 [Obsolete]
|
|
|
2787 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
2788 {
|
|
|
2789 for (var i = 0; i < Tables.Count; i++)
|
|
|
2790 ((ISqlExpressionWalkable)Tables[i]).Walk(skipColumns, func);
|
|
|
2791
|
|
|
2792 return null;
|
|
|
2793 }
|
|
|
2794
|
|
|
2795 #endregion
|
|
|
2796
|
|
|
2797 #region IQueryElement Members
|
|
|
2798
|
|
|
2799 public QueryElementType ElementType { get { return QueryElementType.FromClause; } }
|
|
|
2800
|
|
|
2801 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2802 {
|
|
|
2803 sb.Append(" \nFROM \n");
|
|
|
2804
|
|
|
2805 if (Tables.Count > 0)
|
|
|
2806 {
|
|
|
2807 foreach (IQueryElement ts in Tables)
|
|
|
2808 {
|
|
|
2809 sb.Append('\t');
|
|
|
2810 var len = sb.Length;
|
|
|
2811 ts.ToString(sb, dic).Replace("\n", "\n\t", len, sb.Length - len);
|
|
|
2812 sb.Append(", ");
|
|
|
2813 }
|
|
|
2814
|
|
|
2815 sb.Length -= 2;
|
|
|
2816 }
|
|
|
2817
|
|
|
2818 return sb;
|
|
|
2819 }
|
|
|
2820
|
|
|
2821 #endregion
|
|
|
2822 }
|
|
|
2823
|
|
|
2824 public static FJoin InnerJoin (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Inner, table, null, false, joins); }
|
|
|
2825 public static FJoin InnerJoin (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Inner, table, alias, false, joins); }
|
|
|
2826 public static FJoin LeftJoin (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Left, table, null, false, joins); }
|
|
|
2827 public static FJoin LeftJoin (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Left, table, alias, false, joins); }
|
|
|
2828 public static FJoin Join (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Auto, table, null, false, joins); }
|
|
|
2829 public static FJoin Join (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Auto, table, alias, false, joins); }
|
|
|
2830 public static FJoin CrossApply (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.CrossApply, table, null, false, joins); }
|
|
|
2831 public static FJoin CrossApply (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.CrossApply, table, alias, false, joins); }
|
|
|
2832 public static FJoin OuterApply (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.OuterApply, table, null, false, joins); }
|
|
|
2833 public static FJoin OuterApply (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.OuterApply, table, alias, false, joins); }
|
|
|
2834
|
|
|
2835 public static FJoin WeakInnerJoin(ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Inner, table, null, true, joins); }
|
|
|
2836 public static FJoin WeakInnerJoin(ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Inner, table, alias, true, joins); }
|
|
|
2837 public static FJoin WeakLeftJoin (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Left, table, null, true, joins); }
|
|
|
2838 public static FJoin WeakLeftJoin (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Left, table, alias, true, joins); }
|
|
|
2839 public static FJoin WeakJoin (ISqlTableSource table, params FJoin[] joins) { return new FJoin(JoinType.Auto, table, null, true, joins); }
|
|
|
2840 public static FJoin WeakJoin (ISqlTableSource table, string alias, params FJoin[] joins) { return new FJoin(JoinType.Auto, table, alias, true, joins); }
|
|
|
2841
|
|
|
2842 private FromClause _from;
|
|
|
2843 public FromClause From
|
|
|
2844 {
|
|
|
2845 get { return _from; }
|
|
|
2846 }
|
|
|
2847
|
|
|
2848 #endregion
|
|
|
2849
|
|
|
2850 #region WhereClause
|
|
|
2851
|
|
|
2852 public class WhereClause : ClauseBase<WhereClause,WhereClause.Next>, IQueryElement, ISqlExpressionWalkable
|
|
|
2853 {
|
|
|
2854 public class Next : ClauseBase
|
|
|
2855 {
|
|
|
2856 internal Next(WhereClause parent) : base(parent.SqlQuery)
|
|
|
2857 {
|
|
|
2858 _parent = parent;
|
|
|
2859 }
|
|
|
2860
|
|
|
2861 readonly WhereClause _parent;
|
|
|
2862
|
|
|
2863 public WhereClause Or { get { return _parent.SetOr(true); } }
|
|
|
2864 public WhereClause And { get { return _parent.SetOr(false); } }
|
|
|
2865 }
|
|
|
2866
|
|
|
2867 internal WhereClause(SqlQuery sqlQuery) : base(sqlQuery)
|
|
|
2868 {
|
|
|
2869 SearchCondition = new SearchCondition();
|
|
|
2870 }
|
|
|
2871
|
|
|
2872 internal WhereClause(
|
|
|
2873 SqlQuery sqlQuery,
|
|
|
2874 WhereClause clone,
|
|
|
2875 Dictionary<ICloneableElement,ICloneableElement> objectTree,
|
|
|
2876 Predicate<ICloneableElement> doClone)
|
|
|
2877 : base(sqlQuery)
|
|
|
2878 {
|
|
|
2879 SearchCondition = (SearchCondition)clone.SearchCondition.Clone(objectTree, doClone);
|
|
|
2880 }
|
|
|
2881
|
|
|
2882 internal WhereClause(SearchCondition searchCondition) : base(null)
|
|
|
2883 {
|
|
|
2884 SearchCondition = searchCondition;
|
|
|
2885 }
|
|
|
2886
|
|
|
2887 public SearchCondition SearchCondition { get; private set; }
|
|
|
2888
|
|
|
2889 public bool IsEmpty
|
|
|
2890 {
|
|
|
2891 get { return SearchCondition.Conditions.Count == 0; }
|
|
|
2892 }
|
|
|
2893
|
|
|
2894 protected override SearchCondition Search
|
|
|
2895 {
|
|
|
2896 get { return SearchCondition; }
|
|
|
2897 }
|
|
|
2898
|
|
|
2899 protected override Next GetNext()
|
|
|
2900 {
|
|
|
2901 return new Next(this);
|
|
|
2902 }
|
|
|
2903
|
|
|
2904 #if OVERRIDETOSTRING
|
|
|
2905
|
|
|
2906 public override string ToString()
|
|
|
2907 {
|
|
|
2908 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
2909 }
|
|
|
2910
|
|
|
2911 #endif
|
|
|
2912
|
|
|
2913 #region ISqlExpressionWalkable Members
|
|
|
2914
|
|
|
2915 [Obsolete]
|
|
|
2916 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> action)
|
|
|
2917 {
|
|
|
2918 SearchCondition = (SearchCondition)((ISqlExpressionWalkable)SearchCondition).Walk(skipColumns, action);
|
|
|
2919 return null;
|
|
|
2920 }
|
|
|
2921
|
|
|
2922 #endregion
|
|
|
2923
|
|
|
2924 #region IQueryElement Members
|
|
|
2925
|
|
|
2926 public QueryElementType ElementType
|
|
|
2927 {
|
|
|
2928 get { return QueryElementType.WhereClause; }
|
|
|
2929 }
|
|
|
2930
|
|
|
2931 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
2932 {
|
|
|
2933 if (Search.Conditions.Count == 0)
|
|
|
2934 return sb;
|
|
|
2935
|
|
|
2936 sb.Append("\nWHERE\n\t");
|
|
|
2937 return ((IQueryElement)Search).ToString(sb, dic);
|
|
|
2938 }
|
|
|
2939
|
|
|
2940 #endregion
|
|
|
2941 }
|
|
|
2942
|
|
|
2943 private WhereClause _where;
|
|
|
2944 public WhereClause Where
|
|
|
2945 {
|
|
|
2946 get { return _where; }
|
|
|
2947 }
|
|
|
2948
|
|
|
2949 #endregion
|
|
|
2950
|
|
|
2951 #region GroupByClause
|
|
|
2952
|
|
|
2953 public class GroupByClause : ClauseBase, IQueryElement, ISqlExpressionWalkable
|
|
|
2954 {
|
|
|
2955 internal GroupByClause(SqlQuery sqlQuery) : base(sqlQuery)
|
|
|
2956 {
|
|
|
2957 }
|
|
|
2958
|
|
|
2959 internal GroupByClause(
|
|
|
2960 SqlQuery sqlQuery,
|
|
|
2961 GroupByClause clone,
|
|
|
2962 Dictionary<ICloneableElement,ICloneableElement> objectTree,
|
|
|
2963 Predicate<ICloneableElement> doClone)
|
|
|
2964 : base(sqlQuery)
|
|
|
2965 {
|
|
|
2966 _items.AddRange(clone._items.ConvertAll(e => (ISqlExpression)e.Clone(objectTree, doClone)));
|
|
|
2967 }
|
|
|
2968
|
|
|
2969 internal GroupByClause(IEnumerable<ISqlExpression> items) : base(null)
|
|
|
2970 {
|
|
|
2971 _items.AddRange(items);
|
|
|
2972 }
|
|
|
2973
|
|
|
2974 public GroupByClause Expr(ISqlExpression expr)
|
|
|
2975 {
|
|
|
2976 Add(expr);
|
|
|
2977 return this;
|
|
|
2978 }
|
|
|
2979
|
|
|
2980 public GroupByClause Field(SqlField field)
|
|
|
2981 {
|
|
|
2982 return Expr(field);
|
|
|
2983 }
|
|
|
2984
|
|
|
2985 void Add(ISqlExpression expr)
|
|
|
2986 {
|
|
|
2987 foreach (var e in Items)
|
|
|
2988 if (e.Equals(expr))
|
|
|
2989 return;
|
|
|
2990
|
|
|
2991 Items.Add(expr);
|
|
|
2992 }
|
|
|
2993
|
|
|
2994 readonly List<ISqlExpression> _items = new List<ISqlExpression>();
|
|
|
2995 public List<ISqlExpression> Items
|
|
|
2996 {
|
|
|
2997 get { return _items; }
|
|
|
2998 }
|
|
|
2999
|
|
|
3000 public bool IsEmpty
|
|
|
3001 {
|
|
|
3002 get { return Items.Count == 0; }
|
|
|
3003 }
|
|
|
3004
|
|
|
3005 #if OVERRIDETOSTRING
|
|
|
3006
|
|
|
3007 public override string ToString()
|
|
|
3008 {
|
|
|
3009 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
3010 }
|
|
|
3011
|
|
|
3012 #endif
|
|
|
3013
|
|
|
3014 #region ISqlExpressionWalkable Members
|
|
|
3015
|
|
|
3016 [Obsolete]
|
|
|
3017 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
3018 {
|
|
|
3019 for (var i = 0; i < Items.Count; i++)
|
|
|
3020 Items[i] = Items[i].Walk(skipColumns, func);
|
|
|
3021
|
|
|
3022 return null;
|
|
|
3023 }
|
|
|
3024
|
|
|
3025 #endregion
|
|
|
3026
|
|
|
3027 #region IQueryElement Members
|
|
|
3028
|
|
|
3029 public QueryElementType ElementType { get { return QueryElementType.GroupByClause; } }
|
|
|
3030
|
|
|
3031 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
3032 {
|
|
|
3033 if (Items.Count == 0)
|
|
|
3034 return sb;
|
|
|
3035
|
|
|
3036 sb.Append(" \nGROUP BY \n");
|
|
|
3037
|
|
|
3038 foreach (var item in Items)
|
|
|
3039 {
|
|
|
3040 sb.Append('\t');
|
|
|
3041 item.ToString(sb, dic);
|
|
|
3042 sb.Append(",");
|
|
|
3043 }
|
|
|
3044
|
|
|
3045 sb.Length--;
|
|
|
3046
|
|
|
3047 return sb;
|
|
|
3048 }
|
|
|
3049
|
|
|
3050 #endregion
|
|
|
3051 }
|
|
|
3052
|
|
|
3053 private GroupByClause _groupBy;
|
|
|
3054 public GroupByClause GroupBy
|
|
|
3055 {
|
|
|
3056 get { return _groupBy; }
|
|
|
3057 }
|
|
|
3058
|
|
|
3059 #endregion
|
|
|
3060
|
|
|
3061 #region HavingClause
|
|
|
3062
|
|
|
3063 private WhereClause _having;
|
|
|
3064 public WhereClause Having
|
|
|
3065 {
|
|
|
3066 get { return _having; }
|
|
|
3067 }
|
|
|
3068
|
|
|
3069 #endregion
|
|
|
3070
|
|
|
3071 #region OrderByClause
|
|
|
3072
|
|
|
3073 public class OrderByClause : ClauseBase, IQueryElement, ISqlExpressionWalkable
|
|
|
3074 {
|
|
|
3075 internal OrderByClause(SqlQuery sqlQuery) : base(sqlQuery)
|
|
|
3076 {
|
|
|
3077 }
|
|
|
3078
|
|
|
3079 internal OrderByClause(
|
|
|
3080 SqlQuery sqlQuery,
|
|
|
3081 OrderByClause clone,
|
|
|
3082 Dictionary<ICloneableElement,ICloneableElement> objectTree,
|
|
|
3083 Predicate<ICloneableElement> doClone)
|
|
|
3084 : base(sqlQuery)
|
|
|
3085 {
|
|
|
3086 _items.AddRange(clone._items.ConvertAll(item => (OrderByItem)item.Clone(objectTree, doClone)));
|
|
|
3087 }
|
|
|
3088
|
|
|
3089 internal OrderByClause(IEnumerable<OrderByItem> items) : base(null)
|
|
|
3090 {
|
|
|
3091 _items.AddRange(items);
|
|
|
3092 }
|
|
|
3093
|
|
|
3094 public OrderByClause Expr(ISqlExpression expr, bool isDescending)
|
|
|
3095 {
|
|
|
3096 Add(expr, isDescending);
|
|
|
3097 return this;
|
|
|
3098 }
|
|
|
3099
|
|
|
3100 public OrderByClause Expr (ISqlExpression expr) { return Expr(expr, false); }
|
|
|
3101 public OrderByClause ExprAsc (ISqlExpression expr) { return Expr(expr, false); }
|
|
|
3102 public OrderByClause ExprDesc (ISqlExpression expr) { return Expr(expr, true); }
|
|
|
3103 public OrderByClause Field (SqlField field, bool isDescending) { return Expr(field, isDescending); }
|
|
|
3104 public OrderByClause Field (SqlField field) { return Expr(field, false); }
|
|
|
3105 public OrderByClause FieldAsc (SqlField field) { return Expr(field, false); }
|
|
|
3106 public OrderByClause FieldDesc(SqlField field) { return Expr(field, true); }
|
|
|
3107
|
|
|
3108 void Add(ISqlExpression expr, bool isDescending)
|
|
|
3109 {
|
|
|
3110 foreach (var item in Items)
|
|
|
3111 if (item.Expression.Equals(expr, (x, y) =>
|
|
|
3112 {
|
|
|
3113 var col = x as Column;
|
|
|
3114 return col == null || !col.Parent.HasUnion || x == y;
|
|
|
3115 }))
|
|
|
3116 return;
|
|
|
3117
|
|
|
3118 Items.Add(new OrderByItem(expr, isDescending));
|
|
|
3119 }
|
|
|
3120
|
|
|
3121 readonly List<OrderByItem> _items = new List<OrderByItem>();
|
|
|
3122 public List<OrderByItem> Items
|
|
|
3123 {
|
|
|
3124 get { return _items; }
|
|
|
3125 }
|
|
|
3126
|
|
|
3127 public bool IsEmpty
|
|
|
3128 {
|
|
|
3129 get { return Items.Count == 0; }
|
|
|
3130 }
|
|
|
3131
|
|
|
3132 #if OVERRIDETOSTRING
|
|
|
3133
|
|
|
3134 public override string ToString()
|
|
|
3135 {
|
|
|
3136 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
3137 }
|
|
|
3138
|
|
|
3139 #endif
|
|
|
3140
|
|
|
3141 #region ISqlExpressionWalkable Members
|
|
|
3142
|
|
|
3143 [Obsolete]
|
|
|
3144 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
3145 {
|
|
|
3146 #pragma warning disable 0618
|
|
|
3147 foreach (var t in Items)
|
|
|
3148 t.Walk(skipColumns, func);
|
|
|
3149 #pragma warning restore 0618
|
|
|
3150 return null;
|
|
|
3151 }
|
|
|
3152
|
|
|
3153 #endregion
|
|
|
3154
|
|
|
3155 #region IQueryElement Members
|
|
|
3156
|
|
|
3157 public QueryElementType ElementType { get { return QueryElementType.OrderByClause; } }
|
|
|
3158
|
|
|
3159 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
3160 {
|
|
|
3161 if (Items.Count == 0)
|
|
|
3162 return sb;
|
|
|
3163
|
|
|
3164 sb.Append(" \nORDER BY \n");
|
|
|
3165
|
|
|
3166 foreach (IQueryElement item in Items)
|
|
|
3167 {
|
|
|
3168 sb.Append('\t');
|
|
|
3169 item.ToString(sb, dic);
|
|
|
3170 sb.Append(", ");
|
|
|
3171 }
|
|
|
3172
|
|
|
3173 sb.Length -= 2;
|
|
|
3174
|
|
|
3175 return sb;
|
|
|
3176 }
|
|
|
3177
|
|
|
3178 #endregion
|
|
|
3179 }
|
|
|
3180
|
|
|
3181 private OrderByClause _orderBy;
|
|
|
3182 public OrderByClause OrderBy
|
|
|
3183 {
|
|
|
3184 get { return _orderBy; }
|
|
|
3185 }
|
|
|
3186
|
|
|
3187 #endregion
|
|
|
3188
|
|
|
3189 #region Union
|
|
|
3190
|
|
|
3191 public class Union : IQueryElement
|
|
|
3192 {
|
|
|
3193 public Union()
|
|
|
3194 {
|
|
|
3195 }
|
|
|
3196
|
|
|
3197 public Union(SqlQuery sqlQuery, bool isAll)
|
|
|
3198 {
|
|
|
3199 SqlQuery = sqlQuery;
|
|
|
3200 IsAll = isAll;
|
|
|
3201 }
|
|
|
3202
|
|
|
3203 public SqlQuery SqlQuery { get; private set; }
|
|
|
3204 public bool IsAll { get; private set; }
|
|
|
3205
|
|
|
3206 public QueryElementType ElementType
|
|
|
3207 {
|
|
|
3208 get { return QueryElementType.Union; }
|
|
|
3209 }
|
|
|
3210
|
|
|
3211 #if OVERRIDETOSTRING
|
|
|
3212
|
|
|
3213 public override string ToString()
|
|
|
3214 {
|
|
|
3215 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
3216 }
|
|
|
3217
|
|
|
3218 #endif
|
|
|
3219
|
|
|
3220 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
3221 {
|
|
|
3222 sb.Append(" \nUNION").Append(IsAll ? " ALL" : "").Append(" \n");
|
|
|
3223 return ((IQueryElement)SqlQuery).ToString(sb, dic);
|
|
|
3224 }
|
|
|
3225 }
|
|
|
3226
|
|
|
3227 private List<Union> _unions;
|
|
|
3228 public List<Union> Unions
|
|
|
3229 {
|
|
|
3230 get { return _unions ?? (_unions = new List<Union>()); }
|
|
|
3231 }
|
|
|
3232
|
|
|
3233 public bool HasUnion { get { return _unions != null && _unions.Count > 0; } }
|
|
|
3234
|
|
|
3235 public void AddUnion(SqlQuery union, bool isAll)
|
|
|
3236 {
|
|
|
3237 Unions.Add(new Union(union, isAll));
|
|
|
3238 }
|
|
|
3239
|
|
|
3240 #endregion
|
|
|
3241
|
|
|
3242 #region FinalizeAndValidate
|
|
|
3243
|
|
|
3244 public void FinalizeAndValidate(bool isApplySupported, bool optimizeColumns)
|
|
|
3245 {
|
|
|
3246 #if DEBUG
|
|
|
3247 var sqlText = SqlText;
|
|
|
3248
|
|
|
3249 var dic = new Dictionary<SqlQuery,SqlQuery>();
|
|
|
3250
|
|
|
3251 new QueryVisitor().VisitAll(this, e =>
|
|
|
3252 {
|
|
|
3253 var sql = e as SqlQuery;
|
|
|
3254
|
|
|
3255 if (sql != null)
|
|
|
3256 {
|
|
|
3257 if (dic.ContainsKey(sql))
|
|
|
3258 throw new InvalidOperationException("SqlQuery circle reference detected.");
|
|
|
3259
|
|
|
3260 dic.Add(sql, sql);
|
|
|
3261 }
|
|
|
3262 });
|
|
|
3263 #endif
|
|
|
3264
|
|
|
3265 OptimizeUnions();
|
|
|
3266 FinalizeAndValidateInternal(isApplySupported, optimizeColumns, true, new List<ISqlTableSource>());
|
|
|
3267 ResolveFields();
|
|
|
3268 SetAliases();
|
|
|
3269
|
|
|
3270 #if DEBUG
|
|
|
3271 sqlText = SqlText;
|
|
|
3272 #endif
|
|
|
3273 }
|
|
|
3274
|
|
|
3275 class QueryData
|
|
|
3276 {
|
|
|
3277 public SqlQuery Query;
|
|
|
3278 public List<ISqlExpression> Fields = new List<ISqlExpression>();
|
|
|
3279 public List<QueryData> Queries = new List<QueryData>();
|
|
|
3280 }
|
|
|
3281
|
|
|
3282 void ResolveFields()
|
|
|
3283 {
|
|
|
3284 var root = GetQueryData();
|
|
|
3285
|
|
|
3286 ResolveFields(root);
|
|
|
3287 }
|
|
|
3288
|
|
|
3289 QueryData GetQueryData()
|
|
|
3290 {
|
|
|
3291 var data = new QueryData { Query = this };
|
|
|
3292
|
|
|
3293 new QueryVisitor().VisitParentFirst(this, e =>
|
|
|
3294 {
|
|
|
3295 switch (e.ElementType)
|
|
|
3296 {
|
|
|
3297 case QueryElementType.SqlField :
|
|
|
3298 {
|
|
|
3299 var field = (SqlField)e;
|
|
|
3300
|
|
|
3301 if (field.Name.Length != 1 || field.Name[0] != '*')
|
|
|
3302 data.Fields.Add(field);
|
|
|
3303
|
|
|
3304 break;
|
|
|
3305 }
|
|
|
3306
|
|
|
3307 case QueryElementType.SqlQuery :
|
|
|
3308 {
|
|
|
3309 if (e != this)
|
|
|
3310 {
|
|
|
3311 data.Queries.Add(((SqlQuery)e).GetQueryData());
|
|
|
3312 return false;
|
|
|
3313 }
|
|
|
3314
|
|
|
3315 break;
|
|
|
3316 }
|
|
|
3317
|
|
|
3318 case QueryElementType.Column :
|
|
|
3319 return ((Column)e).Parent == this;
|
|
|
3320
|
|
|
3321 case QueryElementType.SqlTable :
|
|
|
3322 return false;
|
|
|
3323 }
|
|
|
3324
|
|
|
3325 return true;
|
|
|
3326 });
|
|
|
3327
|
|
|
3328 return data;
|
|
|
3329 }
|
|
|
3330
|
|
|
3331 static TableSource FindField(SqlField field, TableSource table)
|
|
|
3332 {
|
|
|
3333 if (field.Table == table.Source)
|
|
|
3334 return table;
|
|
|
3335
|
|
|
3336 foreach (var @join in table.Joins)
|
|
|
3337 {
|
|
|
3338 var t = FindField(field, @join.Table);
|
|
|
3339
|
|
|
3340 if (t != null)
|
|
|
3341 return @join.Table;
|
|
|
3342 }
|
|
|
3343
|
|
|
3344 return null;
|
|
|
3345 }
|
|
|
3346
|
|
|
3347 static ISqlExpression GetColumn(QueryData data, SqlField field)
|
|
|
3348 {
|
|
|
3349 foreach (var query in data.Queries)
|
|
|
3350 {
|
|
|
3351 var q = query.Query;
|
|
|
3352
|
|
|
3353 foreach (var table in q.From.Tables)
|
|
|
3354 {
|
|
|
3355 var t = FindField(field, table);
|
|
|
3356
|
|
|
3357 if (t != null)
|
|
|
3358 {
|
|
|
3359 var n = q.Select.Columns.Count;
|
|
|
3360 var idx = q.Select.Add(field);
|
|
|
3361
|
|
|
3362 if (n != q.Select.Columns.Count)
|
|
|
3363 if (!q.GroupBy.IsEmpty || q.Select.Columns.Exists(c => IsAggregationFunction(c.Expression)))
|
|
|
3364 q.GroupBy.Items.Add(field);
|
|
|
3365
|
|
|
3366 return q.Select.Columns[idx];
|
|
|
3367 }
|
|
|
3368 }
|
|
|
3369 }
|
|
|
3370
|
|
|
3371 return null;
|
|
|
3372 }
|
|
|
3373
|
|
|
3374 static void ResolveFields(QueryData data)
|
|
|
3375 {
|
|
|
3376 if (data.Queries.Count == 0)
|
|
|
3377 return;
|
|
|
3378
|
|
|
3379 var dic = new Dictionary<ISqlExpression,ISqlExpression>();
|
|
|
3380
|
|
|
3381 foreach (SqlField field in data.Fields)
|
|
|
3382 {
|
|
|
3383 if (dic.ContainsKey(field))
|
|
|
3384 continue;
|
|
|
3385
|
|
|
3386 var found = false;
|
|
|
3387
|
|
|
3388 foreach (var table in data.Query.From.Tables)
|
|
|
3389 {
|
|
|
3390 found = FindField(field, table) != null;
|
|
|
3391
|
|
|
3392 if (found)
|
|
|
3393 break;
|
|
|
3394 }
|
|
|
3395
|
|
|
3396 if (!found)
|
|
|
3397 {
|
|
|
3398 var expr = GetColumn(data, field);
|
|
|
3399
|
|
|
3400 if (expr != null)
|
|
|
3401 dic.Add(field, expr);
|
|
|
3402 }
|
|
|
3403 }
|
|
|
3404
|
|
|
3405 if (dic.Count > 0)
|
|
|
3406 new QueryVisitor().VisitParentFirst(data.Query, e =>
|
|
|
3407 {
|
|
|
3408 ISqlExpression ex;
|
|
|
3409
|
|
|
3410 switch (e.ElementType)
|
|
|
3411 {
|
|
|
3412 case QueryElementType.SqlQuery :
|
|
|
3413 return e == data.Query;
|
|
|
3414
|
|
|
3415 case QueryElementType.SqlFunction :
|
|
|
3416 {
|
|
|
3417 var parms = ((SqlFunction)e).Parameters;
|
|
|
3418
|
|
|
3419 for (var i = 0; i < parms.Length; i++)
|
|
|
3420 if (dic.TryGetValue(parms[i], out ex))
|
|
|
3421 parms[i] = ex;
|
|
|
3422
|
|
|
3423 break;
|
|
|
3424 }
|
|
|
3425
|
|
|
3426 case QueryElementType.SqlExpression :
|
|
|
3427 {
|
|
|
3428 var parms = ((SqlExpression)e).Parameters;
|
|
|
3429
|
|
|
3430 for (var i = 0; i < parms.Length; i++)
|
|
|
3431 if (dic.TryGetValue(parms[i], out ex))
|
|
|
3432 parms[i] = ex;
|
|
|
3433
|
|
|
3434 break;
|
|
|
3435 }
|
|
|
3436
|
|
|
3437 case QueryElementType.SqlBinaryExpression :
|
|
|
3438 {
|
|
|
3439 var expr = (SqlBinaryExpression)e;
|
|
|
3440 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3441 if (dic.TryGetValue(expr.Expr2, out ex)) expr.Expr2 = ex;
|
|
|
3442 break;
|
|
|
3443 }
|
|
|
3444
|
|
|
3445 case QueryElementType.ExprPredicate :
|
|
|
3446 case QueryElementType.NotExprPredicate :
|
|
|
3447 case QueryElementType.IsNullPredicate :
|
|
|
3448 case QueryElementType.InSubQueryPredicate :
|
|
|
3449 {
|
|
|
3450 var expr = (Predicate.Expr)e;
|
|
|
3451 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3452 break;
|
|
|
3453 }
|
|
|
3454
|
|
|
3455 case QueryElementType.ExprExprPredicate :
|
|
|
3456 {
|
|
|
3457 var expr = (Predicate.ExprExpr)e;
|
|
|
3458 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3459 if (dic.TryGetValue(expr.Expr2, out ex)) expr.Expr2 = ex;
|
|
|
3460 break;
|
|
|
3461 }
|
|
|
3462
|
|
|
3463 case QueryElementType.LikePredicate :
|
|
|
3464 {
|
|
|
3465 var expr = (Predicate.Like)e;
|
|
|
3466 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3467 if (dic.TryGetValue(expr.Expr2, out ex)) expr.Expr2 = ex;
|
|
|
3468 if (dic.TryGetValue(expr.Escape, out ex)) expr.Escape = ex;
|
|
|
3469 break;
|
|
|
3470 }
|
|
|
3471
|
|
|
3472 case QueryElementType.BetweenPredicate :
|
|
|
3473 {
|
|
|
3474 var expr = (Predicate.Between)e;
|
|
|
3475 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3476 if (dic.TryGetValue(expr.Expr2, out ex)) expr.Expr2 = ex;
|
|
|
3477 if (dic.TryGetValue(expr.Expr3, out ex)) expr.Expr3 = ex;
|
|
|
3478 break;
|
|
|
3479 }
|
|
|
3480
|
|
|
3481 case QueryElementType.InListPredicate :
|
|
|
3482 {
|
|
|
3483 var expr = (Predicate.InList)e;
|
|
|
3484
|
|
|
3485 if (dic.TryGetValue(expr.Expr1, out ex)) expr.Expr1 = ex;
|
|
|
3486
|
|
|
3487 for (var i = 0; i < expr.Values.Count; i++)
|
|
|
3488 if (dic.TryGetValue(expr.Values[i], out ex))
|
|
|
3489 expr.Values[i] = ex;
|
|
|
3490
|
|
|
3491 break;
|
|
|
3492 }
|
|
|
3493
|
|
|
3494 case QueryElementType.Column :
|
|
|
3495 {
|
|
|
3496 var expr = (Column)e;
|
|
|
3497
|
|
|
3498 if (expr.Parent != data.Query)
|
|
|
3499 return false;
|
|
|
3500
|
|
|
3501 if (dic.TryGetValue(expr.Expression, out ex)) expr.Expression = ex;
|
|
|
3502
|
|
|
3503 break;
|
|
|
3504 }
|
|
|
3505
|
|
|
3506 case QueryElementType.SetExpression :
|
|
|
3507 {
|
|
|
3508 var expr = (SetExpression)e;
|
|
|
3509 if (dic.TryGetValue(expr.Expression, out ex)) expr.Expression = ex;
|
|
|
3510 break;
|
|
|
3511 }
|
|
|
3512
|
|
|
3513 case QueryElementType.GroupByClause :
|
|
|
3514 {
|
|
|
3515 var expr = (GroupByClause)e;
|
|
|
3516
|
|
|
3517 for (var i = 0; i < expr.Items.Count; i++)
|
|
|
3518 if (dic.TryGetValue(expr.Items[i], out ex))
|
|
|
3519 expr.Items[i] = ex;
|
|
|
3520
|
|
|
3521 break;
|
|
|
3522 }
|
|
|
3523
|
|
|
3524 case QueryElementType.OrderByItem :
|
|
|
3525 {
|
|
|
3526 var expr = (OrderByItem)e;
|
|
|
3527 if (dic.TryGetValue(expr.Expression, out ex)) expr.Expression = ex;
|
|
|
3528 break;
|
|
|
3529 }
|
|
|
3530 }
|
|
|
3531
|
|
|
3532 return true;
|
|
|
3533 });
|
|
|
3534
|
|
|
3535 foreach (var query in data.Queries)
|
|
|
3536 if (query.Queries.Count > 0)
|
|
|
3537 ResolveFields(query);
|
|
|
3538 }
|
|
|
3539
|
|
|
3540 void OptimizeUnions()
|
|
|
3541 {
|
|
|
3542 var exprs = new Dictionary<ISqlExpression,ISqlExpression>();
|
|
|
3543
|
|
|
3544 new QueryVisitor().Visit(this, e =>
|
|
|
3545 {
|
|
|
3546 var sql = e as SqlQuery;
|
|
|
3547
|
|
|
3548 if (sql == null || sql.From.Tables.Count != 1 || !sql.IsSimple || sql._insert != null || sql._update != null || sql._delete != null)
|
|
|
3549 return;
|
|
|
3550
|
|
|
3551 var table = sql.From.Tables[0];
|
|
|
3552
|
|
|
3553 if (table.Joins.Count != 0 || !(table.Source is SqlQuery))
|
|
|
3554 return;
|
|
|
3555
|
|
|
3556 var union = (SqlQuery)table.Source;
|
|
|
3557
|
|
|
3558 if (!union.HasUnion)
|
|
|
3559 return;
|
|
|
3560
|
|
|
3561 for (var i = 0; i < sql.Select.Columns.Count; i++)
|
|
|
3562 {
|
|
|
3563 var scol = sql. Select.Columns[i];
|
|
|
3564 var ucol = union.Select.Columns[i];
|
|
|
3565
|
|
|
3566 if (scol.Expression != ucol)
|
|
|
3567 return;
|
|
|
3568 }
|
|
|
3569
|
|
|
3570 exprs.Add(union, sql);
|
|
|
3571
|
|
|
3572 for (var i = 0; i < sql.Select.Columns.Count; i++)
|
|
|
3573 {
|
|
|
3574 var scol = sql. Select.Columns[i];
|
|
|
3575 var ucol = union.Select.Columns[i];
|
|
|
3576
|
|
|
3577 scol.Expression = ucol.Expression;
|
|
|
3578 scol._alias = ucol._alias;
|
|
|
3579
|
|
|
3580 exprs.Add(ucol, scol);
|
|
|
3581 }
|
|
|
3582
|
|
|
3583 for (var i = sql.Select.Columns.Count; i < union.Select.Columns.Count; i++)
|
|
|
3584 sql.Select.Expr(union.Select.Columns[i].Expression);
|
|
|
3585
|
|
|
3586 sql.From.Tables.Clear();
|
|
|
3587 sql.From.Tables.AddRange(union.From.Tables);
|
|
|
3588
|
|
|
3589 sql.Where. SearchCondition.Conditions.AddRange(union.Where. SearchCondition.Conditions);
|
|
|
3590 sql.Having. SearchCondition.Conditions.AddRange(union.Having.SearchCondition.Conditions);
|
|
|
3591 sql.GroupBy.Items. AddRange(union.GroupBy.Items);
|
|
|
3592 sql.OrderBy.Items. AddRange(union.OrderBy.Items);
|
|
|
3593 sql.Unions.InsertRange(0, union.Unions);
|
|
|
3594 });
|
|
|
3595
|
|
|
3596 ((ISqlExpressionWalkable)this).Walk(false, expr =>
|
|
|
3597 {
|
|
|
3598 ISqlExpression e;
|
|
|
3599
|
|
|
3600 if (exprs.TryGetValue(expr, out e))
|
|
|
3601 return e;
|
|
|
3602
|
|
|
3603 return expr;
|
|
|
3604 });
|
|
|
3605 }
|
|
|
3606
|
|
|
3607 void FinalizeAndValidateInternal(bool isApplySupported, bool optimizeColumns, bool optimizeSearchCondition, List<ISqlTableSource> tables)
|
|
|
3608 {
|
|
|
3609 OptimizeSearchCondition(Where. SearchCondition);
|
|
|
3610 OptimizeSearchCondition(Having.SearchCondition);
|
|
|
3611
|
|
|
3612 if (optimizeSearchCondition)
|
|
|
3613 {
|
|
|
3614 ForEachTable(table =>
|
|
|
3615 {
|
|
|
3616 foreach (var join in table.Joins)
|
|
|
3617 OptimizeSearchCondition(join.Condition);
|
|
|
3618 }, new HashSet<SqlQuery>());
|
|
|
3619 }
|
|
|
3620
|
|
|
3621 new QueryVisitor().Visit(this, e =>
|
|
|
3622 {
|
|
|
3623 var sql = e as SqlQuery;
|
|
|
3624
|
|
|
3625 if (sql != null && sql != this)
|
|
|
3626 {
|
|
|
3627 sql.ParentSql = this;
|
|
|
3628 sql.FinalizeAndValidateInternal(isApplySupported, optimizeColumns, false, tables);
|
|
|
3629
|
|
|
3630 if (sql.IsParameterDependent)
|
|
|
3631 IsParameterDependent = true;
|
|
|
3632 }
|
|
|
3633 });
|
|
|
3634
|
|
|
3635 ResolveWeakJoins(tables);
|
|
|
3636 OptimizeColumns();
|
|
|
3637 OptimizeApplies (isApplySupported, optimizeColumns);
|
|
|
3638 OptimizeSubQueries(isApplySupported, optimizeColumns);
|
|
|
3639 OptimizeApplies (isApplySupported, optimizeColumns);
|
|
|
3640
|
|
|
3641 new QueryVisitor().Visit(this, e =>
|
|
|
3642 {
|
|
|
3643 var sql = e as SqlQuery;
|
|
|
3644
|
|
|
3645 if (sql != null && sql != this)
|
|
|
3646 sql.RemoveOrderBy();
|
|
|
3647 });
|
|
|
3648 }
|
|
|
3649
|
|
|
3650 internal static void OptimizeSearchCondition(SearchCondition searchCondition)
|
|
|
3651 {
|
|
|
3652 // This 'if' could be replaced by one simple match:
|
|
|
3653 //
|
|
|
3654 // match (searchCondition.Conditions)
|
|
|
3655 // {
|
|
|
3656 // | [SearchCondition(true, _) sc] =>
|
|
|
3657 // searchCondition.Conditions = sc.Conditions;
|
|
|
3658 // OptimizeSearchCondition(searchCodition)
|
|
|
3659 //
|
|
|
3660 // | [SearchCondition(false, [SearchCondition(true, [ExprExpr]) sc])] => ...
|
|
|
3661 //
|
|
|
3662 // | [Expr(true, SqlValue(true))]
|
|
|
3663 // | [Expr(false, SqlValue(false))]
|
|
|
3664 // searchCondition.Conditions = []
|
|
|
3665 // }
|
|
|
3666 //
|
|
|
3667 // One day I am going to rewrite all this crap in Nemerle.
|
|
|
3668 //
|
|
|
3669 if (searchCondition.Conditions.Count == 1)
|
|
|
3670 {
|
|
|
3671 var cond = searchCondition.Conditions[0];
|
|
|
3672
|
|
|
3673 if (cond.Predicate is SearchCondition)
|
|
|
3674 {
|
|
|
3675 var sc = (SearchCondition)cond.Predicate;
|
|
|
3676
|
|
|
3677 if (!cond.IsNot)
|
|
|
3678 {
|
|
|
3679 searchCondition.Conditions.Clear();
|
|
|
3680 searchCondition.Conditions.AddRange(sc.Conditions);
|
|
|
3681
|
|
|
3682 OptimizeSearchCondition(searchCondition);
|
|
|
3683 return;
|
|
|
3684 }
|
|
|
3685
|
|
|
3686 if (sc.Conditions.Count == 1)
|
|
|
3687 {
|
|
|
3688 var c1 = sc.Conditions[0];
|
|
|
3689
|
|
|
3690 if (!c1.IsNot && c1.Predicate is Predicate.ExprExpr)
|
|
|
3691 {
|
|
|
3692 var ee = (Predicate.ExprExpr)c1.Predicate;
|
|
|
3693 Predicate.Operator op;
|
|
|
3694
|
|
|
3695 switch (ee.Operator)
|
|
|
3696 {
|
|
|
3697 case Predicate.Operator.Equal : op = Predicate.Operator.NotEqual; break;
|
|
|
3698 case Predicate.Operator.NotEqual : op = Predicate.Operator.Equal; break;
|
|
|
3699 case Predicate.Operator.Greater : op = Predicate.Operator.LessOrEqual; break;
|
|
|
3700 case Predicate.Operator.NotLess :
|
|
|
3701 case Predicate.Operator.GreaterOrEqual : op = Predicate.Operator.Less; break;
|
|
|
3702 case Predicate.Operator.Less : op = Predicate.Operator.GreaterOrEqual; break;
|
|
|
3703 case Predicate.Operator.NotGreater :
|
|
|
3704 case Predicate.Operator.LessOrEqual : op = Predicate.Operator.Greater; break;
|
|
|
3705 default: throw new InvalidOperationException();
|
|
|
3706 }
|
|
|
3707
|
|
|
3708 c1.Predicate = new Predicate.ExprExpr(ee.Expr1, op, ee.Expr2);
|
|
|
3709
|
|
|
3710 searchCondition.Conditions.Clear();
|
|
|
3711 searchCondition.Conditions.AddRange(sc.Conditions);
|
|
|
3712
|
|
|
3713 OptimizeSearchCondition(searchCondition);
|
|
|
3714 return;
|
|
|
3715 }
|
|
|
3716 }
|
|
|
3717 }
|
|
|
3718
|
|
|
3719 if (cond.Predicate.ElementType == QueryElementType.ExprPredicate)
|
|
|
3720 {
|
|
|
3721 var expr = (Predicate.Expr)cond.Predicate;
|
|
|
3722
|
|
|
3723 if (expr.Expr1 is SqlValue)
|
|
|
3724 {
|
|
|
3725 var value = (SqlValue)expr.Expr1;
|
|
|
3726
|
|
|
3727 if (value.Value is bool)
|
|
|
3728 if (cond.IsNot ? !(bool)value.Value : (bool)value.Value)
|
|
|
3729 searchCondition.Conditions.Clear();
|
|
|
3730 }
|
|
|
3731 }
|
|
|
3732 }
|
|
|
3733
|
|
|
3734 for (var i = 0; i < searchCondition.Conditions.Count; i++)
|
|
|
3735 {
|
|
|
3736 var cond = searchCondition.Conditions[i];
|
|
|
3737
|
|
|
3738 if (cond.Predicate is Predicate.Expr)
|
|
|
3739 {
|
|
|
3740 var expr = (Predicate.Expr)cond.Predicate;
|
|
|
3741
|
|
|
3742 if (expr.Expr1 is SqlValue)
|
|
|
3743 {
|
|
|
3744 var value = (SqlValue)expr.Expr1;
|
|
|
3745
|
|
|
3746 if (value.Value is bool)
|
|
|
3747 {
|
|
|
3748 if (cond.IsNot ? !(bool)value.Value : (bool)value.Value)
|
|
|
3749 {
|
|
|
3750 if (i > 0)
|
|
|
3751 {
|
|
|
3752 if (searchCondition.Conditions[i-1].IsOr)
|
|
|
3753 {
|
|
|
3754 searchCondition.Conditions.RemoveRange(0, i);
|
|
|
3755 OptimizeSearchCondition(searchCondition);
|
|
|
3756
|
|
|
3757 break;
|
|
|
3758 }
|
|
|
3759 }
|
|
|
3760 }
|
|
|
3761 }
|
|
|
3762 }
|
|
|
3763 }
|
|
|
3764 else if (cond.Predicate is SearchCondition)
|
|
|
3765 {
|
|
|
3766 var sc = (SearchCondition)cond.Predicate;
|
|
|
3767 OptimizeSearchCondition(sc);
|
|
|
3768 }
|
|
|
3769 }
|
|
|
3770 }
|
|
|
3771
|
|
|
3772 void ForEachTable(Action<TableSource> action, HashSet<SqlQuery> visitedQueries)
|
|
|
3773 {
|
|
|
3774 if (!visitedQueries.Add(this))
|
|
|
3775 return;
|
|
|
3776
|
|
|
3777 foreach (var table in From.Tables)
|
|
|
3778 table.ForEach(action, visitedQueries);
|
|
|
3779
|
|
|
3780 new QueryVisitor().Visit(this, e =>
|
|
|
3781 {
|
|
|
3782 if (e is SqlQuery && e != this)
|
|
|
3783 ((SqlQuery)e).ForEachTable(action, visitedQueries);
|
|
|
3784 });
|
|
|
3785 }
|
|
|
3786
|
|
|
3787 void RemoveOrderBy()
|
|
|
3788 {
|
|
|
3789 if (OrderBy.Items.Count > 0 && Select.SkipValue == null && Select.TakeValue == null)
|
|
|
3790 OrderBy.Items.Clear();
|
|
|
3791 }
|
|
|
3792
|
|
|
3793 internal void ResolveWeakJoins(List<ISqlTableSource> tables)
|
|
|
3794 {
|
|
|
3795 Func<TableSource,bool> findTable = null; findTable = table =>
|
|
|
3796 {
|
|
|
3797 if (tables.Contains(table.Source))
|
|
|
3798 return true;
|
|
|
3799
|
|
|
3800 foreach (var join in table.Joins)
|
|
|
3801 {
|
|
|
3802 if (findTable(join.Table))
|
|
|
3803 {
|
|
|
3804 join.IsWeak = false;
|
|
|
3805 return true;
|
|
|
3806 }
|
|
|
3807 }
|
|
|
3808
|
|
|
3809 if (table.Source is SqlQuery)
|
|
|
3810 foreach (var t in ((SqlQuery)table.Source).From.Tables)
|
|
|
3811 if (findTable(t))
|
|
|
3812 return true;
|
|
|
3813
|
|
|
3814 return false;
|
|
|
3815 };
|
|
|
3816
|
|
|
3817 var areTablesCollected = false;
|
|
|
3818
|
|
|
3819 ForEachTable(table =>
|
|
|
3820 {
|
|
|
3821 for (var i = 0; i < table.Joins.Count; i++)
|
|
|
3822 {
|
|
|
3823 var join = table.Joins[i];
|
|
|
3824
|
|
|
3825 if (join.IsWeak)
|
|
|
3826 {
|
|
|
3827 if (!areTablesCollected)
|
|
|
3828 {
|
|
|
3829 areTablesCollected = true;
|
|
|
3830
|
|
|
3831 Action<IQueryElement> tableCollector = expr =>
|
|
|
3832 {
|
|
|
3833 var field = expr as SqlField;
|
|
|
3834
|
|
|
3835 if (field != null && !tables.Contains(field.Table))
|
|
|
3836 tables.Add(field.Table);
|
|
|
3837 };
|
|
|
3838
|
|
|
3839 var visitor = new QueryVisitor();
|
|
|
3840
|
|
|
3841 visitor.VisitAll(Select, tableCollector);
|
|
|
3842 visitor.VisitAll(Where, tableCollector);
|
|
|
3843 visitor.VisitAll(GroupBy, tableCollector);
|
|
|
3844 visitor.VisitAll(Having, tableCollector);
|
|
|
3845 visitor.VisitAll(OrderBy, tableCollector);
|
|
|
3846
|
|
|
3847 if (_insert != null)
|
|
|
3848 visitor.VisitAll(Insert, tableCollector);
|
|
|
3849
|
|
|
3850 if (_update != null)
|
|
|
3851 visitor.VisitAll(Update, tableCollector);
|
|
|
3852
|
|
|
3853 if (_delete != null)
|
|
|
3854 visitor.VisitAll(Delete, tableCollector);
|
|
|
3855
|
|
|
3856 visitor.VisitAll(From, expr =>
|
|
|
3857 {
|
|
|
3858 var tbl = expr as SqlTable;
|
|
|
3859
|
|
|
3860 if (tbl != null && tbl.TableArguments != null)
|
|
|
3861 {
|
|
|
3862 var v = new QueryVisitor();
|
|
|
3863
|
|
|
3864 foreach (var arg in tbl.TableArguments)
|
|
|
3865 v.VisitAll(arg, tableCollector);
|
|
|
3866 }
|
|
|
3867 });
|
|
|
3868 }
|
|
|
3869
|
|
|
3870 if (findTable(join.Table))
|
|
|
3871 {
|
|
|
3872 join.IsWeak = false;
|
|
|
3873 }
|
|
|
3874 else
|
|
|
3875 {
|
|
|
3876 table.Joins.RemoveAt(i);
|
|
|
3877 i--;
|
|
|
3878 }
|
|
|
3879 }
|
|
|
3880 }
|
|
|
3881 }, new HashSet<SqlQuery>());
|
|
|
3882 }
|
|
|
3883
|
|
|
3884 TableSource OptimizeSubQuery(
|
|
|
3885 TableSource source,
|
|
|
3886 bool optimizeWhere,
|
|
|
3887 bool allColumns,
|
|
|
3888 bool isApplySupported,
|
|
|
3889 bool optimizeValues,
|
|
|
3890 bool optimizeColumns)
|
|
|
3891 {
|
|
|
3892 foreach (var jt in source.Joins)
|
|
|
3893 {
|
|
|
3894 var table = OptimizeSubQuery(
|
|
|
3895 jt.Table,
|
|
|
3896 jt.JoinType == JoinType.Inner || jt.JoinType == JoinType.CrossApply,
|
|
|
3897 false,
|
|
|
3898 isApplySupported,
|
|
|
3899 jt.JoinType == JoinType.Inner || jt.JoinType == JoinType.CrossApply,
|
|
|
3900 optimizeColumns);
|
|
|
3901
|
|
|
3902 if (table != jt.Table)
|
|
|
3903 {
|
|
|
3904 var sql = jt.Table.Source as SqlQuery;
|
|
|
3905
|
|
|
3906 if (sql != null && sql.OrderBy.Items.Count > 0)
|
|
|
3907 foreach (var item in sql.OrderBy.Items)
|
|
|
3908 OrderBy.Expr(item.Expression, item.IsDescending);
|
|
|
3909
|
|
|
3910 jt.Table = table;
|
|
|
3911 }
|
|
|
3912 }
|
|
|
3913
|
|
|
3914 return source.Source is SqlQuery ?
|
|
|
3915 RemoveSubQuery(source, optimizeWhere, allColumns && !isApplySupported, optimizeValues, optimizeColumns) :
|
|
|
3916 source;
|
|
|
3917 }
|
|
|
3918
|
|
|
3919 static bool CheckColumn(Column column, ISqlExpression expr, SqlQuery query, bool optimizeValues, bool optimizeColumns)
|
|
|
3920 {
|
|
|
3921 if (expr is SqlField || expr is Column)
|
|
|
3922 return false;
|
|
|
3923
|
|
|
3924 if (expr is SqlValue)
|
|
|
3925 return !optimizeValues && 1.Equals(((SqlValue)expr).Value);
|
|
|
3926
|
|
|
3927 if (expr is SqlBinaryExpression)
|
|
|
3928 {
|
|
|
3929 var e = (SqlBinaryExpression)expr;
|
|
|
3930
|
|
|
3931 if (e.Operation == "*" && e.Expr1 is SqlValue)
|
|
|
3932 {
|
|
|
3933 var value = (SqlValue)e.Expr1;
|
|
|
3934
|
|
|
3935 if (value.Value is int && (int)value.Value == -1)
|
|
|
3936 return CheckColumn(column, e.Expr2, query, optimizeValues, optimizeColumns);
|
|
|
3937 }
|
|
|
3938 }
|
|
|
3939
|
|
|
3940 var visitor = new QueryVisitor();
|
|
|
3941
|
|
|
3942 if (optimizeColumns &&
|
|
|
3943 visitor.Find(expr, e => e is SqlQuery || IsAggregationFunction(e)) == null)
|
|
|
3944 {
|
|
|
3945 var n = 0;
|
|
|
3946 var q = query.ParentSql ?? query;
|
|
|
3947
|
|
|
3948 visitor.VisitAll(q, e => { if (e == column) n++; });
|
|
|
3949
|
|
|
3950 return n > 2;
|
|
|
3951 }
|
|
|
3952
|
|
|
3953 return true;
|
|
|
3954 }
|
|
|
3955
|
|
|
3956 TableSource RemoveSubQuery(
|
|
|
3957 TableSource childSource,
|
|
|
3958 bool concatWhere,
|
|
|
3959 bool allColumns,
|
|
|
3960 bool optimizeValues,
|
|
|
3961 bool optimizeColumns)
|
|
|
3962 {
|
|
|
3963 var query = (SqlQuery)childSource.Source;
|
|
|
3964
|
|
|
3965 var isQueryOK = query.From.Tables.Count == 1;
|
|
|
3966
|
|
|
3967 isQueryOK = isQueryOK && (concatWhere || query.Where.IsEmpty && query.Having.IsEmpty);
|
|
|
3968 isQueryOK = isQueryOK && !query.HasUnion && query.GroupBy.IsEmpty && !query.Select.HasModifier;
|
|
|
3969
|
|
|
3970 if (!isQueryOK)
|
|
|
3971 return childSource;
|
|
|
3972
|
|
|
3973 var isColumnsOK =
|
|
|
3974 (allColumns && !query.Select.Columns.Exists(c => IsAggregationFunction(c.Expression))) ||
|
|
|
3975 !query.Select.Columns.Exists(c => CheckColumn(c, c.Expression, query, optimizeValues, optimizeColumns));
|
|
|
3976
|
|
|
3977 if (!isColumnsOK)
|
|
|
3978 return childSource;
|
|
|
3979
|
|
|
3980 var map = new Dictionary<ISqlExpression,ISqlExpression>(query.Select.Columns.Count);
|
|
|
3981
|
|
|
3982 foreach (var c in query.Select.Columns)
|
|
|
3983 map.Add(c, c.Expression);
|
|
|
3984
|
|
|
3985 var top = this;
|
|
|
3986
|
|
|
3987 while (top.ParentSql != null)
|
|
|
3988 top = top.ParentSql;
|
|
|
3989
|
|
|
3990 ((ISqlExpressionWalkable)top).Walk(false, expr =>
|
|
|
3991 {
|
|
|
3992 ISqlExpression fld;
|
|
|
3993 return map.TryGetValue(expr, out fld) ? fld : expr;
|
|
|
3994 });
|
|
|
3995
|
|
|
3996 new QueryVisitor().Visit(top, expr =>
|
|
|
3997 {
|
|
|
3998 if (expr.ElementType == QueryElementType.InListPredicate)
|
|
|
3999 {
|
|
|
4000 var p = (Predicate.InList)expr;
|
|
|
4001
|
|
|
4002 if (p.Expr1 == query)
|
|
|
4003 p.Expr1 = query.From.Tables[0];
|
|
|
4004 }
|
|
|
4005 });
|
|
|
4006
|
|
|
4007 query.From.Tables[0].Joins.AddRange(childSource.Joins);
|
|
|
4008
|
|
|
4009 if (query.From.Tables[0].Alias == null)
|
|
|
4010 query.From.Tables[0].Alias = childSource.Alias;
|
|
|
4011
|
|
|
4012 if (!query.Where. IsEmpty) ConcatSearchCondition(Where, query.Where);
|
|
|
4013 if (!query.Having.IsEmpty) ConcatSearchCondition(Having, query.Having);
|
|
|
4014
|
|
|
4015 ((ISqlExpressionWalkable)top).Walk(false, expr =>
|
|
|
4016 {
|
|
|
4017 if (expr is SqlQuery)
|
|
|
4018 {
|
|
|
4019 var sql = (SqlQuery)expr;
|
|
|
4020
|
|
|
4021 if (sql.ParentSql == query)
|
|
|
4022 sql.ParentSql = query.ParentSql ?? this;
|
|
|
4023 }
|
|
|
4024
|
|
|
4025 return expr;
|
|
|
4026 });
|
|
|
4027
|
|
|
4028 return query.From.Tables[0];
|
|
|
4029 }
|
|
|
4030
|
|
|
4031 static bool IsAggregationFunction(IQueryElement expr)
|
|
|
4032 {
|
|
|
4033 if (expr is SqlFunction)
|
|
|
4034 switch (((SqlFunction)expr).Name)
|
|
|
4035 {
|
|
|
4036 case "Count" :
|
|
|
4037 case "Average" :
|
|
|
4038 case "Min" :
|
|
|
4039 case "Max" :
|
|
|
4040 case "Sum" : return true;
|
|
|
4041 }
|
|
|
4042
|
|
|
4043 return false;
|
|
|
4044 }
|
|
|
4045
|
|
|
4046 void OptimizeApply(TableSource tableSource, JoinedTable joinTable, bool isApplySupported, bool optimizeColumns)
|
|
|
4047 {
|
|
|
4048 var joinSource = joinTable.Table;
|
|
|
4049
|
|
|
4050 foreach (var join in joinSource.Joins)
|
|
|
4051 if (join.JoinType == JoinType.CrossApply || join.JoinType == JoinType.OuterApply)
|
|
|
4052 OptimizeApply(joinSource, join, isApplySupported, optimizeColumns);
|
|
|
4053
|
|
|
4054 if (isApplySupported && !joinTable.CanConvertApply)
|
|
|
4055 return;
|
|
|
4056
|
|
|
4057 if (joinSource.Source.ElementType == QueryElementType.SqlQuery)
|
|
|
4058 {
|
|
|
4059 var sql = (SqlQuery)joinSource.Source;
|
|
|
4060 var isAgg = sql.Select.Columns.Exists(c => IsAggregationFunction(c.Expression));
|
|
|
4061
|
|
|
4062 if (isApplySupported && (isAgg || sql.Select.TakeValue != null || sql.Select.SkipValue != null))
|
|
|
4063 return;
|
|
|
4064
|
|
|
4065 var searchCondition = new List<Condition>(sql.Where.SearchCondition.Conditions);
|
|
|
4066
|
|
|
4067 sql.Where.SearchCondition.Conditions.Clear();
|
|
|
4068
|
|
|
4069 if (!ContainsTable(tableSource.Source, sql))
|
|
|
4070 {
|
|
|
4071 joinTable.JoinType = joinTable.JoinType == JoinType.CrossApply ? JoinType.Inner : JoinType.Left;
|
|
|
4072 joinTable.Condition.Conditions.AddRange(searchCondition);
|
|
|
4073 }
|
|
|
4074 else
|
|
|
4075 {
|
|
|
4076 sql.Where.SearchCondition.Conditions.AddRange(searchCondition);
|
|
|
4077
|
|
|
4078 var table = OptimizeSubQuery(
|
|
|
4079 joinTable.Table,
|
|
|
4080 joinTable.JoinType == JoinType.Inner || joinTable.JoinType == JoinType.CrossApply,
|
|
|
4081 joinTable.JoinType == JoinType.CrossApply,
|
|
|
4082 isApplySupported,
|
|
|
4083 joinTable.JoinType == JoinType.Inner || joinTable.JoinType == JoinType.CrossApply,
|
|
|
4084 optimizeColumns);
|
|
|
4085
|
|
|
4086 if (table != joinTable.Table)
|
|
|
4087 {
|
|
|
4088 var q = joinTable.Table.Source as SqlQuery;
|
|
|
4089
|
|
|
4090 if (q != null && q.OrderBy.Items.Count > 0)
|
|
|
4091 foreach (var item in q.OrderBy.Items)
|
|
|
4092 OrderBy.Expr(item.Expression, item.IsDescending);
|
|
|
4093
|
|
|
4094 joinTable.Table = table;
|
|
|
4095
|
|
|
4096 OptimizeApply(tableSource, joinTable, isApplySupported, optimizeColumns);
|
|
|
4097 }
|
|
|
4098 }
|
|
|
4099 }
|
|
|
4100 else
|
|
|
4101 {
|
|
|
4102 if (!ContainsTable(tableSource.Source, joinSource.Source))
|
|
|
4103 joinTable.JoinType = joinTable.JoinType == JoinType.CrossApply ? JoinType.Inner : JoinType.Left;
|
|
|
4104 }
|
|
|
4105 }
|
|
|
4106
|
|
|
4107 static bool ContainsTable(ISqlTableSource table, IQueryElement sql)
|
|
|
4108 {
|
|
|
4109 return null != new QueryVisitor().Find(sql, e =>
|
|
|
4110 e == table ||
|
|
|
4111 e.ElementType == QueryElementType.SqlField && table == ((SqlField)e).Table ||
|
|
|
4112 e.ElementType == QueryElementType.Column && table == ((Column) e).Parent);
|
|
|
4113 }
|
|
|
4114
|
|
|
4115 static void ConcatSearchCondition(WhereClause where1, WhereClause where2)
|
|
|
4116 {
|
|
|
4117 if (where1.IsEmpty)
|
|
|
4118 {
|
|
|
4119 where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions);
|
|
|
4120 }
|
|
|
4121 else
|
|
|
4122 {
|
|
|
4123 if (where1.SearchCondition.Precedence < Sql.Precedence.LogicalConjunction)
|
|
|
4124 {
|
|
|
4125 var sc1 = new SearchCondition();
|
|
|
4126
|
|
|
4127 sc1.Conditions.AddRange(where1.SearchCondition.Conditions);
|
|
|
4128
|
|
|
4129 where1.SearchCondition.Conditions.Clear();
|
|
|
4130 where1.SearchCondition.Conditions.Add(new Condition(false, sc1));
|
|
|
4131 }
|
|
|
4132
|
|
|
4133 if (where2.SearchCondition.Precedence < Sql.Precedence.LogicalConjunction)
|
|
|
4134 {
|
|
|
4135 var sc2 = new SearchCondition();
|
|
|
4136
|
|
|
4137 sc2.Conditions.AddRange(where2.SearchCondition.Conditions);
|
|
|
4138
|
|
|
4139 where1.SearchCondition.Conditions.Add(new Condition(false, sc2));
|
|
|
4140 }
|
|
|
4141 else
|
|
|
4142 where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions);
|
|
|
4143 }
|
|
|
4144 }
|
|
|
4145
|
|
|
4146 void OptimizeSubQueries(bool isApplySupported, bool optimizeColumns)
|
|
|
4147 {
|
|
|
4148 for (var i = 0; i < From.Tables.Count; i++)
|
|
|
4149 {
|
|
|
4150 var table = OptimizeSubQuery(From.Tables[i], true, false, isApplySupported, true, optimizeColumns);
|
|
|
4151
|
|
|
4152 if (table != From.Tables[i])
|
|
|
4153 {
|
|
|
4154 var sql = From.Tables[i].Source as SqlQuery;
|
|
|
4155
|
|
|
4156 if (!Select.Columns.All(c => IsAggregationFunction(c.Expression)))
|
|
|
4157 if (sql != null && sql.OrderBy.Items.Count > 0)
|
|
|
4158 foreach (var item in sql.OrderBy.Items)
|
|
|
4159 OrderBy.Expr(item.Expression, item.IsDescending);
|
|
|
4160
|
|
|
4161 From.Tables[i] = table;
|
|
|
4162 }
|
|
|
4163 }
|
|
|
4164 }
|
|
|
4165
|
|
|
4166 void OptimizeApplies(bool isApplySupported, bool optimizeColumns)
|
|
|
4167 {
|
|
|
4168 foreach (var table in From.Tables)
|
|
|
4169 foreach (var join in table.Joins)
|
|
|
4170 if (join.JoinType == JoinType.CrossApply || join.JoinType == JoinType.OuterApply)
|
|
|
4171 OptimizeApply(table, join, isApplySupported, optimizeColumns);
|
|
|
4172 }
|
|
|
4173
|
|
|
4174 void OptimizeColumns()
|
|
|
4175 {
|
|
|
4176 ((ISqlExpressionWalkable)Select).Walk(false, expr =>
|
|
|
4177 {
|
|
|
4178 var query = expr as SqlQuery;
|
|
|
4179
|
|
|
4180 if (query != null && query.From.Tables.Count == 0 && query.Select.Columns.Count == 1)
|
|
|
4181 {
|
|
|
4182 new QueryVisitor().Visit(query.Select.Columns[0].Expression, e =>
|
|
|
4183 {
|
|
|
4184 if (e.ElementType == QueryElementType.SqlQuery)
|
|
|
4185 {
|
|
|
4186 var q = (SqlQuery)e;
|
|
|
4187
|
|
|
4188 if (q.ParentSql == query)
|
|
|
4189 q.ParentSql = query.ParentSql;
|
|
|
4190 }
|
|
|
4191 });
|
|
|
4192
|
|
|
4193 return query.Select.Columns[0].Expression;
|
|
|
4194 }
|
|
|
4195
|
|
|
4196 return expr;
|
|
|
4197 });
|
|
|
4198 }
|
|
|
4199
|
|
|
4200 IDictionary<string,object> _aliases;
|
|
|
4201
|
|
|
4202 public void RemoveAlias(string alias)
|
|
|
4203 {
|
|
|
4204 if (_aliases != null)
|
|
|
4205 {
|
|
|
4206 alias = alias.ToUpper();
|
|
|
4207 if (_aliases.ContainsKey(alias))
|
|
|
4208 _aliases.Remove(alias);
|
|
|
4209 }
|
|
|
4210 }
|
|
|
4211
|
|
|
4212 public string GetAlias(string desiredAlias, string defaultAlias)
|
|
|
4213 {
|
|
|
4214 if (_aliases == null)
|
|
|
4215 _aliases = new Dictionary<string,object>();
|
|
|
4216
|
|
|
4217 var alias = desiredAlias;
|
|
|
4218
|
|
|
4219 if (string.IsNullOrEmpty(desiredAlias) || desiredAlias.Length > 30)
|
|
|
4220 {
|
|
|
4221 desiredAlias = defaultAlias;
|
|
|
4222 alias = defaultAlias + "1";
|
|
|
4223 }
|
|
|
4224
|
|
|
4225 for (var i = 1; ; i++)
|
|
|
4226 {
|
|
|
4227 var s = alias.ToUpper();
|
|
|
4228
|
|
|
4229 if (!_aliases.ContainsKey(s) && !_reservedWords.ContainsKey(s))
|
|
|
4230 {
|
|
|
4231 _aliases.Add(s, s);
|
|
|
4232 break;
|
|
|
4233 }
|
|
|
4234
|
|
|
4235 alias = desiredAlias + i;
|
|
|
4236 }
|
|
|
4237
|
|
|
4238 return alias;
|
|
|
4239 }
|
|
|
4240
|
|
|
4241 public string[] GetTempAliases(int n, string defaultAlias)
|
|
|
4242 {
|
|
|
4243 var aliases = new string[n];
|
|
|
4244
|
|
|
4245 for (var i = 0; i < aliases.Length; i++)
|
|
|
4246 aliases[i] = GetAlias(defaultAlias, defaultAlias);
|
|
|
4247
|
|
|
4248 foreach (var t in aliases)
|
|
|
4249 RemoveAlias(t);
|
|
|
4250
|
|
|
4251 return aliases;
|
|
|
4252 }
|
|
|
4253
|
|
|
4254 void SetAliases()
|
|
|
4255 {
|
|
|
4256 _aliases = null;
|
|
|
4257
|
|
|
4258 var objs = new Dictionary<object,object>();
|
|
|
4259
|
|
|
4260 Parameters.Clear();
|
|
|
4261
|
|
|
4262 new QueryVisitor().VisitAll(this, expr =>
|
|
|
4263 {
|
|
|
4264 switch (expr.ElementType)
|
|
|
4265 {
|
|
|
4266 case QueryElementType.SqlParameter:
|
|
|
4267 {
|
|
|
4268 var p = (SqlParameter)expr;
|
|
|
4269
|
|
|
4270 if (p.IsQueryParameter)
|
|
|
4271 {
|
|
|
4272 if (!objs.ContainsKey(expr))
|
|
|
4273 {
|
|
|
4274 objs.Add(expr, expr);
|
|
|
4275 p.Name = GetAlias(p.Name, "p");
|
|
|
4276 }
|
|
|
4277
|
|
|
4278 Parameters.Add(p);
|
|
|
4279 }
|
|
|
4280 else
|
|
|
4281 IsParameterDependent = true;
|
|
|
4282 }
|
|
|
4283
|
|
|
4284 break;
|
|
|
4285
|
|
|
4286 case QueryElementType.Column:
|
|
|
4287 {
|
|
|
4288 if (!objs.ContainsKey(expr))
|
|
|
4289 {
|
|
|
4290 objs.Add(expr, expr);
|
|
|
4291
|
|
|
4292 var c = (Column)expr;
|
|
|
4293
|
|
|
4294 if (c.Alias != "*")
|
|
|
4295 c.Alias = GetAlias(c.Alias, "c");
|
|
|
4296 }
|
|
|
4297 }
|
|
|
4298
|
|
|
4299 break;
|
|
|
4300
|
|
|
4301 case QueryElementType.TableSource:
|
|
|
4302 {
|
|
|
4303 var table = (TableSource)expr;
|
|
|
4304
|
|
|
4305 if (!objs.ContainsKey(table))
|
|
|
4306 {
|
|
|
4307 objs.Add(table, table);
|
|
|
4308 table.Alias = GetAlias(table.Alias, "t");
|
|
|
4309 }
|
|
|
4310 }
|
|
|
4311
|
|
|
4312 break;
|
|
|
4313
|
|
|
4314 case QueryElementType.SqlQuery:
|
|
|
4315 {
|
|
|
4316 var sql = (SqlQuery)expr;
|
|
|
4317
|
|
|
4318 if (sql.HasUnion)
|
|
|
4319 {
|
|
|
4320 for (var i = 0; i < sql.Select.Columns.Count; i++)
|
|
|
4321 {
|
|
|
4322 var col = sql.Select.Columns[i];
|
|
|
4323
|
|
|
4324 foreach (var t in sql.Unions)
|
|
|
4325 {
|
|
|
4326 var union = t.SqlQuery.Select;
|
|
|
4327
|
|
|
4328 objs.Remove(union.Columns[i].Alias);
|
|
|
4329
|
|
|
4330 union.Columns[i].Alias = col.Alias;
|
|
|
4331 }
|
|
|
4332 }
|
|
|
4333 }
|
|
|
4334 }
|
|
|
4335
|
|
|
4336 break;
|
|
|
4337 }
|
|
|
4338 });
|
|
|
4339 }
|
|
|
4340
|
|
|
4341 #endregion
|
|
|
4342
|
|
|
4343 #region ProcessParameters
|
|
|
4344
|
|
|
4345 public SqlQuery ProcessParameters()
|
|
|
4346 {
|
|
|
4347 if (IsParameterDependent)
|
|
|
4348 {
|
|
|
4349 var query = new QueryVisitor().Convert(this, e =>
|
|
|
4350 {
|
|
|
4351 switch (e.ElementType)
|
|
|
4352 {
|
|
|
4353 case QueryElementType.SqlParameter :
|
|
|
4354 {
|
|
|
4355 var p = (SqlParameter)e;
|
|
|
4356
|
|
|
4357 if (p.Value == null)
|
|
|
4358 return new SqlValue(null);
|
|
|
4359 }
|
|
|
4360
|
|
|
4361 break;
|
|
|
4362
|
|
|
4363 case QueryElementType.ExprExprPredicate :
|
|
|
4364 {
|
|
|
4365 var ee = (Predicate.ExprExpr)e;
|
|
|
4366
|
|
|
4367 if (ee.Operator == Predicate.Operator.Equal || ee.Operator == Predicate.Operator.NotEqual)
|
|
|
4368 {
|
|
|
4369 object value1;
|
|
|
4370 object value2;
|
|
|
4371
|
|
|
4372 if (ee.Expr1 is SqlValue)
|
|
|
4373 value1 = ((SqlValue)ee.Expr1).Value;
|
|
|
4374 else if (ee.Expr1 is SqlParameter)
|
|
|
4375 value1 = ((SqlParameter)ee.Expr1).Value;
|
|
|
4376 else
|
|
|
4377 break;
|
|
|
4378
|
|
|
4379 if (ee.Expr2 is SqlValue)
|
|
|
4380 value2 = ((SqlValue)ee.Expr2).Value;
|
|
|
4381 else if (ee.Expr2 is SqlParameter)
|
|
|
4382 value2 = ((SqlParameter)ee.Expr2).Value;
|
|
|
4383 else
|
|
|
4384 break;
|
|
|
4385
|
|
|
4386 var value = Equals(value1, value2);
|
|
|
4387
|
|
|
4388 if (ee.Operator == Predicate.Operator.NotEqual)
|
|
|
4389 value = !value;
|
|
|
4390
|
|
|
4391 return new Predicate.Expr(new SqlValue(value), Sql.Precedence.Comparison);
|
|
|
4392 }
|
|
|
4393 }
|
|
|
4394
|
|
|
4395 break;
|
|
|
4396
|
|
|
4397 case QueryElementType.InListPredicate :
|
|
|
4398 return ConvertInListPredicate((Predicate.InList)e);
|
|
|
4399 }
|
|
|
4400
|
|
|
4401 return null;
|
|
|
4402 });
|
|
|
4403
|
|
|
4404 if (query != this)
|
|
|
4405 {
|
|
|
4406 query.Parameters.Clear();
|
|
|
4407
|
|
|
4408 new QueryVisitor().VisitAll(query, expr =>
|
|
|
4409 {
|
|
|
4410 if (expr.ElementType == QueryElementType.SqlParameter)
|
|
|
4411 {
|
|
|
4412 var p = (SqlParameter)expr;
|
|
|
4413 if (p.IsQueryParameter)
|
|
|
4414 query.Parameters.Add(p);
|
|
|
4415 }
|
|
|
4416 });
|
|
|
4417 }
|
|
|
4418
|
|
|
4419 return query;
|
|
|
4420 }
|
|
|
4421
|
|
|
4422 return this;
|
|
|
4423 }
|
|
|
4424
|
|
|
4425 static Predicate ConvertInListPredicate(Predicate.InList p)
|
|
|
4426 {
|
|
|
4427 if (p.Values == null || p.Values.Count == 0)
|
|
|
4428 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4429
|
|
|
4430 if (p.Values.Count == 1 && p.Values[0] is SqlParameter)
|
|
|
4431 {
|
|
|
4432 var pr = (SqlParameter)p.Values[0];
|
|
|
4433
|
|
|
4434 if (pr.Value == null)
|
|
|
4435 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4436
|
|
|
4437 if (pr.Value is IEnumerable)
|
|
|
4438 {
|
|
|
4439 if (p.Expr1 is ISqlTableSource)
|
|
|
4440 {
|
|
|
4441 var items = (IEnumerable)pr.Value;
|
|
|
4442 var table = (ISqlTableSource)p.Expr1;
|
|
|
4443 var keys = table.GetKeys(true);
|
|
|
4444
|
|
|
4445 if (keys == null || keys.Count == 0)
|
|
|
4446 throw new SqlException("Cant create IN expression.");
|
|
|
4447
|
|
|
4448 if (keys.Count == 1)
|
|
|
4449 {
|
|
|
4450 var values = new List<ISqlExpression>();
|
|
|
4451 var field = GetUnderlayingField(keys[0]);
|
|
|
4452
|
|
|
4453 foreach (var item in items)
|
|
|
4454 {
|
|
|
4455 var value = field.MemberMapper.GetValue(item);
|
|
|
4456 values.Add(new SqlValue(value));
|
|
|
4457 }
|
|
|
4458
|
|
|
4459 if (values.Count == 0)
|
|
|
4460 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4461
|
|
|
4462 return new Predicate.InList(keys[0], p.IsNot, values);
|
|
|
4463 }
|
|
|
4464
|
|
|
4465 {
|
|
|
4466 var sc = new SearchCondition();
|
|
|
4467
|
|
|
4468 foreach (var item in items)
|
|
|
4469 {
|
|
|
4470 var itemCond = new SearchCondition();
|
|
|
4471
|
|
|
4472 foreach (var key in keys)
|
|
|
4473 {
|
|
|
4474 var field = GetUnderlayingField(key);
|
|
|
4475 var value = field.MemberMapper.GetValue(item);
|
|
|
4476 var cond = value == null ?
|
|
|
4477 new Condition(false, new Predicate.IsNull (field, false)) :
|
|
|
4478 new Condition(false, new Predicate.ExprExpr(field, Predicate.Operator.Equal, new SqlValue(value)));
|
|
|
4479
|
|
|
4480 itemCond.Conditions.Add(cond);
|
|
|
4481 }
|
|
|
4482
|
|
|
4483 sc.Conditions.Add(new Condition(false, new Predicate.Expr(itemCond), true));
|
|
|
4484 }
|
|
|
4485
|
|
|
4486 if (sc.Conditions.Count == 0)
|
|
|
4487 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4488
|
|
|
4489 if (p.IsNot)
|
|
|
4490 return new Predicate.NotExpr(sc, true, Sql.Precedence.LogicalNegation);
|
|
|
4491
|
|
|
4492 return new Predicate.Expr(sc, Sql.Precedence.LogicalDisjunction);
|
|
|
4493 }
|
|
|
4494 }
|
|
|
4495
|
|
|
4496 if (p.Expr1 is SqlExpression)
|
|
|
4497 {
|
|
|
4498 var expr = (SqlExpression)p.Expr1;
|
|
|
4499
|
|
|
4500 if (expr.Expr.Length > 1 && expr.Expr[0] == '\x1')
|
|
|
4501 {
|
|
|
4502 var type = TypeHelper.GetListItemType(pr.Value);
|
|
|
4503 var ta = TypeAccessor.GetAccessor(type);
|
|
|
4504 var items = (IEnumerable)pr.Value;
|
|
|
4505 var names = expr.Expr.Substring(1).Split(',');
|
|
|
4506
|
|
|
4507 if (expr.Parameters.Length == 1)
|
|
|
4508 {
|
|
|
4509 var values = new List<ISqlExpression>();
|
|
|
4510
|
|
|
4511 foreach (var item in items)
|
|
|
4512 {
|
|
|
4513 var value = ta[names[0]].GetValue(item);
|
|
|
4514 values.Add(new SqlValue(value));
|
|
|
4515 }
|
|
|
4516
|
|
|
4517 if (values.Count == 0)
|
|
|
4518 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4519
|
|
|
4520 return new Predicate.InList(expr.Parameters[0], p.IsNot, values);
|
|
|
4521 }
|
|
|
4522
|
|
|
4523 {
|
|
|
4524 var sc = new SearchCondition();
|
|
|
4525
|
|
|
4526 foreach (var item in items)
|
|
|
4527 {
|
|
|
4528 var itemCond = new SearchCondition();
|
|
|
4529
|
|
|
4530 for (var i = 0; i < expr.Parameters.Length; i++)
|
|
|
4531 {
|
|
|
4532 var sql = expr.Parameters[i];
|
|
|
4533 var value = ta[names[i]].GetValue(item);
|
|
|
4534 var cond = value == null ?
|
|
|
4535 new Condition(false, new Predicate.IsNull (sql, false)) :
|
|
|
4536 new Condition(false, new Predicate.ExprExpr(sql, Predicate.Operator.Equal, new SqlValue(value)));
|
|
|
4537
|
|
|
4538 itemCond.Conditions.Add(cond);
|
|
|
4539 }
|
|
|
4540
|
|
|
4541 sc.Conditions.Add(new Condition(false, new Predicate.Expr(itemCond), true));
|
|
|
4542 }
|
|
|
4543
|
|
|
4544 if (sc.Conditions.Count == 0)
|
|
|
4545 return new Predicate.Expr(new SqlValue(p.IsNot));
|
|
|
4546
|
|
|
4547 if (p.IsNot)
|
|
|
4548 return new Predicate.NotExpr(sc, true, Sql.Precedence.LogicalNegation);
|
|
|
4549
|
|
|
4550 return new Predicate.Expr(sc, Sql.Precedence.LogicalDisjunction);
|
|
|
4551 }
|
|
|
4552 }
|
|
|
4553 }
|
|
|
4554
|
|
|
4555 /*
|
|
|
4556 var itemType = items.GetType().GetItemType();
|
|
|
4557
|
|
|
4558 if (itemType == typeof(DateTime) || itemType == typeof(DateTimeOffset) ||
|
|
|
4559 itemType == typeof(DateTime?) || itemType == typeof(DateTimeOffset?))
|
|
|
4560 {
|
|
|
4561 var list = new List<SqlParameter>();
|
|
|
4562
|
|
|
4563 foreach (var item in items)
|
|
|
4564 list.Add(new SqlParameter(itemType, "p", item, (MappingSchema)null));
|
|
|
4565
|
|
|
4566 return new Predicate.InList(p.Expr1, p.IsNot, list);
|
|
|
4567 }
|
|
|
4568 */
|
|
|
4569 }
|
|
|
4570 }
|
|
|
4571
|
|
|
4572 return null;
|
|
|
4573 }
|
|
|
4574
|
|
|
4575 static SqlField GetUnderlayingField(ISqlExpression expr)
|
|
|
4576 {
|
|
|
4577 switch (expr.ElementType)
|
|
|
4578 {
|
|
|
4579 case QueryElementType.SqlField: return (SqlField)expr;
|
|
|
4580 case QueryElementType.Column : return GetUnderlayingField(((Column)expr).Expression);
|
|
|
4581 }
|
|
|
4582
|
|
|
4583 throw new InvalidOperationException();
|
|
|
4584 }
|
|
|
4585
|
|
|
4586 #endregion
|
|
|
4587
|
|
|
4588 #region Clone
|
|
|
4589
|
|
|
4590 SqlQuery(SqlQuery clone, Dictionary<ICloneableElement,ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
4591 {
|
|
|
4592 objectTree.Add(clone, this);
|
|
|
4593 objectTree.Add(clone.All, All);
|
|
|
4594
|
|
|
4595 SourceID = Interlocked.Increment(ref SourceIDCounter);
|
|
|
4596
|
|
|
4597 _queryType = clone._queryType;
|
|
|
4598
|
|
|
4599 if (IsInsert) _insert = (InsertClause)clone._insert.Clone(objectTree, doClone);
|
|
|
4600 if (IsUpdate) _update = (UpdateClause)clone._update.Clone(objectTree, doClone);
|
|
|
4601 if (IsDelete) _delete = (DeleteClause)clone._delete.Clone(objectTree, doClone);
|
|
|
4602
|
|
|
4603 _select = new SelectClause (this, clone._select, objectTree, doClone);
|
|
|
4604 _from = new FromClause (this, clone._from, objectTree, doClone);
|
|
|
4605 _where = new WhereClause (this, clone._where, objectTree, doClone);
|
|
|
4606 _groupBy = new GroupByClause(this, clone._groupBy, objectTree, doClone);
|
|
|
4607 _having = new WhereClause (this, clone._having, objectTree, doClone);
|
|
|
4608 _orderBy = new OrderByClause(this, clone._orderBy, objectTree, doClone);
|
|
|
4609
|
|
|
4610 _parameters.AddRange(clone._parameters.ConvertAll(p => (SqlParameter)p.Clone(objectTree, doClone)));
|
|
|
4611 IsParameterDependent = clone.IsParameterDependent;
|
|
|
4612
|
|
|
4613 new QueryVisitor().Visit(this, expr =>
|
|
|
4614 {
|
|
|
4615 var sb = expr as SqlQuery;
|
|
|
4616
|
|
|
4617 if (sb != null && sb.ParentSql == clone)
|
|
|
4618 sb.ParentSql = this;
|
|
|
4619 });
|
|
|
4620 }
|
|
|
4621
|
|
|
4622 public SqlQuery Clone()
|
|
|
4623 {
|
|
|
4624 return (SqlQuery)Clone(new Dictionary<ICloneableElement,ICloneableElement>(), _ => true);
|
|
|
4625 }
|
|
|
4626
|
|
|
4627 public SqlQuery Clone(Predicate<ICloneableElement> doClone)
|
|
|
4628 {
|
|
|
4629 return (SqlQuery)Clone(new Dictionary<ICloneableElement,ICloneableElement>(), doClone);
|
|
|
4630 }
|
|
|
4631
|
|
|
4632 #endregion
|
|
|
4633
|
|
|
4634 #region Helpers
|
|
|
4635
|
|
|
4636 public TableSource GetTableSource(ISqlTableSource table)
|
|
|
4637 {
|
|
|
4638 var ts = From[table];
|
|
|
4639 return ts == null && ParentSql != null? ParentSql.GetTableSource(table) : ts;
|
|
|
4640 }
|
|
|
4641
|
|
|
4642 static TableSource CheckTableSource(TableSource ts, ISqlTableSource table, string alias)
|
|
|
4643 {
|
|
|
4644 if (ts.Source == table && (alias == null || ts.Alias == alias))
|
|
|
4645 return ts;
|
|
|
4646
|
|
|
4647 var jt = ts[table, alias];
|
|
|
4648
|
|
|
4649 if (jt != null)
|
|
|
4650 return jt;
|
|
|
4651
|
|
|
4652 if (ts.Source is SqlQuery)
|
|
|
4653 {
|
|
|
4654 var s = ((SqlQuery)ts.Source).From[table, alias];
|
|
|
4655
|
|
|
4656 if (s != null)
|
|
|
4657 return s;
|
|
|
4658 }
|
|
|
4659
|
|
|
4660 return null;
|
|
|
4661 }
|
|
|
4662
|
|
|
4663 #endregion
|
|
|
4664
|
|
|
4665 #region Overrides
|
|
|
4666
|
|
|
4667 public string SqlText { get { return ToString(); } }
|
|
|
4668
|
|
|
4669 #if OVERRIDETOSTRING
|
|
|
4670
|
|
|
4671 public override string ToString()
|
|
|
4672 {
|
|
|
4673 return ((IQueryElement)this).ToString(new StringBuilder(), new Dictionary<IQueryElement,IQueryElement>()).ToString();
|
|
|
4674 }
|
|
|
4675
|
|
|
4676 #endif
|
|
|
4677
|
|
|
4678 #endregion
|
|
|
4679
|
|
|
4680 #region ISqlExpression Members
|
|
|
4681
|
|
|
4682 public bool CanBeNull()
|
|
|
4683 {
|
|
|
4684 return true;
|
|
|
4685 }
|
|
|
4686
|
|
|
4687 public bool Equals(ISqlExpression other, Func<ISqlExpression, ISqlExpression, bool> comparer)
|
|
|
4688 {
|
|
|
4689 return this == other;
|
|
|
4690 }
|
|
|
4691
|
|
|
4692 public int Precedence
|
|
|
4693 {
|
|
|
4694 get { return Sql.Precedence.Unknown; }
|
|
|
4695 }
|
|
|
4696
|
|
|
4697 public Type SystemType
|
|
|
4698 {
|
|
|
4699 get
|
|
|
4700 {
|
|
|
4701 if (Select.Columns.Count == 1)
|
|
|
4702 return Select.Columns[0].SystemType;
|
|
|
4703
|
|
|
4704 if (From.Tables.Count == 1 && From.Tables[0].Joins.Count == 0)
|
|
|
4705 return From.Tables[0].SystemType;
|
|
|
4706
|
|
|
4707 return null;
|
|
|
4708 }
|
|
|
4709 }
|
|
|
4710
|
|
|
4711 #endregion
|
|
|
4712
|
|
|
4713 #region ICloneableElement Members
|
|
|
4714
|
|
|
4715 public ICloneableElement Clone(Dictionary<ICloneableElement, ICloneableElement> objectTree, Predicate<ICloneableElement> doClone)
|
|
|
4716 {
|
|
|
4717 if (!doClone(this))
|
|
|
4718 return this;
|
|
|
4719
|
|
|
4720 ICloneableElement clone;
|
|
|
4721
|
|
|
4722 if (!objectTree.TryGetValue(this, out clone))
|
|
|
4723 clone = new SqlQuery(this, objectTree, doClone);
|
|
|
4724
|
|
|
4725 return clone;
|
|
|
4726 }
|
|
|
4727
|
|
|
4728 #endregion
|
|
|
4729
|
|
|
4730 #region ISqlExpressionWalkable Members
|
|
|
4731
|
|
|
4732 [Obsolete]
|
|
|
4733 ISqlExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func<ISqlExpression,ISqlExpression> func)
|
|
|
4734 {
|
|
|
4735 if (_insert != null) ((ISqlExpressionWalkable)_insert).Walk(skipColumns, func);
|
|
|
4736 if (_update != null) ((ISqlExpressionWalkable)_update).Walk(skipColumns, func);
|
|
|
4737 if (_delete != null) ((ISqlExpressionWalkable)_delete).Walk(skipColumns, func);
|
|
|
4738
|
|
|
4739 ((ISqlExpressionWalkable)Select) .Walk(skipColumns, func);
|
|
|
4740 ((ISqlExpressionWalkable)From) .Walk(skipColumns, func);
|
|
|
4741 ((ISqlExpressionWalkable)Where) .Walk(skipColumns, func);
|
|
|
4742 ((ISqlExpressionWalkable)GroupBy).Walk(skipColumns, func);
|
|
|
4743 ((ISqlExpressionWalkable)Having) .Walk(skipColumns, func);
|
|
|
4744 ((ISqlExpressionWalkable)OrderBy).Walk(skipColumns, func);
|
|
|
4745
|
|
|
4746 if (HasUnion)
|
|
|
4747 foreach (var union in Unions)
|
|
|
4748 ((ISqlExpressionWalkable)union.SqlQuery).Walk(skipColumns, func);
|
|
|
4749
|
|
|
4750 return func(this);
|
|
|
4751 }
|
|
|
4752
|
|
|
4753 #endregion
|
|
|
4754
|
|
|
4755 #region IEquatable<ISqlExpression> Members
|
|
|
4756
|
|
|
4757 bool IEquatable<ISqlExpression>.Equals(ISqlExpression other)
|
|
|
4758 {
|
|
|
4759 return this == other;
|
|
|
4760 }
|
|
|
4761
|
|
|
4762 #endregion
|
|
|
4763
|
|
|
4764 #region ISqlTableSource Members
|
|
|
4765
|
|
|
4766 public static int SourceIDCounter;
|
|
|
4767
|
|
|
4768 public int SourceID { get; private set; }
|
|
|
4769 public SqlTableType SqlTableType { get { return SqlTableType.Table; } }
|
|
|
4770
|
|
|
4771 private SqlField _all;
|
|
|
4772 public SqlField All
|
|
|
4773 {
|
|
|
4774 get
|
|
|
4775 {
|
|
|
4776 if (_all == null)
|
|
|
4777 {
|
|
|
4778 _all = new SqlField(null, "*", "*", true, -1, null, null);
|
|
|
4779 ((IChild<ISqlTableSource>)_all).Parent = this;
|
|
|
4780 }
|
|
|
4781
|
|
|
4782 return _all;
|
|
|
4783 }
|
|
|
4784
|
|
|
4785 internal set
|
|
|
4786 {
|
|
|
4787 _all = value;
|
|
|
4788
|
|
|
4789 if (_all != null)
|
|
|
4790 ((IChild<ISqlTableSource>)_all).Parent = this;
|
|
|
4791 }
|
|
|
4792 }
|
|
|
4793
|
|
|
4794 List<ISqlExpression> _keys;
|
|
|
4795
|
|
|
4796 public IList<ISqlExpression> GetKeys(bool allIfEmpty)
|
|
|
4797 {
|
|
|
4798 if (_keys == null && From.Tables.Count == 1 && From.Tables[0].Joins.Count == 0)
|
|
|
4799 {
|
|
|
4800 _keys = new List<ISqlExpression>();
|
|
|
4801
|
|
|
4802 var q =
|
|
|
4803 from key in ((ISqlTableSource)From.Tables[0]).GetKeys(allIfEmpty)
|
|
|
4804 from col in Select.Columns
|
|
|
4805 where col.Expression == key
|
|
|
4806 select col as ISqlExpression;
|
|
|
4807
|
|
|
4808 _keys = q.ToList();
|
|
|
4809 }
|
|
|
4810
|
|
|
4811 return _keys;
|
|
|
4812 }
|
|
|
4813
|
|
|
4814 #endregion
|
|
|
4815
|
|
|
4816 #region IQueryElement Members
|
|
|
4817
|
|
|
4818 public QueryElementType ElementType { get { return QueryElementType.SqlQuery; } }
|
|
|
4819
|
|
|
4820 StringBuilder IQueryElement.ToString(StringBuilder sb, Dictionary<IQueryElement,IQueryElement> dic)
|
|
|
4821 {
|
|
|
4822 if (dic.ContainsKey(this))
|
|
|
4823 return sb.Append("...");
|
|
|
4824
|
|
|
4825 dic.Add(this, this);
|
|
|
4826
|
|
|
4827 sb
|
|
|
4828 .Append("(")
|
|
|
4829 .Append(SourceID)
|
|
|
4830 .Append(") ");
|
|
|
4831
|
|
|
4832 ((IQueryElement)Select). ToString(sb, dic);
|
|
|
4833 ((IQueryElement)From). ToString(sb, dic);
|
|
|
4834 ((IQueryElement)Where). ToString(sb, dic);
|
|
|
4835 ((IQueryElement)GroupBy).ToString(sb, dic);
|
|
|
4836 ((IQueryElement)Having). ToString(sb, dic);
|
|
|
4837 ((IQueryElement)OrderBy).ToString(sb, dic);
|
|
|
4838
|
|
|
4839 if (HasUnion)
|
|
|
4840 foreach (IQueryElement u in Unions)
|
|
|
4841 u.ToString(sb, dic);
|
|
|
4842
|
|
|
4843 dic.Remove(this);
|
|
|
4844
|
|
|
4845 return sb;
|
|
|
4846 }
|
|
|
4847
|
|
|
4848 #endregion
|
|
|
4849 }
|
|
|
4850 }
|
