calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [4/4] calcite git commit: [CALCITE-2072] Enable spatial functions by adding 'fun=spatial' to JDBC connect string
Date Mon, 11 Dec 2017 23:15:14 GMT
[CALCITE-2072] Enable spatial functions by adding 'fun=spatial' to JDBC connect string


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/c1749ade
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/c1749ade
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/c1749ade

Branch: refs/heads/master
Commit: c1749ade6638cd2e724f33a09f12f45d8d2a5503
Parents: 26b4712
Author: Julian Hyde <jhyde@apache.org>
Authored: Fri Dec 8 16:10:55 2017 -0800
Committer: Julian Hyde <jhyde@apache.org>
Committed: Mon Dec 11 14:24:53 2017 -0800

----------------------------------------------------------------------
 .../config/CalciteConnectionConfigImpl.java     | 25 +++++---
 .../config/CalciteConnectionProperty.java       |  3 +-
 .../calcite/prepare/CalciteCatalogReader.java   | 63 +++++++++++++++++---
 .../sql/validate/SqlUserDefinedAggFunction.java |  5 ++
 .../java/org/apache/calcite/test/JdbcTest.java  | 18 ++++++
 site/_docs/adapter.md                           |  2 +-
 site/_docs/howto.md                             | 15 +++++
 site/_docs/spatial.md                           | 18 ++++++
 8 files changed, 131 insertions(+), 18 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
index c2a3239..794d1c8 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
@@ -20,6 +20,8 @@ import org.apache.calcite.avatica.ConnectionConfigImpl;
 import org.apache.calcite.avatica.util.Casing;
 import org.apache.calcite.avatica.util.Quoting;
 import org.apache.calcite.model.JsonSchema;
+import org.apache.calcite.prepare.CalciteCatalogReader;
+import org.apache.calcite.runtime.GeoFunctions;
 import org.apache.calcite.sql.SqlOperatorTable;
 import org.apache.calcite.sql.fun.OracleSqlOperatorTable;
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
@@ -27,8 +29,8 @@ import org.apache.calcite.sql.util.ChainedSqlOperatorTable;
 import org.apache.calcite.sql.validate.SqlConformance;
 import org.apache.calcite.sql.validate.SqlConformanceEnum;
 
-import java.util.ArrayList;
-import java.util.List;
+import java.util.Collection;
+import java.util.LinkedHashSet;
 import java.util.Properties;
 
 /** Implementation of {@link CalciteConnectionConfig}. */
@@ -86,22 +88,29 @@ public class CalciteConnectionConfigImpl extends ConnectionConfigImpl
     if (fun == null || fun.equals("") || fun.equals("standard")) {
       return defaultOperatorTable;
     }
-    final List<SqlOperatorTable> tables = new ArrayList<>();
+    final Collection<SqlOperatorTable> tables = new LinkedHashSet<>();
     for (String s : fun.split(",")) {
-      tables.add(operatorTable(s));
+      operatorTable(s, tables);
     }
+    tables.add(SqlStdOperatorTable.instance());
     return operatorTableClass.cast(
         ChainedSqlOperatorTable.of(
             tables.toArray(new SqlOperatorTable[tables.size()])));
   }
 
