Slick: DAO

// 1. Let's create a new test table via Play Evolutions (https://pastebin.com/5qhdsZp2)
// 2.sql
# students schema, course table

# --- !Ups

CREATE TABLE `course` (
    `course_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `name` VARCHAR(64) NOT NULL COMMENT 'course name',
    `duration` TINYINT(2) NOT NULL COMMENT 'duration, in semesters',
    `group` ENUM('A','B','C','D') NOT NULL COMMENT 'course group',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
    PRIMARY KEY (`course_id`),
    INDEX `IDX_course_name` (`name` ASC)
) COMMENT 'course table' COLLATE 'utf8_general_ci' ENGINE InnoDB;

# --- !Downs

DROP TABLE IF EXISTS `course`;


'
// CourseDao.scala
package dao

import java.sql.Timestamp
import java.time.LocalDateTime
import javax.inject.Inject

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

import scala.concurrent.Future

case class Course(courseId: Option[Long] = None, name: String, duration: Int, group: Int, createdAt: Timestamp, updatedAt: Timestamp)

/**
 * Course DAO
 * @param dbConfigProvider DB Config Provider
 */
class CourseDao @Inject() (
    @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
  ) extends HasDatabaseConfigProvider[JdbcProfile] {

  import profile.api._  // slick.driver.MySQLDriver.api._ for old versions

  private class CourseTable(tag: Tag) extends Table[Course](tag, "course") {
    def courseId: Rep[Option[Long]] = column[Option[Long]]("course_id", O.PrimaryKey, O.AutoInc)
    def name: Rep[String] = column[String]("name")
    def duration: Rep[Int] = column[Int]("duration")
    def group: Rep[Int] = column[Int]("group")
    def createdAt: Rep[Timestamp] = column[Timestamp]("created_at")
    def updatedAt: Rep[Timestamp] = column[Timestamp]("updated_at")

    override def * : ProvenShape[Course] =
      (courseId, name, duration, group, createdAt, updatedAt) <> (Course.tupled, Course.unapply)
    //(courseId, name, duration, group, createdAt, updatedAt).mapTo[Course] also possible
  }

  private lazy val table = TableQuery[CourseTable]

  def addCourse(name: String, duration: Int, group: Int) = {
    val now = Timestamp.valueOf(LocalDateTime.now)
    val query = table += Course(None, name, duration, group, now, now)
    println(query.statements.mkString)
    db.run(query)
  }
}


// TestController.scala:
package controllers

import javax.inject.Inject
import scala.concurrent.{ExecutionContext, Future}
import play.api.mvc._
import play.api.libs.json.{JsValue, Json, Reads}

import dao.{CourseDao}

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

  case class CourseRequest(duration: Int, group: Int)
  implicit val courseReads: Reads[CourseRequest] = Json.reads[CourseRequest]

  // add to routes file: POST    /courses/add/:name    controllers.TestController.addNewCourse(name)
  def addNewCourse(name: String): Action[JsValue] = Action(parse.json).async { request =>
    request.body.asOpt[CourseRequest] match {
      case Some(cReq) => courseDao.addCourse(name, cReq.duration, cReq.group) map {res => Ok(s"Result: $res")}
      case None => Future.successful(BadRequest("Cannot parse json"))
    }
  }
}



// 2. Check it out! Use curl, Postman or another tool you wish
http://localhost:9000/courses/add/math
Cannot parse json



// 2.2. Oh, man! We need to specify json data:
http://localhost:9000/courses/add/math with JSON: {"duration": 3, "group": 1}
insert into `course` (`name`,`duration`,`group`,`created_at`,`updated_at`)  values (?,?,?,?,?)
Result: 1 // 1 is how many rows affected
DB:
"1" "math"  "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"



// 3. Let's add "returning" feature, so that we can return new ID generated by MySQL
def addCourse(name: String, duration: Int, group: Int): Future[Option[Long]] = {
  val now = Timestamp.valueOf(LocalDateTime.now)
  val query = (table returning table.map(_.courseId)) += Course(None, name, duration, group, now, now)
  println(query.statements.mkString)
  db.run(query)
}

http://localhost:9000/courses/add/history with JSON: {"duration": 3, "group": 1}
insert into `course` (`name`,`duration`,`group`,`created_at`,`updated_at`)  values (?,?,?,?,?)
Result: Some(2)
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"



// 4. Next feature: we can generate timestamps (and other default values) on the DB side; just
// specify "O.AutoInc" to skip an argument in INSERT code;
// if you wanna force AutoInc parameters, use "forceInsert" instead of "+="
case class Course(
  courseId: Option[Long] = None,
  name: String,
  duration: Int,
  group: Int,
  createdAt: Option[Timestamp] = None,
  updatedAt: Option[Timestamp] = None
)

def createdAt: Rep[Option[Timestamp]] = column[Option[Timestamp]]("created_at", O.AutoInc)
def updatedAt: Rep[Option[Timestamp]] = column[Option[Timestamp]]("updated_at", O.AutoInc)

def addCourse(name: String, duration: Int, group: Int): Future[Option[Long]] = {
  val query = (table returning table.map(_.courseId)) += Course(None, name, duration, group)
  println(query.statements.mkString)
  db.run(query)
}

http://localhost:9000/courses/add/geography with JSON: {"duration": 1, "group": 2}
insert into `course` (`name`,`duration`,`group`)  values (?,?,?)
Result: Some(3)
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"
"3" "geography" "1" "B" "2018-02-06 23:24:57"   "2018-02-06 23:24:57"



// 5. Next feature: we can use String for Enums instead of Int:
case class Course(
  courseId: Option[Long] = None,
  name: String,
  duration: Int,
  group: String,
  createdAt: Option[Timestamp] = None,
  updatedAt: Option[Timestamp] = None
)

def group: Rep[String] = column[String]("group")

def addCourse(name: String, duration: Int, group: String): Future[Option[Long]] = {

// in controller:
case class CourseRequest(duration: Int, group: String)

http://localhost:9000/courses/add/biology with JSON: {"duration": 1, "group": 2}
Cannot parse json



// 5.1. Ooops! Specify "C" group instead of 2 in json data:
http://localhost:9000/courses/add/biology with JSON: {"duration": 1, "group": "C"}
insert into `course` (`name`,`duration`,`group`)  values (?,?,?)
Result: Some(4)
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"
"3" "geography" "1" "B" "2018-02-06 23:24:57"   "2018-02-06 23:24:57"
"4" "biology"   "1" "C" "2018-02-06 23:31:26"   "2018-02-06 23:31:26"



// 6. Next feature: you can use "id.?" to get rid of Option[Long] for your id.
// it allows to return Future[Long] instead of Future[Option[Long]]
def courseId: Rep[Long] = column[Long]("course_id", O.PrimaryKey, O.AutoInc)

override def * : ProvenShape[Course] =
  (courseId.?, name, duration, group, createdAt, updatedAt) <> (Course.tupled, Course.unapply)

def addCourse(name: String, duration: Int, group: String): Future[Long]

http://localhost:9000/courses/add/russian with JSON: {"duration": 2, "group": "C"}
insert into `course` (`name`,`duration`,`group`)  values (?,?,?)
Result: 5
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"
"3" "geography" "1" "B" "2018-02-06 23:24:57"   "2018-02-06 23:24:57"
"4" "biology"   "1" "C" "2018-02-06 23:31:26"   "2018-02-06 23:31:26"
"5" "russian"   "2" "C" "2018-02-06 23:34:10"   "2018-02-06 23:34:10"



// 7. Let's create incorrect group (e.g. "E"):
http://localhost:9000/courses/add/physics with JSON: {"duration": 1, "group": "E"}
insert into `course` (`name`,`duration`,`group`)  values (?,?,?)
[SQLException: Data truncated for column 'group' at row 1]
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"
"3" "geography" "1" "B" "2018-02-06 23:24:57"   "2018-02-06 23:24:57"
"4" "biology"   "1" "C" "2018-02-06 23:31:26"   "2018-02-06 23:31:26"
"5" "russian"   "2" "C" "2018-02-06 23:34:10"   "2018-02-06 23:34:10"



// 7.1. So we can check it directly in App and not pass it to DB:
case class Course(
  courseId: Option[Long] = None,
  name: String,
  duration: Int,
  group: String,
  createdAt: Option[Timestamp] = None,
  updatedAt: Option[Timestamp] = None
) {require(Set("A", "B", "C", "D") contains group)}

http://localhost:9000/courses/add/physics with JSON: {"duration": 1, "group": "E"}
[IllegalArgumentException: requirement failed]
In D:\Workspace\play-scala-starter-example\app\dao\CourseDao.scala:20
DB:
"1" "math"      "3" "A" "2018-02-06 23:07:25"   "2018-02-06 23:07:25"
"2" "history"   "3" "A" "2018-02-06 23:08:40"   "2018-02-06 23:08:40"
"3" "geography" "1" "B" "2018-02-06 23:24:57"   "2018-02-06 23:24:57"
"4" "biology"   "1" "C" "2018-02-06 23:31:26"   "2018-02-06 23:31:26"
"5" "russian"   "2" "C" "2018-02-06 23:34:10"   "2018-02-06 23:34:10"