This is an automated email from the ASF dual-hosted git repository.
desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git
commit 0ddd4d225c806b7b513f8eedce26fe7a4f187aa1
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Wed Mar 12 16:19:55 2025 +0100
Make the search for geometry columns robust to the case where there
is no information about which catalog and schema contain the table.
This case happens with materialized views.
---
.../apache/sis/storage/sql/feature/Database.java | 20 ++++++--
.../sis/storage/sql/feature/InfoStatements.java | 33 ++++++++++--
.../sis/storage/sql/feature/SpatialSchema.java | 59 +++++++++++++++++-----
.../sis/storage/sql/postgis/ExtendedInfo.java | 1 +
4 files changed, 92 insertions(+), 21 deletions(-)
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
index b09cfed294..daa92f3695 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Database.java
@@ -295,6 +295,18 @@ public class Database<G> extends Syntax {
transactionLocks = dialect.supportsConcurrency() ? null : locks;
}
+ /**
+ * Returns the value of a {@code LIKE} statement with wildcard characters
escaped.
+ * The wildcard characters are {@code '_'} and {@code '%'}. This method is
invoked
+ * when an exact match for the given value is desired.
+ *
+ * @param value the {@code LIKE} pattern to search.
+ * @return the given pattern with wildcard characters escaped.
+ */
+ final String escapeWildcards(final String value) {
+ return SQLUtilities.escape(value, escape);
+ }
+
/**
* Detects automatically which spatial schema is in use. Detects also the
catalog name and schema name.
* This method is invoked exactly once after construction and before the
analysis of feature tables.
@@ -336,7 +348,7 @@ public class Database<G> extends Syntax {
String catalog = null, schema = null;
for (final Map.Entry<String,Boolean> entry :
ignoredTables.entrySet()) {
if (entry.getValue()) {
- String table = SQLUtilities.escape(entry.getKey(), escape);
+ String table = escapeWildcards(entry.getKey());
try (ResultSet reflect = metadata.getTables(null, null,
table, tableTypes)) {
while (reflect.next()) {
consistent &= consistent(catalog, catalog =
reflect.getString(Reflection.TABLE_CAT));
@@ -363,14 +375,14 @@ public class Database<G> extends Syntax {
* The preference order will be defined by the `CRSEncoding`
enumeration order.
*/
if (spatialSchema != null) {
- final String schema = SQLUtilities.escape(schemaOfSpatialTables,
escape);
- final String table = SQLUtilities.escape(crsTable, escape);
+ final String schema = escapeWildcards(schemaOfSpatialTables);
+ final String table = escapeWildcards(crsTable);
for (Map.Entry<CRSEncoding, String> entry :
spatialSchema.crsDefinitionColumn.entrySet()) {
String column = entry.getValue();
if (metadata.storesLowerCaseIdentifiers()) {
column = column.toLowerCase(Locale.US);
}
- column = SQLUtilities.escape(column, escape);
+ column = escapeWildcards(column);
try (ResultSet reflect =
metadata.getColumns(catalogOfSpatialTables, schema, table, column)) {
if (reflect.next()) {
crsEncodings.add(entry.getKey());
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
index 111d0ae99f..440e195d90 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/InfoStatements.java
@@ -203,12 +203,37 @@ public class InfoStatements implements Localized,
AutoCloseable {
sql.append(", ").append(geomTypeColumn);
}
appendFrom(sql, table);
- if (database.supportsCatalogs) appendColumn(sql, raster,
schema.geomCatalogColumn).append("=? AND ");
- if (database.supportsSchemas) appendColumn(sql, raster,
schema.geomSchemaColumn) .append("=? AND ");
+ /*
+ * In principle, all tables should be unambiguously specified with
their catalog and schema name.
+ * However, some JDBC drivers do not provide this information in some
circumstances such as materialized views.
+ * Therefore, we use the `LIKE` operator instead of `=` for making
possible to disable the filtering by schema.
+ */
+ if (database.supportsCatalogs) appendColumn(sql, raster,
schema.geomCatalogColumn).append(" LIKE ? AND ");
+ if (database.supportsSchemas) appendColumn(sql, raster,
schema.geomSchemaColumn) .append(" LIKE ? AND ");
appendColumn(sql, raster, schema.geomTableColumn).append("=?");
return connection.prepareStatement(sql.toString());
}
+ /**
+ * Sets the parameter value for a table catalog or schema. Those
parameters use the {@code LIKE} statement
+ * in order to ignore the catalog or schema when it is not specified. A
catalog or schema is not specified
+ * if the string is null or empty. The latter case may happens with some
drivers with, for example,
+ * materialized views.
+ *
+ * @param columnQuery the query where to set the parameter.
+ * @param p index of the parameter to set.
+ * @param source catalog or schema name to set, or null or empty if
unknown.
+ * @throws SQLException if an error occurred while setting the parameter
value.
+ */
+ private void setCatalogOrSchema(final PreparedStatement columnQuery, final
int p, String source) throws SQLException {
+ if (source == null || source.isEmpty()) {
+ source = "%";
+ } else {
+ source = database.escapeWildcards(source);
+ }
+ columnQuery.setString(p, source);
+ }
+
/**
* Gets all geometry and raster columns for the given table and sets
information on the corresponding columns.
* Column instances in the {@code columns} map are modified in-place (the
map itself is not modified).
@@ -251,8 +276,8 @@ public class InfoStatements implements Localized,
AutoCloseable {
final Map<String,Column> columns, final GeometryTypeEncoding
typeValueKind) throws Exception
{
int p = 0;
- if (database.supportsCatalogs) columnQuery.setString(++p,
source.catalog);
- if (database.supportsSchemas) columnQuery.setString(++p,
source.schema);
+ if (database.supportsCatalogs) setCatalogOrSchema(columnQuery, ++p,
source.catalog);
+ if (database.supportsSchemas) setCatalogOrSchema(columnQuery, ++p,
source.schema);
columnQuery.setString(++p, source.table);
try (ResultSet result = columnQuery.executeQuery()) {
while (result.next()) {
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
index 42b62ef88f..712eeb0e36 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SpatialSchema.java
@@ -40,12 +40,22 @@ public enum SpatialSchema {
* except for table names, for the case (Geopackage uses lower case) and
for the addition of a
* {@code geometry_type_name} column.
*/
- GEOPACKAGE("gpkg_spatial_ref_sys", "srs_name", "srs_id", "organization",
"organization_coordsys_id",
- Map.of(CRSEncoding.WKT1, "definition",
- CRSEncoding.WKT2, "definition_12_063"),
- "description",
- "gpkg_geometry_columns", "table_catalog", "table_schema",
"table_name",
- "column_name", "geometry_type_name",
GeometryTypeEncoding.TEXTUAL),
+ GEOPACKAGE(
+ "gpkg_spatial_ref_sys", // Table for Spatial
Reference System definitions.
+ "srs_name", // Column for CRS names.
+ "srs_id", // Column for CRS
identifiers.
+ "organization", // Column for CRS
authority names.
+ "organization_coordsys_id", // Column for CRS
authority codes.
+ Map.of(CRSEncoding.WKT1, "definition", // Columns for CRS
definitions in WKT format.
+ CRSEncoding.WKT2, "definition_12_063"),
+ "description", // Column for the CRS
description.
+ "gpkg_geometry_columns", // Table enumerating the
geometry columns.
+ "table_catalog", // Column where the
catalog of each geometry column is stored.
+ "table_schema", // Column where the schema
of each geometry column is stored.
+ "table_name", // Column where the table
of each geometry column is stored.
+ "column_name", // Column where the column
of each geometry column is stored.
+ "geometry_type_name", // Column where the type
of each geometry column is stored.
+ GeometryTypeEncoding.TEXTUAL), // How geometry types are
encoded in the above-cited type column.
/**
* Table and column names as specified by ISO-13249 SQL/MM. This is the
same thing as {@link #SIMPLE_FEATURE}
@@ -64,9 +74,20 @@ public enum SpatialSchema {
* In Geopackage, this table is named {@code "gpkg_spatial_ref_sys"} but
otherwise has identical content
* except for the case (Geopackage uses lower case).
*/
- SQL_MM("ST_SPATIAL_REFERENCE_SYSTEMS", "SRS_NAME", "SRS_ID",
"ORGANIZATION", "ORGANIZATION_COORDSYS_ID",
- Map.of(CRSEncoding.WKT1, "DEFINITION"), "DESCRIPTION",
- "ST_GEOMETRY_COLUMNS", "TABLE_CATALOG", "TABLE_SCHEMA",
"TABLE_NAME", "COLUMN_NAME", null, null),
+ SQL_MM("ST_SPATIAL_REFERENCE_SYSTEMS", // Table for Spatial
Reference System definitions.
+ "SRS_NAME", // Column for CRS names.
+ "SRS_ID", // Column for CRS
identifiers.
+ "ORGANIZATION", // Column for CRS
authority names.
+ "ORGANIZATION_COORDSYS_ID", // Column for CRS
authority codes.
+ Map.of(CRSEncoding.WKT1, "DEFINITION"), // Columns for CRS
definitions in WKT format.
+ "DESCRIPTION", // Column for the CRS
description.
+ "ST_GEOMETRY_COLUMNS", // Table enumerating the
geometry columns.
+ "TABLE_CATALOG", // Column where the
catalog of each geometry column is stored.
+ "TABLE_SCHEMA", // Column where the schema
of each geometry column is stored.
+ "TABLE_NAME", // Column where the table
of each geometry column is stored.
+ "COLUMN_NAME", // Column where the column
of each geometry column is stored.
+ null, // Column where the type
of each geometry column is stored, or null if none.
+ null), // How geometry types are
encoded in the above-cited type column.
/**
* Table and column names as specified by ISO 19125 / OGC Simple feature
access part 2.
@@ -82,9 +103,21 @@ public enum SpatialSchema {
* SRTEXT CHARACTER VARYING(2048))
* }
*/
- SIMPLE_FEATURE("SPATIAL_REF_SYS", null, "SRID", "AUTH_NAME", "AUTH_SRID",
Map.of(CRSEncoding.WKT1, "SRTEXT"), null,
- "GEOMETRY_COLUMNS", "F_TABLE_CATALOG", "F_TABLE_SCHEMA",
"F_TABLE_NAME", "F_GEOMETRY_COLUMN",
- "GEOMETRY_TYPE", GeometryTypeEncoding.NUMERIC);
+ SIMPLE_FEATURE(
+ "SPATIAL_REF_SYS", // Table for Spatial
Reference System definitions.
+ null, // Column for CRS names,
or `null` if none.
+ "SRID", // Column for CRS
identifiers.
+ "AUTH_NAME", // Column for CRS
authority names.
+ "AUTH_SRID", // Column for CRS
authority codes.
+ Map.of(CRSEncoding.WKT1, "SRTEXT"), // Columns for CRS
definitions in WKT format.
+ null, // Column for the CRS
description, or `null` if none.
+ "GEOMETRY_COLUMNS", // Table enumerating the
geometry columns.
+ "F_TABLE_CATALOG", // Column where the
catalog of each geometry column is stored.
+ "F_TABLE_SCHEMA", // Column where the schema
of each geometry column is stored.
+ "F_TABLE_NAME", // Column where the table
of each geometry column is stored.
+ "F_GEOMETRY_COLUMN", // Column where the column
of each geometry column is stored.
+ "GEOMETRY_TYPE", // Column where the type
of each geometry column is stored.
+ GeometryTypeEncoding.NUMERIC); // How geometry types are
encoded in the above-cited type column.
/**
* Name of the table for Spatial Reference System definitions.
@@ -177,7 +210,7 @@ public enum SpatialSchema {
* @param crsIdentifierColumn name of the column for CRS identifiers.
* @param crsAuthorityNameColumn name of the column for CRS authority
names.
* @param crsAuthorityCodeColumn name of the column for CRS authority
codes.
- * @param crsDefinitionColumn name of the column for CRS definitions
in <abbr>WKT</abbr> format.
+ * @param crsDefinitionColumn name of the columns for CRS definitions
in <abbr>WKT</abbr> format.
* @param crsDescriptionColumn name of the column for the CRS
description, or {@code null} if none.
* @param geometryColumns name of the table enumerating the
geometry columns.
* @param geomCatalogColumn name of the column where the catalog of
each geometry column is stored.
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedInfo.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedInfo.java
index e43555c88e..702a81ab09 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedInfo.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedInfo.java
@@ -94,6 +94,7 @@ final class ExtendedInfo extends InfoStatements {
* Closes all prepared statements. This method does <strong>not</strong>
close the connection.
*/
@Override
+ @SuppressWarnings("ConvertToTryWithResources")
public void close() throws SQLException {
if (geographyColumns != null) {
geographyColumns.close();
