Self replies.... sorry its kinda lame huh? Could you add a column to
your schema such as "LOG #" or so, and do all your work in the same
table. So if your data max limit is 3 you would have...
rowid data logNum
1 x 1
2 y 1
3 z 1
4 a 2
5 b 2
Just thinking out of my finger tips.
On Sun, Jun 15, 2008 at 10:20 PM, Rich Rattanni <[EMAIL PROTECTED]> wrote:
> I am working with SQLite in an embedded environment. With synchronous
> = full, I can say large inserts are abysmal (of course I need the
> protection that full synchronous offers). Of course, as always what I
> call large may not be what you call large. Keep in mind that sqlite
> will make a journal file equal to roughly the size of the data you
> will be moving. Instead of moving the data to a backup, could you
> create a new table and start dumping data there? You know, in your
> program remember the current table (DataLogX). When it comes time to
> roll over the log .... "CREATE TABLE DataLog(X+1) .....Just one man's
> opinion.
>
>
> On Fri, Jun 13, 2008 at 5:25 AM, Al <[EMAIL PROTECTED]> wrote:
>> 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
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
