|
0
|
1 using System;
|
|
|
2 using System.Text;
|
|
|
3
|
|
|
4 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
5 {
|
|
|
6 using DataProvider;
|
|
|
7 using Reflection;
|
|
|
8
|
|
|
9 public class SQLiteSqlProvider : BasicSqlProvider
|
|
|
10 {
|
|
|
11 public override int CommandCount(SqlQuery sqlQuery)
|
|
|
12 {
|
|
|
13 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
|
|
|
14 }
|
|
|
15
|
|
|
16 protected override void BuildCommand(int commandNumber, StringBuilder sb)
|
|
|
17 {
|
|
|
18 sb.AppendLine("SELECT last_insert_rowid()");
|
|
|
19 }
|
|
|
20
|
|
|
21 protected override ISqlProvider CreateSqlProvider()
|
|
|
22 {
|
|
|
23 return new SQLiteSqlProvider();
|
|
|
24 }
|
|
|
25
|
|
|
26 protected override string LimitFormat { get { return "LIMIT {0}"; } }
|
|
|
27 protected override string OffsetFormat { get { return "OFFSET {0}"; } }
|
|
|
28
|
|
|
29 public override bool IsSkipSupported { get { return SqlQuery.Select.TakeValue != null; } }
|
|
|
30 public override bool IsNestedJoinSupported { get { return false; } }
|
|
|
31 public override bool IsInsertOrUpdateSupported { get { return false; } }
|
|
|
32
|
|
|
33 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
34 {
|
|
|
35 expr = base.ConvertExpression(expr);
|
|
|
36
|
|
|
37 if (expr is SqlBinaryExpression)
|
|
|
38 {
|
|
|
39 var be = (SqlBinaryExpression)expr;
|
|
|
40
|
|
|
41 switch (be.Operation)
|
|
|
42 {
|
|
|
43 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
|
|
|
44 case "^": // (a + b) - (a & b) * 2
|
|
|
45 return Sub(
|
|
|
46 Add(be.Expr1, be.Expr2, be.SystemType),
|
|
|
47 Mul(new SqlBinaryExpression(be.SystemType, be.Expr1, "&", be.Expr2), 2), be.SystemType);
|
|
|
48 }
|
|
|
49 }
|
|
|
50 else if (expr is SqlFunction)
|
|
|
51 {
|
|
|
52 var func = (SqlFunction) expr;
|
|
|
53
|
|
|
54 switch (func.Name)
|
|
|
55 {
|
|
|
56 case "Space" : return new SqlFunction(func.SystemType, "PadR", new SqlValue(" "), func.Parameters[0]);
|
|
|
57 case "Convert" :
|
|
|
58 {
|
|
|
59 var ftype = TypeHelper.GetUnderlyingType(func.SystemType);
|
|
|
60
|
|
|
61 if (ftype == typeof(bool))
|
|
|
62 {
|
|
|
63 var ex = AlternativeConvertToBoolean(func, 1);
|
|
|
64 if (ex != null)
|
|
|
65 return ex;
|
|
|
66 }
|
|
|
67
|
|
|
68 if (ftype == typeof(DateTime) || ftype == typeof(DateTimeOffset))
|
|
|
69 {
|
|
|
70 if (IsDateDataType(func.Parameters[0], "Date"))
|
|
|
71 return new SqlFunction(func.SystemType, "Date", func.Parameters[1]);
|
|
|
72 return new SqlFunction(func.SystemType, "DateTime", func.Parameters[1]);
|
|
|
73 }
|
|
|
74
|
|
|
75 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, func.Parameters[1], func.Parameters[0]);
|
|
|
76 }
|
|
|
77 }
|
|
|
78 }
|
|
|
79 else if (expr is SqlExpression)
|
|
|
80 {
|
|
|
81 var e = (SqlExpression)expr;
|
|
|
82
|
|
|
83 if (e.Expr.StartsWith("Cast(StrFTime(Quarter"))
|
|
|
84 return Inc(Div(Dec(new SqlExpression(e.SystemType, e.Expr.Replace("Cast(StrFTime(Quarter", "Cast(StrFTime('%m'"), e.Parameters)), 3));
|
|
|
85
|
|
|
86 if (e.Expr.StartsWith("Cast(StrFTime('%w'"))
|
|
|
87 return Inc(new SqlExpression(e.SystemType, e.Expr.Replace("Cast(StrFTime('%w'", "Cast(strFTime('%w'"), e.Parameters));
|
|
|
88
|
|
|
89 if (e.Expr.StartsWith("Cast(StrFTime('%f'"))
|
|
|
90 return new SqlExpression(e.SystemType, "Cast(strFTime('%f', {0}) * 1000 as int) % 1000", Precedence.Multiplicative, e.Parameters);
|
|
|
91
|
|
|
92 if (e.Expr.StartsWith("DateTime"))
|
|
|
93 {
|
|
|
94 if (e.Expr.EndsWith("Quarter')"))
|
|
|
95 return new SqlExpression(e.SystemType, "DateTime({1}, '{0} Month')", Precedence.Primary, Mul(e.Parameters[0], 3), e.Parameters[1]);
|
|
|
96
|
|
|
97 if (e.Expr.EndsWith("Week')"))
|
|
|
98 return new SqlExpression(e.SystemType, "DateTime({1}, '{0} Day')", Precedence.Primary, Mul(e.Parameters[0], 7), e.Parameters[1]);
|
|
|
99 }
|
|
|
100 }
|
|
|
101
|
|
|
102 return expr;
|
|
|
103 }
|
|
|
104
|
|
|
105 public override SqlQuery Finalize(SqlQuery sqlQuery)
|
|
|
106 {
|
|
|
107 sqlQuery = base.Finalize(sqlQuery);
|
|
|
108
|
|
|
109 switch (sqlQuery.QueryType)
|
|
|
110 {
|
|
|
111 case QueryType.Delete :
|
|
|
112 sqlQuery = GetAlternativeDelete(base.Finalize(sqlQuery));
|
|
|
113 sqlQuery.From.Tables[0].Alias = "$";
|
|
|
114 break;
|
|
|
115
|
|
|
116 case QueryType.Update :
|
|
|
117 sqlQuery = GetAlternativeUpdate(sqlQuery);
|
|
|
118 break;
|
|
|
119 }
|
|
|
120
|
|
|
121 return sqlQuery;
|
|
|
122 }
|
|
|
123
|
|
|
124 protected override void BuildFromClause(StringBuilder sb)
|
|
|
125 {
|
|
|
126 if (!SqlQuery.IsUpdate)
|
|
|
127 base.BuildFromClause(sb);
|
|
|
128 }
|
|
|
129
|
|
|
130 public override void BuildValue(StringBuilder sb, object value)
|
|
|
131 {
|
|
|
132 if (value is Guid)
|
|
|
133 {
|
|
|
134 var s = ((Guid)value).ToString("N");
|
|
|
135
|
|
|
136 sb
|
|
|
137 .Append("Cast(x'")
|
|
|
138 .Append(s.Substring( 6, 2))
|
|
|
139 .Append(s.Substring( 4, 2))
|
|
|
140 .Append(s.Substring( 2, 2))
|
|
|
141 .Append(s.Substring( 0, 2))
|
|
|
142 .Append(s.Substring(10, 2))
|
|
|
143 .Append(s.Substring( 8, 2))
|
|
|
144 .Append(s.Substring(14, 2))
|
|
|
145 .Append(s.Substring(12, 2))
|
|
|
146 .Append(s.Substring(16, 16))
|
|
|
147 .Append("' as blob)");
|
|
|
148 }
|
|
|
149 else
|
|
|
150 base.BuildValue(sb, value);
|
|
|
151 }
|
|
|
152
|
|
|
153 protected override void BuildDateTime(StringBuilder sb, object value)
|
|
|
154 {
|
|
|
155 sb
|
|
|
156 .Append(string.Format("'{0:yyyy-MM-dd HH:mm:ss.fff}", value).TrimEnd('0'))
|
|
|
157 .Append('\'');
|
|
|
158 }
|
|
|
159
|
|
|
160 public override object Convert(object value, ConvertType convertType)
|
|
|
161 {
|
|
|
162 switch (convertType)
|
|
|
163 {
|
|
|
164 case ConvertType.NameToQueryParameter:
|
|
|
165 case ConvertType.NameToCommandParameter:
|
|
|
166 case ConvertType.NameToSprocParameter:
|
|
|
167 return "@" + value;
|
|
|
168
|
|
|
169 case ConvertType.NameToQueryField:
|
|
|
170 case ConvertType.NameToQueryFieldAlias:
|
|
|
171 case ConvertType.NameToQueryTableAlias:
|
|
|
172 {
|
|
|
173 var name = value.ToString();
|
|
|
174
|
|
|
175 if (name.Length > 0 && name[0] == '[')
|
|
|
176 return value;
|
|
|
177 }
|
|
|
178
|
|
|
179 return "[" + value + "]";
|
|
|
180
|
|
|
181 case ConvertType.NameToDatabase:
|
|
|
182 case ConvertType.NameToOwner:
|
|
|
183 case ConvertType.NameToQueryTable:
|
|
|
184 {
|
|
|
185 var name = value.ToString();
|
|
|
186
|
|
|
187 if (name.Length > 0 && name[0] == '[')
|
|
|
188 return value;
|
|
|
189
|
|
|
190 if (name.IndexOf('.') > 0)
|
|
|
191 value = string.Join("].[", name.Split('.'));
|
|
|
192 }
|
|
|
193
|
|
|
194 return "[" + value + "]";
|
|
|
195
|
|
|
196 case ConvertType.SprocParameterToName:
|
|
|
197 {
|
|
|
198 var name = (string)value;
|
|
|
199 return name.Length > 0 && name[0] == '@'? name.Substring(1): name;
|
|
|
200 }
|
|
|
201 }
|
|
|
202
|
|
|
203 return value;
|
|
|
204 }
|
|
|
205 }
|
|
|
206 }
|
