SQLite query progress bar

Question

I am using sqlite from c++ and I want to implement a progress bar that will inform user about the progress of a search.

Using sqlite3_progress_handler I can set ca callback to be called every N virtual machine instructions. This is ok for an infinite progress bar that is notifying user the app is still working.

What I need is a progress from 0 -> 100%. Can this be done ?

Answer 1

I realize that this is a bit late, and that this question already has an accepted answer, but I think a little more information would be useful.

As the OP noted in the question, the sqlite3_progress_handler can be configured to call the callback function every N VM instructions. This is not a time progress monitor or a query statement progress monitor, but a monitor for the VM instructions that the query planner has calculated for the query (which will do in a pinch).

By prefacing the query with 'EXPLAIN QUERY PLAN' (or just 'EXPLAIN' for short) and stepping the results to get a count, you will know how many VM instructions are in the query plan. There's your 100% figure.

BE SURE to read the caveats on the SQLite.org website about the EXPLAIN QUERY PLAN command, especially the part about not relying on the output format. But for this situation, we're not concerned with the information in the results, but only the number of instructions.

---

As of SQLite version 3.24.0 (2018-06-04), the output format for the EXPLAIN QUERY PLAN command diverged significantly from the EXPLAIN command. EXPLAIN QUERY PLAN is no longer suitable for this use case; you have to specify the EXPLAIN command itself.

To be clear, the EXPLAIN command is not published as supporting the sqlite3_progress_handler() API. The fact that it can be used in this case is purely coincidental. You should always test that this works on whichever version of SQLite you are using.