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