Auto Increment not working automatically in child Table Room Database

Auto increment in child Table in Room not auto incrementing

menuID is set from api whereas sectionID and itemID should be auto incremented. Now everything working fine but I need to know why I need sectionIterator and itemIterator to pass auto increment entries to ShopSectionEntity and ShopSectionItemEntity Table

See addSubMenu function below

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,
    var headline: String? = null,
    var styling: String? = null,
) {
    companion object {

        const val TABLE_NAME = "shop_menu_table"
        const val COLUMN_ID = "menu_id"
        const val COLUMN_PARENT_ID = "parentId"

        fun mapHttpResponse(subMenu: ShopMenuResponse,parentId: Int): ShopMenuEntity {
            return ShopMenuEntity(
                menuID = subMenu.id ?: -1,
                level = subMenu.level,
                parentId = parentId,
                headline = subMenu.headline,
                styling = subMenu.styling
            )
        }
    }
}

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,
    var order:Int = 0,

    var headline: String? = null,
    var styling: String? = null,
) {

    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:NetworkSection,shopMenuID:Int,autoSectionID:Int,orderIndex:Int):ShopSectionEntity {

            return ShopSectionEntity(
                sectionID = autoSectionID,
                shopMenuID = shopMenuID,
                order = orderIndex,

                headline = section.headline,
                styling = section.styling
            )
        }
    }
}

ShopSectionItemEntity.kt

@Entity(
    tableName = ShopSectionItemEntity.TABLE_NAME,
    foreignKeys = [ForeignKey(
        entity = ShopSectionEntity::class,
        parentColumns = arrayOf(ShopSectionEntity.COLUMN_ID),
        childColumns = arrayOf(ShopSectionItemEntity.SECTION_ID),
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(ShopSectionItemEntity.SECTION_ID)]
)
data class ShopSectionItemEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    val autoItemID : Int = 0,
    @ColumnInfo(name = SECTION_ID)
    var sectionID: Int,
    @ColumnInfo(name = ITEM_PARENT_MENU_ID)
    var menuID:Int,
    @ColumnInfo(name = ITEM_CHILD_MENU_ID)
    var subMenuID:Int?,

    ) :Serializable {
    companion object {
        const val TABLE_NAME = "shop_section_item_table"
        const val COLUMN_ID = "item_id"
        const val SECTION_ID = "fk_section_id"
        const val ITEM_PARENT_MENU_ID = "item_parent_menu_id"
        const val ITEM_CHILD_MENU_ID = "item_child_menu_id"

        fun mapHttpResponse(item: NetworkItem, sectionID: Int, mID:Int,autoID:Int,itemOrderIndex:Int) = ShopSectionItemEntity(
            autoItemID = autoID,
            sectionID = sectionID,
            menuID = mID,
            subMenuID = item.content?.subMenu?.id,
        )
    }
    fun hasSubMenu() = subMenuID != null
}

Detail code of my implementation is in another question

Edit Hanldling Retrofit Response

if (response.isSuccessful && response.body() != null) {
       val body = response.body()

       body?.let {
                shopMenuDao.deleteAll()
                addSubMenu(shopMenuDao, body, -1)// -1 for top most parent
      }     
 }

AddSubMenu function

private var itemIterator = 0
private var sectionIterator = 0;
private suspend fun addSubMenu( 
shopMenuDao: NewShopMenuDao,
subMenu: ShopMenuResponse,
parentID: Int) {
   val dbRootMenus = ArrayList<ShopMenuEntity>()
   val dbSections = ArrayList<ShopSectionEntity>()
   val dbShopItems = ArrayList<ShopSectionItemEntity>()

  if(parentID == -1){
    itemIterator = 0
    sectionIterator = 0
  }
  val mSubMenu = ShopMenuEntity.mapHttpResponse(subMenu,parentID)
  dbRootMenus.add(mSubMenu)
  subMenu.sections?.forEachIndexed { sectionIndex, mSection ->
  val sectionEntity = ShopSectionEntity.mapHttpResponse(mSection, mSubMenu.menuID, ++sectionIterator,sectionIndex)
  dbSections.add(sectionEntity)
  mSection.items?.forEachIndexed { itemIndex, mItem ->
  val itemEntity = ShopSectionItemEntity.mapHttpResponse(mItem, sectionEntity.sectionID,mSubMenu.menuID, ++itemIterator,itemIndex)
        dbShopItems.add(itemEntity)
        mItem.content?.subMenu?.let {
            addSubMenu(shopMenuDao, it, itemEntity.autoItemID)
        }
    }
}
   shopMenuDao.insertAllLists(dbRootMenus, dbSections,dbShopItems)

}

