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.