#rzeszowjava

Introduction (101)

Let's talk about different approaches to API

Repository

https://github.com/pdolega/slick-101

Preso

http://slides.com/pdolega/slick-101-13

Who am I?

twitter: @pdolega

github: github.com/pdolega

Do stuff @ VirtusLab

What is Slick?

Not an ORM

ORM problems

N+1 problem (lazy / eager fetching)

Session context scope

Execution under the cover (cache)

O-R impedance mismatch

False promise

Leaky abstraction

FRM

Embrace database model through a functional paradigm.

If you come from ORM world:

http://slick.lightbend.com/doc/3.2.3/orm-to-slick.html

Code samples:

SmokeSpec

    




    "correctly open db" in {
      val db = Database.forConfig("memoryDb")
      val results = db.run(sql"SELECT 2 + 3".as[(Int)])
      // results: Future[Seq[Int]]
      

      // option 1 - bad
      blockingWait(results).head shouldBe 5

      // option 2 - better
      results.map { r =>
        r.head shouldBe (5)
      }.futureValue    // this is only for unit tests to work
    }



    memoryDb = {
      connectionPool = disabled
      url = "jdbc:h2:mem:slick-101"
      driver = "org.h2.Driver"
      user = ""
      password = ""
      keepAliveConnection = true
    }

Config

    




    "correctly open db" in {
      val db = Database.forConfig("memoryDb")
      val results = db.run(sql"SELECT 2 + 3".as[(Int)])
      // results: Future[Seq[Int]]
      

      // option 1 - bad
      blockingWait(results).head shouldBe 5

      // option 2 - better
      results.map { r =>
        r.head shouldBe (5)
      }.futureValue    // this is only for unit tests to work
    }



    
        db.run(
            sql"SELECT 2 + 3".as[(Int)]
        )
    
    
    
    
    
        db.run(
            SQLActionBuilder("SELECT 2 + 3", SetUnit).as[Int]
        )

Referential transparency

&

Local Reasoning



      val invitedStudent = profileDao
        .findByUserId(token.invitingUserId)
        .map(_.get)

      val token = inviteTokenDao
        .update(token.copy(
            invitedUser = Some(
                invitedStudent.userId), 
                isUsed = true
            )
        )

      friendsAndFollowersService
        .makeStudentsFriends(invitedStudent.id, invitingSt.id)

      val invitingStudent = studentProfileDao
        .findByUserId(token.invitingUserId).map(_.get)
      
      userDao.findFullUserById(invitedStudent.userId)
        .map(_.get.user)

Spring annotations









            @Transactional
            public List listStudentsBySchool(long id) {
                ...
                repository.findByClasses_School_Id(id);
                ...
            }

Back to reactive

Reactive (™?)

...wait a minute, JDBC is blocking anyway

Everything is async...

How many connections do you need?

db_pool_connections =

(core_count * 2) + effective_spindle_count

source: postgres docs

Imagine that you have a website that while maybe not Facebook-scale still often has 10,000 users making database requests simultaneously -- accounting for some 20,000 transactions per second.

How big should your connection pool be?

Server handling 10 000 client connections may need not more than 10 connection

The point being...

Use less threads / let CPU be busy

Threads are expensive

Yet traditionally they are wasted on IO

Don't block general threads / use designated thread pool

Type-Safe



CREATE TABLE student (
    id BIGINT GENERATED BY DEFAULT 
        AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    surname VARCHAR(100) NOT NULL,
    nationality VARCHAR(100) NOT NULL
);






db.run(
    StudentTable
    .filter(_.name === "John"))
    .result              
)

OK!






db.run(
    StudentTable
    .filter(_.firstName === "John"))
    .result              
)

Wrong!






db.run(
    StudentTable
    .filter(_.name === 153))
    .result              
)

Wrong!

How?

Tables

Code samples:

TableSpec



CREATE TABLE university (
    id BIGINT GENERATED BY DEFAULT 
        AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100)
);


  // in-application respresentation of table's tuple
  case class University(name: String,
                        id: Long = -1L)

  // definition of table
  class UniversityTable(tag: Tag) extends Table[University](
    tag, "university") {
    
    def name = column[String]("name")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    // default projection
    def * = (name, id) <> (University.tupled, 
                           University.unapply)
  }

  // 'table' object used for interacting with in app
  lazy val UniversityTable = TableQuery[UniversityTable]







  // creating schema
  override protected def beforeEach {
    blockingWait(db.run(UniversityTable.schema.create))
  }

    "be insertable and retrievable - poor version" in {
      // read everything from a table
      blockingWait(db.run(UniversityTable.result)) should 
                    have size 0

      // insert into table
      blockingWait(db.run(
        UniversityTable ++= Seq(
          University("Hogwart"),
          University("Scala University")
        )
      ))

      // read again
      val results = blockingWait(
            db.run(UniversityTable.result)
      )

      results.map(_.name) should contain theSameElementsAs
        Seq("Hogwart", "Scala University")
    }

