This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 9b678f1d7ffe030711d86f475c3a9e9e95bacb8f Author: Julian Hyde <[email protected]> AuthorDate: Sun Jul 19 17:23:08 2020 -0700 [CALCITE-4128] Remove dependency of File adapter on Example CSV adapter Move some classes from 'example/csv' to 'file', and make CSV adapter depend on File adapter. Copy some other classes (so that we can keep the 'example/csv' versions simple, and add functionality to the 'file' adapter). Rename SqlTest to FileAdapterTest, factor utility methods into FileAdapterTests, and copy in a bunch of tests from example/csv/.../CsvTest. --- example/csv/build.gradle.kts | 1 + .../calcite/adapter/csv/CsvFilterableTable.java | 7 +- .../calcite/adapter/csv/CsvScannableTable.java | 7 +- .../org/apache/calcite/adapter/csv/CsvSchema.java | 1 + .../calcite/adapter/csv/CsvSchemaFactory.java | 4 - .../adapter/csv/CsvStreamScannableTable.java | 7 +- .../org/apache/calcite/adapter/csv/CsvTable.java | 2 + .../apache/calcite/adapter/csv/CsvTableScan.java | 3 +- .../calcite/adapter/csv/CsvTranslatableTable.java | 9 +- file/build.gradle.kts | 4 +- .../calcite/adapter/file}/CsvEnumerator.java | 85 +- .../apache/calcite/adapter/file}/CsvFieldType.java | 4 +- .../adapter/file/CsvProjectTableScanRule.java | 83 ++ .../calcite/adapter/file}/CsvStreamReader.java | 2 +- .../org/apache/calcite/adapter/file}/CsvTable.java | 5 +- ...FileSchemaFactory.java => CsvTableFactory.java} | 41 +- .../apache/calcite/adapter/file}/CsvTableScan.java | 8 +- .../adapter/file}/CsvTranslatableTable.java | 18 +- .../apache/calcite/adapter/file/FileRules.java} | 17 +- .../apache/calcite/adapter/file/FileSchema.java | 55 +- .../calcite/adapter/file/FileSchemaFactory.java | 20 +- .../calcite/adapter/file}/JsonEnumerator.java | 2 +- .../calcite/adapter/file}/JsonScannableTable.java | 2 +- .../apache/calcite/adapter/file}/JsonTable.java | 4 +- .../calcite/adapter/file/FileAdapterTest.java | 890 +++++++++++++++++++++ .../calcite/adapter/file/FileAdapterTests.java | 222 +++++ .../org/apache/calcite/adapter/file/SqlTest.java | 419 ---------- .../test/resources/bug.json} | 24 +- file/src/test/resources/bug.yaml | 24 + .../test/resources/bug/ARCHERS.json} | 37 +- file/src/test/resources/bug/DATE.csv | 9 + file/src/test/resources/bug/LONG_EMPS.csv | 6 + file/src/test/resources/bug/WACKY_COLUMN_NAMES.csv | 6 + .../test/resources/filterable-model.json} | 29 +- .../test/resources/model-with-custom-table.json} | 30 +- .../test/resources/model.json} | 26 +- file/src/test/resources/sales/DEPTS.csv | 4 + file/src/test/resources/sales/EMPS.csv.gz | Bin 0 -> 262 bytes .../test/resources/smart.json} | 34 +- 39 files changed, 1523 insertions(+), 628 deletions(-) diff --git a/example/csv/build.gradle.kts b/example/csv/build.gradle.kts index c2ac70f..ca5ea27 100644 --- a/example/csv/build.gradle.kts +++ b/example/csv/build.gradle.kts @@ -21,6 +21,7 @@ val sqllineClasspath by configurations.creating { dependencies { api(project(":core")) + api(project(":file")) api(project(":linq4j")) implementation("com.fasterxml.jackson.core:jackson-core") diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFilterableTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFilterableTable.java index f45e4c4..c9cae65 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFilterableTable.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFilterableTable.java @@ -17,6 +17,8 @@ package org.apache.calcite.adapter.csv; import org.apache.calcite.DataContext; +import org.apache.calcite.adapter.file.CsvEnumerator; +import org.apache.calcite.adapter.file.CsvFieldType; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -27,6 +29,7 @@ import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; import org.apache.calcite.schema.FilterableTable; import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.util.ImmutableIntList; import org.apache.calcite.util.Source; import java.util.List; @@ -53,12 +56,12 @@ public class CsvFilterableTable extends CsvTable final List<CsvFieldType> fieldTypes = getFieldTypes(root.getTypeFactory()); final String[] filterValues = new String[fieldTypes.size()]; filters.removeIf(filter -> addFilter(filter, filterValues)); - final int[] fields = CsvEnumerator.identityList(fieldTypes.size()); + final List<Integer> fields = ImmutableIntList.identity(fieldTypes.size()); final AtomicBoolean cancelFlag = DataContext.Variable.CANCEL_FLAG.get(root); return new AbstractEnumerable<Object[]>() { public Enumerator<Object[]> enumerator() { return new CsvEnumerator<>(source, cancelFlag, false, filterValues, - new CsvEnumerator.ArrayRowConverter(fieldTypes, fields)); + CsvEnumerator.arrayConverter(fieldTypes, fields, false)); } }; } diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvScannableTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvScannableTable.java index 237373d..bdc2af0 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvScannableTable.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvScannableTable.java @@ -17,11 +17,14 @@ package org.apache.calcite.adapter.csv; import org.apache.calcite.DataContext; +import org.apache.calcite.adapter.file.CsvEnumerator; +import org.apache.calcite.adapter.file.CsvFieldType; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; import org.apache.calcite.rel.type.RelProtoDataType; import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.util.ImmutableIntList; import org.apache.calcite.util.Source; import java.util.List; @@ -46,12 +49,12 @@ public class CsvScannableTable extends CsvTable public Enumerable<Object[]> scan(DataContext root) { final List<CsvFieldType> fieldTypes = getFieldTypes(root.getTypeFactory()); - final int[] fields = CsvEnumerator.identityList(fieldTypes.size()); + final List<Integer> fields = ImmutableIntList.identity(fieldTypes.size()); final AtomicBoolean cancelFlag = DataContext.Variable.CANCEL_FLAG.get(root); return new AbstractEnumerable<Object[]>() { public Enumerator<Object[]> enumerator() { return new CsvEnumerator<>(source, cancelFlag, false, null, - new CsvEnumerator.ArrayRowConverter(fieldTypes, fields)); + CsvEnumerator.arrayConverter(fieldTypes, fields, false)); } }; } diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java index c5780a1..addefd3 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchema.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.adapter.csv; +import org.apache.calcite.adapter.file.JsonScannableTable; import org.apache.calcite.schema.Table; import org.apache.calcite.schema.impl.AbstractSchema; import org.apache.calcite.util.Source; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchemaFactory.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchemaFactory.java index 624c6e8..cadeb59 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchemaFactory.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvSchemaFactory.java @@ -33,10 +33,6 @@ import java.util.Map; */ @SuppressWarnings("UnusedDeclaration") public class CsvSchemaFactory implements SchemaFactory { - /** Name of the column that is implicitly created in a CSV stream table - * to hold the data arrival time. */ - static final String ROWTIME_COLUMN_NAME = "ROWTIME"; - /** Public singleton, per factory contract. */ public static final CsvSchemaFactory INSTANCE = new CsvSchemaFactory(); diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamScannableTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamScannableTable.java index c5b9497..8285473 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamScannableTable.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamScannableTable.java @@ -17,6 +17,8 @@ package org.apache.calcite.adapter.csv; import org.apache.calcite.DataContext; +import org.apache.calcite.adapter.file.CsvEnumerator; +import org.apache.calcite.adapter.file.CsvFieldType; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -24,6 +26,7 @@ import org.apache.calcite.rel.type.RelProtoDataType; import org.apache.calcite.schema.ScannableTable; import org.apache.calcite.schema.StreamableTable; import org.apache.calcite.schema.Table; +import org.apache.calcite.util.ImmutableIntList; import org.apache.calcite.util.Source; import java.util.List; @@ -52,12 +55,12 @@ public class CsvStreamScannableTable extends CsvScannableTable public Enumerable<Object[]> scan(DataContext root) { final List<CsvFieldType> fieldTypes = getFieldTypes(root.getTypeFactory()); - final int[] fields = CsvEnumerator.identityList(fieldTypes.size()); + final List<Integer> fields = ImmutableIntList.identity(fieldTypes.size()); final AtomicBoolean cancelFlag = DataContext.Variable.CANCEL_FLAG.get(root); return new AbstractEnumerable<Object[]>() { public Enumerator<Object[]> enumerator() { return new CsvEnumerator<>(source, cancelFlag, true, null, - new CsvEnumerator.ArrayRowConverter(fieldTypes, fields, true)); + CsvEnumerator.arrayConverter(fieldTypes, fields, true)); } }; } diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java index d0f6396..8f7cc78 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java @@ -16,6 +16,8 @@ */ package org.apache.calcite.adapter.csv; +import org.apache.calcite.adapter.file.CsvEnumerator; +import org.apache.calcite.adapter.file.CsvFieldType; import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java index ed0a885..f591e29 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java @@ -21,6 +21,7 @@ import org.apache.calcite.adapter.enumerable.EnumerableRel; import org.apache.calcite.adapter.enumerable.EnumerableRelImplementor; import org.apache.calcite.adapter.enumerable.PhysType; import org.apache.calcite.adapter.enumerable.PhysTypeImpl; +import org.apache.calcite.adapter.file.JsonTable; import org.apache.calcite.linq4j.tree.Blocks; import org.apache.calcite.linq4j.tree.Expressions; import org.apache.calcite.linq4j.tree.Primitive; @@ -44,7 +45,7 @@ import java.util.List; /** * Relational expression representing a scan of a CSV file. * - * <p>Like any table scan, it serves as a leaf node of a query tree.</p> + * <p>Like any table scan, it serves as a leaf node of a query tree. */ public class CsvTableScan extends TableScan implements EnumerableRel { final CsvTranslatableTable csvTable; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java index 7a4e13c..e183200 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java +++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java @@ -17,6 +17,7 @@ package org.apache.calcite.adapter.csv; import org.apache.calcite.DataContext; +import org.apache.calcite.adapter.file.CsvEnumerator; import org.apache.calcite.linq4j.AbstractEnumerable; import org.apache.calcite.linq4j.Enumerable; import org.apache.calcite.linq4j.Enumerator; @@ -30,6 +31,7 @@ import org.apache.calcite.schema.QueryableTable; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.schema.Schemas; import org.apache.calcite.schema.TranslatableTable; +import org.apache.calcite.util.ImmutableIntList; import org.apache.calcite.util.Source; import java.lang.reflect.Type; @@ -49,9 +51,8 @@ public class CsvTranslatableTable extends CsvTable return "CsvTranslatableTable"; } - /** Returns an enumerable over a given projection of the fields. - * - * <p>Called from generated code. */ + /** Returns an enumerable over a given projection of the fields. */ + @SuppressWarnings("unused") // called from generated code public Enumerable<Object> project(final DataContext root, final int[] fields) { final AtomicBoolean cancelFlag = DataContext.Variable.CANCEL_FLAG.get(root); @@ -61,7 +62,7 @@ public class CsvTranslatableTable extends CsvTable source, cancelFlag, getFieldTypes(root.getTypeFactory()), - fields); + ImmutableIntList.of(fields)); } }; } diff --git a/file/build.gradle.kts b/file/build.gradle.kts index 1610a03..53555a8 100644 --- a/file/build.gradle.kts +++ b/file/build.gradle.kts @@ -16,12 +16,14 @@ */ dependencies { api(project(":core")) - api(project(":example:csv")) api(project(":linq4j")) implementation("com.google.guava:guava") implementation("com.joestelmach:natty") + implementation("net.sf.opencsv:opencsv") implementation("org.apache.calcite.avatica:avatica-core") + implementation("commons-io:commons-io") + implementation("org.apache.commons:commons-lang3") implementation("org.jsoup:jsoup") testImplementation(project(":core", "testClasses")) diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvEnumerator.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvEnumerator.java similarity index 81% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvEnumerator.java rename to file/src/main/java/org/apache/calcite/adapter/file/CsvEnumerator.java index cdcc967..091a4a1 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvEnumerator.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvEnumerator.java @@ -14,13 +14,15 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.avatica.util.DateTimeUtils; import org.apache.calcite.linq4j.Enumerator; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableIntList; +import org.apache.calcite.util.ImmutableNullableList; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Source; @@ -41,9 +43,9 @@ import java.util.concurrent.atomic.AtomicBoolean; * * @param <E> Row type */ -class CsvEnumerator<E> implements Enumerator<E> { +public class CsvEnumerator<E> implements Enumerator<E> { private final CSVReader reader; - private final String[] filterValues; + private final List<String> filterValues; private final AtomicBoolean cancelFlag; private final RowConverter<E> rowConverter; private E current; @@ -60,23 +62,19 @@ class CsvEnumerator<E> implements Enumerator<E> { FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss", gmt); } - CsvEnumerator(Source source, AtomicBoolean cancelFlag, - List<CsvFieldType> fieldTypes) { - this(source, cancelFlag, fieldTypes, identityList(fieldTypes.size())); - } - - CsvEnumerator(Source source, AtomicBoolean cancelFlag, - List<CsvFieldType> fieldTypes, int[] fields) { + public CsvEnumerator(Source source, AtomicBoolean cancelFlag, + List<CsvFieldType> fieldTypes, List<Integer> fields) { //noinspection unchecked this(source, cancelFlag, false, null, (RowConverter<E>) converter(fieldTypes, fields)); } - CsvEnumerator(Source source, AtomicBoolean cancelFlag, boolean stream, + public CsvEnumerator(Source source, AtomicBoolean cancelFlag, boolean stream, String[] filterValues, RowConverter<E> rowConverter) { this.cancelFlag = cancelFlag; this.rowConverter = rowConverter; - this.filterValues = filterValues; + this.filterValues = filterValues == null ? null + : ImmutableNullableList.copyOf(filterValues); try { if (stream) { this.reader = new CsvStreamReader(source); @@ -90,15 +88,20 @@ class CsvEnumerator<E> implements Enumerator<E> { } private static RowConverter<?> converter(List<CsvFieldType> fieldTypes, - int[] fields) { - if (fields.length == 1) { - final int field = fields[0]; + List<Integer> fields) { + if (fields.size() == 1) { + final int field = fields.get(0); return new SingleColumnRowConverter(fieldTypes.get(field), field); } else { - return new ArrayRowConverter(fieldTypes, fields); + return arrayConverter(fieldTypes, fields, false); } } + public static RowConverter<Object[]> arrayConverter( + List<CsvFieldType> fieldTypes, List<Integer> fields, boolean stream) { + return new ArrayRowConverter(fieldTypes, fields, stream); + } + /** Deduces the names and types of a table's columns by reading the first line * of a CSV file. */ static RelDataType deduceRowType(JavaTypeFactory typeFactory, Source source, @@ -108,12 +111,12 @@ class CsvEnumerator<E> implements Enumerator<E> { /** Deduces the names and types of a table's columns by reading the first line * of a CSV file. */ - static RelDataType deduceRowType(JavaTypeFactory typeFactory, Source source, - List<CsvFieldType> fieldTypes, Boolean stream) { + public static RelDataType deduceRowType(JavaTypeFactory typeFactory, + Source source, List<CsvFieldType> fieldTypes, Boolean stream) { final List<RelDataType> types = new ArrayList<>(); final List<String> names = new ArrayList<>(); if (stream) { - names.add(CsvSchemaFactory.ROWTIME_COLUMN_NAME); + names.add(FileSchemaFactory.ROWTIME_COLUMN_NAME); types.add(typeFactory.createSqlType(SqlTypeName.TIMESTAMP)); } try (CSVReader reader = openCsv(source)) { @@ -161,7 +164,7 @@ class CsvEnumerator<E> implements Enumerator<E> { return typeFactory.createStructType(Pair.zip(names, types)); } - public static CSVReader openCsv(Source source) throws IOException { + static CSVReader openCsv(Source source) throws IOException { Objects.requireNonNull(source, "source"); return new CSVReader(source.reader()); } @@ -193,7 +196,7 @@ class CsvEnumerator<E> implements Enumerator<E> { } if (filterValues != null) { for (int i = 0; i < strings.length; i++) { - String filterValue = filterValues[i]; + String filterValue = filterValues.get(i); if (filterValue != null) { if (!filterValue.equals(strings[i])) { continue outer; @@ -222,7 +225,7 @@ class CsvEnumerator<E> implements Enumerator<E> { } /** Returns an array of integers {0, ..., n - 1}. */ - static int[] identityList(int n) { + public static int[] identityList(int n) { int[] integers = new int[n]; for (int i = 0; i < n; i++) { integers[i] = i; @@ -315,20 +318,16 @@ class CsvEnumerator<E> implements Enumerator<E> { /** Array row converter. */ static class ArrayRowConverter extends RowConverter<Object[]> { - private final CsvFieldType[] fieldTypes; - private final int[] fields; - // whether the row to convert is from a stream + /** Field types. List must not be null, but any element may be null. */ + private final List<CsvFieldType> fieldTypes; + private final ImmutableIntList fields; + /** Whether the row to convert is from a stream. */ private final boolean stream; - ArrayRowConverter(List<CsvFieldType> fieldTypes, int[] fields) { - this.fieldTypes = fieldTypes.toArray(new CsvFieldType[0]); - this.fields = fields; - this.stream = false; - } - - ArrayRowConverter(List<CsvFieldType> fieldTypes, int[] fields, boolean stream) { - this.fieldTypes = fieldTypes.toArray(new CsvFieldType[0]); - this.fields = fields; + ArrayRowConverter(List<CsvFieldType> fieldTypes, List<Integer> fields, + boolean stream) { + this.fieldTypes = ImmutableNullableList.copyOf(fieldTypes); + this.fields = ImmutableIntList.copyOf(fields); this.stream = stream; } @@ -341,27 +340,27 @@ class CsvEnumerator<E> implements Enumerator<E> { } public Object[] convertNormalRow(String[] strings) { - final Object[] objects = new Object[fields.length]; - for (int i = 0; i < fields.length; i++) { - int field = fields[i]; - objects[i] = convert(fieldTypes[field], strings[field]); + final Object[] objects = new Object[fields.size()]; + for (int i = 0; i < fields.size(); i++) { + int field = fields.get(i); + objects[i] = convert(fieldTypes.get(field), strings[field]); } return objects; } public Object[] convertStreamRow(String[] strings) { - final Object[] objects = new Object[fields.length + 1]; + final Object[] objects = new Object[fields.size() + 1]; objects[0] = System.currentTimeMillis(); - for (int i = 0; i < fields.length; i++) { - int field = fields[i]; - objects[i + 1] = convert(fieldTypes[field], strings[field]); + for (int i = 0; i < fields.size(); i++) { + int field = fields.get(i); + objects[i + 1] = convert(fieldTypes.get(field), strings[field]); } return objects; } } /** Single column row converter. */ - private static class SingleColumnRowConverter extends RowConverter { + private static class SingleColumnRowConverter extends RowConverter<Object> { private final CsvFieldType fieldType; private final int fieldIndex; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFieldType.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvFieldType.java similarity index 97% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFieldType.java rename to file/src/main/java/org/apache/calcite/adapter/file/CsvFieldType.java index 3e50e59..9f1d28b 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvFieldType.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvFieldType.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.linq4j.tree.Primitive; @@ -30,7 +30,7 @@ import java.util.Map; * of type {@link #STRING}. But specifying the field type in the header row * makes it easier to write SQL.</p> */ -enum CsvFieldType { +public enum CsvFieldType { STRING(String.class, "string"), BOOLEAN(Primitive.BOOLEAN), BYTE(Primitive.BYTE), diff --git a/file/src/main/java/org/apache/calcite/adapter/file/CsvProjectTableScanRule.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvProjectTableScanRule.java new file mode 100644 index 0000000..4d5416c --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvProjectTableScanRule.java @@ -0,0 +1,83 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.adapter.file; + +import org.apache.calcite.plan.RelOptRuleCall; +import org.apache.calcite.plan.RelRule; +import org.apache.calcite.rel.logical.LogicalProject; +import org.apache.calcite.rex.RexInputRef; +import org.apache.calcite.rex.RexNode; + +import java.util.List; + +/** + * Planner rule that projects from a {@link CsvTableScan} scan just the columns + * needed to satisfy a projection. If the projection's expressions are trivial, + * the projection is removed. + * + * @see FileRules#PROJECT_SCAN + */ +public class CsvProjectTableScanRule + extends RelRule<CsvProjectTableScanRule.Config> { + + /** Creates a CsvProjectTableScanRule. */ + protected CsvProjectTableScanRule(Config config) { + super(config); + } + + @Override public void onMatch(RelOptRuleCall call) { + final LogicalProject project = call.rel(0); + final CsvTableScan scan = call.rel(1); + int[] fields = getProjectFields(project.getProjects()); + if (fields == null) { + // Project contains expressions more complex than just field references. + return; + } + call.transformTo( + new CsvTableScan( + scan.getCluster(), + scan.getTable(), + scan.csvTable, + fields)); + } + + private int[] getProjectFields(List<RexNode> exps) { + final int[] fields = new int[exps.size()]; + for (int i = 0; i < exps.size(); i++) { + final RexNode exp = exps.get(i); + if (exp instanceof RexInputRef) { + fields[i] = ((RexInputRef) exp).getIndex(); + } else { + return null; // not a simple projection + } + } + return fields; + } + + /** Rule configuration. */ + public interface Config extends RelRule.Config { + Config DEFAULT = EMPTY + .withOperandSupplier(b0 -> + b0.operand(LogicalProject.class).oneInput(b1 -> + b1.operand(CsvTableScan.class).noInputs())) + .as(Config.class); + + @Override default CsvProjectTableScanRule toRule() { + return new CsvProjectTableScanRule(this); + } + } +} diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamReader.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvStreamReader.java similarity index 99% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamReader.java rename to file/src/main/java/org/apache/calcite/adapter/file/CsvStreamReader.java index e47b3f4..633079a 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvStreamReader.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvStreamReader.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.util.Source; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvTable.java similarity index 94% copy from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java copy to file/src/main/java/org/apache/calcite/adapter/file/CsvTable.java index d0f6396..20f0023 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTable.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvTable.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.adapter.java.JavaTypeFactory; import org.apache.calcite.rel.type.RelDataType; @@ -28,6 +28,9 @@ import java.util.List; /** * Base class for table that reads CSV files. + * + * <p>Copied from {@code CsvFilterableTable} in demo CSV adapter, + * with more advanced features. */ public abstract class CsvTable extends AbstractTable { protected final Source source; diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvTableFactory.java similarity index 53% copy from file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java copy to file/src/main/java/org/apache/calcite/adapter/file/CsvTableFactory.java index a20d1e6..75e992b 100644 --- a/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvTableFactory.java @@ -17,40 +17,37 @@ package org.apache.calcite.adapter.file; import org.apache.calcite.model.ModelHandler; -import org.apache.calcite.schema.Schema; -import org.apache.calcite.schema.SchemaFactory; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeImpl; +import org.apache.calcite.rel.type.RelProtoDataType; import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.schema.TableFactory; +import org.apache.calcite.util.Source; +import org.apache.calcite.util.Sources; import java.io.File; -import java.util.List; import java.util.Map; /** - * Factory that creates a {@link FileSchema}. + * Factory that creates a {@link CsvTranslatableTable}. * - * <p>Allows a custom schema to be included in a model.json file. - * See <a href="http://calcite.apache.org/docs/file_adapter.html";>File adapter</a>. + * <p>Allows a file-based table to be included in a model.json file, even in a + * schema that is not based upon {@link FileSchema}. */ @SuppressWarnings("UnusedDeclaration") -public class FileSchemaFactory implements SchemaFactory { +public class CsvTableFactory implements TableFactory<CsvTable> { // public constructor, per factory contract - public FileSchemaFactory() { + public CsvTableFactory() { } - public Schema create(SchemaPlus parentSchema, String name, - Map<String, Object> operand) { - @SuppressWarnings("unchecked") List<Map<String, Object>> tables = - (List) operand.get("tables"); - final File baseDirectory = + public CsvTable create(SchemaPlus schema, String name, + Map<String, Object> operand, RelDataType rowType) { + String fileName = (String) operand.get("file"); + final File base = (File) operand.get(ModelHandler.ExtraOperand.BASE_DIRECTORY.camelName); - File directoryFile = baseDirectory; - final String directory = (String) operand.get("directory"); - if (baseDirectory != null && directory != null) { - directoryFile = new File(directory); - if (!directoryFile.isAbsolute()) { - directoryFile = new File(baseDirectory, directory); - } - } - return new FileSchema(parentSchema, name, directoryFile, tables); + final Source source = Sources.file(base, fileName); + final RelProtoDataType protoRowType = + rowType != null ? RelDataTypeImpl.proto(rowType) : null; + return new CsvTranslatableTable(source, protoRowType); } } diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvTableScan.java similarity index 97% copy from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java copy to file/src/main/java/org/apache/calcite/adapter/file/CsvTableScan.java index ed0a885..8d6eb87 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvTableScan.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.adapter.enumerable.EnumerableConvention; import org.apache.calcite.adapter.enumerable.EnumerableRel; @@ -44,11 +44,11 @@ import java.util.List; /** * Relational expression representing a scan of a CSV file. * - * <p>Like any table scan, it serves as a leaf node of a query tree.</p> + * <p>Like any table scan, it serves as a leaf node of a query tree. */ public class CsvTableScan extends TableScan implements EnumerableRel { final CsvTranslatableTable csvTable; - final int[] fields; + private final int[] fields; protected CsvTableScan(RelOptCluster cluster, RelOptTable table, CsvTranslatableTable csvTable, int[] fields) { @@ -80,7 +80,7 @@ public class CsvTableScan extends TableScan implements EnumerableRel { } @Override public void register(RelOptPlanner planner) { - planner.addRule(CsvRules.PROJECT_SCAN); + planner.addRule(FileRules.PROJECT_SCAN); } @Override public RelOptCost computeSelfCost(RelOptPlanner planner, diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java b/file/src/main/java/org/apache/calcite/adapter/file/CsvTranslatableTable.java similarity index 88% copy from example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java copy to file/src/main/java/org/apache/calcite/adapter/file/CsvTranslatableTable.java index 7a4e13c..9f86c53 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTranslatableTable.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/CsvTranslatableTable.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.DataContext; import org.apache.calcite.linq4j.AbstractEnumerable; @@ -30,6 +30,7 @@ import org.apache.calcite.schema.QueryableTable; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.schema.Schemas; import org.apache.calcite.schema.TranslatableTable; +import org.apache.calcite.util.ImmutableIntList; import org.apache.calcite.util.Source; import java.lang.reflect.Type; @@ -37,6 +38,9 @@ import java.util.concurrent.atomic.AtomicBoolean; /** * Table based on a CSV file. + * + * <p>Copied from {@code CsvTranslatableTable} in demo CSV adapter, + * with more advanced features. */ public class CsvTranslatableTable extends CsvTable implements QueryableTable, TranslatableTable { @@ -49,19 +53,15 @@ public class CsvTranslatableTable extends CsvTable return "CsvTranslatableTable"; } - /** Returns an enumerable over a given projection of the fields. - * - * <p>Called from generated code. */ + /** Returns an enumerable over a given projection of the fields. */ + @SuppressWarnings("unused") // called from generated code public Enumerable<Object> project(final DataContext root, final int[] fields) { final AtomicBoolean cancelFlag = DataContext.Variable.CANCEL_FLAG.get(root); return new AbstractEnumerable<Object>() { public Enumerator<Object> enumerator() { - return new CsvEnumerator<>( - source, - cancelFlag, - getFieldTypes(root.getTypeFactory()), - fields); + return new CsvEnumerator<>(source, cancelFlag, + getFieldTypes(root.getTypeFactory()), ImmutableIntList.of(fields)); } }; } diff --git a/file/build.gradle.kts b/file/src/main/java/org/apache/calcite/adapter/file/FileRules.java similarity index 66% copy from file/build.gradle.kts copy to file/src/main/java/org/apache/calcite/adapter/file/FileRules.java index 1610a03..9c7e228 100644 --- a/file/build.gradle.kts +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileRules.java @@ -14,15 +14,14 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) +package org.apache.calcite.adapter.file; - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") +/** Planner rules relating to the File adapter. */ +public abstract class FileRules { + private FileRules() {} - testImplementation(project(":core", "testClasses")) + /** Rule that matches a {@link org.apache.calcite.rel.core.Project} on + * a {@link CsvTableScan} and pushes down projects if possible. */ + public static final CsvProjectTableScanRule PROJECT_SCAN = + CsvProjectTableScanRule.Config.DEFAULT.toRule(); } diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java b/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java index 74a4003..4fb5622 100644 --- a/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java @@ -16,8 +16,6 @@ */ package org.apache.calcite.adapter.file; -import org.apache.calcite.adapter.csv.CsvFilterableTable; -import org.apache.calcite.adapter.csv.JsonScannableTable; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.schema.Table; import org.apache.calcite.schema.impl.AbstractSchema; @@ -47,11 +45,12 @@ class FileSchema extends AbstractSchema { * @param parentSchema Parent schema * @param name Schema name * @param baseDirectory Base directory to look for relative files, or null - * @param tables List containing HTML table identifiers + * @param tables List containing HTML table identifiers, or null */ FileSchema(SchemaPlus parentSchema, String name, File baseDirectory, List<Map<String, Object>> tables) { - this.tables = ImmutableList.copyOf(tables); + this.tables = tables == null ? ImmutableList.of() + : ImmutableList.copyOf(tables); this.baseDirectory = baseDirectory; } @@ -91,29 +90,31 @@ class FileSchema extends AbstractSchema { // Look for files in the directory ending in ".csv", ".csv.gz", ".json", // ".json.gz". - final Source baseSource = Sources.of(baseDirectory); - File[] files = baseDirectory.listFiles((dir, name) -> { - final String nameSansGz = trim(name, ".gz"); - return nameSansGz.endsWith(".csv") - || nameSansGz.endsWith(".json"); - }); - if (files == null) { - System.out.println("directory " + baseDirectory + " not found"); - files = new File[0]; - } - // Build a map from table name to table; each file becomes a table. - for (File file : files) { - Source source = Sources.of(file); - Source sourceSansGz = source.trim(".gz"); - final Source sourceSansJson = sourceSansGz.trimOrNull(".json"); - if (sourceSansJson != null) { - addTable(builder, source, sourceSansJson.relative(baseSource).path(), - null); + if (baseDirectory != null) { + final Source baseSource = Sources.of(baseDirectory); + File[] files = baseDirectory.listFiles((dir, name) -> { + final String nameSansGz = trim(name, ".gz"); + return nameSansGz.endsWith(".csv") + || nameSansGz.endsWith(".json"); + }); + if (files == null) { + System.out.println("directory " + baseDirectory + " not found"); + files = new File[0]; } - final Source sourceSansCsv = sourceSansGz.trimOrNull(".csv"); - if (sourceSansCsv != null) { - addTable(builder, source, sourceSansCsv.relative(baseSource).path(), - null); + // Build a map from table name to table; each file becomes a table. + for (File file : files) { + Source source = Sources.of(file); + Source sourceSansGz = source.trim(".gz"); + final Source sourceSansJson = sourceSansGz.trimOrNull(".json"); + if (sourceSansJson != null) { + addTable(builder, source, sourceSansJson.relative(baseSource).path(), + null); + } + final Source sourceSansCsv = sourceSansGz.trimOrNull(".csv"); + if (sourceSansCsv != null) { + addTable(builder, source, sourceSansCsv.relative(baseSource).path(), + null); + } } } @@ -145,7 +146,7 @@ class FileSchema extends AbstractSchema { } final Source sourceSansCsv = sourceSansGz.trimOrNull(".csv"); if (sourceSansCsv != null) { - final Table table = new CsvFilterableTable(source, null); + final Table table = new CsvTranslatableTable(source, null); builder.put(Util.first(tableName, sourceSansCsv.path()), table); return true; } diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java b/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java index a20d1e6..f0cc654 100644 --- a/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java @@ -33,8 +33,14 @@ import java.util.Map; */ @SuppressWarnings("UnusedDeclaration") public class FileSchemaFactory implements SchemaFactory { - // public constructor, per factory contract - public FileSchemaFactory() { + /** Public singleton, per factory contract. */ + public static final FileSchemaFactory INSTANCE = new FileSchemaFactory(); + + /** Name of the column that is implicitly created in a CSV stream table + * to hold the data arrival time. */ + static final String ROWTIME_COLUMN_NAME = "ROWTIME"; + + private FileSchemaFactory() { } public Schema create(SchemaPlus parentSchema, String name, @@ -43,11 +49,15 @@ public class FileSchemaFactory implements SchemaFactory { (List) operand.get("tables"); final File baseDirectory = (File) operand.get(ModelHandler.ExtraOperand.BASE_DIRECTORY.camelName); - File directoryFile = baseDirectory; final String directory = (String) operand.get("directory"); - if (baseDirectory != null && directory != null) { + File directoryFile = null; + if (directory != null) { directoryFile = new File(directory); - if (!directoryFile.isAbsolute()) { + } + if (baseDirectory != null) { + if (directoryFile == null) { + directoryFile = baseDirectory; + } else if (!directoryFile.isAbsolute()) { directoryFile = new File(baseDirectory, directory); } } diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java b/file/src/main/java/org/apache/calcite/adapter/file/JsonEnumerator.java similarity index 99% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java rename to file/src/main/java/org/apache/calcite/adapter/file/JsonEnumerator.java index 0e8f172..7fd0640 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonEnumerator.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/JsonEnumerator.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.linq4j.Enumerator; import org.apache.calcite.linq4j.Linq4j; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java b/file/src/main/java/org/apache/calcite/adapter/file/JsonScannableTable.java similarity index 97% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java rename to file/src/main/java/org/apache/calcite/adapter/file/JsonScannableTable.java index 0632fdf..2210b7e 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonScannableTable.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/JsonScannableTable.java @@ -14,7 +14,7 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; import org.apache.calcite.DataContext; import org.apache.calcite.linq4j.AbstractEnumerable; diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java b/file/src/main/java/org/apache/calcite/adapter/file/JsonTable.java similarity index 94% rename from example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java rename to file/src/main/java/org/apache/calcite/adapter/file/JsonTable.java index 049881e..5fade31 100644 --- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/JsonTable.java +++ b/file/src/main/java/org/apache/calcite/adapter/file/JsonTable.java @@ -14,9 +14,9 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -package org.apache.calcite.adapter.csv; +package org.apache.calcite.adapter.file; -import org.apache.calcite.adapter.csv.JsonEnumerator.JsonDataConverter; +import org.apache.calcite.adapter.file.JsonEnumerator.JsonDataConverter; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.schema.Statistic; diff --git a/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTest.java b/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTest.java new file mode 100644 index 0000000..5682b77 --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTest.java @@ -0,0 +1,890 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.adapter.file; + +import org.apache.calcite.jdbc.CalciteConnection; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.sql2rel.SqlToRelConverter; +import org.apache.calcite.util.TestUtil; + +import com.google.common.collect.ImmutableMap; + +import org.junit.jupiter.api.Disabled; +import org.junit.jupiter.api.Test; +import org.junit.jupiter.api.extension.ExtendWith; + +import java.sql.Connection; +import java.sql.Date; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Time; +import java.sql.Timestamp; +import java.sql.Types; +import java.util.Properties; +import java.util.function.Consumer; + +import static org.apache.calcite.adapter.file.FileAdapterTests.sql; + +import static org.hamcrest.CoreMatchers.equalTo; +import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.CoreMatchers.isA; +import static org.hamcrest.MatcherAssert.assertThat; +import static org.junit.jupiter.api.Assertions.assertEquals; + +/** + * System test of the Calcite file adapter, which can read and parse + * HTML tables over HTTP, and also read CSV and JSON files from the filesystem. + */ +@ExtendWith(RequiresNetworkExtension.class) +class FileAdapterTest { + /** Reads from a local file and checks the result. */ + @Test void testFileSelect() { + final String sql = "select H1 from T1 where H0 = 'R1C0'"; + sql("testModel", sql).returns("H1=R1C1").ok(); + } + + /** Reads from a local file without table headers <TH> and checks the + * result. */ + @Test @RequiresNetwork void testNoThSelect() { + final String sql = "select \"col1\" from T1_NO_TH where \"col0\" like 'R0%'"; + sql("testModel", sql).returns("col1=R0C1").ok(); + } + + /** Reads from a local file - finds larger table even without <TH> + * elements. */ + @Test void testFindBiggerNoTh() { + final String sql = "select \"col4\" from TABLEX2 where \"col0\" like 'R1%'"; + sql("testModel", sql).returns("col4=R1C4").ok(); + } + + /** Reads from a URL and checks the result. */ + @Disabled("[CALCITE-1789] Wikipedia format change breaks file adapter test") + @Test @RequiresNetwork void testUrlSelect() { + final String sql = "select \"State\", \"Statehood\" from \"States_as_of\"\n" + + "where \"State\" = 'California'"; + sql("wiki", sql).returns("State=California; Statehood=1850-09-09").ok(); + } + + /** Reads the EMPS table. */ + @Test void testSalesEmps() { + final String sql = "select * from sales.emps"; + sql("sales", sql) + .returns("EMPNO=100; NAME=Fred; DEPTNO=30", + "EMPNO=110; NAME=Eric; DEPTNO=20", + "EMPNO=110; NAME=John; DEPTNO=40", + "EMPNO=120; NAME=Wilma; DEPTNO=20", + "EMPNO=130; NAME=Alice; DEPTNO=40") + .ok(); + } + + /** Reads the DEPTS table. */ + @Test void testSalesDepts() { + final String sql = "select * from sales.depts"; + sql("sales", sql) + .returns("DEPTNO=10; NAME=Sales", + "DEPTNO=20; NAME=Marketing", + "DEPTNO=30; NAME=Accounts") + .ok(); + } + + /** Reads the DEPTS table from the CSV schema. */ + @Test void testCsvSalesDepts() { + final String sql = "select * from sales.depts"; + sql("sales-csv", sql) + .returns("DEPTNO=10; NAME=Sales", + "DEPTNO=20; NAME=Marketing", + "DEPTNO=30; NAME=Accounts") + .ok(); + } + + /** Reads the EMPS table from the CSV schema. */ + @Test void testCsvSalesEmps() { + final String sql = "select * from sales.emps"; + final String[] lines = { + "EMPNO=100; NAME=Fred; DEPTNO=10; GENDER=; CITY=; EMPID=30; AGE=25; SLACKER=true; MANAGER=false; JOINEDAT=1996-08-03", + "EMPNO=110; NAME=Eric; DEPTNO=20; GENDER=M; CITY=San Francisco; EMPID=3; AGE=80; SLACKER=null; MANAGER=false; JOINEDAT=2001-01-01", + "EMPNO=110; NAME=John; DEPTNO=40; GENDER=M; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2002-05-03", + "EMPNO=120; NAME=Wilma; DEPTNO=20; GENDER=F; CITY=; EMPID=1; AGE=5; SLACKER=null; MANAGER=true; JOINEDAT=2005-09-07", + "EMPNO=130; NAME=Alice; DEPTNO=40; GENDER=F; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2007-01-01", + }; + sql("sales-csv", sql).returns(lines).ok(); + } + + /** Reads the HEADER_ONLY table from the CSV schema. The CSV file has one + * line - the column headers - but no rows of data. */ + @Test void testCsvSalesHeaderOnly() { + final String sql = "select * from sales.header_only"; + sql("sales-csv", sql).returns().ok(); + } + + /** Reads the EMPTY table from the CSV schema. The CSV file has no lines, + * therefore the table has a system-generated column called + * "EmptyFileHasNoColumns". */ + @Test void testCsvSalesEmpty() { + final String sql = "select * from sales.\"EMPTY\""; + sql("sales-csv", sql) + .checking(FileAdapterTest::checkEmpty) + .ok(); + } + + private static void checkEmpty(ResultSet resultSet) { + try { + final ResultSetMetaData metaData = resultSet.getMetaData(); + assertThat(metaData.getColumnCount(), is(1)); + assertThat(metaData.getColumnName(1), is("EmptyFileHasNoColumns")); + assertThat(metaData.getColumnType(1), is(Types.BOOLEAN)); + String actual = FileAdapterTests.toString(resultSet); + assertThat(actual, is("")); + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1754";>[CALCITE-1754] + * In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values + * to long</a>. */ + @Test void testCsvGroupByTimestampAdd() { + final String sql = "select count(*) as c,\n" + + " {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT) } as t\n" + + "from EMPS group by {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT ) } "; + sql("sales-csv", sql) + .returnsUnordered("C=1; T=1996-08-04", + "C=1; T=2002-05-04", + "C=1; T=2005-09-08", + "C=1; T=2007-01-02", + "C=1; T=2001-01-02") + .ok(); + final String sql2 = "select count(*) as c,\n" + + " {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } as t\n" + + "from EMPS group by {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT ) } "; + sql("sales-csv", sql2) + .returnsUnordered("C=1; T=2002-06-03", + "C=1; T=2005-10-07", + "C=1; T=2007-02-01", + "C=1; T=2001-02-01", + "C=1; T=1996-09-03").ok(); + final String sql3 = "select\n" + + " distinct {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } as t\n" + + "from EMPS"; + sql("sales-csv", sql3) + .returnsUnordered("T=2002-06-03", + "T=2005-10-07", + "T=2007-02-01", + "T=2001-02-01", + "T=1996-09-03").ok(); + } + + /** Reads the DEPTS table from the JSON schema. */ + @Test void testJsonSalesDepts() { + final String sql = "select * from sales.depts"; + sql("sales-json", sql) + .returns("DEPTNO=10; NAME=Sales", + "DEPTNO=20; NAME=Marketing", + "DEPTNO=30; NAME=Accounts") + .ok(); + } + + /** Reads the EMPS table from the JSON schema. */ + @Test void testJsonSalesEmps() { + final String sql = "select * from sales.emps"; + final String[] lines = { + "EMPNO=100; NAME=Fred; DEPTNO=10; GENDER=; CITY=; EMPID=30; AGE=25; SLACKER=true; MANAGER=false; JOINEDAT=1996-08-03", + "EMPNO=110; NAME=Eric; DEPTNO=20; GENDER=M; CITY=San Francisco; EMPID=3; AGE=80; SLACKER=null; MANAGER=false; JOINEDAT=2001-01-01", + "EMPNO=110; NAME=John; DEPTNO=40; GENDER=M; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2002-05-03", + "EMPNO=120; NAME=Wilma; DEPTNO=20; GENDER=F; CITY=; EMPID=1; AGE=5; SLACKER=null; MANAGER=true; JOINEDAT=2005-09-07", + "EMPNO=130; NAME=Alice; DEPTNO=40; GENDER=F; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2007-01-01", + }; + sql("sales-json", sql).returns(lines).ok(); + } + + /** Reads the EMPTY table from the JSON schema. The JSON file has no lines, + * therefore the table has a system-generated column called + * "EmptyFileHasNoColumns". */ + @Test void testJsonSalesEmpty() { + final String sql = "select * from sales.\"EMPTY\""; + sql("sales-json", sql) + .checking(FileAdapterTest::checkEmpty) + .ok(); + } + + /** Test returns the result of two json file joins. */ + @Test void testJsonJoinOnString() { + final String sql = "select emps.EMPNO, emps.NAME, depts.deptno from emps\n" + + "join depts on emps.deptno = depts.deptno"; + final String[] lines = { + "EMPNO=100; NAME=Fred; DEPTNO=10", + "EMPNO=110; NAME=Eric; DEPTNO=20", + "EMPNO=120; NAME=Wilma; DEPTNO=20", + }; + sql("sales-json", sql).returns(lines).ok(); + } + + /** The folder contains both JSON files and CSV files joins. */ + @Test void testJsonWithCsvJoin() { + final String sql = "select emps.empno,\n" + + " NAME,\n" + + " \"DATE\".JOINEDAT\n" + + " from \"DATE\"\n" + + "join emps on emps.empno = \"DATE\".EMPNO\n" + + "order by empno, name, joinedat limit 3"; + final String[] lines = { + "EMPNO=100; NAME=Fred; JOINEDAT=1996-08-03", + "EMPNO=110; NAME=Eric; JOINEDAT=2001-01-01", + "EMPNO=110; NAME=Eric; JOINEDAT=2002-05-03", + }; + sql("sales-json", sql) + .returns(lines) + .ok(); + } + + /** Tests an inline schema with a non-existent directory. */ + @Test void testBadDirectory() throws SQLException { + Properties info = new Properties(); + info.put("model", + "inline:" + + "{\n" + + " version: '1.0',\n" + + " schemas: [\n" + + " {\n" + + " type: 'custom',\n" + + " name: 'bad',\n" + + " factory: 'org.apache.calcite.adapter.file.FileSchemaFactory',\n" + + " operand: {\n" + + " directory: '/does/not/exist'\n" + + " }\n" + + " }\n" + + " ]\n" + + "}"); + + Connection connection = + DriverManager.getConnection("jdbc:calcite:", info); + // must print "directory ... not found" to stdout, but not fail + ResultSet tables = + connection.getMetaData().getTables(null, null, null, null); + tables.next(); + tables.close(); + connection.close(); + } + + /** + * Reads from a table. + */ + @Test void testSelect() { + sql("model", "select * from EMPS").ok(); + } + + @Test void testSelectSingleProjectGz() { + sql("smart", "select name from EMPS").ok(); + } + + @Test void testSelectSingleProject() { + sql("smart", "select name from DEPTS").ok(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-898";>[CALCITE-898] + * Type inference multiplying Java long by SQL INTEGER</a>. */ + @Test void testSelectLongMultiplyInteger() { + final String sql = "select empno * 3 as e3\n" + + "from long_emps where empno = 100"; + + sql("bug", sql).checking(resultSet -> { + try { + assertThat(resultSet.next(), is(true)); + Long o = (Long) resultSet.getObject(1); + assertThat(o, is(300L)); + assertThat(resultSet.next(), is(false)); + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + }).ok(); + } + + @Test void testCustomTable() { + sql("model-with-custom-table", "select * from CUSTOM_TABLE.EMPS").ok(); + } + + @Test void testPushDownProject() { + final String sql = "explain plan for select * from EMPS"; + final String expected = "PLAN=CsvTableScan(table=[[SALES, EMPS]], " + + "fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test void testPushDownProject2() { + sql("smart", "explain plan for select name, empno from EMPS") + .returns("PLAN=CsvTableScan(table=[[SALES, EMPS]], fields=[[1, 0]])\n") + .ok(); + // make sure that it works... + sql("smart", "select name, empno from EMPS") + .returns("NAME=Fred; EMPNO=100", + "NAME=Eric; EMPNO=110", + "NAME=John; EMPNO=110", + "NAME=Wilma; EMPNO=120", + "NAME=Alice; EMPNO=130") + .ok(); + } + + @Test void testPushDownProjectAggregate() { + final String sql = "explain plan for\n" + + "select gender, count(*) from EMPS group by gender"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{0}], EXPR$1=[COUNT()])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test void testPushDownProjectAggregateWithFilter() { + final String sql = "explain plan for\n" + + "select max(empno) from EMPS where gender='F'"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])\n" + + " EnumerableCalc(expr#0..1=[{inputs}], expr#2=['F':VARCHAR], " + + "expr#3=[=($t1, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[0, 3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test void testPushDownProjectAggregateNested() { + final String sql = "explain plan for\n" + + "select gender, max(qty)\n" + + "from (\n" + + " select name, gender, count(*) qty\n" + + " from EMPS\n" + + " group by name, gender) t\n" + + "group by gender"; + final String expected = "PLAN=" + + "EnumerableAggregate(group=[{1}], EXPR$1=[MAX($2)])\n" + + " EnumerableAggregate(group=[{0, 1}], QTY=[COUNT()])\n" + + " CsvTableScan(table=[[SALES, EMPS]], fields=[[1, 3]])\n"; + sql("smart", sql).returns(expected).ok(); + } + + @Test void testFilterableSelect() { + sql("filterable-model", "select name from EMPS").ok(); + } + + @Test void testFilterableSelectStar() { + sql("filterable-model", "select * from EMPS").ok(); + } + + /** Filter that can be fully handled by CsvFilterableTable. */ + @Test void testFilterableWhere() { + final String sql = + "select empno, gender, name from EMPS where name = 'John'"; + sql("filterable-model", sql) + .returns("EMPNO=110; GENDER=M; NAME=John").ok(); + } + + /** Filter that can be partly handled by CsvFilterableTable. */ + @Test void testFilterableWhere2() { + final String sql = "select empno, gender, name from EMPS\n" + + " where gender = 'F' and empno > 125"; + sql("filterable-model", sql) + .returns("EMPNO=130; GENDER=F; NAME=Alice").ok(); + } + + /** Filter that can be slightly handled by CsvFilterableTable. */ + @Test void testFilterableWhere3() { + final String sql = "select empno, gender, name from EMPS\n" + + " where gender <> 'M' and empno > 125"; + sql("filterable-model", sql) + .returns("EMPNO=130; GENDER=F; NAME=Alice") + .ok(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2272";>[CALCITE-2272] + * Incorrect result for {@code name like '%E%' and city not like '%W%'}</a>. + */ + @Test void testFilterableWhereWithNot1() { + sql("filterable-model", + "select name, empno from EMPS " + + "where name like '%E%' and city not like '%W%' ") + .returns("NAME=Eric; EMPNO=110") + .ok(); + } + + /** Similar to {@link #testFilterableWhereWithNot1()}; + * But use the same column. */ + @Test void testFilterableWhereWithNot2() { + sql("filterable-model", + "select name, empno from EMPS " + + "where name like '%i%' and name not like '%W%' ") + .returns("NAME=Eric; EMPNO=110", + "NAME=Alice; EMPNO=130") + .ok(); + } + + @Test void testJson() { + final String sql = "select * from archers\n"; + final String[] lines = { + "id=19990101; dow=Friday; longDate=New Years Day; title=Tractor trouble.; " + + "characters=[Alice, Bob, Xavier]; script=Julian Hyde; summary=; " + + "lines=[Bob's tractor got stuck in a field., " + + "Alice and Xavier hatch a plan to surprise Charlie.]", + "id=19990103; dow=Sunday; longDate=Sunday 3rd January; " + + "title=Charlie's surprise.; characters=[Alice, Zebedee, Charlie, Xavier]; " + + "script=William Shakespeare; summary=; " + + "lines=[Charlie is very surprised by Alice and Xavier's surprise plan.]", + }; + sql("bug", sql) + .returns(lines) + .ok(); + } + + @Test void testJoinOnString() { + final String sql = "select * from emps\n" + + "join depts on emps.name = depts.name"; + sql("smart", sql).ok(); + } + + @Test void testWackyColumns() { + final String sql = "select * from wacky_column_names where false"; + sql("bug", sql).returns().ok(); + + final String sql2 = "select \"joined at\", \"naME\"\n" + + "from wacky_column_names\n" + + "where \"2gender\" = 'F'"; + sql("bug", sql2) + .returns("joined at=2005-09-07; naME=Wilma", + "joined at=2007-01-01; naME=Alice") + .ok(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1754";>[CALCITE-1754] + * In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values + * to long</a>. */ + @Test void testGroupByTimestampAdd() { + final String sql = "select count(*) as c,\n" + + " {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT) } as t\n" + + "from EMPS group by {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT ) } "; + sql("model", sql) + .returnsUnordered("C=1; T=1996-08-04", + "C=1; T=2002-05-04", + "C=1; T=2005-09-08", + "C=1; T=2007-01-02", + "C=1; T=2001-01-02") + .ok(); + + final String sql2 = "select count(*) as c,\n" + + " {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } as t\n" + + "from EMPS group by {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT ) } "; + sql("model", sql2) + .returnsUnordered("C=1; T=2002-06-03", + "C=1; T=2005-10-07", + "C=1; T=2007-02-01", + "C=1; T=2001-02-01", + "C=1; T=1996-09-03") + .ok(); + } + + @Test void testUnionGroupByWithoutGroupKey() { + final String sql = "select count(*) as c1 from EMPS group by NAME\n" + + "union\n" + + "select count(*) as c1 from EMPS group by NAME"; + sql("model", sql).ok(); + } + + @Test void testBoolean() { + sql("smart", "select empno, slacker from emps where slacker") + .returns("EMPNO=100; SLACKER=true").ok(); + } + + @Test void testReadme() { + final String sql = "SELECT d.name, COUNT(*) cnt" + + " FROM emps AS e" + + " JOIN depts AS d ON e.deptno = d.deptno" + + " GROUP BY d.name"; + sql("smart", sql) + .returns("NAME=Sales; CNT=1", "NAME=Marketing; CNT=2").ok(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-824";>[CALCITE-824] + * Type inference when converting IN clause to semijoin</a>. */ + @Test void testInToSemiJoinWithCast() { + // Note that the IN list needs at least 20 values to trigger the rewrite + // to a semijoin. Try it both ways. + final String sql = "SELECT e.name\n" + + "FROM emps AS e\n" + + "WHERE cast(e.empno as bigint) in "; + final int threshold = SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD; + sql("smart", sql + range(130, threshold - 5)) + .returns("NAME=Alice").ok(); + sql("smart", sql + range(130, threshold)) + .returns("NAME=Alice").ok(); + sql("smart", sql + range(130, threshold + 1000)) + .returns("NAME=Alice").ok(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1051";>[CALCITE-1051] + * Underflow exception due to scaling IN clause literals</a>. */ + @Test void testInToSemiJoinWithoutCast() { + final String sql = "SELECT e.name\n" + + "FROM emps AS e\n" + + "WHERE e.empno in " + + range(130, SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD); + sql("smart", sql).returns("NAME=Alice").ok(); + } + + private String range(int first, int count) { + final StringBuilder sb = new StringBuilder(); + for (int i = 0; i < count; i++) { + sb.append(i == 0 ? "(" : ", ").append(first + i); + } + return sb.append(')').toString(); + } + + @Test void testDateType() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + ResultSet res = connection.getMetaData().getColumns(null, null, + "DATE", "JOINEDAT"); + res.next(); + assertEquals(res.getInt("DATA_TYPE"), Types.DATE); + + res = connection.getMetaData().getColumns(null, null, + "DATE", "JOINTIME"); + res.next(); + assertEquals(res.getInt("DATA_TYPE"), Types.TIME); + + res = connection.getMetaData().getColumns(null, null, + "DATE", "JOINTIMES"); + res.next(); + assertEquals(res.getInt("DATA_TYPE"), Types.TIMESTAMP); + + Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery( + "select \"JOINEDAT\", \"JOINTIME\", \"JOINTIMES\" from \"DATE\" where EMPNO = 100"); + resultSet.next(); + + // date + assertEquals(Date.class, resultSet.getDate(1).getClass()); + assertEquals(Date.valueOf("1996-08-03"), resultSet.getDate(1)); + + // time + assertEquals(Time.class, resultSet.getTime(2).getClass()); + assertEquals(Time.valueOf("00:01:02"), resultSet.getTime(2)); + + // timestamp + assertEquals(Timestamp.class, resultSet.getTimestamp(3).getClass()); + assertEquals(Timestamp.valueOf("1996-08-03 00:01:02"), + resultSet.getTimestamp(3)); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1072";>[CALCITE-1072] + * CSV adapter incorrectly parses TIMESTAMP values after noon</a>. */ + @Test void testDateType2() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + Statement statement = connection.createStatement(); + final String sql = "select * from \"DATE\"\n" + + "where EMPNO >= 140 and EMPNO < 200"; + ResultSet resultSet = statement.executeQuery(sql); + int n = 0; + while (resultSet.next()) { + ++n; + final int empId = resultSet.getInt(1); + final String date = resultSet.getString(2); + final String time = resultSet.getString(3); + final String timestamp = resultSet.getString(4); + assertThat(date, is("2015-12-31")); + switch (empId) { + case 140: + assertThat(time, is("07:15:56")); + assertThat(timestamp, is("2015-12-31 07:15:56")); + break; + case 150: + assertThat(time, is("13:31:21")); + assertThat(timestamp, is("2015-12-31 13:31:21")); + break; + default: + throw new AssertionError(); + } + } + assertThat(n, is(2)); + resultSet.close(); + statement.close(); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1673";>[CALCITE-1673] + * Query with ORDER BY or GROUP BY on TIMESTAMP column throws + * CompileException</a>. */ + @Test void testTimestampGroupBy() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + // Use LIMIT to ensure that results are deterministic without ORDER BY + final String sql = "select \"EMPNO\", \"JOINTIMES\"\n" + + "from (select * from \"DATE\" limit 1)\n" + + "group by \"EMPNO\",\"JOINTIMES\""; + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info); + Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql)) { + assertThat(resultSet.next(), is(true)); + final Timestamp timestamp = resultSet.getTimestamp(2); + assertThat(timestamp, isA(Timestamp.class)); + // Note: This logic is time zone specific, but the same time zone is + // used in the CSV adapter and this test, so they should cancel out. + assertThat(timestamp, is(Timestamp.valueOf("1996-08-03 00:01:02.0"))); + } + } + + /** As {@link #testTimestampGroupBy()} but with ORDER BY. */ + @Test void testTimestampOrderBy() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + final String sql = "select \"EMPNO\",\"JOINTIMES\" from \"DATE\"\n" + + "order by \"JOINTIMES\""; + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info); + Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql)) { + assertThat(resultSet.next(), is(true)); + final Timestamp timestamp = resultSet.getTimestamp(2); + assertThat(timestamp, is(Timestamp.valueOf("1996-08-03 00:01:02"))); + } + } + + /** As {@link #testTimestampGroupBy()} but with ORDER BY as well as GROUP + * BY. */ + @Test void testTimestampGroupByAndOrderBy() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + final String sql = "select \"EMPNO\", \"JOINTIMES\" from \"DATE\"\n" + + "group by \"EMPNO\",\"JOINTIMES\" order by \"JOINTIMES\""; + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info); + Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(sql)) { + assertThat(resultSet.next(), is(true)); + final Timestamp timestamp = resultSet.getTimestamp(2); + assertThat(timestamp, is(Timestamp.valueOf("1996-08-03 00:01:02"))); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1031";>[CALCITE-1031] + * In prepared statement, CsvScannableTable.scan is called twice</a>. To see + * the bug, place a breakpoint in CsvScannableTable.scan, and note that it is + * called twice. It should only be called once. */ + @Test void testPrepared() throws SQLException { + final Properties properties = new Properties(); + properties.setProperty("caseSensitive", "true"); + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", properties)) { + final CalciteConnection calciteConnection = connection.unwrap( + CalciteConnection.class); + + final Schema schema = + FileSchemaFactory.INSTANCE + .create(calciteConnection.getRootSchema(), null, + ImmutableMap.of("directory", + FileAdapterTests.resourcePath("sales-csv"), "flavor", "scannable")); + calciteConnection.getRootSchema().add("TEST", schema); + final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" = ?"; + final PreparedStatement statement2 = + calciteConnection.prepareStatement(sql); + + statement2.setString(1, "Sales"); + final ResultSet resultSet1 = statement2.executeQuery(); + Consumer<ResultSet> expect = FileAdapterTests.expect("DEPTNO=10; NAME=Sales"); + expect.accept(resultSet1); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1054";>[CALCITE-1054] + * NPE caused by wrong code generation for Timestamp fields</a>. */ + @Test void testFilterOnNullableTimestamp() throws Exception { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + final Statement statement = connection.createStatement(); + + // date + final String sql1 = "select JOINEDAT from \"DATE\"\n" + + "where JOINEDAT < {d '2000-01-01'}\n" + + "or JOINEDAT >= {d '2017-01-01'}"; + final ResultSet joinedAt = statement.executeQuery(sql1); + assertThat(joinedAt.next(), is(true)); + assertThat(joinedAt.getDate(1), is(Date.valueOf("1996-08-03"))); + + // time + final String sql2 = "select JOINTIME from \"DATE\"\n" + + "where JOINTIME >= {t '07:00:00'}\n" + + "and JOINTIME < {t '08:00:00'}"; + final ResultSet joinTime = statement.executeQuery(sql2); + assertThat(joinTime.next(), is(true)); + assertThat(joinTime.getTime(1), is(Time.valueOf("07:15:56"))); + + // timestamp + final String sql3 = "select JOINTIMES,\n" + + " {fn timestampadd(SQL_TSI_DAY, 1, JOINTIMES)}\n" + + "from \"DATE\"\n" + + "where (JOINTIMES >= {ts '2003-01-01 00:00:00'}\n" + + "and JOINTIMES < {ts '2006-01-01 00:00:00'})\n" + + "or (JOINTIMES >= {ts '2003-01-01 00:00:00'}\n" + + "and JOINTIMES < {ts '2007-01-01 00:00:00'})"; + final ResultSet joinTimes = statement.executeQuery(sql3); + assertThat(joinTimes.next(), is(true)); + assertThat(joinTimes.getTimestamp(1), + is(Timestamp.valueOf("2005-09-07 00:00:00"))); + assertThat(joinTimes.getTimestamp(2), + is(Timestamp.valueOf("2005-09-08 00:00:00"))); + + final String sql4 = "select JOINTIMES, extract(year from JOINTIMES)\n" + + "from \"DATE\""; + final ResultSet joinTimes2 = statement.executeQuery(sql4); + assertThat(joinTimes2.next(), is(true)); + assertThat(joinTimes2.getTimestamp(1), + is(Timestamp.valueOf("1996-08-03 00:01:02"))); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1118";>[CALCITE-1118] + * NullPointerException in EXTRACT with WHERE ... IN clause if field has null + * value</a>. */ + @Test void testFilterOnNullableTimestamp2() throws Exception { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + final Statement statement = connection.createStatement(); + final String sql1 = "select extract(year from JOINTIMES)\n" + + "from \"DATE\"\n" + + "where extract(year from JOINTIMES) in (2006, 2007)"; + final ResultSet joinTimes = statement.executeQuery(sql1); + assertThat(joinTimes.next(), is(true)); + assertThat(joinTimes.getInt(1), is(2007)); + + final String sql2 = "select extract(year from JOINTIMES),\n" + + " count(0) from \"DATE\"\n" + + "where extract(year from JOINTIMES) between 2007 and 2016\n" + + "group by extract(year from JOINTIMES)"; + final ResultSet joinTimes2 = statement.executeQuery(sql2); + assertThat(joinTimes2.next(), is(true)); + assertThat(joinTimes2.getInt(1), is(2007)); + assertThat(joinTimes2.getLong(2), is(1L)); + assertThat(joinTimes2.next(), is(true)); + assertThat(joinTimes2.getInt(1), is(2015)); + assertThat(joinTimes2.getLong(2), is(2L)); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1427";>[CALCITE-1427] + * Code generation incorrect (does not compile) for DATE, TIME and TIMESTAMP + * fields</a>. */ + @Test void testNonNullFilterOnDateType() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + final Statement statement = connection.createStatement(); + + // date + final String sql1 = "select JOINEDAT from \"DATE\"\n" + + "where JOINEDAT is not null"; + final ResultSet joinedAt = statement.executeQuery(sql1); + assertThat(joinedAt.next(), is(true)); + assertThat(joinedAt.getDate(1).getClass(), equalTo(Date.class)); + assertThat(joinedAt.getDate(1), is(Date.valueOf("1996-08-03"))); + + // time + final String sql2 = "select JOINTIME from \"DATE\"\n" + + "where JOINTIME is not null"; + final ResultSet joinTime = statement.executeQuery(sql2); + assertThat(joinTime.next(), is(true)); + assertThat(joinTime.getTime(1).getClass(), equalTo(Time.class)); + assertThat(joinTime.getTime(1), is(Time.valueOf("00:01:02"))); + + // timestamp + final String sql3 = "select JOINTIMES from \"DATE\"\n" + + "where JOINTIMES is not null"; + final ResultSet joinTimes = statement.executeQuery(sql3); + assertThat(joinTimes.next(), is(true)); + assertThat(joinTimes.getTimestamp(1).getClass(), + equalTo(Timestamp.class)); + assertThat(joinTimes.getTimestamp(1), + is(Timestamp.valueOf("1996-08-03 00:01:02"))); + } + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-1427";>[CALCITE-1427] + * Code generation incorrect (does not compile) for DATE, TIME and TIMESTAMP + * fields</a>. */ + @Test void testGreaterThanFilterOnDateType() throws SQLException { + Properties info = new Properties(); + info.put("model", FileAdapterTests.jsonPath("bug")); + + try (Connection connection = + DriverManager.getConnection("jdbc:calcite:", info)) { + final Statement statement = connection.createStatement(); + + // date + final String sql1 = "select JOINEDAT from \"DATE\"\n" + + "where JOINEDAT > {d '1990-01-01'}"; + final ResultSet joinedAt = statement.executeQuery(sql1); + assertThat(joinedAt.next(), is(true)); + assertThat(joinedAt.getDate(1).getClass(), equalTo(Date.class)); + assertThat(joinedAt.getDate(1), is(Date.valueOf("1996-08-03"))); + + // time + final String sql2 = "select JOINTIME from \"DATE\"\n" + + "where JOINTIME > {t '00:00:00'}"; + final ResultSet joinTime = statement.executeQuery(sql2); + assertThat(joinTime.next(), is(true)); + assertThat(joinTime.getTime(1).getClass(), equalTo(Time.class)); + assertThat(joinTime.getTime(1), is(Time.valueOf("00:01:02"))); + + // timestamp + final String sql3 = "select JOINTIMES from \"DATE\"\n" + + "where JOINTIMES > {ts '1990-01-01 00:00:00'}"; + final ResultSet joinTimes = statement.executeQuery(sql3); + assertThat(joinTimes.next(), is(true)); + assertThat(joinTimes.getTimestamp(1).getClass(), + equalTo(Timestamp.class)); + assertThat(joinTimes.getTimestamp(1), + is(Timestamp.valueOf("1996-08-03 00:01:02"))); + } + } +} diff --git a/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTests.java b/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTests.java new file mode 100644 index 0000000..1162516 --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/FileAdapterTests.java @@ -0,0 +1,222 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.adapter.file; + +import org.apache.calcite.util.Sources; +import org.apache.calcite.util.TestUtil; +import org.apache.calcite.util.Util; + +import com.google.common.collect.Ordering; + +import java.io.PrintStream; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.List; +import java.util.Properties; +import java.util.function.Consumer; + +import static org.junit.jupiter.api.Assertions.assertEquals; + +/** Helpers for test suite of the File adapter. */ +abstract class FileAdapterTests { + private FileAdapterTests() { + } + + static Fluent sql(String model, String sql) { + return new Fluent(model, sql, FileAdapterTests::output); + } + + /** Returns a function that checks the contents of a result set against an + * expected string. */ + static Consumer<ResultSet> expect(final String... expected) { + return resultSet -> { + try { + final List<String> lines = new ArrayList<>(); + collect(lines, resultSet); + assertEquals(Arrays.asList(expected), lines); + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + }; + } + + /** Returns a function that checks the contents of a result set against an + * expected string. */ + private static Consumer<ResultSet> expectUnordered(String... expected) { + final List<String> expectedLines = + Ordering.natural().immutableSortedCopy(Arrays.asList(expected)); + return resultSet -> { + try { + final List<String> lines = new ArrayList<>(); + collect(lines, resultSet); + Collections.sort(lines); + assertEquals(expectedLines, lines); + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + }; + } + + private static void collect(List<String> result, ResultSet resultSet) + throws SQLException { + final StringBuilder buf = new StringBuilder(); + while (resultSet.next()) { + buf.setLength(0); + int n = resultSet.getMetaData().getColumnCount(); + String sep = ""; + for (int i = 1; i <= n; i++) { + buf.append(sep) + .append(resultSet.getMetaData().getColumnLabel(i)) + .append("=") + .append(resultSet.getString(i)); + sep = "; "; + } + result.add(Util.toLinux(buf.toString())); + } + } + + static String toString(ResultSet resultSet) throws SQLException { + StringBuilder buf = new StringBuilder(); + while (resultSet.next()) { + int n = resultSet.getMetaData().getColumnCount(); + String sep = ""; + for (int i = 1; i <= n; i++) { + buf.append(sep) + .append(resultSet.getMetaData().getColumnLabel(i)) + .append("=") + .append(resultSet.getObject(i)); + sep = "; "; + } + buf.append("\n"); + } + return buf.toString(); + } + + static void checkSql(String sql, String model, Consumer<ResultSet> fn) + throws SQLException { + Connection connection = null; + Statement statement = null; + try { + Properties info = new Properties(); + info.put("model", jsonPath(model)); + connection = DriverManager.getConnection("jdbc:calcite:", info); + statement = connection.createStatement(); + final ResultSet resultSet = + statement.executeQuery( + sql); + fn.accept(resultSet); + } finally { + close(connection, statement); + } + } + + static String jsonPath(String model) { + return resourcePath(model + ".json"); + } + + static String resourcePath(String path) { + return Sources.of(FileAdapterTest.class.getResource("/" + path)).file().getAbsolutePath(); + } + + private static void output(ResultSet resultSet, PrintStream out) + throws SQLException { + final ResultSetMetaData metaData = resultSet.getMetaData(); + final int columnCount = metaData.getColumnCount(); + while (resultSet.next()) { + for (int i = 1;; i++) { + out.print(resultSet.getString(i)); + if (i < columnCount) { + out.print(", "); + } else { + out.println(); + break; + } + } + } + } + + private static void output(ResultSet resultSet) { + try { + output(resultSet, System.out); + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + } + + static void close(Connection connection, Statement statement) { + if (statement != null) { + try { + statement.close(); + } catch (SQLException e) { + // ignore + } + } + if (connection != null) { + try { + connection.close(); + } catch (SQLException e) { + // ignore + } + } + } + + /** Fluent API to perform test actions. */ + static class Fluent { + private final String model; + private final String sql; + private final Consumer<ResultSet> expect; + + Fluent(String model, String sql, Consumer<ResultSet> expect) { + this.model = model; + this.sql = sql; + this.expect = expect; + } + + /** Runs the test. */ + Fluent ok() { + try { + checkSql(sql, model, expect); + return this; + } catch (SQLException e) { + throw TestUtil.rethrow(e); + } + } + + /** Assigns a function to call to test whether output is correct. */ + Fluent checking(Consumer<ResultSet> expect) { + return new Fluent(model, sql, expect); + } + + /** Sets the rows that are expected to be returned from the SQL query. */ + Fluent returns(String... expectedLines) { + return checking(expect(expectedLines)); + } + + /** Sets the rows that are expected to be returned from the SQL query, + * in no particular order. */ + Fluent returnsUnordered(String... expectedLines) { + return checking(expectUnordered(expectedLines)); + } + } +} diff --git a/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java b/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java deleted file mode 100644 index 444f225..0000000 --- a/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java +++ /dev/null @@ -1,419 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to you under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ -package org.apache.calcite.adapter.file; - -import org.apache.calcite.util.Sources; -import org.apache.calcite.util.TestUtil; -import org.apache.calcite.util.Util; - -import com.google.common.collect.Ordering; - -import org.junit.jupiter.api.Disabled; -import org.junit.jupiter.api.Test; -import org.junit.jupiter.api.extension.ExtendWith; - -import java.sql.Connection; -import java.sql.DriverManager; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; -import java.sql.Types; -import java.util.ArrayList; -import java.util.Arrays; -import java.util.Collections; -import java.util.List; -import java.util.Properties; -import java.util.function.Function; - -import static org.hamcrest.CoreMatchers.is; -import static org.hamcrest.MatcherAssert.assertThat; -import static org.junit.jupiter.api.Assertions.assertEquals; - -/** - * System test of the Calcite file adapter, which can also read and parse - * HTML tables over HTTP. - */ -@ExtendWith(RequiresNetworkExtension.class) -class SqlTest { - // helper functions - - private Fluent sql(String model, String sql) { - return new Fluent(model, sql, input -> { - throw new AssertionError(); - }); - } - - private static Function<ResultSet, Void> expect(String... expectedLines) { - final StringBuilder b = new StringBuilder(); - for (String s : expectedLines) { - b.append(s).append('\n'); - } - final String expected = b.toString(); - return resultSet -> { - try { - String actual = toString(resultSet); - if (!expected.equals(actual)) { - System.out.println("Assertion failure:"); - System.out.println("\tExpected: '" + expected + "'"); - System.out.println("\tActual: '" + actual + "'"); - } - assertEquals(expected, actual); - } catch (SQLException e) { - throw TestUtil.rethrow(e); - } - return null; - }; - } - - /** Returns a function that checks the contents of a result set against an - * expected string. */ - private static Function<ResultSet, Void> expectUnordered(String... expected) { - final List<String> expectedLines = - Ordering.natural().immutableSortedCopy(Arrays.asList(expected)); - return resultSet -> { - try { - final List<String> lines = new ArrayList<>(); - SqlTest.collect(lines, resultSet); - Collections.sort(lines); - assertEquals(expectedLines, lines); - } catch (SQLException e) { - throw TestUtil.rethrow(e); - } - return null; - }; - } - - private static void collect(List<String> result, ResultSet resultSet) - throws SQLException { - final StringBuilder buf = new StringBuilder(); - while (resultSet.next()) { - buf.setLength(0); - int n = resultSet.getMetaData().getColumnCount(); - String sep = ""; - for (int i = 1; i <= n; i++) { - buf.append(sep) - .append(resultSet.getMetaData().getColumnLabel(i)) - .append("=") - .append(resultSet.getString(i)); - sep = "; "; - } - result.add(Util.toLinux(buf.toString())); - } - } - - private void checkSql(String sql, String model, Function<ResultSet, Void> fn) - throws SQLException { - Connection connection = null; - Statement statement = null; - try { - Properties info = new Properties(); - info.put("model", - Sources.of(SqlTest.class.getResource("/" + model + ".json")).path()); - connection = DriverManager.getConnection("jdbc:calcite:", info); - statement = connection.createStatement(); - final ResultSet resultSet = statement.executeQuery(sql); - fn.apply(resultSet); - } finally { - close(connection, statement); - } - } - - private static String toString(ResultSet resultSet) throws SQLException { - StringBuilder buf = new StringBuilder(); - while (resultSet.next()) { - int n = resultSet.getMetaData().getColumnCount(); - String sep = ""; - for (int i = 1; i <= n; i++) { - buf.append(sep) - .append(resultSet.getMetaData().getColumnLabel(i)) - .append("=") - .append(resultSet.getObject(i)); - sep = "; "; - } - buf.append("\n"); - } - return buf.toString(); - } - - private void close(Connection connection, Statement statement) { - if (statement != null) { - try { - statement.close(); - } catch (SQLException e) { - // ignore - } - } - if (connection != null) { - try { - connection.close(); - } catch (SQLException e) { - // ignore - } - } - } - - // tests - - /** Reads from a local file and checks the result. */ - @Test void testFileSelect() throws SQLException { - final String sql = "select H1 from T1 where H0 = 'R1C0'"; - sql("testModel", sql).returns("H1=R1C1").ok(); - } - - /** Reads from a local file without table headers <TH> and checks the - * result. */ - @Test @RequiresNetwork public void testNoThSelect() throws SQLException { - final String sql = "select \"col1\" from T1_NO_TH where \"col0\" like 'R0%'"; - sql("testModel", sql).returns("col1=R0C1").ok(); - } - - /** Reads from a local file - finds larger table even without <TH> - * elements. */ - @Test void testFindBiggerNoTh() throws SQLException { - final String sql = "select \"col4\" from TABLEX2 where \"col0\" like 'R1%'"; - sql("testModel", sql).returns("col4=R1C4").ok(); - } - - /** Reads from a URL and checks the result. */ - @Disabled("[CALCITE-1789] Wikipedia format change breaks file adapter test") - @Test @RequiresNetwork public void testUrlSelect() throws SQLException { - final String sql = "select \"State\", \"Statehood\" from \"States_as_of\"\n" - + "where \"State\" = 'California'"; - sql("wiki", sql).returns("State=California; Statehood=1850-09-09").ok(); - } - - /** Reads the EMPS table. */ - @Test void testSalesEmps() throws SQLException { - final String sql = "select * from sales.emps"; - sql("sales", sql) - .returns("EMPNO=100; NAME=Fred; DEPTNO=30", - "EMPNO=110; NAME=Eric; DEPTNO=20", - "EMPNO=110; NAME=John; DEPTNO=40", - "EMPNO=120; NAME=Wilma; DEPTNO=20", - "EMPNO=130; NAME=Alice; DEPTNO=40") - .ok(); - } - - /** Reads the DEPTS table. */ - @Test void testSalesDepts() throws SQLException { - final String sql = "select * from sales.depts"; - sql("sales", sql) - .returns("DEPTNO=10; NAME=Sales", - "DEPTNO=20; NAME=Marketing", - "DEPTNO=30; NAME=Accounts") - .ok(); - } - - /** Reads the DEPTS table from the CSV schema. */ - @Test void testCsvSalesDepts() throws SQLException { - final String sql = "select * from sales.depts"; - sql("sales-csv", sql) - .returns("DEPTNO=10; NAME=Sales", - "DEPTNO=20; NAME=Marketing", - "DEPTNO=30; NAME=Accounts") - .ok(); - } - - /** Reads the EMPS table from the CSV schema. */ - @Test void testCsvSalesEmps() throws SQLException { - final String sql = "select * from sales.emps"; - final String[] lines = { - "EMPNO=100; NAME=Fred; DEPTNO=10; GENDER=; CITY=; EMPID=30; AGE=25; SLACKER=true; MANAGER=false; JOINEDAT=1996-08-03", - "EMPNO=110; NAME=Eric; DEPTNO=20; GENDER=M; CITY=San Francisco; EMPID=3; AGE=80; SLACKER=null; MANAGER=false; JOINEDAT=2001-01-01", - "EMPNO=110; NAME=John; DEPTNO=40; GENDER=M; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2002-05-03", - "EMPNO=120; NAME=Wilma; DEPTNO=20; GENDER=F; CITY=; EMPID=1; AGE=5; SLACKER=null; MANAGER=true; JOINEDAT=2005-09-07", - "EMPNO=130; NAME=Alice; DEPTNO=40; GENDER=F; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2007-01-01", - }; - sql("sales-csv", sql).returns(lines).ok(); - } - - /** Reads the HEADER_ONLY table from the CSV schema. The CSV file has one - * line - the column headers - but no rows of data. */ - @Test void testCsvSalesHeaderOnly() throws SQLException { - final String sql = "select * from sales.header_only"; - sql("sales-csv", sql).returns().ok(); - } - - /** Reads the EMPTY table from the CSV schema. The CSV file has no lines, - * therefore the table has a system-generated column called - * "EmptyFileHasNoColumns". */ - @Test void testCsvSalesEmpty() throws SQLException { - final String sql = "select * from sales.\"EMPTY\""; - checkSql(sql, "sales-csv", resultSet -> { - try { - assertThat(resultSet.getMetaData().getColumnCount(), is(1)); - assertThat(resultSet.getMetaData().getColumnName(1), - is("EmptyFileHasNoColumns")); - assertThat(resultSet.getMetaData().getColumnType(1), - is(Types.BOOLEAN)); - String actual = toString(resultSet); - assertThat(actual, is("")); - } catch (SQLException e) { - throw TestUtil.rethrow(e); - } - return null; - }); - } - - /** Test case for - * <a href="https://issues.apache.org/jira/browse/CALCITE-1754";>[CALCITE-1754] - * In Csv adapter, convert DATE and TIME values to int, and TIMESTAMP values - * to long</a>. */ - @Test void testCsvGroupByTimestampAdd() throws SQLException { - final String sql = "select count(*) as c,\n" - + " {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT) } as t\n" - + "from EMPS group by {fn timestampadd(SQL_TSI_DAY, 1, JOINEDAT ) } "; - sql("sales-csv", sql) - .returnsUnordered("C=1; T=1996-08-04", - "C=1; T=2002-05-04", - "C=1; T=2005-09-08", - "C=1; T=2007-01-02", - "C=1; T=2001-01-02") - .ok(); - final String sql2 = "select count(*) as c,\n" - + " {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } as t\n" - + "from EMPS group by {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT ) } "; - sql("sales-csv", sql2) - .returnsUnordered("C=1; T=2002-06-03", - "C=1; T=2005-10-07", - "C=1; T=2007-02-01", - "C=1; T=2001-02-01", - "C=1; T=1996-09-03").ok(); - final String sql3 = "select\n" - + " distinct {fn timestampadd(SQL_TSI_MONTH, 1, JOINEDAT) } as t\n" - + "from EMPS"; - sql("sales-csv", sql3) - .returnsUnordered("T=2002-06-03", - "T=2005-10-07", - "T=2007-02-01", - "T=2001-02-01", - "T=1996-09-03").ok(); - } - - /** Fluent API to perform test actions. */ - private class Fluent { - private final String model; - private final String sql; - private final Function<ResultSet, Void> expect; - - Fluent(String model, String sql, Function<ResultSet, Void> expect) { - this.model = model; - this.sql = sql; - this.expect = expect; - } - - /** Runs the test. */ - Fluent ok() { - try { - checkSql(sql, model, expect); - return this; - } catch (SQLException e) { - throw TestUtil.rethrow(e); - } - } - - /** Assigns a function to call to test whether output is correct. */ - Fluent checking(Function<ResultSet, Void> expect) { - return new Fluent(model, sql, expect); - } - - /** Sets the rows that are expected to be returned from the SQL query. */ - Fluent returns(String... expectedLines) { - return checking(expect(expectedLines)); - } - - /** Sets the rows that are expected to be returned from the SQL query, - * in no particular order. */ - Fluent returnsUnordered(String... expectedLines) { - return checking(expectUnordered(expectedLines)); - } - } - - /** Reads the DEPTS table from the JSON schema. */ - @Test void testJsonSalesDepts() throws SQLException { - final String sql = "select * from sales.depts"; - sql("sales-json", sql) - .returns("DEPTNO=10; NAME=Sales", - "DEPTNO=20; NAME=Marketing", - "DEPTNO=30; NAME=Accounts") - .ok(); - } - - /** Reads the EMPS table from the JSON schema. */ - @Test void testJsonSalesEmps() throws SQLException { - final String sql = "select * from sales.emps"; - final String[] lines = { - "EMPNO=100; NAME=Fred; DEPTNO=10; GENDER=; CITY=; EMPID=30; AGE=25; SLACKER=true; MANAGER=false; JOINEDAT=1996-08-03", - "EMPNO=110; NAME=Eric; DEPTNO=20; GENDER=M; CITY=San Francisco; EMPID=3; AGE=80; SLACKER=null; MANAGER=false; JOINEDAT=2001-01-01", - "EMPNO=110; NAME=John; DEPTNO=40; GENDER=M; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2002-05-03", - "EMPNO=120; NAME=Wilma; DEPTNO=20; GENDER=F; CITY=; EMPID=1; AGE=5; SLACKER=null; MANAGER=true; JOINEDAT=2005-09-07", - "EMPNO=130; NAME=Alice; DEPTNO=40; GENDER=F; CITY=Vancouver; EMPID=2; AGE=null; SLACKER=false; MANAGER=true; JOINEDAT=2007-01-01", - }; - sql("sales-json", sql).returns(lines).ok(); - } - - /** Reads the EMPTY table from the JSON schema. The JSON file has no lines, - * therefore the table has a system-generated column called - * "EmptyFileHasNoColumns". */ - @Test void testJsonSalesEmpty() throws SQLException { - final String sql = "select * from sales.\"EMPTY\""; - checkSql(sql, "sales-json", resultSet -> { - try { - assertThat(resultSet.getMetaData().getColumnCount(), is(1)); - assertThat(resultSet.getMetaData().getColumnName(1), - is("EmptyFileHasNoColumns")); - assertThat(resultSet.getMetaData().getColumnType(1), - is(Types.BOOLEAN)); - String actual = toString(resultSet); - assertThat(actual, is("")); - } catch (SQLException e) { - throw TestUtil.rethrow(e); - } - return null; - }); - } - - /** Test returns the result of two json file joins. */ - @Test void testJsonJoinOnString() { - final String sql = "select emps.EMPNO, emps.NAME, depts.deptno from emps\n" - + "join depts on emps.deptno = depts.deptno"; - final String[] lines = { - "EMPNO=100; NAME=Fred; DEPTNO=10", - "EMPNO=110; NAME=Eric; DEPTNO=20", - "EMPNO=120; NAME=Wilma; DEPTNO=20", - }; - sql("sales-json", sql).returns(lines).ok(); - } - - /** The folder contains both JSON files and CSV files joins. */ - @Test void testJsonWithCsvJoin() { - final String sql = "select emps.empno,\n" - + " NAME,\n" - + " \"DATE\".JOINEDAT\n" - + " from \"DATE\"\n" - + "join emps on emps.empno = \"DATE\".EMPNO\n" - + "order by empno, name, joinedat limit 3"; - final String[] lines = { - "EMPNO=100; NAME=Fred; JOINEDAT=1996-08-03", - "EMPNO=110; NAME=Eric; JOINEDAT=2001-01-01", - "EMPNO=110; NAME=Eric; JOINEDAT=2002-05-03", - }; - sql("sales-json", sql) - .returns(lines) - .ok(); - } -} diff --git a/file/build.gradle.kts b/file/src/test/resources/bug.json similarity index 69% copy from file/build.gradle.kts copy to file/src/test/resources/bug.json index 1610a03..46d0cb4 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/bug.json @@ -14,15 +14,17 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +{ + "version": "1.0", + "defaultSchema": "BUG", + "schemas": [ + { + "name": "BUG", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "directory": "bug" + } + } + ] } diff --git a/file/src/test/resources/bug.yaml b/file/src/test/resources/bug.yaml new file mode 100644 index 0000000..fd50d5b --- /dev/null +++ b/file/src/test/resources/bug.yaml @@ -0,0 +1,24 @@ +# +# 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. +# +version: 1.0 +defaultSchema: BUG +schemas: +- name: BUG + type: custom + factory: org.apache.calcite.adapter.csv.CsvSchemaFactory + operand: + directory: bug diff --git a/file/build.gradle.kts b/file/src/test/resources/bug/ARCHERS.json similarity index 56% copy from file/build.gradle.kts copy to file/src/test/resources/bug/ARCHERS.json index 1610a03..0e36759 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/bug/ARCHERS.json @@ -14,15 +14,30 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +[ +{ + "id": "19990101", + "dow": "Friday", + "longDate": "New Years Day", + "title": "Tractor trouble.", + "characters": [ "Alice", "Bob", "Xavier" ], + "script": "Julian Hyde", + "summary": "", + "lines": [ + "Bob's tractor got stuck in a field.", + "Alice and Xavier hatch a plan to surprise Charlie." + ] +}, +{ + "id": "19990103", + "dow": "Sunday", + "longDate": "Sunday 3rd January", + "title": "Charlie's surprise.", + "characters": [ "Alice", "Zebedee", "Charlie", "Xavier" ], + "script": "William Shakespeare", + "summary": "", + "lines": [ + "Charlie is very surprised by Alice and Xavier's surprise plan." + ] } +] diff --git a/file/src/test/resources/bug/DATE.csv b/file/src/test/resources/bug/DATE.csv new file mode 100644 index 0000000..2999baf --- /dev/null +++ b/file/src/test/resources/bug/DATE.csv @@ -0,0 +1,9 @@ +EMPNO:int,JOINEDAT:date,JOINTIME:time,JOINTIMES:timestamp +100,"1996-08-03","00:01:02","1996-08-03 00:01:02" +110,"2001-01-01","00:00:00","2001-01-01 00:00:00" +110,"2002-05-03","00:00:00","2002-05-03 00:00:00" +120,"2005-09-07","00:00:00","2005-09-07 00:00:00" +130,"2007-01-01","00:00:00","2007-01-01 00:00:00" +140,"2015-12-31","07:15:56","2015-12-31 07:15:56" +150,"2015-12-31","13:31:21","2015-12-31 13:31:21" +200,,, diff --git a/file/src/test/resources/bug/LONG_EMPS.csv b/file/src/test/resources/bug/LONG_EMPS.csv new file mode 100644 index 0000000..f69e0c5 --- /dev/null +++ b/file/src/test/resources/bug/LONG_EMPS.csv @@ -0,0 +1,6 @@ +EMPNO:long,NAME:string,DEPTNO:int,GENDER:string,CITY:string,EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date +100,"Fred",10,,,30,25,true,false,"1996-08-03" +110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01" +110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03" +120,"Wilma",20,"F",,1,5,,true,"2005-09-07" +130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01" diff --git a/file/src/test/resources/bug/WACKY_COLUMN_NAMES.csv b/file/src/test/resources/bug/WACKY_COLUMN_NAMES.csv new file mode 100644 index 0000000..453d961 --- /dev/null +++ b/file/src/test/resources/bug/WACKY_COLUMN_NAMES.csv @@ -0,0 +1,6 @@ +EMPNO:int,naME:string,DEPTNO:Integer,2gender:string,CITY:string,EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,joined at:date +100,"Fred",10,,,30,25,true,false,"1996-08-03" +110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01" +110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03" +120,"Wilma",20,"F",,1,5,,true,"2005-09-07" +130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01" diff --git a/file/build.gradle.kts b/file/src/test/resources/filterable-model.json similarity index 62% copy from file/build.gradle.kts copy to file/src/test/resources/filterable-model.json index 1610a03..abc2ab6 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/filterable-model.json @@ -13,16 +13,23 @@ * 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. + * + * A JSON model of a Calcite schema that is similar to model.json, + * except that it produces tables that implement FilterableTable. + * These tables can implement their own simple filtering. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +{ + "version": "1.0", + "defaultSchema": "SALES", + "schemas": [ + { + "name": "SALES", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "directory": "sales", + "flavor": "FILTERABLE" + } + } + ] } diff --git a/file/build.gradle.kts b/file/src/test/resources/model-with-custom-table.json similarity index 66% copy from file/build.gradle.kts copy to file/src/test/resources/model-with-custom-table.json index 1610a03..19aa032 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/model-with-custom-table.json @@ -14,15 +14,23 @@ * See the License for the specific language governing permissions and * limitations under the License. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +{ + "version": "1.0", + "defaultSchema": "CUSTOM_TABLE", + "schemas": [ + { + "name": "CUSTOM_TABLE", + "tables": [ + { + "name": "EMPS", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.CsvTableFactory", + "operand": { + "file": "sales/EMPS.csv.gz", + "flavor": "scannable" + } + } + ] + } + ] } diff --git a/file/build.gradle.kts b/file/src/test/resources/model.json similarity index 69% copy from file/build.gradle.kts copy to file/src/test/resources/model.json index 1610a03..826ca1a 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/model.json @@ -13,16 +13,20 @@ * 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. + * + * A JSON model of a simple Calcite schema. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +{ + "version": "1.0", + "defaultSchema": "SALES", + "schemas": [ + { + "name": "SALES", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "directory": "sales" + } + } + ] } diff --git a/file/src/test/resources/sales/DEPTS.csv b/file/src/test/resources/sales/DEPTS.csv new file mode 100644 index 0000000..628f2d8 --- /dev/null +++ b/file/src/test/resources/sales/DEPTS.csv @@ -0,0 +1,4 @@ +DEPTNO:int,NAME:string +10,"Sales" +20,"Marketing" +30,"Accounts" diff --git a/file/src/test/resources/sales/EMPS.csv.gz b/file/src/test/resources/sales/EMPS.csv.gz new file mode 100644 index 0000000..294bad4 Binary files /dev/null and b/file/src/test/resources/sales/EMPS.csv.gz differ diff --git a/file/build.gradle.kts b/file/src/test/resources/smart.json similarity index 52% copy from file/build.gradle.kts copy to file/src/test/resources/smart.json index 1610a03..9b86761 100644 --- a/file/build.gradle.kts +++ b/file/src/test/resources/smart.json @@ -13,16 +13,28 @@ * 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. + * + * A JSON model of a Calcite schema that is similar to model.json, + * except that it produces tables that implement FilterableTable. + * These tables can implement their own simple filtering. + * + * A JSON model of a Calcite schema that is similar to model.json, + * except that it produces tables that implement + * TranslatableTable. These tables are translated to a CsvTableScan + * relational expression which participates in query planning. */ -dependencies { - api(project(":core")) - api(project(":example:csv")) - api(project(":linq4j")) - - implementation("com.google.guava:guava") - implementation("com.joestelmach:natty") - implementation("org.apache.calcite.avatica:avatica-core") - implementation("org.jsoup:jsoup") - - testImplementation(project(":core", "testClasses")) +{ + "version": "1.0", + "defaultSchema": "SALES", + "schemas": [ + { + "name": "SALES", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "directory": "sales", + "flavor": "TRANSLATABLE" + } + } + ] }
