Re: [sqlite] Persistent snapshots and rollbacks

This suggestion refers to temporal DB. To those interested by this approach, this tutorial could help (implementation coded in SQLite): https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf

Re: [sqlite] Persistent snapshots and rollbacks

On 10/5/18 2:37 PM, Daniel Kraft wrote: > 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

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

> 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 detail. That said, I would personally favor using

Re: [sqlite] Persistent snapshots and rollbacks

On 10/5/18 2:19 PM, James K. Lowden wrote: > On Fri, 5 Oct 2018 17:39:57 +0200 > 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. All of that needs

Re: [sqlite] Persistent snapshots and rollbacks

The sessions extension: https://www.sqlite.org/sessionintro.html 2018-10-05 17:39 GMT+02:00, Daniel Kraft : > 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

Re: [sqlite] Persistent snapshots and rollbacks

On Fri, 5 Oct 2018 17:39:57 +0200 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. All of that needs to be persistent, > i.e. survive closing the

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

> 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? Couldn't

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 the updated

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 unfortunate for me, but

Re: [sqlite] Persistent snapshots and rollbacks

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. You can simulate it by introducing an extra column in each table and writing your own library to simulate each

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. > > If the database is

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 later on. All of that needs to be

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

Re: [sqlite] Persistent snapshots and rollbacks

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 later on. All of that needs to be persistent, i.e. > survive closing the database and

Re: [sqlite] Persistent snapshots and rollbacks

On Fri, Oct 5, 2018 at 8:40 AM Daniel Kraft wrote: > 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. > If the database is small enough that you can have multiple

Re: [sqlite] Persistent snapshots and rollbacks

says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Daniel Kraft >Sent: Friday, 5 October, 2018 09:40 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Per

Re: [sqlite] Persistent snapshots and rollbacks

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 transaction to become visible to readers, so savepoint do not

Re: [sqlite] Persistent snapshots and rollbacks

Savepoints ()? On Fri, Oct 5, 2018 at 11:40 AM Daniel Kraft wrote: > > 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

[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