Poor version

Queries

Code samples:

QueriesSpec

      // here we get Future[Seq[Student]]
      db.run(StudentTable.result) 
        // here I map over Future
        // within map we have results: Seq[Student]
        .map { results => 
            // we map it to (String, String)
            results.map(student => (student.name, 
                                    student.surname))    
      }
      // we end up with Future[Seq[(String, String)]]

select "NAME", "MIDDLE_NAME", "SURNAME", "NATIONALITY", "ID" 
from "STUDENT"

Actual query


      db.run(
        StudentTable.map { student =>
            // here I map over query!
            (student.name, student.surname)
        }
        .result
      )
      // we end up with Future[Seq[(String, String)]]

select "NAME" "SURNAME"
from "STUDENT"

Actual query


      db.run(
        StudentTable.map { student =>
            // here I map over query!
            (student.name, student.surname)
        }
        .result
      )
      // we end up with Future[Seq[(String, String)]]

Steps of execution


def execQuery[E, U, C[_]](q: Query[E, U, C]): Future[C[U]] = 
    db.run(    // convert into Future
        q.result    // convert into DBIO
    )

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.

    select  "NAME", 
            "MIDDLE_NAME", 
            "SURNAME", 
            "NATIONALITY", 
            "ID" 
    
    from    "STUDENT"




    StudentTable

// or

    for {student <- StudentTable }
        yield student

    select  "NAME", 
            "MIDDLE_NAME", 
            "SURNAME", 
            "NATIONALITY", 
            "ID" 
    
    from    "STUDENT"




    for {student <- StudentTable }
        yield student

// or

    StudentTable.map { student =>
        student
    }




    for { 
        x <- XXX 
        y <- YYY
        z <- ZZZ
    } yield x * y * z

// or

    XXX.flatMap { x =>
        YYY.flatMap { y =>
            ZZZ.map { z =>
                x * y * z
            }
        }
    }





execQuery(
    StudentTable
)


def execQuery[E, U, C[_]](q: Query[E, U, C]): Future[C[U]] = 
    db.run(    // convert into Future
        q.result    // convert into DBIO
    )

      


    StudentTable
        .map(_.name)

    

    select "NAME" 
    
    from "STUDENT"




    StudentTable
        .map(s => 
            (s.name, s.middleName.ifNull("*no-middlename*"))
        )
        .sortBy(_.name)

    select  "NAME", 
            ifnull("MIDDLE_NAME",'*no-middlename*') 
    
    from "STUDENT" 
    order by "NAME"

Wrong!

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.




        List<Integer> num = 
            Arrays.asList(
                new Student("John", "Doe"), 
                new Student("Rowan", "Atkinson")
            );

        num.stream()
            .map(s -> new Pair(s.getName(), s.getMiddleName()))
            .filter(s -> s.getName());

Wrong!




    StudentTable
        .map(s => 
            (s.name, s.middleName.ifNull("*no-middlename*"))
        )
        .sortBy(_._1)

    select  "NAME", 
            ifnull("MIDDLE_NAME",'*no-middlename*') 
    
    from "STUDENT" 
    order by "NAME"

