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
The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
new bce9be08df Add a fallback for trying to guess the SRID when the table
of a column is unknown. This is a workaround for incomplete JDBC drivers (in
this case, DuckDB 1.2.2.0).
bce9be08df is described below
commit bce9be08dfb17ed33c571e37374dbdecfb1ec0ee
Author: Martin Desruisseaux <martin.desruisse...@geomatys.com>
AuthorDate: Tue Apr 22 16:08:25 2025 +0200
Add a fallback for trying to guess the SRID when the table of a column is
unknown.
This is a workaround for incomplete JDBC drivers (in this case, DuckDB
1.2.2.0).
---
.../org/apache/sis/metadata/sql/privy/Dialect.java | 2 +
.../apache/sis/metadata/sql/privy/Supports.java | 3 +
.../org/apache/sis/storage/sql/feature/Column.java | 15 ++-
.../apache/sis/storage/sql/feature/Database.java | 2 +-
.../storage/sql/feature/GeometryTypeEncoding.java | 2 +
.../sis/storage/sql/feature/InfoStatements.java | 118 ++++++++++++++++++---
.../sis/storage/sql/feature/QueryAnalyzer.java | 2 +-
.../sis/storage/sql/feature/SpatialSchema.java | 9 +-
.../sis/storage/sql/feature/TableAnalyzer.java | 4 +-
9 files changed, 134 insertions(+), 23 deletions(-)
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Dialect.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Dialect.java
index 83af6d9816..ddec3b61ab 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Dialect.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Dialect.java
@@ -18,6 +18,7 @@ package org.apache.sis.metadata.sql.privy;
import java.sql.SQLException;
import java.sql.DatabaseMetaData;
+import org.apache.sis.util.Workaround;
import org.apache.sis.util.CharSequences;
import org.apache.sis.util.privy.Constants;
@@ -197,6 +198,7 @@ public enum Dialect {
* This flag should be {@code false} when the JDBC driver returns a
non-null catalog name
* (for example, the database name) but doesn't accept the use of that
catalog in SQL.
*/
+ @Workaround(library = "DuckDB", version = "1.2.2.0")
public final boolean supportsCatalog() {
return (flags & Supports.CATALOG) != 0;
}
diff --git
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Supports.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Supports.java
index b4f2086e1b..0820e1ea8f 100644
---
a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Supports.java
+++
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/privy/Supports.java
@@ -16,6 +16,8 @@
*/
package org.apache.sis.metadata.sql.privy;
+import org.apache.sis.util.Workaround;
+
/**
* Enumeration of features that may be supported by a database.
@@ -71,6 +73,7 @@ final class Supports {
* This flag should be {@code false} when the JDBC driver returns a
non-null catalog name
* (for example, the database name) but doesn't accept the use of that
catalog in SQL.
*/
+ @Workaround(library = "DuckDB", version = "1.2.2.0")
static final int CATALOG = 64;
/**
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Column.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Column.java
index 1d23b834c0..9b6dc9444c 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Column.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Column.java
@@ -22,6 +22,7 @@ import java.sql.ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.SQLDataException;
+import java.util.Map;
import java.util.Optional;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.apache.sis.metadata.sql.privy.Reflection;
@@ -250,14 +251,20 @@ public final class Column implements Cloneable {
* Tries to parses the geometry type from the field type.
* This is used as a fallback when no geometry column is found or can be
used.
*
- * @param database the database for which to analyze the tables.
+ * @param analyzer the object used for analyzing the database schema.
+ * @throws Exception if an error occurred while fetching the
<abbr>CRS</abbr>.
*/
- final void tryMakeSpatial(final Database<?> database) {
+ final void tryMakeSpatial(final Analyzer analyzer) throws Exception {
try {
geometryType = GeometryType.forName(typeName);
- geometryAsText = (database.getGeometryEncoding(this) ==
GeometryEncoding.WKT);
+ geometryAsText = (analyzer.database.getGeometryEncoding(this) ==
GeometryEncoding.WKT);
+ final InfoStatements spatialInformation =
analyzer.spatialInformation;
+ if (spatialInformation != null) {
+ spatialInformation.completeIntrospection(analyzer, null,
Map.of());
+ defaultCRS = spatialInformation.guessCRS(name);
+ }
} catch (IllegalArgumentException e) {
- // Ignore.
+ // The `typeName` value is not the name of a geometry type. Ignore.
}
}
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 515e6a0737..2fd5f41a7b 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
@@ -319,7 +319,7 @@ public class Database<G> extends Syntax {
this.cacheOfCRS = new Cache<>(7, 2, false);
this.cacheOfSRID = new WeakHashMap<>();
this.tablesByNames = new FeatureNaming<>();
- supportsCatalogs = metadata.supportsCatalogsInDataManipulation();
+ supportsCatalogs = dialect.supportsCatalog() &&
metadata.supportsCatalogsInDataManipulation();
supportsSchemas = metadata.supportsSchemasInDataManipulation();
supportsJavaTime = dialect.supportsJavaTime();
crsEncodings = EnumSet.noneOf(CRSEncoding.class);
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/GeometryTypeEncoding.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/GeometryTypeEncoding.java
index 6b8c7859f2..ef4d28dd6e 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/GeometryTypeEncoding.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/GeometryTypeEncoding.java
@@ -48,6 +48,8 @@ public enum GeometryTypeEncoding {
/**
* Decodes the geometry type encoded in the specified column of the given
result set.
* If there is no type information, then this method returns {@code null}.
+ *
+ * @throws IllegalArgumentException if the type cannot be decoded.
*/
GeometryType parse(final ResultSet result, final int columnIndex) throws
SQLException {
final int code = result.getInt(columnIndex);
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 294cd19b51..f2b5275f52 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
@@ -57,6 +57,7 @@ import org.apache.sis.util.privy.Constants;
import org.apache.sis.io.wkt.Convention;
import org.apache.sis.io.wkt.WKTFormat;
import org.apache.sis.io.wkt.Warnings;
+import org.apache.sis.util.Workaround;
// Specific to the geoapi-3.1 and geoapi-4.0 branches:
import org.opengis.metadata.Identifier;
@@ -115,35 +116,69 @@ public class InfoStatements implements Localized,
AutoCloseable {
* A statement for fetching geometric information for a specific column.
* May be {@code null} if not yet prepared or if the table does not exist.
* This field is valid if {@link #isAnalysisPrepared} is {@code true}.
+ *
+ * @see #isAnalysisPrepared
+ * @see #completeIntrospection(Analyzer, TableReference, Map)
*/
protected PreparedStatement geometryColumns;
/**
* Whether the statements for schema analysis have been prepared.
- * Includes {@link #geometryColumns}, but not fetching the CRS.
- * A statement may still be null if the table has not been found.
+ * This flag tells whether the statements for the following information
are valid even if {@code null}:
+ *
+ * <ul>
+ * <li>{@linkplain #geometryColumns Geometry columns}</li>
+ * <li>Geography columns (specific to PostGIS)</li>
+ * <li>Raster columns (specific to PostGIS)</li>
+ * </ul>
+ *
+ * This flag does not apply to the statements working on the {@code
SPATIAL_REF_SYS} table,
+ * which is assumed to always exist.
*/
protected boolean isAnalysisPrepared;
/**
- * The statement for fetching CRS Well-Known Text (WKT) from a SRID code.
+ * The statement for fetching a SRID from a geometry column table when the
column's table is unknown.
+ * This is a workaround for <abbr>JDBC</abbr> drivers that do not provide
this information.
+ * It is created only if requested.
*
- * @see <a
href="http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2571265";>PostGIS
documentation</a>
+ * @see #guessCRS(String)
*/
- private PreparedStatement wktFromSrid;
+ @Workaround(library = "DuckDB", version = "1.2.2.0")
+ private PreparedStatement sridForUnknownTable;
/**
* The statement for fetching a SRID from a CRS and its set of authority
codes.
+ * Created when first needed.
+ *
+ * @see #findOrAddCRS(CoordinateReferenceSystem)
*/
private PreparedStatement sridFromCRS;
/**
- * The object to use for parsing or formatting Well-Known Text (WKT),
created when first needed.
+ * The statement for fetching CRS Well-Known Text (<abbr>WKT</abbr>) from
a <abbr>SRID</abbr> code.
+ * Created when first needed.
+ *
+ * @see #parseCRS(int)
+ * @see <a
href="http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2571265";>PostGIS
documentation</a>
+ */
+ private PreparedStatement wktFromSrid;
+
+ /**
+ * The object to use for parsing or formatting Well-Known Text
(<abbr>WKT</abbr>).
+ * Created when first needed.
*
* @see #wktFormat()
*/
private WKTFormat wktFormat;
+ /**
+ * Whether an error occurred while reading the geometry type.
+ * In such case, the type default to {@link GeometryType#GEOMETRY}.
+ * This flag is used for reporting the warning only once.
+ */
+ private boolean cannotReadGeometryType;
+
/**
* Creates an initially empty {@code CachedStatements} which will use
* the given connection for creating {@link PreparedStatement}s.
@@ -194,6 +229,11 @@ public class InfoStatements implements Localized,
AutoCloseable {
* Prepares the statement for fetching information about all geometry or
raster columns in a specified table.
* This method is for {@link #completeIntrospection(Analyzer,
TableReference, Map)} implementations.
*
+ * <h4>PostGIS special case</h4>
+ * By default, the {@code geomColNameColumn} and {@code geomTypeColumn}
argument values are fetched from the
+ * {@link SpatialSchema}. However, PostGIS uses a non-standard {@code
geomTypeColumn} value. It also has many
+ * "geometry columns"-like tables. This is handled by overriding {@code
completeIntrospection(…)}.
+ *
* @param analyzer the opaque temporary object used for
analyzing the database schema.
* @param table name of the geometry table. Standard value
is {@code "GEOMETRY_COLUMNS"}.
* @param raster whether the statement is for raster table
instead of geometry table.
@@ -214,7 +254,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
if (geomColNameColumn == null) {
geomColNameColumn = schema.geomColNameColumn;
}
- appendColumn(sql, raster, geomColNameColumn).append(",
").append(schema.crsIdentifierColumn).append(' ');
+ appendColumn(sql, raster, geomColNameColumn).append(",
").append(schema.crsIdentifierColumn);
if (geomTypeColumn == null) {
geomTypeColumn = schema.geomTypeColumn;
}
@@ -259,8 +299,11 @@ public class InfoStatements implements Localized,
AutoCloseable {
* This method should be invoked at least once before the {@link
Column#valueGetter} field is set.
* It is invoked again for each table or query to analyze.
*
+ * <p>This method may be invoked with a null {@code source} and empty
{@code columns}
+ * for ensuring that {@link #geometryColumns} is initialized but without
executing it.</p>
+ *
* @param analyzer the opaque temporary object used for analyzing the
database schema.
- * @param source the table for which to get all geometry columns.
+ * @param source the table for which to get all geometry columns. May
be null if {@code columns} is empty.
* @param columns all columns for the specified table. Keys are column
names.
* @throws DataStoreContentException if a logical error occurred in
processing data.
* @throws ParseException if the WKT cannot be parsed.
@@ -273,6 +316,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
if (!isAnalysisPrepared) {
isAnalysisPrepared = true;
geometryColumns = prepareIntrospectionStatement(analyzer,
schema.geometryColumns, false, null, null);
+ // The `geometryColumns` field may still be null.
}
configureSpatialColumns(geometryColumns, source, columns,
schema.typeEncoding);
}
@@ -286,7 +330,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
* value returned by {@link #prepareIntrospectionStatement(Analyzer,
String, boolean, String, String)}.
*
* @param columnQuery the statement for fetching information, or
{@code null} if none.
- * @param source the table for which to get all geometry columns.
+ * @param source the table for which to get all geometry columns.
May be null if {@code columns} is empty.
* @param columns all columns for the specified table. Keys are
column names.
* @param typeValueKind {@code NUMERIC}, {@code TEXTUAL} or {@code null}
if none.
* @throws DataStoreContentException if a logical error occurred in
processing data.
@@ -301,7 +345,7 @@ public class InfoStatements implements Localized,
AutoCloseable {
protected final void configureSpatialColumns(final PreparedStatement
columnQuery, final TableReference source,
final Map<String,Column> columns, final GeometryTypeEncoding
typeValueKind) throws Exception
{
- if (columnQuery == null) {
+ if (columnQuery == null || columns.isEmpty()) {
return;
}
int p = 0;
@@ -312,18 +356,66 @@ public class InfoStatements implements Localized,
AutoCloseable {
while (result.next()) {
final Column target = columns.get(result.getString(1));
if (target != null) {
- final CoordinateReferenceSystem crs =
fetchCRS(result.getInt(2));
GeometryType type = null;
if (typeValueKind != null) {
- type = typeValueKind.parse(result, 3);
+ try {
+ type = typeValueKind.parse(result, 3);
+ } catch (IllegalArgumentException e) {
+ if (!cannotReadGeometryType) {
+ cannotReadGeometryType = true;
+
database.warning(Resources.Keys.CanNotAnalyzeFully, e);
+ }
+ }
if (type == null) {
type = GeometryType.GEOMETRY;
}
}
- target.makeSpatial(database, type, crs);
+ target.makeSpatial(database, type,
fetchCRS(result.getInt(2)));
+ }
+ }
+ }
+ }
+
+ /**
+ * Tries to guess the <abbr>CRS</abbr> for the specified column in an
unknown table.
+ * This is used for queries when the <abbr>JDBC</abbr> driver is
incomplete.
+ *
+ * @param column name of the column in unknown table.
+ * @return the <abbr>CRS</abbr>, or {@code null} if none or ambiguous.
+ * @throws Exception if an error occurred while fetching the
<abbr>CRS</abbr>.
+ */
+ @Workaround(library = "DuckDB", version = "1.2.2.0")
+ final CoordinateReferenceSystem guessCRS(final String column) throws
Exception {
+ if (sridForUnknownTable == null) {
+ if (geometryColumns == null) {
+ return null;
+ }
+ final SpatialSchema schema =
database.getSpatialSchema().orElseThrow();
+ final var sql = new SQLBuilder(database)
+ .append(SQLBuilder.SELECT).append("DISTINCT ")
+ .appendIdentifier(schema.crsIdentifierColumn)
+ .append(" FROM ").appendIdentifier(schema.geometryColumns)
+ .append(" WHERE
").appendIdentifier(schema.geomColNameColumn).append("=?");
+ sridForUnknownTable = connection.prepareStatement(sql.toString());
+ }
+ sridForUnknownTable.setString(1, column);
+ CoordinateReferenceSystem first = null;
+ try (ResultSet result = sridForUnknownTable.executeQuery()) {
+ while (result.next()) {
+ final int srid = result.getInt(1);
+ if (!result.wasNull()) {
+ CoordinateReferenceSystem crs = fetchCRS(srid);
+ if (crs != null) {
+ if (first == null) {
+ first = crs;
+ } else if (!Utilities.equalsIgnoreMetadata(first,
crs)) {
+ return null;
+ }
+ }
}
}
}
+ return first;
}
/**
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/QueryAnalyzer.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/QueryAnalyzer.java
index b474ece77a..6ebbd52955 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/QueryAnalyzer.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/QueryAnalyzer.java
@@ -171,7 +171,7 @@ final class QueryAnalyzer extends FeatureAnalyzer {
final var attributes = new ArrayList<Column>();
for (final Column column : columns) {
if (fallback) {
- column.tryMakeSpatial(analyzer.database);
+ column.tryMakeSpatial(analyzer);
}
if (createAttribute(column)) {
attributes.add(column);
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 d4f2b34b9a..c187f0b4d8 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
@@ -59,8 +59,8 @@ public enum SpatialSchema {
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}
- * with only different names. The table definition for CRS is:
+ * Table and column names as specified by ISO-13249 SQL/MM. This is
similar to {@link #SIMPLE_FEATURE}
+ * with different names and no {@code GEOMETRY_TYPE} column. The table
definition for CRS is:
*
* {@snippet lang="sql" :
* CREATE TABLE ST_SPATIAL_REFERENCE_SYSTEMS(
@@ -104,6 +104,11 @@ public enum SpatialSchema {
* AUTH_SRID INTEGER,
* SRTEXT CHARACTER VARYING(2048))
* }
+ *
+ * <h4>PostGIS special case</h4>
+ * PostGIS uses these table and column names (in lower cases), except the
{@code GEOMETRY_TYPE} column
+ * which is named only {@code TYPE} in PostGIS. There is no enumeration
value for PostGIS special case.
+ * Instead, it is handled by {@code
InfoStatements.completeIntrospection(…)} method overriding.
*/
SIMPLE_FEATURE(
"ISO 19125 / OGC Simple feature", // Human-readable name of
this spaial schema.
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/TableAnalyzer.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/TableAnalyzer.java
index 291a1797b5..28d53c2180 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/TableAnalyzer.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/TableAnalyzer.java
@@ -187,9 +187,9 @@ final class TableAnalyzer extends FeatureAnalyzer {
*/
final var attributes = new ArrayList<Column>();
for (final Column column : columns.values()) {
- if (spatialInformation == null) {
+ if (spatialInformation == null ||
spatialInformation.geometryColumns == null) {
// Fallback for databases without "geometry columns" table.
- column.tryMakeSpatial(analyzer.database);
+ column.tryMakeSpatial(analyzer);
}
if (createAttribute(column)) {
attributes.add(column);
