Using an SQLite Database on Android Platform

A Short FAQ

Michał Kołodziejski, Senior Software Engineer at Vertabelo

by
Michał Kołodziejski
Senior Software Engineer at Vertabelo

Posted: March 4, 2014

When developing an application with a SQLite database as persistent storage, it’s worth it (or necessary, in fact) to know some low-level details like: where the data is stored physically and how we can determine if its structure is really the same as what we expect it to be. Being familiar with these things makes it easier and faster to develop, as well as find and fix bugs. I’ll try to explain some of the most frequently asked questions regarding SQLite databases.

What Is a Physical Representation of a SQLite Database?

A SQLite database is stored on disk as a single file. The database name you provide in the SQLiteOpenHelper’s constructor is a filename.

For example, in the ToDo app developed in the previous article the database name was “todo_list_db.” The corresponding file can be found directly in the device’s filesystem:

$ adb shell
root@android:/ # cd /data/data/com.vertabelo.blog.mytodolist/databases
root@android:/data/data/com.vertabelo.blog.mytodolist/databases # ls
todo_list_db
todo_list_db-journal

As we can see, the file is stored under the path:

/data/data/<application_package>/databases/<db_name>

There is also another file: todo_list_db-journal

It is a temporary journal file. It is created by SQLite and it’s associated with transaction control. If you want to know more about it, you can find the information here: http://www.sqlite.org/tempfiles.html

How Can I Inspect the Database Created in My Application?

There are two ways to do this: online and offline.

Inspecting Online

We’re going to connect to the device’s shell and execute the sqlite3 command line interface:

$ adb shell 
root@android:/ # cd /data/data/com.vertabelo.blog.mytodolist/databases 
root@android:/data/data/com.vertabelo.blog.mytodolist/databases # sqlite3 
todo_list_db
SQLite version 3.7.11 2012-03-20 11:35:50 
Enter ".help" for instructions 
Enter SQL statements terminated with a ";" 
sqlite> 

Now we can call all kinds of statements: queries, inserts, updates, etc.

Inspecting Offline

Since a SQLite database is a single file, we can “pull” it from a device to our local disk and analyze it locally:

$ adb pull
/data/data/com.vertabelo.blog.mytodolist/databases/todo_list_db
262 KB/s (24576 bytes in 0.091s) 
$ sqlite3 todo_list_db 
SQLite version 3.7.9 2011-11-01 00:52:41 
Enter ".help" for instructions 
Enter SQL statements terminated with a ";" 
sqlite> 

This method has one very important advantage – we can open the DB with whatever tool we want to. Now we’re only limited with our workstation’s software, not the device’s. If you don’t feel comfortable with a terminal-based interface, you might want to explore the DB with a graphical tool, such as SQLiteStudio:

SQLiteStudio screenshot

Does SQLite Support Transactions?

Yes, it does.

By default, all operations are executed as separate transactions. If you perform a massive number of operations (inserts, updates) it would be very ineffective. These kinds of operations should be wrapped into a single transaction block:

db.beginTransaction();
try {
    // do operations
    // ...
    
    db.setTransactionSuccessful();
    
} finally {
    db.endTransaction();
}

Does SQLite Support Foreign Keys Constraints?

Yes, but not by default. We can read about it in the documentation:

“Foreign key constraints are disabled by default (for backwards compatibility), so [they] must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints [are] enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)”

That’s why we should enable it every time the database is opened.

If you write for API level 16 or higher, you should do it in onConfigure method of the SQLiteOpenHelper class:

@Override
public void onConfigure(SQLiteDatabase db) {
    db.setForeignKeyConstraintsEnabled(true);
}

In the previous API levels it should be done this way:

@Override
public void onOpen(SQLiteDatabase db) { 
    db.execSQL("PRAGMA foreign_keys = ON;"); 
}

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! The only interactive course for SQL window functions on the Internet. View course Discover our other courses: