3

I am updating my app with new library releases and I got an issue with my database. The released app currently using Room version 2.2.6 with FTS4. Currently having LONG for rowid. The app runs smooth and no problem. But I want to use Room version 2.3.0 and according to the docs:

An FTS entity table always has a column named rowid that is the equivalent of an INTEGER PRIMARY KEY index. Therefore, an FTS entity can only have a single field annotated with PrimaryKey, it must be named rowid and must be of INTEGER affinity. The field can be optionally omitted in the class but can still be used in queries.

I should use INT not LONG.

@Entity(tableName = "visit")
@Fts4
public class Visit {

    @PrimaryKey
    @ColumnInfo(name = "rowid")
    private Long identification; //Change to int

}

Is there a way to update the property without ruining the data of my users?

Rence Rei
  • 473
  • 3
  • 9

1 Answers1

2

An FTS entity table always has a column named rowid that is the equivalent of an INTEGER PRIMARY KEY index.

In brief SQLite's INTEGER does not imply a Java/Kotlin Integer/Int/int it is a column affinity/type.

If you look at the Datatypes in SQLite3 then an INTEGER can be up to 8 bytes (64 bit signed). Which in Java/Kotlin is long/Long.

Further evidence can be seen with the SQLiteDatabase insert convenience method as it returns the id (rowid) of the inserted row not as an int but as a long.

Returns long - the row ID of the newly inserted row, or -1 if an error occurred.

SQLiteAutoincrement explains about rowid and that in theory it can be from 1-9223372036854775807 (you can even have negative values).

As such, it is wrong to use int/Int/Integer for the rowid as in theory (not that likely in practice), the rowid can be larger than an int/Int/Integer.

I should use INT not LONG.

I'd suggest that you should use Long. It makes no difference to the data as SQLite will store an integer in as little space as it can. Furthermore, it makes no difference to the table(s) that room creates as the COLUMN TYPE will be INTEGER.

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Thank you for the explanation. But the sample code from Android Development shows int not Long. https://developer.android.com/reference/androidx/room/Fts4. Will there be any problem if I maintain LONG instead of int? – Rence Rei Jun 15 '21 at 03:56
  • @RenceRei there should be problems at all. In fact just done a test and it makes no difference to the schema if you swap between Integer and Long. – MikeT Jun 15 '21 at 04:02