Re: [sqlite] Implementing fast database rotation
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
> sqlite-users@sqlite.org
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing fast database rotation
"Rich Rattanni" <[EMAIL PROTECTED]> writes:
Hello Rich,
Thanks for your proposal, but my purpose is to rotate the databases
and remove the oldest files to avoid filling my file system, so I
can't use the same table as I want to changes files.
The solution which is a good compromise for me for the moment is
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 */
open_new_database();
attach_old_database_and_fiddle_with_database_handle();
INSERT INTO new_table SELECT * FROM old_table order by rowid desc limit
$min;
close_old_db()
}
}
with my current values ( max ~10 and min ~1000), the switch
operation takes around 150ms which is acceptable for me.
> 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...
>
> rowiddata 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
>>> 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
>
--
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing fast database rotation
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
Re: [sqlite] Implementing fast database rotation
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...
rowiddata 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
>> 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
Re: [sqlite] Implementing fast database rotation
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
> 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
[sqlite] Implementing fast database rotation
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
