How can I invert a boolean value inside an SQL query?

Question

I am making an alarm app and I would to write a query that would toggle some alarm properties, here is my alarm class.

@Entity
data class Alarm(
    val label: String?,
    var isEnabled: Boolean,
    val ringTime: LocalTime,
    val occursOn: MutableSet<Day>,
    var isVibrationEnabled: Boolean,
    @PrimaryKey(autoGenerate = true) var id: Int? = null
)

Here is my query that should invert vibration value

    @Query("UPDATE Alarm SET isVibrationEnabled = NOT isVibrationEnabled WHERE id = :alarmId")
    internal abstract fun toggleVibration(alarmId: Int): Completable

The app compiles fine, so it seems that the query is valid, however after I execute it, the value is not inverted and it stays the same

Answer 1

I believe that your issue is not the toggling, that works.

Rather that the issue is that the WHERE clause is not selecting a row (or the expected row) and thus that the value passed as the parameter when calling the toggleVibration function is not the id of an Alarm or the expected Alarm.

Example

Consider the following example (in Java rather than Kotlin for convenience) :-

The AlarmEntity Alarm.java

public class Alarm {

    @PrimaryKey(autoGenerate = true)
    private long id;
    private String label;
    private boolean isEnabled;
    private boolean isVibrationEnabled;

    public Alarm() {

    }

    public Alarm(String label, boolean isEnabled, boolean isVibrationEnabled) {
        this.label = label;
        this.isEnabled = isEnabled;
        this.isVibrationEnabled = isVibrationEnabled;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public boolean isEnabled() {
        return isEnabled;
    }

    public void setEnabled(boolean enabled) {
        isEnabled = enabled;
    }

    public boolean isVibrationEnabled() {
        return isVibrationEnabled;
    }

    public void setVibrationEnabled(boolean vibrationEnabled) {
        isVibrationEnabled = vibrationEnabled;
    }
}

• reduced set of columns for conveneience

AlarmDao.java

@Dao
public interface AlarmDao {

    @Insert
    long[] insertAlarm(Alarm... alarms);
    @Insert
    long insertAlarm(Alarm alarm);
    @Query("SELECT * FROM Alarm")
    List<Alarm> getAllAlarms();
    @Query("UPDATE Alarm SET isVibrationEnabled = NOT isVibrationEnabled WHERE id = :id")
    int toggleVibration(long id);
}

Code from an Activity to test :-

    alarmDao = mDB.getAlarmDao();
    // Add a copule of alarms
    alarmDao.insertAlarm(
            new Alarm("Alarm001",true,true),
            new Alarm("Alarm002",false,false)
    );
    // Get the Alarms and output them to the log
    List<Alarm> myalarms = alarmDao.getAllAlarms();
    for (Alarm a: myalarms) {
        Log.d("ALARMBEFORE","Label = " + a.getLabel() + " Enabaled = " + String.valueOf(a.isEnabled()) + " Vibration =  " + a.isVibrationEnabled());
    }
    //<<<<<<<<<< Toggle the first alarm >>>>>>>>>>
    alarmDao.toggleVibration(myalarms.get(0).getId());
    // Again get all the alarams and output them to the log
    myalarms = alarmDao.getAllAlarms();
    for (Alarm a: myalarms) {
        Log.d("ALARMAFTER","Label = " + a.getLabel() + " Enabaled = " + String.valueOf(a.isEnabled()) + " Vibration =  " + a.isVibrationEnabled());
    }

Result

05-30 10:34:33.853 D/ALARMBEFORE: Label = Alarm001 Enabaled = true Vibration =  true
05-30 10:34:33.853 D/ALARMBEFORE: Label = Alarm002 Enabaled = false Vibration =  false
05-30 10:34:33.856 D/ALARMAFTER: Label = Alarm001 Enabaled = true Vibration =  false
05-30 10:34:33.856 D/ALARMAFTER: Label = Alarm002 Enabaled = false Vibration =  false

i.e. for Alarm001 the isVibrationEnabaled value has been toggled from true to false