[CALCITE-884] Web adapter (Henry Olson) Some fix-ups (Julian Hyde): * Upgrade calcite; * Upgrade natty, jsoup; * Rename files, and move to org.apache.calcite.adapter.file package; * Add file adapter documentation and history (based on original README and HISTORY); * Add EMPS.html and DEPTS.html examples.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/cf46d3be Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/cf46d3be Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/cf46d3be Branch: refs/heads/master Commit: cf46d3be19f701ad056aba3b60a701b962e0f1b0 Parents: 1b11385 Author: Henry Olson <[email protected]> Authored: Thu Apr 14 22:31:10 2016 +0100 Committer: Julian Hyde <[email protected]> Committed: Wed Feb 22 21:09:47 2017 -0800 ---------------------------------------------------------------------- .../apache/calcite/schema/SchemaFactory.java | 34 +- file/pom.xml | 112 ++++++ .../calcite/adapter/file/FileEnumerator.java | 94 +++++ .../calcite/adapter/file/FileFieldType.java | 86 ++++ .../apache/calcite/adapter/file/FileReader.java | 232 +++++++++++ .../adapter/file/FileReaderException.java | 31 ++ .../calcite/adapter/file/FileRowConverter.java | 393 +++++++++++++++++++ .../apache/calcite/adapter/file/FileSchema.java | 67 ++++ .../calcite/adapter/file/FileSchemaFactory.java | 47 +++ .../apache/calcite/adapter/file/FileTable.java | 122 ++++++ .../calcite/adapter/file/FileTableScan.java | 94 +++++ .../calcite/adapter/file/package-info.java | 27 ++ .../calcite/adapter/file/FileReaderTest.java | 185 +++++++++ .../apache/calcite/adapter/file/FileSuite.java | 58 +++ .../apache/calcite/adapter/file/SqlTest.java | 189 +++++++++ .../calcite/adapter/file/package-info.java | 27 ++ file/src/test/resources/sales.json | 18 + file/src/test/resources/sales/DEPTS.html | 42 ++ file/src/test/resources/sales/EMPS.html | 56 +++ file/src/test/resources/tableNoTH.html | 39 ++ file/src/test/resources/tableNoTheadTbody.html | 44 +++ file/src/test/resources/tableOK.html | 30 ++ file/src/test/resources/tableX2.html | 69 ++++ file/src/test/resources/testModel.json | 21 + file/src/test/resources/webjoin.sql | 25 ++ file/src/test/resources/wiki.json | 93 +++++ pom.xml | 18 + site/_docs/adapter.md | 1 + site/_docs/file.md | 236 +++++++++++ site/_docs/history.md | 15 + sqlline | 2 +- sqlline.bat | 2 +- 32 files changed, 2490 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/core/src/main/java/org/apache/calcite/schema/SchemaFactory.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/schema/SchemaFactory.java b/core/src/main/java/org/apache/calcite/schema/SchemaFactory.java index 57f5b4b..89f8496 100644 --- a/core/src/main/java/org/apache/calcite/schema/SchemaFactory.java +++ b/core/src/main/java/org/apache/calcite/schema/SchemaFactory.java @@ -24,27 +24,27 @@ import java.util.Map; * <p>A schema factory allows you to include a custom schema in a model file. * For example, here is a model that contains a custom schema whose tables * read CSV files. (See the - * <a href="https://github.com/julianhyde/optiq-csv";>optiq-csv</a> for more - * details about this particular adapter.)</p> + * <a href="http://calcite.apache.org/apidocs/org/apache/calcite/adapter/csv/package-summary.html";>example CSV adapter</a> + * for more details about this particular adapter.) * * <pre>{@code * { - * version: '1.0', - * defaultSchema: 'SALES', - * schemas: [ + * "version": "1.0", + * "defaultSchema": "SALES", + * "schemas": [ * { - * name: 'SALES', - * type: 'custom', - * factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory', - * mutable: true, - * operand: { - * directory: 'target/test-classes/sales' + * "name": "SALES", + * "type": "custom", + * "factory": "org.apache.calcite.adapter.csv.CsvSchemaFactory", + * "mutable": true, + * "operand": { + * directory: "target/test-classes/sales" * }, - * tables: [ + * "tables": [ * { - * name: 'FEMALE_EMPS', - * type: 'view', - * sql: 'SELECT * FROM emps WHERE gender = \'F\'' + * "name": "FEMALE_EMPS", + * "type": "view", + * "sql": "SELECT * FROM emps WHERE gender = 'F'" * } * ] * } @@ -55,10 +55,10 @@ import java.util.Map; * * <p>If you do not wish to allow model authors to add additional tables * (including views) to an instance of your schema, specify - * 'mutable: false'.</p> + * 'mutable: false'. * * <p>A class that implements SchemaFactory specified in a schema must have a - * public default constructor.</p> + * public default constructor. */ public interface SchemaFactory { /** Creates a Schema. http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/pom.xml ---------------------------------------------------------------------- diff --git a/file/pom.xml b/file/pom.xml new file mode 100644 index 0000000..d8173a5 --- /dev/null +++ b/file/pom.xml @@ -0,0 +1,112 @@ +<!-- +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. +--> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd";> + <modelVersion>4.0.0</modelVersion> + <parent> + <groupId>org.apache.calcite</groupId> + <artifactId>calcite</artifactId> + <version>1.12.0-SNAPSHOT</version> + </parent> + + <!-- The basics. --> + <artifactId>calcite-file</artifactId> + <packaging>jar</packaging> + <version>1.12.0-SNAPSHOT</version> + <name>Calcite File</name> + <description>Calcite provider that reads files and URIs</description> + + <properties> + <top.dir>${project.basedir}/..</top.dir> + <build.timestamp>${maven.build.timestamp}</build.timestamp> + </properties> + + <dependencies> + <dependency> + <groupId>com.google.guava</groupId> + <artifactId>guava</artifactId> + </dependency> + <dependency> + <groupId>com.joestelmach</groupId> + <artifactId>natty</artifactId> + </dependency> + <dependency> + <groupId>junit</groupId> + <artifactId>junit</artifactId> + <scope>test</scope> + </dependency> + <dependency> + <groupId>org.apache.calcite</groupId> + <artifactId>calcite-core</artifactId> + </dependency> + <dependency> + <groupId>org.apache.calcite</groupId> + <artifactId>calcite-linq4j</artifactId> + </dependency> + <dependency> + <groupId>org.jsoup</groupId> + <artifactId>jsoup</artifactId> + </dependency> + </dependencies> + + <build> + <plugins> + <plugin> + <groupId>org.codehaus.mojo</groupId> + <artifactId>jalopy-maven-plugin</artifactId> + <version>1.0-alpha-1</version> + <configuration> + <srcExcludesPattern>**/CsvTableScan.java</srcExcludesPattern> + <failOnError>false</failOnError> + <convention>${top.dir}src/main/config/jalopy.xml</convention> + </configuration> + </plugin> + + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-surefire-plugin</artifactId> + <version>2.18</version> + <configuration> + <includes> + <include>org/apache/calcite/adapter/file/FileSuite.java</include> + </includes> + </configuration> + </plugin> + + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-dependency-plugin</artifactId> + <version>2.8</version> + <executions> + <execution> + <id>copy-dependencies</id> + <phase>package</phase> + <goals> + <goal>copy-dependencies</goal> + </goals> + <configuration> + <outputDirectory>${project.build.directory}/dependencies/</outputDirectory> + <overWriteReleases>false</overWriteReleases> + <overWriteSnapshots>false</overWriteSnapshots> + <overWriteIfNewer>true</overWriteIfNewer> + </configuration> + </execution> + </executions> + </plugin> + </plugins> + </build> + +</project> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileEnumerator.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileEnumerator.java b/file/src/main/java/org/apache/calcite/adapter/file/FileEnumerator.java new file mode 100644 index 0000000..952ccd3 --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileEnumerator.java @@ -0,0 +1,94 @@ +/* + * 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.linq4j.Enumerator; + +import org.jsoup.select.Elements; + +import java.util.Iterator; + +/** + * Wraps WebReader and FileRowConverter, enumerates tr Elements as + * table rows. + */ +class FileEnumerator implements Enumerator<Object> { + + private final Iterator<Elements> iterator; + private final FileRowConverter converter; + private final int[] fields; + private Object current; + + public FileEnumerator(Iterator<Elements> iterator, + FileRowConverter converter) { + this.iterator = iterator; + this.converter = converter; + this.fields = identityList(this.converter.width()); + } + + public FileEnumerator(Iterator<Elements> iterator, FileRowConverter converter, + int[] fields) { + this.iterator = iterator; + this.converter = converter; + this.fields = fields; + } + + public Object current() { + if (current == null) { + this.moveNext(); + } + return current; + } + + public boolean moveNext() { + try { + if (this.iterator.hasNext()) { + final Elements row = this.iterator.next(); + current = this.converter.toRow(row, this.fields); + return true; + } else { + current = null; + return false; + } + } catch (Exception e) { + throw new RuntimeException(e); + } + } + + // required by linq4j Enumerator interface + public void reset() { + throw new UnsupportedOperationException(); + } + + // required by linq4j Enumerator interface + public void close() { + } + + /** Returns an array of integers {0, ..., n - 1}. */ + private static int[] identityList(int n) { + int[] integers = new int[n]; + + for (int i = 0; i < n; i++) { + integers[i] = i; + } + + return integers; + } + +} + +// End FileEnumerator.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileFieldType.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileFieldType.java b/file/src/main/java/org/apache/calcite/adapter/file/FileFieldType.java new file mode 100644 index 0000000..3b3c5b9 --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileFieldType.java @@ -0,0 +1,86 @@ +/* + * 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.adapter.java.JavaTypeFactory; +import org.apache.calcite.linq4j.tree.Primitive; +import org.apache.calcite.rel.type.RelDataType; + +import com.google.common.collect.ImmutableMap; + +import java.util.Map; + +/** + * Type of a field in a Web (HTML) table. + * + * <p>Usually, and unless specified explicitly in the header row, a field is + * of type {@link #STRING}. But specifying the field type in the fields + * makes it easier to write SQL. + * + * <p>Trivially modified from CsvFieldType. + */ +enum FileFieldType { + STRING(null, String.class), + BOOLEAN(Primitive.BOOLEAN), + BYTE(Primitive.BYTE), + CHAR(Primitive.CHAR), + SHORT(Primitive.SHORT), + INT(Primitive.INT), + LONG(Primitive.LONG), + FLOAT(Primitive.FLOAT), + DOUBLE(Primitive.DOUBLE), + DATE(null, java.sql.Date.class), + TIME(null, java.sql.Time.class), + TIMESTAMP(null, java.sql.Timestamp.class); + + private final Primitive primitive; + private final Class clazz; + + private static final Map<String, FileFieldType> MAP; + + static { + ImmutableMap.Builder<String, FileFieldType> builder = + ImmutableMap.builder(); + for (FileFieldType value : values()) { + builder.put(value.clazz.getSimpleName(), value); + + if (value.primitive != null) { + builder.put(value.primitive.primitiveClass.getSimpleName(), value); + } + } + MAP = builder.build(); + } + + FileFieldType(Primitive primitive) { + this(primitive, primitive.boxClass); + } + + FileFieldType(Primitive primitive, Class clazz) { + this.primitive = primitive; + this.clazz = clazz; + } + + public RelDataType toType(JavaTypeFactory typeFactory) { + return typeFactory.createJavaType(clazz); + } + + public static FileFieldType of(String typeString) { + return MAP.get(typeString); + } +} + +// End FileFieldType.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileReader.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileReader.java b/file/src/main/java/org/apache/calcite/adapter/file/FileReader.java new file mode 100644 index 0000000..bd26f4a --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileReader.java @@ -0,0 +1,232 @@ +/* + * 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.jsoup.Jsoup; + +import org.jsoup.nodes.Document; +import org.jsoup.nodes.Element; + +import org.jsoup.select.Elements; + +import java.io.File; + +import java.io.IOException; + +import java.net.MalformedURLException; +import java.net.URL; + +import java.util.Iterator; + +/** + * Scrapes HTML tables from URLs using Jsoup. + */ +public class FileReader implements Iterable<Elements> { + + private static final String DEFAULT_CHARSET = "UTF-8"; + + private final URL url; + private final String selector; + private final Integer index; + private final String charset = DEFAULT_CHARSET; + private Element tableElement; + private Elements headings; + + public FileReader(String url, String selector, Integer index) + throws FileReaderException { + if (url == null) { + throw new FileReaderException("URL must not be null"); + } + + try { + this.url = new URL(url); + } catch (MalformedURLException e) { + throw new FileReaderException("Malformed URL: '" + url + "'", e); + } + this.selector = selector; + this.index = index; + } + + public FileReader(String url, String selector) throws FileReaderException { + this(url, selector, null); + } + + public FileReader(String url) throws FileReaderException { + this(url, null, null); + } + + private void getTable() throws FileReaderException { + + Document doc; + try { + String proto = this.url.getProtocol(); + if (proto.equals("file")) { + doc = Jsoup.parse(new File(this.url.getFile()), this.charset); + } else { + doc = Jsoup.connect(this.url.toString()).get(); + } + } catch (IOException e) { + throw new FileReaderException("Cannot read " + this.url.toString(), e); + } + + this.tableElement = (this.selector != null && !this.selector.equals("")) + ? getSelectedTable(doc, this.selector) : getBestTable(doc); + + } + + private Element getSelectedTable(Document doc, String selector) + throws FileReaderException { + // get selected elements + Elements list = doc.select(selector); + + // get the element + Element el; + + if (this.index == null) { + if (list.size() != 1) { + throw new FileReaderException("" + list.size() + + " HTML element(s) selected"); + } + + el = list.first(); + } else { + el = list.get(this.index); + } + + // verify element is a table + if (el.tag().getName().equals("table")) { + return el; + } else { + throw new FileReaderException("selected (" + selector + ") element is a " + + el.tag().getName() + ", not a table"); + } + } + + private Element getBestTable(Document doc) throws FileReaderException { + Element bestTable = null; + int bestScore = -1; + + for (Element t : doc.select("table")) { + int rows = t.select("tr").size(); + Element firstRow = t.select("tr").get(0); + int cols = firstRow.select("th,td").size(); + int thisScore = rows * cols; + if (thisScore > bestScore) { + bestTable = t; + bestScore = thisScore; + } + } + + if (bestTable == null) { + throw new FileReaderException("no tables found"); + } + + return bestTable; + } + + void refresh() throws FileReaderException { + this.headings = null; + getTable(); + } + + Elements getHeadings() throws FileReaderException { + + if (this.headings == null) { + this.iterator(); + } + + return this.headings; + } + + private String tableKey() { + return "Table: {url: " + this.url + ", selector: " + this.selector; + } + + public FileReaderIterator iterator() { + if (this.tableElement == null) { + try { + getTable(); + } catch (Exception e) { + // TODO: temporary hack + throw new RuntimeException(e); + } + } + + FileReaderIterator iterator = + new FileReaderIterator(this.tableElement.select("tr")); + + // if we haven't cached the headings, get them + // TODO: this needs to be reworked to properly cache the headings + //if (this.headings == null) { + if (true) { + // first row must contain headings + Elements headings = iterator.next("th"); + // if not, generate some default column names + if (headings.size() == 0) { + // rewind and peek at the first row of data + iterator = new FileReaderIterator(this.tableElement.select("tr")); + Elements firstRow = iterator.next("td"); + int i = 0; + headings = new Elements(); + for (Element td : firstRow) { + Element th = td.clone(); + th.tagName("th"); + th.html("col" + i++); + headings.add(th); + } + // rewind, so queries see the first row + iterator = new FileReaderIterator(this.tableElement.select("tr")); + } + this.headings = headings; + } + + return iterator; + } + + public void close() { + } + + /** Iterates over HTML tables, returning an Elements per row. */ + private class FileReaderIterator implements Iterator<Elements> { + Iterator<Element> rowIterator; + + FileReaderIterator(Elements rows) { + this.rowIterator = rows.iterator(); + } + + public boolean hasNext() { + return this.rowIterator.hasNext(); + } + + Elements next(String selector) { + Element row = this.rowIterator.next(); + + return row.select(selector); + } + + // return th and td elements by default + public Elements next() { + return next("th,td"); + } + + public void remove() { + throw new UnsupportedOperationException("NFW - can't remove!"); + } + } +} + +// End FileReader.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileReaderException.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileReaderException.java b/file/src/main/java/org/apache/calcite/adapter/file/FileReaderException.java new file mode 100644 index 0000000..faafa69 --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileReaderException.java @@ -0,0 +1,31 @@ +/* + * 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; + +/** + * Indicates that the FileReader failed. + */ +class FileReaderException extends Exception { + FileReaderException(String message) { + super(message); + } + FileReaderException(String message, Throwable e) { + super(message, e); + } +} + +// End FileReaderException.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileRowConverter.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileRowConverter.java b/file/src/main/java/org/apache/calcite/adapter/file/FileRowConverter.java new file mode 100644 index 0000000..4510f3b --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileRowConverter.java @@ -0,0 +1,393 @@ +/* + * 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.adapter.java.JavaTypeFactory; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.util.Pair; + +import com.google.common.base.Joiner; + +import com.joestelmach.natty.DateGroup; +import com.joestelmach.natty.Parser; + +import org.jsoup.nodes.Element; +import org.jsoup.select.Elements; + +import java.text.NumberFormat; +import java.text.ParseException; + +import java.util.ArrayList; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + + +/** + * FileRowConverter. + */ +class FileRowConverter { + + // cache for lazy initialization + private final FileReader fileReader; + private final List<Map<String, Object>> fieldConfigs; + private boolean initialized = false; + + // row parser configuration + private ArrayList<FieldDef> fields; + + /** Creates a FileRowConverter. */ + FileRowConverter(FileReader fileReader, + List<Map<String, Object>> fieldConfigs) { + this.fileReader = fileReader; + this.fieldConfigs = fieldConfigs; + } + + // initialize() - combine HTML table header information with field definitions + // to initialize the table reader + // NB: object initialization is deferred to avoid unnecessary URL reads + private void initialize() { + if (this.initialized) { + return; + } + try { + this.fields = new ArrayList<>(); + final Elements headerElements = this.fileReader.getHeadings(); + + // create a name to index map for HTML table elements + final Map<String, Integer> headerMap = new HashMap<>(); + int i = 0; + for (Element th : headerElements) { + String heading = th.text(); + if (headerMap.containsKey(heading)) { + throw new Exception("duplicate heading: '" + heading + "'"); + } + headerMap.put(heading, i++); + } + + // instantiate the field definitions + final Set<String> colNames = new HashSet<>(); + final Set<String> sources = new HashSet<>(); + if (this.fieldConfigs != null) { + try { + for (Map<String, Object> fieldConfig : this.fieldConfigs) { + + String thName = (String) fieldConfig.get("th"); + String name = thName; + String newName; + FileFieldType type = null; + boolean skip = false; + + if (!headerMap.containsKey(thName)) { + throw new Exception("bad source column name: '" + thName + "'"); + } + if ((newName = (String) fieldConfig.get("name")) != null) { + name = newName; + } + if (colNames.contains(name)) { + throw new Exception("duplicate column name: '" + name + "'"); + } + + String typeString = (String) fieldConfig.get("type"); + if (typeString != null) { + type = FileFieldType.of(typeString); + } + + String sSkip = (String) fieldConfig.get("skip"); + if (sSkip != null) { + skip = Boolean.parseBoolean(sSkip); + } + + Integer sourceIx = headerMap.get(thName); + colNames.add(name); + sources.add(thName); + if (!skip) { + addFieldDef(name, type, fieldConfig, sourceIx); + } + } + } catch (Exception e) { + throw new RuntimeException(e); + } + } + + // pick up any data elements not explicitly defined + for (String name : headerMap.keySet()) { + if (!sources.contains(name) && !colNames.contains(name)) { + addFieldDef(name, null, null, headerMap.get(name)); + } + } + + // ToDo + } catch (Exception e) { + throw new RuntimeException(e); + } + this.initialized = true; + } + + // add another field definition to the FileRowConverter during initialization + private void addFieldDef(String name, FileFieldType type, + Map<String, Object> config, int sourceCol) { + this.fields.add(new FieldDef(name, type, config, sourceCol)); + } + + /** Converts a row of JSoup Elements to an array of java objects. */ + Object toRow(Elements rowElements, int[] projection) { + initialize(); + final Object[] objects = new Object[projection.length]; + + for (int i = 0; i < projection.length; i++) { + int field = projection[i]; + objects[i] = this.fields.get(field).convert(rowElements); + } + return objects; + } + + int width() { + initialize(); + return this.fields.size(); + } + + RelDataType getRowType(JavaTypeFactory typeFactory) { + initialize(); + List<String> names = new ArrayList<>(); + List<RelDataType> types = new ArrayList<>(); + + // iterate through FieldDefs, populating names and types + for (FieldDef f : this.fields) { + names.add(f.getName()); + + FileFieldType fieldType = f.getType(); + RelDataType type; + + if (fieldType == null) { + type = typeFactory.createJavaType(String.class); + } else { + type = fieldType.toType(typeFactory); + } + + types.add(type); + } + + if (names.isEmpty()) { + names.add("line"); + types.add(typeFactory.createJavaType(String.class)); + } + + return typeFactory.createStructType(Pair.zip(names, types)); + } + + /** Parses an an HTML table cell. */ + private class CellReader { + private String type; + private String selector; + private Integer selectedElement; + private String replaceText; + private Pattern replacePattern; + private String replaceWith; + private String matchText; + private Pattern matchPattern; + private Integer matchSeq; + + CellReader(Map<String, Object> config) { + if (config != null) { + this.type = (String) config.get("type"); + this.selector = (String) config.get("selector"); + this.selectedElement = (Integer) config.get("selectedElement"); + this.replaceText = (String) config.get("replace"); + this.replaceWith = (String) config.get("replaceWith"); + this.matchText = (String) config.get("match"); + this.matchSeq = (Integer) config.get("matchSeq"); + } + + if (this.selector == null) { + this.selector = "*"; + } + + if (this.replaceText != null) { + this.replacePattern = Pattern.compile(this.replaceText); + } + + if (this.replaceWith == null) { + this.replaceWith = ""; + } + + if (this.matchText != null) { + this.matchPattern = Pattern.compile(this.matchText); + } + + if (this.matchSeq == null) { + this.matchSeq = 0; + } + + } + + String read(Element cell) { + ArrayList<String> cellText = new ArrayList<>(); + + if (this.selectedElement != null) { + cellText.add(cell.select(this.selector) + .get(this.selectedElement).ownText()); + } else { + for (Element child : cell.select(this.selector)) { + //String tagName = child.tag().getName(); + cellText.add(child.ownText()); + } + } + + String cellString = Joiner.on(" ").join(cellText).trim(); + + // replace + if (this.replacePattern != null) { + Matcher m = this.replacePattern.matcher(cellString); + cellString = m.replaceAll(this.replaceWith); + } + + // match + if (this.matchPattern == null) { + return cellString; + } else { + List<String> allMatches = new ArrayList<>(); + Matcher m = this.matchPattern.matcher(cellString); + while (m.find()) { + allMatches.add(m.group()); + } + if (allMatches.size() != 0) { + return allMatches.get(this.matchSeq); + } else { + return null; + } + } + } + } + + /** Responsible for managing field (column) definition, + * and for converting an Element to a java data type. */ + private class FieldDef { + String name; + FileFieldType type; + Map<String, Object> config; + CellReader cellReader; + int cellSeq; + + FieldDef(String name, FileFieldType type, Map<String, Object> config, + int cellSeq) { + this.name = name; + this.type = type; + this.config = config; + this.cellReader = new CellReader(config); + this.cellSeq = cellSeq; + } + + Object convert(Elements row) { + return toObject(this.type, this.cellReader.read(row.get(this.cellSeq))); + } + + public String getName() { + return this.name; + } + + FileFieldType getType() { + return this.type; + } + + private java.util.Date parseDate(String string) { + Parser parser = new Parser(); + List groups = parser.parse(string); + DateGroup group = (DateGroup) groups.get(0); + return group.getDates().get(0); + } + + private Object toObject(FileFieldType fieldType, String string) { + if ((string == null) || (string.length() == 0)) { + return null; + } + + if (fieldType == null) { + return string; + } + + switch (fieldType) { + default: + case STRING: + return string; + + case BOOLEAN: + return Boolean.parseBoolean(string); + + case BYTE: + return Byte.parseByte(string); + + case SHORT: + + try { + return NumberFormat.getIntegerInstance().parse(string) + .shortValue(); + } catch (ParseException e) { + return null; + } + + case INT: + + try { + return NumberFormat.getIntegerInstance().parse(string) + .intValue(); + } catch (ParseException e) { + return null; + } + + case LONG: + + try { + return NumberFormat.getInstance().parse(string).longValue(); + } catch (ParseException e) { + return null; + } + + case FLOAT: + + try { + return NumberFormat.getInstance().parse(string).floatValue(); + } catch (ParseException e) { + return null; + } + + case DOUBLE: + + try { + return NumberFormat.getInstance().parse(string).doubleValue(); + } catch (ParseException e) { + return null; + } + + case DATE: + return new java.sql.Date(parseDate(string).getTime()); + + case TIME: + return new java.sql.Time(parseDate(string).getTime()); + + case TIMESTAMP: + return new java.sql.Timestamp(parseDate(string).getTime()); + } + } + } +} + +// End FileRowConverter.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..6df6171 --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileSchema.java @@ -0,0 +1,67 @@ +/* + * 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.schema.SchemaPlus; +import org.apache.calcite.schema.Table; +import org.apache.calcite.schema.impl.AbstractSchema; + +import com.google.common.collect.ImmutableList; +import com.google.common.collect.ImmutableMap; + +import java.util.List; +import java.util.Map; + +/** + * Schema mapped onto a set of URLs / HTML tables. Each table in the schema + * is an HTML table on a URL. + */ +class FileSchema extends AbstractSchema { + private ImmutableList<Map<String, Object>> tables; + + /** + * Creates an HTML tables schema. + * + * @param parentSchema Parent schema + * @param name Schema name + * @param tables List containing HTML table identifiers + */ + FileSchema(SchemaPlus parentSchema, String name, + List<Map<String, Object>> tables) { + this.tables = ImmutableList.copyOf(tables); + } + + @Override protected Map<String, Table> getTableMap() { + final ImmutableMap.Builder<String, Table> builder = ImmutableMap.builder(); + + for (Map<String, Object> tableDef : this.tables) { + String tableName = (String) tableDef.get("name"); + + try { + FileTable table = new FileTable(tableDef, null); + builder.put(tableName, table); + } catch (Exception e) { + e.printStackTrace(); + System.out.println("Unable to instantiate table for: " + tableName); + } + } + + return builder.build(); + } +} + +// End FileSchema.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..361835f --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileSchemaFactory.java @@ -0,0 +1,47 @@ +/* + * 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.schema.Schema; +import org.apache.calcite.schema.SchemaFactory; +import org.apache.calcite.schema.SchemaPlus; + +import java.util.List; +import java.util.Map; + +/** + * Factory that creates a {@link FileSchema}. + * + * <p>Allows a custom schema to be included in a model.json file. + * See <a href="http://calcite.apache.org/docs/file.html";>File adapter</a>. + */ +@SuppressWarnings("UnusedDeclaration") +public class FileSchemaFactory implements SchemaFactory { + // public constructor, per factory contract + public FileSchemaFactory() { + } + + public Schema create(SchemaPlus parentSchema, String name, + Map<String, Object> operand) { + @SuppressWarnings("unchecked") List<Map<String, Object>> tables = + (List) operand.get("tables"); + + return new FileSchema(parentSchema, name, tables); + } +} + +// End FileSchemaFactory.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileTable.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileTable.java b/file/src/main/java/org/apache/calcite/adapter/file/FileTable.java new file mode 100644 index 0000000..c8d1a4b --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileTable.java @@ -0,0 +1,122 @@ +/* + * 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.adapter.enumerable.EnumerableConvention; +import org.apache.calcite.adapter.enumerable.EnumerableTableScan; +import org.apache.calcite.adapter.java.AbstractQueryableTable; +import org.apache.calcite.adapter.java.JavaTypeFactory; +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Enumerator; +import org.apache.calcite.linq4j.QueryProvider; +import org.apache.calcite.linq4j.Queryable; +import org.apache.calcite.plan.RelOptTable; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.rel.type.RelProtoDataType; +import org.apache.calcite.schema.SchemaPlus; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.schema.TranslatableTable; +import org.apache.calcite.schema.impl.AbstractTableQueryable; + +import java.util.ArrayList; +import java.util.Map; + +/** + * Table implementation wrapping a URL / HTML table. + */ +class FileTable extends AbstractQueryableTable + implements TranslatableTable { + + private final RelProtoDataType protoRowType; + private FileReader reader; + private FileRowConverter converter; + + /** Creates a FileTable. */ + FileTable(Map<String, Object> tableDef, RelProtoDataType protoRowType) + throws Exception { + super(Object[].class); + + this.protoRowType = protoRowType; + @SuppressWarnings("unchecked") ArrayList<Map<String, Object>> fieldConfigs = + (ArrayList<Map<String, Object>>) tableDef.get("fields"); + String url = (String) tableDef.get("url"); + String selector = (String) tableDef.get("selector"); + Integer index = (Integer) tableDef.get("index"); + this.reader = new FileReader(url, selector, index); + this.converter = new FileRowConverter(this.reader, fieldConfigs); + //System.out.println("Created FileTable: " + (String) tableDef.get("name")); + + } + + public String toString() { + return "FileTable"; + } + + public Statistic getStatistic() { + return Statistics.UNKNOWN; + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + if (protoRowType != null) { + return protoRowType.apply(typeFactory); + } + return this.converter.getRowType((JavaTypeFactory) typeFactory); + } + + public <T> Queryable<T> asQueryable(QueryProvider queryProvider, + SchemaPlus schema, String tableName) { + return new AbstractTableQueryable<T>(queryProvider, schema, this, + tableName) { + public Enumerator<T> enumerator() { + try { + FileEnumerator enumerator = + new FileEnumerator(reader.iterator(), converter); + //noinspection unchecked + return (Enumerator<T>) enumerator; + } catch (Exception e) { + throw new RuntimeException(e); + } + } + }; + } + + /** Returns an enumerable over a given projection of the fields. */ + public Enumerable<Object> project(final int[] fields) { + return new AbstractEnumerable<Object>() { + public Enumerator<Object> enumerator() { + try { + return new FileEnumerator(reader.iterator(), converter, fields); + } catch (Exception e) { + throw new RuntimeException(e); + } + } + }; + } + + public RelNode toRel(RelOptTable.ToRelContext context, + RelOptTable relOptTable) { + return new EnumerableTableScan(context.getCluster(), + context.getCluster().traitSetOf(EnumerableConvention.INSTANCE), + relOptTable, (Class) getElementType()); + } +} + +// End FileTable.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/FileTableScan.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/FileTableScan.java b/file/src/main/java/org/apache/calcite/adapter/file/FileTableScan.java new file mode 100644 index 0000000..bdd1d50 --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/FileTableScan.java @@ -0,0 +1,94 @@ +/* + * 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.adapter.enumerable.EnumerableConvention; +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.linq4j.tree.Blocks; +import org.apache.calcite.linq4j.tree.Expressions; +import org.apache.calcite.linq4j.tree.Primitive; +import org.apache.calcite.plan.RelOptCluster; +import org.apache.calcite.plan.RelOptTable; +import org.apache.calcite.plan.RelTraitSet; +import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.RelWriter; +import org.apache.calcite.rel.core.TableScan; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.rel.type.RelDataTypeField; + +import java.util.List; + +/** + * Relational expression representing a scan of an HTML table. + * + * <p>Like any table scan, it serves as a leaf node of a query tree. + * + * <p>Trivially modified from CsvTableScan. + */ +class FileTableScan extends TableScan implements EnumerableRel { + private final FileTable webTable; + private final int[] fields; + + protected FileTableScan(RelOptCluster cluster, RelOptTable table, + FileTable webTable, int[] fields) { + super(cluster, cluster.traitSetOf(EnumerableConvention.INSTANCE), table); + this.webTable = webTable; + this.fields = fields; + + assert webTable != null; + } + + @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { + assert inputs.isEmpty(); + return new FileTableScan(getCluster(), table, webTable, fields); + } + + @Override public RelWriter explainTerms(RelWriter pw) { + return super.explainTerms(pw) + .item("fields", Primitive.asList(fields)); + } + + @Override public RelDataType deriveRowType() { + final List<RelDataTypeField> fieldList = table.getRowType().getFieldList(); + final RelDataTypeFactory.FieldInfoBuilder builder = + getCluster().getTypeFactory().builder(); + for (int field : fields) { + builder.add(fieldList.get(field)); + } + return builder.build(); + } + + public Result implement(EnumerableRelImplementor implementor, Prefer pref) { + PhysType physType = + PhysTypeImpl.of( + implementor.getTypeFactory(), + getRowType(), + pref.preferArray()); + + return implementor.result( + physType, + Blocks.toBlock( + Expressions.call(table.getExpression(FileTable.class), "project", + Expressions.constant(fields)))); + } +} + +// End FileTableScan.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/main/java/org/apache/calcite/adapter/file/package-info.java ---------------------------------------------------------------------- diff --git a/file/src/main/java/org/apache/calcite/adapter/file/package-info.java b/file/src/main/java/org/apache/calcite/adapter/file/package-info.java new file mode 100644 index 0000000..6a4921e --- /dev/null +++ b/file/src/main/java/org/apache/calcite/adapter/file/package-info.java @@ -0,0 +1,27 @@ +/* + * 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. + */ + +/** + * Calcite query provider that reads from web tables (HTML). + * + * <p>A Calcite schema that maps onto multiple URLs / HTML Tables. + * Each HTML table appears as a table. + * Full select SQL operations are available on those tables. + */ +package org.apache.calcite.adapter.file; + +// End package-info.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java ---------------------------------------------------------------------- diff --git a/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java b/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java new file mode 100644 index 0000000..535011d --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/FileReaderTest.java @@ -0,0 +1,185 @@ +/* + * 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.jsoup.select.Elements; + +import org.junit.Assume; +import org.junit.Test; + +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + +import java.util.Iterator; + +/** + * Unit tests for FileReader. + */ + +public class FileReaderTest { + + static final String CITIES_URI = + "http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population";; + + static final String STATES_URI = + "http://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States";; + + /** + * Test FileReader URL instantiation - no path + */ + @Test + public void testFileReaderURLNoPath() throws FileReaderException { + Assume.assumeTrue(FileSuite.hazNetwork()); + FileReader t = new FileReader(STATES_URI); + t.refresh(); + } + + /** + * Test FileReader URL instantiation - with path + */ + @Test + public void testFileReaderURLWithPath() throws FileReaderException { + Assume.assumeTrue(FileSuite.hazNetwork()); + FileReader t = + new FileReader(CITIES_URI, + "#mw-content-text > table.wikitable.sortable", 0); + t.refresh(); + } + + /** + * Test FileReader URL fetch + */ + @Test + public void testFileReaderURLFetch() throws FileReaderException { + Assume.assumeTrue(FileSuite.hazNetwork()); + FileReader t = + new FileReader(STATES_URI, + "#mw-content-text > table.wikitable.sortable", 0); + int i = 0; + for (Elements row : t) { + i++; + } + assertTrue(i == 50); + } + + /** + * Test failed FileReader instantiation - malformed URL + */ + @Test(expected = FileReaderException.class) + public void testFileReaderMalURL() throws FileReaderException { + FileReader t = new FileReader("bad" + CITIES_URI, "table:eq(4)"); + t.refresh(); + } + + /** + * Test failed FileReader instantiation - bad URL + */ + @Test(expected = FileReaderException.class) + public void testFileReaderBadURL() throws FileReaderException { + final String uri = + "http://ex.wikipedia.org/wiki/List_of_United_States_cities_by_population";; + FileReader t = new FileReader(uri, "table:eq(4)"); + t.refresh(); + } + + /** + * Test failed FileReader instantiation - bad selector + */ + @Test(expected = FileReaderException.class) + public void testFileReaderBadSelector() throws FileReaderException { + FileReader t = + new FileReader("file:target/test-classes/tableOK.html", "table:eq(1)"); + t.refresh(); + } + + /** + * Test FileReader with static file - headings + */ + @Test + public void testFileReaderHeadings() throws FileReaderException { + FileReader t = new FileReader("file:target/test-classes/tableOK.html"); + Elements headings = t.getHeadings(); + assertTrue(headings.get(1).text().equals("H1")); + } + + /** + * Test FileReader with static file - data + */ + @Test + public void testFileReaderData() throws FileReaderException { + FileReader t = new FileReader("file:target/test-classes/tableOK.html"); + Iterator<Elements> i = t.iterator(); + Elements row = i.next(); + assertTrue(row.get(2).text().equals("R0C2")); + row = i.next(); + assertTrue(row.get(0).text().equals("R1C0")); + } + + /** + * Test FileReader with bad static file - headings + */ + @Test + public void testFileReaderHeadingsBadFile() throws FileReaderException { + FileReader t = + new FileReader("file:target/test-classes/tableNoTheadTbody.html"); + Elements headings = t.getHeadings(); + assertTrue(headings.get(1).text().equals("H1")); + } + + /** + * Test FileReader with bad static file - data + */ + @Test + public void testFileReaderDataBadFile() throws FileReaderException { + final FileReader t = + new FileReader("file:target/test-classes/tableNoTheadTbody.html"); + Iterator<Elements> i = t.iterator(); + Elements row = i.next(); + assertTrue(row.get(2).text().equals("R0C2")); + row = i.next(); + assertTrue(row.get(0).text().equals("R1C0")); + } + + /** + * Test FileReader with no headings static file - data + */ + @Test + public void testFileReaderDataNoTH() throws FileReaderException { + FileReader t = new FileReader("file:target/test-classes/tableNoTH.html"); + Iterator<Elements> i = + new FileReader("file:target/test-classes/tableNoTH.html").iterator(); + Elements row = i.next(); + assertTrue(row.get(2).text().equals("R0C2")); + } + + /** + * Test FileReader iterator with static file + */ + @Test + public void testFileReaderIterator() throws FileReaderException { + FileReader t = new FileReader("file:target/test-classes/tableOK.html"); + Elements row = null; + for (Elements aT : t) { + row = aT; + } + assertFalse(row == null); + assertTrue(row.get(1).text().equals("R2C1")); + } + +} + +// End FileReaderTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/java/org/apache/calcite/adapter/file/FileSuite.java ---------------------------------------------------------------------- diff --git a/file/src/test/java/org/apache/calcite/adapter/file/FileSuite.java b/file/src/test/java/org/apache/calcite/adapter/file/FileSuite.java new file mode 100644 index 0000000..ffaa791 --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/FileSuite.java @@ -0,0 +1,58 @@ +/* + * 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.junit.runner.RunWith; +import org.junit.runners.Suite; + +import java.io.IOException; + +import java.net.Socket; + +/** + * Unit test suite for Calcite File adapter. + */ +@RunWith(Suite.class) [email protected]({ FileReaderTest.class, SqlTest.class }) +public class FileSuite { + private FileSuite() {} + + private static final String TEST_HOST = "en.wikipedia.org"; + + static boolean hazNetwork() { + Socket socket = null; + boolean reachable = false; + try { + socket = new Socket(FileSuite.TEST_HOST, 80); + reachable = true; + } catch (Exception e) { + // do nothing + } finally { + if (socket != null) { + try { + socket.close(); + } catch (IOException e) { + // do nothing + } + } + } + return reachable; + } + +} + +// End FileSuite.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..60549ed --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/SqlTest.java @@ -0,0 +1,189 @@ +/* + * 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 com.google.common.base.Function; + +import org.junit.Assume; +import org.junit.Test; + +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.Properties; + +import static org.junit.Assert.assertEquals; + +/** + * System test of the Calcite file adapter, which can also read and parse + * HTML tables over HTTP. + */ +public class SqlTest { + // helper functions + + private void checkSql(String model, String sql) throws SQLException { + checkSql(sql, model, new Function<ResultSet, Void>() { + public Void apply(ResultSet resultSet) { + try { + output(resultSet, System.out); + } catch (SQLException e) { + throw new RuntimeException(e); + } + return null; + } + }); + } + + private void checkSql(String model, String sql, final String expected) + throws SQLException { + checkSql(sql, model, new Function<ResultSet, Void>() { + public Void apply(ResultSet resultSet) { + try { + String actual = SqlTest.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 new RuntimeException(e); + } + return null; + } + }); + } + + 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", "target/test-classes/" + model + ".json"); + 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 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 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 + public void testFileSelect() throws SQLException { + checkSql("testModel", "select H1 from T1 where H0 = 'R1C0'", "H1=R1C1\n"); + } + + /** + * Reads from a local file without table headers <TH> and checks the result + */ + @Test + public void testNoTHSelect() throws SQLException { + Assume.assumeTrue(FileSuite.hazNetwork()); + checkSql("testModel", + "select \"col1\" from T1_NO_TH where \"col0\" like 'R0%'", + "col1=R0C1\n"); + } + + /** + * Reads from a local file - finds larger table even without <TH> elements + */ + @Test + public void testFindBiggerNoTH() throws SQLException { + checkSql("testModel", + "select \"col4\" from TABLEX2 where \"col0\" like 'R1%'", + "col4=R1C4\n"); + } + + /** + * Reads from a URL and checks the result + */ + @Test + public void testURLSelect() throws SQLException { + Assume.assumeTrue(FileSuite.hazNetwork()); + final String sql = "select \"State\", \"Statehood\" from \"States\"\n" + + "where \"State\" = 'California'"; + checkSql("wiki", sql, + "State=California; Statehood=1850-09-09\n"); + } + +} + +// End SqlTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/java/org/apache/calcite/adapter/file/package-info.java ---------------------------------------------------------------------- diff --git a/file/src/test/java/org/apache/calcite/adapter/file/package-info.java b/file/src/test/java/org/apache/calcite/adapter/file/package-info.java new file mode 100644 index 0000000..edb702f --- /dev/null +++ b/file/src/test/java/org/apache/calcite/adapter/file/package-info.java @@ -0,0 +1,27 @@ +/* + * 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. + */ + +/** + * Query provider that reads from files and web pages in various formats. + * + * <p>A Calcite schema that maps onto multiple URLs / HTML Tables. Each HTML + * table appears as a table. Full select SQL operations are available on those + * tables. + */ +package org.apache.calcite.adapter.file; + +// End package-info.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/sales.json ---------------------------------------------------------------------- diff --git a/file/src/test/resources/sales.json b/file/src/test/resources/sales.json new file mode 100644 index 0000000..9f8f2ec --- /dev/null +++ b/file/src/test/resources/sales.json @@ -0,0 +1,18 @@ +{ + "version": "1.0", + "defaultSchema": "SALES", + "schemas": [ { + "name": "SALES", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "tables": [ { + "name": "EMPS", + "url": "file:file/src/test/resources/sales/EMPS.html" + }, { + "name": "DEPTS", + "url": "file:file/src/test/resources/sales/DEPTS.html" + } ] + } + } ] +} http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/sales/DEPTS.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/sales/DEPTS.html b/file/src/test/resources/sales/DEPTS.html new file mode 100644 index 0000000..302d443 --- /dev/null +++ b/file/src/test/resources/sales/DEPTS.html @@ -0,0 +1,42 @@ +<!-- +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. +--> +<html> + <body> + <table> + <thead> + <tr> + <th>DEPTNO</th> + <th>NAME</th> + </tr> + </thead> + <tbody> + <tr> + <td>10</td> + <td>Sales</td> + </tr> + <tr> + <td>20</td> + <td>Marketing</td> + </tr> + <tr> + <td>30</td> + <td>Accounts</td> + </tr> + </tbody> + </table> + </body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/sales/EMPS.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/sales/EMPS.html b/file/src/test/resources/sales/EMPS.html new file mode 100644 index 0000000..05b166d --- /dev/null +++ b/file/src/test/resources/sales/EMPS.html @@ -0,0 +1,56 @@ +<!-- +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. +--> +<html> + <body> + <table> + <thead> + <tr> + <th>EMPNO</th> + <th>NAME</th> + <th>DEPTNO</th> + </tr> + </thead> + <tbody> + <tr> + <td>100</td> + <td>Fred</td> + <td>30</td> + </tr> + <tr> + <td>110</td> + <td>Eric</td> + <td>20</td> + </tr> + <tr> + <td>110</td> + <td>John</td> + <td>40</td> + </tr> + <tr> + <td>120</td> + <td>Wilma</td> + <td>20</td> + </tr> + <tr> + <td>130</td> + <td>Alice</td> + <td>40</td> + </tr> + </tbody> + </table> + </body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/tableNoTH.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/tableNoTH.html b/file/src/test/resources/tableNoTH.html new file mode 100644 index 0000000..073ab8e --- /dev/null +++ b/file/src/test/resources/tableNoTH.html @@ -0,0 +1,39 @@ +<html> +<!-- +{% comment %} +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. +{% endcomment %} +--> +<body> +<table> +<tr> +<td>R0C0</td> +<td>R0C1</td> +<td>R0C2</td> +</tr> +<tr> +<td>R1C0</td> +<td>R1C1</td> +<td>R1C2</td> +</tr> +<tr> +<td>R2C0</td> +<td>R2C1</td> +<td>R2C2</td> +</tr> +</table> +</body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/tableNoTheadTbody.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/tableNoTheadTbody.html b/file/src/test/resources/tableNoTheadTbody.html new file mode 100644 index 0000000..9e77d88 --- /dev/null +++ b/file/src/test/resources/tableNoTheadTbody.html @@ -0,0 +1,44 @@ +<html> +<!-- +{% comment %} +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. +{% endcomment %} +--> +<body> +<table> +<tr> +<th>H0</th> +<th>H1</th> +<th>H2</th> +</tr> +<tr> +<td>R0C0</td> +<td>R0C1</td> +<td>R0C2</td> +</tr> +<tr> +<td>R1C0</td> +<td>R1C1</td> +<td>R1C2</td> +</tr> +<tr> +<td>R2C0</td> +<td>R2C1</td> +<td>R2C2</td> +</tr> +</table> +</body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/tableOK.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/tableOK.html b/file/src/test/resources/tableOK.html new file mode 100644 index 0000000..8b46aed --- /dev/null +++ b/file/src/test/resources/tableOK.html @@ -0,0 +1,30 @@ +<html> +<body> +<table> +<thead> +<tr> +<th>H0</th> +<th>H1</th> +<th>H2</th> +</tr> +</thead> +<tbody> +<tr> +<td>R0C0</td> +<td>R0C1</td> +<td>R0C2</td> +</tr> +<tr> +<td>R1C0</td> +<td>R1C1</td> +<td>R1C2</td> +</tr> +<tr> +<td>R2C0</td> +<td>R2C1</td> +<td>R2C2</td> +</tr> +</tbody> +</table> +</body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/tableX2.html ---------------------------------------------------------------------- diff --git a/file/src/test/resources/tableX2.html b/file/src/test/resources/tableX2.html new file mode 100644 index 0000000..d3bf8d6 --- /dev/null +++ b/file/src/test/resources/tableX2.html @@ -0,0 +1,69 @@ +<html> +<!-- +{% comment %} +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. +{% endcomment %} +--> +<body> +<table id="smaller"> +<thead> +<tr> +<th>H0</th> +<th>H1</th> +<th>H2</th> +</tr> +</thead> +<tbody> +<tr> +<td>R0C0</td> +<td>R0C1</td> +<td>R0C2</td> +</tr> +<tr> +<td>R1C0</td> +<td>R1C1</td> +<td>R1C2</td> +</tr> +</tbody> +</table> +<table id="bigger"> +<tr> +<td>R0C0</td> +<td>R0C1</td> +<td>R0C2</td> +<td>R0C3</td> +<td>R0C4</td> +<td>R0C5</td> +</tr> +<tr> +<td>R1C0</td> +<td>R1C1</td> +<td>R1C2</td> +<td>R1C3</td> +<td>R1C4</td> +<td>R1C5</td> +</tr> +<tr> +<td>R2C0</td> +<td>R2C1</td> +<td>R2C2</td> +<td>R2C3</td> +<td>R2C4</td> +<td>R2C5</td> +</tr> +</table> +</body> +</html> http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/testModel.json ---------------------------------------------------------------------- diff --git a/file/src/test/resources/testModel.json b/file/src/test/resources/testModel.json new file mode 100644 index 0000000..f88b661 --- /dev/null +++ b/file/src/test/resources/testModel.json @@ -0,0 +1,21 @@ +{ + "version": "1.0", + "defaultSchema": "TEST", + "schemas": [ { + "name": "TEST", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "tables": [ { + "name": "T1", + "url": "file:target/test-classes/tableOK.html" + }, { + "name": "T1_NO_TH", + "url": "file:target/test-classes/tableNoTH.html" + }, { + "name": "TABLEX2", + "url": "file:target/test-classes/tableX2.html" + } ] + } + } ] +} http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/webjoin.sql ---------------------------------------------------------------------- diff --git a/file/src/test/resources/webjoin.sql b/file/src/test/resources/webjoin.sql new file mode 100644 index 0000000..11a54de --- /dev/null +++ b/file/src/test/resources/webjoin.sql @@ -0,0 +1,25 @@ +# 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. +# +!connect jdbc:calcite:model=target/test-classes/wiki.json admin admin + +values 'What are the largest cities in California?'; +select c."Rank", c."City", c."State", c."Population" "City Population", s."Population" "State Population", (100 * c."Population" / s."Population") "Pct State Population" from "Cities" c, "States" s where c."State" = s."State" and s."State" = 'California'; + +values 'What percentage of California residents live in big cities?'; +select count(*) "City Count", sum(100 * c."Population" / s."Population") "Pct State Population" from "Cities" c, "States" s where c."State" = s."State" and s."State" = 'California'; + +values 'What cities comprise the largest percentage of state population?'; +select c."Rank", c."City", c."State", c."Population" "City Population", s."Population" "State Population", (100 * c."Population" / s."Population") "Pct State Population" from "Cities" c, "States" s where c."State" = s."State" order by "Pct State Population" desc limit 10; http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/file/src/test/resources/wiki.json ---------------------------------------------------------------------- diff --git a/file/src/test/resources/wiki.json b/file/src/test/resources/wiki.json new file mode 100644 index 0000000..36654e9 --- /dev/null +++ b/file/src/test/resources/wiki.json @@ -0,0 +1,93 @@ +{ +/* + * Example calcite-file schema using wikipedia pages for us states and + * cities. + */ + version: "1.0", + defaultSchema: "WIKI", + /* + * Schema Definition + * + * Schemata are defined as a list of tables, each containing, + * minimally a table name and a url. Optionally, table definitions + * may also include a selector and index to specify the desired + * table for those pages with many. Absent a table specification, + * calcite-file will choose the largest table on the page. + * calcite-file uses Jsoup (http://jsoup.org/) for HTML DOM + * navigation and selectors for both tables and fields follow the + * Jsoup selector specification + * (http://jsoup.org/cookbook/extracting-data/selector-syntax). + * + * Also optional is the definition of a list of fields for the table + * which can increase the usability of the table (note the + * difference between RawCities and Cities below). Field + * definitions may be used to rename or skip source fields, to + * select and condition the cell contents and to set a data type. + * + * Parsing Cell Contents + * + * calcite-file includes the capability to select DOM nodes within a + * cell, to replace text within the selected element, to match + * within the selected text, and choose a data type for the + * resulting database column. Processing steps are applied in the + * order described and replace and match patterns are based on Java + * Pattern syntax see: + * (http://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html). + * + */ + "schemas": [ { + "name": "WIKI", + "type": "custom", + "factory": "org.apache.calcite.adapter.file.FileSchemaFactory", + "operand": { + "tables": [ { + "name": "RawCities", + "url": "http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population";, + "selector": "#mw-content-text > table.wikitable.sortable", + "index": 0 + }, { + "name": "Cities", + "url": "http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population";, + "selector": "#mw-content-text > table.wikitable.sortable", + "index": 0, + "fields": [ + // note the use of a pattern match in the following + // matchSeq is optional and defaults to 0 + {"th": "2012 rank", "name": "Rank", "type": "int", "match": "\\d+", "matchSeq": 0}, + {"th": "City", "selector": "a", "selectedElement": 0}, + {"th": "State[5]", "name": "State", "selector": "a:eq(0)"}, + {"th": "2012 estimate", "name": "Population", "type": "double"}, + // NB: we're skipping the following two fields + {"th": "2010 Census", "skip": "true"}, + {"th": "Change", "skip": "true"}, + // here, we're using a selector to navigate the dom inside the table cell + {"th": "2012 land area", "name": "Land Area", "type": "double", "selector": ":not(span)"}, + {"th": "2012 population density", "skip": "true"}, + // eliminate the degree symbol from the location field + // note - it's now possible to create multiple columns from one HTML column + {"th": "Location", "name": "Latitude", "replace": "\u00B0", "replaceWith": " ", + "match": "\\d+\\.\\d+", "type": "double"}, + {"th": "Location", "name": "Longitude", "replace": "\u00B0", "replaceWith": " ", + "match": "\\d+\\.\\d+", "matchSeq": 1, "type": "double"}, + // note that field definitions do not need to be in HTML table order - just makes it easier + {"th": "ANSI", "skip": "true"} + ] + }, { + "name": "States", + "url": "http://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States";, + "selector": "#mw-content-text > table.wikitable.sortable", + "index": 0, + "fields": [ + {"th": "State", "selector": "a", "selectedElement": 0}, + {"th": "Largest city[A][10]", "name": "Largest City"}, + {"th": "Statehood[B]", "name": "Statehood", "type": "Date"}, + {"th": "Population (2013 est)[11]", "name": "Population", "type": "double"}, + {"th": "Total area in mi2 (km2)[C][12]", "name": "Total Area", "type": "double"}, + {"th": "Land area in mi2 (km2)[C][12]", "name": "Land Area", "type": "double"}, + {"th": "Water area in mi2 (km2)[C][12]", "name": "Water Area", + "type": "double"} + ] + } ] + } + } ] +} http://git-wip-us.apache.org/repos/asf/calcite/blob/cf46d3be/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 6f683ac..977b8ea 100644 --- a/pom.xml +++ b/pom.xml @@ -90,6 +90,7 @@ limitations under the License. <jcip-annotations.version>1.0-1</jcip-annotations.version> <jetty.version>9.2.15.v20160210</jetty.version> <jmh.version>1.12</jmh.version> + <jsoup.version>1.10.2</jsoup.version> <junit.version>4.12</junit.version> <maven-checkstyle-plugin.version>2.12.1</maven-checkstyle-plugin.version> <maven-dependency-plugin.version>2.10</maven-dependency-plugin.version> @@ -100,6 +101,7 @@ limitations under the License. <mockito-all.version>1.10.19</mockito-all.version> <mongo-java-driver.version>2.12.3</mongo-java-driver.version> <mysql-driver.version>5.1.20</mysql-driver.version> + <natty.version>0.13</natty.version> <opencsv.version>2.3</opencsv.version> <oracle-jdbc6-driver.version>11.2.0.2.0</oracle-jdbc6-driver.version> <aggdesigner.version>6.0</aggdesigner.version> @@ -133,6 +135,7 @@ limitations under the License. <module>druid</module> <module>elasticsearch</module> <module>example</module> + <module>file</module> <module>linq4j</module> <module>mongodb</module> <module>piglet</module> @@ -173,6 +176,11 @@ limitations under the License. </dependency> <dependency> <groupId>org.apache.calcite</groupId> + <artifactId>calcite-example-csv</artifactId> + <version>${project.version}</version> + </dependency> + <dependency> + <groupId>org.apache.calcite</groupId> <artifactId>calcite-linq4j</artifactId> <version>${project.version}</version> </dependency> @@ -224,6 +232,11 @@ limitations under the License. <version>${guava.version}</version> </dependency> <dependency> + <groupId>com.joestelmach</groupId> + <artifactId>natty</artifactId> + <version>${natty.version}</version> + </dependency> + <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>${oracle-jdbc6-driver.version}</version> @@ -309,6 +322,11 @@ limitations under the License. <version>${httpcore.version}</version> </dependency> <dependency> + <groupId>org.jsoup</groupId> + <artifactId>jsoup</artifactId> + <version>${jsoup.version}</version> + </dependency> + <dependency> <groupId>org.mockito</groupId> <artifactId>mockito-all</artifactId> <version>${mockito-all.version}</version>
