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