Support URIs on ATTACH

[davidmartos96]

Hello!
After changing the stack to the latest versions based on sqlite3 v3 (powersync + sqlite_async), we've noticed that some of our read only ATTACHed databases could cause locking issues to the writer isolate if we don't properly ATTACH them with the read only flag ?mode=ro.
https://sqlite.org/uri.html

We still need to isolate to issue to a reproducible case, so I'll update once I have more info, but what we found is that if we mark the database that we attach to as read only with ?mode=ro, it resolves the locking issues. We are using WAL in the main database and sqlite3mc if that matters.

What I would propose is to change the default compilation flags and set SQLITE_USE_URI=1 so that the URI syntax is supported when ATTACHing.

Another option would be using the runtime configuration with sqlite3_config(SQLITE_CONFIG_URI, 1), which is not currently in the bindings. I've tried to expose it, but I wasn't able to make it work correctly, as it needs to be called before sqlite3_initialize, per the sqlite3_config function docs.

[simolus3]

as it needs to be called before sqlite3_initialize, per the sqlite3_config function docs.

I believe we only call sqlite3_initialize when a database connection is opened. So if you override NativeIsolateOpenFactory.openNativeConnection to call sqlite3_config, that should work.

But I also see that it's very difficult to do that correctly at the moment. If you use @Native externals to define sqlite3_config, you'd have to add a @DefaultAsset('package:sqlite3/src/ffi/libsqlite3.g.dart') annotation to the enclosing Dart file to make sure you use the same SQLite library as the sqlite3 package.

I could add sqlite3_config to package:sqlite3/unstable/ffi_bindings.dart if that helps, or expose typed configuration options (like we do for sqlite3_db_config).

What I would propose is to change the default compilation flags and set SQLITE_USE_URI=1 so that the URI syntax is supported when ATTACHing.

I'm kind of worried about this potentially being a breaking change, e.g. if someone manages to use file names that would be valid URIs.

[davidmartos96]

@simolus3 I was getting MISUSE errors when I added the sqlite3_config function and used it from the multi isolate project with powersync. Maybe I did something wrong in the implementation.