Handling Database Structure Changes

In the previous article we wrote a simple Android app allowing the user to manage his ToDo list. He could add new tasks, mark them as done and delete them. That article showed how to create an SQLite DB in an automated way and how to do some simple CRUD operations on it.

Let’s say that the first version of an application is released, we distribute it (i.e., it shows up in Google Play), people download and use it. After some time we decide to improve the app. Our goal is to add the ability to prioritize the tasks.

Handling Database Structure Changes – Migration Scripts

What do we need to do? At first, it seems that it doesn’t differ from the process we went through when implementing the previous version:

• change database schema and replace the file with SQL script,
• implement new operations in code,
• release a new version of the application.

Well, the first two steps are correct. Yet this plan lacks an extremely important point – the database migration from the older version to the new one. If we release a new version of the application, its code must be compatible with the database structure. That’s why we must assure that our app will work if either of the following occurs:

• new installation – one downloads and installs the app for the first time;
• upgrade to the new version.

The first case is obvious – the application is installed, on the first run (first DB open, to be precise) the database structure is created and everything works well.

In the second case, the user may already have stored some data in the DB. That’s why we cannot drop it and create it again from scratch. What we have to do is to change its structure in such a way that no data is lost and its final structure is exactly the same as if it were created at installation time. This process of adjusting the existing database to the new structure is called migration between different versions.

Android SDK supports performing such migrations. The onUpgrade method of the SQLiteOpenHelper class is called when the database version changes. In the code presented in the previous article this method was empty because it was the first version of the DB and no migration was necessary.

OK, now it might look easy: we need to create a migration SQL script and execute it in the onUpgrade method, right? Well... not exactly. I mean yes, we definitely must do this, but it’s not everything. Think of such a scenario: there are 5 releases of the app. User installs the second one, ignores upgrades 3 and 4, and finally upgrades to 5th version. If our implementation of the onUpgrade method executed every migration script between subsequent versions (from 1st to 2nd, from 2nd to 3rd, and so on) it would fail. Why? Because the user already had the 2nd version and the first migration script should be omitted. We also cannot execute only the last migration script (from 4th to 5th version), because the user had an ancient 2nd version of the app, so hte DB structure doesn’t correspond to the 4th version. We must execute migration scripts between the 2nd and 5th versions only.

The best way to manage migrations properly is to have migration scripts between subsequent versions in different files and... automate the process. Let’s do it on our ToDo tasks example app.

Prioritized ToDo Tasks

1. First of all, we need to make changes in our DB structure. Log into or sign up for your Vertabelo account. Add a table, make a reference. To make the data retrieval easier we’ll also create a simple view joining both tables. Export the SQL script and replace the previous one in res/raw/ directory. Done!

   
   
   
   
   

2. We need to populate the priority table. We’ll create an initial_data.sql file in which we’ll store all the data that should be inserted to the DB at installation time:

   INSERT INTO priority (id, name) VALUES (1, 'Low'); 
   INSERT INTO priority (id, name) VALUES (2, 'Medium'); 
   INSERT INTO priority (id, name) VALUES (3, 'High');
   

   We’ll execute it at the onCreate method, just after creating the database structure:

   @Override
   public void onCreate(SQLiteDatabase db) {
       // create structure
       readAndExecuteSQLScript(db, ctx, R.raw.db_create);
       
       // insert initial data
       readAndExecuteSQLScript(db, ctx, R.raw.initial_data);
   }
   

3. Don’t forget to increment the DB_VERSION constant in the DBOpenHelper class! It tells the SQLOpenHelper that the database structure has changed.
4. Implement new operations in the code. Since this is an example, I assumed that the user chooses a priority while creating a task and cannot change the priority later. You may do it better :)

5. Now it’s time for the most important part – taking care of the migration from the older DB versions. I assumed the filename convention: from_X_to_Y.sql in which Y=X+1. In our migration script – from_1_to_2.sql – we have to perform all structure changes:

   • create a new table:

     CREATE TABLE priority ( 
         id integer NOT NULL PRIMARY KEY, 
         name varchar(255) NOT NULL 
     );
     

   • add a new column to todo_task table:

     ALTER TABLE todo_task ADD COLUMN 
         priority_id integer 
         REFERENCES priority (id);
     

   • create a view:

     CREATE VIEW todo_task_view AS 
     SELECT 
       t.id as task_id, 
       t.description as task_description, 
       t.creation_time as task_creation_time, 
       t.is_done as task_is_done, 
       p.id as priority_id, 
       p.name as priority_name 
     FROM 
       todo_task t 
       JOIN priority p 
         ON (t.priority_id = p.id);
     

   • we also need to place all initial data changes – in this specific case it will be copy-pasting everything from initial_data.sql script.

   There is one more thing we must do. If there are any records in the todo_task table, we must set the default priority for all existing tasks. I assumed that all of them will be of “Medium” priority:

   UPDATE todo_task SET priority_id = 2;
   

6. After we created the migration script, we’ll automate the process of executing this and all future scripts:

   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       for(int i = oldVersion; i < newVersion; ++i) {
           String migrationName = String.format("from_%d_to_%d", i, (i+1));
           log("Looking for migration file: " + migrationName);
           int migrationFileResId = ctx.getResources()
                 .getIdentifier(migrationName, "raw", ctx.getPackageName());
           
           if(migrationFileResId != 0) {
               // execute script
               log("Found, executing");
               readAndExecuteSQLScript(db, ctx, migrationFileResId);
               
           } else {
               log("Not found!");
           }
       }
   }
   

   How does it work? We iterate through all versions between oldVersion and newVersion. We look for migration script files (for example from_1_to_2.sql, from_2_to_3.sql, and so on) and if they exist – we execute them. With this few lines of code we don’t need to worry about future migrations. We’ll need to create a migration script file only, and it will be executed automatically.

That’s all! Now, just test the two scenarios: first installation of the app and upgrade from the previous version.

As always, you can download the full source code of this example app here. It’s yours.