|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Text;
|
|
|
4
|
|
|
5 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
6 {
|
|
|
7 using DataProvider;
|
|
|
8 using Reflection;
|
|
|
9
|
|
|
10 public class OracleSqlProvider : BasicSqlProvider
|
|
|
11 {
|
|
|
12 public override bool IsCountSubQuerySupported { get { return false; } }
|
|
|
13 public override bool IsIdentityParameterRequired { get { return true; } }
|
|
|
14 public override int MaxInListValuesCount { get { return 1000; } }
|
|
|
15
|
|
|
16 protected override void BuildSelectClause(StringBuilder sb)
|
|
|
17 {
|
|
|
18 if (SqlQuery.From.Tables.Count == 0)
|
|
|
19 {
|
|
|
20 AppendIndent(sb).Append("SELECT").AppendLine();
|
|
|
21 BuildColumns(sb);
|
|
|
22 AppendIndent(sb).Append("FROM SYS.DUAL").AppendLine();
|
|
|
23 }
|
|
|
24 else
|
|
|
25 base.BuildSelectClause(sb);
|
|
|
26 }
|
|
|
27
|
|
|
28 protected override void BuildGetIdentity(StringBuilder sb)
|
|
|
29 {
|
|
|
30 var identityField = SqlQuery.Insert.Into.GetIdentityField();
|
|
|
31
|
|
|
32 if (identityField == null)
|
|
|
33 throw new SqlException("Identity field must be defined for '{0}'.", SqlQuery.Insert.Into.Name);
|
|
|
34
|
|
|
35 AppendIndent(sb).AppendLine("RETURNING");
|
|
|
36 AppendIndent(sb).Append("\t");
|
|
|
37 BuildExpression(sb, identityField, false, true);
|
|
|
38 sb.AppendLine(" INTO :IDENTITY_PARAMETER");
|
|
|
39 }
|
|
|
40
|
|
|
41 public override ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
|
|
|
42 {
|
|
|
43 if (table.SequenceAttributes != null)
|
|
|
44 {
|
|
|
45 var attr = GetSequenceNameAttribute(table, false);
|
|
|
46
|
|
|
47 if (attr != null)
|
|
|
48 return new SqlExpression(attr.SequenceName + ".nextval", Precedence.Primary);
|
|
|
49 }
|
|
|
50
|
|
|
51 return base.GetIdentityExpression(table, identityField, forReturning);
|
|
|
52 }
|
|
|
53
|
|
|
54 protected override bool BuildWhere()
|
|
|
55 {
|
|
|
56 return base.BuildWhere() || !NeedSkip && NeedTake && SqlQuery.OrderBy.IsEmpty && SqlQuery.Having.IsEmpty;
|
|
|
57 }
|
|
|
58
|
|
|
59 string _rowNumberAlias;
|
|
|
60
|
|
|
61 protected override ISqlProvider CreateSqlProvider()
|
|
|
62 {
|
|
|
63 return new OracleSqlProvider();
|
|
|
64 }
|
|
|
65
|
|
|
66 protected override void BuildSql(StringBuilder sb)
|
|
|
67 {
|
|
|
68 var buildRowNum = NeedSkip || NeedTake && (!SqlQuery.OrderBy.IsEmpty || !SqlQuery.Having.IsEmpty);
|
|
|
69 var aliases = null as string[];
|
|
|
70
|
|
|
71 if (buildRowNum)
|
|
|
72 {
|
|
|
73 aliases = GetTempAliases(2, "t");
|
|
|
74
|
|
|
75 if (_rowNumberAlias == null)
|
|
|
76 _rowNumberAlias = GetTempAliases(1, "rn")[0];
|
|
|
77
|
|
|
78 AppendIndent(sb).AppendFormat("SELECT {0}.*", aliases[1]).AppendLine();
|
|
|
79 AppendIndent(sb).Append("FROM"). AppendLine();
|
|
|
80 AppendIndent(sb).Append("("). AppendLine();
|
|
|
81 Indent++;
|
|
|
82
|
|
|
83 AppendIndent(sb).AppendFormat("SELECT {0}.*, ROWNUM as {1}", aliases[0], _rowNumberAlias).AppendLine();
|
|
|
84 AppendIndent(sb).Append("FROM"). AppendLine();
|
|
|
85 AppendIndent(sb).Append("("). AppendLine();
|
|
|
86 Indent++;
|
|
|
87 }
|
|
|
88
|
|
|
89 base.BuildSql(sb);
|
|
|
90
|
|
|
91 if (buildRowNum)
|
|
|
92 {
|
|
|
93 Indent--;
|
|
|
94 AppendIndent(sb).Append(") ").Append(aliases[0]).AppendLine();
|
|
|
95
|
|
|
96 if (NeedTake && NeedSkip)
|
|
|
97 {
|
|
|
98 AppendIndent(sb).AppendLine("WHERE");
|
|
|
99 AppendIndent(sb).Append("\tROWNUM <= ");
|
|
|
100 BuildExpression(sb, Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue));
|
|
|
101 sb.AppendLine();
|
|
|
102 }
|
|
|
103
|
|
|
104 Indent--;
|
|
|
105 AppendIndent(sb).Append(") ").Append(aliases[1]).AppendLine();
|
|
|
106 AppendIndent(sb).Append("WHERE").AppendLine();
|
|
|
107
|
|
|
108 Indent++;
|
|
|
109
|
|
|
110 if (NeedTake && NeedSkip)
|
|
|
111 {
|
|
|
112 AppendIndent(sb).AppendFormat("{0}.{1} > ", aliases[1], _rowNumberAlias);
|
|
|
113 BuildExpression(sb, SqlQuery.Select.SkipValue);
|
|
|
114 }
|
|
|
115 else if (NeedTake)
|
|
|
116 {
|
|
|
117 AppendIndent(sb).AppendFormat("{0}.{1} <= ", aliases[1], _rowNumberAlias);
|
|
|
118 BuildExpression(sb, Precedence.Comparison, SqlQuery.Select.TakeValue);
|
|
|
119 }
|
|
|
120 else
|
|
|
121 {
|
|
|
122 AppendIndent(sb).AppendFormat("{0}.{1} > ", aliases[1], _rowNumberAlias);
|
|
|
123 BuildExpression(sb, Precedence.Comparison, SqlQuery.Select.SkipValue);
|
|
|
124 }
|
|
|
125
|
|
|
126 sb.AppendLine();
|
|
|
127 Indent--;
|
|
|
128 }
|
|
|
129 }
|
|
|
130
|
|
|
131 protected override void BuildWhereSearchCondition(StringBuilder sb, SqlQuery.SearchCondition condition)
|
|
|
132 {
|
|
|
133 if (NeedTake && !NeedSkip && SqlQuery.OrderBy.IsEmpty && SqlQuery.Having.IsEmpty)
|
|
|
134 {
|
|
|
135 BuildPredicate(
|
|
|
136 sb,
|
|
|
137 Precedence.LogicalConjunction,
|
|
|
138 new SqlQuery.Predicate.ExprExpr(
|
|
|
139 new SqlExpression(null, "ROWNUM", Precedence.Primary),
|
|
|
140 SqlQuery.Predicate.Operator.LessOrEqual,
|
|
|
141 SqlQuery.Select.TakeValue));
|
|
|
142
|
|
|
143 if (base.BuildWhere())
|
|
|
144 {
|
|
|
145 sb.Append(" AND ");
|
|
|
146 BuildSearchCondition(sb, Precedence.LogicalConjunction, condition);
|
|
|
147 }
|
|
|
148 }
|
|
|
149 else
|
|
|
150 BuildSearchCondition(sb, Precedence.Unknown, condition);
|
|
|
151 }
|
|
|
152
|
|
|
153 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
154 {
|
|
|
155 expr = base.ConvertExpression(expr);
|
|
|
156
|
|
|
157 if (expr is SqlBinaryExpression)
|
|
|
158 {
|
|
|
159 var be = (SqlBinaryExpression)expr;
|
|
|
160
|
|
|
161 switch (be.Operation)
|
|
|
162 {
|
|
|
163 case "%": return new SqlFunction(be.SystemType, "MOD", be.Expr1, be.Expr2);
|
|
|
164 case "&": return new SqlFunction(be.SystemType, "BITAND", be.Expr1, be.Expr2);
|
|
|
165 case "|": // (a + b) - BITAND(a, b)
|
|
|
166 return Sub(
|
|
|
167 Add(be.Expr1, be.Expr2, be.SystemType),
|
|
|
168 new SqlFunction(be.SystemType, "BITAND", be.Expr1, be.Expr2),
|
|
|
169 be.SystemType);
|
|
|
170
|
|
|
171 case "^": // (a + b) - BITAND(a, b) * 2
|
|
|
172 return Sub(
|
|
|
173 Add(be.Expr1, be.Expr2, be.SystemType),
|
|
|
174 Mul(new SqlFunction(be.SystemType, "BITAND", be.Expr1, be.Expr2), 2),
|
|
|
175 be.SystemType);
|
|
|
176 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
|
|
|
177 }
|
|
|
178 }
|
|
|
179 else if (expr is SqlFunction)
|
|
|
180 {
|
|
|
181 var func = (SqlFunction) expr;
|
|
|
182
|
|
|
183 switch (func.Name)
|
|
|
184 {
|
|
|
185 case "Coalesce" : return new SqlFunction(func.SystemType, "Nvl", func.Parameters);
|
|
|
186 case "Convert" :
|
|
|
187 {
|
|
|
188 var ftype = TypeHelper.GetUnderlyingType(func.SystemType);
|
|
|
189
|
|
|
190 if (ftype == typeof(bool))
|
|
|
191 {
|
|
|
192 var ex = AlternativeConvertToBoolean(func, 1);
|
|
|
193 if (ex != null)
|
|
|
194 return ex;
|
|
|
195 }
|
|
|
196
|
|
|
197 if (ftype == typeof(DateTime) || ftype == typeof(DateTimeOffset))
|
|
|
198 {
|
|
|
199 if (IsTimeDataType(func.Parameters[0]))
|
|
|
200 {
|
|
|
201 if (func.Parameters[1].SystemType == typeof(string))
|
|
|
202 return func.Parameters[1];
|
|
|
203
|
|
|
204 return new SqlFunction(func.SystemType, "To_Char", func.Parameters[1], new SqlValue("HH24:MI:SS"));
|
|
|
205 }
|
|
|
206
|
|
|
207 if (TypeHelper.GetUnderlyingType(func.Parameters[1].SystemType) == typeof(DateTime) &&
|
|
|
208 IsDateDataType(func.Parameters[0], "Date"))
|
|
|
209 {
|
|
|
210 return new SqlFunction(func.SystemType, "Trunc", func.Parameters[1], new SqlValue("DD"));
|
|
|
211 }
|
|
|
212
|
|
|
213 return new SqlFunction(func.SystemType, "To_Timestamp", func.Parameters[1], new SqlValue("YYYY-MM-DD HH24:MI:SS"));
|
|
|
214 }
|
|
|
215
|
|
|
216 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, FloorBeforeConvert(func), func.Parameters[0]);
|
|
|
217 }
|
|
|
218 case "ContainsExactly":
|
|
|
219 return func.Parameters.Length == 2 ?
|
|
|
220 new SqlFunction(func.SystemType, "Contains", func.Parameters[1], func.Parameters[0]) :
|
|
|
221 new SqlFunction(func.SystemType, "Contains", func.Parameters[1], func.Parameters[0], func.Parameters[2]);
|
|
|
222 case "CharIndex" :
|
|
|
223 return func.Parameters.Length == 2?
|
|
|
224 new SqlFunction(func.SystemType, "InStr", func.Parameters[1], func.Parameters[0]):
|
|
|
225 new SqlFunction(func.SystemType, "InStr", func.Parameters[1], func.Parameters[0], func.Parameters[2]);
|
|
|
226 case "AddYear" : return new SqlFunction(func.SystemType, "Add_Months", func.Parameters[0], Mul(func.Parameters[1], 12));
|
|
|
227 case "AddQuarter" : return new SqlFunction(func.SystemType, "Add_Months", func.Parameters[0], Mul(func.Parameters[1], 3));
|
|
|
228 case "AddMonth" : return new SqlFunction(func.SystemType, "Add_Months", func.Parameters[0], func.Parameters[1]);
|
|
|
229 case "AddDayOfYear" :
|
|
|
230 case "AddWeekDay" :
|
|
|
231 case "AddDay" : return Add<DateTime>(func.Parameters[0], func.Parameters[1]);
|
|
|
232 case "AddWeek" : return Add<DateTime>(func.Parameters[0], Mul(func.Parameters[1], 7));
|
|
|
233 case "AddHour" : return Add<DateTime>(func.Parameters[0], Div(func.Parameters[1], 24));
|
|
|
234 case "AddMinute" : return Add<DateTime>(func.Parameters[0], Div(func.Parameters[1], 60 * 24));
|
|
|
235 case "AddSecond" : return Add<DateTime>(func.Parameters[0], Div(func.Parameters[1], 60 * 60 * 24));
|
|
|
236 case "AddMillisecond" : return Add<DateTime>(func.Parameters[0], Div(func.Parameters[1], 1000 * 60 * 60 * 24));
|
|
|
237 case "Avg" :
|
|
|
238 return new SqlFunction(
|
|
|
239 func.SystemType,
|
|
|
240 "Round",
|
|
|
241 new SqlFunction(func.SystemType, "AVG", func.Parameters[0]),
|
|
|
242 new SqlValue(27));
|
|
|
243 }
|
|
|
244 }
|
|
|
245 else if (expr is SqlExpression)
|
|
|
246 {
|
|
|
247 var e = (SqlExpression)expr;
|
|
|
248
|
|
|
249 if (e.Expr.StartsWith("To_Number(To_Char(") && e.Expr.EndsWith(", 'FF'))"))
|
|
|
250 return Div(new SqlExpression(e.SystemType, e.Expr.Replace("To_Number(To_Char(", "to_Number(To_Char("), e.Parameters), 1000);
|
|
|
251 }
|
|
|
252
|
|
|
253 return expr;
|
|
|
254 }
|
|
|
255
|
|
|
256 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
257 {
|
|
|
258 func = ConvertFunctionParameters(func);
|
|
|
259 base.BuildFunction(sb, func);
|
|
|
260 }
|
|
|
261
|
|
|
262 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
263 {
|
|
|
264 switch (type.SqlDbType)
|
|
|
265 {
|
|
|
266 case SqlDbType.BigInt : sb.Append("Number(19)"); break;
|
|
|
267 case SqlDbType.TinyInt : sb.Append("Number(3)"); break;
|
|
|
268 case SqlDbType.Money : sb.Append("Number(19,4)"); break;
|
|
|
269 case SqlDbType.SmallMoney : sb.Append("Number(10,4)"); break;
|
|
|
270 case SqlDbType.NVarChar :
|
|
|
271 sb.Append("VarChar2");
|
|
|
272 if (type.Length > 0)
|
|
|
273 sb.Append('(').Append(type.Length).Append(')');
|
|
|
274 break;
|
|
|
275 default : base.BuildDataType(sb, type); break;
|
|
|
276 }
|
|
|
277 }
|
|
|
278
|
|
|
279 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
280 {
|
|
|
281 CheckAliases(sqlQuery, 30);
|
|
|
282
|
|
|
283 new QueryVisitor().Visit(sqlQuery.Select, element =>
|
|
|
284 {
|
|
|
285 if (element.ElementType == QueryElementType.SqlParameter)
|
|
|
286 ((SqlParameter)element).IsQueryParameter = false;
|
|
|
287 });
|
|
|
288
|
|
|
289 sqlQuery = base.Finalize(sqlQuery);
|
|
|
290
|
|
|
291 switch (sqlQuery.QueryType)
|
|
|
292 {
|
|
|
293 case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
|
|
|
294 case QueryType.Update : return GetAlternativeUpdate(sqlQuery);
|
|
|
295 default : return sqlQuery;
|
|
|
296 }
|
|
|
297 }
|
|
|
298
|
|
|
299 protected override void BuildFromClause(StringBuilder sb)
|
|
|
300 {
|
|
|
301 if (!SqlQuery.IsUpdate)
|
|
|
302 base.BuildFromClause(sb);
|
|
|
303 }
|
|
|
304
|
|
|
305 public override void BuildValue(StringBuilder sb, object value)
|
|
|
306 {
|
|
|
307 if (value is Guid)
|
|
|
308 {
|
|
|
309 var s = ((Guid)value).ToString("N");
|
|
|
310
|
|
|
311 sb
|
|
|
312 .Append("Cast('")
|
|
|
313 .Append(s.Substring( 6, 2))
|
|
|
314 .Append(s.Substring( 4, 2))
|
|
|
315 .Append(s.Substring( 2, 2))
|
|
|
316 .Append(s.Substring( 0, 2))
|
|
|
317 .Append(s.Substring(10, 2))
|
|
|
318 .Append(s.Substring( 8, 2))
|
|
|
319 .Append(s.Substring(14, 2))
|
|
|
320 .Append(s.Substring(12, 2))
|
|
|
321 .Append(s.Substring(16, 16))
|
|
|
322 .Append("' as raw(16))");
|
|
|
323 }
|
|
|
324 else if (value is DateTime)
|
|
|
325 {
|
|
|
326 sb.AppendFormat("TO_TIMESTAMP('{0:yyyy-MM-dd HH:mm:ss.fffffff}', 'YYYY-MM-DD HH24:MI:SS.FF7')", value);
|
|
|
327 }
|
|
|
328 else
|
|
|
329 base.BuildValue(sb, value);
|
|
|
330 }
|
|
|
331
|
|
|
332 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
333 {
|
|
|
334 var wrap = false;
|
|
|
335
|
|
|
336 if (expr.SystemType == typeof(bool))
|
|
|
337 {
|
|
|
338 if (expr is SqlQuery.SearchCondition)
|
|
|
339 wrap = true;
|
|
|
340 else
|
|
|
341 {
|
|
|
342 var ex = expr as SqlExpression;
|
|
|
343 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
|
|
|
344 }
|
|
|
345 }
|
|
|
346
|
|
|
347 if (wrap) sb.Append("CASE WHEN ");
|
|
|
348 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
|
|
|
349 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
|
|
|
350 }
|
|
|
351
|
|
|
352 public override object Convert(object value, ConvertType convertType)
|
|
|
353 {
|
|
|
354 switch (convertType)
|
|
|
355 {
|
|
|
356 case ConvertType.NameToQueryParameter:
|
|
|
357 return ":" + value;
|
|
|
358 }
|
|
|
359
|
|
|
360 return value;
|
|
|
361 }
|
|
|
362
|
|
|
363 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
364 {
|
|
|
365 BuildInsertOrUpdateQueryAsMerge(sb, "FROM SYS.DUAL");
|
|
|
366 }
|
|
|
367
|
|
|
368 protected override void BuildEmptyInsert(StringBuilder sb)
|
|
|
369 {
|
|
|
370 sb.Append("VALUES ");
|
|
|
371
|
|
|
372 foreach (var col in SqlQuery.Insert.Into.Fields)
|
|
|
373 sb.Append("(DEFAULT)");
|
|
|
374
|
|
|
375 sb.AppendLine();
|
|
|
376 }
|
|
|
377 }
|
|
|
378 }
|
