If this has already been suggested, I apologize.
Add an integer column with a UNIQUE ON CONFLICT REPLACE constraint.Then
after you figure out how many entries are enough (maxcount), insert each
row, specifying that column as mod((lastinsertrowid()+1),maxcount) or
however you specify a modulus
Hi Jay,
Wow, thanks for your detailed message below.much appreciated ;-)
I will try the PRAGMA and also the "msg_seq".great.
Lynton
On 10/05/2011 19:00, Jay A. Kreibich wrote:
> On Tue, May 10, 2011 at 12:42:14PM +0200, Lynton Grice scratched on the wall:
>> Hi all,
>>
>> Thanks
On Tue, May 10, 2011 at 12:00 PM, Jay A. Kreibich wrote:
>> I guess I am looking for a "round robin queue" here?
>
> I'd do something like this. This keeps a constant number of messages
> in the log. The "msg_id" provides a message counter, while the
> "msg_seq" is used to keep the round-robi
Without a view (but with a trigger) and certainly open to improvement
(9 is the
MAX_ENTRIES parameter):
CREATE TABLE "log" (
"id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
"seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE,
"data" CHAR);
CREATE TRIGGER "t
On Tue, May 10, 2011 at 12:42:14PM +0200, Lynton Grice scratched on the wall:
>
> Hi all,
>
> Thanks for your comments...much appreciated..
>
> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
> to say FIX the sqlite database size to say "5 MB"?
PRAGMA max_page_cou
Yup, fair enough...what I think would be better is to have s
variable set called something like "history_retain_time" (like Nico
said)..and perhaps a "log_check_interval" in DAYS or HOURS or
MINUTESwhatever suits the application.
Then perhaps on each insert you get the code to
Hey NIco,
Now this is great.in fact I was playing with an "update hook" the
other dayand was going to put the deletion logic under the
SQLITE_INSERT below
But your code looks better ;-) Thanks !
void update_callback( void* udp, int type, const char* db_name, const
char* tbl_name
On Tue, May 10, 2011 at 4:32 PM, Lynton Grice
wrote:
> I like the ON INSERT trigger.good idea. So perhaps you have a
> "setLogMaxSize" type function in C that allows the client program to say
> "hey, I only want the log to hold a max of 10 000 records".and then
> I do a select count(*) ins
Hi Enrico,
I like the ON INSERT trigger.good idea. So perhaps you have a
"setLogMaxSize" type function in C that allows the client program to say
"hey, I only want the log to hold a max of 10 000 records".and then
I do a select count(*) inside the ON INSERT type trigger and delete
en
Hi Simon,
Thanks for the feedback below, I will write some "delete logic" to run
from time to time...;-)
Lynton
On 10/05/2011 13:34, Simon Slavin wrote:
> On 10 May 2011, at 11:42am, Lynton Grice wrote:
>
>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
>> to say
Slavin wrote:
From: Simon Slavin
Subject: Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?
To: "General Discussion of SQLite Database"
Date: Tuesday, May 10, 2011, 7:34 AM
On 10 May 2011, at 11:42am, Lynton Grice wrote:
> BTW: if I am using SQLIte a
Or just a function to return the size of the current DB. Mind you,
automatically deleting rows from a log table isn't enough: you may
have to periodically VACUUM the DB, or you may have to setup
auto_vacuum (and incremental_vacuum).
I have code like this in one DB:
CREATE TABLE IF NOT EXISTS Log
>
> A round robin queue is fine. Every so often, to kill off old records do
>
> SELECT max(rowid) FROM myTable
>
> then in your code subtract from it however many rows you want to keep, then do
>
> DELETE FROM myTable WHERE rowid < firstToRetain
>
> It won't work perfectly but it's simple and
Why not use INSERT OR REPLACE to your advantage?
If you set the maximum number of log entries you wanted to keep, then kept
track of your log insert statement, you could wrap by
int this_log_entry_id=1; //initialize.. actually could be initialized by
getting the log entry id of the min date in
On 10 May 2011, at 1:57pm, Lauri Nurmi wrote:
> El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
>> On 10 May 2011, at 11:42am, Lynton Grice wrote:
>>
>>> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
>>> to say FIX the sqlite database size to say "5 MB"?
>>
El mar, 10-05-2011 a las 12:34 +0100, Simon Slavin escribió:
> On 10 May 2011, at 11:42am, Lynton Grice wrote:
>
> > BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
> > to say FIX the sqlite database size to say "5 MB"?
>
> There isn't one. SQLite would not know which rec
On 10 May 2011, at 11:42am, Lynton Grice wrote:
> BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
> to say FIX the sqlite database size to say "5 MB"?
There isn't one. SQLite would not know which records to delete.
> Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY
On May 10, 2011 4:09 PM, "Lynton Grice" 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 earl
On Tue, May 10, 2011 at 12:35 PM, Lynton Grice
wrote:
> Thanks for this, much appreciated. My application is written in pure C,
> so I guess I will not be able to use your C++ code?
>
It's actually just a thin coating over C, and the whole class is quite
small, so it would be easy to strip out th
Hi all,
Thanks for your comments...much appreciated..
BTW: if I am using SQLIte as a logger, what PRAGMA statement can I use
to say FIX the sqlite database size to say "5 MB"?
Also, lets say I have a AUTOINCREMENT INTEGER PRIMARY KEY, what will
happen when it reaches 5 MB? Will it just ke
Hi Stephan,
Thanks for this, much appreciated. My application is written in pure C,
so I guess I will not be able to use your C++ code?
Chat later
Lynton
On 10/05/2011 12:06, Stephan Beal wrote:
> On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach wrote:
>
>> I don't think sqlite (or any SQ
Hi Enrico,
Well I have been looking at using a nice light weight C logger.and
played around with LOG4C, Panthious have and also checked out using
syslog-ng, matlog and standard syslogbut in the end decided that for
this specific application an SQLite logger would work well.
I am
On 10.05.2011, at 12:06, Stephan Beal wrote:
> On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach wrote:
>
>> I don't think sqlite (or any SQL database, for that matter) is a perfect
>> fit for a logger, because there is a certain amount of write overhead.
>> Why do you think you would want to
On Tue, May 10, 2011 at 11:52 AM, Enrico Thierbach wrote:
> I don't think sqlite (or any SQL database, for that matter) is a perfect
> fit for a logger, because there is a certain amount of write overhead.
> Why do you think you would want to do this?
>
ALL db insertions in a db are, in effect,
Hi Lynton,
I don't think sqlite (or any SQL database, for that matter) is a perfect fit
for a logger, because there is a certain amount of write overhead.
Why do you think you would want to do this?
/eno
On 10.05.2011, at 10:09, Lynton Grice wrote:
> Hi there,
>
> SQLite is a perfect fit fo
On Tue, May 10, 2011 at 10:09 AM, Lynton Grice
wrote:
> 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?
>
> Is this possible?
The API doesn't
Hi there,
SQLite is a perfect fit for a logger, the only question I have is once
it is in production my database will grow rapidly, 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 D
27 matches
Mail list logo
