Inserting default values if column value is 'None' using slick

Question

My problem is simple.

I have a column seqNum: Double which is NOT NULL DEFAULT 1 in CREATE TABLE statement as follows:

CREATE TABLE some_table
(
    ...
    seq_num DECIMAL(18,10) NOT NULL DEFAULT 1,
    ...
);

User can enter a value for seqNum or not from UI. So the accepting PLAY form is like:

case class SomeCaseClass(..., seqNum: Option[Double], ...)
val secForm = Form(mapping(
    ...
    "seqNum" -> optional(of[Double]),
    ...
  )(SomeCaseClass.apply)(SomeCaseClass.unapply))

The slick Table Schema & Objects looks like this:

case class SomeSection (
   ...
   seqNum: Option[Double],
   ...
)
class SomeSections(tag: Tag) extends Table[SomeSection](tag, "some_table") {
  def * = (
      ...
      seqNum.?,
      ...
    ) <> (SomeSection.tupled, SomeSection.unapply _)

  ...
  def seqNum = column[Double]("seq_num", O.NotNull, O.Default(1))
  ...
}
object SomeSections {

  val someSections = TableQuery[SomeSections]
  val autoInc = someSections returning someSections.map(_.sectionId)

  def insert(s: someSection)(implicit session: Session) = {
     autoInc.insert(s)
  }
}

When I'm sending seqNum from UI, everything is works fine but when None is there, it breaks saying that cannot insert NULL in NOT NULL column which is correct. This question explains why.

But how to solve this problem using slick? Can't understand where should I check about None? I'm creating & sending an object of SomeSection to insert method of SomeSections Object.

I'm using sql-server, if it matters.

score 8 · Answer 1 · edited Feb 22 '18 at 07:50

8

Using the default requires not inserting the value at all rather than inserting NULL. This means you will need a custom projection to insert to.

people.map(_.name).insert("Chris") will use defaults for all other fields. The limitations of scala's native tuple transformations and case class transformations can make this a bit of a hassle. Things like Slick's HLists, Shapeless, Scala Records or Scala XR can help, but are not trivial or very experimental at the moment.

edited Feb 22 '18 at 07:50

Cedric Reichenbach

8,970
6
54
89

answered Feb 12 '15 at 16:04

cvogt

11,260
30
46

This doesn't seem to work for mapped columns; `Slick does not know how to map the given types`. Not sure why, though. – Cedric Reichenbach May 17 '18 at 15:35

score -1 · Answer 2 · answered Feb 12 '15 at 08:53

-1

Either you enforce the Option passed to Slick by suffixing it with a .getOrElse(theDefault), or you make the DB accepts NULL (from a None value) and defaults it using some trigger.

answered Feb 12 '15 at 08:53

cchantep

9,118
3
30
41

3

If I'll use `.getOrElse(theDefault)` then the default value set while creating table will be of no use, as I'll be passing something. – Sunil Kumar Feb 12 '15 at 10:57
Passing `None` is not passing nothing. – cchantep Feb 12 '15 at 11:02

Inserting default values if column value is 'None' using slick

2 Answers2

Linked