SQlite3 embedded with Raylib projects

[AkrilaMayNotBeAvailable]

I use SQlite for (mostly) string data storing, that I use to load other stuff with Raylib. It works with Linux and Windows build normally but when I tried compiling here I found the following problem:

• Seems like it can't find the Database file in ../src/assets/ (it is a .db file) when starting the application
• SQlite3 lib seems to be compiled within the project, as it doesn't return me any error at all
• Assets loaded directly through Raylib seems to load normally

Do you have any idea about why it isn't finding the database file? Is SQlite maybe not supported on android build?

Other than that, everything seems to work normally! Thanks for developing this tool.

[Bigfoot71]

I'm not very familiar with SQLite, but if it's a file reading issue, I can certainly help you.

The first thing that comes to my mind is that your library might not be using Android's AssetManager. Is it possible for you to send me a reproducible example that I can try on my side?

If my intuition is good, I should be able to offer you a solution fairly quickly.

[AkrilaMayNotBeAvailable]

Hello there! Sorry for taking so long. You can check those files, I made them as a example, but my usual framework is pretty much exactly like them.

https://github.com/AkrilaMayNotBeAvailable/Example_Files/tree/main/SQlite%20embedded%20with%20Raylib

On Raymob project my setup is:

Raymob_root/app/src/main/
  > assets/Database/file.db
  > cpp/*.c and *.h files

[Bigfoot71]

Thank you for the example. After some research, it seems that it might be possible to load an SQLite database directly from memory. So, I tried to load the file using the LoadFileData() function from raylib, which uses its AssetManager instance. The file is loaded successfully, but then I receive this error:

com.raylib.raymob     I  FILEIO: [Database/example.db] File loaded successfully
com.raylib.raymob     E  Error executing SQL script: near "SQLite": syntax error

Here's my code, if you are more familiar with sqlite3 than I am, you might see what's wrong:

int ExecuteQuery(const char *fp, const char *query, char *str){
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc;

    // Load the database content from the asset using LoadFileData
    unsigned int byteReaded = 0;
    unsigned char *dbContent = LoadFileData(fp, &byteReaded);
    if (!dbContent) {
        TraceLog(LOG_ERROR, "Error loading asset: %s", fp);
        return -1;
    }

    // SQLite function to open an in-memory database
    rc = sqlite3_open(":memory:", &db);
    if (rc != SQLITE_OK) {
        TraceLog(LOG_ERROR, "Error opening in-memory database: %s", sqlite3_errmsg(db));
        UnloadFileData(dbContent); // Free allocated memory
        return rc; // SQLite Error code
    }

    // Execute the SQL script from the asset (?)
    rc = sqlite3_exec(db, (const char *)dbContent, 0, 0, 0);
    UnloadFileData(dbContent); // Free allocated memory

    if (rc != SQLITE_OK) {
        TraceLog(LOG_ERROR, "Error executing SQL script: %s", sqlite3_errmsg(db));
        sqlite3_close(db); // Close in-memory database if error
        return rc; // SQLite Error code
    }

    // Prepare the query
    rc = sqlite3_prepare_v2(db, query, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        TraceLog(LOG_ERROR, "Error preparing query: %s", sqlite3_errmsg(db));
        sqlite3_close(db); // Close in-memory database if error
        return rc; // SQLite Error code
    }

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        const unsigned char *phrase = sqlite3_column_text(stmt, 0); // Gets string from column 0
        // While phrase != 0
        if (phrase) {
            snprintf(str, STRING_SIZE, "%s", phrase);
        }
    }

    // Something went wrong
    if (rc != SQLITE_DONE) {
        TraceLog(LOG_ERROR, "Error while executing query: %s", sqlite3_errmsg(db));
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);

    return rc; // SQLite error code
}

As additional information, the attempt to open the file using fopen() in your version of FileChecking() fails for an unknown reason, as it should normally correspond to the fopen() overload in raylib for Android.

So, I then wanted to replace your FileChecking() function with raylib's FileExists() function, but I discovered that it directly uses access without any "overload" to check in the assets. So, it will likely be an addition to make in raylib.

By the way, it's normal that you didn't get any error logs because printf and fprintf won't work. You should use the Android log library, and the TraceLog function in raylib will do the job for you.

[AkrilaMayNotBeAvailable]

Hello again, thanks for helping me out with this, so about your code, I did some research as I don't know that much about SQLite in-depth functions, but I can point this:

---

This is creating a empty in-memory database, not loading the one by LoadFileData()

rc = sqlite3_open(":memory:", &db);

---

This is actually a wrapper function for the whole code ( sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize() ) under it. It should receive a query data as 2nd parameter, so it probably fails here as what is being passed as parameter isn't a query.

rc = sqlite3_exec(db, (const char *)dbContent, 0, 0, 0);

You can check the exact same process with the query by commenting this function, it will return:

Error preparing query: no such table: example

---

But anyway those errors come from it not actually being loaded by SQLite.

So it is possible to load it through Raylib LoadFileData function, but then it should be accessible by sqlite3_open() to load data somehow, but this function takes a file path (with a file) to do a bunch of stuff...

I made some unsuccessful tests with sqlite3_open_v2() in which there is some additional parameters for VFS and flags for stuff but I just couldn't make it work. Do you have any idea? I'm really not used to android file system, sorry about that.

Also, thanks for the TraceLog tip.

[Bigfoot71]

Unfortunately, I don't have many ideas as this seems more related to SQLite, and I'm not very familiar with it. I have two other potential solutions that should work but may not be the best.

The first solution

It involves loading the file from assets and then writing it to GetAndroidApp()->activity->internalDataPath, which is a secure directory reserved for your application. The path to this directory will then be accessible without the AssetManager.

I've tried it, and it works (code written hastily but functional):

// Copy database from assets to internal data
void CopyFileToInternalData(const char *src, const char *dst) {
    unsigned int bytesRead = 0;
    unsigned char *data = LoadFileData(src, &bytesRead);

    if (data != NULL) {

        // Open the destination file in binary write mode
        FILE *fileDst = fopen(dst, "wb");

        if (fileDst != NULL) {

            // Write data to the destination file
            size_t bytesWritten = fwrite(data, 1, bytesRead, fileDst);

            if (bytesWritten == bytesRead) {
                TraceLog(LOG_INFO, "Successful copy to %s\n", dst);
            } else {
                TraceLog(LOG_ERROR, "Copy failed to %s\n", dst);
            }

            // Close the destination file
            fclose(fileDst);

        } else {
            TraceLog(LOG_ERROR, "Unable to open destination file %s\n", dst);
        }

        // Free the memory allocated for data
        free(data);

    } else {
        TraceLog(LOG_ERROR, "Error reading source file %s\n", src);
    }
}

// Copy and load from internal data path
ERRORCODE_e DataIntegrity(char *string){
    const char *database = "Database/example.db";

    const char *destination = TextFormat(
            "%s/%s", GetAndroidApp()->activity->internalDataPath, "example.db");

    CopyFileToInternalData(database, destination);

    const char *query = "SELECT str FROM example";
    if(ExecuteQuery(destination, query, string)){
        return DATABASE_NOT_CONNECTED;
    }

    return ERROR_NONE;
}

Result:

com.raylib.raymob     I  FILEIO: [Database/example.db] File loaded successfully
com.raylib.raymob     I  Successful copy to /data/user/0/com.raylib.raymob/files/example.db

## Then the file is loaded by sqlite successfully ##

The second solution

It would be to use SQLiteAssetHelper, which is a Java library. In this case, you would write the methods you need on the Java side in the NativeLoader class and call these Java methods from C/C++ using raymob's functions:

RLAPI JNIEnv *AttachCurrentThread(void);
RLAPI void DetachCurrentThread(void);
RLAPI const jobject GetNativeLoaderInstance(void);

You can see the already existing gists for raymob which shows how to call Java methods from C/C++: https://gist.github.com/Bigfoot71/2c89864354a9254ce555f2eca1961590

Here is a link that might help you write the methods on the Java side: https://stackoverflow.com/a/49768006

You can integrate it via Gradle easily if you use Android Studio or see their repo: https://github.com/jgilfelt/android-sqlite-asset-helper

Perhaps there's a similar alternative to SQLiteAssetHelper in C/C++, but I'm not aware of it.

[AkrilaMayNotBeAvailable]

Thank you for the solutions! The first one worked nicely for me, even if it isn't the best solution, it's okay. For the second one it may take a while for me to try it though... but anyway, that actually solves my problem. Feel free to close this issue :)