Revision 47579 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/main/java/org/gvsig/sqlite/dal/SQLiteSQLBuilder.java
| SQLiteSQLBuilder.java | ||
|---|---|---|
| 1 | 1 |
package org.gvsig.sqlite.dal; |
| 2 | 2 |
|
| 3 |
import java.sql.Connection; |
|
| 3 | 4 |
import java.sql.PreparedStatement; |
| 4 | 5 |
import java.sql.SQLException; |
| 6 |
import java.sql.Time; |
|
| 7 |
import java.sql.Timestamp; |
|
| 8 |
import java.text.MessageFormat; |
|
| 9 |
import java.time.LocalDateTime; |
|
| 10 |
import java.time.format.DateTimeFormatter; |
|
| 5 | 11 |
import java.util.ArrayList; |
| 6 | 12 |
import java.util.Date; |
| 7 | 13 |
import java.util.List; |
| 14 |
import org.apache.commons.lang3.StringUtils; |
|
| 8 | 15 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter; |
| 9 | 16 |
import org.gvsig.expressionevaluator.Formatter; |
| 10 | 17 |
import org.gvsig.fmap.dal.DataTypes; |
| ... | ... | |
| 15 | 22 |
import org.gvsig.fmap.geom.Geometry; |
| 16 | 23 |
import org.gvsig.fmap.geom.exception.CreateGeometryException; |
| 17 | 24 |
import org.gvsig.sqlite.dal.expressionbuilderformatter.SQLiteFormatter; |
| 25 |
import org.gvsig.sqlite.dal.geopackage.GeopackageGeometryBuilder; |
|
| 26 |
import org.gvsig.sqlite.dal.geopackage.GeopackageGeometryColumns; |
|
| 27 |
import org.gvsig.sqlite.dal.geopackage.GeopackageUtils; |
|
| 28 |
import org.gvsig.sqlite.dal.geopackage.index.GeopackageIndexRTree; |
|
| 29 |
import org.gvsig.sqlite.dal.utils.TemplateUtils; |
|
| 30 |
import org.gvsig.tools.dataTypes.DataTypeUtils; |
|
| 18 | 31 |
import org.gvsig.tools.dispose.Disposable; |
| 32 |
import org.sqlite.SQLiteConnection; |
|
| 19 | 33 |
|
| 20 | 34 |
public class SQLiteSQLBuilder extends JDBCSQLBuilderBase {
|
| 21 | 35 |
|
| ... | ... | |
| 38 | 52 |
this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
| 39 | 53 |
this.expressionBuilder.geometry_support_type(this.geometrySupportType); |
| 40 | 54 |
|
| 41 |
// FIXME |
|
| 42 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
|
| 43 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
|
| 55 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; //"DELETE FROM gpkg_geometry_columns WHERE table_name = {1}";
|
|
| 56 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = "DELETE FROM gpkg_geometry_columns WHERE table_name = {0}";
|
|
| 44 | 57 |
|
| 45 | 58 |
this.STMT_UPDATE_TABLE_STATISTICS_table = "SELECT UpdateLayerStatistics({0})";
|
| 46 | 59 |
|
| 47 |
this.type_boolean = "INTEGER";
|
|
| 48 |
this.type_byte = "INTEGER";
|
|
| 60 |
this.type_boolean = "BOOLEAN";
|
|
| 61 |
this.type_byte = "TINYINT";
|
|
| 49 | 62 |
this.type_bytearray = "BLOB"; |
| 50 | 63 |
this.type_geometry = "BLOB"; |
| 51 |
this.type_char = "TEXT";
|
|
| 52 |
this.type_date = "TEXT";
|
|
| 53 |
this.type_double = "REAL";
|
|
| 54 |
this.type_decimal_ps = "REAL";
|
|
| 55 |
this.type_decimal_p = "REAL";
|
|
| 56 |
this.type_float = "REAL";
|
|
| 57 |
this.type_int = "INTEGER";
|
|
| 58 |
this.type_long = "INTEGER";
|
|
| 64 |
this.type_char = "CHARACTER(1)";
|
|
| 65 |
this.type_date = "DATE";
|
|
| 66 |
this.type_double = "DOUBLE PRECISION";
|
|
| 67 |
this.type_decimal_ps = "DECIMAL({0,Number,##########},{1,Number,##########})";
|
|
| 68 |
this.type_decimal_p = "DECIMAL({0,Number,##########})";
|
|
| 69 |
this.type_float = "FLOAT";
|
|
| 70 |
this.type_int = "MEDIUMINT";
|
|
| 71 |
this.type_long = "BIGINT";
|
|
| 59 | 72 |
this.type_string = "TEXT"; |
| 60 |
this.type_string_p = "TEXT";
|
|
| 61 |
this.type_time = "TEXT";
|
|
| 62 |
this.type_timestamp = "TEXT";
|
|
| 63 |
this.type_version = "TEXT";
|
|
| 73 |
this.type_string_p = "VARCHAR({0,Number,#######})";
|
|
| 74 |
this.type_time = "TIME";
|
|
| 75 |
this.type_timestamp = "DATETIME";
|
|
| 76 |
this.type_version = "VARCHAR(30)";
|
|
| 64 | 77 |
this.type_URI = "TEXT"; |
| 65 | 78 |
this.type_URL = "TEXT"; |
| 66 | 79 |
this.type_FILE = "TEXT"; |
| ... | ... | |
| 117 | 130 |
// respecto al que hay por defecto. |
| 118 | 131 |
// |
| 119 | 132 |
List<String> sqls = new ArrayList<>(); |
| 133 |
|
|
| 134 |
sqls.add("CREATE TABLE IF NOT EXISTS gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,data_type TEXT NOT NULL,identifier TEXT UNIQUE,description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),min_x DOUBLE, min_y DOUBLE,max_x DOUBLE, max_y DOUBLE,srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id))");
|
|
| 135 |
if(this.hasGeometry()) {
|
|
| 136 |
sqls.add(TemplateUtils.getSqlTemplate(GeopackageIndexRTree.GROUP_NAME, "create_table_gpkg_extensions")); |
|
| 137 |
sqls.add(GeopackageGeometryColumns.buildCreateTableSQLIfNotExists()); |
|
| 138 |
sqls.add(String.format( |
|
| 139 |
"INSERT INTO \"main\".\"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('%s', '%s', '%s', '', '%s', NULL, NULL, NULL, NULL, NULL)",
|
|
| 140 |
this.table.getName(), |
|
| 141 |
"features", |
|
| 142 |
this.table.getName(), |
|
| 143 |
new java.sql.Timestamp(new Date().getTime()).toString() |
|
| 144 |
)); |
|
| 145 |
} else {
|
|
| 146 |
sqls.add(String.format( |
|
| 147 |
"INSERT INTO \"main\".\"gpkg_contents\" (\"table_name\", \"data_type\", \"identifier\", \"description\", \"last_change\", \"min_x\", \"min_y\", \"max_x\", \"max_y\", \"srs_id\") VALUES ('%s', '%s', '%s', '', '%s', NULL, NULL, NULL, NULL, NULL)",
|
|
| 148 |
this.table.getName(), |
|
| 149 |
"attributes", |
|
| 150 |
this.table.getName(), |
|
| 151 |
new java.sql.Timestamp(new Date().getTime()).toString() |
|
| 152 |
)); |
|
| 153 |
|
|
| 154 |
} |
|
| 155 |
|
|
| 156 |
|
|
| 120 | 157 |
StringBuilder builder = new StringBuilder(); |
| 121 |
|
|
| 158 |
String pkName = null; |
|
| 122 | 159 |
builder.append("CREATE TABLE ");
|
| 123 | 160 |
builder.append(this.table.toString(formatter)); |
| 124 | 161 |
builder.append(" (");
|
| ... | ... | |
| 143 | 180 |
); |
| 144 | 181 |
if (column.isPrimaryKey()) {
|
| 145 | 182 |
builder.append(" PRIMARY KEY");
|
| 183 |
pkName = column.getName(); |
|
| 146 | 184 |
} |
| 147 | 185 |
if( column.isAutomatic() ) {
|
| 148 | 186 |
builder.append(" AUTOINCREMENT");
|
| ... | ... | |
| 168 | 206 |
} |
| 169 | 207 |
builder.append(" )");
|
| 170 | 208 |
sqls.add(builder.toString()); |
| 209 |
|
|
| 210 |
for (ColumnDescriptor column : columns) {
|
|
| 211 |
if (column.isGeometry()) {
|
|
| 212 |
sqls.add(GeopackageGeometryColumns.buildInsertSQL( |
|
| 213 |
this.table.getName(), |
|
| 214 |
column.getName(), |
|
| 215 |
column.getGeometryType(), |
|
| 216 |
column.getGeometrySubtype(), |
|
| 217 |
column.getGeometrySRSId() |
|
| 218 |
)); |
|
| 219 |
|
|
| 220 |
//TODO: A?adir el insert a gpkg_extensions |
|
| 221 |
GeopackageIndexRTree index = new GeopackageIndexRTree(); |
|
| 222 |
sqls.addAll(index.getCreateIndexSQL(this.table.getName(), column.getName(), pkName)); |
|
| 223 |
|
|
| 224 |
} |
|
| 225 |
} |
|
| 226 |
|
|
| 171 | 227 |
|
| 228 |
|
|
| 229 |
|
|
| 172 | 230 |
return sqls; |
| 173 | 231 |
} |
| 232 |
|
|
| 233 |
private boolean hasGeometry() {
|
|
| 234 |
for (ColumnDescriptor column : columns) {
|
|
| 235 |
if (column.isGeometry()) {
|
|
| 236 |
return true; |
|
| 237 |
} |
|
| 238 |
} |
|
| 239 |
return false; |
|
| 240 |
} |
|
| 174 | 241 |
} |
| 175 | 242 |
|
| 243 |
protected class SQLiteDropTableBuilder extends DropTableBuilderBase {
|
|
| 244 |
|
|
| 245 |
@Override |
|
| 246 |
public List<String> toStrings(Formatter formatter) {
|
|
| 247 |
List<String> sqls = new ArrayList<>(); |
|
| 248 |
|
|
| 249 |
String sql; |
|
| 250 |
sql = MessageFormat.format( |
|
| 251 |
STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table, |
|
| 252 |
as_identifier(this.table.getName()) |
|
| 253 |
); |
|
| 254 |
if (!StringUtils.isEmpty(sql)) {
|
|
| 255 |
sqls.add(sql); |
|
| 256 |
} |
|
| 257 |
|
|
| 258 |
sqls.add(String.format( |
|
| 259 |
"DELETE FROM \"main\".\"gpkg_contents\" WHERE \"identifier\" = '%s'", |
|
| 260 |
this.table.getName() |
|
| 261 |
)); |
|
| 262 |
|
|
| 263 |
sqls.add(String.format( |
|
| 264 |
"DELETE FROM \"main\".\"gpkg_extensions\" WHERE \"table_name\" = '%s'", |
|
| 265 |
this.table.getName() |
|
| 266 |
)); |
|
| 267 |
|
|
| 268 |
sqls.add( |
|
| 269 |
MessageFormat.format( |
|
| 270 |
STMT_DROP_TABLE_table, |
|
| 271 |
this.table.toString(formatter) |
|
| 272 |
) |
|
| 273 |
); |
|
| 274 |
|
|
| 275 |
return sqls; |
|
| 276 |
} |
|
| 277 |
} |
|
| 278 |
|
|
| 176 | 279 |
public class SQLiteSelectBuilderBase extends SelectBuilderBase {
|
| 177 | 280 |
|
| 178 | 281 |
@Override |
| ... | ... | |
| 223 | 326 |
builder.append(this.where.toString(formatter)); |
| 224 | 327 |
} |
| 225 | 328 |
|
| 329 |
if( this.has_group_by() ) {
|
|
| 330 |
builder.append(" GROUP BY ");
|
|
| 331 |
builder.append(this.groupColumn.get(0).toString(formatter)); |
|
| 332 |
for (int i = 1; i < groupColumn.size(); i++) {
|
|
| 333 |
builder.append(", ");
|
|
| 334 |
builder.append(this.groupColumn.get(i).toString(formatter)); |
|
| 335 |
} |
|
| 336 |
} |
|
| 337 |
|
|
| 226 | 338 |
if( this.has_order_by() ) {
|
| 227 | 339 |
builder.append(" ORDER BY ");
|
| 228 | 340 |
first = true; |
| ... | ... | |
| 310 | 422 |
if( value == null ) {
|
| 311 | 423 |
values.add(null); |
| 312 | 424 |
} else {
|
| 313 |
values.add(new java.sql.Date(value.getTime())); |
|
| 425 |
LocalDateTime localDate = DataTypeUtils.toLocalDateTime(value); |
|
| 426 |
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
|
|
| 427 |
values.add(localDate.format(formatter)); |
|
| 314 | 428 |
} |
| 315 | 429 |
break; |
| 430 |
case DataTypes.TIME: |
|
| 431 |
Time time = (Time)(feature.get(name)); |
|
| 432 |
if( time == null ) {
|
|
| 433 |
values.add(null); |
|
| 434 |
} else {
|
|
| 435 |
LocalDateTime localDate = DataTypeUtils.toLocalDateTime(time); |
|
| 436 |
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss");
|
|
| 437 |
values.add(localDate.format(formatter)); |
|
| 438 |
} |
|
| 439 |
break; |
|
| 440 |
case DataTypes.TIMESTAMP: |
|
| 441 |
Timestamp timestamp= (Timestamp)(feature.get(name)); |
|
| 442 |
if( timestamp == null ) {
|
|
| 443 |
values.add(null); |
|
| 444 |
} else {
|
|
| 445 |
LocalDateTime localDate = DataTypeUtils.toLocalDateTime(timestamp); |
|
| 446 |
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
|
|
| 447 |
values.add(localDate.format(formatter)); |
|
| 448 |
} |
|
| 449 |
break; |
|
| 316 | 450 |
case DataTypes.GEOMETRY: |
| 317 | 451 |
Geometry geom = this.forceGeometryType( |
| 318 | 452 |
descriptor.getGeomType(), |
| ... | ... | |
| 337 | 471 |
throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);
|
| 338 | 472 |
} |
| 339 | 473 |
} |
| 474 |
|
|
| 475 |
@Override |
|
| 476 |
protected byte[] getNativeBytes(Connection conn, Geometry geometry) throws Exception {
|
|
| 477 |
GeopackageGeometryBuilder builder = GeopackageUtils.createGeometryBuilder(); |
|
| 478 |
builder.setGeometry((SQLiteConnection) conn, geometry); |
|
| 479 |
byte[] bytes = builder.build(); |
|
| 480 |
return bytes; |
|
| 481 |
} |
|
| 482 |
|
|
| 483 |
|
|
| 340 | 484 |
|
| 341 | 485 |
@Override |
| 342 | 486 |
public TableNameBuilder createTableNameBuilder() {
|
| 343 | 487 |
return new SQLiteTableNameBuilderBase(); |
| 344 | 488 |
} |
| 345 | 489 |
|
| 490 |
protected DropTableBuilder createDropTableBuilder() {
|
|
| 491 |
return new SQLiteDropTableBuilder(); |
|
| 492 |
} |
|
| 493 |
|
|
| 494 |
|
|
| 346 | 495 |
@Override |
| 347 | 496 |
protected CreateTableBuilder createCreateTableBuilder() {
|
| 348 | 497 |
return new SQLiteCreateTableBuilder(); |
Also available in: Unified diff