未能在 Quill 中使用事务插入一对多关系对象 [英] Failing to use transactions in Quill to INSERT one-to-many relational objects

问题描述

我有一个人表和动物表，在动物表中，有到 personId 的 FK，因为它们之间存在一对多的关系.

I have a person table and animal table and in the animal table there is FK to personId since there is one-to-many relation between them.

我只想创建一个人并使用事务创建它的动物，因为我希望该过程是原子的(如果我不能创建它的动物，则数据库中没有人使用)

I just want to create a person and create its animals using a transaction cause I want the process to be atomic (there is no use of person in the db if I could not create its animals)

这是我如何接受个人创建请求的模型:

This is the model of how I accept a person creation request:

case class PersonCreateRequest(name: String, age: Int, animals: Seq[AnimalCreateRequest])

DB 是这样认识一个人的:

This is how the DB knows a Person:

case class Person(personId: Long, name, age: Int)

// this is just a companion object to help me take a PersonCreateRequest and make it Person
object Person {
  def apply(person: PersonCreateRequest): Person = {
    Person(0L,
           person.name,
           person.age)
  }
}

我对动物的看法:

case class AnimalCreateRequest(animalType: String, age: Int)

这就是数据库如何知道一个动物(personId = owner):

This is how the db knows a Animal(personId = owner):

case class Animal(animalId: Long, animalType: String, age: Int, personId: Long)

// here I need to get personId as parameter cause I will only have it after a person was created:
object Animal {
  def apply(animal: AnimalCreateRequest, personId: Long): Animal = {
    Animal(0L,
           animal.animalType,
           animal.age,
           personId)
  }
}

所以现在我尝试这样做(但失败了):

lazy val ctx = new MysqlAsyncContext(CamelCase, "ctx")
  import ctx._


  def insertPerson(personToCreate: PersonCreateRequest): Future[Long] = {

    // getting the person object that the db knows
    val dbPerson = Person.apply(personToCreate)

    // INSERT Person Query
    val insertPersonQuery = quote {
      query[Person].insert(lift(dbPerson)).returning(_.personId)
    }

    ctx.transaction { implicit ec =>
      for {
        personId   <- ctx.run(insertPersonQuery)
        contactIds <- {
          Future.sequence(
          personToCreate.animals.map(animal => {
            val animalToInsert = Animal.apply(animal, personId)
            insertAnimal(animalToInsert)
          })
          )
        }
      } yield personId
    }
  }

  def insertAnimal(animal: Animal): Future[Long] = {
    val q = quote {
      query[Animal].insert(lift(animal)).returning(_.animalId)
    }
    ctx.run(q)
  }

发生的情况是我没有得到响应......它继续处理而不返回任何内容或抛出错误

What happens is that I just dont get a response...its keep processing without returning anything or throwing an error

推荐答案

问题是，目前 Quill async 不支持事务内部的并发操作.

Problem was, currently, Quill async does not support concurrent operations inside transactions.

所以必须按顺序插入动物:

So had to do the animal insertion sequentially:

ctx.transaction { implicit ec =>
  for {
    personId <- ctx.run(insertPersonQuery)
    animals = personCreate.animals.map(Animal.apply(personId, _))
    _ <- animals.foldLeft(Future.successful(0l)) {
      case (fut, animal) =>
        fut.flatMap(_ => insertAnimal(animal))
    }
  } yield personId
}

另外，更好的是使用批量插入:)

also, even better is to use batch insertion :)

感谢@fwbrasil 和@mentegy 的帮助！

Thanks for @fwbrasil and @mentegy for the assistance!

这篇关于未能在 Quill 中使用事务插入一对多关系对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！