Android. Weird behavior when deleting a column in room (Foreign key)-CodePudding

I have an android note pet project with room database where I store files and related pages to the file. One to many relationship:

@Entity(tableName = NotepadDatabase.FILES_TABLE)
data class RoomNoteFile(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(name = "file_name") val name: String,
    @ColumnInfo(name = "number_of_pages") val pagesAmount: Int,
)

@Entity(
    tableName = NotepadDatabase.PAGES_TABLE,
    foreignKeys = [ForeignKey(
        entity = RoomNoteFile::class,
        parentColumns = arrayOf("id"),
        childColumns = arrayOf("related_file"),
        onDelete = ForeignKey.CASCADE
    )]
)
data class RoomNotePage(
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    @ColumnInfo(name = "page_text") val text: String,
    @ColumnInfo(name = "page_number") val pageNum: Int,
    @ColumnInfo(name = "related_file", index = true) val fileId: Int,
)

So I noticed that if I delete a file, instead of deleting the file's pages with it (as i expect it to work). Room changes the ids (related_file id) of all the pages of this file and gives them the id of the next file in database, then all the pages shift and the last file's pages get deleted. I can't imagine what might cause that.

CodePudding user response：

From the supplied code, there is not an issue. So your issue is within other than the supplied code. You may wish to edit your question. However, you should certainly investigate the other code. Perhaps consider the following code to get some ideas that could help you pinpoint the issue(s)

As an example consider the following test/demo that uses the code you have supplied plus:-

NotePadDatabaseDao (i.e. Dao's for inserting, deleting and extracting):-

@Dao
interface NotepadDatabaseDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(noteFile: RoomNoteFile): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(notePage: RoomNotePage): Long

    @Delete
    fun delete(noteFile: RoomNoteFile): Int
    @Delete
    fun delete(notePage: RoomNotePage): Int

    @Query("SELECT * FROM ${NotepadDatabase.FILES_TABLE}")
    fun getAllFiles(): List<RoomNoteFile>
    @Query("SELECT * FROM ${NotepadDatabase.PAGES_TABLE} WHERE related_file=:fileId")
    fun getPagesForAFileId(fileId: Int): List<RoomNotePage>
}

NotePadDatabase a pretty stock @Database annotated class BUT allows the testing to be run on the main thread (perhaps running on other threads may be an issue, something to check/consider):-

@Database(entities = [RoomNoteFile::class, RoomNotePage::class], version = NotepadDatabase.DATABASE_VERSION, exportSchema = false)
abstract class NotepadDatabase: RoomDatabase() {
    abstract fun getInstance(): NotepadDatabaseDao

    companion object {
        const val FILES_TABLE = "file"
        const val PAGES_TABLE = "page"
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "notepad.db"

        @Volatile
        private var instance: NotepadDatabase? = null
        fun getInstance(context: Context): NotepadDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,NotepadDatabase::class.java,NotepadDatabase.DATABASE_NAME)
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as NotepadDatabase
        }
    }
}

To test, MainActivity, this inserts some data (Files and then Related pages (3 files each with 3 related pages)), extracts the data and outputs it to the log, also deleting 1 File (ID=1), then extracts the changed data outputting it to the log :-

class MainActivity : AppCompatActivity() {
    lateinit var db: NotepadDatabase
    lateinit var dao: NotepadDatabaseDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = NotepadDatabase.getInstance(this)
        dao = db.getInstance()

        val f1id = dao.insert(RoomNoteFile(0,"File1",0))
        val f2id = dao.insert(RoomNoteFile(0,"File2",1))
        val f3id = dao.insert(RoomNoteFile(0,"File3",2))

