How do you efficiently trim an SQlite database down to a given file size?

CREATE TABLE log_messages (  integer id primary key, -- no autoincrement here  datetime event_time,    -- for last id retrieval  char(248) message       -- fixed field size)

Let's assume and integer field is 4 bytes long, a datetime field is also 4 byes long, and each character is one byte long. Then each record is 256 bytes long, and your space limit is 1Kb. 4 records.

Initialize the table with consecutive ids

1 | 2011-05-01 23:00:01 | null2 | 2011-05-01 23:00:01 | null3 | 2011-05-01 23:00:01 | null4 | 2011-05-01 23:00:01 | null

When your program start you run a query like:

SELECT id FROM log_messages ORDER BY event_time DESC LIMIT 1

The result of this query is 4, now you add 1, since the maximum number of records is also 4, 4 + 1 = 1, that's the id of the record you need to update.

UPDATE log_message SET message = "new message", event_time = NOW() WHERE id = 1

For the next record, you simply add 1 to the latest id you have in memory.

Hope you get the idea.

when you have a "right-sized" database then count the number of log_message rows.

SELECT COUNT(*) FROM LOG_MESSAGE

Store this number.

When you want shrink the file issue the count command again. Calculate the difference, delete that number of rows from your database, then VACCUM.

This can only be approximate but it will get you to near 1GB pretty quick. If you are still over you can go back to the 100 rows at a time method.

If you have required rights to FS, the best way, I think, would be to create a new log db and apply rotation of some kind to db files (deleting the oldest).