The script-writers dream: How to write great SQL in your own - PowerPoint PPT Presentation

The script-writer’s dream: How to write great SQL in your own language and be sure it will succeed. Ezra Cooper University of Edinburgh August 24, 2009

The Problem

The Problem

The Problem Problems with SQL: ◮ Embedding is usually a pain ◮ Lacks nested data structures ◮ Lacks abstraction

The Problem Problems with SQL: ◮ Embedding is usually a pain ◮ Lacks nested data structures ◮ Lacks abstraction The Solution Language-integrated query

Example: Little League database name Marchmont United teams: Bruntsfield Hustlers Newington Numpties Leith Sluggers name team age Sam Marchmont United 17 Ezra Bruntsfield Hustlers 15 Angus Newington Numpties 18 players: Jeremy Newington Numpties 15 Paidi Leith Sluggers 15 Bob Newington Numpties 14 Lucy Leith Sluggers 17 Hugh Bruntsfield Hustlers 13

Meet the comprehension for ( x ← src ) body

Meet the comprehension for ( x ← src ) body Bind x to successive values from src evaluating body for each one; the result is the union of all the collections so produced.

Meet the comprehension for ( x ← src ) body Bind x to successive values from src evaluating body for each one; the result is the union of all the collections so produced. Some syntactic sugar: for ( x ← src ) where ( cond ) body = for ( x ← src ) ( if ( cond ) then body else [] )

Example: A simple query var players = table “ players ” : [( age : int , name : string )]; fun overAgePlayers () { query { for ( p ← players ) where ( p . age > 16) [ ( name = p . name ) ] } }

Example: A simple query var players = table “ players ” : [( age : int , name : string )]; fun overAgePlayers () { query { for ( p ← players ) where ( p . age > 16) [ ( name = p . name ) ] } } SQL: select p.name as name from players as p where p.age > 16

