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 772ea5c3fa75ffeea61f042e7146671b0e2e5d5a Author: Martin Desruisseaux <[email protected]> AuthorDate: Fri Nov 7 17:38:21 2025 +0100 When possible, replace Java expressions by SQL functions in the `SELECT` part of the query. Before this commit, such replacements were done in the `WHERE` part only. --- .../feature/internal/shared/FeatureProjection.java | 82 +++++++++++- .../metadata/sql/internal/shared/SQLBuilder.java | 4 +- .../org/apache/sis/storage/sql/feature/Column.java | 13 +- .../sis/storage/sql/feature/ComputedColumn.java | 88 +++++++++++++ .../apache/sis/storage/sql/feature/Database.java | 13 +- .../sis/storage/sql/feature/FeatureAdapter.java | 24 ++-- .../sis/storage/sql/feature/FeatureIterator.java | 31 ++--- .../sis/storage/sql/feature/FeatureStream.java | 83 +++++++++++-- .../apache/sis/storage/sql/feature/Relation.java | 2 +- .../sis/storage/sql/feature/SelectionClause.java | 52 +++++++- .../storage/sql/feature/SelectionClauseWriter.java | 138 +++++++++++++-------- .../org/apache/sis/storage/sql/feature/Table.java | 11 +- .../sis/storage/sql/feature/ValueGetter.java | 2 +- .../storage/sql/postgis/ExtendedClauseWriter.java | 2 + .../apache/sis/storage/sql/postgis/Postgres.java | 6 +- 15 files changed, 431 insertions(+), 120 deletions(-) diff --git a/endorsed/src/org.apache.sis.feature/main/org/apache/sis/feature/internal/shared/FeatureProjection.java b/endorsed/src/org.apache.sis.feature/main/org/apache/sis/feature/internal/shared/FeatureProjection.java index fb0e0c6b4c..5efe2dc376 100644 --- a/endorsed/src/org.apache.sis.feature/main/org/apache/sis/feature/internal/shared/FeatureProjection.java +++ b/endorsed/src/org.apache.sis.feature/main/org/apache/sis/feature/internal/shared/FeatureProjection.java @@ -16,11 +16,14 @@ */ package org.apache.sis.feature.internal.shared; +import java.util.Arrays; import java.util.List; import java.util.Set; import java.util.Map; import java.util.LinkedHashMap; +import java.util.Objects; import java.util.Optional; +import java.util.function.BiFunction; import java.util.function.UnaryOperator; import org.apache.sis.util.Debug; import org.apache.sis.util.ArraysExt; @@ -33,6 +36,7 @@ import org.apache.sis.io.TableAppender; import org.opengis.feature.Feature; import org.opengis.feature.FeatureType; import org.opengis.filter.Expression; +import org.opengis.filter.Literal; import org.opengis.filter.ValueReference; @@ -119,7 +123,7 @@ public final class FeatureProjection implements UnaryOperator<Feature> { * Creates a new projection with a subset of the properties of another projection. * This constructor is invoked when the caller handles itself some of the properties. * - * <h4>behavioral change</h4> + * <h4>Behavioral change</h4> * Projections created by this constructor assumes that the feature instances given to the * {@link #apply(Feature)} method are already instances of {@link #typeWithDependencies} * and can be modified (if needed) in place. This constructor is designed for cases where @@ -146,7 +150,7 @@ public final class FeatureProjection implements UnaryOperator<Feature> { /** * Returns a variant of this projection where the caller has created the target feature instance itself. - * The callers is may have set some property values itself, and the {@code remaining} argument gives the + * The callers may have set some property values itself, and the {@code remaining} argument gives the * indexes of the properties that still need to be copied after caller's processing. * * @param remaining index of the properties that still need to be copied after the caller did its processing. @@ -160,6 +164,42 @@ public final class FeatureProjection implements UnaryOperator<Feature> { return new FeatureProjection(this, remaining); } + /** + * Creates a new projection with the same properties as the source projection, but modified expressions. + * + * @param source the projection to copy. + * @param mapper a function receiving in arguments the property name and the expression fetching the property value, + * and returning the expression to use in replacement of the function given in argument. + */ + public FeatureProjection(final FeatureProjection source, + final BiFunction<String, Expression<? super Feature, ?>, Expression<? super Feature, ?>> mapper) + { + typeRequested = source.typeRequested; + createInstance = source.createInstance; + propertiesToCopy = source.propertiesToCopy; + expressions = source.expressions.clone(); + for (int i = 0; i < expressions.length; i++) { + expressions[i] = mapper.apply(propertiesToCopy[i], expressions[i]); + } + // TODO: check if we can remove some dependencies. + typeWithDependencies = source.typeWithDependencies; + } + + /** + * Returns {@code true} if this projection contains at least one expression + * which is not a value reference or a literal. + * + * @return whether this projection is presumed to perform some operations. + */ + public final boolean hasOperations() { + for (final var expression : expressions) { + if (!(expression instanceof ValueReference<?,?> || expression instanceof Literal<?,?>)) { + return true; + } + } + return false; + } + /** * Returns the names of all stored properties. This list may be shorter than the list of properties of the * {@linkplain #typeRequested requested feature type} if some feature properties are computed on-the-fly, @@ -171,6 +211,16 @@ public final class FeatureProjection implements UnaryOperator<Feature> { return UnmodifiableArrayList.wrap(propertiesToCopy); } + /** + * Returns the expression which is executed for fetching the property value at the given index. + * + * @param index index of the stored property for which to get the expression for fething the value. + * @return the expression which is executed for fetching the property value at the given index. + */ + public final Expression<? super Feature, ?> expression(final int index) { + return expressions[index]; + } + /** * Returns the path to the value (in source features) of the property at the given index. * The argument corresponds to an index in the list returned by {@link #propertiesToCopy()}. @@ -338,4 +388,32 @@ public final class FeatureProjection implements UnaryOperator<Feature> { */ private String type; } + + /** + * Computes a hash code value for the projection. + */ + @Override + public int hashCode() { + return Objects.hash(typeRequested, typeWithDependencies, createInstance) + + 97 * (Arrays.hashCode(propertiesToCopy) + 97 * Arrays.hashCode(expressions)); + } + + /** + * Compares this projection with the given object for equality. + * + * @param obj the object to compare with this projection. + * @return whether the two objects are equal. + */ + @Override + public boolean equals(final Object obj) { + if (obj instanceof FeatureProjection) { + final var other = (FeatureProjection) obj; + return (createInstance == other.createInstance) + && typeRequested.equals(other.typeRequested) + && typeWithDependencies.equals(other.typeWithDependencies) + && Arrays.equals(propertiesToCopy, other.propertiesToCopy) + && Arrays.equals(expressions, other.expressions); + } + return false; + } } 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 b1bd4e096d..bd2e1af418 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 @@ -91,11 +91,11 @@ public class SQLBuilder extends Syntax { } /** - * Clears this builder and make it ready for creating a new SQL statement. + * Clears this builder and makes it ready for creating a new <abbr>SQL</abbr> statement. * * @return this builder, for method call chaining. */ - public final SQLBuilder clear() { + public SQLBuilder clear() { buffer.setLength(0); return this; } 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 837a9d59c1..fefc2b0473 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 @@ -51,7 +51,7 @@ import org.apache.sis.util.resources.Errors; * @see ResultSet#getMetaData() * @see DatabaseMetaData#getColumns(String, String, String, String) */ -public final class Column implements Cloneable { +public class Column implements Cloneable { /** * Name of the column as declared in the table. * @@ -139,14 +139,15 @@ public final class Column implements Cloneable { ValueGetter<?> valueGetter; /** - * Creates a synthetic column (a column not inferred from database analysis) - * for describing the type of elements in an array. + * Creates a column of the given name and type. + * This constructor is used for columns that are not inferred from database analysis. * - * @param type SQL type of the column. + * @param type SQL type of the column as one of the constants enumerated in {@link Types} class. * @param typeName SQL name of the type. + * @param name the column name, also used as the property name. */ - Column(final int type, final String typeName) { - this.name = label = propertyName = "element"; + Column(final int type, final String typeName, final String name) { + this.name = label = propertyName = name; this.type = type; this.typeName = typeName; this.precision = 0; diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ComputedColumn.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ComputedColumn.java new file mode 100644 index 0000000000..e6ca0de706 --- /dev/null +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ComputedColumn.java @@ -0,0 +1,88 @@ +/* + * 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.storage.sql.feature; + +import java.sql.JDBCType; + +// Specific to the geoapi-3.1 and geoapi-4.0 branches: +import org.opengis.feature.Feature; +import org.opengis.filter.Expression; +import org.opengis.filter.ValueReference; + + +/** + * A column which is the result of a computation. + * + * @author Martin Desruisseaux (Geomatys) + */ +final class ComputedColumn extends Column implements ValueReference<Feature, Object> { + /** + * The <abbr>SQL</abbr> fragment to use for querying this column. + */ + final String sql; + + /** + * Creates a column of the given name and type. + * + * <h4>API design note</h4> + * The {@code type} argument is restricted to the {@link JDBCType} enumeration instead of the more generic + * {@link java.sql.SQLType} interface because we assumes that {@code getVendorTypeNumber()} are constants + * from the {@link java.sql.Types} class. + * + * @param type type of the column. + * @param name the column name, also used as the property name. + * @param sql the <abbr>SQL</abbr> fragment to use for querying this column. + */ + ComputedColumn(final Database<?> database, final JDBCType type, final String name, final String sql) { + super(type.getVendorTypeNumber(), type.getName(), name); + this.sql = sql; + valueGetter = database.getDefaultMapping(); + } + + /** + * Returns the type of object expected by this expression. + */ + @Override + public Class<Feature> getResourceClass() { + return Feature.class; + } + + /** + * Returns the name of the property where the value will be stored. + * This is a simple property name, not an XPath. + */ + @Override + public String getXPath() { + return name; + } + + /** + * Fetches the property value from the given feature. + */ + @Override + public Object apply(final Feature instance) { + return instance.getPropertyValue(name); + } + + /** + * Should not be invoked in the context of the <abbr>SQL</abbr> store. + */ + @Override + public <N> Expression<Feature, N> toValueType(final Class<N> type) { + throw new ClassCastException(); + } +} 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 0068ca915a..43a30133fa 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 @@ -31,6 +31,7 @@ import java.util.logging.LogRecord; import java.util.function.Consumer; import java.util.concurrent.locks.ReadWriteLock; import java.sql.Array; +import java.sql.JDBCType; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; @@ -725,8 +726,8 @@ public class Database<G> extends Syntax { } } case Types.ARRAY: { - final int componentType = getArrayComponentType(columnDefinition); - final ValueGetter<?> component = getMapping(new Column(componentType, columnDefinition.typeName)); + final int componentType = getArrayComponentType(columnDefinition).getVendorTypeNumber(); + final ValueGetter<?> component = getMapping(new Column(componentType, columnDefinition.typeName, columnDefinition.name)); if (component == ValueGetter.AsObject.INSTANCE) { return ValueGetter.AsArray.INSTANCE; } @@ -749,9 +750,9 @@ public class Database<G> extends Syntax { } /** - * Returns the type of components in SQL arrays stored in a column. + * Returns the type of components in <abbr>SQL</abbr> arrays stored in a column. * This method is invoked when {@link Column#type} = {@link Types#ARRAY}. - * The default implementation returns {@link Types#OTHER} because JDBC + * The default implementation returns {@link JDBCType#OTHER} because <abbr>JDBC</abbr> * column metadata does not provide information about component types. * Database-specific subclasses should override this method if they can * provide that information from the {@link Column#typeName} value. @@ -761,8 +762,8 @@ public class Database<G> extends Syntax { * * @see Array#getBaseType() */ - protected int getArrayComponentType(final Column columnDefinition) { - return Types.OTHER; + protected JDBCType getArrayComponentType(final Column columnDefinition) { + return JDBCType.OTHER; } /** diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureAdapter.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureAdapter.java index 90da3bf591..413665c3cf 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureAdapter.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureAdapter.java @@ -38,9 +38,9 @@ import org.opengis.feature.FeatureType; /** * Converter of {@link ResultSet} rows to {@link Feature} instances. - * Each {@code FeatureAdapter} instance is specific to the set of rows given by a SQL query, + * Each {@code FeatureAdapter} instance is specific to the set of rows given by a <abbr>SQL</abbr> query, * ignoring {@code DISTINCT}, {@code ORDER BY} and filter conditions in the {@code WHERE} clause. - * This class does not hold JDBC resources; {@link ResultSet} must be provided by the caller. + * This class does not hold <abbr>JDBC</abbr> resources. Instead, the {@link ResultSet} is given by the caller. * This object can be prepared once and reused every time the query needs to be executed. * * <h2>Multi-threading</h2> @@ -176,7 +176,7 @@ final class FeatureAdapter { if (column.getGeometryType().isPresent()) { function = table.database.getGeometryEncodingFunction(column); } - appendColumn(sql, table.database, function, column.label, columnIndices); + appendColumn(sql, table.database, function, column, column.label, columnIndices); } /* * Collect information about associations in local arrays before to assign @@ -278,23 +278,29 @@ final class FeatureAdapter { * @param sql the SQL statement where to add column identifiers after the {@code SELECT} clause. * @param database the database. May be {@code null} if {@code function} is null. * @param function a function for which the column is an argument, or {@code null} if none. - * @param column name of the column to add. + * @param column the object that provide the definition of the column, or {@code null} if none. + * @param columnName name of the column to add. * @param columnIndices map where to add the mapping from column name to 1-based column index. */ private static int appendColumn(final SQLBuilder sql, final Database<?> database, final String function, - final String column, final Map<String,Integer> columnIndices) throws InternalDataStoreException + final Column column, final String columnName, final Map<String,Integer> columnIndices) + throws InternalDataStoreException { int columnCount = columnIndices.size(); if (columnCount != 0) sql.append(", "); if (function != null) { sql.appendIdentifier(database.catalogOfSpatialTables, database.schemaOfSpatialTables, function, false).append('('); } - sql.appendIdentifier(column); + if (column instanceof ComputedColumn) { + sql.append(((ComputedColumn) column).sql); + } else { + sql.appendIdentifier(columnName); + } if (function != null) { sql.append(')'); } - if (columnIndices.put(column, ++columnCount) == null) return columnCount; - throw new InternalDataStoreException(Resources.format(Resources.Keys.DuplicatedColumn_1, column)); + if (columnIndices.put(columnName, ++columnCount) == null) return columnCount; + throw new InternalDataStoreException(Resources.format(Resources.Keys.DuplicatedColumn_1, columnName)); } /** @@ -315,7 +321,7 @@ final class FeatureAdapter { final int[] indices = new int[columns.size()]; for (final String column : columns) { final Integer pos = columnIndices.get(column); - indices[i++] = (pos != null) ? pos : appendColumn(sql, null, null, column, columnIndices); + indices[i++] = (pos != null) ? pos : appendColumn(sql, null, null, null, column, columnIndices); } return indices; } diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureIterator.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureIterator.java index c2203eeb84..53cafd84e7 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureIterator.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureIterator.java @@ -111,9 +111,11 @@ final class FeatureIterator implements Spliterator<Feature>, AutoCloseable { private final FeatureProjection projection; /** - * Creates a new iterator over features. + * Creates a new iterator over features represented by the give table. + * Note that despite its name, the {@code table} argument may be a subset of a table + * (a "projection" in <abbr>SQL</abbr> sense) or a query (a kind of virtual table). * - * @param table the source table. + * @param table the source table, <abbr>SQL</abbr> projection or query. * @param connection connection to the database, used for creating the statement. * @param distinct whether the set should contain distinct feature instances. * @param selection condition to append, not including the {@code WHERE} keyword. @@ -122,24 +124,20 @@ final class FeatureIterator implements Spliterator<Feature>, AutoCloseable { * @param count maximum number of rows to return, or 0 (not -1) for no limit. * @param projection additional properties to compute, or {@code null} if none. */ - FeatureIterator(final Table table, - final Connection connection, - final boolean distinct, - final SelectionClause selection, + FeatureIterator(final Table table, + final Connection connection, + final InfoStatements spatialInformation, + final boolean distinct, + final SelectionClause selection, final SortBy<? super Feature> sort, - final long offset, - final long count, + final long offset, + final long count, final FeatureProjection projection) throws Exception { adapter = table.adapter(connection); String sql = adapter.sql; // Will be completed below with `WHERE` clause if needed. - - if (table.database.getSpatialSchema().isPresent()) { - spatialInformation = table.database.createInfoStatements(connection); - } else { - spatialInformation = null; - } + this.spatialInformation = spatialInformation; final String filter = (selection != null) ? selection.query(connection, spatialInformation) : null; if (distinct || filter != null || sort != null || (offset | count) != 0) { final var builder = new SQLBuilder(table.database); @@ -184,13 +182,8 @@ final class FeatureIterator implements Spliterator<Feature>, AutoCloseable { /** * Creates a new iterator over the dependencies of a feature. * - * @param table the source table, or {@code null} if we are creating an iterator for a dependency. * @param adapter converter from a {@link ResultSet} row to a {@link Feature} instance. * @param connection connection to the database, used for creating statement. - * @param filter condition to append, not including the {@code WHERE} keyword. - * @param distinct whether the set should contain distinct feature instances. - * @param offset number of rows to skip in underlying SQL query, or ≤ 0 for none. - * @param count maximum number of rows to return, or ≤ 0 for no limit. */ private FeatureIterator(final FeatureAdapter adapter, final Connection connection, final InfoStatements spatialInformation) throws SQLException diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureStream.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureStream.java index 8805ce036a..cef33f702c 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureStream.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/FeatureStream.java @@ -27,6 +27,7 @@ import java.util.function.Predicate; import java.util.function.ToDoubleFunction; import javax.sql.DataSource; import java.sql.Connection; +import java.sql.JDBCType; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; @@ -78,6 +79,8 @@ final class FeatureStream extends DeferredStream<Feature> { * This is used for writing the content of the {@link SelectionClause}. * It is usually a singleton instance shared by all databases. * It is fetched when first needed. + * + * @see #getFilterToSQL() */ private SelectionClauseWriter filterToSQL; @@ -183,7 +186,6 @@ final class FeatureStream extends DeferredStream<Feature> { } if (selection == null) { selection = new SelectionClause(table); - filterToSQL = table.database.getFilterToSupportedSQL(); } /* * Simplify/optimize the filter (it may cause `include` or `exclude` filters to emerge) and try @@ -198,7 +200,7 @@ final class FeatureStream extends DeferredStream<Feature> { for (final var filter : optimization.applyAndDecompose((Filter<? super Feature>) predicate)) { if (filter == Filter.include()) continue; if (filter == Filter.exclude()) return empty(); - if (!selection.tryAppend(filterToSQL, filter)) { + if (!selection.tryAppend(getFilterToSQL(), filter)) { // Delegate to Java code all filters that we cannot translate to SQL statement. if (stream == this) { stream = super.filter(filter); @@ -427,31 +429,88 @@ final class FeatureStream extends DeferredStream<Feature> { @Override protected Spliterator<Feature> createSourceIterator() throws Exception { Table projected = table; + final Database<?> database = projected.database; + lock(database.transactionLocks); + final Connection connection = getConnection(); + setCloseHandler(connection); // Executed only if an exception occurs in the middle of this method. + makeReadOnly(connection); + /* + * Helper methods for spatial functions which expect or return geometry objects: + * find SRID codes of CRSs and conversely by searching in "spatial_ref_sys" table. + */ + final InfoStatements spatialInformation; + if (database.getSpatialSchema().isPresent()) { + spatialInformation = database.createInfoStatements(connection); + } else { + spatialInformation = null; + } + /* + * The `projection` is what the user requested by a call to `Stream.map(Function)`. + * The `queriedProjection` is what we will use for building a SQL query, possibly + * with some expressions replaced by SQL fragments. Then, `completion` is what we + * could not do in SQL and will need to finish in Java. + */ FeatureProjection completion = null; - if (projection != null) { + FeatureProjection queriedProjection = projection; + if (queriedProjection != null) { + /* + * First, if the projection contains expressions other than value references, + * try to replace them by SQL functions written in the "SELECT" statement. + */ + if (queriedProjection.hasOperations()) { + final var columnSQL = new SelectionClause(projected); + @SuppressWarnings("LocalVariableHidesMemberVariable") + final SelectionClauseWriter filterToSQL = getFilterToSQL(); + queriedProjection = new FeatureProjection(queriedProjection, (name, expression) -> { + final JDBCType type = filterToSQL.writeFunction(columnSQL, expression); + if (type != null) try { + columnSQL.append(" AS ").appendIdentifier(name); + expression = new ComputedColumn(database, type, name, + columnSQL.query(connection, spatialInformation)); + } catch (Exception e) { + throw cannotExecute(e); + } + columnSQL.clear(); + return expression; + }); + if (queriedProjection.equals(projection)) { + queriedProjection = projection; // No change, keep the original one. + } + } + /* + * Build a pseudo-table (a view) with the subset of columns specified by the projection. + */ final var unhandled = new BitSet(); final var reusedNames = new HashSet<String>(); - projected = new Table(projected, projection, reusedNames, unhandled); - completion = projection.afterPreprocessing(unhandled.stream().toArray()); + projected = new Table(projected, queriedProjection, reusedNames, unhandled); + completion = queriedProjection.afterPreprocessing(unhandled.stream().toArray()); if (completion != null && !reusedNames.containsAll(completion.dependencies())) { /* * Cannot use `projected` because some expressions need properties available only * in the source features. Request full feature instances from the original table. */ projected = table; - completion = projection; + completion = queriedProjection; } } - lock(projected.database.transactionLocks); - final Connection connection = getConnection(); - setCloseHandler(connection); // Executed only if `FeatureIterator` creation fails, discarded later otherwise. - makeReadOnly(connection); - final var features = new FeatureIterator(projected, connection, distinct, selection, sort, offset, count, completion); + final var features = new FeatureIterator(projected, connection, spatialInformation, + distinct, selection, sort, offset, count, completion); setCloseHandler(features); selection = null; // Let the garbage collector do its work. return features; } + /** + * Returns the converter from filters/expressions to the {@code WHERE} part of <abbr>SQL</abbr> statements. + * The value is cached for avoiding synchronization. + */ + private SelectionClauseWriter getFilterToSQL() { + if (filterToSQL == null) { + filterToSQL = table.database.getFilterToSupportedSQL(); + } + return filterToSQL; + } + /** * Returns a string representation of this stream for debugging purposes. * The returned string tells whether filtering and sorting are done using @@ -460,7 +519,7 @@ final class FeatureStream extends DeferredStream<Feature> { @Override public String toString() { return Strings.toString(getClass(), "table", table.name.table, - "predicates", hasPredicates ? (filterToSQL != null ? "mixed" : "Java") : (filterToSQL != null ? "SQL" : null), + "predicates", hasPredicates ? (filterToSQL != null ? "mixed" : "Java") : (selection != null ? "SQL" : null), "comparator", hasComparator ? (sort != null ? "mixed" : "Java") : (sort != null ? "SQL" : null), "distinct", distinct ? Boolean.TRUE : null, "offset", offset != 0 ? offset : null, diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Relation.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Relation.java index 8d5327042c..a97974f51c 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Relation.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Relation.java @@ -141,7 +141,7 @@ final class Relation extends TableReference implements Cloneable { * The columns of the other table that constitute a primary or foreigner key. Keys are the columns * of the other table and values are columns of the table containing this {@code Relation}. */ - private final Map<String,String> columns; + private final Map<String, String> columns; /** * The other table identified by {@link #catalog}, {@link #schema} and {@link #table} names. 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 7e10d67412..785f296093 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 @@ -21,6 +21,7 @@ import java.util.AbstractMap; import java.util.List; import java.util.ArrayList; import java.util.Optional; +import java.sql.JDBCType; import java.sql.Connection; import org.opengis.geometry.Envelope; import org.opengis.metadata.extent.GeographicBoundingBox; @@ -83,7 +84,7 @@ public final class SelectionClause extends SQLBuilder { * * Elements must be sorted in increasing order of keys. * - * @see #query(InfoStatements) + * @see #query(Connection, InfoStatements) */ private final List<Map.Entry<Integer, CoordinateReferenceSystem>> parameters; @@ -98,6 +99,17 @@ public final class SelectionClause extends SQLBuilder { */ private Optional<CoordinateReferenceSystem> columnCRS; + /** + * If the <abbr>SQL</abbr> fragment invokes a function, the return type of the topmost function. + * The topmost function is the one which contains all other functions as argument. + * This is the function producing the values that users will see. + * If this field is {@code null}, then the <abbr>SQL</abbr> fragment (if valid) is just a column identifier. + * + * @see #functionReturnType() + * @see #declareFunction(JDBCType) + */ + private JDBCType functionReturnType; + /** * 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 @@ -271,6 +283,25 @@ public final class SelectionClause extends SQLBuilder { return value; } + /** + * If an expression has been converted to a <abbr>SQL</abbr> function, returns the function return type. + * If {@code null}, the <abbr>SQL</abbr> fragment (if valid) is just a column identifier. + */ + final JDBCType functionReturnType() { + return isInvalid ? null : functionReturnType; + } + + /** + * Declares that the <abbr>SQL</abbr> fragment contains at least one function. + * Java methods that format a <abbr>SQL</abbr> fragment should invoke this method + * last for ensuring that the topmost function has precedence. + * + * @param returnType the return type of the function. + */ + public final void declareFunction(final JDBCType returnType) { + functionReturnType = returnType; + } + /** * Appends the name of a spatial function. The catalog and schema names are * included for making sure that it works even if the search path is not set. @@ -286,7 +317,7 @@ public final class SelectionClause extends SQLBuilder { } /** - * Tries to append a SQL statement for the given filter. + * Tries to append a <abbr>SQL</abbr> fragment for the given filter. * This method returns {@code true} on success, or {@code false} if the statement can no be written. * In the latter case, the content of this {@code SelectionClause} is unchanged. * @@ -328,6 +359,7 @@ public final class SelectionClause extends SQLBuilder { * Returns the <abbr>SQL</abbr> fragment built by this {@code SelectionClause}. * This method completes the information that we deferred until a connection is established. * + * @param connection connection to use for creating a default {@code spatialInformation}. * @param spatialInformation a cache of statements for fetching spatial information, or {@code null}. * @return the <abbr>SQL</abbr> fragment, or {@code null} if there is no {@code WHERE} clause to add. * @throws Exception if an SQL error, parsing error or other error occurred. @@ -337,7 +369,7 @@ public final class SelectionClause extends SQLBuilder { return null; } boolean close = false; - for (int i = parameters.size(); --i >= 0;) { + for (int i = parameters.size(); --i >= 0;) { // Reverse order is important. if (spatialInformation == null) { spatialInformation = table.database.createInfoStatements(connection); close = true; @@ -356,4 +388,18 @@ public final class SelectionClause extends SQLBuilder { } return buffer.toString(); } + + /** + * Clears this builder and makes it ready for creating a new <abbr>SQL</abbr> statement. + * + * @return this builder, for method call chaining. + */ + @Override + public final SQLBuilder clear() { + isInvalid = false; + functionReturnType = null; + clearColumnCRS(); + parameters.clear(); + return super.clear(); + } } 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 a2d748186c..04982ee265 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 @@ -24,6 +24,7 @@ import java.util.function.BiConsumer; import java.sql.Types; import java.sql.Connection; import java.sql.DatabaseMetaData; +import java.sql.JDBCType; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.sis.filter.base.XPathSource; @@ -90,20 +91,23 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { /* 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) -> { if (f instanceof XPathSource && sql.appendColumnName(((XPathSource) f).getXPath())) { final var filter = (ResourceId<?>) f; sql.append(" = ").appendValue(filter.getIdentifier()); + sql.declareFunction(JDBCType.BOOLEAN); } else { sql.invalidate(); } }); setNullAndNilHandlers((filter, sql) -> { - final List<Expression<Feature, ?>> expressions = filter.getExpressions(); - if (expressions.size() == 1) { - write(sql, expressions.get(0)); + final List<Expression<Feature, ?>> parameters = filter.getExpressions(); + if (parameters.size() == 1) { + write(sql, parameters.get(0)); sql.append(" IS NULL"); + sql.declareFunction(JDBCType.BOOLEAN); } else { sql.invalidate(); } @@ -136,10 +140,10 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { } /** - * Adds all values defined in the given enumeration as functions. + * Adds as functions all values defined by the specified enumeration. */ - private void addAllOf(final Class<? extends Enum<?>> functions) { - for (Enum<?> id : functions.getEnumConstants()) { + private <E extends Enum<E> & FunctionIdentifier> void addAllOf(final Class<E> functions) { + for (E id : functions.getEnumConstants()) { final String name = id.name(); setExpressionHandler(name, new Function(id)); } @@ -225,48 +229,46 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { for (final var entry : expressions.entrySet()) { final BiConsumer<Expression<Feature,?>, SelectionClause> handler = entry.getValue(); if (handler instanceof Function) { - final Enum<?> id = ((Function) handler).function; - if (id instanceof FunctionIdentifier) { - final int[] signature = ((FunctionIdentifier) id).getSignature(); // May be null. - boolean isSupported = false; - String specificName = ""; - String name = id.name(); - if (lowerCase) name = name.toLowerCase(Locale.US); - if (upperCase) name = name.toUpperCase(Locale.US); - try (ResultSet r = metadata.getFunctionColumns(null, null, name, "%")) { - while (r.next()) { - if (!specificName.equals(specificName = r.getString(Reflection.SPECIFIC_NAME))) { - if (isSupported) break; // Found a supported variant of the function. - isSupported = true; - } else if (!isSupported) { - continue; // Continue the search for the next overload variant. - } - switch (r.getShort(Reflection.COLUMN_TYPE)) { - case DatabaseMetaData.functionColumnIn: - case DatabaseMetaData.functionReturn: { - if (signature == null) continue; - final int n = r.getInt(Reflection.ORDINAL_POSITION); - if (n >= 0 && n < signature.length) { - int type = r.getInt(Reflection.DATA_TYPE); - switch (type) { - case Types.SMALLINT: // Derby does not support `TINYINT`. - case Types.TINYINT: - case Types.BIT: type = Types.BOOLEAN; break; - case Types.REAL: - case Types.FLOAT: type = Types.DOUBLE; break; - } - if (signature[n] == type) continue; + final FunctionIdentifier id = ((Function) handler).function; + final int[] signature = id.getSignature(); // May be null. + boolean isSupported = false; + String specificName = ""; + String name = id.name(); + if (lowerCase) name = name.toLowerCase(Locale.US); + if (upperCase) name = name.toUpperCase(Locale.US); + try (ResultSet r = metadata.getFunctionColumns(null, null, name, "%")) { + while (r.next()) { + if (!specificName.equals(specificName = r.getString(Reflection.SPECIFIC_NAME))) { + if (isSupported) break; // Found a supported variant of the function. + isSupported = true; + } else if (!isSupported) { + continue; // Continue the search for the next overload variant. + } + switch (r.getShort(Reflection.COLUMN_TYPE)) { + case DatabaseMetaData.functionColumnIn: + case DatabaseMetaData.functionReturn: { + if (signature == null) continue; + final int n = r.getInt(Reflection.ORDINAL_POSITION); + if (n >= 0 && n < signature.length) { + int type = r.getInt(Reflection.DATA_TYPE); + switch (type) { + case Types.SMALLINT: // Derby does not support `TINYINT`. + case Types.TINYINT: + case Types.BIT: type = Types.BOOLEAN; break; + case Types.REAL: + case Types.FLOAT: type = Types.DOUBLE; break; } + if (signature[n] == type) continue; } } - isSupported = false; - // Continue because the `ResultSet` may return many overload variants. } - } - if (!isSupported) { - unsupportedExpressions.add(entry.getKey()); + isSupported = false; + // Continue because the `ResultSet` may return many overload variants. } } + if (!isSupported) { + unsupportedExpressions.add(entry.getKey()); + } } } } catch (SQLException e) { @@ -340,6 +342,23 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { return sql.isInvalid(); } + /** + * Executes the registered action for the given expression. + * + * <h4>Note on type safety</h4> + * This method applies a theoretically unsafe cast, which is okay in the context of this class. + * See <cite>Note on parameterized type</cite> section in {@link Visitor#visit(Filter, Object)}. + * + * @param sql where to write the result of all actions. + * @param expression the expression for which to execute an action based on its type. + * @return value of {@link SelectionClause#functionReturnType()}. + */ + @SuppressWarnings("unchecked") + final JDBCType writeFunction(final SelectionClause sql, final Expression<? super Feature, ?> expression) { + visit((Expression<Feature, ?>) expression, sql); + return sql.functionReturnType(); + } + /** * Writes the expressions of a filter as a binary operator. * The filter must have exactly two expressions, otherwise the SQL will be declared invalid. @@ -355,15 +374,15 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { /** * Writes the parameters of a function or a binary operator. * - * @param sql where to append the SQL clause. - * @param expressions the expressions to write. - * @param separator the separator to insert between expression. - * @param binary whether the list of expressions shall contain exactly 2 elements. + * @param sql where to append the SQL clause. + * @param parameters the expressions to write as parameters. + * @param separator the separator to insert between expression. + * @param binary whether the list of expressions shall contain exactly 2 elements. */ - private void writeParameters(final SelectionClause sql, final List<Expression<Feature,?>> expressions, + private void writeParameters(final SelectionClause sql, final List<Expression<Feature,?>> parameters, final String separator, final boolean binary) { - final int n = expressions.size(); + final int n = parameters.size(); if (binary && n != 2) { sql.invalidate(); return; @@ -372,7 +391,7 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { sql.append('('); for (int i=0; i<n; i++) { if (i != 0) sql.append(separator); - if (write(sql, expressions.get(i))) return; + if (write(sql, parameters.get(i))) return; } sql.append(')'); } @@ -422,6 +441,7 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { } sql.append(')'); } + sql.declareFunction(JDBCType.BOOLEAN); } } @@ -447,6 +467,7 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { final var filter = (BinaryComparisonOperator<Feature>) f; if (filter.isMatchingCase()) { writeBinaryOperator(sql, filter, operator); + sql.declareFunction(JDBCType.BOOLEAN); } else { sql.invalidate(); } @@ -472,6 +493,7 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { /** Invoked when an arithmetic expression needs to be converted to SQL clause. */ @Override public void accept(final Expression<Feature,?> expression, final SelectionClause sql) { writeParameters(sql, expression.getParameters(), operator, true); + sql.declareFunction(JDBCType.DOUBLE); } } @@ -486,17 +508,22 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { */ private final class Function implements BiConsumer<Expression<Feature,?>, SelectionClause> { /** Identification of the function. */ - final Enum<?> function; + final FunctionIdentifier function; + + /** The type of values returned by the function. */ + private final JDBCType returnType; /** Creates a function. */ - Function(final Enum<?> function) { + Function(final FunctionIdentifier function) { this.function = function; + returnType = JDBCType.valueOf(function.getSignature()[0]); } /** Invoked when an expression should be converted to a <abbr>SQL</abbr> clause. */ @Override public void accept(final Expression<Feature,?> expression, final SelectionClause sql) { - sql.appendSpatialFunction(function.name()); + sql.append(function.name()); writeParameters(sql, expression.getParameters(), ", ", false); + sql.declareFunction(returnType); } } @@ -527,9 +554,9 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { */ @Override public void accept(final Filter<Feature> filter, final SelectionClause sql) { sql.appendSpatialFunction(name); - final List<Expression<Feature, ?>> expressions = filter.getExpressions(); + final List<Expression<Feature, ?>> parameters = filter.getExpressions(); if (SelectionClause.REPLACE_UNSPECIFIED_CRS) { - for (Expression<Feature,?> exp : expressions) { + for (Expression<Feature,?> exp : parameters) { if (exp instanceof ValueReference<?,?>) { if (sql.acceptColumnCRS((ValueReference<Feature,?>) exp)) { break; @@ -537,7 +564,8 @@ public class SelectionClauseWriter extends Visitor<Feature, SelectionClause> { } } } - writeParameters(sql, expressions, ", ", false); + writeParameters(sql, parameters, ", ", false); + sql.declareFunction(JDBCType.BOOLEAN); sql.clearColumnCRS(); } } diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Table.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Table.java index e9e333c074..c6a9fec04e 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Table.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/Table.java @@ -56,6 +56,7 @@ import org.opengis.filter.InvalidFilterValueException; /** * Description of a table in the database, including columns, primary keys and foreigner keys. + * Despite the class name, {@code Table} instances can also be <abbr>SQL</abbr> projections or queries. * This class contains a {@link FeatureType} inferred from the table structure. * The {@link FeatureType} contains an {@link AttributeType} for each table column, * except foreigner keys which are represented by {@link FeatureAssociationRole}s. @@ -180,6 +181,8 @@ final class Table extends AbstractFeatureSet { /** * Creates a description of the table analyzed by the given object. + * This constructor is invoked for both real tables and queries, + * depending on the subclass of the {@code analyzer} argument. * * @param database information about the database (syntax for building SQL statements, …). * @param analyzer helper functions, e.g. for converting SQL types to Java types. @@ -246,7 +249,13 @@ final class Table extends AbstractFeatureSet { unhandled.set(i); continue; } - final Column column = source.getColumn(xpath); + final Column column; + final var expression = projection.expression(i); + if (expression instanceof ComputedColumn) { + column = (ComputedColumn) expression; + } else { + column = source.getColumn(xpath); + } if (column != null) { hasGeometry |= column.getGeometryType().isPresent(); final String name = storedProperties.get(i); diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ValueGetter.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ValueGetter.java index 9233d188cc..276ed3fabd 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ValueGetter.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/feature/ValueGetter.java @@ -491,7 +491,7 @@ public class ValueGetter<T> { * Get a function for getting values of components in the array. * If no match is found, then `cmget` stay null. */ - cmget = stmts.database.getMapping(new Column(array.getBaseType(), array.getBaseTypeName())); + cmget = stmts.database.getMapping(new Column(array.getBaseType(), array.getBaseTypeName(), "element")); } Class<?> componentType = Numbers.primitiveToWrapper(result.getClass().getComponentType()); if (cmget != null && !cmget.valueType.isAssignableFrom(componentType)) { diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedClauseWriter.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedClauseWriter.java index d24d535395..b4664df6c4 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedClauseWriter.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/ExtendedClauseWriter.java @@ -16,6 +16,7 @@ */ package org.apache.sis.storage.sql.postgis; +import java.sql.JDBCType; import org.apache.sis.storage.sql.feature.SelectionClauseWriter; // Specific to the geoapi-3.1 and geoapi-4.0 branches: @@ -46,6 +47,7 @@ final class ExtendedClauseWriter extends SelectionClauseWriter { super(DEFAULT, true, false); setFilterHandler(SpatialOperatorName.BBOX, (f,sql) -> { writeBinaryOperator(sql, f, " && "); + sql.declareFunction(JDBCType.BOOLEAN); }); } diff --git a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/Postgres.java b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/Postgres.java index 2a382b1679..5c17f86caf 100644 --- a/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/Postgres.java +++ b/endorsed/src/org.apache.sis.storage.sql/main/org/apache/sis/storage/sql/postgis/Postgres.java @@ -136,13 +136,13 @@ public final class Postgres<G> extends Database<G> { * This method is invoked when {@link Column#type} = {@link Types#ARRAY}. */ @Override - protected int getArrayComponentType(final Column columnDefinition) { + protected JDBCType getArrayComponentType(final Column columnDefinition) { String typeName = columnDefinition.typeName; if (typeName.equalsIgnoreCase("_text")) { // Common case. - return Types.VARCHAR; + return JDBCType.VARCHAR; } if (typeName.length() >= 2 && typeName.charAt(0) == '_') try { - return JDBCType.valueOf(typeName.substring(1).toUpperCase(Locale.US)).getVendorTypeNumber(); + return JDBCType.valueOf(typeName.substring(1).toUpperCase(Locale.US)); } catch (IllegalArgumentException e) { // Unknown type. Ignore and fallback on `Types.OTHER`. }
