|
0
|
1 using System;
|
|
|
2 using System.Collections;
|
|
|
3 using System.Collections.Generic;
|
|
|
4 using System.Data;
|
|
|
5 using System.Globalization;
|
|
|
6 using System.Linq;
|
|
|
7 using System.Linq.Expressions;
|
|
|
8 using System.Reflection;
|
|
|
9 using System.Text;
|
|
|
10
|
|
|
11 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
12 {
|
|
|
13 using DataProvider;
|
|
|
14 using Mapping;
|
|
|
15 using Linq;
|
|
|
16 using Reflection;
|
|
|
17
|
|
|
18 public abstract class BasicSqlProvider : ISqlProvider
|
|
|
19 {
|
|
|
20 #region Init
|
|
|
21
|
|
|
22 public SqlQuery SqlQuery { get; set; }
|
|
|
23 public int Indent { get; set; }
|
|
|
24
|
|
|
25 private int _nextNesting = 1;
|
|
|
26 private int _nesting;
|
|
|
27 public int Nesting
|
|
|
28 {
|
|
|
29 get { return _nesting; }
|
|
|
30 }
|
|
|
31
|
|
|
32 bool _skipAlias;
|
|
|
33
|
|
|
34 public Step BuildStep { get; set; }
|
|
|
35
|
|
|
36 #endregion
|
|
|
37
|
|
|
38 #region Support Flags
|
|
|
39
|
|
|
40 public virtual bool SkipAcceptsParameter { get { return true; } }
|
|
|
41 public virtual bool TakeAcceptsParameter { get { return true; } }
|
|
|
42 public virtual bool IsTakeSupported { get { return true; } }
|
|
|
43 public virtual bool IsSkipSupported { get { return true; } }
|
|
|
44 public virtual bool IsSubQueryTakeSupported { get { return true; } }
|
|
|
45 public virtual bool IsSubQueryColumnSupported { get { return true; } }
|
|
|
46 public virtual bool IsCountSubQuerySupported { get { return true; } }
|
|
|
47 public virtual bool IsNestedJoinSupported { get { return true; } }
|
|
|
48 public virtual bool IsNestedJoinParenthesisRequired { get { return false; } }
|
|
|
49 public virtual bool IsIdentityParameterRequired { get { return false; } }
|
|
|
50 public virtual bool IsApplyJoinSupported { get { return false; } }
|
|
|
51 public virtual bool IsInsertOrUpdateSupported { get { return true; } }
|
|
|
52 public virtual bool CanCombineParameters { get { return true; } }
|
|
|
53 public virtual bool IsGroupByExpressionSupported { get { return true; } }
|
|
|
54 public virtual int MaxInListValuesCount { get { return int.MaxValue; } }
|
|
|
55
|
|
|
56 public virtual bool ConvertCountSubQuery(SqlQuery subQuery)
|
|
|
57 {
|
|
|
58 return true;
|
|
|
59 }
|
|
|
60
|
|
|
61 #endregion
|
|
|
62
|
|
|
63 #region CommandCount
|
|
|
64
|
|
|
65 public virtual int CommandCount(SqlQuery sqlQuery)
|
|
|
66 {
|
|
|
67 return 1;
|
|
|
68 }
|
|
|
69
|
|
|
70 #endregion
|
|
|
71
|
|
|
72 #region BuildSql
|
|
|
73
|
|
|
74 public virtual int BuildSql(int commandNumber, SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias)
|
|
|
75 {
|
|
|
76 SqlQuery = sqlQuery;
|
|
|
77 Indent = indent;
|
|
|
78 _nesting = nesting;
|
|
|
79 _nextNesting = _nesting + 1;
|
|
|
80 _skipAlias = skipAlias;
|
|
|
81
|
|
|
82 if (commandNumber == 0)
|
|
|
83 {
|
|
|
84 BuildSql(sb);
|
|
|
85
|
|
|
86 if (sqlQuery.HasUnion)
|
|
|
87 {
|
|
|
88 foreach (var union in sqlQuery.Unions)
|
|
|
89 {
|
|
|
90 AppendIndent(sb);
|
|
|
91 sb.Append("UNION");
|
|
|
92 if (union.IsAll) sb.Append(" ALL");
|
|
|
93 sb.AppendLine();
|
|
|
94
|
|
|
95 CreateSqlProvider().BuildSql(commandNumber, union.SqlQuery, sb, indent, nesting, skipAlias);
|
|
|
96 }
|
|
|
97 }
|
|
|
98 }
|
|
|
99 else
|
|
|
100 {
|
|
|
101 BuildCommand(commandNumber, sb);
|
|
|
102 }
|
|
|
103
|
|
|
104 return _nextNesting;
|
|
|
105 }
|
|
|
106
|
|
|
107 protected virtual void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
108 {
|
|
|
109 }
|
|
|
110
|
|
|
111 #endregion
|
|
|
112
|
|
|
113 #region Overrides
|
|
|
114
|
|
|
115 protected virtual int BuildSqlBuilder(SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias)
|
|
|
116 {
|
|
|
117 if (!IsSkipSupported && sqlQuery.Select.SkipValue != null)
|
|
|
118 throw new SqlException("Skip for subqueries is not supported by the '{0}' provider.", Name);
|
|
|
119
|
|
|
120 if (!IsTakeSupported && sqlQuery.Select.TakeValue != null)
|
|
|
121 throw new SqlException("Take for subqueries is not supported by the '{0}' provider.", Name);
|
|
|
122
|
|
|
123 return CreateSqlProvider().BuildSql(0, sqlQuery, sb, indent, nesting, skipAlias);
|
|
|
124 }
|
|
|
125
|
|
|
126 protected abstract ISqlProvider CreateSqlProvider();
|
|
|
127
|
|
|
128 protected virtual bool ParenthesizeJoin()
|
|
|
129 {
|
|
|
130 return false;
|
|
|
131 }
|
|
|
132
|
|
|
133 protected virtual void BuildSql(StringBuilder sb)
|
|
|
134 {
|
|
|
135 switch (SqlQuery.QueryType)
|
|
|
136 {
|
|
|
137 case QueryType.Select : BuildSelectQuery (sb); break;
|
|
|
138 case QueryType.Delete : BuildDeleteQuery (sb); break;
|
|
|
139 case QueryType.Update : BuildUpdateQuery (sb); break;
|
|
|
140 case QueryType.Insert : BuildInsertQuery (sb); break;
|
|
|
141 case QueryType.InsertOrUpdate : BuildInsertOrUpdateQuery(sb); break;
|
|
|
142 default : BuildUnknownQuery (sb); break;
|
|
|
143 }
|
|
|
144 }
|
|
|
145
|
|
|
146 protected virtual void BuildDeleteQuery(StringBuilder sb)
|
|
|
147 {
|
|
|
148 BuildStep = Step.DeleteClause; BuildDeleteClause (sb);
|
|
|
149 BuildStep = Step.FromClause; BuildFromClause (sb);
|
|
|
150 BuildStep = Step.WhereClause; BuildWhereClause (sb);
|
|
|
151 BuildStep = Step.GroupByClause; BuildGroupByClause(sb);
|
|
|
152 BuildStep = Step.HavingClause; BuildHavingClause (sb);
|
|
|
153 BuildStep = Step.OrderByClause; BuildOrderByClause(sb);
|
|
|
154 BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb);
|
|
|
155 }
|
|
|
156
|
|
|
157 protected virtual void BuildUpdateQuery(StringBuilder sb)
|
|
|
158 {
|
|
|
159 BuildStep = Step.UpdateClause; BuildUpdateClause (sb);
|
|
|
160 BuildStep = Step.FromClause; BuildFromClause (sb);
|
|
|
161 BuildStep = Step.WhereClause; BuildWhereClause (sb);
|
|
|
162 BuildStep = Step.GroupByClause; BuildGroupByClause(sb);
|
|
|
163 BuildStep = Step.HavingClause; BuildHavingClause (sb);
|
|
|
164 BuildStep = Step.OrderByClause; BuildOrderByClause(sb);
|
|
|
165 BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb);
|
|
|
166 }
|
|
|
167
|
|
|
168 protected virtual void BuildSelectQuery(StringBuilder sb)
|
|
|
169 {
|
|
|
170 BuildStep = Step.SelectClause; BuildSelectClause (sb);
|
|
|
171 BuildStep = Step.FromClause; BuildFromClause (sb);
|
|
|
172 BuildStep = Step.WhereClause; BuildWhereClause (sb);
|
|
|
173 BuildStep = Step.GroupByClause; BuildGroupByClause(sb);
|
|
|
174 BuildStep = Step.HavingClause; BuildHavingClause (sb);
|
|
|
175 BuildStep = Step.OrderByClause; BuildOrderByClause(sb);
|
|
|
176 BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb);
|
|
|
177 }
|
|
|
178
|
|
|
179 protected virtual void BuildInsertQuery(StringBuilder sb)
|
|
|
180 {
|
|
|
181 BuildStep = Step.InsertClause; BuildInsertClause(sb);
|
|
|
182
|
|
|
183 if (SqlQuery.QueryType == QueryType.Insert && SqlQuery.From.Tables.Count != 0)
|
|
|
184 {
|
|
|
185 BuildStep = Step.SelectClause; BuildSelectClause (sb);
|
|
|
186 BuildStep = Step.FromClause; BuildFromClause (sb);
|
|
|
187 BuildStep = Step.WhereClause; BuildWhereClause (sb);
|
|
|
188 BuildStep = Step.GroupByClause; BuildGroupByClause(sb);
|
|
|
189 BuildStep = Step.HavingClause; BuildHavingClause (sb);
|
|
|
190 BuildStep = Step.OrderByClause; BuildOrderByClause(sb);
|
|
|
191 BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb);
|
|
|
192 }
|
|
|
193
|
|
|
194 if (SqlQuery.Insert.WithIdentity)
|
|
|
195 BuildGetIdentity(sb);
|
|
|
196 }
|
|
|
197
|
|
|
198 protected virtual void BuildUnknownQuery(StringBuilder sb)
|
|
|
199 {
|
|
|
200 throw new SqlException("Unknown query type '{0}'.", SqlQuery.QueryType);
|
|
|
201 }
|
|
|
202
|
|
|
203 public virtual StringBuilder BuildTableName(StringBuilder sb, string database, string owner, string table)
|
|
|
204 {
|
|
|
205 if (database != null)
|
|
|
206 {
|
|
|
207 if (owner == null) sb.Append(database).Append("..");
|
|
|
208 else sb.Append(database).Append(".").Append(owner).Append(".");
|
|
|
209 }
|
|
|
210 else if (owner != null) sb.Append(owner).Append(".");
|
|
|
211
|
|
|
212 return sb.Append(table);
|
|
|
213 }
|
|
|
214
|
|
|
215 public virtual object Convert(object value, ConvertType convertType)
|
|
|
216 {
|
|
|
217 return value;
|
|
|
218 }
|
|
|
219
|
|
|
220 #endregion
|
|
|
221
|
|
|
222 #region Build Select
|
|
|
223
|
|
|
224 protected virtual void BuildSelectClause(StringBuilder sb)
|
|
|
225 {
|
|
|
226 AppendIndent(sb);
|
|
|
227 sb.Append("SELECT");
|
|
|
228
|
|
|
229 if (SqlQuery.Select.IsDistinct)
|
|
|
230 sb.Append(" DISTINCT");
|
|
|
231
|
|
|
232 BuildSkipFirst(sb);
|
|
|
233
|
|
|
234 sb.AppendLine();
|
|
|
235 BuildColumns(sb);
|
|
|
236 }
|
|
|
237
|
|
|
238 protected virtual IEnumerable<SqlQuery.Column> GetSelectedColumns()
|
|
|
239 {
|
|
|
240 return SqlQuery.Select.Columns;
|
|
|
241 }
|
|
|
242
|
|
|
243 protected virtual void BuildColumns(StringBuilder sb)
|
|
|
244 {
|
|
|
245 Indent++;
|
|
|
246
|
|
|
247 var first = true;
|
|
|
248
|
|
|
249 foreach (var col in GetSelectedColumns())
|
|
|
250 {
|
|
|
251 if (!first)
|
|
|
252 sb.Append(',').AppendLine();
|
|
|
253 first = false;
|
|
|
254
|
|
|
255 var addAlias = true;
|
|
|
256
|
|
|
257 AppendIndent(sb);
|
|
|
258 BuildColumnExpression(sb, col.Expression, col.Alias, ref addAlias);
|
|
|
259
|
|
|
260 if (!_skipAlias && addAlias && col.Alias != null)
|
|
|
261 sb.Append(" as ").Append(Convert(col.Alias, ConvertType.NameToQueryFieldAlias));
|
|
|
262 }
|
|
|
263
|
|
|
264 if (first)
|
|
|
265 AppendIndent(sb).Append("*");
|
|
|
266
|
|
|
267 Indent--;
|
|
|
268
|
|
|
269 sb.AppendLine();
|
|
|
270 }
|
|
|
271
|
|
|
272 // protected virtual void BuildColumn(StringBuilder sb, SqlQuery.Column col, ref bool addAlias)
|
|
|
273 // {
|
|
|
274 // BuildExpression(sb, col.Expression, true, true, col.Alias, ref addAlias);
|
|
|
275 // }
|
|
|
276
|
|
|
277 protected virtual void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
278 {
|
|
|
279 BuildExpression(sb, expr, true, true, alias, ref addAlias);
|
|
|
280 }
|
|
|
281
|
|
|
282 #endregion
|
|
|
283
|
|
|
284 #region Build Delete
|
|
|
285
|
|
|
286 protected virtual void BuildDeleteClause(StringBuilder sb)
|
|
|
287 {
|
|
|
288 AppendIndent(sb);
|
|
|
289 sb.Append("DELETE ");
|
|
|
290 }
|
|
|
291
|
|
|
292 #endregion
|
|
|
293
|
|
|
294 #region Build Update
|
|
|
295
|
|
|
296 protected virtual void BuildUpdateClause(StringBuilder sb)
|
|
|
297 {
|
|
|
298 BuildUpdateTable(sb);
|
|
|
299 BuildUpdateSet (sb);
|
|
|
300 }
|
|
|
301
|
|
|
302 protected virtual void BuildUpdateTable(StringBuilder sb)
|
|
|
303 {
|
|
|
304 AppendIndent(sb)
|
|
|
305 .AppendLine("UPDATE")
|
|
|
306 .Append('\t');
|
|
|
307 BuildUpdateTableName(sb);
|
|
|
308 sb.AppendLine();
|
|
|
309 }
|
|
|
310
|
|
|
311 protected virtual void BuildUpdateTableName(StringBuilder sb)
|
|
|
312 {
|
|
|
313 if (SqlQuery.Update.Table != null && SqlQuery.Update.Table != SqlQuery.From.Tables[0].Source)
|
|
|
314 BuildPhysicalTable(sb, SqlQuery.Update.Table, null);
|
|
|
315 else
|
|
|
316 BuildTableName(sb, SqlQuery.From.Tables[0], true, true);
|
|
|
317 }
|
|
|
318
|
|
|
319 protected virtual void BuildUpdateSet(StringBuilder sb)
|
|
|
320 {
|
|
|
321 AppendIndent(sb)
|
|
|
322 .AppendLine("SET");
|
|
|
323
|
|
|
324 Indent++;
|
|
|
325
|
|
|
326 var first = true;
|
|
|
327
|
|
|
328 foreach (var expr in SqlQuery.Update.Items)
|
|
|
329 {
|
|
|
330 if (!first)
|
|
|
331 sb.Append(',').AppendLine();
|
|
|
332 first = false;
|
|
|
333
|
|
|
334 AppendIndent(sb);
|
|
|
335 BuildExpression(sb, expr.Column, false, true);
|
|
|
336 sb.Append(" = ");
|
|
|
337
|
|
|
338 var addAlias = false;
|
|
|
339
|
|
|
340 BuildColumnExpression(sb, expr.Expression, null, ref addAlias);
|
|
|
341 }
|
|
|
342
|
|
|
343 Indent--;
|
|
|
344
|
|
|
345 sb.AppendLine();
|
|
|
346 }
|
|
|
347
|
|
|
348 #endregion
|
|
|
349
|
|
|
350 #region Build Insert
|
|
|
351
|
|
|
352 protected void BuildInsertClause(StringBuilder sb)
|
|
|
353 {
|
|
|
354 BuildInsertClause(sb, "INSERT INTO ", true);
|
|
|
355 }
|
|
|
356
|
|
|
357 protected virtual void BuildEmptyInsert(StringBuilder sb)
|
|
|
358 {
|
|
|
359 sb.AppendLine("DEFAULT VALUES");
|
|
|
360 }
|
|
|
361
|
|
|
362 protected virtual void BuildInsertClause(StringBuilder sb, string insertText, bool appendTableName)
|
|
|
363 {
|
|
|
364 AppendIndent(sb).Append(insertText);
|
|
|
365
|
|
|
366 if (appendTableName)
|
|
|
367 BuildPhysicalTable(sb, SqlQuery.Insert.Into, null);
|
|
|
368
|
|
|
369 if (SqlQuery.Insert.Items.Count == 0)
|
|
|
370 {
|
|
|
371 sb.Append(' ');
|
|
|
372 BuildEmptyInsert(sb);
|
|
|
373 }
|
|
|
374 else
|
|
|
375 {
|
|
|
376 sb.AppendLine();
|
|
|
377
|
|
|
378 AppendIndent(sb).AppendLine("(");
|
|
|
379
|
|
|
380 Indent++;
|
|
|
381
|
|
|
382 var first = true;
|
|
|
383
|
|
|
384 foreach (var expr in SqlQuery.Insert.Items)
|
|
|
385 {
|
|
|
386 if (!first)
|
|
|
387 sb.Append(',').AppendLine();
|
|
|
388 first = false;
|
|
|
389
|
|
|
390 AppendIndent(sb);
|
|
|
391 BuildExpression(sb, expr.Column, false, true);
|
|
|
392 }
|
|
|
393
|
|
|
394 Indent--;
|
|
|
395
|
|
|
396 sb.AppendLine();
|
|
|
397 AppendIndent(sb).AppendLine(")");
|
|
|
398
|
|
|
399 if (SqlQuery.QueryType == QueryType.InsertOrUpdate || SqlQuery.From.Tables.Count == 0)
|
|
|
400 {
|
|
|
401 AppendIndent(sb).AppendLine("VALUES");
|
|
|
402 AppendIndent(sb).AppendLine("(");
|
|
|
403
|
|
|
404 Indent++;
|
|
|
405
|
|
|
406 first = true;
|
|
|
407
|
|
|
408 foreach (var expr in SqlQuery.Insert.Items)
|
|
|
409 {
|
|
|
410 if (!first)
|
|
|
411 sb.Append(',').AppendLine();
|
|
|
412 first = false;
|
|
|
413
|
|
|
414 AppendIndent(sb);
|
|
|
415 BuildExpression(sb, expr.Expression);
|
|
|
416 }
|
|
|
417
|
|
|
418 Indent--;
|
|
|
419
|
|
|
420 sb.AppendLine();
|
|
|
421 AppendIndent(sb).AppendLine(")");
|
|
|
422 }
|
|
|
423 }
|
|
|
424 }
|
|
|
425
|
|
|
426 protected virtual void BuildGetIdentity(StringBuilder sb)
|
|
|
427 {
|
|
|
428 //throw new SqlException("Insert with identity is not supported by the '{0}' sql provider.", Name);
|
|
|
429 }
|
|
|
430
|
|
|
431 #endregion
|
|
|
432
|
|
|
433 #region Build InsertOrUpdate
|
|
|
434
|
|
|
435 protected virtual void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
436 {
|
|
|
437 throw new SqlException("InsertOrUpdate query type is not supported by {0} provider.", Name);
|
|
|
438 }
|
|
|
439
|
|
|
440 protected void BuildInsertOrUpdateQueryAsMerge(StringBuilder sb, string fromDummyTable)
|
|
|
441 {
|
|
|
442 var table = SqlQuery.Insert.Into;
|
|
|
443 var targetAlias = Convert(SqlQuery.From.Tables[0].Alias, ConvertType.NameToQueryTableAlias).ToString();
|
|
|
444 var sourceAlias = Convert(GetTempAliases(1, "s")[0], ConvertType.NameToQueryTableAlias).ToString();
|
|
|
445 var keys = SqlQuery.Update.Keys;
|
|
|
446
|
|
|
447 AppendIndent(sb).Append("MERGE INTO ");
|
|
|
448 BuildPhysicalTable(sb, table, null);
|
|
|
449 sb.Append(' ').AppendLine(targetAlias);
|
|
|
450
|
|
|
451 AppendIndent(sb).Append("USING (SELECT ");
|
|
|
452
|
|
|
453 for (var i = 0; i < keys.Count; i++)
|
|
|
454 {
|
|
|
455 BuildExpression(sb, keys[i].Expression, false, false);
|
|
|
456 sb.Append(" AS ");
|
|
|
457 BuildExpression(sb, keys[i].Column, false, false);
|
|
|
458
|
|
|
459 if (i + 1 < keys.Count)
|
|
|
460 sb.Append(", ");
|
|
|
461 }
|
|
|
462
|
|
|
463 if (!string.IsNullOrEmpty(fromDummyTable))
|
|
|
464 sb.Append(' ').Append(fromDummyTable);
|
|
|
465
|
|
|
466 sb.Append(") ").Append(sourceAlias).AppendLine(" ON");
|
|
|
467
|
|
|
468 AppendIndent(sb).AppendLine("(");
|
|
|
469
|
|
|
470 Indent++;
|
|
|
471
|
|
|
472 for (var i = 0; i < keys.Count; i++)
|
|
|
473 {
|
|
|
474 var key = keys[i];
|
|
|
475
|
|
|
476 AppendIndent(sb);
|
|
|
477
|
|
|
478 sb.Append(targetAlias).Append('.');
|
|
|
479 BuildExpression(sb, key.Column, false, false);
|
|
|
480
|
|
|
481 sb.Append(" = ").Append(sourceAlias).Append('.');
|
|
|
482 BuildExpression(sb, key.Column, false, false);
|
|
|
483
|
|
|
484 if (i + 1 < keys.Count)
|
|
|
485 sb.Append(" AND");
|
|
|
486
|
|
|
487 sb.AppendLine();
|
|
|
488 }
|
|
|
489
|
|
|
490 Indent--;
|
|
|
491
|
|
|
492 AppendIndent(sb).AppendLine(")");
|
|
|
493 AppendIndent(sb).AppendLine("WHEN MATCHED THEN");
|
|
|
494
|
|
|
495 Indent++;
|
|
|
496 AppendIndent(sb).AppendLine("UPDATE ");
|
|
|
497 BuildUpdateSet(sb);
|
|
|
498 Indent--;
|
|
|
499
|
|
|
500 AppendIndent(sb).AppendLine("WHEN NOT MATCHED THEN");
|
|
|
501
|
|
|
502 Indent++;
|
|
|
503 BuildInsertClause(sb, "INSERT", false);
|
|
|
504 Indent--;
|
|
|
505
|
|
|
506 while (_endLine.Contains(sb[sb.Length - 1]))
|
|
|
507 sb.Length--;
|
|
|
508 }
|
|
|
509
|
|
|
510 static readonly char[] _endLine = new[] { ' ', '\r', '\n' };
|
|
|
511
|
|
|
512 protected void BuildInsertOrUpdateQueryAsUpdateInsert(StringBuilder sb)
|
|
|
513 {
|
|
|
514 AppendIndent(sb).AppendLine("BEGIN TRAN").AppendLine();
|
|
|
515
|
|
|
516 BuildUpdateQuery(sb);
|
|
|
517
|
|
|
518 AppendIndent(sb).AppendLine("WHERE");
|
|
|
519
|
|
|
520 var alias = Convert(SqlQuery.From.Tables[0].Alias, ConvertType.NameToQueryTableAlias).ToString();
|
|
|
521 var exprs = SqlQuery.Update.Keys;
|
|
|
522
|
|
|
523 Indent++;
|
|
|
524
|
|
|
525 for (var i = 0; i < exprs.Count; i++)
|
|
|
526 {
|
|
|
527 var expr = exprs[i];
|
|
|
528
|
|
|
529 AppendIndent(sb);
|
|
|
530
|
|
|
531 sb.Append(alias).Append('.');
|
|
|
532 BuildExpression(sb, expr.Column, false, false);
|
|
|
533
|
|
|
534 sb.Append(" = ");
|
|
|
535 BuildExpression(sb, Precedence.Comparison, expr.Expression);
|
|
|
536
|
|
|
537 if (i + 1 < exprs.Count)
|
|
|
538 sb.Append(" AND");
|
|
|
539
|
|
|
540 sb.AppendLine();
|
|
|
541 }
|
|
|
542
|
|
|
543 Indent--;
|
|
|
544
|
|
|
545 sb.AppendLine();
|
|
|
546 AppendIndent(sb).AppendLine("IF @@ROWCOUNT = 0");
|
|
|
547 AppendIndent(sb).AppendLine("BEGIN");
|
|
|
548
|
|
|
549 Indent++;
|
|
|
550
|
|
|
551 BuildInsertQuery(sb);
|
|
|
552
|
|
|
553 Indent--;
|
|
|
554
|
|
|
555 AppendIndent(sb).AppendLine("END");
|
|
|
556
|
|
|
557 sb.AppendLine();
|
|
|
558 AppendIndent(sb).AppendLine("COMMIT");
|
|
|
559 }
|
|
|
560
|
|
|
561 #endregion
|
|
|
562
|
|
|
563 #region Build From
|
|
|
564
|
|
|
565 protected virtual void BuildFromClause(StringBuilder sb)
|
|
|
566 {
|
|
|
567 if (SqlQuery.From.Tables.Count == 0)
|
|
|
568 return;
|
|
|
569
|
|
|
570 AppendIndent(sb);
|
|
|
571
|
|
|
572 sb.Append("FROM").AppendLine();
|
|
|
573
|
|
|
574 Indent++;
|
|
|
575 AppendIndent(sb);
|
|
|
576
|
|
|
577 var first = true;
|
|
|
578
|
|
|
579 foreach (var ts in SqlQuery.From.Tables)
|
|
|
580 {
|
|
|
581 if (!first)
|
|
|
582 {
|
|
|
583 sb.AppendLine(",");
|
|
|
584 AppendIndent(sb);
|
|
|
585 }
|
|
|
586
|
|
|
587 first = false;
|
|
|
588
|
|
|
589 var jn = ParenthesizeJoin() ? ts.GetJoinNumber() : 0;
|
|
|
590
|
|
|
591 if (jn > 0)
|
|
|
592 {
|
|
|
593 jn--;
|
|
|
594 for (var i = 0; i < jn; i++)
|
|
|
595 sb.Append("(");
|
|
|
596 }
|
|
|
597
|
|
|
598 BuildTableName(sb, ts, true, true);
|
|
|
599
|
|
|
600 foreach (var jt in ts.Joins)
|
|
|
601 BuildJoinTable(sb, jt, ref jn);
|
|
|
602 }
|
|
|
603
|
|
|
604 Indent--;
|
|
|
605
|
|
|
606 sb.AppendLine();
|
|
|
607 }
|
|
|
608
|
|
|
609 protected void BuildPhysicalTable(StringBuilder sb, ISqlTableSource table, string alias)
|
|
|
610 {
|
|
|
611 switch (table.ElementType)
|
|
|
612 {
|
|
|
613 case QueryElementType.SqlTable :
|
|
|
614 case QueryElementType.TableSource :
|
|
|
615 sb.Append(GetTablePhysicalName(table, alias));
|
|
|
616 break;
|
|
|
617
|
|
|
618 case QueryElementType.SqlQuery :
|
|
|
619 sb.Append("(").AppendLine();
|
|
|
620 _nextNesting = BuildSqlBuilder((SqlQuery)table, sb, Indent + 1, _nextNesting, false);
|
|
|
621 AppendIndent(sb).Append(")");
|
|
|
622
|
|
|
623 break;
|
|
|
624
|
|
|
625 default:
|
|
|
626 throw new InvalidOperationException();
|
|
|
627 }
|
|
|
628 }
|
|
|
629
|
|
|
630 protected void BuildTableName(StringBuilder sb, SqlQuery.TableSource ts, bool buildName, bool buildAlias)
|
|
|
631 {
|
|
|
632 if (buildName)
|
|
|
633 {
|
|
|
634 var alias = GetTableAlias(ts);
|
|
|
635 BuildPhysicalTable(sb, ts.Source, alias);
|
|
|
636 }
|
|
|
637
|
|
|
638 if (buildAlias)
|
|
|
639 {
|
|
|
640 if (ts.SqlTableType != SqlTableType.Expression)
|
|
|
641 {
|
|
|
642 var alias = GetTableAlias(ts);
|
|
|
643
|
|
|
644 if (!string.IsNullOrEmpty(alias))
|
|
|
645 {
|
|
|
646 if (buildName)
|
|
|
647 sb.Append(" ");
|
|
|
648 sb.Append(Convert(alias, ConvertType.NameToQueryTableAlias));
|
|
|
649 }
|
|
|
650
|
|
|
651 }
|
|
|
652 }
|
|
|
653 }
|
|
|
654
|
|
|
655 void BuildJoinTable(StringBuilder sb, SqlQuery.JoinedTable join, ref int joinCounter)
|
|
|
656 {
|
|
|
657 sb.AppendLine();
|
|
|
658 Indent++;
|
|
|
659 AppendIndent(sb);
|
|
|
660
|
|
|
661 var buildOn = BuildJoinType(sb, join);
|
|
|
662
|
|
|
663 if (IsNestedJoinParenthesisRequired && join.Table.Joins.Count != 0)
|
|
|
664 sb.Append('(');
|
|
|
665
|
|
|
666 BuildTableName(sb, join.Table, true, true);
|
|
|
667
|
|
|
668 if (IsNestedJoinSupported && join.Table.Joins.Count != 0)
|
|
|
669 {
|
|
|
670 foreach (var jt in join.Table.Joins)
|
|
|
671 BuildJoinTable(sb, jt, ref joinCounter);
|
|
|
672
|
|
|
673 if (IsNestedJoinParenthesisRequired && join.Table.Joins.Count != 0)
|
|
|
674 sb.Append(')');
|
|
|
675
|
|
|
676 if (buildOn)
|
|
|
677 {
|
|
|
678 sb.AppendLine();
|
|
|
679 AppendIndent(sb);
|
|
|
680 sb.Append("ON ");
|
|
|
681 }
|
|
|
682 }
|
|
|
683 else if (buildOn)
|
|
|
684 sb.Append(" ON ");
|
|
|
685
|
|
|
686 if (buildOn)
|
|
|
687 {
|
|
|
688 if (join.Condition.Conditions.Count != 0)
|
|
|
689 BuildSearchCondition(sb, Precedence.Unknown, join.Condition);
|
|
|
690 else
|
|
|
691 sb.Append("1=1");
|
|
|
692 }
|
|
|
693
|
|
|
694 if (joinCounter > 0)
|
|
|
695 {
|
|
|
696 joinCounter--;
|
|
|
697 sb.Append(")");
|
|
|
698 }
|
|
|
699
|
|
|
700 if (!IsNestedJoinSupported)
|
|
|
701 foreach (var jt in join.Table.Joins)
|
|
|
702 BuildJoinTable(sb, jt, ref joinCounter);
|
|
|
703
|
|
|
704 Indent--;
|
|
|
705 }
|
|
|
706
|
|
|
707 protected virtual bool BuildJoinType(StringBuilder sb, SqlQuery.JoinedTable join)
|
|
|
708 {
|
|
|
709 switch (join.JoinType)
|
|
|
710 {
|
|
|
711 case SqlQuery.JoinType.Inner : sb.Append("INNER JOIN "); return true;
|
|
|
712 case SqlQuery.JoinType.Left : sb.Append("LEFT JOIN "); return true;
|
|
|
713 case SqlQuery.JoinType.CrossApply : sb.Append("CROSS APPLY "); return false;
|
|
|
714 case SqlQuery.JoinType.OuterApply : sb.Append("OUTER APPLY "); return false;
|
|
|
715 default: throw new InvalidOperationException();
|
|
|
716 }
|
|
|
717 }
|
|
|
718
|
|
|
719 #endregion
|
|
|
720
|
|
|
721 #region Where Clause
|
|
|
722
|
|
|
723 protected virtual bool BuildWhere()
|
|
|
724 {
|
|
|
725 return SqlQuery.Where.SearchCondition.Conditions.Count != 0;
|
|
|
726 }
|
|
|
727
|
|
|
728 protected virtual void BuildWhereClause(StringBuilder sb)
|
|
|
729 {
|
|
|
730 if (!BuildWhere())
|
|
|
731 return;
|
|
|
732
|
|
|
733 AppendIndent(sb);
|
|
|
734
|
|
|
735 sb.Append("WHERE").AppendLine();
|
|
|
736
|
|
|
737 Indent++;
|
|
|
738 AppendIndent(sb);
|
|
|
739 BuildWhereSearchCondition(sb, SqlQuery.Where.SearchCondition);
|
|
|
740 Indent--;
|
|
|
741
|
|
|
742 sb.AppendLine();
|
|
|
743 }
|
|
|
744
|
|
|
745 #endregion
|
|
|
746
|
|
|
747 #region GroupBy Clause
|
|
|
748
|
|
|
749 protected virtual void BuildGroupByClause(StringBuilder sb)
|
|
|
750 {
|
|
|
751 if (SqlQuery.GroupBy.Items.Count == 0)
|
|
|
752 return;
|
|
|
753
|
|
|
754 AppendIndent(sb);
|
|
|
755
|
|
|
756 sb.Append("GROUP BY").AppendLine();
|
|
|
757
|
|
|
758 Indent++;
|
|
|
759
|
|
|
760 for (var i = 0; i < SqlQuery.GroupBy.Items.Count; i++)
|
|
|
761 {
|
|
|
762 AppendIndent(sb);
|
|
|
763
|
|
|
764 BuildExpression(sb, SqlQuery.GroupBy.Items[i]);
|
|
|
765
|
|
|
766 if (i + 1 < SqlQuery.GroupBy.Items.Count)
|
|
|
767 sb.Append(',');
|
|
|
768
|
|
|
769 sb.AppendLine();
|
|
|
770 }
|
|
|
771
|
|
|
772 Indent--;
|
|
|
773 }
|
|
|
774
|
|
|
775 #endregion
|
|
|
776
|
|
|
777 #region Having Clause
|
|
|
778
|
|
|
779 protected virtual void BuildHavingClause(StringBuilder sb)
|
|
|
780 {
|
|
|
781 if (SqlQuery.Having.SearchCondition.Conditions.Count == 0)
|
|
|
782 return;
|
|
|
783
|
|
|
784 AppendIndent(sb);
|
|
|
785
|
|
|
786 sb.Append("HAVING").AppendLine();
|
|
|
787
|
|
|
788 Indent++;
|
|
|
789 AppendIndent(sb);
|
|
|
790 BuildWhereSearchCondition(sb, SqlQuery.Having.SearchCondition);
|
|
|
791 Indent--;
|
|
|
792
|
|
|
793 sb.AppendLine();
|
|
|
794 }
|
|
|
795
|
|
|
796 #endregion
|
|
|
797
|
|
|
798 #region OrderBy Clause
|
|
|
799
|
|
|
800 protected virtual void BuildOrderByClause(StringBuilder sb)
|
|
|
801 {
|
|
|
802 if (SqlQuery.OrderBy.Items.Count == 0)
|
|
|
803 return;
|
|
|
804
|
|
|
805 AppendIndent(sb);
|
|
|
806
|
|
|
807 sb.Append("ORDER BY").AppendLine();
|
|
|
808
|
|
|
809 Indent++;
|
|
|
810
|
|
|
811 for (var i = 0; i < SqlQuery.OrderBy.Items.Count; i++)
|
|
|
812 {
|
|
|
813 AppendIndent(sb);
|
|
|
814
|
|
|
815 var item = SqlQuery.OrderBy.Items[i];
|
|
|
816
|
|
|
817 BuildExpression(sb, item.Expression);
|
|
|
818
|
|
|
819 if (item.IsDescending)
|
|
|
820 sb.Append(" DESC");
|
|
|
821
|
|
|
822 if (i + 1 < SqlQuery.OrderBy.Items.Count)
|
|
|
823 sb.Append(',');
|
|
|
824
|
|
|
825 sb.AppendLine();
|
|
|
826 }
|
|
|
827
|
|
|
828 Indent--;
|
|
|
829 }
|
|
|
830
|
|
|
831 #endregion
|
|
|
832
|
|
|
833 #region Skip/Take
|
|
|
834
|
|
|
835 protected virtual bool SkipFirst { get { return true; } }
|
|
|
836 protected virtual string SkipFormat { get { return null; } }
|
|
|
837 protected virtual string FirstFormat { get { return null; } }
|
|
|
838 protected virtual string LimitFormat { get { return null; } }
|
|
|
839 protected virtual string OffsetFormat { get { return null; } }
|
|
|
840 protected virtual bool OffsetFirst { get { return false; } }
|
|
|
841
|
|
|
842 protected bool NeedSkip { get { return SqlQuery.Select.SkipValue != null && IsSkipSupported; } }
|
|
|
843 protected bool NeedTake { get { return SqlQuery.Select.TakeValue != null && IsTakeSupported; } }
|
|
|
844
|
|
|
845 protected virtual void BuildSkipFirst(StringBuilder sb)
|
|
|
846 {
|
|
|
847 if (SkipFirst && NeedSkip && SkipFormat != null)
|
|
|
848 sb.Append(' ').AppendFormat(SkipFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue));
|
|
|
849
|
|
|
850 if (NeedTake && FirstFormat != null)
|
|
|
851 sb.Append(' ').AppendFormat(FirstFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.TakeValue));
|
|
|
852
|
|
|
853 if (!SkipFirst && NeedSkip && SkipFormat != null)
|
|
|
854 sb.Append(' ').AppendFormat(SkipFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue));
|
|
|
855 }
|
|
|
856
|
|
|
857 protected virtual void BuildOffsetLimit(StringBuilder sb)
|
|
|
858 {
|
|
|
859 var doSkip = NeedSkip && OffsetFormat != null;
|
|
|
860 var doTake = NeedTake && LimitFormat != null;
|
|
|
861
|
|
|
862 if (doSkip || doTake)
|
|
|
863 {
|
|
|
864 AppendIndent(sb);
|
|
|
865
|
|
|
866 if (doSkip && OffsetFirst)
|
|
|
867 {
|
|
|
868 sb.AppendFormat(OffsetFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue));
|
|
|
869
|
|
|
870 if (doTake)
|
|
|
871 sb.Append(' ');
|
|
|
872 }
|
|
|
873
|
|
|
874 if (doTake)
|
|
|
875 {
|
|
|
876 sb.AppendFormat(LimitFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.TakeValue));
|
|
|
877
|
|
|
878 if (doSkip)
|
|
|
879 sb.Append(' ');
|
|
|
880 }
|
|
|
881
|
|
|
882 if (doSkip && !OffsetFirst)
|
|
|
883 sb.AppendFormat(OffsetFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue));
|
|
|
884
|
|
|
885 sb.AppendLine();
|
|
|
886 }
|
|
|
887 }
|
|
|
888
|
|
|
889 #endregion
|
|
|
890
|
|
|
891 #region Builders
|
|
|
892
|
|
|
893 #region BuildSearchCondition
|
|
|
894
|
|
|
895 protected virtual void BuildWhereSearchCondition(StringBuilder sb, SqlQuery.SearchCondition condition)
|
|
|
896 {
|
|
|
897 BuildSearchCondition(sb, Precedence.Unknown, condition);
|
|
|
898 }
|
|
|
899
|
|
|
900 protected virtual void BuildSearchCondition(StringBuilder sb, SqlQuery.SearchCondition condition)
|
|
|
901 {
|
|
|
902 var isOr = (bool?)null;
|
|
|
903 var len = sb.Length;
|
|
|
904 var parentPrecedence = condition.Precedence + 1;
|
|
|
905
|
|
|
906 foreach (var cond in condition.Conditions)
|
|
|
907 {
|
|
|
908 if (isOr != null)
|
|
|
909 {
|
|
|
910 sb.Append(isOr.Value ? " OR" : " AND");
|
|
|
911
|
|
|
912 if (condition.Conditions.Count < 4 && sb.Length - len < 50 || condition != SqlQuery.Where.SearchCondition)
|
|
|
913 {
|
|
|
914 sb.Append(' ');
|
|
|
915 }
|
|
|
916 else
|
|
|
917 {
|
|
|
918 sb.AppendLine();
|
|
|
919 AppendIndent(sb);
|
|
|
920 len = sb.Length;
|
|
|
921 }
|
|
|
922 }
|
|
|
923
|
|
|
924 if (cond.IsNot)
|
|
|
925 sb.Append("NOT ");
|
|
|
926
|
|
|
927 var precedence = GetPrecedence(cond.Predicate);
|
|
|
928
|
|
|
929 BuildPredicate(sb, cond.IsNot ? Precedence.LogicalNegation : parentPrecedence, precedence, cond.Predicate);
|
|
|
930
|
|
|
931 isOr = cond.IsOr;
|
|
|
932 }
|
|
|
933 }
|
|
|
934
|
|
|
935 protected virtual void BuildSearchCondition(StringBuilder sb, int parentPrecedence, SqlQuery.SearchCondition condition)
|
|
|
936 {
|
|
|
937 var wrap = Wrap(GetPrecedence(condition as ISqlExpression), parentPrecedence);
|
|
|
938
|
|
|
939 if (wrap) sb.Append('(');
|
|
|
940 BuildSearchCondition(sb, condition);
|
|
|
941 if (wrap) sb.Append(')');
|
|
|
942 }
|
|
|
943
|
|
|
944 #endregion
|
|
|
945
|
|
|
946 #region BuildPredicate
|
|
|
947
|
|
|
948 protected virtual void BuildPredicate(StringBuilder sb, ISqlPredicate predicate)
|
|
|
949 {
|
|
|
950 switch (predicate.ElementType)
|
|
|
951 {
|
|
|
952 case QueryElementType.ExprExprPredicate :
|
|
|
953 {
|
|
|
954 var expr = (SqlQuery.Predicate.ExprExpr)predicate;
|
|
|
955
|
|
|
956 switch (expr.Operator)
|
|
|
957 {
|
|
|
958 case SqlQuery.Predicate.Operator.Equal :
|
|
|
959 case SqlQuery.Predicate.Operator.NotEqual :
|
|
|
960 {
|
|
|
961 ISqlExpression e = null;
|
|
|
962
|
|
|
963 if (expr.Expr1 is SqlValueBase && ((SqlValueBase)expr.Expr1).Value == null)
|
|
|
964 e = expr.Expr2;
|
|
|
965 else if (expr.Expr2 is SqlValueBase && ((SqlValueBase)expr.Expr2).Value == null)
|
|
|
966 e = expr.Expr1;
|
|
|
967
|
|
|
968 if (e != null)
|
|
|
969 {
|
|
|
970 BuildExpression(sb, GetPrecedence(expr), e);
|
|
|
971 sb.Append(expr.Operator == SqlQuery.Predicate.Operator.Equal ? " IS NULL" : " IS NOT NULL");
|
|
|
972 return;
|
|
|
973 }
|
|
|
974
|
|
|
975 break;
|
|
|
976 }
|
|
|
977 }
|
|
|
978
|
|
|
979 BuildExpression(sb, GetPrecedence(expr), expr.Expr1);
|
|
|
980
|
|
|
981 switch (expr.Operator)
|
|
|
982 {
|
|
|
983 case SqlQuery.Predicate.Operator.Equal : sb.Append(" = "); break;
|
|
|
984 case SqlQuery.Predicate.Operator.NotEqual : sb.Append(" <> "); break;
|
|
|
985 case SqlQuery.Predicate.Operator.Greater : sb.Append(" > "); break;
|
|
|
986 case SqlQuery.Predicate.Operator.GreaterOrEqual : sb.Append(" >= "); break;
|
|
|
987 case SqlQuery.Predicate.Operator.NotGreater : sb.Append(" !> "); break;
|
|
|
988 case SqlQuery.Predicate.Operator.Less : sb.Append(" < "); break;
|
|
|
989 case SqlQuery.Predicate.Operator.LessOrEqual : sb.Append(" <= "); break;
|
|
|
990 case SqlQuery.Predicate.Operator.NotLess : sb.Append(" !< "); break;
|
|
|
991 }
|
|
|
992
|
|
|
993 BuildExpression(sb, GetPrecedence(expr), expr.Expr2);
|
|
|
994 }
|
|
|
995
|
|
|
996 break;
|
|
|
997
|
|
|
998 case QueryElementType.LikePredicate :
|
|
|
999 BuildLikePredicate(sb, (SqlQuery.Predicate.Like)predicate);
|
|
|
1000 break;
|
|
|
1001
|
|
|
1002 case QueryElementType.BetweenPredicate :
|
|
|
1003 {
|
|
|
1004 var p = (SqlQuery.Predicate.Between)predicate;
|
|
|
1005 BuildExpression(sb, GetPrecedence(p), p.Expr1);
|
|
|
1006 if (p.IsNot) sb.Append(" NOT");
|
|
|
1007 sb.Append(" BETWEEN ");
|
|
|
1008 BuildExpression(sb, GetPrecedence(p), p.Expr2);
|
|
|
1009 sb.Append(" AND ");
|
|
|
1010 BuildExpression(sb, GetPrecedence(p), p.Expr3);
|
|
|
1011 }
|
|
|
1012
|
|
|
1013 break;
|
|
|
1014
|
|
|
1015 case QueryElementType.IsNullPredicate :
|
|
|
1016 {
|
|
|
1017 var p = (SqlQuery.Predicate.IsNull)predicate;
|
|
|
1018 BuildExpression(sb, GetPrecedence(p), p.Expr1);
|
|
|
1019 sb.Append(p.IsNot ? " IS NOT NULL" : " IS NULL");
|
|
|
1020 }
|
|
|
1021
|
|
|
1022 break;
|
|
|
1023
|
|
|
1024 case QueryElementType.InSubQueryPredicate :
|
|
|
1025 {
|
|
|
1026 var p = (SqlQuery.Predicate.InSubQuery)predicate;
|
|
|
1027 BuildExpression(sb, GetPrecedence(p), p.Expr1);
|
|
|
1028 sb.Append(p.IsNot ? " NOT IN " : " IN ");
|
|
|
1029 BuildExpression(sb, GetPrecedence(p), p.SubQuery);
|
|
|
1030 }
|
|
|
1031
|
|
|
1032 break;
|
|
|
1033
|
|
|
1034 case QueryElementType.InListPredicate :
|
|
|
1035 BuildInListPredicate(predicate, sb);
|
|
|
1036 break;
|
|
|
1037
|
|
|
1038 case QueryElementType.FuncLikePredicate :
|
|
|
1039 {
|
|
|
1040 var f = (SqlQuery.Predicate.FuncLike)predicate;
|
|
|
1041 BuildExpression(sb, f.Function.Precedence, f.Function);
|
|
|
1042 }
|
|
|
1043
|
|
|
1044 break;
|
|
|
1045
|
|
|
1046 case QueryElementType.SearchCondition :
|
|
|
1047 BuildSearchCondition(sb, predicate.Precedence, (SqlQuery.SearchCondition)predicate);
|
|
|
1048 break;
|
|
|
1049
|
|
|
1050 case QueryElementType.NotExprPredicate :
|
|
|
1051 {
|
|
|
1052 var p = (SqlQuery.Predicate.NotExpr)predicate;
|
|
|
1053
|
|
|
1054 if (p.IsNot)
|
|
|
1055 sb.Append("NOT ");
|
|
|
1056
|
|
|
1057 BuildExpression(sb, p.IsNot ? Precedence.LogicalNegation : GetPrecedence(p), p.Expr1);
|
|
|
1058 }
|
|
|
1059
|
|
|
1060 break;
|
|
|
1061
|
|
|
1062 case QueryElementType.ExprPredicate :
|
|
|
1063 {
|
|
|
1064 var p = (SqlQuery.Predicate.Expr)predicate;
|
|
|
1065
|
|
|
1066 if (p.Expr1 is SqlValue)
|
|
|
1067 {
|
|
|
1068 var value = ((SqlValue)p.Expr1).Value;
|
|
|
1069
|
|
|
1070 if (value is bool)
|
|
|
1071 {
|
|
|
1072 sb.Append((bool)value ? "1 = 1" : "1 = 0");
|
|
|
1073 return;
|
|
|
1074 }
|
|
|
1075 }
|
|
|
1076
|
|
|
1077 BuildExpression(sb, GetPrecedence(p), p.Expr1);
|
|
|
1078 }
|
|
|
1079
|
|
|
1080 break;
|
|
|
1081
|
|
|
1082 default :
|
|
|
1083 throw new InvalidOperationException();
|
|
|
1084 }
|
|
|
1085 }
|
|
|
1086
|
|
|
1087 static SqlField GetUnderlayingField(ISqlExpression expr)
|
|
|
1088 {
|
|
|
1089 switch (expr.ElementType)
|
|
|
1090 {
|
|
|
1091 case QueryElementType.SqlField: return (SqlField)expr;
|
|
|
1092 case QueryElementType.Column : return GetUnderlayingField(((SqlQuery.Column)expr).Expression);
|
|
|
1093 }
|
|
|
1094
|
|
|
1095 throw new InvalidOperationException();
|
|
|
1096 }
|
|
|
1097
|
|
|
1098 void BuildInListPredicate(ISqlPredicate predicate, StringBuilder sb)
|
|
|
1099 {
|
|
|
1100 var p = (SqlQuery.Predicate.InList)predicate;
|
|
|
1101
|
|
|
1102 if (p.Values == null || p.Values.Count == 0)
|
|
|
1103 {
|
|
|
1104 BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(false)));
|
|
|
1105 }
|
|
|
1106 else
|
|
|
1107 {
|
|
|
1108 ICollection values = p.Values;
|
|
|
1109
|
|
|
1110 if (p.Values.Count == 1 && p.Values[0] is SqlParameter &&
|
|
|
1111 !(p.Expr1.SystemType == typeof(string) && ((SqlParameter)p.Values[0]).Value is string))
|
|
|
1112 {
|
|
|
1113 var pr = (SqlParameter)p.Values[0];
|
|
|
1114
|
|
|
1115 if (pr.Value == null)
|
|
|
1116 {
|
|
|
1117 BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(false)));
|
|
|
1118 return;
|
|
|
1119 }
|
|
|
1120
|
|
|
1121 if (pr.Value is IEnumerable)
|
|
|
1122 {
|
|
|
1123 var items = (IEnumerable)pr.Value;
|
|
|
1124
|
|
|
1125 if (p.Expr1 is ISqlTableSource)
|
|
|
1126 {
|
|
|
1127 var firstValue = true;
|
|
|
1128 var table = (ISqlTableSource)p.Expr1;
|
|
|
1129 var keys = table.GetKeys(true);
|
|
|
1130
|
|
|
1131 if (keys == null || keys.Count == 0)
|
|
|
1132 throw new SqlException("Cannot create IN expression.");
|
|
|
1133
|
|
|
1134 if (keys.Count == 1)
|
|
|
1135 {
|
|
|
1136 foreach (var item in items)
|
|
|
1137 {
|
|
|
1138 if (firstValue)
|
|
|
1139 {
|
|
|
1140 firstValue = false;
|
|
|
1141 BuildExpression(sb, GetPrecedence(p), keys[0]);
|
|
|
1142 sb.Append(p.IsNot ? " NOT IN (" : " IN (");
|
|
|
1143 }
|
|
|
1144
|
|
|
1145 var field = GetUnderlayingField(keys[0]);
|
|
|
1146 var value = field.MemberMapper.GetValue(item);
|
|
|
1147
|
|
|
1148 if (value is ISqlExpression)
|
|
|
1149 BuildExpression(sb, (ISqlExpression)value);
|
|
|
1150 else
|
|
|
1151 BuildValue(sb, value);
|
|
|
1152
|
|
|
1153 sb.Append(", ");
|
|
|
1154 }
|
|
|
1155 }
|
|
|
1156 else
|
|
|
1157 {
|
|
|
1158 var len = sb.Length;
|
|
|
1159 var rem = 1;
|
|
|
1160
|
|
|
1161 foreach (var item in items)
|
|
|
1162 {
|
|
|
1163 if (firstValue)
|
|
|
1164 {
|
|
|
1165 firstValue = false;
|
|
|
1166 sb.Append('(');
|
|
|
1167 }
|
|
|
1168
|
|
|
1169 foreach (var key in keys)
|
|
|
1170 {
|
|
|
1171 var field = GetUnderlayingField(key);
|
|
|
1172 var value = field.MemberMapper.GetValue(item);
|
|
|
1173
|
|
|
1174 BuildExpression(sb, GetPrecedence(p), key);
|
|
|
1175
|
|
|
1176 if (value == null)
|
|
|
1177 {
|
|
|
1178 sb.Append(" IS NULL");
|
|
|
1179 }
|
|
|
1180 else
|
|
|
1181 {
|
|
|
1182 sb.Append(" = ");
|
|
|
1183 BuildValue(sb, value);
|
|
|
1184 }
|
|
|
1185
|
|
|
1186 sb.Append(" AND ");
|
|
|
1187 }
|
|
|
1188
|
|
|
1189 sb.Remove(sb.Length - 4, 4).Append("OR ");
|
|
|
1190
|
|
|
1191 if (sb.Length - len >= 50)
|
|
|
1192 {
|
|
|
1193 sb.AppendLine();
|
|
|
1194 AppendIndent(sb);
|
|
|
1195 sb.Append(' ');
|
|
|
1196 len = sb.Length;
|
|
|
1197 rem = 5 + Indent;
|
|
|
1198 }
|
|
|
1199 }
|
|
|
1200
|
|
|
1201 if (!firstValue)
|
|
|
1202 sb.Remove(sb.Length - rem, rem);
|
|
|
1203 }
|
|
|
1204
|
|
|
1205 if (firstValue)
|
|
|
1206 BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(p.IsNot)));
|
|
|
1207 else
|
|
|
1208 sb.Remove(sb.Length - 2, 2).Append(')');
|
|
|
1209 }
|
|
|
1210 else
|
|
|
1211 {
|
|
|
1212 BuildInListValues(sb, p, items);
|
|
|
1213 }
|
|
|
1214
|
|
|
1215 return;
|
|
|
1216 }
|
|
|
1217 }
|
|
|
1218
|
|
|
1219 BuildInListValues(sb, p, values);
|
|
|
1220 }
|
|
|
1221 }
|
|
|
1222
|
|
|
1223 void BuildInListValues(StringBuilder sb, SqlQuery.Predicate.InList predicate, IEnumerable values)
|
|
|
1224 {
|
|
|
1225 var firstValue = true;
|
|
|
1226 var len = sb.Length;
|
|
|
1227 var hasNull = false;
|
|
|
1228 var count = 0;
|
|
|
1229 var longList = false;
|
|
|
1230
|
|
|
1231 foreach (var value in values)
|
|
|
1232 {
|
|
|
1233 if (count++ >= MaxInListValuesCount)
|
|
|
1234 {
|
|
|
1235 count = 1;
|
|
|
1236 longList = true;
|
|
|
1237
|
|
|
1238 // start building next bucked
|
|
|
1239 firstValue = true;
|
|
|
1240 sb.Remove(sb.Length - 2, 2).Append(')');
|
|
|
1241 sb.Append(" OR ");
|
|
|
1242 }
|
|
|
1243
|
|
|
1244 var val = value;
|
|
|
1245
|
|
|
1246 if (val is IValueContainer)
|
|
|
1247 val = ((IValueContainer)value).Value;
|
|
|
1248
|
|
|
1249 if (val == null)
|
|
|
1250 {
|
|
|
1251 hasNull = true;
|
|
|
1252 continue;
|
|
|
1253 }
|
|
|
1254
|
|
|
1255 if (firstValue)
|
|
|
1256 {
|
|
|
1257 firstValue = false;
|
|
|
1258 BuildExpression(sb, GetPrecedence(predicate), predicate.Expr1);
|
|
|
1259 sb.Append(predicate.IsNot ? " NOT IN (" : " IN (");
|
|
|
1260 }
|
|
|
1261
|
|
|
1262 if (value is ISqlExpression)
|
|
|
1263 BuildExpression(sb, (ISqlExpression)value);
|
|
|
1264 else
|
|
|
1265 BuildValue(sb, value);
|
|
|
1266
|
|
|
1267 sb.Append(", ");
|
|
|
1268 }
|
|
|
1269
|
|
|
1270 if (firstValue)
|
|
|
1271 {
|
|
|
1272 BuildPredicate(sb,
|
|
|
1273 hasNull ?
|
|
|
1274 new SqlQuery.Predicate.IsNull(predicate.Expr1, predicate.IsNot) :
|
|
|
1275 new SqlQuery.Predicate.Expr(new SqlValue(predicate.IsNot)));
|
|
|
1276 }
|
|
|
1277 else
|
|
|
1278 {
|
|
|
1279 sb.Remove(sb.Length - 2, 2).Append(')');
|
|
|
1280
|
|
|
1281 if (hasNull)
|
|
|
1282 {
|
|
|
1283 sb.Insert(len, "(");
|
|
|
1284 sb.Append(" OR ");
|
|
|
1285 BuildPredicate(sb, new SqlQuery.Predicate.IsNull(predicate.Expr1, predicate.IsNot));
|
|
|
1286 sb.Append(")");
|
|
|
1287 }
|
|
|
1288 }
|
|
|
1289
|
|
|
1290 if (longList && !hasNull)
|
|
|
1291 {
|
|
|
1292 sb.Insert(len, "(");
|
|
|
1293 sb.Append(")");
|
|
|
1294 }
|
|
|
1295 }
|
|
|
1296
|
|
|
1297 protected void BuildPredicate(StringBuilder sb, int parentPrecedence, ISqlPredicate predicate)
|
|
|
1298 {
|
|
|
1299 BuildPredicate(sb, parentPrecedence, GetPrecedence(predicate), predicate);
|
|
|
1300 }
|
|
|
1301
|
|
|
1302 protected void BuildPredicate(StringBuilder sb, int parentPrecedence, int precedence, ISqlPredicate predicate)
|
|
|
1303 {
|
|
|
1304 var wrap = Wrap(precedence, parentPrecedence);
|
|
|
1305
|
|
|
1306 if (wrap) sb.Append('(');
|
|
|
1307 BuildPredicate(sb, predicate);
|
|
|
1308 if (wrap) sb.Append(')');
|
|
|
1309 }
|
|
|
1310
|
|
|
1311 protected virtual void BuildLikePredicate(StringBuilder sb, SqlQuery.Predicate.Like predicate)
|
|
|
1312 {
|
|
|
1313 var precedence = GetPrecedence(predicate);
|
|
|
1314
|
|
|
1315 BuildExpression(sb, precedence, predicate.Expr1);
|
|
|
1316 sb.Append(predicate.IsNot? " NOT LIKE ": " LIKE ");
|
|
|
1317 BuildExpression(sb, precedence, predicate.Expr2);
|
|
|
1318
|
|
|
1319 if (predicate.Escape != null)
|
|
|
1320 {
|
|
|
1321 sb.Append(" ESCAPE ");
|
|
|
1322 BuildExpression(sb, predicate.Escape);
|
|
|
1323 }
|
|
|
1324 }
|
|
|
1325
|
|
|
1326 #endregion
|
|
|
1327
|
|
|
1328 #region BuildExpression
|
|
|
1329
|
|
|
1330 protected virtual StringBuilder BuildExpression(
|
|
|
1331 StringBuilder sb,
|
|
|
1332 ISqlExpression expr,
|
|
|
1333 bool buildTableName,
|
|
|
1334 bool checkParentheses,
|
|
|
1335 string alias,
|
|
|
1336 ref bool addAlias)
|
|
|
1337 {
|
|
|
1338 expr = ConvertExpression(expr);
|
|
|
1339
|
|
|
1340 switch (expr.ElementType)
|
|
|
1341 {
|
|
|
1342 case QueryElementType.SqlField:
|
|
|
1343 {
|
|
|
1344 var field = (SqlField)expr;
|
|
|
1345
|
|
|
1346 if (field == field.Table.All)
|
|
|
1347 {
|
|
|
1348 sb.Append("*");
|
|
|
1349 }
|
|
|
1350 else
|
|
|
1351 {
|
|
|
1352 if (buildTableName)
|
|
|
1353 {
|
|
|
1354 var ts = SqlQuery.GetTableSource(field.Table);
|
|
|
1355
|
|
|
1356 if (ts == null)
|
|
|
1357 {
|
|
|
1358 #if DEBUG
|
|
|
1359 SqlQuery.GetTableSource(field.Table);
|
|
|
1360 #endif
|
|
|
1361
|
|
|
1362 throw new SqlException(string.Format("Table '{0}' not found.", field.Table));
|
|
|
1363 }
|
|
|
1364
|
|
|
1365 var table = GetTableAlias(ts);
|
|
|
1366
|
|
|
1367 table = table == null ?
|
|
|
1368 GetTablePhysicalName(field.Table, null) :
|
|
|
1369 Convert(table, ConvertType.NameToQueryTableAlias).ToString();
|
|
|
1370
|
|
|
1371 if (string.IsNullOrEmpty(table))
|
|
|
1372 throw new SqlException(string.Format("Table {0} should have an alias.", field.Table));
|
|
|
1373
|
|
|
1374 addAlias = alias != field.PhysicalName;
|
|
|
1375
|
|
|
1376 sb
|
|
|
1377 .Append(table)
|
|
|
1378 .Append('.');
|
|
|
1379 }
|
|
|
1380
|
|
|
1381 sb.Append(Convert(field.PhysicalName, ConvertType.NameToQueryField));
|
|
|
1382 }
|
|
|
1383 }
|
|
|
1384
|
|
|
1385 break;
|
|
|
1386
|
|
|
1387 case QueryElementType.Column:
|
|
|
1388 {
|
|
|
1389 var column = (SqlQuery.Column)expr;
|
|
|
1390
|
|
|
1391 #if DEBUG
|
|
|
1392 //if (column.ToString() == "t8.ParentID")
|
|
|
1393 //{
|
|
|
1394 // column.ToString();
|
|
|
1395 //}
|
|
|
1396
|
|
|
1397 var sql = SqlQuery.SqlText;
|
|
|
1398 #endif
|
|
|
1399
|
|
|
1400 var table = SqlQuery.GetTableSource(column.Parent);
|
|
|
1401
|
|
|
1402 if (table == null)
|
|
|
1403 {
|
|
|
1404 #if DEBUG
|
|
|
1405 table = SqlQuery.GetTableSource(column.Parent);
|
|
|
1406 #endif
|
|
|
1407
|
|
|
1408 throw new SqlException(string.Format("Table not found for '{0}'.", column));
|
|
|
1409 }
|
|
|
1410
|
|
|
1411 var tableAlias = GetTableAlias(table) ?? GetTablePhysicalName(column.Parent, null);
|
|
|
1412
|
|
|
1413 if (string.IsNullOrEmpty(tableAlias))
|
|
|
1414 throw new SqlException(string.Format("Table {0} should have an alias.", column.Parent));
|
|
|
1415
|
|
|
1416 addAlias = alias != column.Alias;
|
|
|
1417
|
|
|
1418 sb
|
|
|
1419 .Append(Convert(tableAlias, ConvertType.NameToQueryTableAlias))
|
|
|
1420 .Append('.')
|
|
|
1421 .Append(Convert(column.Alias, ConvertType.NameToQueryField));
|
|
|
1422 }
|
|
|
1423
|
|
|
1424 break;
|
|
|
1425
|
|
|
1426 case QueryElementType.SqlQuery:
|
|
|
1427 {
|
|
|
1428 var hasParentheses = checkParentheses && sb[sb.Length - 1] == '(';
|
|
|
1429
|
|
|
1430 if (!hasParentheses)
|
|
|
1431 sb.Append("(");
|
|
|
1432 sb.AppendLine();
|
|
|
1433
|
|
|
1434 _nextNesting = BuildSqlBuilder((SqlQuery)expr, sb, Indent + 1, _nextNesting, BuildStep != Step.FromClause);
|
|
|
1435
|
|
|
1436 AppendIndent(sb);
|
|
|
1437
|
|
|
1438 if (!hasParentheses)
|
|
|
1439 sb.Append(")");
|
|
|
1440 }
|
|
|
1441
|
|
|
1442 break;
|
|
|
1443
|
|
|
1444 case QueryElementType.SqlValue:
|
|
|
1445 BuildValue(sb, ((SqlValue)expr).Value);
|
|
|
1446 break;
|
|
|
1447
|
|
|
1448 case QueryElementType.SqlExpression:
|
|
|
1449 {
|
|
|
1450 var e = (SqlExpression)expr;
|
|
|
1451 var s = new StringBuilder();
|
|
|
1452
|
|
|
1453 if (e.Parameters == null || e.Parameters.Length == 0)
|
|
|
1454 sb.Append(e.Expr);
|
|
|
1455 else
|
|
|
1456 {
|
|
|
1457 var values = new object[e.Parameters.Length];
|
|
|
1458
|
|
|
1459 for (var i = 0; i < values.Length; i++)
|
|
|
1460 {
|
|
|
1461 var value = e.Parameters[i];
|
|
|
1462
|
|
|
1463 s.Length = 0;
|
|
|
1464 BuildExpression(s, GetPrecedence(e), value);
|
|
|
1465 values[i] = s.ToString();
|
|
|
1466 }
|
|
|
1467
|
|
|
1468 sb.AppendFormat(e.Expr, values);
|
|
|
1469 }
|
|
|
1470 }
|
|
|
1471
|
|
|
1472 break;
|
|
|
1473
|
|
|
1474 case QueryElementType.SqlBinaryExpression:
|
|
|
1475 BuildBinaryExpression(sb, (SqlBinaryExpression)expr);
|
|
|
1476 break;
|
|
|
1477
|
|
|
1478 case QueryElementType.SqlFunction:
|
|
|
1479 BuildFunction(sb, (SqlFunction)expr);
|
|
|
1480 break;
|
|
|
1481
|
|
|
1482 case QueryElementType.SqlParameter:
|
|
|
1483 {
|
|
|
1484 var parm = (SqlParameter)expr;
|
|
|
1485
|
|
|
1486 if (parm.IsQueryParameter)
|
|
|
1487 {
|
|
|
1488 var name = Convert(parm.Name, ConvertType.NameToQueryParameter);
|
|
|
1489 sb.Append(name);
|
|
|
1490 }
|
|
|
1491 else
|
|
|
1492 BuildValue(sb, parm.Value);
|
|
|
1493 }
|
|
|
1494
|
|
|
1495 break;
|
|
|
1496
|
|
|
1497 case QueryElementType.SqlDataType:
|
|
|
1498 BuildDataType(sb, (SqlDataType)expr);
|
|
|
1499 break;
|
|
|
1500
|
|
|
1501 case QueryElementType.SearchCondition:
|
|
|
1502 BuildSearchCondition(sb, expr.Precedence, (SqlQuery.SearchCondition)expr);
|
|
|
1503 break;
|
|
|
1504
|
|
|
1505 default:
|
|
|
1506 throw new InvalidOperationException();
|
|
|
1507 }
|
|
|
1508
|
|
|
1509 return sb;
|
|
|
1510 }
|
|
|
1511
|
|
|
1512 protected void BuildExpression(StringBuilder sb, int parentPrecedence, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
1513 {
|
|
|
1514 var wrap = Wrap(GetPrecedence(expr), parentPrecedence);
|
|
|
1515
|
|
|
1516 if (wrap) sb.Append('(');
|
|
|
1517 BuildExpression(sb, expr, true, true, alias, ref addAlias);
|
|
|
1518 if (wrap) sb.Append(')');
|
|
|
1519 }
|
|
|
1520
|
|
|
1521 protected StringBuilder BuildExpression(StringBuilder sb, ISqlExpression expr)
|
|
|
1522 {
|
|
|
1523 var dummy = false;
|
|
|
1524 return BuildExpression(sb, expr, true, true, null, ref dummy);
|
|
|
1525 }
|
|
|
1526
|
|
|
1527 protected StringBuilder BuildExpression(StringBuilder sb, ISqlExpression expr, bool buildTableName, bool checkParentheses)
|
|
|
1528 {
|
|
|
1529 var dummy = false;
|
|
|
1530 return BuildExpression(sb, expr, buildTableName, checkParentheses, null, ref dummy);
|
|
|
1531 }
|
|
|
1532
|
|
|
1533 protected void BuildExpression(StringBuilder sb, int precedence, ISqlExpression expr)
|
|
|
1534 {
|
|
|
1535 var dummy = false;
|
|
|
1536 BuildExpression(sb, precedence, expr, null, ref dummy);
|
|
|
1537 }
|
|
|
1538
|
|
|
1539 #endregion
|
|
|
1540
|
|
|
1541 #region BuildValue
|
|
|
1542
|
|
|
1543 interface INullableValueReader
|
|
|
1544 {
|
|
|
1545 object GetValue(object value);
|
|
|
1546 }
|
|
|
1547
|
|
|
1548 class NullableValueReader<T> : INullableValueReader where T : struct
|
|
|
1549 {
|
|
|
1550 public object GetValue(object value)
|
|
|
1551 {
|
|
|
1552 return ((T?)value).Value;
|
|
|
1553 }
|
|
|
1554 }
|
|
|
1555
|
|
|
1556 static readonly Dictionary<Type,INullableValueReader> _nullableValueReader = new Dictionary<Type,INullableValueReader>();
|
|
|
1557
|
|
|
1558 public NumberFormatInfo NumberFormatInfo = new NumberFormatInfo
|
|
|
1559 {
|
|
|
1560 CurrencyDecimalDigits = NumberFormatInfo.InvariantInfo.CurrencyDecimalDigits,
|
|
|
1561 CurrencyDecimalSeparator = NumberFormatInfo.InvariantInfo.CurrencyDecimalSeparator,
|
|
|
1562 CurrencyGroupSeparator = NumberFormatInfo.InvariantInfo.CurrencyGroupSeparator,
|
|
|
1563 CurrencyGroupSizes = NumberFormatInfo.InvariantInfo.CurrencyGroupSizes,
|
|
|
1564 CurrencyNegativePattern = NumberFormatInfo.InvariantInfo.CurrencyNegativePattern,
|
|
|
1565 CurrencyPositivePattern = NumberFormatInfo.InvariantInfo.CurrencyPositivePattern,
|
|
|
1566 CurrencySymbol = NumberFormatInfo.InvariantInfo.CurrencySymbol,
|
|
|
1567 NaNSymbol = NumberFormatInfo.InvariantInfo.NaNSymbol,
|
|
|
1568 NegativeInfinitySymbol = NumberFormatInfo.InvariantInfo.NegativeInfinitySymbol,
|
|
|
1569 NegativeSign = NumberFormatInfo.InvariantInfo.NegativeSign,
|
|
|
1570 NumberDecimalDigits = NumberFormatInfo.InvariantInfo.NumberDecimalDigits,
|
|
|
1571 NumberDecimalSeparator = ".",
|
|
|
1572 NumberGroupSeparator = NumberFormatInfo.InvariantInfo.NumberGroupSeparator,
|
|
|
1573 NumberGroupSizes = NumberFormatInfo.InvariantInfo.NumberGroupSizes,
|
|
|
1574 NumberNegativePattern = NumberFormatInfo.InvariantInfo.NumberNegativePattern,
|
|
|
1575 PercentDecimalDigits = NumberFormatInfo.InvariantInfo.PercentDecimalDigits,
|
|
|
1576 PercentDecimalSeparator = ".",
|
|
|
1577 PercentGroupSeparator = NumberFormatInfo.InvariantInfo.PercentGroupSeparator,
|
|
|
1578 PercentGroupSizes = NumberFormatInfo.InvariantInfo.PercentGroupSizes,
|
|
|
1579 PercentNegativePattern = NumberFormatInfo.InvariantInfo.PercentNegativePattern,
|
|
|
1580 PercentPositivePattern = NumberFormatInfo.InvariantInfo.PercentPositivePattern,
|
|
|
1581 PercentSymbol = NumberFormatInfo.InvariantInfo.PercentSymbol,
|
|
|
1582 PerMilleSymbol = NumberFormatInfo.InvariantInfo.PerMilleSymbol,
|
|
|
1583 PositiveInfinitySymbol = NumberFormatInfo.InvariantInfo.PositiveInfinitySymbol,
|
|
|
1584 PositiveSign = NumberFormatInfo.InvariantInfo.PositiveSign,
|
|
|
1585 };
|
|
|
1586
|
|
|
1587 public virtual void BuildValue(StringBuilder sb, object value)
|
|
|
1588 {
|
|
|
1589 if (value == null) sb.Append("NULL");
|
|
|
1590 else if (value is string) BuildString(sb, value.ToString());
|
|
|
1591 else if (value is char) BuildChar (sb, (char)value);
|
|
|
1592 else if (value is bool) sb.Append((bool)value ? "1" : "0");
|
|
|
1593 else if (value is DateTime) BuildDateTime(sb, value);
|
|
|
1594 else if (value is Guid) sb.Append('\'').Append(value).Append('\'');
|
|
|
1595 else if (value is decimal) sb.Append(((decimal)value).ToString(NumberFormatInfo));
|
|
|
1596 else if (value is double) sb.Append(((double) value).ToString(NumberFormatInfo));
|
|
|
1597 else if (value is float) sb.Append(((float) value).ToString(NumberFormatInfo));
|
|
|
1598 else
|
|
|
1599 {
|
|
|
1600 var type = value.GetType();
|
|
|
1601
|
|
|
1602 if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
|
1603 {
|
|
|
1604 type = type.GetGenericArguments()[0];
|
|
|
1605
|
|
|
1606 if (type.IsEnum)
|
|
|
1607 {
|
|
|
1608 lock (_nullableValueReader)
|
|
|
1609 {
|
|
|
1610 INullableValueReader reader;
|
|
|
1611
|
|
|
1612 if (_nullableValueReader.TryGetValue(type, out reader) == false)
|
|
|
1613 {
|
|
|
1614 reader = (INullableValueReader)Activator.CreateInstance(typeof(NullableValueReader<>).MakeGenericType(type));
|
|
|
1615 _nullableValueReader.Add(type, reader);
|
|
|
1616 }
|
|
|
1617
|
|
|
1618 value = reader.GetValue(value);
|
|
|
1619 }
|
|
|
1620 }
|
|
|
1621 }
|
|
|
1622
|
|
|
1623 if (type.IsEnum)
|
|
|
1624 {
|
|
|
1625 value = Map.EnumToValue(value);
|
|
|
1626
|
|
|
1627 if (value != null && !value.GetType().IsEnum)
|
|
|
1628 BuildValue(sb, value);
|
|
|
1629 else
|
|
|
1630 sb.Append(value);
|
|
|
1631 }
|
|
|
1632 else
|
|
|
1633 sb.Append(value);
|
|
|
1634 }
|
|
|
1635 }
|
|
|
1636
|
|
|
1637 protected virtual void BuildString(StringBuilder sb, string value)
|
|
|
1638 {
|
|
|
1639 sb
|
|
|
1640 .Append('\'')
|
|
|
1641 .Append(value.Replace("'", "''"))
|
|
|
1642 .Append('\'');
|
|
|
1643 }
|
|
|
1644
|
|
|
1645 protected virtual void BuildChar(StringBuilder sb, char value)
|
|
|
1646 {
|
|
|
1647 sb.Append('\'');
|
|
|
1648
|
|
|
1649 if (value == '\'') sb.Append("''");
|
|
|
1650 else sb.Append(value);
|
|
|
1651
|
|
|
1652 sb.Append('\'');
|
|
|
1653 }
|
|
|
1654
|
|
|
1655 protected virtual void BuildDateTime(StringBuilder sb, object value)
|
|
|
1656 {
|
|
|
1657 sb.Append(string.Format("'{0:yyyy-MM-dd HH:mm:ss.fff}'", value));
|
|
|
1658 }
|
|
|
1659
|
|
|
1660 #endregion
|
|
|
1661
|
|
|
1662 #region BuildBinaryExpression
|
|
|
1663
|
|
|
1664 protected virtual void BuildBinaryExpression(StringBuilder sb, SqlBinaryExpression expr)
|
|
|
1665 {
|
|
|
1666 BuildBinaryExpression(sb, expr.Operation, expr);
|
|
|
1667 }
|
|
|
1668
|
|
|
1669 protected void BuildFunction(StringBuilder sb, string name, SqlBinaryExpression expr)
|
|
|
1670 {
|
|
|
1671 sb.Append(name);
|
|
|
1672 sb.Append("(");
|
|
|
1673 BuildExpression(sb, expr.Expr1);
|
|
|
1674 sb.Append(", ");
|
|
|
1675 BuildExpression(sb, expr.Expr2);
|
|
|
1676 sb.Append(')');
|
|
|
1677 }
|
|
|
1678
|
|
|
1679 protected void BuildBinaryExpression(StringBuilder sb, string op, SqlBinaryExpression expr)
|
|
|
1680 {
|
|
|
1681 if (expr.Operation == "*" && expr.Expr1 is SqlValue)
|
|
|
1682 {
|
|
|
1683 var value = (SqlValue)expr.Expr1;
|
|
|
1684
|
|
|
1685 if (value.Value is int && (int)value.Value == -1)
|
|
|
1686 {
|
|
|
1687 sb.Append('-');
|
|
|
1688 BuildExpression(sb, GetPrecedence(expr), expr.Expr2);
|
|
|
1689 return;
|
|
|
1690 }
|
|
|
1691 }
|
|
|
1692
|
|
|
1693 BuildExpression(sb, GetPrecedence(expr), expr.Expr1);
|
|
|
1694 sb.Append(' ').Append(op).Append(' ');
|
|
|
1695 BuildExpression(sb, GetPrecedence(expr), expr.Expr2);
|
|
|
1696 }
|
|
|
1697
|
|
|
1698 #endregion
|
|
|
1699
|
|
|
1700 #region BuildFunction
|
|
|
1701
|
|
|
1702 protected virtual void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
1703 {
|
|
|
1704 if (func.Name == "CASE")
|
|
|
1705 {
|
|
|
1706 sb.Append(func.Name).AppendLine();
|
|
|
1707
|
|
|
1708 Indent++;
|
|
|
1709
|
|
|
1710 var i = 0;
|
|
|
1711
|
|
|
1712 for (; i < func.Parameters.Length - 1; i += 2)
|
|
|
1713 {
|
|
|
1714 AppendIndent(sb).Append("WHEN ");
|
|
|
1715
|
|
|
1716 var len = sb.Length;
|
|
|
1717
|
|
|
1718 BuildExpression(sb, func.Parameters[i]);
|
|
|
1719
|
|
|
1720 if (SqlExpression.NeedsEqual(func.Parameters[i]))
|
|
|
1721 {
|
|
|
1722 sb.Append(" = ");
|
|
|
1723 BuildValue(sb, true);
|
|
|
1724 }
|
|
|
1725
|
|
|
1726 if (sb.Length - len > 20)
|
|
|
1727 {
|
|
|
1728 sb.AppendLine();
|
|
|
1729 AppendIndent(sb).Append("\tTHEN ");
|
|
|
1730 }
|
|
|
1731 else
|
|
|
1732 sb.Append(" THEN ");
|
|
|
1733
|
|
|
1734 BuildExpression(sb, func.Parameters[i+1]);
|
|
|
1735 sb.AppendLine();
|
|
|
1736 }
|
|
|
1737
|
|
|
1738 if (i < func.Parameters.Length)
|
|
|
1739 {
|
|
|
1740 AppendIndent(sb).Append("ELSE ");
|
|
|
1741 BuildExpression(sb, func.Parameters[i]);
|
|
|
1742 sb.AppendLine();
|
|
|
1743 }
|
|
|
1744
|
|
|
1745 Indent--;
|
|
|
1746
|
|
|
1747 AppendIndent(sb).Append("END");
|
|
|
1748 }
|
|
|
1749 else
|
|
|
1750 BuildFunction(sb, func.Name, func.Parameters);
|
|
|
1751 }
|
|
|
1752
|
|
|
1753 protected void BuildFunction(StringBuilder sb, string name, ISqlExpression[] exprs)
|
|
|
1754 {
|
|
|
1755 sb.Append(name).Append('(');
|
|
|
1756
|
|
|
1757 var first = true;
|
|
|
1758
|
|
|
1759 foreach (var parameter in exprs)
|
|
|
1760 {
|
|
|
1761 if (!first)
|
|
|
1762 sb.Append(", ");
|
|
|
1763
|
|
|
1764 BuildExpression(sb, parameter, true, !first || name == "EXISTS");
|
|
|
1765
|
|
|
1766 first = false;
|
|
|
1767 }
|
|
|
1768
|
|
|
1769 sb.Append(')');
|
|
|
1770 }
|
|
|
1771
|
|
|
1772 #endregion
|
|
|
1773
|
|
|
1774 #region BuildDataType
|
|
|
1775
|
|
|
1776 protected virtual void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
1777 {
|
|
|
1778 sb.Append(type.SqlDbType.ToString());
|
|
|
1779
|
|
|
1780 if (type.Length > 0)
|
|
|
1781 sb.Append('(').Append(type.Length).Append(')');
|
|
|
1782
|
|
|
1783 if (type.Precision > 0)
|
|
|
1784 sb.Append('(').Append(type.Precision).Append(',').Append(type.Scale).Append(')');
|
|
|
1785 }
|
|
|
1786
|
|
|
1787 #endregion
|
|
|
1788
|
|
|
1789 #region GetPrecedence
|
|
|
1790
|
|
|
1791 protected virtual int GetPrecedence(ISqlExpression expr)
|
|
|
1792 {
|
|
|
1793 return expr.Precedence;
|
|
|
1794 }
|
|
|
1795
|
|
|
1796 protected virtual int GetPrecedence(ISqlPredicate predicate)
|
|
|
1797 {
|
|
|
1798 return predicate.Precedence;
|
|
|
1799 }
|
|
|
1800
|
|
|
1801 #endregion
|
|
|
1802
|
|
|
1803 #endregion
|
|
|
1804
|
|
|
1805 #region Internal Types
|
|
|
1806
|
|
|
1807 public enum Step
|
|
|
1808 {
|
|
|
1809 SelectClause,
|
|
|
1810 DeleteClause,
|
|
|
1811 UpdateClause,
|
|
|
1812 InsertClause,
|
|
|
1813 FromClause,
|
|
|
1814 WhereClause,
|
|
|
1815 GroupByClause,
|
|
|
1816 HavingClause,
|
|
|
1817 OrderByClause,
|
|
|
1818 OffsetLimit
|
|
|
1819 }
|
|
|
1820
|
|
|
1821 #endregion
|
|
|
1822
|
|
|
1823 #region Alternative Builders
|
|
|
1824
|
|
|
1825 protected virtual void BuildAliases(StringBuilder sb, string table, List<SqlQuery.Column> columns, string postfix)
|
|
|
1826 {
|
|
|
1827 Indent++;
|
|
|
1828
|
|
|
1829 var first = true;
|
|
|
1830
|
|
|
1831 foreach (var col in columns)
|
|
|
1832 {
|
|
|
1833 if (!first)
|
|
|
1834 sb.Append(',').AppendLine();
|
|
|
1835 first = false;
|
|
|
1836
|
|
|
1837 AppendIndent(sb).AppendFormat("{0}.{1}", table, Convert(col.Alias, ConvertType.NameToQueryFieldAlias));
|
|
|
1838
|
|
|
1839 if (postfix != null)
|
|
|
1840 sb.Append(postfix);
|
|
|
1841 }
|
|
|
1842
|
|
|
1843 Indent--;
|
|
|
1844
|
|
|
1845 sb.AppendLine();
|
|
|
1846 }
|
|
|
1847
|
|
|
1848 protected void AlternativeBuildSql(StringBuilder sb, bool implementOrderBy, Action<StringBuilder> buildSql)
|
|
|
1849 {
|
|
|
1850 if (NeedSkip)
|
|
|
1851 {
|
|
|
1852 var aliases = GetTempAliases(2, "t");
|
|
|
1853 var rnaliase = GetTempAliases(1, "rn")[0];
|
|
|
1854
|
|
|
1855 AppendIndent(sb).Append("SELECT *").AppendLine();
|
|
|
1856 AppendIndent(sb).Append("FROM"). AppendLine();
|
|
|
1857 AppendIndent(sb).Append("("). AppendLine();
|
|
|
1858 Indent++;
|
|
|
1859
|
|
|
1860 AppendIndent(sb).Append("SELECT").AppendLine();
|
|
|
1861
|
|
|
1862 Indent++;
|
|
|
1863 AppendIndent(sb).AppendFormat("{0}.*,", aliases[0]).AppendLine();
|
|
|
1864 AppendIndent(sb).Append("ROW_NUMBER() OVER");
|
|
|
1865
|
|
|
1866 if (!SqlQuery.OrderBy.IsEmpty && !implementOrderBy)
|
|
|
1867 sb.Append("()");
|
|
|
1868 else
|
|
|
1869 {
|
|
|
1870 sb.AppendLine();
|
|
|
1871 AppendIndent(sb).Append("(").AppendLine();
|
|
|
1872
|
|
|
1873 Indent++;
|
|
|
1874
|
|
|
1875 if (SqlQuery.OrderBy.IsEmpty)
|
|
|
1876 {
|
|
|
1877 AppendIndent(sb).Append("ORDER BY").AppendLine();
|
|
|
1878 BuildAliases(sb, aliases[0], SqlQuery.Select.Columns.Take(1).ToList(), null);
|
|
|
1879 }
|
|
|
1880 else
|
|
|
1881 BuildAlternativeOrderBy(sb, true);
|
|
|
1882
|
|
|
1883 Indent--;
|
|
|
1884 AppendIndent(sb).Append(")");
|
|
|
1885 }
|
|
|
1886
|
|
|
1887 sb.Append(" as ").Append(rnaliase).AppendLine();
|
|
|
1888 Indent--;
|
|
|
1889
|
|
|
1890 AppendIndent(sb).Append("FROM").AppendLine();
|
|
|
1891 AppendIndent(sb).Append("(").AppendLine();
|
|
|
1892
|
|
|
1893 Indent++;
|
|
|
1894 buildSql(sb);
|
|
|
1895 Indent--;
|
|
|
1896
|
|
|
1897 AppendIndent(sb).AppendFormat(") {0}", aliases[0]).AppendLine();
|
|
|
1898
|
|
|
1899 Indent--;
|
|
|
1900
|
|
|
1901 AppendIndent(sb).AppendFormat(") {0}", aliases[1]).AppendLine();
|
|
|
1902 AppendIndent(sb).Append("WHERE").AppendLine();
|
|
|
1903
|
|
|
1904 Indent++;
|
|
|
1905
|
|
|
1906 if (NeedTake)
|
|
|
1907 {
|
|
|
1908 var expr1 = Add(SqlQuery.Select.SkipValue, 1);
|
|
|
1909 var expr2 = Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue);
|
|
|
1910
|
|
|
1911 if (expr1 is SqlValue && expr2 is SqlValue && Equals(((SqlValue)expr1).Value, ((SqlValue)expr2).Value))
|
|
|
1912 {
|
|
|
1913 AppendIndent(sb).AppendFormat("{0}.{1} = ", aliases[1], rnaliase);
|
|
|
1914 BuildExpression(sb, expr1);
|
|
|
1915 }
|
|
|
1916 else
|
|
|
1917 {
|
|
|
1918 AppendIndent(sb).AppendFormat("{0}.{1} BETWEEN ", aliases[1], rnaliase);
|
|
|
1919 BuildExpression(sb, expr1);
|
|
|
1920 sb.Append(" AND ");
|
|
|
1921 BuildExpression(sb, expr2);
|
|
|
1922 }
|
|
|
1923 }
|
|
|
1924 else
|
|
|
1925 {
|
|
|
1926 AppendIndent(sb).AppendFormat("{0}.{1} > ", aliases[1], rnaliase);
|
|
|
1927 BuildExpression(sb, SqlQuery.Select.SkipValue);
|
|
|
1928 }
|
|
|
1929
|
|
|
1930 sb.AppendLine();
|
|
|
1931 Indent--;
|
|
|
1932 }
|
|
|
1933 else
|
|
|
1934 buildSql(sb);
|
|
|
1935 }
|
|
|
1936
|
|
|
1937 protected void AlternativeBuildSql2(StringBuilder sb, Action<StringBuilder> buildSql)
|
|
|
1938 {
|
|
|
1939 var aliases = GetTempAliases(3, "t");
|
|
|
1940
|
|
|
1941 AppendIndent(sb).Append("SELECT *").AppendLine();
|
|
|
1942 AppendIndent(sb).Append("FROM") .AppendLine();
|
|
|
1943 AppendIndent(sb).Append("(") .AppendLine();
|
|
|
1944 Indent++;
|
|
|
1945
|
|
|
1946 AppendIndent(sb).Append("SELECT TOP ");
|
|
|
1947 BuildExpression(sb, SqlQuery.Select.TakeValue);
|
|
|
1948 sb.Append(" *").AppendLine();
|
|
|
1949 AppendIndent(sb).Append("FROM").AppendLine();
|
|
|
1950 AppendIndent(sb).Append("(") .AppendLine();
|
|
|
1951 Indent++;
|
|
|
1952
|
|
|
1953 if (SqlQuery.OrderBy.IsEmpty)
|
|
|
1954 {
|
|
|
1955 AppendIndent(sb).Append("SELECT TOP ");
|
|
|
1956
|
|
|
1957 var p = SqlQuery.Select.SkipValue as SqlParameter;
|
|
|
1958
|
|
|
1959 if (p != null && !p.IsQueryParameter && SqlQuery.Select.TakeValue is SqlValue)
|
|
|
1960 BuildValue(sb, (int)p.Value + (int)((SqlValue)(SqlQuery.Select.TakeValue)).Value);
|
|
|
1961 else
|
|
|
1962 BuildExpression(sb, Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue));
|
|
|
1963
|
|
|
1964 sb.Append(" *").AppendLine();
|
|
|
1965 AppendIndent(sb).Append("FROM").AppendLine();
|
|
|
1966 AppendIndent(sb).Append("(") .AppendLine();
|
|
|
1967 Indent++;
|
|
|
1968 }
|
|
|
1969
|
|
|
1970 buildSql(sb);
|
|
|
1971
|
|
|
1972 if (SqlQuery.OrderBy.IsEmpty)
|
|
|
1973 {
|
|
|
1974 Indent--;
|
|
|
1975 AppendIndent(sb).AppendFormat(") {0}", aliases[2]).AppendLine();
|
|
|
1976 AppendIndent(sb).Append("ORDER BY").AppendLine();
|
|
|
1977 BuildAliases(sb, aliases[2], SqlQuery.Select.Columns, null);
|
|
|
1978 }
|
|
|
1979
|
|
|
1980 Indent--;
|
|
|
1981 AppendIndent(sb).AppendFormat(") {0}", aliases[1]).AppendLine();
|
|
|
1982
|
|
|
1983 if (SqlQuery.OrderBy.IsEmpty)
|
|
|
1984 {
|
|
|
1985 AppendIndent(sb).Append("ORDER BY").AppendLine();
|
|
|
1986 BuildAliases(sb, aliases[1], SqlQuery.Select.Columns, " DESC");
|
|
|
1987 }
|
|
|
1988 else
|
|
|
1989 {
|
|
|
1990 BuildAlternativeOrderBy(sb, false);
|
|
|
1991 }
|
|
|
1992
|
|
|
1993 Indent--;
|
|
|
1994 AppendIndent(sb).AppendFormat(") {0}", aliases[0]).AppendLine();
|
|
|
1995
|
|
|
1996 if (SqlQuery.OrderBy.IsEmpty)
|
|
|
1997 {
|
|
|
1998 AppendIndent(sb).Append("ORDER BY").AppendLine();
|
|
|
1999 BuildAliases(sb, aliases[0], SqlQuery.Select.Columns, null);
|
|
|
2000 }
|
|
|
2001 else
|
|
|
2002 {
|
|
|
2003 BuildAlternativeOrderBy(sb, true);
|
|
|
2004 }
|
|
|
2005 }
|
|
|
2006
|
|
|
2007 protected void BuildAlternativeOrderBy(StringBuilder sb, bool ascending)
|
|
|
2008 {
|
|
|
2009 AppendIndent(sb).Append("ORDER BY").AppendLine();
|
|
|
2010
|
|
|
2011 var obys = GetTempAliases(SqlQuery.OrderBy.Items.Count, "oby");
|
|
|
2012
|
|
|
2013 Indent++;
|
|
|
2014
|
|
|
2015 for (var i = 0; i < obys.Length; i++)
|
|
|
2016 {
|
|
|
2017 AppendIndent(sb).Append(obys[i]);
|
|
|
2018
|
|
|
2019 if ( ascending && SqlQuery.OrderBy.Items[i].IsDescending ||
|
|
|
2020 !ascending && !SqlQuery.OrderBy.Items[i].IsDescending)
|
|
|
2021 sb.Append(" DESC");
|
|
|
2022
|
|
|
2023 if (i + 1 < obys.Length)
|
|
|
2024 sb.Append(',');
|
|
|
2025
|
|
|
2026 sb.AppendLine();
|
|
|
2027 }
|
|
|
2028
|
|
|
2029 Indent--;
|
|
|
2030 }
|
|
|
2031
|
|
|
2032 protected delegate IEnumerable<SqlQuery.Column> ColumnSelector();
|
|
|
2033
|
|
|
2034 protected IEnumerable<SqlQuery.Column> AlternativeGetSelectedColumns(ColumnSelector columnSelector)
|
|
|
2035 {
|
|
|
2036 foreach (var col in columnSelector())
|
|
|
2037 yield return col;
|
|
|
2038
|
|
|
2039 var obys = GetTempAliases(SqlQuery.OrderBy.Items.Count, "oby");
|
|
|
2040
|
|
|
2041 for (var i = 0; i < obys.Length; i++)
|
|
|
2042 yield return new SqlQuery.Column(SqlQuery, SqlQuery.OrderBy.Items[i].Expression, obys[i]);
|
|
|
2043 }
|
|
|
2044
|
|
|
2045 protected bool IsDateDataType(ISqlExpression expr, string dateName)
|
|
|
2046 {
|
|
|
2047 switch (expr.ElementType)
|
|
|
2048 {
|
|
|
2049 case QueryElementType.SqlDataType : return ((SqlDataType) expr).SqlDbType == SqlDbType.Date;
|
|
|
2050 case QueryElementType.SqlExpression : return ((SqlExpression)expr).Expr == dateName;
|
|
|
2051 }
|
|
|
2052
|
|
|
2053 return false;
|
|
|
2054 }
|
|
|
2055
|
|
|
2056 protected bool IsTimeDataType(ISqlExpression expr)
|
|
|
2057 {
|
|
|
2058 switch (expr.ElementType)
|
|
|
2059 {
|
|
|
2060 case QueryElementType.SqlDataType : return ((SqlDataType)expr). SqlDbType == SqlDbType.Time;
|
|
|
2061 case QueryElementType.SqlExpression : return ((SqlExpression)expr).Expr == "Time";
|
|
|
2062 }
|
|
|
2063
|
|
|
2064 return false;
|
|
|
2065 }
|
|
|
2066
|
|
|
2067 protected ISqlExpression FloorBeforeConvert(SqlFunction func)
|
|
|
2068 {
|
|
|
2069 var par1 = func.Parameters[1];
|
|
|
2070
|
|
|
2071 return TypeHelper.IsFloatType(par1.SystemType) && TypeHelper.IsIntegerType(func.SystemType) ?
|
|
|
2072 new SqlFunction(func.SystemType, "Floor", par1) : par1;
|
|
|
2073 }
|
|
|
2074
|
|
|
2075 protected ISqlExpression AlternativeConvertToBoolean(SqlFunction func, int paramNumber)
|
|
|
2076 {
|
|
|
2077 var par = func.Parameters[paramNumber];
|
|
|
2078
|
|
|
2079 if (TypeHelper.IsFloatType(par.SystemType) || TypeHelper.IsIntegerType(par.SystemType))
|
|
|
2080 {
|
|
|
2081 var sc = new SqlQuery.SearchCondition();
|
|
|
2082
|
|
|
2083 sc.Conditions.Add(
|
|
|
2084 new SqlQuery.Condition(false, new SqlQuery.Predicate.ExprExpr(par, SqlQuery.Predicate.Operator.Equal, new SqlValue(0))));
|
|
|
2085
|
|
|
2086 return ConvertExpression(new SqlFunction(func.SystemType, "CASE", sc, new SqlValue(false), new SqlValue(true)));
|
|
|
2087 }
|
|
|
2088
|
|
|
2089 return null;
|
|
|
2090 }
|
|
|
2091
|
|
|
2092 protected SqlQuery GetAlternativeDelete(SqlQuery sqlQuery)
|
|
|
2093 {
|
|
|
2094 if (sqlQuery.IsDelete &&
|
|
|
2095 (sqlQuery.From.Tables.Count > 1 || sqlQuery.From.Tables[0].Joins.Count > 0) &&
|
|
|
2096 sqlQuery.From.Tables[0].Source is SqlTable)
|
|
|
2097 {
|
|
|
2098 var sql = new SqlQuery { QueryType = QueryType.Delete, IsParameterDependent = sqlQuery.IsParameterDependent };
|
|
|
2099
|
|
|
2100 sqlQuery.ParentSql = sql;
|
|
|
2101 sqlQuery.QueryType = QueryType.Select;
|
|
|
2102
|
|
|
2103 var table = (SqlTable)sqlQuery.From.Tables[0].Source;
|
|
|
2104 var copy = new SqlTable(table) { Alias = null };
|
|
|
2105
|
|
|
2106 var tableKeys = table.GetKeys(true);
|
|
|
2107 var copyKeys = copy. GetKeys(true);
|
|
|
2108
|
|
|
2109 if (sqlQuery.Where.SearchCondition.Conditions.Any(c => c.IsOr))
|
|
|
2110 {
|
|
|
2111 var sc1 = new SqlQuery.SearchCondition(sqlQuery.Where.SearchCondition.Conditions);
|
|
|
2112 var sc2 = new SqlQuery.SearchCondition();
|
|
|
2113
|
|
|
2114 for (var i = 0; i < tableKeys.Count; i++)
|
|
|
2115 {
|
|
|
2116 sc2.Conditions.Add(new SqlQuery.Condition(
|
|
|
2117 false,
|
|
|
2118 new SqlQuery.Predicate.ExprExpr(copyKeys[i], SqlQuery.Predicate.Operator.Equal, tableKeys[i])));
|
|
|
2119 }
|
|
|
2120
|
|
|
2121 sqlQuery.Where.SearchCondition.Conditions.Clear();
|
|
|
2122 sqlQuery.Where.SearchCondition.Conditions.Add(new SqlQuery.Condition(false, sc1));
|
|
|
2123 sqlQuery.Where.SearchCondition.Conditions.Add(new SqlQuery.Condition(false, sc2));
|
|
|
2124 }
|
|
|
2125 else
|
|
|
2126 {
|
|
|
2127 for (var i = 0; i < tableKeys.Count; i++)
|
|
|
2128 sqlQuery.Where.Expr(copyKeys[i]).Equal.Expr(tableKeys[i]);
|
|
|
2129 }
|
|
|
2130
|
|
|
2131 sql.From.Table(copy).Where.Exists(sqlQuery);
|
|
|
2132 sql.Parameters.AddRange(sqlQuery.Parameters);
|
|
|
2133
|
|
|
2134 sqlQuery.Parameters.Clear();
|
|
|
2135
|
|
|
2136 sqlQuery = sql;
|
|
|
2137 }
|
|
|
2138
|
|
|
2139 return sqlQuery;
|
|
|
2140 }
|
|
|
2141
|
|
|
2142 protected SqlQuery GetAlternativeUpdate(SqlQuery sqlQuery)
|
|
|
2143 {
|
|
|
2144 if (sqlQuery.IsUpdate && (sqlQuery.From.Tables[0].Source is SqlTable || sqlQuery.Update.Table != null))
|
|
|
2145 {
|
|
|
2146 if (sqlQuery.From.Tables.Count > 1 || sqlQuery.From.Tables[0].Joins.Count > 0)
|
|
|
2147 {
|
|
|
2148 var sql = new SqlQuery { QueryType = QueryType.Update, IsParameterDependent = sqlQuery.IsParameterDependent };
|
|
|
2149
|
|
|
2150 sqlQuery.ParentSql = sql;
|
|
|
2151 sqlQuery.QueryType = QueryType.Select;
|
|
|
2152
|
|
|
2153 var table = sqlQuery.Update.Table ?? (SqlTable)sqlQuery.From.Tables[0].Source;
|
|
|
2154
|
|
|
2155 if (sqlQuery.Update.Table != null)
|
|
|
2156 if (new QueryVisitor().Find(sqlQuery.From, t => t == table) == null)
|
|
|
2157 table = (SqlTable)new QueryVisitor().Find(sqlQuery.From,
|
|
|
2158 ex => ex is SqlTable && ((SqlTable)ex).ObjectType == table.ObjectType) ?? table;
|
|
|
2159
|
|
|
2160 var copy = new SqlTable(table);
|
|
|
2161
|
|
|
2162 var tableKeys = table.GetKeys(true);
|
|
|
2163 var copyKeys = copy. GetKeys(true);
|
|
|
2164
|
|
|
2165 for (var i = 0; i < tableKeys.Count; i++)
|
|
|
2166 sqlQuery.Where
|
|
|
2167 .Expr(copyKeys[i]).Equal.Expr(tableKeys[i]);
|
|
|
2168
|
|
|
2169 sql.From.Table(copy).Where.Exists(sqlQuery);
|
|
|
2170
|
|
|
2171 var map = new Dictionary<SqlField, SqlField>(table.Fields.Count);
|
|
|
2172
|
|
|
2173 foreach (var field in table.Fields.Values)
|
|
|
2174 map.Add(field, copy[field.Name]);
|
|
|
2175
|
|
|
2176 foreach (var item in sqlQuery.Update.Items)
|
|
|
2177 {
|
|
|
2178 var ex = new QueryVisitor().Convert(item, expr =>
|
|
|
2179 {
|
|
|
2180 var fld = expr as SqlField;
|
|
|
2181 return fld != null && map.TryGetValue(fld, out fld) ? fld : expr;
|
|
|
2182 });
|
|
|
2183
|
|
|
2184 sql.Update.Items.Add(ex);
|
|
|
2185 }
|
|
|
2186
|
|
|
2187 sql.Parameters.AddRange(sqlQuery.Parameters);
|
|
|
2188 sql.Update.Table = sqlQuery.Update.Table;
|
|
|
2189
|
|
|
2190 sqlQuery.Parameters.Clear();
|
|
|
2191 sqlQuery.Update.Items.Clear();
|
|
|
2192
|
|
|
2193 sqlQuery = sql;
|
|
|
2194 }
|
|
|
2195
|
|
|
2196 sqlQuery.From.Tables[0].Alias = "$";
|
|
|
2197 }
|
|
|
2198
|
|
|
2199 return sqlQuery;
|
|
|
2200 }
|
|
|
2201
|
|
|
2202 static bool IsBooleanParameter(ISqlExpression expr, int count, int i)
|
|
|
2203 {
|
|
|
2204 if ((i % 2 == 1 || i == count - 1) && expr.SystemType == typeof(bool) || expr.SystemType == typeof(bool?))
|
|
|
2205 {
|
|
|
2206 switch (expr.ElementType)
|
|
|
2207 {
|
|
|
2208 case QueryElementType.SearchCondition : return true;
|
|
|
2209 }
|
|
|
2210 }
|
|
|
2211
|
|
|
2212 return false;
|
|
|
2213 }
|
|
|
2214
|
|
|
2215 protected SqlFunction ConvertFunctionParameters(SqlFunction func)
|
|
|
2216 {
|
|
|
2217 if (func.Name == "CASE" &&
|
|
|
2218 func.Parameters.Select((p,i) => new { p, i }).Any(p => IsBooleanParameter(p.p, func.Parameters.Length, p.i)))
|
|
|
2219 {
|
|
|
2220 return new SqlFunction(
|
|
|
2221 func.SystemType,
|
|
|
2222 func.Name,
|
|
|
2223 func.Precedence,
|
|
|
2224 func.Parameters.Select((p,i) =>
|
|
|
2225 IsBooleanParameter(p, func.Parameters.Length, i) ?
|
|
|
2226 ConvertExpression(new SqlFunction(typeof(bool), "CASE", p, new SqlValue(true), new SqlValue(false))) :
|
|
|
2227 p
|
|
|
2228 ).ToArray());
|
|
|
2229 }
|
|
|
2230
|
|
|
2231 return func;
|
|
|
2232 }
|
|
|
2233
|
|
|
2234 #endregion
|
|
|
2235
|
|
|
2236 #region Helpers
|
|
|
2237
|
|
|
2238 protected SequenceNameAttribute GetSequenceNameAttribute(SqlTable table, bool throwException)
|
|
|
2239 {
|
|
|
2240 var identityField = table.GetIdentityField();
|
|
|
2241
|
|
|
2242 if (identityField == null)
|
|
|
2243 if (throwException)
|
|
|
2244 throw new SqlException("Identity field must be defined for '{0}'.", table.Name);
|
|
|
2245 else
|
|
|
2246 return null;
|
|
|
2247
|
|
|
2248 if (table.ObjectType == null)
|
|
|
2249 if (throwException)
|
|
|
2250 throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name);
|
|
|
2251 else
|
|
|
2252 return null;
|
|
|
2253
|
|
|
2254 var attrs = table.SequenceAttributes;
|
|
|
2255
|
|
|
2256 if (attrs == null)
|
|
|
2257 if (throwException)
|
|
|
2258 throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name);
|
|
|
2259 else
|
|
|
2260 return null;
|
|
|
2261
|
|
|
2262 SequenceNameAttribute defaultAttr = null;
|
|
|
2263
|
|
|
2264 foreach (var attr in attrs)
|
|
|
2265 {
|
|
|
2266 if (attr.ProviderName == Name)
|
|
|
2267 return attr;
|
|
|
2268
|
|
|
2269 if (defaultAttr == null && attr.ProviderName == null)
|
|
|
2270 defaultAttr = attr;
|
|
|
2271 }
|
|
|
2272
|
|
|
2273 if (defaultAttr == null)
|
|
|
2274 if (throwException)
|
|
|
2275 throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name);
|
|
|
2276 else
|
|
|
2277 return null;
|
|
|
2278
|
|
|
2279 return defaultAttr;
|
|
|
2280 }
|
|
|
2281
|
|
|
2282 static string SetAlias(string alias, int maxLen)
|
|
|
2283 {
|
|
|
2284 if (alias == null)
|
|
|
2285 return null;
|
|
|
2286
|
|
|
2287 alias = alias.TrimStart('_');
|
|
|
2288
|
|
|
2289 var cs = alias.ToCharArray();
|
|
|
2290 var replace = false;
|
|
|
2291
|
|
|
2292 for (var i = 0; i < cs.Length; i++)
|
|
|
2293 {
|
|
|
2294 var c = cs[i];
|
|
|
2295
|
|
|
2296 if (c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9' || c == '_')
|
|
|
2297 continue;
|
|
|
2298
|
|
|
2299 cs[i] = ' ';
|
|
|
2300 replace = true;
|
|
|
2301 }
|
|
|
2302
|
|
|
2303 if (replace)
|
|
|
2304 alias = new string(cs).Replace(" ", "");
|
|
|
2305
|
|
|
2306 return alias.Length == 0 || alias.Length > maxLen ? null : alias;
|
|
|
2307 }
|
|
|
2308
|
|
|
2309 protected void CheckAliases(SqlQuery sqlQuery, int maxLen)
|
|
|
2310 {
|
|
|
2311 new QueryVisitor().Visit(sqlQuery, e =>
|
|
|
2312 {
|
|
|
2313 switch (e.ElementType)
|
|
|
2314 {
|
|
|
2315 case QueryElementType.SqlField : ((SqlField) e).Alias = SetAlias(((SqlField) e).Alias, maxLen); break;
|
|
|
2316 case QueryElementType.SqlParameter : ((SqlParameter) e).Name = SetAlias(((SqlParameter) e).Name, maxLen); break;
|
|
|
2317 case QueryElementType.SqlTable : ((SqlTable) e).Alias = SetAlias(((SqlTable) e).Alias, maxLen); break;
|
|
|
2318 case QueryElementType.Join : ((Join) e).Alias = SetAlias(((Join) e).Alias, maxLen); break;
|
|
|
2319 case QueryElementType.Column : ((SqlQuery.Column) e).Alias = SetAlias(((SqlQuery.Column) e).Alias, maxLen); break;
|
|
|
2320 case QueryElementType.TableSource : ((SqlQuery.TableSource)e).Alias = SetAlias(((SqlQuery.TableSource)e).Alias, maxLen); break;
|
|
|
2321 }
|
|
|
2322 });
|
|
|
2323 }
|
|
|
2324
|
|
|
2325 static bool Wrap(int precedence, int parentPrecedence)
|
|
|
2326 {
|
|
|
2327 return
|
|
|
2328 precedence == 0 ||
|
|
|
2329 precedence < parentPrecedence ||
|
|
|
2330 (precedence == parentPrecedence &&
|
|
|
2331 (parentPrecedence == Precedence.Subtraction ||
|
|
|
2332 parentPrecedence == Precedence.LogicalNegation));
|
|
|
2333 }
|
|
|
2334
|
|
|
2335 protected string[] GetTempAliases(int n, string defaultAlias)
|
|
|
2336 {
|
|
|
2337 return SqlQuery.GetTempAliases(n, defaultAlias + (Nesting == 0? "": "n" + Nesting));
|
|
|
2338 }
|
|
|
2339
|
|
|
2340 protected static string GetTableAlias(ISqlTableSource table)
|
|
|
2341 {
|
|
|
2342 switch (table.ElementType)
|
|
|
2343 {
|
|
|
2344 case QueryElementType.TableSource :
|
|
|
2345 var ts = (SqlQuery.TableSource)table;
|
|
|
2346 var alias = string.IsNullOrEmpty(ts.Alias) ? GetTableAlias(ts.Source) : ts.Alias;
|
|
|
2347 return alias != "$" ? alias : null;
|
|
|
2348
|
|
|
2349 case QueryElementType.SqlTable :
|
|
|
2350 return ((SqlTable)table).Alias;
|
|
|
2351
|
|
|
2352 default :
|
|
|
2353 throw new InvalidOperationException();
|
|
|
2354 }
|
|
|
2355 }
|
|
|
2356
|
|
|
2357 string GetTablePhysicalName(ISqlTableSource table, string alias)
|
|
|
2358 {
|
|
|
2359 switch (table.ElementType)
|
|
|
2360 {
|
|
|
2361 case QueryElementType.SqlTable :
|
|
|
2362 {
|
|
|
2363 var tbl = (SqlTable)table;
|
|
|
2364
|
|
|
2365 var database = tbl.Database == null ? null : Convert(tbl.Database, ConvertType.NameToDatabase). ToString();
|
|
|
2366 var owner = tbl.Owner == null ? null : Convert(tbl.Owner, ConvertType.NameToOwner). ToString();
|
|
|
2367 var physicalName = tbl.PhysicalName == null ? null : Convert(tbl.PhysicalName, ConvertType.NameToQueryTable).ToString();
|
|
|
2368
|
|
|
2369 var sb = new StringBuilder();
|
|
|
2370
|
|
|
2371 if (tbl.SqlTableType == SqlTableType.Expression)
|
|
|
2372 {
|
|
|
2373 if (tbl.TableArguments == null)
|
|
|
2374 physicalName = tbl.PhysicalName;
|
|
|
2375 else
|
|
|
2376 {
|
|
|
2377 var values = new object[tbl.TableArguments.Length + 2];
|
|
|
2378
|
|
|
2379 values[0] = physicalName;
|
|
|
2380 values[1] = Convert(alias, ConvertType.NameToQueryTableAlias);
|
|
|
2381
|
|
|
2382 for (var i = 2; i < values.Length; i++)
|
|
|
2383 {
|
|
|
2384 var value = tbl.TableArguments[i - 2];
|
|
|
2385
|
|
|
2386 sb.Length = 0;
|
|
|
2387 BuildExpression(sb, Precedence.Primary, value);
|
|
|
2388 values[i] = sb.ToString();
|
|
|
2389 }
|
|
|
2390
|
|
|
2391 physicalName = string.Format(tbl.Name, values);
|
|
|
2392
|
|
|
2393 sb.Length = 0;
|
|
|
2394 }
|
|
|
2395 }
|
|
|
2396
|
|
|
2397 BuildTableName(sb, database, owner, physicalName);
|
|
|
2398
|
|
|
2399 if (tbl.SqlTableType == SqlTableType.Function)
|
|
|
2400 {
|
|
|
2401 sb.Append('(');
|
|
|
2402
|
|
|
2403 if (tbl.TableArguments != null && tbl.TableArguments.Length > 0)
|
|
|
2404 {
|
|
|
2405 var first = true;
|
|
|
2406
|
|
|
2407 foreach (var arg in tbl.TableArguments)
|
|
|
2408 {
|
|
|
2409 if (!first)
|
|
|
2410 sb.Append(", ");
|
|
|
2411
|
|
|
2412 BuildExpression(sb, arg, true, !first);
|
|
|
2413
|
|
|
2414 first = false;
|
|
|
2415 }
|
|
|
2416 }
|
|
|
2417
|
|
|
2418 sb.Append(')');
|
|
|
2419 }
|
|
|
2420
|
|
|
2421 return sb.ToString();
|
|
|
2422 }
|
|
|
2423
|
|
|
2424 case QueryElementType.TableSource :
|
|
|
2425 return GetTablePhysicalName(((SqlQuery.TableSource)table).Source, alias);
|
|
|
2426
|
|
|
2427 default :
|
|
|
2428 throw new InvalidOperationException();
|
|
|
2429 }
|
|
|
2430 }
|
|
|
2431
|
|
|
2432 protected StringBuilder AppendIndent(StringBuilder sb)
|
|
|
2433 {
|
|
|
2434 if (Indent > 0)
|
|
|
2435 sb.Append('\t', Indent);
|
|
|
2436
|
|
|
2437 return sb;
|
|
|
2438 }
|
|
|
2439
|
|
|
2440 public ISqlExpression Add(ISqlExpression expr1, ISqlExpression expr2, Type type)
|
|
|
2441 {
|
|
|
2442 return ConvertExpression(new SqlBinaryExpression(type, expr1, "+", expr2, Precedence.Additive));
|
|
|
2443 }
|
|
|
2444
|
|
|
2445 public ISqlExpression Add<T>(ISqlExpression expr1, ISqlExpression expr2)
|
|
|
2446 {
|
|
|
2447 return Add(expr1, expr2, typeof(T));
|
|
|
2448 }
|
|
|
2449
|
|
|
2450 public ISqlExpression Add(ISqlExpression expr1, int value)
|
|
|
2451 {
|
|
|
2452 return Add<int>(expr1, new SqlValue(value));
|
|
|
2453 }
|
|
|
2454
|
|
|
2455 public ISqlExpression Inc(ISqlExpression expr1)
|
|
|
2456 {
|
|
|
2457 return Add(expr1, 1);
|
|
|
2458 }
|
|
|
2459
|
|
|
2460 public ISqlExpression Sub(ISqlExpression expr1, ISqlExpression expr2, Type type)
|
|
|
2461 {
|
|
|
2462 return ConvertExpression(new SqlBinaryExpression(type, expr1, "-", expr2, Precedence.Subtraction));
|
|
|
2463 }
|
|
|
2464
|
|
|
2465 public ISqlExpression Sub<T>(ISqlExpression expr1, ISqlExpression expr2)
|
|
|
2466 {
|
|
|
2467 return Sub(expr1, expr2, typeof(T));
|
|
|
2468 }
|
|
|
2469
|
|
|
2470 public ISqlExpression Sub(ISqlExpression expr1, int value)
|
|
|
2471 {
|
|
|
2472 return Sub<int>(expr1, new SqlValue(value));
|
|
|
2473 }
|
|
|
2474
|
|
|
2475 public ISqlExpression Dec(ISqlExpression expr1)
|
|
|
2476 {
|
|
|
2477 return Sub(expr1, 1);
|
|
|
2478 }
|
|
|
2479
|
|
|
2480 public ISqlExpression Mul(ISqlExpression expr1, ISqlExpression expr2, Type type)
|
|
|
2481 {
|
|
|
2482 return ConvertExpression(new SqlBinaryExpression(type, expr1, "*", expr2, Precedence.Multiplicative));
|
|
|
2483 }
|
|
|
2484
|
|
|
2485 public ISqlExpression Mul<T>(ISqlExpression expr1, ISqlExpression expr2)
|
|
|
2486 {
|
|
|
2487 return Mul(expr1, expr2, typeof(T));
|
|
|
2488 }
|
|
|
2489
|
|
|
2490 public ISqlExpression Mul(ISqlExpression expr1, int value)
|
|
|
2491 {
|
|
|
2492 return Mul<int>(expr1, new SqlValue(value));
|
|
|
2493 }
|
|
|
2494
|
|
|
2495 public ISqlExpression Div(ISqlExpression expr1, ISqlExpression expr2, Type type)
|
|
|
2496 {
|
|
|
2497 return ConvertExpression(new SqlBinaryExpression(type, expr1, "/", expr2, Precedence.Multiplicative));
|
|
|
2498 }
|
|
|
2499
|
|
|
2500 public ISqlExpression Div<T>(ISqlExpression expr1, ISqlExpression expr2)
|
|
|
2501 {
|
|
|
2502 return Div(expr1, expr2, typeof(T));
|
|
|
2503 }
|
|
|
2504
|
|
|
2505 public ISqlExpression Div(ISqlExpression expr1, int value)
|
|
|
2506 {
|
|
|
2507 return Div<int>(expr1, new SqlValue(value));
|
|
|
2508 }
|
|
|
2509
|
|
|
2510 #endregion
|
|
|
2511
|
|
|
2512 #region DataTypes
|
|
|
2513
|
|
|
2514 protected virtual int GetMaxLength (SqlDataType type) { return SqlDataType.GetMaxLength (type.SqlDbType); }
|
|
|
2515 protected virtual int GetMaxPrecision (SqlDataType type) { return SqlDataType.GetMaxPrecision (type.SqlDbType); }
|
|
|
2516 protected virtual int GetMaxScale (SqlDataType type) { return SqlDataType.GetMaxScale (type.SqlDbType); }
|
|
|
2517 protected virtual int GetMaxDisplaySize(SqlDataType type) { return SqlDataType.GetMaxDisplaySize(type.SqlDbType); }
|
|
|
2518
|
|
|
2519 protected virtual ISqlExpression ConvertConvertion(SqlFunction func)
|
|
|
2520 {
|
|
|
2521 var from = (SqlDataType)func.Parameters[1];
|
|
|
2522 var to = (SqlDataType)func.Parameters[0];
|
|
|
2523
|
|
|
2524 if (to.Type == typeof(object))
|
|
|
2525 return func.Parameters[2];
|
|
|
2526
|
|
|
2527 if (to.Precision > 0)
|
|
|
2528 {
|
|
|
2529 var maxPrecision = GetMaxPrecision(from);
|
|
|
2530 var maxScale = GetMaxScale (from);
|
|
|
2531 var newPrecision = maxPrecision >= 0 ? Math.Min(to.Precision, maxPrecision) : to.Precision;
|
|
|
2532 var newScale = maxScale >= 0 ? Math.Min(to.Scale, maxScale) : to.Scale;
|
|
|
2533
|
|
|
2534 if (to.Precision != newPrecision || to.Scale != newScale)
|
|
|
2535 to = new SqlDataType(to.SqlDbType, to.Type, newPrecision, newScale);
|
|
|
2536 }
|
|
|
2537 else if (to.Length > 0)
|
|
|
2538 {
|
|
|
2539 var maxLength = to.Type == typeof(string) ? GetMaxDisplaySize(from) : GetMaxLength(from);
|
|
|
2540 var newLength = maxLength >= 0 ? Math.Min(to.Length, maxLength) : to.Length;
|
|
|
2541
|
|
|
2542 if (to.Length != newLength)
|
|
|
2543 to = new SqlDataType(to.SqlDbType, to.Type, newLength);
|
|
|
2544 }
|
|
|
2545 else if (from.Type == typeof(short) && to.Type == typeof(int))
|
|
|
2546 return func.Parameters[2];
|
|
|
2547
|
|
|
2548 return ConvertExpression(new SqlFunction(func.SystemType, "Convert", to, func.Parameters[2]));
|
|
|
2549 }
|
|
|
2550
|
|
|
2551 #endregion
|
|
|
2552
|
|
|
2553 #region ISqlProvider Members
|
|
|
2554
|
|
|
2555 public virtual ISqlExpression ConvertExpression(ISqlExpression expression)
|
|
|
2556 {
|
|
|
2557 switch (expression.ElementType)
|
|
|
2558 {
|
|
|
2559 case QueryElementType.SqlBinaryExpression:
|
|
|
2560
|
|
|
2561 #region SqlBinaryExpression
|
|
|
2562
|
|
|
2563 {
|
|
|
2564 var be = (SqlBinaryExpression)expression;
|
|
|
2565
|
|
|
2566 switch (be.Operation)
|
|
|
2567 {
|
|
|
2568 case "+":
|
|
|
2569 if (be.Expr1 is SqlValue)
|
|
|
2570 {
|
|
|
2571 var v1 = (SqlValue)be.Expr1;
|
|
|
2572 if (v1.Value is int && (int) v1.Value == 0 ||
|
|
|
2573 v1.Value is string && (string)v1.Value == "") return be.Expr2;
|
|
|
2574 }
|
|
|
2575
|
|
|
2576 if (be.Expr2 is SqlValue)
|
|
|
2577 {
|
|
|
2578 var v2 = (SqlValue) be.Expr2;
|
|
|
2579
|
|
|
2580 if (v2.Value is int)
|
|
|
2581 {
|
|
|
2582 if ((int)v2.Value == 0) return be.Expr1;
|
|
|
2583
|
|
|
2584 if (be.Expr1 is SqlBinaryExpression)
|
|
|
2585 {
|
|
|
2586 var be1 = (SqlBinaryExpression) be.Expr1;
|
|
|
2587
|
|
|
2588 if (be1.Expr2 is SqlValue)
|
|
|
2589 {
|
|
|
2590 var be1v2 = (SqlValue)be1.Expr2;
|
|
|
2591
|
|
|
2592 if (be1v2.Value is int)
|
|
|
2593 {
|
|
|
2594 switch (be1.Operation)
|
|
|
2595 {
|
|
|
2596 case "+":
|
|
|
2597 {
|
|
|
2598 var value = (int)be1v2.Value + (int)v2.Value;
|
|
|
2599 var oper = be1.Operation;
|
|
|
2600
|
|
|
2601 if (value < 0)
|
|
|
2602 {
|
|
|
2603 value = - value;
|
|
|
2604 oper = "-";
|
|
|
2605 }
|
|
|
2606
|
|
|
2607 return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence);
|
|
|
2608 }
|
|
|
2609
|
|
|
2610 case "-":
|
|
|
2611 {
|
|
|
2612 var value = (int)be1v2.Value - (int)v2.Value;
|
|
|
2613 var oper = be1.Operation;
|
|
|
2614
|
|
|
2615 if (value < 0)
|
|
|
2616 {
|
|
|
2617 value = - value;
|
|
|
2618 oper = "+";
|
|
|
2619 }
|
|
|
2620
|
|
|
2621 return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence);
|
|
|
2622 }
|
|
|
2623 }
|
|
|
2624 }
|
|
|
2625 }
|
|
|
2626 }
|
|
|
2627 }
|
|
|
2628 else if (v2.Value is string)
|
|
|
2629 {
|
|
|
2630 if ((string)v2.Value == "") return be.Expr1;
|
|
|
2631
|
|
|
2632 if (be.Expr1 is SqlBinaryExpression)
|
|
|
2633 {
|
|
|
2634 var be1 = (SqlBinaryExpression)be.Expr1;
|
|
|
2635
|
|
|
2636 if (be1.Expr2 is SqlValue)
|
|
|
2637 {
|
|
|
2638 var value = ((SqlValue)be1.Expr2).Value;
|
|
|
2639
|
|
|
2640 if (value is string)
|
|
|
2641 return new SqlBinaryExpression(
|
|
|
2642 be1.SystemType,
|
|
|
2643 be1.Expr1,
|
|
|
2644 be1.Operation,
|
|
|
2645 new SqlValue(string.Concat(value, v2.Value)));
|
|
|
2646 }
|
|
|
2647 }
|
|
|
2648 }
|
|
|
2649 }
|
|
|
2650
|
|
|
2651 if (be.Expr1 is SqlValue && be.Expr2 is SqlValue)
|
|
|
2652 {
|
|
|
2653 var v1 = (SqlValue)be.Expr1;
|
|
|
2654 var v2 = (SqlValue)be.Expr2;
|
|
|
2655 if (v1.Value is int && v2.Value is int) return new SqlValue((int)v1.Value + (int)v2.Value);
|
|
|
2656 if (v1.Value is string || v2.Value is string) return new SqlValue(v1.Value.ToString() + v2.Value);
|
|
|
2657 }
|
|
|
2658
|
|
|
2659 if (be.Expr1.SystemType == typeof(string) && be.Expr2.SystemType != typeof(string))
|
|
|
2660 {
|
|
|
2661 var len = be.Expr2.SystemType == null ? 100 : SqlDataType.GetMaxDisplaySize(SqlDataType.GetDataType(be.Expr2.SystemType).SqlDbType);
|
|
|
2662
|
|
|
2663 if (len <= 0)
|
|
|
2664 len = 100;
|
|
|
2665
|
|
|
2666 return new SqlBinaryExpression(
|
|
|
2667 be.SystemType,
|
|
|
2668 be.Expr1,
|
|
|
2669 be.Operation,
|
|
|
2670 ConvertExpression(new SqlFunction(typeof(string), "Convert", new SqlDataType(SqlDbType.VarChar, len), be.Expr2)),
|
|
|
2671 be.Precedence);
|
|
|
2672 }
|
|
|
2673
|
|
|
2674 if (be.Expr1.SystemType != typeof(string) && be.Expr2.SystemType == typeof(string))
|
|
|
2675 {
|
|
|
2676 var len = be.Expr1.SystemType == null ? 100 : SqlDataType.GetMaxDisplaySize(SqlDataType.GetDataType(be.Expr1.SystemType).SqlDbType);
|
|
|
2677
|
|
|
2678 if (len <= 0)
|
|
|
2679 len = 100;
|
|
|
2680
|
|
|
2681 return new SqlBinaryExpression(
|
|
|
2682 be.SystemType,
|
|
|
2683 ConvertExpression(new SqlFunction(typeof(string), "Convert", new SqlDataType(SqlDbType.VarChar, len), be.Expr1)),
|
|
|
2684 be.Operation,
|
|
|
2685 be.Expr2,
|
|
|
2686 be.Precedence);
|
|
|
2687 }
|
|
|
2688
|
|
|
2689 break;
|
|
|
2690
|
|
|
2691 case "-":
|
|
|
2692 if (be.Expr2 is SqlValue)
|
|
|
2693 {
|
|
|
2694 var v2 = (SqlValue) be.Expr2;
|
|
|
2695
|
|
|
2696 if (v2.Value is int)
|
|
|
2697 {
|
|
|
2698 if ((int)v2.Value == 0) return be.Expr1;
|
|
|
2699
|
|
|
2700 if (be.Expr1 is SqlBinaryExpression)
|
|
|
2701 {
|
|
|
2702 var be1 = (SqlBinaryExpression)be.Expr1;
|
|
|
2703
|
|
|
2704 if (be1.Expr2 is SqlValue)
|
|
|
2705 {
|
|
|
2706 var be1v2 = (SqlValue)be1.Expr2;
|
|
|
2707
|
|
|
2708 if (be1v2.Value is int)
|
|
|
2709 {
|
|
|
2710 switch (be1.Operation)
|
|
|
2711 {
|
|
|
2712 case "+":
|
|
|
2713 {
|
|
|
2714 var value = (int)be1v2.Value - (int)v2.Value;
|
|
|
2715 var oper = be1.Operation;
|
|
|
2716
|
|
|
2717 if (value < 0)
|
|
|
2718 {
|
|
|
2719 value = -value;
|
|
|
2720 oper = "-";
|
|
|
2721 }
|
|
|
2722
|
|
|
2723 return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence);
|
|
|
2724 }
|
|
|
2725
|
|
|
2726 case "-":
|
|
|
2727 {
|
|
|
2728 var value = (int)be1v2.Value + (int)v2.Value;
|
|
|
2729 var oper = be1.Operation;
|
|
|
2730
|
|
|
2731 if (value < 0)
|
|
|
2732 {
|
|
|
2733 value = -value;
|
|
|
2734 oper = "+";
|
|
|
2735 }
|
|
|
2736
|
|
|
2737 return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence);
|
|
|
2738 }
|
|
|
2739 }
|
|
|
2740 }
|
|
|
2741 }
|
|
|
2742 }
|
|
|
2743 }
|
|
|
2744 }
|
|
|
2745
|
|
|
2746 if (be.Expr1 is SqlValue && be.Expr2 is SqlValue)
|
|
|
2747 {
|
|
|
2748 var v1 = (SqlValue)be.Expr1;
|
|
|
2749 var v2 = (SqlValue)be.Expr2;
|
|
|
2750 if (v1.Value is int && v2.Value is int) return new SqlValue((int)v1.Value - (int)v2.Value);
|
|
|
2751 }
|
|
|
2752
|
|
|
2753 break;
|
|
|
2754
|
|
|
2755 case "*":
|
|
|
2756 if (be.Expr1 is SqlValue)
|
|
|
2757 {
|
|
|
2758 var v1 = (SqlValue)be.Expr1;
|
|
|
2759
|
|
|
2760 if (v1.Value is int)
|
|
|
2761 {
|
|
|
2762 var v1v = (int)v1.Value;
|
|
|
2763
|
|
|
2764 switch (v1v)
|
|
|
2765 {
|
|
|
2766 case 0 : return new SqlValue(0);
|
|
|
2767 case 1 : return be.Expr2;
|
|
|
2768 default :
|
|
|
2769 {
|
|
|
2770 var be2 = be.Expr2 as SqlBinaryExpression;
|
|
|
2771
|
|
|
2772 if (be2 != null && be2.Operation == "*" && be2.Expr1 is SqlValue)
|
|
|
2773 {
|
|
|
2774 var be2v1 = be2.Expr1 as SqlValue;
|
|
|
2775
|
|
|
2776 if (be2v1.Value is int)
|
|
|
2777 return ConvertExpression(
|
|
|
2778 new SqlBinaryExpression(be2.SystemType, new SqlValue(v1v * (int)be2v1.Value), "*", be2.Expr2));
|
|
|
2779 }
|
|
|
2780
|
|
|
2781 break;
|
|
|
2782 }
|
|
|
2783
|
|
|
2784 }
|
|
|
2785 }
|
|
|
2786 }
|
|
|
2787
|
|
|
2788 if (be.Expr2 is SqlValue)
|
|
|
2789 {
|
|
|
2790 var v2 = (SqlValue)be.Expr2;
|
|
|
2791 if (v2.Value is int && (int)v2.Value == 1) return be.Expr1;
|
|
|
2792 if (v2.Value is int && (int)v2.Value == 0) return new SqlValue(0);
|
|
|
2793 }
|
|
|
2794
|
|
|
2795 if (be.Expr1 is SqlValue && be.Expr2 is SqlValue)
|
|
|
2796 {
|
|
|
2797 var v1 = (SqlValue)be.Expr1;
|
|
|
2798 var v2 = (SqlValue)be.Expr2;
|
|
|
2799
|
|
|
2800 if (v1.Value is int)
|
|
|
2801 {
|
|
|
2802 if (v2.Value is int) return new SqlValue((int) v1.Value * (int) v2.Value);
|
|
|
2803 if (v2.Value is double) return new SqlValue((int) v1.Value * (double)v2.Value);
|
|
|
2804 }
|
|
|
2805 else if (v1.Value is double)
|
|
|
2806 {
|
|
|
2807 if (v2.Value is int) return new SqlValue((double)v1.Value * (int) v2.Value);
|
|
|
2808 if (v2.Value is double) return new SqlValue((double)v1.Value * (double)v2.Value);
|
|
|
2809 }
|
|
|
2810 }
|
|
|
2811
|
|
|
2812 break;
|
|
|
2813 }
|
|
|
2814 }
|
|
|
2815
|
|
|
2816 #endregion
|
|
|
2817
|
|
|
2818 break;
|
|
|
2819
|
|
|
2820 case QueryElementType.SqlFunction:
|
|
|
2821
|
|
|
2822 #region SqlFunction
|
|
|
2823
|
|
|
2824 {
|
|
|
2825 var func = (SqlFunction)expression;
|
|
|
2826
|
|
|
2827 switch (func.Name)
|
|
|
2828 {
|
|
|
2829 case "ConvertToCaseCompareTo":
|
|
|
2830 return ConvertExpression(new SqlFunction(func.SystemType, "CASE",
|
|
|
2831 new SqlQuery.SearchCondition().Expr(func.Parameters[0]). Greater .Expr(func.Parameters[1]).ToExpr(), new SqlValue(1),
|
|
|
2832 new SqlQuery.SearchCondition().Expr(func.Parameters[0]). Equal .Expr(func.Parameters[1]).ToExpr(), new SqlValue(0),
|
|
|
2833 new SqlValue(-1)));
|
|
|
2834
|
|
|
2835 case "$Convert$": return ConvertConvertion(func);
|
|
|
2836 case "Average" : return new SqlFunction(func.SystemType, "Avg", func.Parameters);
|
|
|
2837 case "Max" :
|
|
|
2838 case "Min" :
|
|
|
2839 {
|
|
|
2840 if (func.SystemType == typeof(bool) || func.SystemType == typeof(bool?))
|
|
|
2841 {
|
|
|
2842 return new SqlFunction(typeof(int), func.Name,
|
|
|
2843 new SqlFunction(func.SystemType, "CASE", func.Parameters[0], new SqlValue(1), new SqlValue(0)));
|
|
|
2844 }
|
|
|
2845
|
|
|
2846 break;
|
|
|
2847 }
|
|
|
2848
|
|
|
2849 case "CASE" :
|
|
|
2850 {
|
|
|
2851 var parms = func.Parameters;
|
|
|
2852 var len = parms.Length;
|
|
|
2853
|
|
|
2854 for (var i = 0; i < parms.Length - 1; i += 2)
|
|
|
2855 {
|
|
|
2856 var value = parms[i] as SqlValue;
|
|
|
2857
|
|
|
2858 if (value != null)
|
|
|
2859 {
|
|
|
2860 if ((bool)value.Value == false)
|
|
|
2861 {
|
|
|
2862 var newParms = new ISqlExpression[parms.Length - 2];
|
|
|
2863
|
|
|
2864 if (i != 0)
|
|
|
2865 Array.Copy(parms, 0, newParms, 0, i);
|
|
|
2866
|
|
|
2867 Array.Copy(parms, i + 2, newParms, i, parms.Length - i - 2);
|
|
|
2868
|
|
|
2869 parms = newParms;
|
|
|
2870 i -= 2;
|
|
|
2871 }
|
|
|
2872 else
|
|
|
2873 {
|
|
|
2874 var newParms = new ISqlExpression[i + 1];
|
|
|
2875
|
|
|
2876 if (i != 0)
|
|
|
2877 Array.Copy(parms, 0, newParms, 0, i);
|
|
|
2878
|
|
|
2879 newParms[i] = parms[i + 1];
|
|
|
2880
|
|
|
2881 parms = newParms;
|
|
|
2882 break;
|
|
|
2883 }
|
|
|
2884 }
|
|
|
2885 }
|
|
|
2886
|
|
|
2887 if (parms.Length == 1)
|
|
|
2888 return parms[0];
|
|
|
2889
|
|
|
2890 if (parms.Length != len)
|
|
|
2891 return new SqlFunction(func.SystemType, func.Name, func.Precedence, parms);
|
|
|
2892 }
|
|
|
2893
|
|
|
2894 break;
|
|
|
2895
|
|
|
2896 case "Convert":
|
|
|
2897 {
|
|
|
2898 var from = func.Parameters[1] as SqlFunction;
|
|
|
2899 var typef = TypeHelper.GetUnderlyingType(func.SystemType);
|
|
|
2900
|
|
|
2901 if (from != null && from.Name == "Convert" && TypeHelper.GetUnderlyingType(from.Parameters[1].SystemType) == typef)
|
|
|
2902 return from.Parameters[1];
|
|
|
2903
|
|
|
2904 var fe = func.Parameters[1] as SqlExpression;
|
|
|
2905
|
|
|
2906 if (fe != null && fe.Expr == "Cast({0} as {1})" && TypeHelper.GetUnderlyingType(fe.Parameters[0].SystemType) == typef)
|
|
|
2907 return fe.Parameters[0];
|
|
|
2908 }
|
|
|
2909
|
|
|
2910 break;
|
|
|
2911 }
|
|
|
2912 }
|
|
|
2913
|
|
|
2914 #endregion
|
|
|
2915
|
|
|
2916 break;
|
|
|
2917
|
|
|
2918 case QueryElementType.SearchCondition :
|
|
|
2919 SqlQuery.OptimizeSearchCondition((SqlQuery.SearchCondition)expression);
|
|
|
2920 break;
|
|
|
2921
|
|
|
2922 case QueryElementType.SqlExpression :
|
|
|
2923 {
|
|
|
2924 var se = (SqlExpression)expression;
|
|
|
2925
|
|
|
2926 if (se.Expr == "{0}" && se.Parameters.Length == 1 && se.Parameters[0] != null)
|
|
|
2927 return se.Parameters[0];
|
|
|
2928 }
|
|
|
2929
|
|
|
2930 break;
|
|
|
2931 }
|
|
|
2932
|
|
|
2933 return expression;
|
|
|
2934 }
|
|
|
2935
|
|
|
2936 public virtual ISqlPredicate ConvertPredicate(ISqlPredicate predicate)
|
|
|
2937 {
|
|
|
2938 switch (predicate.ElementType)
|
|
|
2939 {
|
|
|
2940 case QueryElementType.ExprExprPredicate:
|
|
|
2941 {
|
|
|
2942 var expr = (SqlQuery.Predicate.ExprExpr)predicate;
|
|
|
2943
|
|
|
2944 if (expr.Operator == SqlQuery.Predicate.Operator.Equal && expr.Expr1 is SqlValue && expr.Expr2 is SqlValue)
|
|
|
2945 {
|
|
|
2946 var value = Equals(((SqlValue)expr.Expr1).Value, ((SqlValue)expr.Expr2).Value);
|
|
|
2947 return new SqlQuery.Predicate.Expr(new SqlValue(value), Precedence.Comparison);
|
|
|
2948 }
|
|
|
2949
|
|
|
2950 switch (expr.Operator)
|
|
|
2951 {
|
|
|
2952 case SqlQuery.Predicate.Operator.Equal :
|
|
|
2953 case SqlQuery.Predicate.Operator.NotEqual :
|
|
|
2954 case SqlQuery.Predicate.Operator.Greater :
|
|
|
2955 case SqlQuery.Predicate.Operator.GreaterOrEqual:
|
|
|
2956 case SqlQuery.Predicate.Operator.Less :
|
|
|
2957 case SqlQuery.Predicate.Operator.LessOrEqual :
|
|
|
2958 predicate = OptimizeCase(expr);
|
|
|
2959 break;
|
|
|
2960 }
|
|
|
2961
|
|
|
2962 if (predicate is SqlQuery.Predicate.ExprExpr)
|
|
|
2963 {
|
|
|
2964 expr = (SqlQuery.Predicate.ExprExpr)predicate;
|
|
|
2965
|
|
|
2966 switch (expr.Operator)
|
|
|
2967 {
|
|
|
2968 case SqlQuery.Predicate.Operator.Equal :
|
|
|
2969 case SqlQuery.Predicate.Operator.NotEqual :
|
|
|
2970 var expr1 = expr.Expr1;
|
|
|
2971 var expr2 = expr.Expr2;
|
|
|
2972
|
|
|
2973 if (expr1.CanBeNull() && expr2.CanBeNull())
|
|
|
2974 {
|
|
|
2975 if (expr1 is SqlParameter || expr2 is SqlParameter)
|
|
|
2976 SqlQuery.IsParameterDependent = true;
|
|
|
2977 else
|
|
|
2978 if (expr1 is SqlQuery.Column || expr1 is SqlField)
|
|
|
2979 if (expr2 is SqlQuery.Column || expr2 is SqlField)
|
|
|
2980 predicate = ConvertEqualPredicate(expr);
|
|
|
2981 }
|
|
|
2982
|
|
|
2983 break;
|
|
|
2984 }
|
|
|
2985 }
|
|
|
2986 }
|
|
|
2987
|
|
|
2988 break;
|
|
|
2989
|
|
|
2990 case QueryElementType.NotExprPredicate:
|
|
|
2991 {
|
|
|
2992 var expr = (SqlQuery.Predicate.NotExpr)predicate;
|
|
|
2993
|
|
|
2994 if (expr.IsNot && expr.Expr1 is SqlQuery.SearchCondition)
|
|
|
2995 {
|
|
|
2996 var sc = (SqlQuery.SearchCondition)expr.Expr1;
|
|
|
2997
|
|
|
2998 if (sc.Conditions.Count == 1)
|
|
|
2999 {
|
|
|
3000 var cond = sc.Conditions[0];
|
|
|
3001
|
|
|
3002 if (cond.IsNot)
|
|
|
3003 return cond.Predicate;
|
|
|
3004
|
|
|
3005 if (cond.Predicate is SqlQuery.Predicate.ExprExpr)
|
|
|
3006 {
|
|
|
3007 var ee = (SqlQuery.Predicate.ExprExpr)cond.Predicate;
|
|
|
3008
|
|
|
3009 if (ee.Operator == SqlQuery.Predicate.Operator.Equal)
|
|
|
3010 return new SqlQuery.Predicate.ExprExpr(ee.Expr1, SqlQuery.Predicate.Operator.NotEqual, ee.Expr2);
|
|
|
3011
|
|
|
3012 if (ee.Operator == SqlQuery.Predicate.Operator.NotEqual)
|
|
|
3013 return new SqlQuery.Predicate.ExprExpr(ee.Expr1, SqlQuery.Predicate.Operator.Equal, ee.Expr2);
|
|
|
3014 }
|
|
|
3015 }
|
|
|
3016 }
|
|
|
3017 }
|
|
|
3018
|
|
|
3019 break;
|
|
|
3020 }
|
|
|
3021
|
|
|
3022 return predicate;
|
|
|
3023 }
|
|
|
3024
|
|
|
3025 protected ISqlPredicate ConvertEqualPredicate(SqlQuery.Predicate.ExprExpr expr)
|
|
|
3026 {
|
|
|
3027 var expr1 = expr.Expr1;
|
|
|
3028 var expr2 = expr.Expr2;
|
|
|
3029 var cond = new SqlQuery.SearchCondition();
|
|
|
3030
|
|
|
3031 if (expr.Operator == SqlQuery.Predicate.Operator.Equal)
|
|
|
3032 cond
|
|
|
3033 .Expr(expr1).IsNull. And .Expr(expr2).IsNull. Or
|
|
|
3034 .Expr(expr1).IsNotNull. And .Expr(expr2).IsNotNull. And .Expr(expr1).Equal.Expr(expr2);
|
|
|
3035 else
|
|
|
3036 cond
|
|
|
3037 .Expr(expr1).IsNull. And .Expr(expr2).IsNotNull. Or
|
|
|
3038 .Expr(expr1).IsNotNull. And .Expr(expr2).IsNull. Or
|
|
|
3039 .Expr(expr1).NotEqual.Expr(expr2);
|
|
|
3040
|
|
|
3041 return cond;
|
|
|
3042 }
|
|
|
3043
|
|
|
3044 static SqlQuery.Predicate.Operator InvertOperator(SqlQuery.Predicate.Operator op, bool skipEqual)
|
|
|
3045 {
|
|
|
3046 switch (op)
|
|
|
3047 {
|
|
|
3048 case SqlQuery.Predicate.Operator.Equal : return skipEqual ? op : SqlQuery.Predicate.Operator.NotEqual;
|
|
|
3049 case SqlQuery.Predicate.Operator.NotEqual : return skipEqual ? op : SqlQuery.Predicate.Operator.Equal;
|
|
|
3050 case SqlQuery.Predicate.Operator.Greater : return SqlQuery.Predicate.Operator.LessOrEqual;
|
|
|
3051 case SqlQuery.Predicate.Operator.NotLess :
|
|
|
3052 case SqlQuery.Predicate.Operator.GreaterOrEqual : return SqlQuery.Predicate.Operator.Less;
|
|
|
3053 case SqlQuery.Predicate.Operator.Less : return SqlQuery.Predicate.Operator.GreaterOrEqual;
|
|
|
3054 case SqlQuery.Predicate.Operator.NotGreater :
|
|
|
3055 case SqlQuery.Predicate.Operator.LessOrEqual : return SqlQuery.Predicate.Operator.Greater;
|
|
|
3056 default: throw new InvalidOperationException();
|
|
|
3057 }
|
|
|
3058 }
|
|
|
3059
|
|
|
3060 ISqlPredicate OptimizeCase(SqlQuery.Predicate.ExprExpr expr)
|
|
|
3061 {
|
|
|
3062 var value = expr.Expr1 as SqlValue;
|
|
|
3063 var func = expr.Expr2 as SqlFunction;
|
|
|
3064 var valueFirst = false;
|
|
|
3065
|
|
|
3066 if (value != null && func != null)
|
|
|
3067 {
|
|
|
3068 valueFirst = true;
|
|
|
3069 }
|
|
|
3070 else
|
|
|
3071 {
|
|
|
3072 value = expr.Expr2 as SqlValue;
|
|
|
3073 func = expr.Expr1 as SqlFunction;
|
|
|
3074 }
|
|
|
3075
|
|
|
3076 if (value != null && func != null && func.Name == "CASE")
|
|
|
3077 {
|
|
|
3078 if (value.Value is int && func.Parameters.Length == 5)
|
|
|
3079 {
|
|
|
3080 var c1 = func.Parameters[0] as SqlQuery.SearchCondition;
|
|
|
3081 var v1 = func.Parameters[1] as SqlValue;
|
|
|
3082 var c2 = func.Parameters[2] as SqlQuery.SearchCondition;
|
|
|
3083 var v2 = func.Parameters[3] as SqlValue;
|
|
|
3084 var v3 = func.Parameters[4] as SqlValue;
|
|
|
3085
|
|
|
3086 if (c1 != null && c1.Conditions.Count == 1 && v1 != null && v1.Value is int &&
|
|
|
3087 c2 != null && c2.Conditions.Count == 1 && v2 != null && v2.Value is int && v3 != null && v3.Value is int)
|
|
|
3088 {
|
|
|
3089 var ee1 = c1.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr;
|
|
|
3090 var ee2 = c2.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr;
|
|
|
3091
|
|
|
3092 if (ee1 != null && ee2 != null && ee1.Expr1.Equals(ee2.Expr1) && ee1.Expr2.Equals(ee2.Expr2))
|
|
|
3093 {
|
|
|
3094 int e = 0, g = 0, l = 0;
|
|
|
3095
|
|
|
3096 if (ee1.Operator == SqlQuery.Predicate.Operator.Equal || ee2.Operator == SqlQuery.Predicate.Operator.Equal) e = 1;
|
|
|
3097 if (ee1.Operator == SqlQuery.Predicate.Operator.Greater || ee2.Operator == SqlQuery.Predicate.Operator.Greater) g = 1;
|
|
|
3098 if (ee1.Operator == SqlQuery.Predicate.Operator.Less || ee2.Operator == SqlQuery.Predicate.Operator.Less) l = 1;
|
|
|
3099
|
|
|
3100 if (e + g + l == 2)
|
|
|
3101 {
|
|
|
3102 var n = (int)value.Value;
|
|
|
3103 var i1 = (int)v1.Value;
|
|
|
3104 var i2 = (int)v2.Value;
|
|
|
3105 var i3 = (int)v3.Value;
|
|
|
3106
|
|
|
3107 var n1 = Compare(valueFirst ? n : i1, valueFirst ? i1 : n, expr.Operator) ? 1 : 0;
|
|
|
3108 var n2 = Compare(valueFirst ? n : i2, valueFirst ? i2 : n, expr.Operator) ? 1 : 0;
|
|
|
3109 var n3 = Compare(valueFirst ? n : i3, valueFirst ? i3 : n, expr.Operator) ? 1 : 0;
|
|
|
3110
|
|
|
3111 if (n1 + n2 + n3 == 1)
|
|
|
3112 {
|
|
|
3113 if (n1 == 1) return ee1;
|
|
|
3114 if (n2 == 1) return ee2;
|
|
|
3115
|
|
|
3116 return ConvertPredicate(new SqlQuery.Predicate.ExprExpr(
|
|
|
3117 ee1.Expr1,
|
|
|
3118 e == 0 ? SqlQuery.Predicate.Operator.Equal :
|
|
|
3119 g == 0 ? SqlQuery.Predicate.Operator.Greater :
|
|
|
3120 SqlQuery.Predicate.Operator.Less,
|
|
|
3121 ee1.Expr2));
|
|
|
3122 }
|
|
|
3123
|
|
|
3124 // CASE
|
|
|
3125 // WHEN [p].[FirstName] > 'John'
|
|
|
3126 // THEN 1
|
|
|
3127 // WHEN [p].[FirstName] = 'John'
|
|
|
3128 // THEN 0
|
|
|
3129 // ELSE -1
|
|
|
3130 // END <= 0
|
|
|
3131 if (ee1.Operator == SqlQuery.Predicate.Operator.Greater && i1 == 1 &&
|
|
|
3132 ee2.Operator == SqlQuery.Predicate.Operator.Equal && i2 == 0 &&
|
|
|
3133 i3 == -1 && n == 0)
|
|
|
3134 {
|
|
|
3135 return ConvertPredicate(new SqlQuery.Predicate.ExprExpr(
|
|
|
3136 ee1.Expr1,
|
|
|
3137 valueFirst ? InvertOperator(expr.Operator, true) : expr.Operator,
|
|
|
3138 ee1.Expr2));
|
|
|
3139 }
|
|
|
3140 }
|
|
|
3141 }
|
|
|
3142 }
|
|
|
3143 }
|
|
|
3144 else if (value.Value is bool && func.Parameters.Length == 3)
|
|
|
3145 {
|
|
|
3146 var c1 = func.Parameters[0] as SqlQuery.SearchCondition;
|
|
|
3147 var v1 = func.Parameters[1] as SqlValue;
|
|
|
3148 var v2 = func.Parameters[2] as SqlValue;
|
|
|
3149
|
|
|
3150 if (c1 != null && c1.Conditions.Count == 1 && v1 != null && v1.Value is bool && v2 != null && v2.Value is bool)
|
|
|
3151 {
|
|
|
3152 var bv = (bool)value.Value;
|
|
|
3153 var bv1 = (bool)v1.Value;
|
|
|
3154 var bv2 = (bool)v2.Value;
|
|
|
3155
|
|
|
3156 if (bv == bv1 && expr.Operator == SqlQuery.Predicate.Operator.Equal ||
|
|
|
3157 bv != bv1 && expr.Operator == SqlQuery.Predicate.Operator.NotEqual)
|
|
|
3158 {
|
|
|
3159 return c1;
|
|
|
3160 }
|
|
|
3161
|
|
|
3162 if (bv == bv2 && expr.Operator == SqlQuery.Predicate.Operator.NotEqual ||
|
|
|
3163 bv != bv1 && expr.Operator == SqlQuery.Predicate.Operator.Equal)
|
|
|
3164 {
|
|
|
3165 var ee = c1.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr;
|
|
|
3166
|
|
|
3167 if (ee != null)
|
|
|
3168 {
|
|
|
3169 var op = InvertOperator(ee.Operator, false);
|
|
|
3170 return new SqlQuery.Predicate.ExprExpr(ee.Expr1, op, ee.Expr2);
|
|
|
3171 }
|
|
|
3172
|
|
|
3173 var sc = new SqlQuery.SearchCondition();
|
|
|
3174
|
|
|
3175 sc.Conditions.Add(new SqlQuery.Condition(true, c1));
|
|
|
3176
|
|
|
3177 return sc;
|
|
|
3178 }
|
|
|
3179 }
|
|
|
3180 }
|
|
|
3181 else if (expr.Operator == SqlQuery.Predicate.Operator.Equal && func.Parameters.Length == 3)
|
|
|
3182 {
|
|
|
3183 var sc = func.Parameters[0] as SqlQuery.SearchCondition;
|
|
|
3184 var v1 = func.Parameters[1] as SqlValue;
|
|
|
3185 var v2 = func.Parameters[2] as SqlValue;
|
|
|
3186
|
|
|
3187 if (sc != null && v1 != null && v2 != null)
|
|
|
3188 {
|
|
|
3189 if (Equals(value.Value, v1.Value))
|
|
|
3190 return sc;
|
|
|
3191
|
|
|
3192 if (Equals(value.Value, v2.Value) && !sc.CanBeNull())
|
|
|
3193 return ConvertPredicate(new SqlQuery.Predicate.NotExpr(sc, true, Precedence.LogicalNegation));
|
|
|
3194 }
|
|
|
3195 }
|
|
|
3196 }
|
|
|
3197
|
|
|
3198 return expr;
|
|
|
3199 }
|
|
|
3200
|
|
|
3201 static bool Compare(int v1, int v2, SqlQuery.Predicate.Operator op)
|
|
|
3202 {
|
|
|
3203 switch (op)
|
|
|
3204 {
|
|
|
3205 case SqlQuery.Predicate.Operator.Equal: return v1 == v2;
|
|
|
3206 case SqlQuery.Predicate.Operator.NotEqual: return v1 != v2;
|
|
|
3207 case SqlQuery.Predicate.Operator.Greater: return v1 > v2;
|
|
|
3208 case SqlQuery.Predicate.Operator.NotLess:
|
|
|
3209 case SqlQuery.Predicate.Operator.GreaterOrEqual: return v1 >= v2;
|
|
|
3210 case SqlQuery.Predicate.Operator.Less: return v1 < v2;
|
|
|
3211 case SqlQuery.Predicate.Operator.NotGreater:
|
|
|
3212 case SqlQuery.Predicate.Operator.LessOrEqual: return v1 <= v2;
|
|
|
3213 }
|
|
|
3214
|
|
|
3215 throw new InvalidOperationException();
|
|
|
3216 }
|
|
|
3217
|
|
|
3218 public virtual SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
3219 {
|
|
|
3220 sqlQuery.FinalizeAndValidate(IsApplyJoinSupported, IsGroupByExpressionSupported);
|
|
|
3221
|
|
|
3222 if (!IsCountSubQuerySupported) sqlQuery = MoveCountSubQuery (sqlQuery);
|
|
|
3223 if (!IsSubQueryColumnSupported) sqlQuery = MoveSubQueryColumn(sqlQuery);
|
|
|
3224
|
|
|
3225 if (!IsCountSubQuerySupported || !IsSubQueryColumnSupported)
|
|
|
3226 sqlQuery.FinalizeAndValidate(IsApplyJoinSupported, IsGroupByExpressionSupported);
|
|
|
3227
|
|
|
3228 return sqlQuery;
|
|
|
3229 }
|
|
|
3230
|
|
|
3231 SqlQuery MoveCountSubQuery(SqlQuery sqlQuery)
|
|
|
3232 {
|
|
|
3233 new QueryVisitor().Visit(sqlQuery, MoveCountSubQuery);
|
|
|
3234 return sqlQuery;
|
|
|
3235 }
|
|
|
3236
|
|
|
3237 void MoveCountSubQuery(IQueryElement element)
|
|
|
3238 {
|
|
|
3239 if (element.ElementType != QueryElementType.SqlQuery)
|
|
|
3240 return;
|
|
|
3241
|
|
|
3242 var query = (SqlQuery)element;
|
|
|
3243
|
|
|
3244 for (var i = 0; i < query.Select.Columns.Count; i++)
|
|
|
3245 {
|
|
|
3246 var col = query.Select.Columns[i];
|
|
|
3247
|
|
|
3248 // The column is a subquery.
|
|
|
3249 //
|
|
|
3250 if (col.Expression.ElementType == QueryElementType.SqlQuery)
|
|
|
3251 {
|
|
|
3252 var subQuery = (SqlQuery)col.Expression;
|
|
|
3253 var isCount = false;
|
|
|
3254
|
|
|
3255 // Check if subquery is Count subquery.
|
|
|
3256 //
|
|
|
3257 if (subQuery.Select.Columns.Count == 1)
|
|
|
3258 {
|
|
|
3259 var subCol = subQuery.Select.Columns[0];
|
|
|
3260
|
|
|
3261 if (subCol.Expression.ElementType == QueryElementType.SqlFunction)
|
|
|
3262 isCount = ((SqlFunction)subCol.Expression).Name == "Count";
|
|
|
3263 }
|
|
|
3264
|
|
|
3265 if (!isCount)
|
|
|
3266 continue;
|
|
|
3267
|
|
|
3268 // Check if subquery where clause does not have ORs.
|
|
|
3269 //
|
|
|
3270 SqlQuery.OptimizeSearchCondition(subQuery.Where.SearchCondition);
|
|
|
3271
|
|
|
3272 var allAnd = true;
|
|
|
3273
|
|
|
3274 for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++)
|
|
|
3275 {
|
|
|
3276 var cond = subQuery.Where.SearchCondition.Conditions[j];
|
|
|
3277
|
|
|
3278 if (cond.IsOr)
|
|
|
3279 allAnd = false;
|
|
|
3280 }
|
|
|
3281
|
|
|
3282 if (!allAnd || !ConvertCountSubQuery(subQuery))
|
|
|
3283 continue;
|
|
|
3284
|
|
|
3285 // Collect tables.
|
|
|
3286 //
|
|
|
3287 var allTables = new HashSet<ISqlTableSource>();
|
|
|
3288 var levelTables = new HashSet<ISqlTableSource>();
|
|
|
3289
|
|
|
3290 new QueryVisitor().Visit(subQuery, e =>
|
|
|
3291 {
|
|
|
3292 if (e is ISqlTableSource)
|
|
|
3293 allTables.Add((ISqlTableSource)e);
|
|
|
3294 });
|
|
|
3295
|
|
|
3296 new QueryVisitor().Visit(subQuery, e =>
|
|
|
3297 {
|
|
|
3298 if (e is ISqlTableSource)
|
|
|
3299 if (subQuery.From.IsChild((ISqlTableSource)e))
|
|
|
3300 levelTables.Add((ISqlTableSource)e);
|
|
|
3301 });
|
|
|
3302
|
|
|
3303 Func<IQueryElement,bool> checkTable = e =>
|
|
|
3304 {
|
|
|
3305 switch (e.ElementType)
|
|
|
3306 {
|
|
|
3307 case QueryElementType.SqlField : return !allTables.Contains(((SqlField) e).Table);
|
|
|
3308 case QueryElementType.Column : return !allTables.Contains(((SqlQuery.Column)e).Parent);
|
|
|
3309 }
|
|
|
3310 return false;
|
|
|
3311 };
|
|
|
3312
|
|
|
3313 var join = SqlQuery.LeftJoin(subQuery);
|
|
|
3314
|
|
|
3315 query.From.Tables[0].Joins.Add(join.JoinedTable);
|
|
|
3316
|
|
|
3317 for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++)
|
|
|
3318 {
|
|
|
3319 var cond = subQuery.Where.SearchCondition.Conditions[j];
|
|
|
3320
|
|
|
3321 if (new QueryVisitor().Find(cond, checkTable) == null)
|
|
|
3322 continue;
|
|
|
3323
|
|
|
3324 var replaced = new Dictionary<IQueryElement,IQueryElement>();
|
|
|
3325
|
|
|
3326 var nc = new QueryVisitor().Convert(cond, e =>
|
|
|
3327 {
|
|
|
3328 var ne = e;
|
|
|
3329
|
|
|
3330 switch (e.ElementType)
|
|
|
3331 {
|
|
|
3332 case QueryElementType.SqlField :
|
|
|
3333 if (replaced.TryGetValue(e, out ne))
|
|
|
3334 return ne;
|
|
|
3335
|
|
|
3336 if (levelTables.Contains(((SqlField)e).Table))
|
|
|
3337 {
|
|
|
3338 subQuery.GroupBy.Expr((SqlField)e);
|
|
|
3339 ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)];
|
|
|
3340 break;
|
|
|
3341 }
|
|
|
3342
|
|
|
3343 break;
|
|
|
3344
|
|
|
3345 case QueryElementType.Column :
|
|
|
3346 if (replaced.TryGetValue(e, out ne))
|
|
|
3347 return ne;
|
|
|
3348
|
|
|
3349 if (levelTables.Contains(((SqlQuery.Column)e).Parent))
|
|
|
3350 {
|
|
|
3351 subQuery.GroupBy.Expr((SqlQuery.Column)e);
|
|
|
3352 ne = subQuery.Select.Columns[subQuery.Select.Add((SqlQuery.Column)e)];
|
|
|
3353 break;
|
|
|
3354 }
|
|
|
3355
|
|
|
3356 break;
|
|
|
3357 }
|
|
|
3358
|
|
|
3359 if (!ReferenceEquals(e, ne))
|
|
|
3360 replaced.Add(e, ne);
|
|
|
3361
|
|
|
3362 return ne;
|
|
|
3363 });
|
|
|
3364
|
|
|
3365 if (nc != null && !ReferenceEquals(nc, cond))
|
|
|
3366 {
|
|
|
3367 join.JoinedTable.Condition.Conditions.Add(nc);
|
|
|
3368 subQuery.Where.SearchCondition.Conditions.RemoveAt(j);
|
|
|
3369 j--;
|
|
|
3370 }
|
|
|
3371 }
|
|
|
3372
|
|
|
3373 if (!query.GroupBy.IsEmpty/* && subQuery.Select.Columns.Count > 1*/)
|
|
|
3374 {
|
|
|
3375 var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
|
|
|
3376
|
|
|
3377 subQuery.Select.Columns.RemoveAt(0);
|
|
|
3378
|
|
|
3379 query.Select.Columns[i].Expression =
|
|
|
3380 new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0]);
|
|
|
3381 }
|
|
|
3382 else
|
|
|
3383 {
|
|
|
3384 query.Select.Columns[i].Expression = subQuery.Select.Columns[0];
|
|
|
3385 }
|
|
|
3386 }
|
|
|
3387 }
|
|
|
3388 }
|
|
|
3389
|
|
|
3390 SqlQuery MoveSubQueryColumn(SqlQuery sqlQuery)
|
|
|
3391 {
|
|
|
3392 var dic = new Dictionary<IQueryElement,IQueryElement>();
|
|
|
3393
|
|
|
3394 new QueryVisitor().Visit(sqlQuery, element =>
|
|
|
3395 {
|
|
|
3396 if (element.ElementType != QueryElementType.SqlQuery)
|
|
|
3397 return;
|
|
|
3398
|
|
|
3399 var query = (SqlQuery)element;
|
|
|
3400
|
|
|
3401 for (var i = 0; i < query.Select.Columns.Count; i++)
|
|
|
3402 {
|
|
|
3403 var col = query.Select.Columns[i];
|
|
|
3404
|
|
|
3405 if (col.Expression.ElementType == QueryElementType.SqlQuery)
|
|
|
3406 {
|
|
|
3407 var subQuery = (SqlQuery)col.Expression;
|
|
|
3408 var allTables = new HashSet<ISqlTableSource>();
|
|
|
3409 var levelTables = new HashSet<ISqlTableSource>();
|
|
|
3410
|
|
|
3411 Func<IQueryElement,bool> checkTable = e =>
|
|
|
3412 {
|
|
|
3413 switch (e.ElementType)
|
|
|
3414 {
|
|
|
3415 case QueryElementType.SqlField : return !allTables.Contains(((SqlField)e).Table);
|
|
|
3416 case QueryElementType.Column : return !allTables.Contains(((SqlQuery.Column)e).Parent);
|
|
|
3417 }
|
|
|
3418 return false;
|
|
|
3419 };
|
|
|
3420
|
|
|
3421 new QueryVisitor().Visit(subQuery, e =>
|
|
|
3422 {
|
|
|
3423 if (e is ISqlTableSource /*&& subQuery.From.IsChild((ISqlTableSource)e)*/)
|
|
|
3424 allTables.Add((ISqlTableSource)e);
|
|
|
3425 });
|
|
|
3426
|
|
|
3427 new QueryVisitor().Visit(subQuery, e =>
|
|
|
3428 {
|
|
|
3429 if (e is ISqlTableSource && subQuery.From.IsChild((ISqlTableSource)e))
|
|
|
3430 levelTables.Add((ISqlTableSource)e);
|
|
|
3431 });
|
|
|
3432
|
|
|
3433 if (IsSubQueryColumnSupported && new QueryVisitor().Find(subQuery, checkTable) == null)
|
|
|
3434 continue;
|
|
|
3435
|
|
|
3436 var join = SqlQuery.LeftJoin(subQuery);
|
|
|
3437
|
|
|
3438 query.From.Tables[0].Joins.Add(join.JoinedTable);
|
|
|
3439
|
|
|
3440 SqlQuery.OptimizeSearchCondition(subQuery.Where.SearchCondition);
|
|
|
3441
|
|
|
3442 var isCount = false;
|
|
|
3443 var isAggregated = false;
|
|
|
3444
|
|
|
3445 if (subQuery.Select.Columns.Count == 1)
|
|
|
3446 {
|
|
|
3447 var subCol = subQuery.Select.Columns[0];
|
|
|
3448
|
|
|
3449 if (subCol.Expression.ElementType == QueryElementType.SqlFunction)
|
|
|
3450 {
|
|
|
3451 switch (((SqlFunction)subCol.Expression).Name)
|
|
|
3452 {
|
|
|
3453 case "Min" :
|
|
|
3454 case "Max" :
|
|
|
3455 case "Sum" :
|
|
|
3456 case "Average" : isAggregated = true; break;
|
|
|
3457 case "Count" : isAggregated = true; isCount = true; break;
|
|
|
3458 }
|
|
|
3459 }
|
|
|
3460 }
|
|
|
3461
|
|
|
3462 if (IsSubQueryColumnSupported && !isCount)
|
|
|
3463 continue;
|
|
|
3464
|
|
|
3465 var allAnd = true;
|
|
|
3466
|
|
|
3467 for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++)
|
|
|
3468 {
|
|
|
3469 var cond = subQuery.Where.SearchCondition.Conditions[j];
|
|
|
3470
|
|
|
3471 if (cond.IsOr)
|
|
|
3472 allAnd = false;
|
|
|
3473 }
|
|
|
3474
|
|
|
3475 if (!allAnd)
|
|
|
3476 continue;
|
|
|
3477
|
|
|
3478 var modified = false;
|
|
|
3479
|
|
|
3480 for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++)
|
|
|
3481 {
|
|
|
3482 var cond = subQuery.Where.SearchCondition.Conditions[j];
|
|
|
3483
|
|
|
3484 if (new QueryVisitor().Find(cond, checkTable) == null)
|
|
|
3485 continue;
|
|
|
3486
|
|
|
3487 var replaced = new Dictionary<IQueryElement,IQueryElement>();
|
|
|
3488
|
|
|
3489 var nc = new QueryVisitor().Convert(cond, delegate(IQueryElement e)
|
|
|
3490 {
|
|
|
3491 var ne = e;
|
|
|
3492
|
|
|
3493 switch (e.ElementType)
|
|
|
3494 {
|
|
|
3495 case QueryElementType.SqlField :
|
|
|
3496 if (replaced.TryGetValue(e, out ne))
|
|
|
3497 return ne;
|
|
|
3498
|
|
|
3499 if (levelTables.Contains(((SqlField)e).Table))
|
|
|
3500 {
|
|
|
3501 if (isAggregated)
|
|
|
3502 subQuery.GroupBy.Expr((SqlField)e);
|
|
|
3503 ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)];
|
|
|
3504 break;
|
|
|
3505 }
|
|
|
3506
|
|
|
3507 break;
|
|
|
3508
|
|
|
3509 case QueryElementType.Column :
|
|
|
3510 if (replaced.TryGetValue(e, out ne))
|
|
|
3511 return ne;
|
|
|
3512
|
|
|
3513 if (levelTables.Contains(((SqlQuery.Column)e).Parent))
|
|
|
3514 {
|
|
|
3515 if (isAggregated)
|
|
|
3516 subQuery.GroupBy.Expr((SqlQuery.Column)e);
|
|
|
3517 ne = subQuery.Select.Columns[subQuery.Select.Add((SqlQuery.Column)e)];
|
|
|
3518 break;
|
|
|
3519 }
|
|
|
3520
|
|
|
3521 break;
|
|
|
3522 }
|
|
|
3523
|
|
|
3524 if (!ReferenceEquals(e, ne))
|
|
|
3525 replaced.Add(e, ne);
|
|
|
3526
|
|
|
3527 return ne;
|
|
|
3528 });
|
|
|
3529
|
|
|
3530 if (nc != null && !ReferenceEquals(nc, cond))
|
|
|
3531 {
|
|
|
3532 modified = true;
|
|
|
3533
|
|
|
3534 join.JoinedTable.Condition.Conditions.Add(nc);
|
|
|
3535 subQuery.Where.SearchCondition.Conditions.RemoveAt(j);
|
|
|
3536 j--;
|
|
|
3537 }
|
|
|
3538 }
|
|
|
3539
|
|
|
3540 if (modified || isAggregated)
|
|
|
3541 {
|
|
|
3542 if (isCount && !query.GroupBy.IsEmpty)
|
|
|
3543 {
|
|
|
3544 var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
|
|
|
3545
|
|
|
3546 subQuery.Select.Columns.RemoveAt(0);
|
|
|
3547
|
|
|
3548 query.Select.Columns[i] = new SqlQuery.Column(
|
|
|
3549 query,
|
|
|
3550 new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0]));
|
|
|
3551 }
|
|
|
3552 else if (isAggregated && !query.GroupBy.IsEmpty)
|
|
|
3553 {
|
|
|
3554 var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression;
|
|
|
3555
|
|
|
3556 subQuery.Select.Columns.RemoveAt(0);
|
|
|
3557
|
|
|
3558 var idx = subQuery.Select.Add(oldFunc.Parameters[0]);
|
|
|
3559
|
|
|
3560 query.Select.Columns[i] = new SqlQuery.Column(
|
|
|
3561 query,
|
|
|
3562 new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[idx]));
|
|
|
3563 }
|
|
|
3564 else
|
|
|
3565 {
|
|
|
3566 query.Select.Columns[i] = new SqlQuery.Column(query, subQuery.Select.Columns[0]);
|
|
|
3567 }
|
|
|
3568
|
|
|
3569 dic.Add(col, query.Select.Columns[i]);
|
|
|
3570 }
|
|
|
3571 }
|
|
|
3572 }
|
|
|
3573 });
|
|
|
3574
|
|
|
3575 sqlQuery = new QueryVisitor().Convert(sqlQuery, e =>
|
|
|
3576 {
|
|
|
3577 IQueryElement ne;
|
|
|
3578 return dic.TryGetValue(e, out ne) ? ne : e;
|
|
|
3579 });
|
|
|
3580
|
|
|
3581 return sqlQuery;
|
|
|
3582 }
|
|
|
3583
|
|
|
3584 public virtual ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
|
|
|
3585 {
|
|
|
3586 return null;
|
|
|
3587 }
|
|
|
3588
|
|
|
3589 private string _name;
|
|
|
3590 public virtual string Name
|
|
|
3591 {
|
|
|
3592 get { return _name ?? (_name = GetType().Name.Replace("SqlProvider", "")); }
|
|
|
3593 }
|
|
|
3594
|
|
|
3595 #endregion
|
|
|
3596
|
|
|
3597 #region Linq Support
|
|
|
3598
|
|
|
3599 public virtual LambdaExpression ConvertMember(MemberInfo mi)
|
|
|
3600 {
|
|
|
3601 return Expressions.ConvertMember(Name, mi);
|
|
|
3602 }
|
|
|
3603
|
|
|
3604 #endregion
|
|
|
3605 }
|
|
|
3606 }
|