OK!




    StudentTable
        .sortBy(_.name)
        .map(s => 
            (s.name, s.middleName.ifNull("*no-middlename*"))
        )

    select  "NAME", 
            ifnull("MIDDLE_NAME",'*no-middlename*') 
    
    from "STUDENT" 
    order by "NAME"


      StudentTable
        .map(_.nationality ++ "  ")
        .map(_.toUpperCase)
        .map(_.trim)
        .map((_, currentTime, pi))
        .map(row => 
            row._1 ++ " " ++ 
            row._2.asColumnOf[String] ++ " " ++ 
            row._3
        )

    select  (((ltrim(rtrim(ucase("NATIONALITY"||'  ')))||' ') 
            || cast(curtime() as VARCHAR))||' ') 
            || cast(pi() as VARCHAR) 
    from "STUDENT"


    StudentTable
        .map { nat =>
          (nat.nationality ++ "  ").toUpperCase.trim ++ 
          " " ++
          currentTime.asColumnOf[String] ++ 
          " " ++
          pi
        }


      StudentTable
        .map(_.nationality ++ "  ")
        .map(_.toUpperCase)
        .map(_.trim)
        .map((_, currentTime, pi))
        .map(row => 
            row._1 ++ " " ++ 
            row._2.asColumnOf[String] ++ " " ++ 
            row._3
        )



    StudentTable
        .filter(_.name === "Tom")

    // or

    for {
        student <- StudentTable if student.name === "Tom"
    } yield student


    select  "NAME", "MIDDLE_NAME", "SURNAME", ... 

    from    "STUDENT" 

    where   "NAME" = 'Tom'

    StudentTable
        .filterNot(student => 
            student.name === "Tom" && 
            student.surname.startsWith("Smi")
    )
    // or
    for {
        student <- StudentTable if !(
                student.name === "Tom" && 
                student.surname.startsWith("Smi"))
    } yield student


    select "NAME", 
           "MIDDLE_NAME", 
           "SURNAME", 
           "NATIONALITY", 
           "ID" 

    from "STUDENT" 
    
    where not (("NAME" = 'Tom') and 
               ("SURNAME" like 'Smi%' escape '^'))



    StudentTable
        .filter(student => student.middleName.nonEmpty)
        .sortBy(s => (s.name.desc, s.middleName.asc))
        .distinct


    select distinct "NAME", ... 

    from "STUDENT" 

    where "MIDDLE_NAME" is not null 

    order by "NAME" desc, "MIDDLE_NAME"



      StudentTable
        .map(s => (s.name, s.surname))
        .drop(2)
        .take(3)


    select ... 

    from "STUDENT" 

    limit 3 offset 2



      StudentTable
        .map(s => (s.name, s.surname))
        .take(3)
        .drop(2)


    select ... 

    from "STUDENT" 

    limit 1 offset 2

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.


    StudentTable
        .filter(_.surname =!= "Test")
        .groupBy(_.surname)
        .map { case (surname, group) =>
          (surname, group.map(_.name).countDistinct)
        }
        .filter(row => row._2 > 5)


    select "SURNAME", count(distinct "NAME") 

    from "STUDENT" 

    where not ("SURNAME" = 'Test') 

    group by "SURNAME" 

    having count(distinct "NAME") > 5

Click

Actions

    "be insertable and retrievable - poor version" in {
      // read everything from a table
      blockingWait(db.run(UniversityTable.result)) should 
                    have size 0

      // insert into table
      blockingWait(db.run(
        UniversityTable ++= Seq(
          University("Hogwart"),
          University("Scala University")
        )
      ))

      // read again
      val results = blockingWait(
            db.run(UniversityTable.result)
      )

      results.map(_.name) should contain theSameElementsAs
        Seq("Hogwart", "Scala University")
    }

Poor version






memDb.run(
    UniversityTable                            
    .filter(_.name === "Hogwarth") // Query
    .result               // DBIOAction (DBIO)
)    // Future

Monadic Trio

class Maybe<T> {
    private T arg;

    private static Maybe<?> None = new Maybe<>();

    private Maybe() {
    }

    public Maybe(T arg) {
        this.arg = arg;
    }

    public static <T> Maybe<T> unit(T arg) {
        if(arg == null) {
            return (Maybe<T>)None;
        } else {
            return new Maybe(arg);
        }
    }

    public <R> Maybe<R> flatMap(Function<T, Maybe<R>> func) {
        if(isEmpty()) {
            return (Maybe<R>)None;
        } else {
            return func.apply(arg);
        }
    }

    public <R> Maybe<R> map(Function<T, R> func) {
        return flatMap(x -> Maybe.unit(func.apply(arg)));
    }

    public T getOrElse(T alternative) {
        if(isEmpty()) {
            return alternative;
        } else {
            return arg;
        }
    }

    private boolean isEmpty() {
        return arg == null;
    }
}







class Maybe<T> {
    ...

    public static <T> Maybe<T> unit(T arg) {
        if(arg == null) {
            return (Maybe<T>)None;
        } else {
            return new Maybe(arg);
        }
    }

    ...
}





class Maybe<T> {
    ...

    public <R> Maybe<R> flatMap(Function<T, Maybe<R>> func) {
        if(isEmpty()) {
            return (Maybe<R>)None;
        } else {
            return func.apply(arg);
        }
    }


