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
- Alter the table to add one more column called
- Create a new table together with the intended new column plus, make it unique (we call this
- Copy all row values from
- Drop the
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:
-- NOT NECESSARY SINCE WE ALREADY HAVE THIS TABLE -- 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