.expert via api
Is it already (or will it in the future be) possible to do the equivalent of the cli .expert command via api? My use case is that I have a complex program that uses the SQLite c-api and which generates 1000s of queries dynamically. I would like to extend the debugging/development mode of the program to use equivalent .expert functionality so that I can log potential indices that I should add in order to improve query performance. I would then manually analyse the logs and determine which (if any) indices that I will create and then add these to the list if indices that the program creates.
You should realize that the code supporting the .expert shell command is freely usable. You may also have noticed that [the .expert command](https://www.sqlite.org/cli.html#expert) <b>is experimental</b>. This works against its functionality soon being included in the stable API.
What I tried today: The [https://www.sqlite.org/src/dir?ci=trunk&name=ext/expert | page] about this extension contains all the necessary information if you want to compile and use this feature. Some minor points: * sqlite3expert.c compiles into linkable code (no need for expert.c) and can be statically linked with an executable. If you want to use a dynamic library, export necessary functions (mentioned in the docs) and be ready for a large executable (containing all the SQLite inside). So it's not an extension in the sense how loadable extensions are used. I personally only tried the statically linked approach. * Every successful call to sqlite3_expert_sql implicitly creates an auto-increment statement id that you should pass to sqlite3_expert_report in order to get the report (starting with 0) * The extension provides other information as well. But in order to get index suggestion, call sqlite3_expert_report with EXPERT_REPORT_INDEXES constant. You will get a multi-line suggestion with one or more indexes or Null if there's no need for new indexes * Don't forget to free error messages (if any) with sqlite3_free I was a little late to know of the .expert feature (see my naive suggestion at the [https://sqlite.org/forum/forumpost/952dbb45e8 | Simon's post]). But thanks to the discussion, a new tool is added to the palette.
Just because it is experimental should not exclude it from being in the standard apis and flagged as experimental and subject to change rather than only being part of a command line tool. Yes of course I can hack the code around and include it myself in the core SQLite dll, but why add functionality to the command line tool only rather than including it in the core dll (so that it can be used as standard in many more scenarios) …. Just my 2 cents…. I see it as a missed chance to only include it in the command line tool
> Just because it is experimental should not exclude it from being in the standard apis and flagged as experimental and subject to change Occasionally, as new API functions and features are introduced, there is an interval prior to release when they are marked (or flagged) as you suggest. This is rarely done with anything that goes into released SQLite versions. It would take more than developer convenience to motivate a break with this practice, I think. > Yes of course I can hack the code around and include it myself in the core SQLite dll ... If your term "hack" includes editing<sup><b>a</b></sup>, you may find a useful feature in recent sqlite3.c amalgamations where the preprocessor symbol SQLITE_CUSTOM_INCLUDE is used. > \[W\]hy add functionality to the command line tool only rather than including it in the core dll\[?\] "SQ<b>Lite</b>"'s spelling indicates much of that why. The rest relates to the burden of maintaining a stable API over decades. > (so that it can be used as standard in many more scenarios) I sense a tension here, between "standard" and "experimental". (my $.02) > I see it as a missed chance to only include it in the command line tool\[.\] Well, you could include it in your application(s), the one(s) you wish could use it as part of the SQLite library. The CLI shell is such an application, so the mechanism of including it yourself is there for the study or taking. ---- a. In my parlance, modifying source to make it do what I want is not necessarily a "hack".
I accept and understand your opinions on this, they are also valid. For other functionality of the cli tool it is clear that it is related only to that tool (eg formatting of sql query results), but to me being able to suggest that an index should be created is more core functionality and belongs in the core api rather than a cli tool. Backups, vacuum and other “utility” functionality could also be seen as not needed in core (to keep it “lite”) …. But they are in core … However the end decision is of course the choice of the developers, it is just that in this case I have a different opinion :)
This has been a longstanding issue. For some reason there is a great love of adding things to the Diagnostic Shell Tool (CLI) rather than the actual SQLite product. I cannot fathom why one would do this, as it is fraught with all sorts of peril. However, it is a rather trivial matter to remove all the "crappola" from the Diagnostic Shell Tool (CLI) and add it to the SQLite3 library where it belongs -- I have to do this every single time yet more dreck that belongs not in the CLI is added there instead of to the core.