reading databases using a customized virtual file-system

(1) By anonymous on 2021-09-10 07:34:40 [link] [source]

Hi,

I have sqlite databases stored in something similar to a customized virtual file-system. I want to extend sqlite.c to add support for this vfs. I did start browsing in the code, and I have some idea where to start, but any guides and tips will be very helpful and much appreciated.

I do not need any write functions though, I only wish to read databases without altering them.

Thanks

(2) By Larry Brasfield (larrybr) on 2021-09-10 10:13:16 in reply to 1 [link] [source]

Start at https://sqlite.org
Click "Documentation" on menu bar.
Click "Website Keyword Index".
Look for and click "VFS" by itself toward the end.
Study the doc, especially under "VFS Implementations".

Question are welcome about issues not made clear in the docs or not easily discoverable there.

(3) By ddevienne on 2021-09-10 12:01:08 in reply to 2 [link] [source]

Accessing DBs using a custom VFS is one thing, but the CLI itself is not
using any VFS, but direct C/Posix APIs to access files. I've long wished
for the CLI to also use a few abstractions to be usable embedded in a larger
application, that would provide IO, main-entry-point, etc... Oh well :)

(4.1) By Larry Brasfield (larrybr) on 2021-09-10 12:18:34 edited from 4.0 in reply to 3 [link] [source]

Would you please briefly elaborate? (just the gist, not a specification)

And, for sake of thread-hijack avoidance, start another for your idea?

(5.1) By ddevienne on 2021-09-10 14:45:02 edited from 5.0 in reply to 4.1 [source]

Sure. Two main use-cases to start with.

First, embed the SQLite CLI in an existing CLI app, w/o needed source modification.
The host CLI app is multi-command (like SVN, GIT, etc...), and uses SQLite databases
already. I'd like to add a sql or sqlite command, which embeds the full CLI, so I need
a non-main() entry-point (e.g. sqlite3_cli_main(argc, argv)), and not have that CLI
command ever exit(), but simply return from the entry point.

Note that the CLI has other non-SQLite interactive modes, which can stack, I could
enter the SQLite CLI from another interactive-mode, and .exit in the SQLite CLI should
just go back to the previous mode. The prompt changes back to that previous mode's one.

Also, the host CLI already uses linenoise for CLI-formatting/coloring, so integration would be nice-to-have.

And the CLI has custom SQLite functions and vtable modules it might want to pre-register with the SQLite CLI.
So a pure main()-like entry-point is not ideal, in terms of flexibility.

That host CLI is a single-exe, kitchen-sink style, no need for an installer. SQLite or Fossile style :)
Update: And of course, SQLite (the library) is already linked into that CLI app.

Second, embed the SQLite CLI in a GUI app, opening in a custom dialog and not a console,
thus not using stdin/stdout/stderr, but having the possibility to have those streams provided by the host application.

Here again, calling exit() is a no-no, and also needs a non-main() entry-point, like the CLI use-case above.
In this mode, the CLI is meant to use an existing in-memory connection, with lots of virtual tables in it.
SQLite is at the heart of the app, exposing a lot of the state of this large scientific (proprietary) application,
and having the power of the full SQLite CLI app would be great, to troubleshoot/debug that state.

I think the CLI use-case is easily achievable with a few tweaks.

The second is more difficult, and might need the CLI using a VFS to abstract away the IO streams,
or something of that nature. In that GUI-app use-case, being able to add a .mode to present the
table-data in a native GUI-table-widget would be even better, but that's a different matter and a bit of a stretch goal.

So here you go, this is what I had in mind. FWIW. --DD

(8) By Larry Brasfield (larrybr) on 2021-09-12 02:37:31 in reply to 5.1 [link] [source]

Please see new thread titled "Wishing CLI could be usefully embedded".

(6) By David Jones (vman59) on 2021-09-10 13:43:59 in reply to 1 [link] [source]

I use the undocumented SQLITE_EXTRA_INIT hack to make SQLite's OS initialization install a custom VFS and make it the default VFS.

(7) By ddevienne on 2021-09-10 14:46:10 in reply to 6 [link] [source]

Thanks. Good to know. Takes care of registering stuff then.
But falls short on the other objectives though.