Persistent snapshots and rollbacks

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

Re: Persistent snapshots and rollbacks

Daniel Kraft
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 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.
Those are interesting options for doing things manually, thanks for
pointing them out!

Unfortunately I'm building a platform (where providing rollback-enabled
SQLite databases is one feature) and not an actual application.  So
ideally I need something where I can on the platform-side create
snapshots / start a changeset and let the actual application do SQL
commands, without the need for "instrumenting" the SQL commands themselves.

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 Chris Brody
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 detail.

The first small issue I have with litetree is that as far as I
understand, it won't allow me to remove / "release" previous revisions.
In other words, if I have some row in my table and I change it a hundred
times back and forth, then litetree will always keep those hundred
revisions in its history (just like a version control system would).

That's of course what it is supposed to do, but it means that the size
of the database grows with each change even if the current state still
consists of only one row.

In my usecase, that is unnecessary -- I don't need to keep all versions
forever.  I only need to keep the ability to roll back for a limited
amount of time, and it would be nice to remove old revisions completely
(corresponding to a "release" of savepoints or checkpointing with WAL).
That seems to be not supported by litetree.

> That said, I would personally favor using "standard" SQLite3 if possible.

And this is my second small issue with litetree.  It seems to be
actively developed at the moment, but of course using the official
SQLite3 gives me a dependency that is most likely better maintained and
kept up to date in the future.

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

Richard Damon
In reply to this post by Daniel Kraft
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 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.
> Those are interesting options for doing things manually, thanks for
> pointing them out!
>
> Unfortunately I'm building a platform (where providing rollback-enabled
> SQLite databases is one feature) and not an actual application.  So
> ideally I need something where I can on the platform-side create
> snapshots / start a changeset and let the actual application do SQL
> commands, without the need for "instrumenting" the SQL commands themselves.
>
> Yours,
> Daniel
>
You aren't the first, nor the last person to plan and/or promise
features that aren't available. The issue being that SQL isn't defined
in a way to track how to roll back more than the current transaction.
There may be ways to modify things so that the roll-back journal is
saved past the transaction boundary providing the longer roll back
period. There might be issues with making sure there is a definite order
to transactions from different threads.

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

Jean-Luc Hainaut
In reply to this post by Richard Damon

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 
<https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB%281%29.pdf>

J-L Hainaut


On 05/10/2018 20:27, Richard Damon wrote:

> 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.
>

_______________________________________________
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: [EXTERNAL] Persistent snapshots and rollbacks

Hick Gunter
In reply to this post by Daniel Kraft
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

If you use temp triggers, you can even keep the history tables in a separate database from the live tables and even switch to a new history database every week or month or whatever schedule fits your application.

Reading the old state is just a group by primary key query over the live and history table. And restoring a historic state just means saving the result set of the old state query into the live table.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Daniel Kraft
Gesendet: Freitag, 05. Oktober 2018 17:40
An: [hidden email]
Betreff: [EXTERNAL] [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



___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] Persistent snapshots and rollbacks

Daniel Kraft
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 approach that I hadn't thought of
before.  That makes "manual" handling of the history transparent to the
code that performs operations.  (And avoids the need to instrument /
change every line of code that does a change to the database.)

However, that still means that I need to build the system for a
particular database schema (set of tables and fields in them), right?
In my situation, I would ideally like to apply a method that works "out
of the box" independent of the underlying database schema (because I'm
building an application *platform* rather than a particular
application).  The sessions extension seems to provide this, so I'll try
that one first to see if it fits my requirements.

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