CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Ansh
Ansh

Reputation: 2396

Upgrading Sqlite database in android?

I have an android application which is using Sqlite as database.It has following tables:

  1. Hotels
  2. Locations
  3. Favorites

I am keeping my raw database file in assests folder and when user installs my app i just copies this database to /data/data/package_name/databases directory.Initially Favorites table is empty and it gets populated after user start liking hotels.My problem is that I want to launch my updated version of app with some bug fixes and some new hotels added to the database, so I need to update database of existing users with new hotels and locations without affecting the favorites table.Now if I keep my old approach and update the Database Version Number then application will remove the old database and use the new database but all data in favorites table will be lost.I don't want it to happen.Now problem is how do I update Hotels and Locations table without loosing data in Favorites table.

Upvotes: 4

Views: 2287

Answers (3)

Kyle H
Kyle H

Reputation: 3303

I know this question was asked long ago, but I had a similar issue and wanted to share my solution, seems to do the trick for me. I'm a novice so feel free to give input-

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //code to keep table data
        List<obj> objList = new ArrayList<obj>();

    String selectQuery = "SELECT score,list_name,quiz_length FROM obj_table";

    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            obj o = new obj();
            o.final_score = cursor.getInt(0);
            o.quiz_name = cursor.getString(1);
            o.quiz_length = cursor.getInt(2);
            objList.add(o);
        } while (cursor.moveToNext());
    }

    //done storing data, now upgrade DB from asset file
    try {
                 //my db file is upgraded here
        copyDataBase(); 
    } catch (IOException e) {

    }

    //now insert our saved table data
    for (Score obj_rec: objList){

          ContentValues values = new ContentValues();
          values.put("score", obj_rec.final_score);
          values.put("list_name", obj_rec.quiz_name);
          values.put("quiz_length", obj_rec.quiz_length);

          db.insert("obj_table", null, values);
    }
}

Upvotes: 2

Opiatefuchs
Opiatefuchs

Reputation: 9870

Usualy upgrade a database has to be done with SQLiteOpenHelper class. I would advise You to do some tests at Your own device before publish it. You have to increment Your Database Version and call "ALTER TABLE" method from sqlite. This has been discussed in many threads here, the clearest one I think is this one:

Difficulty in upgrading SQlite table

and here is even a article with some solution:

http://joshhendo.blogspot.de/2012/03/android-sqlite-database-upgrade.html

However, a safe way would be to save the old database in a tempfolder, that the user can get back the old one if anything is running into chaos.

Upvotes: 0

7bluephoenix
7bluephoenix

Reputation: 966

  1. Before updating write the contents of you previous table to a file and save it on the sdcard.

  2. Then you may update your database with new version.

  3. And after doing that copy back the data from the backup file(from sdcard) to the updated database. After the successful copying of the backup, delete the file from the sdcard.

Upvotes: 1

Related Questions