Update, insert and get element from RoomDatabase colum

Question

So I am build an app where I want to store information coming from various source into a single database. The database is as below:

@Entity(tableName = "store")
data class StoreEntity (
    @PrimaryKey()
    @ColumnInfo(name = "")
    var current: String,
    @ColumnInfo(name = "ingredientslist")
    var productsIngredientList: MutableList<IngredientsEntity?>,
    @ColumnInfo(name = "reviewslist")
    var productsReviewsList: MutableList<ReviewsEntity?>,
    @ColumnInfo(name = "listofproductsId")
    var listOfId: MutableList<String>
)

and the other entities are:

@Entity(tableName = "ingredients")
data class IngredientsEntity(
    @PrimaryKey()
    @NonNull var id: String,
    @ColumnInfo(name = "ingredient")
    var ingredient: Ingredient? = null,
)

@Entity(tableName = "reviews")
data class IngredientsEntity(
    @PrimaryKey()
    @NonNull var id: String,
    @ColumnInfo(name = "review")
    var review: Review? = null,
)

I have defined the Doa as below:

    @Query("UPDATE store SELECT ingredientslist SET ingredient=:shadow WHERE id = :id ")
    suspend fun updateIngredients(id: String?, ingredient: Ingredient)

    @Query("SELECT ingredient from ingredientslist WHERE id = :id ")
    suspend fun getIngredients(id: String): Flow<Ingredient?>
    suspend fun getIngredientsDistinctUntilChanged(id: String) = getIngredients(id).distinctUntilChanged()

   

reviews and ingredients are both coming from different sources but I would like a database where I can store :

• current product displayed -> String (only one)
• list of products -> list of String
• list of Ingredients (List of id + ingredients)
• list of Reviews (List of id + reviews)

the goal would be to be able to add/retrieve ingredients using id and same for reviews.

I am not sure if I have to access using the Doa the store database, then select ingrediendslist, then look for ingredients link to an id. or if I can just access directky ingrediendslist because store is automatically linking ingredientslist

Also is there a way to have a single PrimaryKey, which could be a single name ? the Store database will only have one single entry. See this like you can have only one store.

It's look likes to me a database into a database.

Any idea how to make it works ? I tried several Room sql command but I am not sure that it's the right way. Maybe I need to split it into different Dbs. One for ingredients, one for reviews and one for current product id and list of products ids.

I was trying to do it in one single database to avoid having multiple DBs for only 15 products top.

Any idea or advices ?

Thanks

Answer 1

The database is as below:

That would be in theory, there are various issues that would result in the compilation failing. Such as trying to give a column no name as per :-

