[CALCITE-1896] OS adapter and sqlsh Contains tables du, files, git_commits, ps, stdin; option '-o FORMAT' (FORMAT can be mysql, spaced, headers, json, csv); option '--help'.
Tested on Ubuntu and macOS (the 'ps' and 'files' commands are implemented fairly differently). Added Hook.STANDARD_STREAMS to allow stdin, stdout, stderr to be overridden for the duration of a statement, and for thread-safe testing. Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/d23e5295 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/d23e5295 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/d23e5295 Branch: refs/heads/master Commit: d23e529515ce0eb6e0c150052fe3d2c5d90f1747 Parents: f11f89f Author: Julian Hyde <[email protected]> Authored: Fri Jul 21 19:54:05 2017 -0700 Committer: Julian Hyde <[email protected]> Committed: Thu Jul 27 16:03:52 2017 -0700 ---------------------------------------------------------------------- .../java/org/apache/calcite/DataContext.java | 11 + .../calcite/jdbc/CalciteConnectionImpl.java | 10 +- .../calcite/rel/type/RelDataTypeFactory.java | 15 + .../java/org/apache/calcite/runtime/Hook.java | 4 + .../org/apache/calcite/util/JsonBuilder.java | 2 +- plus/pom.xml | 5 +- .../calcite/adapter/os/DuTableFunction.java | 71 +++ .../calcite/adapter/os/FilesTableFunction.java | 281 ++++++++++++ .../adapter/os/GitCommitsTableFunction.java | 164 +++++++ .../apache/calcite/adapter/os/Processes.java | 206 +++++++++ .../calcite/adapter/os/PsTableFunction.java | 167 +++++++ .../org/apache/calcite/adapter/os/SqlShell.java | 438 +++++++++++++++++++ .../calcite/adapter/os/StdinTableFunction.java | 112 +++++ .../apache/calcite/adapter/os/package-info.java | 27 ++ .../calcite/adapter/os/OsAdapterTest.java | 321 ++++++++++++++ .../java/org/apache/calcite/test/PlusSuite.java | 2 + pom.xml | 1 + site/_docs/adapter.md | 1 + site/_docs/os_adapter.md | 221 ++++++++++ sqlsh | 44 ++ 20 files changed, 2100 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/core/src/main/java/org/apache/calcite/DataContext.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/DataContext.java b/core/src/main/java/org/apache/calcite/DataContext.java index 78642aa..c4cafe6 100644 --- a/core/src/main/java/org/apache/calcite/DataContext.java +++ b/core/src/main/java/org/apache/calcite/DataContext.java @@ -25,6 +25,8 @@ import org.apache.calcite.sql.advise.SqlAdvisor; import com.google.common.base.CaseFormat; +import java.io.InputStream; +import java.io.OutputStream; import java.lang.reflect.Modifier; import java.util.TimeZone; import java.util.concurrent.atomic.AtomicBoolean; @@ -86,6 +88,15 @@ public interface DataContext { /** Advisor that suggests completion hints for SQL statements. */ SQL_ADVISOR("sqlAdvisor", SqlAdvisor.class), + /** Writer to the standard error (stderr). */ + STDERR("stderr", OutputStream.class), + + /** Reader on the standard input (stdin). */ + STDIN("stdin", InputStream.class), + + /** Writer to the standard output (stdout). */ + STDOUT("stdout", OutputStream.class), + /** Time zone in which the current statement is executing. Required; * defaults to the time zone of the JVM if the connection does not specify a * time zone. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java index 2df9253..15becf8 100644 --- a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java +++ b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java @@ -391,11 +391,19 @@ abstract class CalciteConnectionImpl final long localOffset = timeZone.getOffset(time); final long currentOffset = localOffset; + // Give a hook chance to alter standard input, output, error streams. + final Holder<Object[]> streamHolder = + Holder.of(new Object[] {System.in, System.out, System.err}); + Hook.STANDARD_STREAMS.run(streamHolder); + ImmutableMap.Builder<Object, Object> builder = ImmutableMap.builder(); builder.put(Variable.UTC_TIMESTAMP.camelName, time) .put(Variable.CURRENT_TIMESTAMP.camelName, time + currentOffset) .put(Variable.LOCAL_TIMESTAMP.camelName, time + localOffset) - .put(Variable.TIME_ZONE.camelName, timeZone); + .put(Variable.TIME_ZONE.camelName, timeZone) + .put(Variable.STDIN.camelName, streamHolder.get()[0]) + .put(Variable.STDOUT.camelName, streamHolder.get()[1]) + .put(Variable.STDERR.camelName, streamHolder.get()[2]); for (Map.Entry<String, Object> entry : parameters.entrySet()) { Object e = entry.getValue(); if (e == null) { http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java index c7f2539..f5e1b44 100644 --- a/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java +++ b/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeFactory.java @@ -16,8 +16,10 @@ */ package org.apache.calcite.rel.type; +import org.apache.calcite.avatica.util.TimeUnit; import org.apache.calcite.sql.SqlCollation; import org.apache.calcite.sql.SqlIntervalQualifier; +import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.validate.SqlValidatorUtil; @@ -403,6 +405,19 @@ public interface RelDataTypeFactory { } /** + * Adds a field with an interval type. + */ + public FieldInfoBuilder add(String name, TimeUnit startUnit, + int startPrecision, TimeUnit endUnit, + int fractionalSecondPrecision) { + final SqlIntervalQualifier q = + new SqlIntervalQualifier(startUnit, startPrecision, endUnit, + fractionalSecondPrecision, SqlParserPos.ZERO); + add(name, typeFactory.createSqlIntervalType(q)); + return this; + } + + /** * Changes the nullability of the last field added. * * @throws java.lang.IndexOutOfBoundsException if no fields have been http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/core/src/main/java/org/apache/calcite/runtime/Hook.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/runtime/Hook.java b/core/src/main/java/org/apache/calcite/runtime/Hook.java index 2936bcc..0cfbc01 100644 --- a/core/src/main/java/org/apache/calcite/runtime/Hook.java +++ b/core/src/main/java/org/apache/calcite/runtime/Hook.java @@ -35,6 +35,10 @@ public enum Hook { * in tests. */ CURRENT_TIME, + /** Called to get stdin, stdout, stderr. + * Use this to re-assign streams in tests. */ + STANDARD_STREAMS, + /** Returns a boolean value, whether RelBuilder should simplify expressions. * Default true. */ REL_BUILDER_SIMPLIFY, http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/core/src/main/java/org/apache/calcite/util/JsonBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/util/JsonBuilder.java b/core/src/main/java/org/apache/calcite/util/JsonBuilder.java index b4acecb..6f42300 100644 --- a/core/src/main/java/org/apache/calcite/util/JsonBuilder.java +++ b/core/src/main/java/org/apache/calcite/util/JsonBuilder.java @@ -78,7 +78,7 @@ public class JsonBuilder { /** * Appends a JSON object to a string builder. */ - private void append(StringBuilder buf, int indent, Object o) { + public void append(StringBuilder buf, int indent, Object o) { if (o == null) { buf.append("null"); } else if (o instanceof Map) { http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/pom.xml ---------------------------------------------------------------------- diff --git a/plus/pom.xml b/plus/pom.xml index abafbf4..2938cae 100644 --- a/plus/pom.xml +++ b/plus/pom.xml @@ -51,7 +51,10 @@ limitations under the License. <groupId>org.apache.calcite</groupId> <artifactId>calcite-linq4j</artifactId> </dependency> - + <dependency> + <groupId>org.apache.calcite.avatica</groupId> + <artifactId>avatica-core</artifactId> + </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java new file mode 100644 index 0000000..bf4a36c --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/DuTableFunction.java @@ -0,0 +1,71 @@ +/* + * 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.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.function.Function1; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; + +/** + * Table function that executes the OS "du" ("disk usage") command + * to compute file sizes. + */ +public class DuTableFunction { + private DuTableFunction() {} + + public static ScannableTable eval(boolean b) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + return Processes.processLines("du", "-ak") + .select(new Function1<String, Object[]>() { + public Object[] apply(String a0) { + final String[] fields = a0.split("\t"); + return new Object[] {Long.valueOf(fields[0]), fields[1]}; + } + }); + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("size_k", SqlTypeName.BIGINT) + .add("path", SqlTypeName.VARCHAR) + .build(); + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } + +} + +// End DuTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java new file mode 100644 index 0000000..2923a0f --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/FilesTableFunction.java @@ -0,0 +1,281 @@ +/* + * 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.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Enumerator; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Util; + +import com.google.common.collect.ImmutableList; + +import java.math.BigDecimal; +import java.util.Arrays; +import java.util.List; + +/** + * Table function that executes the OS "find" command to find files under a + * particular path. + */ +public class FilesTableFunction { + + private static final BigDecimal THOUSAND = BigDecimal.valueOf(1000L); + + private FilesTableFunction() {} + + /** Evaluates the function. + * + * @param path Directory in which to start the search. Typically '.' + * @return Table that can be inspected, planned, and evaluated + */ + public static ScannableTable eval(final String path) { + return new ScannableTable() { + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("access_time", SqlTypeName.TIMESTAMP) // %A@ sec since epoch + .add("block_count", SqlTypeName.INTEGER) // %b in 512B blocks + .add("change_time", SqlTypeName.TIMESTAMP) // %C@ sec since epoch + .add("depth", SqlTypeName.INTEGER) // %d depth in directory tree + .add("device", SqlTypeName.INTEGER) // %D device number + .add("file_name", SqlTypeName.VARCHAR) // %f file name, sans dirs + .add("fstype", SqlTypeName.VARCHAR) // %F file system type + .add("gname", SqlTypeName.VARCHAR) // %g group name + .add("gid", SqlTypeName.INTEGER) // %G numeric group id + .add("dir_name", SqlTypeName.VARCHAR) // %h leading dirs + .add("inode", SqlTypeName.BIGINT) // %i inode number + .add("link", SqlTypeName.VARCHAR) // %l object of sym link + .add("perm", SqlTypeName.CHAR, 4) // %#m permission octal + .add("hard", SqlTypeName.INTEGER) // %n number of hard links + .add("path", SqlTypeName.VARCHAR) // %P file's name + .add("size", SqlTypeName.BIGINT) // %s file's size in bytes + .add("sparseness", SqlTypeName.FLOAT) // %S sparseness + .add("mod_time", SqlTypeName.TIMESTAMP) // %T@ seconds since epoch + .add("user", SqlTypeName.VARCHAR) // %u user name + .add("uid", SqlTypeName.INTEGER) // %U numeric user id + .add("type", SqlTypeName.CHAR, 1) // %Y file type + .build(); + + // Fields in Linux find that are currently ignored: + // %y file type (not following sym links) + // %k block count in 1KB blocks + // %p file name (including argument) + } + + private Enumerable<String> sourceLinux() { + final String[] args = { + "find", path, "-printf", "" + + "%A@\\0" // access_time + + "%b\\0" // block_count + + "%C@\\0" // change_time + + "%d\\0" // depth + + "%D\\0" // device + + "%f\\0" // file_name + + "%F\\0" // fstype + + "%g\\0" // gname + + "%G\\0" // gid + + "%h\\0" // dir_name + + "%i\\0" // inode + + "%l\\0" // link + + "%#m\\0" // perm + + "%n\\0" // hard + + "%P\\0" // path + + "%s\\0" // size + + "%S\\0" // sparseness + + "%T@\\0" // mod_time + + "%u\\0" // user + + "%U\\0" // uid + + "%Y\\0" // type + }; + return Processes.processLines('\0', args); + } + + private Enumerable<String> sourceMacOs() { + if (path.contains("'")) { + // no injection monkey business + throw new IllegalArgumentException(); + } + final String[] args = {"/bin/sh", "-c", "find '" + path + + "' | xargs stat -f " + + "%a%n" // access_time + + "%b%n" // block_count + + "%c%n" // change_time + + "0%n" // depth: not supported by macOS stat + + "%Hd%n" // device: we only use the high part of "H,L" device + + "filename%n" // filename: not supported by macOS stat + + "fstype%n" // fstype: not supported by macOS stat + + "%Sg%n" // gname + + "%g%n" // gid + + "dir_name%n" // dir_name: not supported by macOS stat + + "%i%n" // inode + + "%Y%n" // link + + "%Lp%n" // perm + + "%l%n" // hard + + "%SN%n" // path + + "%z%n" // size + + "0%n" // sparseness: not supported by macOS stat + + "%m%n" // mod_time + + "%Su%n" // user + + "%u%n" // uid + + "%LT%n" // type + }; + return Processes.processLines('\n', args); + } + + public Enumerable<Object[]> scan(DataContext root) { + final RelDataType rowType = getRowType(root.getTypeFactory()); + final List<String> fieldNames = + ImmutableList.copyOf(rowType.getFieldNames()); + final String osName = System.getProperty("os.name"); + final String osVersion = System.getProperty("os.version"); + Util.discard(osVersion); + final Enumerable<String> enumerable; + switch (osName) { + case "Mac OS X": // tested on version 10.12.5 + enumerable = sourceMacOs(); + break; + default: + enumerable = sourceLinux(); + } + return new AbstractEnumerable<Object[]>() { + public Enumerator<Object[]> enumerator() { + final Enumerator<String> e = enumerable.enumerator(); + return new Enumerator<Object[]>() { + Object[] current; + + public Object[] current() { + return current; + } + + public boolean moveNext() { + current = new Object[fieldNames.size()]; + for (int i = 0; i < current.length; i++) { + if (!e.moveNext()) { + return false; + } + final String v = e.current(); + try { + current[i] = field(fieldNames.get(i), v); + } catch (RuntimeException e) { + throw new RuntimeException("while parsing value [" + + v + "] of field [" + fieldNames.get(i) + + "] in line [" + Arrays.toString(current) + "]", e); + } + } + switch (osName) { + case "Mac OS X": + // Strip leading "./" + String path = (String) current[14]; + if (path.equals(".")) { + current[14] = path = ""; + current[3] = 0; // depth + } else if (path.startsWith("./")) { + current[14] = path = path.substring(2); + current[3] = count(path, '/') + 1; // depth + } else { + current[3] = count(path, '/'); // depth + } + final int slash = path.lastIndexOf('/'); + if (slash >= 0) { + current[5] = path.substring(slash + 1); // filename + current[9] = path.substring(0, slash); // dir_name + } else { + current[5] = path; // filename + current[9] = ""; // dir_name + } + + // In Linux, sparseness = (BLOCKSIZE * st_blocks / st_size) + final Integer blocks = (Integer) current[1]; + final Long bytes = (Long) current[15]; + current[16] = blocks.floatValue() * 512f / bytes.floatValue(); + + // Make type values more like those on Linux + final String type = (String) current[20]; + current[20] = type.equals("/") ? "d" + : type.equals("") || type.equals("*") ? "f" + : type.equals("@") ? "l" + : type; + } + return true; + } + + private int count(String s, char c) { + int n = 0; + for (int i = 0, len = s.length(); i < len; i++) { + if (s.charAt(i) == c) { + ++n; + } + } + return n; + } + + public void reset() { + throw new UnsupportedOperationException(); + } + + public void close() { + e.close(); + } + + private Object field(String field, String value) { + switch (field) { + case "block_count": + case "depth": + case "device": + case "gid": + case "uid": + case "hard": + return Integer.valueOf(value); + case "inode": + case "size": + return Long.valueOf(value); + case "access_time": + case "change_time": + case "mod_time": + return new BigDecimal(value).multiply(THOUSAND).longValue(); + case "sparseness": + return Float.valueOf(value); + default: + return value; + } + } + }; + } + }; + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } + +} + +// End FilesTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java new file mode 100644 index 0000000..fd14a44 --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/GitCommitsTableFunction.java @@ -0,0 +1,164 @@ +/* + * 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.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Enumerator; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; + +import java.util.NoSuchElementException; + +/** + * Table function that executes the OS "git log" command + * to discover git commits. + */ +public class GitCommitsTableFunction { + + /** An example of the timestamp + offset at the end of author and committer + * fields. */ + private static final String TS_OFF = "1500769547 -0700"; + + /** An example of the offset at the end of author and committer fields. */ + private static final String OFF = "-0700"; + + private GitCommitsTableFunction() {} + + public static ScannableTable eval(boolean b) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + final Enumerable<String> enumerable = + Processes.processLines("git", "log", "--pretty=raw"); + return new AbstractEnumerable<Object[]>() { + public Enumerator<Object[]> enumerator() { + final Enumerator<String> e = enumerable.enumerator(); + return new Enumerator<Object[]>() { + private Object[] objects; + private final StringBuilder b = new StringBuilder(); + + public Object[] current() { + if (objects == null) { + throw new NoSuchElementException(); + } + return objects; + } + + public boolean moveNext() { + if (!e.moveNext()) { + objects = null; + return false; + } + objects = new Object[9]; + for (;;) { + final String line = e.current(); + if (line.length() == 0) { + break; // next line will be start of comments + } + if (line.startsWith("commit ")) { + objects[0] = line.substring("commit ".length()); + } else if (line.startsWith("tree ")) { + objects[1] = line.substring("tree ".length()); + } else if (line.startsWith("parent ")) { + if (objects[2] == null) { + objects[2] = line.substring("parent ".length()); + } else { + objects[3] = line.substring("parent ".length()); + } + } else if (line.startsWith("author ")) { + objects[4] = line.substring("author ".length(), + line.length() - TS_OFF.length() - 1); + objects[5] = Long.valueOf( + line.substring(line.length() - TS_OFF.length(), + line.length() - OFF.length() - 1)) * 1000; + } else if (line.startsWith("committer ")) { + objects[6] = line.substring("committer ".length(), + line.length() - TS_OFF.length() - 1); + objects[7] = Long.valueOf( + line.substring(line.length() - TS_OFF.length(), + line.length() - OFF.length() - 1)) * 1000; + } + if (!e.moveNext()) { + // We have a row, and it's the last because input is empty + return true; + } + } + for (;;) { + if (!e.moveNext()) { + // We have a row, and it's the last because input is empty + objects[8] = b.toString(); + b.setLength(0); + return true; + } + final String line = e.current(); + if (line.length() == 0) { + // We're seeing the empty line at the end of message + objects[8] = b.toString(); + b.setLength(0); + return true; + } + b.append(line.substring(" ".length())).append("\n"); + } + } + + public void reset() { + throw new UnsupportedOperationException(); + } + + public void close() { + e.close(); + } + }; + } + }; + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("commit", SqlTypeName.CHAR, 40) + .add("tree", SqlTypeName.CHAR, 40) + .add("parent", SqlTypeName.CHAR, 40) + .add("parent2", SqlTypeName.CHAR, 40) + .add("author", SqlTypeName.VARCHAR) + .add("author_timestamp", SqlTypeName.TIMESTAMP) + .add("committer", SqlTypeName.VARCHAR) + .add("commit_timestamp", SqlTypeName.TIMESTAMP) + .add("message", SqlTypeName.VARCHAR) + .build(); + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(0))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } +} + +// End GitCommitsTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/Processes.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/Processes.java b/plus/src/main/java/org/apache/calcite/adapter/os/Processes.java new file mode 100644 index 0000000..19766a8 --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/Processes.java @@ -0,0 +1,206 @@ +/* + * 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.os; + +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Enumerator; + +import com.google.common.base.Supplier; + +import java.io.BufferedInputStream; +import java.io.BufferedReader; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.nio.charset.StandardCharsets; +import java.util.Arrays; + +/** + * Utilities regarding operating system processes. + * + * <p>WARNING: Spawning processes is not secure. + * Use this class caution. + * This class is in the "plus" module because "plus" is not used by default. + * Do not move this class to the "core" module. + */ +public class Processes { + private Processes() {} + + /** Executes a command and returns its result as an enumerable of lines. */ + static Enumerable<String> processLines(String... args) { + return processLines(' ', args); + } + + /** Executes a command and returns its result as an enumerable of lines. */ + static Enumerable<String> processLines(char sep, String... args) { + return processLines(sep, processSupplier(args)); + } + + /** Executes a command and returns its result as an enumerable of lines. + * + * @param sep Separator character + * @param processSupplier Command and its arguments + */ + private static Enumerable<String> processLines(char sep, + Supplier<Process> processSupplier) { + if (sep != ' ') { + return new SeparatedLinesEnumerable(processSupplier, sep); + } else { + return new ProcessLinesEnumerator(processSupplier); + } + } + + private static Supplier<Process> processSupplier(final String... args) { + return new ProcessFactory(args); + } + + /** Enumerator that executes a process and returns each line as an element. */ + private static class ProcessLinesEnumerator + extends AbstractEnumerable<String> { + private Supplier<Process> processSupplier; + + ProcessLinesEnumerator(Supplier<Process> processSupplier) { + this.processSupplier = processSupplier; + } + + public Enumerator<String> enumerator() { + final Process process = processSupplier.get(); + final InputStream is = process.getInputStream(); + final BufferedInputStream bis = + new BufferedInputStream(is); + final InputStreamReader isr = + new InputStreamReader(bis, StandardCharsets.UTF_8); + final BufferedReader br = new BufferedReader(isr); + return new Enumerator<String>() { + private String line; + + public String current() { + return line; + } + + public boolean moveNext() { + try { + line = br.readLine(); + return line != null; + } catch (IOException e) { + throw new RuntimeException(e); + } + } + + public void reset() { + throw new UnsupportedOperationException(); + } + + public void close() { + try { + br.close(); + } catch (IOException e) { + throw new RuntimeException("while running " + processSupplier, e); + } + process.destroy(); + } + }; + } + } + + /** Enumerator that executes a process and returns each line as an element. */ + private static class SeparatedLinesEnumerable + extends AbstractEnumerable<String> { + private final Supplier<Process> processSupplier; + private final int sep; + + SeparatedLinesEnumerable(Supplier<Process> processSupplier, char sep) { + this.processSupplier = processSupplier; + this.sep = sep; + } + + public Enumerator<String> enumerator() { + final Process process = processSupplier.get(); + final InputStream is = process.getInputStream(); + final BufferedInputStream bis = + new BufferedInputStream(is); + final InputStreamReader isr = + new InputStreamReader(bis, StandardCharsets.UTF_8); + final BufferedReader br = new BufferedReader(isr); + return new Enumerator<String>() { + private final StringBuilder b = new StringBuilder(); + private String line; + + public String current() { + return line; + } + + public boolean moveNext() { + try { + for (;;) { + int c = br.read(); + if (c < 0) { + return false; + } + if (c == sep) { + line = b.toString(); + b.setLength(0); + return true; + } + b.append((char) c); + } + } catch (IOException e) { + throw new RuntimeException(e); + } + } + + public void reset() { + throw new UnsupportedOperationException(); + } + + public void close() { + try { + br.close(); + } catch (IOException e) { + throw new RuntimeException("while running " + processSupplier, e); + } + process.destroy(); + } + }; + } + } + + /** Creates processes. */ + private static class ProcessFactory implements Supplier<Process> { + private final String[] args; + + public ProcessFactory(String... args) { + this.args = args; + } + + public Process get() { + try { + return new ProcessBuilder().command(args).start(); + } catch (IOException e) { + throw new RuntimeException("while creating process: " + + Arrays.toString(args), e); + } + } + + @Override public String toString() { + return args[0]; + } + } +} + +// End Processes.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java new file mode 100644 index 0000000..217a4fa --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/PsTableFunction.java @@ -0,0 +1,167 @@ +/* + * 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.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.avatica.util.TimeUnit; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.function.Function1; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; +import org.apache.calcite.util.Util; + +import com.google.common.collect.ImmutableList; + +import java.util.List; +import java.util.regex.Matcher; +import java.util.regex.Pattern; + +/** + * Table function that executes the OS "ps" command + * to list processes. + */ +public class PsTableFunction { + private static final Pattern MINUTE_SECOND_MILLIS_PATTERN = + Pattern.compile("([0-9]+):([0-9]+):([0-9]+)"); + private static final Pattern HOUR_MINUTE_SECOND_PATTERN = + Pattern.compile("([0-9]+):([0-9]+)\\.([0-9]+)"); + + private PsTableFunction() {} + + public static ScannableTable eval(boolean b) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + final RelDataType rowType = getRowType(root.getTypeFactory()); + final List<String> fieldNames = + ImmutableList.copyOf(rowType.getFieldNames()); + final String[] args; + final String osName = System.getProperty("os.name"); + final String osVersion = System.getProperty("os.version"); + Util.discard(osVersion); + switch (osName) { + case "Mac OS X": // tested on version 10.12.5 + args = new String[]{"ps", "ax", "-o", "ppid=,pid=,pgid=,tpgid=,stat=," + + "user=,pcpu=,pmem=,vsz=,rss=,tty=,start=,time=,uid=,ruid=," + + "sess=,comm="}; + break; + default: + args = new String[]{"ps", "--no-headers", "axo", "ppid,pid,pgrp," + + "tpgid,stat,user,pcpu,pmem,vsz,rss,tty,start_time,time,euid," + + "ruid,sess,comm"}; + } + return Processes.processLines(args) + .select( + new Function1<String, Object[]>() { + public Object[] apply(String line) { + final String[] fields = line.trim().split(" +"); + final Object[] values = new Object[fieldNames.size()]; + for (int i = 0; i < values.length; i++) { + try { + values[i] = field(fieldNames.get(i), fields[i]); + } catch (RuntimeException e) { + throw new RuntimeException("while parsing value [" + + fields[i] + "] of field [" + fieldNames.get(i) + + "] in line [" + line + "]"); + } + } + return values; + } + + private Object field(String field, String value) { + switch (field) { + case "pid": + case "ppid": + case "pgrp": // linux only; macOS equivalent is "pgid" + case "pgid": // see "pgrp" + case "tpgid": + return Integer.valueOf(value); + case "pcpu": + case "pmem": + return (int) (Float.valueOf(value) * 10f); + case "time": + final Matcher m1 = + MINUTE_SECOND_MILLIS_PATTERN.matcher(value); + if (m1.matches()) { + final long h = Long.parseLong(m1.group(1)); + final long m = Long.parseLong(m1.group(2)); + final long s = Long.parseLong(m1.group(3)); + return h * 3600000L + m * 60000L + s * 1000L; + } + final Matcher m2 = + HOUR_MINUTE_SECOND_PATTERN.matcher(value); + if (m2.matches()) { + final long m = Long.parseLong(m2.group(1)); + final long s = Long.parseLong(m2.group(2)); + String g3 = m2.group(3); + while (g3.length() < 3) { + g3 = g3 + "0"; + } + final long millis = Long.parseLong(g3); + return m * 60000L + s * 1000L + millis; + } + return 0L; + case "start_time": // linux only; macOS version is "lstart" + case "lstart": // see "start_time" + case "euid": // linux only; macOS equivalent is "uid" + case "uid": // see "euid" + default: + return value; + } + } + }); + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("pid", SqlTypeName.INTEGER) + .add("ppid", SqlTypeName.INTEGER) + .add("pgrp", SqlTypeName.INTEGER) + .add("tpgid", SqlTypeName.INTEGER) + .add("stat", SqlTypeName.VARCHAR) + .add("user", SqlTypeName.VARCHAR) + .add("pcpu", SqlTypeName.DECIMAL, 3, 1) + .add("pmem", SqlTypeName.DECIMAL, 3, 1) + .add("vsz", SqlTypeName.INTEGER) + .add("rss", SqlTypeName.INTEGER) + .add("tty", SqlTypeName.VARCHAR) + .add("start_time", SqlTypeName.VARCHAR) + .add("time", TimeUnit.HOUR, -1, TimeUnit.SECOND, 0) + .add("euid", SqlTypeName.VARCHAR) + .add("ruid", SqlTypeName.VARCHAR) + .add("sess", SqlTypeName.VARCHAR) + .add("command", SqlTypeName.VARCHAR) + .build(); + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } +} + +// End PsTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java b/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java new file mode 100644 index 0000000..8ec69dd --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/SqlShell.java @@ -0,0 +1,438 @@ +/* + * 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.os; + +import org.apache.calcite.linq4j.Enumerator; +import org.apache.calcite.linq4j.Linq4j; +import org.apache.calcite.util.JsonBuilder; + +import com.google.common.base.Function; +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; +import com.google.common.collect.Maps; + +import java.io.InputStreamReader; +import java.io.OutputStreamWriter; +import java.io.PrintWriter; +import java.nio.charset.StandardCharsets; +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.sql.Types; +import java.util.ArrayList; +import java.util.LinkedHashMap; +import java.util.List; +import java.util.Locale; +import java.util.Map; +import java.util.Set; + +/** + * Command that executes its arguments as a SQL query + * against Calcite's OS adapter. + */ +public class SqlShell { + static final String MODEL = model(); + + private final List<String> args; + private final InputStreamReader in; + private final PrintWriter out; + private final PrintWriter err; + + SqlShell(InputStreamReader in, PrintWriter out, + PrintWriter err, String... args) { + this.args = ImmutableList.copyOf(args); + this.in = Preconditions.checkNotNull(in); + this.out = Preconditions.checkNotNull(out); + this.err = Preconditions.checkNotNull(err); + } + + private static String model() { + final StringBuilder b = new StringBuilder(); + b.append("{\n") + .append(" version: '1.0',\n") + .append(" defaultSchema: 'os',\n") + .append(" schemas: [\n") + .append(" {\n") + .append(" \"name\": \"os\",\n") + .append(" \"tables\": [ {\n"); + addView(b, "du", "select *, \"size_k\" * 1024 as \"size_b\"\n" + + "from table(\"du\"(true))"); + addView(b, "files", "select * from table(\"files\"('.'))"); + addView(b, "git_commits", "select * from table(\"git_commits\"(true))"); + addView(b, "ps", "select * from table(\"ps\"(true))"); + addView(b, "stdin", "select * from table(\"stdin\"(true))"); + b.append(" } ],\n") + .append(" functions: [ {\n"); + addFunction(b, "du", DuTableFunction.class); + addFunction(b, "files", FilesTableFunction.class); + addFunction(b, "git_commits", GitCommitsTableFunction.class); + addFunction(b, "ps", PsTableFunction.class); + addFunction(b, "stdin", StdinTableFunction.class); + b.append(" } ]\n") + .append(" }\n") + .append(" ]\n") + .append("}"); + return b.toString(); + } + + /** Main entry point. */ + public static void main(String[] args) { + try (PrintWriter err = + new PrintWriter( + new OutputStreamWriter(System.err, StandardCharsets.UTF_8)); + InputStreamReader in = + new InputStreamReader(System.in, StandardCharsets.UTF_8); + final PrintWriter out = + new PrintWriter( + new OutputStreamWriter(System.out, StandardCharsets.UTF_8))) { + new SqlShell(in, out, err, args).run(); + } catch (Throwable e) { + e.printStackTrace(); + } + } + + void run() throws SQLException { + final String url = "jdbc:calcite:lex=JAVA;conformance=LENIENT" + + ";model=inline:" + MODEL; + final String help = "Usage: sqlsh [OPTION]... SQL\n" + + "Execute a SQL command\n" + + "\n" + + "Options:\n" + + " -o FORMAT Print output in FORMAT; options are 'spaced' (the " + + "default), 'csv',\n" + + " 'headers', 'json', 'mysql'\n" + + " -h --help Print this help"; + try (Connection connection = DriverManager.getConnection(url); + Statement s = connection.createStatement()) { + final StringBuilder b = new StringBuilder(); + final Enumerator<String> args = + Linq4j.asEnumerable(this.args).enumerator(); + Format format = Format.SPACED; + while (args.moveNext()) { + if (args.current().equals("-o")) { + if (args.moveNext()) { + String formatString = args.current(); + try { + format = Format.valueOf(formatString.toUpperCase(Locale.ROOT)); + } catch (IllegalArgumentException e) { + throw new RuntimeException("unknown format: " + formatString); + } + } else { + throw new RuntimeException("missing format"); + } + } else if (args.current().equals("-h") + || args.current().equals("--help")) { + out.println(help); + return; + } else { + if (b.length() > 0) { + b.append(' '); + } + b.append(args.current()); + } + } + final ResultSet r = s.executeQuery(b.toString()); + format.output(out, r); + r.close(); + } finally { + out.flush(); + } + } + + + private static void addView(StringBuilder b, String name, String sql) { + if (!name.equals("du")) { // we know that "du" is the first + b.append("}, {\n"); + } + b.append(" \"name\": \"") + .append(name) + .append("\",\n") + .append(" \"type\": \"view\",\n") + .append(" \"sql\": \"") + .append(sql.replaceAll("\"", "\\\\\"") + .replaceAll("\n", "")) + .append("\"\n"); + } + + private static void addFunction(StringBuilder b, String name, Class c) { + if (!name.equals("du")) { // we know that "du" is the first + b.append("}, {\n"); + } + b.append(" \"name\": \"") + .append(name) + .append("\",\n") + .append(" \"className\": \"") + .append(c.getName()) + .append("\"\n"); + } + + /** Output format. */ + enum Format { + SPACED { + protected void output(PrintWriter out, ResultSet r) throws SQLException { + final int n = r.getMetaData().getColumnCount(); + final StringBuilder b = new StringBuilder(); + while (r.next()) { + for (int i = 0; i < n; i++) { + if (i > 0) { + b.append(' '); + } + b.append(r.getString(i + 1)); + } + out.println(b); + b.setLength(0); + } + } + }, + HEADERS { + protected void output(PrintWriter out, ResultSet r) throws SQLException { + final ResultSetMetaData m = r.getMetaData(); + final int n = m.getColumnCount(); + final StringBuilder b = new StringBuilder(); + for (int i = 0; i < n; i++) { + if (i > 0) { + b.append(' '); + } + b.append(m.getColumnLabel(i + 1)); + } + out.println(b); + b.setLength(0); + SPACED.output(out, r); + } + }, + CSV { + protected void output(PrintWriter out, ResultSet r) throws SQLException { + // We aim to comply with https://tools.ietf.org/html/rfc4180. + // It's a bug if we don't. + final ResultSetMetaData m = r.getMetaData(); + final int n = m.getColumnCount(); + final StringBuilder b = new StringBuilder(); + for (int i = 0; i < n; i++) { + if (i > 0) { + b.append(','); + } + value(b, m.getColumnLabel(i + 1)); + } + out.print(b); + b.setLength(0); + while (r.next()) { + out.println(); + for (int i = 0; i < n; i++) { + if (i > 0) { + b.append(','); + } + value(b, r.getString(i + 1)); + } + out.print(b); + b.setLength(0); + } + } + + private void value(StringBuilder b, String s) { + if (s == null) { + // do nothing - unfortunately same as empty string + } else if (s.contains("\"")) { + b.append('"') + .append(s.replaceAll("\"", "\"\"")) + .append('"'); + } else if (s.indexOf(',') >= 0 + || s.indexOf('\n') >= 0 + || s.indexOf('\r') >= 0) { + b.append('"').append(s).append('"'); + } else { + b.append(s); + } + } + }, + JSON { + protected void output(PrintWriter out, final ResultSet r) + throws SQLException { + final ResultSetMetaData m = r.getMetaData(); + final int n = m.getColumnCount(); + final Map<String, Integer> fieldOrdinals = new LinkedHashMap<>(); + for (int i = 0; i < n; i++) { + fieldOrdinals.put(m.getColumnLabel(i + 1), + fieldOrdinals.size() + 1); + } + final Set<String> fields = fieldOrdinals.keySet(); + final JsonBuilder json = new JsonBuilder(); + final StringBuilder b = new StringBuilder(); + out.println("["); + int i = 0; + while (r.next()) { + if (i++ > 0) { + out.println(","); + } + json.append(b, 0, + Maps.asMap(fields, new Function<String, Object>() { + public Object apply(String columnLabel) { + try { + final int i = fieldOrdinals.get(columnLabel); + switch (m.getColumnType(i)) { + case Types.BOOLEAN: + final boolean b = r.getBoolean(i); + return !b && r.wasNull() ? null : b; + case Types.DECIMAL: + case Types.FLOAT: + case Types.REAL: + case Types.DOUBLE: + final double d = r.getDouble(i); + return d == 0D && r.wasNull() ? null : d; + case Types.BIGINT: + case Types.INTEGER: + case Types.SMALLINT: + case Types.TINYINT: + final long v = r.getLong(i); + return v == 0L && r.wasNull() ? null : v; + default: + return r.getString(i); + } + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + })); + out.append(b); + b.setLength(0); + } + if (i > 0) { + out.println(); + } + out.println("]"); + } + }, + MYSQL { + protected void output(PrintWriter out, final ResultSet r) + throws SQLException { + // E.g. + // +-------+--------+ + // | EMPNO | ENAME | + // +-------+--------+ + // | 7369 | SMITH | + // | 822 | LEE | + // +-------+--------+ + + final ResultSetMetaData m = r.getMetaData(); + final int n = m.getColumnCount(); + final List<String> values = new ArrayList<>(); + final int[] lengths = new int[n]; + final boolean[] rights = new boolean[n]; + for (int i = 0; i < n; i++) { + final String v = m.getColumnLabel(i + 1); + values.add(v); + lengths[i] = v.length(); + switch (m.getColumnType(i + 1)) { + case Types.BIGINT: + case Types.INTEGER: + case Types.SMALLINT: + case Types.TINYINT: + case Types.REAL: + case Types.FLOAT: + case Types.DOUBLE: + rights[i] = true; + } + } + while (r.next()) { + for (int i = 0; i < n; i++) { + final String v = r.getString(i + 1); + values.add(v); + if (v != null && v.length() > lengths[i]) { + lengths[i] = v.length(); + } + } + } + + final StringBuilder b = new StringBuilder("+"); + for (int length : lengths) { + pad(b, length + 2, '-'); + b.append('+'); + } + final String bar = b.toString(); + out.println(bar); + b.setLength(0); + + for (int i = 0; i < n; i++) { + if (i == 0) { + b.append('|'); + } + b.append(' '); + value(b, values.get(i), lengths[i], rights[i]); + b.append(" |"); + } + out.println(b); + b.setLength(0); + out.print(bar); + + for (int h = n; h < values.size(); h++) { + final int i = h % n; + if (i == 0) { + out.println(b); + b.setLength(0); + b.append('|'); + } + b.append(' '); + value(b, values.get(h), lengths[i], rights[i]); + b.append(" |"); + } + out.println(b); + out.println(bar); + + int rowCount = (values.size() / n) - 1; + if (rowCount == 1) { + out.println("(1 row)"); + } else { + out.print("("); + out.print(rowCount); + out.println(" rows)"); + } + out.println(); + } + + private void value(StringBuilder b, String value, int length, + boolean right) { + if (value == null) { + pad(b, length, ' '); + } else { + final int pad = length - value.length(); + if (pad == 0) { + b.append(value); + } else if (right) { + pad(b, pad, ' '); + b.append(value); + } else { + b.append(value); + pad(b, pad, ' '); + } + } + } + + private void pad(StringBuilder b, int pad, char c) { + for (int j = 0; j < pad; j++) { + b.append(c); + } + } + }; + + protected abstract void output(PrintWriter out, ResultSet r) + throws SQLException; + } +} + +// End SqlShell.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java b/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java new file mode 100644 index 0000000..17cfc12 --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/StdinTableFunction.java @@ -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. + */ +package org.apache.calcite.adapter.os; + +import org.apache.calcite.DataContext; +import org.apache.calcite.linq4j.AbstractEnumerable; +import org.apache.calcite.linq4j.Enumerable; +import org.apache.calcite.linq4j.Enumerator; +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.rel.type.RelDataTypeFactory; +import org.apache.calcite.schema.ScannableTable; +import org.apache.calcite.schema.Schema; +import org.apache.calcite.schema.Statistic; +import org.apache.calcite.schema.Statistics; +import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.util.ImmutableBitSet; + +import com.google.common.collect.ImmutableList; + +import java.io.BufferedReader; +import java.io.IOException; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.nio.charset.StandardCharsets; +import java.util.NoSuchElementException; + +/** + * Table function that reads stdin and returns one row per line. + */ +public class StdinTableFunction { + + private StdinTableFunction() {} + + public static ScannableTable eval(boolean b) { + return new ScannableTable() { + public Enumerable<Object[]> scan(DataContext root) { + final InputStream is = DataContext.Variable.STDIN.get(root); + return new AbstractEnumerable<Object[]>() { + final InputStreamReader in = + new InputStreamReader(is, StandardCharsets.UTF_8); + final BufferedReader br = new BufferedReader(in); + public Enumerator<Object[]> enumerator() { + return new Enumerator<Object[]>() { + String line; + int i; + + public Object[] current() { + if (line == null) { + throw new NoSuchElementException(); + } + return new Object[] {i, line}; + } + + public boolean moveNext() { + try { + line = br.readLine(); + ++i; + return line != null; + } catch (IOException e) { + throw new RuntimeException(e); + } + } + + public void reset() { + throw new UnsupportedOperationException(); + } + + public void close() { + try { + br.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + } + }; + } + }; + } + + public RelDataType getRowType(RelDataTypeFactory typeFactory) { + return typeFactory.builder() + .add("ordinal", SqlTypeName.INTEGER) + .add("line", SqlTypeName.VARCHAR) + .build(); + } + + public Statistic getStatistic() { + return Statistics.of(1000d, ImmutableList.of(ImmutableBitSet.of(1))); + } + + public Schema.TableType getJdbcTableType() { + return Schema.TableType.TABLE; + } + }; + } +} + +// End StdinTableFunction.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/main/java/org/apache/calcite/adapter/os/package-info.java ---------------------------------------------------------------------- diff --git a/plus/src/main/java/org/apache/calcite/adapter/os/package-info.java b/plus/src/main/java/org/apache/calcite/adapter/os/package-info.java new file mode 100644 index 0000000..e3e94fa --- /dev/null +++ b/plus/src/main/java/org/apache/calcite/adapter/os/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. + */ + +/** + * The OS adapter contains various table functions that let you query data + * sources in your operating system and environment. + */ +@PackageMarker +package org.apache.calcite.adapter.os; + +import org.apache.calcite.avatica.util.PackageMarker; + +// End package-info.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java ---------------------------------------------------------------------- diff --git a/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java b/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java new file mode 100644 index 0000000..0f1adbb --- /dev/null +++ b/plus/src/test/java/org/apache/calcite/adapter/os/OsAdapterTest.java @@ -0,0 +1,321 @@ +/* + * 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.os; + +import org.apache.calcite.config.CalciteConnectionProperty; +import org.apache.calcite.config.Lex; +import org.apache.calcite.runtime.Hook; +import org.apache.calcite.sql.validate.SqlConformanceEnum; +import org.apache.calcite.test.CalciteAssert; +import org.apache.calcite.util.Holder; + +import com.google.common.base.Function; + +import org.hamcrest.CoreMatchers; +import org.junit.Test; + +import java.io.ByteArrayInputStream; +import java.io.InputStream; +import java.io.InputStreamReader; +import java.io.OutputStream; +import java.io.PrintWriter; +import java.io.StringWriter; +import java.nio.charset.StandardCharsets; +import java.sql.ResultSet; +import java.sql.SQLException; + +import static org.hamcrest.CoreMatchers.is; +import static org.hamcrest.CoreMatchers.notNullValue; +import static org.junit.Assert.assertThat; +import static org.junit.Assert.fail; + +/** + * Unit tests for the OS (operating system) adapter. + * + * <p>Also please run the following tests manually, from your shell: + * + * <ul> + * <li>./sqlsh select \* from du + * <li>./sqlsh select \* from files + * <li>./sqlsh select \* from git_commits + * <li>./sqlsh select \* from ps + * <li>(echo cats; echo and dogs) | ./sqlsh select \* from stdin + * </ul> + */ +public class OsAdapterTest { + @Test public void testDu() { + sql("select * from du") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + assertThat(r.getInt(1), notNullValue()); + assertThat(r.getString(2), CoreMatchers.startsWith("./")); + assertThat(r.wasNull(), is(false)); + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testDuFilterSortLimit() { + sql("select * from du where path like '%/src/test/java/%'\n" + + "order by 1 limit 2") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + assertThat(r.getInt(1), notNullValue()); + assertThat(r.getString(2), CoreMatchers.startsWith("./")); + assertThat(r.wasNull(), is(false)); + assertThat(r.next(), is(true)); + assertThat(r.next(), is(false)); // because of "limit 2" + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testFiles() { + sql("select distinct type from files") + .returnsUnordered("type=d", + "type=f"); + } + + @Test public void testPs() { + sql("select * from ps") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + final StringBuilder b = new StringBuilder(); + final int c = r.getMetaData().getColumnCount(); + for (int i = 0; i < c; i++) { + b.append(r.getString(i + 1)).append(';'); + assertThat(r.wasNull(), is(false)); + } + assertThat(b.toString(), notNullValue()); + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testPsDistinct() { + sql("select distinct `user` from ps") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + assertThat(r.getString(1), notNullValue()); + assertThat(r.wasNull(), is(false)); + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testGitCommits() { + sql("select count(*) from git_commits") + .returns( + new Function<ResultSet, Void>() { + public Void apply(ResultSet r) { + try { + assertThat(r.next(), is(true)); + assertThat(r.getString(1), notNullValue()); + assertThat(r.wasNull(), is(false)); + return null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + }); + } + + @Test public void testGitCommitsTop() { + final String q = "select author from git_commits\n" + + "group by 1 order by count(*) desc limit 1"; + sql(q).returnsUnordered("author=Julian Hyde <[email protected]>"); + } + + @Test public void testStdin() throws SQLException { + try (Hook.Closeable ignore = Hook.STANDARD_STREAMS.addThread( + new Function<Holder<Object[]>, Void>() { + public Void apply(Holder<Object[]> o) { + final Object[] values = o.get(); + final InputStream in = (InputStream) values[0]; + final String s = "First line\n" + + "Second line"; + final ByteArrayInputStream in2 = + new ByteArrayInputStream(s.getBytes(StandardCharsets.UTF_8)); + final OutputStream out = (OutputStream) values[1]; + final OutputStream err = (OutputStream) values[2]; + o.set(new Object[] {in2, out, err}); + return null; + } + })) { + assertThat(foo("select count(*) as c from stdin"), is("2\n")); + } + } + + @Test public void testStdinExplain() { + // Can't execute stdin, because junit's stdin never ends; + // so just run explain + final String explain = "PLAN=" + + "EnumerableAggregate(group=[{}], c=[COUNT()])\n" + + " EnumerableTableFunctionScan(invocation=[stdin(true)], " + + "rowType=[RecordType(INTEGER ordinal, VARCHAR line)], " + + "elementType=[class [Ljava.lang.Object;])"; + sql("select count(*) as c from stdin") + .explainContains(explain); + } + + @Test public void testSqlShellFormat() throws SQLException { + final String q = "select * from (values (-1, true, 'a')," + + " (2, false, 'b, c')," + + " (3, unknown, cast(null as char(1)))) as t(x, y, z)"; + final String empty = q + " where false"; + + final String spacedOut = "-1 true a \n" + + "2 false b, c\n" + + "3 null null\n"; + assertThat(foo("-o", "spaced", q), is(spacedOut)); + + assertThat(foo("-o", "spaced", empty), is("")); + + // default is 'spaced' + assertThat(foo(q), is(spacedOut)); + + final String headersOut = "x y z\n" + + spacedOut; + assertThat(foo("-o", "headers", q), is(headersOut)); + + final String headersEmptyOut = "x y z\n"; + assertThat(foo("-o", "headers", empty), is(headersEmptyOut)); + + final String jsonOut = "[\n" + + "{\n" + + " \"x\": -1,\n" + + " \"y\": true,\n" + + " \"z\": \"a \"\n" + + "},\n" + + "{\n" + + " \"x\": 2,\n" + + " \"y\": false,\n" + + " \"z\": \"b, c\"\n" + + "},\n" + + "{\n" + + " \"x\": 3,\n" + + " \"y\": null,\n" + + " \"z\": null\n" + + "}\n" + + "]\n"; + assertThat(foo("-o", "json", q), is(jsonOut)); + + final String jsonEmptyOut = "[\n" + + "]\n"; + assertThat(foo("-o", "json", empty), is(jsonEmptyOut)); + + final String csvEmptyOut = "[\n" + + "]\n"; + assertThat(foo("-o", "json", empty), is(csvEmptyOut)); + + final String csvOut = "x,y,z\n" + + "-1,true,a \n" + + "2,false,\"b, c\"\n" + + "3,,"; + assertThat(foo("-o", "csv", q), is(csvOut)); + + final String mysqlOut = "" + + "+----+-------+------+\n" + + "| x | y | z |\n" + + "+----+-------+------+\n" + + "| -1 | true | a |\n" + + "| 2 | false | b, c |\n" + + "| 3 | | |\n" + + "+----+-------+------+\n" + + "(3 rows)\n" + + "\n"; + assertThat(foo("-o", "mysql", q), is(mysqlOut)); + + final String mysqlEmptyOut = "" + + "+---+---+---+\n" + + "| x | y | z |\n" + + "+---+---+---+\n" + + "+---+---+---+\n" + + "(0 rows)\n" + + "\n"; + assertThat(foo("-o", "mysql", empty), is(mysqlEmptyOut)); + } + + private String foo(String... args) throws SQLException { + final ByteArrayInputStream inStream = new ByteArrayInputStream(new byte[0]); + final InputStreamReader in = + new InputStreamReader(inStream, StandardCharsets.UTF_8); + final StringWriter outSw = new StringWriter(); + final PrintWriter out = new PrintWriter(outSw); + final StringWriter errSw = new StringWriter(); + final PrintWriter err = new PrintWriter(errSw); + new SqlShell(in, out, err, args).run(); + return outSw.toString(); + } + + @Test public void testSqlShellHelp() throws SQLException { + final String help = "Usage: sqlsh [OPTION]... SQL\n" + + "Execute a SQL command\n" + + "\n" + + "Options:\n" + + " -o FORMAT Print output in FORMAT; options are 'spaced' (the " + + "default), 'csv',\n" + + " 'headers', 'json', 'mysql'\n" + + " -h --help Print this help\n"; + final String q = "select 1"; + assertThat(foo("--help", q), is(help)); + + assertThat(foo("-h", q), is(help)); + + try { + final String s = foo("-o", "bad", q); + fail("expected exception, got " + s); + } catch (RuntimeException e) { + assertThat(e.getMessage(), is("unknown format: bad")); + } + } + + static CalciteAssert.AssertQuery sql(String sql) { + return CalciteAssert.that() + .withModel(SqlShell.MODEL) + .with(CalciteConnectionProperty.LEX.camelName(), Lex.JAVA) + .with(CalciteConnectionProperty.CONFORMANCE.camelName(), + SqlConformanceEnum.LENIENT) + .query(sql); + } +} + +// End OsAdapterTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/plus/src/test/java/org/apache/calcite/test/PlusSuite.java ---------------------------------------------------------------------- diff --git a/plus/src/test/java/org/apache/calcite/test/PlusSuite.java b/plus/src/test/java/org/apache/calcite/test/PlusSuite.java index 46c9e08..8b4fb25 100644 --- a/plus/src/test/java/org/apache/calcite/test/PlusSuite.java +++ b/plus/src/test/java/org/apache/calcite/test/PlusSuite.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.test; +import org.apache.calcite.adapter.os.OsAdapterTest; import org.apache.calcite.adapter.tpcds.TpcdsTest; import org.apache.calcite.adapter.tpch.TpchTest; @@ -27,6 +28,7 @@ import org.junit.runners.Suite; */ @RunWith(Suite.class) @Suite.SuiteClasses({ + OsAdapterTest.class, TpcdsTest.class, TpchTest.class }) http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 54258bf..936908b 100644 --- a/pom.xml +++ b/pom.xml @@ -490,6 +490,7 @@ limitations under the License. <exclude>**/SimpleCharStream.class</exclude> <exclude>**/*TokenManager.class</exclude> <exclude>**/TokenMgrError.class</exclude> + <exclude>**/org/apache/calcite/adapter/os/Processes$ProcessFactory.class</exclude> <exclude>**/org/apache/calcite/runtime/Resources$Inst.class</exclude> <exclude>**/org/apache/calcite/test/concurrent/ConcurrentTestCommandScript.class</exclude> <exclude>**/org/apache/calcite/test/concurrent/ConcurrentTestCommandScript$ShellCommand.class</exclude> http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/site/_docs/adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/adapter.md b/site/_docs/adapter.md index 107849f..ef8e761 100644 --- a/site/_docs/adapter.md +++ b/site/_docs/adapter.md @@ -34,6 +34,7 @@ presenting the data as tables within a schema. * [File adapter](file_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/file/package-summary.html">calcite-file</a>) * JDBC adapter (part of <a href="{{ site.apiRoot }}/org/apache/calcite/adapter/jdbc/package-summary.html">calcite-core</a>) * MongoDB adapter (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/mongodb/package-summary.html">calcite-mongodb</a>) +* [OS adapter](os_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/os/package-summary.html">calcite-os</a>) * [Pig adapter](pig_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/pig/package-summary.html">calcite-pig</a>) * Solr cloud adapter (<a href="https://github.com/bluejoe2008/solr-sql";>solr-sql</a>) * Spark adapter (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/spark/package-summary.html">calcite-spark</a>) http://git-wip-us.apache.org/repos/asf/calcite/blob/d23e5295/site/_docs/os_adapter.md ---------------------------------------------------------------------- diff --git a/site/_docs/os_adapter.md b/site/_docs/os_adapter.md new file mode 100644 index 0000000..1636485 --- /dev/null +++ b/site/_docs/os_adapter.md @@ -0,0 +1,221 @@ +--- +layout: docs +title: OS adapter and sqlsh +permalink: /docs/os_adapter.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 %} +--> + +# Overview + +The OS (operating system) adapter allows you to access data in your operating +system and environment using SQL queries. + +It aims to solve similar problems that have traditionally been solved using UNIX +command pipelines, but with the power and type-safety of SQL. + +The adapter also includes a wrapper called `sqlsh` that allows you to execute +commands from your favorite shell. + +# Security warning + +The OS adapter launches processes, and is potentially a security loop-hole. +It is included in Calcite's "plus" module, which is not enabled by default. +You must think carefully before enabling it in a security-sensitive situation. + +# A simple example + +Every bash hacker knows that to find the 3 largest files you type + +{% highlight bash %} +$ find . -type f -print0 | xargs -0 ls -l | sort -nr -k 5 | head -3 +-rw-r--r-- 1 jhyde jhyde 194877 Jul 16 16:10 ./validate/SqlValidatorImpl.java +-rw-r--r-- 1 jhyde jhyde 73826 Jul 4 21:51 ./fun/SqlStdOperatorTable.java +-rw-r--r-- 1 jhyde jhyde 39214 Jul 4 21:51 ./type/SqlTypeUtil.java +{% endhighlight %} + +This actually a pipeline of relational operations, each tuple represented +by line of space-separated fields. What if we were able to access the list of +files as a relation and use it in a SQL query? And what if we could easily +execute that SQL query from the shell? This is what `sqlsh` does: + +{% highlight bash %} +$ sqlsh select size, path from files where type = \'f\' order by size desc limit 3 +194877 validate/SqlValidatorImpl.java +73826 fun/SqlStdOperatorTable.java +39214 type/SqlTypeUtil.java +{% endhighlight %} + +# sqlsh + +`sqlsh` launches a connection to Calcite whose default schema is the OS adapter. + +It uses the JAVA lexical mode, which means that unquoted table and column names +remain in the case that they were written. This is consistent with how shells like +bash behave. + +Shell meta-characters such as `*`, `>`, `<`, `(`, and `)` have to be treated with +care. Often adding a back-slash will suffice. + +# Tables and commands + +The OS adapter contains the following tables: +* `du` - Disk usage +* `ps` - Processes +* `stdin` - Standard input +* `files` - Files (based on the `find` command) +* `git_commits` - Git commits (based on `git log`) + +Most tables are implemented as views on top of table functions. + +New data sources are straightforward to add; please contribute yours! + +## Example: du + +{% highlight bash %} +$ sqlsh select count\(\*\), sum\(size_k\) from du where path like \'%.class\' +4416 27960 +{% endhighlight %} + +## Example: files + +{% highlight bash %} +$ sqlsh select type, count\(\*\) from files where path like \'%/test/%\' group by type +4416 27960 +{% endhighlight %} + +## Example: ps + +{% highlight bash %} +$ sqlsh select distinct ps.\`user\` from ps +avahi +root +jhyde +syslog +nobody +daemon +{% endhighlight %} + +The `ps.` qualifier is necessary because USER is a SQL reserved word. + +## Example: explain + +To find out what columns a table has, use {{explain}}: + +{% highlight bash %} +$ sqlsh explain plan with type for select \* from du +size_k BIGINT NOT NULL, +path VARCHAR CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, +size_b BIGINT NOT NULL +{% endhighlight %} + +## Aggregation and top-N + +Which user has the most processes? In bash: + +{% highlight bash %} +$ ps aux | awk '{print $1}' | sort | uniq -c | sort -nr | head -3 +{% endhighlight %} + +In `sqlsh`: + +{% highlight bash %} +$ ./sqlsh select count\(\*\), ps.\`user\` from ps group by ps.\`user\` order by 1 desc limit 3 +185 root +69 jhyde +2 avahi +{% endhighlight %} + +## Example: git + +How many commits and distinct authors per year? +The `git_commits` table is based upon the `git log` command. + +{% highlight bash %} +./sqlsh select floor\(commit_timestamp to year\) as y, count\(\*\), count\(distinct author\) from git_commits group by y order by 1 +2012-01-01 00:00:00 180 6 +2013-01-01 00:00:00 502 13 +2014-01-01 00:00:00 679 36 +2015-01-01 00:00:00 470 45 +2016-01-01 00:00:00 465 67 +2017-01-01 00:00:00 279 53 +{% endhighlight %} + +Note that `group by y` is possible because `sqlsh` uses Calcite's +[lenient mode]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#isGroupByAlias--). + +## Example: stdin + +Print the stdin, adding a number to each line. + +{% highlight bash %} +$ (echo cats; echo and dogs) | cat -n - + 1 cats + 2 and dogs +{% endhighlight %} + +In `sqlsh`: + +{% highlight bash %} +$ (echo cats; echo and dogs) | ./sqlsh select \* from stdin +1 cats +2 and dogs +{% endhighlight %} + +## Example: output format + +The `-o` option controls output format. + +{% highlight bash %} +$ ./sqlsh -o mysql select min\(size_k\), max\(size_k\) from du ++--------+--------+ +| EXPR$0 | EXPR$1 | ++--------+--------+ +| 0 | 94312 | ++--------+--------+ +(1 row) + +{% endhighlight %} + +Format options: + +* spaced - spaces between fields (the default) +* headers - as spaced, but with headers +* csv - comma-separated values +* json - JSON, one object per row +* mysql - an aligned table, in the same format used by MySQL + +# Further work + +The OS adapter was created in +[[CALCITE-1896](https://issues.apache.org/jira/browse/CALCITE-1896)] +but is not complete. + +Some ideas for further work: + +* Allow '-'and '.' in unquoted table names (to match typical file names) +* Allow ordinal field references, for example '$3'. This would help for files + that do not have named fields, for instance `stdin`, but you could use them + even if fields have names. Also '$0' to mean the whole input line. +* Use the file adapter, e.g. `select * from file.scott.emp` would use the + [file adapter](file_adapter.html) to open the file `scott/emp.csv` +* More tables based on git, e.g. branches, tags, files changed in each commit +* `wc` function, e.g. `select path, lineCount from git_ls_files cross apply wc(path)` +* Move `sqlsh` command, or at least the java code underneath it, + into [sqlline](https://github.com/julianhyde/sqlline)
