Copyright © 2010 Tata Consultancy Services Ltd. All Rights Reserved
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, delete and 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.
Requirements
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.
gcc -o lemon lemon.c (This command compiles lemon.c file present in a root\tool folder and produces an executable file lemon) ./lemon parse.y (This command uses the executable file lemon to produce parse.c and parse.h files from parse.y)gcc -o mkkeywordhash mkkeywordhash.c (This command compiles mkkeywordhash.c file present in a root\tool folder)./mkkeywordhash>keywordhash.h (This command produces keywordhash.h file)cat parse.h vdbe.c | awk -f mkcodeh.awk>opcodes.h (This command uses files mkcodeh.awk, parse.h, vdbe.c to generate opcodes.h)cat opcodes.h | awk -f mkcodec.awk>opcodes.c (This command uses files mkcodec.awk, opcodes.h, to generate a file opcodes.c)
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, #include a 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.
Paste the following definitions at the beginning of the os.h file.
#define OS_BREW312 1
#include "AEEFile.h"
struct OsFile {
IFileMgr *m_pIFileMgr; IFile *m_pIFile; };
typedef struct OsFile OsFile;
# define SQLITE_TEMPNAME_SIZE 100
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 "AEEStdlib.h"
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 "AEEStdlib.h"
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.
#define SQLITE_OMIT_AUTHORIZATION 1
#define SQLITE_OMIT_UTF16 1
#define SQLITE_OMIT_ALTERTABLE 1
#define SQLITE_OMIT_TRIGGER 1
#define SQLITE_OMIT_VIEW 1
#define SQLITE_OMIT_FOREIGN_KEY 1
#define SQLITE_OMIT_AUTOVACUUM 1
#define SQLITE_OMIT_AUTOINCREMENT 1
#define OMIT_DATE_RELATED_FUNCTIONS 1
#define SQLITE_OMIT_TRACE 1
#define SQLITE_OMIT_ANALYZE 1
#define SQLITE_OMIT_PRAGMA 1
#define SQLITE_OMIT_TCL_VARIABLE 1
#define SQLITE_OMIT_EXPLAIN 1
#define SQLITE_OMIT_PAGER_PRAGMAS 1
#define SQLITE_OMIT_INTEGRITY_CHECK 1
#define SQLITE_OMIT_GLOBALRECOVER 1
#define SQLITE_OMIT_TEMPDB 1
#define SQLITE_OMIT_REINDEX 1
#define SQLITE_OMIT_CAST 1
#define SQLITE_OMIT_SUBQUERY 1
#define SQLITE_OMIT_BLOB_LITERAL 1
#define SQLITE_OMIT_PROGRESS_CALLBACK 1
#define SQLITE_OMIT_MEMORYDB 1
#define SQLITE_OMIT_VACUUM 1
#define SQLITE_OMIT_COMPOUND_SELECT 1
- File: root\src\parse.c
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.
#ifndef SQLITE_OMIT_TRIGGER
case 236:
case 241:
#line 969 "parse.y"
{sqlite3DeleteTriggerStep((yypminor->yy360));}
#line 1387 "parse.c"
break;
#endif
#ifndef SQLITE_OMIT_VIEW
case 99:
#line 354 "parse.y"
{
sqlite3CreateView(pParse, &yymsp[-6].minor.yy0, &yymsp[-3].minor.yy198, \
&yymsp[-2].minor.yy198, yymsp[0].minor.yy375, yymsp[-5].minor.yy280);
}
#line 2280 "parse.c"
break;
#endif
#ifndef SQLITE_OMIT_AUTOVACUUM
case 253:
case 254:
#line 906 "parse.y"
{sqlite3Vacuum(pParse,0);}
#line 2977 "parse.c"
break;
#endif
For details, see the corresponding files in (iSQLite.zip).
- File: root\src\random.c
Defined function sqliteRandomByte() in this file. This definition can be found in a source code of SQLite version 2.2.0. This function is getting called from os.c file.
int sqliteRandomByte(){
int r;
sqlite3OsEnterMutex();
r = randomByte();
sqlite3OsLeaveMutex();
return r;
}
- File: root\src\prepare.c
Replaced an if statement
if( meta[1]>3 ){
with:
if( meta[1]>4 ){
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.
The following sample code illustrates the use of ISQLite interface.
ISQLiteClass *pISQLiteClass = NULL;
AEEApplet *pApplet = (AEEApplet *)GETAPPINSTANCE();
sqlite3 *psqlite3 = NULL;
if(SUCCESS!= ISHELL_CreateInstance(pApplet->m_pIShell,
AEECLSID_ISQLITE,(void **)&pISQLiteClass))
{
DBGPRINTF("failed to create ISQLiteClass instance");
}
else
{
char *pszSQLQueryInsert = "create table SAMPLE
(C1 varchar(10), C2 varchar(10), C3 varchar(10),\
C4 varchar(10), C5 varchar(10), C6 varchar(10));
INSERT INTO SAMPLE \
(C1, C2, C3, C4) values ('123','Govind', 'Afade', '25');
INSERT INTO \
SAMPLE (C1, C2, C3, C4) values
('123','Govind', 'Adle', '26');";
char *pszSQLQuerySelect = "SELECT * from SAMPLE";
if(SQLITE_OK!= ISQLiteClass_sqlite3_open
(a_pApplet->m_pISQLiteClass, "database.db", &psqlite3))
{
DBGPRINTF("db file could not open");
}
else
{
char **ppszResult = NULL;
int nRow = -1;
int nCol = -1;
char *pszErrorString = NULL;
int nReturnValue = -1;
nReturnValue = ISQLiteClass_sqlite3_get_table
(a_pApplet->m_pISQLiteClass,psqlite3,\
pszSQLQueryInsert,&ppszResult,&nRow,&nCol,&pszErrorString);
ISQLiteClass_sqlite3_free_table(a_pApplet->m_pISQLiteClass, ppszResult);
nReturnValue = ISQLiteClass_sqlite3_get_table
(a_pApplet->m_pISQLiteClass,\
psqlite3,pszSQLQuerySelect,&ppszResult,
&nRow,&nCol,&pszErrorString);
ISQLiteClass_sqlite3_free_table(a_pApplet->m_pISQLiteClass, ppszResult);
}
ISQLiteClass_Release(pISQLiteClass);
}
References
[1] SQLite Home page ( http://www.sqlite.org/)
[2] "Databases on Mobile Porting SQLite to BREW", Kushal Gore, Priyanka Kabra, Sanjay Kimbahune, Pankaj Doke (A paper presented in National Asia Pacific Regional Conference in ICTM -2010 “INNOVATION IN ICT AND TECHNOLOGY MANAGEMENT” organized by SVKM's NMIMS(deemed to be university) located at JVPD Scheme Vile Parle, Mumbai on 5-6 March 2010. Website: http://www.ncict-mumbai.org/index.html)
[3] RVDS 3.0 documentation ( http://infocenter.arm.com/help/index.jsp)
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
History
- 13th September, 2010: Initial version
