|
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 InformixSqlProvider : BasicSqlProvider
|
|
|
11 {
|
|
|
12 public override int CommandCount(SqlQuery sqlQuery)
|
|
|
13 {
|
|
|
14 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
|
|
|
15 }
|
|
|
16
|
|
|
17 protected override void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
18 {
|
|
|
19 sb.AppendLine("SELECT DBINFO('sqlca.sqlerrd1') FROM systables where tabid = 1");
|
|
|
20 }
|
|
|
21
|
|
|
22 protected override ISqlProvider CreateSqlProvider()
|
|
|
23 {
|
|
|
24 return new InformixSqlProvider();
|
|
|
25 }
|
|
|
26
|
|
|
27 public override int BuildSql(int commandNumber, SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias)
|
|
|
28 {
|
|
|
29 var n = base.BuildSql(commandNumber, sqlQuery, sb, indent, nesting, skipAlias);
|
|
|
30
|
|
|
31 sb
|
|
|
32 .Replace("NULL IS NOT NULL", "1=0")
|
|
|
33 .Replace("NULL IS NULL", "1=1");
|
|
|
34
|
|
|
35 return n;
|
|
|
36 }
|
|
|
37
|
|
|
38 protected override void BuildSelectClause(StringBuilder sb)
|
|
|
39 {
|
|
|
40 if (SqlQuery.From.Tables.Count == 0)
|
|
|
41 {
|
|
|
42 AppendIndent(sb).Append("SELECT FIRST 1").AppendLine();
|
|
|
43 BuildColumns(sb);
|
|
|
44 AppendIndent(sb).Append("FROM SYSTABLES").AppendLine();
|
|
|
45 }
|
|
|
46 else
|
|
|
47 base.BuildSelectClause(sb);
|
|
|
48 }
|
|
|
49
|
|
|
50 public override bool IsSubQueryTakeSupported { get { return false; } }
|
|
|
51 public override bool IsInsertOrUpdateSupported { get { return false; } }
|
|
|
52 public override bool IsGroupByExpressionSupported { get { return false; } }
|
|
|
53
|
|
|
54 protected override string FirstFormat { get { return "FIRST {0}"; } }
|
|
|
55 protected override string SkipFormat { get { return "SKIP {0}"; } }
|
|
|
56
|
|
|
57 protected override void BuildLikePredicate(StringBuilder sb, SqlQuery.Predicate.Like predicate)
|
|
|
58 {
|
|
|
59 if (predicate.IsNot)
|
|
|
60 sb.Append("NOT ");
|
|
|
61
|
|
|
62 var precedence = GetPrecedence(predicate);
|
|
|
63
|
|
|
64 BuildExpression(sb, precedence, predicate.Expr1);
|
|
|
65 sb.Append(" LIKE ");
|
|
|
66 BuildExpression(sb, precedence, predicate.Expr2);
|
|
|
67
|
|
|
68 if (predicate.Escape != null)
|
|
|
69 {
|
|
|
70 sb.Append(" ESCAPE ");
|
|
|
71 BuildExpression(sb, precedence, predicate.Escape);
|
|
|
72 }
|
|
|
73 }
|
|
|
74
|
|
|
75 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
76 {
|
|
|
77 expr = base.ConvertExpression(expr);
|
|
|
78
|
|
|
79 if (expr is SqlBinaryExpression)
|
|
|
80 {
|
|
|
81 var be = (SqlBinaryExpression)expr;
|
|
|
82
|
|
|
83 switch (be.Operation)
|
|
|
84 {
|
|
|
85 case "%": return new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2);
|
|
|
86 case "&": return new SqlFunction(be.SystemType, "BitAnd", be.Expr1, be.Expr2);
|
|
|
87 case "|": return new SqlFunction(be.SystemType, "BitOr", be.Expr1, be.Expr2);
|
|
|
88 case "^": return new SqlFunction(be.SystemType, "BitXor", be.Expr1, be.Expr2);
|
|
|
89 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
|
|
|
90 }
|
|
|
91 }
|
|
|
92 else if (expr is SqlFunction)
|
|
|
93 {
|
|
|
94 var func = (SqlFunction)expr;
|
|
|
95
|
|
|
96 switch (func.Name)
|
|
|
97 {
|
|
|
98 case "Coalesce" : return new SqlFunction(func.SystemType, "Nvl", func.Parameters);
|
|
|
99 case "Convert" :
|
|
|
100 {
|
|
|
101 var par0 = func.Parameters[0];
|
|
|
102 var par1 = func.Parameters[1];
|
|
|
103
|
|
|
104 switch (Type.GetTypeCode(TypeHelper.GetUnderlyingType(func.SystemType)))
|
|
|
105 {
|
|
|
106 case TypeCode.String : return new SqlFunction(func.SystemType, "To_Char", func.Parameters[1]);
|
|
|
107 case TypeCode.Boolean :
|
|
|
108 {
|
|
|
109 var ex = AlternativeConvertToBoolean(func, 1);
|
|
|
110 if (ex != null)
|
|
|
111 return ex;
|
|
|
112 break;
|
|
|
113 }
|
|
|
114
|
|
|
115 case TypeCode.UInt64:
|
|
|
116 if (TypeHelper.IsFloatType(func.Parameters[1].SystemType))
|
|
|
117 par1 = new SqlFunction(func.SystemType, "Floor", func.Parameters[1]);
|
|
|
118 break;
|
|
|
119
|
|
|
120 case TypeCode.DateTime :
|
|
|
121 if (IsDateDataType(func.Parameters[0], "Date"))
|
|
|
122 {
|
|
|
123 if (func.Parameters[1].SystemType == typeof(string))
|
|
|
124 {
|
|
|
125 return new SqlFunction(
|
|
|
126 func.SystemType,
|
|
|
127 "Date",
|
|
|
128 new SqlFunction(func.SystemType, "To_Date", func.Parameters[1], new SqlValue("%Y-%m-%d")));
|
|
|
129 }
|
|
|
130
|
|
|
131 return new SqlFunction(func.SystemType, "Date", func.Parameters[1]);
|
|
|
132 }
|
|
|
133
|
|
|
134 if (IsTimeDataType(func.Parameters[0]))
|
|
|
135 return new SqlExpression(func.SystemType, "Cast(Extend({0}, hour to second) as Char(8))", Precedence.Primary, func.Parameters[1]);
|
|
|
136
|
|
|
137 return new SqlFunction(func.SystemType, "To_Date", func.Parameters[1]);
|
|
|
138
|
|
|
139 default:
|
|
|
140 if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(DateTimeOffset))
|
|
|
141 goto case TypeCode.DateTime;
|
|
|
142 break;
|
|
|
143 }
|
|
|
144
|
|
|
145 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, par1, par0);
|
|
|
146 }
|
|
|
147
|
|
|
148 case "Quarter" : return Inc(Div(Dec(new SqlFunction(func.SystemType, "Month", func.Parameters)), 3));
|
|
|
149 case "WeekDay" : return Inc(new SqlFunction(func.SystemType, "weekDay", func.Parameters));
|
|
|
150 case "DayOfYear":
|
|
|
151 return
|
|
|
152 Inc(Sub<int>(
|
|
|
153 new SqlFunction(null, "Mdy",
|
|
|
154 new SqlFunction(null, "Month", func.Parameters),
|
|
|
155 new SqlFunction(null, "Day", func.Parameters),
|
|
|
156 new SqlFunction(null, "Year", func.Parameters)),
|
|
|
157 new SqlFunction(null, "Mdy",
|
|
|
158 new SqlValue(1),
|
|
|
159 new SqlValue(1),
|
|
|
160 new SqlFunction(null, "Year", func.Parameters))));
|
|
|
161 case "Week" :
|
|
|
162 return
|
|
|
163 new SqlExpression(
|
|
|
164 func.SystemType,
|
|
|
165 "((Extend({0}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({0}))), Year({0}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int",
|
|
|
166 func.Parameters);
|
|
|
167 case "Hour" :
|
|
|
168 case "Minute" :
|
|
|
169 case "Second" : return new SqlExpression(func.SystemType, string.Format("({{0}}::datetime {0} to {0})::char(3)::int", func.Name), func.Parameters);
|
|
|
170 }
|
|
|
171 }
|
|
|
172
|
|
|
173 return expr;
|
|
|
174 }
|
|
|
175
|
|
|
176 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
177 {
|
|
|
178 func = ConvertFunctionParameters(func);
|
|
|
179 base.BuildFunction(sb, func);
|
|
|
180 }
|
|
|
181
|
|
|
182 public virtual object ConvertBooleanValue(bool value)
|
|
|
183 {
|
|
|
184 return value ? 't' : 'f';
|
|
|
185 }
|
|
|
186
|
|
|
187 public override void BuildValue(StringBuilder sb, object value)
|
|
|
188 {
|
|
|
189 if (value is bool || value is bool?)
|
|
|
190 sb.Append("'").Append(ConvertBooleanValue((bool)value)).Append("'");
|
|
|
191 else
|
|
|
192 base.BuildValue(sb, value);
|
|
|
193 }
|
|
|
194
|
|
|
195 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
196 {
|
|
|
197 switch (type.SqlDbType)
|
|
|
198 {
|
|
|
199 case SqlDbType.TinyInt : sb.Append("SmallInt"); break;
|
|
|
200 case SqlDbType.SmallMoney : sb.Append("Decimal(10,4)"); break;
|
|
|
201 default : base.BuildDataType(sb, type); break;
|
|
|
202 }
|
|
|
203 }
|
|
|
204
|
|
|
205 static void SetQueryParameter(IQueryElement element)
|
|
|
206 {
|
|
|
207 if (element.ElementType == QueryElementType.SqlParameter)
|
|
|
208 ((SqlParameter)element).IsQueryParameter = false;
|
|
|
209 }
|
|
|
210
|
|
|
211 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
212 {
|
|
|
213 CheckAliases(sqlQuery, int.MaxValue);
|
|
|
214
|
|
|
215 new QueryVisitor().Visit(sqlQuery.Select, SetQueryParameter);
|
|
|
216
|
|
|
217 //if (sqlQuery.QueryType == QueryType.InsertOrUpdate)
|
|
|
218 //{
|
|
|
219 // foreach (var key in sqlQuery.Insert.Items)
|
|
|
220 // new QueryVisitor().Visit(key.Expression, SetQueryParameter);
|
|
|
221 //
|
|
|
222 // foreach (var key in sqlQuery.Update.Items)
|
|
|
223 // new QueryVisitor().Visit(key.Expression, SetQueryParameter);
|
|
|
224 //}
|
|
|
225
|
|
|
226 sqlQuery = base.Finalize(sqlQuery);
|
|
|
227
|
|
|
228 switch (sqlQuery.QueryType)
|
|
|
229 {
|
|
|
230 case QueryType.Delete :
|
|
|
231 sqlQuery = GetAlternativeDelete(sqlQuery);
|
|
|
232 sqlQuery.From.Tables[0].Alias = "$";
|
|
|
233 break;
|
|
|
234
|
|
|
235 case QueryType.Update :
|
|
|
236 sqlQuery = GetAlternativeUpdate(sqlQuery);
|
|
|
237 break;
|
|
|
238 }
|
|
|
239
|
|
|
240 return sqlQuery;
|
|
|
241 }
|
|
|
242
|
|
|
243 protected override void BuildFromClause(StringBuilder sb)
|
|
|
244 {
|
|
|
245 if (!SqlQuery.IsUpdate)
|
|
|
246 base.BuildFromClause(sb);
|
|
|
247 }
|
|
|
248
|
|
|
249 public override object Convert(object value, ConvertType convertType)
|
|
|
250 {
|
|
|
251 switch (convertType)
|
|
|
252 {
|
|
|
253 case ConvertType.NameToQueryParameter : return "?";
|
|
|
254 case ConvertType.NameToCommandParameter :
|
|
|
255 case ConvertType.NameToSprocParameter : return ":" + value;
|
|
|
256 case ConvertType.SprocParameterToName :
|
|
|
257 if (value != null)
|
|
|
258 {
|
|
|
259 var str = value.ToString();
|
|
|
260 return (str.Length > 0 && str[0] == ':')? str.Substring(1): str;
|
|
|
261 }
|
|
|
262
|
|
|
263 break;
|
|
|
264 }
|
|
|
265
|
|
|
266 return value;
|
|
|
267 }
|
|
|
268
|
|
|
269 //protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
270 //{
|
|
|
271 // BuildInsertOrUpdateQueryAsMerge(sb, "FROM SYSTABLES");
|
|
|
272 //}
|
|
|
273 }
|
|
|
274 }
|
