svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.compat.cdc / org.gvsig.fmap.dal / org.gvsig.fmap.dal.db / org.gvsig.fmap.dal.db.h2 / src / main / java / org / gvsig / fmap / dal / store / h2 / H2SpatialSQLBuilder.java @ 44678
History | View | Annotate | Download (23.5 KB)
1 | 43377 | jjdelcerro | package org.gvsig.fmap.dal.store.h2; |
---|---|---|---|
2 | |||
3 | import java.sql.PreparedStatement; |
||
4 | 44198 | jjdelcerro | import java.sql.SQLException; |
5 | 43377 | jjdelcerro | import java.text.MessageFormat; |
6 | import java.util.ArrayList; |
||
7 | import java.util.Date; |
||
8 | import java.util.List; |
||
9 | 44296 | jjdelcerro | import java.util.Objects; |
10 | 43650 | jjdelcerro | import org.apache.commons.lang3.tuple.Pair; |
11 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter; |
12 | import org.gvsig.expressionevaluator.Formatter; |
||
13 | 43377 | jjdelcerro | import org.gvsig.fmap.dal.DataTypes; |
14 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
15 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
16 | import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
||
17 | 44198 | jjdelcerro | import org.gvsig.fmap.dal.store.h2.expressionbuilderformatter.H2SpatialFormatter; |
18 | 43377 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
19 | import org.gvsig.fmap.geom.Geometry; |
||
20 | 44198 | jjdelcerro | import org.gvsig.fmap.geom.exception.CreateGeometryException; |
21 | 43629 | jjdelcerro | import org.gvsig.tools.dispose.Disposable; |
22 | 43377 | jjdelcerro | |
23 | public class H2SpatialSQLBuilder extends JDBCSQLBuilderBase { |
||
24 | |||
25 | 44198 | jjdelcerro | protected Formatter formatter = null; |
26 | |||
27 | 43377 | jjdelcerro | public H2SpatialSQLBuilder(H2SpatialHelper helper) {
|
28 | 43687 | jjdelcerro | super(helper);
|
29 | 43377 | jjdelcerro | |
30 | //
|
||
31 | // H2/H2GIS SQL functions reference list
|
||
32 | //
|
||
33 | // http://www.h2database.com/html/functions.html
|
||
34 | // http://www.h2gis.org/docs/1.3/functions/
|
||
35 | //
|
||
36 | // http://www.h2database.com/html/grammar.html
|
||
37 | //
|
||
38 | // http://www.h2database.com/html/datatypes.html
|
||
39 | //
|
||
40 | //
|
||
41 | 44198 | jjdelcerro | |
42 | 43377 | jjdelcerro | |
43 | 44360 | omartinez | this.defaultSchema = "PUBLIC"; |
44 | 44320 | jjdelcerro | this.supportSchemas = true; |
45 | 44198 | jjdelcerro | this.allowAutomaticValues = true; |
46 | this.geometrySupportType = this.helper.getGeometrySupportType(); |
||
47 | this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
||
48 | 43377 | jjdelcerro | |
49 | 44198 | jjdelcerro | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
50 | this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
||
51 | 43377 | jjdelcerro | |
52 | 44198 | jjdelcerro | this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE SAMPLE_SIZE 0"; |
53 | 43377 | jjdelcerro | |
54 | 44198 | jjdelcerro | this.type_boolean = "BOOLEAN"; |
55 | this.type_byte = "TINYINT"; |
||
56 | 44297 | jjdelcerro | this.type_bytearray = "BLOB"; |
57 | 44198 | jjdelcerro | this.type_geometry = "GEOMETRY"; |
58 | this.type_char = "CHAR"; |
||
59 | this.type_date = "DATE"; |
||
60 | this.type_double = "DOUBLE"; |
||
61 | 44678 | jjdelcerro | this.type_decimal_ps = "DECIMAL({0,Number,##########},{1,Number,##########})"; |
62 | this.type_decimal_p = "DECIMAL({0,Number,##########})"; |
||
63 | 44198 | jjdelcerro | this.type_float = "REAL"; |
64 | this.type_int = "INTEGER"; |
||
65 | this.type_long = "BIGINT"; |
||
66 | this.type_string = "VARCHAR"; |
||
67 | 44678 | jjdelcerro | this.type_string_p = "VARCHAR({0,Number,##########})"; |
68 | 44198 | jjdelcerro | this.type_time = "TIME"; |
69 | this.type_timestamp = "TIMESTAMP"; |
||
70 | this.type_version = "VARCHAR"; |
||
71 | this.type_URI = "VARCHAR"; |
||
72 | this.type_URL = "VARCHAR"; |
||
73 | this.type_FILE = "VARCHAR"; |
||
74 | this.type_FOLDER = "VARCHAR"; |
||
75 | 43377 | jjdelcerro | } |
76 | |||
77 | 44198 | jjdelcerro | @Override
|
78 | protected Formatter formatter() { |
||
79 | if( this.formatter==null ) { |
||
80 | this.formatter = new H2SpatialFormatter(this); |
||
81 | } |
||
82 | return this.formatter; |
||
83 | } |
||
84 | |||
85 | 43388 | jjdelcerro | public class H2SpatialTableNameBuilderBase extends TableNameBuilderBase { |
86 | 43377 | jjdelcerro | |
87 | @Override
|
||
88 | public boolean has_database() { |
||
89 | return false; |
||
90 | } |
||
91 | |||
92 | } |
||
93 | 43687 | jjdelcerro | |
94 | public class H2SpatialCreateIndexBuilder extends CreateIndexBuilderBase { |
||
95 | 43377 | jjdelcerro | |
96 | 43687 | jjdelcerro | @Override
|
97 | 44198 | jjdelcerro | public List<String> toStrings(Formatter formatter) { |
98 | 43687 | jjdelcerro | StringBuilder builder = new StringBuilder(); |
99 | builder.append("CREATE ");
|
||
100 | if( this.isUnique ) { |
||
101 | builder.append("UNIQUE ");
|
||
102 | } |
||
103 | if( this.isSpatial ) { |
||
104 | builder.append("SPATIAL ");
|
||
105 | } |
||
106 | builder.append("INDEX ");
|
||
107 | if( this.ifNotExist ) { |
||
108 | builder.append("IF NOT EXISTS ");
|
||
109 | } |
||
110 | 44198 | jjdelcerro | builder.append(as_identifier(this.indexName));
|
111 | 43687 | jjdelcerro | builder.append(" ON ");
|
112 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
113 | 43687 | jjdelcerro | builder.append(" ( ");
|
114 | boolean is_first_column = true; |
||
115 | for( String column : this.columns) { |
||
116 | if( is_first_column ) {
|
||
117 | is_first_column = false;
|
||
118 | } else {
|
||
119 | builder.append(", ");
|
||
120 | } |
||
121 | 44198 | jjdelcerro | builder.append(as_identifier(column)); |
122 | 43687 | jjdelcerro | } |
123 | builder.append(" )");
|
||
124 | |||
125 | List<String> sqls = new ArrayList<>(); |
||
126 | sqls.add(builder.toString()); |
||
127 | return sqls;
|
||
128 | } |
||
129 | |||
130 | } |
||
131 | |||
132 | 43650 | jjdelcerro | protected class H2SpatialAlterTableBuilderBase extends AlterTableBuilderBase { |
133 | @Override
|
||
134 | 44198 | jjdelcerro | public List<String> toStrings(Formatter formatter) { |
135 | 43650 | jjdelcerro | List<String> sqls = new ArrayList<>(); |
136 | 43687 | jjdelcerro | if( this.isEmpty() ) { |
137 | return sqls;
|
||
138 | } |
||
139 | 43650 | jjdelcerro | for (String column : drops) { |
140 | StringBuilder builder = new StringBuilder(); |
||
141 | builder.append("ALTER TABLE ");
|
||
142 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
143 | 43650 | jjdelcerro | builder.append(" DROP COLUMN IF EXISTS ");
|
144 | 44198 | jjdelcerro | builder.append(as_identifier(column)); |
145 | 43650 | jjdelcerro | sqls.add(builder.toString()); |
146 | } |
||
147 | 43739 | jjdelcerro | for (ColumnDescriptor column : adds) {
|
148 | 43650 | jjdelcerro | StringBuilder builder = new StringBuilder(); |
149 | builder.append("ALTER TABLE ");
|
||
150 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
151 | 43650 | jjdelcerro | builder.append(" ADD COLUMN ");
|
152 | 44198 | jjdelcerro | builder.append(as_identifier(column.getName())); |
153 | 43650 | jjdelcerro | builder.append(" ");
|
154 | if( column.getType() == DataTypes.INT && column.isAutomatic() ) {
|
||
155 | builder.append(" SERIAL");
|
||
156 | } else {
|
||
157 | builder.append( |
||
158 | sqltype( |
||
159 | column.getType(), |
||
160 | 44669 | jjdelcerro | column.getSize(), |
161 | 43650 | jjdelcerro | column.getPrecision(), |
162 | 44669 | jjdelcerro | column.getScale(), |
163 | 43650 | jjdelcerro | column.getGeometryType(), |
164 | column.getGeometrySubtype() |
||
165 | ) |
||
166 | ); |
||
167 | } |
||
168 | if (column.getDefaultValue() == null) { |
||
169 | if (column.allowNulls()) {
|
||
170 | builder.append(" DEFAULT NULL");
|
||
171 | } |
||
172 | } else {
|
||
173 | builder.append(" DEFAULT '");
|
||
174 | 44296 | jjdelcerro | builder.append(Objects.toString(column.getDefaultValue(),""));
|
175 | 43650 | jjdelcerro | builder.append("'");
|
176 | } |
||
177 | if (column.allowNulls()) {
|
||
178 | builder.append(" NULL");
|
||
179 | } else {
|
||
180 | builder.append(" NOT NULL");
|
||
181 | } |
||
182 | if (column.isPrimaryKey()) {
|
||
183 | builder.append(" PRIMARY KEY");
|
||
184 | } |
||
185 | sqls.add(builder.toString()); |
||
186 | 43655 | jjdelcerro | |
187 | if( column.isGeometry() ) {
|
||
188 | 44678 | jjdelcerro | String constraint_name = "constraint_" + this.table().getName() + "_" + column.getName()+"_geom"; |
189 | 43655 | jjdelcerro | String sql;
|
190 | 44678 | jjdelcerro | if( column.getGeometrySRSId()==null ) { |
191 | sql = MessageFormat.format(
|
||
192 | "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)",
|
||
193 | this.table().getSchema(),
|
||
194 | this.table().getName(),
|
||
195 | constraint_name, |
||
196 | column.getName(), |
||
197 | sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
||
198 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()), |
||
199 | column.getGeometrySRSId() |
||
200 | ); |
||
201 | } else {
|
||
202 | sql = MessageFormat.format(
|
||
203 | "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)",
|
||
204 | this.table().getSchema(),
|
||
205 | this.table().getName(),
|
||
206 | constraint_name, |
||
207 | column.getName(), |
||
208 | sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
||
209 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()), |
||
210 | column.getGeometrySRSId() |
||
211 | ); |
||
212 | } |
||
213 | 43655 | jjdelcerro | sqls.add(sql); |
214 | } |
||
215 | 43650 | jjdelcerro | } |
216 | 43739 | jjdelcerro | for (ColumnDescriptor column : alters) {
|
217 | 43650 | jjdelcerro | StringBuilder builder = new StringBuilder(); |
218 | builder.append("ALTER TABLE ");
|
||
219 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
220 | 43650 | jjdelcerro | builder.append(" ALTER COLUMN ");
|
221 | 44198 | jjdelcerro | builder.append(as_identifier(column.getName())); |
222 | 43650 | jjdelcerro | builder.append(" ");
|
223 | builder.append( |
||
224 | sqltype( |
||
225 | column.getType(), |
||
226 | 44669 | jjdelcerro | column.getSize(), |
227 | 43650 | jjdelcerro | column.getPrecision(), |
228 | 44669 | jjdelcerro | column.getScale(), |
229 | 43650 | jjdelcerro | column.getGeometryType(), |
230 | column.getGeometrySubtype() |
||
231 | ) |
||
232 | ); |
||
233 | if (column.getDefaultValue() == null) { |
||
234 | if (column.allowNulls()) {
|
||
235 | builder.append(" DEFAULT NULL");
|
||
236 | } |
||
237 | } else {
|
||
238 | builder.append(" DEFAULT '");
|
||
239 | builder.append(column.getDefaultValue().toString()); |
||
240 | builder.append("'");
|
||
241 | } |
||
242 | if( column.isAutomatic() ) {
|
||
243 | builder.append(" AUTO_INCREMENT");
|
||
244 | } |
||
245 | sqls.add(builder.toString()); |
||
246 | 43655 | jjdelcerro | if( column.isGeometry() ) {
|
247 | String sql;
|
||
248 | String constraint_name = "constraint_" + this.table().getName() + "_" + column.getName()+"_dim"; |
||
249 | sql = MessageFormat.format(
|
||
250 | 44361 | jjdelcerro | "ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}",
|
251 | this.table().getSchema(),
|
||
252 | 43655 | jjdelcerro | this.table().getName(),
|
253 | constraint_name, |
||
254 | column.getName(), |
||
255 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) |
||
256 | ); |
||
257 | sqls.add(sql); |
||
258 | } |
||
259 | 43650 | jjdelcerro | } |
260 | for (Pair<String,String> pair : renames) { |
||
261 | StringBuilder builder = new StringBuilder(); |
||
262 | builder.append("ALTER TABLE ");
|
||
263 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
264 | 43650 | jjdelcerro | builder.append(" RENAME COLUMN ");
|
265 | 44198 | jjdelcerro | builder.append(as_identifier(pair.getLeft())); |
266 | 43650 | jjdelcerro | builder.append(" TO ");
|
267 | 44198 | jjdelcerro | builder.append(as_identifier(pair.getRight())); |
268 | 43650 | jjdelcerro | sqls.add(builder.toString()); |
269 | } |
||
270 | return sqls;
|
||
271 | } |
||
272 | |||
273 | } |
||
274 | |||
275 | 43388 | jjdelcerro | protected class H2SpatialCreateTableBuilder extends CreateTableBuilderBase { |
276 | 43377 | jjdelcerro | |
277 | @Override
|
||
278 | 44198 | jjdelcerro | public List<String> toStrings(Formatter formatter) { |
279 | 43377 | jjdelcerro | |
280 | List<String> sqls = new ArrayList<>(); |
||
281 | StringBuilder builder = new StringBuilder(); |
||
282 | |||
283 | builder.append("CREATE TABLE ");
|
||
284 | 44198 | jjdelcerro | builder.append(this.table.toString(formatter));
|
285 | 43377 | jjdelcerro | builder.append(" (");
|
286 | boolean first = true; |
||
287 | 43739 | jjdelcerro | for (ColumnDescriptor column : columns) {
|
288 | 43377 | jjdelcerro | |
289 | if (first) {
|
||
290 | first = false;
|
||
291 | } else {
|
||
292 | builder.append(", ");
|
||
293 | } |
||
294 | 44198 | jjdelcerro | builder.append(as_identifier(column.getName())); |
295 | 43377 | jjdelcerro | builder.append(" ");
|
296 | 43650 | jjdelcerro | builder.append( |
297 | sqltype( |
||
298 | column.getType(), |
||
299 | 44669 | jjdelcerro | column.getSize(), |
300 | 43650 | jjdelcerro | column.getPrecision(), |
301 | 44669 | jjdelcerro | column.getScale(), |
302 | 43377 | jjdelcerro | column.getGeometryType(), |
303 | column.getGeometrySubtype() |
||
304 | 43650 | jjdelcerro | ) |
305 | ); |
||
306 | 44678 | jjdelcerro | if( column.isGeometry() ) {
|
307 | //
|
||
308 | // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view
|
||
309 | // https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/spatial/type/GeometryTypeFromConstraint.java
|
||
310 | //
|
||
311 | if( column.getGeometrySRSId()==null ) { |
||
312 | builder.append( |
||
313 | MessageFormat.format(
|
||
314 | " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1} AND ST_CoordDim(\"{0}\") = {2}, TRUE)",
|
||
315 | column.getName(), |
||
316 | sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
||
317 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) ) |
||
318 | ); |
||
319 | } else {
|
||
320 | builder.append( |
||
321 | MessageFormat.format(
|
||
322 | " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1,number,###} AND ST_CoordDim(\"{0}\") = {2,number,###} AND ST_SRID(\"{0}\") = {3,number,#####}, TRUE)",
|
||
323 | column.getName(), |
||
324 | sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
||
325 | sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()), |
||
326 | column.getGeometrySRSId() |
||
327 | ) |
||
328 | ); |
||
329 | } |
||
330 | } else {
|
||
331 | 43377 | jjdelcerro | if (column.isPrimaryKey()) {
|
332 | builder.append(" PRIMARY KEY");
|
||
333 | 44295 | jjdelcerro | if( column.isAutomatic() ) {
|
334 | builder.append(" AUTO_INCREMENT");
|
||
335 | 43377 | jjdelcerro | } |
336 | } else {
|
||
337 | 44295 | jjdelcerro | if( column.isAutomatic() ) {
|
338 | builder.append(" AUTO_INCREMENT");
|
||
339 | } |
||
340 | if (column.getDefaultValue() == null) { |
||
341 | if (column.allowNulls()) {
|
||
342 | builder.append(" DEFAULT NULL");
|
||
343 | } |
||
344 | 43377 | jjdelcerro | } else {
|
345 | 44295 | jjdelcerro | if( column.getType() == DataTypes.DATE ) {
|
346 | builder.append(" DEFAULT ( TIMESTAMP '");
|
||
347 | Date d = (Date) column.getDefaultValue(); |
||
348 | builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd HH:mm:ss.S}",d)); |
||
349 | builder.append("' )");
|
||
350 | } else {
|
||
351 | builder.append(" DEFAULT '");
|
||
352 | 44296 | jjdelcerro | builder.append(Objects.toString(column.getDefaultValue(),""));
|
353 | 44295 | jjdelcerro | builder.append("'");
|
354 | } |
||
355 | 43377 | jjdelcerro | } |
356 | 43650 | jjdelcerro | } |
357 | 43377 | jjdelcerro | } |
358 | if (!column.allowNulls()) {
|
||
359 | builder.append(" NOT NULL");
|
||
360 | } |
||
361 | } |
||
362 | builder.append(" )");
|
||
363 | sqls.add(builder.toString()); |
||
364 | return sqls;
|
||
365 | } |
||
366 | } |
||
367 | |||
368 | 43388 | jjdelcerro | public class H2SpatialSelectBuilderBase extends SelectBuilderBase { |
369 | 43377 | jjdelcerro | |
370 | 43629 | jjdelcerro | @Override
|
371 | 43377 | jjdelcerro | protected boolean isValid(StringBuilder message) { |
372 | if( message == null ) { |
||
373 | message = new StringBuilder(); |
||
374 | } |
||
375 | if( this.has_offset() && !this.has_order_by() ) { |
||
376 | // Algunos gestores de BBDD requieren que se especifique un
|
||
377 | // orden para poder usar OFFSET. Como eso parece buena idea para
|
||
378 | // asegurar que siempre tengamos los mismo resultados, lo exijimos
|
||
379 | // siempre.
|
||
380 | message.append("Can't use OFFSET without an ORDER BY.");
|
||
381 | return false; |
||
382 | } |
||
383 | return true; |
||
384 | } |
||
385 | |||
386 | @Override
|
||
387 | 44198 | jjdelcerro | public String toString(Formatter formatter) { |
388 | 43377 | jjdelcerro | StringBuilder builder = new StringBuilder(); |
389 | if( !isValid(builder) ) {
|
||
390 | throw new IllegalStateException(builder.toString()); |
||
391 | } |
||
392 | builder.append("SELECT ");
|
||
393 | if( this.distinct ) { |
||
394 | builder.append("DISTINCT ");
|
||
395 | } |
||
396 | boolean first = true; |
||
397 | for (SelectColumnBuilder column : columns) {
|
||
398 | if (first) {
|
||
399 | first = false;
|
||
400 | } else {
|
||
401 | builder.append(", ");
|
||
402 | } |
||
403 | 44198 | jjdelcerro | builder.append(column.toString(formatter)); |
404 | 43377 | jjdelcerro | } |
405 | |||
406 | if ( this.has_from() ) { |
||
407 | builder.append(" FROM ");
|
||
408 | 44198 | jjdelcerro | builder.append(this.from.toString(formatter));
|
409 | 43377 | jjdelcerro | } |
410 | 44349 | jjdelcerro | if( this.has_group_by() ) { |
411 | builder.append(" GROUP BY ");
|
||
412 | 44378 | jjdelcerro | builder.append(this.groupColumn.get(0).toString(formatter)); |
413 | 44349 | jjdelcerro | for (int i = 1; i < groupColumn.size(); i++) { |
414 | builder.append(", ");
|
||
415 | 44378 | jjdelcerro | builder.append(this.groupColumn.get(i).toString(formatter));
|
416 | 44349 | jjdelcerro | } |
417 | } |
||
418 | 43377 | jjdelcerro | if ( this.has_where() ) { |
419 | builder.append(" WHERE ");
|
||
420 | 44198 | jjdelcerro | builder.append(this.where.toString(formatter));
|
421 | 43377 | jjdelcerro | } |
422 | |||
423 | if( this.has_order_by() ) { |
||
424 | builder.append(" ORDER BY ");
|
||
425 | first = true;
|
||
426 | for (OrderByBuilder item : this.order_by) { |
||
427 | if (first) {
|
||
428 | first = false;
|
||
429 | } else {
|
||
430 | builder.append(", ");
|
||
431 | } |
||
432 | 44198 | jjdelcerro | builder.append(item.toString(formatter)); |
433 | 43377 | jjdelcerro | } |
434 | } |
||
435 | |||
436 | if ( this.has_limit() && this.has_offset() ) { |
||
437 | builder.append(" LIMIT ");
|
||
438 | builder.append(this.limit);
|
||
439 | builder.append(" OFFSET ");
|
||
440 | builder.append(this.offset);
|
||
441 | |||
442 | } else if ( this.has_limit()) { |
||
443 | builder.append(" LIMIT ");
|
||
444 | builder.append(this.limit);
|
||
445 | |||
446 | } else if ( this.has_offset() ) { |
||
447 | builder.append(" LIMIT -1 OFFSET ");
|
||
448 | builder.append(this.offset);
|
||
449 | } |
||
450 | return builder.toString();
|
||
451 | |||
452 | } |
||
453 | } |
||
454 | |||
455 | @Override
|
||
456 | public Object sqlgeometrydimension(int type, int subtype) { |
||
457 | //'XY' or 2: 2D points, identified by X and Y coordinates
|
||
458 | //'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
|
||
459 | //'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
|
||
460 | //'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
|
||
461 | switch(subtype) {
|
||
462 | case Geometry.SUBTYPES.GEOM2D:
|
||
463 | default:
|
||
464 | return "XY"; |
||
465 | case Geometry.SUBTYPES.GEOM2DM:
|
||
466 | return "XYM"; |
||
467 | case Geometry.SUBTYPES.GEOM3D:
|
||
468 | return "XYZ"; |
||
469 | case Geometry.SUBTYPES.GEOM3DM:
|
||
470 | return "XYZM"; |
||
471 | } |
||
472 | } |
||
473 | |||
474 | 43650 | jjdelcerro | @Override
|
475 | 44669 | jjdelcerro | public String sqltype(int type, int size, int precision, int scale, int geomtype, int geomSubtype) { |
476 | 43650 | jjdelcerro | if( type!=DataTypes.GEOMETRY ) {
|
477 | 44669 | jjdelcerro | return super.sqltype(type, size, precision, scale, geomtype, geomSubtype); |
478 | 43650 | jjdelcerro | } |
479 | 44678 | jjdelcerro | return "GEOMETRY(1)"; |
480 | } |
||
481 | |||
482 | @Override
|
||
483 | public Object sqlgeometrytype(int geomtype, int geomsubtype) { |
||
484 | 43650 | jjdelcerro | //
|
485 | // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view
|
||
486 | //
|
||
487 | switch(geomtype) {
|
||
488 | case Geometry.TYPES.POINT:
|
||
489 | 44678 | jjdelcerro | return 1; |
490 | 43650 | jjdelcerro | case Geometry.TYPES.MULTIPOINT:
|
491 | 44678 | jjdelcerro | return 4; |
492 | 43650 | jjdelcerro | case Geometry.TYPES.LINE:
|
493 | 44678 | jjdelcerro | return 2; |
494 | 43650 | jjdelcerro | case Geometry.TYPES.MULTILINE:
|
495 | 44678 | jjdelcerro | return 5; |
496 | 43650 | jjdelcerro | case Geometry.TYPES.POLYGON:
|
497 | 44678 | jjdelcerro | return 3; |
498 | 43650 | jjdelcerro | case Geometry.TYPES.MULTIPOLYGON:
|
499 | 44678 | jjdelcerro | return 6; |
500 | case Geometry.TYPES.GEOMETRY:
|
||
501 | 43650 | jjdelcerro | default:
|
502 | 44678 | jjdelcerro | return 0; // "GEOMETRY"; |
503 | 43650 | jjdelcerro | } |
504 | } |
||
505 | |||
506 | 43655 | jjdelcerro | public Object sqlgeometrynumdimension(int type, int subtype) { |
507 | int dimensions=2; |
||
508 | switch(subtype) {
|
||
509 | case Geometry.SUBTYPES.GEOM3D:
|
||
510 | dimensions = 3;
|
||
511 | break;
|
||
512 | case Geometry.SUBTYPES.GEOM2D:
|
||
513 | dimensions = 2;
|
||
514 | break;
|
||
515 | case Geometry.SUBTYPES.GEOM2DM:
|
||
516 | dimensions = 2; // ?????? |
||
517 | break;
|
||
518 | case Geometry.SUBTYPES.GEOM3DM:
|
||
519 | dimensions = 3; // ?????? |
||
520 | break;
|
||
521 | } |
||
522 | return dimensions;
|
||
523 | } |
||
524 | |||
525 | 44198 | jjdelcerro | @Override
|
526 | 43377 | jjdelcerro | public H2SpatialHelper getHelper() {
|
527 | 43687 | jjdelcerro | return (H2SpatialHelper) this.helper; |
528 | 43377 | jjdelcerro | } |
529 | 43736 | jjdelcerro | |
530 | 43687 | jjdelcerro | @Override
|
531 | 43629 | jjdelcerro | public Disposable setParameters(PreparedStatement st, FeatureProvider feature) { |
532 | 43377 | jjdelcerro | try {
|
533 | FeatureType featureType = feature.getType(); |
||
534 | List<Object> values = new ArrayList<>(); |
||
535 | 44198 | jjdelcerro | for (Parameter parameter : this.parameters()) { |
536 | 43377 | jjdelcerro | if (parameter.is_constant()) {
|
537 | 44198 | jjdelcerro | values.add(parameter.value()); |
538 | 43377 | jjdelcerro | } else {
|
539 | 44198 | jjdelcerro | String name = parameter.name();
|
540 | 43377 | jjdelcerro | FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name); |
541 | switch( descriptor.getType() ) {
|
||
542 | case DataTypes.DATE:
|
||
543 | Date value = (Date)(feature.get(name)); |
||
544 | if( value == null ) { |
||
545 | values.add(null);
|
||
546 | } else {
|
||
547 | 44323 | jjdelcerro | values.add(new java.sql.Date(value.getTime()));
|
548 | 43377 | jjdelcerro | } |
549 | break;
|
||
550 | case DataTypes.GEOMETRY:
|
||
551 | 43687 | jjdelcerro | Geometry geom = this.forceGeometryType(
|
552 | 43377 | jjdelcerro | descriptor.getGeomType(), |
553 | (Geometry)(feature.get(name)) |
||
554 | ); |
||
555 | values.add(geom); |
||
556 | break;
|
||
557 | default:
|
||
558 | values.add(feature.get(name)); |
||
559 | break;
|
||
560 | } |
||
561 | } |
||
562 | } |
||
563 | 43629 | jjdelcerro | return this.setStatementParameters(st, values, this.geometry_support_type()); |
564 | 44198 | jjdelcerro | } catch (SQLException | CreateGeometryException ex) { |
565 | 43377 | jjdelcerro | String f = "unknow"; |
566 | try {
|
||
567 | f = feature.toString(); |
||
568 | } catch (Exception ex2) { |
||
569 | // Do nothing
|
||
570 | } |
||
571 | throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex); |
||
572 | } |
||
573 | } |
||
574 | 43650 | jjdelcerro | |
575 | @Override
|
||
576 | protected AlterTableBuilder createAlterTableBuilder() {
|
||
577 | return new H2SpatialAlterTableBuilderBase(); |
||
578 | } |
||
579 | 43377 | jjdelcerro | |
580 | @Override
|
||
581 | 44329 | jjdelcerro | public TableNameBuilder createTableNameBuilder() {
|
582 | 43388 | jjdelcerro | return new H2SpatialTableNameBuilderBase(); |
583 | 43377 | jjdelcerro | } |
584 | |||
585 | @Override
|
||
586 | protected CreateTableBuilder createCreateTableBuilder() {
|
||
587 | 43388 | jjdelcerro | return new H2SpatialCreateTableBuilder(); |
588 | 43377 | jjdelcerro | } |
589 | |||
590 | @Override
|
||
591 | protected SelectBuilder createSelectBuilder() {
|
||
592 | 43388 | jjdelcerro | return new H2SpatialSelectBuilderBase(); |
593 | 43377 | jjdelcerro | } |
594 | |||
595 | 43687 | jjdelcerro | @Override
|
596 | protected CreateIndexBuilder createCreateIndexBuilder() {
|
||
597 | return new H2SpatialCreateIndexBuilder(); |
||
598 | } |
||
599 | |||
600 | 43377 | jjdelcerro | } |