    private boolean isEmpty() {
        return arg == null;
    }

    ...
}





class Maybe<T> {

    ...

    public <R> Maybe<R> map(Function<T, R> func) {
        return flatMap(x -> Maybe.unit(func.apply(arg)));
    }

    ...
}





class Maybe<T> {

    ...

    public T getOrElse(T alternative) {
        if(isEmpty()) {
            return alternative;
        } else {
            return arg;
        }
    }

    ...
}

      


         public Mayber<Person> getParent(...)




          Maybe.unit(getUserId())
                .flatMap(id -> getParent(id))
                .flatMap(id -> getParent(id))
                .map(grandParent -> grandParent.getAge())
                .getOrElse(99)









        public static <T> Maybe<T> unit(Supplier<T> func)

        public <R> Maybe<R> flatMap(Function<T, Maybe<R>> func) 

        public <R> Maybe<R> map(Function<T, R> func)

      


         public Mayber<Person> getParent(...)




          Maybe.unit(getUserId())
                .flatMap(id -> getParent(id))
                .flatMap(id -> getParent(id))
                .map(grandParent -> grandParent.getAge())
                .getOrElse(99)

execution

    "be insertable and retrievable - poor version" in {
      // read everything from a table
      blockingWait(db.run(UniversityTable.result)) should 
                    have size 0

      // insert into table
      blockingWait(db.run(
        UniversityTable ++= Seq(
          University("Hogwart"),
          University("Scala University")
        )
      ))

      // read again
      val results = blockingWait(
            db.run(UniversityTable.result)
      )

      results.map(_.name) should contain theSameElementsAs
        Seq("Hogwart", "Scala University")
    }

Poor version

Description

vs

execution







db.run(                      // execution
    (StudentCourseSegmentTable              // description
          join CourseTable on (_.courseId === _.id)
          join StudentTable on (_._1.studentId === _.id))
    .result
)

      db.run(    // first all within this block is constructed
                 // and the resulting DBIO is passed to db.run
                 // to be executed
          for {
            emptyResults <- UniversityTable.result
            _ <- insertUnis
            nonEmptyResults <- UniversityTable.result
          } yield {
            emptyResults should have size 0
            nonEmptyResults.map(_.name) should 
                    contain theSameElementsAs
              Seq("Hogwart", "Scala University")
          }
      )
...
      def insertUnis = UniversityTable ++= Seq(
          University("Hogwart"),
          University("Scala University")
      )

Better version

transactions.stream()
    .filter(t -> t.getType() == Transaction.GROCERY)
    .sorted(comparing(Transaction::getValue).reversed())
    .map(Transaction::getId) // up to this line - description
    .collect(toList()) // execution

Java 8 streams

db.run(                      // execution
    (StudentCourseSegmentTable              // description
          join CourseTable on (_.courseId === _.id)
          join StudentTable on (_._1.studentId === _.id)).result
)

Scala / Slick

DBIOAction / DBIO

DBIOAction[R, S, E]

R - type of result
S - streaming / not streaming
E - what kind of effect

DBIO[R] =

DBIOAction[R, NoStream, Effect.All]

R - type of result

  def executeReadOnly[R, S <: dbio.NoStream](
    readOnlyOper: DBIOAction[R, S, Effect.Read] // param decl
  ): Future[Unit] = { // result type
    db.run(readOnlyOper).map { results =>
      log.info(s"Results are: $results")
    }
  }

// this works
executeReadOnly(UniversityTable.result)

// this won't even compile
executeReadOnly(UniversityTable += University("Nice try!"))

// DBIOAction[Seq[University], Stream, Effect.Read]
db.run(
    UniversityTable.result
)
...

// DBIOAction[Option[Int], NoStream, Effect.Write]
db.run(
    UniversityTable += University("Nice try!")
)
...

// DBIOAction[Option[Int], NoStream, 
// Effect.Write with Effect.Transactional]
db.run(
    (UniversityTable += University("Nice try!"))
    .transactionally
)
...
// DBIOAction[Unit, NoStream, Effect.Schema]
db.run(
    UniversityTable.schema.create
)

Query

Query[M, U, C]

M - mixed type

U - unpacked type

C - collection type


UniversityTable                            
    .filter(_.name === "Hogwarth")

What are unpacked (U), mixed (M) and collection (C) types?


