B4J Question Best Practice to manage table size in SQLite?

Steve Piehl

Member
Licensed User
Longtime User
What is the best approach to managing the table size in a SQLite database? I have a project where I am writing to the database every 20 seconds an update record. I need to capture the records...but I don't need them long term. Basically after I have XXXXX (say 10000) records, I am fine purging the earlier records.

I am looking for advice/code samples to manage the number of rows in the database on a First-In First-Out (FIFO) basis. I know in other DBMS there are functions like Truncate to handle this...but I don't think SQLite supports this.

I have considered looking at the RowID in the tables as suggested in this post. But I am don't believe this will accomplish the desired FIFO. If I want to truncate a table at 10,000 rows the technique described would work...but I think it will use Last-In First-Out (LIFO) instead of FIFO. I understand it, the first 10,000 records would remain the same every time the code was executed because their RowID's are the lowest in the database.

I believe I will have to create and use an index on my table...but beyond that I am still learning SQLite syntax and methods. Any guidance is greatly appreciated.
 

keirS

Well-Known Member
Licensed User
Longtime User
ROWID would work. Your delete statement would be something like:

B4X:
DELETE FROM ATABLE WHERE ROWID IN(SELECT ROWID FROM ATABLE ORDERBY ROWID DESC LIMIT -1 OFFSET 10000)

It would leave you with the latest 10K records.
 
Upvote 0

Steve Piehl

Member
Licensed User
Longtime User
Thank you very much for the query sample. This works wonderfully. I just had to change the table name and add a space in between "ORDER" and "BY"

As I am sure you can tell...I am still working on my SQL skills.
 
Upvote 0
Top