Prefilled Database in Android application

This tutorial will introduce you the way to include a pre-filled DB in your android application.

Something to remember

First of all, create the DB schema using the application you prefer.

If you have no idea on how to create the schema try SQLiteBrowser, a free and open source application hosted by Source Forge.

Once you have created your schema, fill db with the data you need. Please remind that it’s a good practice to put resources, such as images and videos, in the assets directory and just reference them in the DB using a filepath field.

Splitting DB File

When DB is filled we can start with the DB split procedure. We must follow few rules to reach the goal:
1- split DB in chunks, each chunk must respect the asset file limits (each file must be less than 1MB).
2 – our app must create/extract the DB file once.

With DB file filled we can proceed to the next step: split file in chunks.

Actually if the DB file size doesn’t exceed the 1MB limit you can put everything in a single file. If the db file size exceeds the limit you have to split DB in chunks.
If you Googled a bit, you probably discovered that opinion is divided about the real upper bound limit of an asset file. But in order to be safe, we can split file in chunk of 1048576 Bytes, about 1MB.

To split the DB  we use the split command available for Mac and Linux, if you are using windows try HJSplit. Assuming the db file is named db.sqlite, we create different db_xx files. I usually rename the file in db_##, just because I prefer numbers to letters.

We now split the file named db.sqlite in several chunks named db_xx :

split -b 1048576 db.sqlite db_

We have some files named db_aa, db_ab, db_ac and so on. We rename these files so that  db_aa become db_01, db_ab become db_02 and so on.

DB splitted folder

Assets folder

After we renamed all the db chunks we can put them together in a single folder named db in the Assets folder as shown in the figure.

Now we are ready to write some code for our application!

Some code

Create a SQLDatabaseHandler subclass that handle the DB. One important thing is to create the file only the first time we start the application. To do that our class must check DB file existence before extracting DB from the assets folder.

Here is a simple implementation of the required methods.

First of all, the createDatabase method.

public void createDatabase() throws IOException {
    boolean dbExist = checkDatabase();
    if (!dbExist) {
        super.getReadableDatabase();
        try {
            copyDatabase();
        }
        catch (IOException e) {
            throw new Error("Error copying database");
        }
    }
}

As shown above, the method check for db file existence. If the file doesn’t exist we make a call to the superclass implementation of getReadableDatabase(). By this way we get an empty file so by calling copyDatabase we overwrite database file with the filled one.

private static String DB_PATH = "/data/data/net.luxteam.lab.dbsplit/databases/";
private static String DB_NAME = "db.sqlite";
private static String ASSETS_DB_FOLDER = "db";
private void copyDatabase() throws IOException {
    String[] dbFiles = myContext.getAssets().list(ASSETS_DB_FOLDER);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream myOutput = new FileOutputStream(outFileName);
    for(int i =0; i < dbFiles.length; i++) {
        InputStream myInput = myContext.getAssets().open(ASSETS_DB_FOLDER+"/"+dbFiles[i]);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myInput.close();
    }
    myOutput.flush();
    myOutput.close();
}

The copyDatabase() method move the content of db from assets files to the single db file used by the application. This code is taken from a stackoverflow post.

The methods shown above are all you need to handle DB file split in your application. DBSplit Project is a link to the source code of the project.
That’s all folks!

Questo articolo è stato pubblicato in Pagine e contrassegnato come da Paolo Bianchi . Aggiungi il permalink ai segnalibri.

Informazioni su Paolo Bianchi

Sono nato a Bologna nel 1984. Da sempre appassionato di informatica. Dal 2007 laureato in Informatica presso l'Università degli studi di Ferrara. Dopo qualche anno a Milano ora vivo e lavoro a Firenze dove sviluppo applicazioni mobile.

8 pensieri su “Prefilled Database in Android application

  1. Pingback: Tweets that mention Prefilled Database in Android application | Lux Team -- Topsy.com

  2. Hi,
    I used this method to ship my database from Assets. It works prefectly in some phones, but it does not work in others.
    I check the logs. It copies all files to database, then, when it attempts to create a query it fails and whole app crushs.

    log:
    03-11 01:11:34.111 I/Database( 4516): sqlite returned: error code =
    11, msg = database corruption at line 46886 of [42537b6056]

    03-11 01:11:34.111 I/Database( 4516): sqlite returned: error code =
    11, msg = database disk image is malformed

    03-11 01:11:34.111 D/AndroidRuntime( 4516): Shutting down VM

    03-11 01:11:34.111 W/dalvikvm( 4516): threadid=1: thread exiting with
    uncaught exception (group=0×40018560)

    03-11 01:11:34.111
    E/AndroidRuntime( 4516): FATAL EXCEPTION: main

    03-11 01:11:34.111 E/AndroidRuntime( 4516):
    android.database.sqlite.SQLiteDatabaseCorruptException: database disk
    image is malformed: , while compiling: SELECT Names FROM MyTable WHERE
    PageaNo = 1

    can you please help me

      • what I really do not understand is that it works perfectly in some phones and does not work in others.

        I mean, if there were any problem about codes that copies the database or SQL query codes, it would not work in any phone.

        Besides, I used exactly the same code you wrote above, so I do not think it is about copying.

        • You are probably right. What I suggest is to check the memory on those phones. I mean, is it a memory problem (file copy) or is it an sqlite problem? I’m sorry, I’m not very useful, because I’ve never had this problem.

  3. Hi there just wanted to give you a brief heads up and let you
    know a few of the pictures aren’t loading properly.
    I’m not sure why but I think its a linking issue. I’ve
    tried it in two different browsers and both show the same results.

Lascia un Commento

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *

È possibile utilizzare questi tag ed attributi XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>