@Entity(tableName = "store")
data class StoreEntity (
    @PrimaryKey()
    @ColumnInfo(name = "")
    var current: String,

Furthermore to store a list of Objects, although possible with the use of TypeConverters restricts or complicates matters with regard to best use of the database. Doing so also de-normalises the database and adds bloat as an object if referred to multiple times (such as an ingredient which may in theory be common to a number of stores).

Complexity arises through a value of any such object being stored with all the other values and thus would require complex SQL very likely including CASE THEN ELSE END constructs within and possibly Common Table Expressions.

As you are in the development stage I would suggest consider using the power of relationships basically have an extra 2 tables one to map/reference/associate a Store with it's ingredients, another to map a Store with it's Reviews.

Here's a working example (without products but the same technique applies for products)

So first the StoreEntity :-

@Entity(tableName = "store")
data class StoreEntity (
    @PrimaryKey()
    @ColumnInfo(name = "store_identifier")
    var current: String
    /* lists are mapped so not needed here (see StoreWithMappedReviewsAndWithMappedIngredients)
    @ColumnInfo(name = "ingredientslist")
    var productsIngredientList: MutableList<IngredientsEntity?>,
    @ColumnInfo(name = "reviewslist")
    var productsReviewsList: MutableList<ReviewsEntity?>,
    @ColumnInfo(name = "listofproductsId")
    var listOfId: MutableList<String>
     */
)

The Review class (made up as not included) :-

data class Review(
    var reviewTitle: String,
    var reviewer: String
    /* etc */
)

And like your code the respective ReviewsEnntity (that Embeds (copies the member variables) the underlying Review) :-

@Entity(tableName = "reviews")
data class /*IngredientsEntity????*/ ReviewsEntity(
    @PrimaryKey()
    @NonNull var reviewId: String,
    /* see comments for Ingredient class
    @ColumnInfo(name = "review")
    var review: /*Reviews? ????*/ Review = null,
    */
    @Embedded
    var review: Review
)

Similar for Ingredient (made up again) and IngredientsEntity :-

data class Ingredient(
    var ingredientName: String,
    var ingredientDescription: String
    /* other member variables as required */
)

and :-

@Entity(tableName = "ingredients")
data class IngredientsEntity(
    @PrimaryKey
    @NonNull var ingredientId: String,
    @Embedded
    var ingredient: Ingredient
    /* using below would require a TypeConverter the above embeds the ingredient so a column as per the ingredient class */
    /*var ingredient: Ingredient? = null <<<<< null ???? why have an ingredient row with no ingredient? */
)

Now the two (3rd for products) mapping/associate/reference .... tables. First the table that maps Stores and Reviews with the potential for many stores to have many Reviews and for Reviews to map to many Stores.

StoreReviewMappingEntity

@Entity(
    tableName = "store_review_map",
    primaryKeys = ["storeReviewMap_storeId","storeReviewMap_reviewId"]
/* Optional Foreign Key Constraints to enforce Referential Integrity */
)
data class StoreReviewMappingEntity(
    var storeReviewMap_storeId: String,
    @ColumnInfo(index = true)
    var storeReviewMap_reviewId: String
)

and StoreIngredientMappingEntity for Ingredients :-

@Entity(
    tableName = "store_ingredient_map",
    primaryKeys = ["storeIngredientMap_storeId","storeIngredientMap_ingredientId"]
    /* Optional Foreign Key Constraints to enforce Referential Integrity */
)
data class StoreIngredientMappingEntity(
    var storeIngredientMap_storeId: String,
    @ColumnInfo(index = true) /* index on the ingredient id so getting the ingredient is more efficient */
    var storeIngredientMap_ingredientId: String
)

So instead of the 3 tables 5 tables (entities)

Now a POJO (i.e. not an Entity) StoreWithMappedReviewsAndWithMappedIngredients that allows you to get a Store with all of it's Reviews and all of it's ingredients via the mapping tables:-

data class StoreWithMappedReviewsAndWithMappedIngredients(
    @Embedded
    var storeEntity: StoreEntity,
    @Relation(
        entity = ReviewsEntity::class,
        parentColumn = "store_identifier",
        entityColumn = "reviewId",
        associateBy = Junction(
            value = StoreReviewMappingEntity::class /* the mappping table class/entity */,
            parentColumn = "storeReviewMap_storeId",
            entityColumn = "storeReviewMap_reviewId"
        )
    )
    var reviewList: List<ReviewsEntity>,
    @Relation(
        entity = IngredientsEntity::class,
        parentColumn = "store_identifier",
        entityColumn = "ingredientId",
        associateBy = Junction(
            value = StoreIngredientMappingEntity::class,
            parentColumn = "storeIngredientMap_storeId",
            entityColumn = "storeIngredientMap_ingredientId"
        )
    )
    var ingredientList: List<IngredientsEntity>
)

Now the @Dao annotated interface (one for brevity/convenience) AllDao :-

@Dao
interface AllDao {

    /*
    @Query("UPDATE store SELECT ingredientslist SET ingredient=:shadow WHERE id = :id ")
    suspend fun updateIngredients(id: String?, ingredient: Ingredient)

    @Query("SELECT ingredient from ingredientslist WHERE id = :id ")
    suspend fun getIngredients(id: String): Flow<Ingredient?>
    suspend fun getIngredientsDistinctUntilChanged(id: String) = getIngredients(id).distinctUntilChanged()
     */

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(ingredientsEntity: IngredientsEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(reviewsEntity: ReviewsEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(storeEntity: StoreEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(storeReviewMappingEntity: StoreReviewMappingEntity): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(storeIngredientMappingEntity: StoreIngredientMappingEntity): Long

    /* Allows removal of Review/Ingredient respectively (if only one store then no need for storeId but just-in-case) */
    @Query("DELETE FROM store_review_map WHERE storeReviewMap_ReviewId=:reviewId AND storeReviewMap_StoreId=:storeId")
    fun deleteReviewFromStore(storeId: String, reviewId: String): Int
    @Query("DELETE FROM store_ingredient_map WHERE storeIngredientMap_IngredientId=:ingredientId AND storeIngredientMap_StoreId=:storeId")
    fun deleteIngredientFromStore(storeId: String, ingredientId: String): Int

    @Transaction
    @Query("SELECT * FROM store")
    fun getAllStoresWithReviewsAndWithIngredients(): List<StoreWithMappedReviewsAndWithMappedIngredients>

}

• Note for the example .allowMainThreadQueries has been used so suspend not used just add as appropriate.

Nearly there here's an @Database annotated class :-

@Database(entities = [
    StoreEntity::class,
    ReviewsEntity::class,
    IngredientsEntity::class,
    StoreReviewMappingEntity::class,
    StoreIngredientMappingEntity::class
                     ],
    version = 1,
    exportSchema = false /* consider true  see https://developer.android.com/training/data-storage/room/migrating-db-versions */
)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "the_database.db"
                )
                    .allowMainThreadQueries() /* for convenience/brevity of the example */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Finally putting it al together in an Activity which inserts 2 Stores, 4 reviwa, 4 Ingredients and maps reviews and ingredients to only the first Store and finally extracts all of the Stores with the Reviews and Ingredients and writes the result to the log. :-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

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

        val store1Id = "S001"
        val store2Id = "S002"
        val store1 =  dao.insert(StoreEntity(store1Id))
        val store2 = dao.insert(StoreEntity(store2Id))

        val mercuryId = "I001"
        val silverId = "I002"
        val  copperid = "I003"
        val zincId = "I004"
        val ing01= dao.insert( IngredientsEntity(ingredientId = mercuryId, Ingredient("Mercury","Quicksilver- Hazardous")))
        val ing02 = dao.insert(IngredientsEntity(silverId, Ingredient("Silver","Au - Metal - Safe blah ...")))
        val ing03 = dao.insert(IngredientsEntity( ingredient = Ingredient("Copper","Cu - Metal - Safe"),ingredientId = copperid))
        val ing04 = dao.insert(IngredientsEntity(ingredientId = zincId,ingredient = Ingredient(ingredientDescription = "Zn - Metal - Safe", ingredientName = "Zinc")))
        /* note ing?? values will be either 1 or greater (the rowid) or -1 if the row was not inserted (e.g. duplicate conflict ignored)*/

        val r1Id = "R001"
        val r2Id = "R002"
        val r3Id = "R003"
        val r4Id = "R004"
        val r1 = dao.insert(ReviewsEntity(r1Id, Review("Review 1 - etc","Reviewer1")))
        val r2 = dao.insert(ReviewsEntity(r2Id,Review("Review 2 - etc","Reviewer9")))
        val r3 = dao.insert(ReviewsEntity(r3Id,Review("Review 3 - etc","Reviewer1")))
        val r4 = dao.insert(ReviewsEntity(r4Id,Review("Review 4 - etc","Reviewer8")))


        dao.insert(StoreReviewMappingEntity(store1Id,r2Id))
        dao.insert(StoreReviewMappingEntity(store1Id,r3Id))
        dao.insert(StoreReviewMappingEntity(store1Id,r1Id))
        dao.insert(StoreReviewMappingEntity(store1Id,r4Id))

        dao.insert(StoreIngredientMappingEntity(store1Id,zincId))
        dao.insert(StoreIngredientMappingEntity(store1Id,mercuryId))
        dao.insert(StoreIngredientMappingEntity(store1Id,copperid))
        dao.insert(StoreIngredientMappingEntity(store1Id,silverId))
        dao.insert(StoreIngredientMappingEntity(store1Id,zincId)) //<<<< due to onconflict ignore will not be inserted as duplicate

        val sb: java.lang.StringBuilder = java.lang.StringBuilder().append("Extracting All Stores with Reviews and Ingredients:-")
        for (swmrawmi: StoreWithMappedReviewsAndWithMappedIngredients in dao.getAllStoresWithReviewsAndWithIngredients()) {
            sb.append("\nCurrentStore is ${swmrawmi.storeEntity.current} it has ${swmrawmi.reviewList.size} Reviews and ${swmrawmi.ingredientList.size} ingredients")
            sb.append("\n\tThe Reviews Are:-")
            for(r: ReviewsEntity in swmrawmi.reviewList) {
                sb.append("\n\t\tID is ${r.reviewId}, Title is ${r.review.reviewTitle} etc")
            }
            sb.append("\n\tThe ingredients Are :-")
            for(i: IngredientsEntity in swmrawmi.ingredientList) {
                sb.append("\n\t\tID is ${i.ingredientId}, Name is ${i.ingredient.ingredientName} Description is ${i.ingredient.ingredientDescription}")
            }
        }
        Log.d("RESULTINFO",sb.toString())
    }
}

and the result from the log when run :-

D/RESULTINFO: Extracting All Stores with Reviews and Ingredients:-
    CurrentStore is S001 it has 4 Reviews and 4 ingredients
        The Reviews Are:-
            ID is R001, Title is Review 1 - etc etc
            ID is R002, Title is Review 2 - etc etc
            ID is R003, Title is Review 3 - etc etc
            ID is R004, Title is Review 4 - etc etc
        The ingredients Are :-
            ID is I001, Name is Mercury Description is Quicksilver- Hazardous
            ID is I002, Name is Silver Description is Au - Metal - Safe blah ...
            ID is I003, Name is Copper Description is Cu - Metal - Safe
            ID is I004, Name is Zinc Description is Zn - Metal - Safe
    CurrentStore is S002 it has 0 Reviews and 0 ingredients
        The Reviews Are:-
        The ingredients Are :-