How to all time insert in to first row using ROOM?-CodePudding

How to all time insert in to first row using ROOM?

I need all time update first record in my database.

And I have only one column in it and primary key is this one column.

Row contains JSON. I put Insert with raplace flag, but it replacing only equals jsons but if jasons not equal creats a new row for it. I tried via query, but I haven't any id for WHERE statement, only one column with jason which is Primary key

@Entity
data class ListResponse(
    @SerializedName("json")
    @ColumnInfo(name = "json_column")
    val answer: ResponseJson
)

//@Dao
@Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(list: ResponseJson): Long

Found only one solution by cleaning table before inserting.

@Query("DELETE FROM $TABLE_NAME")
fun cleanTable()

CodePudding user response：

And I have only one column in it and primary key is this one column.

Then if the JSON code (aka the data actually stored) is different in anyway whatsoever then a new row will be added as the the UNIQUENESS implicit with a PRIMARY KEY is the ENTIRE value e.g. *abc is different to abcd and abd even though parts may be the same and may follow part of the same path through the index tree. REPLACE is basically useless as IGNORE will do the same more efficiently as instead, when using REPLACE, of actually deleting the row and inserting another when the UNIQUE conflict happens the conflict is ignored and the table left as it is/was (and potentially a needless disk write averted).

but I haven't any id for WHERE statement

a WHERE statement does not need an id. However you do actually have access to an id as with Room WITHOUT ROWID tables are not used, thus a row will have the normally hidden rowid which uniquely identifies a row (if the id is an integer type and the column is annotated with @PrimaryKey then it becomes and alias of the rowid column).

How to all time insert in to first row using ROOM?

What, in theory, as the primary key is a string, you could do is for example (NOT A GOOD EXAMPLE) is use some way of identifying the row as being similar.

For the example lets say you know that if the first 8 characters (JUST FOR THE SAKE OF EXPLAINING) will always be constant and thus if not the same a new row is indicated, otherwise a replace. Then you could have a UNIQUE index based upon the first 8 characters.

In SQLite (which Room is a wrapper around) then consider the following that uses this (probably unsuitable for actual use) a UNIQUE **PARTIAL** INDEX based upon the first 8 characters (AGAIN FOR EXAMPLE ONLY).

DROP INDEX IF EXISTS idx01;
DROP TABLE IF EXISTS ListResponse;
CREATE TABLE IF NOT EXISTS ListResponse (answer TEXT PRIMARY KEY);
CREATE UNIQUE INDEX IF NOT EXISTS idx01 ON ListResponse(substr(answer,1,8));
INSERT OR REPLACE INTO ListResponse VALUES 
    ('abcdefghi')
    ,('bcdefghij')
    /* different primary key as 9th character is j not i */
    /* BUT as idx001 is only 8 characters then UNIQUE conflict with first row */
    /* THEREFORE row is replaced */
    ,('abcdefghj');
SELECT rowid /*<<<<<<<<<< normally hidden id */,* FROM  ListResponse;
DROP INDEX IF EXISTS idx01;
DROP TABLE IF EXISTS ListResponse;

The SELECT results in:-

i.e. it demonstrates:-

that this hidden rowid exists (this could be extracted if need be with a suitable POJO and query that extracts the rowid see example)
that, as per the comment the similar (ACCORDING TO THE PROBABLY INPRACTICAL EXAMPLE) that the technique REPLACES or INSERTS according to the additional UNIQUE PARTIAL INDEX.

However, contrary to Room's purported ability to fully harness SQLite's abilities. Room DOES NOT cater for:-

@Entity(
    indices = [
        Index(value = ["substr(answer,1,8)"], unique = true)
    ]
)
data class ListResponse(
    @PrimaryKey
    @ColumnInfo(name = "json_column")
    val answer: String /* Changed to String to no need Type Converter */
)

this being the code in theory that could/should cater for the partial index.

As such to make this approach, you would have to circumvent Room's response of (as per build log):-

ListResponse.java:7: error: substr(answer,1,8) referenced in the index does not exists in the Entity. Available column names:json_column
public final class ListResponse {

You could introduce such an index via a CallBack in either or both (probably the more secure) the onCreate and onOpen functions.

Another way around would be to approach the database properly and store the actual data that needs to be stored rather than utilising the lazy chuck the data along with the BLOAT into a single column, with it's inherent disregard for normalisation. Such an approach would be to have columns for the various components of the underlying data. In which case you could create unique indexes on whatever columns that would satisfy your criteria.