Solution

autoGenerate = true is including the AUTOINCREMENT keyword, which results in SQLite (the Database Manager that Room is a wrapper around) generating the column's value in the absence of a value being given. It does not generate values in the objects which appears to be your expectation.

Note that AUTOINCREMENT is not what causes the generation of the value. Any column that is defined as INTEGER (Int Long etc in Room with Kotlin) and is the PRIMARY KEY, will generate the value in the absence of a value being supplied. What AUTOINCREMENT does is add a constraint (rule) that says that the value must be greater than any value ever assigned to the column. It is actually rarely if ever needed and is inefficient as it utilises an additional system table (sqlite_sequence - see the query run via App Inspection at the end) to store the highest value ever assigned.

Room, when inserting considers 0 as indicating no value (if autoGenerate=true is used (0 is considered a value otherwise and it is null that is then considered as no value)) and thus provides no value. Any other integer value including, for example -1, will result in an attempt to insert the row with that value for the column. As the column has to be a primary key, then the values must be unique, so if a value of -1, for example, has been inserted a subsequent insertion with a value of -1 will not be inserted. In such a case it is the onConFlictStrategy that determines what then happens.

It appears that you are trying to ascertain/predict/assume the value that is generated. There is no need as if you use Room's @Insert then the generated value will be returned as a Long or in the case of the row not being inserted, without an error, then the returned value will be -1.

Although highly likely, the generated value will be 1 greater than the highest value that has ever been inserted or in the case of the first inserted row 1. However, there is no guarantee that the value will be 1 greater.

In short you should be inserting the row and then obtaining the value (e.g. utilising the result returned from an @Insert), not trying to make an assumption about the value.

Demonstration

Using the following code:-

@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var id: 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(
                // How can I auto increment below statement?
                id = subMenu.id ?: -1,
                level = subMenu.level,
                parentId = parentId,
            )
        }
         */
    }
}

i.e. your code less the rather awkward function (see the addSubShopMenuEntityAndItsParent in the ShopMenuDAO interface below)

@Dao
interface ShopMenuDAO {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(shopMenuEntity: ShopMenuEntity): Long
    @Query("SELECT coalesece(max(${ShopMenuEntity.COLUMN_ID}),-1) FROM ${ShopMenuEntity.TABLE_NAME}")
    fun getLastInsertedShopMenuEntityId(): Int

    /* An example of how you could achieve what appears to be required */
   @Transaction /* Do everything with a single transaction */
   @Query("") /* fool Room into think this should be in a transaction */
    fun addSubShopMenuEntityAndItsParent(subShopMenuEntity: ShopMenuEntity, parentShopMenuEntity: ShopMenuEntity, parentId: Long?=null): ShopMenuEntity {
       var parentIdToUse = parentId
       if (parentId==null) {
           val insertedParentId = insert(parentShopMenuEntity)
           /* If not inserted then return ShopMenuEntity indicating parent not inserted i.e. id = -99 */
           if (insertedParentId == -99L) return ShopMenuEntity(-1)
           parentIdToUse = insertedParentId
       } else {
            /* Supplying the Parent ID so use that as parent (SHOULD) already exist */
            parentIdToUse = parentId;
       }
       subShopMenuEntity.parentId = parentIdToUse.toInt()
       val insertedSubId = insert(subShopMenuEntity)
       /* If not inserted then return ShopMenuEntity indicating that sub was not inserted i.e. -1 */
       if (insertedSubId == -1L) {
           return ShopMenuEntity(-1)
       }
       subShopMenuEntity.id = insertedSubId.toInt()
       return subShopMenuEntity
    }
}

Note the insert returns the generated id (Long as it should be rather than Int)
The additional but not really needed getLastInsertedShopMenuEntityId which should return the last inserted id (warning this may not always be the case if inserting bay specifying an id)
Lastly a means of inserting a ShopMenuEntity that has a parent (which can be inserted or just according to the provided parentId)
- note this is not necessarily fully comprehensive but is intended to demonstrate the concept

