Persistent snapshots and rollbacks

classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Persistent snapshots and rollbacks

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 that needs to be persistent, i.e.
survive closing the database and restarting the process.  After some
time, I can get rid of old snapshots (my process determines by itself
when and which snapshots can get discarded, it is not based on some
fixed TTL or something like that).

Is it possible to do all that with SQLite?

From reading the docs, it seems to me that (persistent) WAL mode
basically does *exactly that* internally:  Changes are recorded in the
logs so that previous versions are retained.  Rollbacks would be
possible by "simply" discarding the WAL entries after the desired
snapshot.  And discarding of very old snapshots corresponds to
checkpointing.

However, I'm not sure if all of that functionality is (officially)
exposed to me as a user.  There are in particular two points where I
think that my requirements differ from the functionality that WAL mode
exposes:

1) Handles to snapshots can be obtained and stored, but they are
read-only.  It seems to be not possible to tell SQLite to restore the
WAL to a previous version and then continue modifying from that version.
 (Which basically means truncating the WAL file at a certain point.)

2) From what I have seen, checkpointing can only be triggered for the
full WAL (or whatever is possible with existing readers) and not
selectively up to a desired point.  Of course I could work around that
by creating a reader at the point I want to keep.  But then I wonder if
it is a problem if the WAL can never be *fully* checkpointed (as in my
requirement).  Would that mean that it keeps on growing forever, or is
checkpointing able to remove parts from the beginning of the WAL?

Is my understanding here correct?  And is there some way in which I
could achieve my requirements using WAL mode (or somehow else)?

Thank you very much!

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Chris Brody
Savepoints (<https://www.sqlite.org/lang_savepoint.html>)?
On Fri, Oct 5, 2018 at 11:40 AM Daniel Kraft <[hidden email]> 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 of that needs to be persistent, i.e.
> survive closing the database and restarting the process.  After some
> time, I can get rid of old snapshots (my process determines by itself
> when and which snapshots can get discarded, it is not based on some
> fixed TTL or something like that).
>
> Is it possible to do all that with SQLite?
>
> From reading the docs, it seems to me that (persistent) WAL mode
> basically does *exactly that* internally:  Changes are recorded in the
> logs so that previous versions are retained.  Rollbacks would be
> possible by "simply" discarding the WAL entries after the desired
> snapshot.  And discarding of very old snapshots corresponds to
> checkpointing.
>
> However, I'm not sure if all of that functionality is (officially)
> exposed to me as a user.  There are in particular two points where I
> think that my requirements differ from the functionality that WAL mode
> exposes:
>
> 1) Handles to snapshots can be obtained and stored, but they are
> read-only.  It seems to be not possible to tell SQLite to restore the
> WAL to a previous version and then continue modifying from that version.
>  (Which basically means truncating the WAL file at a certain point.)
>
> 2) From what I have seen, checkpointing can only be triggered for the
> full WAL (or whatever is possible with existing readers) and not
> selectively up to a desired point.  Of course I could work around that
> by creating a reader at the point I want to keep.  But then I wonder if
> it is a problem if the WAL can never be *fully* checkpointed (as in my
> requirement).  Would that mean that it keeps on growing forever, or is
> checkpointing able to remove parts from the beginning of the WAL?
>
> Is my understanding here correct?  And is there some way in which I
> could achieve my requirements using WAL mode (or somehow else)?
>
> Thank you very much!
>
> Yours,
> Daniel
>
> --
> https://www.domob.eu/
> OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
> Namecoin: id/domob -> https://nameid.org/?name=domob
> --
> 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
> To go: Arc-Cav-Hea-Kni-Mon-Tou
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Dominique Devienne
On Fri, Oct 5, 2018 at 5:55 PM Chris Brody <[hidden email]> wrote:

