KWeb allowed me to write a public playground for Apache Calcite database tool in ~50 lines of code!

[GavinRay97]

Apache Calcite is a SQL parser and query planning/optimization engine that allows you to query virtually anything:

• https://calcite.apache.org/
• https://github.com/apache/calcite

I've wanted to host a public playground where each user gets an isolated, in-memory database session and can submit queries to the server and see results

I thought this would be a lot of work to build, but with kweb I was able to do it in ~50 lines, not counting imports/whitespace!

Thanks for such a useful tool 🙂

import com.github.benmanes.caffeine.cache.Caffeine
import com.github.benmanes.caffeine.cache.LoadingCache
import kweb.button
import kweb.div
import kweb.form
import kweb.p
import kweb.state.KVar
import kweb.textArea
import org.apache.calcite.adapter.jdbc.JdbcCatalogSchema
import org.apache.calcite.config.CalciteConnectionProperty
import org.apache.calcite.config.Lex
import org.apache.calcite.jdbc.CalciteConnection
import org.h2.jdbcx.JdbcDataSource
import org.jooq.SQLDialect
import org.jooq.impl.DSL
import us.fatehi.chinook_database.ChinookDatabaseUtils
import us.fatehi.chinook_database.DatabaseType
import java.sql.DriverManager
import java.util.Properties
import javax.sql.DataSource
import kotlin.time.Duration.Companion.minutes
import kotlin.time.toJavaDuration

fun main() {
    val query = KVar("SELECT * FROM chinook.public.Album LIMIT 5")
    val queryResults = KVar("")

    Kweb(port = 16097, plugins = listOf(fomanticUIPlugin)) {
        doc.body {
            form {
                p().text("SQL:")
                textArea().also {
                    it.value = query
                    it.text = query
                }
                button { text("Execute") }.on.click {
                    queryResults.value = GlobalDatabaseManager.schemas[sessionId].let {
                        DSL.using(it.calciteConnection, SQLDialect.POSTGRES).fetch(query.value).formatHTML()
                    }
                }
            }
            p().text("Results:")
            div().also { it.innerHTML(queryResults) }
        }
    }
}

object ChinookDatabaseService {
    private const val H2_DB_PREFIX = "jdbc:h2:mem:"

    private val connectionProperties = Properties().apply {
        setProperty("DB_CLOSE_DELAY", "-1")
        setProperty("MODE", "PostgreSQL")
        setProperty("DATABASE_TO_LOWER", "TRUE")
        setProperty("DEFAULT_NULL_ORDERING", "HIGH")
    }

    fun createChinookDatabase(id: String): DataSource {
        val jdbcUrl = H2_DB_PREFIX + id
        DriverManager.getConnection(jdbcUrl, connectionProperties).use { conn ->
            ChinookDatabaseUtils.createChinookDatabase(DatabaseType.postgresql, conn)
            val rs = conn.metaData.getTables(null, "public", null, null)
            val hasTables = rs.first()
            assert(hasTables) { "No tables found in the database, failed to initialize the DB" }
            return JdbcDataSource().apply { setURL(jdbcUrl) }
        }
    }
}


class SessionScopedCalciteSchema(sessionId: String) {
    val calciteConnection = initCalciteConnection()

    init {
        val datasource = ChinookDatabaseService.createChinookDatabase(sessionId)
        val rootSchema = calciteConnection.rootSchema
        val catalog = JdbcCatalogSchema.create(rootSchema, "chinook", datasource, null)
        rootSchema.add("chinook", catalog)
    }

    private fun initCalciteConnection(): CalciteConnection {
        Class.forName(org.apache.calcite.jdbc.Driver::class.java.name)
        DriverManager.registerDriver(org.apache.calcite.jdbc.Driver())
        val properties = Properties()
        properties.setProperty(CalciteConnectionProperty.FUN.camelName(), "standard,postgresql")
        properties.setProperty(CalciteConnectionProperty.LEX.camelName(), Lex.JAVA.name)
        return DriverManager.getConnection("jdbc:calcite:", properties).unwrap(CalciteConnection::class.java)
    }
}


object GlobalDatabaseManager {
    private val EXPIRE_AFTER = 5.minutes

    val schemas: LoadingCache<String, SessionScopedCalciteSchema> =
        Caffeine.newBuilder()
            .recordStats()
            .expireAfterAccess(EXPIRE_AFTER.toJavaDuration())
            .build {
                println("Creating a new SessionScopedCalciteSchema for $it")
                SessionScopedCalciteSchema(it)
            }
}

[sanity]

Thanks for letting us know!