Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
Without exact context that's a bit difficult to say, but in test driven development you normally drop the test db and create new to avoid having data left from last run that would interfere with your test (you often add specific test data on creation). For a tutorial this may have similar reasons like: now after creation of this new record there should be exactly 5 records in the table (the new one and 4 from initialization), not 500 or any other random number. –  thorsten müller Jul 15 '13 at 12:17
    
@thorstenmüller What should i do in the onUpdate() method if I do not want data from the previous run to be erased? –  user221287 Jul 15 '13 at 12:30
1  
Then we can't answer this without exact knowledge of this project. It's a bit uncommon, but there are cases where some app may only need data for the current session and can forget it when the App is closed. (say you only want to use some helpful functionality like sorting that a db can give easily or handling of deep structured data –  thorsten müller Jul 15 '13 at 12:31
1  
I'm no Android programmers, so I don't know the libraries. There should be a method call on something like the db object and removing this call should avoid such behavior. But then most likely follow the part that creates the tables, so you should jump over that too like: if database does not exist: run creation part else: do nothing at all. –  thorsten müller Jul 15 '13 at 12:33
    
does this question belong to stackoverflow? –  k3b Jul 15 '13 at 15:48
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
add comment
  > 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.

share|improve this answer
    
Will all the data be lost? what should i do if I do not wish to loose the data and do not have to change the structure of the table? What should by onUpgrade method contain then? –  user221287 Jul 15 '13 at 16:43
    
Create database in onCreate() do nothing in onUpgrade() –  k3b Jul 15 '13 at 19:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.