CODEWITHSUNDEEP
androidsqliteandroid-room
diogojme
diogojme

Reputation: 2359

Room Database Migration doesnt properly handle ALTER TABLE migration

Java.lang.IllegalStateException

Migration didn't properly handle user(therealandroid.github.com.roomcore.java.User).

Expected:

TableInfo{name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, age=Column{name='age', type='INTEGER', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}}, foreignKeys=[]} Found:

Found

TableInfo{ name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, age=Column{name='age', type='INTEGER', notNull=false, primaryKeyPosition=0}}, foreignKeys=[]}

I'm trying to perform a simple migration, I have a class called Userand it have two columns ID (primary key) and NAME TEXT and then I populate database with two users data, then I add the column AGE in the object User and in the Migration constant I add an alter table to add this new column and lastly I replace version of the database 1 to 2.

Here is the code

User.class

@Entity(tableName = "user")
  public class User {

  @PrimaryKey
  private int id;

  @ColumnInfo(name = "name")
  private String name;

  @ColumnInfo(name = "age")
  private int age;


  public int getId() {
      return id;
  }

  public void setId(int id) {
      this.id = id;
  }

  public String getName() {
      return name;
  }

  public void setName(String name) {
      this.name = name;
  }

  public int getAge() {
      return age;
  }

  public void setAge(int age) {
      this.age = age;
  }
}

Database class

@Database(entities = {User.class}, version = 2)
public abstract class RoomDatabaseImpl extends RoomDatabase {
    abstract UserDao userDao();
}

Migration code

public static Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE 'user' ADD COLUMN 'age' INTEGER");
    }
 };

and it call

Room.databaseBuilder(context, RoomDatabaseImpl.class, "Sample.db")
            .addMigrations(MIGRATION_1_2)
            .allowMainThreadQueries()
            .build();

Before change the object adding AGE and performing the migration I add two register and it works.

After performing the migration, I just tried to add a new User as bellow:

  User user = new User();
  user.setName("JoooJ");
  user.setId(3);
  user.setAge(18);

  List<User> userList = new ArrayList<>();
  userList.add(user);
  App.database(this).userDao().insertAll(userList);  // The crash happens here

Other informations:

Android Studio 3 and I didn't tested in the actual.

Dependencies:

compile "android.arch.persistence.room:runtime:1.0.0-alpha9-1"
annotationProcessor "android.arch.persistence.room:compiler:1.0.0-alpha9-1"

compile "android.arch.persistence.room:rxjava2:1.0.0-alpha9-1"
gradle 2.3.3

Can someone help me please, I realy don't know what im doing wrong or if it is a bug.

Upvotes: 68

Views: 52669

Answers (10)

Man
Man

Reputation: 2817

if you have notNull true and false issue, it can be solved in two ways

  1. Change the types of @Entity class variables to null to NotNull typs or vice-versa using ? eg String? Int?

    val name: String?, // to use with null type column

  2. Add/remove NOT NULL after column name

    database.execSQL("CREATE TABLE Fruit (idINTEGER,name TEXT NOT NULL, PRIMARY KEY(id))")

Make to sure to match Entity param type(nullable or nonnull) with Column type

String -> NOT NULL

