SQLite Database

There are many ways to handle data on mobile devices. Unstructured data such as a jpeg file or html page is best stored in files. Structured or relational data such as your list of contacts is best stored and managed using relational databases. Android supports the very simple and very cool SQLite. In this lecture, we will discuss how to use Android SQLite database through a simple demo project example. We will relate this simple example to the SQLite database you need to design and implement as part of MyRuns3.

If you've not used SQLite (an industry standard and great for mobiles) before then this should be cool. I have tried to describe the software architecture around three layers: the app layer, data storage layer, and the SQLite layer. Abstraction is heavily leverage in use of SQLite on mobiles such as Android. I love abstraction -- it's a powerful idea and allows you to hide the unnecessary details from the app designer.

We will also tie databases to Content Providers -- another important tool in the Android toolbox.

What this lecture will teach you

Demo projects

Resources

Important: Make sure you read the course book section on (it has an example of using a background thread to query the database and another thread to write information to the database):

Databases and Content Providers

Every Android app that wants to store structured data (e.g., contact information, march madness bracket) needs to create its own private database that the app creates and manages by itself. The database access is restricted to the app that created it. If any application wanted to share SQLite its data (e.g., contact list) with other applications running in your phone it would use Content Providers -- content providers provide a uniform way to store, share and consume structured between applications but also within a single application (yes you set up an SQLite database and wrap it in a content provider to manage data). Importantly, content providers provide a generic interface to a data source by decoupling the data storage layer (the app's database) from the application layer. So the application talks content provider language and is unaware of how data is stored or the internals of the database -- it abstracts the data source and provides management APIs to the data. The database plumbing is of little interest to applications, they just want to get the data and manage it in the app domain. More on content providers in the next lecture; let's get back to databases.

SQLite is a transactional database engine which is lightweight: in overhead, fast performance, small footprint and implemented as a c library. When an application creates a private database it is stored in the following folder on your phone -- but you have to root your phone to see it:

Database demo app

The application is very simple but allows us to understand how the database is set up and accessed. When the application first starts up there is not existing database so it displays no items. As the user adds and deletes items (i.e., comment) the UI reflects that. Finally, the user can delete all items in the data base. These various steps are shown in the screen dumps below of the app.

The first image represents the app when it is first installed and started. No database items have been stored. The next iteration is when the user has tapped the add new button a number of times and as a result a number of items have been (randomly selected) inserted in the database and rendered to a listview from the array below. Next, the user tapped on delete first once and the top item is deleted from the database and removed from the listview.

            String[] comments = new String[] { "England", "Dartmouth", "CS65",
                    "is", "is", "the", "best", "coolest", "place", "in", "the",
                    "universe!" };

Next, the app is destroyed and then restarted. When the app is started again it shows the state of the database when it was last closed. In this case there are a number of items still in the database.

Systems Design

While the app is very simple the plumbing around constructing a database is complex at first. The layered architecture is shown i the figure below. The operation on the SQLite database is wrapped in a few layers of abstraction. I view these layers as follows:

You won't see these layers necessarily like this in the literature but I think it helps explain the architecture and operations. There is a separation of concerns: the app deals with objects that matter to it, i.e., comments; and the lower layers deal with storage of data, and eventually the detail operations of an SQL database. You want to hide these details from the user. For example, I'm sure you are thinking -- why make this so hard? Why not have the app reach down into the SQLite code and just leave it like that. But we use abstraction -- of layering -- to hid the grubby details from the app layer.

Content Values and Cursors

The Android framework provides two classes that support database interaction -- helps with the plumbing; these are content values and cursors.

Typically an app with want to store relational data -- a set of data such a contact information (e.g., name, phone number, email address). This relational information will be stored as a row with three columns for each entry. One can view the table as a bunch of rows and columns:

The ID is not part of the contact necessarily but used to index the data -- not important at the app layer, but at the data storage layer. Wow that is a lot of Campbells.

Content values are used to insert a new row into the data base: 5 Mary Campbell 603 717 2479 campbell@cs.dartmouth.edu. So the resulting database would look like this:

Content Values

Importantly each content value object represents a single table row and maps the column names to values. For example, if we jump a head into the data storage layer we'd see the following code that uses ContentValues and Cursors. In the code below the application wants to insert a new row into the database -- in the case of our application this is an application specific comment.

createComment() is called with a single comment (e.g., Dartmouth). A ContentValues object is created to store the complete row which includes the comment only -- but in our contact example above would be NAME, PHONE, EMAIL -- they'd all be loaded into values below.

Tip: Note, in MyRuns3 where you build a database. It will have a table of exercises. Each row will include a number of different items (under some column) -- for example: activityType, dateTime, duration, distance, avgPace, avgSpeed, calorie, heart rate, comment. Consider this row an ExerciseEntry -- which compares in this simple example to the Comment object. When you insert in the database you will be adding an ExerciseEntry object. When you query you might get one or more ExerciseEntry objects. Point is: your database code will be much more complex than this simple example. But it will use content values and cursors in the same manner as discussed here.

    public Comment createComment(String comment) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
        long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
                values);
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
                allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
        cursor.moveToFirst();
        Comment newComment = cursorToComment(cursor);

        cursor.close();
        return newComment;
    }

