Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/MySqlSqlProvider.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.Collections.Generic;
3 using System.Data;
4 using System.Text;
5
6 namespace BLToolkit.Data.Sql.SqlProvider
7 {
8 using DataProvider;
9 using Reflection;
10
11 public class MySqlSqlProvider : BasicSqlProvider
12 {
13 public override int CommandCount(SqlQuery sqlQuery)
14 {
15 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
16 }
17
18 protected override void BuildCommand(int commandNumber, StringBuilder sb)
19 {
20 sb.AppendLine("SELECT LAST_INSERT_ID()");
21 }
22
23 protected override ISqlProvider CreateSqlProvider()
24 {
25 return new MySqlSqlProvider();
26 }
27
28 protected override string LimitFormat { get { return "LIMIT {0}"; } }
29
30 public override bool IsNestedJoinParenthesisRequired { get { return true; } }
31
32 protected override void BuildOffsetLimit(StringBuilder sb)
33 {
34 if (SqlQuery.Select.SkipValue == null)
35 base.BuildOffsetLimit(sb);
36 else
37 {
38 AppendIndent(sb)
39 .AppendFormat(
40 "LIMIT {0},{1}",
41 BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue),
42 SqlQuery.Select.TakeValue == null ?
43 long.MaxValue.ToString() :
44 BuildExpression(new StringBuilder(), SqlQuery.Select.TakeValue).ToString())
45 .AppendLine();
46 }
47 }
48
49 public override ISqlExpression ConvertExpression(ISqlExpression expr)
50 {
51 expr = base.ConvertExpression(expr);
52
53 if (expr is SqlBinaryExpression)
54 {
55 var be = (SqlBinaryExpression)expr;
56
57 switch (be.Operation)
58 {
59 case "+":
60 if (be.SystemType == typeof(string))
61 {
62 if (be.Expr1 is SqlFunction)
63 {
64 var func = (SqlFunction)be.Expr1;
65
66 if (func.Name == "Concat")
67 {
68 var list = new List<ISqlExpression>(func.Parameters) { be.Expr2 };
69 return new SqlFunction(be.SystemType, "Concat", list.ToArray());
70 }
71 }
72
73 return new SqlFunction(be.SystemType, "Concat", be.Expr1, be.Expr2);
74 }
75
76 break;
77 }
78 }
79 else if (expr is SqlFunction)
80 {
81 var func = (SqlFunction) expr;
82
83 switch (func.Name)
84 {
85 case "Convert" :
86 var ftype = TypeHelper.GetUnderlyingType(func.SystemType);
87
88 if (ftype == typeof(bool))
89 {
90 var ex = AlternativeConvertToBoolean(func, 1);
91 if (ex != null)
92 return ex;
93 }
94
95 if ((ftype == typeof(double) || ftype == typeof(float)) && TypeHelper.GetUnderlyingType(func.Parameters[1].SystemType) == typeof(decimal))
96 return func.Parameters[1];
97
98 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, FloorBeforeConvert(func), func.Parameters[0]);
99 }
100 }
101 else if (expr is SqlExpression)
102 {
103 var e = (SqlExpression)expr;
104
105 if (e.Expr.StartsWith("Extract(DayOfYear"))
106 return new SqlFunction(e.SystemType, "DayOfYear", e.Parameters);
107
108 if (e.Expr.StartsWith("Extract(WeekDay"))
109 return Inc(
110 new SqlFunction(e.SystemType,
111 "WeekDay",
112 new SqlFunction(
113 null,
114 "Date_Add",
115 e.Parameters[0],
116 new SqlExpression(null, "interval 1 day"))));
117 }
118
119 return expr;
120 }
121
122 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
123 {
124 switch (type.SqlDbType)
125 {
126 case SqlDbType.Int :
127 case SqlDbType.SmallInt : sb.Append("Signed"); break;
128 case SqlDbType.TinyInt : sb.Append("Unsigned"); break;
129 case SqlDbType.Money : sb.Append("Decimal(19,4)"); break;
130 case SqlDbType.SmallMoney : sb.Append("Decimal(10,4)"); break;
131 #if !MONO
132 case SqlDbType.DateTime2 :
133 #endif
134 case SqlDbType.SmallDateTime : sb.Append("DateTime"); break;
135 case SqlDbType.Bit : sb.Append("Boolean"); break;
136 case SqlDbType.Float :
137 case SqlDbType.Real : base.BuildDataType(sb, SqlDataType.Decimal); break;
138 case SqlDbType.VarChar :
139 case SqlDbType.NVarChar :
140 sb.Append("Char");
141 if (type.Length > 0)
142 sb.Append('(').Append(type.Length).Append(')');
143 break;
144 default: base.BuildDataType(sb, type); break;
145 }
146 }
147
148 protected override void BuildDeleteClause(StringBuilder sb)
149 {
150 var table = SqlQuery.Delete.Table != null ?
151 (SqlQuery.From.FindTableSource(SqlQuery.Delete.Table) ?? SqlQuery.Delete.Table) :
152 SqlQuery.From.Tables[0];
153
154 AppendIndent(sb)
155 .Append("DELETE ")
156 .Append(Convert(GetTableAlias(table), ConvertType.NameToQueryTableAlias))
157 .AppendLine();
158 }
159
160 protected override void BuildUpdateClause(StringBuilder sb)
161 {
162 base.BuildFromClause(sb);
163 sb.Remove(0, 4).Insert(0, "UPDATE");
164 base.BuildUpdateSet(sb);
165 }
166
167 protected override void BuildFromClause(StringBuilder sb)
168 {
169 if (!SqlQuery.IsUpdate)
170 base.BuildFromClause(sb);
171 }
172
173 public static char ParameterSymbol { get; set; }
174 public static bool TryConvertParameterSymbol { get; set; }
175
176 private static string _commandParameterPrefix = "";
177 public static string CommandParameterPrefix
178 {
179 get { return _commandParameterPrefix; }
180 set { _commandParameterPrefix = string.IsNullOrEmpty(value) ? string.Empty : value; }
181 }
182
183 private static string _sprocParameterPrefix = "";
184 public static string SprocParameterPrefix
185 {
186 get { return _sprocParameterPrefix; }
187 set { _sprocParameterPrefix = string.IsNullOrEmpty(value) ? string.Empty : value; }
188 }
189
190 private static List<char> _convertParameterSymbols;
191 public static List<char> ConvertParameterSymbols
192 {
193 get { return _convertParameterSymbols; }
194 set { _convertParameterSymbols = value ?? new List<char>(); }
195 }
196
197 public override object Convert(object value, ConvertType convertType)
198 {
199 if (value == null)
200 throw new ArgumentNullException("value");
201
202 switch (convertType)
203 {
204 case ConvertType.NameToQueryParameter:
205 return ParameterSymbol + value.ToString();
206
207 case ConvertType.NameToCommandParameter:
208 return ParameterSymbol + CommandParameterPrefix + value;
209
210 case ConvertType.NameToSprocParameter:
211 {
212 var valueStr = value.ToString();
213
214 if(string.IsNullOrEmpty(valueStr))
215 throw new ArgumentException("Argument 'value' must represent parameter name.");
216
217 if (valueStr[0] == ParameterSymbol)
218 valueStr = valueStr.Substring(1);
219
220 if (valueStr.StartsWith(SprocParameterPrefix, StringComparison.Ordinal))
221 valueStr = valueStr.Substring(SprocParameterPrefix.Length);
222
223 return ParameterSymbol + SprocParameterPrefix + valueStr;
224 }
225
226 case ConvertType.SprocParameterToName:
227 {
228 var str = value.ToString();
229 str = (str.Length > 0 && (str[0] == ParameterSymbol || (TryConvertParameterSymbol && ConvertParameterSymbols.Contains(str[0])))) ? str.Substring(1) : str;
230
231 if (!string.IsNullOrEmpty(SprocParameterPrefix) && str.StartsWith(SprocParameterPrefix))
232 str = str.Substring(SprocParameterPrefix.Length);
233
234 return str;
235 }
236 case ConvertType.NameToQueryField:
237 case ConvertType.NameToQueryFieldAlias:
238 case ConvertType.NameToQueryTableAlias:
239 {
240 var name = value.ToString();
241 if (name.Length > 0 && name[0] == '`')
242 return value;
243 }
244 return "`" + value + "`";
245 case ConvertType.NameToDatabase:
246 case ConvertType.NameToOwner:
247 case ConvertType.NameToQueryTable:
248 {
249 var name = value.ToString();
250 if (name.Length > 0 && name[0] == '`')
251 return value;
252
253 if (name.IndexOf('.') > 0)
254 value = string.Join("`.`", name.Split('.'));
255 }
256 return "`" + value + "`";
257
258 }
259
260 return value;
261 }
262
263 protected override StringBuilder BuildExpression(StringBuilder sb, ISqlExpression expr, bool buildTableName, bool checkParentheses, string alias, ref bool addAlias)
264 {
265 return base.BuildExpression(
266 sb,
267 expr,
268 buildTableName && SqlQuery.QueryType != QueryType.InsertOrUpdate,
269 checkParentheses,
270 alias,
271 ref addAlias);
272 }
273
274 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
275 {
276 BuildInsertQuery(sb);
277 AppendIndent(sb).AppendLine("ON DUPLICATE KEY UPDATE");
278
279 Indent++;
280
281 var first = true;
282
283 foreach (var expr in SqlQuery.Update.Items)
284 {
285 if (!first)
286 sb.Append(',').AppendLine();
287 first = false;
288
289 AppendIndent(sb);
290 BuildExpression(sb, expr.Column, false, true);
291 sb.Append(" = ");
292 BuildExpression(sb, expr.Expression, false, true);
293 }
294
295 Indent--;
296
297 sb.AppendLine();
298 }
299
300 protected override void BuildEmptyInsert(StringBuilder sb)
301 {
302 sb.AppendLine("() VALUES ()");
303 }
304
305 public static bool GenerateOldGuid = false;
306
307 public override void BuildValue(StringBuilder sb, object value)
308 {
309 if (GenerateOldGuid && value is Guid)
310 {
311 var bytes = ((Guid)value).ToByteArray();
312
313 sb.Append("X'").Append(ByteArrayToHex(bytes)).Append('\'');
314 }
315 else
316 base.BuildValue(sb, value);
317 }
318
319 static string ByteArrayToHex(byte[] barray)
320 {
321 var c = new char[barray.Length * 2];
322
323 for (var i = 0; i < barray.Length; ++i)
324 {
325 var b = ((byte)(barray[i] >> 4));
326
327 c[i * 2] = (char)(b > 9 ? b + 0x37 : b + 0x30);
328 b = ((byte)(barray[i] & 0xF));
329 c[i * 2 + 1] = (char)(b > 9 ? b + 0x37 : b + 0x30);
330 }
331
332 return new string(c);
333 }
334
335 protected override void BuildString(StringBuilder sb, string value)
336 {
337 base.BuildString(sb, value.Replace("\\", "\\\\"));
338 }
339
340 protected override void BuildChar(StringBuilder sb, char value)
341 {
342 if (value == '\\')
343 sb.Append("\\\\");
344 else
345 base.BuildChar(sb, value);
346 }
347 }
348 }