Indexed DB VFS doesn't work with single file databases

[Mathias12345]

Hello,

I have a problem with default indexed DB on WebGL Unity - idbvfs_register(1) in SQLiteExtension.cs SQLite3.Open() results with OK. But opening first table fails with error no such table (in first call to sqlite3LocateTable). When I change idbvfs_register(1) to idbvfs_register(0) it loads correctly.

Do you see any reason why this could happen? Can you change makeDefault flag configurable from outside or can you help me with other solution?

Thanks for help!

[gilzoide]

Hey @Mathias12345 , thanks for the report.

That's really weird, I don't see yet why this would happen. Making IDBVFS the default VFS is pretty much what we want in WebGL builds, or else no data will be saved at all as far as I remember.

Is this database being created in code, or are you using a SQLiteAsset? Did you create the table prior to accessing it? If you clear your Indexed DB for the page and reopen it, do you still have the same error?

[Mathias12345]

Hello. I am using database as read only. I download .sqlite file from cdn and save it manually to path via File.WriteAllBytes. Path is combined with Application.persistentDataPath and looks like this file:///idbfs/888c7743b070f4d9428edbd132f864a1/data.sqlite.

Then create SQLConnection at that path like new SQLiteConnection(databasePath, SQLiteOpenFlags.ReadOnly, false);.

I have tried clearing cache and also deleting that IndexedDB in browser but it didn't help. My guess is that it is somehow connected where the DB is actually stored. So when I try to open it I actually create new (empty) one.

That database in browser looks like this:

[gilzoide]

Hey @Mathias12345 thanks for the additional info.

I think I know the problem. IDBVFS uses a directory with one file for each database page to avoid having to maintain everything in memory at once. It doesn't handle database in single files right now and it totally should, so this is a bug in IDBVFS implementation.

To fix your project right now, you can specify which VFS to use in your SQLiteConnection using the SQLiteConnection(SQLiteConnectionString connectionString) constructor. Pass "unix" to the SQLiteConnectionString's vfsName for this connection and it should work (so it won't use the default VFS which is IDBVFS). Here's an untested example:

var connectionString = new SQLiteConnectionString(
  Application.persistentDataPath + "/data.sqlite",
  SQLiteOpenFlags.ReadOnly,
  true,
  vfsName: "unix"
);
var db = new SQLiteConnection(connectionString);

Another way would be deserializing the file into the connection. In-memory databases don't use any VFS. Here's an untested example:

var bytes = File.ReadAllBytes(Application.persistentDataPath + "/data.sqlite");
var db = new SQLiteConnection();
db.Deserialize(bytes);

[Mathias12345]

Hello, I have tried first approach with "unix" VFS and it worked.

Thanks for help!

[gilzoide]

Nice, I'm glad it worked for you!

I'll keep this Issue opened until we fix IDBVFS to support the use case of opening single files, I'm sure someone else will stumble upon it eventually.