Once the comment is loaded into the content values the insert operation is called on the SQLite layer.

Cursors

As you can see from the snippet of code above the code inserts a comment -- the parameters passed include values, which is the content values object) -- and the immediately reads back what is written (paranoid code). The query on the database returns a cursor which includes the comment object inserted. Note, the insertId returned by the database.insert() is passed as part of the database.query(). A general comment on cursors: they can contain a single row or the complete table (all rows). In addition, cursors are not copies of the database but serve as pointers to the results set provided by the underlying layer.

As you see from the snippet there is control over navigation of the cursor -- see cursor.moveToFirst() in the code. This method provides a managed way to move to the first element in the cursor -- recall there could be many rows returned and cursor.moveToFirst() moves the position of the cursor to the first row. There are a number of navigation methods but here are some commonly used ones:

Other important methods very useful for interacting with cursor objects include:

In our code snippet we simply move the cursor to the first row (cursor.moveToFirst()) and the call a helper function that extracts the cursor information and instantiates an comment object that includes the now application specific information.

Performance issues: CursorLoader and asynchronously to cursor data

Because it takes time to interact with a database in storage and you do not want to block the UI (while the code gets a potentially large amount of data back from the database) there are some clever optimizations that relate to creating threads to go off and wait for the data to be loaded into the cursor -- and only when the data is available in the cursor does the main UI code step back in. Android provides the means to asynchronously read from the database. The CursorLoader associated with the Load Manager provides such an asynchronous mechanism.

In short, the CursorLoader uses a ContentResolver and returns a Cursor. The CursorLoader implements the loader protocol in a standard way for querying cursors, building on AsyncTaskLoader to perform the cursor query on a background thread so that it does not block the application's UI. ContentResolver are a mechanism accessing shared content associated with Content Providers. We will discuss this in the next lecture -- or so.

The App Layer: TestDatabaseActivity

In what follows we describe the top level app that drives the UI (and therefore user input) and interacts with a database created in the onCreate() method. We discuss the user IO and lifecycle methods associated with the launched activity that drives and managed the user interaction and the data store (at an abstract level: note, there is not exposure to the details of the database structure, it could be any sort of database -- file, SQL, something else). So the key design issue here is that the details of the database implementation (storage layer) is abstracted from the app layer. This is cool because you could, if you wanted, completely replace the database implementation defined by the storage layer.

Creating the database

TestDatabaseActivity is the application code and the UI (shown above) simply provides three buttons and a listview which is mapped to a simple_list_item_1. When the activity is launched it's onCreate() method creates the database and then opens it. All items (in this case comments) are then read from the database into a list. The list is then bound to the listview using an adapter. The comments are then rendered to the view when the list adapter is set.

public class TestDatabaseActivity extends ListActivity {
    private CommentsDataSource datasource;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        datasource = new CommentsDataSource(this);
        datasource.open();

        List<Comment> values = datasource.getAllComments();

