root / trunk / org.gvsig.postgresql / org.gvsig.postgresql.provider / src / main / java / org / gvsig / postgresql / dal / PostgreSQLBuilder.java @ 453
History | View | Annotate | Download (11.3 KB)
1 | 362 | jjdelcerro | /**
|
---|---|---|---|
2 | * gvSIG. Desktop Geographic Information System.
|
||
3 | *
|
||
4 | * Copyright (C) 2007-2013 gvSIG Association.
|
||
5 | *
|
||
6 | * This program is free software; you can redistribute it and/or
|
||
7 | * modify it under the terms of the GNU General Public License
|
||
8 | * as published by the Free Software Foundation; either version 3
|
||
9 | * of the License, or (at your option) any later version.
|
||
10 | *
|
||
11 | * This program is distributed in the hope that it will be useful,
|
||
12 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
13 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
14 | * GNU General Public License for more details.
|
||
15 | *
|
||
16 | * You should have received a copy of the GNU General Public License
|
||
17 | * along with this program; if not, write to the Free Software
|
||
18 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
||
19 | * MA 02110-1301, USA.
|
||
20 | *
|
||
21 | * For any additional information, do not hesitate to contact us
|
||
22 | * at info AT gvsig.com, or visit our website www.gvsig.com.
|
||
23 | */
|
||
24 | |||
25 | package org.gvsig.postgresql.dal; |
||
26 | |||
27 | import java.text.MessageFormat; |
||
28 | import java.util.ArrayList; |
||
29 | import java.util.List; |
||
30 | import org.apache.commons.lang3.StringUtils; |
||
31 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
32 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
33 | |||
34 | public class PostgreSQLBuilder extends JDBCSQLBuilderBase { |
||
35 | |||
36 | public PostgreSQLBuilder(JDBCHelper helper) {
|
||
37 | 445 | jjdelcerro | super(helper);
|
38 | 362 | jjdelcerro | |
39 | config.set(SQLConfig.default_schema, "public");
|
||
40 | config.set(SQLConfig.allowAutomaticValues, true);
|
||
41 | config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType());
|
||
42 | config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions());
|
||
43 | config.set(SQLConfig.constant_true, "true");
|
||
44 | config.set(SQLConfig.constant_false, "false");
|
||
45 | |||
46 | config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table); |
||
47 | config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table); |
||
48 | |||
49 | config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"ANALYZE {0}");
|
||
50 | |||
51 | config.set(SQLConfig.ST_GeomFromEWKB, "ST_GeomFromEWKB({0}, {1})");
|
||
52 | config.set(SQLConfig.ST_AsEWKB, "ST_AsEWKB(({0}))");
|
||
53 | config.set(SQLConfig.ST_ExtentAggregate, "ST_Extent({0})");
|
||
54 | config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
||
55 | |||
56 | config.set(SQLConfig.lcase, "lower({0})");
|
||
57 | config.set(SQLConfig.ucase, "upper({0})");
|
||
58 | config.set(SQLConfig.operator_ILIKE, "({0}) ILIKE ({1})");
|
||
59 | config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
||
60 | config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
||
61 | |||
62 | } |
||
63 | |||
64 | public class PostgreSQLUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase { |
||
65 | @Override
|
||
66 | public List<String> toStrings() { |
||
67 | List<String> sqls = new ArrayList<>(); |
||
68 | |||
69 | if( config.has_functionality(SQLConfig.UPDATE_TABLE_STATISTICS_table) ) {
|
||
70 | // In postGIS, UpdateLayerStatistics function, don't allow to
|
||
71 | // use the database name in the table name.
|
||
72 | String name = identifier(this.table.getName()); |
||
73 | if( table.has_schema()) {
|
||
74 | name = identifier(this.table.getSchema()) + "." + name; |
||
75 | } |
||
76 | String sql = MessageFormat.format( |
||
77 | config.getString(SQLConfig.UPDATE_TABLE_STATISTICS_table), |
||
78 | name |
||
79 | ); |
||
80 | if( !StringUtils.isEmpty(sql) ) {
|
||
81 | sqls.add(sql); |
||
82 | } |
||
83 | } |
||
84 | return sqls;
|
||
85 | } |
||
86 | } |
||
87 | 445 | jjdelcerro | protected class PostgreSQLCreateIndexBuilder extends CreateIndexBuilderBase { |
88 | |||
89 | @Override
|
||
90 | public List<String> toStrings() { |
||
91 | StringBuilder builder = new StringBuilder(); |
||
92 | builder.append("CREATE ");
|
||
93 | // if( this.isUnique ) {
|
||
94 | // builder.append("UNIQUE ");
|
||
95 | // }
|
||
96 | builder.append("INDEX ");
|
||
97 | if( this.ifNotExist ) { |
||
98 | builder.append("IF NOT EXISTS ");
|
||
99 | } |
||
100 | builder.append(identifier(this.indexName));
|
||
101 | builder.append(" ON ");
|
||
102 | builder.append(this.table.toString());
|
||
103 | if( this.isSpatial ) { |
||
104 | builder.append(" USING GIST ");
|
||
105 | } |
||
106 | builder.append(" ( ");
|
||
107 | boolean is_first_column = true; |
||
108 | for( String column : this.columns) { |
||
109 | if( is_first_column ) {
|
||
110 | is_first_column = false;
|
||
111 | } else {
|
||
112 | builder.append(", ");
|
||
113 | } |
||
114 | builder.append(column); |
||
115 | } |
||
116 | builder.append(" )");
|
||
117 | |||
118 | List<String> sqls = new ArrayList<>(); |
||
119 | sqls.add(builder.toString()); |
||
120 | return sqls;
|
||
121 | } |
||
122 | |||
123 | } |
||
124 | 362 | jjdelcerro | |
125 | protected class PostgreSQLCreateTableBuilder extends CreateTableBuilderBase { |
||
126 | |||
127 | @Override
|
||
128 | public List<String> toStrings() { |
||
129 | //
|
||
130 | // https://www.postgresql.org/docs/9.1/static/sql-createtable.html
|
||
131 | //
|
||
132 | List<String> sqls = new ArrayList<>(); |
||
133 | StringBuilder builder = new StringBuilder(); |
||
134 | |||
135 | builder.append("CREATE TABLE ");
|
||
136 | builder.append(this.table.toString());
|
||
137 | builder.append(" (");
|
||
138 | boolean first = true; |
||
139 | 453 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
140 | 362 | jjdelcerro | if( column.isGeometry() ) {
|
141 | continue;
|
||
142 | } |
||
143 | if (first) {
|
||
144 | first = false;
|
||
145 | } else {
|
||
146 | builder.append(", ");
|
||
147 | } |
||
148 | builder.append(identifier(column.getName())); |
||
149 | builder.append(" ");
|
||
150 | if( column.isAutomatic() ) {
|
||
151 | builder.append(" SERIAL");
|
||
152 | } else {
|
||
153 | builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize())); |
||
154 | if (column.getDefaultValue() == null) { |
||
155 | if (column.allowNulls()) {
|
||
156 | builder.append(" DEFAULT NULL");
|
||
157 | } |
||
158 | } else {
|
||
159 | builder.append(" DEFAULT '");
|
||
160 | builder.append(column.getDefaultValue().toString()); |
||
161 | builder.append("'");
|
||
162 | } |
||
163 | if (column.allowNulls()) {
|
||
164 | builder.append(" NULL");
|
||
165 | } else {
|
||
166 | builder.append(" NOT NULL");
|
||
167 | } |
||
168 | } |
||
169 | if (column.isPrimaryKey()) {
|
||
170 | builder.append(" PRIMARY KEY");
|
||
171 | } |
||
172 | } |
||
173 | builder.append(" )");
|
||
174 | sqls.add(builder.toString()); |
||
175 | |||
176 | 451 | jjdelcerro | String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2}, {3,number,#######} , {4} , {5}, {6})"; |
177 | 453 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
178 | 362 | jjdelcerro | if( column.isGeometry() ) {
|
179 | String sql = MessageFormat.format( |
||
180 | AddGeometryColumn, |
||
181 | constant(this.table.getSchema()),
|
||
182 | constant(this.table.getName()),
|
||
183 | constant(column.getName()), |
||
184 | column.getGeometrySRSId(), |
||
185 | constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
||
186 | constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
||
187 | constant(column.allowNulls()) |
||
188 | ); |
||
189 | sqls.add(sql); |
||
190 | } |
||
191 | } |
||
192 | return sqls;
|
||
193 | } |
||
194 | } |
||
195 | |||
196 | public class PostgreSQLSelectBuilderBase extends SelectBuilderBase { |
||
197 | |||
198 | @Override
|
||
199 | protected boolean isValid(StringBuilder message) { |
||
200 | if( message == null ) { |
||
201 | message = new StringBuilder(); |
||
202 | } |
||
203 | if( this.has_offset() && !this.has_order_by() ) { |
||
204 | // Algunos gestores de BBDD requieren que se especifique un
|
||
205 | // orden para poder usar OFFSET. Como eso parece buena idea para
|
||
206 | // asegurar que siempre tengamos los mismo resultados, lo exijimos
|
||
207 | // siempre.
|
||
208 | message.append("Can't use OFFSET without an ORDER BY.");
|
||
209 | return false; |
||
210 | } |
||
211 | return true; |
||
212 | } |
||
213 | |||
214 | @Override
|
||
215 | public String toString() { |
||
216 | //
|
||
217 | // https://www.postgresql.org/docs/9.1/static/sql-select.html
|
||
218 | //
|
||
219 | StringBuilder builder = new StringBuilder(); |
||
220 | if( !isValid(builder) ) {
|
||
221 | throw new IllegalStateException(builder.toString()); |
||
222 | } |
||
223 | builder.append("SELECT ");
|
||
224 | if( this.distinct ) { |
||
225 | builder.append("DISTINCT ");
|
||
226 | } |
||
227 | boolean first = true; |
||
228 | for (SelectColumnBuilder column : columns) {
|
||
229 | if (first) {
|
||
230 | first = false;
|
||
231 | } else {
|
||
232 | builder.append(", ");
|
||
233 | } |
||
234 | builder.append(column.toString()); |
||
235 | } |
||
236 | |||
237 | if ( this.has_from() ) { |
||
238 | builder.append(" FROM ");
|
||
239 | builder.append(this.from.toString());
|
||
240 | } |
||
241 | if ( this.has_where() ) { |
||
242 | builder.append(" WHERE ");
|
||
243 | builder.append(this.where.toString());
|
||
244 | } |
||
245 | |||
246 | if( this.has_order_by() ) { |
||
247 | builder.append(" ORDER BY ");
|
||
248 | first = true;
|
||
249 | for (OrderByBuilder item : this.order_by) { |
||
250 | if (first) {
|
||
251 | first = false;
|
||
252 | } else {
|
||
253 | builder.append(", ");
|
||
254 | } |
||
255 | builder.append(item.toString()); |
||
256 | } |
||
257 | } |
||
258 | |||
259 | if ( this.has_limit() && this.has_offset() ) { |
||
260 | builder.append(" OFFSET ");
|
||
261 | builder.append(this.offset);
|
||
262 | builder.append(" FETCH NEXT ");
|
||
263 | builder.append(this.limit);
|
||
264 | builder.append(" ROWS ONLY");
|
||
265 | |||
266 | } else if ( this.has_limit()) { |
||
267 | builder.append(" LIMIT ");
|
||
268 | builder.append(this.limit);
|
||
269 | |||
270 | } else if ( this.has_offset() ) { |
||
271 | builder.append(" LIMIT ALL OFFSET ");
|
||
272 | builder.append(this.offset);
|
||
273 | } |
||
274 | return builder.toString();
|
||
275 | |||
276 | } |
||
277 | } |
||
278 | |||
279 | @Override
|
||
280 | public String bytearray(byte[] data) { |
||
281 | return "decode('"+bytearray_hex(data)+"','hex')"; |
||
282 | } |
||
283 | 445 | jjdelcerro | |
284 | public PostgreSQLHelper getHelper() {
|
||
285 | return (PostgreSQLHelper) helper;
|
||
286 | } |
||
287 | 362 | jjdelcerro | |
288 | @Override
|
||
289 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
290 | return new PostgreSQLCreateTableBuilder(); |
||
291 | } |
||
292 | |||
293 | @Override
|
||
294 | 445 | jjdelcerro | protected CreateIndexBuilder createCreateIndexBuilder() {
|
295 | return new PostgreSQLCreateIndexBuilder(); |
||
296 | } |
||
297 | |||
298 | @Override
|
||
299 | 362 | jjdelcerro | protected SelectBuilder createSelectBuilder() {
|
300 | return new PostgreSQLSelectBuilderBase(); |
||
301 | } |
||
302 | |||
303 | @Override
|
||
304 | protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
||
305 | return new PostgreSQLUpdateTableStatisticsBuilderBase(); |
||
306 | 445 | jjdelcerro | } |
307 | |||
308 | 362 | jjdelcerro | } |