воскресенье, 7 сентября 2014 г.

Slick-pg Enum + Hstore example

While creating my application with Play Framework 2.3, I've spent lots of time for searching some things that seemed to be easy at first glance. One of such a thing was the use of PostgreSQL specific types: hstore and enums. I found a great library for that. Slick-pg provides good covers for the most of the PostgreSQL specific data types. However, I bumped into some problems using enums, so I provide here my example of using it (+ hstore + date). Hope, it'll be helpfull.

build.sbt (Add all necessary dependencies)
libraryDependencies ++= Seq(
  "org.postgresql" % "postgresql" % "9.3-1100-jdbc41",
  "com.typesafe.slick" %% "slick" % "2.1.0",
  "com.typesafe.play" %% "play-slick" % "0.8.0",
  "com.github.tminglei" %% "slick-pg" % "0.6.3"

First of all you need to enable Hstore and create a enum type in your database.

app.utils.helpers.PostgresDriverPlus.scala (extend standart PostgreSQL driver with slick-pg)
package utils.helpers

import com.github.tminglei.slickpg._
import slick.driver.PostgresDriver
import models.MyTableStatus

trait WithPostgresDriver {
  val driver: PostgresDriverPlus

trait PostgresDriverPlus extends PostgresDriver 
                            with PgHStoreSupport
                            with PgEnumSupport
                            with PgDateSupport{

  override lazy val Implicit = new ImplicitsPlus {}
  override val simple = new SimpleQLPlus {}
  trait EnumImplicits {
    implicit val myTableStatusTypeMapper = createEnumJdbcType("pg_enum_type", MyTableStatus )
    implicit val myTableStatusTypeListMapper = createEnumListJdbcType("pg_enum_type", MyTableStatus )
    implicit val myTableStatusExtensionMethodsBuilder = createEnumColumnExtensionMethodsBuilder(MyTableStatus)

  trait ImplicitsPlus extends Implicits 
                         with HStoreImplicits
                         with DateTimeImplicits
                         with EnumImplicits
  trait SimpleQLPlus extends SimpleQL with ImplicitsPlus

object PostgresDriverPlus extends PostgresDriverPlus

app.models.MyTable.scala (all type mappers are here)
package models

import play.api.Play.current
import play.api.db.slick.{ DB, Session }
import utils.helpers.PostgresDriverPlus.simple._
import java.sql.{Timestamp, Date}
import scala.Enumeration

object MyTableStatus extends Enumeration {
  type MyTableStatus = Value
  val CREATED = Value("CREATED")
  val JOINED = Value("JOINED")
  val CLOSED = Value("CLOSED")

case class MyTable(id: Option[Int], date_start: Timestamp, status: MyTableStatus.MyTableStatus, date_finish: Date, spec: Map[String, String])

class MyTables(tag: Tag) extends Table[MyTable](tag, "mytable") /*Don't forget to be "mytable" in small letters!!! */ {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def date_start = column[Timestamp]("date_start", O.NotNull)
  def status = column[MyTableStatus.MyTableStatus]("status", O.NotNull)
  def date_finish = column[Date]("date_finish", O.NotNull)
  def spec = column[Map[String, String]]("spec", O.NotNull) //hstore is mapped to Map[String, String]

  def * = (id.?, date_start, status, date_finish, spec) <> (MyTable.tupled, MyTable.unapply)

object MyTables extends TableQuery(new MyTables(_)) {

  def findById(id: Int): Option[MyTable] = {
    DB withSession {
      implicit session: Session =>
        MyTables.filter(_.id === id).firstOption

  def all = {
    DB withSession {
      implicit session: Session =>

  def create(myTable: MyTable) = {
    DB withSession {
      implicit session: Session =>

So now you can use your model, for example, to insert data. You can run the following with "test-only InsertTestData" command. 

import org.specs2.mutable.Specification
import org.specs2.runner._
import org.junit.runner._
import play.api.test._
import play.api.test.Helpers._
import play.api.Play.current
import models._
import scala.collection.immutable.HashMap

class InsertTestData extends Specification {
  "MyTables insert" should {
    "insert test data if MyTable model is empty" in {
      running(FakeApplication()) {
        if (MyTables.all.isEmpty) {
          import java.sql.Timestamp
          import java.text.SimpleDateFormat
          val sdf = new SimpleDateFormat("dd-M-yyyy")
          val joinBefore = "17-12-2014"
          val dateExpiry = "31-12-2014"
          MyTables.create(MyTable(None, new Timestamp((new java.util.Date).getTime()),  MyTableStatus.CREATED,
              new java.sql.Date(sdf.parse(dateExpiry).getTime()), 
              HashMap("type" -> "SuperType", "bla" -> "alb", "qwerty" -> "asdf")
        MyTables.all must not be empty

0 коммент.:

Отправить комментарий