Insert if not exists in Slick 3.0.0

[Ivan]

I'm trying to insert if not exists, I found this post in StackOverflow for 1.0.1, 2.0, 

1.0.1

db.withTransaction{ implicit session : Session =>
  if( ! Query(b).filter(_.id==="something").exists.run ){
    b.insert( "something" )
    d.insert( (1,"something") )
  }
}

2.0

val b = TableQuery[b]
db.withTransaction{ implicit session =>
  if( ! b.filter(_.id==="something").exists.run ){
    b += "something"
    d += (1,"something")
  }
}

I found snippet using transactionally in the docs of 3.0.0

val a = (for {
  ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
  _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally

val f: Future[Unit] = db.run(a)

I'm struggling to write the logic from insert if not exists with this structure. I'm new to Slick and have little experience with Scala. This is my an attempt to do insert if not exists outside the transaction...

 val result: Future[Boolean] = db.run(products.filter(_.name==="foo").exists.result)

 result.map { exists =>  

   if (!exists) {

     products += Product(

       None,

       productName,

       productPrice

     )

   }  

 }

But how do I put this in the transactionally block? This is the furthest I can go at the moment:

  val a = (for {

   exists <- products.filter(_.name==="foo").exists.result

   //???  

//    _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)

 } yield ()).transactionally

Thanks in advance

[Matthew Pocock]

Hi Ivan,

Here's some slick 3 code I use to memoise database rows.

def createRequest(url: String, verb: String, hostAndPort: String, createdAt: Timestamp) =
  (urlScanRequests returning urlScanRequests.map(_.id)) += (0L, url, verb, hostAndPort, createdAt)


def lookupRequest(url: Rep[String], verb: Rep[String]) =
  urlScanRequests filter (r => r.url === url && r.verb === verb)

val lookupRequestWith = Compiled(lookupRequest _)

def lookupOrCreate(url: String, verb: String, host: String, createdAt: Timestamp = new Timestamp(new Date().getTime))
                  (implicit ec: ExecutionContext) =
  lookupRequestWith(url, verb).result.headOption.flatMap {
    case Some((id, url, verb, host, createdAt)) =>
      DBIO.successful(ScanRequest(id, url, verb, host, createdAt))
    case None =>
      createRequest(url, verb, host, createdAt) map (id => ScanRequest(id, url, verb, host, createdAt))
  } transactionally

Matthew

--

---
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/8fbf215b-6725-43d2-a0c5-dd3cbdb8320a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Dr Matthew Pocock

Turing ate my hamster LTD

mailto: turingate...@gmail.com

Integrative Bioinformatics Group, School of Computing Science, Newcastle University

mailto: matthew...@ncl.ac.uk

gchat: turingate...@gmail.com

msn: matthew...@yahoo.co.uk

irc.freenode.net: drdozer

skype: matthew.pocock

tel: (0191) 2566550

mob: +447535664143

[Ivan]

Thanks. Right, I don't have to use the for comprehension. Here is the version with my code:

    val a = (

        products.filter(_.name==="foo").exists.result.flatMap { exists =>

          if (!exists) {

            products += Product(

              None,

              productName,

              productPrice

            )

          } else {

            DBIO.successful(None) //no-op

          }

        }

    ).transactionally

[Ivan]

Sorry for the extra spacing, somehow the code I insert from Eclipse gets +1 newline even if I remove them here.

[Ivan]

Mhh I see that my version has some shortcomings. I also to return the inserted or existing object. I tried this time to implement a version more similar to yours (without helper functions, but it should be basically the same), but it doesn't compile, this is the code:

  val a = (

   products.filter(_.name==="foo").result.headOption.flatMap {

     case Some(product) =>

       DBIO.successful(Option(product))

     case None =>

         val productId: FixedSqlAction[Int, NoStream, Effect.Write] =

           (products returning products.map(_.id)) += Product(

           None,

           productName,

           productPrice

         )

         val product = productId.map { id =>  //product is of type DBIOAction, no product

           Product(

             Option(id),

             productName,

             productPrice

           )

         }

          product

//            DBIO.successful(Option(product))

     }

 ).transactionally

  val b: Future[Any] = db.run(a)

  val c: Future[Option[Product]] = db.run(a) // doesn't compile

In your code you are able to map to ScanRequest which seems to be your model object, but I don't get a product there but DBIOAction instead, why is that? 

Am Montag, 8. Juni 2015 12:34:47 UTC+2 schrieb Matthew Pocock:

[Ivan]

Ahhh this is actually because of an issue with which I stumbled first. I used an different syntax for the Product class to be able to use an optional id.

In the documentation says though that the id will be ignored - now I understand why you just pass 0 and don't need optional.

My old product class looked like this:

case class Product(id: Option[Int], name: String, price: BigDecimal)

class Products(tag: Tag) extends Table[Product](tag, "product") {

  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column

 def name = column[String]("name")

 def price = column[BigDecimal]("price", O.SqlType("decimal(10, 4)"))

 def * = (id.?, name, price) <> (Product.tupled, Product.unapply)

}

val products = TableQuery[Products]

But now that I don't need the optional id, I could write it with the new syntax:

class Products2(tag: Tag) extends Table[(Int, String, BigDecimal)](tag, "product") {

 def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column

 def name = column[String]("name")

 def price = column[BigDecimal]("price", O.SqlType("decimal(10, 4)"))

 def * = (id, name, price)

}

val products2 = TableQuery[Products2]

And now I can write the example like yours:

  val a = (

    products2.filter(_.name==="foo").result.headOption.flatMap {

      case Some(product) =>

        DBIO.successful(product)

      case None =>

        val productId =

        (products2 returning products2.map(_.id)) += (

          0,

          productName,

          productPrice

        )

        val product = productId.map { id => (

          id,

          productName,

          productPrice

        )

      }

      product

    }

 ).transactionally

val b: Future[(Int, String, BigDecimal)] = db.run(a)

...