org.apache.calcite.schema.impl.ViewTable Java Examples

/** 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);
}
 

/** Executes a {@code CREATE VIEW} command. */
public void execute(SqlCreateView create,
    CalcitePrepare.Context context) {
  final Pair<CalciteSchema, String> pair =
      schema(context, true, create.name);
  final SchemaPlus schemaPlus = pair.left.plus();
  for (Function function : schemaPlus.getFunctions(pair.right)) {
    if (function.getParameters().isEmpty()) {
      if (!create.getReplace()) {
        throw SqlUtil.newContextException(create.name.getParserPosition(),
            RESOURCE.viewExists(pair.right));
      }
      pair.left.removeFunction(pair.right);
    }
  }
  final SqlNode q = renameColumns(create.columnList, create.query);
  final String sql = q.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
  final ViewTableMacro viewTableMacro =
      ViewTable.viewMacro(schemaPlus, sql, pair.left.path(null),
          context.getObjectPath(), false);
  final TranslatableTable x = viewTableMacro.apply(ImmutableList.of());
  Util.discard(x);
  schemaPlus.add(pair.right, viewTableMacro);
}
 

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;
    }
  };
}
 

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;
    }
  };
}
 

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;
    }
  };
}
 

/**
 * 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));
}
 

/** Executes a {@code CREATE MATERIALIZED VIEW} command. */
public void execute(SqlCreateMaterializedView create,
    CalcitePrepare.Context context) {
  final Pair<CalciteSchema, String> pair = schema(context, true, create.name);
  if (pair.left.plus().getTable(pair.right) != null) {
    // Materialized view exists.
    if (!create.ifNotExists) {
      // They did not specify IF NOT EXISTS, so give error.
      throw SqlUtil.newContextException(create.name.getParserPosition(),
          RESOURCE.tableExists(pair.right));
    }
    return;
  }
  final SqlNode q = renameColumns(create.columnList, create.query);
  final String sql = q.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
  final List<String> schemaPath = pair.left.path(null);
  final ViewTableMacro viewTableMacro =
      ViewTable.viewMacro(pair.left.plus(), sql, schemaPath,
          context.getObjectPath(), false);
  final TranslatableTable x = viewTableMacro.apply(ImmutableList.of());
  final RelDataType rowType = x.getRowType(context.getTypeFactory());

  // Table does not exist. Create it.
  final MaterializedViewTable table =
      new MaterializedViewTable(pair.right, RelDataTypeImpl.proto(rowType));
  pair.left.add(pair.right, table);
  populate(create.name, create.query, context);
  table.key =
      MaterializationService.instance().defineMaterialization(pair.left, null,
          sql, schemaPath, pair.right, true, true);
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-937">[CALCITE-937]
 * User-defined function within view</a>. */
@Test void testUserDefinedFunctionInView() throws Exception {
  Class.forName("org.apache.calcite.jdbc.Driver");
  Connection connection = DriverManager.getConnection("jdbc:calcite:");
  CalciteConnection calciteConnection =
      connection.unwrap(CalciteConnection.class);
  SchemaPlus rootSchema = calciteConnection.getRootSchema();
  rootSchema.add("hr", new ReflectiveSchema(new JdbcTest.HrSchema()));

  SchemaPlus post = rootSchema.add("POST", new AbstractSchema());
  post.add("MY_INCREMENT",
      ScalarFunctionImpl.create(Smalls.MyIncrement.class, "eval"));

  final String viewSql = "select \"empid\" as EMPLOYEE_ID,\n"
      + "  \"name\" || ' ' || \"name\" as EMPLOYEE_NAME,\n"
      + "  \"salary\" as EMPLOYEE_SALARY,\n"
      + "  POST.MY_INCREMENT(\"empid\", 10) as INCREMENTED_SALARY\n"
      + "from \"hr\".\"emps\"";
  post.add("V_EMP",
      ViewTable.viewMacro(post, viewSql, ImmutableList.of(),
          ImmutableList.of("POST", "V_EMP"), null));

  final String result = ""
      + "EMPLOYEE_ID=100; EMPLOYEE_NAME=Bill Bill; EMPLOYEE_SALARY=10000.0; INCREMENTED_SALARY=110.0\n"
      + "EMPLOYEE_ID=200; EMPLOYEE_NAME=Eric Eric; EMPLOYEE_SALARY=8000.0; INCREMENTED_SALARY=220.0\n"
      + "EMPLOYEE_ID=150; EMPLOYEE_NAME=Sebastian Sebastian; EMPLOYEE_SALARY=7000.0; INCREMENTED_SALARY=165.0\n"
      + "EMPLOYEE_ID=110; EMPLOYEE_NAME=Theodore Theodore; EMPLOYEE_SALARY=11500.0; INCREMENTED_SALARY=121.0\n";

  Statement statement = connection.createStatement();
  ResultSet resultSet = statement.executeQuery(viewSql);
  assertThat(CalciteAssert.toString(resultSet), is(result));
  resultSet.close();

  ResultSet viewResultSet =
      statement.executeQuery("select * from \"POST\".\"V_EMP\"");
  assertThat(CalciteAssert.toString(viewResultSet), is(result));
  statement.close();
  connection.close();
}
 

/**
 * 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();
}
 

public static MockRelViewTable create(ViewTable viewTable,
    MockCatalogReader catalogReader, String catalogName, String schemaName, String name,
    boolean stream, double rowCount, ColumnResolver resolver) {
  Table underlying = viewTable.unwrap(Table.class);
  InitializerExpressionFactory initializerExpressionFactory =
      underlying instanceof Wrapper
          ? ((Wrapper) underlying).unwrap(InitializerExpressionFactory.class)
          : NullInitializerExpressionFactory.INSTANCE;
  return new MockRelViewTable(viewTable,
      catalogReader, catalogName, schemaName, name, stream, rowCount,
      resolver, Util.first(initializerExpressionFactory,
      NullInitializerExpressionFactory.INSTANCE));
}
 

private MockRelViewTable(ViewTable viewTable,
    MockCatalogReader catalogReader, String catalogName, String schemaName, String name,
    boolean stream, double rowCount, ColumnResolver resolver,
    InitializerExpressionFactory initializerExpressionFactory) {
  super(catalogReader, ImmutableList.of(catalogName, schemaName, name),
      stream, false, rowCount, resolver, initializerExpressionFactory);
  this.viewTable = viewTable;
}
 

public static TranslatableTable str(Object o, Object p) {
  assertThat(RexLiteral.validConstant(o, Litmus.THROW), is(true));
  assertThat(RexLiteral.validConstant(p, Litmus.THROW), is(true));
  return new ViewTable(Object.class, typeFactory ->
      typeFactory.builder().add("c", SqlTypeName.VARCHAR, 100).build(),
      "values " + CalciteSqlDialect.DEFAULT.quoteStringLiteral(o.toString())
          + ", " + CalciteSqlDialect.DEFAULT.quoteStringLiteral(p.toString()),
      ImmutableList.of(), Arrays.asList("view"));
}
 

public void visit(JsonView jsonView) {
  try {
    checkRequiredAttributes(jsonView, "name");
    final SchemaPlus schema = currentMutableSchema("view");
    final List<String> path = Util.first(jsonView.path, currentSchemaPath());
    final List<String> viewPath = ImmutableList.<String>builder().addAll(path)
        .add(jsonView.name).build();
    schema.add(jsonView.name,
        ViewTable.viewMacro(schema, jsonView.getSql(), path, viewPath,
            jsonView.modifiable));
  } catch (Exception e) {
    throw new RuntimeException("Error instantiating " + jsonView, e);
  }
}
 

private Table convertCatalogView(String tableName, CatalogView table, TableSchema resolvedSchema) {
	return new ViewTable(
		null,
		typeFactory -> ((FlinkTypeFactory) typeFactory).buildLogicalRowType(resolvedSchema),
		table.getExpandedQuery(),
		Arrays.asList(catalogName, databaseName),
		Arrays.asList(catalogName, databaseName, tableName)
	);
}
 

@Override public MockCatalogReader init() {
  // Register "DYNAMIC" schema.
  MockSchema schema = new MockSchema("SALES");
  registerSchema(schema);

  MockDynamicTable nationTable =
      new MockDynamicTable(schema.getCatalogName(),
          schema.getName(), "NATION");
  registerTable(nationTable);

  Supplier<MockDynamicTable> customerTableSupplier = () ->
      new MockDynamicTable(schema.getCatalogName(), schema.getName(), "CUSTOMER");

  MockDynamicTable customerTable = customerTableSupplier.get();
  registerTable(customerTable);

  // CREATE TABLE "REGION" - static table with known schema.
  final RelDataType intType =
      typeFactory.createSqlType(SqlTypeName.INTEGER);
  final RelDataType varcharType =
      typeFactory.createSqlType(SqlTypeName.VARCHAR);

  MockTable regionTable =
      MockTable.create(this, schema, "REGION", false, 100);
  regionTable.addColumn("R_REGIONKEY", intType);
  regionTable.addColumn("R_NAME", varcharType);
  regionTable.addColumn("R_COMMENT", varcharType);
  registerTable(regionTable);

  List<String> custModifiableViewNames = Arrays.asList(
      schema.getCatalogName(), schema.getName(), "CUSTOMER_MODIFIABLEVIEW");
  TableMacro custModifiableViewMacro = MockModifiableViewRelOptTable.viewMacro(rootSchema,
      "select n_name from SALES.CUSTOMER", custModifiableViewNames.subList(0, 2),
      Collections.singletonList(custModifiableViewNames.get(2)), true);
  TranslatableTable empModifiableView = custModifiableViewMacro.apply(Collections.emptyList());
  MockTable mockCustViewTable = MockRelViewTable.create(
      (ViewTable) empModifiableView, this,
      custModifiableViewNames.get(0), custModifiableViewNames.get(1),
      custModifiableViewNames.get(2), false, 20, null);
  registerTable(mockCustViewTable);

  // re-registers customer table to clear its row type after view registration
  reregisterTable(customerTableSupplier.get());

  return this;
}
 

/** Executes a {@code CREATE TABLE} command. Called via reflection. */
public void execute(SqlCreateTable create, CalcitePrepare.Context context) {
  final CalciteSchema schema =
      Schemas.subSchema(context.getRootSchema(),
          context.getDefaultSchemaPath());
  final JavaTypeFactory typeFactory = context.getTypeFactory();
  final RelDataType queryRowType;
  if (create.query != null) {
    // A bit of a hack: pretend it's a view, to get its row type
    final String sql =
        create.query.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
    final ViewTableMacro viewTableMacro =
        ViewTable.viewMacro(schema.plus(), sql, schema.path(null),
            context.getObjectPath(), false);
    final TranslatableTable x = viewTableMacro.apply(ImmutableList.of());
    queryRowType = x.getRowType(typeFactory);

    if (create.columnList != null
        && queryRowType.getFieldCount() != create.columnList.size()) {
      throw SqlUtil.newContextException(create.columnList.getParserPosition(),
          RESOURCE.columnCountMismatch());
    }
  } else {
    queryRowType = null;
  }
  final RelDataTypeFactory.Builder builder = typeFactory.builder();
  if (create.columnList != null) {
    final SqlValidator validator = new ContextSqlValidator(context, false);
    create.forEachNameType((name, typeSpec) ->
        builder.add(name.getSimple(), typeSpec.deriveType(validator, true)));
  } else {
    if (queryRowType == null) {
      // "CREATE TABLE t" is invalid; because there is no "AS query" we need
      // a list of column names and types, "CREATE TABLE t (INT c)".
      throw SqlUtil.newContextException(create.name.getParserPosition(),
          RESOURCE.createTableRequiresColumnList());
    }
    builder.addAll(queryRowType.getFieldList());
  }
  final RelDataType rowType = builder.build();
  schema.add(create.name.getSimple(),
      new MutableArrayTable(create.name.getSimple(),
          RelDataTypeImpl.proto(rowType)));
  if (create.query != null) {
    populate(create.name, create.query, context);
  }
}
 

public static TranslatableTable strView(String s) {
  return new ViewTable(Object.class, typeFactory ->
      typeFactory.builder().add("c", SqlTypeName.VARCHAR, 100).build(),
      "values (" + CalciteSqlDialect.DEFAULT.quoteStringLiteral(s) + ")",
      ImmutableList.of(), Arrays.asList("view"));
}
 

public static TranslatableTable view(String s) {
  return new ViewTable(Object.class, typeFactory ->
      typeFactory.builder().add("c", SqlTypeName.INTEGER).build(),
      "values (1), (3), " + s, ImmutableList.of(), Arrays.asList("view"));
}