Easing the pain of Database maintenance on android

I’ve been working on a couple android apps for the past couple weeks, and have gone through some of the “new platform” growing pains.  Not that Android is the problem, just the changing from a platform/framework that I am familiar with, to something new and exciting.  This post won’t cover how to use a database, however I may get around to writing one as i get more experience with it.  This post will cover more specifically how to create/update SQLite databases on the android platform.

Most examples (in fact ALL the examples I could find ) have you put your DDL statements as string inside a class, typically the class you create to extends the SQLiteOpenHelper.

// Database creation sql statement
	private static final String DATABASE_CREATE = "create table todo (_id integer primary key autoincrement, "
			+ "category text not null, summary text not null, description text not null);";

I’ve found this less than ideal when working with multiple tables, indexes, constraints, etc. I’m used to running through my scripts on a database, running them as a script, so having these “hardcoded” didn’t quite seem right.

After trying a few things out, and some searching for alternatives, I’ve decided to go about this another way. I’ve decided to create some script files (that can easily be tested using sqliteman or any other sqlite db tool. I then save these scripts ( create, alter, etc ) into the assets folder and have my code run them.

public class DBOpenHelper extends SQLiteOpenHelper {

    private final Context context;
    private static final String DATABASE_NAME = "mydb";
    private static final String DATABASE_SCRIPT_CREATE_FILENAME = "create_schema_";
    private static final String DATABASE_SCRIPT_UPDATE_FILENAME = "update_schema_";
    private static final String DATABASE_SCRIPT_INIT_FILENAME = "init_schema_";
    private static final int DATABASE_VERSION = 1;

    public DBOpenHelper( Context context ) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
        this.context = context;
    }

    @Override
    public void onCreate( SQLiteDatabase db ) {
        Log.d( "DBOpenHelper", "inside onCreate" );
        String[] items = getSqlItems( DATABASE_SCRIPT_CREATE_FILENAME );
        for ( String item : items ) {
            if ( item.trim().length() != 0 ) {
                Log.d( "DBOpenHelper#execSQL", item );
                db.execSQL( item + ";" );
            }
        }
    }

    @Override
    public void onUpgrade( SQLiteDatabase db, int oldVersion, int newVersion ) {
        String[] items = getSqlItems( DATABASE_SCRIPT_UPDATE_FILENAME + oldVersion + "_" + newVersion + ".sql" );
        for ( String item : items ) {
            if ( item.trim().length() != 0 ) {
                Log.d( "DBOpenHelper#execSQL", item );
                db.execSQL( item + ";" );
            }

        }
    }

    private String[] getSqlItems( String file ) {
        String[] items = null;
        try {
            BufferedReader reader = new BufferedReader( new InputStreamReader( context.getAssets().open( file ,
                    AssetManager.ACCESS_STREAMING ) ) );

            StringBuffer sql = new StringBuffer();
            String line = null;
            while ( ( line = reader.readLine() ) != null ) {
                sql.append( line );
                sql.append( "\n" );
            }
            items = sql.toString().split( ";" );
        } catch ( IOException ioe ) {
            items = new String[] {};
        }
        return items;
    }

Some may say this might be a disclosure risk ( people will see my DB structure ) but since you can access most SQLite db’s from the SD card, the person could grab that and go from there.

The advantage to this, in my opinion is the ability to test your scripts without the code, for upgrade purposes, and you can manage multiple versions easily. i.e. upgrading from version 1, 2,3,4 to 5 can each be managed by their own script.

Scan to Donate Bitcoin
Like this? Donate Bitcoin to at:
Bitcoin 1C3bk51YNGrEFXNy8HxCWs9QMbRkniDjdJ
Donate

Tim Voet has been in the IT industry since 1997. Tim started his career doing tech support and network administration at a large Pharma company. He then spent the next 12 years doing development, leading and managing development teams, mostly in Java, but also some PHP, and Ruby on Rails. Tim has always hands on, and loves challenges that make most people cringe. When he isn't at work, he's spending as much time as he can with his wife and 3 boys. Life can be a busy time with that many young kids. He is currently open to new consulting opportunities, please feel free to contact him with your project information tim - at - timvoet dot com

1 Comment

  1. Thanks for sharing this. I’ve been trying to find the best way to manage database upgrades and I’m gonna give this a try. The whole hardcoding option bothers me for some reason. I’m thinking part of my reasoning is that the DDL portions may end up growing in size and I’d like that separate from my Java code.

Leave a Reply

Your email address will not be published. Required fields are marked *

*