org.apache.calcite.jdbc.CalciteConnection Java Examples

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1919">[CALCITE-1919]
 * NPE when target in ReflectiveSchema belongs to the unnamed package</a>. */
@Test void testReflectiveSchemaInUnnamedPackage() throws Exception {
  final Driver driver = new Driver();
  try (CalciteConnection connection = (CalciteConnection)
      driver.connect("jdbc:calcite:", new Properties())) {
    SchemaPlus rootSchema = connection.getRootSchema();
    final Class<?> c = Class.forName("RootHr");
    final Object o = c.getDeclaredConstructor().newInstance();
    rootSchema.add("hr", new ReflectiveSchema(o));
    connection.setSchema("hr");
    final Statement statement = connection.createStatement();
    final String sql = "select * from \"emps\"";
    final ResultSet resultSet = statement.executeQuery(sql);
    final String expected = "empid=100; name=Bill\n"
        + "empid=200; name=Eric\n"
        + "empid=150; name=Sebastian\n";
    assertThat(CalciteAssert.toString(resultSet), is(expected));
  }
}
 

@Test void testTableFunctionWithArrayParameter() throws SQLException {
  try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
    CalciteConnection calciteConnection =
        connection.unwrap(CalciteConnection.class);
    SchemaPlus rootSchema = calciteConnection.getRootSchema();
    SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
    final TableFunction table =
        TableFunctionImpl.create(Smalls.GENERATE_STRINGS_OF_INPUT_SIZE_METHOD);
    schema.add("GenerateStringsOfInputSize", table);
    final String sql = "select *\n"
        + "from table(\"s\".\"GenerateStringsOfInputSize\"(ARRAY[5,4,3,1,2])) as t(n, c)\n"
        + "where char_length(c) > 3";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    assertThat(CalciteAssert.toString(resultSet),
        equalTo("N=4; C=abcd\n"));
  }
}
 

private CachedStatement buildCachedStatement(final String sql, final ProcessSession session,  final FlowFile flowFile, final RecordSchema schema,
                                             final RecordReaderFactory recordReaderFactory) {

    final CalciteConnection connection = createConnection();
    final SchemaPlus rootSchema = createRootSchema(connection);

    final FlowFileTable flowFileTable = new FlowFileTable(session, flowFile, schema, recordReaderFactory, getLogger());
    rootSchema.add("FLOWFILE", flowFileTable);
    rootSchema.setCacheEnabled(false);

    try {
        final PreparedStatement stmt = connection.prepareStatement(sql);
        return new CachedStatement(stmt, flowFileTable, connection);
    } catch (final SQLException e) {
        throw new ProcessException(e);
    }
}
 

/**
 * Tests a table function with literal arguments.
 */
@Test void testTableFunction() throws SQLException {
  try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
    CalciteConnection calciteConnection =
        connection.unwrap(CalciteConnection.class);
    SchemaPlus rootSchema = calciteConnection.getRootSchema();
    SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
    final TableFunction table =
        TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD);
    schema.add("GenerateStrings", table);
    final String sql = "select *\n"
        + "from table(\"s\".\"GenerateStrings\"(5)) as t(n, c)\n"
        + "where char_length(c) > 3";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    assertThat(CalciteAssert.toString(resultSet),
        equalTo("N=4; C=abcd\n"));
  }
}
 

@Test void testTableFunctionWithMapParameter() throws SQLException {
  try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
    CalciteConnection calciteConnection =
        connection.unwrap(CalciteConnection.class);
    SchemaPlus rootSchema = calciteConnection.getRootSchema();
    SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
    final TableFunction table =
        TableFunctionImpl.create(Smalls.GENERATE_STRINGS_OF_INPUT_MAP_SIZE_METHOD);
    schema.add("GenerateStringsOfInputMapSize", table);
    final String sql = "select *\n"
        + "from table(\"s\".\"GenerateStringsOfInputMapSize\"(Map[5,4,3,1])) as t(n, c)\n"
        + "where char_length(c) > 0";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    assertThat(CalciteAssert.toString(resultSet),
        equalTo("N=1; C=a\n"));
  }
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2382">[CALCITE-2382]
 * Sub-query lateral joined to table function</a>. */
