sqlite 在数据层次中的两个不同位置嵌入多对一RoomDAO对象，其中单个条目多次出现

uklbhaso 于 2023-03-08 发布在 SQLite

关注(0)|答案(1)|浏览(87)

我有下面的数据结构工作包括一个工人列表和海报列表海报有一个工人。
我能够获得嵌入到Job对象中的工人和海报列表;但我无法将单个工作线程放入海报对象。
我尝试过使用关系和实体，但遇到了实体不能包含关系的问题，为了能够包含关系，它必须是实体。这意味着当我尝试使用PosterWithWorker类将Worker关系添加到Poster对象时，JobsWithWorkersAndPosters会拒绝它，因为它不是实体，我不能使它成为实体。
我尝试使用外键将worker表Map到poster表。

@Entity
    (tableName = "poster")
data class Poster (
    @PrimaryKey val poster_id: Int,
    val job_id: Int,
    val qr_code: String,
    var status: Int,
    @ColumnInfo(name = "worker_id", index = true)
    val worker_id: Int,
    val longitude: Double,
    val latitude: Double)

@Entity(tableName = "worker",
        foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )])
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,

)

data class PosterWithWorker(
        @Embedded val poster: Poster,
        @Relation(
            parentColumn = "poster_id",
            entityColumn = "worker_id"
        )
        val worker:  Worker
)

data class JobWithPostersAndWorkers(
    @Embedded val job: Job,
    @Relation(
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(JobWorkerCrossRef::class)
    )
    val workers: List<Worker>,

    @Relation(entity=Poster::class, parentColumn = "job_id", entityColumn = "job_id")
    val poster: List<PosterWithWorker>
)

data class PosterWithWorker(
        @Embedded val poster: Poster,
        @Relation(
            parentColumn = "poster_id",
            entityColumn = "worker_id"
        )
        val worker:  Worker
)

我遵循这个Android room - 3 one-to-many relationship示例，并尝试遵循相同的嵌套模式。
我尝试了不同的变体，得到了不同的错误。
我的第一个问题是，这是否可以不使用外键来完成，然后我做错了什么。

sqlite

来源：https://stackoverflow.com/questions/75656155/embed-a-many-to-one-room-dao-object-with-multiple-occurence-of-single-entry-in-t

1条答案

按热度按时间

aiqt4smr1#

我的第一个问题是，这是否可以不使用外键来完成，然后我做错了什么。
是，参考答案包括：
foreignKeys是可选的，但可以强制并帮助维护参照完整性。在外键中，onDelete和onUpdate是可选的
在SQLite中，一个外键是Room的 Package 器，它是一个约束（一个规则），其中FK约束所应用到的列中的值（子列）必须是一个存在于被引用的列中的值（父列）。即，称为引用完整性。另一种思考方式是外键约束禁止孤儿。
在您的场景中（根据注解 ，我想在poster类中添加一个worker字段。每个poster只有一个worker。）
使用：-

@Entity(tableName = "worker",
        foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )])
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,

)

您实际上是在说worker_id列必须是Poster表的poster_id列中的一个现有值，从而将worker的数量限制为Poster的数量。
我相信你想要的是：

@Entity(
    tableName = "poster",
    foreignKeys = [
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Poster (
    @PrimaryKey val poster_id: Int,
    val job_id: Int,
    val qr_code: String,
    var status: Int,
    @ColumnInfo(name = "worker_id", index = true)
    val worker_id: Int,
    val longitude: Double,
    val latitude: Double)

@Entity(
    tableName = "worker",
    /*
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )]*/
)
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,
    )

data class PosterWithWorker(
    @Embedded val poster: Poster,
    @Relation(
        parentColumn = "worker_id",
        entityColumn = "worker_id"
    )
    val worker:  Worker
)

注意Worker类中注解掉的FKey。

例如，海报包括对单个工人（必须存在）的引用。即海报是（海报是）工人的孩子。工人可以有许多孩子（海报）。

- 其他**

重新
作业包括工人列表和海报列表，海报只有一个工人。
这与下面的评论相矛盾
事实上，这里还有另一个困惑，理想的海报不需要工人。
你还评论
我尝试了您的解决方案，但遇到FOREIGN KEY约束失败（代码787 SQLITE_CONSTRAINT_FOREIGNKEY）错误。
这是因为在父表中没有空行，因此如果您试图引用无海报，则规则将被破坏。
定义外键时，定义FK的列将是子列，引用的列将是父列。
假设这里有一个解决方案，它允许一个作业有许多（0-百万）工作者，并且同一个工作者可以是许多作业的一部分。这是一个多-多关系。它也适合尴尬的海报工作者，但也适合引用完整性（外键）。
所以你可以从三个核心表格开始，工作，工人和海报。前两个是独立于其他表格的。

@Entity
data class Job(
    @PrimaryKey
    val job_id: Long?=null,
    val jobName: String
)
@Entity
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Long?=null,
    val workerName: String,
)

