Revision: 4609
http://sourceforge.net/p/jump-pilot/code/4609
Author: elnico
Date: 2015-12-13 15:02:36 +0000 (Sun, 13 Dec 2015)
Log Message:
-----------
Spatialite and MySQL/MariaDB support:
refactor, new connection icons, better error management, Adhoc query support
for all databases types
Modified Paths:
--------------
core/trunk/ChangeLog
core/trunk/pom.xml
core/trunk/scripts/default-plugins.xml
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSConnection.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreDriver.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleFeatureInputStream.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleResultSetConverter.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleSQLBuilder.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleValueConverterFactory.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSConnection.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDataStoreDriver.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisFeatureInputStream.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisResultSetConverter.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisSQLBuilder.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisValueConverterFactory.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSConnection.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDataStoreDriver.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesFeatureInputStream.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesResultSetConverter.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesValueConverterFactory.java
core/trunk/src/com/vividsolutions/jump/workbench/model/LayerManager.java
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ConnectionManagerPanel.java
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/RunDatastoreQueryPlugIn.java
core/trunk/src/com/vividsolutions/jump/workbench/ui/renderer/ImageCachingRenderer.java
core/trunk/src/language/jump.properties
core/trunk/src/language/jump_de.properties
core/trunk/src/language/jump_es.properties
core/trunk/src/language/jump_fi.properties
core/trunk/src/language/jump_fr.properties
core/trunk/src/language/jump_hu.properties
core/trunk/src/language/jump_it.properties
core/trunk/src/language/jump_ja_JP.properties
core/trunk/src/language/jump_ml.properties
core/trunk/src/language/jump_pt.properties
core/trunk/src/language/jump_pt_BR.properties
core/trunk/src/language/jump_ta_IN.properties
core/trunk/src/language/jump_te.properties
core/trunk/src/language/jump_zh_CN.properties
core/trunk/src/language/jump_zh_HK.properties
core/trunk/src/org/openjump/core/ui/plugin/datastore/AddDataStoreLayerWizard.java
Added Paths:
-----------
core/trunk/licenseheader.txt
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSConnection.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreDriver.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreExtension.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbFeatureInputStream.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbResultSetConverter.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbSQLBuilder.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbValueConverterFactory.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreExtension.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometricColumnType.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometryColumnsLayout.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSConnection.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreDriver.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreExtension.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteFeatureInputStream.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteResultSetConverter.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteValueConverterFactory.java
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ko_mysql.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ko_oracle.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ko_pg.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ko_sqlite.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ko_sqlserver.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ok_mysql.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ok_oracle.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ok_pg.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ok_sqlite.png
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/ok_sqlserver.png
Removed Paths:
-------------
core/trunk/src/com/vividsolutions/jump/datastore/OracleDataStoreExtension.java
Modified: core/trunk/ChangeLog
===================================================================
--- core/trunk/ChangeLog 2015-12-13 14:24:16 UTC (rev 4608)
+++ core/trunk/ChangeLog 2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,5 +1,21 @@
# for display continuity sake please use 2 spaces instead of tabs
+2015-13-11 Nicolas Ribot <nicolas.ri...@gmail.com>
+ * Spatial Databases support enhanced: support for Oracle Spatial,
MySQL/MariaDB
+ Spatialite added.
+ * FilterQuery and Adhoc queries supported for these databases
+ * Error detection in case of bad WHERE clause detected when loading a layer
(layer
+ removed from Panel)
+ * New connection icons according to database type
+ * Oracle Spatial: reads SDO_GEOMETRY with geotools code
+ reflection used on com.oracle.* methods to avoid jar
dependence
+ (ede code)
+ * MariaDB: supports WKB and natives binary types, thanks to code from Larry
Reeder
+ * Spatialite: supports Spatialite binary type, WKB and WKT.
+ supports several geometry_column metadata table layout (code
from
+ Larry Reeder DB Plugin)
+ *
+
2015-12-11 mmichaud <m.michael.mich...@orange.fr>
* Switch pom to official jts-1.14 release
Added: core/trunk/licenseheader.txt
===================================================================
--- core/trunk/licenseheader.txt (rev 0)
+++ core/trunk/licenseheader.txt 2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,8 @@
+<#if licenseFirst??>
+${licenseFirst}
+</#if>
+${licensePrefix}Here comes the text of your license
+${licensePrefix}Each line should be prefixed with ${licensePrefix}
+<#if licenseLast??>
+${licenseLast}
+</#if>
\ No newline at end of file
Modified: core/trunk/pom.xml
===================================================================
--- core/trunk/pom.xml 2015-12-13 14:24:16 UTC (rev 4608)
+++ core/trunk/pom.xml 2015-12-13 15:02:36 UTC (rev 4609)
@@ -902,5 +902,34 @@
<artifactId>org-netbeans-swing-outline</artifactId>
<version>7.2</version>
</dependency>
+ <!--for spatial database plugin-->
+ <dependency>
+ <groupId>mysql</groupId>
+ <artifactId>mysql-connector-java</artifactId>
+ <version>5.1.34</version>
+ </dependency>
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ <version>3.8.7</version>
+ </dependency>
+
+ <dependency>
+ <groupId>com.oracle</groupId>
+ <artifactId>ojdbc6</artifactId>
+ <version>11.2.0.3</version>
+ <scope>runtime</scope>
+ </dependency>
+ <dependency>
+ <groupId>org.geotools</groupId>
+ <artifactId>gt2-oracle-spatial</artifactId>
+ <version>2.3.2</version>
+ <scope>runtime</scope>
+ </dependency>
+<!-- <dependency>
+ <groupId>org.openjump.core.ui.plugin.datastore</groupId>
+ <artifactId>SpatialDatabasesPlugin</artifactId>
+ <version>0.1-SNAPSHOT</version>
+ </dependency>-->
</dependencies>
</project>
Modified: core/trunk/scripts/default-plugins.xml
===================================================================
--- core/trunk/scripts/default-plugins.xml 2015-12-13 14:24:16 UTC (rev
4608)
+++ core/trunk/scripts/default-plugins.xml 2015-12-13 15:02:36 UTC (rev
4609)
@@ -26,8 +26,15 @@
org.openjump.core.ui.plugin.datastore.postgis.SaveToPostGISPlugIn
</plug-in>
<extension>
- com.vividsolutions.jump.datastore.OracleDataStoreExtension
+
com.vividsolutions.jump.datastore.oracle.OracleDataStoreExtension
</extension>
+ <!--adds other Spatial databases extensions-->
+ <extension>
+
com.vividsolutions.jump.datastore.mariadb.MariadbDataStoreExtension
+ </extension>
+ <extension>
+
com.vividsolutions.jump.datastore.spatialite.SpatialiteDataStoreExtension
+ </extension>
<plug-in>
org.openjump.core.ccordsys.srid.EnsureAllLayersHaveSRIDStylePlugIn
</plug-in>
Deleted:
core/trunk/src/com/vividsolutions/jump/datastore/OracleDataStoreExtension.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/OracleDataStoreExtension.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/OracleDataStoreExtension.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,60 +0,0 @@
-package com.vividsolutions.jump.datastore;
-
-import static
com.vividsolutions.jump.datastore.oracle.OracleDataStoreDriver.JDBC_CLASS;
-import static
com.vividsolutions.jump.datastore.oracle.OracleDataStoreDriver.GT_SDO_CLASS_NAME;
-
-import com.vividsolutions.jump.datastore.oracle.OracleDataStoreDriver;
-import com.vividsolutions.jump.workbench.WorkbenchContext;
-import com.vividsolutions.jump.workbench.plugin.Extension;
-import com.vividsolutions.jump.workbench.plugin.PlugInContext;
-import java.sql.Driver;
-import java.sql.DriverManager;
-
-/**
- *
- * @author nicolas ribot
- */
-public class OracleDataStoreExtension extends Extension {
- private static boolean disabled = false;
-
- public String getName() {
- return "Oracle Spatial Datastore Extension";
- }
-
- public String getVersion() {
- return "0.3 (2015-12-04)";
- }
-
- public String getMessage() {
- return disabled ? "Disabled: Missing either ojdbc6.jar or
gt2-oracle-spatial-2.x.jar in classpath"
- : "";
- }
-
- public void configure(PlugInContext context) throws Exception {
- WorkbenchContext wbc = context.getWorkbenchContext();
-
- // registers the OracleDataStore driver to the system:
- try {
- ClassLoader pluginLoader = wbc.getWorkbench().getPlugInManager()
- .getClassLoader();
- // check for ojdbc6.jar
- DriverManager.registerDriver(
- (Driver)Class.forName(JDBC_CLASS, true, pluginLoader).newInstance());
-
- // check for gt2-oracle-spatial-2.x.jar
- Class.forName(GT_SDO_CLASS_NAME, true, pluginLoader)
- .newInstance();
- // register the datastore
- wbc.getRegistry().createEntry(DataStoreDriver.REGISTRY_CLASSIFICATION,
- new OracleDataStoreDriver());
- } catch (Exception e) {
- disabled = true;
- wbc.getWorkbench()
- .getFrame()
- .log(
- "Oracle Spatial Data Store disabled:\n\t" + e.toString()
- + "\n\tOracle JDBC Driver and gt2-oracle-spatial-2.x.jar must
exist in the classpath !", this.getClass());
- }
- }
-
-}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSConnection.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSConnection.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSConnection.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,87 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jump.I18N;
+import com.vividsolutions.jump.datastore.AdhocQuery;
+import com.vividsolutions.jump.datastore.FilterQuery;
+import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+import com.vividsolutions.jump.feature.FeatureSchema;
+import com.vividsolutions.jump.io.FeatureInputStream;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+/**
+ *
+ * @author nicolas
+ */
+public class MariadbDSConnection extends SpatialDatabasesDSConnection {
+
+ public MariadbDSConnection(Connection con) {
+ super(con); // ?
+ connection = con;
+ this.dbMetadata = new MariadbDSMetadata(this);
+ }
+
+ @Override
+ public SpatialDatabasesSQLBuilder getSqlBuilder(SpatialReferenceSystemID
srid, String[] colNames) {
+ return new MariadbSQLBuilder(this.dbMetadata, srid, colNames);
+ }
+
+ /**
+ * Executes a filter query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeFilterQuery(FilterQuery query) throws
SQLException {
+ SpatialReferenceSystemID srid =
dbMetadata.getSRID(query.getDatasetName(), query.getGeometryAttributeName());
+ String[] colNames = dbMetadata.getColumnNames(query.getDatasetName());
+
+ MariadbSQLBuilder builder =
(MariadbSQLBuilder)this.getSqlBuilder(srid, colNames);
+ String queryString = builder.getSQL(query);
+
+ // [mmichaud 2013-08-07] add a parameter for database primary key name
+ return new MariadbFeatureInputStream(connection, queryString,
query.getPrimaryKey());
+ }
+
+ /**
+ * Executes an adhoc query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeAdhocQuery(AdhocQuery query) throws
Exception {
+ String queryString = query.getQuery();
+ MariadbFeatureInputStream ifs = new
MariadbFeatureInputStream(connection, queryString, query.getPrimaryKey());
+
+ // Nicolas Ribot: getting FeatureSchema here actually runs the query:
if an error occurs, must trap it here
+ FeatureSchema fs = null;
+ try {
+ fs = ifs.getFeatureSchema();
+ } catch (Exception e) {
+ throw new Exception(
+
I18N.get(com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".SQL-error") + e.getMessage());
+ }
+
+ if (fs.getGeometryIndex() < 0) {
+ throw new Exception(I18N.get(
+
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".resultset-must-have-a-geometry-column"));
+ }
+ return ifs;
+
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,63 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jump.datastore.DataStoreConnection;
+import com.vividsolutions.jump.datastore.spatialdatabases.*;
+import com.vividsolutions.jump.datastore.GeometryColumn;
+import java.util.List;
+
+public class MariadbDSMetadata extends SpatialDatabasesDSMetadata {
+
+ public MariadbDSMetadata(DataStoreConnection con) {
+ conn = con;
+ // TODO: defaults to database name ?
+ defaultSchemaName = "";
+ // TODO: use bind parameters to avoid SQL injection
+ datasetNameQuery = "select distinct t.TABLE_SCHEMA, t.TABLE_NAME \n" +
+ "from information_schema.TABLES t join information_schema.COLUMNS
C \n" +
+ " on t.TABLE_NAME = c.TABLE_NAME and t.TABLE_SCHEMA =
c.TABLE_SCHEMA\n" +
+ "where t.TABLE_TYPE not in ('SYSTEM VIEW')\n" +
+ "and c.COLUMN_TYPE = 'geometry';";
+ spatialDbName = "MariaDB/MySQL";
+ spatialExtentQuery1 = "select
st_asBinary(st_envelope(Geomfromtext(concat(concat(\"geometrycollection(\",group_concat(astext(%s))),\")\"))))
from %s.%s;";
+ // NO metadata => same query is defined.
+ spatialExtentQuery2 = spatialExtentQuery1;
+ geoColumnsQuery = "select c.COLUMN_NAME, 0, 'geometry' \n" +
+ "from information_schema.TABLES t join information_schema.COLUMNS
C \n" +
+ " on t.TABLE_NAME = c.TABLE_NAME and t.TABLE_SCHEMA =
c.TABLE_SCHEMA\n" +
+ "where t.TABLE_TYPE not in ('SYSTEM VIEW')\n" +
+ "and t.TABLE_SCHEMA = '%s' and t.TABLE_NAME = '%s'\n" +
+ "and c.COLUMN_TYPE = 'geometry'";
+ sridQuery = "select case when min(st_srid(%s)) <> max(st_srid(%s))
then 0 else min(st_srid(%s)) end as srid\n" +
+ "from %s.%s";
+ }
+
+ @Override
+ public String getSpatialExtentQuery1(String schema, String table, String
attributeName) {
+ return String.format(this.spatialExtentQuery1, attributeName, schema,
table);
+ }
+
+ @Override
+ public String getSpatialExtentQuery2(String schema, String table, String
attributeName) {
+ return String.format(this.spatialExtentQuery2, attributeName, schema,
table);
+ }
+
+ @Override
+ public String getGeoColumnsQuery(String datasetName) {
+ return String.format(this.geoColumnsQuery, getSchemaName(datasetName),
getTableName(datasetName));
+ }
+
+ @Override
+ public String getSridQuery(String schemaName, String tableName, String
colName) {
+ // TODO
+ return String.format(this.sridQuery, colName, colName, colName,
schemaName, tableName);
+ }
+
+ @Override
+ public List<GeometryColumn> getGeometryAttributes(String datasetName) {
+ String sql = this.getGeoColumnsQuery(datasetName);
+ // TODO: manage srid by executing 2 SQL queries: one for geo cols, one
for
+ // srids.
+ return getGeometryAttributes(sql, datasetName);
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreDriver.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreDriver.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreDriver.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,34 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jump.datastore.DataStoreConnection;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDataStoreDriver;
+import com.vividsolutions.jump.parameter.ParameterList;
+
+/**
+ * A driver for supplying {@link SpatialDatabaseDSConnection}s
+ */
+public class MariadbDataStoreDriver
+ extends SpatialDatabasesDataStoreDriver {
+
+ // TODO: uniformize
+ public final static String JDBC_CLASS = "com.mysql.jdbc.Driver";
+
+ public MariadbDataStoreDriver() {
+ this.driverName = "MariaDB/MySQL";
+ this.jdbcClass = "com.mysql.jdbc.Driver";
+ this.urlPrefix = "jdbc:mysql://";
+ }
+
+ /**
+ * returns the right type of DataStoreConnection
+ * @param params
+ * @return
+ * @throws Exception
+ */
+ @Override
+ public DataStoreConnection createConnection(ParameterList params)
+ throws Exception {
+ DataStoreConnection ret = super.createConnection(params);
+ return new MariadbDSConnection(ret.getConnection());
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreExtension.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreExtension.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDataStoreExtension.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,56 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jump.datastore.DataStoreDriver;
+import static
com.vividsolutions.jump.datastore.mariadb.MariadbDataStoreDriver.JDBC_CLASS;
+
+import com.vividsolutions.jump.workbench.WorkbenchContext;
+import com.vividsolutions.jump.workbench.plugin.Extension;
+import com.vividsolutions.jump.workbench.plugin.PlugInContext;
+import java.sql.Driver;
+import java.sql.DriverManager;
+
+/**
+ *
+ * @author nicolas ribot
+ */
+public class MariadbDataStoreExtension extends Extension {
+ private static boolean disabled = false;
+
+ public String getName() {
+ return "MariaDB/MySQL Spatial Datastore Extension";
+ }
+
+ public String getVersion() {
+ return "0.3 (2015-12-04)";
+ }
+
+ public String getMessage() {
+ return disabled ? "Disabled: Missing mysql-connector-java-<version>.jar in
classpath"
+ : "";
+ }
+
+ public void configure(PlugInContext context) throws Exception {
+ WorkbenchContext wbc = context.getWorkbenchContext();
+
+ // registers the MariaDBDataStore driver to the system:
+ try {
+ ClassLoader pluginLoader = wbc.getWorkbench().getPlugInManager()
+ .getClassLoader();
+ // check for ojdbc6.jar
+ DriverManager.registerDriver(
+ (Driver)Class.forName(JDBC_CLASS, true, pluginLoader).newInstance());
+
+ // register the datastore
+ wbc.getRegistry().createEntry(DataStoreDriver.REGISTRY_CLASSIFICATION,
+ new MariadbDataStoreDriver());
+ } catch (Exception e) {
+ disabled = true;
+ wbc.getWorkbench()
+ .getFrame()
+ .log(
+ "MariaDB Spatial Data Store disabled:\n\t" + e.toString()
+ + "\n\tMariaDB JDBC Driver (mysql-connector-java-<version>.jar)
must exist in the classpath !", this.getClass());
+ }
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbFeatureInputStream.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbFeatureInputStream.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbFeatureInputStream.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,30 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
+import java.sql.Connection;
+import java.sql.ResultSet;
+
+/**
+ *
+ * @author nicolas Ribot
+ */
+public class MariadbFeatureInputStream extends
SpatialDatabasesFeatureInputStream {
+ public MariadbFeatureInputStream(Connection conn, String queryString) {
+ super(conn, queryString);
+ }
+
+ public MariadbFeatureInputStream(Connection conn, String queryString,
String externalIdentifier) {
+ super(conn, queryString, externalIdentifier);
+ }
+
+ /**
+ * Returns a MariadbResultSetConverter
+ * @param rs
+ * @return
+ */
+ @Override
+ protected SpatialDatabasesResultSetConverter
getResultSetConverter(ResultSet rs) {
+ return new MariadbResultSetConverter(conn, rs);
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbResultSetConverter.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbResultSetConverter.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbResultSetConverter.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,22 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
+import com.vividsolutions.jump.feature.Feature;
+import com.vividsolutions.jump.feature.FeatureSchema;
+import java.sql.Connection;
+import java.sql.ResultSet;
+
+/**
+ * Implements the mapping between a result set and a {@link FeatureSchema} and
+ * {@link Feature} set.
+ *
+ * This is a transient worker class, whose lifetime should be no longer than
the
+ * lifetime of the provided ResultSet
+ */
+public class MariadbResultSetConverter extends
SpatialDatabasesResultSetConverter {
+
+ public MariadbResultSetConverter(Connection conn, ResultSet rs) {
+ super(conn, rs);
+ this.odm = new MariadbValueConverterFactory(conn);
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbSQLBuilder.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbSQLBuilder.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbSQLBuilder.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,85 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jts.geom.Envelope;
+import com.vividsolutions.jump.datastore.FilterQuery;
+import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSMetadata;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+
+
+/**
+ * Creates SQL query strings for a Spatial database.
+ * To be overloaded by classes implementing a spatial database support.
+ */
+public class MariadbSQLBuilder extends SpatialDatabasesSQLBuilder {
+
+ public MariadbSQLBuilder(SpatialDatabasesDSMetadata dsMetadata,
SpatialReferenceSystemID defaultSRID, String[] colNames) {
+ super(dsMetadata, defaultSRID, colNames);
+ }
+
+ /**
+ * Builds a valid SQL spatial query with the given spatial filter.
+ * @param query
+ * @return a SQL query to get column names
+ */
+ @Override
+ public String getSQL(FilterQuery query) {
+ StringBuilder qs = new StringBuilder();
+ //HACK
+ qs.append("SELECT ");
+ qs.append(getColumnListSpecifier(colNames,
query.getGeometryAttributeName()));
+ qs.append(" FROM ").append(query.getDatasetName()).append("");
+ qs.append(" t WHERE ");
+ qs.append(buildBoxFilter(query));
+
+ String whereCond = query.getCondition();
+ if (whereCond != null) {
+ qs.append(" AND ");
+ qs.append(whereCond);
+ }
+ int limit = query.getLimit();
+ if (limit != 0 && limit != Integer.MAX_VALUE) {
+ qs.append(" LIMIT ").append(limit);
+ }
+ return qs.toString();
+ };
+
+ /**
+ * Returns the string representing a SQL column definition.
+ * Implementors should take care of column names (case, quotes)
+ * @param colNames
+ * @param geomColName
+ * @return column list
+ */
+ @Override
+ protected String getColumnListSpecifier(String[] colNames, String
geomColName) {
+ // Added double quotes around each column name in order to read mixed case
table names
+ // correctly [mmichaud 2007-05-13]
+ StringBuilder buf = new StringBuilder();
+ //buf.append("ST_AsBinary(").append(geomColName).append(") as
").append(geomColName);
+ // Nicolas Ribot: 12 dec: Native reader supported now
+ buf.append(geomColName);
+ for (String colName : colNames) {
+ if (! geomColName.equalsIgnoreCase(colName)) {
+ buf.append(",").append(colName).append("");
+ }
+ }
+ return buf.toString();
+ }
+
+ @Override
+ protected String buildBoxFilter(FilterQuery query) {
+ Envelope env = query.getFilterGeometry().getEnvelopeInternal();
+
+ // Example of MariaDB SQL: where st_Intersects(b.geom,
st_polygonFromText('POLYGON((4 4, 5 4, 5 5, 4 5, 4 4))'))
+ StringBuilder buf = new StringBuilder();
+
buf.append("st_intersects(").append(query.getGeometryAttributeName()).append(",
st_polygonFromText('POLYGON((");
+ buf.append(env.getMinX()).append(" ").append(env.getMinY()).append(",")
+ .append(env.getMaxX()).append(" ").append(env.getMinY()).append(",")
+ .append(env.getMaxX()).append(" ").append(env.getMaxY()).append(",")
+ .append(env.getMinX()).append(" ").append(env.getMaxY()).append(",")
+ .append(env.getMinX()).append(" ").append(env.getMinY());
+ buf.append("))'))");
+ return buf.toString();
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbValueConverterFactory.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbValueConverterFactory.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbValueConverterFactory.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,128 @@
+package com.vividsolutions.jump.datastore.mariadb;
+
+import com.vividsolutions.jts.geom.Geometry;
+import com.vividsolutions.jts.io.ParseException;
+import com.vividsolutions.jts.io.WKBReader;
+import com.vividsolutions.jump.datastore.jdbc.ValueConverter;
+import com.vividsolutions.jump.datastore.jdbc.ValueConverterFactory;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesValueConverterFactory;
+import com.vividsolutions.jump.feature.AttributeType;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.Arrays;
+
+/**
+ *
+ */
+public class MariadbValueConverterFactory extends
SpatialDatabasesValueConverterFactory {
+
+ protected final ValueConverter MYSQLWKB_GEOMETRY_MAPPER = new
MariadbValueConverterFactory.MySQLWKBGeometryValueConverter();
+
+ public MariadbValueConverterFactory(Connection conn) {
+ super(conn);
+ }
+
+ @Override
+ public ValueConverter getConverter(ResultSetMetaData rsm, int columnIndex)
+ throws SQLException {
+ String dbTypeName = rsm.getColumnTypeName(columnIndex);
+
+ // manages 2 cases: type retrieved from Database metadata (DataStore
Panel)
+ // and from direct Adhoc query (type of the column resultset).
+ if ("LONGBLOB".equalsIgnoreCase(dbTypeName) ||
"GEOMETRY".equalsIgnoreCase(dbTypeName)) // WKB is now the normal way to store
geometry in PostGIS [mmichaud 2007-05-13]
+ {
+ return MYSQLWKB_GEOMETRY_MAPPER;
+ }
+
+ // handle the standard types
+ ValueConverter stdConverter = ValueConverterFactory.getConverter(rsm,
columnIndex);
+ if (stdConverter != null) {
+ return stdConverter;
+ }
+
+ // default - can always show it as a string!
+ return ValueConverterFactory.STRING_MAPPER;
+ }
+
+ /**
+ * Custom WKB reader for MySQL/MariaDB (cf Jump DB Query plugin source code).
+ * Provides support for both Binary format is blob with 4 empty bytes as the
+ * beginning From Larry Reader code
+ */
+ class MySQLWKBGeometryValueConverter implements ValueConverter {
+
+ public AttributeType getType() {
+ return AttributeType.GEOMETRY;
+ }
+
+ public Object getValue(ResultSet rs, int columnIndex)
+ throws IOException, SQLException, ParseException {
+ byte[] bytes = rs.getBytes(columnIndex);
+
+ Geometry geometry = null;
+ if (bytes == null || bytes.length < 5) {
+ geometry = wktReader.read("GEOMETRYCOLLECTION EMPTY");
+ } else {
+ boolean nativeFormat = appearsToBeNativeFormat(bytes);
+ WKBReader wr = new WKBReader();
+
+ if (nativeFormat) {
+
+ //copy the byte array, removing the first four
+ //zero bytes added by mysql
+ byte[] wkb = new byte[bytes.length - 4];
+ System.arraycopy(bytes, 4, wkb, 0, wkb.length);
+ geometry = wr.read(wkb);
+ } else {
+ // true WKB format as from st_asbinary
+ geometry = wr.read(bytes);
+ }
+ }
+
+ return geometry;
+ }
+ }
+
+ /**
+ * From Larry Reeder, code to detect MySQL spatial type.
+ * Added detection code for older/strange mysql geometry format beginning
with 6A 08 00 00
+ * TODO: make method public static in its package ?
+ * The JUMP DB Query Plugin is Copyright (C) 2007 Larry Reeder
+ * JUMP is Copyright (C) 2003 Vivid Solutions
+ *
+ */
+ private boolean appearsToBeNativeFormat(final byte[] geometryAsBytes) {
+ //use a heuristic here. MySQL seems to store
+ //geometries as WKB with four leading zero bytes
+ //so, the first four should be zero, with the fifth
+ //byte being the byte-order byte, which always seems
+ //to be 0x01 in MySQL
+ int firstFive = geometryAsBytes[0]
+ | geometryAsBytes[1]
+ | geometryAsBytes[2]
+ | geometryAsBytes[3]
+ | geometryAsBytes[4];
+ byte[] ctrl = javax.xml.bind.DatatypeConverter.parseHexBinary("6A080000");
+ byte[] firstFour = new byte[4];
+ System.arraycopy(geometryAsBytes, 0, firstFour, 0, firstFour.length);
+ boolean nativeFormat = false;
+
+ if ((firstFive & 0xFF) == 0x01) {
+ //the next section in WKB is the geometry type.
+ //MySql supports types 1-7
+ if (geometryAsBytes[5] >= 1 && geometryAsBytes[5] <= 7) {
+ nativeFormat = true;
+ }
+
+ } else if (Arrays.equals(ctrl, firstFour)) {
+ // Nicolas Ribot: some geometries begin with: 6A 08 00 00
+ // TODO: document how/why...
+ nativeFormat = true;
+ }
+ return nativeFormat;
+ }
+
+}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSConnection.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSConnection.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSConnection.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,16 +1,12 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
+import com.vividsolutions.jump.I18N;
+import com.vividsolutions.jump.datastore.AdhocQuery;
import com.vividsolutions.jump.datastore.FilterQuery;
import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection;
-import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSMetadata;
-import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+import com.vividsolutions.jump.feature.FeatureSchema;
import com.vividsolutions.jump.io.FeatureInputStream;
import java.sql.Connection;
import java.sql.SQLException;
@@ -44,8 +40,48 @@
*/
@Override
public FeatureInputStream executeFilterQuery(FilterQuery query) throws
SQLException {
- SpatialDatabasesFeatureInputStream fis =
(SpatialDatabasesFeatureInputStream)super.executeFilterQuery(query);
- return new OracleFeatureInputStream(fis.getConnection(),
fis.getQueryString(), query.getPrimaryKey());
+ SpatialReferenceSystemID srid =
dbMetadata.getSRID(query.getDatasetName(), query.getGeometryAttributeName());
+ String[] colNames = dbMetadata.getColumnNames(query.getDatasetName());
+
+ OracleSQLBuilder builder = (OracleSQLBuilder)this.getSqlBuilder(srid,
colNames);
+ String queryString = builder.getSQL(query);
+
+ // [mmichaud 2013-08-07] add a parameter for database primary key name
+ return new OracleFeatureInputStream(connection, queryString,
query.getPrimaryKey());
}
+ /**
+ * Executes an adhoc query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeAdhocQuery(AdhocQuery query) throws
Exception {
+ String queryString = query.getQuery();
+ OracleFeatureInputStream ifs = new
OracleFeatureInputStream(connection, queryString, query.getPrimaryKey());
+
+ // Nicolas Ribot: getting FeatureSchema here actually runs the query:
if an error occurs, must trap it here
+ FeatureSchema fs = null;
+ try {
+ fs = ifs.getFeatureSchema();
+ } catch (Exception e) {
+ throw new Exception(
+
I18N.get(com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".SQL-error") + e.getMessage());
+ }
+
+ if (fs.getGeometryIndex() < 0) {
+ throw new Exception(I18N.get(
+
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".resultset-must-have-a-geometry-column"));
+ }
+ return ifs;
+
+ }
+
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreDriver.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreDriver.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreDriver.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,13 +1,6 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
-import com.vividsolutions.jump.datastore.DataStoreConnection;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDataStoreDriver;
-import com.vividsolutions.jump.parameter.ParameterList;
/**
* A driver for supplying {@link SpatialDatabaseDSConnection}s
@@ -19,22 +12,13 @@
public static final String GT_SDO_CLASS_NAME =
"org.geotools.data.oracle.sdo.SDO";
+ /**
+ * Constructor
+ * TODO: static variables for fields
+ */
public OracleDataStoreDriver() {
this.driverName = "Oracle Spatial";
this.jdbcClass = OracleDataStoreDriver.JDBC_CLASS;
this.urlPrefix = "jdbc:oracle:thin:@//";
}
-
- /**
- * returns the right type of DataStoreConnection
- * @param params
- * @return
- * @throws Exception
- */
- @Override
- public DataStoreConnection createConnection(ParameterList params)
- throws Exception {
- DataStoreConnection ret = super.createConnection(params);
- return new OracleDSConnection(ret.getConnection());
- }
}
Copied:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreExtension.java
(from rev 4579,
core/trunk/src/com/vividsolutions/jump/datastore/OracleDataStoreExtension.java)
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreExtension.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDataStoreExtension.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,60 @@
+package com.vividsolutions.jump.datastore.oracle;
+
+import com.vividsolutions.jump.datastore.DataStoreDriver;
+import static
com.vividsolutions.jump.datastore.oracle.OracleDataStoreDriver.JDBC_CLASS;
+import static
com.vividsolutions.jump.datastore.oracle.OracleDataStoreDriver.GT_SDO_CLASS_NAME;
+
+import com.vividsolutions.jump.workbench.WorkbenchContext;
+import com.vividsolutions.jump.workbench.plugin.Extension;
+import com.vividsolutions.jump.workbench.plugin.PlugInContext;
+import java.sql.Driver;
+import java.sql.DriverManager;
+
+/**
+ *
+ * @author nicolas ribot
+ */
+public class OracleDataStoreExtension extends Extension {
+ private static boolean disabled = false;
+
+ public String getName() {
+ return "Oracle Spatial Datastore Extension";
+ }
+
+ public String getVersion() {
+ return "0.3 (2015-12-04)";
+ }
+
+ public String getMessage() {
+ return disabled ? "Disabled: Missing either ojdbc6.jar or
gt2-oracle-spatial-2.x.jar in classpath"
+ : "";
+ }
+
+ public void configure(PlugInContext context) throws Exception {
+ WorkbenchContext wbc = context.getWorkbenchContext();
+
+ // registers the OracleDataStore driver to the system:
+ try {
+ ClassLoader pluginLoader = wbc.getWorkbench().getPlugInManager()
+ .getClassLoader();
+ // check for ojdbc6.jar
+ DriverManager.registerDriver(
+ (Driver)Class.forName(JDBC_CLASS, true, pluginLoader).newInstance());
+
+ // check for gt2-oracle-spatial-2.x.jar
+ Class.forName(GT_SDO_CLASS_NAME, true, pluginLoader)
+ .newInstance();
+ // register the datastore
+ wbc.getRegistry().createEntry(DataStoreDriver.REGISTRY_CLASSIFICATION,
+ new OracleDataStoreDriver());
+ } catch (Exception e) {
+ disabled = true;
+ wbc.getWorkbench()
+ .getFrame()
+ .log(
+ "Oracle Spatial Data Store disabled:\n\t" + e.toString()
+ + "\n\tOracle JDBC Driver and gt2-oracle-spatial-2.x.jar must
exist in the classpath !", this.getClass());
+ }
+ }
+
+}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleFeatureInputStream.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleFeatureInputStream.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleFeatureInputStream.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleResultSetConverter.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleResultSetConverter.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleResultSetConverter.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
@@ -21,7 +16,7 @@
public class OracleResultSetConverter extends
SpatialDatabasesResultSetConverter {
public OracleResultSetConverter(Connection conn, ResultSet rs) {
- super(conn, rs);
+ this.rs = rs;
this.odm = new OracleValueConverterFactory(conn);
}
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleSQLBuilder.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleSQLBuilder.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleSQLBuilder.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
import com.vividsolutions.jts.geom.Envelope;
@@ -82,7 +77,7 @@
buf.append(geomColName).append(" as
").append("\"").append(geomColName).append("\"");
for (String colName : colNames) {
if (!geomColName.equalsIgnoreCase(colName)) {
- buf.append(",\"").append(colName).append("\"");
+ buf.append(", \"").append(colName).append("\"");
}
}
return buf.toString();
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleValueConverterFactory.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleValueConverterFactory.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleValueConverterFactory.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.oracle;
import com.vividsolutions.jts.io.ParseException;
@@ -51,13 +46,6 @@
.getStatement().getConnection()));
return converterMethod.invoke(converter,
structClazz.cast(geometryObject));
-
- //** this is original implementation w/o reflection **//
- // org.geotools.data.oracle.sdo.GeometryConverter geometryConverter =
- // new
org.geotools.data.oracle.sdo.GeometryConverter((oracle.jdbc.OracleConnection)
- // rs.getStatement().getConnection());
- // return geometryConverter.asGeometry((oracle.sql.STRUCT)
- // geometryObject);
}
}
@@ -68,9 +56,10 @@
@Override
public ValueConverter getConverter(ResultSetMetaData rsm, int columnIndex)
throws SQLException {
- String classname = rsm.getColumnClassName(columnIndex);
String dbTypeName = rsm.getColumnTypeName(columnIndex);
+ // manages 2 cases: type retrieved from Database metadata (DataStore Panel)
+ // and from direct Adhoc query (type of the column resultset).
if (dbTypeName.equalsIgnoreCase("MDSYS.SDO_GEOMETRY")) {
return ORA_STRUCT_GEOMETRY_MAPPER;
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSConnection.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSConnection.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSConnection.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,17 +1,16 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
+import com.vividsolutions.jump.I18N;
+import com.vividsolutions.jump.datastore.AdhocQuery;
import com.vividsolutions.jump.datastore.FilterQuery;
import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection;
-import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSMetadata;
-import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+import com.vividsolutions.jump.feature.FeatureSchema;
import com.vividsolutions.jump.io.FeatureInputStream;
+import com.vividsolutions.jump.workbench.WorkbenchContext;
+import com.vividsolutions.jump.workbench.ui.ErrorDialog;
+import com.vividsolutions.jump.workbench.ui.WorkbenchFrame;
import java.sql.Connection;
import java.sql.SQLException;
@@ -37,6 +36,8 @@
*
* The SRID is optional for queries - it will be determined automatically
* from the table metadata if not supplied.
+ * 13 dec 2015: query is now tested before execution, to prevent adding an
empty
+ * layer built from invalid WHERE clause, for instance.
*
* @param query the query to execute
* @return the results of the query
@@ -44,8 +45,47 @@
*/
@Override
public FeatureInputStream executeFilterQuery(FilterQuery query) throws
SQLException {
- SpatialDatabasesFeatureInputStream fis =
(SpatialDatabasesFeatureInputStream)super.executeFilterQuery(query);
- return new PostgisFeatureInputStream(fis.getConnection(),
fis.getQueryString(), query.getPrimaryKey());
+ SpatialReferenceSystemID srid =
dbMetadata.getSRID(query.getDatasetName(), query.getGeometryAttributeName());
+ String[] colNames = dbMetadata.getColumnNames(query.getDatasetName());
+
+ PostgisSQLBuilder builder =
(PostgisSQLBuilder)this.getSqlBuilder(srid, colNames);
+ String queryString = builder.getSQL(query);
+
+ // [mmichaud 2013-08-07] add a parameter for database primary key name
+ return new PostgisFeatureInputStream(connection, queryString,
query.getPrimaryKey());
}
+ /**
+ * Executes an adhoc query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeAdhocQuery(AdhocQuery query) throws
Exception {
+ String queryString = query.getQuery();
+ PostgisFeatureInputStream ifs = new
PostgisFeatureInputStream(connection, queryString, query.getPrimaryKey());
+
+ // Nicolas Ribot: getting FeatureSchema here actually runs the query:
if an error occurs, must trap it here
+ FeatureSchema fs = null;
+ try {
+ fs = ifs.getFeatureSchema();
+ } catch (Exception e) {
+ throw new Exception(
+ I18N.get(SpatialDatabasesDSConnection.class.getName()
+ +".SQL-error") + e.getMessage());
+ }
+
+ if (fs.getGeometryIndex() < 0) {
+ throw new
Exception(I18N.get(SpatialDatabasesDSConnection.class.getName()
+ +".resultset-must-have-a-geometry-column"));
+ }
+ return ifs;
+
+ }
+
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
import com.vividsolutions.jump.datastore.spatialdatabases.*;
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDataStoreDriver.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDataStoreDriver.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDataStoreDriver.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,13 +1,6 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
-import com.vividsolutions.jump.datastore.DataStoreConnection;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDataStoreDriver;
-import com.vividsolutions.jump.parameter.ParameterList;
/**
* A driver for supplying {@link SpatialDatabaseDSConnection}s
@@ -15,22 +8,11 @@
public class PostgisDataStoreDriver
extends SpatialDatabasesDataStoreDriver {
+ public final static String JDBC_CLASS = "org.postgresql.Driver";
+
public PostgisDataStoreDriver() {
this.driverName = "PostGIS";
this.jdbcClass = "org.postgresql.Driver";
this.urlPrefix = "jdbc:postgresql://";
}
-
- /**
- * returns the right type of DataStoreConnection
- * @param params
- * @return
- * @throws Exception
- */
- @Override
- public DataStoreConnection createConnection(ParameterList params)
- throws Exception {
- DataStoreConnection ret = super.createConnection(params);
- return new PostgisDSConnection(ret.getConnection());
- }
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisFeatureInputStream.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisFeatureInputStream.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisFeatureInputStream.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,27 +1,34 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
+import com.vividsolutions.jump.workbench.JUMPWorkbench;
import java.sql.Connection;
import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
/**
*
- * @author nicolas
+ * @author nicolas Ribot
*/
public class PostgisFeatureInputStream extends
SpatialDatabasesFeatureInputStream {
- public PostgisFeatureInputStream(Connection conn, String queryString) {
- super(conn, queryString);
+
+ public PostgisFeatureInputStream(Connection conn, String queryString) {
+ this(conn, queryString, null);
}
public PostgisFeatureInputStream(Connection conn, String queryString,
String externalIdentifier) {
super(conn, queryString, externalIdentifier);
+ try {
+ JUMPWorkbench.getInstance().getFrame().log("creating a
PostgisFeatureInputStream (class:" + this.getClass()
+ + " ) (driver: " + conn.getMetaData().getDriverName() + ") id"
+ + this.hashCode(), this.getClass());
+ } catch (SQLException ex) {
+
Logger.getLogger(PostgisFeatureInputStream.class.getName()).log(Level.SEVERE,
null, ex);
}
+ }
/**
* Returns a PostgisResultSetConverter
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisResultSetConverter.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisResultSetConverter.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisResultSetConverter.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
@@ -20,8 +15,8 @@
*/
public class PostgisResultSetConverter extends
SpatialDatabasesResultSetConverter {
- public PostgisResultSetConverter(Connection conn, ResultSet rs) {
- super(conn, rs);
- this.odm = new PostgisValueConverterFactory(conn);
- }
+ public PostgisResultSetConverter(Connection conn, ResultSet rs) {
+ this.rs = rs;
+ this.odm = new PostgisValueConverterFactory(conn);
+ }
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisSQLBuilder.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisSQLBuilder.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisSQLBuilder.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
import com.vividsolutions.jts.geom.Envelope;
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisValueConverterFactory.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisValueConverterFactory.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisValueConverterFactory.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.postgis;
import com.vividsolutions.jump.datastore.jdbc.ValueConverter;
@@ -13,38 +8,35 @@
import java.sql.SQLException;
/**
- *
+ * Factory to convert Postgis geometric data type.
+ *
*/
public class PostgisValueConverterFactory extends
SpatialDatabasesValueConverterFactory {
- public PostgisValueConverterFactory(Connection conn) {
- super(conn);
- }
+ public PostgisValueConverterFactory(Connection conn) {
+ super(conn);
+ }
- @Override
- public ValueConverter getConverter(ResultSetMetaData rsm, int columnIndex)
- throws SQLException {
- String classname = rsm.getColumnClassName(columnIndex);
- String dbTypeName = rsm.getColumnTypeName(columnIndex);
+ @Override
+ public ValueConverter getConverter(ResultSetMetaData rsm, int columnIndex)
+ throws SQLException {
+ String dbTypeName = rsm.getColumnTypeName(columnIndex);
- // MD - this is slow - is there a better way?
- if (dbTypeName.equalsIgnoreCase("geometry")) // WKB is now the normal
way to store geometry in PostGIS [mmichaud 2007-05-13]
- {
- return WKB_GEOMETRY_MAPPER;
- }
+ // manages 2 cases: type retrieved from Database metadata (DataStore Panel)
+ // and from direct Adhoc query (type of the column resultset).
+ if ("bytea".equalsIgnoreCase(dbTypeName) ||
"geometry".equalsIgnoreCase(dbTypeName)) {
+ return WKB_GEOMETRY_MAPPER;
+ } else if (dbTypeName.equalsIgnoreCase("text")) {
+ // TODO: wrong: all text column will be treated as text here...
+ return WKT_GEOMETRY_MAPPER;
+ }
- if (dbTypeName.equalsIgnoreCase("bytea")) {
- return WKB_GEOMETRY_MAPPER;
- }
-
- // handle the standard types
- ValueConverter stdConverter = ValueConverterFactory.getConverter(rsm,
columnIndex);
- if (stdConverter != null) {
- return stdConverter;
- }
-
+ // handle the standard types
+ ValueConverter stdConverter = ValueConverterFactory.getConverter(rsm,
columnIndex);
+ if (stdConverter != null) {
+ return stdConverter;
+ }
// default - can always show it as a string!
- return ValueConverterFactory.STRING_MAPPER;
- }
-
+ return ValueConverterFactory.STRING_MAPPER;
+ }
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSConnection.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSConnection.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSConnection.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.spatialdatabases;
import com.vividsolutions.jump.I18N;
@@ -27,9 +22,16 @@
protected SpatialDatabasesDSMetadata dbMetadata;
protected Connection connection;
-
+
public SpatialDatabasesDSConnection(Connection conn) {
- JUMPWorkbench.getInstance().getFrame().log("creating a
SpatialDatabasesDSConnection id" + this.hashCode(), this.getClass());
+
+ try {
+ JUMPWorkbench.getInstance().getFrame().log("creating a
SpatialDatabasesDSConnection (class:" + this.getClass()
+ + " ) (driver: " + conn.getMetaData().getDriverName() + ") id"
+ + this.hashCode(), this.getClass());
+ } catch (SQLException ex) {
+ ex.printStackTrace();
+ }
connection = conn;
dbMetadata = new SpatialDatabasesDSMetadata(this);
}
@@ -74,24 +76,20 @@
* @throws SQLException
*/
public FeatureInputStream executeFilterQuery(FilterQuery query) throws
SQLException {
-
- SpatialReferenceSystemID srid =
dbMetadata.getSRID(query.getDatasetName(), query.getGeometryAttributeName());
- String[] colNames = dbMetadata.getColumnNames(query.getDatasetName());
-
- SpatialDatabasesSQLBuilder builder = this.getSqlBuilder(srid,
colNames);
- String queryString = builder.getSQL(query);
-
- // [mmichaud 2013-08-07] add a parameter for database primary key name
- return new SpatialDatabasesFeatureInputStream(connection, queryString,
query.getPrimaryKey());
+ throw new UnsupportedOperationException();
}
+ /**
+ * select gid, geom
+ * from departement
+ * where nom like 'A%'
+ * Executes an adhoc query (direct SQL query)
+ * @param query the query to execute
+ * @return a featureInputStream containing query's features
+ * @throws Exception if no geometric column is found in the query
+ */
public FeatureInputStream executeAdhocQuery(AdhocQuery query) throws
Exception {
- String queryString = query.getQuery();
- SpatialDatabasesFeatureInputStream ifs = new
SpatialDatabasesFeatureInputStream(connection, queryString,
query.getPrimaryKey());
- if (ifs.getFeatureSchema().getGeometryIndex() < 0) {
- throw new
Exception(I18N.get(this.getClass().getName()+".resultset-must-have-a-geometry-column"));
- }
- return ifs;
+ throw new UnsupportedOperationException();
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,8 +1,3 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.spatialdatabases;
import com.vividsolutions.jts.geom.Envelope;
@@ -98,6 +93,9 @@
}
public SpatialDatabasesDSMetadata(DataStoreConnection conn) {
+ JUMPWorkbench.getInstance().getFrame().log("creating a
SpatialDatabasesDSMetadata (class:" + this.getClass()
+ + " ) (con: " + conn.toString() + ") id"
+ + this.hashCode(), this.getClass());
this.conn = conn;
// TODO: use bind parameters to avoid SQL injection
this.datasetNameQuery = "";
@@ -279,7 +277,7 @@
* @return
*/
public List<GeometryColumn> getGeometryAttributes(String datasetName) {
- String sql = this.getGeoColumnsQuery(datasetName);
+ String sql = getGeoColumnsQuery(datasetName);
return getGeometryAttributes(sql, datasetName);
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDataStoreDriver.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDataStoreDriver.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDataStoreDriver.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -1,12 +1,11 @@
-/*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
package com.vividsolutions.jump.datastore.spatialdatabases;
import com.vividsolutions.jump.datastore.DataStoreConnection;
import com.vividsolutions.jump.datastore.DataStoreDriver;
+import com.vividsolutions.jump.datastore.mariadb.MariadbDSConnection;
+import com.vividsolutions.jump.datastore.oracle.OracleDSConnection;
+import com.vividsolutions.jump.datastore.postgis.PostgisDSConnection;
+import com.vividsolutions.jump.datastore.spatialite.SpatialiteDSConnection;
import com.vividsolutions.jump.parameter.ParameterList;
import com.vividsolutions.jump.parameter.ParameterListSchema;
import java.sql.Connection;
@@ -19,36 +18,43 @@
public class SpatialDatabasesDataStoreDriver
implements DataStoreDriver {
+ /** TODO: I18N */
public static final String PARAM_Server = "Server";
public static final String PARAM_Port = "Port";
public static final String PARAM_Instance = "Database";
public static final String PARAM_User = "User";
public static final String PARAM_Password = "Password";
+ // For Spatialite
+ public static final String PARAM_DB_File = "DB file";
protected String driverName = null;
protected String jdbcClass = null;
protected String urlPrefix = null;
- private static final String[] paramNames = new String[]{
- PARAM_Server,
- PARAM_Port,
- PARAM_Instance,
- PARAM_User,
- PARAM_Password
- };
- private static final Class[] paramClasses = new Class[]{
- String.class,
- Integer.class,
- String.class,
- String.class,
- String.class
- };
- private final ParameterListSchema schema = new
ParameterListSchema(paramNames, paramClasses);
+ protected String[] paramNames = null;
+ protected Class[] paramClasses = null;
+ protected ParameterListSchema schema = null;
public SpatialDatabasesDataStoreDriver() {
-// this.driverName = "";
-// this.jdbcClass = "";
-// this.urlPrefix = "";
+ // Nicolas Ribot:
+ //paramNames are no more static now they can be overloaded by child
classes @link SpatialiteDataStoreDriver for instance
+ paramNames = new String[]{
+ PARAM_Server,
+ PARAM_Port,
+ PARAM_Instance,
+ PARAM_User,
+ PARAM_Password
+ };
+ // Nicolas Ribot: passed protected and not final to allow
spatialiteDataStoreDriver to overload it
+ paramClasses = new Class[]{
+ String.class,
+ Integer.class,
+ String.class,
+ String.class,
+ String.class
+ };
+ // Nicolas Ribot: passed protected and not final to allow
spatialiteDataStoreDriver to overload it
+ schema = new ParameterListSchema(paramNames, paramClasses);
}
public String getDriverName() {
@@ -62,9 +68,19 @@
public String getUrlPrefix() {
return urlPrefix;
}
-
-
+ public String[] getParamNames() {
+ return paramNames;
+ }
+
+ public Class[] getParamClasses() {
+ return paramClasses;
+ }
+
+ public ParameterListSchema getSchema() {
+ return schema;
+ }
+
@Override
public String getName() {
return driverName;
@@ -108,7 +124,21 @@
} else {
System.setProperty("java.net.preferIPv6Addresses",
savePreferIPv6Addresses);
}
- return new SpatialDatabasesDSConnection(conn);
+ //return new SpatialDatabasesDSConnection(conn);
+ // TODO: clean inheritance...
+ if (url.startsWith("jdbc:postgresql")) {
+ return new PostgisDSConnection(conn);
+ } else if (url.startsWith("jdbc:oracle")) {
+ return new OracleDSConnection(conn);
+ } else if (url.startsWith("jdbc:mysql")) {
+ return new MariadbDSConnection(conn);
+ } else if (url.startsWith("jdbc:sqlite")) {
+ return new SpatialiteDSConnection(conn);
+ } else {
+ // TODO: should not pass here
+ System.err.println("ERROR: Returning a SpatialDatabasesDSConnection for
url: " + url + ". Should not happen...");
+ return new SpatialDatabasesDSConnection(conn);
+ }
}
@Override
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesFeatureInputStream.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesFeatureInputStream.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesFeatureInputStream.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -25,20 +25,16 @@
private ResultSet rs = null;
private SpatialDatabasesResultSetConverter mapper;
- int geometryColIndex = -1;
String externalIdentifier = null; // added on 2013-08-07
public SpatialDatabasesFeatureInputStream(Connection conn, String
queryString) {
- this.conn = conn;
- this.queryString = queryString;
- //System.out.println("1building new
SpatialDatabasesFeatureInputStream: " + this.hashCode());
+ this(conn, queryString, null);
}
public SpatialDatabasesFeatureInputStream(Connection conn, String
queryString, String externalIdentifier) {
this.conn = conn;
this.queryString = queryString;
this.externalIdentifier = externalIdentifier;
- //System.out.println("2building new
SpatialDatabasesFeatureInputStream: " + this.hashCode());
}
/**
@@ -76,11 +72,11 @@
stmt = conn.createStatement();
String parsedQuery = queryString;
try {
- rs = stmt.executeQuery(parsedQuery);
+ rs = stmt.executeQuery(parsedQuery);
} catch (SQLException e) {
- SQLException sqle = new SQLException("Error : " + parsedQuery);
- sqle.setNextException(e);
- throw sqle;
+ // adds SQL query to SQLError
+ e.setNextException(new SQLException("Invalid query: " +
queryString));
+ throw e;
}
// mapper = new SpatialDatabasesResultSetConverter(conn, rs);
mapper = getResultSetConverter(rs);
@@ -123,7 +119,7 @@
String message = ex.getLocalizedMessage();
Throwable nextT = ex.getNextException();
if (nextT != null) message = message + "\n" +
nextT.getLocalizedMessage();
- throw new Error(message);
+ throw new Error(message, ex);
}
if (featureSchema == null) {
featureSchema = new FeatureSchema();
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesResultSetConverter.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesResultSetConverter.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesResultSetConverter.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -27,8 +27,17 @@
protected SpatialDatabasesValueConverterFactory odm;
protected boolean isInitialized = false;
+ /**
+ * empty ctor
+ * @param conn
+ * @param rs
+ */
+ public SpatialDatabasesResultSetConverter() {
+ }
+
public SpatialDatabasesResultSetConverter(Connection conn, ResultSet rs) {
this.rs = rs;
+ // try to instantiate the right object type.
odm = new SpatialDatabasesValueConverterFactory(conn);
}
@@ -57,9 +66,6 @@
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
- //String[] columnNames = new String[numberOfColumns];
- //String[] columnTypeNames = new String[numberOfColumns];
- //int[] columnPositions = new int[numberOfColumns];
mapper = new ValueConverter[numberOfColumns];
featureSchema = new FeatureSchema();
@@ -70,6 +76,8 @@
// Convert the first geometry into AttributeType.GEOMETRY and the
following ones
// into AttributeType.STRINGs [mmichaud 2007-05-13]
if (mapper[i].getType() == AttributeType.GEOMETRY) {
+ // Nicolas Ribot: stores geomCol index as it is needed by Adhoc query
+ //geometryColIndex = i+1;
if (featureSchema.getGeometryIndex() == -1) {
// fixed by mmichaud using a patch from jaakko [2008-05-21] :
// use colName instead of "GEOMETRY" for attribute name
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesValueConverterFactory.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesValueConverterFactory.java
2015-12-13 14:24:16 UTC (rev 4608)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesValueConverterFactory.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -7,6 +7,7 @@
import java.sql.*;
import com.vividsolutions.jump.datastore.jdbc.*;
import com.vividsolutions.jump.feature.AttributeType;
+import com.vividsolutions.jump.workbench.JUMPWorkbench;
import java.io.IOException;
/**
@@ -24,12 +25,27 @@
protected final Connection conn;
public SpatialDatabasesValueConverterFactory(Connection conn) {
+ try {
+ JUMPWorkbench.getInstance().getFrame().log("creating a
SpatialDatabasesValueConverterFactory (class:" + this.getClass()
+ + " ) (driver: " + conn.getMetaData().getDriverName() + ") id"
+ + this.hashCode(), this.getClass());
+ } catch (SQLException ex) {
+ ex.printStackTrace();
+ }
this.conn = conn;
}
+ /**
+ * Base class to get converter from factory.
+ * Should never be called !!
+ * @param rsm
+ * @param columnIndex
+ * @return
+ * @throws SQLException
+ */
public ValueConverter getConverter(ResultSetMetaData rsm, int columnIndex)
throws SQLException {
- return null;
+ throw new UnsupportedOperationException();
}
class WKTGeometryValueConverter implements ValueConverter {
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometricColumnType.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometricColumnType.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometricColumnType.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,11 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+/**
+ * DB types for geometric columns
+ * Used by spatialite, for other datastore, default to native type.
+ * @author nicolas
+ */
+public enum GeometricColumnType {
+ WKT, WKB, SPATIALITE, NATIVE
+ // All OGC types can be set as column type
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometryColumnsLayout.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometryColumnsLayout.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/GeometryColumnsLayout.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,10 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+/**
+ * An Enum representing Spatialite Geometry_column layout:
+ * either a FDO layout, an OGR OGC layout, and OGR Spatialite layout, or no
layout (no geometry_column found)
+ * @author nicolas
+ */
+public enum GeometryColumnsLayout {
+ FDO_LAYOUT, OGC_OGR_LAYOUT,OGC_SPATIALITE_LAYOUT, NO_LAYOUT;
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSConnection.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSConnection.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSConnection.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,102 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import com.vividsolutions.jump.I18N;
+import com.vividsolutions.jump.datastore.AdhocQuery;
+import com.vividsolutions.jump.datastore.FilterQuery;
+import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+import com.vividsolutions.jump.feature.FeatureSchema;
+import com.vividsolutions.jump.io.FeatureInputStream;
+import java.sql.Connection;
+import java.sql.SQLException;
+
+/**
+ *
+ * @author nicolas ribot
+ * TODO: Manage converter to handle all geometry type in the same database.
+ */
+public class SpatialiteDSConnection extends SpatialDatabasesDSConnection {
+
+ public SpatialiteDSConnection(Connection con) {
+ super(con); // ?
+ connection = con;
+ this.dbMetadata = new SpatialiteDSMetadata(this);
+ }
+
+ /**
+ * Keeps a reference on SpatialiteDSMetadata into the SQL builder, to
access
+ * Spatialite preferences necessary in order to build proper queries
according to
+ * geometric type
+ * @param srid
+ * @param colNames
+ * @return
+ */
+ @Override
+ public SpatialDatabasesSQLBuilder getSqlBuilder(SpatialReferenceSystemID
srid, String[] colNames) {
+ SpatialiteSQLBuilder ret = new
SpatialiteSQLBuilder((SpatialiteDSMetadata)this.dbMetadata, srid, colNames);
+ return ret;
+ }
+
+ /**
+ * Executes a filter query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeFilterQuery(FilterQuery query) throws
SQLException {
+ SpatialReferenceSystemID srid =
dbMetadata.getSRID(query.getDatasetName(), query.getGeometryAttributeName());
+ String[] colNames = dbMetadata.getColumnNames(query.getDatasetName());
+
+ SpatialiteSQLBuilder builder =
(SpatialiteSQLBuilder)this.getSqlBuilder(srid, colNames);
+ String queryString = builder.getSQL(query);
+
+ // [mmichaud 2013-08-07] add a parameter for database primary key name
+ SpatialiteFeatureInputStream fis = new
SpatialiteFeatureInputStream(connection, queryString, query.getPrimaryKey());
+ // Needed to choose converter according to real geometry type
+ fis.setMetadata((SpatialiteDSMetadata)dbMetadata);
+ return fis;
+ }
+
+ /**
+ * Executes an adhoc query.
+ *
+ * The SRID is optional for queries - it will be determined automatically
+ * from the table metadata if not supplied.
+ *
+ * @param query the query to execute
+ * @return the results of the query
+ * @throws SQLException
+ */
+ @Override
+ public FeatureInputStream executeAdhocQuery(AdhocQuery query) throws
Exception {
+ String queryString = query.getQuery();
+ SpatialiteFeatureInputStream ifs = new
SpatialiteFeatureInputStream(connection, queryString, query.getPrimaryKey());
+ // Needed to choose converter according to real geometry type
+ ifs.setMetadata((SpatialiteDSMetadata)dbMetadata);
+
+ // Nicolas Ribot: getting FeatureSchema here actually runs the query:
if an error occurs, must trap it here
+ FeatureSchema fs = null;
+ try {
+ fs = ifs.getFeatureSchema();
+ } catch (Exception e) {
+ throw new Exception(
+
I18N.get(com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".SQL-error") + e.getMessage());
+ }
+
+ if (fs.getGeometryIndex() < 0) {
+ throw new Exception(I18N.get(
+
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection.class.getName()
+ +".resultset-must-have-a-geometry-column"));
+ }
+ return ifs;
+
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,287 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import com.vividsolutions.jump.datastore.DataStoreConnection;
+import com.vividsolutions.jump.datastore.spatialdatabases.*;
+import com.vividsolutions.jump.datastore.jdbc.JDBCUtil;
+import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock;
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashMap;
+import java.util.Map;
+
+/**
+ * Spatialite connexion metadata. Some extra processing occurs here: telling if
+ * spatialite extension is loaded and what type of geo metatada are present:
+ *
+ * @author nicolas Ribot
+ */
+public class SpatialiteDSMetadata extends SpatialDatabasesDSMetadata {
+
+ public static String GC_COLUMN_NAME = "geometry_columns";
+
+ //TODO= variables for all SQL code + String.format.
+ /**
+ * True if spatialite mod extension loaded
+ */
+ private boolean spatialiteLoaded;
+ /**
+ * spatialite version
+ */
+ private String spatialiteVersion;
+ /**
+ * The geometry_columns table layout for this connection
+ */
+ private GeometryColumnsLayout geometryColumnsLayout;
+
+ /**
+ * The map of geometric columns types (WKB, WKT, SPATIALITE)
+ */
+ private Map<String, GeometricColumnType> geoColTypesdMap = null;
+
+ /**
+ *
+ * @param con
+ */
+ public SpatialiteDSMetadata(DataStoreConnection con) {
+ conn = con;
+ this.spatialiteLoaded = false;
+ this.spatialiteVersion = "";
+ this.geometryColumnsLayout = GeometryColumnsLayout.NO_LAYOUT;
+ this.geoColTypesdMap = new HashMap<String, GeometricColumnType>();
+
+ checkSpatialiteLoaded();
+ setGeoColLayout();
+ // done here as every connection needs it
+ getGeoColumnType();
+
+ // TODO: use bind parameters to avoid SQL injection
+ datasetNameQuery = "SELECT DISTINCT '' as f_table_schema, f_table_name
FROM geometry_columns";
+ defaultSchemaName = "";
+ spatialDbName = isSpatialiteLoaded() ? "Spatialite" : "SQLite";
+ spatialExtentQuery1 = "SELECT %s from %s";
+ spatialExtentQuery2 = "SELECT %s from %s";
+ sridQuery = "SELECT srid FROM geometry_columns where f_table_name = '%s'
and f_geometry_column = '%s'";
+ // geo column query needs to be built occording to geometryColumnsLayout
+ if (this.geometryColumnsLayout == GeometryColumnsLayout.FDO_LAYOUT) {
+ geoColumnsQuery = "SELECT f_geometry_column, srid, geometry_type FROM
geometry_columns where f_table_name = '%s'";
+ } else if (this.geometryColumnsLayout ==
GeometryColumnsLayout.OGC_SPATIALITE_LAYOUT) {
+ geoColumnsQuery = "SELECT f_geometry_column, srid, type FROM
geometry_columns where f_table_name = '%s'";
+ } else if (this.geometryColumnsLayout ==
GeometryColumnsLayout.OGC_OGR_LAYOUT) {
+ geoColumnsQuery = "SELECT f_geometry_column, srid, geometry_type FROM
geometry_columns where f_table_name = '%s'";
+ } else {
+ geoColumnsQuery = "SELECT '' ";
+ }
+
+ }
+
+ @Override
+ public String getSpatialExtentQuery1(String schema, String table, String
attributeName) {
+ // No schema in SQLite, schema param not used
+ // must cast the geometric field according to its type, to be able to use
spatialite functions.
+ //return String.format(spatialExtentQuery1, attributeName, table);
+ String ret = "select 1";
+
+
+ GeometricColumnType gcType = this.geoColTypesdMap.get(table.toLowerCase()
+ "." + attributeName.toLowerCase());
+
+ if (gcType == null) {
+ return "select 1";
+ }
+ // TODO: switch case
+ if (this.isSpatialiteLoaded()) {
+ if (gcType == GeometricColumnType.WKB) {
+ ret = String.format("select st_asBinary(extent(st_geomFromWkb(%s)))
from %s", attributeName, table);
+ } else if (gcType == GeometricColumnType.WKT) {
+ ret = String.format("select st_asBinary(extent(st_geomFromText(%s)))
from %s", attributeName, table);
+ } else if (gcType == GeometricColumnType.SPATIALITE) {
+ ret = String.format("select st_asBinary(extent(%s)) from %s",
attributeName, table);
+ } else {
+ // unknown geom type
+ // TODO: log
+ System.out.println("Unknown geo column type for: " + table + "." +
attributeName + " : " + gcType);
+ ret = "select 1";
+ }
+ } else {
+ // spatialite functions not available: extent cannot be found
+ ret = "select 1";
+ }
+ return ret;
+ }
+
+ @Override
+ public String getSpatialExtentQuery2(String schema, String table, String
attributeName) {
+ // only one mechanism to get extent from spatialite
+ return getSpatialExtentQuery1(schema, table, attributeName);
+ }
+
+ /**
+ * No schema in SQLite
+ *
+ * @param datasetName
+ * @return
+ */
+ @Override
+ public String getGeoColumnsQuery(String datasetName) {
+ // No schema in SQLite
+ return String.format(this.geoColumnsQuery, getTableName(datasetName));
+ }
+
+ @Override
+ public String getSridQuery(String schemaName, String tableName, String
colName) {
+ // no schema in sqlite
+ return String.format(this.sridQuery, tableName, colName);
+ }
+
+ private void checkSpatialiteLoaded() {
+ // tries to load spatialite, assuming it is available on the system's path
+ Statement stmt = null;
+ try {
+ stmt = conn.getConnection().createStatement();
+ stmt.executeUpdate("SELECT load_extension('mod_spatialite')");
+ // ex is thrown if extension cannot be loaded
+ this.spatialiteLoaded = true;
+ ResultSet rs = stmt.executeQuery("select spatialite_version()");
+ rs.next();
+ this.setSpatialiteVersion(rs.getString(1));
+ //TODO: log
+ System.out.println("SpatialDatabasesPlugin: Spatialite extension loaded
for this connexion, version: " + this.getSpatialiteVersion());
+ } catch (Exception e) {
+ System.out.println("SpatialDatabasesPlugin: Cannot load Spatialite
Extention (mod_spatialite), reason:" + e.getMessage());
+ } finally {
+ try {
+ stmt.close();
+ } catch (Throwable th) {
+ // TODO: log
+ th.printStackTrace();
+ }
+ }
+ }
+
+ /**
+ * Sets the geometry_column layout in this sqlite database: either FDO or
+ * OGC or no layout. Also tries to build the geo col type if geometry_columns
+ * table contains such info TODO: generic mechanism to get geo col type for
+ * Spatialite
+ */
+ private void setGeoColLayout() {
+ DatabaseMetaData dbMd = null;
+ try {
+ dbMd = this.conn.getConnection().getMetaData();
+ ResultSet rs = dbMd.getTables(null, null,
SpatialiteDSMetadata.GC_COLUMN_NAME, null);
+ if (rs.next()) {
+ // tableName is third column in this metadata resultSet
+ String col = rs.getString(3);
+ boolean isGC =
SpatialiteDSMetadata.GC_COLUMN_NAME.equalsIgnoreCase(col);
+
+ // gc layout
+ if (isGC) {
+ rs = dbMd.getColumns(null, null,
SpatialiteDSMetadata.GC_COLUMN_NAME, null);
+ int i = 0;
+ // geometry_columns table may have 2 layouts according to ogr2ogr
+ // options used to create the table:
+ // 1\xB0) the "FDO provider for spatialite
(https://trac.osgeo.org/fdo/wiki/FDORfc16)", as used in "regular sqlite
database" (cf.ogr spatialite format doc):
+ // f_table_name TEXT
+ // f_geometry_column TEXT
+ // geometry_type INTEGER
+ // coord_dimension INTEGER
+ // srid INTEGER
+ // geometry_format TEXT
+ // 2\xB0) the "OGC" flavour, as understood by qgis for instance, as
used in spatialite-enabled sqlite database:
+ // f_table_name VARCHAR
+ // f_geometry_column VARCHAR
+ // type VARCHAR
+ // coord_dimension INTEGER
+ // srid INTEGER
+ // spatial_index_enabled INTEGER
+ i = 0;
+ String geoTypeCol = "";
+ String extraInfoCol = "";
+ while (rs.next()) {
+ // assume columns order is respected when gc table is created.
+ // TODO: enhance this
+ if (i == 2) {
+ geoTypeCol = rs.getString(4);
+ }
+ if (i == 5) {
+ extraInfoCol = rs.getString(4);
+ }
+ i++;
+ }
+ if (geoTypeCol.equalsIgnoreCase("geometry_type") &&
extraInfoCol.equalsIgnoreCase("geometry_format")) {
+ geometryColumnsLayout = GeometryColumnsLayout.FDO_LAYOUT;
+ } else if (geoTypeCol.equalsIgnoreCase("type") &&
extraInfoCol.equalsIgnoreCase("spatial_index_enabled")) {
+ geometryColumnsLayout =
GeometryColumnsLayout.OGC_SPATIALITE_LAYOUT;
+ } else if (geoTypeCol.equalsIgnoreCase("geometry_type") &&
extraInfoCol.equalsIgnoreCase("spatial_index_enabled")) {
+ geometryColumnsLayout = GeometryColumnsLayout.OGC_OGR_LAYOUT;
+ } else {
+ geometryColumnsLayout = GeometryColumnsLayout.NO_LAYOUT;
+ };
+ rs.close();
+ }
+ }
+
+ } catch (Exception e) {
+ e.printStackTrace();
+ //TODO: logging
+ System.out.println("error getting geometry_column layout: " +
e.getMessage());
+ }
+ }
+
+ /**
+ * builds the map of geometric columns database type: WKB, WKT, SPATIALITE to
+ * be able to build custom queries for extent and geo type retrieval.
+ * The geometry_format column of the metadata will be queries to find
geometry type
+ * (column only detected in the FDO_LAYOUT format).
+ * For other layout, will default to SPATIALITE type
+ */
+ private void getGeoColumnType() {
+ // Default query gets a hard-coded value for spatialite type
+ String query = "select f_table_name, f_geometry_column, \"SPATIALITE\"
from geometry_columns";
+ if (this.getGeometryColumnsLayout() == GeometryColumnsLayout.FDO_LAYOUT) {
+ // MD table contains a geometry_format column: query it
+ query = "select f_table_name, f_geometry_column, geometry_format from
geometry_columns";
+ }
+ try {
+ JDBCUtil.execute(
+ conn.getConnection(),
+ query,
+ new ResultSetBlock() {
+ public void yield(ResultSet resultSet) throws SQLException {
+ while (resultSet.next()) {
+ // force lowercase as JDBC metadata and OGC spatialite
metadata can return
+ // different cases for the same geometric column
+ String table = resultSet.getString(1).toLowerCase();
+ String col = resultSet.getString(2).toLowerCase();
+ GeometricColumnType gcType =
GeometricColumnType.valueOf(resultSet.getString(3));
+ geoColTypesdMap.put(table + "." + col, gcType);
+ }
+ }
+ });
+ } catch (Exception e) {
+ //TODO...
+ }
+ }
+
+ public boolean isSpatialiteLoaded() {
+ return spatialiteLoaded;
+ }
+
+ public String getSpatialiteVersion() {
+ return spatialiteVersion;
+ }
+
+ public void setSpatialiteVersion(String spatialiteVersion) {
+ this.spatialiteVersion = spatialiteVersion;
+ }
+
+ public GeometryColumnsLayout getGeometryColumnsLayout() {
+ return geometryColumnsLayout;
+ }
+
+ public Map<String, GeometricColumnType> getGeoColTypesdMap() {
+ return geoColTypesdMap;
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreDriver.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreDriver.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreDriver.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,72 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import com.vividsolutions.jump.datastore.DataStoreConnection;
+import com.vividsolutions.jump.parameter.ParameterList;
+import java.sql.Connection;
+import java.sql.Driver;
+import java.sql.DriverManager;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDataStoreDriver;
+import com.vividsolutions.jump.parameter.ParameterListSchema;
+import java.io.File;
+import org.sqlite.SQLiteConfig;
+
+/**
+ * A driver for supplying {@link SpatialDatabaseDSConnection}s
+ */
+public class SpatialiteDataStoreDriver
+ extends SpatialDatabasesDataStoreDriver {
+
+ public final static String JDBC_CLASS = "org.sqlite.JDBC";
+
+ public SpatialiteDataStoreDriver() {
+ this.driverName = "Spatialite";
+ this.jdbcClass = "org.sqlite.JDBC";
+ //TODO: prompt for filename
+ this.urlPrefix = "jdbc:sqlite:";
+
+ // Panel parameters: adds a file chooser for db name
+ paramNames = new String[]{PARAM_DB_File};
+ paramClasses = new Class[]{File.class};
+ schema = new ParameterListSchema(paramNames, paramClasses);
+ }
+
+ /**
+ * returns a spatialite JDBC connexion with spatialite extension loaded if
possible
+ * if not, a property will tell so
+ *
+ * @param params
+ * @return
+ * @throws Exception
+ */
+ @Override
+ public DataStoreConnection createConnection(ParameterList params)
+ throws Exception {
+ // PARAM_DB_File is a fileChooser: the default mechanism used will store a
+ // DefaultAwtShell on OSX. Do not cast to this internal type but gets its
toString() method
+ // returning the choosen filename.
+ String database = params.getParameter(PARAM_DB_File).toString();
+
+ // First checks if the file exists: cannot manage newly created Spatialite
files yet:
+ // File must exists
+ File sqliteFile = new File(database);
+ if (!sqliteFile.exists() || !sqliteFile.canRead()) {
+ // TODO: I18N
+ throw new Exception("Spatialite file: " + database + " does not exist.
cannot create connection");
+ }
+
+
+ SQLiteConfig config = new SQLiteConfig();
+ // mandatory to load spatialite extension
+ config.enableLoadExtension(true);
+
+ String url
+ = String.valueOf(new StringBuffer(urlPrefix).append(database));
+
+ Driver driver = (Driver) Class.forName(this.getJdbcClass()).newInstance();
+ DriverManager.registerDriver(driver);
+
+ Connection conn = DriverManager.getConnection(url, config.toProperties());
+
+ return new SpatialiteDSConnection(conn);
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreExtension.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreExtension.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDataStoreExtension.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,56 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import com.vividsolutions.jump.datastore.DataStoreDriver;
+import static
com.vividsolutions.jump.datastore.spatialite.SpatialiteDataStoreDriver.JDBC_CLASS;
+
+import com.vividsolutions.jump.workbench.WorkbenchContext;
+import com.vividsolutions.jump.workbench.plugin.Extension;
+import com.vividsolutions.jump.workbench.plugin.PlugInContext;
+import java.sql.Driver;
+import java.sql.DriverManager;
+
+/**
+ *
+ * @author nicolas ribot
+ */
+public class SpatialiteDataStoreExtension extends Extension {
+ private static boolean disabled = false;
+
+ public String getName() {
+ return "Spatialite Spatial Datastore Extension";
+ }
+
+ public String getVersion() {
+ return "0.3 (2015-12-04)";
+ }
+
+ public String getMessage() {
+ return disabled ? "Disabled: Missing sqlite-jdbc-<version>.jar in
classpath"
+ : "";
+ }
+ // TODO: refactor in a base class.
+ public void configure(PlugInContext context) throws Exception {
+ WorkbenchContext wbc = context.getWorkbenchContext();
+
+ // registers the MariaDBDataStore driver to the system:
+ try {
+ ClassLoader pluginLoader = wbc.getWorkbench().getPlugInManager()
+ .getClassLoader();
+ // check for ojdbc6.jar
+ DriverManager.registerDriver(
+ (Driver)Class.forName(JDBC_CLASS, true, pluginLoader).newInstance());
+
+ // register the datastore
+ wbc.getRegistry().createEntry(DataStoreDriver.REGISTRY_CLASSIFICATION,
+ new SpatialiteDataStoreDriver());
+ } catch (Exception e) {
+ disabled = true;
+ wbc.getWorkbench()
+ .getFrame()
+ .log(
+ "Sqlite/Spatialite Spatial Data Store disabled:\n\t" +
e.toString()
+ + "\n\tSqlite JDBC Driver (sqlite-jdbc-<version>.jar) must exist
in the classpath !", this.getClass());
+ }
+ }
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteFeatureInputStream.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteFeatureInputStream.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteFeatureInputStream.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,45 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesFeatureInputStream;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
+import java.sql.Connection;
+import java.sql.ResultSet;
+
+/**
+ *
+ * @author nicolas
+ */
+public class SpatialiteFeatureInputStream extends
SpatialDatabasesFeatureInputStream {
+
+ /**
+ * propagate the metadata object through Spatialite classes to get access
to
+ * specific information
+ */
+ private SpatialiteDSMetadata metadata;
+
+ public SpatialiteFeatureInputStream(Connection conn, String queryString) {
+ super(conn, queryString);
+ }
+
+ public SpatialiteFeatureInputStream(Connection conn, String queryString,
String externalIdentifier) {
+ super(conn, queryString, externalIdentifier);
+ }
+
+ public void setMetadata(SpatialiteDSMetadata metadata) {
+ this.metadata = metadata;
+ }
+
+ /**
+ * Returns a SpatialiteResultSetConverter
+ *
+ * @param rs
+ * @return
+ */
+ @Override
+ protected SpatialDatabasesResultSetConverter
getResultSetConverter(ResultSet rs) {
+ SpatialiteResultSetConverter ret = new
SpatialiteResultSetConverter(conn, rs);
+ ret.setMetadata(this.metadata);
+
+ return ret;
+ }
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteResultSetConverter.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteResultSetConverter.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteResultSetConverter.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,37 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesResultSetConverter;
+import com.vividsolutions.jump.feature.Feature;
+import com.vividsolutions.jump.feature.FeatureSchema;
+import java.sql.Connection;
+import java.sql.ResultSet;
+
+/**
+ * Implements the mapping between a result set and a {@link FeatureSchema} and
+ * {@link Feature} set.
+ *
+ * This is a transient worker class, whose lifetime should be no longer than
the
+ * lifetime of the provided ResultSet
+ */
+public class SpatialiteResultSetConverter extends
SpatialDatabasesResultSetConverter {
+ /**
+ * propagate the metadata object through Spatialite classes to get access
to
+ * specific information
+ */
+ private SpatialiteDSMetadata metadata;
+
+ public SpatialiteResultSetConverter(Connection conn, ResultSet rs) {
+ super(conn, rs);
+ this.odm = new SpatialiteValueConverterFactory(conn);
+ }
+
+ public void setMetadata(SpatialiteDSMetadata metadata) {
+ this.metadata = metadata;
+ //hack: todo: clean inheritance ?
+ if (this.odm != null) {
+
((SpatialiteValueConverterFactory)this.odm).setMetadata(this.metadata);
+ }
+ }
+
+
+}
Added:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
(rev 0)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java
2015-12-13 15:02:36 UTC (rev 4609)
@@ -0,0 +1,113 @@
+package com.vividsolutions.jump.datastore.spatialite;
+
+import com.vividsolutions.jts.geom.Envelope;
+import com.vividsolutions.jump.datastore.FilterQuery;
+import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
+import com.vividsolutions.jump.workbench.JUMPWorkbench;
+
+/**
+ * Creates SQL query strings for a Spatial database. To be overloaded by
classes
+ * implementing a spatial database support.
+ */
+public class SpatialiteSQLBuilder extends SpatialDatabasesSQLBuilder {
+
+ private String datasetName;
+
+ public SpatialiteSQLBuilder(SpatialiteDSMetadata dsMetadata,
SpatialReferenceSystemID defaultSRID, String[] colNames) {
+ super(dsMetadata, defaultSRID, colNames);
+ }
+
+ /**
+ * Builds a valid SQL spatial query with the given spatial filter.
+ *
+ * @param query
+ * @return a SQL query to get column names
+ */
+ @Override
+ public String getSQL(FilterQuery query) {
+ this.datasetName = query.getDatasetName();
+ StringBuilder qs = new StringBuilder();
+ //HACK
+ String ret = "SELECT %s FROM %s WHERE %s AND (%s) %s";
+ String cols = getColumnListSpecifier(colNames,
query.getGeometryAttributeName());
+ String bbox = buildBoxFilter(query);
+ 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);
+ JUMPWorkbench.getInstance().getFrame().log(
+ "SQL query to get Spatial table features:\n\t"
+ + s, this.getClass());
+
+ return s;
+ }
+
+ /**
+ * Returns the string representing a SQL column definition. Implementors
+ * should take care of column names (case, quotes)
+ *
+ * @param colNames
+ * @param geomColName
+ * @return column list
+ */
+ @Override
+ protected String getColumnListSpecifier(String[] colNames, String
geomColName) {
+ // Added double quotes around each column name in order to read mixed case
table names
+ // correctly [mmichaud 2007-05-13]
+ StringBuilder buf = new StringBuilder();
+ SpatialiteDSMetadata dsm = (SpatialiteDSMetadata)getDbMetadata();
+ GeometricColumnType gcType =
dsm.getGeoColTypesdMap().get(this.datasetName.toLowerCase() + "." +
geomColName.toLowerCase());
+ String s = null;
+ switch (gcType) {
+ case SPATIALITE:
+ s = geomColName;
+ break;
+ case WKB:
+ s = String.format("st_geomFromWkb(%s) as %s", geomColName,
geomColName);
+ break;
+ case WKT:
+ s = String.format("st_geomFromText(%s) as %s", geomColName,
geomColName);
+ break;
+ }
+ // TODO: use previous code or remove it after check:
+ // geo col should be returned in its default type and handled by the
converter
+ // according to the geo column type (avoid conversion overhead)
+
+ buf.append("").append(geomColName);
+ for (String colName : colNames) {
+ if (!geomColName.equalsIgnoreCase(colName)) {
+ buf.append(",").append(colName);
+ }
+ }
+ return buf.toString();
+ }
+
+ @Override
+ protected String buildBoxFilter(FilterQuery query) {
+ Envelope env = query.getFilterGeometry().getEnvelopeInternal();
+ String ret = "1";
+ // Example of Spatialite SQL:
+ // select nom_comm from commune where st_envIntersects(wkt_geometry,
bbox(516707,6279239,600721,6347851)
+ SpatialiteDSMetadata dsm = (SpatialiteDSMetadata)getDbMetadata();
+ if (dsm.isSpatialiteLoaded()) {
+ GeometricColumnType gcType = dsm.getGeoColTypesdMap().get(
+ query.getDatasetName().toLowerCase() + "." +
query.getGeometryAttributeName().toLowerCase());
+ if (gcType == GeometricColumnType.SPATIALITE) {
+ ret = String.format("st_envIntersects(%s, %f,%f,%f,%f)",
query.getGeometryAttributeName(), env.getMinX(),
+ env.getMinY(), env.getMaxX(), env.getMaxY());
+ } else if (gcType == GeometricColumnType.WKB) {
+ ret = String.format("st_envIntersects(st_geomFromWkb(%s),
%f,%f,%f,%f)", query.getGeometryAttributeName(), env.getMinX(),
+ env.getMinY(), env.getMaxX(), env.getMaxY());
+ } else if (gcType == GeometricColumnType.WKT) {
@@ Diff output truncated at 100000 characters. @@
------------------------------------------------------------------------------
_______________________________________________
Jump-pilot-devel mailing list
Jump-pilot-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
