[sqlite] Implementing fast database rotation

[Al]

Hello,

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 
?

Thanks in advance.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users