typesafecom Scala LanguageIntegrated Connection Kit Jan Christopher Vogt Software Engineer EPFL Lausanne A database query library for Scala select from person or for p lt Persons yield p ID: 404415
Download Presentation The PPT/PDF document " 404415" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
.typesafe.com
Scala Language-Integrated Connection Kit
Jan Christopher VogtSoftware Engineer, EPFL LausanneSlide2
A database query library for Scala
"select * from person"
or
for( p <- Persons ) yield p
idname1Martin2Stefan
3
Chris
4
Eugene
…
…
person
including insert, update, delete, DDLSlide3
ORM is a swamphttp://foter.com/photo/cypresses/Slide4
Slick is to Hibernate and JDBC,what Scala is to Java and GroovySlickEasy, Concise, Scalable, Safe, CompositionalHibernateComplexScalable, if used with cautionHQL: unsafe, non-compositionalCriteria Queries: safer, compositional, verboseJDBC/AnormSQL: unsafe, non-compositionalSlide5
ORM? No. Better Match:Functional ProgrammingRelationalSQLrowsexpressionsNULL…Functionalcomprehensionstuples / case classeslambdasOption…Slide6
AgendaKey featuresLive demoDetailed query featuresUnder the hoodUpcoming featuresSlide7
Slick key featuresEasyaccess stored data like collectionsunified session handlingConciseScala syntaxfetching results without painScales naturallystatelessexplicit controlSafeno SQL-injectionscompile-time checks (names, types, typos, etc.)Composableit‘s Scala code: abstract and re-use with easeSlide8
EasyIt‘s Scala – you already know itAccess stored data like Scala collectionsPersons.withFilter(_.id === 3).map(_.name)for(p <- Persons if p.id === 3) yield p.name
identical
Persons
id : Int
name : Stringage : IntSlide9
Unified Session ManagementUnified: URL, DataSource, JNDITransactionsimport org.slick.session._implicit val session = Database .forURL(
"jdbc:h2:mem:test1", driver="org.h2.Driver
")
.createSession
session.withTransaction {
// execute queries here
}
session
.close()
or
.forDataSource( dataSource )
o
r
.forName( JNDIName )Slide10
val
name =
... // <- e.g. user input
session.createCriteria(
Person.getClass) .add(
Restrictions.and(
.
add(
Restrictions.gt(
"
age", 20) )
.
add(
Restrictions.lt("age", 25) )
))
for( p <-
Persons
if p.age > 20 || p.age < 25 )
yield p
C
oncise: queries
Hibernate
Criteria
QueriesSlide11
Concise: results
val
name =
... // <- e.g. user input
val
sql
=
"select * from person where name = ?“
val
st = conn.prepareStatement(
sql
)
try
{
st.setString(1,
name
)
val
rs = st.executeQuery()
try
{
val
b =
new
ListBuffer[(Int, String)]
while
(rs.next)
b.append((rs.getInt(1), rs.getString(2)))
b.toList
}
finally
rs.close()
}
finally
st.close()
(
for( p <-
Persons
if p.name === name ) yield p
).list
JDBCSlide12
Scales naturallyStatelessNo cachesExplicit controlWhat is transferredWhen is it transferred (execution)( for( p <- Persons
if p.name === name ) yield (p.id,p.name)
).listSlide13
http://xkcd.com/327/Slide14
val name =
... // <- e.g. user input
"
from Person
where name = ' " + name +
"
'
"
"
select * from person
wehre
name =
'
"
+
name
+
"
'
"
session.createCriteria(Person.getClass)
.
add( Restrictions.eq(
"name"
, name) )
for( p <-
Persons
if p.name === name ) yield p
Slick is Safe
Hibernate
HQL
SQL
(
JDBC/Anorm)
Fully type-checked: No SQL-injections, no typos, code completion
Hibernate
Criteria QueriesSlide15
Type-safe use of stored procedures// stored procedure declarationval dayOfWeekDynamic = SimpleFunction[Int]("day_of_week")def dayOfWeek(c: Column[Date]) = dayOfWeekDynamic(Seq(c))// stored procedure usagefor( p <-
Persons ) yield
dayOfWeek(p.birthdate)
Person
birthdate : DateSlide16
// Interests of people between 20 and 25// Cars of people between 55 and 65def personByAge( from:Int, to:Int ) = Persons.filter(
p => p
.age >= from && p
.age <= to )
for( p <- personByAge(20, 25);
i
<-
Interests
; if
i
.personId ===
p
.id)
yield
i
.text
for
(
p
<-
personByAge
(55, 65);
c
<-
Cars
;
if
c
.personId
===
p
.id)
yield
c
.model
Composable queries
Persons
Cars
Interests
*
*Slide17
SQL fallbackval name = ... // <- e.g. user input( for( p <- Persons if p.name === name ) yield p).listval sql =
"select * from person where name = ?“query[String, (
Int, String)](
sql )( name ).list
using SQL
Native SQL fallback
Not type-safe, but still more convenient than JDBCSlide18
ComparisonJDBCAnormSlickSQuerylHQLCrit.Q.API (safe, composable)✔✔
(
✔
)
Concise✔
✔
✔
✔
Scala
coll.
Syntax
✔
SQL-Like
✔
✔
✔
✔
Native SQL
✔
✔
✔
✔
✔
18
Unique Slick features coming up soonSlide19
Supported DBMSJDBC / AnormSlickSquerylHibernateOracleDB2MS SQL ServerSybaseMySQLPostgreSQLDerby/JavaDBH2HSQLDB/HyperSQLMS AccessSQLite
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
(
✔
)
(
✔
)
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
✔
19
NoSQL coming up in Slick: Summer 2013Slide20
Slick in the ecosystemSlick will be official database connector inPlay / Typesafe StackSuccessor of ScalaQueryInspired by LINQCurrently based on JDBCNoSQL coming summer 2013Influenced by Scala Integrated QuerySlide21
Stable VersionsThis talk: Slick 0.11 pre-release for Scala 2.10Slick 1.0 coming during Scala 2.10‘s RC periodhttp://slick.typesafe.comUse ScalaQuery 0.10 for Scala 2.9http://scalaquery.orgLicense: BSDSlide22
Live DemoSetupMeta dataQueriesinsert some datafind all people above a certain age with their tasksAbstractionsResult athttps://github.com/cvogt/slick-presentationPersonsid : Intname : Stringage : IntTasksid : Inttitle : StringpersonId : Int
*Slide23
Sorting and PagingPersons.sortBy(_.name).drop(5).take(10)Slide24
Grouping and aggregation// Number of people per agePersons.groupBy(_.age).map( p =>( p._1, p._
2.length ) )Slide25
First// person 3Persons.filter(_.id === 3).firstSlide26
UnionPersons.filter(_.age < 18) unionAll Persons.filter(_.age > 65)Slide27
NULL supportcase class Person( ..., age : Option[Int] )object Persons extends Table[Person]("person"){ def age = column[Option
[Int]]("id") ...
}Persons.insertAll(
Person( 1, „Chris“, Some
(22) ), Person( 2, „Stefan“, None ))Slide28
Outer Joins (left, right, full)
for
(
Join(
p, t) <-
Tasks
outerJoin
Persons
on
(_.
personId
=== _.id)
)
yield
p
.title
.? ~
t
.name.?
28
Persons
id : Int
name
: String
age : Int
Tasks
id
: Int
title : String
personId
:
Option[Int]
*Slide29
Relationshipsobject Persons extends Table[Person]("person"){ def id = column[Int]("id") ... }object Tasks extends Table[Task]("task"){ def
id = column[Int]("id")
...
def
assignees = for( pt <- PersonsTasksAssociations
;
p <-
pt
.
assignee
;
if
pt
.
taskId
=== id ) yield
p
}
object
PersonsTasksAssociations
extends Table[(Int,Int)]("person_task"){
def
personId
= column[Int]("person_id")
def
taskId
= column[Int]("task_id")
def
assignee
=
foreignKey
( "
person_fk
",
personId
,
Persons
)(_.
id
)
...
}
for( t <-
Tasks
;
ps
<- t.
assignees
; if t.id === 1 )
yield
ps
Personsid : Int
….
Tasks
id
: Int
…
PersonsTasksAssociations
personId
: Int
taskId : Int
*
*
Assignees of task 1:Slide30
Column OperatorsCommon: .in(Query), .notIn(Query), .count, .countDistinct, .isNull, .isNotNull, .asColumnOf, .asColumnOfTypeComparison: === (.is), =!= (.isNot), <, <=, >, >=, .inSet, .inSetBind, .between, .ifNullNumeric: +, -, *, /, %, .abs, .ceil, .floor, .sign, .toDegrees, .toRadiansBoolean: &&, ||, .unary_!String: .length, .like, ++, .startsWith, .endsWith, .toUpperCase, .toLowerCase, .ltrim, .rtrim, .trimSlide31
Other features (not exhaustive)auto-incrementsub-queriesCASEprepared statementscustom data typesforeach-iteration…Slide32
Under the HoodSlide33
Under the hood Slick APISlick Query TreeSQL
Native SQL
optimizations
Your app
Lifting:
Getting Query trees
from Scala codeSlide34
How lifting worksfor( p <- Persons if p.name === "Chris" ) yield p.name
Column[String]
String (implicitly to Column[String])
Persons.filter(p=>p.name ===
"Chris").map(p=>p.name)
"select
name
from person
where
name =
‘Chris’"
Projection(
Filter(
Table( Person ),
Equals
(
ColumnRef(
Person, „name“ ),
Constant( name )
)
),
ColumnRef(Person,„name“)
)
Scala desugaringSlide35
Upcoming features/ Slick milestonesSlide36
2012Slide37
Alternative Frontend Slick „lifted embedding“ APISlick Query TreeSQL
Native SQL
Slick
„direct embedding“
API
optimizations
Scala AST
Slick macros
Scala compilerSlide38
Alternative FrontendReal Scala (types, methods) using macrosinstead of emulation using liftingno need to think about differences anymoreidentical syntax== instead of ===if-else instead of case-when…identical error messagesCompile-time optimizationsMore compile-time checksSlide39
SUMMER 2013Slide40
Type providers using macrosschema auto-generated from databasecompiler checks queries against real database schemaobject Persons extends Table( „person“ )A macro which connects to the db atcompile time to fetch schemaSlide41
Extensible backend Slick „lifted embedding“ APISlick Query TreeSQL
Native SQL
optimizations
Other backends,
e.g. NoSQL like MondoDB
You can hook in hereSlide42
Beginning of 2014Slide43
Scheduling over multiple backendsfor( p <- Persons; t <- Tasks if p.id … && t.id … ) yield ( p, t )Coming from datasource 1,e.g. Orcale SQL DBComing from datasource 2,e.g. MongoDB or webserviceSlide44
Nested ResultsAs demonstrated inScala Integrated Query / Ferryfor( p <- Persons ) yield ( p, for( t <- Tasks; if … ) yield t ) . list : List[ ( Person, List[Task] ) ]Slide45
Maybe 2013Slide46
Comprehensive ComprehensionsFor-comprehension support forSortingGrouping…We are still thinking about itSlide47
SummarySlick makes database accesseasy, concise, scalable, safe, composableUpcoming features will make Slickeasier, extensible, faster, more powerfulSlide48
Thank you!Questions? .typesafe.com
Jan Christopher Vogt
Stefan Zeiger
Martin Odersky
Eugene BurmakoSlide49
Extra slidesSlide50
Direct Embedding== (no need for ===)Person.filter(p=>p.name == name).map(p=>p)String
String
macro (
Scala
2.10)Macro works on this expression‘sScala AST at compile time
Projection
(
Filter(
Table( Person ),
Equals(
ColumnRef( Person, „name“ ),
Constant( name )
)
),
„*“)
generates
Arbitrary compile time checks
or optimizations possible