Calcite Frontend Guide.ipynb

{
  "cells":[
    {
      "cell_type":"markdown",
      "source":[
        "# Calcite New Frontend Guide\n",
        "\n",
        "This notebook will demonstrate how you can build frontends for languages other than SQL for Apache Calcite.\n",
        "\n",
        "There are many languages for querying data. SQL is the most common, but other languages include:\n",
        "- Datalog\n",
        "- RDF\/Graph Queries\n",
        "- GraphQL\n",
        "- Custom DSL's\n",
        "\n",
        "Calcite currently does not have any tutorial materials on how to integrate alternative frontends.\n",
        "\n",
        "This guide demonstrates how to do so, via a guided walkthrough of writing a `GraphQL -> Calcite RelNode` converter.\n",
        "\n",
        "(If you are familiar with the Calcite `Sql2RelConverter` class -- what we will be writing is essentially a `Gql2RelConverter`)"
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# Overview and Goal\n",
        "\n",
        "The aim of this notebook is to walk through the translation of a GraphQL query like this:\n",
        "\n",
        "```graphql\n",
        "query {\n",
        "    EMP(\n",
        "        limit: 2,\n",
        "        offset: 1,\n",
        "        where: {\n",
        "            _or: [\n",
        "                { DEPTNO: { _eq: 20 } },\n",
        "                { DEPTNO: { _eq: 30 } }\n",
        "            ]\n",
        "            _and: [\n",
        "                { SAL: { _gte: 1500 } }\n",
        "                {\n",
        "                    _or: [\n",
        "                        { JOB: { _eq: \"SALESMAN\" } },\n",
        "                        { JOB: { _eq: \"MANAGER\" } }\n",
        "                    ]\n",
        "                }\n",
        "            ]\n",
        "        }\n",
        "    ) {\n",
        "        EMPNO\n",
        "        ENAME\n",
        "        JOB\n",
        "        MGR\n",
        "        SAL\n",
        "        COMM\n",
        "        DEPTNO\n",
        "    }\n",
        "}\n",
        "```\n",
        "\n",
        "Into a SQL query like:\n",
        "\n",
        "```sql\n",
        "SELECT \"EMPNO\", \"ENAME\", \"JOB\", \"MGR\", \"SAL\", \"COMM\", \"DEPTNO\"\n",
        "FROM \"EMP\"\n",
        "WHERE \"DEPTNO\" IN (20, 30) AND \"SAL\" >= 1500 AND \"JOB\" IN ('MANAGER', 'SALESMAN')\n",
        "```\n",
        "\n",
        "And finally, execute that SQL statement & ensure the proper results are returned.\n",
        "\n",
        "You will learn about the following Calcite classes\/concepts:\n",
        "- SqlParser\n",
        "- RexNode\n",
        "- RelNode\n",
        "- RelToSqlConverter\n",
        "- RelBuilder\n",
        "- tools.Frameworks\n",
        "- CalciteConnection\n",
        "- Schema\/SchemaPlus"
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# Technical Overview, 10,000ft View\n",
        "\n",
        "What we need to do in order to accomplish our goal is roughly:\n",
        "\n",
        "1. Parse a GraphQL **query string** into a GraphQL **Document AST**\n",
        "2. Convert the GraphQL **Document AST** into some representation of **Relational Query semantics**\n",
        "3. Generate a Calcite **\"Row Expression\" (RexNode)** from our representation of the **`WHERE` clause**\n",
        "4. Create a Calcite **\"Relational Expression\" (RelNode)** from a combination of the **`WHERE` \"RexNode\" and `SELECT` \"RexNode\"**\n",
        "5. **Execute** the RelNode against the **data source** we are trying to query against\n",
        "\n",
        "As a picture, it looks something like this:\n",
        "\n",
        "![GQL query translation pipeline](https:\/\/i.imgur.com\/yfIVAga.png)"
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# Dependencies & Utility Functions (Skip this)\n",
        "\n",
        "The following code cells simply install required libraries and define a few helper\/utility functions.\n",
        "You can skip this."
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "@file:DependsOn(\"org.apache.calcite:calcite-core:1.29.0\")\n",
        "@file:DependsOn(\"org.apache.calcite:calcite-testkit:1.29.0\")\n",
        "@file:DependsOn(\"com.graphql-java:graphql-java:17.3\")\n",
        "@file:DependsOn(\"org.hsqldb:hsqldb:2.4.1\")"
      ],
      "execution_count":1,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "fun Any.prettyPrint(): String {\n",
        "    var indentLevel = 0\n",
        "    val indentWidth = 4\n",
        "\n",
        "    fun padding() = \"\".padStart(indentLevel * indentWidth)\n",
        "    val toString = toString()\n",
        "    val stringBuilder = StringBuilder(toString.length)\n",
        "\n",
        "    var i = 0\n",
        "    while (i < toString.length) {\n",
        "        when (val char = toString[i]) {\n",
        "            '(', '[', '{' -> {\n",
        "                indentLevel++\n",
        "                stringBuilder.appendLine(char).append(padding())\n",
        "            }\n",
        "            ')', ']', '}' -> {\n",
        "                indentLevel--\n",
        "                stringBuilder.appendLine().append(padding()).append(char)\n",
        "            }\n",
        "            ',' -> {\n",
        "                stringBuilder.appendLine(char).append(padding())\n",
        "                \/\/ ignore space after comma as we have added a newline\n",
        "                val nextChar = toString.getOrElse(i + 1) { char }\n",
        "                if (nextChar == ' ') i++\n",
        "            }\n",
        "            else -> {\n",
        "                stringBuilder.append(char)\n",
        "            }\n",
        "        }\n",
        "        i++\n",
        "    }\n",
        "\n",
        "    return stringBuilder.toString()\n",
        "}"
      ],
      "execution_count":2,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# 1. Parse a GraphQL query string into a GraphQL Document AST\n",
        "\n",
        "Below is code that we're not too interested in from the Calcite side of things.\n",
        "\n",
        "This is necessary for the rest of the tutorial, but here we just set the groundwork for parsing a GraphQL query from a `String` into it's AST form (called a \"`Document`\")."
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import graphql.language.Argument\n",
        "import graphql.language.OperationDefinition\n",
        "import graphql.language.Field\n",
        "import graphql.parser.Parser\n",
        "\n",
        "val sampleQuery =\n",
        "    \"\"\"\n",
        "    query {\n",
        "        EMP(\n",
        "            limit: 2,\n",
        "            offset: 1,\n",
        "            where: {\n",
        "                _or: [\n",
        "                    { DEPTNO: { _eq: 20 } },\n",
        "                    { DEPTNO: { _eq: 30 } }\n",
        "                ]\n",
        "                _and: [\n",
        "                    { SAL: { _gte: 1500 } }\n",
        "                    {\n",
        "                        _or: [\n",
        "                            { JOB: { _eq: \"SALESMAN\" } },\n",
        "                            { JOB: { _eq: \"MANAGER\" } }\n",
        "                        ]\n",
        "                    }\n",
        "                ]\n",
        "            }\n",
        "        ) {\n",
        "            EMPNO\n",
        "            ENAME\n",
        "            JOB\n",
        "            MGR\n",
        "            SAL\n",
        "            COMM\n",
        "            DEPTNO\n",
        "        }\n",
        "    }\n",
        "    \"\"\"\n",
        "\n",
        "fun getFirstQuery(queryString: String): Field? {\n",
        "    val document = Parser.parse(queryString)\n",
        "    val root = document.definitions.first() as OperationDefinition\n",
        "    val queries = root.selectionSet.selections\n",
        "    return queries.first() as Field\n",
        "}\n",
        "\n",
        "fun getWhereArgumentFromGraphQLQuery(queryString: String): Argument? {\n",
        "    val firstQuery = getFirstQuery(queryString)\n",
        "    if (firstQuery != null) {\n",
        "        return firstQuery.arguments.firstOrNull { it.name == \"where\" }\n",
        "    }\n",
        "    return null\n",
        "}\n",
        "\n",
        "fun getQuerySelectedFieldNames(queryString: String): List<String> {\n",
        "    val firstQuery = getFirstQuery(queryString)\n",
        "    if (firstQuery != null) {\n",
        "        return firstQuery.selectionSet.selections.map { (it as Field).name }\n",
        "    }\n",
        "    return emptyList()\n",
        "}"
      ],
      "execution_count":3,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# 2. Convert the GraphQL Document AST into some representation of Relational Query semantics\n",
        "\n",
        "Here, we will write some code that converts the unwieldy GQL Query Document AST into a representation that is easier for us to work with and closer to the relational expression semantics we are trying to express.\n",
        "\n",
        "Our Expression kind hierarchy looks like this:\n",
        "\n",
        "```scala\n",
        "type Expression =\n",
        "    | BinaryExpression\n",
        "    | UnaryExpression\n",
        "    | Column (name)\n",
        "    | Literal (value)\n",
        "\n",
        "type BinaryExpression (left, right) =\n",
        "    | AND\n",
        "    | OR\n",
        "    | NOT\n",
        "    | EQ\n",
        "    | NE\n",
        "    | LT\n",
        "    | LTE\n",
        "    | GT\n",
        "    | GTE\n",
        "    | IN\n",
        "    | NIN\n",
        "\n",
        "type UnaryExpression (operand) =\n",
        "    | NOT\n",
        "    | IS_NULL\n",
        "```\n",
        "\n",
        "Once we have a representation in this form, we can traverse it and convert it to a Calcite Row Expression (RexNode) that has identical semantics.\n",
        "\n",
        "A useful tip here is that the signature for Calcite's `RelBuilder.call()` is:\n",
        "\n",
        "```java\n",
        "public RexNode call(SqlOperator operator, RexNode... operands) {}\n",
        "public RexNode call(SqlOperator operator, Iterable<? extends RexNode> operands) {}\n",
        "```\n",
        "\n",
        "We can model our own Relational Expression types as entities that each have an attached `SqlOperator`.\n",
        "\n",
        "For binary operations, Calcite has a type called `SqlBinaryOperator`.\n",
        "\n",
        "Calcite doesn't have something that maps directly to \"Unary\" operations. It has `SqlPrefixOperation` and `SqlPostfixOperation` instead.\n",
        "\n",
        "You can find the operators under **`org.apache.calcite.sql.fun.SqlStdOperatorTable`**."
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import org.apache.calcite.rex.RexNode\n",
        "import org.apache.calcite.sql.SqlBinaryOperator\n",
        "import org.apache.calcite.sql.SqlOperator\n",
        "import org.apache.calcite.sql.`fun`.SqlStdOperatorTable\n",
        "\n",
        "sealed interface Expression {}\n",
        "\n",
        "sealed interface BinaryOperation : Expression {\n",
        "    val left: Expression\n",
        "    val right: Expression\n",
        "    val operation: SqlBinaryOperator\n",
        "}\n",
        "\n",
        "sealed interface UnaryOperation : Expression {\n",
        "    val operand: Expression\n",
        "    val operation: SqlOperator\n",
        "}\n",
        "\n",
        "data class AND(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.AND\n",
        "}\n",
        "\n",
        "data class OR(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.OR\n",
        "}\n",
        "\n",
        "data class EQ(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.EQUALS\n",
        "}\n",
        "\n",
        "data class NEQ(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.NOT_EQUALS\n",
        "}\n",
        "\n",
        "data class LT(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.LESS_THAN\n",
        "}\n",
        "\n",
        "data class LTE(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.LESS_THAN_OR_EQUAL\n",
        "}\n",
        "\n",
        "data class GT(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.GREATER_THAN\n",
        "}\n",
        "\n",
        "data class GTE(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.GREATER_THAN_OR_EQUAL\n",
        "}\n",
        "\n",
        "data class IN(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.IN\n",
        "}\n",
        "\n",
        "data class NIN(override val left: Expression, override val right: Expression) : BinaryOperation {\n",
        "    override val operation: SqlBinaryOperator = SqlStdOperatorTable.NOT_IN\n",
        "}\n",
        "\n",
        "data class NOT(override val operand: Expression) : UnaryOperation {\n",
        "    override val operation: SqlOperator = SqlStdOperatorTable.NOT\n",
        "}\n",
        "\n",
        "data class IS_NULL(override val operand: Expression) : UnaryOperation {\n",
        "    override val operation: SqlOperator = SqlStdOperatorTable.IS_NULL\n",
        "}\n",
        "\n",
        "@JvmInline\n",
        "value class COLUMN(val name: String) : Expression\n",
        "\n",
        "@JvmInline\n",
        "value class LITERAL(val value: Any) : Expression"
      ],
      "execution_count":4,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import graphql.language.*\n",
        "import graphql.parser.Parser\n",
        "\n",
        "\/\/ Unwrap a GraphQL \"Value\" node to it's Java representation\n",
        "fun graphqlValueToJava(value: Value<*>): Any {\n",
        "    return when (value) {\n",
        "        is IntValue -> value.value\n",
        "        is FloatValue -> value.value\n",
        "        is StringValue -> value.value\n",
        "        is BooleanValue -> value.isValue\n",
        "        is EnumValue -> value.name\n",
        "        is ArrayValue -> value.values.map { graphqlValueToJava(it) }\n",
        "        else -> throw IllegalArgumentException(\"Unsupported value type: ${value.javaClass}\")\n",
        "    }\n",
        "}\n",
        "\n",
        "\/\/ Convert a GraphQL query \"where: {}\" argument to an \"Expression\" AST\n",
        "fun graphqlWhereArgumentToExpression(whereArg: Argument): Expression {\n",
        "\n",
        "    \/\/ Recursive function which walks the GraphQL Query Document AST\n",
        "    \/\/ for the \"where\" argument, and constructs an equivalent relational expression AST\n",
        "    fun go(objectValue: ObjectValue): Expression {\n",
        "        val expr = objectValue.objectFields.map {\n",
        "            when (it.name) {\n",
        "                \"_not\" -> NOT(go(it.value as ObjectValue))\n",
        "\n",
        "                \"_and\" -> (it.value as ArrayValue).values\n",
        "                    .map { go(it as ObjectValue) }\n",
        "                    .reduceRight(::AND) \/\/ Combine with logical AND\n",
        "\n",
        "                \"_or\" -> (it.value as ArrayValue).values\n",
        "                    .map { go(it as ObjectValue) }\n",
        "                    .reduceRight(::OR) \/\/ Combine with logical OR\n",
        "\n",
        "                else -> {\n",
        "                    val name = it.name\n",
        "                    val node = (it.value as ObjectValue).objectFields.first()\n",
        "                    val value = graphqlValueToJava(node.value)\n",
        "                    when (node.name) {\n",
        "                        \"_eq\" -> EQ(COLUMN(name), LITERAL(value))\n",
        "                        \"_ne\" -> NEQ(COLUMN(name), LITERAL(value))\n",
        "                        \"_lt\" -> LT(COLUMN(name), LITERAL(value))\n",
        "                        \"_lte\" -> LTE(COLUMN(name), LITERAL(value))\n",
        "                        \"_gt\" -> GT(COLUMN(name), LITERAL(value))\n",
        "                        \"_gte\" -> GTE(COLUMN(name), LITERAL(value))\n",
        "                        \"_in\" -> IN(COLUMN(name), LITERAL(value))\n",
        "                        \"_nin\" -> NIN(COLUMN(name), LITERAL(value))\n",
        "                        \"_is_null\" -> IS_NULL(COLUMN(name))\n",
        "                        else -> throw IllegalArgumentException(\"Unsupported operator: ${node.name}\")\n",
        "                    }\n",
        "                }\n",
        "            }\n",
        "        }\n",
        "\n",
        "        \/\/ Combine all top-level conditions with logical AND\n",
        "        return expr.reduceRight(::AND)\n",
        "    }\n",
        "\n",
        "    return go(whereArg.value as ObjectValue)\n",
        "}"
      ],
      "execution_count":5,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "val whereArg = getWhereArgumentFromGraphQLQuery(sampleQuery)\n",
        "val testExpression = whereArg?.let { graphqlWhereArgumentToExpression(it) }\n",
        "\n",
        "if (testExpression != null) {\n",
        "    println(testExpression.prettyPrint())\n",
        "}"
      ],
      "execution_count":6,
      "outputs":[
        {
          "name":"stdout",
          "text":[
            "AND(\n",
            "    left=OR(\n",
            "        left=EQ(\n",
            "            left=COLUMN(\n",
            "                name=DEPTNO\n",
            "            ),\n",
            "            right=LITERAL(\n",
            "                value=20\n",
            "            )\n",
            "        ),\n",
            "        right=EQ(\n",
            "            left=COLUMN(\n",
            "                name=DEPTNO\n",
            "            ),\n",
            "            right=LITERAL(\n",
            "                value=30\n",
            "            )\n",
            "        )\n",
            "    ),\n",
            "    right=AND(\n",
            "        left=GTE(\n",
            "            left=COLUMN(\n",
            "                name=SAL\n",
            "            ),\n",
            "            right=LITERAL(\n",
            "                value=1500\n",
            "            )\n",
            "        ),\n",
            "        right=OR(\n",
            "            left=EQ(\n",
            "                left=COLUMN(\n",
            "                    name=JOB\n",
            "                ),\n",
            "                right=LITERAL(\n",
            "                    value=SALESMAN\n",
            "                )\n",
            "            ),\n",
            "            right=EQ(\n",
            "                left=COLUMN(\n",
            "                    name=JOB\n",
            "                ),\n",
            "                right=LITERAL(\n",
            "                    value=MANAGER\n",
            "                )\n",
            "            )\n",
            "        )\n",
            "    )\n",
            ")\n"
          ],
          "output_type":"stream"
        }
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# 3. Generate a Calcite \"Row Expression\" (RexNode) from our representation of the `WHERE` clause\n",
        "\n",
        "Here we convert our \"Expression\" interface to the equivalent Calcite `RexNode`.\n",
        "\n",
        "This is straightforward to do, because we assigned each of our interface types a corresponding `SqlOperator`.\n",
        "\n",
        "We can just switch on the type of the expression and form the proper signature for `RelBuilder.call()`"
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import org.apache.calcite.tools.RelBuilder\n",
        "\n",
        "fun Expression.toRexNode(builder: RelBuilder): RexNode = when (this) {\n",
        "    is BinaryOperation -> {\n",
        "        val left = left.toRexNode(builder)\n",
        "        val right = right.toRexNode(builder)\n",
        "        builder.call(operation, left, right)\n",
        "    }\n",
        "    is UnaryOperation -> {\n",
        "        val operand = operand.toRexNode(builder)\n",
        "        builder.call(operation, operand)\n",
        "    }\n",
        "    is COLUMN -> builder.field(name)\n",
        "    is LITERAL -> builder.literal(value)\n",
        "    else -> throw Exception(\"Impossible\")\n",
        "}"
      ],
      "execution_count":7,
      "outputs":[
        
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# 4. Create a Calcite \"Relational Expression\" (RelNode) from a combination of the `WHERE` \"RexNode\" and `SELECT` \"RexNode\"\n",
        "\n",
        "Now, we need to perform the meat of the functionality.\n",
        "\n",
        "In order to create a full Relational Expression in Calcite, we need an underlying datasource and schema for it to be written against. In the `org.apache.test` namespace there is a class called `CalciteAssert` that can wire up a sample database to Calcite for you.\n",
        "\n",
        "It's important to know what `CalciteAssert` is doing here. What is going to happen behind the scenes is:\n",
        "- Calcite will initialize a JDBC connection, called `CalciteConnection`\n",
        "- A \"root schema\" of type `SchemaPlus` will be created. This is the value that will hold all datasources.\n",
        "    - The \"root schema\" will become available under `CaliteConnection.getRootSchema()`\n",
        "    - NOTE: You can manually create an empty \"root schema\" using `Frameworks.createRootSchema()`\n",
        "- The test database + schema will be added to the \"root schema\"\n",
        "\n",
        "Then we will use the `Frameworks` tool, to create a config object which initializes things like the Planner and many other necessary bits."
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import org.apache.calcite.jdbc.CalciteConnection\n",
        "import org.apache.calcite.sql.dialect.CalciteSqlDialect\n",
        "import org.apache.calcite.sql.parser.SqlParser\n",
        "import org.apache.calcite.test.CalciteAssert\n",
        "import org.apache.calcite.tools.Frameworks\n",
        "import org.apache.calcite.tools.RelBuilder\n",
        "import org.apache.calcite.rel.rel2sql.RelToSqlConverter\n",
        "\n",
        "\/\/ Start by using \"calcite-testkit\" CalciteAssert to create a test JDBC schema and Calcite root schema\n",
        "val calciteAssert = CalciteAssert.that().with(CalciteAssert.SchemaSpec.JDBC_SCOTT)\n",
        "val connection = calciteAssert.connect() as CalciteConnection\n",
        "\n",
        "\/\/ Use the Framework utility, passing it the \"connection.rootSchema\" as the default schema\n",
        "\/\/ Make sure to set SqlParser.withCaseSensitive(false) or else statements won't parse correctly\n",
        "val config = Frameworks.newConfigBuilder()\n",
        "    .defaultSchema(connection.rootSchema)\n",
        "    .parserConfig(SqlParser.config().withCaseSensitive(false))\n",
        "    .build()\n",
        "\n",
        "\/\/ Create a Calcite \"RelBuilder\" using the FrameworkConfig\n",
        "\/\/ This RelBuilder has the context of our JDBC schema in it\n",
        "val relBuilder = RelBuilder.create(config)\n",
        "\n",
        "\/\/ Convert GraphQL query to our \"Expression\" AST\n",
        "val whereArg = requireNotNull(getWhereArgumentFromGraphQLQuery(sampleQuery))\n",
        "val expression = graphqlWhereArgumentToExpression(whereArg)\n",
        "\n",
        "\/\/ Scan the \"JDBC_SCOTT.EMP\" table\n",
        "\/\/\n",
        "\/\/ - Filter by converting our \"Expression\" AST to\n",
        "\/\/   a \"RexNode\" via the \"RelBuilder\" we initialized\n",
        "\/\/\n",
        "\/\/ - Select columns by iterating the GraphQL query selected\n",
        "\/\/   fields and converting to \"RelBuilder.field()\"\n",
        "\/\/\n",
        "val relRoot = relBuilder.scan(\"JDBC_SCOTT\", \"EMP\")\n",
        "    .filter(expression.toRexNode(relBuilder))\n",
        "    .project(\n",
        "        getQuerySelectedFieldNames(sampleQuery).map {\n",
        "            relBuilder.field(it)\n",
        "        }\n",
        "    )\n",
        "    .build()\n",
        "\n",
        "\/\/ Print out the query (logical) plan\n",
        "println(relRoot.explain())\n",
        "\n",
        "\/\/ Convert the RelNode of the query to equivalent SQL expression\n",
        "val sqlNode = RelToSqlConverter(CalciteSqlDialect.DEFAULT).visitRoot(relRoot).asStatement()\n",
        "\n",
        "\/\/ Print the equivalent SQL to check that our new frontend works as we expect\n",
        "println(\"RelToSqlConverter result: \")\n",
        "println(sqlNode.toSqlString(CalciteSqlDialect.DEFAULT))"
      ],
      "execution_count":8,
      "outputs":[
        {
          "name":"stdout",
          "text":[
            "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n",
            "  LogicalFilter(condition=[AND(SEARCH($7, Sarg[20, 30]), >=($5, 1500), SEARCH($2, Sarg['MANAGER':CHAR(8), 'SALESMAN']:CHAR(8)))])\n",
            "    JdbcTableScan(table=[[JDBC_SCOTT, EMP]])\n",
            "\n",
            "RelToSqlConverter result: \n",
            "SELECT \"EMPNO\", \"ENAME\", \"JOB\", \"MGR\", \"SAL\", \"COMM\", \"DEPTNO\"\n",
            "FROM \"SCOTT\".\"EMP\"\n",
            "WHERE \"DEPTNO\" IN (20, 30) AND \"SAL\" >= 1500 AND \"JOB\" IN ('MANAGER', 'SALESMAN')\n"
          ],
          "output_type":"stream"
        }
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"markdown",
      "source":[
        "# 5. Execute the RelNode against the data source we are trying to query against"
      ],
      "attachments":{
        
      },
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    },
    {
      "cell_type":"code",
      "source":[
        "import org.apache.calcite.tools.RelRunner\n",
        "\n",
        "\/\/ We use the \"RelRunner\" class to execute RelNode expressions\n",
        "val runner: RelRunner = connection.unwrap(RelRunner::class.java)\n",
        "\n",
        "\/\/ Execute our query RelNode and print the results\n",
        "runner.prepareStatement(relRoot).executeQuery().let { \n",
        "    val md = it.metaData\n",
        "    val columns = md.columnCount\n",
        "    while (it.next()) {\n",
        "        for (i in 1..columns) {\n",
        "            if (i > 1) print(\",  \")\n",
        "            System.out.print(md.getColumnName(i) + \" \" + it.getString(i))\n",
        "        }\n",
        "        println(\"\")\n",
        "    }\n",
        " }"
      ],
      "execution_count":9,
      "outputs":[
        {
          "name":"stdout",
          "text":[
            "EMPNO 7499,  ENAME ALLEN,  JOB SALESMAN,  MGR 7698,  SAL 1600.00,  COMM 300.00,  DEPTNO 30\n",
            "EMPNO 7566,  ENAME JONES,  JOB MANAGER,  MGR 7839,  SAL 2975.00,  COMM null,  DEPTNO 20\n",
            "EMPNO 7698,  ENAME BLAKE,  JOB MANAGER,  MGR 7839,  SAL 2850.00,  COMM null,  DEPTNO 30\n",
            "EMPNO 7844,  ENAME TURNER,  JOB SALESMAN,  MGR 7698,  SAL 1500.00,  COMM 0.00,  DEPTNO 30\n"
          ],
          "output_type":"stream"
        }
      ],
      "metadata":{
        "datalore":{
          "sheet_delimiter":false
        }
      }
    }
  ],
  "metadata":{
    "kernelspec":{
      "display_name":"Kotlin",
      "language":"kotlin",
      "name":"kotlin"
    }
  },
  "nbformat":4,
  "nbformat_minor":0
}