Examples with CalciteConnection org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection used on opensource projects

Example 61 with CalciteConnection

use of org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection in project calcite by apache.

the class MultiJdbcSchemaJoinTest method test.

@Test
public 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"));
}

Example 62 with CalciteConnection

use of org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection in project calcite by apache.

the class JdbcAdapterTest method testTableModifyInsertWithSubQuery.

@Test
public void testTableModifyInsertWithSubQuery() throws Exception {
    final AssertThat that = CalciteAssert.model(JdbcTest.FOODMART_MODEL).enable(CalciteAssert.DB == DatabaseInstance.HSQLDB);
    that.doWithConnection(new Function<CalciteConnection, Void>() {

        public Void apply(CalciteConnection connection) {
            try (LockWrapper ignore = exclusiveCleanDb(connection)) {
                final String sql = "INSERT INTO \"foodmart\".\"expense_fact\"(\n" + " \"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "SELECT  \"store_id\", \"account_id\", \"exp_date\"," + " \"time_id\" + 1, \"category_id\", \"currency_id\"," + " \"amount\"\n" + "FROM \"foodmart\".\"expense_fact\"\n" + "WHERE \"store_id\" = 666";
                final String explain = "PLAN=JdbcToEnumerableConverter\n" + "  JdbcTableModify(table=[[foodmart, expense_fact]], operation=[INSERT], flattened=[false])\n" + "    JdbcProject(store_id=[$0], account_id=[$1], exp_date=[$2], time_id=[+($3, 1)], category_id=[$4], currency_id=[$5], amount=[$6])\n" + "      JdbcFilter(condition=[=($0, 666)])\n" + "        JdbcTableScan(table=[[foodmart, expense_fact]])\n";
                final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\"" + " (\"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "(SELECT \"store_id\", \"account_id\", \"exp_date\"," + " \"time_id\" + 1 AS \"time_id\", \"category_id\"," + " \"currency_id\", \"amount\"\n" + "FROM \"foodmart\".\"expense_fact\"\n" + "WHERE \"store_id\" = 666)";
                that.query(sql).explainContains(explain).planUpdateHasSql(jdbcSql, 1);
                return null;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    });
}

Example 63 with CalciteConnection

use of org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection in project calcite by apache.

the class JdbcAdapterTest method testTableModifyInsert.

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1527">[CALCITE-1527]
 * Support DML in the JDBC adapter</a>.
 */
@Test
public void testTableModifyInsert() throws Exception {
    final String sql = "INSERT INTO \"foodmart\".\"expense_fact\"(\n" + " \"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "VALUES (666, 666, TIMESTAMP '1997-01-01 00:00:00'," + " 666, '666', 666, 666)";
    final String explain = "PLAN=JdbcToEnumerableConverter\n" + "  JdbcTableModify(table=[[foodmart, expense_fact]], operation=[INSERT], flattened=[false])\n" + "    JdbcValues(tuples=[[{ 666, 666, 1997-01-01 00:00:00, 666, '666', 666, 666.0000 }]])\n";
    final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\"" + " (\"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "VALUES  (666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666.0000)";
    final AssertThat that = CalciteAssert.model(JdbcTest.FOODMART_MODEL).enable(CalciteAssert.DB == DatabaseInstance.HSQLDB || CalciteAssert.DB == DatabaseInstance.POSTGRESQL);
    that.doWithConnection(new Function<CalciteConnection, Void>() {

        public Void apply(CalciteConnection connection) {
            try (LockWrapper ignore = exclusiveCleanDb(connection)) {
                that.query(sql).explainContains(explain).planUpdateHasSql(jdbcSql, 1);
                return null;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    });
}

Example 64 with CalciteConnection

use of org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection in project calcite by apache.

the class JdbcAdapterTest method testTableModifyDelete.

@Test
public void testTableModifyDelete() throws Exception {
    final AssertThat that = CalciteAssert.model(JdbcTest.FOODMART_MODEL).enable(CalciteAssert.DB == DatabaseInstance.HSQLDB);
    that.doWithConnection(new Function<CalciteConnection, Void>() {

        public Void apply(CalciteConnection connection) {
            try (LockWrapper ignore = exclusiveCleanDb(connection)) {
                final String sql = "DELETE FROM \"foodmart\".\"expense_fact\"\n" + "WHERE \"store_id\"=666\n";
                final String explain = "PLAN=JdbcToEnumerableConverter\n" + "  JdbcTableModify(table=[[foodmart, expense_fact]], operation=[DELETE], flattened=[false])\n" + "    JdbcFilter(condition=[=($0, 666)])\n" + "      JdbcTableScan(table=[[foodmart, expense_fact]]";
                final String jdbcSql = "DELETE FROM \"foodmart\".\"expense_fact\"\n" + "WHERE \"store_id\" = 666";
                that.query(sql).explainContains(explain).planUpdateHasSql(jdbcSql, 1);
                return null;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    });
}

Example 65 with CalciteConnection

use of org.apache.beam.vendor.calcite.v1_28_0.org.apache.calcite.jdbc.CalciteConnection in project calcite by apache.

the class JdbcAdapterTest method testTableModifyInsertMultiValues.

@Test
public void testTableModifyInsertMultiValues() throws Exception {
    final String sql = "INSERT INTO \"foodmart\".\"expense_fact\"(\n" + " \"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "VALUES (666, 666, TIMESTAMP '1997-01-01 00:00:00'," + "   666, '666', 666, 666),\n" + " (666, 777, TIMESTAMP '1997-01-01 00:00:00'," + "   666, '666', 666, 666)";
    final String explain = "PLAN=JdbcToEnumerableConverter\n" + "  JdbcTableModify(table=[[foodmart, expense_fact]], operation=[INSERT], flattened=[false])\n" + "    JdbcValues(tuples=[[{ 666, 666, 1997-01-01 00:00:00, 666, '666', 666, 666.0000 }," + " { 666, 777, 1997-01-01 00:00:00, 666, '666', 666, 666.0000 }]])\n";
    final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\"" + " (\"store_id\", \"account_id\", \"exp_date\", \"time_id\"," + " \"category_id\", \"currency_id\", \"amount\")\n" + "VALUES  (666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666.0000),\n" + " (666, 777, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666.0000)";
    final AssertThat that = CalciteAssert.model(JdbcTest.FOODMART_MODEL).enable(CalciteAssert.DB == DatabaseInstance.HSQLDB || CalciteAssert.DB == DatabaseInstance.POSTGRESQL);
    that.doWithConnection(new Function<CalciteConnection, Void>() {

        public Void apply(CalciteConnection connection) {
            try (LockWrapper ignore = exclusiveCleanDb(connection)) {
                that.query(sql).explainContains(explain).planUpdateHasSql(jdbcSql, 2);
                return null;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    });
}