UniversityTable                            
    .filter(_.name === "Hogwarth")    
    // _ above is UniverityTable (M)


  // unpacked type
  case class University(name: String,
                        id: Long = -1L)

  // mixed type
  class UniversityTable(tag: Tag) extends Table[University](
    tag, "university") {
    
    def name = column[String]("name")
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

    // default projection
    def * = (name, id) <> (University.tupled, 
                           University.unapply)
  }








db.run(    // Future[Seq[University]]

   UniversityTable.filter(uni => // Query[UniversityTable,
                                 // University, Seq]
       uni.name === "Hogwarth"
   )
   .result    // DBIO[Seq[University]]

)

If you were to remember only one thing

Monadic Trio

DBIO

Query

description of a DB query

description of 1...N DB operations

Future

gimme a break...

The other thing worth to remember

It's (mostly) all about DBIO composition

Composition / Transactions

DBIO.transactionally

Click

db.run(
    (UniversityTable ++= Seq(
      University("Hogwart"),
      University("Scala University")
    )).transactionally
)

Transactions - example

OK, but how do we combine DB operations?

DBIO is a monad

db.run(
    (for {
        dbio1 <- insertUnis
        dbio2 <- ...
        dbio3 <- ...
        ...
        dbioN <- ...
    } yield {
        ...
    }).transactionally
)

def insertUnis: DBIO[Option[Int]] =
    UniversityTable ++= Seq(
      University("Hogwart"),
      University("Scala University")
    )

map / flatMap

seq

    val combined: DBIO[Unit] = DBIO.seq(
        produceDbOper1,    // DBIO[T]
        produceDbOper2,    // DBIO[R]
        produceDbOper3     // DBIO[M]
    )

sequence

    val combined: DBIO[Seq[T]] = DBIO.sequence(
        Seq(
            produceDbOper1, // DBIO[T]
            produceDbOper2, // DBIO[T]
            produceDbOper3  // DBIO[T]
        )
    )

  db.run( // Future[Seq[Seq[Course]]]
    for {
      students <- StudentTable.result
      courses <- DBIO.sequence(students.map(fetchMoreData))
    } yield {
      courses
    }
  )


...


  private def fetchMoreData(student: Student): 
    DBIO[Seq[Course]] = {
    (for {
      segment <- StudentCourseSegmentTable 
                if segment.studentId === student.id
      course <- segment.course
    } yield {
      course
    }).result
  }

unit

db.run(
    (for {
        student <- StudentTable
                    .filter(_.name === "Tim").result // DBIO
        smthNotDb <- calculateSomethingNotDb(student) // duh?!
        _ <- updateBasedOnCalculation(smthNotDb) // DBIO
    } yield (...)).transcationally
)

Wrong!

DBIO.successful

db.run(
    (for {
        student <- StudentTable
                    .filter(_.name === "Tim").result // DBIO
        smthNotDb <- DBIO.successful(
            calculateSomething(student)
        ) // Yeah!
        _ <- updateBasedOnCalculation(smthNotDb) // DBIO
    } yield (...)).transcationally
)





    def execTransact[T](dbio: DBIO[T]): Future[T] = 
        db.run(dbio.transactionally)

Combine operations & fire within transtion

How does FP deal with effects?

They cheat

If you cannot solve the problem, change it into problem you can solve

Build description (referenetially transparent)

+

combine

+

db.run(...)

Errors?

Exceptions



    // File Name : ExcepTest.java
    import java.io.*;
    
    public class ExcepTest {
    
       public static void main(String args[]) {
          try {
             int a[] = new int[2];
             System.out.println("Access element three :" + a[3]);
          } catch (ArrayIndexOutOfBoundsException e) {
             System.out.println("Exception thrown  :" + e);
          }
          System.out.println("Out of the block");
       }
    }

A better alternative is to have your functions return error values when things go wrong, and to deal with these explicitly, no matter how verbose it might be.

OCTOBER 13, 2003









            int fputc( int c, FILE *fp );

Not new concept - new incarnation Go lang


    input, err := files.ReadInput(inputPath)
    if err != nil {
	logrus.Debugf("Can't open the template: %v", err)
	return err
    }
    

    result, err := r.Render(templateName, string(input))
    if err != nil {
	return err
    }    
    
    err = files.WriteOutput(outputPath, []byte(result), 0644)
    if err != nil {
	logrus.Debugf("Can't save the rendered: %v", err)
	return err
    }

