|
0
|
1 using System;
|
|
|
2 using System.Data;
|
|
|
3 using System.Text;
|
|
|
4
|
|
|
5 namespace BLToolkit.Data.Sql.SqlProvider
|
|
|
6 {
|
|
|
7 using DataProvider;
|
|
|
8
|
|
|
9 public class SybaseSqlProvider : BasicSqlProvider
|
|
|
10 {
|
|
|
11 public SybaseSqlProvider()
|
|
|
12 {
|
|
|
13 }
|
|
|
14
|
|
|
15 protected override void BuildGetIdentity(StringBuilder sb)
|
|
|
16 {
|
|
|
17 sb
|
|
|
18 .AppendLine()
|
|
|
19 .AppendLine("SELECT @@IDENTITY");
|
|
|
20 }
|
|
|
21
|
|
|
22 protected override string FirstFormat { get { return "TOP {0}"; } }
|
|
|
23
|
|
|
24 public override bool IsSkipSupported { get { return false; } }
|
|
|
25 public override bool TakeAcceptsParameter { get { return false; } }
|
|
|
26 public override bool IsSubQueryTakeSupported { get { return false; } }
|
|
|
27 public override bool IsCountSubQuerySupported { get { return false; } }
|
|
|
28 public override bool CanCombineParameters { get { return false; } }
|
|
|
29
|
|
|
30 public override ISqlExpression ConvertExpression(ISqlExpression expr)
|
|
|
31 {
|
|
|
32 expr = base.ConvertExpression(expr);
|
|
|
33
|
|
|
34 if (expr is SqlFunction)
|
|
|
35 {
|
|
|
36 var func = (SqlFunction) expr;
|
|
|
37
|
|
|
38 switch (func.Name)
|
|
|
39 {
|
|
|
40 case "CharIndex" :
|
|
|
41 if (func.Parameters.Length == 3)
|
|
|
42 return Add<int>(
|
|
|
43 ConvertExpression(new SqlFunction(func.SystemType, "CharIndex",
|
|
|
44 func.Parameters[0],
|
|
|
45 ConvertExpression(new SqlFunction(typeof(string), "Substring",
|
|
|
46 func.Parameters[1],
|
|
|
47 func.Parameters[2], new SqlFunction(typeof(int), "Len", func.Parameters[1]))))),
|
|
|
48 Sub(func.Parameters[2], 1));
|
|
|
49 break;
|
|
|
50
|
|
|
51 case "Stuff" :
|
|
|
52 if (func.Parameters[3] is SqlValue)
|
|
|
53 {
|
|
|
54 var value = (SqlValue)func.Parameters[3];
|
|
|
55
|
|
|
56 if (value.Value is string && string.IsNullOrEmpty((string)value.Value))
|
|
|
57 return new SqlFunction(
|
|
|
58 func.SystemType,
|
|
|
59 func.Name,
|
|
|
60 func.Precedence,
|
|
|
61 func.Parameters[0],
|
|
|
62 func.Parameters[1],
|
|
|
63 func.Parameters[1],
|
|
|
64 new SqlValue(null));
|
|
|
65 }
|
|
|
66
|
|
|
67 break;
|
|
|
68 }
|
|
|
69 }
|
|
|
70
|
|
|
71 return expr;
|
|
|
72 }
|
|
|
73
|
|
|
74 protected override void BuildFunction(StringBuilder sb, SqlFunction func)
|
|
|
75 {
|
|
|
76 func = ConvertFunctionParameters(func);
|
|
|
77 base.BuildFunction(sb, func);
|
|
|
78 }
|
|
|
79
|
|
|
80 private bool _isSelect;
|
|
|
81 readonly bool _skipAliases;
|
|
|
82
|
|
|
83 SybaseSqlProvider(bool skipAliases)
|
|
|
84 {
|
|
|
85 _skipAliases = skipAliases;
|
|
|
86 }
|
|
|
87
|
|
|
88 protected override void BuildSelectClause(StringBuilder sb)
|
|
|
89 {
|
|
|
90 _isSelect = true;
|
|
|
91 base.BuildSelectClause(sb);
|
|
|
92 _isSelect = false;
|
|
|
93 }
|
|
|
94
|
|
|
95 protected override void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias)
|
|
|
96 {
|
|
|
97 var wrap = false;
|
|
|
98
|
|
|
99 if (expr.SystemType == typeof(bool))
|
|
|
100 {
|
|
|
101 if (expr is SqlQuery.SearchCondition)
|
|
|
102 wrap = true;
|
|
|
103 else
|
|
|
104 {
|
|
|
105 var ex = expr as SqlExpression;
|
|
|
106 wrap = ex != null && ex.Expr == "{0}" && ex.Parameters.Length == 1 && ex.Parameters[0] is SqlQuery.SearchCondition;
|
|
|
107 }
|
|
|
108 }
|
|
|
109
|
|
|
110 if (wrap) sb.Append("CASE WHEN ");
|
|
|
111 base.BuildColumnExpression(sb, expr, alias, ref addAlias);
|
|
|
112 if (wrap) sb.Append(" THEN 1 ELSE 0 END");
|
|
|
113
|
|
|
114 if (_skipAliases) addAlias = false;
|
|
|
115 }
|
|
|
116
|
|
|
117 protected override ISqlProvider CreateSqlProvider()
|
|
|
118 {
|
|
|
119 return new SybaseSqlProvider(_isSelect);
|
|
|
120 }
|
|
|
121
|
|
|
122 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
|
|
|
123 {
|
|
|
124 switch (type.SqlDbType)
|
|
|
125 {
|
|
|
126 #if !MONO
|
|
|
127 case SqlDbType.DateTime2 : sb.Append("DateTime"); break;
|
|
|
128 #endif
|
|
|
129 default : base.BuildDataType(sb, type); break;
|
|
|
130 }
|
|
|
131 }
|
|
|
132
|
|
|
133 protected override void BuildDeleteClause(StringBuilder sb)
|
|
|
134 {
|
|
|
135 AppendIndent(sb);
|
|
|
136 sb.Append("DELETE FROM ");
|
|
|
137 // BuildTableName(sb, SqlQuery.From.Tables[0], true, false);
|
|
|
138
|
|
|
139 ISqlTableSource table;
|
|
|
140 ISqlTableSource source;
|
|
|
141
|
|
|
142 if (SqlQuery.Delete.Table != null)
|
|
|
143 table = source = SqlQuery.Delete.Table;
|
|
|
144 else
|
|
|
145 {
|
|
|
146 table = SqlQuery.From.Tables[0];
|
|
|
147 source = SqlQuery.From.Tables[0].Source;
|
|
|
148 }
|
|
|
149
|
|
|
150 var alias = GetTableAlias(table);
|
|
|
151 BuildPhysicalTable(sb, source, alias);
|
|
|
152
|
|
|
153 sb.AppendLine();
|
|
|
154 }
|
|
|
155
|
|
|
156 protected override void BuildUpdateTableName(StringBuilder sb)
|
|
|
157 {
|
|
|
158 if (SqlQuery.Update.Table != null && SqlQuery.Update.Table != SqlQuery.From.Tables[0].Source)
|
|
|
159 BuildPhysicalTable(sb, SqlQuery.Update.Table, null);
|
|
|
160 else
|
|
|
161 BuildTableName(sb, SqlQuery.From.Tables[0], true, false);
|
|
|
162 }
|
|
|
163
|
|
|
164 protected override void BuildString(StringBuilder sb, string value)
|
|
|
165 {
|
|
|
166 foreach (var ch in value)
|
|
|
167 {
|
|
|
168 if (ch > 127)
|
|
|
169 {
|
|
|
170 sb.Append("N");
|
|
|
171 break;
|
|
|
172 }
|
|
|
173 }
|
|
|
174
|
|
|
175 base.BuildString(sb, value);
|
|
|
176 }
|
|
|
177
|
|
|
178 protected override void BuildChar(StringBuilder sb, char value)
|
|
|
179 {
|
|
|
180 if (value > 127)
|
|
|
181 sb.Append("N");
|
|
|
182
|
|
|
183 base.BuildChar(sb, value);
|
|
|
184 }
|
|
|
185
|
|
|
186 public override object Convert(object value, ConvertType convertType)
|
|
|
187 {
|
|
|
188 switch (convertType)
|
|
|
189 {
|
|
|
190 case ConvertType.NameToQueryParameter:
|
|
|
191 case ConvertType.NameToCommandParameter:
|
|
|
192 case ConvertType.NameToSprocParameter:
|
|
|
193 {
|
|
|
194 var name = "@" + value;
|
|
|
195
|
|
|
196 if (name.Length > 27)
|
|
|
197 name = name.Substring(0, 27);
|
|
|
198
|
|
|
199 return name;
|
|
|
200 }
|
|
|
201
|
|
|
202 case ConvertType.NameToQueryField:
|
|
|
203 case ConvertType.NameToQueryFieldAlias:
|
|
|
204 case ConvertType.NameToQueryTableAlias:
|
|
|
205 {
|
|
|
206 var name = value.ToString();
|
|
|
207
|
|
|
208 if (name.Length > 28 || name.Length > 0 && name[0] == '[')
|
|
|
209 return value;
|
|
|
210 }
|
|
|
211
|
|
|
212 return "[" + value + "]";
|
|
|
213
|
|
|
214 case ConvertType.NameToDatabase:
|
|
|
215 case ConvertType.NameToOwner:
|
|
|
216 case ConvertType.NameToQueryTable:
|
|
|
217 {
|
|
|
218 var name = value.ToString();
|
|
|
219
|
|
|
220 if (name.Length > 28 || name.Length > 0 && (name[0] == '[' || name[0] == '#'))
|
|
|
221 return value;
|
|
|
222
|
|
|
223 if (name.IndexOf('.') > 0)
|
|
|
224 value = string.Join("].[", name.Split('.'));
|
|
|
225 }
|
|
|
226
|
|
|
227 return "[" + value + "]";
|
|
|
228
|
|
|
229 case ConvertType.SprocParameterToName:
|
|
|
230 if (value != null)
|
|
|
231 {
|
|
|
232 var str = value.ToString();
|
|
|
233 return str.Length > 0 && str[0] == '@'? str.Substring(1): str;
|
|
|
234 }
|
|
|
235
|
|
|
236 break;
|
|
|
237 }
|
|
|
238
|
|
|
239 return value;
|
|
|
240 }
|
|
|
241
|
|
|
242 protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
|
|
|
243 {
|
|
|
244 BuildInsertOrUpdateQueryAsUpdateInsert(sb);
|
|
|
245 }
|
|
|
246
|
|
|
247 protected override void BuildEmptyInsert(StringBuilder sb)
|
|
|
248 {
|
|
|
249 sb.AppendLine("VALUES ()");
|
|
|
250 }
|
|
|
251 }
|
|
|
252 }
|
