Programming Android (2011)

Part II. About the Android Framework

Chapter 10. Handling and Persisting Data

To accomplish many of the activities offered by modern mobile phones, such as tracking contacts, events, and tasks, a mobile operating system and its applications must be adept at storing and keeping track of large quantities of data. This data is usually structured in rows and columns, like a spreadsheet or a very simple database. Beyond a traditional application’s requirements for storing data, the Android application life cycle demands rapid and consistent persistence of data for it to survive the volatility of the mobile environment, where devices can suddenly lose power or the Android operating system can arbitrarily decide to remove your application from memory.

Android provides the light-weight but powerful SQLite relational database engine for persisting data. Furthermore, as described in Chapter 3, the content provider feature lets applications expose their data to other applications.

In this chapter, we provide a simple SQL tutorial so that you can learn to work with Android SQLite persistence. We also walk you through an interesting application—MJAndroid—that provides a real-world look at how to manipulate a database in Android. Later, in Chapter 15, we’ll reference the same example to demonstrate the use of the mapping API in Android. Chapter 12 will show you how to implement a content provider.

Relational Database Overview

A relational database provides an efficient, structured, and generic system for managing persistent information. With a database, applications use structured queries to modify information in persistent two-dimensional matrices called tables (or in the original theoretical papers, relations). Developers write queries in a high-level language called the Standard Query Language, or more commonly, SQL. SQL is the common language for relational database management systems (RDBMSs) that have been a popular tool for data management since the late 1970s. SQL became an industry-wide standard when it was adopted by NIST in 1986 and ISO in 1987. It is used for everything from terabyte Oracle and SQL Server installations to, as we shall see, storing email on your phone.

Database tables are a natural fit for data that includes many instances of the same kind of thing—a typical occurrence in software development. For example, a contact list has many contacts, all of which potentially have the same type of information (i.e., address, phone number, etc.). Each “row” of data in a table stores information about a different person, while each “column” stores a specific attribute of each person: names in one column, address in another column, and home phone number in a third. When someone is related to multiple things (such as multiple addresses), relational databases have ways of handling that too, but we won't go into such detail in this chapter.

SQLite

Android uses the SQLite database engine, a self-contained, transactional database engine that requires no separate server process. Many applications and environments beyond Android make use of it, and a large open source community actively develops SQLite. In contrast to desktop-oriented or enterprise databases, which provide a plethora of features related to fault tolerance and concurrent access to data, SQLite aggressively strips out features that are not absolutely necessary in order to achieve a small footprint. For example, many database systems use static typing, but SQLite does not store database type information. Instead, it pushes the responsibility of keeping type information into high-level languages, such as Java, that map database structures into high-level types.

SQLite is not a Google project, although Google has contributed to it. SQLite has an international team of software developers who are dedicated to enhancing the software’s capabilities and reliability. Reliability is a key feature of SQLite. More than half of the code in the project is devoted to testing the library. The library is designed to handle many kinds of system failures, such as low memory, disk errors, and power failures. The database should never be left in an unrecoverable state, as this would be a showstopper on a mobile phone where critical data is often stored in a database. Fortunately, the SQLite database is not susceptible to easy corruption—if it were, an inopportune battery failure could turn a mobile phone into an expensive paperweight.

The SQLite project provides comprehensive and detailed documentation at http://www.sqlite.org/docs.html.

The SQL Language

Writing Android applications usually requires a basic ability to program in the SQL language, although higher-level classes are provided for the most common data-related activities. This chapter provides a beginner’s introduction to SQLite. Although this is not a book about SQL, we will provide you with enough detail about Android-oriented SQL to let you implement data persistence in a wide variety of Android applications. For more comprehensive information pertaining to the SQLite language, see http://www.sqlite.org/lang.html. We’ll use simple SQL commands to explain the SQLite language, and along the way, we’ll demonstrate how to use the sqlite3 command to see the effects those queries have on the tables they modify. You may also find the W3Schools tutorial useful: http://www.w3schools.com/sql/sql_intro.asp.

With SQLite, the database is a simple file in the Android filesystem, which could reside in flash or external card memory, but you will find that most applications’ databases reside in a directory called /data/data/com.example.yourAppPackage/databases. You can issue the ls command in the adb shell to list the databases that Android has created for you in that directory.

The database takes care of persistence—that is, it updates the SQLite file in the way specified by each SQL statement issued by an application. In the following text, we describe SQLite commands as they are used inside the sqlite3 command-line utility. Later we will show ways to achieve the same effects using the Android API. Although command-line SQL will not be part of the application you ship, it can certainly help to debug applications as you’re developing them. You will find that writing database code in Android is usually an iterative process of writing Java code to manipulate tables, and then peeking at created data using the command line.

