Revision 45008 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
| MDBSQLBuilder.java | ||
|---|---|---|
| 1 | 1 |
package org.gvsig.fmap.dal.store.mdb; |
| 2 | 2 |
|
| 3 |
import java.sql.Clob; |
|
| 3 | 4 |
import java.sql.PreparedStatement; |
| 4 | 5 |
import java.sql.SQLException; |
| 5 | 6 |
import java.sql.Timestamp; |
| ... | ... | |
| 8 | 9 |
import java.util.Date; |
| 9 | 10 |
import java.util.List; |
| 10 | 11 |
import java.util.Objects; |
| 12 |
import org.apache.commons.codec.binary.Hex; |
|
| 13 |
import org.apache.commons.lang3.mutable.MutableBoolean; |
|
| 11 | 14 |
import org.apache.commons.lang3.tuple.Pair; |
| 12 | 15 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
| 13 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_CONSTANT; |
|
| 14 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_VARIABLE; |
|
| 15 | 16 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter; |
| 16 | 17 |
import org.gvsig.expressionevaluator.Formatter; |
| 17 | 18 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper; |
| 18 |
import static org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.PARAMETER_TYPE_GEOMETRY;
|
|
| 19 |
import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometryParameter;
|
|
| 19 | 20 |
import org.gvsig.fmap.dal.DataTypes; |
| 20 | 21 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
| 21 |
import org.gvsig.fmap.dal.feature.FeatureReference; |
|
| 22 | 22 |
import org.gvsig.fmap.dal.feature.FeatureType; |
| 23 | 23 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
| 24 | 24 |
import org.gvsig.fmap.dal.store.mdb.expressionbuilderformatter.MDBFormatter; |
| 25 | 25 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| 26 |
import org.gvsig.fmap.dal.store.mdb.MDBHelper; |
|
| 27 | 26 |
import org.gvsig.fmap.geom.Geometry; |
| 28 | 27 |
import org.gvsig.fmap.geom.exception.CreateGeometryException; |
| 29 | 28 |
import org.gvsig.tools.dispose.Disposable; |
| 29 |
import org.hsqldb.jdbc.JDBCClob; |
|
| 30 | 30 |
|
| 31 | 31 |
public class MDBSQLBuilder extends JDBCSQLBuilderBase {
|
| 32 | 32 |
|
| ... | ... | |
| 44 | 44 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
| 45 | 45 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
| 46 | 46 |
|
| 47 |
this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE SAMPLE_SIZE 0";
|
|
| 47 |
this.STMT_UPDATE_TABLE_STATISTICS_table = ""; |
|
| 48 | 48 |
|
| 49 | 49 |
this.type_boolean = "BOOLEAN"; |
| 50 | 50 |
this.type_byte = "TINYINT"; |
| 51 | 51 |
this.type_bytearray = "BLOB"; |
| 52 |
this.type_geometry = "GEOMETRY";
|
|
| 52 |
this.type_geometry = "CLOB";
|
|
| 53 | 53 |
this.type_char = "CHAR"; |
| 54 | 54 |
this.type_date = "DATETIME"; |
| 55 | 55 |
this.type_double = "FLOAT"; |
| ... | ... | |
| 66 | 66 |
this.type_URI = "VARCHAR"; |
| 67 | 67 |
this.type_URL = "VARCHAR"; |
| 68 | 68 |
this.type_FILE = "VARCHAR"; |
| 69 |
this.type_FOLDER = "VARCHAR"; |
|
| 69 |
this.type_FOLDER = "VARCHAR";
|
|
| 70 | 70 |
} |
| 71 | 71 |
|
| 72 | 72 |
@Override |
| ... | ... | |
| 280 | 280 |
builder.append(" (");
|
| 281 | 281 |
boolean first = true; |
| 282 | 282 |
for (ColumnDescriptor column : columns) {
|
| 283 |
if( column.isGeometry() ) {
|
|
| 284 |
continue; |
|
| 285 |
} |
|
| 286 | 283 |
|
| 287 | 284 |
if (first) {
|
| 288 | 285 |
first = false; |
| ... | ... | |
| 291 | 288 |
} |
| 292 | 289 |
builder.append(as_identifier(column.getName())); |
| 293 | 290 |
builder.append(" ");
|
| 294 |
builder.append( |
|
| 295 |
sqltype( |
|
| 296 |
column.getType(), |
|
| 297 |
column.getSize(), |
|
| 298 |
column.getPrecision(), |
|
| 299 |
column.getScale(), |
|
| 300 |
column.getGeometryType(), |
|
| 301 |
column.getGeometrySubtype() |
|
| 302 |
) |
|
| 303 |
); |
|
| 304 | 291 |
if( column.isGeometry() ) {
|
| 305 |
// // |
|
| 306 |
// // https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view |
|
| 307 |
// // https://github.com/orbisgis/h2gis/blob/master/h2gis-functions/src/main/java/org/h2gis/functions/spatial/type/GeometryTypeFromConstraint.java |
|
| 308 |
// // |
|
| 309 |
// if( column.getGeometrySRSId()==null ) {
|
|
| 310 |
// builder.append( |
|
| 311 |
// MessageFormat.format( |
|
| 312 |
// " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1} AND ST_CoordDim(\"{0}\") = {2}, TRUE)",
|
|
| 313 |
// column.getName(), |
|
| 314 |
// sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
|
| 315 |
// sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) ) |
|
| 316 |
// ); |
|
| 317 |
// } else {
|
|
| 318 |
// builder.append( |
|
| 319 |
// MessageFormat.format( |
|
| 320 |
// " CHECK NVL2(\"{0}\", ST_GeometryTypeCode(\"{0}\") = {1,number,###} AND ST_CoordDim(\"{0}\") = {2,number,###} AND ST_SRID(\"{0}\") = {3,number,#####}, TRUE)",
|
|
| 321 |
// column.getName(), |
|
| 322 |
// sqlgeometrytype(column.getGeometryType(),column.getGeometrySubtype()), |
|
| 323 |
// sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()), |
|
| 324 |
// column.getGeometrySRSId() |
|
| 325 |
// ) |
|
| 326 |
// ); |
|
| 327 |
// } |
|
| 328 |
|
|
| 292 |
builder.append(type_geometry); |
|
| 329 | 293 |
} else {
|
| 330 |
if (column.isPrimaryKey()) {
|
|
| 331 |
builder.append(" PRIMARY KEY");
|
|
| 332 |
if( column.isAutomatic() ) {
|
|
| 333 |
builder.append(" AUTO_INCREMENT");
|
|
| 294 |
builder.append( |
|
| 295 |
sqltype( |
|
| 296 |
column.getType(), |
|
| 297 |
column.getSize(), |
|
| 298 |
column.getPrecision(), |
|
| 299 |
column.getScale(), |
|
| 300 |
column.getGeometryType(), |
|
| 301 |
column.getGeometrySubtype() |
|
| 302 |
) |
|
| 303 |
); |
|
| 304 |
} |
|
| 305 |
if (column.isPrimaryKey()) {
|
|
| 306 |
builder.append(" PRIMARY KEY");
|
|
| 307 |
if( column.isAutomatic() ) {
|
|
| 308 |
builder.append(" AUTO_INCREMENT");
|
|
| 309 |
} |
|
| 310 |
} else {
|
|
| 311 |
if( column.isAutomatic() ) {
|
|
| 312 |
builder.append(" AUTO_INCREMENT");
|
|
| 313 |
} |
|
| 314 |
if (column.getDefaultValue() == null) {
|
|
| 315 |
if (column.allowNulls()) {
|
|
| 316 |
builder.append(" DEFAULT NULL");
|
|
| 334 | 317 |
} |
| 335 | 318 |
} else {
|
| 336 |
if( column.isAutomatic() ) {
|
|
| 337 |
builder.append(" AUTO_INCREMENT");
|
|
| 338 |
} |
|
| 339 |
if (column.getDefaultValue() == null) {
|
|
| 340 |
if (column.allowNulls()) {
|
|
| 341 |
builder.append(" DEFAULT NULL");
|
|
| 342 |
} |
|
| 319 |
if( column.getType() == DataTypes.DATE ) {
|
|
| 320 |
builder.append(" DEFAULT ( TIMESTAMP '");
|
|
| 321 |
Date d = (Date) column.getDefaultValue(); |
|
| 322 |
builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd HH:mm:ss.S}",d));
|
|
| 323 |
builder.append("' )");
|
|
| 343 | 324 |
} else {
|
| 344 |
if( column.getType() == DataTypes.DATE ) {
|
|
| 345 |
builder.append(" DEFAULT ( TIMESTAMP '");
|
|
| 346 |
Date d = (Date) column.getDefaultValue(); |
|
| 347 |
builder.append(MessageFormat.format( "{0,date,yyyy-MM-dd HH:mm:ss.S}",d));
|
|
| 348 |
builder.append("' )");
|
|
| 349 |
} else {
|
|
| 350 |
builder.append(" DEFAULT '");
|
|
| 351 |
builder.append(Objects.toString(column.getDefaultValue(),"")); |
|
| 352 |
builder.append("'");
|
|
| 353 |
} |
|
| 325 |
builder.append(" DEFAULT '");
|
|
| 326 |
builder.append(Objects.toString(column.getDefaultValue(),"")); |
|
| 327 |
builder.append("'");
|
|
| 354 | 328 |
} |
| 355 |
} |
|
| 329 |
}
|
|
| 356 | 330 |
} |
| 357 | 331 |
if (!column.allowNulls()) {
|
| 358 | 332 |
builder.append(" NOT NULL");
|
| ... | ... | |
| 381 | 355 |
} |
| 382 | 356 |
} |
| 383 | 357 |
|
| 358 |
public class MDBSelectColumnBuilderBase extends SelectColumnBuilderBase {
|
|
| 359 |
@Override |
|
| 360 |
public String toString(Formatter formatter) {
|
|
| 361 |
return super.toString(formatter); |
|
| 362 |
} |
|
| 363 |
} |
|
| 364 |
|
|
| 384 | 365 |
public class MDBSelectBuilderBase extends SelectBuilderBase {
|
| 385 | 366 |
|
| 386 | 367 |
@Override |
| ... | ... | |
| 400 | 381 |
} else {
|
| 401 | 382 |
builder.append(", ");
|
| 402 | 383 |
} |
| 384 |
|
|
| 403 | 385 |
builder.append(column.toString(formatter)); |
| 404 | 386 |
} |
| 405 | 387 |
|
| ... | ... | |
| 475 | 457 |
if( type!=DataTypes.GEOMETRY ) {
|
| 476 | 458 |
return super.sqltype(type, size, precision, scale, geomtype, geomSubtype); |
| 477 | 459 |
} |
| 478 |
return "GEOMETRY(1)";
|
|
| 460 |
return this.type_geometry;
|
|
| 479 | 461 |
} |
| 480 | 462 |
|
| 481 | 463 |
@Override |
| ... | ... | |
| 531 | 513 |
try {
|
| 532 | 514 |
FeatureType featureType = feature.getType(); |
| 533 | 515 |
List<Object> values = new ArrayList<>(); |
| 534 |
for (Parameter parameter : this.parameters()) {
|
|
| 516 |
List<Parameter> parameters = this.parametersWithoutSRS(); |
|
| 517 |
for (Parameter parameter : parameters) {
|
|
| 535 | 518 |
if (parameter.is_constant()) {
|
| 536 | 519 |
values.add(parameter.value()); |
| 537 | 520 |
} else {
|
| ... | ... | |
| 571 | 554 |
} |
| 572 | 555 |
} |
| 573 | 556 |
|
| 557 |
public List<Parameter> parametersWithoutSRS() {
|
|
| 558 |
final List<Parameter> params = new ArrayList<>(); |
|
| 559 |
MutableBoolean skipNextParameter = new MutableBoolean(false); |
|
| 560 |
this.accept((ExpressionBuilder.Visitable value1) -> {
|
|
| 561 |
if (skipNextParameter.isTrue()) {
|
|
| 562 |
skipNextParameter.setFalse(); |
|
| 563 |
return; |
|
| 564 |
} |
|
| 565 |
if (value1 instanceof GeometryExpressionBuilderHelper.GeometryParameter) {
|
|
| 566 |
GeometryParameter g = (GeometryParameter) value1; |
|
| 567 |
if (g.srs()!= null) {
|
|
| 568 |
skipNextParameter.setTrue(); |
|
| 569 |
} |
|
| 570 |
|
|
| 571 |
} |
|
| 572 |
params.add((Parameter) value1); |
|
| 573 |
}, new ExpressionBuilder.ClassVisitorFilter(Parameter.class)); |
|
| 574 |
return params; |
|
| 575 |
} |
|
| 576 |
//. |
|
| 577 |
// 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
|
|
| 578 |
// final List<Parameter> params = new ArrayList<>(); |
|
| 579 |
// this.accept(new ExpressionBuilder.Visitor() {
|
|
| 580 |
// @Override |
|
| 581 |
// public void visit(ExpressionBuilder.Visitable value) {
|
|
| 582 |
// params.add((Parameter) value); |
|
| 583 |
// } |
|
| 584 |
// }, new ExpressionBuilder.ClassVisitorFilter(Parameter.class)); |
|
| 585 |
// return params; |
|
| 586 |
// } |
|
| 587 |
|
|
| 574 | 588 |
@Override |
| 575 | 589 |
public InsertColumnBuilderBase createInsertColumnBuilder() {
|
| 576 | 590 |
return new MDBInsertColumnBuilderBase(); |
| ... | ... | |
| 602 | 616 |
} |
| 603 | 617 |
|
| 604 | 618 |
@Override |
| 619 |
protected SelectColumnBuilder createSelectColumnBuilder() {
|
|
| 620 |
|
|
| 621 |
return new MDBSelectColumnBuilderBase(); //super.createSelectColumnBuilder().; // SelectColumnBuilderBase, override el metodo toString |
|
| 622 |
} |
|
| 623 |
|
|
| 624 |
@Override |
|
| 605 | 625 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
| 606 | 626 |
return new MDBCreateIndexBuilder(); |
| 607 | 627 |
} |
| ... | ... | |
| 620 | 640 |
List values, |
| 621 | 641 |
GeometryExpressionBuilderHelper.GeometrySupportType geometrySupportType) throws SQLException {
|
| 622 | 642 |
|
| 643 |
// |
|
| 644 |
DisposableClobs disposableClobs = new DisposableClobs(); |
|
| 645 |
// |
|
| 623 | 646 |
if (values == null) {
|
| 624 |
return new Disposable() {
|
|
| 625 |
@Override |
|
| 626 |
public void dispose() {
|
|
| 627 |
} |
|
| 628 |
}; |
|
| 647 |
return disposableClobs; |
|
| 629 | 648 |
} |
| 630 | 649 |
if (true || LOGGER.isDebugEnabled()) {
|
| 631 | 650 |
StringBuilder debug = new StringBuilder(); |
| ... | ... | |
| 683 | 702 |
} |
| 684 | 703 |
} |
| 685 | 704 |
byte[] bytes; |
| 705 |
char[] hexGeomEwkb; |
|
| 686 | 706 |
int columnIndex = 1; |
| 687 | 707 |
Object theValue; |
| 708 |
Clob cl; |
|
| 688 | 709 |
try {
|
| 689 | 710 |
for (Object value : values) {
|
| 690 | 711 |
theValue = value; |
| ... | ... | |
| 698 | 719 |
case NATIVE: |
| 699 | 720 |
case WKB: |
| 700 | 721 |
bytes = ((Geometry) value).convertToWKB(); |
| 701 |
st.setBytes(columnIndex, bytes); |
|
| 722 |
cl = disposableClobs.add(bytes); |
|
| 723 |
st.setClob(columnIndex, cl); |
|
| 702 | 724 |
break; |
| 725 |
|
|
| 703 | 726 |
case EWKB: |
| 704 | 727 |
bytes = ((Geometry) value).convertToEWKB(); |
| 705 |
st.setBytes(columnIndex, bytes); |
|
| 728 |
cl = disposableClobs.add(bytes); |
|
| 729 |
st.setClob(columnIndex, cl); |
|
| 706 | 730 |
break; |
| 731 |
|
|
| 732 |
|
|
| 707 | 733 |
} |
| 708 | 734 |
} else if (value instanceof Date) {
|
| 709 | 735 |
// Access solo soporta timestamp |
| ... | ... | |
| 718 | 744 |
} |
| 719 | 745 |
columnIndex++; |
| 720 | 746 |
} |
| 721 |
return new Disposable() {
|
|
| 722 |
@Override |
|
| 723 |
public void dispose() {
|
|
| 724 |
} |
|
| 725 |
}; |
|
| 747 |
return disposableClobs; |
|
| 726 | 748 |
} catch (Exception ex) {
|
| 727 | 749 |
throw new SQLException("Can't set values for the prepared statement.", ex);
|
| 728 | 750 |
} |
| ... | ... | |
| 782 | 804 |
} |
| 783 | 805 |
|
| 784 | 806 |
|
| 785 |
|
|
| 786 |
|
|
| 787 |
|
|
| 807 |
public class DisposableClobs implements Disposable {
|
|
| 788 | 808 |
|
| 809 |
private final List<Clob> clobList = new ArrayList<>(); |
|
| 810 |
|
|
| 811 |
public Clob add(byte[] bytes) throws SQLException {
|
|
| 812 |
char[] hexGeom = Hex.encodeHex(bytes); |
|
| 813 |
String strHexGeo = new String(hexGeom); |
|
| 814 |
JDBCClob clob = new JDBCClob(strHexGeo); |
|
| 815 |
clobList.add(clob); |
|
| 816 |
return clob; |
|
| 817 |
} |
|
| 818 |
|
|
| 819 |
@Override |
|
| 820 |
public void dispose() {
|
|
| 821 |
clobList.forEach((Clob clob) -> {
|
|
| 822 |
try {
|
|
| 823 |
clob.free(); |
|
| 824 |
} catch (SQLException ex) {
|
|
| 825 |
} |
|
| 826 |
}); |
|
| 827 |
} |
|
| 828 |
|
|
| 829 |
} |
|
| 789 | 830 |
} |
Also available in: Unified diff