[JPP-Devel] SVN: [5489] core/trunk

2017-08-11 Thread jump-pilot-svn--- via Jump-pilot-devel 
Revision: 5489
  http://sourceforge.net/p/jump-pilot/code/5489
Author:   elnico
Date: 2017-08-11 10:38:28 + (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/ChangeLog2017-08-11 07:54:41 UTC (rev 5488)
+++ core/trunk/ChangeLog2017-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 
+  * 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

Re: [JPP-Devel] Requst to update SQLite driver

2017-08-11 Thread edgar . soldin 
done in r5488.. ede

On 8/11/2017 9:22, Rahkonen Jukka (MML) wrote:
> Hi,
> 
> For getting even more speed for BBOX queries which utilize spatial index of 
> GeoPackage and Spatialite databases it would be good to update the SQLite 
> driver that is delivered with OpenJUJMP Plus. The version we use now is 
> sqlite-jdbc-3.8.11.2.jar but starting from version 3.17 the R-Tree index 
> should be 25% faster, see https://www.sqlite.org/releaselog/3_17_0.html.
> 
> I made some tests with the newest driver version 3.20 that I found from 
> https://bitbucket.org/xerial/sqlite-jdbc/downloads/ and it did not seem to 
> break anything. So I suggest to start delivering this one with OJ Plus:
> https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.20.0.jar
> 
> -Jukka Rahkonen-
> 
> 
> 
> --
> 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
> 


--
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

[JPP-Devel] SVN: [5488] core/trunk

2017-08-11 Thread jump-pilot-svn--- via Jump-pilot-devel 
Revision: 5488
  http://sourceforge.net/p/jump-pilot/code/5488
Author:   edso
Date: 2017-08-11 07:54:41 + (Fri, 11 Aug 2017)
Log Message:
---
update to latest sqlite jdbc

Modified Paths:
--
core/trunk/ChangeLog

Added Paths:
---
core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar

Removed Paths:
-
core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.8.11.2.jar

Modified: core/trunk/ChangeLog
===
--- core/trunk/ChangeLog2017-08-10 16:21:24 UTC (rev 5487)
+++ core/trunk/ChangeLog2017-08-11 07:54:41 UTC (rev 5488)
@@ -3,6 +3,9 @@
 # 2. make sure that lines break at 80 chars for constricted display situations
 #< 80 chars 
-->#
 
+2017-08-11 ede
+  * PLUS: update to latest sqlite-jdbc-3.20.0.jar
+
 2017-08-10 Nicolas Ribot 
   * Spatial Index support for SQLite/Spatialte datasources: wrong test for 
 GeoPackage spatial query bbox overlaping

Added: core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar
===
(Binary files differ)

Index: core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar
===
--- core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar  2017-08-10 
16:21:24 UTC (rev 5487)
+++ core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar  2017-08-11 
07:54:41 UTC (rev 5488)

Property changes on: core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.20.0.jar
___
Added: svn:mime-type
## -0,0 +1 ##
+application/octet-stream
\ No newline at end of property
Deleted: core/trunk/lib/plus/dbdatastore/sqlite-jdbc-3.8.11.2.jar
===
(Binary files differ)


--
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

[JPP-Devel] Requst to update SQLite driver

2017-08-11 Thread Rahkonen Jukka (MML) 
Hi,

For getting even more speed for BBOX queries which utilize spatial index of 
GeoPackage and Spatialite databases it would be good to update the SQLite 
driver that is delivered with OpenJUJMP Plus. The version we use now is 
sqlite-jdbc-3.8.11.2.jar but starting from version 3.17 the R-Tree index should 
be 25% faster, see https://www.sqlite.org/releaselog/3_17_0.html.

I made some tests with the newest driver version 3.20 that I found from 
https://bitbucket.org/xerial/sqlite-jdbc/downloads/ and it did not seem to 
break anything. So I suggest to start delivering this one with OJ Plus:
https://bitbucket.org/xerial/sqlite-jdbc/downloads/sqlite-jdbc-3.20.0.jar

-Jukka Rahkonen-



--
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

[JPP-Devel] Good news about GeoPackage and SpatiaLite and spatial index

2017-08-11 Thread Rahkonen Jukka (MML) 
Hi,

Nicolas Ribot has kindly improved the SQLite based datastores so that the 
R-Tree spatial index is utilized if it exists for GeoPackages. This makes a 
huge speedup for dynamic browsing of GeoPackage layers which are added to the 
map through Open Datastore Layer.

The same speedup is available also for Spatialite databases but by now it 
requires that spatialite extension (mod_spatialite) is available. Perhaps 
easiest way for OpenJUMP users for getting mod_spatialite is to install the 
Spatialite reader plugin 
https://sourceforge.net/projects/jump-pilot/files/OpenJUMP_plugins/Database%20Plugins/SpatialLite_reader_plugin/.

-Jukka Rahkonen-
--
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