Migration from SQLite to Room DB

Question

I am facing a problem while migrating from SQLite to Room DB.

The problem is my old SQLite schema doesn't match with the new Room DB schema in my old SQLite DB I forgot to set primaryKey to NOT NULL and also I have a column URL and that column doesn't have any type like TEXT, INTEGER or BOOLEAN.

So now when I try to migrate my SQLite to Room I got Schema don't match error and my App is published on play store with SQLite DB.

So any help will be highly appreciated.

My old SQLite DB code:

private static final String DATABASE_NAME = "mylist.db";
private static final String TABLE_NAME = "mylist_data";
private static final String POST_TITLE = "ITEM1";
private static final String POST_URL = "URL";
private static final String KEY_ID = "ID";


public BookmarksDb(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            " ITEM1 TEXT," +
            " URL)";

    db.execSQL(createTable);
}

My new Room DB code:

@Entity(tableName = "mylist_data")
public class Bookmark {
@PrimaryKey()
@ColumnInfo(name = "ID")
private int id;

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

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

Problems are:

1. ID is NOT NULL = false in SQLite and in Room it is true by default(can't change it)
2. URL column in SQLite doesn't have any type and in Room it is TEXT by default.

I don't want to lose my old data which is stored in SQLite and my app is published, so now I want to migrate to Room without losing old user data.

Please help me to solve this problem.

Answer 1

You have two issue, the first the NOT NULL required is due to how Room handles primatives. So instead of using int use Integer (although really you should use Long). So change the Entity to be :-

@Entity(tableName = "mylist_data")
public class Bookmark {
@PrimaryKey()
@ColumnInfo(name = "ID")
private Integer id;

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

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

The second issue is the column affinity, you need to ALTER your table to suit the Entity, As you have private String postUrl; then as you have found Room expects a column type of TEXT as opposed to nothing (UNDEFINED Affinity = 1).

To circumvent this, you could run the following SQL's to convert the table to suit Room:-

DROP TABLE IF EXISTS converted_mylist_data;
DROP TABLE IF EXISTS old_mylist_data; 
CREATE TABLE IF NOT EXISTS converted_mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEM1 TEXT, URL TEXT);
INSERT INTO converted_mylist_data SELECT * FROM mylist_data; /* copies existing data into new table */
ALTER TABLE mylist_data RENAME TO old_mylist_data;
ALTER TABLE converted_mylist_data RENAME TO mylist_data;
DROP TABLE IF EXISTS old_mylist_data;

• Note you can actually retrieve the SQL to create the new table from the java(generated)

Example

Run 1 creates the database (version 1) not using Room using:-

db.execSQL("CREATE TABLE mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT,ITEM1 TEXT, URL);");
db.execSQL("INSERT INTO mylist_data VALUES(null,'item1','my url');");

The following changes are then made :-

1. Added the Entity

:-

@Entity(tableName = "mylist_data")
public class Bookmark {
    @PrimaryKey()
    @ColumnInfo(name = "ID")
    private Long id; /* <<<<<<<<<< CHANGED (could be Integer) from primative to object*/

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

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

    public Bookmark(){}

    public Long getId() {
        return id;
    }

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

    public String getPostTitle() {
        return postTitle;
    }

    public void setPostTitle(String postTitle) {
        this.postTitle = postTitle;
    }

    public String getPostUrl() {
        return postUrl;
    }

    public void setPostUrl(String postUrl) {
        this.postUrl = postUrl;
    }
}

2. A Dao

:-

@Dao
interface AllDao {
    @Query("SELECT * FROM mylist_data")
    List<Bookmark> getAll();
}

3. The Database with the version increased and a Migration for version 1 to 2

:-

@Database(entities = Bookmark.class,version = 2 /*<<<<<<<<<<*/,exportSchema = false)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();    
    private static volatile TheDatabase instance;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"mylist.db")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_1_2)
                    .build();
        }
        return instance;
    }

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

            database.beginTransaction();
            database.execSQL("DROP TABLE IF EXISTS converted_mylist_data;");
            database.execSQL("DROP TABLE IF EXISTS oldmylist_data;");
            database.execSQL("CREATE TABLE IF NOT EXISTS converted_mylist_data (ID INTEGER PRIMARY KEY AUTOINCREMENT, ITEM1 TEXT, URL TEXT);");
            database.execSQL("INSERT INTO converted_mylist_data SELECT * FROM mylist_data;");
            database.execSQL("ALTER TABLE mylist_data RENAME TO oldmylist_data;");
            database.execSQL("ALTER TABLE main.converted_mylist_data RENAME TO mylist_data;");
            database.setTransactionSuccessful();
            database.endTransaction();

        }
    };
}

4. The changed invoking/using activity (from SQLite to Room with old code commented out)

:-

public class MainActivity extends AppCompatActivity {

    //DBHelper db; /* Run 1 */
    TheDatabase db; /* Run 2  NEW */
    AllDao dao; /* Run 2  NEW */

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        /* Run 1 create the SQLite based database */
        /*
        db = new DBHelper(this);
        db.getWritableDatabase();
         */
        /* Run 2  NEW */
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();
        for (Bookmark b: dao.getAll()) {
            Log.d("BOOKMARKINFO","ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
        }
    }
}

Result :-

Successfully runs and outputs :-

D/BOOKMARKINFO: ID = 1 PostTitle = item1 PostURL =my url

i.e. data has been kept.

Answer 2

By default sqlite use Blob column type if type not defined in create table statement . Paragraph 3.1.3 of sqlite doc. That's why you can use @ColumnInfo(name = "URL", typeAffinity = ColumnInfo.BLOB) to solve your second problem. You declare id with type int which cant be null, try to use Integer instead int - i think it solve your first problem.

I think you have other option to migrate on room and not lose your data: use migration mechanism.