|
0
|
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 }
|
