The following is the first few sections of a chapter from The Busy Coder's Guide to Android Development, plus headings for the remaining major sections, to give you an idea about the content of the chapter.


Packaging and Distributing Data

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.

Prerequisites

Understanding this chapter requires that you have read the chapters on:

Packing a Database To Go

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

Create and Pack the 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:

  1. Create an assets/databases/ directory in your project
  2. Copy your database into this directory (or put it there in the first place, if you prefer)

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

Unpack the Database, With a Little Help(er)

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.

Upgrading Sans Java

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.

Limitations

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.