Relationell auch ohne SQL Relationale Datenbanken mit Scala

Relationell auch ohne SQL Relationale Datenbanken mit Scala. Query nutzen Stefan Zeiger Commerzbank AG

Relationale Datenbanken • Größere Anwendungen brauchen oft Datenbanken • Relationales Modell verhindert Silobildung Herbstcampus 2011 – Relationell auch ohne SQL 2

Wozu? Wir haben doch JDBC Herbstcampus 2011 – Relationell auch ohne SQL 3

Wozu? Wir haben doch JDBC def users. Matching(pattern: String)(conn: Connection) = { val st = conn. prepare. Statement("select id, name from users where name like ? ") try { st. set. String(1, pattern) val rs = st. execute. Query() try { val b = new List. Buffer[(Int, String)] while(rs. next) b. append((rs. get. Int(1), rs. get. String(2))) b. to. List } finally rs. close() } finally st. close() } Class. for. Name("org. h 2. Driver") val conn = Driver. Manager. get. Connection("jdbc: h 2: test 1") try { println(users. Matching("%zeiger%")(conn)) } finally conn. close() Herbstcampus 2011 – Relationell auch ohne SQL 4

JDBC • Gute Grundlage für Frameworks • Zu niedrige Abstraktionsebene für Anwendungen Herbstcampus 2011 – Relationell auch ohne SQL 5

Scala. Query: Simple Queries val users. Matching = query[String, (Int, String)] ("select id, name from users where name like ? ") Database. for. URL("jdbc: h 2: test 1", driver = "org. h 2. Driver") with. Session { println(users. Matching("%zeiger%"). list) } Herbstcampus 2011 – Relationell auch ohne SQL 6

• Object/Relational Mapping Tools • Hibernate, Toplink, JPA 95% Wozu? Wir haben doch ORMs 80% des Problems • Lösen 50% Herbstcampus 2011 – Relationell auch ohne SQL 7

Relationales Modell Relational Model: COF_NAME • Relation Colombian • Attribute • Tuple French_Roast_Decaf SUP_ID 101 7. 99 49 8. 99 Espresso 150 9. 99 Colombian_Decaf 101 8. 99 49 9. 99 French_Roast TABLE COFFEES • Relation Value • Relation Variable Beispiele aus: http: //download. oracle. com/javase/tutorial/jdbc/basics/index. html Herbstcampus 2011 – Relationell auch ohne SQL PRICE 8

Impedance Mismatch: Konzepte Object-Oriented: Relational: • Identity • State : Transactional • Behaviour • Encapsulation Herbstcampus 2011 – Relationell auch ohne SQL 9

Impedance Mismatch: Retrieval Strategies Colombian French_Roast Espresso Colombian_Decaf French_Roast_Decaf select COF_NAME from COFFEES Herbstcampus 2011 – Relationell auch ohne SQL Espresso Price: Supplier: 9. 99 The High Ground select c. *, s. SUP_NAME from COFFEES c, SUPPLIERS s where c. COF_NAME = ? and c. SUP_ID = s. SUP_ID 10

Impedance Mismatch: Retrieval Strategies Herbstcampus 2011 – Relationell auch ohne SQL 11

