gvsig-sqlite / trunk / org.gvsig.spatialite / org.gvsig.spatialite.provider / src / main / java / org / gvsig / spatialite / dal / SpatiaLiteSQLBuilder.java @ 138
History | View | Annotate | Download (20.2 KB)
1 |
package org.gvsig.spatialite.dal; |
---|---|
2 |
|
3 |
import java.sql.PreparedStatement; |
4 |
import java.text.MessageFormat; |
5 |
import java.util.ArrayList; |
6 |
import java.util.Date; |
7 |
import java.util.List; |
8 |
import org.gvsig.fmap.dal.DataTypes; |
9 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
10 |
import org.gvsig.fmap.dal.feature.FeatureType; |
11 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
12 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
13 |
import org.gvsig.fmap.geom.Geometry; |
14 |
import org.gvsig.tools.dispose.Disposable; |
15 |
import org.apache.commons.lang3.tuple.Pair; |
16 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
17 |
|
18 |
public class SpatiaLiteSQLBuilder extends JDBCSQLBuilderBase { |
19 |
|
20 |
public SpatiaLiteSQLBuilder(SpatiaLiteHelper helper) {
|
21 |
super(helper);
|
22 |
|
23 |
//
|
24 |
// SpatiaLite 4.3.0, SQL functions reference list
|
25 |
//
|
26 |
// http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html
|
27 |
//
|
28 |
// https://sqlite.org/lang.html
|
29 |
//
|
30 |
|
31 |
config.set(SQLConfig.default_schema, "");
|
32 |
config.set(SQLConfig.support_schemas, false);
|
33 |
config.set(SQLConfig.allowAutomaticValues, true);
|
34 |
config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType());
|
35 |
config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions());
|
36 |
|
37 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table); |
38 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table); |
39 |
|
40 |
config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"SELECT UpdateLayerStatistics({0})");
|
41 |
|
42 |
config.set(SQLConfig.ST_GeomFromEWKB, "GeomFromWKB({0}, {1})");
|
43 |
config.set(SQLConfig.ST_AsEWKB, "AsEWKB(({0}))");
|
44 |
config.set(SQLConfig.ST_ExtentAggregate, "Extent({0})");
|
45 |
config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
46 |
|
47 |
config.set(SQLConfig.lcase, "lower({0})");
|
48 |
config.set(SQLConfig.ucase, "upper({0})");
|
49 |
config.set(SQLConfig.operator_ILIKE, "lower({0}) LIKE lower({1})");
|
50 |
config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
51 |
config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
52 |
|
53 |
config.set(SQLConfig.type_boolean, "INTEGER");
|
54 |
config.set(SQLConfig.type_byte, "INTEGER");
|
55 |
config.set(SQLConfig.type_bytearray, "");
|
56 |
config.set(SQLConfig.type_geometry, "BLOB");
|
57 |
config.set(SQLConfig.type_char, "TEXT");
|
58 |
config.set(SQLConfig.type_date, "INTEGER");
|
59 |
config.set(SQLConfig.type_double, "REAL");
|
60 |
config.set(SQLConfig.type_numeric_p, "REAL");
|
61 |
config.set(SQLConfig.type_numeric_ps, "REAL");
|
62 |
config.set(SQLConfig.type_bigdecimal, "REAL");
|
63 |
config.set(SQLConfig.type_float, "REAL");
|
64 |
config.set(SQLConfig.type_int, "INTEGER");
|
65 |
config.set(SQLConfig.type_long, "INTEGER");
|
66 |
config.set(SQLConfig.type_string, "TEXT");
|
67 |
config.set(SQLConfig.type_string_p, "TEXT");
|
68 |
config.set(SQLConfig.type_time, "INTEGER");
|
69 |
config.set(SQLConfig.type_timestamp, "INTEGER");
|
70 |
config.set(SQLConfig.type_version, "TEXT");
|
71 |
config.set(SQLConfig.type_URI, "TEXT");
|
72 |
config.set(SQLConfig.type_URL, "TEXT");
|
73 |
config.set(SQLConfig.type_FILE, "TEXT");
|
74 |
config.set(SQLConfig.type_FOLDER, "TEXT");
|
75 |
} |
76 |
|
77 |
public class SpatiaLiteTableNameBuilderBase extends TableNameBuilderBase { |
78 |
|
79 |
@Override
|
80 |
public boolean has_schema() { |
81 |
return false; |
82 |
} |
83 |
|
84 |
@Override
|
85 |
public boolean has_database() { |
86 |
return false; |
87 |
} |
88 |
|
89 |
} |
90 |
|
91 |
protected class SpatiaLiteCreateTableBuilder extends CreateTableBuilderBase { |
92 |
|
93 |
@Override
|
94 |
public List<String> toStrings() { |
95 |
//
|
96 |
// Respecto al base cambia la declaracion de campo automaticos:
|
97 |
// - Los campos se crean autom?ticamente como SERIAL si son INTEGER PRIMARY KEY.
|
98 |
// - Existe la palabra clave AUTOINCREMENT que se puede usar
|
99 |
// en los campos INTEGER PRIMARY KEY, pero no se recomienda su uso (penaliza el rendimiento).
|
100 |
// - Spatialite no permite definir SERIALs para el resto de campos.
|
101 |
// - Hay que usar la funcion AddGeometryColumn para a?adir las columnas de tipo geometria
|
102 |
// - El orden en el que hay que declarar las constrains tambien cambia
|
103 |
// respecto al que hay por defecto.
|
104 |
//
|
105 |
List<String> sqls = new ArrayList<>(); |
106 |
StringBuilder builder = new StringBuilder(); |
107 |
|
108 |
builder.append("CREATE TABLE ");
|
109 |
builder.append(this.table.toString());
|
110 |
builder.append(" (");
|
111 |
boolean first = true; |
112 |
for (ColumnDescriptor column : columns) {
|
113 |
if( column.isGeometry() ) {
|
114 |
continue;
|
115 |
} |
116 |
if (first) {
|
117 |
first = false;
|
118 |
} else {
|
119 |
builder.append(", ");
|
120 |
} |
121 |
builder.append(identifier(column.getName())); |
122 |
builder.append(" ");
|
123 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize(), column.getGeometryType(), column.getGeometrySubtype())); |
124 |
if (column.isPrimaryKey()) {
|
125 |
builder.append(" PRIMARY KEY");
|
126 |
} |
127 |
if (column.getDefaultValue() == null) { |
128 |
if (column.allowNulls()) {
|
129 |
builder.append(" DEFAULT NULL");
|
130 |
} |
131 |
} else {
|
132 |
if( column.getType() == DataTypes.DATE ) {
|
133 |
builder.append(" DEFAULT ( date('");
|
134 |
builder.append(column.getDefaultValue().toString()); |
135 |
builder.append("') )");
|
136 |
} else {
|
137 |
builder.append(" DEFAULT '");
|
138 |
builder.append(column.getDefaultValue().toString()); |
139 |
builder.append("'");
|
140 |
} |
141 |
} |
142 |
if (!column.allowNulls()) {
|
143 |
builder.append(" NOT NULL");
|
144 |
} |
145 |
} |
146 |
builder.append(" )");
|
147 |
sqls.add(builder.toString()); |
148 |
|
149 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######} , {3}, {4}, {5})"; |
150 |
for (ColumnDescriptor column : columns) {
|
151 |
if( column.isGeometry() ) {
|
152 |
String sql = MessageFormat.format( |
153 |
AddGeometryColumn, |
154 |
this.table.toString(),
|
155 |
identifier(column.getName()), |
156 |
column.getGeometrySRSId(), |
157 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
158 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
159 |
column.allowNulls()? 0:1 |
160 |
); |
161 |
sqls.add(sql); |
162 |
} |
163 |
} |
164 |
return sqls;
|
165 |
} |
166 |
} |
167 |
|
168 |
public class SpatiaLiteSelectBuilderBase extends SelectBuilderBase { |
169 |
|
170 |
@Override
|
171 |
protected boolean isValid(StringBuilder message) { |
172 |
if( message == null ) { |
173 |
message = new StringBuilder(); |
174 |
} |
175 |
if( this.has_offset() && !this.has_order_by() ) { |
176 |
// Algunos gestores de BBDD requieren que se especifique un
|
177 |
// orden para poder usar OFFSET. Como eso parece buena idea para
|
178 |
// asegurar que siempre tengamos los mismo resultados, lo exijimos
|
179 |
// siempre.
|
180 |
message.append("Can't use OFFSET without an ORDER BY.");
|
181 |
return false; |
182 |
} |
183 |
return true; |
184 |
} |
185 |
|
186 |
@Override
|
187 |
public String toString() { |
188 |
// SpatiaLite requiere que si se especifica OFFSET deba especificarse
|
189 |
// LIMIT obligatoriamente. Se le puede poner un LIMIT -1 cuando
|
190 |
// queramos un OFFSET sin especificar un LIMIT.
|
191 |
StringBuilder builder = new StringBuilder(); |
192 |
if( !isValid(builder) ) {
|
193 |
throw new IllegalStateException(builder.toString()); |
194 |
} |
195 |
builder.append("SELECT ");
|
196 |
if( this.distinct ) { |
197 |
builder.append("DISTINCT ");
|
198 |
} |
199 |
boolean first = true; |
200 |
for (SelectColumnBuilder column : columns) {
|
201 |
if (first) {
|
202 |
first = false;
|
203 |
} else {
|
204 |
builder.append(", ");
|
205 |
} |
206 |
builder.append(column.toString()); |
207 |
} |
208 |
|
209 |
if ( this.has_from() ) { |
210 |
builder.append(" FROM ");
|
211 |
builder.append(this.from.toString());
|
212 |
} |
213 |
if ( this.has_where() ) { |
214 |
builder.append(" WHERE ");
|
215 |
builder.append(this.where.toString());
|
216 |
} |
217 |
|
218 |
if( this.has_order_by() ) { |
219 |
builder.append(" ORDER BY ");
|
220 |
first = true;
|
221 |
for (OrderByBuilder item : this.order_by) { |
222 |
if (first) {
|
223 |
first = false;
|
224 |
} else {
|
225 |
builder.append(", ");
|
226 |
} |
227 |
builder.append(item.toString()); |
228 |
} |
229 |
} |
230 |
|
231 |
if ( this.has_limit() && this.has_offset() ) { |
232 |
builder.append(" LIMIT ");
|
233 |
builder.append(this.limit);
|
234 |
builder.append(" OFFSET ");
|
235 |
builder.append(this.offset);
|
236 |
|
237 |
} else if ( this.has_limit()) { |
238 |
builder.append(" LIMIT ");
|
239 |
builder.append(this.limit);
|
240 |
|
241 |
} else if ( this.has_offset() ) { |
242 |
builder.append(" LIMIT -1 OFFSET ");
|
243 |
builder.append(this.offset);
|
244 |
} |
245 |
return builder.toString();
|
246 |
|
247 |
} |
248 |
} |
249 |
|
250 |
@Override
|
251 |
public String bytearray(byte[] data) { |
252 |
// SpatiaLite usa un formato diferencte para especificar un array de
|
253 |
// bytes. En lugar de 0x... usa x'...' .
|
254 |
StringBuilder builder = new StringBuilder(); |
255 |
builder.append("x'");
|
256 |
for (byte abyte : data) { |
257 |
int v = abyte & 0xff; |
258 |
builder.append(String.format("%02x", v)); |
259 |
} |
260 |
builder.append("'");
|
261 |
return builder.toString();
|
262 |
} |
263 |
|
264 |
@Override
|
265 |
public Object sqlgeometrydimension(int type, int subtype) { |
266 |
//'XY' or 2: 2D points, identified by X and Y coordinates
|
267 |
//'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
|
268 |
//'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
|
269 |
//'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
|
270 |
switch(subtype) {
|
271 |
case Geometry.SUBTYPES.GEOM2D:
|
272 |
default:
|
273 |
return "XY"; |
274 |
case Geometry.SUBTYPES.GEOM2DM:
|
275 |
return "XYM"; |
276 |
case Geometry.SUBTYPES.GEOM3D:
|
277 |
return "XYZ"; |
278 |
case Geometry.SUBTYPES.GEOM3DM:
|
279 |
return "XYZM"; |
280 |
} |
281 |
} |
282 |
|
283 |
public SpatiaLiteHelper getHelper() {
|
284 |
return (SpatiaLiteHelper) this.helper; |
285 |
} |
286 |
|
287 |
@Override
|
288 |
public Disposable setParameters(PreparedStatement st, FeatureProvider feature) { |
289 |
try {
|
290 |
FeatureType featureType = feature.getType(); |
291 |
List<Object> values = new ArrayList<>(); |
292 |
for (Parameter parameter : this.getParameters()) { |
293 |
if (parameter.is_constant()) {
|
294 |
values.add(parameter.getValue()); |
295 |
} else {
|
296 |
String name = parameter.getName();
|
297 |
FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name); |
298 |
switch( descriptor.getType() ) {
|
299 |
case DataTypes.DATE:
|
300 |
Date value = (Date)(feature.get(name)); |
301 |
if( value == null ) { |
302 |
values.add(null);
|
303 |
} else {
|
304 |
values.add(value.getTime()); |
305 |
} |
306 |
break;
|
307 |
case DataTypes.GEOMETRY:
|
308 |
Geometry geom = this.getHelper().forceGeometryType(
|
309 |
descriptor.getGeomType(), |
310 |
(Geometry)(feature.get(name)) |
311 |
); |
312 |
values.add(geom); |
313 |
break;
|
314 |
default:
|
315 |
values.add(feature.get(name)); |
316 |
break;
|
317 |
} |
318 |
} |
319 |
} |
320 |
return this.setStatementParameters(st, values, this.geometry_support_type()); |
321 |
} catch (Exception ex) { |
322 |
String f = "unknow"; |
323 |
try {
|
324 |
f = feature.toString(); |
325 |
} catch (Exception ex2) { |
326 |
// Do nothing
|
327 |
} |
328 |
throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex); |
329 |
} |
330 |
} |
331 |
|
332 |
public class SpatiaLiteCreateIndexBuilder extends CreateIndexBuilderBase { |
333 |
|
334 |
@Override
|
335 |
public List<String> toStrings() { |
336 |
List<String> sqls = new ArrayList<>(); |
337 |
if( this.isSpatial && this.columns.size()==1) { |
338 |
StringBuilder builder = new StringBuilder(); |
339 |
builder.append("SELECT CreateSpatialIndex('");
|
340 |
builder.append(this.table().getName());
|
341 |
builder.append("', '");
|
342 |
builder.append(this.columns.get(0)); |
343 |
builder.append("')");
|
344 |
sqls.add(builder.toString()); |
345 |
} else {
|
346 |
StringBuilder builder = new StringBuilder(); |
347 |
builder.append("CREATE ");
|
348 |
if( this.isUnique ) { |
349 |
builder.append("UNIQUE ");
|
350 |
} |
351 |
builder.append("INDEX ");
|
352 |
if( this.ifNotExist ) { |
353 |
builder.append("IF NOT EXISTS ");
|
354 |
} |
355 |
builder.append(identifier(this.indexName));
|
356 |
builder.append(" ON ");
|
357 |
builder.append(this.table.toString());
|
358 |
builder.append(" ( ");
|
359 |
boolean is_first_column = true; |
360 |
for( String column : this.columns) { |
361 |
if( is_first_column ) {
|
362 |
is_first_column = false;
|
363 |
} else {
|
364 |
builder.append(", ");
|
365 |
} |
366 |
builder.append(column); |
367 |
} |
368 |
builder.append(" )");
|
369 |
sqls.add(builder.toString()); |
370 |
} |
371 |
return sqls;
|
372 |
} |
373 |
} |
374 |
|
375 |
@Override
|
376 |
protected TableNameBuilder createTableNameBuilder() {
|
377 |
return new SpatiaLiteTableNameBuilderBase(); |
378 |
} |
379 |
|
380 |
@Override
|
381 |
protected CreateTableBuilder createCreateTableBuilder() {
|
382 |
return new SpatiaLiteCreateTableBuilder(); |
383 |
} |
384 |
|
385 |
@Override
|
386 |
protected SelectBuilder createSelectBuilder() {
|
387 |
return new SpatiaLiteSelectBuilderBase(); |
388 |
} |
389 |
|
390 |
@Override
|
391 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
392 |
return new SpatiaLiteCreateIndexBuilder(); |
393 |
} |
394 |
|
395 |
@Override
|
396 |
protected AlterTableBuilder createAlterTableBuilder() {
|
397 |
return new SpatialiteAlterTableBuilder(); |
398 |
} |
399 |
|
400 |
public class SpatialiteAlterTableBuilder extends AlterTableBuilderBase { |
401 |
|
402 |
@Override
|
403 |
public List<String> toStrings() { |
404 |
// Notes:
|
405 |
// - Alter column is not supported in SQLite
|
406 |
// - Fields are automatically created as serial if they are INTEGER PRIMARY KEY
|
407 |
// - It is not possible to define serial fields for other columns
|
408 |
List<String> sqls = new ArrayList<>(); |
409 |
if( this.isEmpty() ) { |
410 |
return sqls;
|
411 |
} |
412 |
for (String column : drops) { |
413 |
StringBuilder builder = new StringBuilder(); |
414 |
builder.append("ALTER TABLE ");
|
415 |
builder.append(this.table.toString());
|
416 |
builder.append(" DROP COLUMN IF EXISTS ");
|
417 |
builder.append(identifier(column)); |
418 |
sqls.add(builder.toString()); |
419 |
} |
420 |
|
421 |
for (ColumnDescriptor column : adds) {
|
422 |
if (column.isGeometry()) {
|
423 |
String addGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######}, {3}, {4}, {5})"; |
424 |
String sql = MessageFormat.format( |
425 |
addGeometryColumn, |
426 |
this.table.toString(),
|
427 |
identifier(column.getName()), |
428 |
column.getGeometrySRSId(), |
429 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
430 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
431 |
column.allowNulls()? 0:1 |
432 |
); |
433 |
sqls.add(sql); |
434 |
} |
435 |
else {
|
436 |
StringBuilder builder = new StringBuilder(); |
437 |
builder.append("ALTER TABLE ");
|
438 |
builder.append(this.table.toString());
|
439 |
builder.append(" ADD COLUMN ");
|
440 |
builder.append(identifier(column.getName())); |
441 |
builder.append(" ");
|
442 |
builder.append( |
443 |
sqltype( |
444 |
column.getType(), |
445 |
column.getPrecision(), |
446 |
column.getSize(), |
447 |
column.getGeometryType(), |
448 |
column.getGeometrySubtype() |
449 |
) |
450 |
); |
451 |
if (column.getDefaultValue() == null) { |
452 |
if (column.allowNulls()) {
|
453 |
builder.append(" DEFAULT NULL");
|
454 |
} |
455 |
} else {
|
456 |
builder.append(" DEFAULT '");
|
457 |
builder.append(column.getDefaultValue().toString()); |
458 |
builder.append("'");
|
459 |
} |
460 |
if (column.allowNulls()) {
|
461 |
builder.append(" NULL");
|
462 |
} else {
|
463 |
builder.append(" NOT NULL");
|
464 |
} |
465 |
if (column.isPrimaryKey()) {
|
466 |
builder.append(" PRIMARY KEY");
|
467 |
} |
468 |
sqls.add(builder.toString()); |
469 |
} |
470 |
} |
471 |
for (Pair<String,String> pair : renames) { |
472 |
StringBuilder builder = new StringBuilder(); |
473 |
builder.append("ALTER TABLE ");
|
474 |
builder.append(this.table.toString());
|
475 |
builder.append(" RENAME COLUMN ");
|
476 |
builder.append(identifier(pair.getLeft())); |
477 |
builder.append(" TO ");
|
478 |
builder.append(identifier(pair.getRight())); |
479 |
sqls.add(builder.toString()); |
480 |
} |
481 |
return sqls;
|
482 |
} |
483 |
} |
484 |
|
485 |
private ColumnDescriptor getDescriptorForUseInIndex(Value arg) {
|
486 |
if( arg instanceof Variable ) { |
487 |
ColumnDescriptor descriptor = ((Variable)arg).getDescriptor(); |
488 |
if( descriptor.getType()==DataTypes.GEOMETRY && descriptor.isIndexed() ) {
|
489 |
return descriptor;
|
490 |
} |
491 |
} |
492 |
// Aqui probablemente estaria bien comprobar si el argumento es la funcion
|
493 |
// ST_Envelope sobre un campo geometria y tambien usar el indice espacial
|
494 |
// para cubrir el caso que se use algo como:
|
495 |
// ST_Intersecta(x, ST_Envelope("geom"))
|
496 |
return null; |
497 |
} |
498 |
|
499 |
private ColumnDescriptor getDescriptorForUseInIndex(Value... args) {
|
500 |
for (Value arg : args) {
|
501 |
ColumnDescriptor descriptor = getDescriptorForUseInIndex(arg); |
502 |
if( descriptor != null ) { |
503 |
return descriptor;
|
504 |
} |
505 |
} |
506 |
return null; |
507 |
} |
508 |
|
509 |
@Override
|
510 |
public Function ST_Intersects(Value geom1, Value geom2) {
|
511 |
// return super.ST_Intersects(geom1, geom2);
|
512 |
JDBCStoreParameters jdbcparams = null;
|
513 |
ColumnDescriptor descriptor = getDescriptorForUseInIndex(geom1, geom2); |
514 |
|
515 |
if( descriptor != null ) { |
516 |
jdbcparams = (JDBCStoreParameters) descriptor.getStoreParameters(); |
517 |
} |
518 |
Function f; |
519 |
if( jdbcparams!=null ) { |
520 |
// Usamos indices espaciales
|
521 |
f = function( |
522 |
"ST_Intersects",
|
523 |
"(ST_Intersects({0},{1}) AND ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = ''"+jdbcparams.getTable()+"'' AND f_geometry_column = ''"+descriptor.getName()+"'' AND search_frame = \""+jdbcparams.getTable()+"\".\""+descriptor.getName()+"\"))", |
524 |
geom1, |
525 |
geom2 |
526 |
); |
527 |
} else {
|
528 |
f = super.ST_Intersects(geom1, geom2);
|
529 |
} |
530 |
return f;
|
531 |
} |
532 |
} |