5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
28 using System.Collections.Generic;
\r
29 using System.Globalization;
\r
31 using System.Linq.Expressions;
\r
32 using System.Text.RegularExpressions;
\r
34 using DbLinq.Data.Linq.Sql;
\r
35 using DbLinq.Data.Linq.Sugar.Expressions;
\r
39 namespace DbLinq.Vendor.Implementation
\r
44 class SqlProvider : ISqlProvider
\r
46 public virtual ExpressionTranslator GetTranslator()
\r
48 return new ExpressionTranslator();
\r
52 /// Builds an insert clause
\r
54 /// <param name="table">Table name</param>
\r
55 /// <param name="inputColumns">Columns to be inserted</param>
\r
56 /// <param name="inputValues">Values to be inserted into columns</param>
\r
57 /// <returns></returns>
\r
58 public virtual SqlStatement GetInsert(SqlStatement table, IList<SqlStatement> inputColumns, IList<SqlStatement> inputValues)
\r
60 if (inputColumns.Count == 0)
\r
61 return SqlStatement.Empty;
\r
63 var insertBuilder = new SqlStatementBuilder("INSERT INTO ");
\r
64 insertBuilder.Append(table);
\r
65 insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputColumns));
\r
66 insertBuilder.Append(" VALUES");
\r
67 insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputValues));
\r
68 return insertBuilder.ToSqlStatement();
\r
72 /// Builds the statements that gets back the IDs for the inserted statement
\r
74 /// <param name="table"></param>
\r
75 /// <param name="autoPKColumn">Auto-generated PK columns for reference (i.e. AUTO_INCREMENT)</param>
\r
76 /// <param name="inputPKColumns">PK columns for reference</param>
\r
77 /// <param name="inputPKValues">PK values for reference</param>
\r
78 /// <param name="outputParameters">Expected output parameters</param>
\r
79 /// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
\r
80 /// <returns></returns>
\r
81 public virtual SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> pkColumns, IList<SqlStatement> pkValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions)
\r
83 if (autoPKColumn.Count == outputParameters.Count)
\r
84 return "SELECT @@IDENTITY";
\r
86 var insertIds = new SqlStatementBuilder("SELECT ");
\r
87 insertIds.AppendFormat(" ({0})", SqlStatement.Join(", ", outputColumns));
\r
88 insertIds.Append(" FROM ");
\r
89 insertIds.Append(table);
\r
90 insertIds.Append(" WHERE ");
\r
91 bool valueSet = false;
\r
92 if (autoPKColumn.Count > 0)
\r
94 insertIds.AppendFormat("{0} = @@IDENTITY", autoPKColumn[0]);
\r
97 for (IEnumerator<SqlStatement> column = pkColumns.GetEnumerator(), value = pkValues.GetEnumerator(); column.MoveNext() && value.MoveNext();)
\r
100 insertIds.Append(" AND ");
\r
101 insertIds.AppendFormat("{0} = {1}", column.Current, value.Current);
\r
104 return insertIds.ToSqlStatement();
\r
108 /// Builds an update clause
\r
110 /// <param name="table"></param>
\r
111 /// <param name="inputColumns">Columns to be inserted</param>
\r
112 /// <param name="inputValues">Values to be inserted into columns</param>
\r
113 /// <param name="outputParameters">Expected output parameters</param>
\r
114 /// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
\r
115 /// <param name="inputPKColumns">PK columns for reference</param>
\r
116 /// <param name="inputPKValues">PK values for reference</param>
\r
117 /// <returns></returns>
\r
118 public SqlStatement GetUpdate(SqlStatement table, IList<SqlStatement> inputColumns,
\r
119 IList<SqlStatement> inputValues,
\r
120 IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions,
\r
121 IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
\r
123 if (inputColumns.Count == 0)
\r
124 return SqlStatement.Empty;
\r
126 var updateBuilder = new SqlStatementBuilder("UPDATE ");
\r
127 updateBuilder.Append(table);
\r
128 updateBuilder.Append(" SET ");
\r
129 bool valueSet = false;
\r
130 for (IEnumerator<SqlStatement> column = inputColumns.GetEnumerator(), value = inputValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
\r
133 updateBuilder.Append(", ");
\r
134 updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
\r
137 updateBuilder.Append(" WHERE ");
\r
139 for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
\r
142 updateBuilder.Append(" AND ");
\r
143 updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
\r
146 return updateBuilder.ToSqlStatement();
\r
150 /// Builds a delete clause
\r
152 /// <param name="table"></param>
\r
153 /// <param name="inputPKColumns">PK columns for reference</param>
\r
154 /// <param name="inputPKValues">PK values for reference</param>
\r
155 /// <returns></returns>
\r
156 public SqlStatement GetDelete(SqlStatement table, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
\r
158 if (inputPKColumns.Count == 0)
\r
159 return SqlStatement.Empty;
\r
161 var deleteBuilder = new SqlStatementBuilder("DELETE FROM ");
\r
162 deleteBuilder.Append(table);
\r
163 deleteBuilder.Append(" WHERE ");
\r
164 bool valueSet = false;
\r
165 for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
\r
168 deleteBuilder.Append(" AND ");
\r
169 deleteBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
\r
172 return deleteBuilder.ToSqlStatement();
\r
176 /// Gets the new line string.
\r
178 /// <value>The new line.</value>
\r
179 public string NewLine
\r
181 get { return Environment.NewLine; }
\r
184 /// Converts a constant value to a literal representation
\r
186 /// <param name="literal"></param>
\r
187 /// <returns></returns>
\r
188 public virtual SqlStatement GetLiteral(object literal)
\r
190 if (literal == null)
\r
191 return GetNullLiteral();
\r
192 if (literal is string)
\r
193 return GetLiteral((string)literal);
\r
194 if (literal is char)
\r
195 return GetLiteral(literal.ToString());
\r
196 if (literal is bool)
\r
197 return GetLiteral((bool)literal);
\r
198 if (literal is DateTime)
\r
199 return GetLiteral((DateTime)literal);
\r
200 if (literal.GetType().IsArray)
\r
201 return GetLiteral((Array)literal);
\r
202 return Convert.ToString(literal, CultureInfo.InvariantCulture);
\r
205 public virtual SqlStatement GetLiteral(DateTime literal)
\r
207 return literal.ToString("o");
\r
210 public virtual SqlStatement GetLiteral(bool literal)
\r
212 return Convert.ToString(literal, CultureInfo.InvariantCulture);
\r
216 /// Converts a standard operator to an expression
\r
218 /// <param name="operationType"></param>
\r
219 /// <param name="p"></param>
\r
220 /// <returns></returns>
\r
221 public virtual SqlStatement GetLiteral(ExpressionType operationType, IList<SqlStatement> p)
\r
223 switch (operationType)
\r
225 case ExpressionType.Add:
\r
226 return GetLiteralAdd(p[0], p[1]);
\r
227 case ExpressionType.AddChecked:
\r
228 return GetLiteralAddChecked(p[0], p[1]);
\r
229 case ExpressionType.And:
\r
230 return GetLiteralAnd(p[0], p[1]);
\r
231 case ExpressionType.AndAlso:
\r
232 return GetLiteralAndAlso(p[0], p[1]);
\r
233 case ExpressionType.ArrayLength:
\r
234 return GetLiteralArrayLength(p[0], p[1]);
\r
235 case ExpressionType.ArrayIndex:
\r
236 return GetLiteralArrayIndex(p[0], p[1]);
\r
237 case ExpressionType.Call:
\r
238 return GetLiteralCall(p[0]);
\r
239 case ExpressionType.Coalesce:
\r
240 return GetLiteralCoalesce(p[0], p[1]);
\r
241 case ExpressionType.Conditional:
\r
242 return GetLiteralConditional(p[0], p[1], p[2]);
\r
243 //case ExpressionType.Constant:
\r
245 case ExpressionType.Divide:
\r
246 return GetLiteralDivide(p[0], p[1]);
\r
247 case ExpressionType.Equal:
\r
248 return GetLiteralEqual(p[0], p[1]);
\r
249 case ExpressionType.ExclusiveOr:
\r
250 return GetLiteralExclusiveOr(p[0], p[1]);
\r
251 case ExpressionType.GreaterThan:
\r
252 return GetLiteralGreaterThan(p[0], p[1]);
\r
253 case ExpressionType.GreaterThanOrEqual:
\r
254 return GetLiteralGreaterThanOrEqual(p[0], p[1]);
\r
255 //case ExpressionType.Invoke:
\r
257 //case ExpressionType.Lambda:
\r
259 case ExpressionType.LeftShift:
\r
260 return GetLiteralLeftShift(p[0], p[1]);
\r
261 case ExpressionType.LessThan:
\r
262 return GetLiteralLessThan(p[0], p[1]);
\r
263 case ExpressionType.LessThanOrEqual:
\r
264 return GetLiteralLessThanOrEqual(p[0], p[1]);
\r
265 //case ExpressionType.ListInit:
\r
267 //case ExpressionType.MemberAccess:
\r
269 //case ExpressionType.MemberInit:
\r
271 case ExpressionType.Modulo:
\r
272 return GetLiteralModulo(p[0], p[1]);
\r
273 case ExpressionType.Multiply:
\r
274 return GetLiteralMultiply(p[0], p[1]);
\r
275 case ExpressionType.MultiplyChecked:
\r
276 return GetLiteralMultiplyChecked(p[0], p[1]);
\r
277 case ExpressionType.Negate:
\r
278 return GetLiteralNegate(p[0]);
\r
279 case ExpressionType.UnaryPlus:
\r
280 return GetLiteralUnaryPlus(p[0]);
\r
281 case ExpressionType.NegateChecked:
\r
282 return GetLiteralNegateChecked(p[0]);
\r
283 //case ExpressionType.New:
\r
285 //case ExpressionType.NewArrayInit:
\r
287 //case ExpressionType.NewArrayBounds:
\r
289 case ExpressionType.Not:
\r
290 return GetLiteralNot(p[0]);
\r
291 case ExpressionType.NotEqual:
\r
292 return GetLiteralNotEqual(p[0], p[1]);
\r
293 case ExpressionType.Or:
\r
294 return GetLiteralOr(p[0], p[1]);
\r
295 case ExpressionType.OrElse:
\r
296 return GetLiteralOrElse(p[0], p[1]);
\r
297 //case ExpressionType.Parameter:
\r
299 case ExpressionType.Power:
\r
300 return GetLiteralPower(p[0], p[1]);
\r
301 //case ExpressionType.Quote:
\r
303 case ExpressionType.RightShift:
\r
304 return GetLiteralRightShift(p[0], p[1]);
\r
305 case ExpressionType.Subtract:
\r
306 return GetLiteralSubtract(p[0], p[1]);
\r
307 case ExpressionType.SubtractChecked:
\r
308 return GetLiteralSubtractChecked(p[0], p[1]);
\r
309 //case ExpressionType.TypeAs:
\r
311 //case ExpressionType.TypeIs:
\r
314 throw new ArgumentException(operationType.ToString());
\r
318 /// Converts a special expression type to literal
\r
320 /// <param name="operationType"></param>
\r
321 /// <param name="p"></param>
\r
322 /// <returns></returns>
\r
323 public virtual SqlStatement GetLiteral(SpecialExpressionType operationType, IList<SqlStatement> p)
\r
325 switch (operationType) // SETuse
\r
327 case SpecialExpressionType.IsNull:
\r
328 return GetLiteralIsNull(p[0]);
\r
329 case SpecialExpressionType.IsNotNull:
\r
330 return GetLiteralIsNotNull(p[0]);
\r
331 case SpecialExpressionType.Concat:
\r
332 return GetLiteralStringConcat(p[0], p[1]);
\r
333 case SpecialExpressionType.Count:
\r
334 return GetLiteralCount(p[0]);
\r
335 case SpecialExpressionType.Exists:
\r
336 return GetLiteralExists(p[0]);
\r
337 case SpecialExpressionType.Like:
\r
338 return GetLiteralLike(p[0], p[1]);
\r
339 case SpecialExpressionType.Min:
\r
340 return GetLiteralMin(p[0]);
\r
341 case SpecialExpressionType.Max:
\r
342 return GetLiteralMax(p[0]);
\r
343 case SpecialExpressionType.Sum:
\r
344 return GetLiteralSum(p[0]);
\r
345 case SpecialExpressionType.Average:
\r
346 return GetLiteralAverage(p[0]);
\r
347 case SpecialExpressionType.StringLength:
\r
348 return GetLiteralStringLength(p[0]);
\r
349 case SpecialExpressionType.ToUpper:
\r
350 return GetLiteralStringToUpper(p[0]);
\r
351 case SpecialExpressionType.ToLower:
\r
352 return GetLiteralStringToLower(p[0]);
\r
353 case SpecialExpressionType.In:
\r
354 return GetLiteralIn(p[0], p[1]);
\r
355 case SpecialExpressionType.Substring:
\r
357 return GetLiteralSubString(p[0], p[1], p[2]);
\r
358 return GetLiteralSubString(p[0], p[1]);
\r
359 case SpecialExpressionType.Trim:
\r
360 case SpecialExpressionType.LTrim:
\r
361 case SpecialExpressionType.RTrim:
\r
362 return GetLiteralTrim(p[0]);
\r
363 case SpecialExpressionType.StringInsert:
\r
364 return GetLiteralStringInsert(p[0], p[1], p[2]);
\r
365 case SpecialExpressionType.Replace:
\r
366 return GetLiteralStringReplace(p[0], p[1], p[2]);
\r
367 case SpecialExpressionType.Remove:
\r
369 return GetLiteralStringRemove(p[0], p[1], p[2]);
\r
370 return GetLiteralStringRemove(p[0], p[1]);
\r
371 case SpecialExpressionType.IndexOf:
\r
373 return GetLiteralStringIndexOf(p[0], p[1]);
\r
374 else if (p.Count == 3)
\r
375 return GetLiteralStringIndexOf(p[0], p[1], p[2]);
\r
376 else if (p.Count == 4)
\r
377 return GetLiteralStringIndexOf(p[0], p[1], p[2], p[3]);
\r
379 case SpecialExpressionType.Year:
\r
380 case SpecialExpressionType.Month:
\r
381 case SpecialExpressionType.Day:
\r
382 case SpecialExpressionType.Hour:
\r
383 case SpecialExpressionType.Minute:
\r
384 case SpecialExpressionType.Second:
\r
385 case SpecialExpressionType.Millisecond:
\r
386 return GetLiteralDateTimePart(p[0], operationType);
\r
387 case SpecialExpressionType.Date:
\r
389 case SpecialExpressionType.DateDiffInMilliseconds:
\r
390 return GetLiteralDateDiff(p[0], p[1]);
\r
391 case SpecialExpressionType.Abs:
\r
392 return GetLiteralMathAbs(p[0]);
\r
393 case SpecialExpressionType.Exp:
\r
394 return GetLiteralMathExp(p[0]);
\r
395 case SpecialExpressionType.Floor:
\r
396 return GetLiteralMathFloor(p[0]);
\r
397 case SpecialExpressionType.Ln:
\r
398 return GetLiteralMathLn(p[0]);
\r
400 case SpecialExpressionType.Log:
\r
402 return GetLiteralMathLog(p[0]);
\r
404 return GetLiteralMathLog(p[0], p[1]);
\r
405 case SpecialExpressionType.Pow:
\r
406 return GetLiteralMathPow(p[0], p[1]);
\r
407 case SpecialExpressionType.Round:
\r
408 return GetLiteralMathRound(p[0]);
\r
409 case SpecialExpressionType.Sign:
\r
410 return GetLiteralMathSign(p[0]);
\r
411 case SpecialExpressionType.Sqrt:
\r
412 return GetLiteralMathSqrt(p[0]);
\r
415 throw new ArgumentException(operationType.ToString());
\r
418 protected virtual SqlStatement GetLiteralExists(SqlStatement sqlStatement)
\r
420 return SqlStatement.Format("EXISTS {0}", sqlStatement);
\r
423 private int SpecificVendorStringIndexStart
\r
427 if (this.StringIndexStartsAtOne)
\r
433 /// Gets the literal math SQRT.
\r
435 /// <param name="p">The p.</param>
\r
436 /// <returns></returns>
\r
437 protected virtual SqlStatement GetLiteralMathSqrt(SqlStatement p)
\r
439 return SqlStatement.Format("SQRT({0})", p);
\r
443 /// Gets the literal math sign.
\r
445 /// <param name="p">The p.</param>
\r
446 /// <returns></returns>
\r
447 protected virtual SqlStatement GetLiteralMathSign(SqlStatement p)
\r
449 return SqlStatement.Format("SIGN({0})", p);
\r
453 /// Gets the literal math round.
\r
455 /// <param name="p">The p.</param>
\r
456 /// <returns></returns>
\r
457 protected virtual SqlStatement GetLiteralMathRound(SqlStatement p)
\r
459 return SqlStatement.Format("ROUND({0})", p);
\r
463 /// Gets the literal math pow.
\r
465 /// <param name="p">The p.</param>
\r
466 /// <param name="p_2">The P_2.</param>
\r
467 /// <returns></returns>
\r
468 protected virtual SqlStatement GetLiteralMathPow(SqlStatement p, SqlStatement p_2)
\r
470 return SqlStatement.Format("POW({0},{1})", p, p_2);
\r
474 /// Gets the literal math log.
\r
476 /// <param name="p">The p.</param>
\r
477 /// <returns></returns>
\r
478 protected virtual SqlStatement GetLiteralMathLog(SqlStatement p)
\r
480 return SqlStatement.Format("LOG({0})", p);
\r
484 /// Gets the literal math log.
\r
486 /// <param name="p">The p.</param>
\r
487 /// <param name="p_2">The P_2.</param>
\r
488 /// <returns></returns>
\r
489 protected virtual SqlStatement GetLiteralMathLog(SqlStatement p, SqlStatement p_2)
\r
491 return SqlStatement.Format("LOG({0},{1})", p, p_2);
\r
495 /// Gets the literal math ln.
\r
497 /// <param name="p">The p.</param>
\r
498 /// <returns></returns>
\r
499 protected virtual SqlStatement GetLiteralMathLn(SqlStatement p)
\r
501 return SqlStatement.Format("LN({0})", p);
\r
505 /// Gets the literal math floor.
\r
507 /// <param name="p">The p.</param>
\r
508 /// <returns></returns>
\r
509 protected virtual SqlStatement GetLiteralMathFloor(SqlStatement p)
\r
511 return SqlStatement.Format("FLOOR({0})", p);
\r
515 /// Gets the literal math exp.
\r
517 /// <param name="p">The p.</param>
\r
518 /// <returns></returns>
\r
519 protected virtual SqlStatement GetLiteralMathExp(SqlStatement p)
\r
521 return SqlStatement.Format("EXP({0})", p);
\r
525 /// Gets the literal math abs.
\r
527 /// <param name="p">The p.</param>
\r
528 /// <returns></returns>
\r
529 protected virtual SqlStatement GetLiteralMathAbs(SqlStatement p)
\r
531 return SqlStatement.Format("ABS({0})", p);
\r
535 /// It should return a int with de difference in milliseconds between two dates.
\r
536 /// It is used in a lot of tasks, ie: operations of timespams ej: timespam.Minutes or timespam.TotalMinutes
\r
539 /// In the implementation you should pay atention in overflows inside the database engine, since a difference of dates in milliseconds
\r
540 /// maybe deliver a very big integer int. Ie: sqlServer provider has to do some tricks with castings for implementing such requeriments.
\r
542 /// <param name="dateA"></param>
\r
543 /// <param name="dateB"></param>
\r
544 /// <returns></returns>
\r
545 protected virtual SqlStatement GetLiteralDateDiff(SqlStatement dateA, SqlStatement dateB)
\r
547 return SqlStatement.Format("DATEDIFF(MILLISECOND,{0},{1})", dateA, dateB);
\r
552 /// Gets the literal date time part.
\r
554 /// <param name="dateExpression">The date expression.</param>
\r
555 /// <param name="operationType">Type of the operation.</param>
\r
556 /// <returns></returns>
\r
557 protected virtual SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SpecialExpressionType operationType)
\r
559 return SqlStatement.Format("EXTRACT({0} FROM {1})", operationType.ToString().ToUpper(), dateExpression);
\r
564 /// Gets the literal string index of.
\r
566 /// <param name="baseString">The base string.</param>
\r
567 /// <param name="searchString">The search string.</param>
\r
568 /// <param name="startIndex">The start index.</param>
\r
569 /// <param name="count">The count.</param>
\r
570 /// <returns></returns>
\r
571 protected virtual SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString, SqlStatement startIndex, SqlStatement count)
\r
573 //trim left the string
\r
574 var substring = GetLiteralSubString(baseString, startIndex, count);
\r
576 var substringIndexOf = SqlStatement.Format("STRPOS({0},{1})", substring, searchString).ToString();
\r
577 // TODO: the start index MUST be handled above at code generation
\r
578 var indexOf = GetLiteralAdd(substringIndexOf, startIndex);
\r
584 /// This function should return the first index of the string 'searchString' in a string 'baseString' but starting in 'the startIndex' index . This can be a problem since most of database
\r
585 /// engines doesn't have such overload of SUBSTR, the base implementation do it in a pretty complex with the goal of be most generic syntax as possible using a set of primitives(SUBSTRING(X,X,X) and STRPOS(X,X),+ , *).
\r
586 /// This function is usually used in others methods of this sqlprovider.
\r
589 /// In the impleementation you should pay atention that in some database engines the indexes of arrays or strings are shifted one unit.
\r
590 /// ie: in .NET stringExpression.Substring(2,2) should be translated as SUBSTRING (stringExpression, 3 , 2) since the first element in sqlserver in a SqlStatement has index=1
\r
591 protected virtual SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString, SqlStatement startIndex)
\r
593 var substring = GetLiteralSubString(baseString, startIndex);
\r
595 var substringIndexOf = SqlStatement.Format("STRPOS({0},{1})", substring, searchString);
\r
597 return GetLiteralMultiply(GetLiteralAdd(substringIndexOf, startIndex), substringIndexOf);
\r
601 /// Gets the literal string index of.
\r
603 /// <param name="baseString">The base string.</param>
\r
604 /// <param name="searchString">The search string.</param>
\r
605 /// <returns></returns>
\r
606 protected virtual SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString)
\r
608 return SqlStatement.Format("STRPOS({0},{1})", baseString, searchString);
\r
612 /// Gets the literal string remove.
\r
614 /// <param name="baseString">The base string.</param>
\r
615 /// <param name="startIndex">The start index.</param>
\r
616 /// <param name="count">The count.</param>
\r
617 /// <returns></returns>
\r
618 protected virtual SqlStatement GetLiteralStringRemove(SqlStatement baseString, SqlStatement startIndex, SqlStatement count)
\r
620 return GetLiteralStringConcat(
\r
621 GetLiteralSubString(baseString, SqlStatement.Format(SpecificVendorStringIndexStart.ToString()), startIndex),
\r
622 GetLiteralSubString(baseString, GetLiteralAdd(startIndex, count).ToString(), GetLiteralStringLength(baseString)));
\r
626 /// Gets the literal string remove.
\r
628 /// <param name="baseString">The base string.</param>
\r
629 /// <param name="startIndex">The start index.</param>
\r
630 /// <returns></returns>
\r
631 protected virtual SqlStatement GetLiteralStringRemove(SqlStatement baseString, SqlStatement startIndex)
\r
633 return GetLiteralSubString(baseString, "1", startIndex);
\r
637 /// Gets the literal string replace.
\r
639 /// <param name="stringExpresision">The string expresision.</param>
\r
640 /// <param name="searchString">The search string.</param>
\r
641 /// <param name="replacementstring">The replacementstring.</param>
\r
642 /// <returns></returns>
\r
643 protected SqlStatement GetLiteralStringReplace(SqlStatement stringExpresision, SqlStatement searchString, SqlStatement replacementstring)
\r
645 return SqlStatement.Format("REPLACE({0},{1},{2})", stringExpresision, searchString, replacementstring);
\r
649 /// Gets the literal string insert.
\r
651 /// <param name="stringExpression">The string expression.</param>
\r
652 /// <param name="position">The position.</param>
\r
653 /// <param name="insertString">The insert string.</param>
\r
654 /// <returns></returns>
\r
655 protected virtual SqlStatement GetLiteralStringInsert(SqlStatement stringExpression, SqlStatement position, SqlStatement insertString)
\r
658 return this.GetLiteralStringConcat(
\r
659 this.GetLiteralStringConcat(
\r
660 GetLiteralSubString(stringExpression, "1", position),
\r
662 this.GetLiteralSubString(stringExpression, GetLiteralAdd(position, "1")));
\r
667 /// Returns an operation between two SELECT clauses (UNION, UNION ALL, etc.)
\r
669 /// <param name="selectOperator"></param>
\r
670 /// <param name="selectA"></param>
\r
671 /// <param name="selectB"></param>
\r
672 /// <returns></returns>
\r
673 public virtual SqlStatement GetLiteral(SelectOperatorType selectOperator, SqlStatement selectA, SqlStatement selectB)
\r
675 switch (selectOperator)
\r
677 case SelectOperatorType.Union:
\r
678 return GetLiteralUnion(selectA, selectB);
\r
679 case SelectOperatorType.UnionAll:
\r
680 return GetLiteralUnionAll(selectA, selectB);
\r
681 case SelectOperatorType.Intersection:
\r
682 return GetLiteralIntersect(selectA, selectB);
\r
683 case SelectOperatorType.Exception:
\r
684 return GetLiteralExcept(selectA, selectB);
\r
686 throw new ArgumentOutOfRangeException(selectOperator.ToString());
\r
691 /// Places the expression into parenthesis
\r
693 /// <param name="a"></param>
\r
694 /// <returns></returns>
\r
695 public virtual SqlStatement GetParenthesis(SqlStatement a)
\r
697 return SqlStatement.Format("({0})", a);
\r
701 /// Returns a column related to a table.
\r
702 /// Ensures about the right case
\r
704 /// <param name="table"></param>
\r
705 /// <param name="column"></param>
\r
706 /// <returns></returns>
\r
707 public virtual string GetColumn(string table, string column)
\r
709 return string.Format("{0}.{1}", table, GetColumn(column));
\r
713 /// Returns a column related to a table.
\r
714 /// Ensures about the right case
\r
716 /// <param name="column"></param>
\r
717 /// <returns></returns>
\r
718 public string GetColumn(string column)
\r
720 return GetSafeNamePart(column);
\r
724 /// Returns a table alias
\r
725 /// Ensures about the right case
\r
727 /// <param name="table"></param>
\r
728 /// <param name="alias"></param>
\r
729 /// <returns></returns>
\r
730 public virtual string GetTableAsAlias(string table, string alias)
\r
732 return string.Format("{0} {1}", GetTable(table), GetTableAlias(alias));
\r
736 /// Returns a table alias
\r
737 /// Ensures about the right case
\r
739 /// <param name="table"></param>
\r
740 /// <param name="alias"></param>
\r
741 /// <returns></returns>
\r
742 public virtual string GetSubQueryAsAlias(string subquery, string alias)
\r
744 return string.Format("({0}) {1}", subquery, GetTableAlias(alias));
\r
748 /// Returns a table alias
\r
750 /// <param name="table"></param>
\r
751 /// <returns></returns>
\r
752 public virtual string GetTable(string table)
\r
754 // we use the full version, since the table name may include the schema
\r
755 return GetSafeName(table);
\r
759 /// Joins a list of table selection to make a FROM clause
\r
761 /// <param name="tables"></param>
\r
762 /// <returns></returns>
\r
763 public virtual SqlStatement GetFromClause(SqlStatement[] tables)
\r
765 if (tables.Length == 0)
\r
766 return SqlStatement.Empty;
\r
767 return SqlStatement.Format("FROM {0}", SqlStatement.Join(", ", tables));
\r
771 /// Concatenates all join clauses
\r
773 /// <param name="joins"></param>
\r
774 /// <returns></returns>
\r
775 public virtual SqlStatement GetJoinClauses(SqlStatement[] joins)
\r
777 if (joins.Length == 0)
\r
778 return SqlStatement.Empty;
\r
780 return space + SqlStatement.Join(NewLine + space, joins);
\r
784 /// Returns an INNER JOIN syntax
\r
786 /// <param name="joinedTable"></param>
\r
787 /// <param name="joinExpression"></param>
\r
788 /// <returns></returns>
\r
789 public virtual SqlStatement GetInnerJoinClause(SqlStatement joinedTable, SqlStatement joinExpression)
\r
791 return SqlStatement.Format("INNER JOIN {0} ON {1}", joinedTable, joinExpression);
\r
795 /// Returns a LEFT JOIN syntax
\r
797 /// <param name="joinedTable"></param>
\r
798 /// <param name="joinExpression"></param>
\r
799 /// <returns></returns>
\r
800 public virtual SqlStatement GetLeftOuterJoinClause(SqlStatement joinedTable, SqlStatement joinExpression)
\r
802 return SqlStatement.Format("LEFT JOIN {0} ON {1}", joinedTable, joinExpression);
\r
806 /// Returns a RIGHT JOIN syntax
\r
808 /// <param name="joinedTable"></param>
\r
809 /// <param name="joinExpression"></param>
\r
810 /// <returns></returns>
\r
811 public virtual SqlStatement GetRightOuterJoinClause(SqlStatement joinedTable, SqlStatement joinExpression)
\r
813 return SqlStatement.Format("RIGHT JOIN {0} ON {1}", joinedTable, joinExpression);
\r
817 /// Joins a list of conditions to make a WHERE clause
\r
819 /// <param name="wheres"></param>
\r
820 /// <returns></returns>
\r
821 public virtual SqlStatement GetWhereClause(SqlStatement[] wheres)
\r
823 if (wheres.Length == 0)
\r
824 return SqlStatement.Empty;
\r
825 return SqlStatement.Format("WHERE ({0})", SqlStatement.Join(") AND (", wheres));
\r
829 /// Joins a list of conditions to make a HAVING clause
\r
831 /// <param name="havings"></param>
\r
832 /// <returns></returns>
\r
833 public virtual SqlStatement GetHavingClause(SqlStatement[] havings)
\r
835 if (havings.Length == 0)
\r
836 return SqlStatement.Empty;
\r
837 return SqlStatement.Format("HAVING {0}", SqlStatement.Join(" AND ", havings));
\r
841 /// Joins a list of operands to make a SELECT clause
\r
843 /// <param name="selects"></param>
\r
844 /// <returns></returns>
\r
845 public virtual SqlStatement GetSelectClause(SqlStatement[] selects)
\r
847 if (selects.Length == 0)
\r
848 return SqlStatement.Empty;
\r
849 return SqlStatement.Format("SELECT {0}", SqlStatement.Join(", ", selects));
\r
853 /// Joins a list of operands to make a SELECT clause
\r
855 /// <param name="selects"></param>
\r
856 /// <returns></returns>
\r
857 public virtual SqlStatement GetSelectDistinctClause(SqlStatement[] selects)
\r
859 if (selects.Length == 0)
\r
860 return SqlStatement.Empty;
\r
861 return SqlStatement.Format("SELECT DISTINCT {0}", SqlStatement.Join(", ", selects));
\r
865 /// Returns all table columns (*)
\r
867 /// <returns></returns>
\r
868 public virtual string GetColumns()
\r
874 /// Returns a literal parameter name
\r
876 /// <returns></returns>
\r
877 public virtual string GetParameterName(string nameBase)
\r
879 return string.Format(":{0}", nameBase);
\r
883 /// Returns a valid alias syntax for the given table
\r
885 /// <param name="nameBase"></param>
\r
886 /// <returns></returns>
\r
887 public virtual string GetTableAlias(string nameBase)
\r
889 return string.Format("{0}$", nameBase);
\r
893 /// Gets the literal add.
\r
895 /// <param name="a">A.</param>
\r
896 /// <param name="b">The b.</param>
\r
897 /// <returns></returns>
\r
898 protected virtual SqlStatement GetLiteralAdd(SqlStatement a, SqlStatement b)
\r
900 return SqlStatement.Format("{0} + {1}", a, b);
\r
904 /// Gets the literal add checked.
\r
906 /// <param name="a">A.</param>
\r
907 /// <param name="b">The b.</param>
\r
908 /// <returns></returns>
\r
909 protected virtual SqlStatement GetLiteralAddChecked(SqlStatement a, SqlStatement b)
\r
911 return GetLiteralAdd(a, b);
\r
915 /// Gets the literal and.
\r
917 /// <param name="a">A.</param>
\r
918 /// <param name="b">The b.</param>
\r
919 /// <returns></returns>
\r
920 protected virtual SqlStatement GetLiteralAnd(SqlStatement a, SqlStatement b)
\r
922 return SqlStatement.Format("({0}) AND ({1})", a, b);
\r
926 /// Gets the literal and also.
\r
928 /// <param name="a">A.</param>
\r
929 /// <param name="b">The b.</param>
\r
930 /// <returns></returns>
\r
931 protected virtual SqlStatement GetLiteralAndAlso(SqlStatement a, SqlStatement b)
\r
933 return GetLiteralAnd(a, b);
\r
937 /// Gets the length of the literal array.
\r
939 /// <param name="a">A.</param>
\r
940 /// <param name="b">The b.</param>
\r
941 /// <returns></returns>
\r
942 protected virtual SqlStatement GetLiteralArrayLength(SqlStatement a, SqlStatement b)
\r
944 throw new NotImplementedException();
\r
948 /// Gets the index of the literal array.
\r
950 /// <param name="a">A.</param>
\r
951 /// <param name="b">The b.</param>
\r
952 /// <returns></returns>
\r
953 protected virtual SqlStatement GetLiteralArrayIndex(SqlStatement a, SqlStatement b)
\r
955 throw new NotImplementedException();
\r
959 /// Gets the literal call.
\r
961 /// <param name="a">A.</param>
\r
962 /// <returns></returns>
\r
963 protected virtual SqlStatement GetLiteralCall(SqlStatement a)
\r
965 throw new NotImplementedException();
\r
969 /// Gets the literal coalesce.
\r
971 /// <param name="a">A.</param>
\r
972 /// <param name="b">The b.</param>
\r
973 /// <returns></returns>
\r
974 protected virtual SqlStatement GetLiteralCoalesce(SqlStatement a, SqlStatement b)
\r
976 return SqlStatement.Format("COALESCE({0}, {1})", a, b);
\r
980 /// Gets the literal conditional.
\r
982 /// <param name="a">A.</param>
\r
983 /// <param name="b">The b.</param>
\r
984 /// <param name="c">The c.</param>
\r
985 /// <returns></returns>
\r
986 protected virtual SqlStatement GetLiteralConditional(SqlStatement a, SqlStatement b, SqlStatement c)
\r
988 throw new NotImplementedException();
\r
992 /// Gets the literal convert.
\r
994 /// <param name="a">A.</param>
\r
995 /// <param name="newType">The new type.</param>
\r
996 /// <returns></returns>
\r
997 public virtual SqlStatement GetLiteralConvert(SqlStatement a, Type newType)
\r
1003 /// Gets the literal divide.
\r
1005 /// <param name="a">A.</param>
\r
1006 /// <param name="b">The b.</param>
\r
1007 /// <returns></returns>
\r
1008 protected virtual SqlStatement GetLiteralDivide(SqlStatement a, SqlStatement b)
\r
1010 return SqlStatement.Format("{0} / {1}", a, b);
\r
1014 /// Gets the literal equal.
\r
1016 /// <param name="a">A.</param>
\r
1017 /// <param name="b">The b.</param>
\r
1018 /// <returns></returns>
\r
1019 protected virtual SqlStatement GetLiteralEqual(SqlStatement a, SqlStatement b)
\r
1021 return SqlStatement.Format("{0} = {1}", a, b);
\r
1025 /// Gets the literal exclusive or.
\r
1027 /// <param name="a">A.</param>
\r
1028 /// <param name="b">The b.</param>
\r
1029 /// <returns></returns>
\r
1030 protected virtual SqlStatement GetLiteralExclusiveOr(SqlStatement a, SqlStatement b)
\r
1032 return SqlStatement.Format("({0}) XOR ({1})", a, b);
\r
1036 /// Gets the literal greater than.
\r
1038 /// <param name="a">A.</param>
\r
1039 /// <param name="b">The b.</param>
\r
1040 /// <returns></returns>
\r
1041 protected virtual SqlStatement GetLiteralGreaterThan(SqlStatement a, SqlStatement b)
\r
1043 return SqlStatement.Format("{0} > {1}", a, b);
\r
1047 /// Gets the literal greater than or equal.
\r
1049 /// <param name="a">A.</param>
\r
1050 /// <param name="b">The b.</param>
\r
1051 /// <returns></returns>
\r
1052 protected virtual SqlStatement GetLiteralGreaterThanOrEqual(SqlStatement a, SqlStatement b)
\r
1054 return SqlStatement.Format("{0} >= {1}", a, b);
\r
1058 /// Gets the literal left shift.
\r
1060 /// <param name="a">A.</param>
\r
1061 /// <param name="b">The b.</param>
\r
1062 /// <returns></returns>
\r
1063 protected virtual SqlStatement GetLiteralLeftShift(SqlStatement a, SqlStatement b)
\r
1065 throw new NotImplementedException();
\r
1069 /// Gets the literal less than.
\r
1071 /// <param name="a">A.</param>
\r
1072 /// <param name="b">The b.</param>
\r
1073 /// <returns></returns>
\r
1074 protected virtual SqlStatement GetLiteralLessThan(SqlStatement a, SqlStatement b)
\r
1076 return SqlStatement.Format("{0} < {1}", a, b);
\r
1080 /// Gets the literal less than or equal.
\r
1082 /// <param name="a">A.</param>
\r
1083 /// <param name="b">The b.</param>
\r
1084 /// <returns></returns>
\r
1085 protected virtual SqlStatement GetLiteralLessThanOrEqual(SqlStatement a, SqlStatement b)
\r
1087 return SqlStatement.Format("{0} <= {1}", a, b);
\r
1091 /// Gets the literal modulo.
\r
1093 /// <param name="a">A.</param>
\r
1094 /// <param name="b">The b.</param>
\r
1095 /// <returns></returns>
\r
1096 protected virtual SqlStatement GetLiteralModulo(SqlStatement a, SqlStatement b)
\r
1098 return SqlStatement.Format("{0} % {1}", a, b);
\r
1102 /// Gets the literal multiply.
\r
1104 /// <param name="a">A.</param>
\r
1105 /// <param name="b">The b.</param>
\r
1106 /// <returns></returns>
\r
1107 protected virtual SqlStatement GetLiteralMultiply(SqlStatement a, SqlStatement b)
\r
1109 return SqlStatement.Format("{0} * {1}", a, b);
\r
1113 /// Gets the literal multiply checked.
\r
1115 /// <param name="a">A.</param>
\r
1116 /// <param name="b">The b.</param>
\r
1117 /// <returns></returns>
\r
1118 protected virtual SqlStatement GetLiteralMultiplyChecked(SqlStatement a, SqlStatement b)
\r
1120 return GetLiteralMultiply(a, b);
\r
1124 /// Gets the literal negate.
\r
1126 /// <param name="a">A.</param>
\r
1127 /// <returns></returns>
\r
1128 protected virtual SqlStatement GetLiteralNegate(SqlStatement a)
\r
1130 return SqlStatement.Format("-{0}", a);
\r
1134 /// Gets the literal unary plus.
\r
1136 /// <param name="a">A.</param>
\r
1137 /// <returns></returns>
\r
1138 protected virtual SqlStatement GetLiteralUnaryPlus(SqlStatement a)
\r
1140 return SqlStatement.Format("+{0}", a);
\r
1144 /// Gets the literal negate checked.
\r
1146 /// <param name="a">A.</param>
\r
1147 /// <returns></returns>
\r
1148 protected virtual SqlStatement GetLiteralNegateChecked(SqlStatement a)
\r
1150 return GetLiteralNegate(a);
\r
1154 /// Gets the literal not.
\r
1156 /// <param name="a">A.</param>
\r
1157 /// <returns></returns>
\r
1158 protected virtual SqlStatement GetLiteralNot(SqlStatement a)
\r
1160 return SqlStatement.Format("NOT {0}", a);
\r
1164 /// Gets the literal not equal.
\r
1166 /// <param name="a">A.</param>
\r
1167 /// <param name="b">The b.</param>
\r
1168 /// <returns></returns>
\r
1169 protected virtual SqlStatement GetLiteralNotEqual(SqlStatement a, SqlStatement b)
\r
1171 return SqlStatement.Format("{0} <> {1}", a, b);
\r
1175 /// Gets the literal or.
\r
1177 /// <param name="a">A.</param>
\r
1178 /// <param name="b">The b.</param>
\r
1179 /// <returns></returns>
\r
1180 protected virtual SqlStatement GetLiteralOr(SqlStatement a, SqlStatement b)
\r
1182 return SqlStatement.Format("({0}) OR ({1})", a, b);
\r
1186 /// Gets the literal or else.
\r
1188 /// <param name="a">A.</param>
\r
1189 /// <param name="b">The b.</param>
\r
1190 /// <returns></returns>
\r
1191 protected virtual SqlStatement GetLiteralOrElse(SqlStatement a, SqlStatement b)
\r
1193 return GetLiteralOr(a, b);
\r
1197 /// Gets the literal power.
\r
1199 /// <param name="a">A.</param>
\r
1200 /// <param name="b">The b.</param>
\r
1201 /// <returns></returns>
\r
1202 protected virtual SqlStatement GetLiteralPower(SqlStatement a, SqlStatement b)
\r
1204 return SqlStatement.Format("POWER ({0}, {1})", a, b);
\r
1208 /// Gets the literal right shift.
\r
1210 /// <param name="a">A.</param>
\r
1211 /// <param name="b">The b.</param>
\r
1212 /// <returns></returns>
\r
1213 protected virtual SqlStatement GetLiteralRightShift(SqlStatement a, SqlStatement b)
\r
1215 throw new NotImplementedException();
\r
1219 /// Gets the literal subtract.
\r
1221 /// <param name="a">A.</param>
\r
1222 /// <param name="b">The b.</param>
\r
1223 /// <returns></returns>
\r
1224 protected virtual SqlStatement GetLiteralSubtract(SqlStatement a, SqlStatement b)
\r
1226 return SqlStatement.Format("{0} - {1}", a, b);
\r
1230 /// Gets the literal subtract checked.
\r
1232 /// <param name="a">A.</param>
\r
1233 /// <param name="b">The b.</param>
\r
1234 /// <returns></returns>
\r
1235 protected virtual SqlStatement GetLiteralSubtractChecked(SqlStatement a, SqlStatement b)
\r
1237 return GetLiteralSubtract(a, b);
\r
1241 /// Gets the literal is null.
\r
1243 /// <param name="a">A.</param>
\r
1244 /// <returns></returns>
\r
1245 protected virtual SqlStatement GetLiteralIsNull(SqlStatement a)
\r
1247 return SqlStatement.Format("{0} IS NULL", a);
\r
1251 /// Gets the literal is not null.
\r
1253 /// <param name="a">A.</param>
\r
1254 /// <returns></returns>
\r
1255 protected virtual SqlStatement GetLiteralIsNotNull(SqlStatement a)
\r
1257 return SqlStatement.Format("{0} IS NOT NULL", a);
\r
1261 /// Gets the literal string concat.
\r
1263 /// <param name="a">A.</param>
\r
1264 /// <param name="b">The b.</param>
\r
1265 /// <returns></returns>
\r
1266 protected virtual SqlStatement GetLiteralStringConcat(SqlStatement a, SqlStatement b)
\r
1268 // for some vendors, it is "CONCAT(a,b)"
\r
1269 return SqlStatement.Format("{0} || {1}", a, b);
\r
1273 /// Gets the length of the literal string.
\r
1275 /// <param name="a">A.</param>
\r
1276 /// <returns></returns>
\r
1277 protected virtual SqlStatement GetLiteralStringLength(SqlStatement a)
\r
1279 return SqlStatement.Format("CHARACTER_LENGTH({0})", a);
\r
1283 /// Gets the literal string to upper.
\r
1285 /// <param name="a">A.</param>
\r
1286 /// <returns></returns>
\r
1287 protected virtual SqlStatement GetLiteralStringToUpper(SqlStatement a)
\r
1289 return SqlStatement.Format("UCASE({0})", a);
\r
1293 /// Gets the literal string to lower.
\r
1295 /// <param name="a">A.</param>
\r
1296 /// <returns></returns>
\r
1297 protected virtual SqlStatement GetLiteralStringToLower(SqlStatement a)
\r
1299 return SqlStatement.Format("LCASE({0})", a);
\r
1304 /// Gets the literal trim.
\r
1306 /// <param name="a">A.</param>
\r
1307 /// <returns></returns>
\r
1308 protected virtual SqlStatement GetLiteralTrim(SqlStatement a)
\r
1310 return SqlStatement.Format("TRIM({0})", a);
\r
1314 /// Gets the literal L trim.
\r
1316 /// <param name="a">A.</param>
\r
1317 /// <returns></returns>
\r
1318 protected virtual SqlStatement GetLiteralLeftTrim(SqlStatement a)
\r
1320 return SqlStatement.Format("LTRIM({0})", a);
\r
1324 /// Gets the literal R trim.
\r
1326 /// <param name="a">A.</param>
\r
1327 /// <returns></returns>
\r
1328 protected virtual SqlStatement GetLiteralRightTrim(SqlStatement a)
\r
1330 return SqlStatement.Format("RTRIM({0})", a);
\r
1334 /// Gets the literal sub string.
\r
1336 /// <param name="baseString">The base string.</param>
\r
1337 /// <param name="startIndex">The start index.</param>
\r
1338 /// <param name="count">The count.</param>
\r
1339 /// <returns></returns>
\r
1340 protected virtual SqlStatement GetLiteralSubString(SqlStatement baseString, SqlStatement startIndex, SqlStatement count)
\r
1342 //in standard sql base SqlStatement index is 1 instead 0
\r
1343 return SqlStatement.Format("SUBSTR({0}, {1}, {2})", baseString, startIndex, count);
\r
1347 /// Gets the literal sub string.
\r
1349 /// <param name="baseString">The base string.</param>
\r
1350 /// <param name="startIndex">The start index.</param>
\r
1351 /// <returns></returns>
\r
1352 protected virtual SqlStatement GetLiteralSubString(SqlStatement baseString, SqlStatement startIndex)
\r
1354 //in standard sql base SqlStatement index is 1 instead 0
\r
1355 return SqlStatement.Format("SUBSTR({0}, {1})", baseString, startIndex);
\r
1359 /// Gets the literal like.
\r
1361 /// <param name="a">A.</param>
\r
1362 /// <param name="b">The b.</param>
\r
1363 /// <returns></returns>
\r
1364 protected virtual SqlStatement GetLiteralLike(SqlStatement a, SqlStatement b)
\r
1366 return SqlStatement.Format("{0} LIKE {1}", a, b);
\r
1370 /// Gets the literal count.
\r
1372 /// <param name="a">A.</param>
\r
1373 /// <returns></returns>
\r
1374 protected virtual SqlStatement GetLiteralCount(SqlStatement a)
\r
1376 return SqlStatement.Format("COUNT({0})", a);
\r
1380 /// Gets the literal min.
\r
1382 /// <param name="a">A.</param>
\r
1383 /// <returns></returns>
\r
1384 protected virtual SqlStatement GetLiteralMin(SqlStatement a)
\r
1386 return SqlStatement.Format("MIN({0})", a);
\r
1390 /// Gets the literal max.
\r
1392 /// <param name="a">A.</param>
\r
1393 /// <returns></returns>
\r
1394 protected virtual SqlStatement GetLiteralMax(SqlStatement a)
\r
1396 return SqlStatement.Format("MAX({0})", a);
\r
1400 /// Gets the literal sum.
\r
1402 /// <param name="a">A.</param>
\r
1403 /// <returns></returns>
\r
1404 protected virtual SqlStatement GetLiteralSum(SqlStatement a)
\r
1406 return SqlStatement.Format("SUM({0})", a);
\r
1410 /// Gets the literal average.
\r
1412 /// <param name="a">A.</param>
\r
1413 /// <returns></returns>
\r
1414 protected virtual SqlStatement GetLiteralAverage(SqlStatement a)
\r
1416 return SqlStatement.Format("AVG({0})", a);
\r
1420 /// Gets the literal in.
\r
1422 /// <param name="a">A.</param>
\r
1423 /// <param name="b">The b.</param>
\r
1424 /// <returns></returns>
\r
1425 protected virtual SqlStatement GetLiteralIn(SqlStatement a, SqlStatement b)
\r
1427 return SqlStatement.Format("{0} IN {1}", a, b);
\r
1431 /// Gets the null literal.
\r
1433 /// <returns></returns>
\r
1434 protected virtual SqlStatement GetNullLiteral()
\r
1440 /// Returns a LIMIT clause around a SELECT clause
\r
1442 /// <param name="select">SELECT clause</param>
\r
1443 /// <param name="limit">limit value (number of columns to be returned)</param>
\r
1444 /// <returns></returns>
\r
1445 public virtual SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit)
\r
1447 return SqlStatement.Format("{0} LIMIT {1}", select, limit);
\r
1451 /// Returns a LIMIT clause around a SELECT clause, with offset
\r
1453 /// <param name="select">SELECT clause</param>
\r
1454 /// <param name="limit">limit value (number of columns to be returned)</param>
\r
1455 /// <param name="offset">first row to be returned (starting from 0)</param>
\r
1456 /// <param name="offsetAndLimit">limit+offset</param>
\r
1457 /// <returns></returns>
\r
1458 public virtual SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit, SqlStatement offset, SqlStatement offsetAndLimit)
\r
1460 // default SQL syntax: LIMIT limit OFFSET offset
\r
1461 return SqlStatement.Format("{0} LIMIT {1} OFFSET {2}", select, limit, offset);
\r
1465 /// Gets the literal for a given string.
\r
1467 /// <param name="str">The STR.</param>
\r
1468 /// <returns></returns>
\r
1469 protected virtual string GetLiteral(string str)
\r
1471 return string.Format("'{0}'", str.Replace("'", "''"));
\r
1475 /// Gets the literal array.
\r
1477 /// <param name="array">The array.</param>
\r
1478 /// <returns></returns>
\r
1479 protected virtual SqlStatement GetLiteral(Array array)
\r
1481 var listItems = new List<SqlStatement>();
\r
1482 foreach (object o in array)
\r
1483 listItems.Add(GetLiteral(o));
\r
1484 return SqlStatement.Format("({0})", SqlStatement.Join(", ", listItems.ToArray()));
\r
1488 /// Returns an ORDER criterium
\r
1490 /// <param name="expression"></param>
\r
1491 /// <param name="descending"></param>
\r
1492 /// <returns></returns>
\r
1493 public virtual SqlStatement GetOrderByColumn(SqlStatement expression, bool descending)
\r
1496 return expression;
\r
1497 return SqlStatement.Format("{0} DESC", expression);
\r
1501 /// Joins a list of conditions to make a ORDER BY clause
\r
1503 /// <param name="orderBy"></param>
\r
1504 /// <returns></returns>
\r
1505 public virtual SqlStatement GetOrderByClause(SqlStatement[] orderBy)
\r
1507 if (orderBy.Length == 0)
\r
1508 return SqlStatement.Empty;
\r
1509 return SqlStatement.Format("ORDER BY {0}", SqlStatement.Join(", ", orderBy));
\r
1513 /// Joins a list of conditions to make a GROUP BY clause
\r
1515 /// <param name="groupBy"></param>
\r
1516 /// <returns></returns>
\r
1517 public virtual SqlStatement GetGroupByClause(SqlStatement[] groupBy)
\r
1519 if (groupBy.Length == 0)
\r
1520 return SqlStatement.Empty;
\r
1521 return SqlStatement.Format("GROUP BY {0}", SqlStatement.Join(", ", groupBy));
\r
1525 /// Gets the literal union.
\r
1527 /// <param name="selectA">The select A.</param>
\r
1528 /// <param name="selectB">The select B.</param>
\r
1529 /// <returns></returns>
\r
1530 protected virtual SqlStatement GetLiteralUnion(SqlStatement selectA, SqlStatement selectB)
\r
1532 return SqlStatement.Format("{0}{2}UNION{2}{1}", selectA, selectB, NewLine);
\r
1536 /// Gets the literal union all.
\r
1538 /// <param name="selectA">The select A.</param>
\r
1539 /// <param name="selectB">The select B.</param>
\r
1540 /// <returns></returns>
\r
1541 protected virtual SqlStatement GetLiteralUnionAll(SqlStatement selectA, SqlStatement selectB)
\r
1543 return SqlStatement.Format("{0}{2}UNION ALL{2}{1}", selectA, selectB, NewLine);
\r
1547 /// Gets the literal intersect.
\r
1549 /// <param name="selectA">The select A.</param>
\r
1550 /// <param name="selectB">The select B.</param>
\r
1551 /// <returns></returns>
\r
1552 protected virtual SqlStatement GetLiteralIntersect(SqlStatement selectA, SqlStatement selectB)
\r
1554 return SqlStatement.Format("{0}{2}INTERSECT{2}{1}", selectA, selectB, NewLine);
\r
1558 /// Gets the literal except.
\r
1560 /// <param name="selectA">The select A.</param>
\r
1561 /// <param name="selectB">The select B.</param>
\r
1562 /// <returns></returns>
\r
1563 protected virtual SqlStatement GetLiteralExcept(SqlStatement selectA, SqlStatement selectB)
\r
1565 return SqlStatement.Format("{0}{2}EXCEPT{2}{1}", selectA, selectB, NewLine);
\r
1569 /// given 'User', return '[User]' to prevent a SQL keyword conflict
\r
1571 /// <param name="name"></param>
\r
1572 /// <returns></returns>
\r
1573 public virtual string GetSafeName(string name)
\r
1575 string[] nameParts = name.Split('.');
\r
1576 for (int index = 0; index < nameParts.Length; index++)
\r
1578 nameParts[index] = GetSafeNamePart(nameParts[index]);
\r
1580 return string.Join(".", nameParts);
\r
1584 /// Gets the safe name part.
\r
1586 /// <param name="namePart">The name part.</param>
\r
1587 /// <returns></returns>
\r
1588 protected virtual string GetSafeNamePart(string namePart)
\r
1590 return IsMadeSafe(namePart) ? namePart : MakeNameSafe(namePart);
\r
1594 /// Determines whether [is made safe] [the specified name part].
\r
1596 /// <param name="namePart">The name part.</param>
\r
1598 /// <c>true</c> if [is made safe] [the specified name part]; otherwise, <c>false</c>.
\r
1600 protected virtual bool IsMadeSafe(string namePart)
\r
1602 var l = namePart.Length;
\r
1605 return namePart[0] == SafeNameStartQuote && namePart[l - 1] == SafeNameEndQuote;
\r
1609 /// Gets the safe name start quote.
\r
1611 /// <value>The safe name start quote.</value>
\r
1612 protected virtual char SafeNameStartQuote { get { return '"'; } }
\r
1614 /// Gets the safe name end quote.
\r
1616 /// <value>The safe name end quote.</value>
\r
1617 protected virtual char SafeNameEndQuote { get { return '"'; } }
\r
1620 /// Makes the name safe.
\r
1622 /// <param name="namePart">The name part.</param>
\r
1623 /// <returns></returns>
\r
1624 protected virtual string MakeNameSafe(string namePart)
\r
1626 return namePart.Enquote(SafeNameStartQuote, SafeNameEndQuote);
\r
1629 private static readonly Regex _fieldIdentifierEx = new Regex(@"\[(?<var>[\w.]+)\]",
\r
1630 RegexOptions.Singleline |
\r
1631 RegexOptions.ExplicitCapture |
\r
1632 RegexOptions.Compiled);
\r
1634 public virtual string GetSafeQuery(string sqlString)
\r
1636 if (sqlString == null)
\r
1638 return _fieldIdentifierEx.Replace(sqlString, delegate(Match e)
\r
1640 var field = e.Groups[1].Value;
\r
1641 var safeField = GetSafeNamePart(field);
\r
1646 // TODO: remove this
\r
1647 public virtual bool StringIndexStartsAtOne
\r
1649 get { return true; }
\r