Vlad Nevzorov's blog

GUI tool to work with SQLite

There is a number of GUI tools for SQLite available for Linux and Windows. We’ll be using Sqliteman. It is free, available for Linux, Windows, Mac OS X and various versions of Unix. This makes instructions we are going to provide applicable to all those OS.

To install it on Ubuntu 10.10 Linux use the command:

sudo apt-get install sqliteman

Installation instructions and Sqliteman installer for Windows could be found here as well as instructions and distributives for other OSs.

Let’s try to explore an existing database from Contacts application using Sqliteman.

Sqliteman can not access files inside Android AVD or Android device. To make use of Sqliteman we have to copy the database file we want to work with from  Android filesystem to your PC local filesystem. This can be done with adb tool. If you want to work with files inside Android filesystem, use SQLite command line shell described here.

Contacts application uses a database which is stored within contacts2.db file. It is located in /data/data/com.android.providers.contacts/databases/ directory of Android device or AVD. Type this command (I assume you have running AVD or a device attached to your computer):

adb pull /data/data/com.android.providers.contacts/databases/contacts2.db ~/android

Here ~/android is a directory where we want to copy the database file.

If everything is OK, you’ll see something like this:

590 KB/s (109568 bytes in 0.181s)
vlad@vlad-laptop:~/mydroid$

This confirms that the file was copied successfully.

Now let’s start Sqliteman. If you use Ubuntu Linux as I do, go to Applications -> Programming -> Sqliteman. This will launch Sqliteman:

Now we have to open the DB file. Go to File -> Open and select the file you downloaded from Android AVD or device. You will see something like this:

Let’s explore the database. Click on Tables in the left pane. This will show you the list of tables in the database. Click on data table; this will show you a description of the table:

You can drill down to find more details… But for the purpose of the post let’s try to run an SQL statement. Type

select * from data

in the top right pane, then click on a green triangle above it. This will display the content of the table data in the pane below the one where you typed the SQL statement:

After we finished your work with the database using Sqliteman, we can copy the database file to the device or AVD. For this we should use adb push command. In our case this will be:

adb push ~/android/contacts2.db /data/data/com.android.providers.contacts/databases/contacts2.db

That’s all for now.

Read Full Post »

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.

I assume you have already installed Android development environment. If not, instructions for Windows are here, for Linux they are here.

The instructions below work for both Windows and Linux.

Command-line interface

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:

emulator @TestAVD

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:

 adb devices

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:

adb shell

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:

sqlite3

Now you should see SQLite shell prompt:

sqlite>

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.

Read Full Post »