How to saparate room databases by months?

Question

I want to make separated room databases due to my needs which is showing the data by months. For example: I need to show the expenses of April month so I need to export a database that represent April month's expenses and use it just for this month. Is there any solution for this? Here is my database:

Expense.kt

@Entity(tableName = "expenses_table")
data class Expense (
    @PrimaryKey(autoGenerate = true)
    val id: Int,
    val expenseDate: String,
    val expenseType: String,
    val expenseCost: Int
)

ExpenseDao.kt

@Dao
interface ExpenseDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun addExpense(expense: Expense)

    @Query("SELECT * FROM expenses_table ORDER BY id ASC")
    fun readAllData(): LiveData>
}

ExpenseDatabase.kt

@Database(entities = [Expense::class], version = 1, exportSchema = false)
   abstract class ExpenseDatabase: RoomDatabase() {

    abstract fun expenseDao(): ExpenseDao

    companion object {
        @Volatile
        private var INSTANCE: ExpenseDatabase? = null

        fun getDatabase(context: Context): ExpenseDatabase {
            val tempInstance = INSTANCE
            if (tempInstance != null) {
                return tempInstance
            }
            synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    ExpenseDatabase::class.java,
                    "expense_table"
                ).build()
                INSTANCE = instance
                return instance
            }
        }

        }
    }

user_11538740 · Answer

That would not be an ideal solution. Even if you find a solution imagine after an year you will be having 12 different databases.

I will suggest you to query the database according to your need.

user_4744514 · Answer

I want to make separated room databases due to my needs which is showing the data by months.

The need for getting data by months does not equate to the need to have separate databases. However, the following is an example that just requires a few modifications to your ExpenseDatabase class :-

@Database(entities = [Expense::class], version = 1, exportSchema = false)
abstract class ExpenseDatabase: RoomDatabase() {

    abstract fun expenseDao(): ExpenseDao

    companion object {
        @Volatile
        private var INSTANCE: ExpenseDatabase? = null

        fun getDatabase(context: Context, /* ADDED >>>>>*/yearMonthPrefix: String, /* ADDED >>>>>*/ swap: Boolean = false): ExpenseDatabase {
            val tempInstance = INSTANCE
            if (tempInstance != null && !swap) {
                return tempInstance
            }
            synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    ExpenseDatabase::class.java,
                     /* CHANGED >>>>>*/ "${yearMonthPrefix}_expense_table")
                    .build()
                INSTANCE = instance
                return instance
            }
        }
    }
}

From the information you have provided. The simplest and probably most efficient solution to your problem is to have a single database where all expenses are stored in the expenses_table and a query is used to extract the expenses for the month.

The important factor here is the expenseDate column/field and the suitability of the format of the stored data. If you use an SQLite recognised format such as YYYY-MM-DD then this format is known/understood by the SQLite Date/Time functions.

If so you could then use the following to get a list of the Expense's for the current month.

@Query("SELECT * FROM expenses_table WHERE strftime('%Y%m',expenseDate) = strftime('%Y%m','now') ORDER BY id ASC")
fun readCurrentMonthsData(): LiveData>

this taking advantage of the SQLite strftime function and the now time value

The following is a variation where you pass the year and month as a string and can thus get any month's data for any year:-

@Query("SELECT * FROM expenses_table WHERE substr(expenseDate,1,7)=:datepart ORDER BY id ASC")
fun readMonthsData(datepart: String): LiveData>

this uses the SQLite substr function

DEMO

Consider the following ( .allowMainTrhreadQueries added to the buildDatabase to allow demo to use the main thread) :-

Note includes the queries (demo versions that return List as opposed to LiveData> for convenience and brevity)

class MainActivity : AppCompatActivity() {
    lateinit var db: ExpenseDatabase
    lateinit var dao: ExpenseDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = ExpenseDatabase.getDatabase(this, "202201")
        dao = db.expenseDao()

        dao.addExpenseDemo(Expense(0,"2022-01-01","Type",100))
        dao.addExpenseDemo(Expense(0,"2022-01-11","Type",100))
        dao.addExpenseDemo(Expense(0,"2022-01-21","Type",100))
        dao.addExpenseDemo(Expense(0,"2022-01-31","Type",100))

        /* Swap to February Dataabase */
        db = ExpenseDatabase.getDatabase(this,"202202",true)
        dao = db.expenseDao()
        dao.addExpenseDemo(Expense(0,"2022-02-01","Type",100))
        dao.addExpenseDemo(Expense(0,"2022-02-02","Type",100))
        dao.addExpenseDemo(Expense(0,"2022-02-03","Type",100))

        for (e: Expense in dao.readCurrentMonthsDataDemo()) {
            Log.d("EXPENSEINFO001","Expense ID is ${e.id} Date is ${e.expenseDate} etc.")
        }
        /* None will be located as only 2022-02 rows in database */
        for (e: Expense in dao.readMonthsDataDemo("2022-01")) {
            Log.d("EXPENSEINFO002","Expense ID is ${e.id} Date is ${e.expenseDate} etc.")
        }

        /* Swap to January Database */
        db = ExpenseDatabase.getDatabase(this,"202201", true)
        dao = db.expenseDao()
        /* None will be located as only 2022-01 rows in database */
        for (e: Expense in dao.readCurrentMonthsDataDemo()) {
            Log.d("EXPENSEINFO003","Expense ID is ${e.id} Date is ${e.expenseDate} etc.")
        }
        for (e: Expense in dao.readMonthsDataDemo("2022-01")) {
            Log.d("EXPENSEINFO004","Expense ID is ${e.id} Date is ${e.expenseDate} etc.")
        }
    }
}

Demo Results (included in the log) :-

D/EXPENSEINFO001: Expense ID is 1 Date is 2022-02-01 etc.
D/EXPENSEINFO001: Expense ID is 2 Date is 2022-02-02 etc.
D/EXPENSEINFO001: Expense ID is 3 Date is 2022-02-03 etc.
 
 
D/EXPENSEINFO004: Expense ID is 1 Date is 2022-01-01 etc.
D/EXPENSEINFO004: Expense ID is 2 Date is 2022-01-11 etc.
D/EXPENSEINFO004: Expense ID is 3 Date is 2022-01-21 etc.
D/EXPENSEINFO004: Expense ID is 4 Date is 2022-01-31 etc.

The databases via App Inspection :-

And via Device File Explorer :-

Note that although the actual database files are only 4k each that the data in the -wal file will be applied (not all of it but at least 12K (at least 4K per table)). So multiple database files will waste a relatively high amount of the file space per database.
swapping databases will also result additional overheads.

How to saparate room databases by months?

2 Answers