Android SQLite Database, Why Drop Table and Recreate on Upgrade

Android SQLite Database, WHY drop table and recreate on upgrade

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.

Dropping SQLite database on upgrading application

For throw-away databases (where the data is e.g. a cached copy of data available in the cloud) I usually make onUpgrade() just call onCreate() and make onCreate() execute DROP TABLE IF EXISTS <tablename> before creating the tables.

For example:

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

@Override
public void onCreate(SQLiteDatabase db) {
  db.execSQL("DROP TABLE IF EXISTS foo");
  db.execSQL("CREATE TABLE foo(bar INTEGER, baz TEXT");
}

Why does SQLiteOpenHelper drop the table in onUpgrade method?

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.

Android database recreates every time application is launched

Look at your DBWrapper constructor,

you're calling

context.deleteDatabase(DATABASE_NAME);

This will delete the database file every time you call it. Forcing the SQLHelper to recreate the database.

How to recreate a database on an Android Application Update?

I like to use the built-in SQLiteOpenHelper class to maintain the Sqlite version numbers and providing the process for upgrading the database.

Using the builtin Android.Database.Sqlite.SQLiteOpenHelper you can easily use it to maintain your database version and if the version changes, delete the database and start fresh with a blank one (no tables/no data).

Remember: This is destructive "upgrade"...

public class SqliteOnVersionChangeCreateBlankDB : SQLiteOpenHelper
{
    new const string DatabaseName = "myDBName";
    const int DatabaseVersion = 1;

    public SqliteOnVersionChangeCreateBlankDB(Context context)  : base(context, DatabaseName, null, DatabaseVersion)
    {
    }

    public override SQLiteDatabase ReadableDatabase
    {
        get
        {
            try
            {
                return base.ReadableDatabase;
            }
            catch
            {
                File.Delete(DatabaseName);
                return base.WritableDatabase;
            }
        }
    }

    public override void OnCreate(SQLiteDatabase db)
    {
        Console.WriteLine($"{db}");
        // You can create the DB tables/data here if needed...
        // or use your favorite SQLite framework/library later...
    }

    public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        Console.WriteLine($"{db}:{oldVersion}:{newVersion}");
        if (oldVersion < newVersion)
        {
            // Normally this is where you would alter the existing schema to the new version
            // but this is a destructive upgrade.
            db.Close();
            File.Delete(db.Path);
        }
    }
}

Usage:

Starting with a version of 1 in the SQLiteOpenHelper subclass:

const int DatabaseVersion = 1;

Execute this code:

var sqliteHelper = new SqliteOnVersionChangeCreateBlankDB(this);
var db = sqliteHelper.ReadableDatabase;
db.Close();

You now have a blank database that is assigned version 1. It is empty, no tables/data so use your favorite Sqlite ORM/framework/library to create the tables and populate it with data...

Unless the database version changes, executing this code each time your app starts will not delete the existing database:

var sqliteHelper = new SqliteOnVersionChangeCreateBlankDB(this);
var db = sqliteHelper.ReadableDatabase;
db.Close();

Later on, App is updated and you also need to change the database, so assign a new version number that is higher the last, 2 in this case, to the database.

const int DatabaseVersion = 2;

Execute the same code as before:

var sqliteHelper = new SqliteOnVersionChangeCreateBlankDB(this);
var db = sqliteHelper.ReadableDatabase;
db.Close();

You now have a blank database again, but it is assigned version 2. Again your favorite Sqlite ORM/framework/library to create the tables and populate it with data...

---

Related Topics