String? -> NULL (//default type in sqlite, no need to mention it)

Upvotes: 1

Coffe Milk
Coffe Milk

Reputation: 31

I had problems with roomVersion '2.4.0-alpha01', this version not generate Index table for my case

@Entity(
    tableName = "SoundRules",
    indices = [
        Index(value = ["remoteId"], unique = true)
    ]
)

I resolve problem just updated room version to '2.4.0-alpha03'

Upvotes: 0

ravi
ravi

Reputation: 1001

The tool mentioned by @HRankit in his answer: link did not seem to work when I tried it today. If that is the case for you too, please read on:

If anyone is struggling with error message mentioned in the question and have a huge table with lots of columns, you might want to try this online tool to check between expected and found schema.

Also its really important that you decide upon the column names and data types before the <db_version_number>.json (eg: 13.json) file is generated. If the json file was already generated and you made some changes to the Entity class afterwards, you might want to delete the json file and rebuild the project to generate it with correct set of values.

Lastly you should check your sql statements on the migration itself.

Upvotes: 2

Foroogh Varmazyar
Foroogh Varmazyar

Reputation: 1565

if you want to add Integer type column , add this code

database.execSQL("ALTER TABLE users"
                    + " ADD COLUMN year INTEGER NOT NULL DEFAULT 0 ")

Upvotes: 5

Dilanka Laksiri
Dilanka Laksiri

Reputation: 268

I have faced notNull differences in kotlin and found exception like below

Expected:
TableInfo{name='enDic', columns={definition=Column{name='definition', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, favourite=Column{name='favourite', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, word=Column{name='word', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, usage=Column{name='usage', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
Found:
TableInfo{name='enDic', columns={usage=Column{name='usage', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, definition=Column{name='definition', type='text', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, _id=Column{name='_id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, favourite=Column{name='favourite', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, word=Column{name='word', type='text', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

Then I used below mentioned code to solve that problem

@Entity(tableName = "enDic")
data class Word(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    var _id: Int?,

    @ColumnInfo(name = "word")
    var word: String?,

    @ColumnInfo(name = "definition")
    var definition: String,

    @ColumnInfo(name = "favourite")
    var favourite: Int?,

    @ColumnInfo(name = "usage")
    var usage: Int?

)

Instead of this code

@Entity(tableName = "enDic")
data class Word(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    var _id: Int,

    @ColumnInfo(name = "word")
    var word: String,

    @ColumnInfo(name = "definition")
    var definition: String,

    @ColumnInfo(name = "favourite")
    var favourite: Int,

    @ColumnInfo(name = "usage")
    var usage: Int

)

Upvotes: 0

live-love
live-love

Reputation: 52366

If you are getting notNull differences, you can simply mark your class field with @NonNull annotation, or change your sql with ALTER TABLE. But if you are getting column type differences, such as expected: TYPE=TEXT, then found TYPE='' (COLLATE NOCASE), or expected INTEGER, found INT, then the only solution is to drop and recreate your table. Sqlite does not allow changing column types.

Use INTEGER in Sqlite instead of INT and mark your Java entity with @ColumnInfo(collate = NOCASE) (if you use NOCASE in Sqlite).

Take a look at the json file under app\schemas to get the sql for the expected queries.

static final Migration MIGRATION_2_3= new Migration(2, 3) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {

            database.execSQL("DROP TABLE IF EXISTS table_tmp");

            database.execSQL("CREATE TABLE IF NOT EXISTS `table_tmp` ...");

            database.execSQL("insert into table_tmp (`id`, `name` , ...");

            database.execSQL("DROP INDEX IF EXISTS `index_table_name`");

            database.execSQL("CREATE INDEX IF NOT EXISTS `index_table_name` ON `table_tmp` (`name`)");

            database.execSQL("DROP TABLE IF EXISTS table");

            database.execSQL("alter table table_tmp rename to table");

        }
    };

Upvotes: 1

Zaid Mirza
Zaid Mirza

Reputation: 3699

I faced this issue today, I just changed int fields to Integer in Entities. As int cannot be null but Integer objects could be null.

Upvotes: 7

Prabhtej Singh
Prabhtej Singh

Reputation: 345

None of the answers are correct in any of the links. After much experiments, found a way for it. The ALTER query needs to be written in the following way to make it work:

database.execSQL("ALTER TABLE 'user' ADD COLUMN 'age' INTEGER NOT NULL DEFAULT 0")

However, the Integer DEFAULT value can be anything.

If you want to add String type column, add in the following manner:

database.execSQL("ALTER TABLE 'user' ADD COLUMN 'address' TEXT")

This works like a charm.

Upvotes: 27

HRankit
HRankit

Reputation: 481

I too wrote a small JS script which you can find https://hrankit.github.io/RoomSQLiteDifferenceFinder/

The process is pretty Simple.

  1. Input the Expected error log in Expected column which is the Left One.

  2. Input the Found error log in Found column which is the Right One.

  3. Press Go. button. The error logs get converted to JSON.

  4. Press Compare button and Voila, you have the difference you need.

This plugin finds out the difference in the two Expected and Found dump from the Android Studio Logcat.

Checkout the image of comparison here

Upvotes: 48

Topper Harley
Topper Harley

Reputation: 12374

The error message is hard to parse, but there's a difference:

TableInfo{name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, age=Column{name='age', type='INTEGER', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}}, foreignKeys=[]} Found:

Found

TableInfo{ name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, age=Column{name='age', type='INTEGER', notNull=false, primaryKeyPosition=0}}, foreignKeys=[]}

Age is nullable but Room expected it to be not null.

Change your migration to:

database.execSQL("ALTER TABLE 'user' ADD COLUMN 'age' INTEGER NOT NULL");

Since this exception explanation is VERY difficult to parse, I have created a small script that does the diff for you.

Example:

mig "java.lang.IllegalStateException: Migration failed. expected:TableInfo{name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, age=Column{name='age', type='INTEGER', notNull=true, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}}, foreignKeys=[]} , found:TableInfo{name='user', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, id=Column{name='id', type='INTEGER', notNull=true, primaryKeyPosition=1}, age=Column{name='age', type='INTEGER', notNull=false, primaryKeyPosition=0}}, foreignKeys=[]}"

Result:

expected/found diff

Upvotes: 139

Related Questions