Limit the number of rows in SQLite Database

Question

I currently have a SQLite Database to save a list of recent searches done in my app. I am currently inserting like this:

  /**
   * Inserts a RecentSearch into the Recent Searches Table
   *
   * @param aRecentSearch - The RecentSearch
   */
  public void insertSearchIntoDB(RecentSearch aRecentSearch) {
    SQLiteDatabase db = helper.getWritableDatabase();
    try {
      ContentValues contentValues = new ContentValues();
      contentValues.put(DBHelper.ENTITY_ID, aRecentSearch.getEntityId());
      contentValues.put(DBHelper.SEARCH_PHRASE, aRecentSearch.getPhrase());
      contentValues.put(DBHelper.SEARCH_TYPE, aRecentSearch.getSearchType().getItemId());
      contentValues.put(DBHelper.SEARCH_CATEGORY_NAME, aRecentSearch.getCategoryName());
      contentValues.put(DBHelper.SEARCH_TIME, aRecentSearch.getSearchTime());
      contentValues.put(DBHelper.RATINGS, aRecentSearch.getSellerRatingsCount());
      contentValues.put(DBHelper.STAR_COUNT, aRecentSearch.getSellerStarCount());
      contentValues.put(DBHelper.SELLER_VERIFIED, TradeUtils.getBooleanAsInt(aRecentSearch.isSellerVerified()));

      db.insertWithOnConflict(DBHelper.RECENT_SEARCHES_TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

    } catch (Exception e) {
      Log.e("TAG", "insertSearchIntoDB: " + aRecentSearch.getPhrase());
    } finally {
      if (db.isOpen()) {
        db.close();
      }
    }
  }

and getting the lastest 8 recent searches from the database like this:

  public List<RecentSearch> getRecentSearchesFromDataBase() {
    SQLiteDatabase db = helper.getWritableDatabase();
    Cursor cursor = db.query(DBHelper.RECENT_SEARCHES_TABLE, DBHelper.RECENT_SEARCHES_COLUMNS_AS_ARRAY, null, null, null, null, DBHelper.SEARCH_TIME + " DESC LIMIT 8");
    List<RecentSearch> recentSearchesList = new ArrayList<>();
    try {
      if (cursor.getCount() > 0) {
        while (cursor.moveToNext()) {
          RecentSearch recentSearch = new RecentSearch();

          int id_Index = cursor.getColumnIndex(DBHelper.ID);
          int entityId_Index = cursor.getColumnIndex(DBHelper.ENTITY_ID);
          int searchCategory_Index = cursor.getColumnIndex(DBHelper.SEARCH_CATEGORY_NAME);
          int searchPhrase_Index = cursor.getColumnIndex(DBHelper.SEARCH_PHRASE);
          int searchType_Index = cursor.getColumnIndex(DBHelper.SEARCH_TYPE);
          int searchTime_Index = cursor.getColumnIndex(DBHelper.SEARCH_TIME);
          int sellerRatings_Index = cursor.getColumnIndex(DBHelper.RATINGS);
          int sellerVerified_Index = cursor.getColumnIndex(DBHelper.SELLER_VERIFIED);
          int sellerStarCount_Index = cursor.getColumnIndex(DBHelper.STAR_COUNT);

          recentSearch.setId(cursor.getInt(id_Index));
          recentSearch.setEntityId(cursor.getInt(entityId_Index));
          recentSearch.setPhrase(cursor.getString(searchPhrase_Index));
          recentSearch.setCategoryName(cursor.getString(searchCategory_Index));
          recentSearch.setSearchType(SearchType.getValueOrDefault(cursor.getInt(searchType_Index), SearchType.TRADE_SEARCH));
          recentSearch.setSearchTime(cursor.getLong(searchTime_Index));
          recentSearch.setSellerVerified(TradeUtils.getBooleanFromInt(cursor.getInt(sellerVerified_Index)));
          recentSearch.setSellerRatingsCount(cursor.getInt(sellerRatings_Index));
          recentSearch.setSellerStarCount(cursor.getInt(sellerStarCount_Index));

          recentSearchesList.add(recentSearch);
        }
      }
    } catch (Exception e) {
      Log.e("TAG", "getRecentSearchesFromDataBase");
    } finally {
      cursor.close();
    }
    return recentSearchesList;
  }

Now my worry is that the database still saves all the recent search and all I am doing is just pulling the latests 8 recent searches with this line:

Cursor cursor = db.query(DBHelper.RECENT_SEARCHES_TABLE, DBHelper.RECENT_SEARCHES_COLUMNS_AS_ARRAY, null, null, null, null, DBHelper.SEARCH_TIME + " DESC LIMIT 8");

So my table will have for example 30 recent searches but I am only pulling the latest 8.

I would like to know is how can I either prevent more than 8 entries being in the table or delete all but the latest 8 entries in the table?

Thank you

Do you need the pseudo code? – K Neeraj Lal Nov 10 '16 at 07:24 — K Neeraj Lal, Nov 10 '16 at 07:24

score 0 · Answer 1 · answered Nov 10 '16 at 06:33

0

One possible way is to use the trigger here while entering data into the particular table to check and delete any unnecessary rows from that table.

A trigger is an event-driven action that are run automatically when a specified change operation ( INSERT, UPDATE and DELETE statement) is performed on a specified table. you can go through the triggers here in more details.

answered Nov 10 '16 at 06:33

Umais Gillani

608
4
9

I found this (http://stackoverflow.com/a/18677756/4578531) Where a trigger is used, am a little confused where i use this trigger though? – Stillie Nov 10 '16 at 06:37
You don't need to call triggers yourself from your code. just create trigger same as we create tables in DB. DB will take care of calling that on each event for which you created that trigger. – Umais Gillani Nov 10 '16 at 06:42
If you don't want to write trigger. you need to execute delete query each time after your insert query to delete unnecessary rows. – Umais Gillani Nov 10 '16 at 06:43
I don't have a problem with triggers or using them just battling to understand how to use them – Stillie Nov 10 '16 at 06:49
1

I would suggest a quick go through [here](https://www.tutorialspoint.com/sqlite/sqlite_triggers.htm) on triggers. you will get the clear idea about its usage. – Umais Gillani Nov 10 '16 at 06:53
great will have a look at that doc – Stillie Nov 10 '16 at 06:58

Limit the number of rows in SQLite Database

1 Answers1