@Test void testInlineViewLateralTableFunction() throws SQLException {
  try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
    CalciteConnection calciteConnection =
        connection.unwrap(CalciteConnection.class);
    SchemaPlus rootSchema = calciteConnection.getRootSchema();
    SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
    final TableFunction table =
        TableFunctionImpl.create(Smalls.GENERATE_STRINGS_METHOD);
    schema.add("GenerateStrings", table);
    Table tbl = new ScannableTableTest.SimpleTable();
    schema.add("t", tbl);

    final String sql = "select *\n"
        + "from (select 5 as f0 from \"s\".\"t\") \"a\",\n"
        + "  lateral table(\"s\".\"GenerateStrings\"(f0)) as t(n, c)\n"
        + "where char_length(c) > 3";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    final String expected = "F0=5; N=4; C=abcd\n"
        + "F0=5; N=4; C=abcd\n"
        + "F0=5; N=4; C=abcd\n"
        + "F0=5; N=4; C=abcd\n";
    assertThat(CalciteAssert.toString(resultSet), equalTo(expected));
  }
}
 

public void checkMazeTableFunction(Boolean solution, String maze)
    throws SQLException, ClassNotFoundException {
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
  final TableFunction table = TableFunctionImpl.create(MAZE_METHOD);
  schema.add("Maze", table);
  final TableFunction table2 = TableFunctionImpl.create(SOLVE_METHOD);
  schema.add("Solve", table2);
  final String sql;
  if (solution) {
    sql = "select *\n"
        + "from table(\"s\".\"Solve\"(5, 3, 1)) as t(s)";
  } else {
    sql = "select *\n"
        + "from table(\"s\".\"Maze\"(5, 3, 1)) as t(s)";
  }
  ResultSet resultSet = connection.createStatement().executeQuery(sql);
  final StringBuilder b = new StringBuilder();
  while (resultSet.next()) {
    b.append(resultSet.getString(1)).append("\n");
  }
  assertThat(b.toString(), is(maze));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1031">[CALCITE-1031]
 * In prepared statement, CsvScannableTable.scan is called twice</a>. To see
 * the bug, place a breakpoint in CsvScannableTable.scan, and note that it is
 * called twice. It should only be called once. */
@Test void testPrepared() throws SQLException {
  final Properties properties = new Properties();
  properties.setProperty("caseSensitive", "true");
  try (Connection connection =
      DriverManager.getConnection("jdbc:calcite:", properties)) {
    final CalciteConnection calciteConnection = connection.unwrap(
        CalciteConnection.class);

    final Schema schema =
        CsvSchemaFactory.INSTANCE
            .create(calciteConnection.getRootSchema(), null,
                ImmutableMap.of("directory",
                    resourcePath("sales"), "flavor", "scannable"));
    calciteConnection.getRootSchema().add("TEST", schema);
    final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" = ?";
    final PreparedStatement statement2 =
        calciteConnection.prepareStatement(sql);

    statement2.setString(1, "Sales");
    final ResultSet resultSet1 = statement2.executeQuery();
    Consumer<ResultSet> expect = expect("DEPTNO=10; NAME=Sales");
    expect.accept(resultSet1);
  }
}
 

/**
 * Tests a relation that is accessed via method syntax.
 * The function returns a {@link org.apache.calcite.linq4j.Queryable}.
 */
@Disabled
@Test void testOperator() throws SQLException, ClassNotFoundException {
  Connection connection =
      DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
  schema.add("GenerateStrings",
      TableMacroImpl.create(Smalls.GENERATE_STRINGS_METHOD));
  schema.add("StringUnion",
      TableMacroImpl.create(Smalls.STRING_UNION_METHOD));
  rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));
  ResultSet resultSet = connection.createStatement().executeQuery(
      "select *\n"
      + "from table(s.StringUnion(\n"
      + "  GenerateStrings(5),\n"
      + "  cursor (select name from emps)))\n"
      + "where char_length(s) > 3");
  assertTrue(resultSet.next());
}
 

/**
 * Tests a view.
 */