-  private static SqlOperatorTable operatorTable(String s) {
+  private static void operatorTable(String s,
+        Collection<SqlOperatorTable> tables) {
     switch (s) {
     case "standard":
-      return SqlStdOperatorTable.instance();
+      tables.add(SqlStdOperatorTable.instance());
+      return;
     case "oracle":
-      return ChainedSqlOperatorTable.of(OracleSqlOperatorTable.instance(),
-          SqlStdOperatorTable.instance());
+      tables.add(OracleSqlOperatorTable.instance());
+      return;
+    case "spatial":
+      tables.add(
+          CalciteCatalogReader.operatorTable(GeoFunctions.class.getName()));
+      return;
     default:
       throw new IllegalArgumentException("Unknown operator table: " + s);
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
index b576aac..67909da 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
@@ -74,7 +74,8 @@ public enum CalciteConnectionProperty implements ConnectionProperty {
   LEX("lex", Type.ENUM, Lex.ORACLE, false),
 
   /** Collection of built-in functions and operators. Valid values include
-   * "standard" and "oracle". */
+   * "standard", "oracle" and "spatial", and also comma-separated lists, for
+   * example "oracle,spatial". */
   FUN("fun", Type.STRING, "standard", true),
 
   /** How identifiers are quoted.

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java
index af0d3a6..4dc8be7 100644
--- a/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java
+++ b/core/src/main/java/org/apache/calcite/prepare/CalciteCatalogReader.java
@@ -19,11 +19,13 @@ package org.apache.calcite.prepare;
 import org.apache.calcite.config.CalciteConnectionConfig;
 import org.apache.calcite.jdbc.CalciteSchema;
 import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
+import org.apache.calcite.model.ModelHandler;
 import org.apache.calcite.plan.RelOptPlanner;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
 import org.apache.calcite.rel.type.RelDataTypeFactoryImpl;
 import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.rel.type.RelDataTypeSystem;
 import org.apache.calcite.runtime.PredicateImpl;
 import org.apache.calcite.schema.AggregateFunction;
 import org.apache.calcite.schema.Function;
@@ -38,14 +40,18 @@ import org.apache.calcite.sql.SqlFunctionCategory;
 import org.apache.calcite.sql.SqlIdentifier;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.sql.SqlOperatorBinding;
+import org.apache.calcite.sql.SqlOperatorTable;
 import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.parser.SqlParserPos;
 import org.apache.calcite.sql.type.FamilyOperandTypeChecker;
 import org.apache.calcite.sql.type.InferTypes;
 import org.apache.calcite.sql.type.OperandTypes;
 import org.apache.calcite.sql.type.ReturnTypes;
 import org.apache.calcite.sql.type.SqlReturnTypeInference;
+import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
 import org.apache.calcite.sql.type.SqlTypeFamily;
 import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.util.ListSqlOperatorTable;
 import org.apache.calcite.sql.validate.SqlMoniker;
 import org.apache.calcite.sql.validate.SqlMonikerImpl;
 import org.apache.calcite.sql.validate.SqlMonikerType;
@@ -275,7 +281,43 @@ public class CalciteCatalogReader implements Prepare.CatalogReader {
             }));
   }
 
+  /** Creates an operator table that contains functions in the given class.
+   *
+   * @see ModelHandler#addFunctions */
+  public static SqlOperatorTable operatorTable(String className) {
+    // Dummy schema to collect the functions
+    final CalciteSchema schema =
+        CalciteSchema.createRootSchema(false, false);
+    ModelHandler.addFunctions(schema.plus(), null, ImmutableList.<String>of(),
+        className, "*", true);
+
+    // The following is technical debt; see [CALCITE-2082] Remove
+    // RelDataTypeFactory argument from SqlUserDefinedAggFunction constructor
+    final SqlTypeFactoryImpl typeFactory =
+        new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
+
+    final ListSqlOperatorTable table = new ListSqlOperatorTable();
+    for (String name : schema.getFunctionNames()) {
+      for (Function function : schema.getFunctions(name, true)) {
+        final SqlIdentifier id = new SqlIdentifier(name, SqlParserPos.ZERO);
+        table.add(
+            toOp(typeFactory, id, function));
+      }
+    }
+    return table;
+  }
+
   private SqlOperator toOp(SqlIdentifier name, final Function function) {
+    return toOp(typeFactory, name, function);
+  }
+
+  /** Converts a function to a {@link org.apache.calcite.sql.SqlOperator}.
+   *
+   * <p>The {@code typeFactory} argument is technical debt; see [CALCITE-2082]
+   * Remove RelDataTypeFactory argument from SqlUserDefinedAggFunction
+   * constructor. */
+  private static SqlOperator toOp(RelDataTypeFactory typeFactory,
+      SqlIdentifier name, final Function function) {
     List<RelDataType> argTypes = new ArrayList<>();
     List<SqlTypeFamily> typeFamilies = new ArrayList<>();
     for (FunctionParameter o : function.getParameters()) {
@@ -292,7 +334,7 @@ public class CalciteCatalogReader implements Prepare.CatalogReader {
         };
     final FamilyOperandTypeChecker typeChecker =
         OperandTypes.family(typeFamilies, optional);
-    final List<RelDataType> paramTypes = toSql(argTypes);
+    final List<RelDataType> paramTypes = toSql(typeFactory, argTypes);
     if (function instanceof ScalarFunction) {
       return new SqlUserDefinedFunction(name, infer((ScalarFunction) function),
           InferTypes.explicit(argTypes), typeChecker, paramTypes, function);
@@ -313,9 +355,10 @@ public class CalciteCatalogReader implements Prepare.CatalogReader {
     }
   }
 
-  private SqlReturnTypeInference infer(final ScalarFunction function) {
+  private static SqlReturnTypeInference infer(final ScalarFunction function) {
     return new SqlReturnTypeInference() {
       public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
+        final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
         final RelDataType type;
         if (function instanceof ScalarFunctionImpl) {
           type = ((ScalarFunctionImpl) function).getReturnType(typeFactory,
@@ -323,30 +366,34 @@ public class CalciteCatalogReader implements Prepare.CatalogReader {
         } else {
           type = function.getReturnType(typeFactory);
         }
-        return toSql(type);
+        return toSql(typeFactory, type);
       }
     };
   }
 
-  private SqlReturnTypeInference infer(final AggregateFunction function) {
+  private static SqlReturnTypeInference infer(
+      final AggregateFunction function) {
     return new SqlReturnTypeInference() {
       public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
+        final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
         final RelDataType type = function.getReturnType(typeFactory);
-        return toSql(type);
+        return toSql(typeFactory, type);
       }
     };
   }
 
-  private List<RelDataType> toSql(List<RelDataType> types) {
+  private static List<RelDataType> toSql(
+      final RelDataTypeFactory typeFactory, List<RelDataType> types) {
     return Lists.transform(types,
         new com.google.common.base.Function<RelDataType, RelDataType>() {
           public RelDataType apply(RelDataType type) {
-            return toSql(type);
+            return toSql(typeFactory, type);
           }
         });
   }
 
-  private RelDataType toSql(RelDataType type) {
+  private static RelDataType toSql(RelDataTypeFactory typeFactory,
+      RelDataType type) {
     if (type instanceof RelDataTypeFactoryImpl.JavaType
         && ((RelDataTypeFactoryImpl.JavaType) type).getJavaClass()
         == Object.class) {

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java
index a8be50b..68c954f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlUserDefinedAggFunction.java
@@ -17,6 +17,7 @@
 package org.apache.calcite.sql.validate;
 
 import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
+import org.apache.calcite.linq4j.function.Experimental;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
 import org.apache.calcite.rel.type.RelDataTypeFactoryImpl;
@@ -46,6 +47,10 @@ import java.util.List;
  */
 public class SqlUserDefinedAggFunction extends SqlAggFunction {
   public final AggregateFunction function;
+
+  /** This field is is technical debt; see [CALCITE-2082] Remove
+   * RelDataTypeFactory argument from SqlUserDefinedAggFunction constructor. */
+  @Experimental
   public final RelDataTypeFactory typeFactory;
 
   /** Creates a SqlUserDefinedAggFunction. */

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index cdebd47..2fd5d0a 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -6157,6 +6157,24 @@ public class JdbcTest {
         .throws_("No match found for function signature NVL(<NUMERIC>, <NUMERIC>)");
   }
 
+  /** Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-2072">[CALCITE-2072]
+   * Enable spatial operator table by adding 'fun=spatial'to JDBC URL</a>. */
+  @Test public void testFunSpatial() {
+    final String sql = "select distinct\n"
+        + "  ST_PointFromText('POINT(-71.0642.28)') as c\n"
+        + "from \"hr\".\"emps\"";
+    CalciteAssert.that(CalciteAssert.Config.REGULAR)
+        .with("fun", "spatial")
+        .query(sql)
+        .returnsUnordered("C={\"x\":-71.0642,\"y\":0.28}");
+
+    // NVL is present in the Oracle operator table, but not spatial or core
+    CalciteAssert.that(CalciteAssert.Config.REGULAR)
+        .query("select nvl(\"commission\", -99) as c from \"hr\".\"emps\"")
+        .throws_("No match found for function signature NVL(<NUMERIC>, <NUMERIC>)");
+  }
+
   /** Tests that {@link Hook#PARSE_TREE} works. */
   @Test public void testHook() {
     final int[] callCount = {0};

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/adapter.md
----------------------------------------------------------------------
diff --git a/site/_docs/adapter.md b/site/_docs/adapter.md
index 5562c4c..35001ed 100644
--- a/site/_docs/adapter.md
+++ b/site/_docs/adapter.md
@@ -88,7 +88,7 @@ as implemented by Avatica's
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#DEFAULT_NULL_COLLATION">defaultNullCollation</a>
| How NULL values should be sorted if neither NULLS FIRST nor NULLS LAST are specified in
a query. The default, HIGH, sorts NULL values the same as Oracle.
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#DRUID_FETCH">druidFetch</a>
| How many rows the Druid adapter should fetch at a time when executing SELECT queries.
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FORCE_DECORRELATE">forceDecorrelate</a>
| Whether the planner should try de-correlating as much as possible. Default true.
-| <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FUN">fun</a>
| Collection of built-in functions and operators. Valid values: "standard" (the default),
"oracle".
+| <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#FUN">fun</a>
| Collection of built-in functions and operators. Valid values are "standard" (the default),
"oracle", "spatial", and may be combined using commas, for example "oracle,spatial".
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#LEX">lex</a>
| Lexical policy. Values are ORACLE (default), MYSQL, MYSQL_ANSI, SQL_SERVER, JAVA.
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#MATERIALIZATIONS_ENABLED">materializationsEnabled</a>
| Whether Calcite should use materializations. Default false.
 | <a href="{{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#MODEL">model</a>
| URI of the JSON model file.

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/howto.md
----------------------------------------------------------------------
diff --git a/site/_docs/howto.md b/site/_docs/howto.md
index 742a83f..437f64d 100644
--- a/site/_docs/howto.md
+++ b/site/_docs/howto.md
@@ -464,6 +464,21 @@ Before you start:
   a fix version assigned (most likely the version we are
   just about to release)
 
+Smoke-test `sqlline` with Spatial and Oracle function tables:
+
+{% highlight sql %}
+$ ./sqlline
+> !connect jdbc:calcite:fun=spatial,oracle "sa" ""
+SELECT NVL(ST_Is3D(ST_PointFromText('POINT(-71.064544 42.28787)')), TRUE);
++--------+
+| EXPR$0 |
++--------+
+| false  |
++--------+
+1 row selected (0.039 seconds)
+> !quit
+{% endhighlight %}
+
 Create a release branch named after the release, e.g. `branch-1.1`, and push it to Apache.
 
 {% highlight bash %}

http://git-wip-us.apache.org/repos/asf/calcite/blob/c1749ade/site/_docs/spatial.md
----------------------------------------------------------------------
diff --git a/site/_docs/spatial.md b/site/_docs/spatial.md
index 3b98464..1521ce1 100644
--- a/site/_docs/spatial.md
+++ b/site/_docs/spatial.md
@@ -51,6 +51,24 @@ Calcite's support for spatial data includes:
 
 and will at some point also include query rewrites to use spatial indexes.
 
+## Enabling spatial support
+
+Though the `GEOMETRY` data type is built-in, the functions are not enabled by
+default. You need to add `fun=spatial` to the JDBC connect string to enable
+the functions. For example, `sqlline`:
+
+{% highlight sql %}
+$ ./sqlline
+> !connect jdbc:calcite:fun=spatial "sa" ""
+SELECT ST_PointFromText('POINT(-71.064544 42.28787)');
++-------------------------------+
+| EXPR$0                        |
++-------------------------------+
+| {"x":-71.064544,"y":42.28787} |
++-------------------------------+
+1 row selected (0.323 seconds)
+{% endhighlight %}
+
 ## Acknowledgements
 
 Calcite's OpenGIS implementation uses the


Mime
View raw message