Trying the new relations in Room . Why My insertion suddenly stopped?
from the CommonsWare Community archivesAt June 18, 2020, 7:59am, root-ansh asked:
I have 2 tables MenuItem and MenuCategory . Earlier i was using a single table to represent either of them, but while migration to kotlin, i thought about giving 3 tables with many to many a try. So i am basically trying to get a many to may relation running.
Initially when i had not added any sort of “relation” (i.e the 2 tables being seperate), all the insertion, deleteion and getAll was working. But after adding relation entities(i.e 3 new entities: 1 is the joiner, while the other 2 will be for retrieving data, similar to your room book’s tutorial. But when i started , testing , all the methods failed and furthermore there was an error saying foreign key constraint failed (code 787 sqlite_constraint_foreignkey)
why?
Main Entities : MenuItem and MenuCategory
@Entity(tableName = "menu_cat")
data class MenuCategory(
@ColumnInfo(name = "cat_id") @PrimaryKey
val id: String = UUID.randomUUID().toString().substring(0, 16),
@ColumnInfo(name = "cat_name")
var name: String
) {
constructor(catName: String) : this(name = catName)
override fun toString(): String {
return "MenuCategory(id='$id', name='$name')"
}
}
@Entity(tableName = "menu_item")
data class MenuItem(
@ColumnInfo(name = "item_id") @PrimaryKey
var id: String = UUID.randomUUID().toString().substring(0, 16),
@ColumnInfo(name = "item_name")
var itemName: String,
@ColumnInfo(name = "p_half")
var priceHalf: Int,
@ColumnInfo(name = "p_full")
var priceFull: Int
) {
constructor(name: String, pHalf: Int, pFull: Int) : this(itemName = name, priceHalf = pHalf, priceFull = pFull)
constructor(itemName: String, priceTotal: Int) : this(itemName = itemName, priceHalf = -1, priceFull = priceTotal)
override fun toString(): String {
return "Item{" +
"id='$id', " +
"itemName='$itemName', " +
"priceHalf=$priceHalf, " +
"priceFull=$priceFull" +
"}"
}
}
relation entities : ItemCategoryRelation & MenuCategoryWithItems
@Entity(
tableName = "item_cat_relation",
primaryKeys = ["cat_id", "item_id"],
indices = [Index("cat_id"), Index("item_id")],
foreignKeys = [
ForeignKey(entity = MenuCategory::class, parentColumns = ["cat_id"], childColumns = ["cat_id"]),
ForeignKey(entity = MenuItem::class, parentColumns = ["item_id"], childColumns = ["item_id"])
]
)
data class ItemCategoryRelation(
val cat_id: String, //warning(?) :exact names as parent columns
val item_id: String
) {
override fun toString() = "ItemCategoryRelation(categor_id='$cat_id', i_id='$item_id')"
}
data class MenuCategoryWithItems(
@Embedded
val category: MenuCategory, //parent object
@Relation(
parentColumn = "cat_id",
entityColumn = "item_id",
associateBy = Junction(
value = ItemCategoryRelation::class, parentColumn = "cat_id", entityColumn = "item_id"
)
)
val items: List<MenuItem>
)
DAO
@Dao
interface MenuActionsDao {
//insert,delete ,getall for Menu ITEM
@Insert()
fun insertMenuItems(vararg items: MenuItem)
@Query("DELETE FROM menu_item WHERE item_id = :id") //order by??
fun deleteMenuItemByID(id: String)
@Query("SELECT * FROM menu_item")
fun getAllMenuItems(): List<MenuItem>
//insert,delete ,getall for Menu CATEGORY
@Insert()
fun insertMenuCategories(vararg categories: MenuCategory)
@Query("DELETE FROM menu_cat WHERE cat_id = :id")
fun deleteMenuCategoryByID(id: String)
@Query("SELECT * FROM menu_cat")
fun getAllMenuCategories(): List<MenuCategory>
//relational queries
@Insert
fun insertItemCategoryRelation(vararg rel: ItemCategoryRelation)
@Transaction
@Query("SELECT * FROM menu_cat")
fun getAllMenuCategoriesWithItems(): List<MenuCategoryWithItems>
}
db MenuDB2 (newer db)
@Database(
entities = [,MenuItem::class,ItemCategoryRelation::class, MenuCategory::class],
version = 1
/* ,exportSchema = false*/
)
abstract class MenuDB2 : RoomDatabase() {
abstract val getMenuActionsDao: MenuActionsDao?
companion object {
private var INSTANCE: MenuDB2? = null
private const val DB_NAME = "MENU.db"
@JvmStatic @Synchronized
fun getInstance(context: Context, debug:Boolean=false): MenuDB2 {
if(debug){
return Room.inMemoryDatabaseBuilder(context,MenuDB2::class.java).build()
}
if (INSTANCE == null) {
INSTANCE =
Room
.databaseBuilder(context, MenuDB2::class.java, DB_NAME)
.fallbackToDestructiveMigration().build()
}
return INSTANCE!!
}
}
}
Some Utility Functions:
fun getRandomMenuCategory() = MenuCategory("Category${System.currentTimeMillis()}".substring(0, 12))
fun getRandomMenuItem2Price(): MenuItem {
val rnd = Random.nextInt(0, 1000) % 200
return MenuItem(
"Category $rnd", "name $rnd", rnd, rnd + 50
)
}
fun getRandomMenuItem1Price(): MenuItem {
val rnd = Random.nextInt(0, 1000) % 200
return MenuItem(
"name $rnd", rnd + 50
)
}
Instrumented test runner:
@RunWith(AndroidJUnit4::class)
class MenuDbAndDaoTest {
private var menuDb :MenuDB2? = null
private var tableDao : MenuActionsDao? =null
@Before
fun initDb(){
menuDb = MenuDB2.getInstance(InstrumentationRegistry.getInstrumentation().context,debug = true)
tableDao = menuDb?.getMenuActionsDao
}
@Test
fun test() {
val cat1 = getRandomMenuCategory()
val cat2 = getRandomMenuCategory()
val cat3 = getRandomMenuCategory()
val itm1 = getRandomMenuItem1Price()
val itm2 = getRandomMenuItem2Price()
val itm3 = getRandomMenuItem2Price()
loggy(cat1)
loggy(cat2)
loggy(cat3)
loggy(itm1)
loggy(itm2)
loggy(itm3)
tableState()
loggy("=========insert Test================================================")
//insert Test
tableDao?.insertMenuItems(itm1)
tableDao?.insertMenuItems(itm2)
tableDao?.insertMenuItems(itm3)
tableState()
loggy("---------------------------------------")
tableDao?.insertMenuCategories(cat1)
tableDao?.insertMenuCategories(cat2)
tableDao?.insertMenuCategories(cat3)
tableState()
loggy("---------------------------------------")
tableDao?.insertItemCategoryRelation(ItemCategoryRelation(cat1.id,itm1.id))
tableDao?.insertItemCategoryRelation(ItemCategoryRelation(cat2.id,itm2.id))
tableDao?.insertItemCategoryRelation(ItemCategoryRelation(cat3.id,itm3.id))
tableState()
loggy("===========delete test================================================")
//delete
tableDao?.deleteMenuCategoryByID(cat1.id)
tableState()
tableDao?.deleteMenuCategoryByID(cat2.id)
tableState()
tableDao?.deleteMenuCategoryByID(cat3.id)
tableState()
tableDao?.deleteMenuItemByID(cat1.id)
loggy("====================================================================")
}
private fun tableState() {
loggy("GET ALL CATEGORIES:")
tableDao?.getAllMenuCategories()?: listOf("Empty menu categories").forEach{loggy(it)}
loggy("GET ALL ITEMS:")
tableDao?.getAllMenuItems()?: listOf("Empty items").forEach{loggy(it)}
loggy("GET ALL CATEGORIES WITH ITEMS")
tableDao?.getAllMenuCategoriesWithItems()?: listOf("Empty categories with items").forEach{loggy(it)}
}
private fun loggy(obj:Any ) = Log.e("MenuDbTest>>>", "$obj" )
@After
fun closeDb(){
menuDb?.close()
}
}
At June 18, 2020, 11:13am, mmurphy replied:
MenuCategory and MenuItem are data classes. I recommend getting rid of your equals() and hashCode() overrides (and perhaps toString()), as those get supplied for you automatically with a data class. Similarly, ItemCategoryRelation is a data class — you get a toString() implementation “for free”.
I would be interested in knowing the details of this comment: //warning(?) :exact names as parent columns.
Beyond that, I would need a stack trace for a failing test to provide further advice.
At June 18, 2020, 10:22pm, root-ansh replied:
@mmurphy apologies for the late reply, i am having a disturbed sleep routine. I removed the equals() and hashcode() from the data classes along with some minor refactoring, and also updated the original classes in the question( so kindly check them again 
) Keeping the string override because the objects were not showing their data i logs when i tried without .
Apart from that, i added that comment because i was not sure if the names used for variables in the ItemCategoryRelation class has to be same as the columns it is making a relation in ? I would like to know from you.
And about the sack trace, i am currently not running the db in my app but rather testing it via instrument test, so the above(Updated) runner is for that. When i run the tests , it shows failure at 2 places in runtime, inside my logcat (courtesy of my log.e statements) and in the usual run trace. So both are here:
logcat error trace:
32209-32209 E/ools.menu_make: Unknown bits set in runtime_flags: 0x8000
32209-32241 E/Perf: Fail to get file list in.curioustools.menu_maker
32209-32241 E/Perf: getFolderSize() : Exception_1 = java.lang.NullPointerException: Attempt to get length of null array
32209-32240 E/MenuDbTest>>>: MenuCategory(id='210baed3-3320-4c', name='Category1592')
32209-32240 E/MenuDbTest>>>: MenuCategory(id='5ec3c530-44a0-44', name='Category1592')
32209-32240 E/MenuDbTest>>>: MenuCategory(id='afa0e296-0c6d-44', name='Category1592')
32209-32240 E/MenuDbTest>>>: Item{id='2476bc95-041c-40', itemName='name 128', priceHalf=-1, priceFull=178}
32209-32240 E/MenuDbTest>>>: Item{id='Category 175', itemName='name 175', priceHalf=175, priceFull=225}
32209-32240 E/MenuDbTest>>>: Item{id='Category 34', itemName='name 34', priceHalf=34, priceFull=84}
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES:
32209-32240 E/MenuDbTest>>>: GET ALL ITEMS:
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES WITH ITEMS
32209-32240 E/MenuDbTest>>>: =========insert Test================================================
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES:
32209-32240 E/MenuDbTest>>>: GET ALL ITEMS:
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES WITH ITEMS
32209-32240 E/MenuDbTest>>>: ---------------------------------------
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES:
32209-32240 E/MenuDbTest>>>: GET ALL ITEMS:
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES WITH ITEMS
32209-32240 E/MenuDbTest>>>: ---------------------------------------
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES:
32209-32240 E/MenuDbTest>>>: GET ALL ITEMS:
32209-32240 E/MenuDbTest>>>: GET ALL CATEGORIES WITH ITEMS
32209-32240 E/MenuDbTest>>>: ===========delete test================================================
32209-32240 E/TestRunner: failed: test(in.curioustools.menu_maker.MenuDbAndDaoTest)
32209-32240 E/TestRunner: ----- begin exception -----
32209-32240 E/TestRunner: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:831)
at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeUpdateDelete(FrameworkSQLiteStatement.java:46)
at in.curioustools.menu_maker.modal.MenuActionsDao_MenuDB2_Impl.deleteMenuCategoryByID(MenuActionsDao_MenuDB2_Impl.java:183)
at in.curioustools.menu_maker.MenuDbAndDaoTest.test(MenuDbAndDaoTest.kt:80)
at java.lang.reflect.Method.invoke(Native Method)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at androidx.test.internal.runner.junit4.statement.RunBefores.evaluate(RunBefores.java:80)
at androidx.test.internal.runner.junit4.statement.RunAfters.evaluate(RunAfters.java:61)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at androidx.test.ext.junit.runners.AndroidJUnit4.run(AndroidJUnit4.java:104)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
at androidx.test.internal.runner.TestExecutor.execute(TestExecutor.java:56)
at androidx.test.runner.AndroidJUnitRunner.onStart(AndroidJUnitRunner.java:392)
at android.app.Instrumentation$InstrumentationThread.run(Instrumentation.java:2202)
32209-32240 E/TestRunner: ----- end exception -----
run error trace :
Testing started at 03:14 ...
06/19 03:14:24: Launching 'MenuDbAndDaoTest' on Physical Device.
Running tests
$ adb shell am instrument -w -m -e debug false -e class 'in.curioustools.menu_maker.MenuDbAndDaoTest' in.curioustools.menu_maker.test/androidx.test.runner.AndroidJUnitRunner
Connected to process 32209 on device 'xiaomi-mi_a2-47d44963'.
Started running tests
android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:831)
at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeUpdateDelete(FrameworkSQLiteStatement.java:46)
at in.curioustools.menu_maker.modal.MenuActionsDao_MenuDB2_Impl.deleteMenuCategoryByID(MenuActionsDao_MenuDB2_Impl.java:183)
at in.curioustools.menu_maker.MenuDbAndDaoTest.test(MenuDbAndDaoTest.kt:80)
at java.lang.reflect.Method.invoke(Native Method)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at androidx.test.internal.runner.junit4.statement.RunBefores.evaluate(RunBefores.java:80)
at androidx.test.internal.runner.junit4.statement.RunAfters.evaluate(RunAfters.java:61)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at androidx.test.ext.junit.runners.AndroidJUnit4.run(AndroidJUnit4.java:104)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
at androidx.test.internal.runner.TestExecutor.execute(TestExecutor.java:56)
at androidx.test.runner.AndroidJUnitRunner.onStart(AndroidJUnitRunner.java:392)
at android.app.Instrumentation$InstrumentationThread.run(Instrumentation.java:2202)
Tests ran to completion.
To me it looks like the insertion and get all are not working correctly, therefore the tablestate() function produced empty lists for them (but not the default error message that would have been produced if the dao instance was itself null ) and when i try to do any delete operation, it throws error simply because the items don’t really exist
At June 18, 2020, 10:51pm, mmurphy replied:
No. Your ForiegnKey annotations provide the mapping, so you can use whatever names you want. I interpreted the comment as meaning you were getting an IDE or compiler warning.
You should not get a foreign key constraint violation for trying to delete from an empty table, though.
It is not clear to me where your problem is coming from. While instrumented tests are wonderful… for situations like this, I like having an actual database that I can examine in a SQLite client. If I were in your position, I would have some app code instantiate the MenuDB2 database and insert some items, akin to your test code. Then, examine the database that you get and see whether your rows are what you expect.
At June 19, 2020, 5:29am, root-ansh replied:
Hi mark, so as i said in the office hours, i was able to figure out the issue that was the usage of foreign keys . I guess you will be looking at that. Let me know if you need any file regarding my test case. i will be integrating the database with my actual app and see what happens.
But now i have some side questions
1.
I wanted to know if there is a way to perform the insert functions as a single query? Like i pass a obj as SomeClass(cat1, {itm1,itm2}) and automatically it inserts/updates the category, items and their associated relations?
I can think of a way to do it from my repository( ie calling insert for 5 times for such object: 1 for category , 2 for entries, 2 for relations) , but is that possible inside the dao, via @Transaction or some abstract class concept?
2 is there a way to somehow see the queries that these @Insert or @Delete Methods generate for us ?
3 in the query :
//new
@Transaction
@Query("SELECT * FROM menu_item")
fun getAllMenuItemsWithCategory(): List<MenuItemWithCategories>
we are actually performing a getall on table menu_item but due to magic(data’s return type and @Transaction i guess?) the room is able fetch the complete list of objects [ {item1,[cat1,cat2,cat3]}, {item2,[cat1,cat2]}, {item3,[cat3] } ] . but (a) what if i want to get only a paticular list of objects?like only list of items which are related to cat3 (i.e [ {item1,[cat1,cat2,cat3]}, {item3,[cat3] } ] . this i believe is a kind of join query, but am not too good with that concept and (b) what if I wanted to use the category’s relation to fetch items(like in previous part) but not fetch all the other categories, i.e just recieve [ {item1}, {item3}]. in terms of code, i want to figure out the syntax.queries of following dao methods:
fun getAllItems_JustTheItem_HavingCategoryID(catID: String): List<MenuItem>
fun getAllItems_Complete_HavingCategoryID(catID: String): List<MenuItemWithCategories>
fun getAllCategories_JustTheCategory_HavingItemID(catID: String): List<MenuCategory>
fun getAllCategories_Complete_HavingItemID(catID: String): List<MenuCategoryWithItems>
// from question 1
fun insertItemAndAssocCategories(itemWithCategories: MenuItemWithCategories)
fun insertCategoryAndAssocItems(categoryWithItems: MenuCategoryWithItems)
4 I believe I should test this out first, but just having a quick check here, these nested queries (The one returning list<MenuItemWithCategories> and likewise classes) are safe to use with with livedata, pager and other reactive stuff, right?
At June 19, 2020, 10:57pm, mmurphy replied:
I stand by my use of foreign keys. The one thing that I failed to include in my M:N example in Elements of Android Room is an onDelete property in the @ForeignKey annotations:
@Entity(
primaryKeys = ["isbn", "shortCode"],
indices = [Index("isbn"), Index("shortCode")],
foreignKeys = [
ForeignKey(
entity = Book::class,
parentColumns = arrayOf("isbn"),
childColumns = arrayOf("isbn"),
onDelete = CASCADE
), ForeignKey(
entity = Category::class,
parentColumns = arrayOf("shortCode"),
childColumns = arrayOf("shortCode"),
onDelete = CASCADE
)
]
)
data class BookCategoryJoin(
val isbn: String,
val shortCode: String
)
That basically says “when you delete a Book or Category, delete its associated BookCategoryJoin entities”. I then added delete() functions to the DAO and some delete test code to the instrumentation test, and it all works fine.
Create your own DAO function that inserts the individual items and creates the necessary join entities. In the context of my example from the book, you would need to change Bookstore to be an abstract class, then use something like:
@Insert
abstract suspend fun insertAll(joins: List<BookCategoryJoin>)
open suspend fun insertAll(category: Category, vararg books: Book) {
save(category)
save(*books)
insertAll(books.map { BookCategoryJoin(isbn = it.isbn, shortCode = category.shortCode) })
}
Unfortunately, @Transaction does not work here — I think there are some code-generation reasons for this. I will need to investigate that more sometime, as I recall running into this before and forget the solution.
All the generated code will be in build/generated/source/kapt/ in your module. Warning: it’s not designed to be very readable.
You would need a suitable WHERE clause to restrict your query.
Your query would be against the menu_item table, with two JOIN clauses to pull in menu_cat and item_cat_relation via your relationships, then a WHERE clause to identify the category that you want.
If you are going to use SQLite in a sophisticated fashion like this, with a light object wrapper like Room or SQLDelight, you need to learn SQL. Conversely, if you do not want to learn SQL, you may be better served by an ORM that hides those details from you, or by using an object database instead of SQLite.