Android : Change SQLite DB on upgrade

Question

I want to add a column to my existing database

However I'm imagining I'm in the scenario where people have already got a version of the code and database and I'm going to make the changes through a update on google play, therefore previous data cannot be lost

To create my database I used the following code;

protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_calculation);
  db = openOrCreateDatabase("EJuiceData", Context.MODE_PRIVATE, null);
  db.execSQL("create table if not exists Inventory(_id integer primary key autoincrement, NAME TEXT NOT NULL, AMOUNT TEXT, PRICE TEXT, AMOUNTLEFT TEXT, WEIGHT TEXT);");
....

I've looked around online and people are mentioning using the following command to update;

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (newVersion > oldVersion) {
    db.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
  }
}

However onUpgrade is commented out stating the it isn't being called, also no where in my code have I stated what version of the database it is or given a new version

Anyone know how I get around this issue?

Thanks

EDIT;

From looking at the answers I've decided to try do this the most efficient way and what's seen as the best practice

So I've created a new Java Class called MyDBHelper which has the following

package com.sjhdevelopment.shaunharrison.myejuiceapp;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper {

  public static final String DATABASE_NAME = "EJuiceData.db";
  public static final int DATABASE_VERSION = 1;



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

  }

  public void onCreate(SQLiteDatabase database) {
    database.execSQL("create table if not exists Recipe");
    .....

  }

  @Override
  public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
    if (newVersion > oldVersion)
        database.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
    onCreate(database);
  }

}

So far I have an error on MovieDatabaseHelper stating 'invalid method declared; return type required

Can you post the logs ? Which line is the error ? You also forgot the @Override before onCreate — ThomasThiebaud
– ThomasThiebaud, Commented Oct 12, 2015 at 10:43
There is no log as I haven't ran the code yet, there is a red line underneath public MovieDatabaseHelper(Context context) — Sjharrison
– Sjharrison, Commented Oct 12, 2015 at 10:45
replace MovieDatabaseHelper by the name of your class (MyDBHelper). This is the constructor ! — ThomasThiebaud
– ThomasThiebaud, Commented Oct 12, 2015 at 10:46
That did work, I blame this on it being monday morning! thanks for that — Sjharrison
– Sjharrison, Commented Oct 12, 2015 at 10:48
So on a different form now i've put in the following MyDBHelper helper = new MyDBHelper(getContext()); however getContext is causing an error 'can't resolve method 'getContext()'' — Sjharrison
– Sjharrison, Commented Oct 12, 2015 at 10:52

ThomasThiebaud · Accepted Answer · 2015-10-12 10:45:52Z

4

Are you using SQLiteOpenHelper ? If yes you must have a version you send to the super class from the constructor (see documentation)

Here is a code sample. The onUpgrade function will be called when the DATABASE_VERSION changes.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "EJuiceData.db";

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

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL("create table if not exists Inventory(_id integer primary key autoincrement, NAME TEXT NOT NULL, AMOUNT TEXT, PRICE TEXT, AMOUNTLEFT TEXT, WEIGHT TEXT);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
            onCreate(database);
        }
    }
}

EDIT

now you can get access your database using

DatabaseHelper helper = new DatabaseHelper(getContext());
helper.getReadableDatabase().query(...);
//or
helper.getWritableDatabase().insert(...);

edited Oct 12, 2015 at 10:45

answered Oct 12, 2015 at 9:58

ThomasThiebaud

12.1k6 gold badges57 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sjharrison Over a year ago

I'm not using a SQLiteOpenHelper, the create command is in the OnCreate of the first form that opens

ThomasThiebaud Over a year ago

You can create a private attribute called verion and manually upgrade your database if your version change. But this is better practice (and easier) to use the SQLiteOpenHelper. (See edit)

Sjharrison Over a year ago

Hi @Maloubobola, I'm trying to use your answer to get this working I've copied your code into a new Java class however MovieDatabaseHelper is throwing an error stating that a return type is required

ThomasThiebaud Over a year ago

where is the error thrown ? Can you provide more details ?

Ziem · Accepted Answer · 2015-10-13 10:41:36Z

2

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion)
        db.execSQL("DROP TABLE IF EXISTS '" + TABLE_NAME + "'");
        onCreate(db);
    }
}

edited Oct 13, 2015 at 10:41

Ziem

6,7078 gold badges57 silver badges86 bronze badges

answered Oct 12, 2015 at 10:44

alican akyol

2883 silver badges14 bronze badges

2 Comments

Sjharrison Over a year ago

Unfortunately this doesn't answer my question, I have seen plenty of methods like this, which is why I've posted a question on here

alican akyol Over a year ago

You can add @Override above of oncreate()

firemaples · Accepted Answer · 2015-10-12 10:04:38Z

1

You can extend SQLiteOpenHelper like below code, and new your helper class with version param which db version you want to open.

If you open db with version 1, and next time you open db with version 2, the onUpgrade method will be called with param ordVersion = 1 and newVersion = 2, then you can do something to upgrade your db schema here.

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "mydata.db";
    public static final int VERSION = 1;    
    private static SQLiteDatabase database;

    public MyDBHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
    }

    public static SQLiteDatabase getDatabase(Context context) {
        if (database == null || !database.isOpen()) {
            database = new MyDBHelper(context, DATABASE_NAME, 
                    null, VERSION).getWritableDatabase();
        }

        return database;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onCreate(db);
    }

}

answered Oct 12, 2015 at 10:04

firemaples

1,54113 silver badges19 bronze badges

3 Comments

Sjharrison Over a year ago

Hi thanks for your answer, so would it be best for me to create a new Java class and use the code above in there? If so on my previous class I've been using db = openOrCreateDatabase("EJuiceData", Context.MODE_PRIVATE, null); to connect to the database, will I still be able to use that? thanks

firemaples Over a year ago

You can change the code to db = new MyDBHelper(ActivityContext, "EJuiceData", null, DBVersionCode).getWritableDatabase();

firemaples Over a year ago

or MyDBHelper.getDatabase(ActivityContext) and change "DATABASE_NAME" "VERSION" in MyDBHelper class

Collectives™ on Stack Overflow

Android : Change SQLite DB on upgrade

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related