This is an automated email from the ASF dual-hosted git repository. amanin pushed a commit to branch refactor/sql-store in repository https://gitbox.apache.org/repos/asf/sis.git
commit 16759b4c9a90e83b9e41b6a59874ddebdab2b243 Author: Alexis Manin <[email protected]> AuthorDate: Tue Oct 1 17:41:27 2019 +0200 wip(SQLStore): First draft for SQL filter --- .../apache/sis/filter/DefaultFilterFactory.java | 53 ++- .../sis/internal/sql/feature/ANSIInterpreter.java | 423 +++++++++++++++++++++ .../apache/sis/internal/sql/feature/Features.java | 42 +- .../sis/internal/sql/feature/SQLQueryAdapter.java | 33 +- .../org/apache/sis/internal/sql/feature/Table.java | 6 +- .../sis/internal/sql/feature/TableSubset.java | 67 ++++ .../org/apache/sis/storage/sql/SQLStoreTest.java | 60 ++- .../apache/sis/internal/storage/SubsetAdapter.java | 10 +- 8 files changed, 664 insertions(+), 30 deletions(-) diff --git a/core/sis-feature/src/main/java/org/apache/sis/filter/DefaultFilterFactory.java b/core/sis-feature/src/main/java/org/apache/sis/filter/DefaultFilterFactory.java index e74cef6..b1b1197 100644 --- a/core/sis-feature/src/main/java/org/apache/sis/filter/DefaultFilterFactory.java +++ b/core/sis-feature/src/main/java/org/apache/sis/filter/DefaultFilterFactory.java @@ -23,19 +23,40 @@ import java.util.List; import java.util.Map; import java.util.ServiceLoader; import java.util.Set; -import org.apache.sis.internal.feature.FunctionRegister; + import org.opengis.filter.*; import org.opengis.filter.capability.*; -import org.opengis.filter.capability.SpatialOperator; -import org.opengis.filter.expression.*; -import org.opengis.filter.identity.*; -import org.opengis.filter.sort.*; -import org.opengis.filter.spatial.*; +import org.opengis.filter.expression.Add; +import org.opengis.filter.expression.Divide; +import org.opengis.filter.expression.Expression; +import org.opengis.filter.expression.Function; +import org.opengis.filter.expression.Literal; +import org.opengis.filter.expression.Multiply; +import org.opengis.filter.expression.PropertyName; +import org.opengis.filter.expression.Subtract; +import org.opengis.filter.identity.FeatureId; +import org.opengis.filter.identity.GmlObjectId; +import org.opengis.filter.identity.Identifier; +import org.opengis.filter.sort.SortBy; +import org.opengis.filter.sort.SortOrder; +import org.opengis.filter.spatial.BBOX; +import org.opengis.filter.spatial.Beyond; +import org.opengis.filter.spatial.Contains; +import org.opengis.filter.spatial.Crosses; +import org.opengis.filter.spatial.DWithin; +import org.opengis.filter.spatial.Disjoint; +import org.opengis.filter.spatial.Equals; +import org.opengis.filter.spatial.Intersects; +import org.opengis.filter.spatial.Overlaps; +import org.opengis.filter.spatial.Touches; +import org.opengis.filter.spatial.Within; import org.opengis.filter.temporal.*; import org.opengis.geometry.Envelope; import org.opengis.geometry.Geometry; import org.opengis.util.GenericName; +import org.apache.sis.internal.feature.FunctionRegister; + /** * Default implementation of GeoAPI filter factory for creation of {@link Filter} and {@link Expression} instances. @@ -65,6 +86,12 @@ public class DefaultFilterFactory implements FilterFactory2 { } } + /** + * According to OGC Filter encoding v2.0, comparison operators should default to cas sensitive comparison. We'll + * use this constant to model it, so it will be easier to change default value is the standard evolves. + * Doc reference : OGC 09-026r1 and ISO 19143:2010(E), section 7.7.3.2 + */ + private static final boolean DEFAULT_MATCH_CASE = true; /** * Creates a new factory. @@ -409,7 +436,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsEqualTo equals(final Expression expression1, final Expression expression2) { - return equal(expression1, expression2, true, MatchAction.ANY); + return equal(expression1, expression2, DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -427,7 +454,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsNotEqualTo notEqual(final Expression expression1, final Expression expression2) { - return notEqual(expression1, expression2, true, MatchAction.ANY); + return notEqual(expression1, expression2, DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -445,7 +472,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsGreaterThan greater(final Expression expression1, final Expression expression2) { - return greater(expression1,expression2,false, MatchAction.ANY); + return greater(expression1,expression2,DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -463,7 +490,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsGreaterThanOrEqualTo greaterOrEqual(final Expression expression1, final Expression expression2) { - return greaterOrEqual(expression1, expression2,false, MatchAction.ANY); + return greaterOrEqual(expression1, expression2,DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -481,7 +508,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsLessThan less(final Expression expression1, final Expression expression2) { - return less(expression1, expression2, false, MatchAction.ANY); + return less(expression1, expression2, DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -499,7 +526,7 @@ public class DefaultFilterFactory implements FilterFactory2 { */ @Override public PropertyIsLessThanOrEqualTo lessOrEqual(final Expression expression1, final Expression expression2) { - return lessOrEqual(expression1, expression2, false, MatchAction.ANY); + return lessOrEqual(expression1, expression2, DEFAULT_MATCH_CASE, MatchAction.ANY); } /** @@ -527,7 +554,7 @@ public class DefaultFilterFactory implements FilterFactory2 { public PropertyIsLike like(final Expression expression, final String pattern, final String wildcard, final String singleChar, final String escape) { - return like(expression,pattern,wildcard,singleChar,escape,false); + return like(expression,pattern,wildcard,singleChar,escape,DEFAULT_MATCH_CASE); } /** diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ANSIInterpreter.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ANSIInterpreter.java new file mode 100644 index 0000000..48c7d63 --- /dev/null +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ANSIInterpreter.java @@ -0,0 +1,423 @@ +package org.apache.sis.internal.sql.feature; + +import java.util.List; +import java.util.function.BooleanSupplier; +import java.util.function.Supplier; +import java.util.stream.Collectors; + +import org.opengis.filter.*; +import org.opengis.filter.expression.Add; +import org.opengis.filter.expression.BinaryExpression; +import org.opengis.filter.expression.Divide; +import org.opengis.filter.expression.Expression; +import org.opengis.filter.expression.ExpressionVisitor; +import org.opengis.filter.expression.Function; +import org.opengis.filter.expression.Literal; +import org.opengis.filter.expression.Multiply; +import org.opengis.filter.expression.NilExpression; +import org.opengis.filter.expression.PropertyName; +import org.opengis.filter.expression.Subtract; +import org.opengis.filter.spatial.BBOX; +import org.opengis.filter.spatial.Beyond; +import org.opengis.filter.spatial.Contains; +import org.opengis.filter.spatial.Crosses; +import org.opengis.filter.spatial.DWithin; +import org.opengis.filter.spatial.Disjoint; +import org.opengis.filter.spatial.Equals; +import org.opengis.filter.spatial.Intersects; +import org.opengis.filter.spatial.Overlaps; +import org.opengis.filter.spatial.Touches; +import org.opengis.filter.spatial.Within; +import org.opengis.filter.temporal.*; +import org.opengis.util.GenericName; +import org.opengis.util.LocalName; + +import org.apache.sis.util.iso.Names; + +import static org.apache.sis.util.ArgumentChecks.ensureNonNull; + +/** + * Port of Geotk FilterToSQL for an ANSI compliant query builder. + * + * @implNote For now, we over-use parenthesis to ensure consistent operator priority. In the future, we could evolve + * this component to provide more elegant transcription of filter groups. + * + * No case insensitive support of binary comparison is done. + * + * TODO: define a set of accepter property names, so any {@link PropertyName} filter refering to non pure SQL property + * (like relations) will cause a failure. + * + * @author Alexis Manin (Geomatys) + */ +public class ANSIInterpreter implements FilterVisitor, ExpressionVisitor { + + private final java.util.function.Function<Literal, CharSequence> valueFormatter; + + private final java.util.function.Function<PropertyName, CharSequence> nameFormatter; + + public ANSIInterpreter() { + this(ANSIInterpreter::format, ANSIInterpreter::format); + } + + public ANSIInterpreter( + java.util.function.Function<Literal, CharSequence> valueFormatter, + java.util.function.Function<PropertyName, CharSequence> nameFormatter + ) { + ensureNonNull("Literal value formmatter", valueFormatter); + ensureNonNull("Property name formmatter", nameFormatter); + this.valueFormatter = valueFormatter; + this.nameFormatter = nameFormatter; + } + + @Override + public CharSequence visitNullFilter(Object extraData) { + throw new UnsupportedOperationException("Null filter is not supported."); + } + + @Override + public Object visit(ExcludeFilter filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(IncludeFilter filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public CharSequence visit(And filter, Object extraData) { + return join(filter, " AND ", extraData); + } + + @Override + public Object visit(Id filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Not filter, Object extraData) { + final CharSequence innerFilter = evaluateMandatory(filter.getFilter(), extraData); + return "NOT (" + innerFilter + ")"; + } + + @Override + public Object visit(Or filter, Object extraData) { + return join(filter, " OR ", extraData); + } + + @Override + public Object visit(PropertyIsBetween filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(PropertyIsEqualTo filter, Object extraData) { + return joinMatchCase(filter, " = ", extraData); + } + + @Override + public Object visit(PropertyIsNotEqualTo filter, Object extraData) { + return joinMatchCase(filter, " <> ", extraData); + } + + @Override + public Object visit(PropertyIsGreaterThan filter, Object extraData) { + return joinMatchCase(filter, " > ", extraData); + } + + @Override + public Object visit(PropertyIsGreaterThanOrEqualTo filter, Object extraData) { + return joinMatchCase(filter, " >= ", extraData); + } + + @Override + public Object visit(PropertyIsLessThan filter, Object extraData) { + return joinMatchCase(filter, " < ", extraData); + } + + @Override + public Object visit(PropertyIsLessThanOrEqualTo filter, Object extraData) { + return joinMatchCase(filter, " <= ", extraData); + } + + @Override + public Object visit(PropertyIsLike filter, Object extraData) { + // TODO: PostgreSQL extension : ilike + ensureMatchCase(filter::isMatchingCase); + // TODO: port Geotk + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(PropertyIsNull filter, Object extraData) { + return evaluateMandatory(filter.getExpression(), extraData) + " = NULL"; + } + + @Override + public Object visit(PropertyIsNil filter, Object extraData) { + return evaluateMandatory(filter.getExpression(), extraData) + " = NULL"; + } + + @Override + public Object visit(BBOX filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Beyond filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Contains filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Crosses filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Disjoint filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(DWithin filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Equals filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Intersects filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Overlaps filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Touches filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Within filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(After filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(AnyInteracts filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Before filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Begins filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(BegunBy filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(During filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(EndedBy filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Ends filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(Meets filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(MetBy filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(OverlappedBy filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(TContains filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(TEquals filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + @Override + public Object visit(TOverlaps filter, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 30/09/2019 + } + + /* + * Expression visitor + */ + + @Override + public Object visit(NilExpression expression, Object extraData) { + return "NULL"; + } + + @Override + public Object visit(Add expression, Object extraData) { + return join(expression, " + ", extraData); + } + + @Override + public Object visit(Divide expression, Object extraData) { + return join(expression, " / ", extraData); + } + + @Override + public Object visit(Function expression, Object extraData) { + throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 01/10/2019 + } + + @Override + public Object visit(Literal expression, Object extraData) { + return valueFormatter.apply(expression); + } + + @Override + public Object visit(Multiply expression, Object extraData) { + return join(expression, " * ", extraData); + } + + + @Override + public Object visit(PropertyName expression, Object extraData) { + return nameFormatter.apply(expression); + } + + @Override + public Object visit(Subtract expression, Object extraData) { + return join(expression, " - ", extraData); + } + + /* + * UTILITIES + */ + + protected static CharSequence format(Literal candidate) { + final Object value = candidate == null ? null : candidate.getValue(); + if (value == null) return "NULL"; + else if (value instanceof CharSequence) { + final String asStr = value.toString(); + asStr.replace("'", "''"); + return "'"+asStr+"'"; + } + + throw new UnsupportedOperationException("Not supported yet: Literal value of type "+value.getClass()); + } + + /** + * Beware ! This implementation is a naïve one, expecting given property name to match exactly SQL database names. + * In the future, it would be appreciable to be able to configure a mapper between feature and SQL names. + * @param candidate The property name to parse. + * @return The SQL representation of the given name. + */ + protected static CharSequence format(PropertyName candidate) { + final GenericName pName = Names.parseGenericName(null, ":", candidate.getPropertyName()); + return pName.getParsedNames().stream() + .map(LocalName::toString) + .collect(Collectors.joining("\".\"", "\"", "\"")); + } + + protected CharSequence join(final BinaryLogicOperator filter, String separator, Object extraData) { + final List<Filter> subFilters = filter.getChildren(); + if (subFilters == null || subFilters.isEmpty()) return ""; + return subFilters.stream() + .map(sub -> sub.accept(this, extraData)) + .filter(ANSIInterpreter::isNonEmptyText) + .map( result -> (CharSequence) result) + .collect(Collectors.joining(separator, "(", ")")); + } + + protected CharSequence joinMatchCase(BinaryComparisonOperator filter, String operator, Object extraData) { + ensureMatchCase(filter); + return join(filter, operator, extraData); + } + + protected CharSequence join(BinaryComparisonOperator candidate, String operator, Object extraData) { + return join(candidate::getExpression1, candidate::getExpression2, operator, extraData); + } + + protected CharSequence join(BinaryExpression candidate, String operator, Object extraData) { + return join(candidate::getExpression1, candidate::getExpression2, operator, extraData); + } + + protected CharSequence join( + Supplier<Expression> leftOperand, + Supplier<Expression> rightOperand, + String operator, Object extraData + ) { + return "(" + + evaluateMandatory(leftOperand.get(), extraData) + + operator + + evaluateMandatory(rightOperand.get(), extraData) + + ")"; + } + + protected CharSequence evaluateMandatory(final Filter candidate, Object extraData) { + final Object exp = candidate == null ? null : candidate.accept(this, extraData); + if (isNonEmptyText(exp)) return (CharSequence) exp; + else throw new IllegalArgumentException("Filter evaluate to an empty text: "+candidate); + } + + protected CharSequence evaluateMandatory(final Expression candidate, Object extraData) { + final Object exp = candidate == null ? null : candidate.accept(this, extraData); + if (isNonEmptyText(exp)) return (CharSequence) exp; + else throw new IllegalArgumentException("Expression evaluate to an empty text: "+candidate); + } + + protected static boolean isNonEmptyText(final Object toCheck) { + return toCheck instanceof CharSequence && ((CharSequence) toCheck).length() > 0; + } + + private static void ensureMatchCase(BinaryComparisonOperator filter) { + ensureMatchCase(filter::isMatchingCase); + } + private static void ensureMatchCase(BooleanSupplier filter) { + if (!filter.getAsBoolean()) + throw new UnsupportedOperationException("case insensitive match is not defined by ANSI SQL"); + } + + protected static CharSequence append(CharSequence toAdd, Object extraData) { + if (extraData instanceof StringBuilder) return ((StringBuilder) extraData).append(toAdd); + return toAdd; + } +} diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java index 9559bd7..5ce2195 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java @@ -33,12 +33,12 @@ import java.util.Map; import java.util.Spliterator; import java.util.function.Consumer; import java.util.function.Function; +import java.util.regex.Pattern; import java.util.stream.Stream; import java.util.stream.StreamSupport; import org.opengis.feature.Feature; import org.opengis.feature.FeatureType; -import org.opengis.filter.Filter; import org.opengis.filter.sort.SortBy; import org.opengis.util.GenericName; @@ -65,6 +65,7 @@ final class Features implements Spliterator<Feature> { * An empty array of iterators, used when there is no dependency. */ private static final Features[] EMPTY = new Features[0]; + public static final Pattern WHERE_REGEX = Pattern.compile("^\\s*WHERE\\s+", Pattern.CASE_INSENSITIVE); /** * The type of features to create. @@ -152,8 +153,6 @@ final class Features implements Spliterator<Feature> { /** * Creates a new iterator over the feature instances. - * TODO: This object is far too complicated. A builder of some sort should be used. We should even consider a - * third-party tool like JOOQ, which is a great abstraction for SQL query building. * * @param table the table for which we are creating an iterator. * @param connection connection to the database. @@ -169,7 +168,7 @@ final class Features implements Spliterator<Feature> { */ Features(final Table table, final Connection connection, final Collection<ColumnRef> columns, final List<Relation> following, final Relation noFollow, - boolean distinct, final long offset, final long limit) + boolean distinct, final long offset, final long limit, CharSequence where) throws SQLException, InternalDataStoreException { ensureNonEmpty("Columns to fetch", columns); @@ -269,6 +268,7 @@ final class Features implements Spliterator<Feature> { * a possibility that many rows reference the same feature instance. */ sql.append(" FROM ").appendIdentifier(table.name.catalog, table.name.schema, table.name.table); + appendWhere(sql, where); if (following.isEmpty()) { statement = null; instances = null; // A future SIS version could use the map opportunistically if it exists. @@ -565,14 +565,19 @@ final class Features implements Spliterator<Feature> { long limit, offset; SortBy[] sort; + ColumnRef[] columns; + boolean distinct; + CharSequence whereClause; + Builder(Table parent) { this.parent = parent; } - Builder where(final Filter filter) { - throw new UnsupportedOperationException("TODO"); + Builder where(final CharSequence whereClause) { + this.whereClause = whereClause; + return this; } Builder sortBy(final SortBy...sorting) { @@ -581,6 +586,12 @@ final class Features implements Spliterator<Feature> { return this; } + Builder setColumns(final ColumnRef... columns) { + if (columns == null || columns.length < 1) this.columns = null; + else this.columns = Arrays.copyOf(columns, columns.length); + return this; + } + @Override public StreamSQL.QueryBuilder limit(long limit) { this.limit = limit; @@ -601,7 +612,7 @@ final class Features implements Spliterator<Feature> { @Override public Connector select(ColumnRef... columns) { - return new TableConnector(this, columns); + return new TableConnector(this, columns == null || columns.length < 1 ? this.columns : columns); } } @@ -613,18 +624,21 @@ final class Features implements Spliterator<Feature> { final SortBy[] sort; + final CharSequence where; + TableConnector(Builder source, ColumnRef[] columns) { this.source = source; this.distinct = source.distinct; this.columns = columns; this.sort = source.sort == null ? null : Arrays.copyOf(source.sort, source.sort.length); + this.where = source.whereClause; } public Stream<Feature> connect(final Connection conn) throws SQLException, DataStoreException { final Features features = new Features( source.parent, conn, columns == null || columns.length < 1 ? source.parent.attributes : Arrays.asList(columns), - new ArrayList<>(), null, distinct, source.offset, source.limit + new ArrayList<>(), null, distinct, source.offset, source.limit, where ); return StreamSupport.stream(features, false); } @@ -652,6 +666,7 @@ final class Features implements Spliterator<Feature> { if (count) sql.append(')'); sql.append(" FROM ").appendIdentifier(source.parent.name.catalog, source.parent.name.schema, source.parent.name.table); + appendWhere(sql, where); if (!count && sort != null && sort.length > 0) { sql.append(" ORDER BY "); append(sql, sort[0]); @@ -665,6 +680,17 @@ final class Features implements Spliterator<Feature> { } } + private static void appendWhere(SQLBuilder sql, CharSequence whereClause) { + if (whereClause != null) { + final String whereStr = whereClause.toString(); + if (!whereStr.isEmpty()) { + sql.append(" "); + if (!WHERE_REGEX.matcher(whereStr).find()) sql.append("WHERE "); + sql.append(whereStr); + } + } + } + private static void append(SQLBuilder target, SortBy toAppend) { target.appendIdentifier(toAppend.getPropertyName().getPropertyName()).append(" "); if (toAppend.getSortOrder() != null) target.append(toAppend.getSortOrder().toSQL()); diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/SQLQueryAdapter.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/SQLQueryAdapter.java index 0ddde08..e471567 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/SQLQueryAdapter.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/SQLQueryAdapter.java @@ -15,9 +15,11 @@ public class SQLQueryAdapter implements SubsetAdapter.AdapterBuilder { final Table parent; - private SimpleQuery.Column[] columns; + private ColumnRef[] columns; private SortBy[] sorting; + private CharSequence where; + public SQLQueryAdapter(Table parent) { this.parent = parent; } @@ -44,7 +46,18 @@ public class SQLQueryAdapter implements SubsetAdapter.AdapterBuilder { @Override public Filter filter(Filter filter) { - throw new UnsupportedOperationException("Not supported yet"); // "Alexis Manin (Geomatys)" on 18/09/2019 + try { + final Object result = filter.accept(new ANSIInterpreter(), null); + if (ANSIInterpreter.isNonEmptyText(result)) { + where = (CharSequence) result; + return Filter.INCLUDE; + } + } catch (UnsupportedOperationException e) { + // TODO: log + where = null; + } + + return filter; } @Override @@ -55,12 +68,24 @@ public class SQLQueryAdapter implements SubsetAdapter.AdapterBuilder { @Override public boolean select(List<SimpleQuery.Column> columns) { - this.columns = columns.toArray(new SimpleQuery.Column[columns.size()]); + /* We've got a lot of trouble with current column API. It defines an expression and an alias, which allow to + * infer output property type. However, it's very difficult with current methods to infer source columns used + * for building output. Note that we could check if column expression is a property name or a literal, but if + * any column is not one of those two, it means it uses unknown (for us) SQL columns, so we cannot filter + * selected columns safely. + */ return false; } @Override public Optional<FeatureSet> build() { - return Optional.empty(); + if (isNoOp()) return Optional.empty(); + return Optional.of(new TableSubset(parent, sorting, where)); + } + + private boolean isNoOp() { + return (sorting == null || sorting.length < 1) + && (columns == null || columns.length < 1) + && (where == null || where.length() < 1); } } diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java index 07f658f..b60ec46 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java @@ -29,6 +29,7 @@ import java.util.stream.Collectors; import java.util.stream.Stream; import javax.sql.DataSource; +import org.opengis.feature.Attribute; import org.opengis.feature.AttributeType; import org.opengis.feature.Feature; import org.opengis.feature.FeatureAssociationRole; @@ -86,7 +87,8 @@ final class Table extends AbstractFeatureSet { /** * Name of all columns to fetch from database, optionally amended with an alias. Alias is used for feature type * attributes which have been renamed to avoid name collisions. In any case, a call to {@link ColumnRef#getAttributeName()}} - * will return the name available in target feature type. + * will return the name available in target feature type. This list contains only {@link Attribute} names, not any + * relation one. */ final List<ColumnRef> attributes; @@ -422,6 +424,6 @@ final class Table extends AbstractFeatureSet { final Features features(final Connection connection, final List<Relation> following, final Relation noFollow) throws SQLException, InternalDataStoreException { - return new Features(this, connection, attributes, following, noFollow, false, -1, -1); + return new Features(this, connection, attributes, following, noFollow, false, -1, -1, null); } } diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/TableSubset.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/TableSubset.java new file mode 100644 index 0000000..dc9e427 --- /dev/null +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/TableSubset.java @@ -0,0 +1,67 @@ +package org.apache.sis.internal.sql.feature; + +import java.util.Optional; +import java.util.stream.Stream; + +import org.opengis.feature.Feature; +import org.opengis.feature.FeatureType; +import org.opengis.filter.sort.SortBy; +import org.opengis.geometry.Envelope; +import org.opengis.metadata.Metadata; +import org.opengis.util.GenericName; + +import org.apache.sis.storage.DataStoreException; +import org.apache.sis.storage.FeatureSet; +import org.apache.sis.storage.event.ChangeEvent; +import org.apache.sis.storage.event.ChangeListener; + +public class TableSubset implements FeatureSet { + + final Table parent; + final SortBy[] sorting; + final CharSequence where; + + public TableSubset(Table parent, SortBy[] sorting, CharSequence where) { + this.parent = parent; + this.sorting = sorting; + this.where = where; + } + + @Override + public FeatureType getType() throws DataStoreException { + return parent.getType(); + } + + @Override + public Stream<Feature> features(boolean parallel) throws DataStoreException { + final Features.Builder builder = new Features.Builder(parent) + .where(where) + .sortBy(sorting); + return new StreamSQL(builder, parent.source, parallel); + } + + @Override + public Optional<Envelope> getEnvelope() throws DataStoreException { + return parent.getEnvelope(); + } + + @Override + public Optional<GenericName> getIdentifier() throws DataStoreException { + return Optional.empty(); + } + + @Override + public Metadata getMetadata() throws DataStoreException { + return parent.getMetadata(); + } + + @Override + public <T extends ChangeEvent> void addListener(ChangeListener<? super T> listener, Class<T> eventType) { + parent.addListener(listener, eventType); + } + + @Override + public <T extends ChangeEvent> void removeListener(ChangeListener<? super T> listener, Class<T> eventType) { + parent.removeListener(listener, eventType); + } +} diff --git a/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java index 6345fbc..5142fa1 100644 --- a/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java +++ b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java @@ -36,17 +36,21 @@ import org.opengis.feature.Feature; import org.opengis.feature.FeatureAssociationRole; import org.opengis.feature.FeatureType; import org.opengis.feature.PropertyType; +import org.opengis.filter.sort.SortOrder; import org.opengis.util.GenericName; +import org.apache.sis.filter.DefaultFilterFactory; import org.apache.sis.internal.feature.AttributeConvention; import org.apache.sis.internal.metadata.sql.SQLBuilder; import org.apache.sis.internal.sql.feature.QueryFeatureSet; +import org.apache.sis.internal.storage.query.SimpleQuery; import org.apache.sis.storage.DataStoreException; import org.apache.sis.storage.FeatureSet; import org.apache.sis.storage.StorageConnector; import org.apache.sis.test.TestCase; import org.apache.sis.test.sql.TestDatabase; +import org.junit.Assert; import org.junit.Test; import static org.apache.sis.test.Assert.assertEquals; @@ -83,6 +87,8 @@ public final strictfp class SQLStoreTest extends TestCase { 531902 // Québec, 2016. }; + private static final DefaultFilterFactory FF = new DefaultFilterFactory(); + /** * Number of time that the each country has been seen while iterating over the cities. */ @@ -165,7 +171,9 @@ public final strictfp class SQLStoreTest extends TestCase { // Now, we'll check that overloaded stream operations are functionally stable, even stacked. verifyStreamOperations(cities); - verifyQueries(tmp.source); + verifySimpleQueries(store); + + verifySQLQueries(tmp.source); } } assertEquals(Integer.valueOf(2), countryCount.remove("CAN")); @@ -174,7 +182,55 @@ public final strictfp class SQLStoreTest extends TestCase { assertTrue (countryCount.isEmpty()); } - private void verifyQueries(DataSource source) throws Exception { + private void verifySimpleQueries(SQLStore dataset) throws Exception { + verifySimpleQuerySorting(dataset); + verifySimpleWhere(dataset); + } + + private void verifySimpleQuerySorting(SQLStore dataset) throws DataStoreException { + final FeatureSet parks = (FeatureSet) dataset.findResource("Parks"); + final SimpleQuery query = new SimpleQuery(); + query.setColumns(new SimpleQuery.Column(FF.property("english_name"))); + query.setSortBy( + FF.sort("country", SortOrder.DESCENDING), + FF.sort("english_name", SortOrder.ASCENDING) + ); + final FeatureSet subset = parks.subset(query); + String[] expectedPNames = {"english_name"}; + try (Stream<Feature> features = subset.features(false)) { + final Object[] values = features.map(f -> { + final String[] names = f.getType().getProperties(true).stream() + .map(PropertyType::getName) + .map(GenericName::toString) + .toArray(size -> new String[size]); + assertArrayEquals(expectedPNames, names); + return f.getPropertyValue(expectedPNames[0]); + }) + .toArray(); + String[] expectedValues = {"Shinjuku Gyoen", "Yoyogi-kōen", "Luxembourg Garden", "Tuileries Garden", "Mount Royal"}; + assertArrayEquals("Read values are not sorted as expected.", expectedValues, values); + } + } + + private void verifySimpleWhere(SQLStore dataset) throws Exception { + final SimpleQuery q = new SimpleQuery(); + q.setSortBy(FF.sort("native_name", SortOrder.ASCENDING)); + q.setFilter(FF.equals(FF.property("country"), FF.literal("CAN"))); + final FeatureSet cities = (FeatureSet) dataset.findResource("Cities"); + final Object[] names; + try (Stream<Feature> features = cities.subset(q).features(false)) { + names = features.map(f -> f.getPropertyValue("native_name")) + .toArray(); + } + + Assert.assertArrayEquals( + "Filtered cities should only contains Canadian ones", + new String[] {"Montréal", "Québec"}, + names + ); + } + + private void verifySQLQueries(DataSource source) throws Exception { verifyFetchCityTableAsQuery(source); verifyLimitOffsetAndColumnSelectionFromQuery(source); verifyDistinctQuery(source); diff --git a/storage/sis-storage/src/main/java/org/apache/sis/internal/storage/SubsetAdapter.java b/storage/sis-storage/src/main/java/org/apache/sis/internal/storage/SubsetAdapter.java index 88f3924..737d6d6 100644 --- a/storage/sis-storage/src/main/java/org/apache/sis/internal/storage/SubsetAdapter.java +++ b/storage/sis-storage/src/main/java/org/apache/sis/internal/storage/SubsetAdapter.java @@ -33,7 +33,15 @@ public final class SubsetAdapter { final long limit = query.getLimit(); if (limit != UNLIMITED) remaining.setLimit(driver.limit(limit)); - if (filteringRequired(query)) remaining.setFilter(driver.filter(query.getFilter())); + if (filteringRequired(query)) { + final Filter baseFilter = query.getFilter(); + try { + final Filter remainingFilter = driver.filter(baseFilter); + remaining.setFilter(remainingFilter); + } catch (UnsupportedOperationException e) { + remaining.setFilter(baseFilter); + } + } if (sortRequired(query) && !driver.sort(query.getSortBy())) remaining.setSortBy(query.getSortBy());
