ActiveAndroid Schema Migration

Today I learned something. Something other people have already learned long time ago.

While I was debugging MindFuel app, I noticed that I have missed a column in one of its database table. So I go around and do some research on how to do a schema migration to add a column in ActiveAndroid. It took me 2 days (with some rest of course), to finally found a solution.

The problem was 1) to add a column, and 2) make it a unique value. Seems so simple, but I was the one who made it complex. Well, at least I learned in this process.

Initially, I thought it would be as simple as executing alter table command; I was wrong. I've found out that SQLite doesn't support modifying a column type. Along the way, I have figured out how to do both operations in one sql file.

This is the correct process on how to solve this issue:

Assuming we have a table with 5 columns, we call this old_table.

  1. Alter the table to add one more column called quote_id
  2. Create a new table together with the intended new column plus, make it unique (we call this new_table)
  3. Copy all row values from old_table to new_table
  4. Drop the old_table
  5. Rename new_table to old_table

The first step is crucial for the whole process to be correctly executed, especially when it comes to step 3. If we have not done so, SQL will give you an error saying that the old_table only have 5 columns, whereas in new_table have 6 columns. I did this mistake. That tells me why I delayed this into two days.

The whole process can be executed by using the following command:

-- create table if not exists Quotes (
--     id integer primary key autoincrement,
--     quote text,
--     author text,
--     created_at integer,
--     quote_type text
-- );

alter table Quotes add column remote_quote_id text;

create table if not exists new_table (
    id integer primary key autoincrement,
    quote text,
    author text,
    created_at integer,
    quote_type text,
    remote_quote_id text unique

insert into new_table select * from Quotes;

drop table Quotes;

alter table new_table rename to Quotes;

The next step is to change the manifest file to increase database version to 2 and add a migration file in assets/migrations/ folder called 2.sql.

Show Comments

Get the latest posts delivered right to your inbox.