How does scala slick determin which rows to update in this query

Question

I was asked how scala slick determines which rows need to update given this code

      def updateFromLegacy(criteria: CertificateGenerationState, fieldA: CertificateGenerationState, fieldB: Option[CertificateNotification]) = {
        val a: Query[CertificateStatuses, CertificateStatus, Seq] = CertificateStatuses.table.filter(status => status.certificateState === criteria)
        val b: Query[(Column[CertificateGenerationState], Column[Option[CertificateNotification]]), (CertificateGenerationState, Option[CertificateNotification]), Seq] = a.map(statusToUpdate => (statusToUpdate.certificateState, statusToUpdate.notification))
        val c: (CertificateGenerationState, Option[CertificateNotification]) = (fieldA, fieldB)
        b.update(c)
      }

Above code is (as i see it)

a) looking for all rows that have "criteria" for "certificateState"
b) a query for said columns is created
c) a tuple with the values i want to update to is created

then the query is used to find rows where tuple needs to be applied.

Background

I wonder were slick keeps track of the Ids of the rows to update.

What i would like to find out

What is happening behind the covers?
What is Seq in "val a: Query[CertificateStatuses, CertificateStatus, Seq]"
Can someone maybe point out the slick source where the moving parts are located?

Paul Dolega Paul Dolega · Accepted Answer · 2017-01-23T11:20:42

OK - I reformatted your code a little bit to easier see it here and divided it into chunks. Let's go through this one by one:

    val a: Query[CertificateStatuses, CertificateStatus, Seq] = 
        CertificateStatuses.table
            .filter(status => status.certificateState === criteria)

Above is a query that translated roughly to something along these lines:

SELECT *  // Slick would list here all your columns but it's essiantially same thing
FROM certificate_statuses
WHERE certificate_state = $criteria

Below this query is mapped that is, there is a SQL projection applied to it:

    val b: Query[
        (Column[CertificateGenerationState], Column[Option[CertificateNotification]]), 
        (CertificateGenerationState, Option[CertificateNotification]), 
        Seq] = a.map(statusToUpdate =>
            (statusToUpdate.certificateState, statusToUpdate.notification))

So instead of * you will have this:

SELECT certificate_status, notification
FROM certificate_statuses
WHERE certificate_state = $criteria

And last part is reusing this constructed query to perform update:

    val c: (CertificateGenerationState, Option[CertificateNotification]) = 
        (fieldA, fieldB)

    b.update(c)

Translates to:

UPDATE certificate_statuses
SET certificate_status = $fieldA, notification = $fieldB
WHERE certificate_state = $criteria

I understand that last step may be a little bit less straightforward then others but that's essentially how you do updates with Slick (here - although it's in monadic version).

As for your questions:

What is happening behind the covers?

This is actually outside of my area of expertise. That being said it's relatively straightforward piece of code and I guess that an update transformation may be of some interest. I provided you a link to relevant piece of Slick sources at the end of this answer.

What is Seq in "val a:Query[CertificateStatuses, CertificateStatus, Seq]"

It's collection type. Query specifies 3 type parameters:

mixed type - Slick representation of table (or column - Rep)
unpacked type - type you get after executing query
collection type - collection type were above unpacked types are placed for you as a result of a query.

So to have an example:

CertificateStatuses - this is your Slick table definition
CertificateStatus this is your case class
Seq - this is how your results would be retrieved (it would be Seq[CertificateStatus] basically)

I have it explained here: http://slides.com/pdolega/slick-101#/47 (and 3 next slides or so)

Can someone maybe point out the slick source where the moving parts are located?

I think this part may be of interest - it shows how query is converted in update statement: https://github.com/slick/slick/blob/51e14f2756ed29b8c92a24b0ae24f2acd0b85c6f/slick/src/main/scala/slick/jdbc/JdbcActionComponent.scala#L320

It may be also worth to emphasize this:

I wonder were slick keeps track of the Ids of the rows to update.

It doesn't. Look at generated SQLs. You may see them by adding following configuration to your logging (but you also have them in this answer):

<logger name="slick.jdbc.JdbcBackend.statement" level="DEBUG" />

(I assumed logback above).

How does scala slick determin which rows to update in this query

1 Answers