Runtime SQLiteException IO error on WebGL platform

[tealcwu]

When I build and run WebGL build, it reports disk IO exception as below: "SQLiteException: disk I/O error at SQLite.SQLite3.Prepare2 (System.IntPtr db, System.String query) [0x00000] in <00000000000000000000000000000000>:0 "

I just testing WebGL with the demo in the readme to show player info in texts. But it failes now.

The Unity version I'm using is 2022.1.20f1. And I have selected the 3 files in Plugins/sqlite-amalgamation for WebGL platform.

Please refer to the log as below.

[gilzoide]

Hey @tealcwu, thanks for the report.

So, yeah, WebGL doesn't work with database files yet, we need a custom VFS for this first.

I started playing with a VFS based on the Indexed DB (#24) at some point. It actually works, but the problem is that saving data is too slow, a simple write query takes \~20ms, which is almost the entire frame time when playing at 30FPS. Now, that is the case when using the rollback journal, turning journaling off or using in-memory journals perform ok (5~10ms).

I also want to try using SQLite's own Wasm/JS VFSs at some point. OPFS-VFS will likely perform better than my idbvfs.

And I have selected the 3 files in Plugins/sqlite-amalgamation for WebGL platform.

You don't need to do this on Unity 2022, this problem arised on Unity 2020, likely on 2021.2 as well, but 2021.3+ should be fine.

[tealcwu]

@gilzoide Hey Gil, thanks for your effort!

So you need more testing on different VFS strategies, right?

[gilzoide]

Hey @tealcwu, I found a much better way to handle I/O in idbvfs, making it quite faster. If you'd like to try the code in branch idbvfs, feel free to do so. Please share any feedback if you try it: if it works, if it's slow or not, etc...

[tealcwu]

Sure, Thanks Gil!

[tealcwu]

@gilzoide Test with idvfs branch in the same project. The new exception is cannot find table as below. WebGL.framework.js.gz:3 SQLiteException: no such table: Player at SQLite.SQLite3.Prepare2 (System.IntPtr db, System.String query) [0x00000] in <00000000000000000000000000000000>:0

This project works fine on Windows platform.

I'm sing Unity 2022.1.20f1 and Chrome 129.0.6668.70 (64bit).

[gilzoide]

Hmm, weird. Without a closer look at your project it's hard to tell what's going on.

Maybe the data was not persisted between sessions. Maybe the database wasn't opened correctly, even though the open call didn't throw any exceptions. Maybe the app logic in WebGL just didn't run the create table, possibly because of an exception thrown before it could happen in the same flow. Anyway, lots of possibilities.

There's a simple REPL sample in this project that you can import from UPM, that's what I'm testing this package with, running SQL manually and seeing that data is persisted correctly between sessions.

[tealcwu]

@gilzoide Clean the project and reimport idbvfs, then it works now!

[gilzoide]

Nice \o/ Thanks for testing it out ^^

I'll keep this issue opened until we merge #24.