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
        )
    } 

}

Ilario

i dont know about kotlin and dont know about anko...but i know about java – Observatory

maybe i suggest something you need to convert that it in kotlin – Observatory

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

Amin

Thanks! The latest anko seems to delete the keyword WhereClause – Ilario

So it should be fun deleteOldAndKeepLatest(maxLogCountToKeep:Int)=mDBLogHelper.use{ delete(DBLogTable.TableNAME, "$idName NOT IN (SELECT $idName FROM {TABLE_NAME} ORDER BY CreatedDate Desc LIMIT {TOP})", "TOP" to maxLogCountToKeep, "TABLE_NAME" to DBLogTable.TableNAME) }, right? – Ilario

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) }

Reliquiae

Not a good option because if you a 1M records, you fire 1M - 10 queries and then you are also getting the data back to the client which we don't need as well. Since we don't care about the records, the delete should be done server side itself using a query which doesn't bring back any data – Amin