Why does SQLiteOpenHelper drop the table in "onUpgrade" method?

Question

I learned a way which use SQLite3 on Android OS recently. But it has a non acceptable part.

I can't understand why drop the table when called "onUpgrade" method of SQLiteOpenHelper. Why need "onUpgarde" method?

If code executes "drop table", table data of old version DB will be removed, isn't it?

Why delete existing data of old DB?

How to restore existing DB data when drop the table?

[Here is learned code]

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    final String CONFIRMED_SHEETS_TABLE = "confirmed_sheets";

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

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql = "create table" +
                CONFIRMED_SHEETS_TABLE +
                "_id integer primary key autoincrement, " +
                "group text, " +
                "num001 text, " +
                "num002 text, " +
                "num003 text, " +
                "num004 text, " +
                "num005 text, " +
                "num006 text, " +
                "date text)";
        sqLiteDatabase.execSQL(sql);
    }

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

        // non acceptable part //
        String sql = "drop table if exists " + CONFIRMED_SHEETS_TABLE;
        sqLiteDatabase.execSQL(sql);

        onCreate(sqLiteDatabase);
    }
}

The `drop table` command is called by *your* code, not the system. You are free to take it out and do migration logic instead. — A--C, Nov 29 '13 at 02:31

score 5 · Accepted Answer · edited May 23 '17 at 12:03

If code executes "drop table", DB table data of old version DB will be removed, isn't it?

Yup

Why need "onUpgrade" method?

If you are switching databases (for example because you added a new column), your app (usually) now depends on that change. Increasing the database version in your Helper class calls onUpgrade(), which allows you to take care of any migration to prepare the app to use your new schema.

Did you know why learned code executes "drop table"?

Convenience. It's not necessarily the right approach, but a database change can make it hard to take old data and merge it in the new table. Thus, it is easier logic-wise to simply start anew.

If you want to merge an existing and new table, have a look at this question.

Yes. When you create a new `SQLiteHelper` object, the database version is checked against and if needed, `onUpgrade()` is called. — A--C, Nov 29 '13 at 02:48

score 2 · Answer 2 · answered Nov 29 '13 at 02:34

You do not need to perform a DROP TABLE in onUpgrade(), as it is currently written in your code. The purpose of onUpgrade() is for your app to check if a new version of your app's database is being installed on a user's device, and if so, if there are any changes to your database, such as adding a new column, you can make those changes within onUpgrade(). If you never change your database schema, you never need to do anything in onUpgrade().

For more information and a introductory tutorial on Databases in Android, refer to the Notepad sample code here.

EDIT: also, here's an example of an onUpgrade() I wrote for one of my apps:

  /**
    * Handle upgrades to the database.
    */
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       if(DBG) Utils.log(TAG, "DBHelper: onUpgrade()...");

       // Logs that the database is being upgraded
       Utils.log(TAG, "w", "Upgrading database from version " + oldVersion + " to "
               + newVersion);

       /*
        * Every time you add new columns to the database in the phone, you will want
        * to increment the Database version above and then add a condition in here for
        * upgrading to it. Otherwise it will cause upgrading users to be nontrivial and
        * lead to unnecessary crashes or upgrade instructions.
        */
       if (newVersion > oldVersion && newVersion <= 4) {
           db.execSQL("ALTER TABLE " + TrackerDb.SomeTable.TABLE_NAME + " ADD COLUMN "
                   + TrackerDb.SomeTable.COLUMN_NAME_DATE_MODIFIED + " TEXT");
       }
   }

Thanks for your detail explaination. :D – S.J. Lim Nov 29 '13 at 03:04 — S.J. Lim, Nov 29 '13 at 03:04

Yaqub Ahmad · Answer 3 · 2013-11-29T05:29:34.000

1

OnUpgrade() is called when db versions "old and new" does not match; which means that the user wants to change Database Structure (Dropping a table, Adding new Table, Modifying Table definition).

So the OnUpgrade() should contain the logic as:

Take Backup of the existing data (e.g. Generate insert statements for existing data).
Modify Database Structure accordingly.
Restore the data from the backup.

edited Nov 29 '13 at 05:29

answered Nov 29 '13 at 02:33

Yaqub Ahmad

27,569
23
102
149

Why does SQLiteOpenHelper drop the table in "onUpgrade" method?

3 Answers3