org.apache.calcite.sql.dialect.AnsiSqlDialect 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();
}
 

/** Tests that the validator expands identifiers by default.
 *
 * <p>Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-593">[CALCITE-593]
 * Validator in Frameworks should expand identifiers</a>.
 */
@Test void testFrameworksValidatorWithIdentifierExpansion()
    throws Exception {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .defaultSchema(
          CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.HR))
      .build();
  final Planner planner = Frameworks.getPlanner(config);
  SqlNode parse = planner.parse("select * from \"emps\" ");
  SqlNode val = planner.validate(parse);

  String valStr =
      val.toSqlString(AnsiSqlDialect.DEFAULT, false).getSql();

  String expandedStr =
      "SELECT `emps`.`empid`, `emps`.`deptno`, `emps`.`name`, `emps`.`salary`, `emps`.`commission`\n"
          + "FROM `hr`.`emps` AS `emps`";
  assertThat(Util.toLinux(valStr), equalTo(expandedStr));
}
 

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

public String toString() {
  return toSqlString(c -> c.withDialect(AnsiSqlDialect.DEFAULT)
      .withAlwaysUseParentheses(false)
      .withSelectListItemsOnSeparateLines(false)
      .withUpdateSetListNewline(false)
      .withIndentation(0)).getSql();
}
 

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 void checkRewrite(String query, String expectedRewrite) {
  final SqlValidator validator = validatorTransform.apply(getValidator());
  SqlNode rewrittenNode = parseAndValidate(validator, query);
  String actualRewrite =
      rewrittenNode.toSqlString(AnsiSqlDialect.DEFAULT, false).getSql();
  TestUtil.assertEqualsVerbose(expectedRewrite, Util.toLinux(actualRewrite));
}
 

private void printLimit(
    PrintWriter pw,
    String desc,
    RelDataType type,
    boolean sign,
    SqlTypeName.Limit limit,
    boolean beyond) {
  Object o = ((BasicSqlType) type).getLimit(sign, limit, beyond);
  if (o == null) {
    return;
  }
  pw.print(desc);
  String s;
  if (o instanceof byte[]) {
    int k = 0;
    StringBuilder buf = new StringBuilder("{");
    for (byte b : (byte[]) o) {
      if (k++ > 0) {
        buf.append(", ");
      }
      buf.append(Integer.toHexString(b & 0xff));
    }
    buf.append("}");
    s = buf.toString();
  } else if (o instanceof Calendar) {
    Calendar calendar = (Calendar) o;
    DateFormat dateFormat = getDateFormat(type.getSqlTypeName());
    dateFormat.setTimeZone(DateTimeUtils.UTC_ZONE);
    s = dateFormat.format(calendar.getTime());
  } else {
    s = o.toString();
  }
  pw.print(s);
  SqlLiteral literal =
      type.getSqlTypeName().createLiteral(o, SqlParserPos.ZERO);
  pw.print("; as SQL: ");
  pw.print(literal.toSqlString(AnsiSqlDialect.DEFAULT));
  pw.println();
}
 

