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.
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.
