Need and Introduction

A typical mobile phone application is based on request response pattern. Whenever application needs a data, it sends a web request to the server. The server processes the request on database kept at the server and sends a response to the application. However this approach tends to be contrary to the basic requirement that the wireless network bandwidth be optimally utilized. The ‘disconnected database model’ approach provides a solution to overcome this issue. Here, the database along with a database engine sits on the mobile phone itself, whenever the application needs a data, instead of sending web request to server, it sends a database query to the database residing on the handset. This reduces number of web calls to the server and helps to better the performance by decreasing response time. The disadvantage of this approach is that we might have stale data on handset. But this is acceptable if data changes less frequently. Database on mobile can be synchronized with database on server at regular time intervals to suppress the disadvantage. See figure 1 and 2.

Figure 1: Request Response model

Figure 2: Disconnected database model

This approach was advocated by Sun Microsystems for the j2me applications. It is also popular on other mobile platforms like BREW and Android. However, the traditional approaches for this solution, namely the JAVA2 ME SDK provided RMS store or BREW IDatabase, do not adequately address the real needs. This belief has seen the porting of embedded database systems onto mobile platforms, for example, Qualcomm has ported SQLite as an extension named ISQL on to newer versions of BREW. This extension works only on BREW SDK v3.1.4 and above. We cannot use that in developing mobile applications for handsets having SDK version 3.1.2. Hence, we decided to port SQLite for SDK version 3.1.2.

As a first step towards this, we started off with a lower version of SQLite and ported version 2.2.0 of SQLite on BREW version 3.1.2. Our experience and learning during this exercise is described in [2].

Currently SQLite has moved to version 3.x which provides much more features than their older version 2.x. Hence we decided to upgrade ourselves by taking a task of porting some release from 3.x version on BREW. As our needs were simple, we just wanted to successfully execute select, update, deleteand insert commands on handsets we opted for the version 3.2.8. Instead of integrating SQLite source code with our applications, we decided to make this version of SQLite as a BREW extension. This article describes steps involved in the work of making this extension from the source code of SQLite.

SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. Click here know more about SQLite.

Steps to Create SQLite Extension

Pre - requisites

Install BREW SDK 3.1.2(or higher version) and RVDS 3.0

Download elf2mod utility

Download SQLite version 3.2.8 source code

Step 1: Generating Source Code Files

Files for source code of version3.2.8 are in a folder named SQLite-e61382aed45a83ad. Henceforth, we will refer to this folder as root. All the source files are not ready made in the root\src folder. We need to create few files using some other files provided in the root folder. See the link for the realization http://www.sqlite.org/cvstrac/wiki?p=SqliteBuildProcess.

We need to generate files parse.h, parse.c, keywordhash.h, opcodes.h, opcodes.c, sqlite3.h using the following Unix commands.

Use sed command to generate file sqlite3.h using a file sqlite.h.in provided in root/src and a file VERSION provided in the root folder.

Put all the generated .h and .c files in root\src folder.

Step 2: Setting Up Visual Studio Workspace and Linking

Create a new BREW workspace and include the following files from the root/src folder:

analyze.c

attach.c

auth.c

btree.c

btree.h

build.c

callback.c

complete.c

delete.c

expr.c

func.c

hash.c

hash.h

insert.c

keywordhash.h

legacy.c

main.c

opcodes.c

opcodes.h

os_common.h

os.h

os.c

pager.c

pager.h

parse.c

parse.h

prepare.c

printf.c

random.c

select.c

sqlite3.h

sqliteInt.h

table.c

tokenize.c

update.c

utf.c

util.c

vacuum.c

vdbe.h

vdbeapi.c

vdbeapi.h

vdbeaux.c

vdbefifo.c

vdbeInt.h

vdbemem.c

where.c

Specify all the required paths in the properties of the project to eliminate all linking errors.

Step 3: Modifications in Files

In the source code, #includea file Locale.h and add the following line at the beginning of the application source code:

setlocale(LC_ALL,"")

SQLite source code has made extensive use of various C library functions. If we do not call the function setlocale() at the beginning of program execution, calls to some of the C lib functions might result in crashing the application on handset.

Make changes in the following files:

File: root\src\os.h and root\src\os.c

Functions defined in os.h and os.c have operating system specific implementations. We need to provide BREW specific implementations of these functions. For that reason, we can redefine structure OsFile. We also need to add AEEFile.h of BREW API. We may need few macros defined. All these things are covered in the following snippet of code.

