classic save possibility
Is there any possibility to achieve in sqlite the classic Save functionality. I mean such a scenario: create a new file - new database, file edition - "write" transactions, close the application with classic question "save the changes to disk" - what then ?. Additionally, it is possible to save at any time by explicitly running the Save option. Note! I wants to avoid closing/reopening all connections because its hard in my case. I'm thinking about using WAL file with manual checkpoints control where checkpoint act as Save. Is this possible. If yes - pros and cons. Thanks
(2) By Igor Tandetnik (itandetnik) on 2021-04-30 21:32:21 in reply to 1 [link] [source]
Open a transaction and just keep it open. "Save" would run
BEGIN again (so that a transaction is open at all times). "Quit without saving" would
Another possibility is to use the online backup API. You can use a temporary database and then use this to save it to a file or to load it from a file.
Thanks. I have already considered that but in my case I have to have opened connection all time to both databases and I'm not sure online Bakup API do not affect connections of target database. I'm not found enough information for this in documentation. As in doc - target connection used in backup operation cannot touch db during the backup operation or behavior is undefined. I think this could be dengerous pattern for me.
Thanks. Transactions are already used to perform edition in consistent way so its not possible in my case.
Could you perhaps use savepoints instead? They do nest, where ordinary transactions do not.
Just beware that you cannot use
END inside another transaction, even if it is the savepoint type.
(7) By Igor Tandetnik (itandetnik) on 2021-05-01 11:32:56 in reply to 5 [link] [source]
I'm not sure I understand. What does it mean to require consistency for data that is not saved persistently?
Let's put it this way: if your application is killed or the computer loses power, what state do you want the database to be when the user next opens your application - the state after the most recent "edition in consistent way", or the state after the user last executed "Save" command?
I talked about existing application with more than 1 milion sourcecode line, where transactions are already used in this way to check consistency of single operation(edition) f.e. Fk, checks. Moreover transactions are used intensively to perform more sofisticated purposes and its not possible to change that behaviour by me in predicatable time. So its the reason why I try to find something other as my solution. Using transaction in that way is still required even if transactions could be rejected in case of no save(are not persistent). Of cource in case of crush some kind of data recovery process is required. I will also need to resolve this data rescuing. I not mensioned this earlier because of improving simplicity of my question as much as possible.
(9) By Richard Damon (RichardDamon) on 2021-05-04 11:58:58 in reply to 1 [link] [source]
A basic comment, if by "Save", you mean functionality like with a normal word processing document or a spread-sheet where you can save the document, then edit the document for a while, and have the option to revert back to that previous save point if you want, then you need to understand that databases just don't work that way.
Every Transaction basically is that 'SAVE' command.
Either your application needs to keep a change history to allow it to perform an 'undo' function, or you need to 'backup' your database to give you the save file you could roll back to.
-WAL mode with manual checkpoints could work sort of like that, where the -WAL file becomes a record of the changes that have been done, but you are working outside the documentation (I think) if you do the roll-back by just deleting the WAL file. That becomes your risk, you would be depending on an operation that is not documented to work.
RichardDamond thanks for your answer. So if I understood the only problematic case is rolling back transaction written in WAL file. Your description is due to deleting WAL file directly on disk. Yes it could be dangerous to do that. I hope that option SQLITE_FCNTL_PERSIST_WAL with turning off autosevepoint prohibit transvering data from WAL to orginal file after closing all connections and therefore I sill stay only in documented behaviour. Unfortunately, I cannot find equivalent of this option in PRAGAs and haven't yet tested it because I used System.Data.Sqlite provider. Meybe someone aready used the flag SQLITE_FCNTL_PERSIST_WAL and already know its behaviour in described case.
(11) By Richard Damon (RichardDamon) on 2021-05-04 13:28:36 in reply to 10 [link] [source]
The issue is that once you commit the transaction and it gets written to the WAL file, the ONLY way that I know of to 'undo' that transaction is to delete the WAL file, and the behavior of that is technically undefined.
So yes, you can keep your main database from being updated, but every usage to read the data from it will see the data in the WAL file, so you haven't established a way to roll back to that last 'save' command. That would require doing something undocumented.
ok I understood. So I will try to use "mirroring" orginal file - Copy ordinal file to mirror, operate on the mirror and finaly on "Save" event overwrite orginal file by the mirror. Proper closing/reopening connections looks difficult but if there is no native solution for my requrements I try do it. Thanks a lot.
(13) By Richard Damon (RichardDamon) on 2021-05-04 15:10:38 in reply to 12 [link] [source]
Be sure to think through the ramifications of what you are trying to do, and if there is any possibility of multiple actors working on the same database.
Depending on the requirements, saving a. mirror of the old as a 'backup' that you can roll back to, or working with the mirror might be better. It somewhat depends on how often/likely you are to want to do that rollback.
Saving the backup and working with the original will avoid needing to worry about merging conflicts from other accesses to the file unless you actually want to do a rollback, but this won't seem like the Document case, but in the document case, normally no one else is allowed to access the document while you are editing, or maybe only in a read-only mode.
If you want to recreate that, make the copy and use it, but start a BEGIN IMMEDIATE transaction on the original so no one else can modify it. Then rolling in the new database won't need the check for conflicts.
The fundamental issue is a database really isn't just like a simple document, so a flow that treats it as one will have problems.
Thanks a lot for your opionion. Fortunately, I have single writting actor and few actors with readonly access. Epecially important to me is a point with imediate transaction on orginal, good idea. I will implement it with simple technical difference - I open file directly to lock the file. I don't need access to orginal file by connection. All actors will use "mirror" not orginal file. Locking is neccessary because of attempts of file change/removal from os level.