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