How to update table with activeandroid after adding a new column

Question

thank you for reading and helping :)

I am using ActiveAndroid 3.0 - Android 2.2 - In my app I have a model called "user". initially I only created the model with (id, name, passcode) columns/attributes, I ran the app on the emulator, and it worked.

@Table(name = "user")
public class User extends Model {
  @Column (name = "name")  
  public String name;
  @Column (name = "pass_code")
  public String passCode;
}

Then I added a new column/attribute - profileImage. I ran the app on the emulator and i got SQLite column profileImage does not exist error.

@Table(name = "user")
public class User extends Model {
  @Column (name = "name")  
  public String name;
  @Column (name = "pass_code")
  public String passCode;
  @Column (name = "profile_image")
  public String profileImage;
}

I have tried changing the ActiveAndroid db name and updating the db version properties in the manifest file.

<meta-data android:name="AA_DB_NAME" android:value="my_app.db" />
<meta-data android:name="AA_DB_VERSION" android:value="2" />

But I keep getting the column does not exist error.

ERROR   AndroidRuntime  Caused by: android.database.sqlite.SQLiteException: 
no such column: profile_image: , while compiling: SELECT * FROM user WHERE profile_image = ?

I have also tried, un-installing the app from emulator, restarting the emulator, calling ActiveAndroid.cacheClear(). So far none of this worked.

I really appreciate any help. thanks.

Telegu

Answer 1

From the sounds of it, you're missing the upgrade script to migrate your user table to the new schema. The upgrade scripts basically contains the instructions on how to get from the old state of the database to the new one. In your specific case, you'll want to tell SQLite that it should add a new column, profile_image, to the existing user table.

To upgrade your database from schema 1 to 2, bump up the AA_DB_VERSION value to 2 and give the upgrade script the name 2.sql. Save the file under assets/migrations and give it the following content:

ALTER TABLE user ADD profile_image TEXT;

The scripts may contains any set of SQL statements that can be executed by the SQLiteDatabase through execSQL(...).

More details on schema migrations with Active Android can be found in the wiki on the GitHub project page.

Diffuse

Out of curiosity, shouldn't removing the app and reinstalling it have worked? Or does removing the app not remove the DB as well? – Reniti

@JoshPinter: Yes, uninstalling the app should work. As a matter of fact, hitting 'Clear Data' should do the track too. That's not a long-term solution though, because you probably don't want your app's users to go through that process. The correct approach is to provide appropriate schema upgrade logic. – Diffuse

@Diffuse Absolutely. Just curious why that didn't work for the OP. – Reniti

@JoshPinter: I can only guess that the OP mixed something up along the way, while trying to solve the upgrade problem. Not sure, as removing the app should also always remove any databases the app created through a SQLiteOpenHelper. – Diffuse

so i need to keep creating file with version.sql that contains the COLUMN added from the first version ? – Winwaloe

@JesusDimrix: No, the upgrade scripts are cumulative. That is, if the database version is at 2 and the new version is at 5, the upgrade process will first execute 3.sql, then 4.sql and finally 5.sql. (you can easily see this in ActiveAndroid's #executeMigrations()) In other words: you don't (and shouldn't) copy the changes from one upgrade script to the next. – Diffuse

here is ActiveAndroid's wiki page for schema migrations github.com/pardom/ActiveAndroid/wiki/Schema-migrations – Thermel

Answer 2

The "TEXT" label is changed to "VARCHAR" for database migration using ActiveAndroid

All other Reserved words were highlighted except for "TEXT". I used "VARCHAR" and it worked.

Tinney