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