Derive column value from another row's column value Slick

2
votes

I'm trying to derive a newly inserted column's value from a previously inserted column's value. For instance, if I had this database, where every row (except the first row) must reference the ID of another row. I would like to increment the Height column by one from the value of Height in the row that Previous ID references 

----------
ID | Previous ID | Height

0  | null        | 123   

1  | 0           | 124 

2  | 1           | 125 

3  | 1           | 125

Note how height is incremented by one from the previous value of height with the primary key ID. Is there any easy way to do this with Slick? A table could look something like this 

case class ExampleCaseClass(id: Option[Long], previousId: Long)

class ExampleTable(tag: Tag)  extends Table[ExampleCaseClass](tag,"example") { 

def id = column[Long]("id",O.PrimaryKey, O.AutoInc) 

def previousId = column[Long]("previous_id")

//this is the column that needs to be derived based on the height of 'previousId'
def height = column[Long]("height") 

}

Can this be done simply?

2
scalaslickslick-3.0
When do you calculate height? While inserting - do you only know ID and PreviousID and the height should be auto-calculated? (and once calculated it will be read only?) - Piotr Reszke
@PiotrR It should be auto calculated, it should be incremented by 1. When inserting, you only know the 'previous id' the specific application I'm referring to is a blockchain, an append only data structure where each block references the previous block (thus the previous ID), and the block we are inserting increments the height of the blockchain by one - Chris Stewart
Do you just want to query the height of previous_id and then insert the new row with height + 1? And you want to do that in one statement? - tksfz
@tksfz Yes, that sounds right. The new height should be auto calculated (to any user that is inserting a new row). I don't want users of the table to have to query the previous id every time and then increment the height - Chris Stewart

2 Answers

1
votes

You can use a plain sql query to do this, if you don't want to expose the height field:

def insertExample(previousId: Int): DBIO[Int] = {
  sqlu"insert into example (previous_id, height) select $previousId, height + 1 from example where id = $previousId"
}

Another way of implement this is adding a database trigger. By this way, you can use a plain insert, and the database will do the auto increment:

CREATE TRIGGER auto_height BEFORE INSERT ON example
FOR EACH ROW
SET NEW.height = 1 + (SELECT height FROM example WHERE id = NEW.previous_id);
1
votes

There are two parts to this:

  1. How do you insert a row using values that depend on some other row?
  2. How do you package that up in such a way that the logic is "transparent" to users of your table?

There are two approaches for (1):

  • (1a) Do the query and the insert in separate SQL statements, with two round-trips to the database. (Fine performance-wise in almost all cases.)

  • (1b) Do the query and insert in one SQL statement using a INSERT INTO ... SELECT statement. (Marginally faster in certain uncommon situations).

Aside from performance, the two require different syntactic approaches. Both approaches in Slick are discussed in the two answers for:

Scala+Slick 3: Inserting the result of one query into another table

I actually think (1b) is more concise anyway. I mention (1a) to give you another option to explore. (1b) is accomplished using forceInsertQuery as described in:

http://slick.lightbend.com/doc/3.1.1/queries.html#inserting

val example = TableQuery[ExampleTable]
def insertAuto(previousId: Int) = {
  val query = example.filter(_.id == previousId).map(r => (previousId, r.height))
  DBIO.seq(example.forceInsertQuery(query))
}

(Note that when using INSERT INTO .. SELECT you can mix user-supplied literal values with queried values simply by embedding the literals in the SELECT clause.)

There might be an issue where we need to supply the id even though it's auto-incremented because the docs seem to suggest forceInsertQuery requires explicit values even for autoinc columns.

Now, how you want to package up that def to make it convenient for your users to invoke, I'm not actually sure. That should be a simpler thing to accomplish. You could consider making this an extension method on TableQuery[ExampleTable]:

implicit class RichExampleTable(example: TableQuery[ExampleTable]) {
  // def extension methods here
}

But this does require your users to invoke the insertAuto method rather than use "direct" Slick statements. I don't think there's any way around that.