Slick 3 Transaction

Question

I'm trying to figure out how to port my own closure table implementation from another language to Scala with concurrency in mind.

I have two models, a Node (id | parentID) and a NodeTree (id | ancestor | descendant) where each entry resembles an edge in the tree.

For each new node I must do the following: Query all the ancestors (or filter the TableQuery for them) and then add a NodeTree-Entry (an edge) for each ancestor

Thanks to panther I got this far:

private val nodes = TableQuery[Nodes]

override def create(node: Node): Future[Seq[Int]] =
    {
        val createNodesAction = (
            for
            {
                parent <- nodes
                node <- (nodeTrees returning nodeTrees.map(_.id) into ((ntEntry, ntId) => ntEntry.copy(id = Some(ntId))) += NodeTree(id = None, ancestor = parent.id, descendant = node.id, deleted = None, createdAt = new Timestamp(now.getTime), updatedAt = new Timestamp(now.getTime)))
            } yield (node)
        ).transactionally

        db run createNodesAction
    }

But this yields into a type mismatch;

type mismatch; found : slick.lifted.Rep[Long] required: Option[Long]

Once again: All I want to do is: For each parentNode (= each parent's parent until the last ancestor-node has no parent!) I want to create an entry in the nodeTree so that later I can easily grab all the descendants and ancestors with just another method call that filters through the NodeTree-Table.

(Just a closure table, really)

edit: These are my models

case class Node(id: Option[Long], parentID: Option[Long], level: Option[Long], deleted: Option[Boolean], createdAt: Timestamp, updatedAt: Timestamp)

class Nodes(tag: Tag) extends Table[Node](tag, "nodes")
{
    implicit val dateColumnType = MappedColumnType.base[Timestamp, Long](d => d.getTime, d => new Timestamp(d))

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def parentID = column[Long]("parent_id")
    def level = column[Long]("level")
    def deleted = column[Boolean]("deleted")
    def createdAt = column[Timestamp]("created_at")
    def updatedAt = column[Timestamp]("updated_at")

    def * = (id.?, parentID.?, level.?, deleted.?, createdAt, updatedAt) <> (Node.tupled, Node.unapply)
}

case class NodeTree(id: Option[Long], ancestor: Option[Long], descendant: Option[Long], deleted: Option[Boolean], createdAt: Timestamp, updatedAt: Timestamp)

class NodeTrees(tag: Tag) extends Table[NodeTree](tag, "nodetree")
{
    implicit val dateColumnType = MappedColumnType.base[Timestamp, Long](d => d.getTime, d => new Timestamp(d))

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def ancestor = column[Long]("ancestor")
    def descendant = column[Long]("descendant")
    def deleted = column[Boolean]("deleted")
    def createdAt = column[Timestamp]("created_at")
    def updatedAt = column[Timestamp]("updated_at")

    def * = (id.?, ancestor.?, descendant.?, deleted.?, createdAt, updatedAt) <> (NodeTree.tupled, NodeTree.unapply)
}

What I want to do is a closure table (http://technobytz.com/closure_table_store_hierarchical_data.html) that fills it's edges (nodeTree) automatically when I create a node. So I don't want to manually add all these entries to the database, but when I create a node on level 5 I want the whole path (= entries in the nodetree-table) to be created automatically.

I hope that clears stuff up a bit :)

panther panther · Accepted Answer · 2015-05-19T21:34:47

Try this:

override def create(node: Node): Future[Seq[Int]] =
{
    val parents = getAllParents(node)
    val createNodesAction = (
      for {
        parent <- parents
        node <- nodeTrees += NodeTree(id = None, ancestor = parent.id, descendant = node.id)
      } yield (node)
    ).transactionally

   db run createNodesAction
}

You should not have to separately retrieve the parents separately. It can be done in the same session. Above, you can easily replace 'parents' in for-comprehension with the TableQuery you want to work against (with or without filter).

Also note that, here you would be returning sequence of number of rows affected by the insert action. To instead return list of node Ids (assuming you would have marked node Ids as AUTO_INC in db), then you can do something like this:

override def create(node: Node): Future[Seq[Int]] =
{
    val createNodesAction = (
      for {
        parent <- parents
        node <- (nodeTrees returning nodeTrees.map(_.id) into ((ntEntry, ntId) => ntEntry.copy(id = Some(ntId))) += NodeTree(id = None, ancestor = parent.id, descendant = node.id)
      } yield (node)
    ).transactionally

   db run createNodesAction
}

The difference is: (nodeTrees returning nodeTrees.map(_.id) into ((ntEntry, ntId) => ntEntry.copy(id = Some(ntId))) instead of just (nodeTrees) that retrieves and maps auto inc id into result.

Update: Try this:

override def create(node: Node): Future[Seq[Int]] =
{
    def createNodesAction(parentId: Long): DBIOAction[NodeTree, NoStream, Read with Write] = (
      for {
        node <- (nodeTrees returning nodeTrees.map(_.id) into ((ntEntry, ntId) => ntEntry.copy(id = Some(ntId))) += NodeTree(id = None, ancestor = parentId, descendant = node.id)
      } yield (node)
    ).transactionally

   // TODO: Init and pass in 'parents'
   db.run(DBIO.sequence(parents.map(createNodesAction(_.id)))
}

Slick 3 Transaction

2 Answers