pradeep_ch pradeep_ch - 3 months ago 43
Android Question

How to create a new column if not exist in ORMLite while DB Upgrade

I am using ORMLite, and I am going to release new version.
For this I have a DB upgrade script(json) like this.

{
"version": 9,
"ddlQueries": [
{
"queryDescription": "add a new column column name in tablename table",
"commandType": "ALTER",
"table": "table name",
"column": {
"name": "column name",
"type": "BLOB"
}
},
{
"queryDescription": "Create a new column column name in table name table",
"commandType": "ALTER",
"table": "table name",
"column": {
"name": "column name",
"type": "TEXT"
}
}
]
}


And my onUpgrade method like this.

public void onUpgrade(Object db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
try {
while(++oldVersion<=newVersion) {
AssetManager assetManager = context.getAssets();
InputStream inputStream = assetManager.open(MIGRATION_SCRIPT_PATH + "version-" + oldVersion + ".json");
List<String> queries = new QueryBuilder().loadQueries(new InputStreamReader(inputStream));
inputStream.close();
for (String sql : queries) {
executeSQL(db, sql);
}
postUpgrade(db, connectionSource, oldVersion);
}
} catch (IOException e) {
Log.e(AccountsDBHelper.class.getName(), "exception during onUpgrade", e);
//Below exception must be thrown to rollback changes.
throw new RuntimeException(e);
}
}


Now I want to check if the column already exists, if not exists then only I want to create new column otherwise want to ignore.

How can I do that.

Thanks.

Answer

I am not sure whether we can check for existed column at this stage, I have workaround for this problem.

Here is my workaround!

public void onUpgrade(Object db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
        try {
            while(++oldVersion<=newVersion) {
                if(oldVersion == 9) {
                    Cursor cursor = rawQuery(db,"PRAGMA table_info(TABLE_NAME)",null);
                    int count = cursor.getCount();
                    if(count > 0){
                        boolean isColumn1Available = false;
                        boolean isColumn2Available = false;
                        while (cursor.moveToNext()) {
                            String columnName = cursor.getString(1);
                            if(columnName.equals("column1"))
                                isColumn1Available = true;
                            if(columnName.equals("column2"))
                                isColumn2Available = true;

                        }
                       //Here I want to skip if columns are already created 
                        if(isColumn1Available && isColumn2Available )
                            return;
                    }
                }
                AssetManager assetManager = context.getAssets();
                InputStream inputStream = assetManager.open(MIGRATION_SCRIPT_PATH + "version-" + oldVersion + ".json");
                List<String> queries = new QueryBuilder().loadQueries(new InputStreamReader(inputStream));
                inputStream.close();
                for (String sql : queries) {
                    executeSQL(db, sql);
                }

            }
        } catch (IOException e) {
            Log.e(AccountsDBHelper.class.getName(), "exception during onUpgrade", e);
            //Below exception must be thrown to rollback changes.
            throw new RuntimeException(e);
        }
    }

Thanks.

Comments