Slick: upsert

// 1.1. Let's add a new table via Play Evolutions (https://pastebin.com/5qhdsZp2)
// 4.sql:
# students schema, stipend table

# --- !Ups

# Attention! if you don't have `students` table, please remove CONSTRAINT clause
CREATE TABLE `stipend` (
    `stipend_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `student_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'reference to a student table',
    `semester` TINYINT(5) UNSIGNED NOT NULL COMMENT 'semester number',
    `value` INT(11) UNSIGNED NOT NULL COMMENT 'payment, unique for student/semester pair',
    PRIMARY KEY (`stipend_id`),
    UNIQUE INDEX `IDX_stipend_student_semester` (`student_id` ASC, `semester` ASC),
    CONSTRAINT `FK_stipend_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT 'table to store students stipend' COLLATE 'utf8_general_ci' ENGINE InnoDB;

# --- !Downs

DROP TABLE IF EXISTS `stipend`;


'
// 1.2. Also create DAO
import javax.inject.Inject

import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.jdbc.JdbcProfile
import slick.lifted.ProvenShape

class StipendDao @Inject()(
    @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
  ) extends HasDatabaseConfigProvider[JdbcProfile] {

  import profile.api._

  case class Stipend(stipendId: Option[Long], studentId: Long, semester: Int, count: Int)

  private class StipendTable(tag: Tag) extends Table[Stipend](tag, "stipend") {
    def stipendId: Rep[Long] = column[Long]("stipend_id", O.PrimaryKey, O.AutoInc)
    def studentId: Rep[Long] = column[Long]("student_id")
    def semester: Rep[Int] = column[Int]("semester")
    def value: Rep[Int] = column[Int]("value")

    override def * : ProvenShape[Stipend] =
      (stipendId.?, studentId, semester, value) <> (Stipend.tupled, Stipend.unapply)
  }

  private lazy val table = TableQuery[StipendTable]

  def insert(studentId: Long, semester: Int, stipendValue: Int) = {
    val query = table insertOrUpdate Stipend(None, studentId, semester, stipendValue)
    println(query.statements.mkString)
    db.run(query)
  }
}



// 1.3. and controller:
package controllers

import javax.inject.Inject

import scala.concurrent.{ExecutionContext, Future}
import scala.util.Try
import scala.xml.NodeSeq

import play.api.libs.json.{JsValue, Json, Reads}
import play.api.mvc._

import dao.StipendDao


class TestController @Inject()(
    stipendDao: StipendDao, cc: ControllerComponents)(implicit ec: ExecutionContext) extends AbstractController(cc) {

  // add to routes file: PUT     /students/:studentId/semester/:semester/stipend    controllers.TestController.setStipend(studentId, semester)
  def setStipend(studentId: Long, semester: Int): Action[NodeSeq] = Action(parse.xml).async { request =>
    val valueOpt = for {
      node <- (request.body \\ "value").headOption
      text <- node.map(_.text).headOption
      value <- Try(text.toInt).toOption
    } yield value

    valueOpt match {
      case Some(value) => stipendDao.insert(studentId, semester, value) map {t => Ok(s"Result: $t")}
      case None => Future.successful(BadRequest("Cannot parse XML"))
    }
  }
}



// 2. If you have a student table - insert a row into it
INSERT INTO `students`.`student` (`name`, `birthday`, `sex`, `admission_year`, `faculty_id`) VALUES ('Tommy', '2000-01-01', 'Male', 2017, 1);



// 3.1. Let's test it! We add stipend for the first semester. Use curl, Postman or another tool you wish
http://localhost:9000/students/1/semester/1/stipend

// Output: For request 'PUT /students/1/semester/1/stipend' [Expecting xml body]



// 3.2. Error! Don't forget to specify application/xml header and XML-body:
http://localhost:9000/students/1/semester/1/stipend with XML: <value>1250</value>

// Output: Result: 1
// DB:
// "stipend_id" "student_id"    "semester"  "value"
// "1"  "1" "1" "1250"



// 4. Also add stipend for the second semester:
http://localhost:9000/students/1/semester/2/stipend with XML: <value>1750</value>

// Output: Result: 1
// DB:
// "stipend_id" "student_id"    "semester"  "value"
// "1"  "1" "1" "1250"
// "2"  "1" "2" "1750"



// 5. And finally let's cheat! Call endpoints:
http://localhost:9000/students/1/semester/1/stipend with XML: <value>5000</value>
http://localhost:9000/students/1/semester/2/stipend with XML: <value>6000</value>

// Output:
// insert into `stipend` (`stipend_id`,`student_id`,`semester`,`value`)  values (?,?,?,?) on duplicate key update `student_id`=VALUES(`student_id`), `semester`=VALUES(`semester`), `value`=VALUES(`value`)
// Result: 1
// Result: 1
// DB:
// "stipend_id" "student_id"    "semester"  "value"
// "1"  "1" "1" "5000"
// "2"  "1" "2" "6000"