Impedance Mismatch: Retrieval Strategies def get. All. Coffees(): Seq[Coffee] = … def print. Links(s: Seq[Coffee]) { for(c <- s) println(c. name + " " + c. price) } } def print. Details(c: Coffee) { println(c. name) println("Price: " + c. price) println("Supplier: " + c. supplier. name) Herbstcampus 2011 – Relationell auch ohne SQL 12

O/R-Mapper • Falsche Abstraktionsebene • Nicht transparent Herbstcampus 2011 – Relationell auch ohne SQL 13

“Object/Relational Mapping is The Vietnam of Computer Science” (Ted Neward) http: //blogs. tedneward. com/2006/06/26/The+Vietnam+Of+Computer+Science. aspx Herbstcampus 2011 – Relationell auch ohne SQL 14

A Better Match: Functional Programming • Relation case class Coffee(name: String, supplier. Id: Int, price: Double) • Attribute • Tuple • Relation Value • Relation Variable Herbstcampus 2011 – Relationell auch ohne SQL val coffees = Set( Coffee("Colombian", 101, 7. 99), Coffee("French_Roast", 49, 8. 99), Coffee("Espresso", 150, 9. 99) ) - mutable state in the DB 15

• Session-Management org. scalaquery. session • Typsichere Queries in Scala org. scalaquery. ql + Insert, Update, Delete, DDL Scala. Query • Direkte SQL-Statements org. scalaquery. simple • Gemeinsames API zur Ausführung beider Arten org. scalaquery von Statements Herbstcampus 2011 – Relationell auch ohne SQL 16

Session Management: Database • JDBC kennt zwei Connection-Management. Modelle: Driver. Manager und Data. Source • Wie mit Driver. Manager Connections zu einer URL öffnen: Database. for. URL(…) • Ein Data. Source-Objekt verwenden: Database. for. Data. Source(…) • Ein Data. Source-Objekt über einen JNDINamen holen: Database. for. Name(…) Herbstcampus 2011 – Relationell auch ohne SQL 17

Session Management: Session • Alle Zugriffe auf die Datenbank erfolgen über ein Session-Objekt • Wrapper für java. sql. Connection • Oft als implizites Objekt verwendet: Database. thread. Local. Session • Kein Caching von Connections und Prepared. Statements Herbstcampus 2011 – Relationell auch ohne SQL 18

Session Management import org. scalaquery. session. _ import org. scalaquery. session. Database. thread. Local. Session val db = Database. for. URL("jdbc: h 2: mem: test 1", driver = "org. h 2. Driver") with. Transaction db with. Session { s: {Session => s with. Transaction { do. Something. With. Session(s) } } Herbstcampus 2011 – Relationell auch ohne SQL 19

Typsichere Queries: Scala-Collections case class Coffee( name: String, sup. ID: Int, price: Double ) val coffees = List( Coffee("Colombian", 101, 7. 99), Coffee("Colombian_Decaf", 101, 8. 99), Coffee("French_Roast_Decaf", 49, 9. 99) ) val l = for { c <- coffees if c. sup. ID == 101 } yield (c. name, c. price) Scala Collections l. foreach { case (n, p) => println(n + ": " + p) } Herbstcampus 2011 – Relationell auch ohne SQL 20

Typsichere Queries: Query Language val Coffees = new Table[(String, Int, Double)]("COFFEES") { def name = column[String]("COF_NAME", O. Primary. Key) def sup. ID = column[Int]("SUP_ID") def price = column[Double]("PRICE") def * = name ~ sup. ID ~ price } Coffees. insert. All( ("Colombian", 101, 7. 99), ("Colombian_Decaf", 101, 8. 99), ("French_Roast_Decaf", 49, 9. 99) ) val q = for { c <- Coffees if c. sup. ID === 101 } yield c. name ~ c. price Scala. Query q. foreach { case (n, p) => println(n + ": " + p) } Herbstcampus 2011 – Relationell auch ohne SQL 21

Tabellendefinitionen val Suppliers = new Table[(Int, String, String)]("SUPPLIERS") { def def def id name street city state zip = = = column[Int ]("SUP_ID", O. Primary. Key) column[String]("SUP_NAME") column[String]("STREET") column[String]("CITY") column[String]("STATE") column[String]("ZIP") def * = id ~ name ~ street ~ city ~ state ~ zip } def name. Constraint = index("SUP_NAME_IDX", name, true) Herbstcampus 2011 – Relationell auch ohne SQL 22

Tabellendefinitionen val Coffees = new Table[(String, Int, Double, Int)]("COFFEES") { def def def name sup. ID price sales total = = = column[String]("COF_NAME") column[Int ]("SUP_ID") column[Double]("PRICE") column[Int ]("SALES") column[Int ]("TOTAL") def * = name ~ sup. ID ~ price ~ sales ~ total def supplier = foreign. Key("SUP_FK", sup. ID, Suppliers)(_. id) } def pk = primary. Key("COF_NAME_PK", name) Herbstcampus 2011 – Relationell auch ohne SQL 23

Tabellen Erzeugen val db = Database. for. URL("jdbc: h 2: mem: test 1", driver = "org. h 2. Driver") val Suppliers = … val Coffees = … db with. Session { (Suppliers. ddl ++ Coffees. ddl). create } Herbstcampus 2011 – Relationell auch ohne SQL 24

Query Language Imports import org. scalaquery. ql. _ import org. scalaquery. ql. Type. Mapper. _ import • • • org. scalaquery. ql. extended. H 2 Driver. Implicit. _ org. scalaquery. ql. extended. {Extended. Table => Table} basic. Basic. Driver extended. Access. Driver extended. Derby. Driver extended. H 2 Driver extended. Hsqldb. Driver def column[C : Type. Mapper](n: String, extended. My. SQLDriver options: Column. Option[C, Profile. Type]*) = … extended. Postgres. Driver extended. SQLite. Driver extended. SQLServer. Driver Herbstcampus 2011 – Relationell auch ohne SQL 25

Ein DAO-Pattern class DAO(driver: Extended. Profile, db: Database) { import driver. Implicit. _ val Props = new Table[(String, String)]("properties") { def key = column[String]("key", O. Primary. Key) def value = column[String]("value") def * = key ~ value } def insert(k: String, v: String) = db with. Session Props. insert(k, v) } def get(k: String) = db with. Session ( for(p <- Props if p. key === k) yield p. value ). first. Option Herbstcampus 2011 – Relationell auch ohne SQL 26

Inner Joins & Abstraktionen for { Coffees if c. price < 9. 0 c <- coffees Suppliers if s. id == ===c. sup. ID s <- suppliers c. name ~ s. name) s. name } yield (c. name, Scala. Query Collections for { c <- Coffees. cheaper. Than(9. 0) Coffees if c. price < 9. 0 s <- c. supplier } yield c. name ~ s. name val Coffees = new Table … { def supplier = Suppliers. where(_. id foreign. Key("SUP_FK", === sup. ID, sup. ID) Suppliers)(_. id) } def cheaper. Than(d: Double) = this. where(_. price < d) } Herbstcampus 2011 – Relationell auch ohne SQL 27

Datentypen • Basistypen • • • Byte, Int, Long String Boolean Date, Timestamp Float, Double Blob, Clob, Array[Byte] • Option[T] für alle Basistypen T 0 "" false 1970 -1 -1 00: 00 0. 0 null, [] None • Datenbank-NULL wird auf Default-Wert gemappt Herbstcampus 2011 – Relationell auch ohne SQL 28

NULL • Three-Valued Logic (3 VL) in SQL a b → NULL wenn a = NULL oder b = NULL • Gilt auch für „=“ a = NULL = a a IS NULL → → → NULL TRUE oder FALSE Herbstcampus 2011 – Relationell auch ohne SQL 29

NULL • In Scala. Query über Option. Mapper abgebildet • Für Basistypen A, B, C: Column[ A ] Column[Option[A]] Column[ B ] → Column[ C ] B ] → Column[Option[C]] A ] Column[Option[B]] → Column[Option[C]] Column[Option[A]] Column[Option[B]] → Column[Option[C]] Herbstcampus 2011 – Relationell auch ohne SQL 30

