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 |
/**
|
---|---|
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.sql.Connection; |
28 |
import java.sql.DatabaseMetaData; |
29 |
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.expressionevaluator.Formatter; |
34 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
35 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
36 |
import org.gvsig.postgresql.dal.expressionbuilderformatter.PostgreSQLFormatter; |
37 |
|
38 |
@SuppressWarnings("UseSpecificCatch") |
39 |
public class PostgreSQLBuilder extends JDBCSQLBuilderBase { |
40 |
|
41 |
protected Formatter formatter = null; |
42 |
|
43 |
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 |
|
64 |
public Version getDatabaseVersion() {
|
65 |
if( databaseVersion == null ) { |
66 |
Connection conn = null; |
67 |
try {
|
68 |
conn = this.getHelper().getConnection();
|
69 |
DatabaseMetaData metadata = conn.getMetaData();
|
70 |
databaseVersion = new Version(
|
71 |
metadata.getDatabaseMajorVersion(), |
72 |
metadata.getDatabaseMinorVersion() |
73 |
); |
74 |
} catch (Exception ex) { |
75 |
databaseVersion = new Version(0,0); |
76 |
} finally {
|
77 |
this.getHelper().closeConnectionQuietly(conn);
|
78 |
} |
79 |
} |
80 |
return databaseVersion;
|
81 |
} |
82 |
|
83 |
public PostgreSQLBuilder(JDBCHelper helper) {
|
84 |
super(helper);
|
85 |
this.defaultSchema = "public"; |
86 |
this.supportSchemas = true; |
87 |
this.allowAutomaticValues = true; |
88 |
this.geometrySupportType = this.helper.getGeometrySupportType(); |
89 |
this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
90 |
|
91 |
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 |
|
101 |
// config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
102 |
// config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
103 |
|
104 |
} |
105 |
|
106 |
public class PostgreSQLUpdateTableStatisticsBuilderBase extends UpdateTableStatisticsBuilderBase { |
107 |
@Override
|
108 |
public List<String> toStrings() { |
109 |
List<String> sqls = new ArrayList<>(); |
110 |
|
111 |
if( !StringUtils.isBlank(STMT_UPDATE_TABLE_STATISTICS_table) ) {
|
112 |
// In postGIS, UpdateLayerStatistics function, don't allow to
|
113 |
// use the database name in the table name.
|
114 |
String name = as_identifier(this.table.getName()); |
115 |
if( table.has_schema()) {
|
116 |
name = as_identifier(this.table.getSchema()) + "." + name; |
117 |
} |
118 |
String sql = MessageFormat.format( |
119 |
STMT_UPDATE_TABLE_STATISTICS_table, |
120 |
name |
121 |
); |
122 |
if( !StringUtils.isEmpty(sql) ) {
|
123 |
sqls.add(sql); |
124 |
} |
125 |
} |
126 |
return sqls;
|
127 |
} |
128 |
} |
129 |
protected class PostgreSQLCreateIndexBuilder extends CreateIndexBuilderBase { |
130 |
|
131 |
@Override
|
132 |
public List<String> toStrings(Formatter formatter) { |
133 |
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 |
Version version = getDatabaseVersion(); |
141 |
if( version.getMajor()>=9 && version.getMinor()>=5 ) { |
142 |
builder.append("IF NOT EXISTS ");
|
143 |
} |
144 |
} |
145 |
builder.append(as_identifier(this.indexName));
|
146 |
builder.append(" ON ");
|
147 |
builder.append(this.table.toString(formatter));
|
148 |
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 |
builder.append(as_identifier(column)); |
160 |
} |
161 |
builder.append(" )");
|
162 |
|
163 |
List<String> sqls = new ArrayList<>(); |
164 |
sqls.add(builder.toString()); |
165 |
return sqls;
|
166 |
} |
167 |
|
168 |
} |
169 |
|
170 |
protected class PostgreSQLCreateTableBuilder extends CreateTableBuilderBase { |
171 |
|
172 |
@Override
|
173 |
public List<String> toStrings(Formatter formatter) { |
174 |
//
|
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 |
builder.append(this.table.toString(formatter));
|
182 |
builder.append(" (");
|
183 |
boolean first = true; |
184 |
for (ColumnDescriptor column : columns) {
|
185 |
if( column.isGeometry() ) {
|
186 |
continue;
|
187 |
} |
188 |
if (first) {
|
189 |
first = false;
|
190 |
} else {
|
191 |
builder.append(", ");
|
192 |
} |
193 |
builder.append(as_identifier(column.getName())); |
194 |
builder.append(" ");
|
195 |
if( column.isAutomatic() ) {
|
196 |
builder.append(" SERIAL");
|
197 |
} else {
|
198 |
builder.append(sqltype( |
199 |
column.getType(), |
200 |
column.getSize(), |
201 |
column.getPrecision(), |
202 |
column.getScale(), |
203 |
column.getGeometryType(), |
204 |
column.getGeometrySubtype() |
205 |
)); |
206 |
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 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2}, {3,number,#######} , {4} , {5}, {6})"; |
229 |
for (ColumnDescriptor column : columns) {
|
230 |
if( column.isGeometry() ) {
|
231 |
String sql = MessageFormat.format( |
232 |
AddGeometryColumn, |
233 |
as_string(this.table.has_schema()?this.table.getSchema():"public"), |
234 |
as_string(this.table.getName()),
|
235 |
as_string(column.getName()), |
236 |
column.getGeometrySRSId(), |
237 |
as_string(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
238 |
as_string(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
239 |
as_string(column.allowNulls()) |
240 |
); |
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 |
public String toString(Formatter formatter) { |
268 |
//
|
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 |
builder.append(column.toString(formatter)); |
287 |
} |
288 |
|
289 |
if ( this.has_from() ) { |
290 |
builder.append(" FROM ");
|
291 |
builder.append(this.from.toString(formatter));
|
292 |
} |
293 |
|
294 |
if ( this.has_where() ) { |
295 |
builder.append(" WHERE ");
|
296 |
builder.append(this.where.toString(formatter));
|
297 |
} |
298 |
if( this.has_group_by() ) { |
299 |
builder.append(" GROUP BY ");
|
300 |
builder.append(this.groupColumn.get(0).toString(formatter)); |
301 |
for (int i = 1; i < groupColumn.size(); i++) { |
302 |
builder.append(", ");
|
303 |
builder.append(this.groupColumn.get(i).toString(formatter));
|
304 |
} |
305 |
} |
306 |
|
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 |
builder.append(item.toString(formatter)); |
317 |
} |
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 |
@Override
|
341 |
public Formatter formatter() { |
342 |
if( this.formatter==null ) { |
343 |
this.formatter = new PostgreSQLFormatter(this); |
344 |
} |
345 |
return this.formatter; |
346 |
} |
347 |
|
348 |
@Override
|
349 |
public PostgreSQLHelper getHelper() {
|
350 |
return (PostgreSQLHelper) helper;
|
351 |
} |
352 |
|
353 |
@Override
|
354 |
protected CreateTableBuilder createCreateTableBuilder() {
|
355 |
return new PostgreSQLCreateTableBuilder(); |
356 |
} |
357 |
|
358 |
@Override
|
359 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
360 |
return new PostgreSQLCreateIndexBuilder(); |
361 |
} |
362 |
|
363 |
@Override
|
364 |
protected SelectBuilder createSelectBuilder() {
|
365 |
return new PostgreSQLSelectBuilderBase(); |
366 |
} |
367 |
|
368 |
@Override
|
369 |
protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
370 |
return new PostgreSQLUpdateTableStatisticsBuilderBase(); |
371 |
} |
372 |
|
373 |
} |