The Usual way

Android applications usually do not need to start with a preloaded database file. Usually, the application will rely on a web service to persist such data. And when it needs, it asks the web service for the data it needs. The use of SQLiteOpenHelper fits well with such use case.
But in the rare situations when the application needs to start with a pre-loaded database, you have to manipulate the SQLiteOpenHelper to play nicely.
One of the most clean manipulations (in my opinion) is to override the onCreate() to create the database, and then read a sql script file (that inserts the data you want) into sql statements and execute them with SQLiteDatabase#execSql(sql_statement_string)

public class Repository extends SQLiteOpenHelper {

    private static final String CREATE_SCRIPT = "create_script.sql";
    private static final String INSERT_SCRIPT = "insert_script.sql";

    private String[] parseSql(InputStream input) throws IOException {
        //parse the sql script file here and return array of
        //string statements
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        InputStream in_create_stream = null;
        InputStream in_insert_stream = null;
        try {
            in_create_stream = mContext.getResources().getAssets()
                .open(CREATE_SCRIPT);
            String[] statements = parseSqlFile(in_create_stream);
            for (String statement : statements) {
                db.execSQL(statement);
            }
            in_insert_stream = mContext.getResources().getAssets()
                .open(INSERT_SCRIPT);
            statements = parseSqlFile(in_insert_stream);
            for (String statement : statements) {
                db.execSQL(statement);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (in_create_stream != null) {
                    in_create_stream.close();
                }
                if (in_insert_stream != null) {
                    in_insert_stream.close();
                }
            } catch (IOException e) {
            }
        }
    }
}

There is no problem at all with that, except when the data to be inserted into the database is so big that it takes forever to execute.

The Quest

Looking at the source code I notices that the SQLiteStatement uses a native method native_execute() to actually execute our sql insert statements. but, it seems to do a long expensive preparations before calling the native method.
So, all I have to do is step over these preparations. But since, this is not doable throw the exposed interface, I had to step over the whole thing and do my own native call.

The NDK

Before you jump to conclusions, the sqlite library is not exposed in the NDK, either. The route I'm taking here is:

  1. compile the sqlite library from source.
  2. link it as a static library with my application
  3. write a native function that takes the sql script and execute it

I'll not guide you through setting up your NDK environment. There are lots of tutorials that do a good job in that.
worth mentioning that I'm using NDK r8.

SQlite

SQlite is open source. And to ease things more, SQlite team has combined all the C source code for SQlite into a single C file. Go get it. [ download ]
Create a directory under your project root and name it jni.
Now, extract the files sqlite3.c and sqlite3.h, from the SQlite source code archive you downloaded earlier, into the jni directory.

The Java Side of the Story

We will call our class that extends SQLiteOpenHelper, Repository. In the Repository class add the following:

public class Repository extends SQLiteOpenHelper {  
    // BEGIN
    static {
        System.loadLibrary("sqlitenative");
    }
    private native int nativeExecFile(AssetManager assetManager,
        String scriptFile);
    // END
}

Lines from 3-5 tells the system to load our library. The sqlitenative is our native library name. The library that will host our native methods.
Lines 6-7 declares our native method which is called nativeExecFile. The method takes the AssetManager as a parameter because the sql script is stored as an asset in the application. The native function cannot acquire an instance of the AssetManager, so we pass one to it.
That's it on the java side for now.

Our Native Library

Create a file sqlitenative.c in the jni folder. We will write the following content to the file.

#include <jni.h>
#include "sqlite3.h"
#include <android/asset_manager_jni.h>

#ifdef __cplusplus
extern "C" {  
#endif

#define DB_FILE "/data/data/your.package.namespace/databases/data.db"

JNIEXPORT jint JNICALL Java_your_package_namespace_orm_Repository_nativeExecFile  
  (JNIEnv *env, jobject jobj, jobject jasset_manager, jstring script_file_name) {
  // get the insert script file name
  const char* file_name = (*env)->GetStringUTFChars(env, script_file_name, NULL);
  // get the asset manager
  AAssetManager* asset_manager = AAssetManager_fromJava(env, jasset_manager);
  if (asset_manager == NULL) {
    // failed to get the asset manager from the java AssetManager passed to the function
    return -1;
  } 
  // get the asset descriptor for the insert script file
  AAsset* asset = AAssetManager_open(asset_manager, file_name, AASSET_MODE_STREAMING);
  if (asset == NULL) {
    // failed to get the asset descriptor.
    return -1;
  }

  // your code to splite the sqlite script into statements and execute them goes here.

}

#ifdef __cplusplus
}
#endif

I'll not explain the declaration of the native function. you can see a tutorial in that.
The android/assetmanager.h_ header is the one you need, to work with the asset manager natively.
The database file should be in the /data/data/your.package.namespace/databases/datafilename.
Now you are ready to write some code to to splite your sqlite script file into statements and execute them against our statically linked sqlite library.
I attached the complete file for you to download, if you find it useful. [see the attachments section below]

Be aware though..

This native method opens it's own connection to the sqlite database of yours. that is, any statement executed here cannot be part of any transactions open by the java sqlite API.
To be more clear, consider the following:

SQLiteDatabase db = Repository.getReadableDatabase();  
db.beginTransaction();  
//some sql statements
// sql file through nativeExecFile(...);
//some other sql statements;
if (any_error == true) {  
    db.setTransactionSuccessful(false);
} else {
    db.setTransactionSuccessful(true);
}
db.endTransaction();  

The expected behavior, is to roll back all statements on error. But, because the native method opens it's own connection and does not use the connection opened by the SQLiteDatabase, it will not roll back with the other statements. That is, the transactions opened by the SQLiteDatabase does not affect the native method execution.

The last piece

The last piece of the puzzle is the Android.mk file. This is the make file for the native code. The actual work that builds the sqlite library as a static library for our application to use.
create a Android.mk file in the jni directory and write the following content to it.

LOCAL_PATH:= $(call my-dir)

include $(CLEAR_VARS)

LOCAL_MODULE    := sqlitenative  
LOCAL\_SRC\_FILES := sqlitenative.c \  
                   sqlite3.c

LOCAL_LDLIBS += -landroid

include $(BUILD_SHARED_LIBRARY)  

that's it. You are good to go

Attachments