org.apache.calcite.sql.pretty.SqlPrettyWriter Java Examples

/**
 * Returns the projection format of the DDL columns(including computed columns).
 * e.g. If we got a DDL:
 * <pre>
 *   create table tbl1(
 *     col1 int,
 *     col2 varchar,
 *     col3 as to_timestamp(col2)
 *   ) with (
 *     'connector' = 'csv'
 *   )
 * </pre>
 * we would return a query like:
 *
 * <p>"col1, col2, to_timestamp(col2) as col3", caution that the "computed column" operands
 * have been reversed.
 */
public String getColumnSqlString() {
	SqlPrettyWriter writer = new SqlPrettyWriter(AnsiSqlDialect.DEFAULT);
	writer.setAlwaysUseParentheses(true);
	writer.setSelectListItemsOnSeparateLines(false);
	writer.setIndentation(0);
	writer.startList("", "");
	for (SqlNode column : columnList) {
		writer.sep(",");
		if (column instanceof SqlTableColumn) {
			SqlTableColumn tableColumn = (SqlTableColumn) column;
			tableColumn.getName().unparse(writer, 0, 0);
		} else {
			column.unparse(writer, 0, 0);
		}
	}

	return writer.toString();
}
 

/**
 * Returns the projection format of the DDL columns(including computed columns).
 * i.e. the following DDL:
 * <pre>
 *   create table tbl1(
 *     col1 int,
 *     col2 varchar,
 *     col3 as to_timestamp(col2)
 *   ) with (
 *     'connector' = 'csv'
 *   )
 * </pre>
 * is equivalent with query:
 *
 * <p>"col1, col2, to_timestamp(col2) as col3", caution that the "computed column" operands
 * have been reversed.
 */
public String getColumnSqlString() {
	SqlPrettyWriter writer = new SqlPrettyWriter(
		SqlPrettyWriter.config()
			.withDialect(AnsiSqlDialect.DEFAULT)
			.withAlwaysUseParentheses(true)
			.withSelectListItemsOnSeparateLines(false)
			.withIndentation(0));
	writer.startList("", "");
	for (SqlNode column : columnList) {
		writer.sep(",");
		if (column instanceof SqlTableColumn) {
			SqlTableColumn tableColumn = (SqlTableColumn) column;
			tableColumn.getName().unparse(writer, 0, 0);
		} else {
			column.unparse(writer, 0, 0);
		}
	}

	return writer.toString();
}
 

protected void generateTypeString(StringBuilder sb, boolean withDetail) {
  sb.append("INTERVAL ");
  final SqlDialect dialect = AnsiSqlDialect.DEFAULT;
  final SqlPrettyWriter writer = new SqlPrettyWriter(dialect);
  writer.setAlwaysUseParentheses(false);
  writer.setSelectListItemsOnSeparateLines(false);
  writer.setIndentation(0);
  intervalQualifier.unparse(writer, 0, 0);
  final String sql = writer.toString();
  sb.append(new SqlString(dialect, sql).getSql());
}
 

/**
 * @param sqlNode
 * @return SQL representation of the node
 */
public static String toSQLString(SqlNode sqlNode) {
  SqlPrettyWriter writer = new SqlPrettyWriter(DREMIO_DIALECT);
  writer.setSelectListItemsOnSeparateLines(false);
  writer.setIndentation(0);
  writer.setQuoteAllIdentifiers(false);
  sqlNode.unparse(writer, 0, 0);
  return writer.toString();
}
 

public int executeCreateView(SqlCreateQuarkView sqlNode) throws SQLException {
  DBI dbi = getDbi();
  List<String> tableNameList = sqlNode.getTableName().names;
  String dataSourceName = tableNameList.get(0);

  ViewDAO viewDAO = dbi.onDemand(ViewDAO.class);

  JdbcSourceDAO jdbcDAO = dbi.onDemand(JdbcSourceDAO.class);
  QuboleDbSourceDAO quboleDAO = dbi.onDemand(QuboleDbSourceDAO.class);
  DataSource dataSource = jdbcDAO.findByName(dataSourceName,
      connection.getDSSet().getId());
  if (dataSource == null) {
    dataSource = quboleDAO.findByName(dataSourceName, connection.getDSSet().getId());
  }

  if (dataSource == null) {
    throw new SQLException("DataSource with name '" + dataSourceName + "' not found");
  }

  SqlPrettyWriter writer = new SqlPrettyWriter(SqlDialect.CALCITE);
  writer.setAlwaysUseParentheses(false);
  writer.setSelectListItemsOnSeparateLines(false);
  writer.setIndentation(0);
  writer.setQuoteAllIdentifiers(true);
  sqlNode.getQuery().unparse(writer, 0, 0);
  final String sql = writer.toString();

  LOG.debug(sql);
  return viewDAO.insert(sqlNode.getName(),
      "No Description", sql,
      0L, dataSource.getId(),
      tableNameList.get(1), tableNameList.get(2),
      connection.getDSSet().getId());
}
 