SQL Data Definition Commands

Statements in the SQL language fall into two distinct categories: those used to create and modify tables—the locations where data is stored—and those used to create, read, update, and delete the data in those tables. In this section we’ll look at the former, the data definition commands:

CREATE TABLE

Developers start working with SQL by creating a table to store data. The CREATE TABLE command creates a new table in an SQLite database. It specifies a name, which must be unique among the tables in the database, and various columns to hold the data. Each column has a unique name within the table and a type (the types are defined by SQL, such as a date or text string). The column may also specify other attributes, such as whether values have to be unique, whether there is a default value when a row is inserted without specifying a value, and whether NULL is allowed in the column.

A table is similar to a spreadsheet. Returning to the example of a contact database, each row in the table contains the information for one contact. The columns in the table are the various bits of information you collect about each individual contact: first name, last name, birthday, and so on. We provide several examples in this chapter that will help you to begin using our job database.

NOTE

The tables created by SQL CREATE TABLE statements and the attributes they contain are called a database schema.

DROP TABLE

This removes a table added with the CREATE TABLE statement. It takes the name of the table to be deleted. On completion, any data that was stored in the table may not be retrieved.

Here is some SQL code that will create and then delete a simple table for storing contacts:

CREATE TABLE contacts (

first_name TEXT,

last_name TEXT,

phone_number TEXT,

height_in_meters REAL);

DROP TABLE contacts;

When entering commands through sqlite3, you must terminate each command with a semicolon.

You may change the database schema after you create tables (which you may want to do to add a column or change the default value of a column) by entering the ALTER TABLE command.

SQLite types

You must specify a type for each column that you create in all tables that you define, as discussed in SQL Data Definition Commands. SQLite supports the following data types:

TEXT

A text string, stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE). You will find that the TEXT type is the most common.

REAL

A floating-point value, stored as an 8-byte IEEE floating-point number.

BLOB

Arbitrary binary data, stored exactly as if it was input. You can use the BLOB data type to store any kind of variable-length data, such as an executable file, or a downloaded image. Generally, blobs can add a large performance overhead to a mobile database and you should usually avoid using them. In Chapter 13, we present an alternate scheme to store images downloaded from the Internet.

INTEGER

A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Specific information regarding SQLite types is available at http://www.sqlite.org/datatype3.html.

Database constraints

Database constraints mark a column with particular attributes. Some constraints enforce data-oriented limitations, such as requiring all values in a column to be unique (e.g., a column containing Social Security numbers). Other constraints exhibit more functional uses. Relational constraints,PRIMARY KEY and FOREIGN KEY, form the basis of intertable relationships.

Most tables should have a particular column that uniquely identifies each given row. Designated in SQL as a PRIMARY KEY, this column tends to be used only as an identifier for each row and (unlike a Social Security number) has no meaning to the rest of the world. Thus, you do not need to specify values for the column. Instead, you can let SQLite assign incrementing integer values as new rows are added. Other databases typically require you to specially mark the column as autoincrementing to achieve this result. SQLite also offers an explicit AUTOINCREMENT constraint, but autoincrements primary keys by default. The incrementing values in the column take on a role similar to an opaque object pointer in a high-level language such as Java or C: other database tables and code in a high-level language can use the column to reference that particular row.

When database rows have a unique primary key, it is possible to start thinking about dependencies between tables. For example, a table used as an employee database could define an integer column called employer_id that would contain the primary key values of rows in a different table calledemployers. If you perform a query and select one or more rows from the employers table, you can use grab their IDs and to look up employees in an employees table through the table's employer_id column. This allows a program to find the employees of a given employer. The two tables (stripped down to a few columns relevant to this example) might look like this:

CREATE TABLE employers (

_id INTEGER PRIMARY KEY,

company_name TEXT);

CREATE TABLE employees (

name TEXT,

annual_salary REAL NOT NULL CHECK (annual_salary > 0),

employer_id REFERENCES employers(_id));

The idea of a table referring to another table’s primary key has formal support in SQL as the FOREIGN KEY column constraint, which enforces the validity of cross-table references. This constraint tells the database that integers in a column with a foreign key constraint must refer to valid primary keys of database rows in another table. Thus, if you insert a row into the employees table with an employer_id for a row that does not exist in the employers table, many flavors of SQL will raise a constraint violation. This may help you to avoid orphaned references, also known as enforcement of foreign keys. However, the foreign key constraint in SQLite is optional, and is turned off in Android. As of Android 2.2, you cannot rely on a foreign key constraint to catch incorrect foreign key references, so you will need to take care when creating database schemas that use foreign keys.