        // Use the SimpleCursorAdapter to show the
        // elements in a ListView
        ArrayAdapter<Comment> adapter = new ArrayAdapter<Comment>(this,
                android.R.layout.simple_list_item_1, values);
        setListAdapter(adapter);
    }

Setting up the UI and onClick() callback

All the buttons in the UI use the same onClick() callback. There is a switch statement for each of the three buttons: add, delete and delete all.

In the case of "add new" the app randomly selects a comment and adds the new comment to the database using datasource.createComment(comments[nextInt]). The datasource.createComment method returns the comment that was successfully written to the database. The code then adds the new comment to the adapter. The adapter is notify that the data set if changed and updates the UI using the adapter.notifyDataSetChanged() method.

public void onClick(View view) {
        @SuppressWarnings("unchecked")
        ArrayAdapter<Comment> adapter = (ArrayAdapter<Comment>) getListAdapter();
        Comment comment = null;
        switch (view.getId()) {
        case R.id.add:
            String[] comments = new String[] { "England", "Dartmouth", "CS65",
                    "is", "is", "the", "best", "coolest", "place", "in", "the",
                    "universe!" };
            int nextInt = new Random().nextInt(9);
            // Save the new comment to the database
            comment = datasource.createComment(comments[nextInt]);
            adapter.add(comment);
            break;
        case R.id.delete:
            if (getListAdapter().getCount() > 0) {
                comment = (Comment) getListAdapter().getItem(0);
                datasource.deleteComment(comment);
                adapter.remove(comment);
            }
            break;
        case R.id.deleteall:
            if (getListAdapter().getCount() > 0) {
                datasource.deleteAllComments();
                adapter.clear();
            }
            break;
        }
        adapter.notifyDataSetChanged();
    }

In the case of "delete first" the app will delete the first item in the database. The delete code simply checks that the database is not empty and then gets the first comment from the list adapter and calls datasource.deleteComment(comment) to remove the comment from the database. Finally, the comment is removed from the adapter. Again, adapter.notifyDataSetChanged() updates the UI.

In the case of "delete all" the code simply checks the database is not empty and then issues a datasource.deleteAllComments(). The adapter is then cleared and notified on the change.

Activity lifecycle processing

The final part if the TestDatabaseActivity app code includes code for the onResume() and onPause() lifecycle events. In the case of onPause() the database is closed. In the case of onResume() the database is opened. Recall that onPause/onResume are used to release resources used by the application and then reinitialize when the application is brought back into focus.

    @Override
    protected void onResume() {
        datasource.open();
        super.onResume();
    }

    @Override
    protected void onPause() {
        datasource.close();
        super.onPause();    
      }

App specific data storage object: comment

The application simply allows the user to add canned comments to the database and display them to the UI. Comments can be added one at a time, deleted (from the top) one at a time or all comments can be deleted. So comment is the main object that the user deals with. The Comment class includes getters/setters for id and comment. Every time a comment needs to be added a Comment is instantiated. The Comment class is the app model and contains the data that is inserted, queried and deleted in the database. Comments are also shown in the UI.

public class Comment {
    private long id;
    private String comment;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    // Will be used by the ArrayAdapter in the ListView
    @Override
    public String toString() {
        return comment;
    }
}

The data storage layer: CommentsDataSource

CommentsDataSource maintains the database connection and supports adding, fetching and deleting comments. CommentsDataSource creates the MySQLiteHelper class which details with the actual SQLite database. Very little of the internal details of the SQLite layer are exposed to the data storage layer, as you can see in the code below. The database constants such as the column names are exposed; in our case just one column name: COLUMN_COMMENT and the column ID: COLUMN_ID. These database constants are made public by the SQLite layer discussed in the next section. These public constants are needed for inserting and querying comment objects in the database.

Constructor

The CommentsDataSource constructor creates the database. It first creates a db helper MySQLiteOpenHelper to deal with operations on the database. After the db helper is created the constructor opens the database, as shown the code below.

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

The database is a SQLiteDatabase object, exposing methods to manage a SQLite database. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

The code calls getWritableDatabase() to open and obtain a writable instance of the underlying database using the db helper implemented in the SQLite layer. If the database does not exist the helper executes its onCreate() handler -- see MySQLiteHelper code. Whether the database has already been created or not the getWritableDatabase() returns a reference to the database.

