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