The macro OS_BREW312 is used while providing BREW specific implementations for the functions in os.c file.

Add the line at the beginning of os.c file.

#include<spanclass="code-string">"AEEStdlib.h"</span>

Provide BREW specific implementation to functions defined in this file. For details, see os.h and os.c files provided in (iSQLite.zip).

File: root\src\util.c and root\src\table

cFunctions like malloc(), realloc() and free() are called from util.c and table.c. Replace calls to functions malloc(), realloc(), free() by MALLOC(), REALLOC() and FREE() respectively. These functions are BREW specific implementations of malloc(), realloc() and free() provided with BREW API.

File: root\src\sqlite3.h

Add the line at the beginning of the file:

#include<spanclass="code-string">"AEEStdlib.h"</span>

We may not need all features provided. We can # define some macros to disable these features.

We # defined the following macros to avoid inclusion of features that we didn’t need.

In some files like parse.c, update.c, insert.c, vdbe.c, some functions for doing work related to feature like triggers, altering table, indexing, etc. are called. As mentioned above, we didn't want all these features for our application. We commented corresponding part of the code using conditional compilation with the help of macros defined in SQLite3.h.

For example, below is a part of code snippet which shows use of conditional compilation in parse.c file.

Step 4: Adding isqlite.c and isqlite.h File

Include the files isqlite.h and isqlite.c in the workspace. Interface for SQLite version 3.2.8 named as ISQLiteClass is defined in isqlite.h. Functions of this interface are defined in isqlite.c. See the files in (iSQLite.zip) for details.

Step 5: Compilation and Linking in RVDS

To generate BREW executable (.mod) file for the extension, Include all the source files into a workspace. Instead of adding AEEAppgen.c and AEEModGen.c files in the workspace, add the file named AppgenModGen.a provided in iSQLite.zip. This file is precompiled library of AEEAppgen.c and AEEModGen.c files.

Use the settings as shown in the following screen shots:

Target settings: Post linker was disabled because we make use of brew elf2mod utility to generate .mod file from .elf.

Access Paths: Specify all the required paths in the Access Path tab of the project to eliminate all linking errors.

Compiler settings

Linker settings: we are setting output file name as isqlite.elf

Final Step: Generate .mod File

Convert the elf into BREW executable (.mod) using elf2mod utility as shown below:

elf2mod isqlite.elf

This will create isqlite.mod file in the same folder. This file is the mod file for BREW extension. Add isqlite.mod on handset using BREW Apploader.

How to Use this Extension in an Application

If an application wants to use the functions of this interface, then the following files should be # included in its source code.

isqlite.h - This file has declarations of functions of ISQLite interface.

isqlite.bid - This file has class ID of this extension. This class ID is needed while creating an instance of ISQLiteClass using ISHELL_CreateInstance.The following files need to be included in the working folder of the application.

isqlite.h

isqlite.bid

sqlite3.h

The application has to add the class ID of extension (defined in isqlite.bid) under the section of dependencies in its MIF.

Authors

Kushal Gore

Kushal Gore is a Master of Science in Mathematics and Computing, Indian Institute of Technology, Guwahati. He is a research team member with Mumbai Innovation Labs, Tata Consultancy Services Limited. His research interests is Mobile computing.Contact: kushal.gore@tcs.com

Pankaj Doke

Pankaj Doke is a Bachelor in Computer Engineering, Mumbai University. He is a research team lead with Mumbai Innovation Labs, Tata Consultancy Services Limited. His research interests are Mobile computing, Computer Security, Pattern Recognition, Machine Learning and Large Scale Systems.Contact: pankaj.doke@tcs.com

Sanjay Kimbahune

Sanjay Kimbahune is a Bachelor of Engineering in Electronics, Amravati University. He is a research scientist with Mumbai Innovation Labs, Tata Consultancy Services Limited. His research interests are Mobile Computing and Telecom Systems.Contact: sanjay.kimbahune@tcs.com

Hi,
our requirement were simple.
We wanted to execute simple SQL select, delete and update commands on the handset.
The handset we use for this purpose is Motorola Motorazr v3C which has BREW 3.1.2 and Brew SQL Extension 1.0.x requires BREW 3.1.4 or higher. Hence we decided to go for some basic version of SQLite 3.x instead of going for latest version.