Eigene Datentypen Verwenden object Values extends Enumeration { val a, b, c = Value } implicit values. Type. Mapper = Mapped. Type. Mapper. base[Values. Value, Int](_. id, Values(_)) val My. Table = new Table[Values. Value]("MYTABLE") { def a = column[Values. Value]("A") def * = a } My. Table. ddl. create My. Table. insert. All(Values. a, Values. c) val q = My. Table. map(t => t. a ~ t. a. as. Column. Of[Int]) q. foreach(println) (a, 0) (c, 2) Herbstcampus 2011 – Relationell auch ohne SQL 31

Aggregieren und Sortieren val q = for { c <- Coffees s <- c. supplier _ <- Query group. By s. id _ <- Query order. By c. name. count } yield s. id ~ s. name. min. get ~ c. name. count • Aggregierungsmethoden: . min, . max, . avg, . sum, . count Herbstcampus 2011 – Relationell auch ohne SQL 32

Operatoren Für Columns • Allgemein: . in(Query), . not. In(Query), . count. Distinct, . is. Null, . is. Not. Null, . as. Column. Of. Type • Vergleiche: === (. is), =!= (. is. Not), <, <=, >, >=, . in. Set. Bind, . between, . if. Null • Numerisch: +, -, *, /, %, . abs, . ceil, . floor, . sign, . to. Degrees, . to. Radians • Boolean: &&, ||, . unary_! • String: . length, . like, ++, . starts. With, . ends. With, . to. Upper. Case, . to. Lower. Case, . ltrim, . rtrim, . trim Herbstcampus 2011 – Relationell auch ohne SQL 33

