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