Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/SQLiteSqlProvider.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.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 }