I'm migrating the database access from SQLiteOpenHelper to Room.
However I've noticed that the database schema is not accepted in Room.
There is a table that has a primary key composed of two columns and one of them is nullable.
In Room the primary key must be non null.
So I want to perform a query to fix the schema before start using Room.
The current database version set using SQLiteOpenHelper is 8 and I'll set Room's database version to 9.
I added a migration in Room so a upgrade could be performed but nothing happens.
Room.databaseBuilder(
context.applicationContext,
AppDatabase::class.java,
"databse")
.addMigrations(MIGRATION_8_9)
.fallbackToDestructiveMigration()
.build()
private val MIGRATION_8_9 = object: Migration(8, 9) {
override fun migrate(database: SupportSQLiteDatabase) {
L.tag(TAG).info("Performing database migration from SQLiteHelper to Room")
database.execSQL("DO SOME WORK")
}
}
How can I run a SQLite statement to fix the database schema before I start using Room?
I added a migration in Room so a upgrade could be performed but nothing happens.
Your code should work (as per the demo below) BUT only if you actually try to do something with the database other than instantiating it. That is the database isn't opened until it is actually required.
Consider the following example:-
@Database(entities = [MyTable::class],version = 9,exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
private var instance: TheDatabase? = null
private val TAG = "ROOMDBINFO"
fun getInstance(context: Context): TheDatabase {
if (instance == null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,"database")
.allowMainThreadQueries()
.addMigrations(MIGRATION_8_9)
.build()
}
return instance as TheDatabase
}
private val MIGRATION_8_9 = object: Migration(8, 9) {
override fun migrate(database: SupportSQLiteDatabase) {
Log.d(TAG,"Performing database migration from SQLiteHelper to Room")
var csr = database.query("SELECT * FROM sqlite_master")
DatabaseUtils.dumpCursor(csr)
}
}
}
}
along with :-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
createBaseDatabaseToMigrate() //<<<<< Create and populate the database before Room
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
//dao.getAllFromMytable() //<<<<< Commented out so DB isn't opened
}
/* This will create the database if it doesn't exist*/
private fun createBaseDatabaseToMigrate() {
val TAG = "ORIGINALDATA"
var db = openOrCreateDatabase(this.getDatabasePath("database").absolutePath,0,null)
db.beginTransaction()
db.execSQL("CREATE TABLE IF NOT EXISTS mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))")
var csr = db.query("mytable",null,null,null,null,null,null)
var dataExists = csr.count > 0
csr.close()
if (!dataExists) {
db.execSQL("INSERT OR IGNORE INTO mytable VALUES(1,null,'data1'),(2,2,'data2'),(3,3,'data3');")
db.execSQL("PRAGMA user_version = 8;")
} else {
Log.d(TAG,"Data already existed.")
}
csr = db.query("mytable",null,null,null,null,null,null)
while(csr.moveToNext()) {
Log.d(TAG,
"COL1 = ${csr.getLong(csr.getColumnIndex("col1"))} " +
"COL2 = ${csr.getLong(csr.getColumnIndex("col2"))} " +
"COL3 = ${csr.getString(csr.getColumnIndex("col3"))}"
)
}
csr = db.query("sqlite_master",null,null,null,null,null,null)
DatabaseUtils.dumpCursor(csr)
csr.close()
db.setTransactionSuccessful()
db.endTransaction()
db.close()
}
}
Note the createDatabaseToMigrate shows how you could run a SQLite statement to fix the database schema before I start using Room. However, this is not suggested/needed as will be demonstrated.
Run on the main thread for convenience and brevity.
NOTE dao.getAllFromMytable() is commented out.
Test/Demo
Using the above code it is run and as per the log nothing happens in the Migration :-
2021-06-30 06:47:18.341 W/onversion8_to_: Accessing hidden method Landroid/view/ViewGroup;->makeOptionalFitsSystemWindows()V (light greylist, reflection)
2021-06-30 06:47:18.407 D/ORIGINALDATA: Data already existed.
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 1 COL2 = 0 COL3 = 0
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 2 COL2 = 2 COL3 = 0
2021-06-30 06:47:18.408 D/ORIGINALDATA: COL1 = 3 COL2 = 3 COL3 = 0
2021-06-30 06:47:18.408 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@be55dc1
2021-06-30 06:47:18.408 I/System.out: 0 {
2021-06-30 06:47:18.409 I/System.out: type=table
2021-06-30 06:47:18.409 I/System.out: name=android_metadata
2021-06-30 06:47:18.409 I/System.out: tbl_name=android_metadata
2021-06-30 06:47:18.409 I/System.out: rootpage=3
2021-06-30 06:47:18.409 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:47:18.409 I/System.out: }
2021-06-30 06:47:18.409 I/System.out: 1 {
2021-06-30 06:47:18.409 I/System.out: type=table
2021-06-30 06:47:18.409 I/System.out: name=mytable
2021-06-30 06:47:18.409 I/System.out: tbl_name=mytable
2021-06-30 06:47:18.409 I/System.out: rootpage=4
2021-06-30 06:47:18.409 I/System.out: sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:47:18.409 I/System.out: }
2021-06-30 06:47:18.409 I/System.out: 2 {
2021-06-30 06:47:18.409 I/System.out: type=index
2021-06-30 06:47:18.410 I/System.out: name=sqlite_autoindex_mytable_1
2021-06-30 06:47:18.410 I/System.out: tbl_name=mytable
2021-06-30 06:47:18.410 I/System.out: rootpage=5
2021-06-30 06:47:18.410 I/System.out: sql=null
2021-06-30 06:47:18.410 I/System.out: }
2021-06-30 06:47:18.410 I/System.out: <<<<<
2021-06-30 06:47:18.439 D/OpenGLRenderer: Skia GL Pipeline
2021-06-30 06:47:18.460 W/onversion8_to_: Accessing hidden method Landroid/graphics/Insets;->of(IIII)Landroid/graphics/Insets; (light greylist, linking)
As second run with the line //dao.getAllFromMytable() //<<<<< Commented out so DB isn't changed to dao.getAllFromMytable() //<<<<< Commented out so DB isn't opened
and:-
2021-06-30 06:51:28.059 W/onversion8_to_: Accessing hidden method Landroid/view/ViewGroup;->makeOptionalFitsSystemWindows()V (light greylist, reflection)
2021-06-30 06:51:28.129 D/ORIGINALDATA: Data already existed.
2021-06-30 06:51:28.129 D/ORIGINALDATA: COL1 = 1 COL2 = 0 COL3 = 0
2021-06-30 06:51:28.129 D/ORIGINALDATA: COL1 = 2 COL2 = 2 COL3 = 0
2021-06-30 06:51:28.130 D/ORIGINALDATA: COL1 = 3 COL2 = 3 COL3 = 0
2021-06-30 06:51:28.130 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@be55dc1
2021-06-30 06:51:28.130 I/System.out: 0 {
2021-06-30 06:51:28.130 I/System.out: type=table
2021-06-30 06:51:28.131 I/System.out: name=android_metadata
2021-06-30 06:51:28.131 I/System.out: tbl_name=android_metadata
2021-06-30 06:51:28.131 I/System.out: rootpage=3
2021-06-30 06:51:28.131 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:51:28.131 I/System.out: }
2021-06-30 06:51:28.131 I/System.out: 1 {
2021-06-30 06:51:28.131 I/System.out: type=table
2021-06-30 06:51:28.131 I/System.out: name=mytable
2021-06-30 06:51:28.131 I/System.out: tbl_name=mytable
2021-06-30 06:51:28.131 I/System.out: rootpage=4
2021-06-30 06:51:28.131 I/System.out: sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:51:28.131 I/System.out: }
2021-06-30 06:51:28.131 I/System.out: 2 {
2021-06-30 06:51:28.131 I/System.out: type=index
2021-06-30 06:51:28.132 I/System.out: name=sqlite_autoindex_mytable_1
2021-06-30 06:51:28.132 I/System.out: tbl_name=mytable
2021-06-30 06:51:28.132 I/System.out: rootpage=5
2021-06-30 06:51:28.132 I/System.out: sql=null
2021-06-30 06:51:28.132 I/System.out: }
2021-06-30 06:51:28.133 I/System.out: <<<<<
2021-06-30 06:51:28.161 D/ROOMDBINFO: Performing database migration from SQLiteHelper to Room
2021-06-30 06:51:28.162 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@71135f2
2021-06-30 06:51:28.162 I/System.out: 0 {
2021-06-30 06:51:28.162 I/System.out: type=table
2021-06-30 06:51:28.162 I/System.out: name=android_metadata
2021-06-30 06:51:28.162 I/System.out: tbl_name=android_metadata
2021-06-30 06:51:28.162 I/System.out: rootpage=3
2021-06-30 06:51:28.162 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-30 06:51:28.162 I/System.out: }
2021-06-30 06:51:28.162 I/System.out: 1 {
2021-06-30 06:51:28.163 I/System.out: type=table
2021-06-30 06:51:28.163 I/System.out: name=mytable
2021-06-30 06:51:28.163 I/System.out: tbl_name=mytable
2021-06-30 06:51:28.163 I/System.out: rootpage=4
2021-06-30 06:51:28.163 I/System.out: sql=CREATE TABLE mytable (col1 INTEGER NOT NULL, col2 INTEGER, col3 TEXT, PRIMARY KEY(col1,col2))
2021-06-30 06:51:28.163 I/System.out: }
2021-06-30 06:51:28.163 I/System.out: 2 {
2021-06-30 06:51:28.163 I/System.out: type=index
2021-06-30 06:51:28.163 I/System.out: name=sqlite_autoindex_mytable_1
2021-06-30 06:51:28.163 I/System.out: tbl_name=mytable
2021-06-30 06:51:28.163 I/System.out: rootpage=5
2021-06-30 06:51:28.164 I/System.out: sql=null
2021-06-30 06:51:28.164 I/System.out: }
2021-06-30 06:51:28.164 I/System.out: <<<<<
2021-06-30 06:51:28.169 D/AndroidRuntime: Shutting down VM
2021-06-30 06:51:28.171 E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so68183015kotlinroommigrationconversion8_to_9, PID: 24101
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so68183015kotlinroommigrationconversion8_to_9/a.a.so68183015kotlinroommigrationconversion8_to_9.MainActivity}: java.lang.IllegalStateException: Migration didn't properly handle: mytable(a.a.so68183015kotlinroommigrationconversion8_to_9.MyTable).
Expected:
TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
Found:
TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6669)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
Caused by: java.lang.IllegalStateException: Migration didn't properly handle: mytable(a.a.so68183015kotlinroommigrationconversion8_to_9.MyTable).
Expected:
TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
Found:
TableInfo{name='mytable', columns={col2=Column{name='col2', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=2, defaultValue='null'}, col3=Column{name='col3', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, col1=Column{name='col1', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.java:103)
at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onUpgrade(FrameworkSQLiteOpenHelper.java:183)
at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:398)
at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:298)
at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.getWritableSupportDatabase(FrameworkSQLiteOpenHelper.java:151)
at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper.getWritableDatabase(FrameworkSQLiteOpenHelper.java:112)
at androidx.room.RoomDatabase.inTransaction(RoomDatabase.java:705)
at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.java:482)
at a.a.so68183015kotlinroommigrationconversion8_to_9.AllDao_Impl.getAllFromMytable(AllDao_Impl.java:28)
2021-06-30 06:51:28.172 E/AndroidRuntime: at a.a.so68183015kotlinroommigrationconversion8_to_9.MainActivity.onCreate(MainActivity.kt:20)
at android.app.Activity.performCreate(Activity.java:7136)
at android.app.Activity.performCreate(Activity.java:7127)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
... 11 more
2021-06-30 06:51:28.185 I/Process: Sending signal. PID: 24101 SIG: 9
and you can see that the Migration has been called.
An Actual Migration
Based upon the above but amending the TheDatabase class and adding the conversion, then :-
The @Entity in this case is :-
@Entity(tableName = "mytable", primaryKeys = ["col1","col2"])
data class MyTable(
val col1: Long,
val col2: Long,
val col3: String
)
i.e. in this case both col2 and col3 columns do not have NOT NULL but room expects that they should. (look at comments re the SQL as it's been copied from the generated java).
Then (perhaps a little long-winded) TheDatabase could be :-
@Database(entities = [MyTable::class],version = 9,exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDao(): AllDao
companion object {
private var instance: TheDatabase? = null
private val TAG = "ROOMDBINFO"
fun getInstance(context: Context): TheDatabase {
if (instance == null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,"database")
.allowMainThreadQueries()
.addMigrations(MIGRATION_8_9)
.build()
}
return instance as TheDatabase
}
// copied from java(generated) <thisclass>_Impl.java> (i.e. TheDatabase_Impl):-
// From the createAllTables method
// _db.execSQL("CREATE TABLE IF NOT EXISTS `mytable` (`col1` INTEGER NOT NULL, `col2` INTEGER NOT NULL, `col3` TEXT NOT NULL, PRIMARY KEY(`col1`, `col2`))");
private val MIGRATION_8_9 = object: Migration(8, 9) {
override fun migrate(database: SupportSQLiteDatabase) {
val inTransaction = database.inTransaction()
Log.d(TAG,"Performing database migration from SQLiteHelper to Room")
if (!inTransaction) database.beginTransaction()
var csr = database.query("SELECT * FROM sqlite_master")
DatabaseUtils.dumpCursor(csr)
csr.close()
// SEE ABOVE FROM GETTING CORRECT SQL
database.execSQL("CREATE TABLE IF NOT EXISTS `mytable_new` (`col1` INTEGER NOT NULL, `col2` INTEGER NOT NULL, `col3` TEXT NOT NULL, PRIMARY KEY(`col1`, `col2`))")
csr = database.query("SELECT coalesce(col1,0) AS col1, coalesce(col2,0) AS col2, coalesce(col3,'nothing') AS col3 FROM `mytable`")
DatabaseUtils.dumpCursor(csr)
var cv = ContentValues()
while (csr.moveToNext()) {
cv.clear()
cv.put("col1",csr.getLong(csr.getColumnIndex("col1")))
cv.put("col2",csr.getLong(csr.getColumnIndex("col2")))
cv.put("col3",csr.getString(csr.getColumnIndex("col3")))
database.insert("`mytable_new`",OnConflictStrategy.IGNORE,cv)
}
csr.close()
csr = database.query("SELECT * FROM sqlite_master")
DatabaseUtils.dumpCursor(csr)
csr = database.query("SELECT * FROM `mytable`")
while (csr.moveToNext()) {
Log.d(TAG,
"COL1 = ${csr.getLong(csr.getColumnIndex("col1"))} " +
"COL2 = ${csr.getLong(csr.getColumnIndex("col2"))} " +
"COL3 = ${csr.getString(csr.getColumnIndex("col3"))}"
)
}
csr.close()
database.execSQL("ALTER TABLE `mytable` RENAME TO `mytable_original`")
database.execSQL("ALTER TABLE `mytable_new` RENAME TO `mytable`")
database.execSQL("DROP TABLE IF EXISTS `mytable_original`")
csr = database.query("SELECT * FROM sqlite_master")
DatabaseUtils.dumpCursor(csr)
csr.close()
if (!inTransaction) {
database.setTransactionSuccessful()
database.endTransaction()
}
}
}
}
}
When run (App is uninstalled so original non Room DB is created) ``mytable is converted (with null in col2 converted to 0 due to the use of coalesce (obviously you may want another value rather than 0)). Subsequent runs are then fine.
(answer too long to include the logs so you will have to believe me)