When a database has been opened successfully for the first time it will be cached by the MySQLiteHelper within MySQLiteHelper. If there is an exception in trying to open the database due to an error with SQL parsing or execution then an SQLException is thrown.

public class CommentsDataSource {

    // Database fields
    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;
    private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
            MySQLiteHelper.COLUMN_COMMENT };

    private static final String TAG = "DBDEMO";

    public CommentsDataSource(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        database = dbHelper.getWritableDatabase();
    }

    public void close() {
        dbHelper.close();
    }

App specific operations on storage: insert and deleting comments

The data storage layer also provides the key operations on the database in terms of the application specific data object, in our case: comments. These operations are:

    public Comment createComment(String comment) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
        long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
                values);
        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
                allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
        cursor.moveToFirst();
        Comment newComment = cursorToComment(cursor);

        // Log the comment stored
        Log.d(TAG, "comment = " + cursorToComment(cursor).toString()
                + " insert ID = " + insertId);

        cursor.close();
        return newComment;
    }

    public void deleteComment(Comment comment) {
        long id = comment.getId();
        Log.d(TAG, "delete comment = " + id);
        System.out.println("Comment deleted with id: " + id);
        database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
                + " = " + id, null);
    }
    
    public void deleteAllComments() {
        System.out.println("Comment deleted all");
        Log.d(TAG, "delete all = ");
        database.delete(MySQLiteHelper.TABLE_COMMENTS, null, null);
    }

Each of these application specific operations translate to specific operations on the database including relating to a single comment:

and an operation on the complete database, in this case delete all comments and get all comments (as shown in the snippet code below):

The code snippet below reads all comments from the database. The cursorToComment() method simply creates a comment from a cursor object and copies the string over. The getAllComments() is called by the launched activity -- TestDatabaseActivity -- to render the comments in the database to the UI when the activity is restarted after being destroyed or resumed.

    public List<Comment> getAllComments() {
        List<Comment> comments = new ArrayList<Comment>();

        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
                allColumns, null, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Comment comment = cursorToComment(cursor);
            Log.d(TAG, "get comment = " + cursorToComment(cursor).toString());
            comments.add(comment);
            cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        return comments;
    }

    private Comment cursorToComment(Cursor cursor) {
        Comment comment = new Comment();
        comment.setId(cursor.getLong(0));
        comment.setComment(cursor.getString(1));
        return comment;
    }
}

Database operations

The definition of the operations discussed above can be found in the SQLiteDatabase. For example consider:

database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, null, null, null, null, null);

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)

Query the given table, returning a Cursor over the result set. The definition of the parameters are as follows:

The limit argument is not shown in the table but is shown in the code below and described here. In the example below the "where" statement is: "run_Id=10". Because we specify placeholder values in the where clause using ``?'' we have to pass the selectionArgs parameter as well -- that is, new String[]{ String.valueOf(id) }


    private static final String COLUMN_LOCATION_RUN_ID = "run_id";
    query(10); // only hard coded for illustration
    .....

    public RunCursor queryRun(long id) {
        Cursor wrapped = getReadableDatabase().query(TABLE_RUN, 
                null, // all columns 
                COLUMN_RUN_ID + " = ?", // look for a run ID
                new String[]{ String.valueOf(id) }, // with this value
                null, // group by
                null, // having
                null, // Order by
                "1"); // limit 1 row
        return new RunCursor(wrapped);
    }

The SQLite layer: finally the SQLiteOpenHelper plumbing exposed!

The MySQLiteHelper (which extends SQLiteOpenHelper) implements the helper class to manage database creation and version management. It implements onCreate() and onUpgrade() to take care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state (e.g., database.execSQL(DATABASE_CREATE)).

Creating the SQLite database

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

The onCreate() method is invoked when the database is created for the first time. It calls database.execSQL(DATABASE_CREATE) to create the database. Note that the DATABASE_CREATE captures the SQL database schema for this app:

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table "
                + TABLE_COMMENTS
                + "("
            + COLUMN_ID
            + " integer primary key autoincrement, "
            + COLUMN_COMMENT
            + " text not null);";