There are several other constraints with less far-reaching effects:

UNIQUE

Forces the value of the given column to be different from the values in that column in all existing rows, whenever a row is inserted or updated. Any insert or update operation that attempts to insert a duplicate value will result in an SQLite constraint violation.

NOT NULL

Requires a value in the column; NULL cannot be assigned. Note that a primary key is both UNIQUE and NOT NULL.

CHECK

Takes a Boolean-valued expression and requires that the expression return true for any value inserted in the column. An example is the CHECK (annual_salary > 0), attribute shown earlier in the employees table.

SQL Data Manipulation Commands

Once you have defined tables using data definition commands, you can then insert your data and query the database. The following data manipulation commands are the most commonly used SQL statements:

SELECT

This statement provides the main tool for querying the database. The result of this statement is zero or more rows of data, where each row has a fixed number of columns. You can think of the SELECT statement as producing a new table with only the rows and columns that you choose in the statement. The SELECT statement is the most complicated command in the SQL language, and supports a broad number of ways to build relationships between data across one or more database tables. Clauses for SQL’s SELECT command, which are all supported by the Android API, include the following:

§ FROM, which specifies the tables from which data will be pulled to fulfill the query.

§ WHERE, which specifies conditions that selected rows in the tables must match to be returned by the query.

§ GROUP BY, which orders results in clusters according to column name.

§ HAVING, which further limits results by evaluating groups against expressions. You might remove groups from your query that do not have a minimum number of elements.

§ ORDER BY, which sets the sort order of query results by specifying a column name that will define the sort, and a function (e.g., ASC for ascending, DSC for descending) that will sort the rows by elements in the specified column.

§ LIMIT, which limits the number of rows in a query to the specified value (e.g., five rows).

Here are a few examples of SELECT statements:

SELECT * FROM contacts;

SELECT first_name, height_in_meters

FROM contacts

WHERE last_name = "Smith";

SELECT employees.name, employers.name

FROM employees, employers

WHERE employee.employer_id = employer._id

ORDER BY employer.company_name ASC;

The first statement retrieves all the rows in the contacts table, because no WHERE clause filters results. All columns (indicated by the asterisk, *) of the rows are returned. The second statement gets the names and heights of the members of the Smith family. The last statement prints a list of employees and their employers, sorted by company name.

For more information, see http://www.sqlite.org/lang_select.html.

INSERT

This statement adds a new data row to a specified database table along with a set of specified values of the proper SQLite type for each column (e.g., 5 for an integer). The insert may specify a list of columns affected by the insert, which may be less than the number of columns in the table. If you don’t specify values for all columns, SQLite will fill in a default value for each unspecified column, if you defined one for that column in your CREATE TABLE statement. If you don’t provide a default, SQLite uses a default of NULL.

Here are a few examples of INSERT statements:

INSERT INTO contacts(first_name)

VALUES("Thomas");

INSERT INTO employers VALUES(1, "Acme Balloons");

INSERT INTO employees VALUES("Wile E. Coyote", 100000.000, 1);

The first adds a new row to the contacts for someone whose first name is Thomas and whose last name, phone number, and height are unknown (NULL). The second adds Acme Balloons as a new employer, and the third adds Wile E. Coyote as an employee there.

For more information, see http://www.sqlite.org/lang_insert.html.

UPDATE

This statement modifies some rows in a given table with new values. Each assignment specifies a table name and a given function that should provide a new value for the column. Like SELECT, you can specify a WHERE clause that will identify the rows that should be updated during an invocation of the UPDATE command. Like INSERT, you can also specify a list of columns to be updated during command execution. The list of columns works in the same manner as it does with INSERT. The WHERE clause is critical; if it matches no rows, the UPDATE command will have no effect, but if the clause is omitted, the statement will affect every row in the table.

Here are a few examples of UPDATE statements:

UPDATE contacts

SET height_in_meters = 10, last_name = "Jones"

UPDATE employees

SET annual_salary = 200000.00

WHERE employer_id = (

SELECT _id

FROM employers

WHERE company_name = "Acme Balloons");

The first claims that all your friends are giants with the last name Jones. The second is a more complex query. It gives a substantial raise to all the employees of Acme Balloons.

For more information, see http://www.sqlite.org/lang_update.html.

Additional Database Concepts

You now know enough simple SQL to be able to start working with databases in Android. As the applications you write grow in sophistication, you are likely to make use of the following SQL constructs that we won’t cover in detail in this book:

Inner join

An inner join selects data across two or more tables where data is related by a foreign key. This type of query is useful for assembling objects that need to be distributed across one or more tables. The employee/employer example earlier demonstrated an inner join. As we’ve noted, since Android does not enforce foreign keys, you can get into trouble here if a key for a join does not exist as a valid cross-table reference—that is, a foreign key column actually points to a primary key of a row in another table that actually exists.