> Savepoints (<https://www.sqlite.org/lang_savepoint.html>)?


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 apply here.
Daniel wants regular transactions to become savepoints basically.

Sounds a bit like Oracle's configurable RETENTION policy on UNDO/REDO logs.
Which allow SELECT ... AS OF (timepoint | SCN), i.e. queries in the past.
You don't mention
it Daniel, but if you can rollback to a given TX, you can also query at the
time of that TX.

This is an interesting use-case. But the WAL file is changes to the DB not
yet in the DB file.
After checkpointing, the DB file is updated with those changes, but even if
you keep the part
of the WAL that was checkpointed, you still cannot go back in time. You
need the "reverse"
of what's in the WAL file, i.e. the "UNDO" log, the old values from the DB
file overwritten during
checkpointing. Or you don't have a DB file at all anymore, and the WAL file
*is* the DB. But
then the older your DB gets (the more TX are added), the slower its gets.

So basically:
1) WAL mode only has REDO logs.
2) JOURNAL mode only has UNDO logs.

And your requirements call for both REDO and UNDO logs IMHO.
I'm not an SQLite or Oracle expert, thus take the above with a grain of
salt :). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Keith Medcalf
In reply to this post by Daniel Kraft

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 only OPEN read-only snapshots in the past, you cannot roll back to them.

I do not think this is a built-in feature of SQLite3.   Basically you want to keep your own "list of changes" made to the database (as in a log) and within a transaction apply those (or unapply those going backward in time) until you arrive at the point it time you want.  If you commit that transaction you have restored to a previous point-in-time and should get rid of the log entries after that point.  Otherwise the "restored" snapshot is only available to the one writer that has performed that "roll-back" operation until the entire undo is released (ROLLACK).

There may be other interfaces that you can use to make this easier since presumably it will affect multiple tables in the database.  This I don't know but perhaps Richard or Dan can comment ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Daniel Kraft
>Sent: Friday, 5 October, 2018 09:40
>To: [hidden email]
>Subject: [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 rid of old snapshots (my process determines by itself
>when and which snapshots can get discarded, it is not based on some
>fixed TTL or something like that).
>
>Is it possible to do all that with SQLite?
>
>From reading the docs, it seems to me that (persistent) WAL mode
>basically does *exactly that* internally:  Changes are recorded in
>the
>logs so that previous versions are retained.  Rollbacks would be
>possible by "simply" discarding the WAL entries after the desired
>snapshot.  And discarding of very old snapshots corresponds to
>checkpointing.
>
>However, I'm not sure if all of that functionality is (officially)
>exposed to me as a user.  There are in particular two points where I
>think that my requirements differ from the functionality that WAL
>mode
>exposes:
>
>1) Handles to snapshots can be obtained and stored, but they are
>read-only.  It seems to be not possible to tell SQLite to restore the
>WAL to a previous version and then continue modifying from that
>version.
> (Which basically means truncating the WAL file at a certain point.)
>
>2) From what I have seen, checkpointing can only be triggered for the
>full WAL (or whatever is possible with existing readers) and not
>selectively up to a desired point.  Of course I could work around
>that
>by creating a reader at the point I want to keep.  But then I wonder
>if
>it is a problem if the WAL can never be *fully* checkpointed (as in
>my
>requirement).  Would that mean that it keeps on growing forever, or
>is
>checkpointing able to remove parts from the beginning of the WAL?
>
>Is my understanding here correct?  And is there some way in which I
>could achieve my requirements using WAL mode (or somehow else)?
>
>Thank you very much!
>
>Yours,
>Daniel
>
>--
>https://www.domob.eu/
>OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
>Namecoin: id/domob -> https://nameid.org/?name=domob
>--
>3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
>To go: Arc-Cav-Hea-Kni-Mon-Tou




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Gerry Snyder-4
In reply to this post by Daniel Kraft
On Fri, Oct 5, 2018 at 8:40 AM Daniel Kraft <[hidden email]> 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 copies of it,
backup and restore could be a cumbersome way to do what you want.


Gerry Snyder
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Simon Slavin-3
In reply to this post by Daniel Kraft
On 5 Oct 2018, at 4:39pm, Daniel Kraft <[hidden email]> 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 restarting the process.

This cannot be done within SQLite as it is written.  You need a project which takes those requirements into account, such as

<https://github.com/aergoio/litetree>

That project may be what you want, but I have no experience with it.  But it looks like it's worth a try.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
In reply to this post by Dominique Devienne
Hi!

