Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
On Mon, Oct 3, 2016 at 11:51 AM, Richard Hipp wrote: > 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. This sounds good to built a maintanance program to keep just in case, I will go for this solution at the moment. > > 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. When working with PostgreSQL (not meant to flame) I would solve with partitioning: create a table which refers to a time period and move all records of that period to such table. In the cas eof sqlite I have to move the period table to another database, but that be simpler than dumping the whole database and archive it (I mean it could keep the original database online). By the way, my database is growing around 1 MB per day, so nothing scaring (at least now). In other words, I've a chance to study the problem and get a suitable solution. Thanks all, Luca ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
Hello Richard ! Ok I missed this point, but still while fighting to use sqlite3 with big databases I was thinking on some custom changes to allow sqlite relax some restrictions: 1- Create a new sqlite reserved table for register attached databases, this way every time a program try to open an sqlite3 database it will look if this table exists and will automatically attach then, also will allow views and triggers between attached databases. 2- For operations like "vacuum" and to force processes to reopen the database another sqlite reserved table could be created like "sqlite_open_instead" if that table exists it indicates anyone trying to open that database to open the one indicated there instead, this would work on any operating system (maybe not too elegant but it should work, or anything else using a similar mechanism). Cheers ! On 03/10/16 09:53, Richard Hipp wrote: On 10/3/16, Domingo Alvarez Duarte wrote: 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. That might work on unix. But on Windows, the operating system prohibits files from being renamed while the file is open. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
On 10/3/16, Domingo Alvarez Duarte wrote: > 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. > That might work on unix. But on Windows, the operating system prohibits files from being renamed while the file is open. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
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 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
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
It seems that you just want to keep access to all of your historic logging so rather than copy/backup the entire database you could just create a new archive DB (or open an old one), attach it, copy x records to the archive and then delete the same x records from the master. How big is your log database and how many records are added daily? Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 3 October 2016 at 12:57, Stephen Chrzanowski wrote: > Are you looking to keep the logs in the same file, or, are you looking to > put your log entries in a new file? > > If you're interested in just keeping a single file, and if you've got > access to change your code that is writing to the database, then, what I > would do is "create table if not exists Logs (LogDate, LogMessage)", then > do the insert. Then you can purge previous logs with a simple DROP TABLE > whenever you want to clean things up. No worries about OS file handling, > unless you have a burst of log entries, file sizes are going to pretty much > stay the same size, etc, since you'll be just freeing pages up internally > in the database. > > As others have mentioned, if you plan on a log per day, then, you'll need > to close the file handle, reopen with a new file and database. > > On Mon, Oct 3, 2016 at 5:18 AM, Luca Ferrari 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)? >> >> Thanks, >> Luca >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
Are you looking to keep the logs in the same file, or, are you looking to put your log entries in a new file? If you're interested in just keeping a single file, and if you've got access to change your code that is writing to the database, then, what I would do is "create table if not exists Logs (LogDate, LogMessage)", then do the insert. Then you can purge previous logs with a simple DROP TABLE whenever you want to clean things up. No worries about OS file handling, unless you have a burst of log entries, file sizes are going to pretty much stay the same size, etc, since you'll be just freeing pages up internally in the database. As others have mentioned, if you plan on a log per day, then, you'll need to close the file handle, reopen with a new file and database. On Mon, Oct 3, 2016 at 5:18 AM, Luca Ferrari 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)? > > Thanks, > Luca > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
* Richard Hipp: > 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. The journal, SHM and WAL files will also not be renamed at the same time on POSIX systems, which could have nasty consequences. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
> On Oct 3, 2016, at 11:18 AM, Luca Ferrari wrote: > > 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)? You could use the attach/detach [1] functionality to transparently roll the logs over. Something like main -> attach current -> insert current.log Every now and then, change what ‘current’ points to. [1] https://www.sqlite.org/lang_attach.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
On 10/3/16, Luca Ferrari 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. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
On 3 Oct 2016, at 10:18, Luca Ferrari wrote: > 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)? I think that your application must be responsible for this, not sqlite3. I don't know about a "right" way to do it, but I can describe what I did in a similar situation. In my case it seemed natural to start a new database every day, at 00:00 UTC. Hourly, weekly, monthly, or according to a size-related criterion may suit your situation better. Each time the application has something to write to the database, it must calculate the file name to use. If this has changed since the last time, the application must close the current database file and create a new one. For example, when the date changes from 2016-10-02 to 2016-10-03, it might be time to do this. Alternatively, the application could track the number of entries in the database and change over when the count reaches 100,000 or whatever. You need to have some idea how long, or how many entries, it takes to reach your chosen file-size limit. When reading the database, your application will need to identify and open as many files as necessary so as to avoid ignoring relevant data. I hope this helps. Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
