Make SQLite only store the 50 latest - Android!

I have a table in my Android app that only should store the 50 latest texts and nothing more. When the table has reached an ID of 50 I want it to delete the old and only insert the new.

I'm guessing it would be best if when I insert something new it would put it with an ID of 1 and make all the other go down one step. ID 1 would become ID 2 etc, and the new text would be ID 1. Although this is a guess. But it should only store the 50 latest, and everything else to be deleted.

This is how I create the Table:

private final String CreateTable =  "CREATE TABLE " + 
SqConstants.TABLE_LOCAL         +   " (" + 
SqConstants.EXTERNAL_ID                 +   " INTEGER PRIMARY KEY AUTOINCREMENT, " +
SqConstants.TEXT                        +   " TEXT NOT NULL, " + 
SqConstants.TIME                        +   " TEXT NOT NULL, " +
SqConstants.IMAGE_URL                   +   " TEXT N开发者_运维百科OT NULL, " +
SqConstants.USER                        +   " TEXT NOT NULL, " +
SqConstants.LONG_EXT                    +   " LONG NOT NULL);";

And this is the method for inserting it in to the database:

public long insert(
        String text,
        String time,
        String imageUrl,
        String user,
        long ExtLong) {
    try {
        ContentValues cv = new ContentValues();
        cv.put(SqConstants.TEXT, text);
        cv.put(SqConstants.TIME, time);
        cv.put(SqConstants.IMAGE_URL, imageUrl);
        cv.put(SqConstants.USER, user);
        cv.put(SqConstants.LONG_EXT, id);
        return dataBase.insert(SqConstants.TABLE_LOCAL, null, cv);

    } catch(SQLiteException ex) {
        Log.e(TAG, "FAILED TO PUT INTO CONTENTVALUES. Exception: " + ex.getMessage());
        return -1;
    }
}

Any ideas how this could best be done?

---

Yup, a trigger is your solution. Something like the following:

db.execSQL("CREATE TRIGGER fifty_rows AFTER INSERT ON "+ SqConstants.TABLE_LOCAL +
   " BEGIN DELETE FROM "+ SqConstants.TABLE_LOCAL +" WHERE " +
   SqConstants.EXTERNAL_ID +" IN (SELECT "+SqConstants.EXTERNAL_ID+" FROM " +
   SqConstants.TABLE_LOCAL +" ORDER BY "+SqConstants.EXTERNAL_ID+" DESC LIMIT 50, 
   -1);END;");

---

A fine way to achieve this is the use of triggers, which are available in sql lite. Define a trigger before insert on your table. If there are already 50 items, fetch the lowest id from the table, and delete its row.

Then, i'm not sure you want to mess with the auto-increment ids, as it's supposed to be handled by the system. The solution i proposed would not require to update the ids.

---

The trigger approach is what I have always used in the past, however Gerry Snyder just suggested this nice solution on the SQLite mailing list for a related inquiry:

Add an integer column with a UNIQUE ON CONFLICT REPLACE constraint. Then after you figure out how many entries are enough (maxcount), insert each row, specifying that column as mod((lastinsertrowid()+1),maxcount) or however you specify a modulus or remainder. That column will just wrap around when it hits maxcount; and you extract based on rowid, not that column to keep things in proper order.