Room database with one-to-one relation

Question

I have 2 Entities, Coin and CoinRevenue.

Basically, coin holds the price in USD for some other currency.

For example, Coin with symbol EUR with value of 1.0356

@Entity(tableName = "coin")
data class Coin(
        @field:PrimaryKey(autoGenerate = false)
        var id: String = "",
        var symbol: String = "",
        var pricInUsd: Float = 0f)

CoinRevenue is an Entity that I use to hold how much coins of that specific coins the User have. For example, CoinRevenue has relation to Coin Entity with EUR symbol and amount of 1000.

@Entity(tableName = "coinRevenue")
    data class CoinRevenueNew(
            @field:PrimaryKey(autoGenerate = true)
            var id: Int = 0,
            var coin: Coin? = null,
            var amount: Float = 0f)

Now I want to fetch CoinRevenue from the database and get the updated Coin from the database.

for example, i saved the Coin with (EUR,1.0253) and than Saved a CoinRevenue with that coin.

After that I updated the Coin with (EUR,2.522) I want that the Coin object inside CoinRevenue will be updated as well.

I understand that @Embedded just add the inner objet fields as colums to the same parent object. and when I use relation, I have to use a List or a Set. but I always have 1 Coin inside CoinRevenue.

My coinDAO:

@Query("select * from coin order by rank")
fun getAllCoins(): Flowable<List<CoinDB>>

@Query("select * from coin where rank = 1")
fun getFirstCoin(): Maybe<CoinDB>

@Query("select * from coin where favourite = 1 order by rank")
fun getAllFavouriteCoins(): Flowable<List<CoinDB>>

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoin(coinDB: CoinDB)

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoins(coinsList: List<CoinDB>)

// -----------------
// CoinRevenue
// -----------------

@Query("select * from coinRevenue order by rank")
fun getAllCoinsRevenue(): Flowable<List<CoinRevenue>>

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertCoinRevenue(coinRevenue: CoinRevenue)

@Delete()
fun deleteCoinRevenue(coinRevenue: CoinRevenue)

What is the best way to creat this?

Answer 1

It's a little hard to tell what you are really trying to achieve. Also, your naming is a little odd. It seems that the coin table really contains the currency information. The coinRevenueNew is a ledger entry or order. If you pick easier to follow examples, more people will try to finish reading your posts.

Also, the problem that you are trying to solve is a little unclear. - Is your problem modelling it in the database? - Is your problem that you want to have all amounts automatically updated when the currency changes? - Is your problem that you want to have the objects in memory updated when the database changes? - Is your problem with using foreign keys with Room?

The first issue with modelling has been hinted on by other people. You use a foreign key. There are plenty of articles about it.

Using a little more understandable domain names, you'd have two tables like these:

create table currency (id integer primary key, exchange_rate float);
create table order (id integer primary key, int total, int currency_id, foreign key(currency_id) references currency(id));

You'd create Room entities for each. You'd create a third class (do not mark it with @Entity) that combines both. You can use the annotations @Embedded or @Relation here. The documentation explains this further:

https://developer.android.com/reference/androidx/room/Relation.html

If you change the currency, the storage system will not automatically update all the order totals. If you have a field of "total_in_foreign_currency" and a field of "total_in_master_currency", the database will not recalculate for you. You have to manually iterate over each row and recalculate it.

In memory data objects won't magically update. You have to keep track of when you retrieved the data and if it is still now. You can use LiveData to be notified whenever the data changes (but it won't magically update your objects).

Answer 2

Your solution has several major drawback. One of them is that the tables' columns has to have different names. Instead of using @embededed I suggest to apply @Relation.

@Entity(tableName = "coin")
data class Coin(
        @field:PrimaryKey(autoGenerate = false)
        var id: String = "",
        var symbol: String = "",
        var pricInUsd: Float = 0f)

@Entity(tableName = "coinRevenue", foreignKeys = (arrayOf(ForeignKey(
        entity = CoinDB::class,
        onUpdate = ForeignKey.CASCADE,
        parentColumns = arrayOf("coinId"),
        childColumns = arrayOf("coinDbId"))))
)
data class CoinRevenue(
        @ColumnInfo(name = "mid")
        @PrimaryKey(autoGenerate = true)
        var id: Long = 0L,
        @ColumnInfo(name = "coinDbId")
        var coinDbId: String? = null,
        @ColumnInfo(name = "amount")
        var amount: Double = 0.toDouble()
) 

I am not familiar with Kotlin so the solution is in Java

class CoinRevenueExt extends CoinRevenue {
        @Relation(parentColumn = "coinDbId", entityColumn = "coinId" ) 
        List<Coin> coins;

        public Coin getCoin() {
            return coins.get(0);
        }

}

And Dao is simple as that

@Query("select * from coinRevenue")
public Flowable<List<CoinRevenueExt>> getAllCoinsRevenueWithCoin();

Answer 3

So after a lot of tries, I've managed to get it working.

I Changed the CoinRevenue object to hold a foreign key to the Coin id

@Entity(tableName = "coinRevenue", foreignKeys = (arrayOf(ForeignKey(
        entity = CoinDB::class,
        onUpdate = ForeignKey.CASCADE,
        parentColumns = arrayOf("coinId"),
        childColumns = arrayOf("coinDbId"))))
)
data class CoinRevenue(
        @ColumnInfo(name = "mid")
        @PrimaryKey(autoGenerate = true)
        var id: Long = 0L,
        @ColumnInfo(name = "coinDbId")
        var coinDbId: String? = null,
        @ColumnInfo(name = "amount")
        var amount: Double = 0.toDouble()
)

I needed to create a POJO with both objects, like that:

class CoinRevenueWithCoin() : Parcelable {
@Embedded lateinit var coinDB: CoinDB
@Embedded lateinit var coinRevenue: CoinRevenue
}

and the query it like this:

@Query("select * from coinRevenue, coin where coinRevenue.coinDbId = coin.coinId order by coin.rank")
fun getAllCoinsRevenueWithCoin(): Flowable<List<CoinRevenueWithCoin>>

That's it.

In addition this query, as any other regular objects query, emit objects if there is any change in the 'coin' table or the 'coinRevenue' table