Re: [sqlite] Persistent snapshots and rollbacks

[Jean-Luc Hainaut]

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 



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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Richard Damon]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Chris Brody]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Richard Damon]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Abroży Nieprzełoży]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[James K. Lowden]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  do the trick for you?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Simon Slavin]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Daniel Kraft]

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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Simon Slavin]

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



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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Gerry Snyder]

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 copies of it,
backup and restore could be a cumbersome way to do what you want.


Gerry Snyder
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Keith Medcalf]

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-
>boun...@mailinglists.sqlite.org] On Behalf Of Daniel Kraft
>Sent: Friday, 5 October, 2018 09:40
>To: sqlite-users@mailinglists.sqlite.org
>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Dominique Devienne]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Chris Brody]

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 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users