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
CodePudding user response:
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(accActions.transactionally)