Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite

Question

I've been trying to migrate my app to Room. I'm struggling with a particular table that can't be migrated directly because of the way it has been created.

The fields were created with datatype BOOL and BYTE instead of INTEGER.

I've already failed trying:

Change my entity fields to Int/Boolean/Byte with the same error
Creating a TypeConverter to save it as Boolean/Byte
Adding typeAffinity as UNDEFINED in @ColumnInfo of my entity that is affinity = 1

My databaseSQL creation sentence:

CREATE TABLE IF NOT EXISTS myTable (_id INTEGER PRIMARY KEY AUTOINCREMENT,
my_first_field BOOL NOT NULL DEFAULT 0,
my_second_field BYTE NOT NULL DEFAULT 0)

My Entity:

@Entity(tableName = "myTable")
data class MyTable(
        @PrimaryKey(autoGenerate = true)
        @ColumnInfo(name = "_id")
        var id: Int,

        @ColumnInfo(name = "my_first_field")
        var myFirstField: Boolean = false,

        @ColumnInfo(name = "my_second_field")
        var mySecondField: Byte = false
)

The error I'm constantly getting is:

Expected:
TableInfo{name='my_table', columns={_id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, my_first_field=Column{name='my_first_field', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}, my_second_field=Column{name='my_second_field', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}
     Found:
TableInfo{name='my_table', columns={_id=Column{name='_id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1}, my_first_field=Column{name='my_first_field', type='BOOL', affinity='1', notNull=true, primaryKeyPosition=0}, my_second_field=Column{name='my_second_field', type='BYTE', affinity='1', notNull=true, primaryKeyPosition=0}}, foreignKeys=[], indices=[]}

Is there any way to make straight forward without creating a migration strategy?

MikeT · Accepted Answer

I believe you could, before building the room database:-

Check to see if anything needs to be done e.g. by using :-
- SELECT count() FROM sqlite_master WHERE name = 'myTable' AND instr(sql,' BOOL ') AND instr(sql,' BYTE ');
- and then checking the result.
- If it is 0 do nothing else (although to be safe you could only use DROP TABLE IF EXISTS oldmyTable when it is 0).
- ONLY If the above returns 1 then :-
drop the renamed original table (see below and also above) just in case it exists :-
- DROP TABLE IF EXISTS oldmyTable;
define another table using
- CREATE TABLE IF NOT EXISTS myOtherTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, my_first_field INTEGER NOT NULL DEFAULT 0, my_second_field INTEGER NOT NULL DEFAULT 0)
- i.e. the expected schema
populate the new table using
- INSERT INTO myOtherTable SELECT * FROM myTable;
rename mytable using :-
- ALTER TABLE mytable RENAME TO oldmyTable;
rename myOtherTable using the original name :-
- ALTER TABLE myOtherTable RENAME TO mytable;
drop the renamed original table (obviously only when tested) :-
- DROP TABLE IF EXISTS oldmyTable;
  - You may wish to omit this until you are sure that the migration has worked.

The net result is that the table should be as is expected.

With regards to the comment :-

Problem is that I have like 16-20 tables to migrate.

The you could use something like :-

public static int preMigrateAdjustment(SQLiteDatabase mDB) {

    String original_rename_prefix = "old";
    String tempname_suffix = "temp";
    String newsql_column = "newsql";
    String[] columns = new String[]{
            "name",
            "replace(replace(sql,' BOOL ',' INTEGER '),' BYTE ',' INTEGER ') AS " + newsql_column
    };

    int count_done = 0;
    String whereclause = "name LIKE('" + 
            original_rename_prefix +
            "%') AND type = 'table'";
    Cursor csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
    while (csr.moveToNext()) {
        mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
    }


    whereclause = "type = 'table' AND (instr(sql,' BOOL ')  OR instr(sql,' BYTE '))";
    csr = mDB.query(
            "sqlite_master",
            columns,
            whereclause,
            null,null,null,null
    );
    while (csr.moveToNext()) {
        String base_table_name = csr.getString(csr.getColumnIndex("name"));
        String newsql = csr.getString(csr.getColumnIndex(newsql_column));
        String temp_table_name = base_table_name + tempname_suffix;
        String renamed_table_name = original_rename_prefix+base_table_name;
        mDB.execSQL(newsql.replace(base_table_name,temp_table_name));
        mDB.execSQL("INSERT INTO " + temp_table_name + " SELECT * FROM " + base_table_name);
        mDB.execSQL("ALTER TABLE " + base_table_name + " RENAME TO " + renamed_table_name);
        mDB.execSQL("ALTER TABLE " + temp_table_name + " RENAME TO " + base_table_name);
        count_done++;
    }
    whereclause = "name LIKE('" + 
            original_rename_prefix +
            "%') AND type = 'table'";
    csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
    while (csr.moveToNext()) {
        mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
    }
    csr.close();
    return count_done;
}

Note that this isn't fool proof e.g. if you happened to have tables that already start with old, then these would be dropped.
The above assumes a second run to actually drop the renamed original tables.

Additional

Looking into this and actually testing (in this case using 5 tables) with identical schema after resolving the BOOL BYTE types an additional issue comes to light in that coding

_id INTEGER PRIMARY KEY AUTOINCREMENT

results in notNull = false, whilst coding

@PrimaryKey(autoGenerate = true)
private long _id;

results in notNull=true

As such as quick fix that assumes that AUTOINCREMENT NOT NULL isn't coded the line in the preMigrateAdjustment has been changed from :-

mDB.execSQL((newsql.replace(base_table_name,temp_table_name)));

to :-

mDB.execSQL((newsql.replace(base_table_name,temp_table_name)).replace("AUTOINCREMENT","AUTOINCREMENT NOT NULL"));

Working Demo

Creating and Populating the old (pre-room) tables.

Creating and populating the old tables is done within the Database Helper OrginalDBHelper.java :-

public class OriginalDBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    int tables_to_create = 5; //<<<<<<<<<< 5 sets of tables

    SQLiteDatabase mDB;

    public OriginalDBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        for (int i=0;i < tables_to_create;i++) {

            db.execSQL("CREATE TABLE IF NOT EXISTS myTable" + String.valueOf(i) + "X (_id INTEGER PRIMARY KEY AUTOINCREMENT,
" +
                    "            my_first_field BOOL NOT NULL DEFAULT 0,
" +
                    "                    my_second_field BYTE NOT NULL DEFAULT 0)"
            );

            db.execSQL("INSERT INTO myTable" + String.valueOf(i) + "X (my_first_field,my_second_field) VALUES(0,0),(1,0),(1,1),(0,1)");
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

The pre-migration conversion of the tables

i.e. adjust the schema to suit room) PreMigrationAdjustment.java

public class PreMigrationAdjustment {

    public static int preMigrateAdjustment(SQLiteDatabase mDB) {

        String original_rename_prefix = "old";
        String tempname_suffix = "temp";
        String newsql_column = "newsql";
        String[] columns = new String[]{
                "name",
                "replace(replace(sql,' BOOL ',' INTEGER '),' BYTE ',' INTEGER ') AS " + newsql_column
        };

        int count_done = 0;
        String whereclause = "name LIKE('" +
                original_rename_prefix +
                "%') AND type = 'table'";
        Cursor csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
        while (csr.moveToNext()) {
            mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
        }


        whereclause = "type = 'table' AND (instr(sql,' BOOL ')  OR instr(sql,' BYTE '))";
        csr = mDB.query(
                "sqlite_master",
                columns,
                whereclause,
                null,null,null,null
        );
        while (csr.moveToNext()) {
            String base_table_name = csr.getString(csr.getColumnIndex("name"));
            String newsql = csr.getString(csr.getColumnIndex(newsql_column));
            String temp_table_name = base_table_name + tempname_suffix;
            String renamed_table_name = original_rename_prefix+base_table_name;
            mDB.execSQL((newsql.replace(base_table_name,temp_table_name)).replace("AUTOINCREMENT","AUTOINCREMENT NOT NULL"));
            //mDB.execSQL((newsql.replace(base_table_name,temp_table_name)));
            mDB.execSQL("INSERT INTO " + temp_table_name + " SELECT * FROM " + base_table_name);
            mDB.execSQL("ALTER TABLE " + base_table_name + " RENAME TO " + renamed_table_name);
            mDB.execSQL("ALTER TABLE " + temp_table_name + " RENAME TO " + base_table_name);
            count_done++;
        }
        whereclause = "name LIKE('" +
                original_rename_prefix +
                "%') AND type = 'table'";
        csr = mDB.query("sqlite_master",null,whereclause,null,null,null,null);
        while (csr.moveToNext()) {
            mDB.execSQL("DROP TABLE IF EXISTS " + csr.getString(csr.getColumnIndex("name")));
        }
        csr.close();
        return count_done;
    }
}

WARNING this is too simple to be used without consideration of it's flaws and is for demonstration only.

The Entities for room

only 1 of the 5 shown for brevity i.e. myTable0X.java

Obviously these have to be carefully written to match the pre-room tables.

@Entity()
public class myTable0X {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    private long id;

    @ColumnInfo(name = "my_first_field")
    private boolean my_first_field;
    @ColumnInfo(name = "my_second_field")
    private boolean my_second_field;

    public long getId() {
        return id;
    }

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

    public boolean isMy_first_field() {
        return my_first_field;
    }

    public void setMy_first_field(boolean my_first_field) {
        this.my_first_field = my_first_field;
    }

    public boolean isMy_second_field() {
        return my_second_field;
    }

    public void setMy_second_field(boolean my_second_field) {
        this.my_second_field = my_second_field;
    }
}

A single DAO interface DAOmyTablex.java

@Dao
public interface DAOmyTablex {

    @Query("SELECT * FROM myTable0X")
    List getAllFrommyTable0();

    @Query("SELECT * FROM myTable1X")
    List getAllFrommyTable1();

    @Query("SELECT * FROM myTable2X")
    List getAllFrommyTable2();

    @Query("SELECT * FROM myTable3X")
    List getAllFrommyTable3();

    @Query("SELECT * FROM myTable4X")
    List getAllFrommyTable4();

    @Insert
    long[] insertAll(myTable0X... myTable0XES);

    @Insert
    long[] insertAll(myTable1X... myTable1XES);

    @Insert
    long[] insertAll(myTable2X... myTable2XES);

    @Insert
    long[] insertAll(myTable3X... myTable3XES);

    @Insert
    long[] insertAll(myTable4X... myTable4XES);

    @Delete
    int delete(myTable0X mytable0X);

    @Delete
    int delete(myTable1X mytable1X);

    @Delete
    int delete(myTable2X mytable2X);

    @Delete
    int delete(myTable3X mytable3X);

    @Delete
    int delete(myTable4X mytable4X);

}

The Database mydb.java

@Database(entities = {myTable0X.class, myTable1X.class, myTable2X.class, myTable3X.class, myTable4X.class},version = 2)
public abstract class mydb extends RoomDatabase {
    public abstract DAOmyTablex dbDAO();
}

note that all 5 Entities have been utilised.
note that as the current database version is 1, room requires the version number to be increased hence version = 2

Putting it all together MainActivity.java

This consists of 3 core Stages

Building the pre-room database.
Converting the tables to suit room.
Opening (handing over) the database via room.

When the app starts it will automatically do stages 1 and 2 a button has been added that when clicked will then undertake stage 3 (just the once).

Finally, data is extracted from the tables (this actually opens the Room database) and data from one of the tables is output to the log.

public class MainActivity extends AppCompatActivity {

    OriginalDBHelper mDBHlpr;
    Button mGo;
    mydb mMyDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mGo = this.findViewById(R.id.go);
        mGo.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                goForIt();
            }
        });

        mDBHlpr = new OriginalDBHelper(this);
        Log.d("STAGE1","The original tables");
        dumpAllTables();
        Log.d("STAGE2", "Initiaing pre-mirgration run.");
        Log.d("STAGE2 A RESULT",
                String.valueOf(
                        PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()
                        )
                ) + " tables converted."
        ); //<<<<<<<<<< CONVERT THE TABLES
        Log.d("STAGE2 B","Dumping adjusted tables");
        dumpAllTables();
        Log.d("STAGE2 C","Second run Cleanup");
        Log.d("STAGE2 DRESULT",
                String.valueOf(
                        PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()
                        )
                ) + " tables converted."
        ); //<<<<<<<<<< CONVERT THE TABLES
        dumpAllTables();
        Log.d("STAGE3","Handing over to ROOM (when button is clicked)");
    }

    private void goForIt() {
        if (mMyDB != null) return;
        mMyDB = Room.databaseBuilder(this,mydb.class,OriginalDBHelper.DBNAME).addMigrations(MIGRATION_1_2).allowMainThreadQueries().build();
        List mt0 = mMyDB.dbDAO().getAllFrommyTable0();
        List mt1 = mMyDB.dbDAO().getAllFrommyTable1();
        List mt2 = mMyDB.dbDAO().getAllFrommyTable2();
        List mt3 = mMyDB.dbDAO().getAllFrommyTable3();
        List mt4 = mMyDB.dbDAO().getAllFrommyTable4();
        for (myTable0X mt: mt0) {
            Log.d("THIS_MT","ID is " + String.valueOf(mt.getId()) + " FIELD1 is " + String.valueOf(mt.isMy_first_field()) + " FIELD2 is " + String.valueOf(mt.isMy_second_field()));
        }
        // etc.......
    }

    private void dumpAllTables() {
        SQLiteDatabase db = mDBHlpr.getWritableDatabase();
        Cursor c1 = db.query("sqlite_master",null,"type = 'table'",null,null,null,null);
        while (c1.moveToNext()) {
            Log.d("TABLEINFO","Dmuping Data for Table " + c1.getString(c1.getColumnIndex("name")));
            Cursor c2 = db.query(c1.getString(c1.getColumnIndex("name")),null,null,null,null,null,null);
            DatabaseUtils.dumpCursor(c2);
            c2.close();
        }
        c1.close();
    }

    public final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            /**NOTES
            //Tried the pre-migration here BUT SQLiteDatabaseLockedException: database is locked (code 5 SQLITE_BUSY)
            //Cannot use SupportSQLiteDatabase as that locks out access to sqlite_master
            //PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()); //Initial run
            //PreMigrationAdjustment.preMigrateAdjustment(mDBHlpr.getWritableDatabase()); //Cleanup run
            */
        }
    };
}

