Why can't Anko ignore the passed value of _id when _id is INTEGER + PRIMARY_KEY+ AUTOINCREMENT?

Question

I have designed the _id field is INTEGER + PRIMARY_KEY+ AUTOINCREMENT, I use the code SettingManage().addSetting(MSetting(10L,"My Settings",2000L,"This is description!")) to insert a record to the table.

I think Anko will ignore the passed value 10 of _id and pass a new value to _id automatically, but in fact the value 10 of _id is inserted into the table.

How can make Anko ignore the passed value of _id when _id is INTEGER + PRIMARY_KEY+ AUTOINCREMENT ?

Insert Data

SettingManage().addSetting(MSetting(10L,"My Settings",2000L,"This is description!"))

Design Table

    class DBSettingHelper(mContext: Context = UIApp.instance) : ManagedSQLiteOpenHelper(
            mContext,
            DB_NAME,
            null,
            DB_VERSION) {

        companion object {
            val DB_NAME = "setting.db"
            val DB_VERSION = 5
            val instance by lazy { DBSettingHelper() }
        }

        override fun onCreate(db: SQLiteDatabase) {
            db.createTable( DBSettingTable.TableNAME , true,
                    DBSettingTable._ID to INTEGER + PRIMARY_KEY+ AUTOINCREMENT ,
                    DBSettingTable.Name to TEXT,
                    DBSettingTable.CreatedDate to INTEGER,
                    DBSettingTable.Description to TEXT
            )
        }

        override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
            db.dropTable(DBSettingTable.TableNAME, true)
            onCreate(db)
        }

    }



    class DBSetting(val mMutableMap: MutableMap<String, Any?>) {
        var _id: Long by mMutableMap
        var name: String by mMutableMap
        var createdDate: Long by mMutableMap
        var description: String by mMutableMap

        constructor(_id: Long, name: String, createdDate: Long, description: String)
                : this(HashMap()) {
            this._id = _id
            this.name = name
            this.createdDate = createdDate
            this.description=description
        }
    }


    object DBSettingTable {
        val TableNAME = "SettingTable"

        val _ID = "_id"
        val Name = "name"
        val CreatedDate = "createdDate"
        val Description="description"
    }


   data class MSetting (
        val _id: Long,  
        val name: String,
        val createdDate: Long,
        val description: String
   )

Business Logic

class SettingManage {
    fun addSetting(mMSetting:MSetting){
        DBSettingManage().addDBSetting(DbDataMapper().convertMSetting_To_DBSetting(mMSetting))
    }
}

class DBSettingManage(private val mDBSettingHelper: DBSettingHelper =DBSettingHelper.instance) {
    fun addDBSetting(mDBSetting: DBSetting)=mDBSettingHelper.use{
        insert(DBSettingTable.TableNAME,*mDBSetting.mMutableMap.toVarargArray())
    }
}

class DbDataMapper {
    fun convertMSetting_To_DBSetting(mMSetting: MSetting) =with(mMSetting){
        DBSetting(_id,name,createdDate,description)
    }
    fun convertDBSetting_To_MSetting(mDBSetting: DBSetting)=with(mDBSetting){
        MSetting(_id,name,createdDate,description )
    }
}


fun <T : Any> SelectQueryBuilder.parseList(parser: (Map<String, Any?>) -> T): List<T> =
        parseList(object : MapRowParser<T> {
            override fun parseRow(columns: Map<String, Any?>): T = parser(columns)
        })

Answer 1

Anko, in your usage, is a wrapper for SQLite. SQL itself overrides auto increment when a custom value is passed. If no value is passed -> Automatic value. Otherwise -> manual. It assumes it's unique because of PRIMARY_KEY, but that's a different problem.

As far as I know, there is no integrated feature into Anko that allows for overriding this manually. Instead, the only thing you can do is not pass a value. Any SQL query that's wrong won't be caught by Anko itself, but by the raw SQL. It's SQL that throws any exceptions meaning Anko won't check for missing data.

Simply, don't pass an ID. You can write a function that still takes in the ID but discards it if the row is set to PRIMARY_KEY and AUTO_INCREMENT.

---

I have done more digging in the source code and there's no support for ignoring passed values if it's set to auto increment.

This means the only way you can get it to actually automatically increment is by not passing any values. If you are using manual queries of any kind, you simply don't pass any value. So, don't give the database the ID. Because it's auto increment it'll automatically add it and that's on the base SQL level.

Ank doesn't ignore it by default because it's essentially a wrapper for SQLite. Meaning it has to be able to be passed if the ID should be overridden (see the link in the second sentence of this answer). As such, Anko ignoring it would cause more problems than it'd do good. You have to make sure it isn't passed instead