Is it possible to run dependent queries transactionally?

[Josh]

I understand that a sequence of DB operations can be executed transactionally, using DBIO.seq.

This is fine if the operations do not depend on each other.

But if I have two operations, where one operation depends on the result of the other, is it possible to run them transactionally?

For example, if I want to insert a user and a dog (which requires the owner's userId), currently I do it like this:

val queryResult = for {
  userId <- db.run((users returning users.map(_.userId)) += user)
  dogId <- db.run((dogs returning dogs.map(_.dogId)) += dog.copy(ownerId = Some(userId))

} yield userId

Is it possible to do this transactionally, so that e.g. if the dog update fails, the user update will be rolled back?

Thanks!

[Josh]

By the way, I am using Slick 3.0.0

[Matan Safriel]

Have you tried .transactionally (http://slick.typesafe.com/doc/3.0.0-RC3/dbio.html#transactions-and-pinned-sessions) ?

[Stefan Zeiger]

On 2015-04-13 17:28, Josh wrote:

val queryResult = for {

  userId <- db.run((users returning users.map(_.userId)) += user)
  dogId <- db.run((dogs returning dogs.map(_.dogId)) += dog.copy(ownerId = Some(userId))

} yield userId

Is it possible to do this transactionally, so that e.g. if the dog update fails, the user update will be rolled back?

Compose the calls in DBIO, not in Future:

val queryResult = db.run((for {
  userId <- (users returning users.map(_.userId)) += user
  dogId <- (dogs returning dogs.map(_.dogId)) += dog.copy(ownerId = Some(userId)
} yield userId).transactionally)

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[Josh]

Thanks Stefan, that works well.

Can you give any general advice about when to compose in Future vs in DBIO?

Should you always compose in DBIO where possible, or only when you need to do joins or perform operations in a single transaction or withPinnedSession?

Are there situations when you should compose in Future instead?

[Christopher Vogt]

Hi Josh,

always compose in Query when possible, which means single SQL query. If
not possible compose in DBIO, which means single connection. If not
possible compose in Future, which means multiple connections (but
potential re-use by your pool).

Chris

On 14.04.15 13:32, Josh wrote:
> Thanks Stefan, that works well.
>
> Can you give any general advice about when to compose in Future vs in DBIO?
> Should you always compose in DBIO where possible, or only when you need
> to do joins or perform operations in a single transaction or
> withPinnedSession?
> Are there situations when you should compose in Future instead?
>
> On Tuesday, April 14, 2015 at 9:32:57 AM UTC+1, Stefan Zeiger wrote:
>
> On 2015-04-13 17:28, Josh wrote:

>> valqueryResult = for {

>> userId <- db.run((users returning users.map(_.userId)) += user)
>> dogId <- db.run((dogs returning dogs.map(_.dogId)) += dog.copy(ownerId = Some(userId))
>> } yield userId
>>
>> Is it possible to do this transactionally, so that e.g. if the dog update fails, the user update will be rolled back?
>
> Compose the calls in DBIO, not in Future:
>
> val queryResult = db.run((for {
> userId <- (users returning users.map(_.userId)) += user
> dogId <- (dogs returning dogs.map(_.dogId)) += dog.copy(ownerId =
> Some(userId)
> } yield userId).transactionally)
>
> --
> Stefan Zeiger

> /Slick <http://slick.typesafe.com> Tech Lead/
> Typesafe <http://typesafe.com> - Build Reactive Apps!
> Twitter: @StefanZeiger <http://twitter.com/#%21/StefanZeiger>
>
> --
>
> ---
> You received this message because you are subscribed to the Google
> Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to scalaquery+...@googlegroups.com
> <mailto:scalaquery+...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/scalaquery/94ddf7a0-099f-4262-ae80-b41af59eb88f%40googlegroups.com
> <https://groups.google.com/d/msgid/scalaquery/94ddf7a0-099f-4262-ae80-b41af59eb88f%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Josh]

Hi Chris,

Thanks, that makes sense..

Following on from my original question and Stefan's answer, I'm trying to extend it to a slightly more complex query but can't figure out how. 

Say I want to insert several dogs, and then also create a dog bowl for each one:

val queryResult = db.run((for {
  userId <- (users returning users.map(_.userId)) += user

  dogId <- sequenceOfDogs.map(dog =>

      (dogs returning dogs.map(_.dogId)) += dog.copy(ownerId = Some(userId)

  )

  dogBowlId <- (dogBowls returning dogBowls.map(_.dogBowlId)) += dogBowl.copy(dogId = Some(dogId))
} yield userId).transactionally)

 

This doesn't work because dogId doesn't conform to the Int type any more. 

This is because dogId is now a:

MySQLDriver.DriverAction[Effect.Write, MySQLDriver.ReturningInsertActionComposer[Dogs#TableElementType, Int]#SingleInsertResult, NoStream]

instead of:

MySQLDriver.ReturningInsertActionComposer[Dogs#TableElementType, Int]#SingleInsertResult

How can this type of query be done when composing in DBIO?

I guess I want a Seq(MySQLDriver.ReturningInsertActionComposer[Dogs#TableElementType, Int]#SingleInsertResult) but can't figure out how to get there!

Thanks again,

Josh

[Josh]

Well, I think I've found the correct way to do this:

val queryResult = db.run((for {
  userId <- (users returning users.map(_.userId)) += user

  dogIds <- (dogs returning dogs.map(_.dogId)) ++= sequenceOfDogs.map(dog => dog.copy(ownerId = Some(userId)))

  _ <- dogBowls ++= dogIds.map(dogId => dogBowl.copy(dogId = Some(dogId)))
} yield userId).transactionally)

It really does take a while to figure these queries out....

but neat once it works :)

[Sandeep Singh]

Thanks Josh, the example helped up to one level of dependency ( user having multiple dogs). If I have a second level of dependent object to insert, for example, I have a different dog bowl for a given dog name. One way I thought of solving it was by getting dogId as well as dog name, and then do lookup to get the dog bowl and save it. However query fails, as underlying database only returns a single key, which is id, and name cannot be returned. Is there any other option than returning for a insert stement?

val dogBowlMap: Map[String, DogBowl] = .....

val queryResult = db.run((for {
  userId <- (users returning users.map(_.userId)) += user

  dogIdAndNames <- (dogs returning dogs.map(d=> (d.dogId, d.dogName))) ++= sequenceOfDogs.map(dog => dog.copy(ownerId = Some(userId)))

  _ <- dogBowls ++= dogIdAndNames.map(dogIdName => dogBowlMap(dogIdName._2).copy(dogId = Some(dogIdName_.1)))

} yield userId).transactionally)

[Sandeep Singh]

Was able to resolve it by adding "into" after returning the dog ids, which enabled me to get the actual dog item with id and name.

Thanks!

Sandeep