What is Slick and why we use it?

tenderlaΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 3 μήνες)

71 εμφανίσεις

What is Slick and
why we use it?

Mark van der Tol

Why we moved to SQL

MongoDB has


No joins or referential integrity


No transactions


Max element size for
index

A
dvantages PostgreSQL has


Better indexes


Better tooling


Strict schema

2

Problem faced with MongoDB

Query unseen alerts for users

Data:

{


"message": "Example alert",


"seenBy": [...]

}

Query:

{


"seenBy": { "$ne": "user" }

}

Max element size for indexed elements: 1 KB

3

Trivial in SQL

SELECT message

FROM alerts

WHERE id NOT IN (


SELECT messageId


FROM views


WHERE user = "user"

)

4

MongoDB


We keep using Mongo for statistics data


Easier to shard/replicate


No schema enforcement

5

What is Slick


Database query library for Scala



Table mapping


Strongly typed


Collection like syntax

6

Available for


DB2*


Derby/JavaDB


H2


HSQLDB/HyperSQL


Microsoft Access


Microsoft SQL Server


MySQL


Oracle*


PostgreSQL


SQLite

*Requires subscription for production use


7

Database connection

import

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

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

Simple SELECT query

val

minPrice:
BigDecimal

= 1.0


val

query =
for

{


c <
-

CoffeeTable


if

(c.price >= minPrice)

}
yield

(c.name)


val

names = query.list


10

Table definition for Case class

case

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

Simple SELECT query

val

query =
for

{


c <
-

CoffeeTable

}
yield

(c)


val

coffees = query.list


12

SELECT query with join

val

query =
for

{



p
<
-

PersonTable


c <
-

CoffeeTable


if

(p.favoriteCoffee === c.name)

}
yield

(p.name, c.name, c.price)

13

INSERT query

CoffeeTable
.insert(


Coffee
("Java", 2.50, 0)

)

14

UPDATE/DELETE query

val

query =
for

{


c <
-

CoffeeTable


if

(c.price < 1.50)

}
yield

(c.price)


query.update(1.50)


query.delete

15

Plain SQL queries

val

query =
StaticQuery



.query[
BigDecimal
, (
String
,
Int
)](


"""select c.name, c.sales



from coffees c


where c.price
<
?"""

)


16

Features not shown


Queries with parameters


Extensible: Add own types and functions


"Direct embedding"


17

Features


Query library that stays close to SQL


Hides dbms
specific syntax


Prevents SQL
-
injections


Many checks compile time

18

Disadvantages


DSL not always intuitive


Difficult compile errors


Focus on single thread usage


Not very comprehensive documentation

19

Multi
-
threaded use workaround

val

session =
Database
.forURL("jdbc:…", driver =
“…").createSession()


session.conn.setAutoCommit(false)

//prevent Slick from creating transactions itself

session.asInstanceOf[
BaseSession
].inTransaction = true





session.conn.commit()

session.close()



20

Resources


Slick:


http
://slick.typesafe.com/


http
://groups.google.com/group/scalaquery



Sheets:


http://www.plotprojects.com/

21

THANK YOU


22