Al wrote:
>
> I'm using sqlite to implement a fast logging system in an embbeded system. For
> mainly space but also performance reason, I need to rotate the databases.
>
> The database is queried regularly and I need to keep at least $min rows in
> it.
>
> What I plan, is inside my logging loop, to do something like this.
>
> while(1) {
> read_informations_from_several_sources();
> INSERT(informations);
>
> if(count > max) {
> /* I want to move all oldest rows in another database */
> BEGIN;
> INSERT INTO logs_backup
> SELECT * FROM logs order by rowid limit ($max - $min);
>
> DELETE FROM logs WHERE rowid IN (SELECT rowid FROM logs ORDER BY rowid
> LIMIT ($max - $min));
> COMMIT;
> }
> }
>
> rowid is an autoincremented field.
> I am not an sql expert, and would like to find the fastest solution to move
> the
> oldest rows into another database. Am I doing silly things ? Can it be
> improved ?
>
Al,
There have been a few past discussions of using SQLite to create a FIFO
table with a fixed maximum size. See
http://www.nabble.com/limiting-table-size--to2035232.html#a2063150 for
one way to do this. You would have to modify the insert trigger to make
the backup copy of the old row before it is deleted.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
