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] [source]

Mr. Devienne (sp?), in a semi-hijacked thread, 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".a Features suggested so far include:

1. Devienne suggests that it be possible to call the shell's REPLb 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 [link] [source]

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] [source]

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] [source]

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] [source]

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] [source]

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] [source]

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] [source]

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 [source]

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.