CODEWITHSUNDEEP
sqlscalaslick
SkyWalker
SkyWalker

Reputation: 14309

Slick 3.1.x Insert all details from one master to the other, transactionally?

I have an interesting use case. Basically I have a sourceUser: UserRow and targetUser: UserRow that I need to merge. The merge means marking sourceUser as inactive and copying over all source linked accounts (detail) to the target user. All the missing details e.g. UserRow slick generated type etc can be checked out in this github project.

I can do this in SQL no problem e.g.

UPDATE "user" SET active=false WHERE user_id=${sourceUserId};
INSERT INTO linked_account(user_id, provider_key, provider_hashed) 
   SELECT ${targetUserId}, provider_key, provider_hashed
   FROM linked_account 
   WHERE user_id=${sourceUserId}

My Slick attempt was a mix of generic dao and a slick action that can't see how to compose transactionally. I also couldn't find a way to select and insert at the same time i.e. this attempt doesn't compile:

def merge(targetUser: UserRow, sourceUser: UserRow) : Future[Unit] = {
  // deactivate the source user
  update(sourceUser.copy(active = false))

  val action = (for {
    // get the linked accounts to source 
    linkedAccountSource <- LinkedAccount
    // match the source user
    source <- User if source.id === sourceUser.id && linkedAccountSource.userId === source.id
    // insert into target the previously matched source linked 
    // account but having target user as user_id
    // ........................................ doesn't compile here VVVVVVVVVVVVVVVVVVVVVVV
    linkedAccountTarget <- (LinkedAccount += linkedAccountSource.copy(userId = targetUser.id))
    // match the target user attempting to update set active=false
    target <- User if target.id === targetUser.id
  } yield linkedAccountTarget).transactionally
  db.run(action)
}

UPDATE I got this far but I get one last compiler error, basically want to assign targetUser.id to the linked account results of sourceUser.id

def merge(targetUser: UserRow, sourceUser: UserRow) : Future[Unit] = {
  // define an update DBIOAction to deactivate sourceUser
  val updateAction = User.filter(_.id === sourceUser.id).update(sourceUser.copy(active = false))

  // selects all linkedAccount from sourceUser but yield the userId of the targetUser
  val selectAction = (for {
    linkedAccount <- LinkedAccount
    user <- User if user.id === sourceUser.id && user.id === linkedAccount.userId
  } yield (targetUser.id, linkedAccount.providerKey, linkedAccount.providerPassword, linkedAccount.modified)).result

  // define an insert DBIOAction to insert all the selected linked accounts from sourceUser to targetUser
  val insertAction = selectAction.flatMap(LinkedAccount ++= _)

  // combine both actions using >> and transactionally
  db.run((updateAction >> insertAction).transactionally).map(_ => ())
}

and the last error, I tried doing .as[LinkedAccountRow] in different places but it won't like it:

[error] /home/bravegag/code/play-authenticate-usage-scala/app/dao/UserDao.scala:101: type mismatch;
[error]  found   : Seq[(Long, String, String, Option[java.sql.Timestamp])]
[error]  required: Iterable[generated.Tables.LinkedAccountRow]
[error]       val insertAction = selectAction.flatMap(LinkedAccount ++= _)
[error]                                                                 ^

Upvotes: 1

Views: 311

Answers (2)

Paul Dolega
Paul Dolega

Reputation: 2476

Wouldn't it all work if you just changed this:

...
} yield (targetUser.id, linkedAccount.providerKey, linkedAccount.providerPassword, linkedAccount.modified)).result
...

to something along these lines:

...
} yield (targetUser.id, linkedAccount.providerKey, linkedAccount.providerPassword, linkedAccount.modified)).result
.map(LinkedAcount.tuppled)
...

Also you can use a neat trick to perform INSERT...SELECT in one SQL operation. This would mimic your desired SQL:

INSERT INTO linked_account(user_id, provider_key, provider_hashed) 
SELECT ${targetUserId}, provider_key, provider_hashed
FROM linked_account 
WHERE user_id=${sourceUserId}

It would be something along these lines:

LinkedAccount
      .map(acc => (acc.userId, acc.prividerKey, providerHashed))
      .forceInsertQuery(
           for {
                acc <- LinkedAccount if acc.userId === sourceUser.id
            } yield (acc))
            .map(acc => 
                (acc.userId, acc.privderKey, acc.providerHashed))
       )

This forceInsertQuery obviously is the key here. You can basically have anything you want inside it (like query with multiple joins etc) as long as it it eventually produce projection matching the original map (the one before forceInsertQuery).

Additional note: As what's inside forceInsertQuery is actually a regular Query you can do e.g. precompilation. This would be something like this:

// this would need to be obviously created once, perhaps in your DAO
// as member value
private val query = Compiled(sourceUserId: Rep[Long] => (for {
    acc <- LinkedAccount if acc.userId === sourceUserId
} yield (acc))
    .map(acc => 
        (acc.userId, acc.privderKey, acc.providerHashed)
    )
)
...
// and inside your method
LinkedAccount
      .map(acc => (acc.userId, acc.prividerKey, providerHashed))
      .forceInsertQuery( query(sourceUser.id) )

Upvotes: 4

pagoda_5b
pagoda_5b

Reputation: 7373

I'm on the phone now, but I think you should use the <> operator on the columns tuple instead of as[LinkedAccountRow].

The <> takes as two parameters a function pair converting between the column tuple and the destination object. For case classes this is the apply.tupled, unapply functions.

The most appropriate place to call this should be in the yield clause of the selectAction comprehension.

Upvotes: 1

Related Questions