Slick 3.0.0 Select and Create or Update

Question

I am in a situation where I must first make a selection, use the value to create create. These are some versions that I am trying to implement. Here is the table definition:

  class Table1(tag: Tag) extends Table[(Int, String, Int)](tag, "TABLE1") {
    def id = column[Int]("ID")
    def name = column[String]("NAME")
    def version = column[Int]("VERSION")

    def indexCol = index("_a", (id, version))

    val tbl1Elems = TableQuery[Table1]
  }

Therefore, when a request comes to create or update an entry in table 1, I must do the following:

1. Select for the given id, if exists, get the version
2. Increment the version
3. Create a new entry

Everything that should happen in one transaction. Here is what I got so far:

  // this entry should be first checked if the id exists and if yes get //the complete set of columns by applying a filter that returns the max //version
  val table1 = Table1(2, "some name", 1)
  for {
    tbl1: Table1 <- tbl1MaxVersionFilter(table1.id)
    maxVersion: Column[Int] = tbl1.version
    result <- tbl1Elems += table1.copy(version = maxVersion + 1) // can't use this!!!
  } yield result

Then I wrap the entire block in one transaction. But I wonder how to do this, will create a new version? How can I get the maxVersion value from a column so that I can increment it 1 and use it?

Answer 1

I would go with a static query, with something like this

import scala.slick.jdbc.{StaticQuery=>Q}
def insertWithVersion(id: Int,name:String) = 
   ( Q.u + "insert into table1 select  " +?id + "," +?name + ", (
     select coalese(max(version),1) from table1 where id=" +?id +")" ).execute

If you want to record it using a smooth path, look at

val tableOne = TableQuery[Table1]

def updateWithVersion(newId:Int,name:String):Unit = {
    val version = tableOne.filter( _.id === newId).map( _.version).max.run.getOrElse(1)
    tableOne += (newId,name,version) 
} 

- , 1, . , , .

P.S. , - sql .