Upgrade SQLite Database from One Version to Another

Upgrade SQLite database from one version to another?

Ok, before you run into bigger problems you should know that SQLite is limited on the ALTER TABLE command, it allows add and rename only no remove/drop which is done with recreation of the table.

You should always have the new table creation query at hand, and use that for upgrade and transfer any existing data. Note: that the onUpgrade methods runs one for your sqlite helper object and you need to handle all the tables in it.

So what is recommended onUpgrade:

• beginTransaction
• run a table creation with if not exists (we are doing an upgrade, so the table might not exists yet, it will fail alter and drop)
• put in a list the existing columns List<String> columns = DBUtils.GetColumns(db, TableName);
• backup table (ALTER table " + TableName + " RENAME TO 'temp_" + TableName)
• create new table (the newest table creation schema)
• get the intersection with the new columns, this time columns taken from the upgraded table (columns.retainAll(DBUtils.GetColumns(db, TableName));)
• restore data (String cols = StringUtils.join(columns, ",");
db.execSQL(String.format(
"INSERT INTO %s (%s) SELECT %s from temp_%s",
TableName, cols, cols, TableName));
)
• remove backup table (DROP table 'temp_" + TableName)
• setTransactionSuccessful

.

public static List<String> GetColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("select * from " + tableName + " limit 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

public static String join(List<String> list, String delim) {
    StringBuilder buf = new StringBuilder();
    int num = list.size();
    for (int i = 0; i < num; i++) {
        if (i != 0)
            buf.append(delim);
        buf.append((String) list.get(i));
    }
    return buf.toString();
}

How Upgrade SQLite Data without losing old version data in Android?

My question is this how do I migrate database without losing data
which already exists.

It's possible. You should read onUpgrade at first.

public abstract void onUpgrade (SQLiteDatabase db,
int oldVersion,
int newVersion)

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

EXAMPLE

If you add new column then,

@Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {      
          Log.e("VERSION", "Updating table from " + oldVersion + " to " + newVersion);
           if (oldVersion < Your_New_version){
            db.execSQL("ALTER TABLE " + TABLE_NAME  + " ADD COLUMN new_column_name TEXT;");
        }

    }

upgrade sqlite database in my app

You can use onUpgrade() method for handling this.

Something like this:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
     if (oldVersion == 1 && newVersion == 2) {
      db.execSQL("create temporary table people_tmp ("
          + "id integer, name text, position text, posid integer);");

      db.execSQL("insert into people_tmp select id, name, position, posid from people;");
      db.execSQL("drop table people;");

      db.execSQL("create table people ("
          + "id integer primary key autoincrement,"
          + "name text, posid integer);");

      db.execSQL("insert into people select id, name, posid from people_tmp;");
      db.execSQL("drop table people_tmp;");
    }

}

So. You are creating temporary table and saving all needed info inside that table. Next you dropping your table, creating new one and inserting values to it from your temporary table. You can add additional fields and feel free to put there all what you want.

UPDATE:
After a little googling i found an easier solution:

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

 // If you need to add a column
 if (newVersion == 2) {
     db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
 }
}

Alter table method will change your database structure without loosing data.

Android SQLite: Replace old database with a new one or use migration scripts

You dont need to do that (renaming stuff or anything)

You just need to change your database version and write a sql command to alter your previous table to migrate from version A to B.

Look at this link:

Android: upgrading DB version and adding new table

Android : Change SQLite DB on upgrade

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(...);

---

Related Topics