Revision: 5489
http://sourceforge.net/p/jump-pilot/code/5489
Author: elnico
Date: 2017-08-11 10:38:28 +0000 (Fri, 11 Aug 2017)
Log Message:
-----------
Spatial Index support for SQLite/Spatialte datasources: quotes identifiers when
generating SQL queries
Modified Paths:
--------------
core/trunk/ChangeLog
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
Modified: core/trunk/ChangeLog
===================================================================
--- core/trunk/ChangeLog 2017-08-11 07:54:41 UTC (rev 5488)
+++ core/trunk/ChangeLog 2017-08-11 10:38:28 UTC (rev 5489)
@@ -3,6 +3,10 @@
# 2. make sure that lines break at 80 chars for constricted display situations
#<-------------------------------- 80 chars
---------------------------------->#
+2017-08-11 Nicolas Ribot <nicolas.ri...@gmail.com>
+ * Spatial Index support for SQLite/Spatialte datasources: quotes identifiers
+ when generating SQL queries
+
2017-08-11 ede
* PLUS: update to latest sqlite-jdbc-3.20.0.jar
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
2017-08-11 07:54:41 UTC (rev 5488)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
2017-08-11 10:38:28 UTC (rev 5489)
@@ -2,6 +2,7 @@
import com.vividsolutions.jump.datastore.DataStoreConnection;
import com.vividsolutions.jump.datastore.GeometryColumn;
+import com.vividsolutions.jump.datastore.SQLUtil;
import com.vividsolutions.jump.datastore.spatialdatabases.*;
import com.vividsolutions.jump.datastore.jdbc.JDBCUtil;
import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock;
@@ -255,14 +256,16 @@
*/
@Override
public String getGeoColumnsQuery(String datasetName) {
- // No schema in SQLite
- return String.format(this.geoColumnsQuery, getTableName(datasetName));
+ // No schema in SQLite, escape single quotes in name:
+ return String.format(this.geoColumnsQuery,
SQLUtil.escapeSingleQuote(getTableName(datasetName)));
}
@Override
public String getSridQuery(String schemaName, String tableName, String
colName) {
// no schema in sqlite
- return String.format(this.sridQuery, tableName, colName);
+ return String.format(this.sridQuery,
+ SQLUtil.escapeSingleQuote(tableName),
+ SQLUtil.escapeSingleQuote(colName));
}
private void checkSpatialiteLoaded() {
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
2017-08-11 07:54:41 UTC (rev 5488)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
2017-08-11 10:38:28 UTC (rev 5489)
@@ -6,6 +6,7 @@
import com.vividsolutions.jump.datastore.DataStoreLayer;
import com.vividsolutions.jump.datastore.FilterQuery;
import com.vividsolutions.jump.datastore.GeometryColumn;
+import com.vividsolutions.jump.datastore.SQLUtil;
import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
import com.vividsolutions.jump.workbench.JUMPWorkbench;
@@ -39,8 +40,8 @@
String and = query.getCondition() == null ? "1" : query.getCondition();
String lim = (query.getLimit() != 0 && query.getLimit() !=
Integer.MAX_VALUE) ? " LIMIT " + query.getLimit() : "";
- //System.out.println(qs);
- String s = String.format(ret, cols, this.datasetName, bbox, and, lim);
+ // quotes identifier to manage several cases. TODO: escape single/double
quotes ?
+ String s = String.format(ret, cols, SQLUtil.quote(this.datasetName), bbox,
and, lim);
JUMPWorkbench.getInstance().getFrame().log(
"SQL query to get Spatial table features:\n\t"
+ s, this.getClass());
@@ -155,10 +156,11 @@
GeometryColumn gc = dsm.getGeometryColumn(query.getDatasetName(),
query.getGeometryAttributeName());
if (gc.isIndexed()) {
if (dsm.getGeometryColumnsLayout() ==
GeometryColumnsLayout.OGC_GEOPACKAGE_LAYOUT) {
+ String idxName = SQLUtil.quote(String.format("rtree_%s_%s",
+ query.getDatasetName(), query.getGeometryAttributeName()));
ret = String.format(Locale.US,
- " AND ROWID IN (SELECT id FROM rtree_%s_%s WHERE minx < %f and maxx
> %f and miny < %f and maxy > %f) ",
- query.getDatasetName(), query.getGeometryAttributeName(),
- env.getMaxX(), env.getMinX(), env.getMaxY(), env.getMinY());
+ " AND ROWID IN (SELECT id FROM %s WHERE minx < %f and maxx > %f and
miny < %f and maxy > %f) ",
+ idxName, env.getMaxX(), env.getMinX(), env.getMaxY(), env.getMinY());
} else if (dsm.isSpatialiteLoaded()) {
// always use spatialIndex table if spatialite
ret = String.format(Locale.US,
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
