Re: [sqlite] Inverted changesets and UNIQUE constraints

Hi! On 26.08.19 14:05, Simon Slavin wrote: > On 26 Aug 2019, at 12:43pm, Dan Kennedy wrote: >> When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change >> into a "retry buffer". Then, once it has attempted all changes in the >> changeset, it goes back and retries those in the

[sqlite] Inverted changesets and UNIQUE constraints

Hi! I'm using the SQLite session extension to create changesets, invert them and apply them to undo previous changes in the database. (Essentially what I need to do is persistent savepoints.) This works well so far, but I recently wondered about the interaction with UNIQUE constraints. In

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Hi Dan! On 07.01.19 16:58, Dan Kennedy wrote: > I think it was a bug. Thanks for reporting it. Now fixed here: > >   https://www.sqlite.org/src/info/6281ef974c0ac7a7 Cool, thanks for confirming it was indeed a bug (and not my stupidity), and the quick fix! Yours, Daniel --

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Since it seems that the mailing list swallowed my attached example code, I've put it on Github as well: https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1 On 06.01.19 14:08, Daniel Kraft wrote: > Hi! > > It seems to me that the session extension is broken in a

[sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

Hi! It seems to me that the session extension is broken in a situation that involves a "WITHOUT ROWID" table and "INSERT OR REPLACE" statements (but not if only one of those is used). Note that I'm using SQLite version 3.26.0 (> 3.17.0), so the session extension should work also for my WITHOUT

Re: [sqlite] [EXTERNAL] Persistent snapshots and rollbacks

Hi! On 2018-10-08 08:11, Hick Gunter wrote: > 1) Include an "inserted at" timestamp column in each table > 2) create a history table for each real table > 3) use before triggers to copy the old record to the history table and set > the "inserted at" timestamp Using triggers is an interesting

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 20:33, Chris Brody wrote: >> Yes, but there are some things I don't like about it -- see my earlier >> reply in this thread (to Simon's first message). > > Gotta say I could not follow what you said in the earlier reply. It > would be nice if you could explain in some finer

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 20:27, Richard Damon wrote: > On 10/5/18 2:19 PM, James K. Lowden wrote: >> Add a "version" column to your table. Create views that (using a >> self-join) show only the latest version. Periodically purge old >> versions. Roll back by deleting new versions. > > One design

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 20:22, Abroży Nieprzełoży wrote: > The sessions extension: https://www.sqlite.org/sessionintro.html Interesting, that gets me indeed very close to my usecase! It seems that the sessions extension and changesets won't support changes to the actual database schema (e.g. newly

Re: [sqlite] Persistent snapshots and rollbacks

On 2018-10-05 19:59, Chris Brody wrote: >> I did now a quick experiment with the sqlite3 command-line, and it seems >> that savepoints indeed work exactly what I need *except* for being >> non-persistent. Is there some way or trick I could use to make them (or >> the "current session") persist? >

Re: [sqlite] Persistent snapshots and rollbacks

On 2018-10-05 19:47, Daniel Kraft wrote: > I'm still thinking about the savepoints that Chris pointed out, though. > They seem to be very close to what I need. And even if I don't commit > the transaction, I imagine that at least the database connection that is > building it sees

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 19:39, Simon Slavin wrote: > On 5 Oct 2018, at 6:17pm, Daniel Kraft wrote: >> If there is indeed no way to achieve my requirements with SQLite > > There isn't. I understand what you want and SQLite can't do it. Ok, thanks for confirming -- that's unf

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 19:00, Gerry Snyder wrote: > On Fri, Oct 5, 2018 at 8:40 AM Daniel Kraft wrote: >> I need the ability to make multiple changes / commits to my SQLite >> database but keep snapshots of previous states and potentially roll back >> to those states later on.

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 19:05, Simon Slavin wrote: > On 5 Oct 2018, at 4:39pm, Daniel Kraft wrote: > >> I need the ability to make multiple changes / commits to my SQLite >> database but keep snapshots of previous states and potentially roll back >> to those states late

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 18:47, Keith Medcalf wrote: > The experimental ENABLE_SNAPSHOT interface can do this sort-of. The > transaction still has to be in the WAL file (which means you may need to make > the WAL file persistent across closes using the appropriate file control). > However, you can

Re: [sqlite] Persistent snapshots and rollbacks

Hi! On 2018-10-05 18:30, Dominique Devienne wrote: > On Fri, Oct 5, 2018 at 5:55 PM Chris Brody wrote: > >> Savepoints ()? > > Savepoints are still part of a transaction, so not visible to readers until > the COMMIT. > Daniel's use case wants each

[sqlite] Persistent snapshots and rollbacks

Hi! I need the ability to make multiple changes / commits to my SQLite database but keep snapshots of previous states and potentially roll back to those states later on. All of that needs to be persistent, i.e. survive closing the database and restarting the process. After some time, I can get