I have a menu that can have multiple Sections. I have passed the menuID of a ShopMenuEntity as foreign key in ShopSectionEntity with name fk_menu_id.
Both id's have to be autoGenerated in ShopMenu and ShopSection.
ShopMenuEntity.kt
@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = COLUMN_ID)
var menuID: Int = 0,
var level:Int? = null,
@ColumnInfo(name = COLUMN_PARENT_ID)
var parentId: Int = -1,
) {
companion object {
const val TABLE_NAME = "shop_menu_table"
const val COLUMN_ID = "menu_id"
const val COLUMN_PARENT_ID = "parentId"
fun mapHttpResponse(subMenu: NewShopMenuResponse,parentId: Int): ShopMenuEntity {
return ShopMenuEntity(
// below menuID should be auto incremented
///menuID = subMenu.id ?: -1,
level = subMenu.level,
parentId = parentId,
)
}
}
}
ShopSectionEntity.kt
@Entity(tableName = ShopSectionEntity.TABLE_NAME,
foreignKeys = [ForeignKey(
entity = ShopMenuEntity::class,
parentColumns = arrayOf(ShopMenuEntity.COLUMN_ID),
childColumns = arrayOf(ShopSectionEntity.SHOP_MENU_ID),
onDelete = ForeignKey.CASCADE
)],
indices = [Index(ShopSectionEntity.SHOP_MENU_ID)])
data class ShopSectionEntity(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = COLUMN_ID)
var sectionID: Int= 0,
@ColumnInfo(name = SHOP_MENU_ID)
var shopMenuID: Int,
) {
companion object {
const val TABLE_NAME = "shop_section_table"
const val COLUMN_ID = "section_id"
const val SHOP_MENU_ID = "fk_menu_id"
fun mapHttpResponse(section:Section,shopMenuID:Int,orderIndex:Int):ShopSectionEntity {
return ShopSectionEntity(
// sectionID should be auto incremented
shopMenuID = shopMenuID,
)
}
}
}
Relattion ShopMenuDB.kt
data class ShopMenuDB(
@field:Embedded
var shopEntity: ShopMenuEntity,
@field:Relation(parentColumn = ShopMenuEntity.COLUMN_ID, entityColumn = ShopSectionEntity.SHOP_MENU_ID, entity = ShopSectionEntity::class)
var sections: List<ShopSectionEntity>,
)
RoomActivity.kt
class RoomActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_room)
CoroutineScope(Dispatchers.IO).launch{
readDataFromRaw()?.let {
addSubMenu(shopMenuDao(), it, -1)
}
}
}
private fun shopMenuDao() = Room.databaseBuilder(
applicationContext,
MatasDatabase::class.java, "matas_database"
).build().newShopMenuDao()
private fun readDataFromRaw():NewShopMenuResponse?{
val jsonText = resources.openRawResource(R.raw.respons)
.bufferedReader().use { it.readText() }
val moshi: Moshi = Moshi.Builder().build()
val jsonAdapter = moshi.adapter(
NewShopMenuResponse::class.java
)
val shopResponse = jsonAdapter.fromJson(jsonText)
return shopResponse
}
private suspend fun addSubMenu(
shopMenuDao: NewShopMenuDao,
subMenu: NewShopMenuResponse,
parentID: Int
) {
val dbRootMenus = ArrayList<ShopMenuEntity>()
val dbSections = ArrayList<ShopSectionEntity>()
val mSubMenu = ShopMenuEntity.mapHttpResponse(subMenu, parentID)
dbRootMenus.add(mSubMenu)
subMenu.sections?.forEachIndexed { sectionIndex, mSection ->
val sectionEntity = ShopSectionEntity.mapHttpResponse(mSection, mSubMenu.menuID,sectionIndex + 1)
dbSections.add(sectionEntity)
}
///shopMenuDao.insertAllLists(dbRootMenus, dbSections)
// I tried both above and below way to save menu and sections
shopMenuDao.insertShopMenu(dbRootMenus[0])
shopMenuDao.insertSections(dbSections)
}
}
NewShopMenuDao.kt
@Dao
interface NewShopMenuDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAllLists(shopMenuEntityList: List<ShopMenuEntity>, shopSectionEntities: List<ShopSectionEntity>?)
@Transaction
@Query("SELECT * FROM shop_menu_table WHERE shop_menu_table.parentId = :parentId")
fun getShopMenu(parentId:Int): Flow<ShopMenuDB?>
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertShopMenu(shopMenuEntity: ShopMenuEntity)
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertSections(shopSectionEntities: List<ShopSectionEntity>?)
@Query("SELECT * FROM ${ShopMenuEntity.TABLE_NAME}")
fun getAll(): List<ShopMenuEntity>
@Insert
fun insertAll(vararg shopMenuEntity: ShopMenuEntity)
@Query("DELETE FROM ${ShopMenuEntity.TABLE_NAME}")
fun deleteAll()
@Delete
fun delete(shopMenuEntity: ShopMenuEntity)
}
I find after debug that menuId in ShopMenuEntity and sectionID in ShopSectionEntity is not auto incrementing.
I tried two different approaches to save data but was not successful.
Exception
SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY[787])
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:1127)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.kt:42)
at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.kt:85)
at matas.matas.core.data.db.dao.NewShopMenuDao_Impl$8.call(NewShopMenuDao_Impl.java:164)
at matas.matas.core.data.db.dao.NewShopMenuDao_Impl$8.call(NewShopMenuDao_Impl.java:159)
at androidx.room.CoroutinesRoom$Companion$execute$2.invokeSuspend(CoroutinesRoom.kt:65)
at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
at androidx.room.TransactionExecutor.execute$lambda$1$lambda$0(TransactionExecutor.kt:36)
at androidx.room.TransactionExecutor.$r8$lambda$AympDHYBb78s7_N_9gRsXF0sHiw(Unknown Source:0)
at androidx.room.TransactionExecutor$$ExternalSyntheticLambda0.run(Unknown Source:4)