Expose row values for SQLITE_TRACE_ROW and revamp documentation around tracing

[y2kbugger]

I'm volunteering improve documentation in the following ways:

Compare and contrast ALL types of trace callbacks in one place, with the following info:

• User api to register callback
• Link to formal api docs
• What underlying trace hook api is used?
• When is it safe/unsafe to register more than once?

I'm always a little confused at what traces hook where, and have to refer to source code. If they use sqlite3_trace_v2 they collide with other hooks outside of apsw even. But I do remember reading at least once instance where docs say you can register more than one and apsw manages as a list of callbacks

If you are will to let me try and can provide feedback on PR let this issue track my progress, otherwise close.

[rogerbinns]

I don't accept PRs, but am always delighted with feedback on where the docs need improvement. This has been in my thoughts for a while.

• The original tracing was implemented by me as exec and row tracers as SQLite didn't have its own thing
• I also added the apswtrace tool
• Later on a sqlite3 api was added, but it doesn't expose what the bindings are whereas my tracers did
• Later on I added multiplexing to the sqlite3 api so sqlite only saw one callback, and APSW managed multiple at the same time. (I did this for many other sqlite3 callbacks too.)
• I added apsw.ext.Trace so you can trace in a block. The multiplexing is crucial because it means apsw.ext.Trace won't step on the toes of any other tracer already in use. apswtrace was also updated for the same reason.
• SQLite still has no equivalent to the row tracers
• Exec tracers were the only way to convert unsupported bindings until convert_binding was added recently

As a general rule the tour contains the best way of doing things.

The next release coming out with SQLite 3.52 includes async support. That has updates to the execution documentation, which is intertwined with the exec and row tracers.

The evolution listed above is messy and the docs reflect that. They definitely need to be clarified and shortened. Thank you for the reminder.

[y2kbugger]

Could you elaborate on difference between your row tracers vs trace_v2?

SQLITE_TRACE_ROW
An SQLITE_TRACE_ROW callback is invoked whenever a prepared statement generates a single row of result.

https://sqlite.org/c3ref/c_trace.html

[rogerbinns]

When you hit SQLITE3_TRACE_ROW the only information provided is a pointer back to the prepared statement - ie you know what SQL is currently being executed, but you do not know what the contents of the row being returned are. Also this is all rows processed by SQLite, not just those from your SQL - for example virtual tables and SQLite internals also execute their own SQL in order to satisfy your SQL. You can register as many of these as you want, and APSW multiplexes them.

The row tracer is called with an APSW cursor and the row about to be returned. You can suppress the row, or change its contents. This is only called for your SQL and there is no multiplexing.

On reflection I could provide the row being returned for SQLITE3_TRACE_ROW but it would have to be the raw unconverted values. so it wouldn't reflect what the Python code saw (if applicable). ie it would be before the row trace or convert_jsonb ran. For completeness I think I should expose this.