what is slick and why we use it? mark van der tol
TRANSCRIPT
![Page 1: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/1.jpg)
What is Slick and why we use it?Mark van der Tol
![Page 2: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/2.jpg)
Why we moved to SQLMongoDB has• No joins or referential integrity• No transactions• Max element size for indexAdvantages PostgreSQL has• Better indexes• Better tooling• Strict schema
2
![Page 3: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/3.jpg)
Problem faced with MongoDBQuery unseen alerts for usersData:{ "message": "Example alert", "seenBy": [...]}Query:{ "seenBy": { "$ne": "user" }}Max element size for indexed elements: 1 KB
3
![Page 4: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/4.jpg)
Trivial in SQLSELECT messageFROM alertsWHERE id NOT IN ( SELECT messageId FROM views WHERE user = "user")
4
![Page 5: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/5.jpg)
MongoDB• We keep using Mongo for statistics data• Easier to shard/replicate• No schema enforcement
5
![Page 6: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/6.jpg)
What is Slick• Database query library for Scala
• Table mapping• Strongly typed• Collection like syntax
6
![Page 7: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/7.jpg)
Available for • DB2*• Derby/JavaDB• H2• HSQLDB/HyperSQL• Microsoft Access• Microsoft SQL Server• MySQL• Oracle*• PostgreSQL• SQLite*Requires subscription for production use
7
![Page 8: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/8.jpg)
Database connectionimport scala.slick.driver.PostgresDriver.simple._ import Database.threadLocalSession
Database.forURL("jdbc:postgresql://…", driver = "org.postgresql.Driver") withSession { //session is now implicitly available in thread-local storage}
Database.forURL("jdbc:postgresql://…", driver = "org.postgresql.Driver") withTransaction { //session is now implicitly available in thread-local storage}
8
![Page 9: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/9.jpg)
Table definition
object CoffeeTable extends Table[(String, BigDecimal, Int)]("COFFEE") {
def name = column[String]("NAME", O.PrimaryKey) def price = column[BigDecimal]("PRICE") def sales = column[Int]("SALES")
def * = name ~ price ~ sales} 9
![Page 10: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/10.jpg)
Simple SELECT queryval minPrice: BigDecimal = 1.0
val query = for { c <- CoffeeTable if (c.price >= minPrice)} yield (c.name)
val names = query.list10
![Page 11: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/11.jpg)
Table definition for Case classcase class Coffee (name: String, price: BigDecimal, sales: Int)
object CoffeeTable extends Table[Coffee]("coffee") {
def name = column[String]("NAME", O.PrimaryKey) def price = column[BigDecimal]("PRICE") def sales = column[Int]("SALES") def * = name ~ price ~ sales <> (Coffee, Coffee.unapply _)} 11
![Page 12: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/12.jpg)
Simple SELECT query
val query = for { c <- CoffeeTable} yield (c)
val coffees = query.list
12
![Page 13: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/13.jpg)
SELECT query with join
val query = for { p <- PersonTable c <- CoffeeTable if (p.favoriteCoffee === c.name)} yield (p.name, c.name, c.price)
13
![Page 14: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/14.jpg)
INSERT query
CoffeeTable.insert( Coffee("Java", 2.50, 0))
14
![Page 15: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/15.jpg)
UPDATE/DELETE queryval query = for { c <- CoffeeTable if (c.price < 1.50)} yield (c.price)
query.update(1.50)
query.delete15
![Page 16: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/16.jpg)
Plain SQL queries
val query = StaticQuery .query[BigDecimal, (String, Int)]( """select c.name, c.sales from coffees c where c.price < ?""")
16
![Page 17: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/17.jpg)
Features not shown
• Queries with parameters• Extensible: Add own types and functions• "Direct embedding"
17
![Page 18: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/18.jpg)
Features
• Query library that stays close to SQL• Hides dbms specific syntax• Prevents SQL-injections• Many checks compile time
18
![Page 19: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/19.jpg)
Disadvantages
• DSL not always intuitive• Difficult compile errors• Focus on single thread usage• Not very comprehensive documentation
19
![Page 20: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/20.jpg)
Multi-threaded use workaround val session = Database.forURL("jdbc:…", driver = “…").createSession()
session.conn.setAutoCommit(false)//prevent Slick from creating transactions itselfsession.asInstanceOf[BaseSession].inTransaction = true
…
session.conn.commit()session.close()
20
![Page 21: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/21.jpg)
Resources
• Slick:http://slick.typesafe.com/http://groups.google.com/group/scalaquery
• Sheets:http://www.plotprojects.com/
21
![Page 22: What is Slick and why we use it? Mark van der Tol](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649dd45503460f94acbd6d/html5/thumbnails/22.jpg)
THANK YOU 22