We create the table and specify the columns which is simply ID and a comment. Importantly, this method is invoked when the database does not exist on the disk. Therefore it is only ever called once. After that the database only needs to be opened and not created each time the app runs -- make sense.

SQLite create table syntax

The syntax for the SQLite create table looks a little odd first time you see it. The "CREATE TABLE" command is used to create a new table in an SQLite database -- see create table for details. I am looking for a better reference that explains the table.

COLUMN_ID and we declare the type as "integer primary key autoincrement" meaning it starts from 0 and increments up as we add entries to the table. Similarly each COLUMN_ID will have a COLUMN_COMMENT and we declared type of the column to be "text not null"

Tip: MyRuns3 will have a more complex database table than the simple example in this demo. In the code below the table is called CREATE_TABLE_ENTRIES and includes exercise objects. This code will be given out as part of the MyRuns3. The onCreate() and onUpgrade() methods are also detailed. The code in the methods looks very similar to this comment demo code but the database is considerable different. You need to get started early on this weeks lab to build the database.

            // SQL query to create the table for the first time
            // Data types are defined below
            // Note that we use strings rather than INTEGER or FLOAT for entries
            //
            public static final String CREATE_TABLE_ENTRIES = "CREATE TABLE IF NOT EXISTS "
                    + TABLE_NAME_ENTRIES
                    + " ("
                    + KEY_ROWID
                    + " TEXT, "
                    + KEY_INPUT_TYPE
                    + " TEXT, "
                    + KEY_ACTIVITY_TYPE
                    + " TEXT, "
                    + KEY_DATE_TIME
                    + " TEXT, "
                    + KEY_DURATION
                    + " TEXT, "
                    + KEY_DISTANCE
                    + " TEXT, "
                    + KEY_AVG_PACE
                    + " TEXT, ""
                    + KEY_AVG_SPEED
                    + " TEXT, "
                    + KEY_CALORIES
                    + " TEXT, "
                    + KEY_CLIMB
                    + " TEXT, "
                    + KEY_HEARTRATE
                    + " TEXT, "                 
                    + KEY_COMMENT
                    + " TEXT, "
                    + KEY_PRIVACY
                    + " TEXT, "
                    + KEY_GPS_DATA
                    + " TEXT "
                    + ");"; 

            public static void onCreate(SQLiteDatabase database) {
                database.execSQL(CREATE_TABLE_ENTRIES);
              }

              public static void onUpgrade(SQLiteDatabase database, int oldVersion,
                  int newVersion) {
                Log.w(HistoryTable.class.getName(), "Upgrading database from version "
                    + oldVersion + " to " + newVersion
                    + ", which will destroy all old data");
                database.execSQL("DROP TABLE IF EXISTS ");
                onCreate(database);
              }

Constructor

This MySQLiteHelper includes the constructor and two methods to deal with the database. The constructor is:

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

This creates the MySQLiteHelper (which extends SQLiteOpenHelper) object to create, open, and manage a database. This method always returns very quickly. The database is not actually created or opened until getWritableDatabase() (see CommentsDataSource). The parameters past in the super class are as follows:

Note that the TABLE_COMMENTS, COLUMN_ID and COLUMN_COMMENT are made public and therefore accessible to the database storage layer, as discussed earlier.

public class MySQLiteHelper extends SQLiteOpenHelper {

    public static final String TABLE_COMMENTS = "comments";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_COMMENT = "comment";

    private static final String DATABASE_NAME = "commments.db";
    private static final int DATABASE_VERSION = 1;

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table "
            + TABLE_COMMENTS + "(" + COLUMN_ID
            + " integer primary key autoincrement, " + COLUMN_COMMENT
            + " text not null);";

    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(MySQLiteHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
        onCreate(db);
    }

}

Both onCreate() and onUpgrade() methods receive an SQLiteDatabase object as parameter which represents the database. SQLiteOpenHelper provides the methods getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode. See SQLiteOpenHelper for a description of the getWriteableDatabase() method.The database tables should use the identifier "_id" (as shown above) for the primary key of the table.