One to many relationship in Room without Relation

Here's what I'm trying to achieve:

A User entity (app can have multiple users)

Each User has a multiple University objects
Each University has multiple Semester objects
Each Semester has a list of Course objects

An Event entity (a User can have multiple events)

I want to be able to:

Insert a user
User inserts a university
User inserts semesters, courses, and so on
User can access a list of all of the above when he wants (only his own data, not other users')

How do I achieve the above? I started with the User object having an @Embedded field of University, which in turn has an @Embedded field Semester but how do I separate User data from each other? Do all these classes need a separate Dao or can one UserDao work for everything (since they're all nested and use @Embedded?

Solution

If you mean with no @Relation rather than without relationships, then perhaps consider the following which allows :-

data class UserEvents(
    @Embedded
    val user: User,
    @Embedded
    val university: University,
    @Embedded
    val semester: Semester,
    @Embedded
    val course: Course
)

and from the demo/example below will produce something along the lines of :-

2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part1: Semester is Semester 1 2022, from 2022-01-17 to 2022-03-10:Univeristy is Cambridge:User is Jane
2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part2: Semester is Semester 2 2022, from 2022-03-24 to 2022-06-14:Univeristy is Cambridge:User is Jane
2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part 1: Semester is Semester 1, from 2022-01-15 to 2022-03-31:Univeristy is Oxford:User is Jane

2021-11-06 13:25:28.965 D/EVENTS_1: Event is Mathematcis part2: Semester is Semester 3 2022, from 2022-06-24 to 2022-09-15:Univeristy is Cambridge:User is Fred
2021-11-06 13:25:28.965 D/EVENTS_1: Event is Mathematcis part2: Semester is Semester 4 2022, from 2022-10-03 to 2022-12-15:Univeristy is Cambridge:User is Fred

noting that Jane's UserEvents are extracted when Jane is logged in and Fred's when Fred is logged in.

So in addition to the UserEvents POJO there are @Entityclasses :-

User :-

@Entity
data class User (
    @PrimaryKey
    val userId: Long? = null,
    val userName: String,
    val userPassword: String,
    val userOtherData: String
    )

University

@Entity(
    indices = [
        Index(value = ["universityName"], unique = true)
    ]
)
data class University(
    @PrimaryKey
    val universityId: Long?=null,
    val universityName: String,
    val universityOtherData: String
)

Semester

@Entity
data class Semester(
    @PrimaryKey
    val semesterId: Long?=null,
    val semesterName: String,
    val semesterStartDate: String,
    val semesterEndDate: String,
    val semesterUniversityMap: Long
)

Course

@Entity
data class Course(
    @PrimaryKey
    val courseId: Long?=null,
    val courseName: String,
    val courseSemesterMap: Long
)

UserCourseMap noting that the relationship offered is many-many but that can facilitate 1-many.

@Entity(
    primaryKeys = ["userCourseMapUserId","userCourseMapCourseId"],
    indices = [
        Index(value = ["userCourseMapCourseId"]
        )
    ])
data class UserCourseMap(
    val userCourseMapUserId: Long,
    val userCourseMapCourseId: Long
)

The design above doesn't earmark the universities, semesters or courses to a specific user. They are shared e.g. so user 1 adds Oxford uni and then user 2 tries it is not duplicated but already exists and so on.

Only a user's enrolment in a course and therefore the semester and therefore the uni is specific to a user.

Do all these classes need a separate Dao or can one UserDao

AllDao :-

@Dao
abstract class AllDao {

    @Insert(onConflict = IGNORE)
    abstract fun insert(user: User): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(university: University): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(semester: Semester): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(course: Course): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(userCourseMap: UserCourseMap): Long

    @Query("SELECT universityId FROM University WHERE universityName=:universityName")
    abstract fun getUniversityIdByName(universityName: String): Long
    @Query("SELECT semesterId FROM semester WHERE semesterName=:semesterName AND semesterUniversityMap=:universityId")
    abstract fun getSemesterByNameAndUniversityId(semesterName: String, universityId: Long): Long
    @Query("SELECT courseId FROM course WHERE courseName=:courseName AND courseSemesterMap=:semesterId")
    abstract fun getCourseByCourseNameAndSemesterId(courseName: String, semesterId: Long): Long

    @Query("SELECT coalesce(max(userid),-1) FROM user WHERE userName=:userName AND userPassword =:userPassword")
    abstract fun userLogin(userName: String, userPassword: String): Long

    @Query("SELECT * FROM usercoursemap " +
            "JOIN User on userCourseMapUserId = userId " +
            "JOIN course on userCourseMapCourseId = courseId " +
            "JOIN semester ON courseSemesterMap = semesterId " +
            "JOIN university ON semesterUniversityMap = universityId " +
            "WHERE userId=:userId")
    abstract fun getUserEvents(userId: Long): List<UserEvents>

}

up to you if you want to have multiple @Daos

how do I separate User data from each other?

See above re UserCourseMap and the getUserEvents dao

I would suggest, if happy with the above, considering defining foreign key constraints to enforce referential integrity but have omitted them for brevity and to reduce the complexity of understanding.

So with a pretty typical @Database TheDatabase :-

@Database(entities = [
    User::class,University::class,Semester::class,Course::class,UserCourseMap::class,
    version = 1)
@TypeConverters(DateTimeConverter::class)
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,"thedatabase.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

an exception to being typical is that for brevity .allowMainThreadQueries has been utilised.

Finally putting it all together in a demonstration (that produced the output above) MainActivity :-

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()
        var currentUserId: Long = -1 /* no user not logged in */

        /* Add a couple of users */
        dao.insert(User(userName = "Fred",userPassword = "passwordforfred", userOtherData = "blah"))
        dao.insert(User(userName = "Jane", userPassword = "passwordforjane", userOtherData = "blah"))

        /* add some universities, semesters and courses all 3 are globally accessible */
        val yaleid = dao.insert(University(universityName = "Yale", universityOtherData = "blah"))
        val cambridgeid = dao.insert(University(universityName = "Cambridge", universityOtherData = "blah"))
        val semester1yale = dao.insert(Semester(semesterName = "Semester 1 2022", semesterStartDate = "2022-01-23", semesterEndDate = "2022-04-07", semesterUniversityMap = yaleid))
        val semester2yale = dao.insert(Semester(semesterName = "Semester 2 2022", semesterStartDate = "2022-04-17", semesterEndDate = "2022-07-01", semesterUniversityMap = yaleid))
        val semester3yale = dao.insert(Semester(semesterName = "Semester 3 2022", semesterStartDate = "2022-07-28", semesterEndDate = "2022-10-01", semesterUniversityMap = yaleid))
        val semester4yale = dao.insert(Semester(semesterName = "Semester 4 2022", semesterStartDate = "2022-10-25", semesterEndDate = "2022-12-18", semesterUniversityMap = yaleid))
        val semester1camb = dao.insert(Semester(semesterName = "Semester 1 2022", semesterStartDate = "2022-01-17", semesterEndDate = "2022-03-10", semesterUniversityMap = cambridgeid))
        val semester2camb = dao.insert(Semester(semesterName = "Semester 2 2022", semesterStartDate = "2022-03-24", semesterEndDate = "2022-06-14", semesterUniversityMap = cambridgeid))
        val semester3camb = dao.insert(Semester(semesterName = "Semester 3 2022", semesterStartDate = "2022-06-24", semesterEndDate = "2022-09-15", semesterUniversityMap = cambridgeid))
        val semester4camb = dao.insert(Semester(semesterName = "Semester 4 2022", semesterStartDate = "2022-10-03", semesterEndDate = "2022-12-15", semesterUniversityMap = cambridgeid))

        val coursecambengp1 = dao.insert(Course(courseName = "English part1",courseSemesterMap = semester1camb))
        val coursecambengp2 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester2camb))
        val coursecambengp3 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester3camb))
        val coursecambengp4 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester4camb))

        val coursecambmthp1 = dao.insert(Course(courseName = "Mathematics part1",courseSemesterMap = semester1camb))
        val coursecambmthp2 = dao.insert(Course(courseName = "Mathematics part2",courseSemesterMap = semester2camb))
        val coursecambmthp3 = dao.insert(Course(courseName = "Mathematcis part2",courseSemesterMap = semester3camb))
        val coursecambmthp4 = dao.insert(Course(courseName = "Mathematcis part2",courseSemesterMap = semester4camb))

        /* Logon in eventually to Jane, after 2 failed login attempts */
        currentUserId = dao.userLogin("Not a known user","obviously not a valid password")
        if (currentUserId < 1) {
            /* try again */
            currentUserId = dao.userLogin("Fred","wrongpassword")
            if (currentUserId < 1) {
                currentUserId = dao.userLogin("Jane","passwordforjane")
            }
        }
        if (currentUserId > 0) {

            /* all in one add of English part 1 - semster 1 at Oxford (i.e. bar the user all are added in one go) */
            dao.insert(
                UserCourseMap(
                    userCourseMapUserId = currentUserId,
                    userCourseMapCourseId =
                    dao.insert(
                        Course(
                            courseName = "English part 1",
                            courseSemesterMap = dao.insert(
                                Semester(
                                    semesterName = "Semester 1",
                                    semesterStartDate =  "2022-01-15",
                                    semesterEndDate = "2022-03-31",
                                    semesterUniversityMap = dao.insert(
                                        University(
                                            universityName = "Oxford",
                                            universityOtherData = "blah"
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )

            /* add event (mapping course to user and this implicitly adding semester and uni) to pre-existing */
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambengp1))
            dao.insert(UserCourseMap(userCourseMapCourseId = currentUserId,userCourseMapUserId = coursecambengp2))
        }

        /* get the events for Jane */
        for(ue: UserEvents in dao.getUserEvents(currentUserId)) {
            Log.d("EVENTS_1",
                "Event is ${ue.course.courseName}: " +
                        "Semester is ${ue.semester.semesterName}, from ${ue.semester.semesterStartDate} to ${ue.semester.semesterEndDate}:" +
                        "Univeristy is ${ue.university.universityName}:" +
                        "User is ${ue.user.userName}")
        }


        /* SWITCH TO USER FRED */
        currentUserId = dao.userLogin("Fred","passwordforfred")

        if (currentUserId > 0) {
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambmthp3))
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambmthp4))
        }

        /* Get the events for Fred */
        for(ue: UserEvents in dao.getUserEvents(currentUserId)) {
            Log.d("EVENTS_1",
                "Event is ${ue.course.courseName}: " +
                        "Semester is ${ue.semester.semesterName}, from ${ue.semester.semesterStartDate} to ${ue.semester.semesterEndDate}:" +
                        "Univeristy is ${ue.university.universityName}:" +
                        "User is ${ue.user.userName}")
        }
    }
}