How to Extend DataFrame Library for Custom SQL Database Support: Example with HSQLDB

plugins {
    id("org.jetbrains.kotlinx.dataframe") version "$dataframe_version"
}

dependencies {
    implementation("org.jetbrains.kotlinx:dataframe:$dataframe_version")
    implementation("org.hsqldb:hsqldb:$version")
}

java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/demodb --dbname.0 testdb

/**
 * Represents the HSQLDB database type.
 *
 * This class provides methods to convert data from a ResultSet to the appropriate type for HSQLDB,
 * and to generate the corresponding column schema.
 */
public object HSQLDB : DbType("hsqldb") {
    override val driverClassName: String
        get() = "org.hsqldb.jdbcDriver"

    override fun convertSqlTypeToColumnSchemaValue(tableColumnMetadata: TableColumnMetadata): ColumnSchema? {
        return null
    }

    override fun isSystemTable(tableMetadata: TableMetadata): Boolean {
        val locale = Locale.getDefault()
        fun String?.containsWithLowercase(substr: String) = this?.lowercase(locale)?.contains(substr) == true
        val schemaName = tableMetadata.schemaName
        val name = tableMetadata.name
        return schemaName.containsWithLowercase("information_schema") ||
            schemaName.containsWithLowercase("system") ||
            name.containsWithLowercase("system_")
    }

    override fun buildTableMetadata(tables: ResultSet): TableMetadata =
        TableMetadata(
            tables.getString("TABLE_NAME"),
            tables.getString("TABLE_SCHEM"),
            tables.getString("TABLE_CAT"),
        )

    override fun convertSqlTypeToKType(tableColumnMetadata: TableColumnMetadata): KType? {
        return null
    }
}

const val URL = "jdbc:hsqldb:hsql://localhost/testdb"
const val USER_NAME = "SA"
const val PASSWORD = ""


fun removeTable(con: Connection): Int {
    val stmt = con.createStatement()
    return stmt.executeUpdate("""DROP TABLE orders""")
}

fun createAndPopulateTable(con: Connection) {
    val stmt = con.createStatement()
    stmt.executeUpdate(
        """CREATE TABLE IF NOT EXISTS orders (
                        id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                        item VARCHAR(50) NOT NULL,
                        price DOUBLE NOT NULL,
                        order_date DATE
                    );
                """.trimIndent()
    )

    stmt.executeUpdate(
        """INSERT INTO orders (item, price, order_date)
                        VALUES ('Laptop', 1500.00, NOW())""".trimIndent()
    )

    stmt.executeUpdate(
        """INSERT INTO orders (item, price, order_date)
                        VALUES ('Smartphone', 700.00, NOW())""".trimIndent()
    )
}

@DataSchema
interface Orders {
    val id: Int
    val item: String
    val price: Double
    val orderDate: java.util.Date
}

fun main() {
    DriverManager.getConnection(URL, USER_NAME, PASSWORD).use { con ->
        createAndPopulateTable(con)

        val df = con
            .readDataFrame("SELECT * FROM orders", dbType = HSQLDB)
            .rename { all() }.into { it.name.lowercase(Locale.getDefault()).toCamelCaseByDelimiters(DELIMITERS_REGEX) }
            .cast<Orders>(verify = true)

        df.filter { it.price > 800 }.print()

        removeTable(con)
    }
}