I'm trying to set the maximum size of Room database. I found that I can set maximumSize in database onCreate() callback, but then when I'm checking this value in onOpen() callback it is not changed.
Here is my code:
val db = Room.databaseBuilder(
appContext,
MyDatabase::class.java,
"name",
).addCallback(
object : RoomDatabase.Callback() {
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
db.maximumSize = 8796093014016
Log.e("size in onCreate = ", db.maximumSize.toString())
}
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
Log.e("size in onOpen = ", db.maximumSize.toString())
}
},
).build()
it prints:
size in onCreate = 8796093014016
size in onOpen = 4398046507008
It confuses me as I would expect it to print the following:
size in onCreate = 8796093014016
size in onOpen = 8796093014016
Any ideas what is wrong with this approach of changing room database size?
Do you know how to configure a database not to accept files above some limit?
First some understanding of what the "file(s)" consists off is likely very important.
In short by default Room will use WAL (Write Ahead Logging) rather than Journal mode.
In WAL mode the Database may consist of 3 files:-
In Journal mode the Database will consist of 2 files:-
So really the file size should be the Core database's file size plus the size of the -wal file and the -shm file.
However, SQLite stores data in blocks of a set size called pages (by default 4k in size). A page may or may not be full and the data held by pages may be combined or reduced by housekeeping (e.g. a VACUUM).
There is not a means to restrict the file size directly, to SQLite it is just a file. However the number of pages can be restricted by using the max_page_count PRAGMA.
So there are two ways to implement a restriction you can either check the file size and then implement some handling or you can rely upon the page count in which case you would have to then handle the ensuing SQLITE_FULL exception that would result if a page were required that would exceed the page count.
The following demonstrates retrieving the file size and setting the maximum page count:-
The @Database annotated class TheDatabase :-
@Database(entities = [AuthorEntity::class,BookEntity::class,AuthorBookCrossRef::class,InfoTable::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
fun getOverSizeIndicator(): Boolean {
return TheDatabase.isDatabaseOversize(this, maxsize)
}
fun getFileSize(): Long {
return TheDatabase.getDatabaseFileSize(this.openHelper.writableDatabase.path!!)
}
fun getPageSize(): Long {
return TheDatabase.getPageSize(this)
}
fun getPageCount(): Long {
return TheDatabase.getPageCount(this)
}
companion object {
val maxsize = /*4398046507008 + 1024*/ 8796093014016
var overSize = false
var instance: TheDatabase?=null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
.allowMainThreadQueries()
.addCallback(object : RoomDatabase.Callback(){
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
logMMAPSize(db,"CRTS")
db.setMaximumSize(maxsize)
logMMAPSize(db,"CRTE")
Log.d("PGCOUNT","PAGE COUNT is ${getPageCount(db)} MAXPAGECOUNT is ${getMaxPageCount(db)}")
setMaxPageCount(db, getPageCount(db)+100)
Log.d("PGCOUNT","PAGE COUNT is ${getPageCount(db)} MAXPAGECOUNT is ${getMaxPageCount(db)}")
}
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
logMMAPSize(db,"OPNS")
if (db.maximumSize < maxsize) {
db.setMaximumSize(maxsize)
}
logMMAPSize(db,"OPNE")
overSize = isDatabaseOversize(db, maxsize)
}
})
.build()
}
return instance as TheDatabase
}
fun logMMAPSize(db: SupportSQLiteDatabase, tagSuffix: String) {
Log.d("MMAPSize_$tagSuffix","MMAPSize is ${db.maximumSize}")
}
fun getDatabaseFileSize(databasePath: String): Long {
var size = File(databasePath).totalSpace
if (File("$databasePath-shm").exists()) {
size += File("$databasePath-shm").totalSpace
}
if (File("$databasePath-wal").exists()) {
size += File("$databasePath-wal").totalSpace
}
return size
}
private fun isDatabaseOversize(db: SupportSQLiteDatabase, maxSize: Long): Boolean {
val dbPath = db.path ?: return false
return getDatabaseFileSize(dbPath) >= maxSize
}
fun isDatabaseOversize(db: TheDatabase, maxSize: Long): Boolean {
return isDatabaseOversize(db.openHelper.writableDatabase,maxSize)
}
private fun getPageSize(db: SupportSQLiteDatabase): Long {
var rv: Long = 0
val csr = db.query("SELECT * FROM pragma_page_size")
if (csr.moveToFirst()) {
rv = csr.getLong(0)
}
csr.close()
return rv
}
fun getPageSize(db: TheDatabase): Long {
return getPageSize(db.openHelper.writableDatabase)
}
private fun getPageCount(db: SupportSQLiteDatabase): Long {
var rv: Long = 0
val csr = db.query("SELECT * FROM pragma_page_count")
if (csr.moveToFirst()) {
rv = csr.getLong(0)
}
csr.close()
return rv
}
fun getPageCount(db: TheDatabase): Long {
return getPageCount(db.openHelper.writableDatabase)
}
private fun getMaxPageCount(db: SupportSQLiteDatabase): Long {
var rv: Long = 0
val csr = db.query("SELECT * FROM pragma_max_page_count")
if (csr.moveToFirst()) {
rv = csr.getLong(0)
}
csr.close()
return rv;
}
fun getMaxPageCount(db: TheDatabase): Long {
return getMaxPageCount(db.openHelper.writableDatabase)
}
fun setMaxPageCount(db: TheDatabase,maxPageCount: Long): Long {
return setMaxPageCount(db.openHelper.writableDatabase,maxPageCount)
}
private fun setMaxPageCount(db: SupportSQLiteDatabase, maxPageCount: Long): Long {
var rv: Long = 0
val csr = db.query("PRAGMA max_page_count=${maxPageCount}")
rv = if (csr.moveToFirst()) {
csr.getLong(0)
} else {
getMaxPageCount(db)
}
csr.close()
return rv
}
}
}
The @Dao annotated AllDAOs interface includes:-
@Query("")
fun logMMAPSize(db: SupportSQLiteDatabase,tagSuffix: String) {
TheDatabase.logMMAPSize(db,tagSuffix)
}
The Activity code includes :-
db = TheDatabase.getInstance(this)
dao = db.getAllDAOs()
dao.logMMAPSize(db.openHelper.writableDatabase,"AFTOPN")
dao.deleteAllInfoTableRows()
dao.insert(InfoTable(createdDate = "2023-01-01", starred = "starred001", text = "ZZZZ"))
dao.insert(InfoTable(createdDate = "2022-01-01", starred = "starred009", text = "YYYY"))
dao.insert(InfoTable(createdDate = "2022-02-01", starred = "starred008", text = "XXXX"))
dao.insert(InfoTable(createdDate = "2022-03-01", starred = "starred007", text = "WWWW"))
dao.logMMAPSize(db.openHelper.writableDatabase,"AFTCHG")
Log.d("DB_OVERSIZE","The indicator that the database files are oversize is ${db.getOverSizeIndicator()}")
Log.d("DB_FILESIZE","Total file size is ${db.getFileSize()} " +
"PageSize is ${db.getPageSize()} " +
"PageCount is ${db.getPageCount()} " +
"Size according to Pages = ${db.getPageSize() * db.getPageCount()} " +
"MaxPageCount = ${TheDatabase.getMaxPageCount(db)}")
TheDatabase.setMaxPageCount(db,TheDatabase.getPageCount(db))
Log.d("DB_FILESIZE","Total file size is ${db.getFileSize()} " +
"PageSize is ${db.getPageSize()} " +
"PageCount is ${db.getPageCount()} " +
"Size according to Pages = ${db.getPageSize() * db.getPageCount()} " +
"MaxPageCount = ${TheDatabase.getMaxPageCount(db)}")
dao.insert(InfoTable(createdDate = "2023-01-01", starred = "starred001", text = "ZZZZ"))
dao.insert(InfoTable(createdDate = "2022-01-01", starred = "starred009", text = "YYYY"))
dao.insert(InfoTable(createdDate = "2022-02-01", starred = "starred008", text = "XXXX"))
dao.insert(InfoTable(createdDate = "2022-03-01", starred = "starred007", text = "WWWW"))
Result
When run from a fresh install:-
2023-05-24 08:24:23.614 D/MMAPSize_CRTS: MMAPSize is 4398046507008
2023-05-24 08:24:23.617 D/MMAPSize_CRTE: MMAPSize is 8796093014016
2023-05-24 08:24:23.618 D/PGCOUNT: PAGE COUNT is 11 MAXPAGECOUNT is 2147483646
2023-05-24 08:24:23.623 D/PGCOUNT: PAGE COUNT is 11 MAXPAGECOUNT is 111
2023-05-24 08:24:23.633 D/MMAPSize_OPNS: MMAPSize is 4398046507008
2023-05-24 08:24:23.636 D/MMAPSize_OPNE: MMAPSize is 8796093014016
2023-05-24 08:24:23.638 D/MMAPSize_AFTOPN: MMAPSize is 8796093014016
2023-05-24 08:24:23.664 D/MMAPSize_AFTCHG: MMAPSize is 8796093014016
2023-05-24 08:24:23.664 D/DB_OVERSIZE: The indicator that the database files are oversize is false
2023-05-24 08:24:23.671 D/DB_FILESIZE: Total file size is 2437595136 PageSize is 4096 PageCount is 11 Size according to Pages = 45056 MaxPageCount = 2147483646
2023-05-24 08:24:23.683 D/DB_FILESIZE: Total file size is 2437595136 PageSize is 4096 PageCount is 11 Size according to Pages = 45056 MaxPageCount = 11
In a subsequent run (the database exists) then the log includes:-
2023-05-24 08:39:33.457 26881-26881/a.a.so76273561fkey D/MMAPSize_OPNS: MMAPSize is 4398046507008
2023-05-24 08:39:33.459 26881-26881/a.a.so76273561fkey D/MMAPSize_OPNE: MMAPSize is 8796093014016
2023-05-24 08:39:33.461 26881-26881/a.a.so76273561fkey D/MMAPSize_AFTOPN: MMAPSize is 8796093014016
2023-05-24 08:39:33.482 26881-26881/a.a.so76273561fkey D/MMAPSize_AFTCHG: MMAPSize is 8796093014016
2023-05-24 08:39:33.483 26881-26881/a.a.so76273561fkey D/DB_OVERSIZE: The indicator that the database files are oversize is false
2023-05-24 08:39:33.489 26881-26881/a.a.so76273561fkey D/DB_FILESIZE: Total file size is 2437595136 PageSize is 4096 PageCount is 11 Size according to Pages = 45056 MaxPageCount = 2147483646
2023-05-24 08:39:33.498 26881-26881/a.a.so76273561fkey D/DB_FILESIZE: Total file size is 2437595136 PageSize is 4096 PageCount is 11 Size according to Pages = 45056 MaxPageCount = 11