Slick 3.0.0 - update row with only non-null values

Question

Having a table with the columns

class Data(tag: Tag) extends Table[DataRow](tag, "data") {
  def id = column[Int]("id", O.PrimaryKey)
  def name = column[String]("name")
  def state = column[State]("state")
  def price = column[Int]("price")

  def * = (id.?, name, state, price) <> ((DataRow.apply _).tupled, DataRow.unapply)
}

I'd like to write a function that would select a single row, and update the columns where the supplied values are not null.

def update(id: Int, name: Option[String], state: Option[State], price: Option[Int])

eg.

update(1, None, None, Some(5)) would update only the price of the data row 1, leaving the name and state intact

update(1, Some("foo"), None, Some(6)) would update the name and price, but leave its state intact.

I guess some smart mapping could be used, but I'm having a hard time expressing it, not sure how it could spit out different length tuples depending on the inputs (wether their value is defined), since they are more or less "unrelated" classes.

def update(id: Int, name: Option[String], state: Option[State], price: Option[Int]) = {
  table.fiter(_.id == id). ???? .update(name, state, price)
}

Answer 1

I solved it in the following way.

The implementation below works only if it is a Product object.

Execute the update statement except for None for the Option type and null for the object type.

package slick.extensions

import slick.ast._
import slick.dbio.{ Effect, NoStream }
import slick.driver.JdbcDriver
import slick.jdbc._
import slick.lifted._
import slick.relational.{ CompiledMapping, ProductResultConverter, ResultConverter, TypeMappingResultConverter }
import slick.util.{ ProductWrapper, SQLBuilder }

import scala.language.{ existentials, higherKinds, implicitConversions }

trait PatchActionExtensionMethodsSupport { driver: JdbcDriver =>

  trait PatchActionImplicits {
    implicit def queryPatchActionExtensionMethods[U <: Product, C[_]](
        q: Query[_, U, C]
    ): PatchActionExtensionMethodsImpl[U] =
      createPatchActionExtensionMethods(updateCompiler.run(q.toNode).tree, ())
  }

  ///////////////////////////////////////////////////////////////////////////////////////////////
  //////////////////////////////////////////////////////////// Patch Actions
  ///////////////////////////////////////////////////////////////////////////////////////////////

  type PatchActionExtensionMethods[T <: Product] = PatchActionExtensionMethodsImpl[T]

  def createPatchActionExtensionMethods[T <: Product](tree: Node, param: Any): PatchActionExtensionMethods[T] =
    new PatchActionExtensionMethodsImpl[T](tree, param)