Example: Detecting non-queryizable operations fun overAgePlayersReversed () { query { for ( p ← players ) where ( p . age > 16) [ ( name = reverse ( p . name )) ] # ERROR! } }

Example: Abstracting the query fun selectedPlayers ( pred ) { query { for ( p ← players ) where ( pred ( p )) [ ( name = p . name ) ] } }

Example: Abstracting the query fun selectedPlayers ( pred ) { query { for ( p ← players ) where ( pred ( p )) [ ( name = p . name ) ] } } SQL ?? select p.name as name from players as p [ · · · ] where

Example: Nested query fun unusablePlayers () { query { var teamRosters : [ ( roster : [ Player ] ) ] = for ( t ← teams ) [ ( roster = for ( p ← players ) where ( p . team == t . name ) [ p ] ) ] ; for ( tr ← teamRosters ) where ( length ( tr . roster ) < 9) tr . roster } }

Example: Nested query, unnested as SQL SQL: select p .* from players as p , teams as t where p .team = t .name and ((select count(*) from players as p2 where p2 .team = t .name) < 9)

Example: Nested query, refactored fun teamRosters () { for ( t ← teams ) [ ( name = t . name roster = for ( p ← players ) where ( p . team == t . team )) [ p ]] ; } fun unusablePlayers () { query { for ( tr ← teamRosters () ) where ( length ( tr . roster ) < 9) tr . roster } }

Example: Nested query, abstracted fun unusablePlayers ( pred ) { query { for ( t ← teamRosters () ) where ( pred ( t . roster )) t . roster } }

Example: Nested query, abstracted fun unusablePlayers ( pred ) { query { for ( t ← teamRosters () ) where ( pred ( t . roster )) t . roster } } Very hard in SQL

Example: Nested query, abstracted, attempted in SQL select p.* from players as p, teams as t where p.team = t.name and ((select count(*) from players as p2 where p2.team = t.name) < 9)

The goal Write queries in an ordinary programming language.

The goal Write queries in an ordinary programming language. ◮ Allow nested data structures.

The goal Write queries in an ordinary programming language. ◮ Allow nested data structures. ◮ Allow abstraction.

The goal Write queries in an ordinary programming language. ◮ Allow nested data structures. ◮ Allow abstraction. ◮ Statically detect unqueryizable expressions.

The goal Write queries in an ordinary programming language. ◮ Allow nested data structures. ◮ Allow abstraction. ◮ Statically detect unqueryizable expressions. “Language-integrated query”

Previous work, language-integrated query ◮ Libkin and Wong (early 90s): Theory; proof for first-order unnesting, pure setting, total. ◮ Kleisli, LINQ, Links (originally): Implemented, impure setting, partial. ◮ Ferry (Grust, et al., 2009): Implemented, pure setting, total, handles nested final results. ◮ Fegaras (1998): Theory, higher-order, but no proof of termination. ◮ Van den Bussche (2001): Theory, nested final results.

Example: Nested query with untranslatable fragment fun teamRosters () { query { for ( t ← teams ) [ ( name = reverse ( t . name ) # ERROR! roster = for ( p ← players ) where ( p . team == t . team )) [ p ]] ; } }

Example: Nested query with untranslatable fragment fun teamRosters () { query { for ( t ← teams ) [ ( name = reverse ( t . name ) # ERROR! roster = for ( p ← players ) where ( p . team == t . team )) [ p ]] ; } } Original version of Links, like Kleisli, translates this to an iteration over a query—one players query for each row of teams .

What I want to show you

What I want to show you How to translate any “pure” expression of relational type to an equivalent single SQL query.

What I want to show you How to translate any “pure” expression of relational type to an equivalent single SQL query. and How to statically detect whether a designated expression is queryizable.

The contribution Add to language-integrated query ◮ Handling higher-order functions. ◮ Separating query-translatable from non-translatable sublanguages of a general-purpose language. ◮ Providing a complete translation from the translatable sublanguage.

The Solution

Plan Compile time Run time Type-and-effect check Normalize Translate

The source language o | ( − − → e (types) T ::= l : T ) | [ T ] | S → T (base types) ::= bool | int | string | · · · o (terms) B , L , M , N ::= for ( x ← L ) M | if B then M else N | table s : T | [ M ] | [] | M ⊎ N ( − − − → | l = M ) | M . l | LM | λ x . N | x | c | length ( M ) | empty ( M ) prim ( − → | M ) | query { M } (atomic effects) ::= noQ E (effect sets) e a set of effects E

Type-and-effect checking

Typing judgment Γ ⊢ M : T ! e Γ variable typing environment M expression T type of M e effects (a set) “In environment Γ, evaluating expression M has effects contained in e and results in type T .”

Typing rules T has the form [ ( − − → Γ ⊢ M : T ! ∅ l : o ) ] ( T-Query ) Γ ⊢ query { M } : T ! ∅

Typing rules e prim : S 1 × · · · × S n → T Γ ⊢ M i : S i ! e i for each 1 ≤ i ≤ n ( T-Prim ) Γ ⊢ prim ( − → M ) : T ! e ∪ � i e i

Typing rules e prim : S 1 × · · · × S n → T Γ ⊢ M i : S i ! e i for each 1 ≤ i ≤ n ( T-Prim ) Γ ⊢ prim ( − → M ) : T ! e ∪ � i e i Side condition: Every primitive must have an SQL equivalent or an effect tag.

Typing rules T has the form [ ( − − → l : o ) ] ( T-Table ) Γ ⊢ ( table s : T ) : T ! ∅

Typing rules Γ , x : S ⊢ N : [ T ] ! e ′ Γ ⊢ M : [ S ] ! e ( T-For ) Γ ⊢ for ( x ← M ) N : [ T ] ! e ∪ e ′

Typing rules Γ ⊢ c : T c ! ∅ ( T-Const ) Γ , x : T ⊢ x : T ! ∅ ( T-Var ) Γ , x : S ⊢ N : T ! e ′ ( T-Abs ) Γ ⊢ λ x . N : S e ′ → T ! ∅ e → T ! e ′ Γ ⊢ M : S ! e ′′ Γ ⊢ L : S ( T-App ) Γ ⊢ LM : T ! e ∪ e ′ ∪ e ′′

Typing rules Γ ⊢ M : [ T ] ( T-Empty ) Γ ⊢ empty ( M ) : bool Γ ⊢ M : [ T ] ( T-Length ) Γ ⊢ length ( M ) : int

Typing rules Γ ⊢ [] : [ T ] ! ∅ ( T-Null ) Γ ⊢ M : T ! e ( T-Singleton ) Γ ⊢ [ M ] : [ T ] ! e Γ ⊢ N : [ T ] ! e ′ Γ ⊢ M : [ T ] ! e ( T-Union ) Γ ⊢ M ⊎ N : [ T ] ! e ∪ e ′

Typing rules Γ ⊢ M i : T i ! e i for each M i , T i in − − − → M , T ( T-Record ) Γ ⊢ ( − l = M ) : ( − − − → − → l : T ) ! � i e i Γ ⊢ M : ( − − → ( l : T ) ∈ ( − − → l : T ) ! e l : T ) ( T-Project ) Γ ⊢ M . l : T ! e Γ ⊢ M : T ! e ′ Γ ⊢ N : T ! e ′′ Γ ⊢ L : bool ! e ( T-If ) Γ ⊢ if L then M 1 else M 2 : T ! e ∪ e ′ ∪ e ′′ e e ⊂ e ′ Γ ⊢ M : S → T ( T-Subsump ) Γ ⊢ M : S e ′ → T

What the typing gives us Any well-typed expression query { M } : has relation type and never executes any primitive that lacks an SQL equivalent.

What the typing gives us Any well-typed expression query { M } : has relation type and never executes any primitive that lacks an SQL equivalent. So, at runtime M can be translated to an SQL query.

Run Time

Translation

Normal forms (normal forms) V , U ::= V ⊎ U | [] | F (comprehension NFs) ::= for ( x ← table s : T ) F | Z F (comprehension bodies) Z ::= if B then Z else [] | [ ( − − − → l = B ) ] | table s : T if B then B ′ else B ′′ | (basic expressions) B ::= empty ( V ) | length ( V ) | prim ( − → B ) | x . l | c