Looks like the experts have given good advice. I'll try a different tack...
Have a look at http://www.sqlite.org/cvstrac/tktview?tn=1476 The author pointed me to this patch in response to my question about keeping a foreign sequence. But, I think the patch could work equally well for you, and for the poster of the 'ring buffer table' question. If you were to set-up a sequence with a "max" of 10,000, and set it to cycle, you could then use this sequence to set the primary key in your log table. So, if your log table looked like: create table log_10k (log_oid integer primary key, timestamp, reading); Using the enhancement, and a sequence named "sq10k", you could do everything in a single SQL statement: insert or replace into log_10k values (nextval('sq10k'), '2005-12-22 00:00:01', 12345); It should add records until the sequence (nextval()) wrapped back to 1, then simply overwrite the lowest numbered ones. I don't know the cost of using these enhanced functions versus the trigger suggestion given by others. This method has the added benefit of allowing you to tailor the size of the log table per installation by simply changing the values in the sequence record. Plus, once implemented, the sequence funcs are always available. The downside is you would have to fiddle with SQLite itself, and maintain a 'custom' build. For my project/environment, a Perl/CGI app on Solaris where I don't have shell/build access, building/maintaining a special SQLite build was too much hassle. I took the idea from the enhancement in ticket 1476, and built my own equivalent functions in Perl. Probably not really fast, but reasonable for a Web app in my environment. Good luck! -Clark ----- Original Message ---- From: Sean Machin <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, December 20, 2005 11:02:39 AM Subject: [sqlite] limiting table size? Hi All, I'm considering using SQLLite for an embedded project. Some of the data I'd like to store is timestamped sensor readings. I'd like to know if there is a way to configure a table so that it acts like a fixed length FIFO queue, e.g. stores 10,000 records then once full drops off the oldest record each time a new one is inserted. Can anyone tell me if we can do that with SQLLite? Thanks, Sean
