Today we are going to look at how to work with Android application database
Android OS 2.X uses SQLite relational database for storing data. SQLite website states:
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
Basically, there are two main ways to explore or to change Android SQLite database:
- We can use SQLite command line client to access files containing SQLite database tables inside Android device (a phone, a tab) or inside Android emulator
- Alternatively we can copy the files from the device or emulator to our workstation, work with them there using a handy GUI tools and copy them back to the device or emulator after we done
Both methods have advantages and disadvantages, therefor we need to get familiar with both.
The instructions below work for both Windows and Linux.
Android provides command line shell access to its SQLite databases through a standard SQLite command line shell. You can find a good manual on it here. Just keep in mind, that Android OS 2.3 contains SQLite version 3.6.22 while the documentation has been written for a slightly earlier version (3.6.11) which accounts for small discrepancies. Otherwise Android team implemented a complete command line access to SQLite databases (unlike they did with ash shell; Android provides a reduced set of ash shell commands).
We can start SQLite command line shell while running adb. adb stands for Android Debug Bridge, a tool similar to SSH or telnet which allows accessing either AVD (Android Virtual Device, essentially Android emulator) or a real Android device connected to our computer.
I highly recommend experimenting with AVDs instead of real devices until you feel confident with what you are doing.
So let’s start AVD. Type:
Here TestAVD is a name of AVD we are going to work with (of course, your AVD may have a different name).
If you get a message saying that emulator not found, you may have forgotten to add tools and platform-tools directories of Android SDK to your system PATH. If all goes fine, we’ll see Android emulator GUI in a few seconds. Give it a time to start Android OS; it may take up to a minute or so.
Now we have to open another command line console window. Type there:
This command should give us a list of all AVDs or Android devices running. You’ll see something like this:
vlad@vlad-laptop:~$ adb devices * daemon not running. starting it now on port 5037 * * daemon started successfully * List of devices attached emulator-5554 offline
Please note, it says that emulator-5554 is offline. The most likely it is since the AVD went to a sleeping mode after it started:
Click on Menu button on AVD to “wake” it up. Type:
This will launch Android ash shell and you will see Android shell prompt (#). Note, this will not work if you have several AVD or devices running; in that case you need to type
adb -s emulator-5554 shell
explicitly describing what AVD or device you want to connect to. Anyway, after we got the shell prompt (#), we can start SQLite shell. Type:
Now you should see SQLite shell prompt:
SQLite command line shell accepts SQL statements and so-called “dot” commands, i.e. commands starting from a dot. Those commands unlike SQL are SQLite-specific.
To see the list of “dot” commands type .help (note the period) after you get SQLite shell prompt (sqlite>):
# sqlite3 sqlite3 SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .help .help .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .genfkey ?OPTIONS? Options are: --no-drop: Do not drop old fkey triggers. --ignore-errors: Ignore tables with fkey errors --exec: Execute generated SQL immediately See file tool/genfkey.README in the source distribution for further information. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements .nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .schema ?TABLE? Show the CREATE statements If TABLE specified, only show tables matching LIKE pattern TABLE. .separator STRING Change separator used by output mode and .import .show Show the current values for various settings .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. .timeout MS Try opening locked tables for MS milliseconds .width NUM1 NUM2 ... Set column widths for "column" mode .timer ON|OFF Turn the CPU timer measurement on or off sqlite>
To exit from SQLite shell type .exit (note the dot) or Ctrl-D. To interrupt a long running SQL statement type Ctrl-C.
Until now SQLite shell hasn’t been connected to any database. To make the shell connected to a particular DB, launch it with DB file as a parameter (SQLite contains the whole DB in one single file which is very handy). But where to find those files?
Each time a new package is installed in Android OS, the OS creates a sort of “home directory” for it. All those “home directories” are located in /data/data directory of the device or AVD. The names of the directories correspond to names of the packages. All package data including its DB or DBs is stored within the directories.
Type following while you are in Android command line shell (not SQLite command line shell! You should see # as a prompt):
ls -l /data/data
You will see something like this:
drwxr-x--x app_0 app_0 2011-01-04 15:36 com.android.sdksetup drwxr-x--x app_2 app_2 2011-01-04 15:36 com.android.calculator2 drwxr-x--x app_4 app_4 2011-01-04 15:36 com.android.packageinstaller drwxr-x--x app_6 app_6 2011-01-04 15:36 com.android.development ........... #
Those are package “home directories”. You can either dig down into those directories to find database files (they have .db extension), or you can use this command which will list all database files:
cd /data/data ls -R
This will show you all directories and files in /data/data directory (unfortunately Android command line shell does not support find command). You’ll see something like this:
............. ./com.android.providers.userdictionary: lib databases ./com.android.providers.userdictionary/lib: ./com.android.providers.userdictionary/databases: user_dict.db ............
Let’s try to see what is inside EmailProvider.db database:
# sqlite3 /data/data/com.android.email/databases/EmailProvider.db SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .tables Account HostAuth Message Message_Updates Attachment Mailbox Message_Deletes android_metadata sqlite> select * from Account; sqlite>
This shows that EmailProvider.db contains a number of tables: Account, HostAuth etc. Output of SQL statement select * from Account shows that table Account is empty.
We are not going to discuss here all capabilities of SQLite command line shell. If you are interested, take a look here.
While it is cool to be able to access SQLite database on a real Android device with so powerful tool, I think the use of SQLite command shell in this case is limited to debugging and research purposes. Indeed, I would not design a database inside a live device. Designing a database makes sense as a part of an overall application development which in Android case is done on a computer running Eclipse IDE and Android SDK. We’ll discuss tools more suitable for SQLite development in one of the next posts. There are such tools. They provide a handy GUI.