Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/DB2SqlProvider.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.Text;
3
4 using BLToolkit.Reflection;
5
6 namespace BLToolkit.Data.Sql.SqlProvider
7 {
8 using DataProvider;
9
10 public class DB2SqlProvider : BasicSqlProvider
11 {
12 public override bool TakeAcceptsParameter { get { return SqlQuery.Select.SkipValue != null; } }
13
14 SqlField _identityField;
15
16 public override int CommandCount(SqlQuery sqlQuery)
17 {
18 if (sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity)
19 {
20 _identityField = sqlQuery.Insert.Into.GetIdentityField();
21
22 if (_identityField == null)
23 return 2;
24 }
25
26 return 1;
27 }
28
29 public override int BuildSql(int commandNumber, SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias)
30 {
31 if (_identityField != null)
32 {
33 indent += 2;
34
35 AppendIndent(sb).AppendLine("SELECT");
36 AppendIndent(sb).Append("\t");
37 BuildExpression(sb, _identityField, false, true);
38 sb.AppendLine();
39 AppendIndent(sb).AppendLine("FROM");
40 AppendIndent(sb).AppendLine("\tNEW TABLE");
41 AppendIndent(sb).AppendLine("\t(");
42 }
43
44 var ret = base.BuildSql(commandNumber, sqlQuery, sb, indent, nesting, skipAlias);
45
46 if (_identityField != null)
47 sb.AppendLine("\t)");
48
49 return ret;
50 }
51
52 protected override void BuildCommand(int commandNumber, StringBuilder sb)
53 {
54 sb.AppendLine("SELECT identity_val_local() FROM SYSIBM.SYSDUMMY1");
55 }
56
57 protected override ISqlProvider CreateSqlProvider()
58 {
59 return new DB2SqlProvider();
60 }
61
62 protected override void BuildSql(StringBuilder sb)
63 {
64 AlternativeBuildSql(sb, false, base.BuildSql);
65 }
66
67 protected override void BuildSelectClause(StringBuilder sb)
68 {
69 if (SqlQuery.From.Tables.Count == 0)
70 {
71 AppendIndent(sb).AppendLine("SELECT");
72 BuildColumns(sb);
73 AppendIndent(sb).AppendLine("FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY");
74 }
75 else
76 base.BuildSelectClause(sb);
77 }
78
79 protected override string LimitFormat
80 {
81 get { return SqlQuery.Select.SkipValue == null ? "FETCH FIRST {0} ROWS ONLY" : null; }
82 }
83
84 public override ISqlExpression ConvertExpression(ISqlExpression expr)
85 {
86 expr = base.ConvertExpression(expr);
87
88 if (expr is SqlBinaryExpression)
89 {
90 var be = (SqlBinaryExpression)expr;
91
92 switch (be.Operation)
93 {
94 case "%":
95 {
96 var expr1 = !TypeHelper.IsIntegerType(be.Expr1.SystemType) ? new SqlFunction(typeof(int), "Int", be.Expr1) : be.Expr1;
97 return new SqlFunction(be.SystemType, "Mod", expr1, be.Expr2);
98 }
99 case "&": return new SqlFunction(be.SystemType, "BitAnd", be.Expr1, be.Expr2);
100 case "|": return new SqlFunction(be.SystemType, "BitOr", be.Expr1, be.Expr2);
101 case "^": return new SqlFunction(be.SystemType, "BitXor", be.Expr1, be.Expr2);
102 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
103 }
104 }
105 else if (expr is SqlFunction)
106 {
107 var func = (SqlFunction) expr;
108
109 switch (func.Name)
110 {
111 case "Convert" :
112 if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(bool))
113 {
114 var ex = AlternativeConvertToBoolean(func, 1);
115 if (ex != null)
116 return ex;
117 }
118
119 if (func.Parameters[0] is SqlDataType)
120 {
121 var type = (SqlDataType)func.Parameters[0];
122
123 if (type.Type == typeof(string) && func.Parameters[1].SystemType != typeof(string))
124 return new SqlFunction(func.SystemType, "RTrim", new SqlFunction(typeof(string), "Char", func.Parameters[1]));
125
126 if (type.Length > 0)
127 return new SqlFunction(func.SystemType, type.SqlDbType.ToString(), func.Parameters[1], new SqlValue(type.Length));
128
129 if (type.Precision > 0)
130 return new SqlFunction(func.SystemType, type.SqlDbType.ToString(), func.Parameters[1], new SqlValue(type.Precision), new SqlValue(type.Scale));
131
132 return new SqlFunction(func.SystemType, type.SqlDbType.ToString(), func.Parameters[1]);
133 }
134
135 if (func.Parameters[0] is SqlFunction)
136 {
137 var f = (SqlFunction)func.Parameters[0];
138
139 return
140 f.Name == "Char" ?
141 new SqlFunction(func.SystemType, f.Name, func.Parameters[1]) :
142 f.Parameters.Length == 1 ?
143 new SqlFunction(func.SystemType, f.Name, func.Parameters[1], f.Parameters[0]) :
144 new SqlFunction(func.SystemType, f.Name, func.Parameters[1], f.Parameters[0], f.Parameters[1]);
145 }
146
147 {
148 var e = (SqlExpression)func.Parameters[0];
149 return new SqlFunction(func.SystemType, e.Expr, func.Parameters[1]);
150 }
151
152 case "Millisecond" : return Div(new SqlFunction(func.SystemType, "Microsecond", func.Parameters), 1000);
153 case "SmallDateTime" :
154 case "DateTime" :
155 case "DateTime2" : return new SqlFunction(func.SystemType, "TimeStamp", func.Parameters);
156 case "TinyInt" : return new SqlFunction(func.SystemType, "SmallInt", func.Parameters);
157 case "Money" : return new SqlFunction(func.SystemType, "Decimal", func.Parameters[0], new SqlValue(19), new SqlValue(4));
158 case "SmallMoney" : return new SqlFunction(func.SystemType, "Decimal", func.Parameters[0], new SqlValue(10), new SqlValue(4));
159 case "VarChar" :
160 if (TypeHelper.GetUnderlyingType(func.Parameters[0].SystemType) == typeof(decimal))
161 return new SqlFunction(func.SystemType, "Char", func.Parameters[0]);
162 break;
163 case "NChar" :
164 case "NVarChar" : return new SqlFunction(func.SystemType, "Char", func.Parameters);
165 case "DateDiff" :
166 {
167 switch ((Linq.Sql.DateParts)((SqlValue)func.Parameters[0]).Value)
168 {
169 case Linq.Sql.DateParts.Day : return new SqlExpression(typeof(int), "((Days({0}) - Days({1})) * 86400 + (MIDNIGHT_SECONDS({0}) - MIDNIGHT_SECONDS({1}))) / 86400", Precedence.Multiplicative, func.Parameters[2], func.Parameters[1]);
170 case Linq.Sql.DateParts.Hour : return new SqlExpression(typeof(int), "((Days({0}) - Days({1})) * 86400 + (MIDNIGHT_SECONDS({0}) - MIDNIGHT_SECONDS({1}))) / 3600", Precedence.Multiplicative, func.Parameters[2], func.Parameters[1]);
171 case Linq.Sql.DateParts.Minute : return new SqlExpression(typeof(int), "((Days({0}) - Days({1})) * 86400 + (MIDNIGHT_SECONDS({0}) - MIDNIGHT_SECONDS({1}))) / 60", Precedence.Multiplicative, func.Parameters[2], func.Parameters[1]);
172 case Linq.Sql.DateParts.Second : return new SqlExpression(typeof(int), "(Days({0}) - Days({1})) * 86400 + (MIDNIGHT_SECONDS({0}) - MIDNIGHT_SECONDS({1}))", Precedence.Additive, func.Parameters[2], func.Parameters[1]);
173 case Linq.Sql.DateParts.Millisecond : return new SqlExpression(typeof(int), "((Days({0}) - Days({1})) * 86400 + (MIDNIGHT_SECONDS({0}) - MIDNIGHT_SECONDS({1}))) * 1000 + (MICROSECOND({0}) - MICROSECOND({1})) / 1000", Precedence.Additive, func.Parameters[2], func.Parameters[1]);
174 }
175 }
176
177 break;
178 }
179 }
180
181 return expr;
182 }
183
184 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
185 {
186 func = ConvertFunctionParameters(func);
187 base.BuildFunction(sb, func);
188 }
189
190 static void SetQueryParameter(IQueryElement element)
191 {
192 if (element.ElementType == QueryElementType.SqlParameter)
193 ((SqlParameter)element).IsQueryParameter = false;
194 }
195
196 public override SqlQuery Finalize(SqlQuery sqlQuery)
197 {
198 new QueryVisitor().Visit(sqlQuery.Select, SetQueryParameter);
199
200 //if (sqlQuery.QueryType == QueryType.InsertOrUpdate)
201 // foreach (var key in sqlQuery.Insert.Items)
202 // if (((SqlField)key.Column).IsPrimaryKey)
203 // new QueryVisitor().Visit(key.Expression, SetQueryParameter);
204
205 sqlQuery = base.Finalize(sqlQuery);
206
207 switch (sqlQuery.QueryType)
208 {
209 case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
210 case QueryType.Update : return GetAlternativeUpdate(sqlQuery);
211 default : return sqlQuery;
212 }
213 }
214
215 protected override void BuildFromClause(StringBuilder sb)
216 {
217 if (!SqlQuery.IsUpdate)
218 base.BuildFromClause(sb);
219 }
220
221 public override void BuildValue(StringBuilder sb, object value)
222 {
223 if (value is Guid)
224 {
225 var s = ((Guid)value).ToString("N");
226
227 sb
228 .Append("Cast(x'")
229 .Append(s.Substring( 6, 2))
230 .Append(s.Substring( 4, 2))
231 .Append(s.Substring( 2, 2))
232 .Append(s.Substring( 0, 2))
233 .Append(s.Substring(10, 2))
234 .Append(s.Substring( 8, 2))
235 .Append(s.Substring(14, 2))
236 .Append(s.Substring(12, 2))
237 .Append(s.Substring(16, 16))
238 .Append("' as char(16) for bit data)");
239 }
240 else
241 base.BuildValue(sb, value);
242 }
243
244 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
245 {
246 var wrap = false;
247
248 if (expr.SystemType == typeof(bool))
249 {
250 if (expr is SqlQuery.SearchCondition)
251 wrap = true;
252 else
253 {
254 var ex = expr as SqlExpression;
255 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
256 }
257 }
258
259 if (wrap) sb.Append("CASE WHEN ");
260 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
261 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
262 }
263
264 public static bool QuoteIdentifiers = true;
265
266 public override object Convert(object value, ConvertType convertType)
267 {
268 switch (convertType)
269 {
270 case ConvertType.NameToQueryParameter:
271 return "@" + value;
272
273 case ConvertType.NameToCommandParameter:
274 case ConvertType.NameToSprocParameter:
275 return ":" + value;
276
277 case ConvertType.SprocParameterToName:
278 if (value != null)
279 {
280 var str = value.ToString();
281 return str.Length > 0 && str[0] == ':'? str.Substring(1): str;
282 }
283
284 break;
285
286 case ConvertType.NameToQueryField:
287 case ConvertType.NameToQueryFieldAlias:
288 case ConvertType.NameToQueryTable:
289 case ConvertType.NameToQueryTableAlias:
290 if (QuoteIdentifiers)
291 {
292 var name = value.ToString();
293
294 if (name.Length > 0 && name[0] == '"')
295 return value;
296
297 return '"' + name + '"';
298 }
299
300 break;
301 }
302
303 return value;
304 }
305
306 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
307 {
308 BuildInsertOrUpdateQueryAsMerge(sb, "FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY");
309 }
310
311 protected override void BuildEmptyInsert(StringBuilder sb)
312 {
313 sb.Append("VALUES ");
314
315 foreach (var col in SqlQuery.Insert.Into.Fields)
316 sb.Append("(DEFAULT)");
317
318 sb.AppendLine();
319 }
320 }
321 }