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 |
/**
|
---|---|
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.fmap.dal.store.jdbc2.JDBCHelper; |
34 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
35 |
import org.gvsig.tools.packageutils.Version; |
36 |
import org.gvsig.tools.packageutils.impl.DefaultVersion; |
37 |
|
38 |
public class PostgreSQLBuilder extends JDBCSQLBuilderBase { |
39 |
|
40 |
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 |
|
61 |
public Version getDatabaseVersion() {
|
62 |
if( databaseVersion == null ) { |
63 |
Connection conn = null; |
64 |
try {
|
65 |
conn = this.getHelper().getConnection();
|
66 |
DatabaseMetaData metadata = conn.getMetaData();
|
67 |
databaseVersion = new Version(
|
68 |
metadata.getDatabaseMajorVersion(), |
69 |
metadata.getDatabaseMinorVersion() |
70 |
); |
71 |
} catch (Exception ex) { |
72 |
databaseVersion = new Version(0,0); |
73 |
} finally {
|
74 |
this.getHelper().closeConnectionQuietly(conn);
|
75 |
} |
76 |
} |
77 |
return databaseVersion;
|
78 |
} |
79 |
|
80 |
public PostgreSQLBuilder(JDBCHelper helper) {
|
81 |
super(helper);
|
82 |
|
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 |
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 |
Version version = getDatabaseVersion(); |
143 |
if( version.getMajor()>=9 && version.getMinor()>=5 ) { |
144 |
builder.append("IF NOT EXISTS ");
|
145 |
} |
146 |
} |
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 |
|
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 |
for (ColumnDescriptor column : columns) {
|
187 |
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 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2}, {3,number,#######} , {4} , {5}, {6})"; |
224 |
for (ColumnDescriptor column : columns) {
|
225 |
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 |
|
331 |
public PostgreSQLHelper getHelper() {
|
332 |
return (PostgreSQLHelper) helper;
|
333 |
} |
334 |
|
335 |
@Override
|
336 |
protected CreateTableBuilder createCreateTableBuilder() {
|
337 |
return new PostgreSQLCreateTableBuilder(); |
338 |
} |
339 |
|
340 |
@Override
|
341 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
342 |
return new PostgreSQLCreateIndexBuilder(); |
343 |
} |
344 |
|
345 |
@Override
|
346 |
protected SelectBuilder createSelectBuilder() {
|
347 |
return new PostgreSQLSelectBuilderBase(); |
348 |
} |
349 |
|
350 |
@Override
|
351 |
protected UpdateTableStatisticsBuilder createUpdateTableStatisticsBuilder() {
|
352 |
return new PostgreSQLUpdateTableStatisticsBuilderBase(); |
353 |
} |
354 |
|
355 |
} |