SQLite Forum

Native support for pluggable backends in SQLite
Login
I like this idea. SQLite4 has a similar idea, but SQLite3 doesn't have any so far. However, due to how the way SQLite3 storage works (and due to other things), I have a few suggestions:

* The comparison function will presumably need to be set for the cursor (when you are opening it), I would expect. (This is only applicable for cursors that do not use 64-bit integer keys, though.)

* Each "KV table" has either 64-bit integer keys (which always uses numerical order, I think) and arbitrary sequences of bytes as data, or the arbitrary byte sequence used as data is also used as the key and no separate key is stored (in which case the comparison function is needed, I think). This could be specified perhaps when creating the table, and maybe it is also needed when opening the cursor. This can be specified as flags, either `SQLITE_KV_TABLE` (for ordindary tables) or `SQLITE_KV_INDEX` (for indexes and WITHOUT ROWID tables). This means that the types for `xCursorPut`, `xCursorSeek`, and `xCursorCurrent` will need to be corrected.

* For accessing the data stored in the header of the standard SQLite file format, you could have a method that is given the number which is the offset in the standard SQLite format, for example 68 to read or set the application ID, or 56 for the text encoding. Some of these header fields will not be applicable to custom storage engines.

* It shouldn't care about filename extensions (different programs might use different filenames, and some might not use any filename extension at all; not everything uses "`.sqlite`", and SQLite itself doesn't care), although it will need a name, like the VFS needs a name to refer to it. Which one to use can be specified as a URI parameter, or by trying different storage implementations until one works. (If multiples are loaded, and one finds that the file is not in its format, it can return `SQLITE_NOTADB` to indicate that the next one should be tried. If it does care about the filename, its `xOpen` method can check the filename and return `SQLITE_NOTADB` based on the filename, without trying to open the file.)

* It might need to indicate savepoint numbers.

* There are a few other problems with the API that you have specified. (For one thing, some of it is unclear. Some things might also be unnecessary, and some things might be missing.)

Virtual tables are useful for different things than a storage implementation is useful for, and each has its advantages and disadvantages. (Even so, I do think there are some deficiencies in the virtual table mechanism which could be corrected, but some things they just won't or shouldn't do, and there are different things that the key/value mechanism just won't or shouldn't do.)