native sqlite extension

[carlsverre]

Currently SQLSync wraps SQLite in order to carefully control how SQLite behaves and improve the user experience of using SQLSync. But this makes SQLSync harder to port to new SQLite environments.

This feature request is to build a version of SQLSync that can run entirely within SQLite as a native extension.

Things to figure out:

• What does the mutation/reducer layers look like? If the reducer continues to be Wasm, it can run internally; alternatively SQLSync can somehow call out to the host to run mutations when needed.
• How easy is it for SQLSync to have a private timelines table if it's entirely inside the SQLite engine?
• Will need to add some pragmas/custom functions to inspect and control SQLSync from the host

[carlsverre]

The initial goal is to just get a loadable extension that works with the native run-time loadable extension system. This will ideally make SQLSync work with the largest amount of targets.

Then, work can begin on making it easier for folks running in the browser to use SQLite extensions. Some projects/prior work that is relevant to this effort:

• https://github.com/llimllib/wasm_sqlite_with_stats
• https://github.com/asg017/sqlite-path
• https://github.com/asg017/sqlite-lines
• https://github.com/sql-js/sql.js
• https://sqlite.org/wasm/doc/trunk/index.md

It would be very cool to look into dynamically loading extensions at runtime via Wasm dynlinking, and ideally working with https://github.com/nalgeon/sqlean to support a large library of extensions.

[carlsverre]

Research notes as of Jan 24 2024

sqlite-loadable-rs

• seems like a fairly mature library for implementing extensions using rust
• vfs support is only available in a PR

Just do it manually?

• Current SQLSync code already knows how to register itself as a VFS
• The only missing pieces are: compiling to a shared module and supporting the SQLite extension registration function calls
• I can probably just write this part in C
• Or just start building my own lightweight wrapper with direct usage of bindgen

Vlcn bindings

• Matt released a very clean set of SQLite bindings
• Seems to expose most (all?) of the SQLite API
• nostd compatible
• currently seems to require rust nightly?

VFS stacking

• SQLite has the concept of VFS stacking (or shimming)
• At any point SQLite has a "default" vfs
• You can call sqlite3_vfs_find(0) to get the current default VFS
• Then, if you want to write a "higher order vfs" the recommended approach is to wrap and then replace the current default VFS
• Good example of this is the checksum extension

SQLite batch write mode (atomic write mode)

• SQLite VFS's can implement a IOCAP + FCNTL which signals support for batch atomic writes
• need to set the SQLITE_IOCAP_BATCH_ATOMIC bit
• During a batch write SQLite will make no other calls on the same file object except for calls to xWrite and xFileControl
• this allows the VFS to internally optimize batch writes to the underlying file, but must guarantee that the writes are committed atomically to durable storage (or rolled back completely) upon receiving the COMMIT or ROLLBACK FCNTL's