Why not have TWO tables? Log_A and Log_B? When Log_A is full, DELETE everything from Log_B and start logging to it. When Lob_B is full, DELETE everything from Log_A and start logging to it again.
If you want, while logging to one, the other can be archived ... --- On Tue, 5/10/11, Simon Slavin <slav...@bigfraud.org> wrote: From: Simon Slavin <slav...@bigfraud.org> Subject: Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"? To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Tuesday, May 10, 2011, 7:34 AM 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 KEY, what will > happen when it reaches 5 MB? Will it just keep returning SQLITE_FULL or > similar? I guess I am looking for a "round robin queue" here? 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 fast. > While I'm on it, if I have an AUTOINCREMENT INTEGER PRIMARY KEY with a > LOGGER implementation, and the integer reaches it's limit (even though I > am deleting previous records), will the sqlite database assign "un-used > primary keys" (previously deleted) to any NEW inserts? A SQLite integer can get /really/ big: 2^63. There's no way you could ever write enough records to push it over the limit. Your hardware will disintegrate first. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