On 2018-10-05 18:30, Dominique Devienne wrote:
> On Fri, Oct 5, 2018 at 5:55 PM Chris Brody <[hidden email]> wrote:
>
>> Savepoints (<https://www.sqlite.org/lang_savepoint.html>)?
>
> 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 apply here.
> Daniel wants regular transactions to become savepoints basically.

Yes, savepoints sound very close to what I need -- except that I need
the current state to be visible.  (And all changes to be persistent; I'm
not sure if a savepoint in an uncommitted transaction is really persistent?)

Note, though, that I only have a single-user setting.  In other words, I
do not need "other" reads to see my changes.  It would be enough if I
can create savepoints and the same process / connection afterwards sees
the updated state even before the transaction is committed.  Is that the
case?  (I could imagine it is.)

> Sounds a bit like Oracle's configurable RETENTION policy on UNDO/REDO logs.
> Which allow SELECT ... AS OF (timepoint | SCN), i.e. queries in the past.
> You don't mention
> it Daniel, but if you can rollback to a given TX, you can also query at the
> time of that TX.

My particular usecase does not need queries in the past, only rollbacks
to that state.  But yes of course, if you have that ability, you can
also query at a past snapshot.  (In fact, that's what my understanding
of WAL mode already enables?  Just not the rollbacks.)

> This is an interesting use-case. But the WAL file is changes to the DB not
> yet in the DB file.
> After checkpointing, the DB file is updated with those changes, but even if
> you keep the part
> of the WAL that was checkpointed, you still cannot go back in time.

Ok, but from my understanding, the "current view" that SQLite exposes of
the DB is actually the real DB file + any changes from the WAL.  So that
gives me the current state, which is all I need.

And from the docs it seems that with WAL mode, it also allows me to keep
readers open at older revisions -- but again only for reading, and not
for restoring the old state.  (Although that would in theory be "just"
truncating the WAL file, right?)

> Or you don't have a DB file at all anymore, and the WAL file
> *is* the DB. But
> then the older your DB gets (the more TX are added), the slower its gets.

Yes exactly.  In principle that may be fine for me, but as I don't need
very old revisions anymore, it would be nice if I could partially
checkpoint those.

There's a project called litetree [1], which AFAIK supports all I need
except that it keeps old revisions forever.  (And of course I'd rather
go with the officially maintained SQLite if possible than with some fork.)

  [1] https://github.com/aergoio/litetree

> So basically:
> 1) WAL mode only has REDO logs.
> 2) JOURNAL mode only has UNDO logs.
>
> And your requirements call for both REDO and UNDO logs IMHO.
> I'm not an SQLite or Oracle expert, thus take the above with a grain of
> salt :). --DD

Actually, I would only need UNDO logs (as written by Keith in another
reply).  Does JOURNAL mode actually support a "persistent" journal that
allows me to roll back later?  Or is that only temporarily and limited
while I create an individual transaction to the database?

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
In reply to this post by Keith Medcalf
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 only OPEN read-only snapshots in the past, you cannot roll back to them.

According to my understanding of the docs I read, this is already
available (non-experimentally), no?  I thought that is basically what
sqlite3_snapshot_get does.  Unfortunately, that has indeed the problem
that it seems to only allow me to read the old snapshot but not restore
to it.

> I do not think this is a built-in feature of SQLite3.   Basically you want to keep your own "list of changes" made to the database (as in a log) and within a transaction apply those (or unapply those going backward in time) until you arrive at the point it time you want.  If you commit that transaction you have restored to a previous point-in-time and should get rid of the log entries after that point.  Otherwise the "restored" snapshot is only available to the one writer that has performed that "roll-back" operation until the entire undo is released (ROLLACK).

Exactly -- I can manually keep undo logs to perform the rollbacks, but
I'm hoping that SQLite can do that for me.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
In reply to this post by Simon Slavin-3
Hi!

On 2018-10-05 19:05, Simon Slavin wrote:

> On 5 Oct 2018, at 4:39pm, Daniel Kraft <[hidden email]> 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 restarting the process.
>
> This cannot be done within SQLite as it is written.  You need a project which takes those requirements into account, such as
>
> <https://github.com/aergoio/litetree>
>
> That project may be what you want, but I have no experience with it.  But it looks like it's worth a try.
I'm actually aware of litetree, and yes, it does support my usecase.
But there are two things that I'm worried about:  One is that it seems
to keep old revisions forever without any means to "checkpoint them
away".  So if I keep changing my database, the storage size grows
forever even if the database itself does not grow.