To allow the actual demonstration a basic @Database annotated class:-

/* Basic @Database abstract class */
@Database(entities = [ShopMenuEntity::class], exportSchema = false, version = 1)
abstract class ShopDatabase: RoomDatabase() {
    abstract fun getShopMenuDAO(): ShopMenuDAO

    companion object {
        private var instance: ShopDatabase?=null

        fun getInstance(context: Context): ShopDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,ShopDatabase::class.java,"whatever.db")
                    .allowMainThreadQueries() /* use the main thread for brevity */
                    .build()
            }
            return instance as ShopDatabase
        }
    }
}

Finally some activity code to actually demonstrate:-

class MainActivity : AppCompatActivity() {

    lateinit var db: ShopDatabase
    lateinit var dao: ShopMenuDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = ShopDatabase.getInstance(this)
        dao = db.getShopMenuDAO()

        /* Normal insert of a shop only */
        val s1Id = dao.insert(ShopMenuEntity(0,1))
        Log.d("DBINFO","The id of the last inserted shop was ${dao.getLastInsertedShopMenuEntityId()}")
        /* Insert of a shop with a parent, the parent's id being passed (the parent ShopMenuEntity is ignored) */
        val s2 = dao.addSubShopMenuEntityAndItsParent(ShopMenuEntity(0,100),ShopMenuEntity(-100),s1Id)
        if (s2.id < 0) Log.d("DBINFO","Oooops something not quite right as id of subShop is less than 0, it was ${s2.id}")
        Log.d("DBINFO","The id of the last inserted shop was ${dao.getLastInsertedShopMenuEntityId()}")
        /* Add a subShop along with the new parentShop */
        val s3 = dao.addSubShopMenuEntityAndItsParent(ShopMenuEntity(level = 3), ShopMenuEntity(99,0),null)
        if (s3.id < 0) Log.d("DBINFO","Oooops something not quite right as id of subShop is less than 0, it was ${s3.id}")
        Log.d("DBINFO","The id of the last inserted shop was ${dao.getLastInsertedShopMenuEntityId()}")


        for (sme in dao.getAllShopMenuEntities()) {
            Log.d("DBINFO","Shop ID is ${sme.id} Level is ${sme.level} Parent ID is ${sme.parentId}")
        }
    }
}

When the above is run (as a new App, it is not designed to be rerun) then the log contains:-

2023-08-04 10:46:44.820 D/DBINFO: The id of the last inserted shop was 1
2023-08-04 10:46:44.825 D/DBINFO: The id of the last inserted shop was 2
2023-08-04 10:46:44.845 D/DBINFO: The id of the last inserted shop was 100
2023-08-04 10:46:44.847 D/DBINFO: Shop ID is 1 Level is 1 Parent ID is -1
2023-08-04 10:46:44.847 D/DBINFO: Shop ID is 2 Level is 100 Parent ID is 1
2023-08-04 10:46:44.847 D/DBINFO: Shop ID is 99 Level is 0 Parent ID is -1
2023-08-04 10:46:44.847 D/DBINFO: Shop ID is 100 Level is 3 Parent ID is 99

As can be seen:-

ID's 1, 2 and 100 have been generated and that the ID 99 was specified and set accordingly (not advisable to specify id but have them generated).
That parents have been set accordingly i.e.
1. ID 1 has -1 (no parent) and likewise for ID 99
2. ID 2 has parent 1 (the parent ID obtained via the @Insert)
3. that ID 99 was inserted along with it's child which has the generated ID 100 and has 99 as it's parent. Both being specified as new inserts via the addSubShopMenuEntityAndItsParent function (which is likely an equivalent of your mapHttpResponse function).

If App Inspection is used then the above 4 rows are reflected as per:-

In addition using the query via Open New Query Tab SELECT * FROM sqlite_sequence; then you get:-

i.e. the highest assigned id of 100 has been stored in thesqlite_sequence table (as AUTOINCRMENT has been used). Thus when generating the value of the menu_id column it will be 1 + the higher of the highest menu_id value and the value saved in sqlite_sequence for the table (the name column of the sqlite_sequence table).

You may wish to refer to https://www.sqlite.org/autoinc.html for more in regards to AUTOINCERMENT.