        val p1id = dao.insert(RoomNotePage(0,"F1Page1",1,f1id.toInt()))
        val p2id = dao.insert(RoomNotePage(0,"F1Page2",2,f1id.toInt()))
        val p3id = dao.insert(RoomNotePage(0,"F1Page3",3,f1id.toInt()))
        dao.insert(RoomNotePage(0,"F2Page1",1,f2id.toInt()))
        dao.insert(RoomNotePage(0,"F2Page2",2,f2id.toInt()))
        dao.insert(RoomNotePage(0,"F2Page3",3,f2id.toInt()))
        dao.insert(RoomNotePage(0,"F3Page1",1,f3id.toInt()))
        dao.insert(RoomNotePage(0,"F3Page2",2,f3id.toInt()))
        dao.insert(RoomNotePage(0,"F3Page3",3,f3id.toInt()))

        for (rnf in dao.getAllFiles()) {
            logFile(rnf,"_STEP1")
            if (rnf.id == 1) {
                dao.delete(rnf)
            }
        }
        for (rnf in dao.getAllFiles()) {
            logFile(rnf,"_STEP2")
        }
    }

    fun logFile(roomNoteFile: RoomNoteFile,suffix: String) {
        Log.d("NPDBINFO$suffix","NAME=${roomNoteFile.name} ID=${roomNoteFile.id} AMOUNT=${roomNoteFile.pagesAmount} ${getFilePagesForLogging(roomNoteFile)}")
    }

    fun getFilePagesForLogging(roomNoteFile: RoomNoteFile): String {
        val sb: StringBuilder = java.lang.StringBuilder()
        for (page: RoomNotePage in dao.getPagesForAFileId(roomNoteFile.id.toInt())) {
            sb.append("\n\tID=${page.id} TEXT=${page.text} PAGE#=${page.pageNum} FILEID=${page.fileId}")
        }
        if (sb.isEmpty()) {
            sb.append("No Pages Found")
        }
        return sb.toString()
    }
}

The resultant output being :-

2022-05-27 05:16:23.256 D/NPDBINFO_STEP1: NAME=File1 ID=1 AMOUNT=0 
        ID=1 TEXT=F1Page1 PAGE#=1 FILEID=1
        ID=2 TEXT=F1Page2 PAGE#=2 FILEID=1
        ID=3 TEXT=F1Page3 PAGE#=3 FILEID=1
2022-05-27 05:16:23.259 D/NPDBINFO_STEP1: NAME=File2 ID=2 AMOUNT=1 
        ID=4 TEXT=F2Page1 PAGE#=1 FILEID=2
        ID=5 TEXT=F2Page2 PAGE#=2 FILEID=2
        ID=6 TEXT=F2Page3 PAGE#=3 FILEID=2
2022-05-27 05:16:23.260 D/NPDBINFO_STEP1: NAME=File3 ID=3 AMOUNT=2 
        ID=7 TEXT=F3Page1 PAGE#=1 FILEID=3
        ID=8 TEXT=F3Page2 PAGE#=2 FILEID=3
        ID=9 TEXT=F3Page3 PAGE#=3 FILEID=3
        
        
2022-05-27 05:16:23.262 D/NPDBINFO_STEP2: NAME=File2 ID=2 AMOUNT=1 
        ID=4 TEXT=F2Page1 PAGE#=1 FILEID=2
        ID=5 TEXT=F2Page2 PAGE#=2 FILEID=2
        ID=6 TEXT=F2Page3 PAGE#=3 FILEID=2
2022-05-27 05:16:23.265 D/NPDBINFO_STEP2: NAME=File3 ID=3 AMOUNT=2 
        ID=7 TEXT=F3Page1 PAGE#=1 FILEID=3
        ID=8 TEXT=F3Page2 PAGE#=2 FILEID=3
        ID=9 TEXT=F3Page3 PAGE#=3 FILEID=3

i.e. As far as from the File being deleted that has worked and no Page relationships have changed to belong to another File.

From the database aspect, via App Inspection, it can be seen that the pages are as they should be. i.e. only 6 rows remain and 3 are related to File2 and 3 are related to File3:-

i.e. The CASCADE has done what is expected

As such I believe that you need to look for the issue in other that the code you have provided.

CodePudding user response：

I found the issue, it's just view pager rewrites the values of all data that was after the deleted file