I’ve written a project using a RealSQL database. Everything functions well, but…
I want to limit the database size to 50,000 records, so I use rs.deleterecord to remove the oldest records. The delete works correctly because the next time I open my database I have 50k records, but the database size is still growing! Its grown from 77Kb to 300 Kb in less than a day. I remember from my days working with SQL in Microsoft access that occasionally you had to “compress” a database to get rid of the empty space left from when you delete records. How do I do this with a RealSQL database? I guess instead of deleting records I could create a new database and copy the records I want to save into it, then delete the old DB and rename the new one, but that seems like the long way around.
take a look at the VACUUM sqlite command.
Yes! That is exactly what I want, but for RealSQL.
Or are you saying I have to use SQlite in order to compress?
REALSQLDatabase is also SQLite, but older version.
Be aware that vacuum can change some IDs when it renumbers them.
Another way would be to make new database and copy all tables with data.
REALSQLDatabase is SQLite, but hasn’t been updated in a while because it was replaced with the SQLiteDatabase class back in 2013. So you should switch to SQLiteDatabase.
Regardless, the VACUUM command is what you want to use to remove deleted rows from the file.
[quote=308220:@Christian Schmitz]REALSQLDatabase is also SQLite, but older version.
Be aware that vacuum can change some IDs when it renumbers them.
Another way would be to make new database and copy all tables with data.[/quote]
To be clear, the docs say this:
“The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.”
I think that is wrong @Greg O’Lone . At least it was wrong. the ROWID will change with a vacuum regardless of having a IPK or not. Never use ROWID as your Key.
I copied that straight from the docs as sqlite.org. But I’d agree, ROWID would be changed by VACUUM and that “explicit INTEGER PRIMARY KEY” probably means a column other than ROWID.
As I know, RealSQL is a 3.x version of Sqlite. So it should support trigger?! In that case one workaround could be to trigger the deletion before inserting. The unused/free space of the delete-statement could directly be used by the inserted data.
Just an idea…