RewriteSampleWithCalcite.kotlin

fun calcite() {
    val schema = Frameworks.createRootSchema(true)
    val config = Frameworks.newConfigBuilder()
        .defaultSchema(schema)
        .sqlValidatorConfig(SqlValidator.Config.DEFAULT)
        .parserConfig(
            SqlParser.config()
                .withQuoting(Quoting.BRACKET)
                .withCaseSensitive(true)
                .withQuotedCasing(Casing.UNCHANGED)
                .withUnquotedCasing(Casing.UNCHANGED)
        )
        .build()
    val planner = Frameworks.getPlanner(config)

    val sql = """
WITH tq_gnOjk2mbD_cTable AS (
  SELECT *, from_unixtime([time]) as t
  FROM [sample_datasets].[www_access]
)
SELECT [YsnsAls_0002] FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT [TempTableQuerySchema].[YsnsAls_0002]) ASC) as rn,
    (CAST(DateAdd(yy,YEAR([TempTableQuerySchema].[YsnsAls_0002]) - 1904, DateAdd(mm,1 - 1, DateAdd(dd, 1 - 1, '1904-01-01'))) AS DateTime)) AS [YsnsAls_0002]
  FROM (
    SELECT ([TempTableQuerySchema].[YsnsAls_0002]) AS [YsnsAls_0002]
    FROM (
      SELECT ([TempTableQuerySchema].[YsnsAls_0002]) AS [YsnsAls_0002]
      FROM (
        SELECT ([TempTableQuerySchema].[YsnsAls_0002]) AS [YsnsAls_0002] FROM (
          SELECT (CAST(DateAdd(yy,YEAR([tq_gnOjk2mbD].[t]) - 1904, DateAdd(mm,1 - 1, DateAdd(dd, 1 - 1, '1904-01-01'))) AS DateTime)) AS [YsnsAls_0002]
          FROM [tq_gnOjk2mbD_cTable] [tq_gnOjk2mbD]
        ) AS [TempTableQuerySchema]
      ) AS [TempTableQuerySchema]
      GROUP BY [TempTableQuerySchema].[YsnsAls_0002]
    ) AS [TempTableQuerySchema]
  ) AS [TempTableQuerySchema]
) AS [TempTableQuerySchema]
WHERE (rn > 0 AND rn <= 50001 )
ORDER BY [TempTableQuerySchema].[YsnsAls_0002] ASC
    """.trimIndent()

    val node = planner.parse(sql)
    val typeFactory = JavaTypeFactoryImpl(config.typeSystem)
    class SqlRewriter : SqlValidatorImpl(
        config.operatorTable,
        CalciteCatalogReader(
            CalciteSchema.from(schema),
            CalciteSchema.from(schema).path(null),
            typeFactory,
            null
        ),
        typeFactory,
        config.sqlValidatorConfig
    ) {
        fun rewrite(node: SqlNode): SqlNode {
            return super.performUnconditionalRewrites(node, false)
        }
    }
    val rewritten = SqlRewriter().rewrite(node.accept(MySqlVisitor())!!)
    println(rewritten.toSqlString { c ->
        c.withDialect(PrestoSqlDialect.DEFAULT)
            .withAlwaysUseParentheses(false)
            .withSubQueryStyle(SqlWriter.SubQueryStyle.HYDE)
            .withClauseStartsLine(false)
            .withClauseEndsLine(false)
    })
}

class MySqlVisitor : SqlShuttle() {
    override fun visit(call: SqlCall?): SqlNode? {
        call!!
        /*
        println("<<<<<<<${call.kind}>>>>>>> ${call.operator} : ${call.operandList}")
        if (call.kind == SqlKind.CAST) {
            call.operandList.withIndex().forEach {
                println(">>>>>>>>>>>>>>>>>>>>>> ${it.index}: ${it.value.kind} : ${it.value}")
            }
        }
         */

        when (call.kind) {
            SqlKind.CAST -> {
                val origDstType = call.operand<SqlDataTypeSpec>(1)
                val dstType = if (origDstType.typeNameSpec.typeName.simple.uppercase() == "DATETIME") {
                    SqlUserDefinedTypeNameSpec("TIMESTAMP", call.parserPosition)
                }
                else {
                    origDstType.typeNameSpec
                }
                return SqlBasicCall(
                    SqlCastFunction(),
                    visit(SqlNodeList.of(call.operand(0), SqlDataTypeSpec(dstType, call.parserPosition))) as SqlNodeList,
                    call.parserPosition
                )
            }
            SqlKind.OTHER_FUNCTION -> {

                if (call.operator.name.uppercase() == "DATEADD") {
                    val timeunit = when (call.operand<SqlIdentifier>(0).simple.lowercase()) {
                        "yy", "yyyy" -> "year"
                        "mm", "m" -> "month"
                        "dd", "d" -> "day"
                        "hh" -> "hour"
                        "mi", "n" -> "minute"
                        "ss", "s" -> "second"
                        else -> throw IllegalArgumentException("Unexpected identifier: ${call.operand<SqlCall>(0)}")
                    }
                    val origDiff = call.operand<SqlNode>(1)
                    val diff =
                        when (origDiff.kind) {
                            SqlKind.LITERAL -> visit(origDiff as SqlNumericLiteral)
                            else -> visit(origDiff as SqlBasicCall)
                        }
                    val origTarget = call.operand<SqlNode>(2)
                    val target =
                        when (origTarget.kind) {
                            SqlKind.LITERAL -> visit(
                                SqlParserUtil.parseTimestampLiteral(origTarget.toString(), call.parserPosition)
                            )
                            else -> visit(origTarget as SqlBasicCall)
                        }
                    return SqlBasicCall(
                        SqlUnresolvedFunction(
                            SqlIdentifier("DATE_ADD", call.parserPosition),
                            null, null, null, null, SqlFunctionCategory.TIMEDATE
                        ),
                        SqlNodeList.of(
                            SqlLiteral.createCharString(timeunit, call.parserPosition),
                            diff,
                            target
                        ),
                        call.parserPosition
                    )
                }
            }
        }
        return super.visit(call)
    }
}