@Test void testView() throws SQLException, ClassNotFoundException {
  Connection connection =
      DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
  schema.add("emps_view",
      ViewTable.viewMacro(schema,
          "select * from \"hr\".\"emps\" where \"deptno\" = 10",
          null, Arrays.asList("s", "emps_view"), null));
  rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));
  ResultSet resultSet = connection.createStatement().executeQuery(
      "select *\n"
      + "from \"s\".\"emps_view\"\n"
      + "where \"empid\" < 120");
  assertEquals(
      "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000\n"
      + "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250\n",
      CalciteAssert.toString(resultSet));
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("geode", new GeodeSchema(POLICY.cache(), Collections.singleton("zips")));

      // add calcite view programmatically
      final String viewSql =  "select \"_id\" AS \"id\", \"city\", \"loc\", "
          + "cast(\"pop\" AS integer) AS \"pop\", cast(\"state\" AS varchar(2)) AS \"state\" "
          + "from \"geode\".\"zips\"";


      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("geode"), Arrays.asList("geode", "view"), false);
      root.add("view", macro);

      return connection;
    }
  };
}
 

/**
 * Tests a table function that implements {@link ScannableTable} and returns
 * a single column.
 */
@Test void testScannableTableFunction()
    throws SQLException, ClassNotFoundException {
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
  final TableFunction table = TableFunctionImpl.create(Smalls.MAZE_METHOD);
  schema.add("Maze", table);
  final String sql = "select *\n"
      + "from table(\"s\".\"Maze\"(5, 3, 1))";
  ResultSet resultSet = connection.createStatement().executeQuery(sql);
  final String result = "S=abcde\n"
      + "S=xyz\n"
      + "S=generate(w=5, h=3, s=1)\n";
  assertThat(CalciteAssert.toString(resultSet), is(result));
}
 

@Override
public Connection connect(String url, Properties info) throws SQLException {
  if(!this.acceptsURL(url)) {
    return null;
  }

  Connection connection = super.connect(url, info);
  CalciteConnection calciteConnection = (CalciteConnection) connection;
  final SchemaPlus rootSchema = calciteConnection.getRootSchema();

  String schemaName = info.getProperty("zk");
  if(schemaName == null) {
    throw new SQLException("zk must be set");
  }
  final SolrSchema solrSchema = new SolrSchema(info, solrClientCache);
  rootSchema.add(schemaName, solrSchema);

  // Set the default schema
  calciteConnection.setSchema(schemaName);
  return calciteConnection;
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), NAME));

      // add calcite view programmatically
      final String viewSql = String.format(Locale.ROOT,
          "select _MAP['a'] AS \"a\", "
              + " _MAP['b.a']  AS \"b.a\", "
              +  " _MAP['b.b'] AS \"b.b\", "
              +  " _MAP['b.c.a'] AS \"b.c.a\", "
              +  " _MAP['_id'] AS \"id\" " // _id field is implicit
              +  " from \"elastic\".\"%s\"", NAME);

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("elastic"), Arrays.asList("elastic", "view"), false);
      root.add("VIEW", macro);
      return connection;
    }
  };
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), ZIPS));

      // add calcite view programmatically
      final String viewSql = String.format(Locale.ROOT,
          "select cast(_MAP['city'] AS varchar(20)) AS \"city\", "
          + " cast(_MAP['loc'][0] AS float) AS \"longitude\",\n"
          + " cast(_MAP['loc'][1] AS float) AS \"latitude\",\n"
          + " cast(_MAP['pop'] AS integer) AS \"pop\", "
          +  " cast(_MAP['state'] AS varchar(2)) AS \"state\", "
          +  " cast(_MAP['id'] AS varchar(5)) AS \"id\" "
          +  "from \"elastic\".\"%s\"", ZIPS);

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("elastic"), Arrays.asList("elastic", "view"), false);
      root.add(ZIPS, macro);

      return connection;
    }
  };
}
 

