Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

[Luca Ferrari]

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

[Domingo Alvarez Duarte]

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

[Richard Hipp]

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

[Domingo Alvarez Duarte]

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

[Paul Sanderson]

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

[Stephen Chrzanowski]

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

[Florian Weimer]

* 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

[Petite Abeille]

> 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

[Richard Hipp]

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

[Niall O'Reilly]

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

[sqlite] smartest way to exchange a sqlite3 database with another empty

[Luca Ferrari]

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