Here is a quick overview for the Oracle Lite guys that are going to be or planning to move to Berkeley DB / SQLite API pretty soon.
There are plenty of options in Berkeley DB that make the the database more stable then Oracle Lite on the mobile device.
I was playing around with the Berkeley DB last night and here are some of the features I could see really useful that Oracle Lite does not have out of the box or at least you aren’t able to change the default option.
- auto_vacuum – By turning on this option, basically it reduces the high watermark on the database when bulk of records are deleted. Oracle Lite has the use of the defrag tool, but that has to be run manually and it has to be provided to the users. It does not come built in. auto_vacuum has three modes: NONE, FULL, and INCREMENTAL. Read up on the auto_vacuum pragma here and the VACUUM statement here. Note that auto_vacuum does not defrag the database, but it can fragment it more. VACUUM command is the defrag option.
- journal_mode – Journal mode is the rollback segment of the database. I am not going to repeat all of the 6 modes but here is a quick highlight: OFF, DELETE, TRUNCATE, PERSIST, MEMORY, and WAL (Write-ahead-log). For users in a synchronization environment, the following may be your best options in the following order MEMORY, TRUNCATE, and DELETE (Normal Mode). Since most of your data is stored on the server, it is probably safe to use a rollback segment that is in volatile memory. Read more about the journal_mode here.
- synchronous – There are three modes here; FULL, NORMAL, and OFF. The default is FULL. What this pragma does is check the database on occasion to ensure that information is written to disk. So if your system crashes, you will be relatively safe from a corrupt database. Now with the mobile server, you always have a backup of your data on the server, so even if your client database becomes corrupt, completely refreshing the clients isn’t a problem. They will lose the data the was updated since the last sync. So, the damage is minimal. Knowing this, you can decide how synchronous you want your client database to be. By turning off the synchronous mode, some operations can improve by 50x. Something to consider.
As I get myself a little more familiar with Berkeley DB, I hope to have some more Do’s and Don’ts posted.