As room will consider a migration underway a Migration object has the migration method overridden by a method that does nothing.
As per the comments attempts were made to utilise the migration, the issue is that the database is locked by room and that the the SupportSQliteDatabase passed to the migration method doesn't allow access to sqlite_master.

Result

The result (just the STAGE???? output) is :-

2019-05-19 13:18:12.227 D/STAGE1: The original tables
2019-05-19 13:18:12.244 D/STAGE2: Initiaing pre-mirgration run.
2019-05-19 13:18:12.281 D/STAGE2 A RESULT: 5 tables converted.
2019-05-19 13:18:12.281 D/STAGE2 B: Dumping adjusted tables
2019-05-19 13:18:12.303 D/STAGE2 C: Second run Cleanup
2019-05-19 13:18:12.304 D/STAGE2 DRESULT: 0 tables converted.
2019-05-19 13:18:12.331 D/STAGE3: Handing over to ROOM (when button is clicked)

The finals rows being :-

2019-05-19 13:20:03.090 D/THIS_MT: ID is 1 FIELD1 is false FIELD2 is false
2019-05-19 13:20:03.090 D/THIS_MT: ID is 2 FIELD1 is true FIELD2 is false
2019-05-19 13:20:03.090 D/THIS_MT: ID is 3 FIELD1 is true FIELD2 is true
2019-05-19 13:20:03.090 D/THIS_MT: ID is 4 FIELD1 is false FIELD2 is true

Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite

Answers (1)

Additional

Working Demo

Creating and Populating the old (pre-room) tables.

The pre-migration conversion of the tables

The Entities for room

A single DAO interface DAOmyTablex.java

The Database mydb.java

Putting it all together MainActivity.java

Result

Related Questions

Can&#39;t migrate a table to Room do to an error with the way booleans are saved in Sqlite

Answers (1)

Additional

Working Demo

Creating and Populating the old (pre-room) tables.

The pre-migration conversion of the tables

The Entities for room

A single DAO interface DAOmyTablex.java

The Database mydb.java

Putting it all together MainActivity.java

Result

Related Questions

Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite