Android Database
The Android platform ships with support for the SQLite database built in. The android.data.sqlite package provides classes for accessing a SQLite database.
Before a database can be used you must, of course, create a schema for it. You can either use standard SQL commands to create the tables in the database or you can use one of the many SQLite graphical tools for this. I like to use the SQLite Manager add-on for Firefox. Another option is the SQLite Administrator tool.
After creating your database file with one of these tools, you then create the schema.
*** Describe foreign key work-around. ***
CREATE TRIGGER "fk_insert_pet_animal_type" BEFORE INSERT ON pet FOR EACH ROW
WHEN (select _id from animal_type where _id = new.animal_type_id) is null
BEGIN
select raise(rollback,
'insert on table "pet" violates foreign key contraint "fk_insert_pet_animal_type".');
END;
Once the schema is created then you can use the SQLite tool to generate the necessary SQL commands to create your database schema. This SQL can then be copied into your Android app so you can create the SQLite database on the end-user’s device at run-time. The SQLite Manager tool has the following commands (on the File menu) which are useful for this:
Export Database Structure—Generates SQL for the tables and triggers.
Export All Tables—Generates SQL for the tables (including a drop statements for each) and the table data.
Export Database—Generates SQL for the tables (including a drop statements for each), the table data, and any triggers.
Android reserves the table named android_metadata. This table can be created with SQL commands, as follows:
CREATE TABLE android_metadata (locale TEXT);
INSERT INTO "android_metadata" VALUES('en_US');
or the setLocale method can be called after the database has been created.
Once I have the database schema built I like to put it into a raw text file resource for my project. In Eclipse, right-click the project’s res/raw directory and select New → File. Enter a name for the file, like my_schema_v1.sql. This will create a resource for you named R.raw.my_schema_v1.sql. You might use LineNumberReader to load the schema in the onCreate method of your class that extends the SQLiteOpenHandler. Using a LineNumberReader allows you to provide very precise error messages if there are any problems processing the schema on the device. For example:
public void onCreate(SQLiteDatabase db)
{
InputStream inputStream = null;
InputStreamReader streamReader = null;
BufferedReader in = null;
LineNumberReader schema = null;
String sql = null;
try
{
inputStream = _context.getResources().openRawResource(
R.raw.my_schema_v1);
streamReader = new InputStreamReader(inputStream,
"UTF8");
in = new BufferedReader(streamReader);
schema = new LineNumberReader(in);
while ((sql = schema.readLine()) != null)
{
SQLiteStatement stmnt = db.compileStatement(sql);
stmnt.execute();
}
Log.i(Consts.LOG_TAG, String.format(
"Database Created, version: %d", db.getVersion()));
}
catch (Exception error)
{
int lineNumber = (schema == null) ? -1 : schema
.getLineNumber();
if (sql == null) sql = "NULL";
Log
.e(
Consts.LOG_TAG,
String
.format(
"%s - Error creating database at line #%d. (SQL = [%s])",
CLASS_NAME, lineNumber, sql), error);
}
finally
{
try
{
if (schema != null) schema.close();
if (in != null) in.close();
if (streamReader != null) streamReader.close();
if (inputStream != null) inputStream.close();
}
catch (Exception e)
{
Log.e(Consts.LOG_TAG, "Cannot close schema.", e);
}
}
}
Now whenever you need to update this schema just change the contents of the raw file and ADT takes care of the rest.
TO DO
Why aren’t UTF-8 characters (like ’) being preserved in the SQL text stirngs?
Miscellaneous Notes
setLocale
After creating a SQLite database on an Android device, a special table named android_metadata will exist. This table contains one column named locale. The table also contains a single row with the locale field set to the locale that the device has defined. In the U.S. the locale field will contain the value en_US.
setLockingEnabled
Controls whether or not a database is thread-safe or not. By default, locking is enabled and the database is thread-safe. If you know that the database will be accessed by only one thread for a given operation then you can disable locking.