public TranslatableTable apply(List<Object> arguments) {
  final CalciteConnection connection =
      MaterializedViewTable.MATERIALIZATION_CONNECTION;
  CalcitePrepare.AnalyzeViewResult parsed =
      Schemas.analyzeView(connection, schema, schemaPath, viewSql, viewPath,
          modifiable != null && modifiable);
  final List<String> schemaPath1 =
      schemaPath != null ? schemaPath : schema.path(null);
  if ((modifiable == null || modifiable)
      && parsed.modifiable
      && parsed.table != null) {
    return modifiableViewTable(parsed, viewSql, schemaPath1, viewPath, schema);
  } else {
    return viewTable(parsed, viewSql, schemaPath1, viewPath);
  }
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), NAME));

      // add calcite view programmatically
      final String viewSql = String.format(Locale.ROOT,
          "select cast(_MAP['A'] AS varchar(2)) AS a,"
              + " cast(_MAP['b'] AS varchar(2)) AS b, "
              +  " cast(_MAP['cCC'] AS varchar(2)) AS c, "
              +  " cast(_MAP['DDd'] AS varchar(2)) AS d "
              +  " from \"elastic\".\"%s\"", NAME);

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
              Collections.singletonList("elastic"), Arrays.asList("elastic", "view"), false);
      root.add("VIEW", macro);

      return connection;
    }
  };
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), NAME));

      // add calcite view programmatically
      final String viewSql = String.format(Locale.ROOT,
          "select _MAP['cat1'] AS \"cat1\", "
              + " _MAP['cat2']  AS \"cat2\", "
              +  " _MAP['cat3'] AS \"cat3\", "
              +  " _MAP['cat4'] AS \"cat4\", "
              +  " _MAP['cat5'] AS \"cat5\", "
              +  " _MAP['val1'] AS \"val1\", "
              +  " _MAP['val2'] AS \"val2\" "
              +  " from \"elastic\".\"%s\"", NAME);

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("elastic"), Arrays.asList("elastic", "view"), false);
      root.add("view", macro);
      return connection;
    }
  };
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:lex=JAVA");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), ZIPS));

      // add calcite view programmatically
      final String viewSql = "select cast(_MAP['city'] AS varchar(20)) AS \"city\", "
          + " cast(_MAP['loc'][0] AS float) AS \"longitude\",\n"
          + " cast(_MAP['loc'][1] AS float) AS \"latitude\",\n"
          + " cast(_MAP['pop'] AS integer) AS \"pop\", "
          +  " cast(_MAP['state'] AS varchar(2)) AS \"state\", "
          +  " cast(_MAP['id'] AS varchar(5)) AS \"id\" "
          +  "from \"elastic\".\"zips\"";

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("elastic"), Arrays.asList("elastic", "view"), false);
      root.add("zips", macro);

      return connection;
    }
  };
}
 

private CalciteAssert.ConnectionFactory newConnectionFactory() {
  return new CalciteAssert.ConnectionFactory() {
    @Override public Connection createConnection() throws SQLException {
      final Connection connection = DriverManager.getConnection("jdbc:calcite:");
      final SchemaPlus root = connection.unwrap(CalciteConnection.class).getRootSchema();

      root.add("elastic", new ElasticsearchSchema(NODE.restClient(), NODE.mapper(), NAME));

      // add calcite view programmatically
      final String viewSql = String.format(Locale.ROOT,
          "select cast(_MAP['a'] AS varchar(2)) AS a, "
              + " cast(_MAP['b'] AS varchar(2)) AS b, "
              +  " cast(_MAP['c'] AS varchar(2)) AS c, "
              +  " cast(_MAP['int'] AS integer) AS num"
              +  " from \"elastic\".\"%s\"", NAME);

      ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
          Collections.singletonList("elastic"),
          Arrays.asList("elastic", "view"), false);
      root.add("VIEW", macro);

      return connection;
    }
  };
}
 

public ModelHandler(CalciteConnection connection, String uri)
    throws IOException {
  super();
  this.connection = connection;
  this.modelUri = uri;

  JsonRoot root;
  ObjectMapper mapper;
  if (uri.startsWith("inline:")) {
    // trim here is to correctly autodetect if it is json or not in case of leading spaces
    String inline = uri.substring("inline:".length()).trim();
    mapper = (inline.startsWith("/*") || inline.startsWith("{"))
        ? JSON_MAPPER
        : YAML_MAPPER;
    root = mapper.readValue(inline, JsonRoot.class);
  } else {
    mapper = uri.endsWith(".yaml") || uri.endsWith(".yml") ? YAML_MAPPER : JSON_MAPPER;
    root = mapper.readValue(new File(uri), JsonRoot.class);
  }
  visit(root);
}
 

/** Creates a config builder that will contain a view, "MYVIEW", and also
 * the SCOTT JDBC schema, whose tables implement
 * {@link org.apache.calcite.schema.TranslatableTable}. */