Invokers • Alle Datenbankzugriffe erfolgen über Invoker • Eine implizite Konvertierung von Query nach Invoker erlaubt das direkte Ausführen von Queries Herbstcampus 2011 – Relationell auch ohne SQL 34

Invoker-Methoden: Strict • . to[C]() – erzeugt eine Collection C aller Ergebnisse z. B. my. Query. to[List]() my. Query. to[Array]() • . list – Shortcut für. to[List]() • . to. Map – erzeugt eine Map[K, V] für einen Query[(K, V)] • . first, . first. Option, . first. Flatten – geben das erste Ergebnis zurück Herbstcampus 2011 – Relationell auch ohne SQL 35

Invoker-Methoden: Lazy / Incremental • . elements – erzeugt Closeable. Iterator, der alle Ergebnisse bei Bedarf liest • . elements. To – nur bis zur angegebenen Maximalanzahl • . foreach – führt die angegebene Funktion für jedes Ergebnis aus for(r <- my. Query). . . • Optional mit Maximalanzahl • . fold. Left – berechnet einen Wert aus allen Ergebnissen • . execute – führt das Statement aus Herbstcampus 2011 – Relationell auch ohne SQL 36

Debugging val q = for { c <- Coffees if c. sup. ID === 101 } yield c. name ~ c. price q. dump("q: ") SELECT "t 1". "COF_NAME", "t 1". "PRICE" FROM "COFFEES" "t 1“ WHERE ("t 1". "SUP_ID"=101) q: Query select: Projection 2 0: Named. Column COF_NAME table: <t 1> Abstract. Table. Alias 0: <t 2> Table COFFEES 1: Named. Column PRICE table: <t 1>. . . where: Is(Named. Column SUP_ID, Const. Column[Int] 101) 0: Named. Column SUP_ID table: <t 1>. . . 1: Const. Column[Int] 101 println(q. select. Statement) Herbstcampus 2011 – Relationell auch ohne SQL 37

sup. ID Colombian 101 Espresso 150 Colombian_Decaf 42 id name 101 Acme, Inc. 49 Superior Coffee 150 The High Ground for ( Join(c, s) <- Coffees inner. Join Suppliers on (_. sup. ID === _. id) ) yield c. name ~ s. name (Colombian, Acme, Inc. ) (Espresso, The High Ground) Herbstcampus 2011 – Relationell auch ohne SQL 38 Suppliers name Coffees Explizite Inner Joins

sup. ID Colombian 101 Espresso 150 Colombian_Decaf 42 id name 101 Acme, Inc. 49 Superior Coffee 150 The High Ground for ( Join(c, s) <- Coffees left. Join Suppliers on (_. sup. ID === _. id) ) yield c. name. ? c. name ~ ~ s. name. ? (Colombian, Acme, Inc. ) (Some(Colombian), Some(Acme, Inc. )) (Espresso, The High Ground) (Some(Espresso), Some(The High Ground)) (Colombian_Decaf, ) (Some(Colombian_Decaf), None) Herbstcampus 2011 – Relationell auch ohne SQL 39 Suppliers name Coffees Left Outer Joins

sup. ID Colombian 101 Espresso 150 Colombian_Decaf 42 id name 101 Acme, Inc. 49 Superior Coffee 150 The High Ground for ( Join(c, s) <- Coffees right. Join Suppliers on (_. sup. ID === _. id) ) yield c. name. ? ~ s. name. ? (Some(Colombian), Some(Acme, Inc. )) (None, Some(Superior Coffee)) (Some(Espresso), Some(The High Ground)) Herbstcampus 2011 – Relationell auch ohne SQL 40 Suppliers name Coffees Right Outer Joins

sup. ID Colombian 101 Espresso 150 Colombian_Decaf 42 id name 101 Acme, Inc. 49 Superior Coffee 150 The High Ground for ( Join(c, s) <- Coffees outer. Join Suppliers on (_. sup. ID === _. id) ) yield c. name. ? ~ s. name. ? (Some(Colombian), Some(Acme, Inc. )) (None, Some(Superior Coffee)) (Some(Espresso), Some(The High Ground)) (Some(Colombian_Decaf), None) Herbstcampus 2011 – Relationell auch ohne SQL 41 Suppliers name Coffees Full Outer Joins

