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

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

  1. RickB on June 12th, 2015 5:39 am

    In IOS we have something called Core Data and in Android we have all the above madness. No wonder one is the rich cousin while the other one is the poor cousin

  2. Sonia on June 17th, 2015 9:53 am

    Great line of code… Very Useful, But can you please share the sample file which will be assets.

Leave a Reply