How to migrate existing table in sqlite to room database in android

Question

I am trying to migrate my existing data in sqlite to room database.

I have provided a migration rule .

This is the migration rule:
private  static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
           database.execSQL("CREATE TABLE  temp_account (\n"+
                    " _id  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,"
                    + "name TEXT NOT NULL UNIQUE, "
                    +  "number TEXT DEFAULT 0, "
                    +  "balance REAL DEFAULT 0 NOT NULL, "
                    +  "account_type TEXT);");

            database.execSQL("INSERT INTO temp_account( name,number,balance,account_type) " +
                    "SELECT  name,number,balance,account_type FROM account");

            database.execSQL("DROP TABLE account");

            database.execSQL("ALTER TABLE temp_account RENAME TO account;");
;
Below is my original table which I want to migrate:
"CREATE TABLE  account (\n"+
                    " _id  INTEGER PRIMARY KEY AUTOINCREMENT ,"
                    + "name TEXT NOT NULL UNIQUE, "
                    +  "number TEXT DEFAULT 0, "
                    +  "balance REAL , "
                    +  "account_type TEXT);");

While it worked in most of the devices but the app crashes in few devices.Crashes are not specific to any device or android version.Below is error report from Google:

java.lang.RuntimeException: 
  at android.os.AsyncTask$3.done (AsyncTask.java:354)
  at java.util.concurrent.FutureTask.finishCompletion (FutureTask.java:383)
  at java.util.concurrent.FutureTask.setException (FutureTask.java:252)
  at java.util.concurrent.FutureTask.run (FutureTask.java:271)
  at android.os.AsyncTask$SerialExecutor$1.run (AsyncTask.java:245)
  at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1167)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:641)
  at java.lang.Thread.run (Thread.java:764)
Caused by: android.database.sqlite.SQLiteConstraintException:   at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:796)
    at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)

Any help is highly appreciated.

MikeT · Accepted Answer · 2019-09-18 16:36:44Z

2

I believe the issue is that some of the balances in the original table are NULL and when inserting that the newly added NOT NULL constraint is in conflict.

You can overcome this by replacing balance with either

CASE WHEN balance IS NULL THEN 0 ELSE balance END, or
coalesce(balance,0)

Both will effectively use a value of 0 instead of NULL.

e.g.

database.execSQL("INSERT INTO temp_account( name,number,balance,account_type) " +
                "SELECT  name,number,
                    coalesce(balance,0),
                    account_type FROM account");

or

database.execSQL("INSERT INTO temp_account( name,number,balance,account_type) " +
                "SELECT  name,number,
                    CASE WHEN balance IS NULL THEN 0 ELSE balance END,
                    account_type FROM account");

answered Sep 18, 2019 at 16:36

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

ANKIT Over a year ago

Thanks for your help.I had to set balance not null in new table as room was not allowing me to have a real type column as non null.But I in my original database I had made sure that a value is inserted in balance column so that value is never null.Also do I have do copy id also from old to new table or autoincrement will generate id by itself.

MikeT Over a year ago

@ANKIT you can have a real column as null, BUT the entity must not be a java primary type but an object. i.e. double mycolumn; requires not null in the table (even without @NonNull) BUT Double mycolumn; can be null (if @NonNull is not coded). You only need to copy the id, if the id is referenced by another table (safest to copy).

ANKIT Over a year ago

I have checked and your suggestions are working fine on my testing devices.But as my app is live in play store I will release the new version to my testers and wait for their response.One more question what if I don't copy Balance from old to new table.i.e. if I write database.execSQL("INSERT INTO temp_account( name,number,account_type) " + "SELECT name,number, account_type FROM account"); Will the migaration rule except me to provide a value for this non null column and throw an error.

MikeT Over a year ago

@ANKIT That should work as the default value would come into play and set the balance for all rows to 0. The checking that made you specify NOT NULL (the migration failed expected...../found....) is a comparison of the schema as generated from the Entities against the schema of the table. The Entities rule as such.

ANKIT Over a year ago

Means if I don't copy balance values then default value of 0 will be set and I won't get any errors.

|

Collectives™ on Stack Overflow

How to migrate existing table in sqlite to room database in android

1 Answer 1

23 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

23 Comments

Your Answer

Sign up or log in

Post as a guest

Related