Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/PostgreSQLSqlProvider.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.Data;
3 using System.Text;
4
5 namespace BLToolkit.Data.Sql.SqlProvider
6 {
7 using DataProvider;
8 using Reflection;
9
10 public class PostgreSQLSqlProvider : BasicSqlProvider
11 {
12 public override bool IsInsertOrUpdateSupported { get { return false; } }
13
14 public override int CommandCount(SqlQuery sqlQuery)
15 {
16 return sqlQuery.IsInsert && sqlQuery.Insert.WithIdentity ? 2 : 1;
17 }
18
19 protected override void BuildCommand(int commandNumber, StringBuilder sb)
20 {
21 var into = SqlQuery.Insert.Into;
22 var attr = GetSequenceNameAttribute(into, false);
23 var name =
24 attr != null ?
25 attr.SequenceName :
26 Convert(
27 string.Format("{0}_{1}_seq", into.PhysicalName, into.GetIdentityField().PhysicalName),
28 ConvertType.NameToQueryField);
29
30 AppendIndent(sb)
31 .Append("SELECT currval('")
32 .Append(name)
33 .AppendLine("')");
34 }
35
36 protected override ISqlProvider CreateSqlProvider()
37 {
38 return new PostgreSQLSqlProvider();
39 }
40
41 protected override string LimitFormat { get { return "LIMIT {0}"; } }
42 protected override string OffsetFormat { get { return "OFFSET {0} "; } }
43
44 public override ISqlExpression ConvertExpression(ISqlExpression expr)
45 {
46 expr = base.ConvertExpression(expr);
47
48 if (expr is SqlBinaryExpression)
49 {
50 var be = (SqlBinaryExpression)expr;
51
52 switch (be.Operation)
53 {
54 case "^": return new SqlBinaryExpression(be.SystemType, be.Expr1, "#", be.Expr2);
55 case "+": return be.SystemType == typeof(string)? new SqlBinaryExpression(be.SystemType, be.Expr1, "||", be.Expr2, be.Precedence): expr;
56 }
57 }
58 else if (expr is SqlFunction)
59 {
60 var func = (SqlFunction) expr;
61
62 switch (func.Name)
63 {
64 case "Convert" :
65 if (TypeHelper.GetUnderlyingType(func.SystemType) == typeof(bool))
66 {
67 var ex = AlternativeConvertToBoolean(func, 1);
68 if (ex != null)
69 return ex;
70 }
71
72 return new SqlExpression(func.SystemType, "Cast({0} as {1})", Precedence.Primary, FloorBeforeConvert(func), func.Parameters[0]);
73
74 case "CharIndex" :
75 return func.Parameters.Length == 2?
76 new SqlExpression(func.SystemType, "Position({0} in {1})", Precedence.Primary, func.Parameters[0], func.Parameters[1]):
77 Add<int>(
78 new SqlExpression(func.SystemType, "Position({0} in {1})", Precedence.Primary, func.Parameters[0],
79 ConvertExpression(new SqlFunction(typeof(string), "Substring",
80 func.Parameters[1],
81 func.Parameters[2],
82 Sub<int>(ConvertExpression(new SqlFunction(typeof(int), "Length", func.Parameters[1])), func.Parameters[2])))),
83 Sub(func.Parameters[2], 1));
84 }
85 }
86 else if (expr is SqlExpression)
87 {
88 var e = (SqlExpression)expr;
89
90 if (e.Expr.StartsWith("Extract(DOW"))
91 return Inc(new SqlExpression(expr.SystemType, e.Expr.Replace("Extract(DOW", "Extract(Dow"), e.Parameters));
92
93 if (e.Expr.StartsWith("Extract(Millisecond"))
94 return new SqlExpression(expr.SystemType, "Cast(To_Char({0}, 'MS') as int)", e.Parameters);
95 }
96
97 return expr;
98 }
99
100 public override void BuildValue(StringBuilder sb, object value)
101 {
102 if (value is bool)
103 sb.Append(value);
104 else
105 base.BuildValue(sb, value);
106 }
107
108 protected override void BuildDataType(StringBuilder sb, SqlDataType type)
109 {
110 switch (type.SqlDbType)
111 {
112 case SqlDbType.TinyInt : sb.Append("SmallInt"); break;
113 case SqlDbType.Money : sb.Append("Decimal(19,4)"); break;
114 case SqlDbType.SmallMoney : sb.Append("Decimal(10,4)"); break;
115 #if !MONO
116 case SqlDbType.DateTime2 :
117 #endif
118 case SqlDbType.SmallDateTime :
119 case SqlDbType.DateTime : sb.Append("TimeStamp"); break;
120 case SqlDbType.Bit : sb.Append("Boolean"); break;
121 case SqlDbType.NVarChar :
122 sb.Append("VarChar");
123 if (type.Length > 0)
124 sb.Append('(').Append(type.Length).Append(')');
125 break;
126 default : base.BuildDataType(sb, type); break;
127 }
128 }
129
130 public override SqlQuery Finalize(SqlQuery sqlQuery)
131 {
132 CheckAliases(sqlQuery, int.MaxValue);
133
134 sqlQuery = base.Finalize(sqlQuery);
135
136 switch (sqlQuery.QueryType)
137 {
138 case QueryType.Delete : return GetAlternativeDelete(sqlQuery);
139 case QueryType.Update : return GetAlternativeUpdate(sqlQuery);
140 default : return sqlQuery;
141 }
142 }
143
144 protected override void BuildFromClause(StringBuilder sb)
145 {
146 if (!SqlQuery.IsUpdate)
147 base.BuildFromClause(sb);
148 }
149
150 public static bool QuoteIdentifiers;
151
152 public override object Convert(object value, ConvertType convertType)
153 {
154 switch (convertType)
155 {
156 case ConvertType.NameToQueryField:
157 case ConvertType.NameToQueryFieldAlias:
158 case ConvertType.NameToQueryTable:
159 case ConvertType.NameToQueryTableAlias:
160 if (QuoteIdentifiers)
161 {
162 var name = value.ToString();
163
164 if (name.Length > 0 && name[0] == '"')
165 return value;
166
167 return '"' + name + '"';
168 }
169
170 break;
171
172 case ConvertType.NameToQueryParameter:
173 case ConvertType.NameToCommandParameter:
174 case ConvertType.NameToSprocParameter:
175 return ":" + value;
176
177 case ConvertType.SprocParameterToName:
178 if (value != null)
179 {
180 var str = value.ToString();
181 return (str.Length > 0 && str[0] == ':')? str.Substring(1): str;
182 }
183
184 break;
185 }
186
187 return value;
188 }
189
190 public override ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning)
191 {
192 if (table.SequenceAttributes != null)
193 {
194 var attr = GetSequenceNameAttribute(table, false);
195
196 if (attr != null)
197 return new SqlExpression("nextval('" + attr.SequenceName+"')", Precedence.Primary);
198 }
199
200 return base.GetIdentityExpression(table, identityField, forReturning);
201 }
202
203 //protected override void BuildInsertOrUpdateQuery(StringBuilder sb)
204 //{
205 // BuildInsertOrUpdateQueryAsMerge(sb, null);
206 //}
207 }
208 }