  class PatchActionExtensionMethodsImpl[T <: Product](tree: Node, param: Any) {
    protected[this] val ResultSetMapping(_, CompiledStatement(_, sres: SQLBuilder.Result, _),
      CompiledMapping(_converter, _)) = tree
    protected[this] val converter = _converter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, Product]]
    protected[this] val TypeMappingResultConverter(childConverter, toBase, toMapped) = converter
    protected[this] val ProductResultConverter(elementConverters @ _ *) =
      childConverter.asInstanceOf[ResultConverter[JdbcResultConverterDomain, Product]]
    private[this] val updateQuerySplitRegExp = """(.*)(?<=set )((?:(?= where)|.)+)(.*)?""".r
    private[this] val updateQuerySetterRegExp = """[^\s]+\s*=\s*\?""".r

    /** An Action that updates the data selected by this query. */
    def patch(value: T): DriverAction[Int, NoStream, Effect.Write] = {
      val (seq, converters) = value.productIterator.zipWithIndex.toIndexedSeq
        .zip(elementConverters)
        .filter {
          case ((Some(_), _), _) => true
          case ((None, _), _) => false
          case ((null, _), _) => false
          case ((_, _), _) => true
        }
        .unzip

      val (products, indexes) = seq.unzip

      val newConverters = converters.zipWithIndex
        .map(c => (c._1, c._2 + 1))
        .map {
          case (c: BaseResultConverter[_], idx) => new BaseResultConverter(c.ti, c.name, idx)
          case (c: OptionResultConverter[_], idx) => new OptionResultConverter(c.ti, idx)
          case (c: DefaultingResultConverter[_], idx) => new DefaultingResultConverter(c.ti, c.default, idx)
          case (c: IsDefinedResultConverter[_], idx) => new IsDefinedResultConverter(c.ti, idx)
        }

      val productResultConverter =
        ProductResultConverter(newConverters: _*).asInstanceOf[ResultConverter[JdbcResultConverterDomain, Any]]
      val newConverter = TypeMappingResultConverter(productResultConverter, (p: Product) => p, (a: Any) => toMapped(a))

      val newValue: Product = new ProductWrapper(products)
      val newSql = sres.sql match {
        case updateQuerySplitRegExp(prefix, setter, suffix) =>
          val buffer = StringBuilder.newBuilder
          buffer.append(prefix)
          buffer.append(
            updateQuerySetterRegExp
              .findAllIn(setter)
              .zipWithIndex
              .filter(s => indexes.contains(s._2))
              .map(_._1)
              .mkString(", ")
          )
          buffer.append(suffix)
          buffer.toString()
      }

      new SimpleJdbcDriverAction[Int]("patch", Vector(newSql)) {
        def run(ctx: Backend#Context, sql: Vector[String]): Int =
          ctx.session.withPreparedStatement(sql.head) { st =>
            st.clearParameters
            newConverter.set(newValue, st)
            sres.setter(st, newConverter.width + 1, param)
            st.executeUpdate
          }
      }
    }
  }
}

Example

// Model
case class User(
  id: Option[Int] = None,
  name: Option[String] = None,
  username: Option[String] = None,
  password: Option[String] = None
)

// Table
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def username = column[String]("username")
  def password = column[String]("password")
  override def * = (id.?, name.?, username.?, password.?) <>(User.tupled, User.unapply)
}

// TableQuery
object Users extends TableQuery(new Users(_))

// CustomDriver 
trait CustomDriver extends PostgresDriver with PatchActionExtensionMethodsSupport {
  override val api: API = new API {}
  trait API extends super.API  with PatchActionImplicits
}

// Insert
Users += User(Some(1), Some("Test"), Some("test"), Some("1234"))

// User patch
Users.filter(_.id === 1).patch(User(name = Some("Change Name"), username = Some("")))

https://gist.github.com/bad79s/1edf9ea83ba08c46add03815059acfca

Answer 2

Building on JonasAnso's answer, converting that to slick v3.0+, and putting it into a transaction:

  def partialUpdate(id: Int, name: Option[String], login: Option[String]): Future[Int] = {
    val selectQ = users.filter(_.id === id)

    val query = selectQ.result.head.flatMap { data =>
      selectQ.update(data.patch(name, login))
    }

    db.run(query)
  }

Answer 3

As I commented the question is similar to an existing one, but you don't seem to have any extra requirements.

The simplest approach is just SELECT + UPDATE. For example you add a patch function in your DataRow class defining how you want to update your model

      def patch(name: Option[String], state: Option[State], price: Option[Int]): Data {
         this.copy(name = name.getOrElse(this.name), ...)
      }

And you add a partialUpdate method in your repo class

class DataRepo {
  private val Datas = TableQuery[Data]
  val db = ???

  def partialUpdate(id: Int, name: Option[String], state: Option[State], price: Option[Int]): Future[Int] = {
    val query = Datas.filter(_.id === id)
    for {
      data <- db.run(query.result.head)
      result <- db.run(query.update(data.patch(name, state, price)))
    } yield result
  }

}

As you see the main problem of this solution is that there are 2 SQL statements, SELECT and UPDATE.

Other solution is to use plain SQL (http://slick.typesafe.com/doc/3.0.0/sql.html) but of course this gives other problems.