0

I'm using a room DB. I've 2 tables A and B.

The primary key of A is foreign key in Table B.

Below is my DB structure -

@Entity(tableName = "A")
  data class A(
  @PrimaryKey
  @NotNull
  @ColumnInfo(name = "IDKEY")
  var xID: String
)

@Entity(
tableName = "B",
foreignKeys = [ForeignKey(
    entity = A::class,
    parentColumns = ["IDKEY"],
    childColumns = ["KEY"],
    onDelete = NO_ACTION
)])
data class B(
    @PrimaryKey(autoGenerate = true)
    @NotNull
    @ColumnInfo(name = "KEY")
    var id: Int = 0,
    @ColumnInfo(name = "IDKEY")
    var parentIDId: String)

Now I want to delete the table A from the room database. However table B will be there. So how should I handle this migration? I'm specifically looking out for removing the foreignKeys constraint. Not able to figure it out. I believe that should be removed before deleting the parent table A.

Also certain columns in Table B will be deleted.

So here's what I'm thinking about the approach -

  1. Remove foreign key constraints (need solution on how to do this?)
  2. Delete table A
  3. Alter table B

Please guide me on point 1 and suggest me if any better approach to deal with this.

Thanks.

Amrut
  • 2,655
  • 3
  • 24
  • 44

1 Answers1

1

Start by Creating a table based upon table B e.g.

 DROP TABLE IF EXISTS B_temp;
 CREATE TABLE B_temp AS SELECT * FROM B;

Then delete all rows from table B e.g.

DELETE FROM B;

Then DROP (or RENAME and DROP later) TABLE A;

DROP TABLE IF EXISTS A;

The make the alterations to table B.

Then reload table B with the data from the B_temp table e.g.

INSERT INTO B SELECT * FROM B_temp;

Finally drop the B_temp table;

An Alternative would be to use the SupportSQliteDatabase's setForeignKeyConstraintsEnabled before (false) and after (true). In which case the Foreign Keys will not be enforced and therefore you could just perform the steps you have specified.

Example using method 1

The following is a working example based upon the code in your question.

Pre-Migration - Version 1 with some data.

The @Dao annotated AllDao abstract class:-

@Dao abstract class AllDao {

/* Will be commented out (deleted) for Version 2 */
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract fun insert(a: A): Long

@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract fun insert(b: B): Long
@Query("SELECT * FROM B")
abstract fun getAllFromB(): List<B>

}

An @Database annotated class TheDatabase for Version 1 and 2 :-

const val DATABASE_VERSION = 1
@Database(entities = [A::class,/*<<<<< NOTE will be commented out for Version 2*/B::class], exportSchema = false, version = DATABASE_VERSION)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    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")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_1_2)
                    .build()
            }
            return instance as TheDatabase
        }

        private val MIGRATION_1_2: Migration = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("DROP TABLE IF EXISTS B_temp")
                db.execSQL("CREATE TABLE IF NOT EXISTS B_temp AS SELECT * FROM B;")
                db.execSQL("DELETE FROM B")
                db.execSQL("DROP TABLE IF EXISTS A;")
                db.execSQL("DROP TABLE IF EXISTS B")
                db.execSQL("CREATE TABLE IF NOT EXISTS `B` (`KEY` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `IDKEY` TEXT NOT NULL)")
                db.execSQL("INSERT INTO B SELECT * FROM B_temp")
            }
        }
    }
}

  • NOTE the CREATE TABLE for the new table B was copied from the generated java after compiling when the changes for version 2 were made.

  • Run on the main thread for convenience and brevity.

An activity MainActivity that will, when at version 1, load some data into both tables A and B:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    @SuppressLint("Range")
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        if (DATABASE_VERSION == 1) {

            /* Will be commented out for Version 2 */
            dao.insert(A("1"))
            dao.insert(A("2"))
            dao.insert(A("3"))

            dao.insert(B(id = 1,parentIDId = "ID001"))
            dao.insert(B(id = 2,parentIDId = "ID002"))
            dao.insert(B(id = 3,parentIDId = "ID003"))
        }

        /* Write the schema to the log */
        val suppdb = db.openHelper.writableDatabase
        val csr = suppdb.query("SELECT * FROM sqlite_master")
        while (csr.moveToNext()) {
            Log.d("DBINFO",
                "Component is ${csr.getString(csr.getColumnIndex("name"))} " +
                        "Type is ${csr.getString(csr.getColumnIndex("type"))} " +
                        "SQL is \n\t${csr.getString(csr.getColumnIndex("sql"))}")
        }
        /* Write the data in table B to the log */
        for(b in dao.getAllFromB()) {
            Log.d("DBINFO","KEY = ${b.id} IDKEY = ${b.parentIDId}")
        }
    }
}

Result :-

2022-03-03 08:39:35.353 D/DBINFO: Component is android_metadata Type is table SQL is 
        CREATE TABLE android_metadata (locale TEXT)
2022-03-03 08:39:35.353 D/DBINFO: Component is A Type is table SQL is 
        CREATE TABLE `A` (`IDKEY` TEXT NOT NULL, PRIMARY KEY(`IDKEY`))
2022-03-03 08:39:35.353 D/DBINFO: Component is sqlite_autoindex_A_1 Type is index SQL is 
        null
2022-03-03 08:39:35.354 D/DBINFO: Component is B Type is table SQL is 
        CREATE TABLE `B` (`KEY` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `IDKEY` TEXT NOT NULL, FOREIGN KEY(`KEY`) REFERENCES `A`(`IDKEY`) ON UPDATE NO ACTION ON DELETE NO ACTION )
