Vritual Tables Performing SQL Queries
(1) By brianchrz on 2021-06-11 07:54:34 [link] [source]
I would like to use SQLite to do something a little crazy: basically, on insert / update to a table, I would like a trigger to effectively, insert or update a virtual table, and create some sort of large artifact. Like, a PDF report, send off an email, etc., using the contents of the record post insert / update. I can't, for the life of me, figure out how you would use any amount of SQL from within a virtual table function. It doesn't look like you get a
sqlite3 context to query over willy-nilly, and I can't quite find any examples of virtual table functions that demonstrate reading / writing to existing tables.
I'm hoping this is actually quite a trivial thing, and I just can't seem to find it in the docs somewhere. It kind of looks like this might be close, but I can't really tell.
(2) By ddevienne on 2021-06-11 08:41:28 in reply to 1 updated by 2.1 [source]
See (https://github.com/0x09/sqlite-statement-vtab/blob/master/statement_vtab.c) from [this post](https://github.com/0x09/sqlite-statement-vtab/blob/master/statement_vtab.c)
(2.1) By ddevienne on 2021-06-11 08:44:13 edited from 2.0 in reply to 1 [link] [source]
Another classic one would be a Pivot VTab, like this one
(3) By Dan Kennedy (dan) on 2021-06-11 11:39:29 in reply to 1 [link] [source]
An (sqlite3*) handle is passed to the xConnect/xCreate method. You can save a copy in your sqlite3_vtab structure and use it from within any of the other methods.
This is what fts5, rtree and lots more do.