static Frameworks.ConfigBuilder expandingConfig(Connection connection)
    throws SQLException {
  final CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  final SchemaPlus root = calciteConnection.getRootSchema();
  CalciteAssert.SchemaSpec spec = CalciteAssert.SchemaSpec.SCOTT;
  CalciteAssert.addSchema(root, spec);
  final String viewSql =
      String.format(Locale.ROOT, "select * from \"%s\".\"%s\" where 1=1",
          spec.schemaName, "EMP");

  // create view
  ViewTableMacro macro = ViewTable.viewMacro(root, viewSql,
      Collections.singletonList("test"), Arrays.asList("test", "view"), false);

  // register view (in root schema)
  root.add("MYVIEW", macro);

  return Frameworks.newConfigBuilder().defaultSchema(root);
}
 

/** Analyzes a view. For use within Calcite only. */
public static CalcitePrepare.AnalyzeViewResult analyzeView(
    final CalciteConnection connection, final CalciteSchema schema,
    final List<String> schemaPath, final String viewSql,
    List<String> viewPath, boolean fail) {
  final CalcitePrepare prepare = CalcitePrepare.DEFAULT_FACTORY.apply();
  final ImmutableMap<CalciteConnectionProperty, String> propValues =
      ImmutableMap.of();
  final CalcitePrepare.Context context =
      makeContext(connection, schema, schemaPath, viewPath, propValues);
  CalcitePrepare.Dummy.push(context);
  try {
    return prepare.analyzeView(context, viewSql, fail);
  } finally {
    CalcitePrepare.Dummy.pop(context);
  }
}
 

/** Parses and validates a SQL query and converts to relational algebra. For
 * use within Calcite only. */
public static CalcitePrepare.ConvertResult convert(
    final CalciteConnection connection, final CalciteSchema schema,
    final List<String> schemaPath, final String sql) {
  final CalcitePrepare prepare = CalcitePrepare.DEFAULT_FACTORY.apply();
  final ImmutableMap<CalciteConnectionProperty, String> propValues =
      ImmutableMap.of();
  final CalcitePrepare.Context context =
      makeContext(connection, schema, schemaPath, null, propValues);
  CalcitePrepare.Dummy.push(context);
  try {
    return prepare.convert(context, sql);
  } finally {
    CalcitePrepare.Dummy.pop(context);
  }
}
 

@Test void testTableWhere() throws SQLException,
    ClassNotFoundException {
  final Connection connection =
      CalciteAssert.that(CalciteAssert.Config.JDBC_FOODMART).connect();
  final CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  final SchemaPlus rootSchema = calciteConnection.getRootSchema();
  ParameterExpression c =
      Expressions.parameter(JdbcTest.Customer.class, "c");
  String s =
      Schemas.queryable(Schemas.createDataContext(connection, rootSchema),
          rootSchema.getSubSchema("foodmart"),
          JdbcTest.Customer.class, "customer")
          .where(
              Expressions.lambda(
                  Expressions.lessThan(
                      Expressions.field(c, "customer_id"),
                      Expressions.constant(5)),
                  c))
          .toList()
          .toString();
  Util.discard(s);
}
 

private Connection setup() throws SQLException {
  // Create a jdbc database & table
  final String db = TempDb.INSTANCE.getUrl();
  Connection c1 = DriverManager.getConnection(db, "", "");
  Statement stmt1 = c1.createStatement();
  // This is a table we can join with the emps from the hr schema
  stmt1.execute("create table table1(id integer not null primary key, "
      + "field1 varchar(10))");
  stmt1.execute("insert into table1 values(100, 'foo')");
  stmt1.execute("insert into table1 values(200, 'bar')");
  c1.close();

  // Make a Calcite schema with both a jdbc schema and a non-jdbc schema
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  rootSchema.add("DB",
      JdbcSchema.create(rootSchema, "DB",
          JdbcSchema.dataSource(db, "org.hsqldb.jdbcDriver", "", ""),
          null, null));
  rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));
  return connection;
}
 

