Hello !
Thinking about this and the problem I'm experiencing with big databases
with sqlite3 "vacuum" probably could be a good idea to use a flag in the
sqlite3 header to inform other processes to reopen the database.
Right now every time sqlite3 would perform an operation on a database it
aquires a lock on the file and check to see if the schema has changed,
at the same time it can check if it needs to reopen the database if a
flag is set, this way it can work in any operating system.
Cheers !
On 03/10/16 06:51, Richard Hipp wrote:
On 10/3/16, Luca Ferrari <fluca1...@infinito.it> wrote:
Hi all,
in one of my application I use a sqlite3 database as a log of
activity. As you can imagine the file grows as time goes by, so I'm
figuring I've to substitute it with an empty one once a good size is
reached.
What is the right way to do it without having to stop the application
(and therefore without knowing when a new I/O operation will be
issued)?
Does sqlite3 provide some facility that could come into help (e.g.,
connected databases)?
Safe way: In a separate process, use the backup API
(https://www.sqlite.org/backup.html) to copy the content of the main
DB over to a separate DB, then "DELETE FROM log;" on the main DB.
This will work without any cooperation on the part of the application.
But it does involving a lot of I/O.
Alternative: Modify the application so that it automatically detects
when the database is getting to large (perhaps using PRAGMA page_count
- https://www.sqlite.org/pragma.html#pragma_page_count) and then (1)
closes the database connection, (2) renames the database file to a
backup, and (3) reopens the main DB and reinitializes the schema.
You cannot rename a database file while another process has that
database open. Windows simply will not allow that. If you do it on
Unix, then the process that has the file open will not know that the
file has been renamed and will continue to write to the original file.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
