root / trunk / org.gvsig.postgresql / org.gvsig.postgresql.provider / src / main / java / org / gvsig / postgresql / dal / PostgreSQLBuilder.java @ 931
History | View | Annotate | Download (13.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 | 464 | jjdelcerro | import java.sql.Connection; |
28 | import java.sql.DatabaseMetaData; |
||
29 | 362 | jjdelcerro | import java.text.MessageFormat; |
30 | import java.util.ArrayList; |
||
31 | import java.util.List; |
||
32 | import org.apache.commons.lang3.StringUtils; |
||
33 | 516 | jjdelcerro | import org.gvsig.expressionevaluator.Formatter; |
34 | 362 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
35 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
36 | 516 | jjdelcerro | import org.gvsig.postgresql.dal.expressionbuilderformatter.PostgreSQLFormatter; |
37 | 362 | jjdelcerro | |
38 | 576 | jjdelcerro | @SuppressWarnings("UseSpecificCatch") |
39 | 362 | jjdelcerro | public class PostgreSQLBuilder extends JDBCSQLBuilderBase { |
40 | |||
41 | 516 | jjdelcerro | protected Formatter formatter = null; |
42 | |||
43 | 495 | jjdelcerro | public static class Version { |
44 | |||
45 | private final int major; |
||
46 | private final int minor; |
||
47 | |||
48 | public Version(int major, int minor) { |
||
49 | this.major = major;
|
||
50 | this.minor = minor;
|
||
51 | } |
||
52 | |||
53 | public int getMajor() { |
||
54 | return major;
|
||
55 | } |
||
56 | |||
57 | public int getMinor() { |
||
58 | return minor;
|
||
59 | } |
||
60 | |||
61 | } |
||
62 | private Version databaseVersion = null; |
||
63 | 464 | jjdelcerro | |
64 | 495 | jjdelcerro | public Version getDatabaseVersion() {
|
65 | 464 | jjdelcerro | if( databaseVersion == null ) { |
66 | 510 | jjdelcerro | Connection conn = null; |
67 | 464 | jjdelcerro | try {
|
68 | conn = this.getHelper().getConnection();
|
||
69 | DatabaseMetaData metadata = conn.getMetaData();
|
||
70 | 495 | jjdelcerro | databaseVersion = new Version(
|
71 | 464 | jjdelcerro | metadata.getDatabaseMajorVersion(), |
72 | metadata.getDatabaseMinorVersion() |
||
73 | 495 | jjdelcerro | ); |
74 | 464 | jjdelcerro | } catch (Exception ex) { |
75 | 495 | jjdelcerro | databaseVersion = new Version(0,0); |
76 | 510 | jjdelcerro | } finally {
|
77 | this.getHelper().closeConnectionQuietly(conn);
|
||
78 | 464 | jjdelcerro | } |
79 | } |
||
80 | return databaseVersion;
|
||
81 | } |
||
82 | |||
83 | 362 | jjdelcerro | public PostgreSQLBuilder(JDBCHelper helper) {
|
84 | 445 | jjdelcerro | super(helper);
|
85 | 511 | jjdelcerro | this.defaultSchema = "public"; |
86 | this.supportSchemas = true; |
||
87 | this.allowAutomaticValues = true; |
||
88 | this.geometrySupportType = this.helper.getGeometrySupportType(); |
||
89 | this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
||
90 | 362 | jjdelcerro | |
91 | 511 | jjdelcerro | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
92 | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
||
93 | |||
94 | this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE {0}"; |
||
95 | |||
96 | // config.set(SQLConfig.ST_GeomFromEWKB, "ST_GeomFromEWKB({0}, {1})");
|
||
97 | // config.set(SQLConfig.ST_AsEWKB, "ST_AsEWKB(({0}))");
|
||
98 | // config.set(SQLConfig.ST_ExtentAggregate, "ST_Extent({0})");
|
||
99 | // config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
||
100 | 362 | jjdelcerro | |
101 | 511 | jjdelcerro | // config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
102 | // config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
||
103 | 362 | jjdelcerro | |
104 | } |
||
105 | |||
106 | public class PostgreSQLUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase { |
||
107 | @Override
|
||
108 | public List<String> toStrings() { |
||
109 | List<String> sqls = new ArrayList<>(); |
||
110 | |||
111 | 511 | jjdelcerro | if( !StringUtils.isBlank(STMT_UPDATE_TABLE_STATISTICS_table) ) {
|
112 | 362 | jjdelcerro | // In postGIS, UpdateLayerStatistics function, don't allow to
|
113 | // use the database name in the table name.
|
||
114 | 511 | jjdelcerro | String name = as_identifier(this.table.getName()); |
115 | 362 | jjdelcerro | if( table.has_schema()) {
|
116 | 511 | jjdelcerro | name = as_identifier(this.table.getSchema()) + "." + name; |
117 | 362 | jjdelcerro | } |
118 | String sql = MessageFormat.format( |
||
119 | 511 | jjdelcerro | STMT_UPDATE_TABLE_STATISTICS_table, |
120 | 362 | jjdelcerro | name |
121 | ); |
||
122 | if( !StringUtils.isEmpty(sql) ) {
|
||
123 | sqls.add(sql); |
||
124 | } |
||
125 | } |
||
126 | return sqls;
|
||
127 | } |
||
128 | } |
||
129 | 445 | jjdelcerro | protected class PostgreSQLCreateIndexBuilder extends CreateIndexBuilderBase { |
130 | |||
131 | @Override
|
||
132 | 516 | jjdelcerro | public List<String> toStrings(Formatter formatter) { |
133 | 445 | jjdelcerro | StringBuilder builder = new StringBuilder(); |
134 | builder.append("CREATE ");
|
||
135 | // if( this.isUnique ) {
|
||
136 | // builder.append("UNIQUE ");
|
||
137 | // }
|
||
138 | builder.append("INDEX ");
|
||
139 | if( this.ifNotExist ) { |
||
140 | 495 | jjdelcerro | Version version = getDatabaseVersion(); |
141 | if( version.getMajor()>=9 && version.getMinor()>=5 ) { |
||
142 | 464 | jjdelcerro | builder.append("IF NOT EXISTS ");
|
143 | } |
||
144 | 445 | jjdelcerro | } |
145 | 511 | jjdelcerro | builder.append(as_identifier(this.indexName));
|
146 | 445 | jjdelcerro | builder.append(" ON ");
|
147 | 516 | jjdelcerro | builder.append(this.table.toString(formatter));
|
148 | 445 | jjdelcerro | if( this.isSpatial ) { |
149 | builder.append(" USING GIST ");
|
||
150 | } |
||
151 | builder.append(" ( ");
|
||
152 | boolean is_first_column = true; |
||
153 | for( String column : this.columns) { |
||
154 | if( is_first_column ) {
|
||
155 | is_first_column = false;
|
||
156 | } else {
|
||
157 | builder.append(", ");
|
||
158 | } |
||
159 | 527 | jjdelcerro | builder.append(as_identifier(column)); |
160 | 445 | jjdelcerro | } |
161 | builder.append(" )");
|
||
162 | |||
163 | List<String> sqls = new ArrayList<>(); |
||
164 | sqls.add(builder.toString()); |
||
165 | return sqls;
|
||
166 | } |
||
167 | |||
168 | } |
||
169 | 362 | jjdelcerro | |
170 | protected class PostgreSQLCreateTableBuilder extends CreateTableBuilderBase { |
||
171 | |||
172 | @Override
|
||
173 | 516 | jjdelcerro | public List<String> toStrings(Formatter formatter) { |
174 | 362 | jjdelcerro | //
|
175 | // https://www.postgresql.org/docs/9.1/static/sql-createtable.html
|
||
176 | //
|
||
177 | List<String> sqls = new ArrayList<>(); |
||
178 | StringBuilder builder = new StringBuilder(); |
||
179 | |||
180 | builder.append("CREATE TABLE ");
|
||
181 | 516 | jjdelcerro | builder.append(this.table.toString(formatter));
|
182 | 362 | jjdelcerro | builder.append(" (");
|
183 | boolean first = true; |
||
184 | 453 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
185 | 362 | jjdelcerro | if( column.isGeometry() ) {
|
186 | continue;
|
||
187 | } |
||
188 | if (first) {
|
||
189 | first = false;
|
||
190 | } else {
|
||
191 | builder.append(", ");
|
||
192 | } |
||
193 | 511 | jjdelcerro | builder.append(as_identifier(column.getName())); |
194 | 362 | jjdelcerro | builder.append(" ");
|
195 | if( column.isAutomatic() ) {
|
||
196 | builder.append(" SERIAL");
|
||
197 | } else {
|
||
198 | 511 | jjdelcerro | builder.append(sqltype( |
199 | column.getType(), |
||
200 | 576 | jjdelcerro | column.getSize(), |
201 | 511 | jjdelcerro | column.getPrecision(), |
202 | 576 | jjdelcerro | column.getScale(), |
203 | 511 | jjdelcerro | column.getGeometryType(), |
204 | column.getGeometrySubtype() |
||
205 | )); |
||
206 | 362 | jjdelcerro | if (column.getDefaultValue() == null) { |
207 | if (column.allowNulls()) {
|
||
208 | builder.append(" DEFAULT NULL");
|
||
209 | } |
||
210 | } else {
|
||
211 | builder.append(" DEFAULT '");
|
||
212 | builder.append(column.getDefaultValue().toString()); |
||
213 | builder.append("'");
|
||
214 | } |
||
215 | if (column.allowNulls()) {
|
||
216 | builder.append(" NULL");
|
||
217 | } else {
|
||
218 | builder.append(" NOT NULL");
|
||
219 | } |
||
220 | } |
||
221 | if (column.isPrimaryKey()) {
|
||
222 | builder.append(" PRIMARY KEY");
|
||
223 | } |
||
224 | } |
||
225 | builder.append(" )");
|
||
226 | sqls.add(builder.toString()); |
||
227 | |||
228 | 451 | jjdelcerro | String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2}, {3,number,#######} , {4} , {5}, {6})"; |
229 | 453 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
230 | 362 | jjdelcerro | if( column.isGeometry() ) {
|
231 | String sql = MessageFormat.format( |
||
232 | AddGeometryColumn, |
||
233 | 527 | jjdelcerro | as_string(this.table.has_schema()?this.table.getSchema():"public"), |
234 | 511 | jjdelcerro | as_string(this.table.getName()),
|
235 | as_string(column.getName()), |
||
236 | 362 | jjdelcerro | column.getGeometrySRSId(), |
237 | 511 | jjdelcerro | as_string(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
238 | as_string(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
||
239 | as_string(column.allowNulls()) |
||
240 | 362 | jjdelcerro | ); |
241 | sqls.add(sql); |
||
242 | } |
||
243 | } |
||
244 | return sqls;
|
||
245 | } |
||
246 | } |
||
247 | |||
248 | public class PostgreSQLSelectBuilderBase extends SelectBuilderBase { |
||
249 | |||
250 | @Override
|
||
251 | protected boolean isValid(StringBuilder message) { |
||
252 | if( message == null ) { |
||
253 | message = new StringBuilder(); |
||
254 | } |
||
255 | if( this.has_offset() && !this.has_order_by() ) { |
||
256 | // Algunos gestores de BBDD requieren que se especifique un
|
||
257 | // orden para poder usar OFFSET. Como eso parece buena idea para
|
||
258 | // asegurar que siempre tengamos los mismo resultados, lo exijimos
|
||
259 | // siempre.
|
||
260 | message.append("Can't use OFFSET without an ORDER BY.");
|
||
261 | return false; |
||
262 | } |
||
263 | return true; |
||
264 | } |
||
265 | |||
266 | @Override
|
||
267 | 516 | jjdelcerro | public String toString(Formatter formatter) { |
268 | 362 | jjdelcerro | //
|
269 | // https://www.postgresql.org/docs/9.1/static/sql-select.html
|
||
270 | //
|
||
271 | StringBuilder builder = new StringBuilder(); |
||
272 | if( !isValid(builder) ) {
|
||
273 | throw new IllegalStateException(builder.toString()); |
||
274 | } |
||
275 | builder.append("SELECT ");
|
||
276 | if( this.distinct ) { |
||
277 | builder.append("DISTINCT ");
|
||
278 | } |
||
279 | boolean first = true; |
||
280 | for (SelectColumnBuilder column : columns) {
|
||
281 | if (first) {
|
||
282 | first = false;
|
||
283 | } else {
|
||
284 | builder.append(", ");
|
||
285 | } |
||
286 | 516 | jjdelcerro | builder.append(column.toString(formatter)); |
287 | 362 | jjdelcerro | } |
288 | |||
289 | if ( this.has_from() ) { |
||
290 | builder.append(" FROM ");
|
||
291 | 516 | jjdelcerro | builder.append(this.from.toString(formatter));
|
292 | 362 | jjdelcerro | } |
293 | 829 | omartinez | |
294 | if ( this.has_where() ) { |
||
295 | builder.append(" WHERE ");
|
||
296 | builder.append(this.where.toString(formatter));
|
||
297 | } |
||
298 | 528 | jjdelcerro | if( this.has_group_by() ) { |
299 | builder.append(" GROUP BY ");
|
||
300 | 534 | jjdelcerro | builder.append(this.groupColumn.get(0).toString(formatter)); |
301 | 528 | jjdelcerro | for (int i = 1; i < groupColumn.size(); i++) { |
302 | builder.append(", ");
|
||
303 | 534 | jjdelcerro | builder.append(this.groupColumn.get(i).toString(formatter));
|
304 | 528 | jjdelcerro | } |
305 | 829 | omartinez | } |
306 | 362 | jjdelcerro | |
307 | if( this.has_order_by() ) { |
||
308 | builder.append(" ORDER BY ");
|
||
309 | first = true;
|
||
310 | for (OrderByBuilder item : this.order_by) { |
||
311 | if (first) {
|
||
312 | first = false;
|
||
313 | } else {
|
||
314 | builder.append(", ");
|
||
315 | } |
||
316 | 516 | jjdelcerro | builder.append(item.toString(formatter)); |
317 | 362 | jjdelcerro | } |
318 | } |
||
319 | |||
320 | if ( this.has_limit() && this.has_offset() ) { |
||
321 | builder.append(" OFFSET ");
|
||
322 | builder.append(this.offset);
|
||
323 | builder.append(" FETCH NEXT ");
|
||
324 | builder.append(this.limit);
|
||
325 | builder.append(" ROWS ONLY");
|
||
326 | |||
327 | } else if ( this.has_limit()) { |
||
328 | builder.append(" LIMIT ");
|
||
329 | builder.append(this.limit);
|
||
330 | |||
331 | } else if ( this.has_offset() ) { |
||
332 | builder.append(" LIMIT ALL OFFSET ");
|
||
333 | builder.append(this.offset);
|
||
334 | } |
||
335 | return builder.toString();
|
||
336 | |||
337 | } |
||
338 | } |
||
339 | |||
340 | 516 | jjdelcerro | @Override
|
341 | 931 | omartinez | public Formatter formatter() { |
342 | 516 | jjdelcerro | if( this.formatter==null ) { |
343 | this.formatter = new PostgreSQLFormatter(this); |
||
344 | } |
||
345 | return this.formatter; |
||
346 | } |
||
347 | 445 | jjdelcerro | |
348 | 516 | jjdelcerro | @Override
|
349 | 445 | jjdelcerro | public PostgreSQLHelper getHelper() {
|
350 | return (PostgreSQLHelper) helper;
|
||
351 | } |
||
352 | 362 | jjdelcerro | |
353 | @Override
|
||
354 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
355 | return new PostgreSQLCreateTableBuilder(); |
||
356 | } |
||
357 | |||
358 | @Override
|
||
359 | 445 | jjdelcerro | protected CreateIndexBuilder createCreateIndexBuilder() {
|
360 | return new PostgreSQLCreateIndexBuilder(); |
||
361 | } |
||
362 | |||
363 | @Override
|
||
364 | 362 | jjdelcerro | protected SelectBuilder createSelectBuilder() {
|
365 | return new PostgreSQLSelectBuilderBase(); |
||
366 | } |
||
367 | |||
368 | @Override
|
||
369 | protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
||
370 | return new PostgreSQLUpdateTableStatisticsBuilderBase(); |
||
371 | 445 | jjdelcerro | } |
372 | |||
373 | 362 | jjdelcerro | } |