Sqlite3 table with a limited maximum number of rows (optional)

Question

I store data based on intervals (gps location), and I don't want the database to inflate, so I determined the maximum number of rows that it can go to, and then just deletes the oldest row every time I insert a new one.

Now the database expert has looked at my code, and he says that his method is inefficient, because deleting a row from the database is the most amount of time / memory / procedures, and I should avoid it at all costs.

He says that instead I should run the oldest line (update) after reaching MAX. (so every time it goes from top to bottom)

This means that I need to save a separate "header" table in order to save the current pointer to the oldest row and update it on every insert (I don't want to lose it if the application crashes). is it really more efficient? any other ways to do this more efficiently?

Answer 1

Including a database table in a circular buffer is stupid. If you really want to use this approach ...

• Do not use database, just use data file and IO
• • In your data file, each record will be a fixed size
• • • [Timestamp] [Latitude] [Longitude]
• • • You can use string or binary representations of your data, it does not matter if they are fixed.

---------- ---------- gps.dat

[Ring Pointer] [Time Stamp][Latitude][Longitude] [Time Stamp][Latitude][Longitude] ... [Time Stamp][Latitude][Longitude] 

• Ring Pointer is a binary representation of a long integer
• The first time you create a file, you set its size to LONG_INTEGER_SIZE + (MAX_RECORDS * RECORD_SIZE)

If you want to add an entry:

• Read [Ring Pointer] from the beginning of the file
• Write [Ring Pointer] + 1 to the beginning of the file (so that people don’t get confused, save the [Ring Pointer] variable as soon as you write the new value back to the file)
• Go to the LONG_INTEGER_SIZE + folder (([[Ring Index]% MAX_RECORDS) * RECORD_SIZE)
• Write a new entry at this location