sis-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ama...@apache.org
Subject [sis] 23/45: wip(SQLStore): First draft for SQL filter
Date Tue, 12 Nov 2019 16:44:50 GMT
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 <amanin@apache.org>
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());
 


Mime
View raw message