Case for { c <- Coffees } yield (Case when c. price < 8. 0 then "cheap" when c. price < 9. 0 then "medium" otherwise "expensive") ~ c. name • If-then-else für Queries • Rückgabetyp wird automatisch zu Option, wenn otherwise fehlt Herbstcampus 2011 – Relationell auch ohne SQL 42

Sub-Queries for { c <- Coffees s <- c. supplier _ <-lowest. Price. For. Supplier val Query group. By s. id order. By = (for s. id { } yield c 2 <s. name. min. get Coffees ~ c. price. min. get s 2 <- c 2. supplier if s 2. id === s. id } yield c 2. price. min). as. Column _ <- Query if c. price === lowest. Price. For. Supplier _ <- Query order. By s. id } yield s. name ~ c. price • Auch in yield verwendbar • Direkt (ohne. as. Column) mit. in und. not. In • . exists, . count Herbstcampus 2011 – Relationell auch ohne SQL 43

Unions Scala Collections val l 1 = coffees. filter(_. sup. ID == 101) val l 2 = coffees. filter(_. sup. ID == 150) val l 3 = l 1 ++ l 2 Scala. Query val q 1 = Coffees. filter(_. sup. ID === 101) val q 2 = Coffees. filter(_. sup. ID === 150) val q 3 = q 1 union. All q 2 Herbstcampus 2011 – Relationell auch ohne SQL 44

Paginierung val l = for { c <- coffees if … } yield … val l 2 = l. drop(20). take(10) val q = for { c <- Coffees if … _ <- Query order. By c. name } yield … val q 2 = q. drop(20). take(10) Herbstcampus 2011 – Relationell auch ohne SQL 45 Scala Collections Scala. Query

Bind-Variablen def coffees. For. Supplier(sup. ID: Int) = for { c <- Coffees if c. sup. ID === sup. ID. bind } yield c. name coffees. For. Supplier(42). list Query select: Named. Column COF_NAME SELECT "t 1". "COF_NAME" FROM "COFFEES" "t 1" table: <t 1> Abstract. Table. Alias =? ) WHERE ("t 1". "SUP_ID"=42) 0: <t 2> Table COFFEES where: Is(Named. Column SUP_ID, Const. Column[Int] 42) Bind 0: Named. Column SUP_ID table: <t 1>. . . Bind 1: Const. Column[Int] 42 Herbstcampus 2011 – Relationell auch ohne SQL 46

Query-Templates val coffees. For. Supplier = for { sup. ID <- Parameters[Int] c <- Coffees if c. sup. ID === sup. ID } yield c. name coffees. For. Supplier(42). list Query select: Named. Column COF_NAME SELECT "t 1". "COF_NAME" FROM "COFFEES" "t 1" table: <t 1> Abstract. Table. Alias WHERE ("t 1". "SUP_ID"=? ) 0: <t 2> Table COFFEES where: Is(Named. Column SUP_ID, Parameter. Column[Int]) 0: Named. Column SUP_ID table: <t 1>. . . 1: Parameter. Column[Int] Herbstcampus 2011 – Relationell auch ohne SQL 47

