Update parameter inside child table room database

Question

I need help. I need to update lastStatus inside ConnectionWithPassenger which is part of the list of LocalConnectionsNearbyList. If I try to update it with the first query my build fails, I get an error: "There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: single_connection)". What is makes sence. But I can't also update it with the second query. I get another error: "no viable alternative at input 'UPDATE connections_new WHERE' public abstract void updateOut(@org.jetbrains.annotations.NotNull()". I don't want to change the database structure. Thanks in advance for any ideas.

@TypeConverters(ConnectionConverter::class)
@Entity(tableName = "connections_new")
data class LocalConnectionsNearbyList(
    @PrimaryKey
    var rideId: String,
    var connectionsWithPassengers: MutableList<ConnectionWithPassenger>,
)

@Entity(tableName = "single_connection")
data class ConnectionWithPassenger(
    @PrimaryKey
    val serverId: String,
    var connectionIdNearBy: String,
    var lastStatus: String,
    var lastTimeStamp: Long,
    var isSent: Boolean,
    var name:String,
    var image:String
)

DB:

@Database(
    entities = [
....
        LocalConnectionsNearbyList::class
    ], version = 2, exportSchema = false
)

Query idea 1:

 @Query("UPDATE single_connection SET lastStatus=:lastStatus WHERE connectionIdNearBy LIKE :lastStatus")
 fun updateOut(idOfConnection:String, lastStatus:String)

Query idea 2:

@Query("UPDATE connections_new WHERE rideId=:id SET lastStatus=:lastStatus WHERE connectionIdNearBy LIKE :lastStatus")
fun updateOut(idOfConnection:String, lastStatus:String)

Answer 1

The first query doesn't work, because you probably don't have the ConnectionWithPassenger entity defined as an entity in the entities = [] in the class annotated with @Database.

• if not the @Entity annotation is useless (even damaging/frustating as such as it allows you to code single_connection as if it were a table).

The second query is trying to reference columns that are not in the connections_new table. That table has just two columns rideId and connectionsWithPassengers.

The data held in the connectionsWithPassengers column may well contain the lastStatus value (that depends upon the TypeConverter) but the lastStatus is not a column (likewise for the connectionIdNearBy column).

Using what you have you would have to either:-

• locate and change the value within the connectionsWithPassengers column (which SQL wise could be a nightmare but is dependant upon the TypeConverter as the TypeConverter determines how the data is saved in the column), or
• extract the connectionsWithPassengers list then extract/locate the respective connectionsWithPassengers object(s) from the list and then update the column with the amended list of connectionsWithPassengers objects.

Example

As much depends upon the TypeConverters in this example they are :-

@TypeConverter
fun fromConnectionWithPassengerListHolder(cwplh: ConnectionWithPassengerListHolder): String {
    return Gson().toJson(cwplh)
}
@TypeConverter
fun toConnectionWithPassengerListHolder(json: String): ConnectionWithPassengerListHolder {
    return Gson().fromJson(json,ConnectionWithPassengerListHolder::class.java)
} 

A new class ConnectionWithPassengerListHolder has been added, as you there are issue with trying to use var connectionsWithPassengers: MutableList<ConnectionWithPassenger>

So ConnectionWithPassengerListHolder is :-

data class ConnectionWithPassengerListHolder (
    val connectionWithPassengerList: MutableList<ConnectionWithPassenger>
    )

Yo utilise the above LocalConnectionsNearbyList has been changed to be:-

@TypeConverters(ConnectionConverter::class)
@Entity(tableName = "connections_new")
data class LocalConnectionsNearbyList(
    @PrimaryKey
    var rideId: String,
    //var connectionsWithPassengers: MutableList<ConnectionWithPassenger>,
    var connectionsWithPassengers: ConnectionWithPassengerListHolder
)

The AllDao abstract class (rather than an interface) :-

@Dao
abstract class AllDao {

    @Insert(onConflict = IGNORE)
    abstract fun insert(localConnectionsNearbyList: LocalConnectionsNearbyList): Long

    @Query("SELECT * FROM connections_new WHERE rideId=:rideId")
    abstract fun getNewConnectionByRideId(rideId: String): LocalConnectionsNearbyList

    @Update
    abstract fun update(localConnectionsNearbyList: LocalConnectionsNearbyList): Int

    @Query("")
    @Transaction
    fun updateLastStatusOfConnectionNearby(rideId: String, connectionNearBy: String, newStatus: String) {
        var update_needed = false
        var lcnl: LocalConnectionsNearbyList = getNewConnectionByRideId(rideId)
        for (cwp: ConnectionWithPassenger in lcnl.connectionsWithPassengers.connectionWithPassengerList) {
            if (cwp.connectionIdNearBy == connectionNearBy) {
                cwp.lastStatus = newStatus
                update_needed = true
            }
        }
        if (update_needed) {
            update(lcnl)
        }
    }
}

and the following in an activity ( .allowMainThreadQueries used for brevity when building the database)

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()

    var lc1 = LocalConnectionsNearbyList("ride1",
        ConnectionWithPassengerListHolder(
            mutableListOf(
                ConnectionWithPassenger("S1","n1","NEW",0,false,"Fred","i1"),
                ConnectionWithPassenger("S2","n2","NEW",0,false,"Mary","i2"),
                ConnectionWithPassenger("S3","n3","NEW",0,false,"Jane","i3"),
                ConnectionWithPassenger("S4","n4","NEW",0,false,"Tom","i4")
            )
        )
    )
    dao.insert(lc1)
    dao.updateLastStatusOfConnectionNearby(lc1.rideId,"n3","CHANGED")
    dao.updateLastStatusOfConnectionNearby(lc1.rideId,"n1","ALTERED")

}

This will :-

1. get the database instance
2. get the AllDao instance
3. add a single LocalConnectionsNearbyList with 4 ConnectionsWithPassengers (note the ConnectionWithPassengerListHolder is to circumvent the use of a List/Array for a column) to the database.
4. Change the 3rd ConnectionWithPassenger's lastSatatus from NEW to CHANGED in the database.
5. Change the 1st ConnectionWithPassenger's lastSatatus from NEW to ALTERED in the database.

Note the above is designed/intended to run only the once.

Result

After running the example above then using Android Studio's App Inspection :-

As can be seen not all the data in the connectionsWithPassengers is displayed. Copying the data shows the data as :-

{"connectionWithPassengerList":[
    {"connectionIdNearBy":"n1","image":"i1","isSent":false,"lastStatus":"ALTERED","lastTimeStamp":0,"name":"Fred","serverId":"S1"},
    {"connectionIdNearBy":"n2","image":"i2","isSent":false,"lastStatus":"NEW","lastTimeStamp":0,"name":"Mary","serverId":"S2"},
    {"connectionIdNearBy":"n3","image":"i3","isSent":false,"lastStatus":"CHANGED","lastTimeStamp":0,"name":"Jane","serverId":"S3"},
    {"connectionIdNearBy":"n4","image":"i4","isSent":false,"lastStatus":"NEW","lastTimeStamp":0,"name":"Tom","serverId":"S4"}
    ]
}

• note the data has been split using line-feeds and tabs to make it more presentable.

• As can be seen the lastStatus for the 1st connectionWithPassengers has been changed to ALTERED and the 3rd has been changed to CHANGED with the other two unaltered as NEW.