Try

  
  object Test extends App {
    
        def readTextFile(filename: String): Try[List[String]] = {
            Try(Source.fromFile(filename).getLines.toList)
        }
    
        val filename = "/etc/passwd"
        readTextFile(filename) match {
            case Success(lines) => lines.foreach(println)
            case Failure(f) => println(f)
        }
    
    }





db.run(
    (UniversityTable ++= Seq(
      University("Hogwart"),
      University("Scala University")
    )).transactionally.asTry
)








db.run(    // Future[Seq[University]]

   UniversityTable.filter(uni => // Query[UniversityTable,
                                 // University, Seq]
       uni.name === "Hogwarth"
   )
   .result.asTry    // DBIO[Try[Seq[University]]]

)

       

        tryDbioResult match {    // DBIO[Try[Result]]

            case Success(myResult) => ....
            case Failure(exception) => ...

        }

Joins

Monadic

applicative


  // student course segment
  case class StudentCourseSegment(studentId: Id[Student],
                                  courseId: Id[Course],
                                  semesterId: Id[Semester],
                                  id: Id[StudentCourseSegment] = Id.none)

  class StudentCourseSegmentTable(tag: Tag) extends 
    Table[StudentCourseSegment](tag, "STUDENT_COURSE_SEGMENT") {
    def studentId = column[Id[Student]]("STUDENT_ID")
    def courseId = column[Id[Course]]("COURSE_ID")
    def semesterId = column[Id[Semester]]("SEMESTER_ID")
    def id = column[Id[StudentCourseSegment]]("ID", O.PrimaryKey, O.AutoInc)

    def * = (studentId, courseId, semesterId, id) <> (StudentCourseSegment.tupled, 
                                                      StudentCourseSegment.unapply)

    // foreign keys
    def student = foreignKey("fk_segment_student", studentId, StudentTable)(_.id)
    def course = foreignKey("fk_segment_course", courseId, CourseTable)(_.id)
    def semester = foreignKey("fk_segment_semester", semesterId, SemesterTable)(_.id)
  }

  lazy val StudentCourseSegmentTable = TableQuery[StudentCourseSegmentTable]




def student = 
    foreignKey("fk_segment_student", 
        studentId, StudentTable)(_.id)
def course = 
    foreignKey("fk_segment_course", 
        courseId, CourseTable)(_.id)
def semester = 
    foreignKey("fk_segment_semester", 
        semesterId, SemesterTable)(_.id)

db.run((
    for {
      segment <- StudentCourseSegmentTable
      course <- segment.course // foreign key
      student <- segment.student // foreign key
    } yield (course, student)
)

db.run(
        StudentCourseSegmentTable
          join CourseTable on (_.courseId === _.id)
          join StudentTable on (_._1.studentId === _.id)
        ).map {
            case ((segment, course), student) => 
                (course, student)
        }
)

Monadic

Applicative





... 
    // in my table mapping
    def id = column[Id[StudentCourseSegment]]("ID", 
        O.PrimaryKey, O.AutoInc)
...
    // in my case class
    case class StudentCourseSegment(studentId: Id[Student],
                        courseId: Id[Course],
                        semesterId: Id[Semester],
                        id: Id[StudentCourseSegment] = Id.none)
...




case class Id[T](value: Long)


object TypesafeId {
  implicit def columnType[T]: BaseColumnType[Id[T]] =
    MappedColumnType.base[Id[T], Long](toLong, fromLong)

  private def fromLong[T](dbId: Long): Id[T] = Id(dbId)

  private def toLong[T](id: Id[T]): Long = id.value
}





    StudentCourseSegmentTable
          join CourseTable on (_.studentId === _.id)    // WTF
          join StudentTable on (_._1.courseId === _.id)

Type-safety again

Wrong!

More complicated join

Applicative vs monadic

StudentCourseSegmentTable
    .join(StudentTable)
        .on { case (segment, student) => 
            student.id === segment.studentId }
    .join(CourseTable)
        .on { case ((segment, _), course) => 
            course.id === segment.courseId }
    .join(SemesterTable)
        .on { case (((segment, _), _), semester) => 
            semester.id === segment.semesterId }
    .filter { case (((_, student), _), _) =>
        student.name === "Tim"
    }

    select x2."NAME",
           ...
    from    "STUDENT_COURSE_SEGMENT" x2, 
            "STUDENT" x3, 
            "COURSE" x4, 
            "SEMESTER" x5 

    where   (x3."NAME" = 'Tim') and 
            (((x3."ID" = x2."STUDENT_ID") and 
            (x4."ID" = x2."COURSE_ID")) and 
            (x5."ID" = x2."SEMESTER_ID"))

    for {
      segment <- StudentCourseSegmentTable
      student <- segment.student if student.name === "Tim"
      course <- segment.course
      semester <- segment.semester
    } yield (segment, student, course, semester)

    select x2."NAME",
           ...
    from    "STUDENT_COURSE_SEGMENT" x2, 
            "STUDENT" x3, 
            "COURSE" x4, 
            "SEMESTER" x5 

    where   (x3."NAME" = 'Tim') and 
            (((x3."ID" = x2."STUDENT_ID") and 
            (x4."ID" = x2."COURSE_ID")) and 
            (x5."ID" = x2."SEMESTER_ID"))

