This is an automated email from the ASF dual-hosted git repository. asf-gitbox-commits pushed a commit to branch geoapi-4.0 in repository https://gitbox.apache.org/repos/asf/sis.git
commit 27beef8199f07ad2e458967ee722f6dd2d2c8c12 Author: Martin Desruisseaux <[email protected]> AuthorDate: Sat Oct 17 00:33:40 2026 +0200 Partial workaround for the problem of HSQL which ignore collations in LIKE clause. We cannot compensate fully this problem, but we can make it less frequent. --- .../sis/metadata/sql/internal/shared/Dialect.java | 11 ++++ .../metadata/sql/internal/shared/SQLBuilder.java | 3 +- .../metadata/sql/internal/shared/ScriptRunner.java | 4 +- .../sis/metadata/sql/internal/shared/Supports.java | 8 ++- .../factory/ConcurrentAuthorityFactory.java | 2 +- .../factory/IdentifiedObjectFinder.java | 2 +- .../referencing/factory/sql/EPSGCodeFinder.java | 61 ++++++++++++++++------ .../sis/referencing/factory/sql/EPSGInstaller.java | 4 ++ .../sis/referencing/factory/sql/SQLTranslator.java | 16 +++++- .../sis/referencing/factory/sql/package-info.java | 2 +- .../main/org/apache/sis/pending/jdk/JDK26.java | 41 +++++++++++++++ 11 files changed, 130 insertions(+), 24 deletions(-) diff --git a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Dialect.java b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Dialect.java index adbf5d1e63..ff2a4ee83e 100644 --- a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Dialect.java +++ b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Dialect.java @@ -49,6 +49,7 @@ public enum Dialect { * <a href="https://issues.apache.org/jira/browse/DERBY-6445";>DERBY-6445</a> */ DERBY("derby", Supports.ALTER_TABLE_WITH_ADD_CONSTRAINT + | Supports.COLLATION_IN_LIKE | Supports.READ_ONLY_UPDATE | Supports.CONCURRENCY), @@ -82,6 +83,7 @@ public enum Dialect { | Supports.CONCURRENCY | Supports.JAVA_TIME | Supports.ILIKE + | Supports.COLLATION_IN_LIKE // Since PostgreSQL 18. | Supports.SRID), /** @@ -271,6 +273,15 @@ public enum Dialect { return (flags & Supports.SRID) != 0; } + /** + * Whether the database takes collation in account in {@code LIKE} clauses. + * + * @return whether the database supports collations in {@code LIKE} clauses. + */ + public final boolean supportsCollationInLike() { + return (flags & Supports.COLLATION_IN_LIKE) != 0; + } + /** * Returns the keyword for a case-insensitive {@code LIKE}, or {@code null} if none. * The default implementation returns {@code null} because {@code ILIKE} is not standard <abbr>SQL</abbr>. diff --git a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLBuilder.java b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLBuilder.java index 2455bff4f5..2fdd2fbffd 100644 --- a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLBuilder.java +++ b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/SQLBuilder.java @@ -258,7 +258,7 @@ public class SQLBuilder extends Syntax { /** * Appends a {@code "= <value>"} string in a {@code SELECT} statement. - * The value is written between quotes, except if it is a number or a boolean. + * The value is written between quotes, except if it is a number or a Boolean. * * @param value the value to append, or {@code null}. * @return this builder, for method call chaining. @@ -278,6 +278,7 @@ public class SQLBuilder extends Syntax { * @return this builder, for method call chaining. */ public final SQLBuilder appendValue(final String value) { + // TODO: use Connection.enquoteLiteral(String) with JDK26. return (value == null) ? append(NULL) : append('\'').append(value.replace("'", "''")).append('\''); } diff --git a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java index 84ab3300d0..cf7ba6d13a 100644 --- a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java +++ b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/ScriptRunner.java @@ -178,8 +178,8 @@ public class ScriptRunner implements AutoCloseable { * In principle, {@link Connection#rollback()} should be sufficient, but it appears to not be the case * with all databases. * - * <p>This is a workaround for what seems to be a bug in <abbr>HSQLDB</abbr>, where - * {@link Connection#rollback()} does drop the schema created before the rollback.</p> + * <p>This is a workaround for a limitation in <abbr>HSQLDB</abbr>, where + * {@link Connection#rollback()} does not drop the schema created before the rollback.</p> * * @see <a href="https://sourceforge.net/p/hsqldb/bugs/1754/";>Connection.rollback() does not remove schemas</a> */ diff --git a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Supports.java b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Supports.java index bf4b3e9765..5bd73cec98 100644 --- a/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Supports.java +++ b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Supports.java @@ -86,10 +86,16 @@ final class Supports { */ public static final int ILIKE = 0x200; + /** + * Whether the database supports collations in {@code LIKE} clauses. + * Some databases support collations in {@code =} comparisons but silently ignore it in {@code LIKE} clauses. + */ + public static final int COLLATION_IN_LIKE = 0x400; + /** * Whether the spatial extension supports <abbr>SRID</abbr> in {@code ST_*} functions. */ - public static final int SRID = 0x400; + public static final int SRID = 0x800; /** * Do not allow instantiation of this class. diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/ConcurrentAuthorityFactory.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/ConcurrentAuthorityFactory.java index 32cf7fb30d..dbd98f44c4 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/ConcurrentAuthorityFactory.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/ConcurrentAuthorityFactory.java @@ -300,7 +300,7 @@ public abstract class ConcurrentAuthorityFactory<DAO extends GeodeticAuthorityFa * If more than this number of threads are querying this {@code ConcurrentAuthorityFactory} concurrently, * additional threads will be blocked until a Data Access Object become available. */ - @SuppressWarnings("this-escape") // Phantom reference. + @SuppressWarnings({"this-escape", "LeakingThisInConstructor"}) // Phantom reference. protected ConcurrentAuthorityFactory(final Class<DAO> dataAccessClass, final int maxStrongReferences, final int maxConcurrentQueries) { diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/IdentifiedObjectFinder.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/IdentifiedObjectFinder.java index 49c7bd77f8..aed8f8eaf0 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/IdentifiedObjectFinder.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/IdentifiedObjectFinder.java @@ -447,7 +447,7 @@ public class IdentifiedObjectFinder { /** Snapshot of {@link IdentifiedObjectFinder#proxy}. */ private final AuthorityFactoryProxy<?> proxy; - /** The authority codes form which to create object candidates. */ + /** The authority codes from which to create object candidates. */ private final Iterable<String> codes; /** The comparison mode for deciding if a candidate is a match. */ diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java index 36f13b09a4..3f8040eb23 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGCodeFinder.java @@ -16,10 +16,12 @@ */ package org.apache.sis.referencing.factory.sql; +import java.util.Map; import java.util.Set; import java.util.List; import java.util.ArrayList; import java.util.LinkedHashSet; +import java.util.LinkedHashMap; import java.util.Collection; import java.util.Iterator; import java.sql.Statement; @@ -54,6 +56,7 @@ import org.apache.sis.util.collection.Containers; import org.apache.sis.util.collection.BackingStoreException; import org.apache.sis.pending.jdk.JDK16; import org.apache.sis.pending.jdk.JDK19; +import org.apache.sis.pending.jdk.JDK26; import org.apache.sis.metadata.internal.shared.ReferencingServices; import org.apache.sis.metadata.iso.citation.Citations; import org.apache.sis.referencing.IdentifiedObjects; @@ -434,20 +437,22 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { * so that buffer shall not contain valuable information yet. */ final var buffer = new StringBuilder(350); // Temporary buffer for building SQL query. - final Set<String> namePatterns; // Name including aliases. + final Map<String, String> namePatterns; // Name and aliases as keys, LIKE patterns as values. final String aliasSQL; if (ArraysExt.containsIdentity(filters, Condition.NAME)) { - namePatterns = new LinkedHashSet<>(); - namePatterns.add(toDatumPattern(getName(object), buffer)); - for (final GenericName id : object.getAlias()) { - namePatterns.add(toDatumPattern(id.tip().toString(), buffer)); + namePatterns = new LinkedHashMap<>(); + String name = getName(object); + namePatterns.put(name, toDatumPattern(name, buffer)); + for (final GenericName alias : object.getAlias()) { + name = alias.tip().toString(); + namePatterns.put(name, toDatumPattern(name, buffer)); } buffer.setLength(0); buffer.append("SELECT OBJECT_CODE FROM \"Alias\" WHERE OBJECT_TABLE_NAME='") .append(dao.translator.toActualTableName(source.table)) .append("' AND "); // PostgreSQL does not require explicit cast when the value is a literal instead of "?". - appendLikeNames(namePatterns, "ALIAS", buffer); + appendLikeNames(namePatterns, "ALIAS", dao.translator.supportsCollationInLike, buffer); aliasSQL = dao.translator.apply(buffer.toString()); buffer.setLength(0); } else { @@ -480,9 +485,11 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { */ try (Statement stmt = dao.connection.createStatement()) { if (namePatterns != null) { - if (isNext) buffer.append(" AND "); - isNext = false; - appendLikeNames(namePatterns, source.nameColumn, buffer); + if (isNext) { + isNext = false; + buffer.append(" AND "); + } + appendLikeNames(namePatterns, source.nameColumn, dao.translator.supportsCollationInLike, buffer); try (ResultSet result = stmt.executeQuery(aliasSQL)) { while (result.next()) { final int code = result.getInt(1); @@ -594,18 +601,42 @@ crs: if (isInstance(CoordinateReferenceSystem.class, object)) { * (<column> LIKE '<pattern>' OR …) * } * + * Theoretically, using only the above {@code LIKE} conditions should be sufficient. + * However, some databases support collations only during comparisons with {@code =} + * and ignores collations when using {@code LIKE}. If the databases is known to have + * this limitation, the {@code supportsCollationInLike} flag should be {@code false} + * for writing all conditions twice: once with {@code =} and once with {@code LIKE}. + * This duplication can mitigate the problem, but not fully resolve it. + * * This method assumes that {@code namePatterns} contains at least one element. * * @param namePatterns the patterns created by {@link #toDatumPattern(String, StringBuilder)}. * @param column column where the search for the names. * @param buffer buffer where to add the SQL fragment. */ - private static void appendLikeNames(final Set<String> namePatterns, final String column, final StringBuilder buffer) { - String separator = "("; - for (final String pattern : namePatterns) { - buffer.append(separator).append(column).append(" LIKE '").append(pattern).append('\''); - separator = " OR "; - } + private static void appendLikeNames(final Map<String, String> namePatterns, final String column, + boolean supportsCollationInLike, final StringBuilder buffer) + { + buffer.append('('); + boolean isNext = false; + do { + for (final Map.Entry<String, String> entry : namePatterns.entrySet()) { + if (isNext) { + buffer.append(" OR "); + } + final String name; + final String operator; + if (supportsCollationInLike) { + name = entry.getValue(); // LIKE pattern. + operator = " LIKE "; + } else { + name = entry.getKey(); // Original name. + operator = " = "; + } + buffer.append(column).append(operator).append(JDK26.enquoteLiteral(name)); + isNext = true; + } + } while ((supportsCollationInLike = !supportsCollationInLike) == true); buffer.append(')'); } diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java index cd8f9a434c..5ffe3d52ee 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/EPSGInstaller.java @@ -63,10 +63,14 @@ final class EPSGInstaller extends ScriptRunner { /** * Creates a new runner which will execute the statements using the given connection. * This constructor creates immediately a schema of the given name in the database. + * Replacements are prepared for features not supported by the target database, + * such as enumerations and collations. * * @param connection the connection to the database. * @param schema the schema, or {@code null} for any, or empty for none. * @throws SQLException if an error occurred while executing a SQL statement. + * + * @see <a href="https://hsqldb.org/doc/guide/databaseobjects-chapt.html#dbc_collations";>HSQLDB collations</a> */ public EPSGInstaller(final Connection connection, final String schema) throws SQLException { super(connection, schema == null ? Constants.EPSG : schema.isEmpty() ? null : schema, 100); diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java index 11bf562de0..de2b2b0003 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/SQLTranslator.java @@ -26,11 +26,13 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; +import org.apache.sis.util.Workaround; import org.apache.sis.util.CharSequences; import org.apache.sis.util.OptionalCandidate; import org.apache.sis.util.StringBuilders; import org.apache.sis.util.resources.Errors; import org.apache.sis.util.internal.shared.Constants; +import org.apache.sis.metadata.sql.internal.shared.Dialect; import org.apache.sis.metadata.sql.internal.shared.Reflection; import org.apache.sis.metadata.sql.internal.shared.SQLUtilities; import org.apache.sis.referencing.internal.Resources; @@ -202,7 +204,7 @@ public class SQLTranslator implements UnaryOperator<String> { * and values are the names used in the <abbr>EPSG</abbr> database on which {@link EPSGDataAccess} is connected. * By convention, all column names are in upper-case while table names are in mixed-case characters.</p> */ - private Map<String,String> tableRewording; + private Map<String, String> tableRewording; /** * Replacements to perform on <abbr>SQL</abbr> statements before execution. Some entries are the replacements @@ -211,7 +213,7 @@ public class SQLTranslator implements UnaryOperator<String> { * Other entries are replacements of missing columns by {@code CAST(NULL AS …)} expressions and replacements * of {@code BOOLEAN} comparator operands by {@code SMALLINT}. */ - private Map<String,String> replacements; + private Map<String, String> replacements; /** * The characters used for quoting identifiers, or an empty string if none. @@ -242,6 +244,15 @@ public class SQLTranslator implements UnaryOperator<String> { */ private boolean useBoolean; + /** + * Whether the database takes collation in account in {@code LIKE} clauses. + * Collations were working well in Derby, but the migration to HSQLDB forces + * us to create redundant conditions in the <abbr>SQL</abbr> statement without + * reaching the same power as Derby or PostgreSQL. + */ + @Workaround(library="HSQLDB", version="2.7.4") + final boolean supportsCollationInLike; + /** * Whether the sentinel table {@code "Coordinate_Operation"} or a variant has been found. * If {@code false}, then {@link EPSGInstaller} needs to be run. @@ -287,6 +298,7 @@ public class SQLTranslator implements UnaryOperator<String> { * @throws SQLException if an error occurred while querying the database metadata. */ public SQLTranslator(final DatabaseMetaData md, final String catalog, final String schema) throws SQLException { + supportsCollationInLike = Dialect.guess(md).supportsCollationInLike(); identifierQuote = md.getIdentifierQuoteString().trim(); wildcardEscape = md.getSearchStringEscape(); this.catalog = catalog; diff --git a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java index 7a366ea7a1..9c3be9e7c9 100644 --- a/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java +++ b/endorsed/src/org.apache.sis.referencing/main/org/apache/sis/referencing/factory/sql/package-info.java @@ -83,7 +83,7 @@ * @author Jody Garnett (Refractions) * @author Didier Richard (IGN) * @author John Grange - * @version 1.7 + * @version 1.8 * * @see org.apache.sis.metadata.sql * diff --git a/endorsed/src/org.apache.sis.util/main/org/apache/sis/pending/jdk/JDK26.java b/endorsed/src/org.apache.sis.util/main/org/apache/sis/pending/jdk/JDK26.java new file mode 100644 index 0000000000..c2f442f8be --- /dev/null +++ b/endorsed/src/org.apache.sis.util/main/org/apache/sis/pending/jdk/JDK26.java @@ -0,0 +1,41 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.sis.pending.jdk; + + +/** + * Place holder for some functionalities defined in a JDK more recent than Java 11. + * + * @author Martin Desruisseaux (Geomatys) + */ +public final class JDK26 { + /** + * Do not allow instantiation of this class. + */ + private JDK26() { + } + + /** + * Placeholder for {@code Connection.enquoteLiteral(String)}. + * + * @param value the string to encode. + * @return the quoted value. + */ + public static String enquoteLiteral(String value) { + return '\'' + value.replace("'", "''") + '\''; + } +}
