Understanding Bundled SQLite databases in AIR for Mobile

Quite often you will want to ship your mobile application with a prepopulated SQLite database. However there are a few things you need to do to before you can fully use them in your application. This article will walk you through the steps you need to take to use a SQLite database on your mobile device.

The first thing we need to do is include our prepopulated SQLite database in our AIR application. For applications built with Flash Professional CS5, go to File > AIR for Android Settings…, then select the General tab . Click the plus icon above the Include Files section, then select your .db file. If you are building your AIR application using Flash Builder, you just need to include the database in src directory.

Now, when you publish your application for your favorite mobile device, the prepopulated database will be automatically included when the application is installed. Here is where things can get a little tricky if you don’t understand the various application directories and permissions.

Without doing anything our application will read the data from our database using this ActionScript snippet:

var sessionDBConn:SQLConnection;
var sessionDBFile:File;
var selectStmt:SQLStatement;
sessionDBConn = new SQLConnection();
sessionDBConn.addEventListener(SQLEvent.OPEN, openSessionSuccess);
sessionDBConn.addEventListener(SQLErrorEvent.ERROR, openSessionFailure);

var embededSessionDB:File = File.applicationDirectory.resolvePath("demo.db");
sessionDBConn.openAsync(embededSessionDB);

The issue will be when we try to perform any create/update/delete functions on the database, as they will fail. Although we can read from the NativeApplication.applicationDirectory, we are not allowed to write to this directory. We need to find a solution to transfer the database to a location that we can have write access to. Thankfully, AIR also has another custom directory just for this type of use; NativeApplication.applicationStorageDirectory.

The first thing we need to check for is if we have already copied our database to the writable directory. We can check the exists property on the File reference to database in the writable directory to test this condition.

var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("demo.db");
if (!writeSessionDB.exists) {
    embededSessionDB.copyTo(writeSessionDB);
}

Since we have not copied our database to a writable location, the exists property will be false. Now to need to perform our file copy from the applicationDirectory to the applicationStorageDirectory using copyTo method:

embededSessionDB.copyTo(writeSessionDB);

If the database is large, you might want to consider using the copyToAsync method instead.

As with all read and write methods, make sure you include event listeners for both IOErrors and SecurityErrors.

With that, our prepopulated database has been copied to a writable directory and is ready for use.

sessionDBConn = new SQLConnection();
sessionDBConn.addEventListener(SQLEvent.OPEN, openSessionSuccess);
sessionDBConn.addEventListener(SQLErrorEvent.ERROR, openSessionFailure);
var embededSessionDB:File = File.applicationDirectory.resolvePath("demo.db");
var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("demo.db");
if (!writeSessionDB.exists) {
    embededSessionDB.copyTo(writeSessionDB);
}
sessionDBFile = writeSessionDB;
sessionDBConn.openAsync(writeSessionDB);

Hopefully this article will help you as you start developing mobile applications that use prepopulated databases.