This is a good question that is also relevant to other environments.
The Core Issue: How to store large data without effecting your database?
As a rule of thumb you should avoid storing information in your database that is not queryable. Large strings, images, or event metadata which you will never query - does not belong in your db. I was surprised when I realized how many design patterns there are regarding to mongo db (which are relevant to other noSQL databases as well)
So, we know that this data should NOT be stored in the DB. But, because the alternative (file system) is WAY worse than that (unless you would like to implement your own secured file-system-based store) - we should at least try to minimize its footprint.
Our Strategy: save large data chunks in a different table without defining it as an entity (there is no need to wrap it as entity anyway)
How Are We Going To Do That?
Well, thankfully, android room has a direct access to sqLite and it can be used directly (read the docs). This is the place to remind us that android room is built on-top of sqLite - which is (in my own opinion) a fascinating database. I enjoy working with it very much and it's just getting better as the time goes by (personal opinion). Advantages? we are still using android APIs while storing large data in a performant, unified and secure way. yay
Steps we are going to perform:
- Initiate a class which will manage a new database - for storing large data only
- Define a command that will create our table - constructed of 2 columns
- key (primary key) - the id of the item
- value - the item itself
- In original db for the
Text entity - define a column that will hold the id (key) of the large text stored
- Whenever you save an item to your large items table - get the id and store it in your entity
You can of course use only 1 table for this.. but.. I know that sqLite requires a certain amount of understanding and it is NOT as easy as android room so.. it's your choice whenever to use 1 or 2 tables in your solution
Below is a code that demonstrates the main principal of my proposal
object LargeDataContract {
// all tables for handling large data will be defined here
object TextEntry : BaseColumns {
const val TABLE_NAME = "text_entry"
const val COLUMN_NAME_KEY = "key"
const val COLUMN_NAME_VALUE = "value"
}
}
// in the future - append this "create statement" whenever you add more tables to your database
private const val SQL_CREATE_ENTRIES =
"CREATE TABLE ${TextEntry.TABLE_NAME} (" +
"${TextEntry.COLUMN_NAME_KEY} INTEGER PRIMARY KEY," +
"${TextEntry.COLUMN_NAME_VALUE} TEXT)"
// create a helper that will assist you to initiate your database properly
class LargeDataDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(SQL_CREATE_ENTRIES)
}
companion object {
// If you change the database schema, you must increment the database version. Also - please read `sqLite` documentation to better understand versioning ,upgrade and downgrade operations
const val DATABASE_VERSION = 1
const val DATABASE_NAME = "LargeData.db"
}
}
// create an instance and connect to your database
val dbHelper = LargeDataDbHelper(context)
// write an item to your database
val db = dbHelper.writableDatabase
val values = ContentValues().apply {
put(TextEntry.COLUMN_NAME_VALUE, "some long value goes here")
}
val key = db?.insert(TextEntry.TABLE_NAME, null, values)
// now take the key variable and store it in you entity. this is the only reference you should need
Bottom Line: This approach will assist you to gain as much performance as possible while using android APIs. Sure thing, not the most "intuitive" solution, but - this is how we gain performance and making great apps as well as educating ourselves and upgrading our knowledge and skillset. Cheers
