[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
From:       Domingo Alvarez Duarte <mingodad () gmail ! com>
Date:       2016-10-03 12:49:48
Message-ID: 82d7f2bf-3440-2a7a-c467-4ac8dda83a98 () gmail ! com
[Download RAW message or body]

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

[prev in list] [next in list] [prev in thread] [next in thread]