Sqlite to Room DB Migration affinity issue

Question

I I have list of tables in sqlite and in one table schema is something like that

         CREATE TABLE IF NOT EXIST `charges`(
              `chargeId int(11) NOT NULL,
              `name varchar`(256) NOT NULL,
              `nameOnBill` varchar(30),
              `chargeType` text,
              `value` decimal(13,4) NOT NULL,
              `isActive` tinyint(1) NOT NULL DEFAULT '1',
              PRIMARY KEY (chargeId))

But when I am create a Room Entity for data type decimal I am using Double (For this value decimal(13,4) NOT NULL ) I am getting pre-package error affinity issue (expect affinity= 4 found affinity =1 ).

Here is my column info @ColumnInfo(name = "value", typeAffinity = 1) val value: Double, Adding typeAffinity = 1 is also not working

Error: Expected: value=Column{name='value', type='REAL', affinity='4', notNull=true, primaryKeyPosition=0, defaultValue='undefined'} Found: value=Column{name='value', type='decimal(13,4)', affinity='1', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}

Let me know if any one has faced this problem and have solution, how to forcefully define typeAffinity as 1 (Which is in my case SQLite DB has and can't change on production running app.)

Answer 1

Unlike SQLite's flexible type's Room restricts types to INTEGER, TEXT, REAL, or BLOB. As such you need to convert the pre-existing database so that the tables only use these types.

• Room also expects other parts of the column definition to comply with it's schema such as NOT NULL.

The simplest way to ascertain Room's expectations is to

1. create the @Entity annotated classes.
2. create an @Database annotated class with the entities included in the @Database annotation.
3. compile the project.
4. in Android View find the class, in the java (generated) directort/folder that is the same name as the @Database annotated class but suffixed with _Impl.
5. in the class locate the createAllTables function. This executes the SQL that Room would use to create the tables and is EXACTLY according to Room's expectations.

The generated SQL should be used to create the table(s) this may be via running a conversion process that:-

1. renames the original table(s)
2. creates the table(s) using the generated SQL
3. populates the newly created table(s) by using an INSERT .... SELECT ....
4. drops the renamed original table(s)

The conversion could be done using an SQLite tool prior to copying the database into the asset folder. Alternately you could use the prePackagedDatabaseCallback to convert the database on the fly using the same conversion process.

Example/Demo (based upon your code)

Stage 1. Create the @Entity and @Database annotated classes:-

@Entity
data class Charges(
    @PrimaryKey
    val chargeId: Long?=null,
    val name: String,
    val nameOnBill: String,
    val chargeType: String,
    val value: Double,
    @ColumnInfo(defaultValue = "1")
    val isActive: Boolean
)

@Database(entities = [Charges::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
} 

Stage 2. Compile the project and extract the SQL:-

• ignore the room_master_table code as Room will manage these

Stage 3. Convert the database in an SQLite tool (Navicat for SQLite used).

This is a pseudo conversion that creates an populates the table as per the original schema and then converts it. The following is the SQL run in the tool:-

/* make sure the environment is clean (just in case)*/
DROP TABLE IF EXISTS `charges`;
DROP TABLE IF EXISTS `charges_original`;
/* Create the original table */
CREATE TABLE IF NOT EXISTS `charges`(
              `chargeId` int(11) NOT NULL,
              `name` varchar(256) NOT NULL,
              `nameOnBill` varchar(30),
              `chargeType` text,
              `value` decimal(13,4) NOT NULL,
              `isActive` tinyint(1) NOT NULL DEFAULT '1',
              PRIMARY KEY (chargeId)
);
/* Add some data to the original table */
INSERT INTO `charges` VALUES
    (1,'C1','Jane Doe','T1',100.01,0),
    (2,'C2','Fred Bloggs','T2',-999.01,1)
;

/*2. The Conversion process */
/* 2.1 rename the original table */
ALTER TABLE `charges` RENAME TO `charges_original`;
/* 2.2 Create the table according to the SQL created by Room */
CREATE TABLE IF NOT EXISTS 
    `Charges` (
        `chargeId` INTEGER, 
        `name` TEXT NOT NULL, 
        `nameOnBill` TEXT NOT NULL, 
        `chargeType` TEXT NOT NULL, 
        `value` REAL NOT NULL, 
        `isActive` INTEGER NOT NULL DEFAULT 1, 
        PRIMARY KEY(`chargeId`)
);
/* 2.3 Populate the new Room compatible table */
INSERT INTO `charges` SELECT * FROM `charges_original`;
/* 2.4 Drop the orignal renamed table*/
DROP TABLE IF EXISTS `charges_original`;

• The important factor is that the SQL for the 2nd create is as copied from the generated java.
• The INSERT (copy from original into the new table) is in this case simple but in some circumstances it may need to be more complex.

Stage 4 Copying the asset (database). It is important that the database is properly closed (in Navicat this means exiting the program). Properly closed means that there is just the single file. not a file that is the same name suffixed with -wal and/or - shm (-journal is not an issue).

• MUST NOT BE

• the single file can be renamed accordingly e.g.

The end result being:-

Stage 4. amending the code to test.

First an @Dao annotated interface is added:-

@Dao
interface TheDAOs {
    @Query("SELECT * FROM charges")
    fun getAllCharges(): List<Charges>
}

The @Database class is expanded for it to be more functional:-

@Database(entities = [Charges::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs
    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance= Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .createFromAsset("the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Finally some activity code is added to extract all data from the database (which will copy the asset/pre-packaged database):-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao=db.getTheDAOs()
        for (c in dao.getAllCharges()) {
            Log.d("DBINFO","Charge ID is ${c.chargeId} Name is ${c.name} BillName is ${c.nameOnBill} Type is ${c.chargeType} Value is ${c.value} ....")
        }
    }
}

When run:-

D/DBINFO: Charge ID is 1 Name is C1 BillName is Jane Doe Type is T1 Value is 100.01 ....
D/DBINFO: Charge ID is 2 Name is C2 BillName is Fred Bloggs Type is T2 Value is -999.01 ....

• i.e. the converted database is fine and the expected two rows are extracted. Notably without any need to try overriding any attributes.