19

Issue

My app is crashing because I am not handling migration properly. I'm looking for a solution to migrate the name of 1 column in my table.

In my project I a have a room table named 'content' with a Double attribute 'archivedCount'. In the latest version of the app the attribute archivedCount attribute is re-named to dismissCount, still as type Double.

Original Content model

@Entity(tableName = "content")
data class Content(@PrimaryKey var id: String, var archiveCount: Double) : Parcelable {...}

New Content model

@Entity(tableName = "content")
data class Content(@PrimaryKey var id: String, var dismissCount: Double) : Parcelable {...}

Attempted Solution

After reading a Google Developer Advocate's explanation Understanding migrations with Room, I attempted her solution outlined in the post's section Migrations with complex schema changes which entails making a copy of the original table, deleting the old table, then renaming the newly created table.

With the following approach below there is a runtime error on this line: database.execSQL("INSERT INTO content_new (id, dismissCount) SELECT id, archiveCount FROM users"); because I already cleared my app's cache so the old table no longer exists.

Can I update a single column without re-creating the entire table?

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
@Override
public void migrate(SupportSQLiteDatabase database) {
    // Create the new table
    database.execSQL(
            "CREATE TABLE content_new (id TEXT, dismissCount REAL, PRIMARY KEY(id))");
    // Copy the data
    database.execSQL("INSERT INTO content_new (id, dismissCount) SELECT id, archiveCount FROM users");
    // Remove the old table
    database.execSQL("DROP TABLE content");
    // Change the table name to the correct one
    database.execSQL("ALTER TABLE content_new RENAME TO content");
  }
};
AdamHurwitz
  • 9,758
  • 10
  • 72
  • 134
  • 2
    Have you read [this](https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table)? – Tim Biegeleisen Oct 05 '18 at 03:02
  • Thank you @TimBiegeleisen. How would I format the SQL command as the post doesn't provide an example. I'm trying this, but the IDE does not like the syntax: `database.execSQL("ALTER TABLE 'content' RENAME COLUMN 'archiveCount' TO 'dismissCount'")`. Is it possible my syntax is correct, and IntelliJ just has not updated to this new rule? – AdamHurwitz Oct 07 '18 at 02:32
  • It turns out my syntax in the comment above causes a crash. How would I format this new SQL action? `android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE 'content' RENAME COLUMN 'archiveCount' TO 'dismissCount'` – AdamHurwitz Oct 07 '18 at 02:57
  • 2
    Stop putting single quotes around your table and column names. – Tim Biegeleisen Oct 07 '18 at 03:07
  • @TimBiegeleisen I removed the quotes within the command `database.execSQL("ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount")`. IntelliJ still does not like the syntax. – AdamHurwitz Oct 07 '18 at 03:13
  • 1
    What does IntelliJ have to do with this? What _SQLite_ errors are you still getting? – Tim Biegeleisen Oct 07 '18 at 03:15
  • 1
    @TimBiegeleisen IntelliJ is showing a warning that it is not a valid SQL command. **COLUM** is higlighted in red and it says *TO expected, got 'COLUMN'* This is the specific error I'm receiving in runtime which causes the crash: `android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount` – AdamHurwitz Oct 07 '18 at 03:24
  • What version of SQLite are you using? – Tim Biegeleisen Oct 07 '18 at 03:33
  • The API I'm on uses **3.19**. From your link it looks like I'll have to wait until they update to **3.25**. – AdamHurwitz Oct 07 '18 at 03:41
  • 1
    Yeah...that's what I was going to say. You may have to drop and recreate the table in your case. By the way, for future reference for a question like this, please tell us your SQLite version in the question. – Tim Biegeleisen Oct 07 '18 at 03:43
  • Will do! May you upvote as this is going to be a common issue for developers on Android once Room is adopted more widely. – AdamHurwitz Oct 07 '18 at 03:47
  • 1
    I know this is an older question, but as of November 2020, this is still 100% not doable with the current room db logic. You cannot rename a column since the SQLite version is not up to 3.25 and until that point you need to create a new table, copy the table data, drop the old table, and rename the new table. It's extremely tedious, but it's the only working way that isn't a hack like renaming the serialized vars. – PGMacDesign Nov 11 '20 at 21:16
  • Does this answer your question? [How do I rename a column in a SQLite database table?](https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table) – Xid Nov 10 '21 at 03:19

2 Answers2

18

Solution

Thanks to the guidance from @TimBiegeleisen we discovered that the Android implementation of SQLite 3.19 for API 27 and 28 has not yet upgraded to the version 3.25 SQLite which allows this feature outlined in this StackOverflow post.

Once Android upgrades a command such as this to alter a table column will be possible: database.execSQL("ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount")

Community
  • 1
  • 1
AdamHurwitz
  • 9,758
  • 10
  • 72
  • 134
  • 8
    Is it still not there in SQLite? I have written this: database.execSQL("ALTER TABLE Expense RENAME COLUMN price TO amount"); Getting an error android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE Expense RENAME COLUMN price TO amount And also I get hint at that line: got COLUMN, expected TO – sagar.android Mar 05 '20 at 20:42
  • 5
    It really needs to be noted that this does not work on Android devices that are not using SQLite 3.25, which at the time of this comment is **ALL** of them, so this answer is not correct as of 2020-11-11. The only solution I have found is to use the logic to create a new table, copy over the data, drop the old table, and rename the new table. – PGMacDesign Nov 10 '20 at 19:08
14

There is a solution without migration - use ColumnInfo:

data class Content(@PrimaryKey var id: String,  @ColumnInfo(name = "archiveCount") var dismissCount: Double) : Parcelable{...}

Database column will be still archiveCount, but in Kotlin property will be renamed.

atarasenko
  • 1,768
  • 14
  • 19
  • 8
    Thanks @atarasenki, this is a nice workaround. However, it does not solve the root of the issue and is more of a "band aid" type solution. – AdamHurwitz Oct 07 '18 at 02:43