SQLite Forum

Wishing CLI could be usefully embedded
Login

Wishing CLI could be usefully embedded

(1.1) By Larry Brasfield (larrybr) on 2021-09-12 10:36:34 edited from 1.0 [link]

Mr. Devienne (sp?), in [a semi-hijacked thread](https://sqlite.org/forum/forumpost/fdc585c712e6e66c?t=h), has described a set of features for the SQLite CLI shell in support of it then being usefully embedded in a larger application, without need of shell source modification.

As somebody working on substantial shell enhancement, I have an interest in enhancements that serve the project purposes and make the shell more useful. So this thread is about one such enhancement, "embeddibility".<sup>a</sup> Features suggested so far include:

\1. Devienne suggests that it be possible to call the shell's REPL<sup>b</sup> and that instead of ever calling "exit()" it just return from that call.

That seems quite easy to do. I have already setup infrastructure to pass "time to quit" up from dispatched commands rather than abruptly ending the process. (This aids cleanup.) Exposing the REPL function and having it always return when .quit or .exit commands are interpreted is a very minor change, and could easily involve some clean preprocessor trickery (if such exists).

\2. Devienne suggests that the shell's main(...) be convertable to something like sqlite3_cli_main().

That would be among the cleanest transformations the preprocessor is made to do.

\3. If the embedding app already uses linenoise, integration would be nice-to-have.

This is not clear, and how that should work is debatable. A separate history for the embedded sub-shell may not be problematic. Linkage and initialization issues are another tangle.

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

Delegating such functionality to a function replaceable via preprocessing might be workable.

\5. In case the embedding app presents a GUI, with the embedded sub-shell appearing in a dialog, it would be useful for stdin/stdout/stderr streams to be provided by the host application.

A few FILE pointers, set during replaceable initialization should suffice.

\6. Add a .mode to present the table-data in a native GUI-table-widget.

As it happens, among the enhancements I'm developing are pluggable output modes and import varieties. I will think about how such plug-in action might function as an embedding-app-callable function.

\7. ??

Further suggestions in service of embeddibility  are welcome.

----

a. The word "embeddibility" is not yet in common dictionaries, but is used commonly for the concept it obviously denotes.

b. "REPL" => Read, Evaluate, Print Loop

Edited to add points 3+.

(2) By David Jones (vman59) on 2021-09-12 12:52:55 in reply to 1.1

How is SIGINT dealt with? Can an application GUI simulate/generate one with a 'cancel' button?

Will the SQLITE_SHELL_DBNAME_PROC and SQLITE_SHELL_INIT_PROC hacks be made part of the formal interface?

I think a good test of the interface would be that all the Windows-related cruft in shell.c could be removed and replaced with a Windows application that calls the embedded CLI.

(3) By Larry Brasfield (larrybr) on 2021-09-12 14:12:05 in reply to 2 [link]

> How is SIGINT dealt with? Can an application GUI simulate/generate one with a 'cancel' button?

(From memory:) That trap's handler sets a flag which the REPL checks. Clearly, setup of trap handlers should be the embedding app's responsibility, as a compilation option.  Setting that flag needs no simulation -- just doing.

> Will the SQLITE_SHELL_DBNAME_PROC and SQLITE_SHELL_INIT_PROC hacks be made part of the formal interface?

Given the comments near those PP variable usages, those provisions are already part of the interface. I expect that either a new doc page or extension of the shell's present doc page will address use of the shell's code in non-stand-alone contexts. That would make the interfaces "formal", I suppose.

> I think a good test of the interface would be that all the Windows-related cruft in shell.c could be removed and replaced with a Windows application that calls the embedded CLI.

I agree, and the same goes for the *Nix-related cruft.

(4) By Keith Medcalf (kmedcalf) on 2021-09-13 00:32:50 in reply to 3 [link]

>>  think a good test of the interface would be that all the Windows-related cruft in shell.c could be removed and replaced with a Windows application that calls the embedded CLI.

> I agree, and the same goes for the *Nix-related cruft.

I, for one, do not follow.  The CLI does nothing at all except send SQL text to the SQLite3 library for processing.

What on earth would anyone want to "embed" it in another application for?

Why not just write your other application to interface with the SQLite3 library and use the API to execute SQL commands?  (which is all the CLI does).

(5) By Scott Robison (casaderobison) on 2021-09-13 02:09:17 in reply to 4 [link]

There are multiple dot commands that do not exist in the SQLite library but do exist in the shell. The ability to embed it in another application would expose that functionality without needing to pipe or otherwise redirect IO between processes. Some people prefer not to reinvent the wheel, even if it means taking a wheel off another car and using it on their own. Not unlike what fossil does to provide a sqlite command in its bag of tricks.

(6) By anonymous on 2021-09-13 05:22:50 in reply to 5 [link]

The fact that people see a benefit of being able to use the cli specific functionality in their own programs really seems to suggest that there is functionality embedded in the cli that really should be part of the core api…. 

Wrapping the cli and allowing it to be embedded is just an ugly kludge….

(7) By Scott Robison (casaderobison) on 2021-09-13 07:35:11 in reply to 6 [link]

And anyone is free to refactor it into a useful library. It is in the public domain, after all. Promoting anything into the core API brings a long term commitment to maintain it.

As for it being an "ugly kludge" that depends on what they're looking for. It is used to good effect to provide the sqlite interface for fossil so that maintenance tasks that might not be first class features in fossil are still possible. And thus it exists for SQLite: The shell is a place for "second class" features to exist that provide various types of utility but that either don't belong in the core API or are not yet ready for the core API. I don't know if any of the latter have ever come pass (something introduced to the shell and later promoted to first class status).

(8) By Stephan (stephancb) on 2021-09-13 07:52:39 in reply to 1.1 [link]

My wish list for SQLite shell enhancements:

1. Before the SQLite shell introduced its own tab completion, readline's tab would come up with suggestions for file names. This was quite useful, especially for ".read ..." etc. The SQLite shell completion (it suggests tables, columns etc.) is of course also useful, but the best would be to have both. Can file name completion be put back (as part of the SQLite shell).

2. Other shells that I use (bash, ohmyrepl, ...) become greatly more powerful when embedding fuzzy search [https://github.com/junegunn/fzf] into the shell, especially for command history. (Fuzzy search is kind of competitor to SQL search :). This would be great to have also in the SQLite shell. I often want to reuse complicated SELECTs, but they have dropped out from the command history or are difficult to find "way back". With fzf, integrated into the SQLITE shell, I could much more easily find commands also in very long history.

Sorry for semi-hijacking the thread again, this is only partially related to embedding the shell.

(9) By Harald Hanche-Olsen (hanche) on 2021-09-13 10:02:46 in reply to 4 [link]

> What on earth would anyone want to "embed" it in another application for?

I have no wish to do so myself, but I can't resist pointing out that fossil's sql subcommand appears to do just that.

(10) By Jason Gibson (Jason_Gibson) on 2021-12-06 18:12:46 in reply to 1.1 [link]

Hi,

Having recently integrated shell.c into an application, I can provide some feedback on the subject.

Starting with sqlite-amalgamation-3360000/shell.c, this is what I changed:

- Added a line at the top of the file to prevent the language-supplied build tool from building it (Golang wants to build all .c files itself, but it was better to have the project Makefile handle it).
- Change the include of sqlite3.h to match the project-specific rename of the header (trying to prevent accidentally using the system copy).
- Add some extern definitions for callbacks into the host program.
- Change any of the functions that print output (e.g. utf8_printf(), output_c_string()) to instead call into the host program so output can be redirected from there.
- Crossing the language barrier from the host program to C is relatively expensive in this case, so the output functions that printed lots of single characters were changed to buffer the output with sqlite3_str.  Trading potential memory use for speed is ok in this application's case.
- Combined the Windows and unix utf8_printf() versions into a single one.
- Modified needCsvQuote[] to change which characters required quoting.
- Changed some of the BOX_$N defines so box output looks slightly different.
- Replaced fputs() calls with utf8_printf() in various places (e.g. print_box_row_separator()) to route all output to a single function.
- Changed exec_prepared_stmt_columnar() and exec_prepared_stmt() to return a row count.
- Changed box output content/header alignment from center to left.
- Changed open_db() to set globalDb=p->db and set up a few sqlite3_create_function(p->db, "shell_add_schema") type calls that weren't being initialized.
- Renamed the call to sqlite3_fileio_init() to sqlite3_fileio_init1() because it was conflicting with the same name from integrating fileio.c elsewhere in the application.
- Created a wrapper version of exec_prepared_stmt() that initializes a ShellState and some other global type stuff needed in order for the host application to send a prepared query into shell.c and get formatted output back out.
- Renamed main() to sqlite_shell_main() so it won't conflict with the host application and so that it takes an optional sqlite3* that the host application controls.
- Removed calls to close_db() and session_close_all() since the sqlite3* is managed by the host application.

There's of course different ways some of these things could be done, but this is how they ended up in this case.  The goal with embedding the shell was to gain access to custom virtual tables, to provide a REPL, and to re-use the box/csv/etc output routines.

'Hope this is useful.

Thanks.

(11) By Jason Gibson (Jason_Gibson) on 2021-12-06 18:52:03 in reply to 1.1 [link]

Oh, and one item that I forgot to mention in my reply is that since output from functions like utf8_printf() is being redirected to the hosting application, it'd be useful to have those functions return an error, so processing can be interrupted by the host application.  This isn't a big deal though since the application already installs a progress hook, but error returns are cleaner and more immediate.

(12) By ddevienne on 2021-12-07 09:06:17 in reply to 10 [link]

Good list. Thanks for sharing.

> goal [...] gain access to custom virtual tables

That's one of the main reason of my original post too.

Those are part of a large application that can't be made into a loadable  
extension of the *regular* (official) SQLite shell, so the logical solution  
is to embed the shell into that large application.

Others in the company have created a GUI dialog that allows running queries,  
including against the vtables, displaying the results in a GUI table control/widget.  
But of course one loses all the *.commands* of the shell, and doing a one-time embedding  
is IMHO a losing proposition, given the fast pace of the SQLite sources (including its shell.c).

As I wrote originally, just allowing IO to be fully *redirected* to the hosting  
app is required, while the ability to side-step the text-based `.mode` processing  
to have the hosting app using a table GUI control/widget is more *nice-to-have*.  
(Optional side-stepping, since `.mode` is also used for output to CSV, HTLM, etc...  
Which should be retained and is part of the appeal to embed the shell)

(13) By Larry Brasfield (larrybr) on 2021-12-10 14:58:23 in reply to 2 [link]

(Un-hijacking this thread:)

I would like any suggestions regarding how to treat SIGINT (or its Windows equivalent) handling. There are multiple aspects to this:<br>
(1) The handler, if there is to be one, must be registered.<br>
(2) The handler must exist, either the existing one or a replacement.<br>
(3) The primary DB connection must be visible to the handler if long-running queries are to be interrupted.<br>
(4) A "exit shell" flag must be visible to the handler if the shell is to return control to the embedding app.<br>
(5) Coordination between DB closing and DB query interruption must occur.

At present, I'm thinking a small struct containing the data and possibly function pointers to support this handling should be passed as an in/out parameter to the main()-like shell entry point when it is embedded.

It seems a bit kludgy. Maybe that's inherent in the problem, but any ideas toward a clean interface are welcome.

(14) By ddevienne on 2021-12-10 15:18:45 in reply to 13 [link]

> (3) The primary DB connection
> (5) Coordination between DB closing

In embedded mode, the pimary DB connection can be coming from the host.  
I.e. one should neither open nor close it.

In my case, it is **the** DB with all the vtables representing the state  
of the C++ host application, which only the host app can configure, and  
which the host app will continue using once the embedded shell *exists*.

The embedded shell can open other connections (recent addition), or attach other DBs.

If attaching other DBs to the primary host-managed connection, once might  
wonder whether the shell should detach those, or let them attached, to be  
available again later if/when another embedded shell is re-started.

I think leaving them as-is makes more sense. The host can setup an [authorizer][AUTH]  
if it wants to, to prevent or know about those, and detach them *post* embedded-shell *exit*.

Regarding your thinking of a struct and a main()-like entry point.  
Why not replicate the design of SQLite itself, with the shell taking  
an optional VFS, defaulted otherwise, and a new VIO (Virtual IO) interface  
specific to the shell? I.e. make the shell a stateful object to new/config/close?

[AUTH]: https://www.sqlite.org/c3ref/set_authorizer.html

(15) By Larry Brasfield (larrybr) on 2021-12-10 16:12:42 in reply to 14 [link]

Thanks for that. It has become food for thought.

I like the idea of using an interface for this. Something like <u>[the upcoming MetaCommand interface](https://sqlite.org/src/file?name=src/shext_linkage.h&ci=8dc69c81b9ad1c45&ln=78-85)</u>, with methods specific to ctrl-C (or other interrupting), would be perfectly replaceable and clean from a data-hiding, well-defined operation perspective.

As for DB management, that's still achewing.