Repository: calcite Updated Branches: refs/heads/master 0ea976eed -> a5d520df7
http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/server/src/test/resources/sql/table_as.iq ---------------------------------------------------------------------- diff --git a/server/src/test/resources/sql/table_as.iq b/server/src/test/resources/sql/table_as.iq new file mode 100644 index 0000000..68ca9f1 --- /dev/null +++ b/server/src/test/resources/sql/table_as.iq @@ -0,0 +1,251 @@ +# table_as.iq - "CREATE TABLE AS ..." DDL +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to you under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +!use server +!set outputformat mysql + +# Create a source table +create table dept (deptno int not null, name varchar(10)); +(0 rows modified) + +!update + +insert into dept +values (10, 'Sales'), (20, 'Marketing'), (30, 'Engineering'); +(3 rows modified) + +!update + +# Create as select +create table d as +select * from dept where deptno > 10; +(0 rows modified) + +!update + +# Check contents +select * from d; ++--------+-------------+ +| DEPTNO | NAME | ++--------+-------------+ +| 20 | Marketing | +| 30 | Engineering | ++--------+-------------+ +(2 rows) + +!ok + +# Try to create again - fails +create table d as +select * from dept where deptno < 30; +Table 'D' already exists +!error + +# Try to create again - fails silently +create table if not exists d as +select * from dept where deptno < 30; +(0 rows modified) + +!update + +# Check contents are unchanged +select * from d; ++--------+-------------+ +| DEPTNO | NAME | ++--------+-------------+ +| 20 | Marketing | +| 30 | Engineering | ++--------+-------------+ +(2 rows) + +!ok + +# Drop +drop table if exists d; +(0 rows modified) + +!update + +# It's gone +select * from d; +Object 'D' not found +!error + +# Drop does nothing because table does not exist +drop table if exists d; +(0 rows modified) + +!update + +# Create table without either AS or column list - fails +create table d; +At line 1, column 14: Missing column list +!error + +# Create table without AS or column types - fails +create table d (x, y); +At line 1, column 17: Type required for column 'X' in CREATE TABLE without AS +!error + +# Create table without AS or column types - fails +create table d (x int, y); +At line 1, column 24: Type required for column 'Y' in CREATE TABLE without AS +!error + +# Create based on itself - fails +create table d2 as select * from d2; +Object 'D2' not found +!error + +# Create table based on UNION +create table d3 as +select deptno as dd from dept where deptno < 15 +union all +select deptno as ee from dept where deptno > 25; +(0 rows modified) + +!update + +# Check contents +select * from d3; ++----+ +| DD | ++----+ +| 10 | +| 30 | ++----+ +(2 rows) + +!ok + +# Drop +drop table d3; +(0 rows modified) + +!update + +# Create table based on UNION and ORDER BY +create table d4 as +select deptno as dd from dept where deptno < 15 +union all +select deptno as dd from dept where deptno > 25 +order by 1 desc; +(0 rows modified) + +!update + +# Check contents +select * from d4; ++----+ +| DD | ++----+ +| 10 | +| 30 | ++----+ +(2 rows) + +!ok + +# Drop +drop table d4; + +# Create table based on VALUES +create table d5 as +values (1, 'a'), (2, 'b'); +(0 rows modified) + +!update + +# Check contents +select * from d5; ++--------+--------+ +| EXPR$0 | EXPR$1 | ++--------+--------+ +| 1 | a | +| 2 | b | ++--------+--------+ +(2 rows) + +!ok + +# Use just aliases +create table d6 (x, y) as +select * from dept where deptno < 15; +(0 rows modified) + +!update + +# Check contents +select * from d6; ++----+-------+ +| X | Y | ++----+-------+ +| 10 | Sales | ++----+-------+ +(1 row) + +!ok + +# Use a mixture of aliases and column declarations +create table d7 (x int, y) as +select * from dept where deptno < 15; +(0 rows modified) + +!update + +# Check contents +select * from d7; ++----+-------+ +| X | Y | ++----+-------+ +| 10 | Sales | ++----+-------+ +(1 row) + +!ok + +# Too many columns +create table d8 (x, y, z) as +select * from dept where deptno < 15; +Number of columns must match number of query columns +!error + +# Too few columns +create table d9 (x) as +select * from dept where deptno < 15; +Number of columns must match number of query columns +!error + +# Specify column names and types +create table d10 (x int, y varchar(20)) as +select * from dept where deptno < 15; +(0 rows modified) + +!update + +# Check contents +select * from d10; ++----+-------+ +| X | Y | ++----+-------+ +| 10 | Sales | ++----+-------+ +(1 row) + +!ok + +# End table_as.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/server/src/test/resources/sql/view.iq ---------------------------------------------------------------------- diff --git a/server/src/test/resources/sql/view.iq b/server/src/test/resources/sql/view.iq new file mode 100755 index 0000000..e1aeff5 --- /dev/null +++ b/server/src/test/resources/sql/view.iq @@ -0,0 +1,183 @@ +# view.iq - DDL on views +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to you under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +!use server +!set outputformat mysql + +# Create a view +create view v as +select a, a + 1 as b +from (values 1, 2) as t(a); +(0 rows modified) + +!update + +select * from v; ++---+---+ +| A | B | ++---+---+ +| 1 | 2 | +| 2 | 3 | ++---+---+ +(2 rows) + +!ok + +# Try to create a view that already exists +create view v as +select a, a + 2 as b +from (values 1, 2) as t(a); +View 'V' already exists and REPLACE not specified +!error + +create or replace view v as +select a, a + 3 as b +from (values 1, 2) as t(a); +(0 rows modified) + +!update + +select * from v; ++---+---+ +| A | B | ++---+---+ +| 1 | 4 | +| 2 | 5 | ++---+---+ +(2 rows) + +!ok + +# Drop view +drop view v; +(0 rows modified) + +!update + +# Explicit column names +create view v (x, "y z") as +select a, a + 4 as b +from (values 1, 2) as t(a); +(0 rows modified) + +!update + +select * from v; ++---+-----+ +| X | y z | ++---+-----+ +| 1 | 5 | +| 2 | 6 | ++---+-----+ +(2 rows) + +!ok + +# Wrong number of columns +create or replace view v (x, y, z) as +select a, a + 5 as b +from (values 1, 2) as t(a); +List of column aliases must have same degree as table; table has 2 columns ('A', 'B'), whereas alias list has 3 columns +!error + +# Column names not unique +create or replace view v (x, x) as +select a, a + 6 as b +from (values 1, 2) as t(a); +Duplicate name 'X' in column alias list +!error + +# View based on VALUES +create or replace view v (p, q) as +values (1, 'a'), (2, 'b'); +(0 rows modified) + +!update + +select * from v; ++---+---+ +| P | Q | ++---+---+ +| 1 | a | +| 2 | b | ++---+---+ +(2 rows) + +!ok + +# View based on table +create table t (i int); +(0 rows modified) + +!update + +insert into t values (1), (2), (3); +(3 rows modified) + +!update + +create or replace view v (e, d) as +select i, i + 1 from t; +(0 rows modified) + +!update + +# View based on UNION of another view +create or replace view v2 as +select * from v +union all +select e + d, e - d from v; +(0 rows modified) + +!update + +select * from v2; ++---+----+ +| E | D | ++---+----+ +| 1 | 2 | +| 2 | 3 | +| 3 | -1 | +| 3 | 4 | +| 5 | -1 | +| 7 | -1 | ++---+----+ +(6 rows) + +!ok + +# Drop view, then make sure that a query can't find it +drop view if exists v2; +(0 rows modified) + +!update + +select * from v2; +Object 'V2' not found +!error + +# Try to drop view that does not exist +drop view v3; +View 'V3' not found +!error + +drop view if exists v3; +(0 rows modified) + +!update + +# End view.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/site/_docs/adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/adapter.md b/site/_docs/adapter.md index 1c2a312..5562c4c 100644 --- a/site/_docs/adapter.md +++ b/site/_docs/adapter.md @@ -137,6 +137,82 @@ makes a connection to the Cassandra adapter, equivalent to writing the following Note how each key in the `operand` section appears with a `schema.` prefix in the connect string. +## Server + +Calcite's core module (`calcite-core`) supports SQL queries (`SELECT`) and DML +operations (`INSERT`, `UPDATE`, `DELETE`, `MERGE`) +but does not support DDL operations such as `CREATE SCHEMA` or `CREATE TABLE`. +As we shall see, DDL complicates the state model of the repository and makes +the parser more difficult to extend, so we left DDL out of core. + +The server module (`calcite-server`) adds DDL support to Calcite. +It extends the SQL parser, +[using the same mechanism used by sub-projects](#extending-the-parser), +adding some DDL commands: + +* `CREATE` and `DROP SCHEMA` +* `CREATE` and `DROP FOREIGN SCHEMA` +* `CREATE` and `DROP TABLE` (including `CREATE TABLE ... AS SELECT`) +* `CREATE` and `DROP MATERIALIZED VIEW` +* `CREATE` and `DROP VIEW` + +Commands are described in the [SQL reference](reference.html#ddl-extensions). + +To enable, include `calcite-server.jar` in your class path, and add +`parserFactory=org.apache.calcite.sql.parser.ddl.SqlDdlParserImpl#FACTORY` +to the JDBC connect string (see connect string property +[parserFactory]({{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#PARSER_FACTORY)). +Here is an example using the `sqlline` shell. + +{% highlight sql %} +$ ./sqlline +sqlline version 1.3.0 +> !connect jdbc:calcite:parserFactory=org.apache.calcite.sql.parser.ddl.SqlDdlParserImpl#FACTORY sa "" +> CREATE TABLE t (i INTEGER, j VARCHAR(10)); +No rows affected (0.293 seconds) +> INSERT INTO t VALUES (1, 'a'), (2, 'bc'); +2 rows affected (0.873 seconds) +> CREATE VIEW v AS SELECT * FROM t WHERE i > 1; +No rows affected (0.072 seconds) +> SELECT count(*) FROM v; ++---------------------+ +| EXPR$0 | ++---------------------+ +| 1 | ++---------------------+ +1 row selected (0.148 seconds) +> !quit +{% endhighlight %} + +The `calcite-server` module is optional. +One of its goals is to showcase Calcite's capabilities +(for example materialized views, foreign tables and generated columns) using +concise examples that you can try from the SQL command line. +All of the capabilities used by `calcite-server` are available via APIs in +`calcite-core`. + +If you are the author of a sub-project, it is unlikely that your syntax +extensions match those in `calcite-server`, so we recommend that you add your +SQL syntax extensions by [extending the core parser](#extending-the-parser); +if you want DDL commands, you may be able to copy-paste from `calcite-server` +into your project. + +At present, the repository is not persisted. As you execute DDL commands, you +are modifying an in-memory repository by adding and removing objects +reachable from a root +[<tt>Schema</tt>]({{ site.apiRoot }}/org/apache/calcite/schema/Schema.html). +All commands within the same SQL session will see those objects. +You can create the same objects in a future session by executing the same +script of SQL commands. + +Calcite could also act as a data virtualization or federation server: +Calcite manages data in multiple foreign schemas, but to a client the data +all seems to be in the same place. Calcite chooses where processing should +occur, and whether to create copies of data for efficiency. +The `calcite-server` module is a step towards that goal; an +industry-strength solution would require further on packaging (to make Calcite +runnable as a service), repository persistence, authorization and security. + ## Extensibility There are many other APIs that allow you to extend Calcite's capabilities. http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index ea6e983..ce85272 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2035,7 +2035,7 @@ measureColumn: expression AS alias pattern: - patternTerm ['|' patternTerm ]* + patternTerm [ '|' patternTerm ]* patternTerm: patternFactor [ patternFactor ]* @@ -2067,3 +2067,98 @@ intervalLiteral: In *patternQuantifier*, *repeat* is a positive integer, and *minRepeat* and *maxRepeat* are non-negative integers. + +### DDL Extensions + +DDL extensions are only available in the calcite-server module. +To enable, include `calcite-server.jar` in your class path, and add +`parserFactory=org.apache.calcite.sql.parser.ddl.SqlDdlParserImpl#FACTORY` +to the JDBC connect string (see connect string property +[parserFactory]({{ site.apiRoot }}/org/apache/calcite/config/CalciteConnectionProperty.html#PARSER_FACTORY)). + +{% highlight sql %} +ddlStatement: + createSchemaStatement + | createForeignSchemaStatement + | createTableStatement + | createViewStatement + | createMaterializedViewStatement + | dropSchemaStatement + | dropForeignSchemaStatement + | dropTableStatement + | dropViewStatement + | dropMaterializedViewStatement + +createSchemaStatement: + CREATE [ OR REPLACE ] SCHEMA [ IF NOT EXISTS ] name + +createForeignSchemaStatement: + CREATE [ OR REPLACE ] FOREIGN SCHEMA [ IF NOT EXISTS ] name + ( + TYPE 'type' + | LIBRARY 'com.example.calcite.ExampleSchemaFactory' + ) + [ OPTIONS '(' option [, option ]* ')' ] + +option: + name literal + +createTableStatement: + CREATE TABLE [ IF NOT EXISTS ] name + [ '(' tableElement [, tableElement ]* ')' ] + [ AS query ] + +tableElement: + columnName type [ columnGenerator ] [ columnConstraint ] + | columnName + | tableConstraint + +columnGenerator: + DEFAULT expression + | [ GENERATED ALWAYS ] AS '(' expression ')' + { VIRTUAL | STORED } + +columnConstraint: + [ CONSTRAINT name ] + [ NOT ] NULL + +tableConstraint: + [ CONSTRAINT name ] + { + CHECK '(' expression ')' + | PRIMARY KEY '(' columnName [, columnName ]* ')' + | UNIQUE '(' columnName [, columnName ]* ')' + } + +createViewStatement: + CREATE [ OR REPLACE ] VIEW name + [ '(' columnName [, columnName ]* ')' ] + AS query + +createMaterializedViewStatement: + CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name + [ '(' columnName [, columnName ]* ')' ] + AS query + +dropSchemaStatement: + DROP SCHEMA name [ IF EXISTS ] + +dropForeignSchemaStatement: + DROP FOREIGN SCHEMA name [ IF EXISTS ] + +dropTableStatement: + DROP TABLE name [ IF EXISTS ] + +dropViewStatement: + DROP VIEW name [ IF EXISTS ] + +dropMaterializedViewStatement: + DROP MATERIALIZED VIEW name [ IF EXISTS ] +{% endhighlight %} + +In *createTableStatement*, if you specify *AS query*, you may omit the list of +*tableElement*s, or you can omit the data type of any *tableElement*, in which +case it just renames the underlying column. + +In *columnGenerator*, if you do not specify `VIRTUAL` or `STORED` for a +generated column, `VIRTUAL` is the default. http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/sqlline ---------------------------------------------------------------------- diff --git a/sqlline b/sqlline index b0fc489..b23dcd9 100755 --- a/sqlline +++ b/sqlline @@ -37,7 +37,7 @@ if [ ! -f target/fullclasspath.txt ]; then fi CP= -for module in core cassandra druid elasticsearch2 elasticsearch5 file mongodb spark splunk example/csv example/function; do +for module in core cassandra druid elasticsearch2 elasticsearch5 file mongodb server spark splunk example/csv example/function; do CP=${CP}${module}/target/classes: CP=${CP}${module}/target/test-classes: done http://git-wip-us.apache.org/repos/asf/calcite/blob/238b3225/sqlline.bat ---------------------------------------------------------------------- diff --git a/sqlline.bat b/sqlline.bat index 53f7159..f7165c0 100644 --- a/sqlline.bat +++ b/sqlline.bat @@ -23,6 +23,6 @@ :: Copy dependency jars on first call. (To force jar refresh, remove target\dependencies) if not exist target\dependencies (call mvn -B dependency:copy-dependencies -DoverWriteReleases=false -DoverWriteSnapshots=false -DoverWriteIfNewer=true -DoutputDirectory=target\dependencies) -java -Xmx1G -cp ".\target\dependencies\*;core\target\dependencies\*;cassandra\target\dependencies\*;druid\target\dependencies\*;elasticsearch2\target\dependencies\*;elasticsearch5\target\dependencies\*;file\target\dependencies\*;mongodb\target\dependencies\*;spark\target\dependencies\*;splunk\target\dependencies\*" sqlline.SqlLine --verbose=true %* +java -Xmx1G -cp ".\target\dependencies\*;core\target\dependencies\*;cassandra\target\dependencies\*;druid\target\dependencies\*;elasticsearch2\target\dependencies\*;elasticsearch5\target\dependencies\*;file\target\dependencies\*;mongodb\target\dependencies\*;server\target\dependencies\*;spark\target\dependencies\*;splunk\target\dependencies\*" sqlline.SqlLine --verbose=true %* :: End sqlline.bat
