Auto trim your database upon insertion

Image for post
Image for post

If you have a SQLite database in your App, that get inserted constantly, you might like to trim it often to avoid it oversize. E.g. you save a list of User Actions for UNDO purpose, or a list of User Search Entries, you don’t want the list to grow infinitely. You would hope to have it trimmed to certain size.

You probably think of having a routine to check after INSERTION, to delete off your the additional dated rows. This is so troublesome. How nice if upon INSERTION, the database will automatically trim it accordingly.

Auto Trimming SQL

Be of good cheers! The SQL World already have such thing prepared for us to use even in SQLite. Just like creating a table when you start your App below, you could also trigger an SQL Statement to tell the Database to automatic trigger needed action when you perform insertion on your Database Table. The code shows below.

Code in Kotlin

The DATABASE_TRIM string contains SQL Command CREATE TRIGGER trimTable AFTER INSERT ON $DATABASE_TABLE that instruct the database to perform specific subsequence instruction after every INSERTION into the table is performed.

The DELETE FROM $DATABASE_TABLE WHERE $KEY_ID NOT IN ( $TOP_LIST ) is to it to delete all rows that is not in the top latest List.

Once you have run db.execSQL(DATABASE_TRIM) at the start of your App, you need not need to worry about trimming your Database anymore. Whenever you insert new row to your table, it will automatically get trimmed. No need extra routine to check for trimming operation… Hurray!

p/s: Those who read your sql insertion command codes later will simply puzzled of how it magically got trimmed if this code is not made known to them. :P

Written by

Passionate about learning, and sharing mobile development and others

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store