Compound query

SQLite supports complex database manipulations through combinations of statements. One of the update examples shown earlier was a compound query with a SELECT embedded in an UPDATE.

Triggers

A database trigger allows a developer to write SQL statements that will receive a callback when particular database conditions occur.

For detailed information on these topics, we suggest you consult a book on SQL, such as Learning SQL by Alan Beaulieu or SQL Pocket Guide by Jonathan Gennick, both published by O’Reilly.

Database Transactions

Database transactions make sequences of SQL statements atomic: either all statements succeed or none of them have any effect on the database. This can be important, for instance, if your app encounters an unfortunate occurrence such as a system crash. A transaction will guarantee that if the device fails partway through a given sequence of operations, none of the operations will affect the database. In database jargon, SQLite transactions support the widely recited ACID transaction properties: http://en.wikipedia.org/wiki/ACID.

With SQLite, every database operation that modifies a database runs in its own database transaction, which means a developer can be assured that all values of an insert will be written if the statement succeeds at all. You can also explicitly start and end a transaction so that it encompasses multiple statements. For a given transaction, SQLite does not modify the database until all statements in the transaction have completed successfully.

Given the volatility of the Android mobile environment, we recommend that in addition to meeting the needs for consistency in your app, you also make liberal use of transactions to support fault tolerance in your application.

Example Database Manipulation Using sqlite3

Now that you understand the basics of SQL as it pertains to SQLite, let’s have a look at a simple database for storing video metadata using the sqlite3 command-line tool and the Android debug shell, which you can start by using the adb command. Using the command line will allow us to view database changes right away, and will provide some simple examples of how to work with this useful database debugging tool. SQLite has more information on sqlite3 at http://www.sqlite.org/sqlite.html. Note that it is likely easiest at first to run this example using the Android emulator, since you will need root access in order to run it on a device.

We’ll get the example started by initializing the database:

$ adb shell

# cd /data/data/

# mkdir com.oreilly.demo.pa.ch10.sql

# cd com.oreilly.demo.pa.ch10.sql

# mkdir databases

# cd databases

# sqlite3 simple_video.db

SQLite version 3.6.22

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

NOTE

Note that developers should not create these directories by hand, as we have done in this example, since Android will create them during installation of an application. Directory creation is merely useful for this particular example because we do not yet have an application in which the directories would have been automatically created.

The sqlite3 command line accepts two kinds of commands: legal SQL, and single-word commands that begin with a period (.). You can see the first (and probably most important!) of these in the introduction message: .help. Try it out, just to get an idea of the options available to you:

sqlite> .help

.bail ON|OFF Stop after hitting an error. Default OFF

.databases List names and files of attached databases

.dump ?TABLE? ... Dump the database in a SQL text format

.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.

.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 on TABLE

.load FILE ?ENTRY? Load an extension library

.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

.schema ?TABLE? Show the CREATE statements

.separator STRING Change separator used by output mode and .import

.show Show the current values for various settings

.tables ?PATTERN? List names of tables matching a LIKE pattern

.timeout MS Try opening locked tables for MS milliseconds

.timer ON|OFF Turn the CPU timer measurement on or off

.width NUM NUM ... Set column widths for "column" mode

There’s another important command in this list: .exit. Remember it! It’s how you get out of here. Alternatively, you can quit with the Ctrl-D keystroke.

Another important thing to remember is that every SQL command needs to be terminated with a semicolon. If you see something like this:

sqlite> select * from video

...>

it just means SQLite thinks you’ve started to enter SQL, and it is waiting for the ; at the end. Note that the . commands do not need to be terminated by a semicolon.

NOTE

We’ve used ls as an example of a command a user might have absentmindedly typed if he forgot he was using sqlite3. ls is not actually a sqlite3 command; if you type ; after ls, sqlite will complain with an error, and then you can enter correct dot commands or sql statements.

Most of the “dot” commands aren’t very interesting at this point, because this database is still empty. So let’s add some data:

sqlite> create table video (

...> _id integer primary key,

...> title text,

...> description text,

...> url text);

These lines create a new table called video. The types of the columns are integer and text. The table contains a primary key called _id. This particular column name is not chosen accidentally. Android requires the use of this exact name in order for the table to work with its cursor system.

We can see the newly created tables using the “dot” command .table:

sqlite> .table

video

sqlite>

Next we’ll go through a few different queries that illustrate the SQL concepts we introduced earlier, and that an application based on these tables. First, let’s insert some data into our new tables so that our queries return some example results: