Upgrading my SQLite db

Question

I am quite new to SQLite.
How do you upgrade a DB?

My previous database version had 7 questions in it.
Today I added about 13 new ones, making them 20 in total.
I added them by using my addQuestion method.
However, it does not work.

I have been tinkering with my onUpgrade.
I am doing something wrong.

public class QuizHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "creativequestion";
    private static final String TABLE_QUEST = "quest";
    private static final String KEY_ID = "qid";
    private static final String KEY_QUEST = "question";

    private SQLiteDatabase dbase;



    public QuizHelper(Context context) {
        super( context, DATABASE_NAME, null, DATABASE_VERSION );
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        dbase = db;
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_QUEST + " ( " + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_QUEST + " TEXT)";
        db.execSQL( sql );
        addQuestion();


    }

    private void addQuestion() {

        QuestionFaci q1 = new QuestionFaci( "OTHER USES: Name other uses of a Hammer. \n\n Example: Stir a soup." );
        this.addQuestion(q1);
        QuestionFaci q2 = new QuestionFaci( "RHYMES: Words that rhymes with Rice. \n\n Example: Ice" );
        this.addQuestion(q2);
        QuestionFaci q3 = new QuestionFaci( "WITH: I can cook eggs with... \n\n Example: A Piece of Plywood" );
        this.addQuestion(q3);
        QuestionFaci q4 = new QuestionFaci( "WITHOUT: I can wash my clothes without... \n\n Example: My Aunt" );
        this.addQuestion(q4);
        QuestionFaci q5 = new QuestionFaci( "I WILL: If I was Bill Gates, I will... \n\n Example: Buy a spaceship" );
        this.addQuestion(q5);
        QuestionFaci q6 = new QuestionFaci( "CREATE A MOVIE TITLE: A NIGHT \n\n Example: To Remember" );
        this.addQuestion(q6);
        QuestionFaci q7 = new QuestionFaci( "OTHER NAMES: Other names of a cow \n\n Example: Milk giver" );
        this.addQuestion(q7);
        QuestionFaci q8 = new QuestionFaci( "OTHER USES: Name other uses of a Cowboy Boots. \n\n Example: Pound a nail." );
        this.addQuestion(q8);
        QuestionFaci q9 = new QuestionFaci( "RHYMES: Bake a. \n\n Example: Flake." );
        this.addQuestion(q9);
        QuestionFaci q10 = new QuestionFaci( "I WILL: I will drive a helicopter \n\n Example: with eyes closed" );
        this.addQuestion(q10);
        QuestionFaci q11 = new QuestionFaci( "CREATE A TITLE: The Greatest \n\n Example: Heist" );
        this.addQuestion(q11);
        QuestionFaci q12 = new QuestionFaci( "CHANGE AN INGREDIENT: --- Cookie \n\n Example: Orange Chip" );
        this.addQuestion(q12);
        QuestionFaci q13 = new QuestionFaci( "FINISH ME: Can you remember the times when.. \n\n Example: push me over a cliff" );
        this.addQuestion(q13);
        QuestionFaci q14 = new QuestionFaci( "OTHER NAMES: Ball bearings \n\n Example: Mommy's new bangles" );
        this.addQuestion(q14);
        QuestionFaci q15 = new QuestionFaci( "FINISH ME: The donut rolls \n\n Example: down the hill" );
        this.addQuestion(q15);
        QuestionFaci q16 = new QuestionFaci( "RHYMES: Flip the \n\n Example: clip" );
        this.addQuestion(q16);
        QuestionFaci q17 = new QuestionFaci( "OTHER NAMES: Police \n\n Example: civilian peacekeeper" );
        this.addQuestion(q17);
        QuestionFaci q18 = new QuestionFaci( "CREATE A TITLE: Go Go \n\n Example: Changin" );
        this.addQuestion(q18);
        QuestionFaci q19 = new QuestionFaci( "I WILL: I will distribute screwdrivers \n\n Example: to all drivers" );
        this.addQuestion(q19);
        QuestionFaci q20 = new QuestionFaci( "CREATE A DISH: Chicken --- Soup \n\n Example: Dumpling" );
        this.addQuestion(q20);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
        String upgradeQuery = "ALTER TABLE quest ADD COLUMN question TEXT";
        if(newV>oldV)
            db.execSQL( upgradeQuery );
        onCreate( db );


    }

    private void addQuestion(QuestionFaci quest) {
        ContentValues values = new ContentValues(  );
        values.put(KEY_QUEST, quest.getQUESTION());
        dbase.insert( TABLE_QUEST, null, values );
    }


    public List<QuestionFaci> getAllQuestions(){
        List<QuestionFaci> quesList = new ArrayList<QuestionFaci>(  );
        String selectQuery = "SELECT * FROM " + TABLE_QUEST;
        dbase = this.getReadableDatabase();
        Cursor cursor = dbase.rawQuery( selectQuery, null );
        if (cursor.moveToFirst()){
            do{
                QuestionFaci quest = new QuestionFaci(  );
                quest.setID( cursor.getInt( 0 ) );
                quest.setQUESTION( cursor.getString( 1 ) );

                quesList.add(quest);
                } while (cursor.moveToNext());
         }

        return quesList;

    }
}

