Return values of Create, Update, Delete queries in sqlite db (Using Room in this case)

roomdb.md

I configured my Room query responses as per the official docs during db layer setup. While writing unit test for the layer, I realised some anomalises in many edge cases.

Following are my findings based on a number of use cases that i tried with insert(), update() and delete() query. We are running all these queries on a very basic table called Task.

The table schema looks something like this :

    /*
     *   task_table
     *   id -> String PrimaryKey
     *   status -> String, Not Null
     *   title -> String, Not Null
     *   description -> String
     *   deadline -> Long epoch time in ms
     *   created_at -> Long epoch time in ms
     */

Insert :

Long insert(Task)

1. Inserting a newly created model (When row with the same id does not exist).
   Task("some_id", "ACTIVE" ,"some title")
   Task saved in table.
   returns an autoincremented value 1, 2, 3, ....

2. When we try to insert another row with same id (breaking the UNIQUE Constraint of Primary Key).
   Task("some_id", "ACTIVE" ,"some title")
   Task is not saved in table.
   returns an autoincremented value 1, 2, 3, ....

Conclusion :

Insert query will always return a long value > 0, even when we try to break the UNIQUE constraint of Primary key. Don't use the return value of insert() to check for uniqueness of rows. It returns long > 0 even when nothing is stored in table. It should return -1 when any internal error occurs (Haven't tested it yet). Breaking UNIQUE constraint does not fall under internal errors.

Delete

int delete(Task)

1. Trying to delete a row that already exists.
   Task("some_id", "ACTIVE" ,"some title")
   Task removed from table.
   returns 1, i.e. number of rows affected by queries.

2. Trying to delete a row that does not exist.
   Task("some_id", "ACTIVE" ,"some title") (After deletion in previous step)
   No change in table.
   returns 0, i.e. no rows were affected by the query.

Conclusion

Delete works the way it is mentioned in the docs. No surprises here.

Update

int update(Task)

1. Trying to update a row that already exists.
   Task("some_id", "COMPLETED" ,"some title")
   row updated in table.
   returns 1, i.e. no of rows affected by the query.

2. Trying to update a row that does not exist.
   Task("some_non_existent_id", "COMPLETED" ,"some title")
   no affect on the table.
   returns 0, i.e. no rows were affected by the query.

3. Trying to call update() without making any changes to Task model Task("some_id", "COMPLETED" ,"some title")
   no change in the table. Any listener set on the table will be triggered.
   returns 1, i.e. no of rows affected by the query.

Conclusion

Updated work very similar to what the official docs say. Although there is a tiny problem here. If you don't want your SELECT query to get triggered when an unchanged Task model calls the update query, you should handle it explicitly.