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;

    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 + ";" );

    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.

Category shuffling

category structureI took some time over the last few days to reshuffle/restructure some of the tags and categories on this site.  I’ve been doing more product reviews lately, and those have found themselves in a very generic category, while they are precise in nature. I hope this new structure will help my readers get the information that they want.

Posts about men’s grooming topics, including but not limited to product reviews now have their own category called, you guessed it “Grooming”.  If this is the only subject you are interested in, you can now filter out the rest.

For the technologically inclined of you that only want my take on the technology aspects, there is a category ( including a couple sub-categories ) for you “Technology” and that covers “Gadgets” and “Programming” ( for now )

For those who just want to hear me venting, I tend to tag my rants under the “Rant” category, but the rants sometimes are specific to something and thus will appear in the Rant category and whatever I am ranting about.

sorry if this changes things for you somewhat, but I’ve been wanting to do this for a little bit to make things clearer.


Montreal is falling apart

I am proud to call myself a Canadian, and proud to be from Montreal ( but don’t tell anyone. ).  In the past couple of years, we have seen our proud and wonderful city begin to fall into ruin.  From overpasses falling down on the highway, to trains falling over on bridges to bridges being considered unsafe to drive on, to potholes the size of a VW beetle, how can we hope to attract tourists ( which means revenue ), respect and prestige that was once common place with Montreal.

People would come from all over the world to visit Montreal.  From the world class festivals ( Jazz, Just for Laughs, Francopholies, … ) to the historical areas ( Old Port, cathedrals, … ) to the social diversity present in our town. website domain names search .  But now, I can’t begin to imagine people will continue to see us as a destination spot, with so many “incidents” ( as the minister of transport calls them ) happening.  It all started with the overpass in Laval that fell and killed 5 people ( it even has its own wiki page ).  The city and the province are quick to blame everything on the “unique” climate condition we face here, but come on, there are other city’s provinces close by that have close enough conditions and they don’t have 2 tonnes of concrete landing on people, or cave like pot holes ( http://www.cbc.ca/news/canada/montreal/story/2011/08/02/large-pothole-in-montreal.html ) in the middle of a metropolitan city.

Not to deter the communities that receive relief funds after major storms/earthquakes I wonder at which point they will start refusing our aide/help thinking “If they can’t take care of their own city, how can they possibly help us”.  Personally, if i was someone in Haiti in charge of rebuilding roads, houses, structures i’m not sure i would want someone from Montreal helping out after the incidents spanning the past couple of years, to name a few incidents.

My question is what has the mayor of Montreal been doing with all the taxes, money from the provincial government, and tourist revenue?  It sure hasn’t been to take care of the beautiful city that was entrusted to him when he was elected mayor.  We have gone from first class destination to 3rd world war zone.  Our potholed covered roads are still in better condition that most war ravaged countries.

When will we wake and do something about it.  Its obvious with the scandals that have plagued this administration that they aren’t worthy of the trust we put in them, maybe its time to change a few things.

As far as I’m concerned, its going to continue to get a lot worse before it gets better, at least if we continue down the path we are going, “Critical” status reports obviously don’t matter to our government and administration, they don’t act upon them.  They put our lives at risk everyday because its inconvenient to do the things that actually matter.  For those of you who work near the festival area in montreal ( in/around Complexe desjardins ) I would have been ashamed to host the festivals with the road conditions.  you had constructions crews blocking the roads.  Instead of a light and festive atmosphere, you had dust, heavy equipment, and fencing to enjoy.  This was a massive fail for you Mr Tremblay, you have disrespected our great and wonderful city.  Shame on you.

a concerned citizen