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

}

i dont know about kotlin and dont know about anko...but i know about java — BiRjU, May 16 '18 at 12:28
maybe i suggest something you need to convert that it in kotlin — BiRjU, May 16 '18 at 12:28

score 3 · Accepted Answer · answered May 22 '18 at 08:36

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

Thanks! The latest anko seems to delete the keyword WhereClause — HelloCW, May 23 '18 at 02:13
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? — HelloCW, May 23 '18 at 02:13

score 1 · Answer 2 · answered May 22 '18 at 13:36

1

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

answered May 22 '18 at 13:36

Demigod

5,073
3
31
49

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 – Tarun Lalwani May 23 '18 at 06:47

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

2 Answers2