QSqlDatabase Size Limit Question

[ken123]

Is there a way to limit a fix number of records for all tables under QSqlDatabase? For example, I just need to store 100 records for all tables, if newer record comes in, I need to replace the oldest with the newest after 100 entries are filled. Is it possible? if so, how? Thanks.

[Lykurg]

You can create a trigger that does the job for you.

[ken123]

Can you give me some specific examples? I am still novice when it comes to DB. Thanks.

[Lykurg]

see http://en.wikipedia.org/wiki/Database_trigger. In basic a trigger is a sql command which is executed when a specific condition is true. In your case: If after an insert the row number is 100, delete the first ones.

[ken123]

I know I am asking a bit much, but are there any example on how to use any kind of trigger along with QSqlDatabase? Thanks.

[Lykurg]

It has nothing to do with QSqlDatabase or Qt in general. It is a pure SQL statement which is handled by the DBMS. Therefore you have to tell us, what DBMS you use. MySQL, SQLite... and for them, all the examples are on the URL I gave. So try it yourself first, and then post what you get and we will move on from that.

[ken123]

I am using SQLite. Assuming my table is called Professor with columns of: id, last, first.
id is auto-incremented. I can't really depend on id because it will increment every time I insert the data.
What I want do is to delete row #1 whenever number of row is reached 100.
I tried:
"delete from professor where rowid = 1;"
But it only delete it one time, it won't delete the 2nd time. Somehow it still remembers the row #1 I deleted.

What would be the SQL statement to do what I wanted? Thanks.

[Lykurg]

You can do something like

DELETE FROM professor ORDER BY rowid ASC LIMIT 1

DELETE FROM professor ORDER BY rowid ASC LIMIT 1

To copy to clipboard, switch view to plain text mode 

[ChrisW67]

The notion of "row #1" doesn't make sense unless you have a definite ordering of rows, e.g, by timestamp, insertion order or something else (you said you didn't want to rely on id (wisely)). Lykurg's example is using the internal rowid for this purpose. Anyway here is an example for Sqlite where each row is time stamped and only the most recent 5 are kept:

create table test ( stamp datetime, data varchar(10));
-- Create stuff in random time order
insert into test values (datetime('now', '-09 hours'), 'B');
insert into test values (datetime('now', '-04 hours'), 'G');
insert into test values (datetime('now', '-10 hours'), 'A');
insert into test values (datetime('now', '-03 hours'), 'H');
insert into test values (datetime('now', '-08 hours'), 'C');
insert into test values (datetime('now', '-01 hours'), 'J');
insert into test values (datetime('now', '-02 hours'), 'I');
insert into test values (datetime('now', '-06 hours'), 'E');
insert into test values (datetime('now', '-05 hours'), 'F');
insert into test values (datetime('now', '-07 hours'), 'D');
-- Display it in default and time order
select 'Default order';
select * from test;
select 'Time order';
select * from test order by stamp;
--
-- Keep only five most recent rows
delete from test
where rowid not in (
    select rowid
    from test
    order by stamp desc
    limit 5)
;
 
select 'Default order after delete';
select * from test;
select 'Time order after delete';
select * from test order by stamp;

create table test ( stamp datetime, data varchar(10));
-- Create stuff in random time order
insert into test values (datetime('now', '-09 hours'), 'B');
insert into test values (datetime('now', '-04 hours'), 'G');
insert into test values (datetime('now', '-10 hours'), 'A');
insert into test values (datetime('now', '-03 hours'), 'H');
insert into test values (datetime('now', '-08 hours'), 'C');
insert into test values (datetime('now', '-01 hours'), 'J');
insert into test values (datetime('now', '-02 hours'), 'I');
insert into test values (datetime('now', '-06 hours'), 'E');
insert into test values (datetime('now', '-05 hours'), 'F');
insert into test values (datetime('now', '-07 hours'), 'D');
-- Display it in default and time order
select 'Default order';
select * from test;
select 'Time order';
select * from test order by stamp;
--
-- Keep only five most recent rows
delete from test
where rowid not in (
    select rowid
    from test
    order by stamp desc
    limit 5)
;

select 'Default order after delete';
select * from test;
select 'Time order after delete';
select * from test order by stamp;

To copy to clipboard, switch view to plain text mode 

There are many ways to do it. There are also quite few ways to get unexpected results. The example may not do the expected thing if two recent timestamps are equal.

[ken123]

Thanks all for your helps, I'll give it a try.

[ken123]

It works. If I have over 10K worth of rows for the table, is there a way to improve the performance for this scheme? Or this is the best strategy to go with?
Currently if I used the following query statements as ChrisW67 suggested:

delete from test
where rowid not in (
select rowid
from test
order by stamp desc
limit 10000);

delete from test
where rowid not in (
select rowid
from test
order by stamp desc
limit 10000);

To copy to clipboard, switch view to plain text mode 

Thanks.

[Lykurg]

What do you do not like with that solution? Is it too slow? Improvements cannot be advised without knowledge of your real use case.
If e.g. you use no transactions and only add one row then querying 10000 ids and use them in a NOT IN is slow. In that particular situation my solution for deleting only the oldest is more efficient.

Maybe you can think of calculating the count of row which should be deleted and then delete just them.

[ChrisW67]

It works. If I have over 10K worth of rows for the table, is there a way to improve the performance for this scheme? Or this is the best strategy to go with?

I agree with Lykurg: we can only give general answers without specific knowledge of your actual usage patterns.

An index on stamp is probably good place to look for performance improvement of my query.
Different database systems will optimise the query different ways. You might also like to try alternate formulations of the query like :

DELETE FROM test
WHERE stamp <= (
        SELECT stamp
        FROM test
        ORDER BY stamp DESC
        LIMIT 1 OFFSET 5 -- keep this many records
    )
;

DELETE FROM test
WHERE stamp <= (
        SELECT stamp
        FROM test
        ORDER BY stamp DESC
        LIMIT 1 OFFSET 5 -- keep this many records
    )
;

To copy to clipboard, switch view to plain text mode 

You could also check the existing row count (a relatively quick query) before running an expensive query that might ultimately delete nothing.