Custom SQLite functions

[mrVin99]

I'm facing a bit of a problem and I'm not sure where exactly the issue lies, but my guess is that it has to do with the way Hiraki creates a connection.

I'm trying to register a custom SQLite function:

object CleanDiacritics : Function() {
    private fun cleanDiacritics(input: String): String {
        return input.map { char ->
            when (char) {
                'á', 'à', 'ä', 'â', 'ã' -> 'a'
                'é', 'è', 'ë', 'ê' -> 'e'
                'í', 'ì', 'î' -> 'i'
                'ó', 'ò', 'ö', 'ô', 'õ' -> 'o'
                'ú', 'ù', 'ü', 'û' -> 'u'
                'ç' -> 'c'
                'Á', 'À', 'Ä', 'Â', 'Ã' -> 'A'
                'É', 'È', 'Ë', 'Ê' -> 'E'
                'Í', 'Ì', 'Î' -> 'I'
                'Ó', 'Ò', 'Ö', 'Ô', 'Õ' -> 'O'
                'Ú', 'Ù', 'Ü', 'Û' -> 'U'
                'Ç' -> 'C'
                else -> char
            }
        }.joinToString("")
    }

    override fun xFunc() {
        val input = value_text(0)
        result(cleanDiacritics(input))
    }
}

This custom function does work, but only when I use a connection created by DriverManager.

fun testCleanDiacritics() {
    val url = "jdbc:sqlite:run/database/construction.db"

    try {
        val connection = DriverManager.getConnection(url)
        Function.create(connection, "clean_diacritics", CleanDiacritics)
        testSelectByName("hidraulica", connection)
    } catch (e: SQLException) {
        e.printStackTrace()
    }
}

fun testSelectByName(name: String, connection: Connection) {
    try {
        val sql = """
                SELECT * FROM construction_catalog_category
                WHERE clean_diacritics(construction_catalog_category_name) LIKE ?
            """.trimIndent()
        val preparedStatement = connection.prepareStatement(sql)
        preparedStatement.setString(1, "%$name%")

        val resultSet = preparedStatement.executeQuery()
        while (resultSet.next()) {
            val id = resultSet.getInt("construction_catalog_category_id")
            val categoryName = resultSet.getString("construction_catalog_category_name")
            val categoryType = resultSet.getString("construction_catalog_category_type")

            Logger.info("ID: $id, Name: $categoryName, Type: $categoryType")
       // logged result: ID: 12, Name: Hidráulica, Type: Tubos e Conexões
        }

        resultSet.close()
        preparedStatement.close()

    } catch (e: SQLException) {
        println("Failed to execute query: ${e.message}")
        e.printStackTrace()
    }
}

But when running this same function with a connection from Hiraki, i get: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such function: clean_diacritics)

class Database(
    uri: String = DEFAULT_DB,
    poolConfig: Config = Config(),
) {
    private val dataSource: HikariDataSource
    private val url = parseConnURI(uri)

    init {
        if (uri != DEFAULT_DB) prepareBaseDir()

        Class.forName("org.sqlite.JDBC")

        val config = HikariConfig().apply {
            jdbcUrl = url
            maximumPoolSize = poolConfig.maximumPoolSize
            minimumIdle = poolConfig.minimumIdle
            idleTimeout = poolConfig.idleTimeout
            connectionTimeout = poolConfig.connectionTimeout
            maxLifetime = poolConfig.maxLifetime
            connectionTestQuery = poolConfig.connectionTestQuery
        }

        dataSource = runCatching {
            HikariDataSource(config)
        }.getOrElse {
            throw RuntimeException("Failed to connect to database: $uri", it)
        }

        Function.create(conn().unwrap(SQLiteConnection::class.java), "clean_diacritics", CleanDiacritics)

        //testing my custom function
        testSelectByName("hidraulica", conn())
    }

    fun conn(): Connection {
        return dataSource.connection
    }
)

Does anyone have an idea for a workaround I can apply to make this work? Thanks.