Sometimes one form is more elegant than the other

Outer joins

// document
case class Document(studentId: Option[Id[Student]],
                    name: String,
                    uuid: String,
                    id: Id[Document] = Id.none)

class DocumentTable(tag: Tag) extends Table[Document]
    (tag, "DOCUMENT") {
    def studentId = column[Option[Id[Student]]]("STUDENT_ID")
    def name = column[String]("NAME")
    def uuid = column[String]("UUID")
    def id = column[Id[Document]]("ID", 
                        O.PrimaryKey, O.AutoInc)
    
    def * = (studentId, name, uuid, id) <> 
            (Document.tupled, Document.unapply)

    def student = foreignKey("fk_document_student", 
        studentId, StudentTable)(_.id.?)
}

lazy val DocumentTable = TableQuery[DocumentTable]




case class Document(studentId: Option[Id[Student]],
                    name: String,
                    uuid: String,
                    id: Id[Document] = Id.none)
//...
//...

class DocumentTable(tag: Tag) extends Table[Document]
    (tag, "DOCUMENT") {
    def studentId = column[Option[Id[Student]]]("STUDENT_ID")
//...
//...
    def student = foreignKey("fk_document_student", 
        studentId, StudentTable)(_.id.?)
}




    DocumentTable
          .joinLeft(StudentTable).on(_.studentId === _.id)

    select x2."STUDENT_ID", ..., x3."ID", ... 

    from "DOCUMENT" x2 

    left outer join "STUDENT" x3 
        on x2."STUDENT_ID" = x3."ID"



        DocumentTable
          .joinLeft(StudentTable).on(_.studentId === _.id)
          .filter { case(doc, student) => 
                student.map(_.name) === "Tom" 
        }

    select x2."STUDENT_ID", ..., x3."ID", ... 

    from "DOCUMENT" x2 

    left outer join "STUDENT" x3 
        on x2."STUDENT_ID" = x3."ID"

     where x3."NAME" = 'Tom'

Slick 3.2 ?

https://github.com/slick/slick/issues/179

No nice monadic form for outer joins

Summary

Future / DBIO / Query

Slick is not ORM

DBIO composition

Think in terms of Collection API

Questions?

@pdolega

Resources

Slick docs: http://slick.lightbend.com/docs/

Online workshop by Dave Gurnell: https://vimeo.com/148074461

Excelent book: http://underscore.io/books/essential-slick/

Bonus

    





    for {
          segment <- StudentCourseSegmentTable
          student <- segment.student if student.name === "Tim"
          course <- segment.course
          semester <- segment.semester
    } yield (segment, student, course, semester)


DEBUG s.c.QueryCompilerBenchmark - ------------------- Phase: Time ---------
DEBUG s.c.QueryCompilerBenchmark -       assignUniqueSymbols:    0.446286 ms
DEBUG s.c.QueryCompilerBenchmark -                inferTypes:    0.239283 ms
DEBUG s.c.QueryCompilerBenchmark -              expandTables:    0.984031 ms
DEBUG s.c.QueryCompilerBenchmark -           forceOuterBinds:    1.048910 ms
DEBUG s.c.QueryCompilerBenchmark -         removeMappedTypes:    0.536035 ms
DEBUG s.c.QueryCompilerBenchmark -                expandSums:    0.016068 ms
DEBUG s.c.QueryCompilerBenchmark -         emulateOuterJoins:    0.108066 ms
DEBUG s.c.QueryCompilerBenchmark -             expandRecords:    0.421471 ms
DEBUG s.c.QueryCompilerBenchmark -        flattenProjections:    2.212847 ms
DEBUG s.c.QueryCompilerBenchmark -              rewriteJoins:    2.219579 ms
DEBUG s.c.QueryCompilerBenchmark -             verifySymbols:    0.204175 ms
DEBUG s.c.QueryCompilerBenchmark -             relabelUnions:    0.086386 ms
DEBUG s.c.QueryCompilerBenchmark -          createAggregates:    0.013349 ms
DEBUG s.c.QueryCompilerBenchmark -           resolveZipJoins:    0.126179 ms
DEBUG s.c.QueryCompilerBenchmark -          pruneProjections:    0.567347 ms
DEBUG s.c.QueryCompilerBenchmark -           rewriteDistinct:    0.018858 ms
DEBUG s.c.QueryCompilerBenchmark -    createResultSetMapping:    0.274411 ms
DEBUG s.c.QueryCompilerBenchmark -            hoistClientOps:    0.649790 ms
DEBUG s.c.QueryCompilerBenchmark -         reorderOperations:    0.643005 ms
DEBUG s.c.QueryCompilerBenchmark -     mergeToComprehensions:    4.482695 ms
DEBUG s.c.QueryCompilerBenchmark -            optimizeScalar:    0.179255 ms
DEBUG s.c.QueryCompilerBenchmark -          removeFieldNames:    1.145481 ms
DEBUG s.c.QueryCompilerBenchmark -                   codeGen:    3.783455 ms
DEBUG s.c.QueryCompilerBenchmark -                     TOTAL:   20.406962 ms

      
    val query = Compiled { name: Rep[String] =>
        for {
          segment <- StudentCourseSegmentTable
          student <- segment.student if student.name === name
          course <- segment.course
          semester <- segment.semester
        } yield (segment, student, course, semester)
    }
...
...
    db.run(query("Tim").result)

Precompilation

    Property age = Property.forName("age");
    List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.disjunction()
        .add( age.isNull() )
        .add( age.eq( new Integer(0) ) )
        .add( age.eq( new Integer(1) ) )
        .add( age.eq( new Integer(2) ) )
    ) )
    .add( Property.forName("name").in( 
        new String[] { "Fritz", "Izi", "Pk" } ) 
    )
    .list();

Criteria API?

    StudentTable.filter(row: StudentTable => ...)

Criteria API?

  
  StudentTable.filter(myExtractedFilter)


  def myExtractedFilter(row: StudentTable): Rep[Boolean] = {
    row.name === "Tom" && row.nationality === "American"
  }




    StudentTable
        .map(s => (s.name, s.surname))
        .distinctOn(_._1)

    
    select "NAME", min("SURNAME") 

    from "STUDENT" 

    group by "NAME"

Kotlin

Ratpack

Hadoop

Akka

Kafka

Kubernetes

AWS

Engineering spirit

Quality orientation

Experienced people to learn from

Pair programming

Community

Autonomy (technical / processes)

Slick 101 - 2018

By Pawel Dolega

Slick 101 - 2018

1,808

#rzeszowjava

Introduction (101)

Introduction (101)

Let's talk about different approaches to API

Repository

Preso

Who am I?

What is Slick?

Not an ORM

ORM problems

N+1 problem (lazy / eager fetching)

Session context scope

Execution under the cover (cache)

O-R impedance mismatch

False promise

Leaky abstraction

FRM

Code samples:

Config

Referential transparency

&

Local Reasoning

Spring annotations

Back to reactive

Reactive (™?)

How many connections do you need?

Server handling 10 000 client connections may need not more than 10 connection

The point being...

Type-Safe

OK!

Wrong!

Wrong!

How?

Tables

Code samples:

Poor version

Queries

Code samples:

Actual query

Actual query

Steps of execution

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.

Wrong!

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.

Wrong!

OK!

The best approach to write queries using Slick’s type-safe API is thinking in terms of Scala collections.

*Click*

Actions

Poor version

Monadic Trio

Poor version

Description

vs

execution

Better version

DBIOAction / DBIO

DBIOAction[R, S, E]

R - type of result S - streaming / not streaming E - what kind of effect

DBIO[R] =

DBIOAction[R, NoStream, Effect.All]

R - type of result

Query

Query[M, U, C]

M - mixed type

U - unpacked type

C - collection type

If you were to remember only one thing

Monadic Trio

The other thing worth to remember

It's (mostly) all about DBIO composition

Composition / Transactions

DBIO.transactionally

*Click*

Transactions - example

OK, but how do we combine DB operations?

DBIO is a monad

map / flatMap

seq

sequence

Click

R - type of result
S - streaming / not streaming
E - what kind of effect

Click