/**
 * Converts a Pig script to a list of SQL statements.
 *
 * @param pigQuery Pig script
 * @param sqlDialect Dialect of SQL language
 * @throws IOException Exception during parsing or translating Pig
 */
public List<String> pigToSql(String pigQuery, SqlDialect sqlDialect)
    throws IOException {
  final SqlWriterConfig config = SqlPrettyWriter.config()
      .withQuoteAllIdentifiers(false)
      .withAlwaysUseParentheses(false)
      .withSelectListItemsOnSeparateLines(false)
      .withIndentation(2)
      .withDialect(sqlDialect);
  final SqlPrettyWriter writer = new SqlPrettyWriter(config);
  return pigToSql(pigQuery, writer);
}
 

/** Populates the table called {@code name} by executing {@code query}. */
static void populate(SqlIdentifier name, SqlNode query,
    CalcitePrepare.Context context) {
  // Generate, prepare and execute an "INSERT INTO table query" statement.
  // (It's a bit inefficient that we convert from SqlNode to SQL and back
  // again.)
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .defaultSchema(context.getRootSchema().plus())
      .build();
  final Planner planner = Frameworks.getPlanner(config);
  try {
    final StringBuilder buf = new StringBuilder();
    final SqlWriterConfig writerConfig =
        SqlPrettyWriter.config().withAlwaysUseParentheses(false);
    final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, buf);
    buf.append("INSERT INTO ");
    name.unparse(w, 0, 0);
    buf.append(' ');
    query.unparse(w, 0, 0);
    final String sql = buf.toString();
    final SqlNode query1 = planner.parse(sql);
    final SqlNode query2 = planner.validate(query1);
    final RelRoot r = planner.rel(query2);
    final PreparedStatement prepare = context.getRelRunner().prepare(r.rel);
    int rowCount = prepare.executeUpdate();
    Util.discard(rowCount);
    prepare.close();
  } catch (SqlParseException | ValidationException
      | RelConversionException | SQLException e) {
    throw new RuntimeException(e);
  }
}
 

SqlString generateSql() {
  final SqlNodeList selectList = SqlNodeList.SINGLETON_STAR;
  SqlSelect node =
      new SqlSelect(SqlParserPos.ZERO, SqlNodeList.EMPTY, selectList,
          tableName(), null, null, null, null, null, null, null, null);
  final SqlWriterConfig config = SqlPrettyWriter.config()
      .withAlwaysUseParentheses(true)
      .withDialect(jdbcSchema.dialect);
  final SqlPrettyWriter writer = new SqlPrettyWriter(config);
  node.unparse(writer, 0, 0);
  return writer.toSqlString();
}
 

protected void generateTypeString(StringBuilder sb, boolean withDetail) {
  sb.append("INTERVAL ");
  final SqlDialect dialect = AnsiSqlDialect.DEFAULT;
  final SqlWriterConfig config = SqlPrettyWriter.config()
      .withAlwaysUseParentheses(false)
      .withSelectListItemsOnSeparateLines(false)
      .withIndentation(0)
      .withDialect(dialect);
  final SqlPrettyWriter writer = new SqlPrettyWriter(config);
  intervalQualifier.unparse(writer, 0, 0);
  final String sql = writer.toString();
  sb.append(new SqlString(dialect, sql).getSql());
}
 

