Creating Your Application’s SQLite ContentProvider

The best place to store and retrieve a user’s tasks in the Task Reminder application is inside an SQLite database. Your application needs to be able to perform CRUD — create, read, update, and delete — tasks from the database using a ContentProvider.

Understanding how the SQLite ContentProvider works

The two fragments in the Task Reminder application need to perform various duties to operate. ReminderEditFragment needs to complete these steps:

1. Create a new record.

2. Read a record so that it can display the details for editing.

3. Update the existing record.

The ReminderListFragment needs to perform these duties:

1. Read all tasks to show them onscreen.

2. Delete a task by responding to the click event from the context menu after a user has long-pressed an item.

To work with an SQLite database, you communicate with the database via a ContentProvider. Programmers commonly remove as much of the database communication as possible away from the Activity and Fragment objects. The database mechanisms are placed into a ContentProvider to help separate the application into layers of functionality. Therefore, if you need to alter code that affects the database, you know that you need to change the code in only one location to do so.

Creating a ContentProvider to hold the database code

To create a ContentProvider in your Android project that will house the database-centric code, you first name the file ReminderProvider.java.

Defining the key elements of a database

Before you create and open a database, you need to define a few key fields. Replace the code in your ReminderProvider class with the code from Listing 12-1.

Listing 12-1: The Constants, Fields, and Constructors of the RemindersDbAdapter Class

package com.dummies.android.taskreminder;

import android.content.ContentProvider;

import android.database.sqlite.SQLiteDatabase;

public class ReminderProvider extends ContentProvider {

// Database Related Constants

private static final int DATABASE_VERSION = 1; →8

private static final String DATABASE_NAME = “data”; →9

private static final String DATABASE_TABLE = “reminders”; →10

// Database Columns

public static final String COLUMN_ROWID = “_id”; →13

public static final String COLUMN_DATE_TIME = “reminder_date_time”; →14

public static final String COLUMN_BODY = “body”; →15

public static final String COLUMN_TITLE = “title”; →16

private static final String DATABASE_CREATE = “create table “ →18

+ DATABASE_TABLE + “ (“ + COLUMN_ROWID

+ “ integer primary key autoincrement, “ + COLUMN_TITLE

+ “ text not null, “ + COLUMN_BODY + “ text not null, “

+ COLUMN_DATE_TIME + “ integer not null);”;

private SQLiteDatabase mDb; →24

@Override

public boolean onCreate() { →27

mDb = new DatabaseHelper(getContext()).getWritableDatabase(); →28

return true;

}

}

The numbered lines are explained in this list:

→8 The version of the database. If you were to update the schema in your database, you would increment the version and provide an implementation of the onUpgrade() method of the DatabaseHelper.

→9 The physical name of the database that will exist in the Android file system.

→10 The name of the database table that will hold the tasks.

→13–16 Define the column names of the database table.

→18 Defines the create script for the database. Column names from earlier lines are combined into a single SQL statement that will create the database.

→24 The class-level instance of the SQLite database object that allows you to create, read, update, and delete records.

→27 Creates the ContentProvider and calls the onCreate().

→28 Calls getWritableDatabase() on a DatabaseHelper object.

The SQL database is ready to be created!

For information about the database table or components of the script, see the next section “Visualizing the SQL table.” For information about the database helper or the database table, see the later section “Creating the database table.”

Visualizing the SQL table

The table object in SQL is the construct that holds the data you manage. Visualizing a table in SQLite is similar to looking at a spreadsheet: Each row consists of data, and each column represents the data inside the row. Earlier in this chapter, Listing 12-1 defines column names for the database. These column names equate to the header values in a spreadsheet, as shown in Figure 12-1. Each row contains a value for each column, which is how data is stored in SQLite.

Line 18 in Listing 12-1 assembles the database create script, which concatenates various constants from within the file to create a database create script. When you run this script in SQLite, SQLite creates a table named reminders in a database named data. The columns and how they’re built in the create script are described in this list:

create table DATABASE_TABLE: This portion of the script notifies SQLite that you want to create a database table named reminders.

COLUMN_ROWID: This property acts as the identifier for the task. This column has the integer primary key autoincrement attributes applied to it. The integer attribute specifies that the row is an integer. The primary key attribute states that the COLUMN_ROWID is the primary identifier for a task. The autoincrement attribute notifies SQLite, whenever a new task is inserted, to simply set the row’s ID to the next available integer automatically. For example, if rows 1, 2, and 3 exist and you insert another record, the value of the COLUMN_ROWID in the next row is 4.

COLUMN_TITLE: The user provides this task title, such as Schedule Vacation. The text attribute informs SQLite that the column is a text column. The not null attribute states that the value of this column cannot be null — the user must provide a value.

COLUMN_BODY: This is the body or description of the task. The attributes for this column are the same as for COLUMN_TITLE.

COLUMN_DATE_TIME: The date and time of the reminder are stored in this field. It stores an integer because SQLite has no storage class associated with storing dates or times, so you convert the Calendar object to a Java long, which can be represented — problem free — as an SQL integer.

For more information on dates and times in SQLite, visit www.sqlite.org/datatype3.html#datetime .

Creating the database table

When you’re ready to create the database table, you provide an implementation of SQLiteOpenHelper. The ReminderProvider class type, shown in Listing 12-2, lets you create a nested Java class inside the RemindersDbAdapter class.

Listing 12-2: Creating a Database Table

private static class DatabaseHelper extends SQLiteOpenHelper { →1

DatabaseHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION); →3

}

@Override

public void onCreate(SQLiteDatabase db) { →7

db.execSQL(DATABASE_CREATE); →8

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion,

int newVersion) { →12

throw new UnsupportedOperationException();

}

}

The numbered lines are described in this list:

→1 The implementation of SQLiteOpenHelper.

→3 The call made to the base SQLiteOpenHelper constructor. This call creates, opens, and/or manages a database, which isn’t created or opened until getReadableDatabase() or getWriteable Database() is called on the SQLiteOpenHelper instance.

→7 The onCreate() method, which is called when the database is created for the first time.

→8 Creates your database and database table (“where the magic happens”). The execSQL() method accepts an SQL script string as a parameter. The SQLite database executes the SQL from Listing 12-1 to create the database table.

→12 Uses the onUpgrade() method when you need to upgrade an existing database.