title: Using onUpgrade() to create new database table SQLite Android
body: <p>I'm new to SQLite databases in Android so I need a little help. The app I'm trying to make will store the users workout exercises and reps for each exercise. I have a database set up and working that will store one exercise and it's reps but I can't get the onUpgrade() method to work so that I can create a larger database with the rest of the exercises. </p>
  
  <p>The original code made a database with the following columns </p>
  
  <pre><code>public void onCreate(SQLiteDatabase db) {
          // TODO Auto-generated method stub
          db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + 
                  KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                  KEY_EXERCISE + " TEXT NOT NULL, " +
                  KEY_EX1REPS1 + " TEXT NOT NULL," +
                  KEY_EX1REPS2 + " TEXT NOT NULL," +
                  KEY_EX1REPS3 +  " TEXT NOT NULL);"
  
                  );
  
      }
  </code></pre>
  
  <p>But I want to add the following for example</p>
  
  <pre><code>                KEY_EXERCISE2 + " TEXT NOT NULL, " +
                  KEY_EX2REPS1 + " TEXT NOT NULL," +
                  KEY_EX2REPS2 + " TEXT NOT NULL," +
                  KEY_EX2REPS3 + " TEXT NOT NULL);
  </code></pre>
  
  <p>I've tried doing this using the below onUpgrade method but it won't work</p>
  
  <pre><code>        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
          // TODO Auto-generated method stub
          db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE); 
          onCreate(db);
  
      }
  </code></pre>
  
  <p>If someone could tell me how to add the addition columns it would be greatly appreciated. The following is the complete database.java file.</p>
  
  <pre><code>public class Database {
  
  public static final String KEY_ROWID = "_id";
  
  public static final String KEY_EXERCISE = "exercise_name";
  public static final String KEY_EX1REPS1 = "exercise_rep1";
  public static final String KEY_EX1REPS2 = "exercise_rep2";
  public static final String KEY_EX1REPS3 = "exercise_rep3";
  
  
  public static final String KEY_EXERCISE2 = "exercise2_name";
  public static final String KEY_EX2REPS1 = "exercise2_rep1";
  public static final String KEY_EX2REPS2 = "exercise2_rep2";
  public static final String KEY_EX2REPS3 = "exercise2_rep3";
  
  
  private DbHelper ourHelper; //just a insince of the DbHelper class
  private final Context ourContext;
  public static SQLiteDatabase ourDatabase;
  
  public static final int COL_ROWID = 0;
  
  private static final String DATABASE_NAME = "Workouts";
  static final String DATABASE_TABLE = "exercisesandreps";
  private static final int DATABASE_VERSION = 2;
  
  public static final String[] ALL_KEYS = new String[] {KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
  
  
  private static class DbHelper extends SQLiteOpenHelper{
  
      public DbHelper(Context context) {
          super(context, DATABASE_NAME, null, DATABASE_VERSION);
          //context, name, factory, version
          // TODO Auto-generated constructor stub
      }
  
      @Override
      public void onCreate(SQLiteDatabase db) {
          // TODO Auto-generated method stub
          db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + 
                  KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + //each one of these lines defines a column of the database
                  KEY_EXERCISE + " TEXT NOT NULL, " +
                  KEY_EX1REPS1 + " TEXT NOT NULL," +
                  KEY_EX1REPS2 + " TEXT NOT NULL," +
                  KEY_EX1REPS3 + " TEXT NOT NULL," +
                  KEY_EXERCISE2 + " TEXT NOT NULL, " +
                  KEY_EX2REPS1 + " TEXT NOT NULL," +
                  KEY_EX2REPS2 + " TEXT NOT NULL," +
                  KEY_EX2REPS3 + " TEXT NOT NULL);"
  
                  );
  
      }
  
      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
          // TODO Auto-generated method stub
          db.execSQL("DROP TABLE IF EXISTS" + DATABASE_TABLE); 
          onCreate(db);
  
      }   
  }
  public Database(Context c){
      ourContext = c;
  }
  
  public Database open() throws SQLException{
      ourHelper = new DbHelper(ourContext);
      ourDatabase = ourHelper.getWritableDatabase(); //use writable because if you can write to it, you can read it
      return this;
  }
  
  public void close(){
      ourHelper.close();
  }
  
  public long createEntry(String Exercise, String Ex1R1, String Ex1R2, String Ex1R3, String Exercise2, String Ex2R1, String Ex2R2, String Ex2R3) {
      // TODO Auto-generated method stub
      ContentValues cv = new ContentValues();
  
      //String Exercise1 = convertArrayToString(ExerciseArray);
  
      cv.put(KEY_EXERCISE, Exercise);
      cv.put(KEY_EX1REPS1, Ex1R1);
      cv.put(KEY_EX1REPS2, Ex1R2);
      cv.put(KEY_EX1REPS3, Ex1R3);
  
  
      cv.put(KEY_EXERCISE2, Exercise2);
      cv.put(KEY_EX2REPS1, Ex2R1);
      cv.put(KEY_EX2REPS2, Ex2R2);
      cv.put(KEY_EX2REPS3, Ex2R3);
  
      return ourDatabase.insert(DATABASE_TABLE, null, cv); //this will crash if you haven't set up a table with rows + columns
  
  }
  
  public String getData() {
      // TODO Auto-generated method stub
      String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
      //you read information through a cursor
      Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
      String result = "";
  
      //these keep track of the current row, name, hotness
      int iRow = c.getColumnIndex(KEY_ROWID);//position 0
      int iExercise1 = c.getColumnIndex(KEY_EXERCISE);//position 1
      int iEx1R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
      int iEx1R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
      int iEx1R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4
  
      int iExercise2 = c.getColumnIndex(KEY_EXERCISE);//position 1
      int iEx2R1 = c.getColumnIndex(KEY_EX1REPS1);//position 2
      int iEx2R2 = c.getColumnIndex(KEY_EX1REPS2);//position 3
      int iEx2R3 = c.getColumnIndex(KEY_EX1REPS3);//position 4
  
      for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){  //!c.isAfterLast = if our cursor is not after the last entry in our database 
          //starting at the first position, it will move one each go unless it hasn't exceeded the last entry
          result = result + c.getString(iRow) + " " + c.getString(iExercise1) + " " + c.getString(iEx1R1) + " " + c.getString(iEx1R2) + " " + c.getString(iEx1R3) + 
                                                      c.getString(iExercise2) + " " + c.getString(iEx2R1) + " " + c.getString(iEx2R2) + " " + c.getString(iEx2R3) + 
  
                  "\n";
  
      }
      return result;
  }
  
  public String getExercise(long l) throws SQLException{
      // TODO Auto-generated method stub
      String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3, KEY_EXERCISE2, KEY_EX2REPS1, KEY_EX2REPS2, KEY_EX2REPS3};
      Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);
  
      if(c != null){
          c.moveToFirst();
          String exercise = c.getString(1);//we know the name column is the second column in the database so it will have a position 1
          return exercise;//the code will only return the name if the cursor is set to null
      }
  
      return null;
  }
  
  
  
  public String[] getReps1(long l) throws SQLException{
      // TODO Auto-generated method stub
      String[] columns = new String[]{KEY_ROWID, KEY_EXERCISE, KEY_EX1REPS1, KEY_EX1REPS2, KEY_EX1REPS3};
      Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l,null, null, null, null);
  
      if(c != null){
          c.moveToFirst();
          String reps1 = c.getString(2);//we know the name column is the second column in the database so it will have a position 1
          String reps2 = c.getString(3);
          String reps3 = c.getString(4);
          String[] result = new String[]{reps1, reps2, reps3};
          return result;//the code will only return the name if the cursor is set to null
      }
      return null;
  }
  
  
  public void updateEntry (long lRow, String mExercise, String mEx1Reps1, String mEx1Reps2, String mEx1Reps3) throws SQLException{
      // TODO Auto-generated method stub
      ContentValues cvUpdate = new ContentValues();
      cvUpdate.put(KEY_EXERCISE, mExercise);
      cvUpdate.put(KEY_EX1REPS1, mEx1Reps1);
      cvUpdate.put(KEY_EX1REPS2, mEx1Reps2);
      cvUpdate.put(KEY_EX1REPS3, mEx1Reps3);
  
      ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
  }
  
  public void deleteEntry(long lRow1) throws SQLException{
      // TODO Auto-generated method stub
      ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
  
  }
  
  
  public static String convertArrayToString(String[] array){
      String str = "";
      for (int i = 0;i&lt;array.length; i++) {
          str = str+array[i];
          // Do not append comma at the end of last element
          if(i&lt;array.length-1){
              str = str+",";
          }
      }
      return str;
  }
  public static String[] convertStringToArray(String str){
      String[] arr = str.split(",");
      return arr;
  }
  
  public void deleteAll() {
      Cursor c = getAllRows();
      long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
      if (c.moveToFirst()) {
          do {
              deleteRow(c.getLong((int) rowId));              
          } while (c.moveToNext());
      }
      c.close();
  }
  
  public Cursor getAllRows() {
      String where = null;
      Cursor c =  ourDatabase.query(true, DATABASE_TABLE, ALL_KEYS, 
                          where, null, null, null, null, null);
      if (c != null) {
          c.moveToFirst();
      }
      return c;
  }
  public boolean deleteRow(long rowId) {
      String where = KEY_ROWID + "=" + rowId;
      return ourDatabase.delete(DATABASE_TABLE, where, null) != 0;
  }
  }
  </code></pre>