What id of the item we need to put when adding item in SQLite through Room? Android-CodePudding

I have a todo list app that use room for storing data.

My data class looks like this

@Entity
data class ToDoItem(
    @PrimaryKey val id: Int,
    val title: String,
    val description: String
)

When I add the new item in the list I am making this way

  viewModel.addItem(ToDoItem(0,title.toString(), description.toString()))

So it works fine. My question is why I can always send 0 as a primary key value? Why I don't have to get last item primary key value and send it as a parameter? How it works? Why I can always send hardcoded 0 and it works without any issues?

CodePudding user response：

My question is why I can always send 0 as a primary key value?

The exact reason depends upon how you are inserting rows. If using an @Insert annotated function, then the onConflict= value.

Why I don't have to get last item primary key value and send it as a parameter?

because, as explained below, a combination of an integer type such as Int and the @primayKey annotation is an SQLite special case that allows generation of the value. Again this depends upon how you are inserting the rows.

How it works? and Why I can always send hardcoded 0 and it works without any issues?

as explained below

Explanation

With SQLite, which is the database around which Room is a wrapper. The combination of a column type of INTEGER and PRIMARY KEY is a special case scenario where the column is an alias of a special column named rowid. The rowid exists for all tables (except for some special cases such as WITHOUT ROWID tables, which Room doesn't cater for).

If no value is specified for rowid then SQLite ascertains the value to be assigned (typically 1 greater than the last rowid for that table). There is a variation in that the use of the AUTOINCREMENT keyword, which can only be used if INTEGER PRIMARY KEY is specified. This implements a rule that the rowid must be greater than any rowid assigned (even if the row has been deleted).

AUTOINCREMENT is inefficient as it requires a table (sqlite_sequence), that has to be searched and updated for any row inserted into the table that has AUOINCREMENT coded.

In Room specifying autoGenerate = true results in the use of AUTOINCREMENT.

Room, at compile time, looks at the @Entity annotated classes that are in the list of entities defined in the @Database annotation and uses members/fields in the class to determine the table's definition.

If a member/field is annotated with @PrimaryKey AND the type of the member/variable is an integer type such as Long, Int then it will be an alias of the rowid.

As such Room has to decide when and when not to apply a value or not when inserting a row.

If the value is null (Long? Int?) then Room knows not to apply a value and doesn't.

However, as in your case, as you have Int=0 as opposed to Int?=null then IF autogenerate is true then Room assigns no value and the value is generated. Without autoGenerate=true then Room assigns 0 (in the case of Int=0) and every id would be 0 if the value isn't set to another value. As PRIMARY KEY implies UNIQUE then only 1 row can have a rowid with a value of 0 and a UNIQUE conflict occurs.

SQLite caters for handling of some conflicts giving some control as to how the conflict is handled. That is the INSERT can be followed by the keyword OR and an action which can be:-

ABORT (the default)
ROLLBACK (which will ABORT if not in a specified transaction)
FAIL
IGNORE (the conflict is ignored)
REAPLCE (the conflict is ignored after overwriting the existing row)

Room allows you to specify one of these by using the onConflict parameter when using the @Insert annotation.

So how your repeated insertion of an id with 0, as you do not have autogenerate=true depends upon the onConflict parameter.

If it is IGNORE (onConflict=OnConflictStrategy.IGNORE) then only the first row is inserted.

If it is REPLACE (onConflict=OnConflictStrategy.REPLACE) then each subsequent insertion overwrites the previous row.

Suggestion

Even though it is contrary to the examples that Room provides. It is suggested that

a) that integer primary keys use a type of Long. The reason being:-
- SQLite's INTEGER caters for a 64 bit signed integer, so in theory an Int is not large enough. SQLite will in fact store the value in as little space as needed (as low as 1 byte).
- the id returned when inserting using an @Insert annotated function is a Long or an array of Long's, so there is no need to use the Int's toInt method. (see link to demo for example of retrieving the id).
b) that Long?=null is used, so that by default an id is generated.
c) that autoGenerate=true is not used, unless strictly needed.
- typically the id will be greater, that until the 9223372036854775807th has been inserted, when
  - if AUTOINCREMENT (aka autoGenerate=true) is specified then an SQLITE_FULL error will result.
  - otherwise attempts will be made to return an unused value rather than an error.
    - 9223372036854775807's rows is beyond the storage capacity of any existing storage device.

You may wish to refer to

CodePudding user response：

Lok at this answer regarding how Room handles duplicates. From your code, it looks like you're either creating duplicate entries, all with the ID of 0, or you just keep updating the same entry every time.

What happens when you try to retrieve all the entries? Specifically, are you getting multiple entries with the id=0? If not, does it look like there's just one entry with the id=0? Or is it auto-generating the IDs for you?

CodePudding user response：

You need to use the autoGenerate property

Your primary key annotation should be like this:

@PrimaryKey(autoGenerate = true)

For more details: https://developer.android.com/reference/androidx/room/PrimaryKey#autoGenerate()