2022-03-03 08:39:35.354 D/DBINFO: Component is sqlite_sequence Type is table SQL is 
        CREATE TABLE sqlite_sequence(name,seq)
2022-03-03 08:39:35.354 D/DBINFO: Component is room_master_table Type is table SQL is 
        CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
  • android_metadata is a table created by the android SQLite API
  • sqlite_sequence is a table created if ANY tables include the AUTOINCREMENT keyword
  • room_master_table is created by Room, it stores the hash which is generated according to the schema and is used for detecting a changed schema.

AND:-

2022-03-03 08:39:35.361 D/DBINFO: KEY = 1 IDKEY = ID001
2022-03-03 08:39:35.361 D/DBINFO: KEY = 2 IDKEY = ID002
2022-03-03 08:39:35.361 D/DBINFO: KEY = 3 IDKEY = ID003

Migration

Class B is changed to not have the Foreign Key constraint tying it to table *A :-

@Entity(
    tableName = "B"/*,
    foreignKeys = [ForeignKey(
        entity = A::class,
        parentColumns = ["IDKEY"],
        childColumns = ["KEY"],
        onDelete = NO_ACTION
    )]*/)
data class B(
    @PrimaryKey(autoGenerate = true)
    @NotNull
    @ColumnInfo(name = "KEY")
    var id: Int = 0,
    @ColumnInfo(name = "IDKEY")
    var parentIDId: String)

AllDao has to be changed to not reference tale A so:-

@Dao
abstract class AllDao {

    /* Will be commented out (deleted) for Version 2 */
    /*
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(a: A): Long
   
     */

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(b: B): Long
    @Query("SELECT * FROM B")
    abstract fun getAllFromB(): List<B>
}

TheDatabase change to Version 2 and to exclude table A :-

const val DATABASE_VERSION = 2 /*<<<<<<<<<< changed for version 2 */
@Database(entities = [/*A::class,*//*<<<<< NOTE will be commented out for Version 2*/B::class], exportSchema = false, version = DATABASE_VERSION)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    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")
                    .allowMainThreadQueries()
                    .addMigrations(MIGRATION_1_2)
                    .build()
            }
            return instance as TheDatabase
        }

        private val MIGRATION_1_2: Migration = object : Migration(1, 2) {
            override fun migrate(db: SupportSQLiteDatabase) {
                db.execSQL("DROP TABLE IF EXISTS B_temp")
                db.execSQL("CREATE TABLE IF NOT EXISTS B_temp AS SELECT * FROM B;")
                db.execSQL("DELETE FROM B")
                db.execSQL("DROP TABLE IF EXISTS A;")
                db.execSQL("DROP TABLE IF EXISTS B")
                db.execSQL("CREATE TABLE IF NOT EXISTS `B` (`KEY` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `IDKEY` TEXT NOT NULL)")
                db.execSQL("INSERT INTO B SELECT * FROM B_temp")
            }
        }
    }
}

MainActivity is changed so that A objects are not referenced:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    @SuppressLint("Range")
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        if (DATABASE_VERSION == 1) {

            /* Will be commented out for Version 2

            dao.insert(A("1"))
            dao.insert(A("2"))
            dao.insert(A("3"))

             */

            dao.insert(B(id = 1,parentIDId = "ID001"))
            dao.insert(B(id = 2,parentIDId = "ID002"))
            dao.insert(B(id = 3,parentIDId = "ID003"))
        }

        /* Write the schema to the log */
        val suppdb = db.openHelper.writableDatabase
        val csr = suppdb.query("SELECT * FROM sqlite_master")
        while (csr.moveToNext()) {
            Log.d("DBINFO",
                "Component is ${csr.getString(csr.getColumnIndex("name"))} " +
                        "Type is ${csr.getString(csr.getColumnIndex("type"))} " +
                        "SQL is \n\t${csr.getString(csr.getColumnIndex("sql"))}")
        }
        /* Write the data in table B to the log */
        for(b in dao.getAllFromB()) {
            Log.d("DBINFO","KEY = ${b.id} IDKEY = ${b.parentIDId}")
        }
    }
}

Result

After running with the above changes the the log includes:-

2022-03-03 08:53:06.016 D/DBINFO: Component is android_metadata Type is table SQL is 
        CREATE TABLE android_metadata (locale TEXT)
2022-03-03 08:53:06.016 D/DBINFO: Component is sqlite_sequence Type is table SQL is 
        CREATE TABLE sqlite_sequence(name,seq)
2022-03-03 08:53:06.016 D/DBINFO: Component is room_master_table Type is table SQL is 
        CREATE TABLE room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)
2022-03-03 08:53:06.017 D/DBINFO: Component is B_temp Type is table SQL is 
        CREATE TABLE B_temp("KEY" INT,IDKEY TEXT)
2022-03-03 08:53:06.017 D/DBINFO: Component is B Type is table SQL is 
        CREATE TABLE `B` (`KEY` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `IDKEY` TEXT NOT NULL)
  • as can be seen no table A and table B has been changed accordingly (not Foreign Key constraints)

AND The data has been preserved:-

2022-03-03 08:53:06.023 D/DBINFO: KEY = 1 IDKEY = ID001
2022-03-03 08:53:06.023 D/DBINFO: KEY = 2 IDKEY = ID002
2022-03-03 08:53:06.023 D/DBINFO: KEY = 3 IDKEY = ID003
MikeT
  • 51,415
  • 16
  • 49
  • 68