Mapped Entities case class Coffee(name: String, sup. ID: Int, price: Double) Int, Double) ]("COFFEES") { val Coffees = new Table[(String, Coffee def name = column[String]("COF_NAME", O. Primary. Key) def sup. ID = column[Int]("SUP_ID") def price = column[Double]("PRICE") def * = name ~ sup. ID ~ price <> (Coffee, Coffee. unapply _) } Coffees. insert. All( Coffee ("Colombian", 101, 7. 99), Coffee ("French_Roast", 49, 8. 99) ) val q = for(c <- Coffees if c. sup. ID === 101) yield c q. foreach(println) Coffee (Colombian, 101, 7. 99) Herbstcampus 2011 – Relationell auch ohne SQL 48

Insert, Delete, Update class Coffees(n: String) extends Table[(String, Int, Double)](n) { def name = column[String]("COF_NAME") def sup. ID = column[Int]("SUP_ID") def price = column[Double]("PRICE") def * = name ~ sup. ID ~ price } val Coffees 1 = new Coffees("COFFEES_1") val Coffees 2 = new Coffees("COFFEES_2") (Coffees 1. ddl ++ Coffees 2. ddl). create INSERT INTO "COFFEES 1" ("COF_NAME", "SUP_ID", "PRICE") VALUES (? , ? ) Coffees 1. insert. All( ("Colombian", 101, 7. 99), ("French_Roast", 49, 8. 99), ("Espresso", 150, 9. 99) ) println(Coffees 1. insert. Statement) Herbstcampus 2011 – Relationell auch ohne SQL 49

Insert, Delete, Update val q = Coffees 1. where(_. sup. ID === 101) Coffees 2. insert(q) println(Coffees 2. insert. Statement. For(q)) INSERT INTO "COFFEES 2" ("COF_NAME", "SUP_ID", "PRICE") SELECT "t 1". "COF_NAME", "t 1". "SUP_ID", "t 1". "PRICE" FROM "COFFEES 1" "t 1" WHERE ("t 1". "SUP_ID"=101) q. delete println(q. delete. Statement) DELETE FROM "COFFEES 1" WHERE ("COFFEES 1". "SUP_ID"=101) Herbstcampus 2011 – Relationell auch ohne SQL 50

Insert, Delete, Update val q 2 = q. map(_. sup. ID) q 2. update(49) println(q 2. update. Statement) UPDATE "COFFEES 1" SET "SUP_ID"=? WHERE ("COFFEES 1". "SUP_ID"=101) Herbstcampus 2011 – Relationell auch ohne SQL 51

Static Queries import org. scalaquery. simple. _ import org. scalaquery. simple. Static. Query. _ def all. Coffees = query. NA[String]( "select cof_name from coffees"). list def supplier. Name. For. Coffee(name: String) = query[String, String](""" select s. sup_name from suppliers s, coffees c where c. cof_name = ? and c. sup_id = s. sup_id """). first. Option(name) def coffees. In. Price. Range(min: Double, max: Double) = query[(Double, Double), (String, Int, Double)](""" select cof_name, sup_id, price from coffees where price >= ? and price <= ? """). list(min, max) Herbstcampus 2011 – Relationell auch ohne SQL 52

Static Queries import org. scalaquery. simple. _ import org. scalaquery. simple. Static. Query. _ case class Coffee( name: String, sup. ID: Int, price: Double) implicit val get. Coffee. Result = Get. Result(r => Coffee(r<<, r<<)) [P : Set. Parameter, R : Get. Result] def coffees. In. Price. Range(min: Double, max: Double) = Coffee query[(Double, Double), (String, Int, Double)](""" select cof_name, sup_id, price from coffees where price >= ? and price <= ? """). list(min, max) Herbstcampus 2011 – Relationell auch ohne SQL 53

Weitere Features • Mutating Queries Mutating. Invoker. mutate • JDBC-Metadaten org. scalaquery. meta • Iteratees org. scalaquery. iter • Sequences • Dynamic Queries Herbstcampus 2011 – Relationell auch ohne SQL org. scalaquery. simple 54

Getting Started • http: //scalaquery. org • https: //github. com/szeiger/scalaquery-examples • https: //github. com/szeiger/scala-query/ tree/master/src/test/scala/org/scalaquery/test Herbstcampus 2011 – Relationell auch ohne SQL 55

Ausblick • • Slick – A common framework for connecting with databases and distributed collections by Christopher Vogt http: //code. google. com/p/scala-integrated-query/ Herbstcampus 2011 – Relationell auch ohne SQL 56

Ausblick Scala. Query 0. 9 Scala. Query 0. 10 for { c Coffees if c. price === 8. 99 || c. price === 9. 99 s <- Suppliers s <-cs c. supplier order. By s. id val = Coffees. filter(c => c. sup. ID == ===s. id&& && } yield ((s. id, s. id (c. price ~ s. name), ~ c) ~ … 9. 99)) === ==c. name 8. 99|| ||c. price~ == === 9. 99)) } yield ((s. id, s. name), cs) Virtualized Scala Herbstcampus 2011 – Relationell auch ohne SQL SIQ 57

Vielen Dank! Stefan Zeiger Commerzbank AG http: //szeiger. de Twitter: @Stefan. Zeiger