|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Text;
|
|
|
4
|
|
|
5 #region ReSharper disable
|
|
|
6 // ReSharper disable SuggestUseVarKeywordEverywhere
|
|
|
7 // ReSharper disable SuggestUseVarKeywordEvident
|
|
|
8 #endregion
|
|
|
9
|
|
|
10 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
11 {
|
|
|
12 using DataProvider;
|
|
|
13 using Mapping;
|
|
|
14 using Reflection;
|
|
|
15
|
|
|
16 using Linq;
|
|
|
17
|
|
|
18 public class FirebirdSqlProvider : BasicSqlProvider, IMappingSchemaProvider
|
|
|
19 {
|
|
|
20 public FirebirdSqlProvider()
|
|
|
21 {
|
|
|
22 }
|
|
|
23
|
|
|
24 // public override int CommandCount(SqlQuery sqlQuery)
|
|
|
25 // {
|
|
|
26 // return sqlQuery.IsInsert && sqlQuery.Set.WithIdentity ? 2 : 1;
|
|
|
27 // }
|
|
|
28
|
|
|
29 // protected override void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
30 // {
|
|
|
31 // SequenceNameAttribute attr = GetSequenceNameAttribute(true);
|
|
|
32 //
|
|
|
33 // AppendIndent(sb)
|
|
|
34 // .Append("SELECT gen_id(")
|
|
|
35 // .Append(attr.SequenceName)
|
|
|
36 // .AppendLine(", 0) FROM rdb$database");
|
|
|
37 // }
|
|
|
38
|
|
|
39 protected override ISqlProvider CreateSqlProvider()
|
|
|
40 {
|
|
|
41 return new FirebirdSqlProvider();
|
|
|
42 }
|
|
|
43
|
|
|
44 protected override void BuildSelectClause(StringBuilder sb)
|
|
|
45 {
|
|
|
46 if (SqlQuery.From.Tables.Count == 0)
|
|
|
47 {
|
|
|
48 AppendIndent(sb);
|
|
|
49 sb.Append("SELECT").AppendLine();
|
|
|
50 BuildColumns(sb);
|
|
|
51 AppendIndent(sb);
|
|
|
52 sb.Append("FROM rdb$database").AppendLine();
|
|
|
53 }
|
|
|
54 else
|
|
|
55 base.BuildSelectClause(sb);
|
|
|
56 }
|
|
|
57
|
|
|
58 protected override bool SkipFirst { get { return false; } }
|
|
|
59 protected override string SkipFormat { get { return "SKIP {0}"; } }
|
|
|
60 protected override string FirstFormat { get { return "FIRST {0}"; } }
|
|
|
61
|
|
|
62 public override bool IsIdentityParameterRequired { get { return true; } }
|
|
|
63
|
|
|
64 protected override void BuildGetIdentity(StringBuilder sb)
|
|
|
65 {
|
|
|
66 var identityField = SqlQuery.Insert.Into.GetIdentityField();
|
|
|
67
|
|
|
68 if (identityField == null)
|
|
|
69 throw new SqlException("Identity field must be defined for '{0}'.", SqlQuery.Insert.Into.Name);
|
|
|
70
|
|
|
71 AppendIndent(sb).AppendLine("RETURNING");
|
|
|
72 AppendIndent(sb).Append("\t");
|
|
|
73 BuildExpression(sb, identityField, false, true);
|
|
|
74 }
|
|
|
75
|
|
|
76 public override ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
|
|
|
77 {
|
|
|
78 if (table.SequenceAttributes != null)
|
|
|
79 return new SqlExpression("GEN_ID(" + table.SequenceAttributes[0].SequenceName + ", 1)", Precedence.Primary);
|
|
|
80
|
|
|
81 return base.GetIdentityExpression(table, identityField, forReturning);
|
|
|
82 }
|
|
|
83
|
|
|
84 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
85 {
|
|
|
86 expr = base.ConvertExpression(expr);
|
|
|
87
|
|
|
88 if (expr is SqlBinaryExpression)
|
|
|
89 {
|
|
|
90 SqlBinaryExpression be = (SqlBinaryExpression)expr;
|
|
|
91
|
|
|
92 switch (be.Operation)
|
|
|
93 {
|
|
|
94 case "%": return new SqlFunction(be.SystemType, "Mod", be.Expr1, be.Expr2);
|
|
|
95 case "&": return new SqlFunction(be.SystemType, "Bin_And", be.Expr1, be.Expr2);
|
|
|
96 case "|": return new SqlFunction(be.SystemType, "Bin_Or", be.Expr1, be.Expr2);
|
|
|
97 case "^": return new SqlFunction(be.SystemType, "Bin_Xor", be.Expr1, be.Expr2);
|
|
|
98 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
|
|
|
99 }
|
|
|
100 }
|
|
|
101 else if (expr is SqlFunction)
|
|
|
102 {
|
|
|
103 SqlFunction func = (SqlFunction)expr;
|
|
|
104
|
|
|
105 switch (func.Name)
|
|
|
106 {
|
|
|
107 case "Convert" :
|
|
|
108 if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(bool))
|
|
|
109 {
|
|
|
110 ISqlExpression ex = AlternativeConvertToBoolean(func, 1);
|
|
|
111 if (ex != null)
|
|
|
112 return ex;
|
|
|
113 }
|
|
|
114
|
|
|
115 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, FloorBeforeConvert(func), func.Parameters[0]);
|
|
|
116
|
|
|
117 case "DateAdd" :
|
|
|
118 switch ((Sql.DateParts)((SqlValue)func.Parameters[0]).Value)
|
|
|
119 {
|
|
|
120 case Sql.DateParts.Quarter :
|
|
|
121 return new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Month), Mul(func.Parameters[1], 3), func.Parameters[2]);
|
|
|
122 case Sql.DateParts.DayOfYear:
|
|
|
123 case Sql.DateParts.WeekDay:
|
|
|
124 return new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Day), func.Parameters[1], func.Parameters[2]);
|
|
|
125 case Sql.DateParts.Week :
|
|
|
126 return new SqlFunction(func.SystemType, func.Name, new SqlValue(Sql.DateParts.Day), Mul(func.Parameters[1], 7), func.Parameters[2]);
|
|
|
127 }
|
|
|
128
|
|
|
129 break;
|
|
|
130 }
|
|
|
131 }
|
|
|
132 else if (expr is SqlExpression)
|
|
|
133 {
|
|
|
134 SqlExpression e = (SqlExpression)expr;
|
|
|
135
|
|
|
136 if (e.Expr.StartsWith("Extract(Quarter"))
|
|
|
137 return Inc(Div(Dec(new SqlExpression(e.SystemType, "Extract(Month from {0})", e.Parameters)), 3));
|
|
|
138
|
|
|
139 if (e.Expr.StartsWith("Extract(YearDay"))
|
|
|
140 return Inc(new SqlExpression(e.SystemType, e.Expr.Replace("Extract(YearDay", "Extract(yearDay"), e.Parameters));
|
|
|
141
|
|
|
142 if (e.Expr.StartsWith("Extract(WeekDay"))
|
|
|
143 return Inc(new SqlExpression(e.SystemType, e.Expr.Replace("Extract(WeekDay", "Extract(weekDay"), e.Parameters));
|
|
|
144 }
|
|
|
145
|
|
|
146 return expr;
|
|
|
147 }
|
|
|
148
|
|
|
149 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
150 {
|
|
|
151 func = ConvertFunctionParameters(func);
|
|
|
152 base.BuildFunction(sb, func);
|
|
|
153 }
|
|
|
154
|
|
|
155 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
156 {
|
|
|
157 switch (type.SqlDbType)
|
|
|
158 {
|
|
|
159 case SqlDbType.Decimal :
|
|
|
160 base.BuildDataType(sb, type.Precision > 18 ? new SqlDataType(type.SqlDbType, type.Type, 18, type.Scale) : type);
|
|
|
161 break;
|
|
|
162 case SqlDbType.TinyInt : sb.Append("SmallInt"); break;
|
|
|
163 case SqlDbType.Money : sb.Append("Decimal(18,4)"); break;
|
|
|
164 case SqlDbType.SmallMoney : sb.Append("Decimal(10,4)"); break;
|
|
|
165 #if !MONO
|
|
|
166 case SqlDbType.DateTime2 :
|
|
|
167 #endif
|
|
|
168 case SqlDbType.SmallDateTime :
|
|
|
169 case SqlDbType.DateTime : sb.Append("TimeStamp"); break;
|
|
|
170 case SqlDbType.NVarChar :
|
|
|
171 sb.Append("VarChar");
|
|
|
172 if (type.Length > 0)
|
|
|
173 sb.Append('(').Append(type.Length).Append(')');
|
|
|
174 break;
|
|
|
175 default : base.BuildDataType(sb, type); break;
|
|
|
176 }
|
|
|
177 }
|
|
|
178
|
|
|
179 static void SetNonQueryParameter(IQueryElement element)
|
|
|
180 {
|
|
|
181 if (element.ElementType == QueryElementType.SqlParameter)
|
|
|
182 ((SqlParameter)element).IsQueryParameter = false;
|
|
|
183 }
|
|
|
184
|
|
|
185 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
186 {
|
|
|
187 CheckAliases(sqlQuery, int.MaxValue);
|
|
|
188
|
|
|
189 new QueryVisitor().Visit(sqlQuery.Select, SetNonQueryParameter);
|
|
|
190
|
|
|
191 if (sqlQuery.QueryType == QueryType.InsertOrUpdate)
|
|
|
192 {
|
|
|
193 foreach (var key in sqlQuery.Insert.Items)
|
|
|
194 new QueryVisitor().Visit(key.Expression, SetNonQueryParameter);
|
|
|
195
|
|
|
196 foreach (var key in sqlQuery.Update.Items)
|
|
|
197 new QueryVisitor().Visit(key.Expression, SetNonQueryParameter);
|
|
|
198
|
|
|
199 foreach (var key in sqlQuery.Update.Keys)
|
|
|
200 new QueryVisitor().Visit(key.Expression, SetNonQueryParameter);
|
|
|
201 }
|
|
|
202
|
|
|
203 new QueryVisitor().Visit(sqlQuery, element =>
|
|
|
204 {
|
|
|
205 if (element.ElementType == QueryElementType.InSubQueryPredicate)
|
|
|
206 new QueryVisitor().Visit(((SqlQuery.Predicate.InSubQuery)element).Expr1, SetNonQueryParameter);
|
|
|
207 });
|
|
|
208
|
|
|
209 sqlQuery = base.Finalize(sqlQuery);
|
|
|
210
|
|
|
211 switch (sqlQuery.QueryType)
|
|
|
212 {
|
|
|
213 case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
|
|
|
214 case QueryType.Update : return GetAlternativeUpdate(sqlQuery);
|
|
|
215 default : return sqlQuery;
|
|
|
216 }
|
|
|
217 }
|
|
|
218
|
|
|
219 protected override void BuildFromClause(StringBuilder sb)
|
|
|
220 {
|
|
|
221 if (!SqlQuery.IsUpdate)
|
|
|
222 base.BuildFromClause(sb);
|
|
|
223 }
|
|
|
224
|
|
|
225 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
226 {
|
|
|
227 var wrap = false;
|
|
|
228
|
|
|
229 if (expr.SystemType == typeof(bool))
|
|
|
230 {
|
|
|
231 if (expr is SqlQuery.SearchCondition)
|
|
|
232 wrap = true;
|
|
|
233 else
|
|
|
234 {
|
|
|
235 var ex = expr as SqlExpression;
|
|
|
236 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
|
|
|
237 }
|
|
|
238 }
|
|
|
239
|
|
|
240 if (wrap) sb.Append("CASE WHEN ");
|
|
|
241 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
|
|
|
242 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
|
|
|
243 }
|
|
|
244
|
|
|
245 public static bool QuoteIdentifiers = false;
|
|
|
246
|
|
|
247 public override object Convert(object value, ConvertType convertType)
|
|
|
248 {
|
|
|
249 switch (convertType)
|
|
|
250 {
|
|
|
251 case ConvertType.NameToQueryField:
|
|
|
252 case ConvertType.NameToQueryTable:
|
|
|
253 if (QuoteIdentifiers)
|
|
|
254 {
|
|
|
255 string name = value.ToString();
|
|
|
256
|
|
|
257 if (name.Length > 0 && name[0] == '"')
|
|
|
258 return value;
|
|
|
259
|
|
|
260 return '"' + name + '"';
|
|
|
261 }
|
|
|
262
|
|
|
263 break;
|
|
|
264
|
|
|
265 case ConvertType.NameToQueryParameter:
|
|
|
266 case ConvertType.NameToCommandParameter:
|
|
|
267 case ConvertType.NameToSprocParameter:
|
|
|
268 return "@" + value;
|
|
|
269
|
|
|
270 case ConvertType.SprocParameterToName:
|
|
|
271 if (value != null)
|
|
|
272 {
|
|
|
273 string str = value.ToString();
|
|
|
274 return str.Length > 0 && str[0] == '@' ? str.Substring(1) : str;
|
|
|
275 }
|
|
|
276
|
|
|
277 break;
|
|
|
278 }
|
|
|
279
|
|
|
280 return value;
|
|
|
281 }
|
|
|
282
|
|
|
283 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
284 {
|
|
|
285 BuildInsertOrUpdateQueryAsMerge(sb, "FROM rdb$database");
|
|
|
286 }
|
|
|
287
|
|
|
288 #region IMappingSchemaProvider Members
|
|
|
289
|
|
|
290 readonly FirebirdMappingSchema _mappingSchema = new FirebirdMappingSchema();
|
|
|
291
|
|
|
292 MappingSchema IMappingSchemaProvider.MappingSchema
|
|
|
293 {
|
|
|
294 get { return _mappingSchema; }
|
|
|
295 }
|
|
|
296
|
|
|
297 #endregion
|
|
|
298 }
|
|
|
299 }
|