Answer 1

If your saved data is simple, as mentioned, you can wipe the Db and then re-create it. If you have other data in there, perhaps user results, user progress or anything else, you'll need to handle the update properly in the onUpgrade.

What I usually do is have an onUpgrade that will read migration files from the android resources. These files will have DB schema changes (if necessary) and any update statements needed to migrate the database between versions. I would rather not have the case statement below and look for migrations dynamically, but it's somewhat of a necessary evil allowing me to tailor and tweak each migration if necessary.

@Override
   public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion)
   {
      AppLog.i(TAG, "onUpgrade, oldVersion=["+oldVersion+"], newVersion=["+newVersion+"]");
      try 
      {
          // Simply loop round until newest version has been reached and add the appropriate migration
          while (++oldVersion <= newVersion) 
          {
              switch (oldVersion) 
              {
              // The version in the case statement is the new (target) version. So case 2: will be an upgrade from 2 to 3.  
                  case 2: {
                      UpgradeUtils.addUpgrade(oldVersion);
                      break;
                  }
                  case 3: {
                      UpgradeUtils.addUpgrade(oldVersion);

                      break;
                  }
                  case 7: {
                     UpgradeUtils.addUpgrade(oldVersion);
                  }

                  case 8: {
                     UpgradeUtils.addUpgrade(oldVersion);
                  }
              }
          }
          // Get all the available updates
          final List<String> availableUpdates = UpgradeUtils.availableUpdates(DatabaseHelper.context.getResources());
          AppLog.d(TAG, "Found a total of " + availableUpdates.size() +" update statements" );

          for (final String statement : availableUpdates) 
          {
              db.beginTransaction();
              try {
                  AppLog.d(TAG, "Executing statement: " + statement);
                  db.execSQL(statement);
                  db.setTransactionSuccessful();
              }
              finally {
                  db.endTransaction();
              }
          }
      }
      catch (Exception e)
      {
         Log.e(TAG,"Error executing sql while upgrading database", e);
      }

   }

UpgradeUtils.java

public class UpgradeUtils
{
   private static final String TAG = "UpgradeHelper"; 

   protected static final Set<Integer> mVersionSet;
   static 
   {
      mVersionSet = new LinkedHashSet<Integer>();
   }

   public static final void addUpgrade(final int version) 
   {
      AppLog.d(TAG, "Adding " + version + " to upgrade path");
      mVersionSet.add(version);
  }

   public static List<String> availableUpdates(final Resources resources) 
   {
      final List<String> updates = new ArrayList<String>();

      for (Integer version : mVersionSet) 
      {
              // Migration files are kept in assets/updates/migration-X.sql
          String fileName = String.format("updates/migration-%s.sql", version);

          AppLog.d(TAG, "Adding db version [" + version + "] to update list, loading file ["+ fileName +"]" );

          final String sqlStatements = DBUtils.loadAssetFile(resources, fileName);

          final String[] splitSql = sqlStatements.split("\\r?\\n");
          for (final String sql : splitSql) 
          {
              if (DBUtils.isNotComment(sql)) 
              {
                  updates.add(sql);
              }
          }
      }
      return updates;
  }

You should also consider loading your questions from a resource file as well.

Answer 2

You should increase your DATABASE_VERSION variable to for example 2. This will call onUpgrade method.

It's not the best approach but you can just drop table if version of your current database is higher and then call onCreate().

  @Override
    public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
        String upgradeQuery = "DROP TABLE IF EXISTS quest";
        db.execSQL( upgradeQuery );
        onCreate( db );    
    }