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
The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
new 722c224375 feat(sql): handle LIKE filter in SQL storage.
https://github.com/apache/sis/pull/47
722c224375 is described below
commit 722c224375d5d56e76736422b92584b1fc9f774f
Author: Martin Desruisseaux <[email protected]>
AuthorDate: Tue Jul 21 15:32:01 2026 +0200
feat(sql): handle LIKE filter in SQL storage.
https://github.com/apache/sis/pull/47
Co-authored-by: glegal <[email protected]>
---
.../sis/metadata/sql/internal/shared/Dialect.java | 43 ++++++++++--
.../metadata/sql/internal/shared/SQLBuilder.java | 11 +++-
.../sis/metadata/sql/internal/shared/Supports.java | 7 +-
.../sis/storage/sql/feature/SelectionClause.java | 77 +++++++++++++++++++++-
.../storage/sql/feature/SelectionClauseWriter.java | 35 ++++++++--
.../org/apache/sis/storage/sql/SQLStoreTest.java | 20 +++++-
6 files changed, 173 insertions(+), 20 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 f9c2497d7d..3c3c50b804 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
@@ -23,15 +23,15 @@ import org.apache.sis.util.internal.shared.Constants;
/**
- * The SQL dialect used by a connection. This class defines also a few
driver-specific operations
- * that cannot (to our knowledge) be inferred from the {@link
DatabaseMetaData}.
+ * The <abbr>SQL</abbr> dialect used by a connection. This class defines also
a few driver-specific
+ * information that cannot (to our knowledge) be inferred from the {@link
DatabaseMetaData}.
*
* @author Martin Desruisseaux (Geomatys)
* @author Johann Sorel (Geomatys)
*/
public enum Dialect {
/**
- * The database is presumed to use ANSI SQL syntax.
+ * The database is presumed to use <abbr>ANSI</abbr> <abbr>SQL</abbr>
syntax.
*
* @see DatabaseMetaData#supportsANSI92EntryLevelSQL()
*/
@@ -73,6 +73,7 @@ public enum Dialect {
| Supports.READ_ONLY_UPDATE
| Supports.CONCURRENCY
| Supports.JAVA_TIME
+ | Supports.ILIKE
| Supports.SRID),
/**
@@ -137,6 +138,8 @@ public enum Dialect {
/**
* Whether this dialect supports table inheritance.
+ *
+ * @return whether the database supports table inheritance.
*/
public final boolean supportsTableInheritance() {
return (flags & Supports.TABLE_INHERITANCE) != 0;
@@ -146,6 +149,7 @@ public enum Dialect {
* {@code true} if child tables inherit the index of their parent tables.
* This feature is not yet supported in PostgreSQL.
*
+ * @return whether child tables inherit the index of their parent tables.
* @see <a href="https://issues.apache.org/jira/browse/SIS-358";>SIS-358</a>
*/
public final boolean supportsIndexInheritance() {
@@ -156,6 +160,7 @@ public enum Dialect {
* Whether this dialect supports adding table constraints after creation.
* This feature is not yet supported in SQLite.
*
+ * @return whether the database supports adding table constraints after
creation.
* @see DatabaseMetaData#supportsAlterTableWithAddColumn()
*/
public final boolean supportsAlterTableWithAddConstraint() {
@@ -170,6 +175,8 @@ public enum Dialect {
* {@snippet lang="sql" :
* GRANT USAGE ON SCHEMA metadata TO PUBLIC;
* }
+ *
+ * @return whether the database supports {@code "GRANT USAGE ON SCHEMA"}
statements.
*/
public final boolean supportsGrantUsageOnSchema() {
return (flags & Supports.GRANT_USAGE_ON_SCHEMA) != 0;
@@ -184,6 +191,7 @@ public enum Dialect {
* GRANT SELECT ON TABLE "Coordinate Reference System" TO PUBLIC;
* }
*
+ * @return whether the database supports {@code "GRANT SELECT ON TABLE"}
statements.
*/
public final boolean supportsGrantSelectOnTable() {
return (flags & Supports.GRANT_SELECT_ON_TABLE) != 0;
@@ -191,6 +199,8 @@ public enum Dialect {
/**
* Whether this dialect supports all the {@code "GRANT … ON …"} features
documented in this class.
+ *
+ * @return whether the database supports the {@code "GRANT … ON …"}
features.
*/
public final boolean supportsAllGrants() {
return (flags & (Supports.GRANT_USAGE_ON_SCHEMA |
Supports.GRANT_SELECT_ON_TABLE))
@@ -204,33 +214,40 @@ public enum Dialect {
* {@snippet lang="sql" :
* COMMENT ON SCHEMA metadata IS 'ISO 19115 metadata';
* }
+ *
+ * @return whether the database supports the {@code COMMENT} statement.
*/
public final boolean supportsComment() {
return (flags & Supports.COMMENT) != 0;
}
/**
- * Whether the JDBC driver supports configuring read-only mode on
connection instances.
+ * Whether the <abbr>JDBC</abbr> driver supports configuring read-only
mode on connection instances.
* This feature is not supported in SQLite.
+ *
+ * @return whether the database supports configuring read-only mode on
connection instances.
*/
public final boolean supportsReadOnlyUpdate() {
return (flags & Supports.READ_ONLY_UPDATE) != 0;
}
/**
- * Whether the JDBC driver supports concurrent transactions.
+ * Whether the <abbr>JDBC</abbr> driver supports concurrent transactions.
* This feature is not well supported in SQLite.
+ *
+ * @return whether the database supports concurrent transactions.
*/
public final boolean supportsConcurrency() {
return (flags & Supports.CONCURRENCY) != 0;
}
/**
- * Whether the JDBC driver supports conversions from objects to {@code
java.time} API.
+ * Whether the <abbr>JDBC</abbr> driver supports conversions from objects
to {@code java.time} API.
* The JDBC 4.2 specification provides a mapping from {@link
java.sql.Types} to temporal objects.
* The specification suggests that {@link
java.sql.ResultSet#getObject(int, Class)} should accept
* those temporal types in the {@link Class} argument, but not all drivers
support that.
*
+ * @return whether the database supports conversions from objects to
{@code java.time} <abbr>API</abbr>.
* @see <a
href="https://jcp.org/aboutJava/communityprocess/maintenance/jsr221/JDBC4.2MR-January2014.pdf";>JDBC
Maintenance Release 4.2</a>
*/
public final boolean supportsJavaTime() {
@@ -239,13 +256,25 @@ public enum Dialect {
/**
* Whether the spatial extension supports <abbr>SRID</abbr> in {@code
ST_*} functions.
+ *
+ * @return whether the database supports <abbr>SRID</abbr> in {@code ST_*}
functions.
*/
public final boolean supportsSRID() {
return (flags & Supports.SRID) != 0;
}
/**
- * Returns the presumed SQL dialect.
+ * 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>.
+ *
+ * @return case-insensitive {@code LIKE}, or {@code null} if none.
+ */
+ public final String caseInsensitiveLike() {
+ return (flags & Supports.ILIKE) != 0 ? "ILIKE" : null;
+ }
+
+ /**
+ * Returns the presumed <abbr>SQL</abbr> dialect.
* If this method cannot guess the dialect, than {@link #ANSI} is presumed.
*
* @param metadata the database metadata.
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 bd2e1af418..7e70bfb436 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
@@ -29,7 +29,7 @@ import org.apache.sis.util.internal.shared.Strings;
/**
- * Utility methods for building SQL statements.
+ * Utility methods for building <abbr>SQL</abbr> statements.
* This class is for internal purpose only and may change or be removed in any
future SIS version.
*
* @author Martin Desruisseaux (Geomatys)
@@ -56,6 +56,11 @@ public class SQLBuilder extends Syntax {
*/
public static final String DELETE = "DELETE FROM ";
+ /**
+ * The {@value} keyword.
+ */
+ public static final String NULL = "NULL";
+
/**
* The buffer where the SQL query is created.
*/
@@ -148,7 +153,7 @@ public class SQLBuilder extends Syntax {
/**
* Appends the given text verbatim.
- * The text should be SQL keywords like {@code "SELECT * FROM"}.
+ * The text should be <abbr>SQL</abbr> keywords like {@code "SELECT *
FROM"}.
*
* @param keyword the keyword to append verbatim.
* @return this builder, for method call chaining.
@@ -253,7 +258,7 @@ public class SQLBuilder extends Syntax {
* @return this builder, for method call chaining.
*/
public final SQLBuilder appendValue(final String value) {
- return (value == null) ? append("NULL") :
append('\'').append(value.replace("'", "''")).append('\'');
+ 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/Supports.java
b/endorsed/src/org.apache.sis.metadata/main/org/apache/sis/metadata/sql/internal/shared/Supports.java
index 9e0fe48e4b..bf4b3e9765 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
@@ -81,10 +81,15 @@ final class Supports {
*/
public static final int JAVA_TIME = 0x100;
+ /**
+ * Whether the database supports the {@code ILIKE} function.
+ */
+ public static final int ILIKE = 0x200;
+
/**
* Whether the spatial extension supports <abbr>SRID</abbr> in {@code
ST_*} functions.
*/
- public static final int SRID = 0x200;
+ public static final int SRID = 0x400;
/**
* Do not allow instantiation of this class.
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClause.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClause.java
index 785f296093..fb0355910e 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClause.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClause.java
@@ -37,6 +37,7 @@ import org.apache.sis.util.Workaround;
// Specific to the geoapi-3.1 and geoapi-4.0 branches:
import org.opengis.feature.Feature;
import org.opengis.filter.Filter;
+import org.opengis.filter.LikeOperator;
import org.opengis.filter.ValueReference;
@@ -110,6 +111,14 @@ public final class SelectionClause extends SQLBuilder {
*/
private JDBCType functionReturnType;
+ /**
+ * If this builder is writing the patten of a {@code LIKE} operator, the
operator.
+ * Otherwise, {@code null}.
+ *
+ * @see #appendLiteral(Object)
+ */
+ LikeOperator<?> writingPattenOf;
+
/**
* Flag sets to {@code true} if a filter or expression cannot be converted
to SQL.
* When a SQL string become flagged as invalid, it is truncated to the
length that
@@ -181,10 +190,17 @@ public final class SelectionClause extends SQLBuilder {
}
/**
- * Writes a literal value, or marks this SQL as invalid if the value
cannot be formatted.
+ * Writes a literal value, or marks this <abbr>SQL</abbr> as invalid if
the value cannot be formatted.
+ * The value can be {@code null}, a character string, a Boolean, a number,
a temporal object,
+ * a geographic bounding box or an envelope. If the given object cannot be
formatted,
+ * then this clause is {@linkplain #invalidate() marked as invalid}.
*/
final void appendLiteral(final Object value) {
- if (value instanceof GeographicBoundingBox) {
+ if (value == null) {
+ append(NULL);
+ } else if (value instanceof CharSequence) {
+ appendLiteralOrPattern(value.toString());
+ } else if (value instanceof GeographicBoundingBox) {
appendGeometry(null, new GeneralEnvelope((GeographicBoundingBox)
value));
} else if (value instanceof Envelope) {
appendGeometry(null, (Envelope) value);
@@ -195,6 +211,63 @@ public final class SelectionClause extends SQLBuilder {
}
}
+ /**
+ * Appends a literal which may be the pattern in a {@code LIKE ? ESCAPE
'\'} <abbr>SQL</abbr> fragment.
+ * This method appends also the {@code ESCAPE} fragment if it appears to
be necessary. If the given text
+ * cannot be formatted, then this clause is {@linkplain #invalidate()
marked as invalid}.
+ *
+ * @param text the literal or pattern to add, or {@code null}.
+ */
+ private void appendLiteralOrPattern(String text) {
+ @SuppressWarnings("LocalVariableHidesMemberVariable")
+ final LikeOperator<?> writingPattenOf = this.writingPattenOf;
+ if (writingPattenOf == null || text == null) {
+ appendValue(text);
+ return;
+ }
+ final char escape = writingPattenOf.getEscapeChar();
+ StringBuilder replacement = null;
+ boolean s = false;
+ do {
+ final char source, target;
+ if (s) {target = '_'; source = writingPattenOf.getSingleChar();}
+ else {target = '%'; source = writingPattenOf.getWildCard();}
+ if (source == escape || target == escape) {
+ invalidate();
+ return;
+ }
+ if (source != target) {
+ int i = text.indexOf(source);
+ while (i >= 0) {
+ // Search the first previous character which is not an
escape character.
+ int p = i;
+ while (--p >= 0) {
+ if (text.charAt(p) != escape) break;
+ }
+ /*
+ * The wildcard is not escaped in the number of escape
characters is even.
+ * That number is `(i-1) - p`, replaced by checking
whether `i - p` is odd.
+ */
+ if (((i - p) & 1) != 0) {
+ if (replacement == null) {
+ replacement = new StringBuilder(text);
+ }
+ replacement.setCharAt(i, target);
+ }
+ i = text.indexOf(source, i + 1);
+ }
+ }
+ } while ((s = !s) == true);
+ if (replacement != null) {
+ text = replacement.toString();
+ }
+ appendValue(text);
+ final String customEscape = String.valueOf(escape);
+ if (!customEscape.equals(wildcardEscape)) {
+ append(" ESCAPE ").appendValue(customEscape);
+ }
+ }
+
/**
* Appends the given geometry in Well-Known Text (WKT) format.
* Exactly one of the given argument should be non-null.
diff --git
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClauseWriter.java
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClauseWriter.java
index 750d6e6095..114a2fff87 100644
---
a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClauseWriter.java
+++
b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/SelectionClauseWriter.java
@@ -43,6 +43,7 @@ import org.opengis.filter.LogicalOperator;
import org.opengis.filter.LogicalOperatorName;
import org.opengis.filter.ComparisonOperatorName;
import org.opengis.filter.BinaryComparisonOperator;
+import org.opengis.filter.LikeOperator;
import org.opengis.filter.SpatialOperatorName;
import org.opengis.filter.BetweenComparisonOperator;
import org.opengis.filter.ResourceId;
@@ -50,7 +51,7 @@ import org.apache.sis.filter.base.XPathSource;
/**
- * Converter from filters/expressions to the {@code WHERE} part of SQL
statement.
+ * Converter from filters/expressions to the {@code WHERE} part of
<abbr>SQL</abbr> statement.
* This base class handles ANSI compliant SQL. Subclasses can add
database-specific syntax.
*
* <p>As soon as a filter or expression is not supported by this interpreter,
the writing
@@ -74,7 +75,7 @@ public class SelectionClauseWriter extends Visitor<Feature,
SelectionClause> {
protected static final SelectionClauseWriter DEFAULT = new
SelectionClauseWriter();
/**
- * Creates a new converter from filters/expressions to SQL.
+ * Creates a new converter from filters/expressions to <abbr>SQL</abbr>.
*/
private SelectionClauseWriter() {
setFilterHandler(LogicalOperatorName.AND, new Logic(" AND ", false));
@@ -86,14 +87,14 @@ public class SelectionClauseWriter extends Visitor<Feature,
SelectionClause> {
setFilterHandler(ComparisonOperatorName.PROPERTY_IS_GREATER_THAN_OR_EQUAL_TO,
new Comparison(" >= "));
setFilterHandler(ComparisonOperatorName.PROPERTY_IS_LESS_THAN,
new Comparison(" < "));
setFilterHandler(ComparisonOperatorName.PROPERTY_IS_LESS_THAN_OR_EQUAL_TO,
new Comparison(" <= "));
-
setFilterHandler(ComparisonOperatorName.valueOf(FunctionNames.PROPERTY_IS_BETWEEN),
(f,sql) -> {
+
setFilterHandler(ComparisonOperatorName.valueOf(FunctionNames.PROPERTY_IS_BETWEEN),
(f, sql) -> {
final var filter = (BetweenComparisonOperator<Feature>) f;
/* Nothing to append */ if (write(sql, filter.getExpression()))
return;
sql.append(" BETWEEN "); if (write(sql,
filter.getLowerBoundary())) return;
sql.append(" AND "); write(sql, filter.getUpperBoundary());
sql.declareFunction(JDBCType.BOOLEAN);
});
- setFilterHandler(FunctionNames.resourceId(), (f,sql) -> {
+ setFilterHandler(FunctionNames.resourceId(), (f, sql) -> {
if (f instanceof XPathSource &&
sql.appendColumnName(((XPathSource) f).getXPath())) {
final var filter = (ResourceId<?>) f;
sql.append(" = ").appendValue(filter.getIdentifier());
@@ -112,6 +113,28 @@ public class SelectionClauseWriter extends
Visitor<Feature, SelectionClause> {
sql.invalidate();
}
});
+
setFilterHandler(ComparisonOperatorName.valueOf(FunctionNames.PROPERTY_IS_LIKE),
(f, sql) -> {
+ final var filter = (LikeOperator<Feature>) f;
+ final boolean isMatchingCase = filter.isMatchingCase();
+ String keyword = "LIKE";
+ if (isMatchingCase || (keyword =
sql.dialect.caseInsensitiveLike()) != null) {
+ final List<Expression<Feature, ?>> parameters =
filter.getExpressions();
+ if (parameters.size() == 2) {
+ write(sql, parameters.get(0));
+ sql.append(' ').append(keyword).append(' ');
+ final LikeOperator<?> old = sql.writingPattenOf;
+ try {
+ sql.writingPattenOf = filter;
+ write(sql, parameters.get(1));
+ } finally {
+ sql.writingPattenOf = old;
+ }
+ sql.declareFunction(JDBCType.BOOLEAN);
+ return;
+ }
+ }
+ sql.invalidate();
+ });
/*
* Spatial filters.
*/
@@ -134,8 +157,8 @@ public class SelectionClauseWriter extends Visitor<Feature,
SelectionClause> {
setExpressionHandler(FunctionNames.Subtract, new Arithmetic(" - "));
setExpressionHandler(FunctionNames.Divide, new Arithmetic(" / "));
setExpressionHandler(FunctionNames.Multiply, new Arithmetic(" * "));
- setExpressionHandler(FunctionNames.Literal, (e,sql) ->
sql.appendLiteral(((Literal<Feature,?>) e).getValue()));
- setExpressionHandler(FunctionNames.ValueReference, (e,sql) ->
sql.appendColumnName(((ValueReference<Feature,?>) e).getXPath()));
+ setExpressionHandler(FunctionNames.Literal, (e, sql) ->
sql.appendLiteral(((Literal<Feature,?>) e).getValue()));
+ setExpressionHandler(FunctionNames.ValueReference, (e, sql) ->
sql.appendColumnName(((ValueReference<Feature,?>) e).getXPath()));
setExpressionHandler(FunctionNames.PropertyName,
getExpressionHandler(FunctionNames.ValueReference));
}
diff --git
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/SQLStoreTest.java
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/SQLStoreTest.java
index bf3cc4110e..e0f04f8e94 100644
---
a/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/SQLStoreTest.java
+++
b/endorsed/src/org.apache.sis.storage.sql/test/org/apache/sis/storage/sql/SQLStoreTest.java
@@ -195,6 +195,7 @@ public final class SQLStoreTest extends TestOnAllDatabases {
verifySimpleWhere(store);
verifyWhereOnLink(store);
verifyLinkInProjection(store);
+ verifyLike(store);
verifyStreamOperations(store.findResource("Cities"));
}
canada = null;
@@ -453,7 +454,7 @@ public final class SQLStoreTest extends TestOnAllDatabases {
try (Stream<Feature> features = cities.subset(query).features(false)) {
result = features.map(f ->
f.getPropertyValue("native_name")).toArray();
}
- assertSetEquals(Arrays.asList("Montréal", "Québec"),
Arrays.asList(result));
+ assertSetEquals(List.of("Montréal", "Québec"), Arrays.asList(result));
}
/**
@@ -509,6 +510,23 @@ public final class SQLStoreTest extends TestOnAllDatabases
{
assertSetEquals(List.of("Canada", "France", "日本"), names);
}
+ /**
+ * Requests a new set of features filtered by a {@code LIKE} condition.
+ *
+ * @param dataset the store on which to query the features.
+ * @throws DataStoreException if an error occurred during query execution.
+ */
+ private void verifyLike(final SimpleFeatureStore dataset) throws Exception
{
+ final FeatureQuery query = new FeatureQuery();
+ query.setSelection(FF.like(FF.property("english_name"), "M#o?t*al",
'*', '?', '#', false));
+ final FeatureSet cities = dataset.findResource("Cities");
+ final Object[] result;
+ try (Stream<Feature> features = cities.subset(query).features(false)) {
+ result = features.map(f ->
f.getPropertyValue("english_name")).toArray();
+ }
+ assertSetEquals(List.of("Montreal"), Arrays.asList(result));
+ }
+
/**
* Checks that operations stacked on feature stream are well executed.
* This test focuses on mapping and peeking actions overloaded by
<abbr>SQL</abbr> streams.
