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 <nicolas.ri...@gmail.com> - * 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 Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel