Failing insert Slick 3.2.0 and foreign keys

[Samuel Sayag]

Hello,

I have already posted this question in the gitter channel and wasn't answered for the moment and I still could not solve it by myself so maybe with more details it will be more readable/reproductible.

I try insert data in a table with four foreign keys that point on ids from other tables. I don't think providing the whole model would help but would it be necessary there is no much problem doing it so just ask.

However this is the basic table (and the associated case class):

final case class TechnicalEvent(name: String,
                                eventTypeId: PK[EventTypeTable],
                                pageTypeId: PK[PageTypeTable],
                                userActionId: Option[PK[UserActionTable]] = Some(PK(0)),
                                dependentId: Option[PK[TechnicalEventTable]] = Some(PK(0)),
                                id: PK[TechnicalEventTable] = PK(0))


final class TechnicalEventTable(tag: Tag) extends
  Table[TechnicalEvent](tag, "TECHNICAL_EVENT") {

  def technicalEventId =
    column[PK[TechnicalEventTable]]("technicalEventId", O.AutoInc, O.PrimaryKey)

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

  def eventTypeId = column[PK[EventTypeTable]]("eventTypeId")

  def pageTypeId = column[PK[PageTypeTable]]("pageTypeId")

  def userActionId = column[Option[PK[UserActionTable]]]("userActionId")

  def dependentId = column[Option[PK[TechnicalEventTable]]]("dependentId")

  def eventType = foreignKey("technicalEvent_eventTypeId_eventType_eventTypeId_fk",
    eventTypeId, eventTypes)(
    _.eventTypeId, onUpdate = ForeignKeyAction.NoAction, onDelete = ForeignKeyAction.NoAction
  )

  def pageType = foreignKey("technicalEvent_pageTypeId_pageType_pageTypeId_fk",
    pageTypeId, pageTypes)(
    _.pageTypeId, onUpdate = ForeignKeyAction.NoAction, onDelete = ForeignKeyAction.NoAction
  )

  def userAction = foreignKey("technicalEvent_userActionId_userAction_userActionId_fk",
    userActionId, userActions)(
    _.userActionId, onUpdate = ForeignKeyAction.NoAction, onDelete = ForeignKeyAction.NoAction)

  def dependent = foreignKey("technicalEvent_technicalEventId_technicalEvent_technicalEventId_fk",
    dependentId, technicalEvents)(_.technicalEventId,
    onUpdate = ForeignKeyAction.NoAction, onDelete = ForeignKeyAction.NoAction)

  def * = (name, eventTypeId, pageTypeId, userActionId, dependentId, technicalEventId)
    .mapTo[TechnicalEvent]
}

lazy val technicalEvents = TableQuery[TechnicalEventTable]
lazy val insertTechnicalEvents =
  technicalEvents returning technicalEvents.map(_.technicalEventId)

