svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.plugin / org.gvsig.h2spatial / org.gvsig.h2spatial.h2gis132 / org.gvsig.h2spatial.h2gis132.provider / src / main / java / org / gvsig / fmap / dal / store / h2 / H2SpatialSQLBuilder.java @ 46505
History | View | Annotate | Download (33.5 KB)
1 | 45472 | jjdelcerro | package org.gvsig.fmap.dal.store.h2; |
---|---|---|---|
2 | |||
3 | import java.sql.PreparedStatement; |
||
4 | import java.sql.SQLException; |
||
5 | import java.sql.Time; |
||
6 | import java.sql.Timestamp; |
||
7 | import java.text.MessageFormat; |
||
8 | import java.util.ArrayList; |
||
9 | import java.util.Date; |
||
10 | import java.util.List; |
||
11 | import java.util.Objects; |
||
12 | 46102 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
13 | 45472 | jjdelcerro | import org.apache.commons.lang3.tuple.Pair; |
14 | 46271 | jjdelcerro | import org.cresques.cts.IProjection; |
15 | 45472 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter; |
16 | import org.gvsig.expressionevaluator.ExpressionUtils; |
||
17 | import org.gvsig.expressionevaluator.Formatter; |
||
18 | 46271 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper; |
19 | 45472 | jjdelcerro | import org.gvsig.fmap.dal.DataTypes; |
20 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
21 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
22 | import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
||
23 | import org.gvsig.fmap.dal.store.h2.expressionbuilderformatter.H2SpatialFormatter; |
||
24 | 46271 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
25 | 45472 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
26 | import org.gvsig.fmap.geom.Geometry; |
||
27 | import org.gvsig.fmap.geom.exception.CreateGeometryException; |
||
28 | 46271 | jjdelcerro | import org.gvsig.fmap.geom.operation.GeometryOperationNotSupportedException; |
29 | import org.gvsig.fmap.geom.type.GeometryType; |
||
30 | 45472 | jjdelcerro | import org.gvsig.tools.dataTypes.DataTypeUtils; |
31 | import org.gvsig.tools.dispose.Disposable; |
||
32 | |||
33 | public class H2SpatialSQLBuilder extends JDBCSQLBuilderBase { |
||
34 | |||
35 | protected Formatter formatter = null; |
||
36 | |||
37 | public H2SpatialSQLBuilder(H2SpatialHelper helper) {
|
||
38 | super(helper);
|
||
39 | |||
40 | //
|
||
41 | // H2/H2GIS SQL functions reference list
|
||
42 | //
|
||
43 | // http://www.h2database.com/html/functions.html
|
||
44 | // http://www.h2gis.org/docs/1.3/functions/
|
||
45 | //
|
||
46 | // http://www.h2database.com/html/grammar.html
|
||
47 | //
|
||
48 | // http://www.h2database.com/html/datatypes.html
|
||
49 | //
|
||
50 | //
|
||
51 | |||
52 | |||
53 | this.defaultSchema = "PUBLIC"; |
||
54 | this.supportSchemas = true; |
||
55 | this.allowAutomaticValues = true; |
||
56 | this.geometrySupportType = this.helper.getGeometrySupportType(); |
||
57 | this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
||
58 | |||
59 | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
||
60 | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
||
61 | |||
62 | this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE SAMPLE_SIZE 0"; |
||
63 | |||
64 | this.type_boolean = "BOOLEAN"; |
||
65 | this.type_byte = "TINYINT"; |
||
66 | this.type_bytearray = "BLOB"; |
||
67 | this.type_geometry = "GEOMETRY"; |
||
68 | this.type_char = "CHAR"; |
||
69 | this.type_date = "DATE"; |
||
70 | this.type_double = "DOUBLE"; |
||
71 | this.type_decimal_ps = "DECIMAL({0,Number,##########},{1,Number,##########})"; |
||
72 | this.type_decimal_p = "DECIMAL({0,Number,##########})"; |
||
73 | this.type_float = "REAL"; |
||
74 | this.type_int = "INTEGER"; |
||
75 | this.type_long = "BIGINT"; |
||
76 | this.type_string = "CLOB"; |
||
77 | this.type_string_p = "VARCHAR({0,Number,##########})"; |
||
78 | this.type_time = "TIME"; |
||
79 | this.type_timestamp = "TIMESTAMP"; |
||
80 | this.type_version = "VARCHAR"; |
||
81 | this.type_URI = "VARCHAR"; |
||
82 | this.type_URL = "VARCHAR"; |
||
83 | this.type_FILE = "VARCHAR"; |
||
84 | this.type_FOLDER = "VARCHAR"; |
||
85 | } |
||
86 | |||
87 | @Override
|
||
88 | 46104 | omartinez | public Formatter formatter() { |
89 | 45472 | jjdelcerro | if( this.formatter==null ) { |
90 | this.formatter = new H2SpatialFormatter(this); |
||
91 | } |
||
92 | return this.formatter; |
||
93 | } |
||
94 | |||
95 | public class H2SpatialTableNameBuilderBase extends TableNameBuilderBase { |
||
96 | |||
97 | @Override
|
||
98 | public boolean has_database() { |
||
99 | return false; |
||
100 | } |
||
101 | |||
102 | } |
||
103 | |||
104 | public class H2SpatialCreateIndexBuilder extends CreateIndexBuilderBase { |
||
105 | |||
106 | @Override
|
||
107 | public List<String> toStrings(Formatter formatter) { |
||
108 | StringBuilder builder = new StringBuilder(); |
||
109 | builder.append("CREATE ");
|
||
110 | if( this.isUnique ) { |
||
111 | builder.append("UNIQUE ");
|
||
112 | } |
||
113 | if( this.isSpatial ) { |
||
114 | builder.append("SPATIAL ");
|
||
115 | } |
||
116 | builder.append("INDEX ");
|
||
117 | if( this.ifNotExist ) { |
||
118 | builder.append("IF NOT EXISTS ");
|
||
119 | } |
||
120 | builder.append(as_identifier(this.indexName));
|
||
121 | builder.append(" ON ");
|
||
122 | builder.append(this.table.toString(formatter));
|
||
123 | builder.append(" ( ");
|
||
124 | boolean is_first_column = true; |
||
125 | for( String column : this.columns) { |
||
126 | if( is_first_column ) {
|
||
127 | is_first_column = false;
|
||
128 | } else {
|
||
129 | builder.append(", ");
|
||
130 | } |
||
131 | builder.append(as_identifier(column)); |
||
132 | } |
||
133 | builder.append(" )");
|
||
134 | |||
135 | List<String> sqls = new ArrayList<>(); |
||
136 | sqls.add(builder.toString()); |
||
137 | return sqls;
|
||
138 | } |
||
139 | |||
140 | } |
||
141 | 46102 | jjdelcerro | |
142 | 45472 | jjdelcerro | protected class H2SpatialAlterTableBuilderBase extends AlterTableBuilderBase { |
143 | 46102 | jjdelcerro | |
144 | 45472 | jjdelcerro | @Override
|
145 | public List<String> toStrings(Formatter formatter) { |
||
146 | List<String> sqls = new ArrayList<>(); |
||
147 | if( this.isEmpty() ) { |
||
148 | return sqls;
|
||
149 | } |
||
150 | 46102 | jjdelcerro | |
151 | String local_drop_primary_key_column = this.drop_primary_key_column; |
||
152 | 45472 | jjdelcerro | for (String column : drops) { |
153 | StringBuilder builder = new StringBuilder(); |
||
154 | builder.append("ALTER TABLE ");
|
||
155 | builder.append(this.table.toString(formatter));
|
||
156 | builder.append(" DROP COLUMN IF EXISTS ");
|
||
157 | builder.append(as_identifier(column)); |
||
158 | sqls.add(builder.toString()); |
||
159 | 46102 | jjdelcerro | if( StringUtils.equals(local_drop_primary_key_column, column) ) {
|
160 | // Si hemos eliminado la columna sobre la que estaba la pk, ya no hay que
|
||
161 | // eliminar la pk
|
||
162 | local_drop_primary_key_column = null;
|
||
163 | } |
||
164 | 45472 | jjdelcerro | } |
165 | for (ColumnDescriptor column : adds) {
|
||
166 | StringBuilder builder = new StringBuilder(); |
||
167 | builder.append("ALTER TABLE ");
|
||
168 | builder.append(this.table.toString(formatter));
|
||
169 | builder.append(" ADD COLUMN ");
|
||
170 | builder.append(as_identifier(column.getName())); |
||
171 | builder.append(" ");
|
||
172 | if( column.getType() == DataTypes.INT && column.isAutomatic() ) {
|
||
173 | builder.append(" SERIAL");
|
||
174 | } else {
|
||
175 | builder.append( |
||
176 | sqltype( |
||
177 | column.getType(), |
||
178 | column.getSize(), |
||
179 | column.getPrecision(), |
||
180 | column.getScale(), |
||
181 | column.getGeometryType(), |
||
182 | column.getGeometrySubtype() |
||
183 | ) |
||
184 | ); |
||
185 | } |
||
186 | if (column.getDefaultValue() == null) { |
||
187 | if (column.allowNulls()) {
|
||
188 | builder.append(" DEFAULT NULL");
|
||
189 | } |
||
190 | } else {
|
||
191 | builder.append(" DEFAULT '");
|
||
192 | builder.append(Objects.toString(column.getDefaultValue(),""));
|
||
193 | builder.append("'");
|
||
194 | } |
||
195 | if (column.allowNulls()) {
|
||
196 | builder.append(" NULL");
|
||
197 | } else {
|
||
198 | builder.append(" NOT NULL");
|
||
199 | } |
||
200 | 46102 | jjdelcerro | sqls.add(builder.toString()); |
201 | |||
202 | 45472 | jjdelcerro | if (column.isPrimaryKey()) {
|
203 | 46102 | jjdelcerro | String sql;
|
204 | sql = MessageFormat.format(
|
||
205 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT \"{2}\" PRIMARY KEY ( \"{3}\" ) INDEX \"{4}\"",
|
||
206 | this.table().getSchema(),
|
||
207 | this.table().getName(),
|
||
208 | this.getConstrainName("PK", column.getName()), |
||
209 | column.getName(), |
||
210 | this.getConstrainName("IDX", column.getName()) |
||
211 | ); |
||
212 | sqls.add(sql); |
||
213 | } else if( column.isIndexed() ) { |
||
214 | String sql;
|
||
215 | sql = MessageFormat.format(
|
||
216 | "CREATE INDEX IF NOT EXISTS \"{0}\" ON \"{1}\".\"{2}\" ( \"{3}\" )",
|
||
217 | this.getConstrainName("IDX", column.getName()), |
||
218 | this.table().getSchema(),
|
||
219 | this.table().getName(),
|
||
220 | column.getName() |
||
221 | ); |
||
222 | sqls.add(sql); |
||
223 | 45472 | jjdelcerro | } |
224 | |||
225 | if( column.isGeometry() ) {
|
||
226 | 46102 | jjdelcerro | String constraint_name = this.getConstrainName("GEOM", column.getName()); |
227 | 45472 | jjdelcerro | String sql;
|
228 | 45691 | fdiaz | if (column.getGeometrySRSId() == null) { |
229 | if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
||
230 | sql = MessageFormat.format(
|
||
231 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###}, TRUE)",
|
||
232 | this.table().getSchema(),
|
||
233 | this.table().getName(),
|
||
234 | constraint_name, |
||
235 | column.getName(), |
||
236 | (int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype())
|
||
237 | ); |
||
238 | } else {
|
||
239 | sql = MessageFormat.format(
|
||
240 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###}, TRUE)",
|
||
241 | this.table().getSchema(),
|
||
242 | this.table().getName(),
|
||
243 | constraint_name, |
||
244 | column.getName(), |
||
245 | (int)sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()),
|
||
246 | (int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype())
|
||
247 | ); |
||
248 | } |
||
249 | 45472 | jjdelcerro | } else {
|
250 | 45691 | fdiaz | if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
251 | sql = MessageFormat.format(
|
||
252 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_CoordDim(\"{3}\") = {4,number,###} AND ST_SRID(\"{3}\") = {5,number,#####}, TRUE)",
|
||
253 | this.table().getSchema(),
|
||
254 | this.table().getName(),
|
||
255 | constraint_name, |
||
256 | column.getName(), |
||
257 | (int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()),
|
||
258 | (int)column.getGeometrySRSId()
|
||
259 | ); |
||
260 | } else {
|
||
261 | sql = MessageFormat.format(
|
||
262 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK NVL2(\"{3}\", ST_GeometryTypeCode(\"{3}\") = {4,number,###} AND ST_CoordDim(\"{3}\") = {5,number,###} AND ST_SRID(\"{3}\") = {6,number,#####}, TRUE)",
|
||
263 | this.table().getSchema(),
|
||
264 | this.table().getName(),
|
||
265 | constraint_name, |
||
266 | column.getName(), |
||
267 | (int)sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()),
|
||
268 | (int)sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()),
|
||
269 | (int)column.getGeometrySRSId()
|
||
270 | ); |
||
271 | } |
||
272 | 45472 | jjdelcerro | } |
273 | sqls.add(sql); |
||
274 | } |
||
275 | 46102 | jjdelcerro | if( StringUtils.equals(local_drop_primary_key_column, column.getName()) ) {
|
276 | // Si la columna de la que tenemos que quitar la pk la acabamos de a?adir
|
||
277 | // ya se habra a?adido como toca, y no tendremos que quitar luego la pk.
|
||
278 | local_drop_primary_key_column = null;
|
||
279 | } |
||
280 | 45472 | jjdelcerro | } |
281 | 46102 | jjdelcerro | |
282 | if( StringUtils.isNotBlank(local_drop_primary_key_column) ) {
|
||
283 | String sql;
|
||
284 | sql = MessageFormat.format(
|
||
285 | "ALTER TABLE \"{0}\".\"{1}\" DROP CONSTRAINT IF EXISTS \"{2}\"",
|
||
286 | this.table().getSchema(),
|
||
287 | this.table().getName(),
|
||
288 | this.getConstrainName("PK", local_drop_primary_key_column) |
||
289 | ); |
||
290 | sqls.add(sql); |
||
291 | sql = MessageFormat.format(
|
||
292 | "DROP INDEX IF EXISTS \"{0}\"",
|
||
293 | this.getConstrainName("IDX", local_drop_primary_key_column) |
||
294 | ); |
||
295 | sqls.add(sql); |
||
296 | } |
||
297 | |||
298 | 45472 | jjdelcerro | for (ColumnDescriptor column : alters) {
|
299 | StringBuilder builder = new StringBuilder(); |
||
300 | builder.append("ALTER TABLE ");
|
||
301 | builder.append(this.table.toString(formatter));
|
||
302 | builder.append(" ALTER COLUMN ");
|
||
303 | builder.append(as_identifier(column.getName())); |
||
304 | builder.append(" ");
|
||
305 | builder.append( |
||
306 | sqltype( |
||
307 | column.getType(), |
||
308 | column.getSize(), |
||
309 | column.getPrecision(), |
||
310 | column.getScale(), |
||
311 | column.getGeometryType(), |
||
312 | column.getGeometrySubtype() |
||
313 | ) |
||
314 | ); |
||
315 | if (column.getDefaultValue() == null) { |
||
316 | if (column.allowNulls()) {
|
||
317 | builder.append(" DEFAULT NULL");
|
||
318 | } |
||
319 | } else {
|
||
320 | builder.append(" DEFAULT '");
|
||
321 | builder.append(column.getDefaultValue().toString()); |
||
322 | builder.append("'");
|
||
323 | } |
||
324 | if( column.isAutomatic() ) {
|
||
325 | builder.append(" AUTO_INCREMENT");
|
||
326 | } |
||
327 | 46102 | jjdelcerro | if (column.allowNulls()) {
|
328 | builder.append(" NULL");
|
||
329 | } else {
|
||
330 | builder.append(" NOT NULL");
|
||
331 | } |
||
332 | 45472 | jjdelcerro | sqls.add(builder.toString()); |
333 | 46102 | jjdelcerro | if (column.isPrimaryKey()) {
|
334 | String sql;
|
||
335 | sql = MessageFormat.format(
|
||
336 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT \"{2}\" PRIMARY KEY ( \"{3}\" ) INDEX \"{4}\"",
|
||
337 | this.table().getSchema(),
|
||
338 | this.table().getName(),
|
||
339 | this.getConstrainName("PK", column.getName()), |
||
340 | column.getName(), |
||
341 | this.getConstrainName("IDX", column.getName()) |
||
342 | ); |
||
343 | sqls.add(sql); |
||
344 | } |
||
345 | 45472 | jjdelcerro | if( column.isGeometry() ) {
|
346 | String sql;
|
||
347 | sql = MessageFormat.format(
|
||
348 | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}",
|
||
349 | this.table().getSchema(),
|
||
350 | this.table().getName(),
|
||
351 | 46102 | jjdelcerro | this.getConstrainName("DIM", column.getName()), |
352 | 45472 | jjdelcerro | column.getName(), |
353 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) |
||
354 | ); |
||
355 | sqls.add(sql); |
||
356 | } |
||
357 | } |
||
358 | for (Pair<String,String> pair : renames) { |
||
359 | StringBuilder builder = new StringBuilder(); |
||
360 | builder.append("ALTER TABLE ");
|
||
361 | builder.append(this.table.toString(formatter));
|
||
362 | builder.append(" RENAME COLUMN ");
|
||
363 | builder.append(as_identifier(pair.getLeft())); |
||
364 | builder.append(" TO ");
|
||
365 | builder.append(as_identifier(pair.getRight())); |
||
366 | sqls.add(builder.toString()); |
||
367 | } |
||
368 | return sqls;
|
||
369 | } |
||
370 | |||
371 | } |
||
372 | |||
373 | protected class H2SpatialCreateTableBuilder extends CreateTableBuilderBase { |
||
374 | |||
375 | @Override
|
||
376 | public List<String> toStrings(Formatter formatter) { |
||
377 | |||
378 | List<String> sqls = new ArrayList<>(); |
||
379 | StringBuilder builder = new StringBuilder(); |
||
380 | |||
381 | builder.append("CREATE TABLE ");
|
||
382 | builder.append(this.table.toString(formatter));
|
||
383 | builder.append(" (");
|
||
384 | boolean first = true; |
||
385 | for (ColumnDescriptor column : columns) {
|
||
386 | |||
387 | if (first) {
|
||
388 | first = false;
|
||
389 | } else {
|
||
390 | builder.append(", ");
|
||
391 | } |
||
392 | builder.append(as_identifier(column.getName())); |
||
393 | builder.append(" ");
|
||
394 | builder.append( |
||
395 | sqltype( |
||
396 | column.getType(), |
||
397 | column.getSize(), |
||
398 | column.getPrecision(), |
||
399 | column.getScale(), |
||
400 | column.getGeometryType(), |
||
401 | column.getGeometrySubtype() |
||
402 | ) |
||
403 | ); |
||
404 | if( column.isGeometry() ) {
|
||
405 | //
|
||
406 | // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view
|
||
407 | // https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/spatial/type/GeometryTypeFromConstraint.java
|
||
408 | //
|
||
409 | 45691 | fdiaz | if (column.getGeometrySRSId() == null) { |
410 | if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
||
411 | builder.append( |
||
412 | MessageFormat.format(
|
||
413 | " CHECK NVL2(\"{0}\", ST_CoordDim(\"{0}\") = {1}, TRUE)",
|
||
414 | column.getName(), |
||
415 | sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype())) |
||
416 | ); |
||
417 | } else {
|
||
418 | builder.append( |
||
419 | MessageFormat.format(
|
||
420 | " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1} AND ST_CoordDim(\"{0}\") = {2}, TRUE)",
|
||
421 | column.getName(), |
||
422 | sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()), |
||
423 | sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype())) |
||
424 | ); |
||
425 | } |
||
426 | 45472 | jjdelcerro | } else {
|
427 | 45691 | fdiaz | if ((int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()) == 0) { |
428 | builder.append( |
||
429 | MessageFormat.format(
|
||
430 | " CHECK NVL2(\"{0}\", ST_CoordDim(\"{0}\") = {1,number,###} AND ST_SRID(\"{0}\") = {2,number,#####}, TRUE)",
|
||
431 | column.getName(), |
||
432 | (int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()),
|
||
433 | (int) column.getGeometrySRSId()
|
||
434 | ) |
||
435 | ); |
||
436 | |||
437 | } else {
|
||
438 | builder.append( |
||
439 | MessageFormat.format(
|
||
440 | " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1,number,###} AND ST_CoordDim(\"{0}\") = {2,number,###} AND ST_SRID(\"{0}\") = {3,number,#####}, TRUE)",
|
||
441 | column.getName(), |
||
442 | (int) sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype()),
|
||
443 | (int) sqlgeometrynumdimension(column.getGeometryType(), column.getGeometrySubtype()),
|
||
444 | (int) column.getGeometrySRSId()
|
||
445 | ) |
||
446 | ); |
||
447 | } |
||
448 | 45472 | jjdelcerro | } |
449 | } else {
|
||
450 | if (column.isPrimaryKey()) {
|
||
451 | builder.append(" PRIMARY KEY");
|
||
452 | if( column.isAutomatic() ) {
|
||
453 | builder.append(" AUTO_INCREMENT");
|
||
454 | } |
||
455 | } else {
|
||
456 | if( column.isAutomatic() ) {
|
||
457 | builder.append(" AUTO_INCREMENT");
|
||
458 | } |
||
459 | if (column.getDefaultValue() == null || |
||
460 | ExpressionUtils.isDynamicText(Objects.toString(column.getDefaultValue(), null))) {
|
||
461 | if (column.allowNulls()) {
|
||
462 | builder.append(" DEFAULT NULL");
|
||
463 | } |
||
464 | } else {
|
||
465 | switch(column.getType()) {
|
||
466 | case DataTypes.TIMESTAMP:
|
||
467 | builder.append(" DEFAULT ( TIMESTAMP '");
|
||
468 | Timestamp dtimestamp = (Timestamp) DataTypeUtils.toTimestamp(column.getDefaultValue()); |
||
469 | builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd HH:mm:ss.S}",dtimestamp)); |
||
470 | builder.append("' )");
|
||
471 | break;
|
||
472 | case DataTypes.TIME:
|
||
473 | builder.append(" DEFAULT ( TIME '");
|
||
474 | Time dtime = (Time) DataTypeUtils.toTime(column.getDefaultValue()); |
||
475 | builder.append(MessageFormat.format( "{0,date,HH:mm:ss}",dtime)); |
||
476 | builder.append("' )");
|
||
477 | break;
|
||
478 | case DataTypes.DATE:
|
||
479 | builder.append(" DEFAULT ( DATE '");
|
||
480 | java.sql.Date ddate = (java.sql.Date) DataTypeUtils.toDate(column.getDefaultValue()); |
||
481 | builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd}",ddate)); |
||
482 | builder.append("' )");
|
||
483 | break;
|
||
484 | default:
|
||
485 | builder.append(" DEFAULT '");
|
||
486 | builder.append(Objects.toString(column.getDefaultValue(),""));
|
||
487 | builder.append("'");
|
||
488 | } |
||
489 | } |
||
490 | } |
||
491 | } |
||
492 | if (!column.allowNulls()) {
|
||
493 | builder.append(" NOT NULL");
|
||
494 | } |
||
495 | } |
||
496 | builder.append(" )");
|
||
497 | sqls.add(builder.toString()); |
||
498 | return sqls;
|
||
499 | } |
||
500 | } |
||
501 | |||
502 | public class H2SpatialSelectBuilderBase extends SelectBuilderBase { |
||
503 | |||
504 | @Override
|
||
505 | public String toString(Formatter formatter) { |
||
506 | StringBuilder builder = new StringBuilder(); |
||
507 | if( !isValid(builder) ) {
|
||
508 | throw new IllegalStateException(builder.toString()); |
||
509 | } |
||
510 | builder.append("SELECT ");
|
||
511 | if( this.distinct ) { |
||
512 | builder.append("DISTINCT ");
|
||
513 | } |
||
514 | boolean first = true; |
||
515 | for (SelectColumnBuilder column : columns) {
|
||
516 | if (first) {
|
||
517 | first = false;
|
||
518 | } else {
|
||
519 | builder.append(", ");
|
||
520 | } |
||
521 | builder.append(column.toString(formatter)); |
||
522 | } |
||
523 | |||
524 | if ( this.has_from() ) { |
||
525 | builder.append(" FROM ");
|
||
526 | builder.append(this.from.toString(formatter));
|
||
527 | } |
||
528 | if ( this.has_where() ) { |
||
529 | builder.append(" WHERE ");
|
||
530 | builder.append(this.where.toString(formatter));
|
||
531 | } |
||
532 | if( this.has_group_by() ) { |
||
533 | builder.append(" GROUP BY ");
|
||
534 | builder.append(this.groupColumn.get(0).toString(formatter)); |
||
535 | for (int i = 1; i < groupColumn.size(); i++) { |
||
536 | builder.append(", ");
|
||
537 | builder.append(this.groupColumn.get(i).toString(formatter));
|
||
538 | } |
||
539 | } |
||
540 | if( this.has_order_by() ) { |
||
541 | builder.append(" ORDER BY ");
|
||
542 | first = true;
|
||
543 | for (OrderByBuilder item : this.order_by) { |
||
544 | if (first) {
|
||
545 | first = false;
|
||
546 | } else {
|
||
547 | builder.append(", ");
|
||
548 | } |
||
549 | builder.append(item.toString(formatter)); |
||
550 | } |
||
551 | } |
||
552 | |||
553 | if ( this.has_limit() && this.has_offset() ) { |
||
554 | builder.append(" LIMIT ");
|
||
555 | builder.append(this.limit);
|
||
556 | builder.append(" OFFSET ");
|
||
557 | builder.append(this.offset);
|
||
558 | |||
559 | } else if ( this.has_limit()) { |
||
560 | builder.append(" LIMIT ");
|
||
561 | builder.append(this.limit);
|
||
562 | |||
563 | } else if ( this.has_offset() ) { |
||
564 | builder.append(" LIMIT -1 OFFSET ");
|
||
565 | builder.append(this.offset);
|
||
566 | } |
||
567 | return builder.toString();
|
||
568 | |||
569 | } |
||
570 | } |
||
571 | |||
572 | @Override
|
||
573 | public Object sqlgeometrydimension(int type, int subtype) { |
||
574 | //'XY' or 2: 2D points, identified by X and Y coordinates
|
||
575 | //'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
|
||
576 | //'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
|
||
577 | //'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
|
||
578 | switch(subtype) {
|
||
579 | case Geometry.SUBTYPES.GEOM2D:
|
||
580 | default:
|
||
581 | return "XY"; |
||
582 | case Geometry.SUBTYPES.GEOM2DM:
|
||
583 | return "XYM"; |
||
584 | case Geometry.SUBTYPES.GEOM3D:
|
||
585 | return "XYZ"; |
||
586 | case Geometry.SUBTYPES.GEOM3DM:
|
||
587 | return "XYZM"; |
||
588 | } |
||
589 | } |
||
590 | |||
591 | @Override
|
||
592 | public String sqltype(int type, int size, int precision, int scale, int geomtype, int geomSubtype) { |
||
593 | if( type!=DataTypes.GEOMETRY ) {
|
||
594 | return super.sqltype(type, size, precision, scale, geomtype, geomSubtype); |
||
595 | } |
||
596 | return "GEOMETRY("+sqlgeometrytype(geomtype, geomSubtype)+")"; |
||
597 | } |
||
598 | |||
599 | @Override
|
||
600 | public Object sqlgeometrytype(int geomtype, int geomsubtype) { |
||
601 | //
|
||
602 | // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view
|
||
603 | //
|
||
604 | switch(geomtype) {
|
||
605 | case Geometry.TYPES.POINT:
|
||
606 | return 1; |
||
607 | case Geometry.TYPES.MULTIPOINT:
|
||
608 | return 4; |
||
609 | case Geometry.TYPES.LINE:
|
||
610 | return 2; |
||
611 | case Geometry.TYPES.MULTILINE:
|
||
612 | return 5; |
||
613 | case Geometry.TYPES.POLYGON:
|
||
614 | return 3; |
||
615 | case Geometry.TYPES.MULTIPOLYGON:
|
||
616 | return 6; |
||
617 | case Geometry.TYPES.GEOMETRY:
|
||
618 | default:
|
||
619 | return 0; // "GEOMETRY"; |
||
620 | } |
||
621 | } |
||
622 | |||
623 | public Object sqlgeometrynumdimension(int type, int subtype) { |
||
624 | int dimensions=2; |
||
625 | switch(subtype) {
|
||
626 | case Geometry.SUBTYPES.GEOM3D:
|
||
627 | dimensions = 3;
|
||
628 | break;
|
||
629 | case Geometry.SUBTYPES.GEOM2D:
|
||
630 | dimensions = 2;
|
||
631 | break;
|
||
632 | case Geometry.SUBTYPES.GEOM2DM:
|
||
633 | dimensions = 2; // ?????? |
||
634 | break;
|
||
635 | case Geometry.SUBTYPES.GEOM3DM:
|
||
636 | dimensions = 3; // ?????? |
||
637 | break;
|
||
638 | } |
||
639 | return dimensions;
|
||
640 | } |
||
641 | |||
642 | @Override
|
||
643 | public H2SpatialHelper getHelper() {
|
||
644 | return (H2SpatialHelper) this.helper; |
||
645 | } |
||
646 | |||
647 | @Override
|
||
648 | public Disposable setParameters(PreparedStatement st, FeatureProvider feature) { |
||
649 | try {
|
||
650 | FeatureType featureType = feature.getType(); |
||
651 | List<Object> values = new ArrayList<>(); |
||
652 | for (Parameter parameter : this.parameters()) { |
||
653 | if (parameter.is_constant()) {
|
||
654 | values.add(parameter.value()); |
||
655 | } else {
|
||
656 | String name = parameter.name();
|
||
657 | FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name); |
||
658 | switch( descriptor.getType() ) {
|
||
659 | case DataTypes.DATE:
|
||
660 | Date value = (Date)(feature.get(name)); |
||
661 | if( value == null ) { |
||
662 | values.add(null);
|
||
663 | } else {
|
||
664 | values.add(new java.sql.Date(value.getTime()));
|
||
665 | } |
||
666 | break;
|
||
667 | case DataTypes.GEOMETRY:
|
||
668 | Geometry geom = this.forceGeometryType(
|
||
669 | descriptor.getGeomType(), |
||
670 | (Geometry)(feature.get(name)) |
||
671 | ); |
||
672 | values.add(geom); |
||
673 | break;
|
||
674 | default:
|
||
675 | values.add(feature.get(name)); |
||
676 | break;
|
||
677 | } |
||
678 | } |
||
679 | } |
||
680 | return this.setStatementParameters(st, values, this.geometry_support_type()); |
||
681 | } catch (SQLException | CreateGeometryException ex) { |
||
682 | String f = "unknow"; |
||
683 | try {
|
||
684 | f = feature.toString(); |
||
685 | } catch (Exception ex2) { |
||
686 | // Do nothing
|
||
687 | } |
||
688 | throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex); |
||
689 | } |
||
690 | } |
||
691 | |||
692 | @Override
|
||
693 | protected AlterTableBuilder createAlterTableBuilder() {
|
||
694 | return new H2SpatialAlterTableBuilderBase(); |
||
695 | } |
||
696 | |||
697 | @Override
|
||
698 | public TableNameBuilder createTableNameBuilder() {
|
||
699 | return new H2SpatialTableNameBuilderBase(); |
||
700 | } |
||
701 | |||
702 | @Override
|
||
703 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
704 | return new H2SpatialCreateTableBuilder(); |
||
705 | } |
||
706 | |||
707 | @Override
|
||
708 | 46505 | fdiaz | public SelectBuilder createSelectBuilder() {
|
709 | 45472 | jjdelcerro | return new H2SpatialSelectBuilderBase(); |
710 | } |
||
711 | |||
712 | @Override
|
||
713 | protected CreateIndexBuilder createCreateIndexBuilder() {
|
||
714 | return new H2SpatialCreateIndexBuilder(); |
||
715 | } |
||
716 | |||
717 | 46271 | jjdelcerro | @Override
|
718 | public Disposable setStatementParameters(
|
||
719 | PreparedStatement st,
|
||
720 | List values,
|
||
721 | List<Integer> types, //Can be null |
||
722 | GeometryExpressionBuilderHelper.GeometrySupportType geometrySupportType) throws SQLException { |
||
723 | |||
724 | if (values == null) { |
||
725 | return new Disposable() { |
||
726 | @Override
|
||
727 | public void dispose() { |
||
728 | } |
||
729 | }; |
||
730 | } |
||
731 | if( LOGGER.isDebugEnabled() ) {
|
||
732 | debug_setStatementParameters(st, values, types, geometrySupportType); |
||
733 | } |
||
734 | byte[] bytes; |
||
735 | int columnIndex = 1; |
||
736 | try {
|
||
737 | for (Object value : values) { |
||
738 | if (value instanceof Geometry) { |
||
739 | switch(geometrySupportType) {
|
||
740 | case WKT:
|
||
741 | value = ((Geometry) value).convertToWKT(); |
||
742 | st.setObject(columnIndex, value); |
||
743 | break;
|
||
744 | case NATIVE:
|
||
745 | case WKB:
|
||
746 | bytes = toWKB((Geometry) value); |
||
747 | st.setBytes(columnIndex, bytes); |
||
748 | break;
|
||
749 | case EWKB:
|
||
750 | bytes = ((Geometry) value).convertToEWKB(); |
||
751 | st.setBytes(columnIndex, bytes); |
||
752 | break;
|
||
753 | } |
||
754 | } else {
|
||
755 | if(types == null){ |
||
756 | st.setObject(columnIndex, value); |
||
757 | } else {
|
||
758 | setStatementValue(st, columnIndex, types.get(columnIndex-1), value);
|
||
759 | } |
||
760 | |||
761 | } |
||
762 | columnIndex++; |
||
763 | } |
||
764 | return new Disposable() { |
||
765 | @Override
|
||
766 | public void dispose() { |
||
767 | } |
||
768 | }; |
||
769 | } catch(Exception ex) { |
||
770 | throw new SQLException("Can't set values for the prepared statement.", ex); |
||
771 | } |
||
772 | } |
||
773 | |||
774 | private byte[] toWKB(Geometry geom) throws Exception { |
||
775 | GeometryType geomtype = geom.getGeometryType(); |
||
776 | if( !geomtype.hasM() && !geomtype.hasZ() ) {
|
||
777 | return geom.convertToWKB();
|
||
778 | } |
||
779 | com.vividsolutions.jts.geom.Geometry geom_jts = (com.vividsolutions.jts.geom.Geometry) geom.convertTo("jts");
|
||
780 | IProjection proj = geom.getProjection(); |
||
781 | if( proj != null ) { |
||
782 | geom_jts.setSRID((int) this.srs_id(proj)); |
||
783 | } |
||
784 | com.vividsolutions.jts.io.WKBWriter writer = new com.vividsolutions.jts.io.WKBWriter(geomtype.hasZ()?3:2,true); |
||
785 | byte[] bytes = writer.write(geom_jts); |
||
786 | return bytes;
|
||
787 | } |
||
788 | 46309 | jjdelcerro | |
789 | @Override
|
||
790 | public int getMaxRecomendedSQLLength() { |
||
791 | return 10240; |
||
792 | } |
||
793 | |||
794 | 45472 | jjdelcerro | } |