Dennis Cote <[EMAIL PROTECTED]> writes:
> 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.
>
Hello Denis,
Thank you very much for your link. I find the solution quite elegant
and I will compare the performance with the implemented one.
> HTH
> Dennis Cote
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Alain ENOUT, Phone: +33 4 93 00 16 82, Fax : +33 4 93 00 16 61
UDcast, 2455 Route des Dolines, BP 355, F-06906 Sophia Antipolis, France
UDcast: IP - Broadcast - Wireless http://www.UDcast.com
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