public SqlDialect create(DatabaseMetaData databaseMetaData) {
  String databaseProductName;
  int databaseMajorVersion;
  int databaseMinorVersion;
  String databaseVersion;
  try {
    databaseProductName = databaseMetaData.getDatabaseProductName();
    databaseMajorVersion = databaseMetaData.getDatabaseMajorVersion();
    databaseMinorVersion = databaseMetaData.getDatabaseMinorVersion();
    databaseVersion = databaseMetaData.getDatabaseProductVersion();
  } catch (SQLException e) {
    throw new RuntimeException("while detecting database product", e);
  }
  final String upperProductName =
      databaseProductName.toUpperCase(Locale.ROOT).trim();
  final String quoteString = getIdentifierQuoteString(databaseMetaData);
  final NullCollation nullCollation = getNullCollation(databaseMetaData);
  final SqlDialect.Context c = SqlDialect.EMPTY_CONTEXT
      .withDatabaseProductName(databaseProductName)
      .withDatabaseMajorVersion(databaseMajorVersion)
      .withDatabaseMinorVersion(databaseMinorVersion)
      .withDatabaseVersion(databaseVersion)
      .withIdentifierQuoteString(quoteString)
      .withNullCollation(nullCollation);
  switch (upperProductName) {
  case "ACCESS":
    return new AccessSqlDialect(c);
  case "APACHE DERBY":
    return new DerbySqlDialect(c);
  case "DBMS:CLOUDSCAPE":
    return new DerbySqlDialect(c);
  case "HIVE":
    return new HiveSqlDialect(c);
  case "INGRES":
    return new IngresSqlDialect(c);
  case "INTERBASE":
    return new InterbaseSqlDialect(c);
  case "JETHRODATA":
    return new JethroDataSqlDialect(
        c.withJethroInfo(jethroCache.get(databaseMetaData)));
  case "LUCIDDB":
    return new LucidDbSqlDialect(c);
  case "ORACLE":
    return new OracleSqlDialect(c);
  case "PHOENIX":
    return new PhoenixSqlDialect(c);
  case "MYSQL (INFOBRIGHT)":
    return new InfobrightSqlDialect(c);
  case "MYSQL":
    return new MysqlSqlDialect(c);
  case "REDSHIFT":
    return new RedshiftSqlDialect(c);
  case "SPARK":
    return new SparkSqlDialect(c);
  }
  // Now the fuzzy matches.
  if (databaseProductName.startsWith("DB2")) {
    return new Db2SqlDialect(c);
  } else if (upperProductName.contains("FIREBIRD")) {
    return new FirebirdSqlDialect(c);
  } else if (databaseProductName.startsWith("Informix")) {
    return new InformixSqlDialect(c);
  } else if (upperProductName.contains("NETEZZA")) {
    return new NetezzaSqlDialect(c);
  } else if (upperProductName.contains("PARACCEL")) {
    return new ParaccelSqlDialect(c);
  } else if (databaseProductName.startsWith("HP Neoview")) {
    return new NeoviewSqlDialect(c);
  } else if (upperProductName.contains("POSTGRE")) {
    return new PostgresqlSqlDialect(c);
  } else if (upperProductName.contains("SQL SERVER")) {
    return new MssqlSqlDialect(c);
  } else if (upperProductName.contains("SYBASE")) {
    return new SybaseSqlDialect(c);
  } else if (upperProductName.contains("TERADATA")) {
    return new TeradataSqlDialect(c);
  } else if (upperProductName.contains("HSQL")) {
    return new HsqldbSqlDialect(c);
  } else if (upperProductName.contains("H2")) {
    return new H2SqlDialect(c);
  } else if (upperProductName.contains("VERTICA")) {
    return new VerticaSqlDialect(c);
  } else if (upperProductName.contains("SPARK")) {
    return new SparkSqlDialect(c);
  } else {
    return new AnsiSqlDialect(c);
  }
}
 

/** As {@link #asSql(boolean, boolean, SqlDialect)} but with SQL standard
 * dialect. */
public String asSql(boolean prefix, boolean suffix) {
  return asSql(prefix, suffix, AnsiSqlDialect.DEFAULT);
}
 

/**
 * 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 dialect     Dialect (null for ANSI SQL)
 * @param forceParens Whether to wrap all expressions in parentheses;
 *                    useful for parse test, but false by default
 */
public SqlString toSqlString(SqlDialect dialect, boolean forceParens) {
  return toSqlString(c ->
      c.withDialect(Util.first(dialect, AnsiSqlDialect.DEFAULT))
          .withAlwaysUseParentheses(forceParens)
          .withSelectListItemsOnSeparateLines(false)
          .withUpdateSetListNewline(false)
          .withIndentation(0));
}