I order to type a bit stronger the Ids of the tables I used the technique expose in the book of R. Dallaway (http://underscore.io/training/courses/essential-slick/):

case class PK[A](value: Long) extends AnyVal with MappedTo[Long]

Now is coming the question, I am trying to write a method that can insert raw data in the data base being that some of the id fields may be NULL:

type TechEvtRaw = (String, String, String, Option[String])
type TechEvtAndDepRaw = (TechEvtRaw, Option[TechEvtRaw], Option[Long])
 
def insertTechnicalEvent(techEvtsRaw: Seq[TechEvtAndDepRaw]) = {
    techEvtsRaw map {
      t =>
//         Query[Rep[PK[EventTypeTable]], PK[EventTypeTable], Seq]
        val evtTypeIdQ = selectEventTypeId(t._1._2)
//        Query[Rep[PK[PageTypeTable]], PK[PageTypeTable], Seq]
        val pageTypeIdQ = selectPageTypeId(t._1._3)
//        Query[Rep[Option[PK[UserActionTable]]], Option[PK[UserActionTable]], Seq]
        val userActionIdQ = t._1._4.map(selectUserActionId2(_)).
          getOrElse(Query(Rep.None[PK[UserActionTable]]))
//        Query[Rep[Option[PK[TechnicalEventTable]]], Option[PK[TechnicalEventTable]], Seq]
        val techEvtIdQ = t._2.map(selectTechnicalEventId(_)).
          getOrElse(Query(Rep.None[PK[TechnicalEventTable]]))

        val toIns = for {
          evtTypeId <- evtTypeIdQ
          pageTypeId <- pageTypeIdQ
          userActionId <- userActionIdQ
          techEvtId <- techEvtIdQ
        } yield (t._1._1, evtTypeId, pageTypeId, userActionId, techEvtId)

        technicalEvents.map(
          i => (i.name, i.eventTypeId, i.pageTypeId, i.userActionId, i.dependentId)
        ).forceInsertQuery(toIns) // compiling + RuntimeError
      //        ).forceInsertQuery(Query( // compiling + inserting
      //          "toto",
      //          PK[EventTypeTable](1),
      //          PK[PageTypeTable](1),
      //          Rep.None[PK[UserActionTable]],
      //          Rep.None[PK[TechnicalEventTable]]))
    }
  }

The method is compiling fine but is failing at runtime. However a hardcoded datum containing apparently the same type is compiling and also running fine. So I am a bit perplexed about what is causing this.

Here is the error I get and which I have a hard time to decrypt:

Unexpected node Pure t2 -- SQL prefix: select 'some dummy data', x2."eventTypeId", x3."pageTypeId", x4.x5, x6.x7 from "EVENT_TYPE" x2, "PAGE_TYPE" x3,
slick.SlickException: Unexpected node Pure t2 -- SQL prefix: select 'some dummy data', x2."eventTypeId", x3."pageTypeId", x4.x5, x6.x7 from "EVENT_TYPE" x2, "PAGE_TYPE" x3,
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.expr(JdbcStatementBuilderComponent.scala:435)
    at slick.jdbc.H2Profile$QueryBuilder.expr(H2Profile.scala:100)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder$$anonfun$buildFrom$1.apply$mcV$sp(JdbcStatementBuilderComponent.scala:276)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.building(JdbcStatementBuilderComponent.scala:124)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.buildFrom(JdbcStatementBuilderComponent.scala:267)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder$$anonfun$buildFromClause$1$$anonfun$apply$mcV$sp$4.apply(JdbcStatementBuilderComponent.scala:210)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder$$anonfun$buildFromClause$1$$anonfun$apply$mcV$sp$4.apply(JdbcStatementBuilderComponent.scala:210)
    at slick.util.SQLBuilder$$anonfun$sep$1.apply(SQLBuilder.scala:23)
    at slick.util.SQLBuilder$$anonfun$sep$1.apply(SQLBuilder.scala:21)
    at scala.collection.immutable.List.foreach(List.scala:392)
    at slick.util.SQLBuilder.sep(SQLBuilder.scala:21)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder$$anonfun$buildFromClause$1.apply$mcV$sp(JdbcStatementBuilderComponent.scala:210)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.building(JdbcStatementBuilderComponent.scala:124)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.buildFromClause(JdbcStatementBuilderComponent.scala:205)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.buildComprehension(JdbcStatementBuilderComponent.scala:144)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.expr(JdbcStatementBuilderComponent.scala:424)
    at slick.jdbc.H2Profile$QueryBuilder.expr(H2Profile.scala:100)
    at slick.jdbc.JdbcStatementBuilderComponent$QueryBuilder.buildSelect(JdbcStatementBuilderComponent.scala:117)
    at slick.jdbc.JdbcProfile$$anonfun$queryCompiler$1.apply(JdbcProfile.scala:30)
    at slick.jdbc.JdbcProfile$$anonfun$queryCompiler$1.apply(JdbcProfile.scala:30)
    at slick.jdbc.JdbcMappingCompilerComponent$JdbcCodeGen.compileServerSideAndMapping(JdbcMappingCompilerComponent.scala:59)
    at slick.compiler.CodeGen$$anonfun$apply$2$$anonfun$1.apply(CodeGen.scala:22)
    at slick.compiler.CodeGen$$anonfun$apply$2$$anonfun$1.apply(CodeGen.scala:20)
    at slick.ast.ClientSideOp$.mapServerSide(ClientSideOp.scala:21)
    at slick.compiler.CodeGen$$anonfun$apply$2.apply(CodeGen.scala:20)
    at slick.compiler.CodeGen$$anonfun$apply$2.apply(CodeGen.scala:16)
    at slick.ast.ClientSideOp$.mapResultSetMapping(ClientSideOp.scala:24)
    at slick.compiler.CodeGen.apply(CodeGen.scala:16)
    at slick.compiler.CodeGen$$anonfun$apply$1.apply(CodeGen.scala:13)
    at slick.compiler.CodeGen$$anonfun$apply$1.apply(CodeGen.scala:13)
    at slick.compiler.CompilerState.map(QueryCompiler.scala:228)
    at slick.compiler.CodeGen.apply(CodeGen.scala:13)
    at slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:79)
    at slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:78)
    at scala.util.DynamicVariable.withValue(DynamicVariable.scala:58)
    at slick.ast.SymbolNamer.use(Symbol.scala:112)
    at slick.compiler.QueryCompiler.runPhase(QueryCompiler.scala:78)
    at slick.compiler.QueryCompiler$$anonfun$runPhases$4.apply(QueryCompiler.scala:75)
    at slick.compiler.QueryCompiler$$anonfun$runPhases$4.apply(QueryCompiler.scala:75)
    at scala.collection.TraversableOnce$$anonfun$foldLeft$1.apply(TraversableOnce.scala:157)
    at scala.collection.TraversableOnce$$anonfun$foldLeft$1.apply(TraversableOnce.scala:157)
    at scala.collection.Iterator$class.foreach(Iterator.scala:891)
    at scala.collection.AbstractIterator.foreach(Iterator.scala:1334)
    at scala.collection.TraversableOnce$class.foldLeft(TraversableOnce.scala:157)
    at scala.collection.AbstractIterator.foldLeft(Iterator.scala:1334)
    at slick.compiler.QueryCompiler.runPhases(QueryCompiler.scala:75)
    at slick.compiler.QueryCompiler.run(QueryCompiler.scala:53)
    at slick.compiler.QueryCompiler.run(QueryCompiler.scala:47)
    at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl.buildQueryBasedInsert(JdbcActionComponent.scala:447)
    at slick.jdbc.JdbcActionComponent$InsertActionComposerImpl.forceInsertQuery(JdbcActionComponent.scala:484)
    at com.affinytix.config.test.TrackingScrappingAPI$$anonfun$insertTechnicalEvent$1.apply(TrackingScrappingAPI.scala:195)
    at com.affinytix.config.test.TrackingScrappingAPI$$anonfun$insertTechnicalEvent$1.apply(TrackingScrappingAPI.scala:174)
    at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234)
    at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:234)
    at scala.collection.immutable.List.foreach(List.scala:392)
    at scala.collection.TraversableLike$class.map(TraversableLike.scala:234)
    at scala.collection.immutable.List.map(List.scala:296)

Many thanks by advance to any help that may be provided.

Sam

[Samuel Sayag]

It is possible to test the problem by using this project:

https://github.com/samouille666/slick-insert-optional

[Samuel Sayag]

To sum up following points have been raised:

1. Recall that working on/combining query with for comprehension or anything else has effect on the query syntax only and does not allow to work on the result.
2. Some inserting queries that may be difficult to express as queries may be obtain by combining simpler actions
   

Some very illustrative example have been provided by Richard Dallaway:

https://github.com/d6y/insert_from_selects

that respond to the problem illustrated by the attempt: https://github.com/samouille666/slick-insert-optional

Many thanks

On Tuesday, 11 July 2017 05:23:25 UTC+3, Samuel Sayag wrote: