B4J Question [Databases] How can I store ONLY the last 20 records for each unique key?

[Mashiane]

Ola

What is the best way to implement something like this.

Lets assume that....

1. A timer is running every 5 minutes, this sends a payload with a unique key, timestamp etc.
2. I receive the payload and save it to a database of choice. Over time, the database will be huge.
3. To deal with this "huge" problem, I'm assuming that I can just be able to ensure that at anytime, I can just store only the last 20 records that were added.
4. So the timestamp of the record depending on the "secret key" is crucial, and perhaps in descending order.

Thing is, how do I implement this?

Thanks

 

[Erel]

Get all items for a specific key, sorted DESC by the time column. Go over the results and find the timestamp of the 20 value. Delete all items with the key where the timestamp is smaller than that value.

 

[emexes]

If you're only storing 20 records, why even use a database? Perhaps instead use a 20-element array of records, and a pointer to tell you which element (0..19) was the most recently written one. Each time a new record comes in, increment the pointer (mod 20) and put the new record into that element.

The newest record is Array(Pointer) and the oldest record is the one just after that ie Array((Pointer + 1) mod 20)

Remember that some elements of the array will be empty until the first 20 records have arrived.

 

[emexes]

Hang on, I might have misunderstood.

Do you want to store "the last 20 records that were added" or "the last 20 records for each unique key" ?

 

[Mashiane]

Do you want to store "the last 20 records that were added" or "the last 20 records for each unique key" ?

Both apply, so a DB is needed.

 

[Mashiane]

Get all items for a specific key, sorted DESC by the time column. Go over the results and find the timestamp of the 20 value. Delete all items with the key where the timestamp is smaller than that value.

Use a TRIGGER / B4X Code

The best approach would be to create a TRIGGER on the database backend, or via code each time a record is added to check and process accordingly?

 

[aeric]

Something like:
DELETE FROM Log WHERE ID NOT IN (SELECT TOP 20 ID FROM Log ORDER BY ID DESC)

Edit: for MySQL use LIMIT 20 instead