Wouldn´t a SELECT COUNT(*) just read the table header and get the amount of records from there? That should be faster and simpler than maintaning a separate table for the counters.
-----Original Message----- From: Paul Bohme [mailto:[EMAIL PROTECTED] Sent: Viernes, 23 de Diciembre de 2005 02:52 p.m. To: sqlite-users@sqlite.org Subject: Re: [sqlite] ring buffer table Julien LEFORT wrote: >Hi, >I would like to implement a log table with a finite dimension, for exemple a >table with 500 records, and when the last record is set in the table I would >like to come back at the first tuplet and write over the previous value >recorded. I think it's the way SQLite journal is implmented. >Is there any way to declare this table with these properties included, so I >don't have to add code to do this function? >Thanks > > I need something similar, so was planning a couple of simple tricks to keep the overhead low. I want to avoid "select count" queries as much as possible, so how does the following sound: - insert/delete triggers on the table I want to limit the size of - a separate table with a row that contains a single counter, that is incremented and decremented by the above triggers - a process that runs at intervals that checks the counter, and if over the limit trims the appropriate number of records Seems like a reasonable way to keep a table to a limited growth without too much of a hit on every insert. The counter table would be a serious hot spot in other databases, but SQLite's locking is simple enough that it doesn't seem like it will be a problem. -P
