Andrew Jake Villegas Andrew Jake Villegas - 3 months ago 11
Android Question

Upgrading my SQLite db

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

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 );    
    }