Android Database Sqlite : Handling creation and upgrade

Hi, i explain in this post the way i use to manage the sqlite database creation and upgrades in my android applications. As you may have already seen, this is done using the class SQLiteOpenHelper : you have to write your own sub class and fill the method onCreate and onUpgrade. So, from that point, there can be a lot of strategies like deleting/recreating the database for upgrades. Let’s talk about mine :

Example of versions :

App version App version code DB version Sql script file
1.00 (first version) 100 100 create.sql
1.01 101 101 upgrade-0101.sql
1.10 110 110 upgrade-0110.sql
2.00 200 200 upgrade-0200.sql


Examples of executions:

Now, about coding:

I store all the sql script files the following directory : assets/sql

Here is my implementation of android.database.sqlite.SQLiteOpenHelper :

package org.michenux.android.db.sqlite;

import java.io.IOException;

import org.michenux.android.db.utils.SqlParser;
import org.michenux.android.resources.AssetUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteDatabaseHelper extends SQLiteOpenHelper {

    private static final Logger log = LoggerFactory.getLogger(SQLiteDatabaseHelper.class);
   
    private static final String SQL_DIR = "sql" ;
   
    private static final String CREATEFILE = "create.sql";
   
    private static final String UPGRADEFILE_PREFIX = "upgrade-";
   
    private static final String UPGRADEFILE_SUFFIX = ".sql";
   
    private Context context ;
   
    public SQLiteDatabaseHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.context = context ;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            log.info("create database");
            execSqlFile( CREATEFILE, db );
        } catch( IOException exception ) {
            throw new RuntimeException("Database creation failed", exception );
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            log.info("upgrade database from {} to {}", oldVersion, newVersion );
            for( String sqlFile : AssetUtils.list(SQL_DIR, this.context.getAssets())) {
                if ( sqlFile.startsWith(UPGRADEFILE_PREFIX)) {
                    int fileVersion = Integer.parseInt(sqlFile.substring( UPGRADEFILE_PREFIX.length(),  sqlFile.length() - UPGRADEFILE_SUFFIX.length()));
                    if ( fileVersion > oldVersion && fileVersion <= newVersion ) {
                        execSqlFile( sqlFile, db );
                    }
                }
            }
        } catch( IOException exception ) {
            throw new RuntimeException("Database upgrade failed", exception );
        }
    }
   
    protected void execSqlFile(String sqlFile, SQLiteDatabase db ) throws SQLException, IOException {
        log.info("  exec sql file: {}", sqlFile );
        for( String sqlInstruction : SqlParser.parseSqlFile( SQL_DIR + "/" + sqlFile, this.context.getAssets())) {
            log.trace("    sql: {}", sqlInstruction );
            db.execSQL(sqlInstruction);
        }
    }
}

How to instanciate it :

this.helper = new SQLiteDatabaseHelper(context, "database", null,
    VersionUtils.getVersionCode(context));

You need this class to retrieve application version code :

package org.michenux.android.info;

import android.content.Context;
import android.content.pm.PackageInfo;
import android.content.pm.PackageManager.NameNotFoundException;

public class VersionUtils {

    public static int getVersionCode( Context context ) throws NameNotFoundException {
        PackageInfo manager= context.getPackageManager().getPackageInfo(
                context.getPackageName(), 0);
        return manager.versionCode;
    }
}

And this one for parsing sql file:

package org.michenux.android.db.utils;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import android.content.res.AssetManager;

public class SqlParser {

    public static List<String> parseSqlFile(String sqlFile, AssetManager assetManager) throws IOException {
        List<String> sqlIns = null ;
        InputStream is = assetManager.open(sqlFile);
        try {
            sqlIns = parseSqlFile(is);
        }
        finally {
            is.close();
        }
        return sqlIns;
    }
   
    public static List<String> parseSqlFile(InputStream is) throws IOException {
        String script = removeComments(is);
        return splitSqlScript(script, ';');
    }

    private static String removeComments(InputStream is) throws IOException {

        StringBuilder sql = new StringBuilder();

        InputStreamReader isReader = new InputStreamReader(is);
        try {
            BufferedReader buffReader = new BufferedReader(isReader);
            try {
                String line;
                String multiLineComment = null;
                while ((line = buffReader.readLine()) != null) {
                    line = line.trim();

                    if (multiLineComment == null) {
                        if (line.startsWith("/*")) {
                            if (!line.endsWith("}")) {
                                multiLineComment = "/*";
                            }
                        } else if (line.startsWith("{")) {
                            if (!line.endsWith("}")) {
                                multiLineComment = "{";
                            }
                        } else if (!line.startsWith("--") && !line.equals("")) {
                            sql.append(line);
                        }
                    } else if (multiLineComment.equals("/*")) {
                        if (line.endsWith("*/")) {
                            multiLineComment = null;
                        }
                    } else if (multiLineComment.equals("{")) {
                        if (line.endsWith("}")) {
                            multiLineComment = null;
                        }
                    }

                }
            } finally {
                buffReader.close();
            }

        } finally {
            isReader.close();
        }

        return sql.toString();
    }

    private static List<String> splitSqlScript(String script, char delim) {
        List<String> statements = new ArrayList<String>();
        StringBuilder sb = new StringBuilder();
        boolean inLiteral = false;
        char[] content = script.toCharArray();
        for (int i = 0; i < script.length(); i++) {
            if (content[i] == ''') {
                inLiteral = !inLiteral;
            }
            if (content[i] == delim && !inLiteral) {
                if (sb.length() > 0) {
                    statements.add(sb.toString().trim());
                    sb = new StringBuilder();
                }
            } else {
                sb.append(content[i]);
            }
        }
        if (sb.length() > 0) {
            statements.add(sb.toString().trim());
        }
        return statements;
    }

}

And this one for listing files in assets/sql :

package org.michenux.android.resources;

import java.io.IOException;
import java.util.Arrays;

import android.content.res.AssetManager;

public class AssetUtils {

    public static boolean exists( String fileName, String path, AssetManager assetManager ) throws IOException  {
        for( String currentFileName : assetManager.list(path)) {
            if ( currentFileName.equals(fileName)) {
                return true ;
            }
        }
        return false ;
    }
   
    public static String[] list( String path, AssetManager assetManager ) throws IOException {
        String[] files = assetManager.list(path);
        Arrays.sort( files );
        return files ;
    }

That’s all, i hope this article was helpful to u.

Share Button

Comments

4 Responses to “Android Database Sqlite : Handling creation and upgrade”

  1. St on June 2nd, 2012 9:59 am

    So parser will fail on sql-insert
    insert into any_table values (‘;’);

  2. Michenux on June 3rd, 2012 9:00 am

    Yes, i know the parser is not perfect. I’ll try to improve it later. Suggestions are welcome.

  3. Michenux on June 3rd, 2012 9:22 pm

    I have updated the parser class. Insert can contain ‘;’ in value.

  4. Android : Around Me (local provider) | Michenux.net on December 21st, 2013 4:41 pm

    […] Here’s the sql script to create the T_PLACE table and to populate it with some data. This script must be executed at the first run of the application. I will not detail this part but you can have a look at this tutorial : Android Database Sqlite : Handling creation and upgrade […]

Leave a Reply