Revision 44678 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
| H2SpatialSQLBuilder.java | ||
|---|---|---|
| 51 | 51 |
|
| 52 | 52 |
this.STMT_UPDATE_TABLE_STATISTICS_table = "ANALYZE SAMPLE_SIZE 0"; |
| 53 | 53 |
|
| 54 |
// config.set(SQLConfig.ST_AsBinary, "NVL2({0}, ST_AsBinary({0}), null)");
|
|
| 55 |
|
|
| 56 | 54 |
this.type_boolean = "BOOLEAN"; |
| 57 | 55 |
this.type_byte = "TINYINT"; |
| 58 | 56 |
this.type_bytearray = "BLOB"; |
| ... | ... | |
| 60 | 58 |
this.type_char = "CHAR"; |
| 61 | 59 |
this.type_date = "DATE"; |
| 62 | 60 |
this.type_double = "DOUBLE"; |
| 63 |
this.type_numeric_p = "DECIMAL({0,Number,#######})";
|
|
| 64 |
this.type_numeric_ps = "DECIMAL({0,Number,#######},{1,Number,#######})";
|
|
| 65 |
this.type_bigdecimal = "DOUBLE"; |
|
| 61 |
this.type_decimal_ps = "DECIMAL({0,Number,##########},{1,Number,##########})";
|
|
| 62 |
this.type_decimal_p = "DECIMAL({0,Number,##########})";
|
|
| 66 | 63 |
this.type_float = "REAL"; |
| 67 | 64 |
this.type_int = "INTEGER"; |
| 68 | 65 |
this.type_long = "BIGINT"; |
| 69 | 66 |
this.type_string = "VARCHAR"; |
| 70 |
this.type_string_p = "VARCHAR({0,Number,#######})";
|
|
| 67 |
this.type_string_p = "VARCHAR({0,Number,##########})";
|
|
| 71 | 68 |
this.type_time = "TIME"; |
| 72 | 69 |
this.type_timestamp = "TIMESTAMP"; |
| 73 | 70 |
this.type_version = "VARCHAR"; |
| ... | ... | |
| 188 | 185 |
sqls.add(builder.toString()); |
| 189 | 186 |
|
| 190 | 187 |
if( column.isGeometry() ) {
|
| 188 |
String constraint_name = "constraint_" + this.table().getName() + "_" + column.getName()+"_geom"; |
|
| 191 | 189 |
String sql; |
| 192 |
String constraint_name = "constraint_" + this.table().getName() + "_" + column.getName()+"_dim"; |
|
| 193 |
sql = MessageFormat.format( |
|
| 194 |
"ALTER TABLE \"{0}\".\"{1}\" ADD CONSTRAINT IF NOT EXISTS \"{2}\" CHECK ST_CoordDim(\"{3}\") = {4}",
|
|
| 195 |
this.table().getSchema(), |
|
| 196 |
this.table().getName(), |
|
| 197 |
constraint_name, |
|
| 198 |
column.getName(), |
|
| 199 |
sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) |
|
| 200 |
); |
|
| 190 |
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 |
} |
|
| 201 | 213 |
sqls.add(sql); |
| 202 | 214 |
} |
| 203 | 215 |
} |
| ... | ... | |
| 291 | 303 |
column.getGeometrySubtype() |
| 292 | 304 |
) |
| 293 | 305 |
); |
| 294 |
if( !column.isGeometry() ) {
|
|
| 306 |
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 {
|
|
| 295 | 331 |
if (column.isPrimaryKey()) {
|
| 296 | 332 |
builder.append(" PRIMARY KEY");
|
| 297 | 333 |
if( column.isAutomatic() ) {
|
| ... | ... | |
| 325 | 361 |
} |
| 326 | 362 |
builder.append(" )");
|
| 327 | 363 |
sqls.add(builder.toString()); |
| 328 |
for (ColumnDescriptor column : columns) {
|
|
| 329 |
if( column.isGeometry() ) {
|
|
| 330 |
String sql; |
|
| 331 |
String constraint_name = "constraint_" + this.table().getName() + "_" + column.getName()+"_dim"; |
|
| 332 |
sql = MessageFormat.format( |
|
| 333 |
"ALTER TABLE {0} ADD CONSTRAINT IF NOT EXISTS \"{1}\" CHECK NVL2(\"{2}\", ST_CoordDim(\"{2}\") = {3}, TRUE)",
|
|
| 334 |
this.table().toString(), |
|
| 335 |
constraint_name, |
|
| 336 |
column.getName(), |
|
| 337 |
sqlgeometrynumdimension(column.getGeometryType(),column.getGeometrySubtype()) |
|
| 338 |
); |
|
| 339 |
if( column.getGeometrySRSId()!=null ) {
|
|
| 340 |
String sql2; |
|
| 341 |
String constraint_name2 = "constraint_" + this.table().getName() + "_" + column.getName()+"_srid"; |
|
| 342 |
sql2 = MessageFormat.format( |
|
| 343 |
"ALTER TABLE {0} ADD CONSTRAINT IF NOT EXISTS \"{1}\" CHECK NVL2(\"{2}\", ST_SRID(\"{2}\") = {3,number,#####}, TRUE)",
|
|
| 344 |
this.table().toString(), |
|
| 345 |
constraint_name2, |
|
| 346 |
column.getName(), |
|
| 347 |
column.getGeometrySRSId() |
|
| 348 |
); |
|
| 349 |
sqls.add(sql2); |
|
| 350 |
} |
|
| 351 |
sqls.add(sql); |
|
| 352 |
} |
|
| 353 |
} |
|
| 354 |
|
|
| 355 | 364 |
return sqls; |
| 356 | 365 |
} |
| 357 | 366 |
} |
| ... | ... | |
| 467 | 476 |
if( type!=DataTypes.GEOMETRY ) {
|
| 468 | 477 |
return super.sqltype(type, size, precision, scale, geomtype, geomSubtype); |
| 469 | 478 |
} |
| 479 |
return "GEOMETRY(1)"; |
|
| 480 |
} |
|
| 481 |
|
|
| 482 |
@Override |
|
| 483 |
public Object sqlgeometrytype(int geomtype, int geomsubtype) {
|
|
| 470 | 484 |
// |
| 471 | 485 |
// https://github.com/orbisgis/h2gis/wiki/1.-Spatial-data#geometry-columns-view |
| 472 | 486 |
// |
| 473 | 487 |
switch(geomtype) {
|
| 474 | 488 |
case Geometry.TYPES.POINT: |
| 475 |
return "POINT";
|
|
| 489 |
return 1;
|
|
| 476 | 490 |
case Geometry.TYPES.MULTIPOINT: |
| 477 |
return "MULTIPOINT";
|
|
| 491 |
return 4;
|
|
| 478 | 492 |
case Geometry.TYPES.LINE: |
| 479 |
return "LINESTRING";
|
|
| 493 |
return 2;
|
|
| 480 | 494 |
case Geometry.TYPES.MULTILINE: |
| 481 |
return "MULTILINESTRING";
|
|
| 495 |
return 5;
|
|
| 482 | 496 |
case Geometry.TYPES.POLYGON: |
| 483 |
return "POLYGON";
|
|
| 497 |
return 3;
|
|
| 484 | 498 |
case Geometry.TYPES.MULTIPOLYGON: |
| 485 |
return "MULTIPOLYGON"; |
|
| 499 |
return 6; |
|
| 500 |
case Geometry.TYPES.GEOMETRY: |
|
| 486 | 501 |
default: |
| 487 |
return "GEOMETRY"; |
|
| 502 |
return 0; // "GEOMETRY";
|
|
| 488 | 503 |
} |
| 489 | 504 |
} |
| 490 | 505 |
|
Also available in: Unified diff