SQLite Forum

Session Extension: Changeset for Query/View
Login

Session Extension: Changeset for Query/View

(1) By anonymous on 2022-01-24 19:37:56 [link] [source]

Hey, does the session extension support getting changesets for SQL queries / views? If not, is there another extension that does this? If not, why not? Wouldn't this be super useful? :)

(2) By Keith Medcalf (kmedcalf) on 2022-01-24 19:52:00 in reply to 1 [link] [source]

Neither a SELECT nor a VIEW change the database.

As such, there is nothing to record in a changeset (that is, a changeset is a collection of changes, and neither a SELECT nor VIEW causes a change to the database, hence there are no changes to record).

(3.1) By Larry Brasfield (larrybr) on 2022-01-24 19:58:07 edited from 3.0 in reply to 1 [source]

There is no present direct support for such a thing, and I know of no extension that does it. (That latter is what I call "a statement of ignorance.")

It might be useful at times. You can get the same effect by using the CREATE TABLE AS SELECT ... construct and creating a changeset from the resulting tables.

The ease of doing this would be part of the "why not?".

(6) By anonymous on 2022-01-24 20:55:10 in reply to 3.1 [link] [source]

Thanks! I understand how I can create a (temporary) table with CREATE TABLE AS SELECT ... but it was my understanding that the resulting table will not automatically reflect changes made to the tables it has been created from. Could you explain to an SQL/SQLite noob like me how the table is updated (based on the same SELECT ... query) in order to generate the desired changeset? Do these updates have to be triggered manually? If so, is there a good way to find out for which database changes an update is necessary?

(7) By Larry Brasfield (larrybr) on 2022-01-24 21:16:01 in reply to 6 [link] [source]

Your understanding of table stability is correct. I did not mean to imply that a table created using "CREATE TABLE <name> AS SELECT ..." would somewhat dynamically reflect changes to whatever data the SELECT drew from.

Do these updates have to be triggered manually?

Yes, if you really want them to happen and have not bothered to effect those changes automatically with UPDATE and INSERT trigers.

(8) By anonymous on 2022-01-24 21:37:28 in reply to 7 [link] [source]

Okay! Didn't you imply though that basically this one-liner would suffice to get the changeset/incremental view updates? I still don't know how that would work without rerunning the entire SELECT query on every database update and then somehow manually computing the difference between the stored table and the updated query result.

(9) By Larry Brasfield (larrybr) on 2022-01-24 21:42:41 in reply to 8 [link] [source]

Didn't you imply though that basically this one-liner would suffice to get the changeset/incremental view updates?

There was nothing about this incremental or updating behavior in your original post. So I have no idea where such an implication could have arisen.

I still don't know how that would work without rerunning the entire SELECT query on every database update and then somehow manually computing the difference between the stored table and the updated query result.

There are more incremental ways. The INSERT and UPDATE triggers have enough information available to permit a less heavy-handed approach. Whether the ROI on exploiting that information is positive is for you to determine.

(10) By anonymous on 2022-01-24 22:04:16 in reply to 9 [link] [source]

Okay, thank you! Do you have anything specific in mind that is less heavy-handed or pointers to prior work (code/papers)?

(4) By anonymous on 2022-01-24 20:28:11 in reply to 1 [link] [source]

Sry I should have been more clear. What I meant is more like incremental view updates that reflect how a view/query result changed as a result of modifications to the database, i.e. like what https://materialize.com does for PostgreSQL.

(5) By Larry Brasfield (larrybr) on 2022-01-24 20:45:49 in reply to 4 [link] [source]

I understood that intention when I posted #3 (which remains pertinent.)