public static void main(String[] args) throws SqlParseException {
  final String sql = "select x as a, b as b, c as c, d,"
      + " 'mixed-Case string',"
      + " unquotedCamelCaseId,"
      + " \"quoted id\" "
      + "from"
      + " (select *"
      + " from t"
      + " where x = y and a > 5"
      + " group by z, zz"
      + " window w as (partition by c),"
      + "  w1 as (partition by c,d order by a, b"
      + "   range between interval '2:2' hour to minute preceding"
      + "    and interval '1' day following)) "
      + "order by gg desc nulls last, hh asc";
  final SqlNode node = SqlParser.create(sql).parseQuery();

  final SqlWriterConfig config = SqlPrettyWriter.config()
      .withLineFolding(SqlWriterConfig.LineFolding.STEP)
      .withSelectFolding(SqlWriterConfig.LineFolding.TALL)
      .withFromFolding(SqlWriterConfig.LineFolding.TALL)
      .withWhereFolding(SqlWriterConfig.LineFolding.TALL)
      .withHavingFolding(SqlWriterConfig.LineFolding.TALL)
      .withIndentation(4)
      .withClauseEndsLine(true);
  System.out.println(new SqlPrettyWriter(config).format(node));
}
 

/** Populates the table called {@code name} by executing {@code query}. */
protected static void populate(SqlIdentifier name, SqlNode query,
    CalcitePrepare.Context context) {
  // Generate, prepare and execute an "INSERT INTO table query" statement.
  // (It's a bit inefficient that we convert from SqlNode to SQL and back
  // again.)
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .defaultSchema(
          Objects.requireNonNull(
              Schemas.subSchema(context.getRootSchema(),
                  context.getDefaultSchemaPath())).plus())
      .build();
  final Planner planner = Frameworks.getPlanner(config);
  try {
    final StringBuilder buf = new StringBuilder();
    final SqlPrettyWriter w =
        new SqlPrettyWriter(
            SqlPrettyWriter.config()
                .withDialect(CalciteSqlDialect.DEFAULT)
                .withAlwaysUseParentheses(false),
            buf);
    buf.append("INSERT INTO ");
    name.unparse(w, 0, 0);
    buf.append(" ");
    query.unparse(w, 0, 0);
    final String sql = buf.toString();
    final SqlNode query1 = planner.parse(sql);
    final SqlNode query2 = planner.validate(query1);
    final RelRoot r = planner.rel(query2);
    final PreparedStatement prepare = context.getRelRunner().prepare(r.rel);
    int rowCount = prepare.executeUpdate();
    Util.discard(rowCount);
    prepare.close();
  } catch (SqlParseException | ValidationException
      | RelConversionException | SQLException e) {
    throw new RuntimeException(e);
  }
}
 

/**
 * Returns the SQL text of the tree of which this <code>SqlNode</code> is
 * the root.
 *
 * @param dialect     Dialect
 * @param forceParens wraps all expressions in parentheses; good for parse
 *                    test, but false by default.
 *
 *                    <p>Typical return values are:</p>
 *                    <ul>
 *                    <li>'It''s a bird!'</li>
 *                    <li>NULL</li>
 *                    <li>12.3</li>
 *                    <li>DATE '1969-04-29'</li>
 *                    </ul>
 */
public SqlString toSqlString(SqlDialect dialect, boolean forceParens) {
  if (dialect == null) {
    dialect = AnsiSqlDialect.DEFAULT;
  }
  SqlPrettyWriter writer = new SqlPrettyWriter(dialect);
  writer.setAlwaysUseParentheses(forceParens);
  writer.setSelectListItemsOnSeparateLines(false);
  writer.setIndentation(0);
  unparse(writer, 0, 0);
  return writer.toSqlString();
}
 

/**
 * Returns the SQL text of the tree of which this <code>SqlNode</code> is
 * the root.
 *
 * <p>Typical return values are:
 *
 * <ul>
 * <li>'It''s a bird!'
 * <li>NULL
 * <li>12.3
 * <li>DATE '1969-04-29'
 * </ul>
 *
 * @param transform   Transform that sets desired writer configuration
 */
public SqlString toSqlString(UnaryOperator<SqlWriterConfig> transform) {
  final SqlWriterConfig config = transform.apply(SqlPrettyWriter.config());
  SqlPrettyWriter writer = new SqlPrettyWriter(config);
  unparse(writer, 0, 0);
  return writer.toSqlString();
}