Sometimes, you not only want to ship your code and simple resources with your app, but you also want to ship other types of data, such as an initial database that your app will use when first run. This chapter will examine the means by which you can do those sorts of things.
Understanding this chapter requires that you have read the chapters on:
Android’s support for databases is focused on databases you create
and populate entirely at runtime. Even if you want some initial data
in the database, the expectation is that you would add that via Java
code, such as the series of insert() calls we made in the
DatabaseHelper of the various flavors of the ConstantsBrowser
sample application.
However, that is tedious and slow for larger initial data sets, even if you make careful use of transactions to minimize the disk I/O.
What would be nice is to be able to ship a pre-populated database
with your app. While Android does not offer built-in support for
this, there are a few ways you can accomplish it yourself. One of the
easiest, though, is to use existing third-party code that supports
this pattern, such as Jeff Gilfelt’s SQLiteAssetHelper, available
via
a GitHub repository.
Android Studio users can add a compile
statement to the dependencies closure in build.gradle to pull in
com.readystatesoftware.sqliteasset:sqliteassethelper:... (for some
version indicated by ...).
SQLiteAssetHelper replaces your existing SQLiteOpenHelper
subclass with one that handles database creation and upgrading for
you. Rather than you writing a lot of SQL code for each of those, you
provide a pre-populated SQLite database (for
creation) and a series of SQL scripts (for upgrades).
SQLiteAssetHelper then does the work to set up your pre-populated
database when the database is first accessed and running your SQL
scripts as needed to handle schema changes. And, SQLiteAssetHelper
is open source, licensed under the same Apache License 2.0 that is
used for Android proper.
To examine SQLiteAssetHelper in action, let’s look at the
Database/ConstantsAssets-AndroidStudio
sample project. This is yet another
rendition of the same app as the other flavors of ConstantsBrowser,
but one where we use a pre-populated database.
Whereas normally you create your SQLite database at runtime from Java
code in your app, you now create your SQLite database using whatever
tools you like, at development time. Whether you use the command-line
sqlite3 utility, the SQLite Manager extension for Firefox, or
anything else, is up to you. You will need to set up all of your
tables, indexes, and so forth.
Then, you need to:
assets/databases/ directory in your projectIf your minSdkVersion is less than 11, you will instead need to have
a ZIP or GZIP archive containing the database. The archive should have
the same name as the database file, just with the .zip or .gz
extension.
The reason for the ZIP compression comes from an Android 1.x/2.x limitation
– assets that are compressed by the Android build tools have a
file-size limitation (around 1MB). Hence, you need to store larger
files in a file format that will not be compressed by the Android
build tools, and those tools will not try to compress a .zip file.
In the ConstantsAssets project, you will see an
assets/databases/constants.db file, containing a copy of the
SQLite database with our constants table and pre-populated values.
Your compressed database will ship with your APK. To get it into its
regular position on internal storage, you use SQLiteAssetHelper.
Simply create a subclass of SQLiteAssetHelper and override its
constructor, supplying the same values as you would for a
SQLiteOpenHelper subclass, notably the database name and schema
revision number. Note that the database name that you use must match
the filename of the compressed database (minus the .zip extension,
if you needed that).
So, for example, our new DatabaseHelper looks like this:
package com.commonsware.android.dbasset;
import android.content.Context;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
class DatabaseHelper extends SQLiteAssetHelper {
static final String TITLE="title";
static final String VALUE="value";
static final String TABLE="constants";
private static final String DATABASE_NAME="constants.db";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
}
SQLiteAssetHelper will then copy your database out of assets and
set it up for conventional use, as soon as you call
getReadableDatabase() or getWritableDatabase() on an instance of
your SQLiteAssetHelper subclass.
Traditionally, with SQLiteOpenHelper, to handle a revision in your
schema, you override onUpgrade() and do the upgrade work in there.
With SQLiteAssetHelper, there is a built-in onUpgrade() method
that uses SQL scripts in your APK to do the upgrade work instead.
These scripts will also reside in your assets/databases/ directory
of your project. The name of the file will be
$NAME_upgrade_$FROM-$TO.sql, where you replace $NAME with the
name of your database (e.g., constants.db), $FROM with the old
schema version number (e.g., 1) and $TO with the new schema
version number (e.g., 2). Hence, you wind up with files like
assets/databases/constants.db_upgrade_1-2.sql. This should contain
the SQL statements necessary to upgrade your schema between the
versions.
SQLiteAssetHelper will chain these together as needed. Hence, to
upgrade from schema version 1 to 3, you could either have a single
dedicated 1->3 script, or a 1->2 script and a 2->3 script.
The biggest limitation comes with disk space. Since APK files are
read-only at runtime, you cannot delete the copy of the database held
as an asset in your APK file once SQLiteAssetHelper has unpacked
it. This means that the space taken up by your ZIP file will be taken
up indefinitely. Note, though, that you could use this to your
advantage, offering the user a “start over from scratch” option that
deletes their existing database, so SQLiteAssetHelper will unpack a
fresh original copy on the next run. Or, you could implement a
SQLiteDownloadHelper that follows the SQLiteAssetHelper approach
but obtains its database from the Internet instead of from assets.
In principle, SQLite could change their file format. If that ever happens, you will need to make sure that you create a SQLite database in the file format that can be used by Android, more so than what can be used by the latest SQLite standalone tools.