And the second is that it is most likely not as well maintained and
supported in the future as stock SQLite (even if it seems to be actively
developed at the moment).

If there is indeed no way to achieve my requirements with SQLite, I will
most likely give litetree a try (as both of those issues are no show
stoppers for me).  But from reading about WAL mode it seemed to me that
SQLite would most likely be at least close to supporting my usecase, so
I hoped it could perhaps do it.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
In reply to this post by Gerry Snyder-4
Hi!

On 2018-10-05 19:00, Gerry Snyder wrote:
> On Fri, Oct 5, 2018 at 8:40 AM Daniel Kraft <[hidden email]> 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 small enough that you can have multiple copies of it,
> backup and restore could be a cumbersome way to do what you want.

Unfortunately, I think that my database is too large for that.  Perhaps
it could work if I use external copy-on-write support (e.g. by the
filesystem), but that likely makes the whole system more complex than I
want.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Simon Slavin-3
In reply to this post by Daniel Kraft
On 5 Oct 2018, at 6:17pm, Daniel Kraft <[hidden email]> 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 snapshot.  I've never seen it done.  But I would imagine it slows down all database operations quite a bit.

Another way to simulate it is to keep a log of each SQL command which changes the database in, for example, a table called "changeLog".  This needs just two rows: tbe normal INTEGER rowid column SQLite makes for most tables, and a TEXT column for the SQL command.  You also add a row to this table when the programmer wants to create a snapshot.

Then, to restore the database as it is at a snapshot point, just start with blank tables and process all commands up to the savepoint.

I've used this technique myself, and it works.  Of course, restoring to a savepoint can take a long time, but in my usecase this was needed very rarely.  In this actual usecase I added another column to record which session had processed that SQL command.  This gave me a perfect log of who had done what.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
Hi!

On 2018-10-05 19:39, Simon Slavin wrote:
> On 5 Oct 2018, at 6:17pm, Daniel Kraft <[hidden email]> 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 certainly
good to know (so I won't try doing it and fail after wasting time).

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 state also for SELECT's, right?  That's
enough for me since I only have a single-user situation anyway.
However, it seems that unfinished transactions / savepoints cannot be
persisted when closing the database (which I would need).

> Another way to simulate it is to keep a log of each SQL command which changes the database in, for example, a table called "changeLog".  This needs just two rows: tbe normal INTEGER rowid column SQLite makes for most tables, and a TEXT column for the SQL command.  You also add a row to this table when the programmer wants to create a snapshot.
>
> Then, to restore the database as it is at a snapshot point, just start with blank tables and process all commands up to the savepoint.

That's an interesting approach.  I might be able to do something like
this in my situation, but need to think about it some more.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
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 state also for SELECT's, right?  That's
> enough for me since I only have a single-user situation anyway.
> However, it seems that unfinished transactions / savepoints cannot be
> persisted when closing the database (which I would need).

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?

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Chris Brody
> 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 <https://github.com/aergoio/litetree> do the trick for you?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
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?
>
> Couldn't <https://github.com/aergoio/litetree> do the trick for you?

Yes, but there are some things I don't like about it -- see my earlier
reply in this thread (to Simon's first message).

It might be the solution I go for in the end, though.

Alternatively, I was thinking about this idea:  If it is not possible to
persist savepoints, then I could simply discard the uncommitted changes
when the connection is closed, and redo them on the next startup of the
application.  In my particular situation, this is possible (the
information needed to redo the changes is available) and may be
acceptable performance-wise.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

James K. Lowden
In reply to this post by Daniel Kraft
On Fri, 5 Oct 2018 17:39:57 +0200
Daniel Kraft <[hidden email]> 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 restarting the process.  After
> some time, I can get rid of old snapshots (my process determines by
> itself when and which snapshots can get discarded, it is not based on
> some fixed TTL or something like that).

        "The totality of data in a data bank may be viewed
        as a collection of time-varying relations."
        -- E.F. Codd in
        "A Relational Model of Data for Large Shared Data Banks"

You're not the first.  Data change over time.  SQL doesn't support data
versions as a language feature, but you can implement it yourself
in your database design.  

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.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Abroży Nieprzełoży
In reply to this post by Daniel Kraft
The sessions extension: https://www.sqlite.org/sessionintro.html

2018-10-05 17:39 GMT+02:00, Daniel Kraft <[hidden email]>:

> 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 rid of old snapshots (my process determines by itself
> when and which snapshots can get discarded, it is not based on some
> fixed TTL or something like that).
>
> Is it possible to do all that with SQLite?
>
> From reading the docs, it seems to me that (persistent) WAL mode
> basically does *exactly that* internally:  Changes are recorded in the
> logs so that previous versions are retained.  Rollbacks would be
> possible by "simply" discarding the WAL entries after the desired
> snapshot.  And discarding of very old snapshots corresponds to
> checkpointing.
>
> However, I'm not sure if all of that functionality is (officially)
> exposed to me as a user.  There are in particular two points where I
> think that my requirements differ from the functionality that WAL mode
> exposes:
>
> 1) Handles to snapshots can be obtained and stored, but they are
> read-only.  It seems to be not possible to tell SQLite to restore the
> WAL to a previous version and then continue modifying from that version.
>  (Which basically means truncating the WAL file at a certain point.)
>
> 2) From what I have seen, checkpointing can only be triggered for the
> full WAL (or whatever is possible with existing readers) and not
> selectively up to a desired point.  Of course I could work around that
> by creating a reader at the point I want to keep.  But then I wonder if
> it is a problem if the WAL can never be *fully* checkpointed (as in my
> requirement).  Would that mean that it keeps on growing forever, or is
> checkpointing able to remove parts from the beginning of the WAL?
>
> Is my understanding here correct?  And is there some way in which I
> could achieve my requirements using WAL mode (or somehow else)?
>
> Thank you very much!
>
> Yours,
> Daniel
>
> --
> https://www.domob.eu/
> OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
> Namecoin: id/domob -> https://nameid.org/?name=domob
> --
> 3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
> To go: Arc-Cav-Hea-Kni-Mon-Tou
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Richard Damon
In reply to this post by James K. Lowden
On 10/5/18 2:19 PM, James K. Lowden wrote:

