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

Question

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 1

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.