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.

Abstract

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 sqlite3 was therefore built, installed and tested. This TN describes this work.

Introduction

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 isql for 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 version of 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 before sqlite3.

Method

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.

Discussion

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 working 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

References found in this section, and in particular the HTML links were correct at time of writing (2010-09-15).

[au]. Martin Sullivan:

http://www.zois.co.uk/people/martin_sullivan

[ad]. Android Debug Bridge:

http://developer.android.com/guide/developing/tools/adb.html

[sl]. SQLite:

http://www.sqlite.org

[cs]. CodeSourcery:

http://www.codesourcery.com

[pt]. Lee EA (2006) The Problem with Threads:

http://www.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.html

[ro]. Perez S (2010) How to Hack Your Android Phone:

http://www.readwriteweb.com/archives/how_to_hack_your_android_phone.php

[bb]. BusyBox:

http://www.busybox.net

[dl]. Sqlite3 for Android download:

http://www.zois.co.uk/dl/sqlite3 (Size: 1448982 bytes, MD5 checksum: c14086bcc1b32503e0ebfe711dda73ee)

~Z~