Drop table if it already exists and then re-create it?

Question

I was just learning about sqlite in android programming from the New Boston Travis videos and I have come across something strange:

It is said that if the database does not already exists, onCreate() method is called to create it. so far, so good. But, if the database already exists, then onUpgrade() method is called which simply drops the database and then re-creates it.

What is the sense behind such an update? why not just leave the database alone?

Answer 1

I can't add comments to expand on k3b's answer... so I have to add a new answer.

YOU should decide how to handle the onUpgrade process yourself.

Notice the arguments in onUpgrade:

public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {...}

You are provided with the "oldVersion" and the "newVersion". oldVersion is the version of the DB schema that is on the device at the time this method is called. newVersion is the version of the DB schema that is in your application being run.

Potential solutions:

Solution #1 - Brute Force

If you don't care a lick about the data currently on the device:

public void onUpgrade(...)
{
  dropDatabase();    // whatever it takes
  createDatabase();
}

Solution #2 - Save some data you care about

Maybe you only care about some list of favorites.

public void onUpgrade(...)
{
  var currentFavorites = saveFavoritesTo();

  dropDatabase();    // whatever it takes
  createDatabase();

  loadFavorites(currentFavorites);
}

Solution #3 - Maintain a list of migrations

When schema changes get complex AND you need to keep data in tact, you will need to write the sql statements to alter tables and migrate data. You might consider using the versions to figure out which migrations to run.

Somewhere you need to decide which migrations to run. That's up to you. But that's why you're provided with the old and new versions.

public void onUpgrade(...)
{
  var migrations = getMigrationsToRun(oldVersion, newVersion);
  foreach (migration in migrations)
  {
    runMigrationOnDb(db, migration)
  }
}

You asked in your comment about keeping the schema but dropping the data. In that case, you'd want to execute a "truncate table" sql statement on the tables you want to clean the data from. In sqlite this would probably be: "DELETE FROM table_name;"

http://www.sqlite.org/lang_delete.html

Answer 2

  > if the database already exists, then onUpgrade() method is called 
  > which simply drops the database and then re-creates it.
  > What is the sense behind such an update? 
  > why not just leave the database alone?

The Android specific virtual method SQLiteOpenHelper::OnUpgrade

    public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
        final int newVersion) {...}

is called because the database-structure has changed (oldVersion is not equal to newVersion, tables/fields have been modified/renamed/added/deleted).

So the author of the sourcecode you are looking at has decided to recreate database from the scratch and loosing all the database-data.

The Programm would not run with the old database structure, because tables/fields would be missing.