Sqlite3 on an Android HTC Hero
ZOIS Technical Note TN-2010-09-16.
Author and Audience
A version of
sqlite3 has been ported to
the Android based HTC Hero, for this platform is deficient in this
program. The reader is assumed to be familiar with programming
techniques, particularly in C. A download of the resultant binary is
available[dl]. Written by Martin Sullivan[au],
ZOIS Limited, Cockermouth.
The HTC Hero is a 'smart' mobile phone using the Android operating
system (version 1.6). Normally Android comes with the
sqlite3 tool to allow inspection and modification of the
SQLite[sl databases used extensively with is
system. Sadly, in the authors particular example at least, this very
useful program has been omitted. A de-novo
therefore built, installed and tested. This TN describes this work.
SQLite is a lightweight SQL based relational database
engine designed for embedded applications. It has found favour with a
number of developers and, to a certain extent, has supplanted more
traditional direct-access embedded databases such as Berkeley DB. It
comes with a fairly complete implementation of the SQL language
although it succeeds in being light-weight by jettisoning a lot of the
'normal' concurrency control that is observed in heavier
offerings. SQLite databases themselves are wholly contained within one
file, and Android the 'smart' phone operating system from Google uses
it extensively. Android itself is an operating system based on the
more familiar and now widely understood Linux. Android goes beyond
traditional distributions of Linux, though, and is specifically
targeted at modern mobile phones with a limited display and input
options. The CPU of the small computer found in such devices generally
has an ARM architecture and itself has compromises to allow a
cost-effective platform for the well-connected extended multi-media
experience that mobile phones offer these days.
Google offers a development system, ADB, which allows connection
through USB cables to an Android 'phone. ADB has file transfer and
shell interfaces to the 'phone, which can then be used as a
development and test platform. As part of such work, SQLite databases
need to be inspected and occasionally modified. Traditionally this has
been accomplished using an SQL Console, such as
Ingres. In SQLite, a program
sqlite3 takes this role.
It would seem that, as distributed on the HTC Hero,
sqlite3 is missing from Android. Some versions have been
found on the web, but it do not work becuase of versioning issues in
loading dynamic libraries from the base operating system.
It was therefore thought necessary to to build a completely new
sqlite3, for the Android platform, that was
'self contained' and thus portable.
Materials and Platform
To permit manipulation of the 'phone at a command level the Android Debug Bridge[ad] is required.
It is recommended that the 'phone be 'rooted' to get system-level privileges. This has been extensively documented elsewhere[ro].
Work was done on an Ubuntu Linux release, other UNIX-like operating systems may require some additional porting work. Non-UNIX OS like Microsoft Windows remain unexplored.
The binary is built using an ARM based GNU G++ Compiler tool-chain from CodeSourcery[cs]
Busybox[bb] is practically mandated, as the usual
UNIX-like tools found on Android are considered inferior. It is
recommended that these tools are installed in conjunction with, if not
Download and install the Linux/ARM G++ Tool-chain from CodeSourcery; the narrative assumes that the binaries are in /opt/arm/bin. This tool kit contains cross-platform C compiler and linkage-editor for ARM.
Download and install ADB. The eponymous adb program should connect to the 'phone using USB, wires permitting. The connection can be checked using the ADB shell command. In some instances it is necessary to start adb with system privileges (sudo(8), or as root) this is because the USB devices may have restricted permissions and the adb server needs to connect to them. Adb should not to be confused with the ancient UNIX debugger (old hands may remember it, '$c', '$q').
Download the source to SQLite and unpack it. Use the configure command on a Linux box to build the includes and so forth, defaults may be used throughout. Then use the following Makefile. This 'make' omits threading for threading is always problematic[pt], and the tool-chain doesn't support it. The tool-chain doesn't support dynamic libraries either so the sqlite is compiled static, with dynamic-load extensions switched off.
PATH=.:/opt/arm/bin:/usr/bin:/bin CC=arm-none-linux-gnueabi-gcc LD=arm-none-linux-gnueabi-ld LDFLAGS=-static CFLAGS=-static -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION all: sqlite3 sqlite3: sqlite3.o shell.o clean: rm *.o rm sqlite3
The resultant binary can be pushed into the 'phone using
ADB. Should the 'phone be rooted then you may like to install it in
/xbin, which is where the author has put it, together with BusyBox. To
do this on an out-of-the-box Hero the directory '/' needs to remount
as read-write and root privileges are obviously mandated.
This version of
sqlite3 has been cursively tested. It
is found that selects, inserts, updates, drops and creates can be
performed with it. As distributed the binary requires the $HOME
environment variable set. A download of the binary[dl] is available for your convenience.
The binary is statically linked. While this confers a high degree
of portability it means that the binary is relatively large. It also
therefore does not use the native SQLite libraries on your mobile
phone. There are therefore version implications on this, but the
databases' binary format seems to be stable across versions. Having a
sqlite3 on the 'phone allows various other
experiments to continue, including some crude script driven automation
from Linux via ADB.
It is anticipated that this
sqlite3 will also work on
other Android 'phones too, and may therefore be useful on other
platforms where the expected binary is missing. The same approach may
be used on other programs as demanded by future projects.
As with other Technical Notes, feedback is actively solicited. The
author may be contacted via the e-mail address found on his public
biography page[au]. Should something require
changing or enhancing then the fact will be acknowledged with
attribution in an Update section.
References found in this section, and in particular the HTML links were correct at time of writing (2010-09-15).
- [au]. Martin Sullivan:
- [ad]. Android Debug Bridge:
- [sl]. SQLite:
- [cs]. CodeSourcery:
- [pt]. Lee EA (2006) The Problem with Threads:
- [ro]. Perez S (2010) How to Hack Your Android Phone:
- [bb]. BusyBox:
- [dl]. Sqlite3 for Android download:
- http://www.zois.co.uk/dl/sqlite3 (Size: 1448982 bytes, MD5 checksum: c14086bcc1b32503e0ebfe711dda73ee)