greenDao Schema Upgrade

Question

I have seen another question about schema upgrade/migration using green dao (here)

There are lots of links in that answer for a good pattern to use when doing schema upgrades - however there are no examples of what you actually do to your data to migrate it properly and I'm having trouble finding anything.

In my case, my migration is incredibly straight forward - I do not wish to transform any existing data, I simply need to add some new tables to my schema, which I suspect is a fairly common situation.

What is the easiest way to add new tables to your schema without deleting data your users have already saved? A specific example would be greatly appreciated.

It would be awesome if greenDao provided a class similar to DevOpenHelper that would simply add new tables/columns that didn't previously exist in the schema without dropping existing tabes/data first.

Answer 1

I finally had time to dig in to this myself and realized it's quite easy to add a new table while retaining data in old tables.

DISCLAIMER: While I realize this implementation is specific to my scenario, I think it's helpful for someone like me who has used an Android ORM tool (greenDao) exclusively to deal with SQLite on Android. I understand this is pretty common for those of you who have written your own table creation queries from the beginning, but for someone who has been sheltered from the guts of using a SQLite DB with Android, I think this example will be helpful.

ANSWER: You can either modify the DevOpenHelper inner class or create your own class. I chose to edit DevOpenHelper for the time being to keep my example simple - however, note that if you regenerate your greendao classes, DevOpenHelper will be overwritten. It would be a better idea to create your own class like "MyOpenHelper" and use that instead.

Before my changes, DevOpenHelper.onUpgrade looked like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
        Log.i("greenDAO", "Upgrading schema from version " + oldVersion + " to " + newVersion + " by dropping all tables");
        dropAllTables(db, true);
        onCreate(db);
}

Instead of dropping all tables, take a look at the createAllTables method that is auto-generated by GreenDao.

Rewrite onUpgrade to check if the "oldVersion" is the one you want to upgrade from, then only call the createTable methods for "new" tables. Here is what my onUpgrade method looks like now:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
        Log.i("greenDAO", "Upgrading schema from version " + oldVersion + " to " + 

        //Going from older schema to new schema
        if(oldVersion == 3 && newVersion == 4)
        {
            boolean ifNotExists = false;

            //Leave old tables alone and only create ones that didn't exist
            //in the previous schema
            NewTable1Dao.createTable(db, ifNotExists);
            NewTable2Dao.createTable(db, ifNotExists);
            NewTable3Dao.createTable(db, ifNotExists);
            NewTable4Dao.createTable(db, ifNotExists);
        }
        else
        {
            dropAllTables(db, true);
            onCreate(db);
        }
}

Adding a new column would be similar, except you'd have to write some SQL or take a look at the auto-generated SQL create statements from greenDao and leverage those.

To add a single new column (NEW_COLUMN, assuming it's an INTEGER type) to an existing table (EXISTING_TABLE), do the following:

db.execSQL("ALTER TABLE 'EXISTING_TABLE' ADD 'NEW_COLUMN' INTEGER");

For me right now, all I needed to do was add new Tables so this ended up being rather straight forward. Hopefully someone else finds this useful.