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

Question

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

Also think about including the SQL `VACUUM` command as suggested [here](http://stackoverflow.com/a/5477526/356895) — JJD, Jun 29 '12 at 11:43

pawelzieba · Answer 1 · 2011-07-19T10:45:00.827

22

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.

edited Jul 19 '11 at 10:45

answered Jul 19 '11 at 10:26

pawelzieba

16,082
3
46
72

1

Don't you need `BEFORE`, `AFTER` or `INSTEAD OF`, as described here? https://www.sqlite.org/lang_createtrigger.html – IgorGanapolsky Nov 13 '15 at 15:53

score 16 · Answer 2 · answered Jul 19 '11 at 10:15

16

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)

answered Jul 19 '11 at 10:15

Mojo Risin

8,136
5
45
58

1

What does insertion_date look like? SQLite can only accept strings, so you cannot order dates like that... – IgorGanapolsky Jul 30 '13 at 16:23
We can covert datetime to millis and then can use them – Ankur Chaudhary Dec 18 '15 at 09:33

hidro · Answer 3 · 2015-11-26T09:03:38.330

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

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

3 Answers3

Linked

Related