How can delete all records except latest 10 records with in Kotlin with anko?

Question

The Code B define a Log table, I hope to clear all records except latest 10 records.

At present, I list all records order by CreatedDate first, then I do a loop from 11th record to last record, and delete the record using Code A.

Is there a better way to do that in Kotlin with anko ?

Code A

fun deleteDBLogByID(_id:Long)=mDBLogHelper.use{
        delete(DBLogTable.TableNAME,"$idName = {$idName} ","$idName" to _id.toString() )
}

Code B

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

    companion object {
        val DB_NAME = "log.db"
        val DB_VERSION = 1
        val instance by lazy { DBLogHelper() }
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.createTable( DBLogTable.TableNAME , true,
                DBLogTable._ID to INTEGER + PRIMARY_KEY+ AUTOINCREMENT,
                DBLogTable.CreatedDate to INTEGER,
                DBLogTable.Status to INTEGER  +DEFAULT("0"),
                DBLogTable.Description to TEXT
        )
    } 

}

Answer 1

Checking the source code of same at below location

https://github.com/Kotlin/anko/blob/e388295c70963d97d26820d4ecdf48ead8dba05e/anko/library/static/sqlite/src/Database.kt#L73

The function definition also takes a whereClause

fun SQLiteDatabase.delete(tableName: String, whereClause: String = "", vararg args: Pair<String, Any>): Int {
    return delete(tableName, applyArguments(whereClause, *args), null)
}

Which you can also see in below SO thread

How to delete rows in SQLite with multiple by where args using Anko?

Now combining above and below SO thread

Delete all but top n from database table in SQL

WHERE id NOT IN (SELECT id FROM table ORDER BY id LIMIT n);

You could do something like below

delete(TABLE_NAME, whereClause = "WHERE _ID NOT IN (SELECT _ID FROM {TABLE_NAME} ORDER BY CreatedDate Desc LIMIT {TOP})", 
                                                "TOP" to 10,
                                                "TABLE_NAME" to TABLE_NAME)

Above may need small fine tuning if it doesn't work but the approach should work. I don't have Kotlin setup to test and confirm the same. But you can provide feedback if you face an issue

Answer 2

If you're talking about how to do it with the list so it would be more concise, than you can try:

list.filterIndexed({ index, _ -> index > 10 }).forEach { delete(it) }