How to run multiple plain sql queries ― multiple times ― in a single transaction in slick?

user_784980 0
Viewed 79

I have a Play/Slick application where I need to run several plain sql queries multiple times (n) in a single transaction.

Here is the code for one iteration, i.e. when n = 1:

val query1 = sql"""insert into some_table_1 (date) values (NOW())""".asUpdate
val query2 = sql"""insert into some_table_2 (date) values (NOW())""".asUpdate
val query3 = sql"""insert into some_table_3 (date) values (NOW())""".asUpdate

val composedAction = for {
  result1 <- query1
  result2 <- query2
  result3 <- query3
} yield result3

val result = db.run(composedAction.transactionally)

Now, I actually want to change the above composedAction so that it would iterate over the 3 queries n = 1,000 times in one transaction (i.e. I want the 3 * 1,000 = 3,000 queries to either all fail or all succeed).

How can I do that?

Update:

The reason I want to keep the for structure is that result1 is the input of query2, result2 is the input of query3.

Something like this (it doesn't compile):

def query1(number: Int) = sql"""insert into some_table_1 (number) values ($number)""".asUpdate
def query2(number: Int) = sql"""insert into some_table_2 (number) values ($number)""".asUpdate
def query3(number: Int) = sql"""insert into some_table_3 (number) values ($number)""".asUpdate

val composedAction = for {
  i <- (1 to 1000)
  result1 <- query1(i)
  result2 <- query2(result1)
  result3 <- query3(result2)
} yield result3 //this is the 1000th result3
slick playframework scala transactions sql
1 Answers

I couldn't try this locally, but you can try this and see if it helps:

// A type alias for ease of use
type Query = SqlStreamingAction[Vector[Int], Int, Effect]#ResultAction[Int, NoStream, Effect]
// this type alias is basically the type of q1, q2, q3

val actions = (1 to 1000).foldLeft(List.empty[Query]) {
  case (agg, _) =>
    q1 :: q2 :: q3 :: agg // append queries in order like this
}
db.run(
  DBIO.sequence(actions).transactionally
)

I assumed that you want to have a list of queries such as this:

List(q1, q2, q3, q1, q2, q3, q1, q2, q3, ...)

If you wanted something else, that would be easy to manage. Like if you need:

List(q1, q1, q1, q1, q1, ..., q2, q2, q2, q2, q2, ..., q3, q3, q3, q3, q3, ...)

You would want to do something like this to order your queries:

val range = 1 to 1000
val actions = range.map(_ => q1).toList ::: range.map(_ => q2).toList ::: range.map(_ => q3).toList

Update

Since I find that type aliasing a bit annoying, you can also use this instead:

val queries = q1 :: q2 :: q3 :: Nil
val actions = (1 until 1000).foldLeft(queries) { // note the until instead of to here
  case (aggregator, _) => queries ::: aggregator
}
db.run(
  DBIO.sequence(actions).transactionally
)

Update No. 2

Based on your comment, yes you can also do that, try this:

val composedAction = {
  for {
    r1 <- q1
    r2 <- q2
    r3 <- q1
  } yield r3
}.transactionally // maybe also remove this transactionally call

val allActions = (1 until 1000).foldLeft(composedAction) { 
  case (queryAgg, _) => 
    queryAgg.flatMap(_ => composedAction)
}
db.run(allActions.transactionally)
user_146723830
Related