Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/OracleSqlProvider.cs @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
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 }