-
|
Summary: I am working on some data syncing algorithms. I would like a way to "rollback" the DB to a known previous point and then re-apply some changes. You could think of this as a fork and rebase. I am struggling to understand the best route to get this behaviour with GRDB / SQLite and any guidance would be appreciated. Details:
Current thoughts:
Is this even possible with SQLite / GRDB or should I give up and try doing to rollback journalling as an App concern? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
|
Hello @andykent, The word "snapshot" is full of different meanings, so let's first clarify what's a snapshot for SQLite (and GRBD). It is the same "snapshot" as in "snapshot isolation" (Wikipedia definition, SQLite definition). It is the behavior exhibited by one particular runtime database connection, that starts when the connection enters snapshot isolation, and ends when the connection leaves snapshot isolation. During snapshot isolation, the connection sees an unchanging "snapshot" of the database file as it existed at the moment isolation was started. If other database connections perform concurrent writes, those writes are not seen by the isolated connection. In SQLite, snapshot isolation starts with a read transaction, and ends when that transaction ends. In a WAL SQLite database, snapshot isolation does not prevent concurrent writes. In other journaling modes, snapshot isolation is also guaranteed, but this is because concurrent writes are forbidden. -- Enter snapshot isolation
BEGIN TRANSACTION;
-- Read count number 1.
SELECT COUNT(*) FROM player;
-- Read count number 2, guaranteed to be equal to count number 1.
SELECT COUNT(*) FROM player;
-- Leave snapshot isolation
COMMIT;
-- Enter snapshot isolation again
BEGIN TRANSACTION;
-- Read count number 3, not guaranteed to be equal to count number 1.
SELECT COUNT(*) FROM player;
COMMIT;Snapshot isolation is a purely runtime concept, tied to one particular database connection, not to one particular database file. This means that when the database connection is closed, or when the process killed, this isolation ends, the "snapshot" vanishes. What you are asking for is different, because you are looking for "snapshots" that outlive one particular runtime database connection. Those are more like backups, a word you have used as well.
Yes. What you are missing is the lack of any SQLite api to define the "point in time" you want to refer to. Or rather, those apis exist, but such a "point in time" can only be referred to during the duration of a "snapshot isolation" (as described above), and are thus impossible to persist across process launches. So. I hope I could help you understand that SQLite does not provide built-in support for the feature you are looking for (I mean, not according to my knowledge and understanding). That said, copying the whole db file is one of your options indeed. You can choose between Doing smart tricks with the WAL file could be an option as well. As long as no checkpointing operation is performed, the main database file does not contain changes stored in the WAL file. It is possible to prevent WAL checkpoints until you decide to define the next "backup" 1. Maybe SQLite could support going back in time if you just get rid of the WAL file? Take great care, though. There's no public SQLite api that tells "discard the whole WAL, I don't care about anything there". And check How To Corrupt An SQLite Database File first. Footnotes
|
Beta Was this translation helpful? Give feedback.
-
|
Hello and thank you @groue! This is a super helpful reply, answered all my questions and helped me understand the snapshot capabilities better. Whilst the end result isn't great news it has at least saved me from wasting a lot of time on the wrong path so thanks again. I think I am just going to have to handle the rollback logic in Application code for now. |
Beta Was this translation helpful? Give feedback.
-
|
Happy I could help, Andy! Happy GRDB, and happy 2023! |
Beta Was this translation helpful? Give feedback.
Hello @andykent,
The word "snapshot" is full of different meanings, so let's first clarify what's a snapshot for SQLite (and GRBD). It is the same "snapshot" as in "snapshot isolation" (Wikipedia definition, SQLite definition).
It is the behavior exhibited by one particular runtime database connection, that starts when the connection enters snapshot isolation, and ends when the connection leaves snapshot isolation. During snapshot isolation, the connection sees an unchanging "snapshot" of the database file as it existed at the moment isolation was started. If other database connections perform concurrent writes, those writes are not seen by the isolated connection.
In SQLite, snapshot iso…