<?xml version="1.0" encoding="UTF-8"?>
  <project xmlns="http://maven.apache.org/POM/4.0.0"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.polarsparc</groupId>
    <artifactId>Calcite</artifactId>
    <version>1.0</version>

    <properties>
        <maven.compiler.source>21</maven.compiler.source>
        <maven.compiler.target>21</maven.compiler.target>
        <slf4j.version>2.0.16</slf4j.version>
        <calcite.version>1.37.0</calcite.version>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-simple -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>${slf4j.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.calcite/calcite-core -->
        <dependency>
            <groupId>org.apache.calcite</groupId>
            <artifactId>calcite-core</artifactId>
            <version>${calcite.version}</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.calcite/calcite-file -->
        <dependency>
            <groupId>org.apache.calcite</groupId>
            <artifactId>calcite-file</artifactId>
            <version>${calcite.version}</version>
        </dependency>
    </dependencies>

</project>

#
### SLF4J Simple Logger properties
#

org.slf4j.simpleLogger.defaultLogLevel=info
org.slf4j.simpleLogger.showDateTime=true
org.slf4j.simpleLogger.dateTimeFormat=yyyy-MM-dd HH:mm:ss:SSS
org.slf4j.simpleLogger.showThreadName=true

/*
 * Name:   SimpleSqlParser
 * Author: Bhaskar S
 * Date:   08/17/2024
 * Blog:   https://www.polarsparc.com
 */

package com.polarsparc.calcite;

import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParseException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class SimpleSqlParser {
    public static final Logger LOGGER = LoggerFactory.getLogger(SimpleSqlParser.class);

    public static void main(String[] args) {
        String goodQuery = "SELECT f2, f3, f5 FROM tbl WHERE f1 = 1 AND f4 = 'N'";
        String badQuery = "SELECT f2, f3, f5 FROM tbl WHERE f1 = ";

        parseSelectStatement(goodQuery);
        parseSelectStatement(badQuery);
    }

    static void parseSelectStatement(String sql) {
        LOGGER.info("------------------------- [ Start ] -------------------------");

        try {
            SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
            SqlNode node = parser.parseStmt();
            SqlSelect select = (SqlSelect) node;

            LOGGER.info("Query statement:");
            LOGGER.info(select.toString());

            assert select.getFrom() != null;

            LOGGER.info("Select from Table: " + select.getFrom().toString());

            LOGGER.info("List of selected fields:");
            select.getSelectList().forEach(sn -> LOGGER.info("-> {}", sn.toString()));
        }
        catch (SqlParseException e) {
            LOGGER.error(e.getMessage());
        }
        finally {
            LOGGER.info("------------------------- [  End  ] -------------------------");
        }
    }
}

/*
 * Name:   SimpleMemDbSql
 * Author: Bhaskar S
 * Date:   08/17/2024
 * Blog:   https://www.polarsparc.com
 */

package com.polarsparc.calcite;

import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Properties;

public class SimpleMemDbSql {
    public static final Logger LOGGER = LoggerFactory.getLogger(SimpleMemDbSql.class);

    static ContactSchema contactSchema = new ContactSchema();

    public static void main(String[] args) {
        CalciteConnection connection = getCalciteConnection();
        if (connection != null) {
            dataSetup();

            schemaSetup(connection, contactSchema);

            String sql = "SELECT c.lastName, c.email, c.mobile FROM cs.contacts AS c";
            queryAll(connection, sql);

            String sql2 = "SELECT c.email, c.mobile FROM cs.contacts AS c WHERE c.lastName = 'Doctor'";
            queryWhere(connection, sql2);

            try {
                connection.close();
            }
            catch (SQLException ignored) {}
        }
    }

    static void dataSetup() {
        contactSchema.contacts = new Contact[] {
            new Contact("Alice", "Doctor", "alice.d@space.com", "123-321-1000"),
            new Contact("Bob", "Carpenter", "bob.c@space.com", "234-432-2000"),
            new Contact("Charlie", "Painter", "charlie_p@space.com", "345-543-3000"),
            new Contact("Donna", "Plumber", "donna.p@space.com", "456-654-4000"),
            new Contact("Eve", "Dentist", "eve_d@space.com", "567-765-5000")
        };
    }

    static CalciteConnection getCalciteConnection() {
        Properties props = new Properties();
        props.setProperty("lex", "JAVA");

        CalciteConnection calciteConn = null;

        try {
            Connection conn = DriverManager.getConnection("jdbc:calcite:", props);
            calciteConn = conn.unwrap(CalciteConnection.class);
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }

        LOGGER.info("Calcite connection created !!!");

        return calciteConn;
    }

    static void schemaSetup(CalciteConnection connection, ContactSchema contactSchema) {
        SchemaPlus rootSchema = connection.getRootSchema();
        Schema schema = new ReflectiveSchema(contactSchema);

        LOGGER.info("Tables names: {}", schema.getTableNames());

        rootSchema.add("cs", schema);

        LOGGER.info("Calcite schema setup !!!");
    }

    static void queryAll(CalciteConnection connection, String sql) {
        try {
            LOGGER.info("Executing queryAll: {}", sql);

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                LOGGER.info("Last name: {}, Email: {}, Mobile: {}",
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3));
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    static void queryWhere(CalciteConnection connection, String sql) {
        try {
            LOGGER.info("Executing queryWhere: {}", sql);

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                LOGGER.info("Email: {}, Mobile: {}",
                        rs.getString(1),
                        rs.getString(2));
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    // The ReflectiveSchema requires that all the fields are public

    static public class ContactSchema {
        public Contact[] contacts;
    }

    static public class Contact {
        public String firstName;
        public String lastName;
        public String email;
        public String mobile;

        public Contact(String firstName, String lastName, String email, String mobile) {
            this.firstName = firstName;
            this.lastName = lastName;
            this.email = email;
            this.mobile = mobile;
        }
    }
}

/*
 * Name:   SimpleMemDbJoinSql
 * Author: Bhaskar S
 * Date:   08/17/2024
 * Blog:   https://www.polarsparc.com
 */

package com.polarsparc.calcite;

import org.apache.calcite.adapter.java.ReflectiveSchema;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.Properties;

public class SimpleMemDbJoinSql {
    public static final Logger LOGGER = LoggerFactory.getLogger(SimpleMemDbJoinSql.class);

    static PublicationSchema publicationSchema = new PublicationSchema();

    public static void main(String[] args) {
        CalciteConnection connection = getCalciteConnection();
        if (connection != null) {
            dataSetup();

            schemaSetup(connection, publicationSchema);

            String sql = "SELECT a.lastName, b.title, b.price FROM " +
                    "pub.authors AS a JOIN pub.books AS b ON a.id = b.authorId WHERE a.id = 2";
            queryJoin(connection, sql);

            try {
                connection.close();
            }
            catch (SQLException ignored) {}
        }
    }

    static void dataSetup() {
        publicationSchema.authors = new Author[] {
            new Author(1, "Alice", "Doctor"),
            new Author(2, "Bob", "Carpenter"),
            new Author(3, "Charlie", "Painter"),
        };

        publicationSchema.books = new Book[] {
            new Book("111", "Guide to Common Cold", 19.99f, 1),
            new Book("222", "How to Build Decks", 15.49f, 2),
            new Book("333", "Awesome Room Colors", 9.99f, 3),
            new Book("444", "How to Treat Cough", 17.49f, 1),
            new Book("555", "Fixing Wall Cracks", 11.99f, 2)
        };
    }

    static CalciteConnection getCalciteConnection() {
        Properties props = new Properties();
        props.setProperty("lex", "JAVA");

        CalciteConnection calciteConn = null;

        try {
            Connection conn = DriverManager.getConnection("jdbc:calcite:", props);
            calciteConn = conn.unwrap(CalciteConnection.class);
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }

        LOGGER.info("Calcite connection created !!!");

        return calciteConn;
    }

    static void schemaSetup(CalciteConnection connection, PublicationSchema publicationSchema) {
        SchemaPlus rootSchema = connection.getRootSchema();
        Schema schema = new ReflectiveSchema(publicationSchema);

        LOGGER.info("Tables names: {}", schema.getTableNames());

        rootSchema.add("pub", schema);

        LOGGER.info("Calcite schema setup !!!");
    }

    static void queryJoin(CalciteConnection connection, String sql) {
        try {
            LOGGER.info("Executing queryAll: {}", sql);

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                LOGGER.info("Author last name: {}, Book title: {}, Book price: {}",
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3));
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    // The ReflectiveSchema requires that all the fields are public

    static public class PublicationSchema {
        public Author[] authors;
        public Book[] books;
    }

    static public class Book {
        public String isbn;
        public String title;
        public float price;
        public int authorId;

        public Book(String isbn, String title, float price, int authorId) {
            this.isbn = isbn;
            this.title = title;
            this.price = price;
            this.authorId = authorId;
        }
    }

    static public class Author {
        public int id;
        public String firstName;
        public String lastName;

        public Author(int id, String firstName, String lastName) {
            this.id = id;
            this.firstName = firstName;
            this.lastName = lastName;
        }
    }
}

/*
 * Name:   SimpleCustomDbSql
 * Author: Bhaskar S
 * Date:   08/17/2024
 * Blog:   https://www.polarsparc.com
 */

package com.polarsparc.calcite;

import org.apache.calcite.DataContext;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.linq4j.Enumerable;
import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.schema.ScannableTable;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.sql.type.SqlTypeName;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.stream.Stream;

public class SimpleCustomDbSql {
    public static final Logger LOGGER = LoggerFactory.getLogger(SimpleCustomDbSql.class);

    public static void main(String[] args) {
        CalciteConnection connection = getCalciteConnection();
        if (connection != null) {
            schemaSetup(connection);

            String sql = "SELECT d.item, d.store, d.discount FROM dl.deals AS d";
            queryAll(connection, sql);

            try {
                connection.close();
            }
            catch (SQLException ignored) {}
        }
    }

    static CalciteConnection getCalciteConnection() {
        Properties props = new Properties();
        props.setProperty("lex", "JAVA");

        CalciteConnection calciteConn = null;

        try {
            Connection conn = DriverManager.getConnection("jdbc:calcite:", props);
            calciteConn = conn.unwrap(CalciteConnection.class);
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }

        LOGGER.info("Calcite connection created !!!");

        return calciteConn;
    }

    static void schemaSetup(CalciteConnection connection) {
        SchemaPlus rootSchema = connection.getRootSchema();
        Schema schema = new DealsCustomSchema();

        LOGGER.info("Tables names: {}", schema.getTableNames());

        rootSchema.add("dl", schema);

        LOGGER.info("Calcite schema setup !!!");
    }

    static void queryAll(CalciteConnection connection, String sql) {
        try {
            LOGGER.info("Executing queryAll: {}", sql);

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                LOGGER.info("Last name: {}, Email: {}, Mobile: {}",
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3));
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

    // Represents the data table in-memory
    static class DealsCustomTable extends AbstractTable implements ScannableTable {
        private final List<String> fieldNames = new ArrayList<>();
        private final List<SqlTypeName> fieldTypes = new ArrayList<>();

        private final List<Deal> dealsData = new ArrayList<>();

        public DealsCustomTable() {
            fieldNames.add("item");
            fieldNames.add("store");
            fieldNames.add("price");
            fieldNames.add("discount");

            fieldTypes.add(SqlTypeName.VARCHAR);
            fieldTypes.add(SqlTypeName.VARCHAR);
            fieldTypes.add(SqlTypeName.FLOAT);
            fieldTypes.add(SqlTypeName.INTEGER);

            dataSetup();
        }

        private void dataSetup() {
            dealsData.add(new Deal("iPhone 15 Pro", "Costco", 639.99f, 20));
            dealsData.add(new Deal("iPad 10.9 10th Gen", "BestBuy", 349.99f, 5));
            dealsData.add(new Deal("iWatch Series 9", "Target", 329.99f, 15));
        }

        // Return an enumerator to the stream of data rows (as individual column objects)
        @Override
        public Enumerable<Object[]> scan(DataContext context) {
            Stream<Object[]> fieldsStream = dealsData.stream().map(Deal::toObjectArray);
            return Linq4j.asEnumerable(fieldsStream.toList());
        }

        // Return the fields names and data types for a row in the table
        @Override
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
            List<RelDataType> dataTypes = fieldTypes.stream()
                    .map(typeFactory::createSqlType)
                    .toList();
            return typeFactory.createStructType(dataTypes, fieldNames);
        }
    }

    // Represents the schema that holds table(s) in-memory
    static class DealsCustomSchema extends AbstractSchema {
        @Override
        protected Map<String, Table> getTableMap() {
            return Collections.singletonMap("deals", new DealsCustomTable());
        }
    }

    // Represents a row of the table in-memory
    record Deal(String item, String store, float price, int discount) {
        public Object[] toObjectArray() {
            Object[] fields = new Object[4];
            fields[0] = this.item;
            fields[1] = this.store;
            fields[2] = this.price;
            fields[3] = this.discount;
            return fields;
        }
    }
}

cid:int,name:string,email:string,mobile:string
1,"Alice Doctor","alice.d@space.com","123-321-1000"
2,"Bob Carpenter","bob.c@space.com","234-432-2000"
3,"Charlie Painter", "charlie_p@space.com", "345-543-3000"

{
  "version": "1.0",
  "defaultSchema": "customers",
  "schemas": [
    {
      "name": "customers",
      "type": "custom",
      "factory": "org.apache.calcite.adapter.file.FileSchemaFactory",
      "operand": {
        "directory": "customers"
      }
    }
  ]
}

/*
 * Name:   SimpleFileSql
 * Author: Bhaskar S
 * Date:   08/17/2024
 * Blog:   https://www.polarsparc.com
 */

package com.polarsparc.calcite;

import org.apache.calcite.jdbc.CalciteConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

public class SimpleFileSql {
    public static final Logger LOGGER = LoggerFactory.getLogger(SimpleFileSql.class);

    public static void main(String[] args) {
        CalciteConnection connection = getCalciteConnection();
        if (connection != null) {
            // The column names *MUST* be enclosed in double quotes
            String sql = "SELECT \"name\", \"email\", \"mobile\" FROM customers";
            queryAll(connection, sql);

            try {
                connection.close();
            }
            catch (SQLException ignored) {}
        }
    }

    static CalciteConnection getCalciteConnection() {
        CalciteConnection calciteConn = null;

        try {
            Connection conn = DriverManager.getConnection("jdbc:calcite:model=target/classes/model.json");
            calciteConn = conn.unwrap(CalciteConnection.class);
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }

        LOGGER.info("Calcite connection created !!!");

        return calciteConn;
    }

    static void queryAll(Connection connection, String sql) {
        try {
            LOGGER.info("Executing queryAll: {}", sql);

            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                LOGGER.info("Name: {}, Email: {}, Mobile: {}",
                        rs.getString(1),
                        rs.getString(2),
                        rs.getString(3));
            }
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }
}