mORMot Open Source

isa

Member

Registered: 2018-01-09

Posts: 22

How to enable SQLite Journal File Persistence?

Hi, I have a server on which I have multiple db files linked to (so multiple server instances running in this application). And very rarely it happens that somehow a .db file gets locked. Might be a bug in my code or something else not sure.
When this happens, a .db-journal file gets created and all modifications from the moment on when the actual .db file got locked are stored in that journal file one.

However. When such a lock occurs. Chances are that the UI of my server application is also locked, so I cannot stop the server normally, and have to kill the process.
When I restart the server, I would expect (/Want) that the server detects this journal file and merges it with the actual .db file. But instead it disposes this file and the modifications starting from when the actual .db got locked are lost.

I read that you have SQLite options where you set the journal file to persistent, which will merge the journal file with the db file. Does that have to do anything with my question? Can I somehow enable this option in the delphi mormot framework? Or is it already enabled, but me killing the process makes it impossible to merge this file?

Also is there a way I can still merge an existing journal file with a db file later on? Or perhaps a way to read the content of the Journal file, so that I can at least see. what transactions were lost?

Thanks in advance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: How to enable SQLite Journal File Persistence?

By definition, the SQlite3 database, in the mORMot context, should always have a single process/thread accessing it.
You will expose it as a REST server, either via ORM or SOA high-level types.

If you don't, there are default restart way as done by Sqlite3 itself.
It is not part of the framework itself, but a Sqlite3 feature.
See https://sqlite.org/atomiccommit.html

You have the corresponding journaling options in TSQLDataBase: see WALMode/Synchronous/LockingMode properties.