/** Parses and validates a SQL query. For use within Calcite only. */
public static CalcitePrepare.ParseResult parse(
    final CalciteConnection connection, final CalciteSchema schema,
    final List<String> schemaPath, final String sql) {
  final CalcitePrepare prepare = CalcitePrepare.DEFAULT_FACTORY.apply();
  final ImmutableMap<CalciteConnectionProperty, String> propValues =
      ImmutableMap.of();
  final CalcitePrepare.Context context =
      makeContext(connection, schema, schemaPath, null, propValues);
  CalcitePrepare.Dummy.push(context);
  try {
    return prepare.parse(context, sql);
  } finally {
    CalcitePrepare.Dummy.pop(context);
  }
}
 

@Test void test() throws SQLException, ClassNotFoundException {
  // Create two databases
  // It's two times hsqldb, but imagine they are different rdbms's
  final String db1 = TempDb.INSTANCE.getUrl();
  Connection c1 = DriverManager.getConnection(db1, "", "");
  Statement stmt1 = c1.createStatement();
  stmt1.execute("create table table1(id varchar(10) not null primary key, "
      + "field1 varchar(10))");
  stmt1.execute("insert into table1 values('a', 'aaaa')");
  c1.close();

  final String db2 = TempDb.INSTANCE.getUrl();
  Connection c2 = DriverManager.getConnection(db2, "", "");
  Statement stmt2 = c2.createStatement();
  stmt2.execute("create table table2(id varchar(10) not null primary key, "
      + "field1 varchar(10))");
  stmt2.execute("insert into table2 values('a', 'aaaa')");
  c2.close();

  // Connect via calcite to these databases
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  final DataSource ds1 =
      JdbcSchema.dataSource(db1, "org.hsqldb.jdbcDriver", "", "");
  rootSchema.add("DB1",
      JdbcSchema.create(rootSchema, "DB1", ds1, null, null));
  final DataSource ds2 =
      JdbcSchema.dataSource(db2, "org.hsqldb.jdbcDriver", "", "");
  rootSchema.add("DB2",
      JdbcSchema.create(rootSchema, "DB2", ds2, null, null));

  Statement stmt3 = connection.createStatement();
  ResultSet rs = stmt3.executeQuery("select table1.id, table1.field1 "
      + "from db1.table1 join db2.table2 on table1.id = table2.id");
  assertThat(CalciteAssert.toString(rs), equalTo("ID=a; FIELD1=aaaa\n"));
}
 

/**
 * Tests a view with a path.
 */
@Test void testViewPath() throws SQLException, ClassNotFoundException {
  Connection connection =
      DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  SchemaPlus schema = rootSchema.add("s", new AbstractSchema());
  // create a view s.emps based on hr.emps. uses explicit schema path "hr".
  schema.add("emps",
      ViewTable.viewMacro(schema,
          "select * from \"emps\" where \"deptno\" = 10",
          ImmutableList.of("hr"), ImmutableList.of("s", "emps"), null));
  schema.add("hr_emps",
      ViewTable.viewMacro(schema,
          "select * from \"emps\"",
          ImmutableList.of("hr"), ImmutableList.of("s", "hr_emps"), null));
  schema.add("s_emps",
      ViewTable.viewMacro(schema,
          "select * from \"emps\"",
          ImmutableList.of("s"), ImmutableList.of("s", "s_emps"), null));
  schema.add("null_emps",
      ViewTable.viewMacro(schema, "select * from \"emps\"", null,
          ImmutableList.of("s", "null_emps"), null));
  rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));
  final Statement statement = connection.createStatement();
  ResultSet resultSet;
  resultSet = statement.executeQuery(
      "select * from \"s\".\"hr_emps\"");
  assertEquals(4, count(resultSet)); // "hr_emps" -> "hr"."emps", 4 rows
  resultSet = statement.executeQuery(
      "select * from \"s\".\"s_emps\""); // "s_emps" -> "s"."emps", 3 rows
  assertEquals(3, count(resultSet));
  resultSet = statement.executeQuery(
      "select * from \"s\".\"null_emps\""); // "null_emps" -> "s"."emps", 3
  assertEquals(3, count(resultSet));
  statement.close();
}
 

@BeforeEach
public void setUp() throws SQLException {
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  rootSchema.add("test", new ReflectiveSchema(new TestSchema()));
  calciteConnection.setSchema("test");
  this.conn = calciteConnection;
}