On May 10, 2011 4:09 PM, "Lynton Grice" <lynton.gr...@logosworld.com> wrote:
>
> Hi there,
>
> how can I implement /
> mimic a type of "rotating log"?
>
> So in my mind I am thinking that perhaps I can LIMIT the size of the
> SQLIte DB to say 5 MB? And once the DB reaches that size it starts
> INSERTING new logs over the earliest records in the database?
>
What I did was to create a separate thread / process which is suspended most
of the time. Every certain interval, it wakes up and do clean up according
to some conditions.
For my case, it will DELETE records older than 60 days by using the
timestamp column I added. I used REAL for the timestamp as I read somewhere
that it will save space and be faster for sorting. It also used its own DB
connection so SQLite will deal with any concurrency issues.
DELETE FROM logs WHERE timestamp < julianday('now','-60 days')
Alternatively, you can keep the last N number of records, for example 1000.
DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY timestamp
DESC LIMIT 1000,-1)
By issuing DELETE statement, you will actually reuse the storage space for
subsequent new data. Issue VACUUM if you want to slowly reclaim unused
space.
Afriza N. Arief
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