> On Fri, 5 Oct 2018 17:39:57 +0200
> Daniel Kraft <[hidden email]> 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 restarting the process.  After
>> some time, I can get rid of old snapshots (my process determines by
>> itself when and which snapshots can get discarded, it is not based on
>> some fixed TTL or something like that).
> "The totality of data in a data bank may be viewed
> as a collection of time-varying relations."
> -- E.F. Codd in
> "A Relational Model of Data for Large Shared Data Banks"
>
> You're not the first.  Data change over time.  SQL doesn't support data
> versions as a language feature, but you can implement it yourself
> in your database design.  
>
> 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 that I have used is to add two timestamps to every record
(with sufficient precision for your versioning, it could be a version
number too), one is the starting time for the record, and the second for
the ending time (NULL if to 'now'). To update a record, you get the
current time stamp (or next version number), alter the existing record
to have that as its end and create a new record with it as the start
time and NULL for the end. To get 'current' data, you condition selects
with ISNULL(endtime), to get a historical record you select such that
start is less than or equal to the time, and the end is greater than the
time or NULL.

You can purge old records based on the end time being old enough, or
total roll back by deleting records with start greater than the time,
and changing end date greater to NULL.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Chris Brody
In reply to this post by Daniel Kraft
> 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 "standard" SQLite3 if possible.

> Alternatively, I was thinking about this idea:  If it is not possible to
> persist savepoints, then I could simply discard the uncommitted changes
> when the connection is closed, and redo them on the next startup of the
> application.  In my particular situation, this is possible (the
> information needed to redo the changes is available) and may be
> acceptable performance-wise.

Makes sense to me. I just had the following idea that I hope can
satisfy your use case a little more smoothly:

Add some kind of a record state column that you can use to track which
"saveset" you want for each record in the database. (You would
probably use a special "saveset" number to mean that you want to
persist the record unless you decide to explicitly remove it someday.)
Then it should be really easy for you to include or exclude certain
records when selecting based on your "saveset" number.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Persistent snapshots and rollbacks

Daniel Kraft
In reply to this post by Abroży Nieprzełoży
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 created or dropped
tables), though, right?  I don't think that is very important in my
usecase (even though it would be nice to have), so it should be fine.

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (849 bytes) Download Attachment
12