Revision: 5976
http://sourceforge.net/p/jump-pilot/code/5976
Author: elnico
Date: 2018-10-12 09:19:25 +0000 (Fri, 12 Oct 2018)
Log Message:
-----------
New mechanism for SpatialDatabasesDSMetadata to get information about spatial
tables: done in one query, to reduce the number of queries sent to the backend
(took several minutes on big DB).
Retrieving geo tables now takes < 1s for big DB (250+ schemas, 3000+ tables)
Modified Paths:
--------------
core/trunk/ChangeLog
core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/AddDatastoreLayerPanel.java
Modified: core/trunk/ChangeLog
===================================================================
--- core/trunk/ChangeLog 2018-10-10 11:04:39 UTC (rev 5975)
+++ core/trunk/ChangeLog 2018-10-12 09:19:25 UTC (rev 5976)
@@ -3,6 +3,11 @@
# 2. make sure that lines break at 80 chars for constricted display situations
#<-------------------------------- 80 chars
---------------------------------->#
+2018-10-12 Nicolas Ribot
+ * New mechanism for SpatialDatabasesDSMetadata to get information about
+ spatial tables: done in one query, to reduce the number of queries
+ sent to the backend (took several minutes on big DB)
+
2018-10-08
* Small fix in Topology extension (Adjust Polygon Boundaries)
@@ -230,9 +235,6 @@
- is now cancelable via TaskMonitor button
- reports feature count written so far now
-2018-01-03 Nicolas Ribot <[email protected]>
- * Icons for layers according to geometry type in the Data Store Layer panel
-
2017-12-26 ede
* ChangeSRIDPlugin
- setting SRID tags the feature collection to modified now
Modified: core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java
===================================================================
--- core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++ core/trunk/src/com/vividsolutions/jump/datastore/h2/H2DSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -23,6 +23,7 @@
geoColumnsQuery = "SELECT f_geometry_column, coord_dimension, srid,
type FROM geometry_columns where f_table_schema = '%s' and f_table_name = '%s'";
sridQuery = "SELECT srid FROM geometry_columns where f_table_schema =
'%s' and f_table_name = '%s' and f_geometry_column = '%s'";
coordDimQuery = "SELECT coord_dimension FROM geometry_columns where
f_table_schema = '%s' and f_table_name = '%s' and f_geometry_column = '%s'";
+ datasetInfoQuery = "SELECT f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, type FROM geometry_columns";
}
@Override
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/datastore/mariadb/MariadbDSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -72,7 +72,6 @@
// query according to detected layout:
geoColumnsQuery = "SELECT f_geometry_column, coord_dimension, srid, type
FROM geometry_columns where f_table_name = '%s'";
- // TODO: not the same number of param to replace...
if (geometryColumnsLayout == GeometryColumnsLayout.NO_LAYOUT) {
geoColumnsQuery = "select c.COLUMN_NAME, 2, 0, 'geometry' \n"
+ "from information_schema.TABLES t join information_schema.COLUMNS C
\n"
@@ -82,7 +81,6 @@
+ "and c.COLUMN_TYPE = 'geometry'";
}
- // TODO: test for big datasets...
// query according to detected layout:
sridQuery = "SELECT srid FROM geometry_columns where f_table_name = '%s'
and f_geometry_column = '%s'";
if (geometryColumnsLayout == GeometryColumnsLayout.NO_LAYOUT) {
@@ -96,7 +94,18 @@
// quote identifiers
coordDimQuery = "select 2 as coord_dimension";
}
-
+
+ // query according to detected layout:
+ datasetInfoQuery = "SELECT '' as f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, type FROM geometry_columns";
+ if (geometryColumnsLayout == GeometryColumnsLayout.NO_LAYOUT) {
+ datasetInfoQuery = "select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME,
2, 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 c.COLUMN_TYPE = 'geometry'";
+ }
+
+
}
@Override
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/datastore/oracle/OracleDSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -6,11 +6,13 @@
package com.vividsolutions.jump.datastore.oracle;
import com.vividsolutions.jump.datastore.DataStoreConnection;
+import com.vividsolutions.jump.datastore.DataStoreLayer;
import com.vividsolutions.jump.datastore.SQLUtil;
import com.vividsolutions.jump.datastore.jdbc.JDBCUtil;
import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock;
import com.vividsolutions.jump.datastore.spatialdatabases.*;
import com.vividsolutions.jump.datastore.GeometryColumn;
+import com.vividsolutions.jump.workbench.JUMPWorkbench;
import java.sql.Array;
import java.sql.ResultSet;
@@ -63,6 +65,9 @@
coordDimQuery = "select t.diminfo from ALL_SDO_GEOM_METADATA t "
+ "where t.owner = '%s' and t.table_name = '%s'";
+
+ datasetInfoQuery = "select t.owner, t.table_name, t.column_name,
t.diminfo, t.srid, 'SDO_GEOMETRY' as type from ALL_SDO_GEOM_METADATA t";
+
}
@Override
@@ -145,4 +150,52 @@
return Integer.parseInt(coordDim.toString());
}
+ /**
+ * overloaded from SpatialDatabasesDS to cope with special oracle columns
types
+ * @return
+ */
+ @Override
+ public String[] getDatasetNames() {
+ final List datasetNames = new ArrayList();
+ this.dataStoreLayers = new ArrayList<DataStoreLayer>();
+
+ // Spatial tables only.
+ try {
+ JDBCUtil.execute(
+ conn.getJdbcConnection(),
+ this.getDatasetInfoQuery(),
+ new ResultSetBlock() {
+ public void yield(ResultSet resultSet) throws
SQLException {
+ while (resultSet.next()) {
+ String schema = resultSet.getString(1);
+ String table = resultSet.getString(2);
+ if
(!schema.equalsIgnoreCase(OracleDSMetadata.this.getDefaultSchemaName())) {
+ table = schema + "." + table;
+ }
+ // checks if dataset already exists
+ if (! datasetNames.contains(table)) {
+ datasetNames.add(table);
+ }
+ // datastoreLayers
+ GeometryColumn geo = new GeometryColumn(
+ resultSet.getString(3),
+
((Object[])resultSet.getArray(4).getArray()).length,
+ resultSet.getInt(5),
+ resultSet.getString(6));
+ dataStoreLayers.add(new DataStoreLayer(table,
geo));
+ }
+ }
+ });
+ } catch (Exception e) {
+ // Nico Ribot: TODO: returns a custom Ex ?
+ if (this.missingGeoException(e)) {
+ // TODO: logger + I18N
+ JUMPWorkbench.getInstance().getFrame().log("not a " +
this.getSpatialDbName()
+ + " db or bad search_path", this.getClass());
+ } else {
+ e.printStackTrace();
+ }
+ }
+ return (String[]) datasetNames.toArray(new
String[datasetNames.size()]);
+ }
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/datastore/postgis/PostgisDSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -23,6 +23,8 @@
geoColumnsQuery = "SELECT f_geometry_column, coord_dimension, srid,
type FROM geometry_columns where f_table_schema='%s' and f_table_name = '%s'";
sridQuery = "SELECT srid FROM geometry_columns where f_table_schema =
'%s' and f_table_name = '%s' and f_geometry_column = '%s'";
coordDimQuery = "SELECT coord_dimension FROM geometry_columns where
f_table_schema = '%s' and f_table_name = '%s' and f_geometry_column = '%s'";
+ datasetInfoQuery = "SELECT f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, type FROM geometry_columns order by
1, 2";
+
}
@Override
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialdatabases/SpatialDatabasesDSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -101,6 +101,20 @@
*/
protected String coordDimQuery = null;
+ // Nico Ribot, 2018-08-07: adds a new mechanism to load datasets info with
geo columns and all OGC info
+ // with one query, instead of launching one query per dataset !
+ /**
+ * The SQL query to get all dataset OGC information
+ */
+ protected String datasetInfoQuery = null;
+
+ /**
+ * The list of dataStoreLayer for this ds metadata. Built when list of
dataset names
+ * is requested: avoids to call getGeometryAttributes for each dataset,
which takes too much time
+ * on big DB. Instead, all dataStoreLayer are built once and filtered out to
get dataset names
+ */
+ protected ArrayList<DataStoreLayer> dataStoreLayers = null;
+
public SpatialDatabasesDSMetadata() {
}
@@ -118,6 +132,8 @@
this.geoColumnsQuery = "";
// TODO
this.sridQuery = "";
+
+ this.dataStoreLayers = new ArrayList<DataStoreLayer>();
}
public String getDatasetNameQuery() {
@@ -155,6 +171,14 @@
return String.format(this.coordDimQuery, schemaName, tableName, colName);
}
+ public String getDatasetInfoQuery() {
+ return this.datasetInfoQuery;
+ }
+
+ public ArrayList<DataStoreLayer> getDataStoreLayers() {
+ return this.dataStoreLayers;
+ }
+
/**
* Returns the schema name based on the given tableName: string before . if
* exists, else returns schemaName
@@ -197,13 +221,52 @@
return (e instanceof SQLException &&
e.getMessage().contains("geometry_columns"));
}
+// public String[] getDatasetNames() {
+// final List datasetNames = new ArrayList();
+// // Spatial tables only.
+// try {
+// JDBCUtil.execute(
+// conn.getJdbcConnection(),
+// this.getDatasetNameQuery(),
+// new ResultSetBlock() {
+// public void yield(ResultSet resultSet) throws SQLException {
+// while (resultSet.next()) {
+// String schema = resultSet.getString(1);
+// String table = resultSet.getString(2);
+// if
(!schema.equalsIgnoreCase(SpatialDatabasesDSMetadata.this.getDefaultSchemaName()))
{
+// table = schema + "." + table;
+// }
+// datasetNames.add(table);
+// }
+// }
+// });
+// } catch (Exception e) {
+// // Nico Ribot: TODO: returns a custom Ex ?
+// if (this.missingGeoException(e)) {
+// // TODO: logger + I18N
+// JUMPWorkbench.getInstance().getFrame().log("not a " +
this.getSpatialDbName()
+// + " db or bad search_path", this.getClass());
+// } else {
+// e.printStackTrace();
+// }
+// }
+// return (String[]) datasetNames.toArray(new String[datasetNames.size()]);
+// }
+
+ /**
+ * Nico Ribot: 2018-08-07: new method using a query to get all information
for datasets
+ * in a structure, to avoid querying too much the server
+ * @return
+ */
public String[] getDatasetNames() {
final List datasetNames = new ArrayList();
+ this.dataStoreLayers = new ArrayList<DataStoreLayer>();
+
// Spatial tables only.
try {
JDBCUtil.execute(
conn.getJdbcConnection(),
- this.getDatasetNameQuery(),
+ this.getDatasetInfoQuery(),
new ResultSetBlock() {
public void yield(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
@@ -212,7 +275,17 @@
if
(!schema.equalsIgnoreCase(SpatialDatabasesDSMetadata.this.getDefaultSchemaName()))
{
table = schema + "." + table;
}
- datasetNames.add(table);
+ // checks if dataset already exists
+ if (! datasetNames.contains(table)) {
+ datasetNames.add(table);
+ }
+ // datastoreLayers
+ GeometryColumn geo = new GeometryColumn(
+ resultSet.getString(3),
+ resultSet.getInt(4),
+ resultSet.getInt(5),
+ resultSet.getString(6));
+ dataStoreLayers.add(new DataStoreLayer(table, geo));
}
}
});
@@ -220,7 +293,8 @@
// Nico Ribot: TODO: returns a custom Ex ?
if (this.missingGeoException(e)) {
// TODO: logger + I18N
- JUMPWorkbench.getInstance().getFrame().log("not a " +
this.getSpatialDbName() + "db or bad search_path", this.getClass());
+ JUMPWorkbench.getInstance().getFrame().log("not a " +
this.getSpatialDbName()
+ + " db or bad search_path", this.getClass());
} else {
e.printStackTrace();
}
Modified:
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -157,14 +157,31 @@
// TODO:
spatialIndexQuery = "";
}
-
- // TODO: remove in prod.
-// JUMPWorkbench.getInstance().getFrame().log(
-// "Spatialite MD:\n\t"
-// + "geo col layout: " + geometryColumnsLayout + "\n\t"
-// + "spatialite loaded: " + spatialiteLoaded + "\n\t"
-// + "version: " + spatialiteVersion,
-// this.getClass());
+
+ // geo column query needs to be built occording to geometryColumnsLayout
+ if (this.geometryColumnsLayout == GeometryColumnsLayout.FDO_LAYOUT
+ || this.geometryColumnsLayout ==
GeometryColumnsLayout.OGC_OGR_LAYOUT) {
+ datasetInfoQuery = "SELECT '' as f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid,\n"
+ + " case\n"
+ + " when geometry_type = 1 then 'POINT'\n"
+ + " when geometry_type = 2 then 'LINESTRING'\n"
+ + " when geometry_type = 3 then 'POLYGON'\n"
+ + " when geometry_type = 4 then 'MULTIPOINT'\n"
+ + " when geometry_type = 5 then 'MULTILINESTRING'\n"
+ + " when geometry_type = 6 then 'MULTIPOLYGON'\n"
+ + " when geometry_type = 7 then 'GEOMETRY COLLECTION'\n"
+ + " else geometry_type end as geometry_type\n"
+ + "FROM geometry_columns";
+ } else if (this.geometryColumnsLayout ==
GeometryColumnsLayout.OGC_SPATIALITE_LAYOUT) {
+ datasetInfoQuery = "SELECT '' as f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, type FROM geometry_columns";
+ } else if (this.geometryColumnsLayout ==
GeometryColumnsLayout.OGC_GEOPACKAGE_LAYOUT) {
+ datasetInfoQuery = "SELECT '' as table_schema, table_name, column_name,
" +
+ "case when z+m = 0 then 2 when z = 1 and m = 1 then 4 else 3 end
as coord_dimension, " +
+ "srs_id, geometry_type_name FROM gpkg_geometry_columns where
table_name = '%s'";
+ } else {
+ datasetInfoQuery = "SELECT '' ";
+ }
+
}
/**
Modified:
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/AddDatastoreLayerPanel.java
===================================================================
---
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/AddDatastoreLayerPanel.java
2018-10-10 11:04:39 UTC (rev 5975)
+++
core/trunk/src/com/vividsolutions/jump/workbench/ui/plugin/datastore/AddDatastoreLayerPanel.java
2018-10-12 09:19:25 UTC (rev 5976)
@@ -20,6 +20,7 @@
import com.vividsolutions.jump.datastore.SpatialReferenceSystemID;
import com.vividsolutions.jump.datastore.jdbc.JDBCUtil;
import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock;
+import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSMetadata;
import
com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder;
import com.vividsolutions.jump.task.TaskMonitor;
import com.vividsolutions.jump.workbench.WorkbenchContext;
@@ -287,14 +288,14 @@
connectionManager(), connectionDescriptor,
AddDatastoreLayerPanel.this).getMetadata();
- for (String dsName : datasetNames) {
- for (GeometryColumn geo : md.getGeometryAttributes(dsName)) {
- DataStoreLayer layer = new DataStoreLayer(dsName, geo);
+ // Nico Ribot, 2018-08-07: new mechanism in SpatialDatabasesDSMetadata;
+ // DataStoreLayer list is retrieved when getDatasetNames is called
+ // TODO: propagate to DataStoreMetadata interface
+ if (md instanceof SpatialDatabasesDSMetadata &&
((SpatialDatabasesDSMetadata)md).getDataStoreLayers() != null) {
+ System.out.println("adding datastorelayer directly !");
+ for (DataStoreLayer layer :
((SpatialDatabasesDSMetadata)md).getDataStoreLayers()) {
ArrayList<DataStoreLayer> newEntry = new ArrayList<>();
newEntry.add(layer);
- // ON Java 8:
-// ArrayList<DataStoreLayer> list =
ret.putIfAbsent(layer.getSchema(), newEntry);
- // On Java 6, 7
ArrayList<DataStoreLayer> list = ret.get(layer.getSchema());
if (list == null) {
ret.put(layer.getSchema(), newEntry);
@@ -303,7 +304,27 @@
list.addAll(newEntry);
}
}
+ } else {
+ // normal mechanims
+ for (String dsName : datasetNames) {
+ for (GeometryColumn geo : md.getGeometryAttributes(dsName)) {
+ DataStoreLayer layer = new DataStoreLayer(dsName, geo);
+ ArrayList<DataStoreLayer> newEntry = new ArrayList<>();
+ newEntry.add(layer);
+ // ON Java 8:
+ // ArrayList<DataStoreLayer> list =
ret.putIfAbsent(layer.getSchema(), newEntry);
+ // On Java 6, 7
+ ArrayList<DataStoreLayer> list = ret.get(layer.getSchema());
+ if (list == null) {
+ ret.put(layer.getSchema(), newEntry);
+ } else {
+ // this schema exists: add newEntry into existing list
+ list.addAll(newEntry);
+ }
+ }
+ }
}
+
return ret;
}
}
_______________________________________________
Jump-pilot-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel
