Object Relational Mapping in Scala

Introduction to ORMs, Impedance-Mismatch challenge and how to solve it with functional relational mapping, ORMs review, Slick review.

Presented as a part of Software engineering workshop course, Ben Gurion University of Beer Sheva, Israel. April 2015.

Transcript

1. OBJECT RELATIONAL MAPPING Lidan Hifi SCALA IN Workshop on Software Engineering Project games of for ums
2. Quick Introduction
3. Games Of For ums Object-Relational-Mapping in Scala Object Relational Mapping A technique for converting data between incompatible type systems in object oriented programming languages. This creates a “virtual object database” that can be used from within the programming language. —wikipedia
4. Wait, What?
5. Games Of For ums Object-Relational-Mapping in Scala Object Relation
6. Games Of For ums Object-Relational-Mapping in Scala Object Relation
7. Games Of For ums Object-Relational-Mapping in Scala Impedance-Mismatch Object-Oriented Relational Encapsulation No Encapsulation Inheritance, Polymorphism No Polymorphism Behavior No Behaviour Complex data types Primitive data types* * PostgreSQL supports blobs, JSON, XML, enums, geolocation,… even composite types!
8. Functional Relational Mapping
9. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
10. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
11. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
12. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
13. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
14. Games Of For ums Object-Relational-Mapping in Scala Relational Model • Relation • Attribute • Tuple • Relation Value • Relation Variable NAME :String PRICE :Double SUP_ID :Int Johnnie Walker $150 107 Jack Daniel’s $50 401 Glenfiddich 30 $1020 212 WHISKEYS
15. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50  ,401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
16. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50,  401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
17. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50,  401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
18. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50,  401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
19. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50,  401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
20. Games Of For ums Object-Relational-Mapping in Scala Mapped to Scala • Relation • Attribute • Tuple • Relation Value • Relation Variable case  class  Whiskey(      name:  String,      price:  Double,      supplierId:  Int   )   val  whiskeys  =  Set(      Whiskey(“Johnnie  Walker”,150,  107),      Whiskey(“Jack  Daniels”,50,  401),        Whiskey(“Glenfiddich  30”,  1020,  212)   )
21. Games Of For ums Object-Relational-Mapping in Scala Write Database Code in Scala for  {  w  <-­‐  whiskeys  }  yield  w.name SELECT  w.name  FROM  whiskeys  w
22. Games Of For ums Object-Relational-Mapping in Scala Write Database Code in Scala (for  {  w  <-­‐  whiskeys.filter(_.price  <  100)                        if  w.name.startsWith(“J”)              }  yield  w)   .groupBy(_.supId)   .map  {  (sup,  ws)  =>  (sup,  ws.length)  }  
23. Games Of For ums Object-Relational-Mapping in Scala Object Relation
24. Object Relational Mapping in Scala
25. Games Of For ums Object-Relational-Mapping in Scala JDBC • Not an ORM! Have to map the objects by ourselves • lot of Q&A in Stack Overflow • Low level • Inherently blocking (and blocking ties up threads)
26. Games Of For ums Object-Relational-Mapping in Scala Hibernate • Mature project, natural choice for the most JVM-based projects • Pure java, so we cannot use Scala collections framework features (at least without a lot of conversions & plugins) • Inherently blocking • XML-based, yuk!
27. Games Of For ums Object-Relational-Mapping in Scala Squeryl • Pure Scala! • SQL-like queries, but type-safe! • Convention over configuration- less boilerplate • Complete documentation with extensive test suite • Not reactive :(
28. Games Of For ums Object-Relational-Mapping in Scala SORM • Complete abstraction from relational concepts • Very clear, no boilerplate! • Connection-agnostic API- integrated connection pooler (automatic). • Highly concurrent • Not completely type-safe
29. Games Of For ums Object-Relational-Mapping in Scala Slick • Writing functional queries, not SQL 
 (plain SQL is also supported) with query composition • 100% type safe • Highly verbose (Slick 3.0 solves it with Scala-Macros) • Reactive by nature • Backed by Typesafe Scala Language Integrated Connection Kit
30. Welcome Slick
31. Games Of For ums Object-Relational-Mapping in Scala Getting Started libraryDependencies  ++=  Seq(      "com.typesafe.slick"  %%  "slick"  %  "3.0.0-­‐RC3",      "org.slf4j"  %  "slf4j-­‐nop"  %  "1.6.4"   ) mydb  =  {      url  =  “jdbc:h2:mem:games_of_forums”      driver  =  org.h2.Driver      connectionPool  =  disabled      keepAliveConnection  =  true   } in-memory database!
32. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
33. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
34. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
35. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
36. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
37. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
38. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
39. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
40. Games Of For ums Object-Relational-Mapping in Scala Table Schema class  Whiskeys(tag:  Tag)  extends  
 Table[(Int,  String,  Double,  Int)](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id,  name,  price,  supId)   }   val  whiskeys  =  TableQuery[Whiskeys]
41. Games Of For ums Object-Relational-Mapping in Scala Table Schema case  class  Whiskey(id:  Option[Int],  name:  String,
                                      price:  Double,  supId:  Int)   class  Whiskeys(tag:  Tag)  extends  
 Table[Whiskey](tag,  “WHISKEYS”)   {      def  id  =  column[Int](“ID”,  O.PrimaryKey,  O.AutoInc)      def  name  =  column[String]("NAME",  O.NotNull)      def  price  =  column[Double](“PRICE”,  O.Default(0))      def  supId  =  column[Int]("SUP_ID")      def  *  =  (id.?,  name,  price,  supId)  <>  
                    (Whiskey.supId,Whiskey.unapply)   }
42. Games Of For ums Object-Relational-Mapping in Scala Foreign keys & Constraints val  suppliers  =  TableQuery[Supplier]   class  Whiskeys(tag:  Tag)  extends  
 Table[Whiskey](tag,  “WHISKEYS”)   {      //...   def  supID  =  column[Int]("SUP_ID")      //...      def  supplier  =  foreignKey("SUP_FK",  supId,   suppliers)(_.id,  onUpdate=ForeignKeyAction.Restrict,   onDelete=ForeignKeyAction.Cascade)      //...   }
43. Games Of For ums Object-Relational-Mapping in Scala DDL val  whiskeys  =  TableQuery[Whiskeys]   val  suppliers  =  TableQuery[Suppliers]   val  schema  =  whiskeys.schema  ++  suppliers.schema   db.run(DBIO.seq(      schema.create,      //...      schema.drop   ))
44. Games Of For ums Object-Relational-Mapping in Scala Insert, Update val  whiskeys  =  TableQuery[Whiskeys]   whiskeys  +=  (“Grant’s”,  10,  432)   whiskeys  ++=  Seq(   (“Jameson”,  80,  322),   (“Wild  Turkey”,  115,  3)   )   whiskeys.filter(_.name  ===  “Jameson”)                  .map(p  =>  (p.name,  p.price))
                .update((“Jameson  15”,  100.0))
45. Games Of For ums Object-Relational-Mapping in Scala Querying val  whiskeys  =  TableQuery[Whiskeys]   //  SELECT  name  FROM  WHISKEYS  WHERE  price  >  1000   for  (w  <-­‐  whiskeys  if  w.price  >  1000)  yield  w.name   equivalent to: whiskeys.filter(_.price  >  1000).map(_.name)   //  SELECT  *  FROM  WHISKEYS  LIMIT  5  OFFSET  10   val  q1  =  whiskeys.drop(5).take(5)     //  SELECT  *  FROM  WHISKEYS  ORDER  BY  name  DESC   val  q2  =  whiskeys.sortBy(_.name.desc)
46. Games Of For ums Object-Relational-Mapping in Scala Aggregations val  q  =  whiskeys.map(_.price)     //  SELECT  MIN(price)  FROM  WHISKEYS   val  q1  =  q.min   //  SELECT  MAX(price)  FROM  WHISKEY   val  q2  =  q.max   //  SELECT  SUM(price)  FROM  WHISKEY   val  q3  =  q.sum   //  SELECT  AVG(price)  FROM  WHISKEY   val  q4  =  q.avg  
47. Games Of For ums Object-Relational-Mapping in Scala Grouping //  Group  by   val  q  =  (for  {  w  <-­‐  whiskeys                                s  <-­‐  suppliers  }  yield  (w,  s))   .groupBy((w,s)  =>  w.supId)  
48. Games Of For ums Object-Relational-Mapping in Scala Joins //  SELECT  w.name,  s.name     //  FROM  WHISKEYS  w  INNER  JOIN  SUPPLIERS  s   val  crossJoin  =  for  {                                (w,  s)  <-­‐  whiskeys  join  suppliers  }                                  yield  (w.name,  s.name)   //  SELECT  w.name,  s.name     //  FROM  WHISKEYS  w  INNER  JOIN  SUPPLIERS  s   //  ON  s.supId  ==  w.supId   val  innerJoin  =  for  {                                (w,  s)  <-­‐  whiskeys  join  suppliers  on                                                            w.supId  ===  s.supId  }                                  yield  (w.name,  s.name)
49. Games Of For ums Object-Relational-Mapping in Scala Plain SQL - JDBC import  java.sql._   Class.forName("org.h2.Driver")   val  conn  =  DriverManager.getConnection("jdbc:h2:mem:test1")   val  people  =  new  scala.collection.mutable.MutableList[(Int,String,Int)]()   try  {      val  stmt  =  conn.createStatement()      try  {          val  rs  =  stmt.executeQuery("select  ID,  NAME,  AGE  from  PERSON")          try  {              while  (rs.next())  {                  people  +=  ((rs.getInt(1),  rs.getString(2),  rs.getInt(3)))              }          }  finally  {              rs.close()          }      }  finally  {          stmt.close()      }   }  finally  {      conn.close()   }
50. Games Of For ums Object-Relational-Mapping in Scala Plain SQL - JDBC import  java.sql._   Class.forName("org.h2.Driver")   val  conn  =  DriverManager.getConnection("jdbc:h2:mem:test1")   val  people  =  new  scala.collection.mutable.MutableList[(Int,String,Int)]()   try  {      val  stmt  =  conn.createStatement()      try  {          val  rs  =  stmt.executeQuery("select  ID,  NAME,  AGE  from  PERSON")          try  {              while  (rs.next())  {                  people  +=  ((rs.getInt(1),  rs.getString(2),  rs.getInt(3)))              }          }  finally  {              rs.close()          }      }  finally  {          stmt.close()      }   }  finally  {      conn.close()   }
51. Games Of For ums Object-Relational-Mapping in Scala Plain SQL - Slick val  db  =  Database.forConfig("mydb")   val  people  =  tsql"select  ID,  NAME  from  PERSON".as[(Int,String)]   db.run(people)   val  people2  =  sql"select  *  from  PERSON".as[Person]   db.run(people2)  
52. Games Of For ums Object-Relational-Mapping in Scala More features • Joins- left join, right join, full outer join • Monadic joins • Union queries • Query composition • Compiled queries • Recommended reading: 
 http://slick.typesafe.com/doc/3.0.0-RC3/sql-to-slick.html
53. Live Demo Enough talk!
54. #GamesOf Forums Questions? @lidanh