在Slick 3中的事务中执行非数据库操作 [英] Executing non-database actions in a transaction in Slick 3

问题描述

我无法理解新的Slick DBIOAction API，它在文档中似乎没有很多示例。我使用Slick 3.0.0，我需要执行一些数据库操作和一些计算与从数据库接收的数据，但所有这些操作必须在一个事务中完成。我尝试执行以下操作：

1. 对数据库执行查询（ types

2. 执行查询结果的汇总和过滤（此计算无法在数据库上完成）。


3. 执行另一个查询，基于从步骤2（ messages 表）的计算，由于一些限制，此查询必须在原始SQL中）。


4. 将来自第2步和第3步的数据合并到内存中。

我希望在第1步和第3步执行查询一个事务，因为它们的结果集中的数据必须是一致的。

我试图在monadic连接风格中这样做。这是一个过于简化的代码，但我甚至不能编译：

  val compositeAction = 
 rawTypes<  -  TableQuery [DBType] .result 
（projectId，types）<  -  rawTypes.groupBy（_。projectId）.toSeq.map（group =>（group._1，group。 _2.slice（0，10）））
 counts < -  DBIO.sequence（types.map（aType => sqlselect count（*）from message where type_id = $ {aType.id} ;.as [Int]））
} yield（projectId，types.zip（counts）））transactionally 
  

1. 的第一行选择 types $ c> table。


2. 的第二行应该对结果做一些分组和切片， Seq [（Option [String]，Seq [String]）]


3. / code> comprehension必须为上一步中的每个元素执行一组查询，特别是必须对 Seq [String] 中的每个值执行单个SQL查询。 / code>。因此，在第三行中，我构建了一个 DBIOAction 的序列。


4. yield $ $ zip s >




但是，此结构不起作用，并产生两个编译时错误：

 错误：（129，16）类型不匹配; 
 found：slick.dbio.DBIOAction [（Option [String]，Seq [（com.centreit.proto.repiso.storage.db.models.DBType＃TableElementType，Vector [Int]）]），slick.dbio .NoStream，slick.dbio.Effect] 
（扩展为）slick.dbio.DBIOAction [（Option [String]，Seq [（com.centreit.proto.repiso.storage.db.models.TypeModel，Vector [int]）]），slick.dbio.NoStream，slick.dbio.Effect] 
必需：scala.collection.GenTraversableOnce [？] 
 count < -  DBIO.sequence（types.map => sqlselect count（*）from message where type_id = $ {aType.id}as。[Int]））
 ^ 
错误：（128，28）不匹配
 found：Seq [Nothing] 
必需：slick.dbio.DBIOAction [？，？，？] 
（projectId，types）<  -  rawTypes.groupBy（_。projectId）.toSeq .map（group =>（group._1，group._2.slice（0，10）））
 ^ 
  

$ b b


我尝试使用 DBIO.successful DBIOAction >，它应该将一个常量值提升到 DBIOAction monad：

 （projectId，types）<  -  DBIO.successful（rawTypes.groupBy（_。projectId）.toSeq.map（group =>（group._1，group._2.slice（0，10）））
  

但是在这段代码中， types


解决方案

p>尝试这样：

  val compositeAction =（for {
 rawTypes<  -  TableQuery [DBType]。 result 
 pair<  -  DBIO.sequence（rawTypes.groupBy（_。projectId）.toSeq.map（group => DBIO.successful（group）））
 counts < -  DBIO.sequence（pair.head._2.map（aType => sqlselect count（*）from message where type_id = $ {aType。 id}。as [Int]））
} yield（pair.head._1，pair.head._2.zip（counts）））transactionally 
  




I'm having trouble understanding the new Slick DBIOAction API, which does not seem to have a lot of examples in the docs. I am using Slick 3.0.0, and I need to execute some DB actions and also some calculations with the data received from the database, but all of those actions have to be done inside a single transaction. I'm trying to do the following:

1. Execute a query to database (the types table).
2. Do some aggregations and filtering of the query results (this calculation can't be done on the database).
3. Execute another query, based on the calculations from step 2 (the messages table — due to some limitations, this query has to be in raw SQL).
4. Join data from step 2 and 3 in memory.

I want the queries from step 1 and 3 to be executed inside a transaction, as the data from their result sets has to be consistent.

I've tried to do this in a monadic join style. Here's an overly simplified version of my code, but I can't even get it to compile:

  val compositeAction = (for {
    rawTypes <- TableQuery[DBType].result
    (projectId, types) <- rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10)))
    counts <- DBIO.sequence(types.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
  } yield (projectId, types.zip(counts))).transactionally

1. The first row of for comprehension selects the data from the types table.
2. The second row of for comprehension is supposed to do some grouping and slicing of the results, resulting in a Seq[(Option[String], Seq[String])]
3. The third row of for comprehension has to execute a set of queries for every element from the previous step, in particular, it has to execute a single SQL query for each of the values inside Seq[String]. So in the third row I build a sequence of DBIOActions.
4. The yield clause zips types from the second step and counts from the third step.

This construction, however, does not work and gives two compile time errors:

Error:(129, 16) type mismatch;
 found   : slick.dbio.DBIOAction[(Option[String], Seq[(com.centreit.proto.repiso.storage.db.models.DBType#TableElementType, Vector[Int])]),slick.dbio.NoStream,slick.dbio.Effect]
    (which expands to)  slick.dbio.DBIOAction[(Option[String], Seq[(com.centreit.proto.repiso.storage.db.models.TypeModel, Vector[Int])]),slick.dbio.NoStream,slick.dbio.Effect]
 required: scala.collection.GenTraversableOnce[?]
        counts <- DBIO.sequence(types.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
               ^
Error:(128, 28) type mismatch;
 found   : Seq[Nothing]
 required: slick.dbio.DBIOAction[?,?,?]
        (projectId, types) <- rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10)))
                           ^

I've tried to wrap the second line in a DBIOAction by using DBIO.successful, which is supposed to lift a constant value into the DBIOAction monad:

(projectId, types) <- DBIO.successful(rawTypes.groupBy(_.projectId).toSeq.map(group => (group._1, group._2.slice(0, 10))))

But in this code the types variable is inferred to be Any, and the code does not compile because of that.

解决方案

Try it this way :

val compositeAction = (for {
  rawTypes <- TableQuery[DBType].result
  pair <- DBIO.sequence(rawTypes.groupBy(_.projectId).toSeq.map(group => DBIO.successful(group)))
  counts <- DBIO.sequence(pair.head._2.map(aType => sql"""select count(*) from messages where type_id = ${aType.id}""".as[Int]))
} yield (pair.head._1, pair.head._2.zip(counts))).transactionally

这篇关于在Slick 3中的事务中执行非数据库操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！