Godot SQLite - Current issues

[2shady4u]

As many of you know, I'm currently, together with @chippmann and @piiertho , trying to get the Godot SQLite Kotlin plugin ready for release on the Godot Asset Library. I've had a lot of discussions with all Godot-kotlin developers on missing or faulty issues and features that still have to be rectified or added.

Most of these discussions get lost somewhere on Discord and it is a pain to dig them up all the time to remind myself what was said by who and when. I discussed this with @chippmann and we agreed that it might be easier for everyone involved to just open an issue on Github for easier backtracking and discussion.

At the moment there a couple of issues that stop Godot SQLite Kotlin from being released.
Some of them aren't directly connected to Godot Kotlin (this repo) but i added them just for completeness' sake.

High priority

• Building for Linux/X11 is currently impossible due to Kotlin throwing an undefined type error 'off64_t'. This is quite strange because the Godot SQLite C++ library doesn't have any problem compiling for X11 and only uses conventional compiler flags. Probably the solution is just adding a single line somewhere, but it has eluded everyone until now.
• A race condition exists in one of the function that wasn't any problem in the C++ library. This race conditions has following nature:

fun callback(closure: COpaquePointer?, argc: Int, argv: CPointer<CPointerVar<ByteVar>>?, azColName: CPointer<CPointerVar<ByteVar>>?): Int {
    val columnDictionary = Dictionary()
    /* Get a reference to the instanced object */
    val stableRef = closure?.asStableRef<SQLiteWrapper>()
    val obj = stableRef?.get()
    val stmt: CPointer<sqlite3_stmt>? = sqlite3_next_stmt(obj?.db, null)
    var columnValue: Variant

    /* Add result to query_result Array */
    obj?.queryResult?.append(Variant(columnDictionary))

    /* cleanup! */
    stableRef?.dispose()

    return 0
}

@RegisterFunction
fun query(queryString : String) : Boolean {
   /* Clear the previous query results */
   queryResult.clear()

   val stableRef = StableRef.create(this)
   val voidPointer = stableRef.asCPointer()
   /* Execute SQL statement */
   memScoped {
      val q: CPointerVar<ByteVar> = alloc()
      rc = sqlite3_exec(db, queryString, staticCFunction(::callback), voidPointer, q.ptr)
      zErrMsg = q.value
   }
   /* cleanup! */
   stableRef.dispose()
}

@RegisterFunction
fun selectRows() : GDArray {
   /* Make the queryString here... */
   query(queryString)
   return queryResult <- JUST RETURNS AN EMPTY GDARRAY HERE!
}

Thus when returning the 'queryResult' it is just an empty GDArray instead of containing the rows that should've been captured by the callback function 'callback'.
I expect this to be a race condition, meaning that the return statement returns an empty GDArray that didn't get filled by the callback, but I'm unsure why this never happens in the C++ Godot SQLite library.

Lower priority

• Kotlin functions such as 'openDatabase' need to have an alias that follows Godot function conventions, such as 'open_database' such that the plugin is compatible, without any code modification, with the Godot SQLite plugin written in C++.

Non-Kotlin issues (added for completeness's sake)

• Android only allows databases (and other data structures like JSON for that matter) to be created inside of the data-folder (user:// in Godot) and thus the OS needs to be checked and adequate precautions need to be taken when the OS is found to be Android.

Hopefully opening this issue is ok for all parties involved. It would most definitely help me immensely to retain some oversight on the current situation.

[piiertho]

For empty GDArray, maybe is obj or the contained queryResult is null. As there are some null checks in calls, it won't append anything if any one of them is null