Kotlin room database boolean

Question

Im trying to create an app where a user can has a list of goals of steps to reach they create and then choose one of them to be active and to follow. The database works when I was just using the goal id, name, and steps but now I realised I need to insert another column defining when a goal is active so Im trying to insert that, however I don't know how I should handle the boolean especially in the repository and viewModel. I'd appreciate any help. Thanks in advance

here's my code

interface Dao {

@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(goal: Goal)

@Update
suspend fun updateGoal(goal: Goal)

@Query("SELECT * FROM user_goal_table order by goalId")
fun getAll(): LiveData<List<Goal>>


@Query("SELECT * FROM user_goal_table WHERE goalId = :key")
suspend fun getGoal(key: Int): Goal

@Delete
suspend fun delete(goal: Goal)

@Query("SELECT * FROM user_goal_table WHERE goal_is_active = 1 order by goalId")
suspend fun makeGoalActive(key: Int): Goal




class Repository (private val dao : Dao){

val allGoals: LiveData<List<Goal>> = Dao.getAll()

suspend fun insert(goal: Goal){
    dao.insert(goal)
}

suspend fun update(goal: Goal){
    dao.update(goal)
}

suspend fun delete(goal: Goal){
    dao.delete(goal)
}

suspend fun active(goal: Goal, int: Int){
    dao.makeGoalActive(int)
}



class ViewModel (application: Application) : AndroidViewModel(application) {

val allGoals: LiveData<List<Goal>>
private val repository: Repository

init{
    val dao = GoalDatabase.getInstance(application).getGoalDao()
    repo = Repository(dao)
    allGoals = repository.allGoals

}


fun insert(goal: Goal) = viewModelScope.launch(Dispatchers.IO){
    repository.insert(goal)
}

fun update(goal: Goal) = viewModelScope.launch(Dispatchers.IO){
    repository.update(goal)
}

fun delete(goal: Goal) = viewModelScope.launch(Dispatchers.IO){
    repository.delete(goal)
}

Answer 1

Just add a new property like isActive: Boolean to your Goal class and then use @Update annotation in Room (that you've already implemented in updateGoal(goal: Goal) method of your Dao) or UPDATE command itself in SQLite to update the row you want to change its isActive state. For using SQLite do something like below:

@Query("UPDATE user_goal_table SET isActive = 1 WHERE goalId = :goalId")
suspend fun makeGoalActive(goalId: Int)

For boolean properties, use 1 for true and 0 for false in SQLite commands.

In Repository, this method is enough:

suspend fun active(goalId: Int) {
    dao.makeGoalActive(int)
}

And in the ViewModel:

fun insert(goal: Goal) = viewModelScope.launch {
    repository.insert(goal)
}

Btw, you don't need to determine IO dispatcher for Room methods, Room uses its own dispatcher to run queries.