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