In this post, I will try to give a quick overview of setting up Berkeley DB to Sync with Mobile Server. It is important to note that we will be overriding the SQLite deployment.
Step 1: Building the Berkeley DB
Here is the document to get you started.
Step 2: Rename Binaries
Once you have built the binaries (libdb_sql50.dll and dbsql.exe) rename those files to sqlite3.dll and sqlite3.exe.
Step 3: Copy File to the Mobile Server
Copy those files to the Mobile Server under the following path:
Modify the following file:
Add the following entry that is in bold lettering:
This is optional. The sqlite3.exe is equivalent to msql or sql*plus.
You should now be able to run the setup.exe for the SQLite Win32 and the client will be set up for Berkeley DB SQL API.
If you perform a sync with the SQLite client, your directory structure will look something like this:
08/26/2010 01:25 PM 41,984 default.db
08/26/2010 08:42 PM 67,584 fod.db
08/26/2010 08:42 PM 20,480 OSE$default.db
08/26/2010 08:42 PM 34,816 OSE$fod.db
By using the Berkeley DB SQL API, your structure will differ by having <database>-journal folders.
08/26/2010 09:44 PM 32,768 default.db
08/26/2010 09:44 PM <DIR> default.db-journal
08/26/2010 09:44 PM 32,768 fod.db
08/26/2010 09:44 PM <DIR> fod.db-journal
08/26/2010 09:44 PM 32,768 OSE$default.db
08/26/2010 09:44 PM <DIR> OSE$default.db-journal
08/26/2010 09:44 PM 32,768 OSE$fod.db
08/26/2010 09:44 PM <DIR> OSE$fod.db-journal
For a lot of the Oracle SQL Developers, some things you will need to brush up on while using the new SQLite environment. Lack of Stored Procedures… but since Berkeley DB source code is availble as open source, a developer should be able to add functions to be able to perform the same tasks. SQLite doesn’t support sequences in the same way Oracle supports sequences. So, to have the ability to sync your sequences to the client, you will need to performance the following sql statements to keep your client and server in sync for sequences.
To get the nextval of a sequence
SELECT IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
WHERE NAME = 'MY_SEQ';
To update the sequence with the current value
SET CUR_VALUE = IFNULL((CUR_VALUE + INCREMENT_BY ), MIN_VALUE )
WHERE NAME = 'MY_SEQ';
Other differences are obvious ones like SQL functions. As I do a little bit more development with Berkeley DB, I will try to benchmark it against the SQLite flavour and the Oracle Lite database to see how everything stacks up.