Android SQLite Database, WHY drop table and recreate on upgrade

Question

In the tutorials I am following and a lot of more places I see this, onUpgrade -> drop table if exists, then recreate table.

What is the purpose of this?

private static class DbHelper extends SQLiteOpenHelper{

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);"
        );  
    }

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

Solution 1

I agree when you upgrade you should be adding columns or adding tables to your database. Most of the onupgrade samples actually suck because why am I deleting all this data then recreating the table? I found this blog entry I call it the Adams Incremental Update Method. It also handles situations where users may have not upgraded your app with each release.

Here is a good blog on sqlite onupgrade that doesn't do drop table.

Solution 2

Well, the most common method in android applications is to "relog" the user when a database upgrade is in order. And considering any local database should only be mirroring what is on the serverside application, it is much easier to just drop the database, recreate it and repopulate from the server than it is to carefully plan migrations from one version to the other.

It certainly isn't the best approach, but it is easier.

To make an example of how it would be implementing a migration (a change from an older version of a database to a newer one)

Lets say in your DbHelper class you define that your database is version 1, in a later version of your application (version 2), you need a few more columns in one of your tables.

So you would need to upgrade your table and add the columns via ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Check this link for that -> Insert new column into table in sqlite ?

so your onUpgrade() method would have to reflect that change by adding:

 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion){
        case 1:
            db.execSQL("ALTER TABLE " + DATABASE_TABLE + " ADD COLUMN " + NEW_COLUMN_NAME + TYPE);
    }
}  

Solution 3

Depends on the kind of approach you want to create and how important your data, what complexity of table it is. Example: If your app has been upgraded quite a lot of times and table structure have change enough times, then its better to drop table and re-create it, rather than writing code for changing structure for every version of db, in this approach you will have to make sure that you can backup any data that you have on server side, so that things remain ok.

From my exp: recreating is better if you can judge that there might further be changes in future, else it gets quite complicated

Solution 4

It's a clean start. Method onCreate has the currently valid database structure. Users that install the app will execute this method. For the users that are upgrading, method onUpgrade will be executed, and DROP TABLE IF EXIST is a clean start - just in case the structure of the old and the new table is different - drop it and then recreate it in onCreate method. Hope this helps! :)

View more solutions

Comments

[Esqarrouth]

i am not using server on my application and the tutorials did not have anything about the servers too. what do you mean about migrations from versions?

[Esqarrouth]

thanks, this process seems like it will delete the the previously stored values in the database, does that happen? is there a chance for the user to lose their data?

[Aedis]

i'll edit my post to answer your question- it's too long for the comment :3

[MSquare]

If DROP is used, yes the data is gone. If the table structure is not changing, in onCreate method the table can be created as CREATE TABLE IF NOT EXISTS, without dropping it in onUpgrade method.

[Esqarrouth]

thanks, so in case i decided to use the classic drop, i would have to write that line of code for each table right?

[Aedis]

right, you should have to check if the table exists and then drop it

[G_V]

Is there a particular reason for the loop in the link? What if you just leave out the break statements and let it fall through? It would always start with your earliest old version that matches, then upgrades incrementally from that version to your latest, unless I'm missing something here.

[danny117]

@G_V Absolutely it would be faster to let it drop through. The breaks and loop are there to assist with implementing a percent complete notification.

[G_V]

Oh I see, yeah that could definitely be handy for large databases. The ones I've used so far were quite tiny so there was no point to showing progress but I'll definitely keep it in mind for future applications.

[stdout]

This gives "no such table: xx" error all the time when I upgrade the db version. What might be the reason you think?

[Benny]

You need to add space + " " + between NEW_COLUMN_NAME to TYPE

[Suragch]

Seems like it would be better to copy the data to a temporary table rather than a cvs file. I would just use cvs files for exporting data.

[Luten]

+1 for the an easier approach. Also it can be the right one (aka the best) - considering the expences on migration planning, implementing, support and risks of something going wrong.