作为一个海报必须有一个工作作为它的父和一个工作可以有许多海报（即海报列表）。这是一个1（工作）到许多（海报）。一个外键可以用来加强引用完整性。即海报不能存在没有一个父。所以：-

@Entity(
    foreignKeys = [
        /* POSTER MUST be a child of a Job (no Job no poster)*/
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Poster(
    @PrimaryKey
    val poster_id: Long?=null,
    @ColumnInfo(index = true)
    val job_id_map: Long, /* CHILD OF Job */
    val qr_code: String,
    var status: Int,
    val longitude: Double,
    val latitude: Double
)

接下来是Workers per Job的列表，假设一个Worker可以是多个工作的Worker，因此存在一个many0many关系，因此存在一个中间/交叉引用/Map/关联....表。
因此：

@Entity(
    primaryKeys = ["job_id_map","worker_id_map"], /* composite primary key */
    foreignKeys = [
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class JobWorkerMap(
    val job_id_map: Long,
    @ColumnInfo(index = true)
    val worker_id_map: Long,
)

这在前面已经涉及。

在此阶段，海报的工作者已被省略。因此，可能出现无工作者的情况。然而，为了允许工作者，可以使用Map表（0-多个），但需要限制为单个工作者（如果有）。这可以通过仅在海报列上设置主键而不是在两列的组合上设置主键来实现。因此：-

@Entity
data class PosterWorkerRestrictedMap(
    @PrimaryKey /*Primary Key restricts to 1 poster */
    val poster_id_map: Long,
    @ColumnInfo(index = true)
    val worker_id_map: Long
)

若要支持海报包含此0或1工人，则：-

data class PosterWithWorkerOrNot(
    @Embedded
    val poster: Poster,
    @Relation(
        entity = Worker::class,
        parentColumn = "poster_id",
        entityColumn = "worker_id",
        associateBy = Junction(
            value = PosterWorkerRestrictedMap::class,
            parentColumn = "poster_id_map",
            entityColumn = "worker_id_map"
        )
    )
    val worker: List<Worker> /* List should be empty or have 1 element */
)

最后，为了迎合工作与它的工人和它的海报与工人或不那么：

data class JobWithWorkersAndPosterWithPosterWorker(
    @Embedded
    val job:Job,
    @Relation(
        entity = Worker::class,
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(
            value = JobWorkerMap::class,
            parentColumn = "job_id_map",
            entityColumn = "worker_id_map"
        )

    )
    val workers: List<Worker>,
    @Relation(
        entity = Poster::class,
        parentColumn = "job_id",
        entityColumn = "job_id_map"
    )
    val posters: List<PosterWithWorkerOrNot>
)

演示一些DAO函数：

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(worker: Worker): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(poster: Poster): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(job: Job): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(jobWorkerMap: JobWorkerMap): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(posterWorkerRestrictedMap: PosterWorkerRestrictedMap): Long

    @Transaction
    @Query("SELECT * FROM job")
    fun getAllJobsWithWorkersAndPostersWithPosterWorker(): List<JobWithWorkersAndPosterWithPosterWorker>
}

即，插入到所有表中的能力，以及提取具有工人列表和海报列表的作业的查询，其中每个海报列表可以具有或不具有海报工人。

实际演示以下内容：-

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

    val w1id = dao.insert(Worker(null,"W1"))
    val w2id = dao.insert(Worker(null,"W2"))
    val w3id = dao.insert(Worker(null,"W3"))
    val w4id = dao.insert(Worker(null,"W4"))
    val w5id = dao.insert(Worker(null,"W5"))

    val j1id = dao.insert(Job(null,"J1"))
    val j2id = dao.insert(Job(null,"J2"))
    val j3id = dao.insert(Job(null,"J3"))

    val p1id = dao.insert(Poster(null,j1id,"QRXXX",1,10.132,20.78))
    val p2id = dao.insert(Poster(null,j2id,"QRYYY",2,1.333,12.765))
    val p3id = dao.insert(Poster(null,j2id,"QRZZZ",3,2.456,13.675))
    val p4id = dao.insert(Poster(null,j1id,"QRAAA",2,3.213,14.902))

    dao.insert(JobWorkerMap(j1id,w2id))
    dao.insert(JobWorkerMap(j1id,w3id))
    dao.insert(JobWorkerMap(j1id,w1id))
    dao.insert(JobWorkerMap(j2id,w4id))
    dao.insert(JobWorkerMap(j2id,w5id))
    dao.insert(JobWorkerMap(j2id,w3id))
    dao.insert(JobWorkerMap(j2id,w1id))
    logAll("_R1")

    dao.insert(PosterWorkerRestrictedMap(p1id,w2id))
    dao.insert(PosterWorkerRestrictedMap(p2id,w4id))
    dao.insert(PosterWorkerRestrictedMap(p3id,w5id))
    logAll("_R2")

}

fun logAll(suffix: String) {
    for (jwwapwpw in dao.getAllJobsWithWorkersAndPostersWithPosterWorker()) {
        val sbwl = StringBuilder()
        val sbpl = StringBuilder()
        for (w in jwwapwpw.workers) {
            sbwl.append("\n\tWorker is ${w.workerName} ID is ${w.worker_id}")
        }
        for (p in jwwapwpw.posters) {
            sbpl.append("\n\tPoster is ${p.poster.qr_code}")
            if (p.worker.size > 0) {
                sbpl.append(" PosterWorker is ${p.worker[0].workerName} ID is ${p.worker[0].worker_id}")
            } else {
                sbpl.append(" NO POSTER WORKER")
            }
        }
        Log.d("DBINFO${suffix}","JOB IS ${jwwapwpw.job.jobName} it has ${jwwapwpw.workers.size} Workers and ${jwwapwpw.posters.size}. posters. They are${sbwl}${sbpl} ")
    }
}

- 结果**日志的输出：-

2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J1 it has 3 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W2 ID is 2
        Worker is W3 ID is 3
        Poster is QRXXX NO POSTER WORKER
        Poster is QRAAA NO POSTER WORKER 
2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J2 it has 4 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W3 ID is 3
        Worker is W4 ID is 4
        Worker is W5 ID is 5
        Poster is QRYYY NO POSTER WORKER
        Poster is QRZZZ NO POSTER WORKER 
2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J3 it has 0 Workers and 0. posters. They are 


2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J1 it has 3 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W2 ID is 2
        Worker is W3 ID is 3
        Poster is QRXXX PosterWorker is W2 ID is 2
        Poster is QRAAA NO POSTER WORKER 
2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J2 it has 4 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W3 ID is 3
        Worker is W4 ID is 4
        Worker is W5 ID is 5
        Poster is QRYYY PosterWorker is W4 ID is 4
        Poster is QRZZZ PosterWorker is W5 ID is 5 
2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J3 it has 0 Workers and 0. posters. They are

请注意，R1是在任何工人被分配到任何海报之前。R2是在一些工人被分配之后。因此，输出已被拆分，以便于查看。
可以看到，作业J1有2个海报，一个有工人，另一个没有工人。避免了空值的潜在问题，以及当海报没有工人时的FK错误。

赞(0）回复(0）举报 2023-03-08

我来回答

sqlite 在数据层次中的两个不同位置嵌入多对一RoomDAO对象，其中单个条目多次出现

1条答案

相关问题

热门标签

最新问答