How can I limit the number of rows in Android SQLite table-ThrowExceptions

Exception or error:

I create an Android app that has “recents history”.
I would like to limit the size of this table to a maximum of 50 rows (according to their insert date).

I saw several threads that talk about limiting the number of deleted rows, but I’m not sure if this feature is even enabled in Android’s SQLite.

Can anyone help here?

Thanks

How to solve:

Create a trigger

CREATE TRIGGER delete_till_50 INSERT ON _table WHEN (select count(*) from _table)>50 
BEGIN
    DELETE FROM _table WHERE _table._id IN  (SELECT _table._id FROM _table ORDER BY _table._id limit (select count(*) -50 from _table ));
END;

EDIT:

You can change
DELETE FROM ... WHERE ... IN ...
to
DELETE FROM ... WHERE ... NOT IN ...
as Mojo Risin wrote. I’m not sure about difference of performance for large tables for using IN and NOT IN, but for your problem it’s no difference.

###

I think sql can’t manage the number of rows in your table so you’ll have to manage it by yourself. You can execute query after data insertion that will reduce the data – something like this should work

DELETE FROM table where _id NOT IN (SELECT _id from table ORDER BY insertion_date DESC LIMIT 50)

###

Check out SearchRecentSuggestions‘s source code for an example. It has a method to truncate history up to a given number of entries, using LIMIT -1 OFFSET <maxEntries>. You have to sort the entries by the reversed order of insertion first, then skip the first maxEntries.

If you call this automatically every upon insertion, then you only need to LIMIT 1 as there can never be more than maxEntries + 1 anyway.

/**
 * Reduces the length of the history table, to prevent it from growing too large.
 *
 * @param cr Convenience copy of the content resolver.
 * @param maxEntries Max entries to leave in the table. 0 means remove all entries.
 */
protected void truncateHistory(ContentResolver cr, int maxEntries) {
    if (maxEntries < 0) {
        throw new IllegalArgumentException();
    }

    try {
        // null means "delete all".  otherwise "delete but leave n newest"
        String selection = null;
        if (maxEntries > 0) {
            selection = "_id IN " +
                    "(SELECT _id FROM suggestions" +
                    " ORDER BY " + SuggestionColumns.DATE + " DESC" +
                    " LIMIT -1 OFFSET " + String.valueOf(maxEntries) + ")";
        }
        cr.delete(mSuggestionsUri, selection, null);
    } catch (RuntimeException e) {
        Log.e(LOG_TAG, "truncateHistory", e);
    }
}

Leave a Reply

Your email address will not be published. Required fields are marked *