Manipulating Abstract Syntax Trees (ASTs) to generate safe SQL Queries with Quill

Slide 1

Slide 1 text

Juliano Alves @vonjuliano juliano-alves.com Manipulating Abstract Syntax Trees (ASTs) to generate safe SQL Queries with Quill.

Slide 2

Slide 2 text

Who am I? ● Software Engineer, Searcher of perfect modularization, Lover of Functional Languages ● The cool ones Scala, Clojure, Elixir ● The "vintage" ones Java, C#, Python, Ruby @vonjuliano juliano-alves.com

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

https://getquill.io

Slide 8

Slide 8 text

Quill Secret Sauce

Slide 9

Slide 9 text

Quill Secret Sauce

Slide 10

Slide 10 text

Quill Secret Sauce

Slide 11

Slide 11 text

What is an AST?

Slide 12

Slide 12 text

Abstract syntax trees are data structures widely used in compilers to represent the structure of program code ... It often serves as an intermediate representation of the program https://en.wikipedia.org/wiki/Abstract_syntax_tree

Slide 13

Slide 13 text

Quill AST

Slide 14

Slide 14 text

Overview of Quill AST

Slide 15

Slide 15 text

A Query represents database/collections actions.

Slide 16

Slide 16 text

An Operation happens between operands and operators. (A || B && C)

Slide 17

Slide 17 text

An Action is basically an statement.

Slide 18

Slide 18 text

Ordering deﬁnes the order.

Slide 19

Slide 19 text

A Value indicates the different contexts for values.

Slide 20

Slide 20 text

A Lift represents lifted values/structures coming from outside the quotation scope

Slide 21

Slide 21 text

Iterable Operation handles collections ops

Slide 22

Slide 22 text

Option Operation knows EVERYTHING about options

Slide 23

Slide 23 text

Other elements

Slide 24

Slide 24 text

Complete-ish Quill AST

Slide 25

Slide 25 text

Show me the code

Slide 26

Slide 26 text

scala> case class Person(id: Int, name: String, age: Int) scala> case class Address(fk: Option[Int], street: String, number :Int) scala> val q1 = quote { query[Person] } scala> pprint.pprintln(q1.ast) Entity("Person", List())

Slide 27

Slide 27 text

Slide 28

Slide 28 text

scala> val q3 = quote { | query[Person].filter(_.age > 25).map(_.name) | } scala> pprint.pprintln(q3.ast) Map( Filter( Entity("Person", List()), Ident("x1"), BinaryOperation( Property(Ident("x1"), "age"), >, Constant(25)) ), Ident("x2"), Property(Ident("x2"), "name") )

Slide 29

Slide 29 text

scala> val q4 = quote { | query[Person] | .leftJoin(query[Address]) | .on((p, a) => a.fk.exists(_ == p.id)) | .map {case (p, a) => (p.name, a.flatMap(_.fk))} | } scala> pprint.pprintln(q4.ast)

Slide 30

Slide 30 text

Map( Join( LeftJoin, Entity("Person", List()), Entity("Address", List()), Ident("p"), Ident("a"), OptionExists(Property(Ident("a"), "fk"), Ident("x1"), BinaryOperation(Ident("x1"), ==, Property(Ident("p"), "id"))) ), Ident("x01"), Tuple( List( Property(Property(Ident("x01"), "_1"), "name"), OptionTableFlatMap(Property(Ident("x01"), "_2"), Ident("x2"), Property(Ident("x2"), "fk")) ) ) )

Slide 31

Slide 31 text

Tokens and Tokenizers

Slide 32

Slide 32 text

"A Token is a sequence of characters that can be treated as a unit in the grammar of the programming languages" https://www.geeksforgeeks.org/introduction-of-lexical-analysis/ Keywords, identiﬁers or operators are tokens.

Slide 33

Slide 33 text

// Statement.scala sealed trait Token sealed trait TagToken extends Token case class StringToken(string: String) extends Token { override def toString = string } case class ScalarTagToken(lift: ScalarTag) extends TagToken { override def toString = s"lift(${lift.name})" } case class QuotationTagToken(tag: QuotationTag) extends TagToken { override def toString = s"quoted(${tag.uid()})" }

Slide 34

Slide 34 text

// Statement.scala case class ScalarLiftToken(lift: ScalarLift) extends Token { override def toString = s"lift(${lift.name})" } case class Statement(tokens: List[Token]) extends Token { override def toString = tokens.mkString } case class SetContainsToken(a: Token, op: Token, b: Token) extends Token { override def toString = s"${a.toString} ${op.toString} (${b.toString})" }

Slide 35

Slide 35 text

// Ast.scala sealed trait Ast { override def toString = { import ... implicit def externalTokenizer: Tokenizer[External] = Tokenizer[External](_ => stmt"?") implicit val namingStrategy: NamingStrategy = io.getquill.Literal this.token.toString } }

Slide 36

Slide 36 text

// StatementInterpolator.scala trait Tokenizer[T] { def token(v: T): Token } implicit def listTokenizer[T](implicit tokenize: Tokenizer[T]): Tokenizer[List[T]] = Tokenizer[List[T]] { case list => list.mkStmt() } implicit def stringTokenizer: Tokenizer[String] = Tokenizer[String] { case string => StringToken(string) } // many more implementations

Slide 37

Slide 37 text

Let's tokenize!

Slide 38

Slide 38 text

// SqlQuery.scala case class FlattenSqlQuery( from: List[FromContext] = List(), where: Option[Ast] = None, groupBy: Option[Ast] = None, orderBy: List[OrderByCriteria] = Nil, limit: Option[Ast] = None, offset: Option[Ast] = None, select: List[SelectValue], distinct: Boolean = false ) extends SqlQuery

Slide 39

Slide 39 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper protected class FlattenSqlQueryTokenizerHelper(q: FlattenSqlQuery) (implicit astTokenizer: Tokenizer[Ast], strategy: NamingStrategy) { import q._ def distinctTokenizer = (if (distinct) "DISTINCT " else "").token def withDistinct = select match { case Nil => stmt"$distinctTokenizer*" case _ => stmt"$distinctTokenizer${select.token}" } DISTINCT $t

Slide 40

Slide 40 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper def withFrom = from match { case Nil => withDistinct case head :: tail => val t = tail.foldLeft(stmt"${head.token}") { case (a, b: FlatJoinContext) => stmt"$a ${(b: FromContext).token}" case (a, b) => stmt"$a, ${b.token}" } stmt"$withDistinct FROM $t" } DISTINCT $t FROM $t

Slide 41

Slide 41 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper def withWhere = where match { case None => withFrom case Some(where) => stmt"$withFrom WHERE ${where.token}" } DISTINCT $t FROM $t WHERE $t

Slide 42

Slide 42 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper def withGroupBy = groupBy match { case None => withWhere case Some(groupBy) => stmt"$withWhere GROUP BY ${tokenizeGroupBy(groupBy)}" } DISTINCT $t FROM $t WHERE $t GROUP BY $t

Slide 43

Slide 43 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper def withOrderBy = orderBy match { case Nil => withGroupBy case orderBy => stmt"$withGroupBy ${tokenOrderBy(orderBy)}" } protected def tokenOrderBy(criterias: List[OrderByCriteria]) (implicit astTokenizer: Tokenizer[Ast], strategy: NamingStrategy) = stmt"ORDER BY ${criterias.token}" DISTINCT $t FROM $t WHERE $t GROUP BY $t ORDER BY $t

Slide 44

Slide 44 text

// SqlIdiom.scala def withLimitOffset = limitOffsetToken(withOrderBy).token((limit, offset)) protected def limitOffsetToken(query: Statement) (implicit astTokenizer: Tokenizer[Ast], strategy: NamingStrategy) = Tokenizer[(Option[Ast], Option[Ast])] { case (None, None) => query case (Some(limit), None) => stmt"$query LIMIT ${limit.token}" case (Some(limit), Some(offset)) => stmt"$query LIMIT ${limit.token} OFFSET ${offset.token}" case (None, Some(offset)) => stmt"$query OFFSET ${offset.token}" } DISTINCT $t FROM $t WHERE $t GROUP BY $t ORDER BY $t LIMIT $t

Slide 45

Slide 45 text

// SqlIdiom.scala > FlattenSqlQueryTokenizerHelper def apply = stmt"SELECT $withLimitOffset" SELECT DISTINCT $t FROM $t WHERE $t GROUP BY $t ORDER BY $t LIMIT $t

Slide 46

Slide 46 text

// SqlNormalize.scala private val normalize = (identity[Ast] _) .andThen(DemarcateExternalAliases.apply _) .andThen(new FlattenOptionOperation(concatBehavior).apply _) .andThen(new SimplifyNullChecks(equalityBehavior).apply _) .andThen(Normalize.apply _) // Need to do RenameProperties before ExpandJoin which normalizes-out all the tuple indexes // on which RenameProperties relies .andThen(RenameProperties.apply _) .andThen(ExpandDistinct.apply _) .andThen(NestImpureMappedInfix.apply _) .andThen(Normalize.apply _) .andThen(ExpandJoin.apply _) .andThen(ExpandMappedInfix.apply _) .andThen(Normalize.apply _)) def apply(ast: Ast) = normalize(ast)

Slide 47

Slide 47 text

scala> case class Person(id: Int, name: String, age: Int) scala> case class Address(fk: Option[Int], street: String, number :Int) scala> import io.getquill._ scala> val ctx = new SqlMirrorContext(PostgresDialect, Literal) scala> import ctx._

Slide 48

Slide 48 text

scala> val q1 = quote { query[Person] } scala> pprint.pprintln(q1.ast) Entity("Person", List()) scala> pprint.pprintln(ctx.run(q1).string) "SELECT x.id, x.name, x.age FROM Person x"

Slide 49

Slide 49 text

scala> val q2 = quote { query[Person].filter(_.age > 25) } scala> pprint.pprintln(q2.ast) Filter( Entity("Person", List()), Ident("x1"), BinaryOperation( Property(Ident("x1"), "age"), >, Constant(25) ) ) scala> pprint.pprintln(ctx.run(q2).string) "SELECT x1.id, x1.name, x1.age FROM Person x1 WHERE x1.age > 25"

Slide 50

Slide 50 text

Slide 51

Slide 51 text

scala> pprint.pprintln(ctx.run(q3).string) "SELECT x1.name FROM Person x1 WHERE x1.age > 25"

Slide 52

Slide 52 text

scala> val q4 = quote { | query[Person] | .leftJoin(query[Address]) | .on((p, a) => a.fk.exists(_ == p.id)) | .map {case (p, a) => (p.name, a.flatMap(_.fk))} | } scala> pprint.pprintln(q4.ast)

Slide 53

Slide 53 text

Slide 54

Slide 54 text

scala> pprint.pprintln(ctx.run(q4).string) "SELECT p.name, a.fk FROM Person p LEFT JOIN Address a ON a.fk = p.id"

Slide 55

Slide 55 text

Beta Reductions

Slide 56

Slide 56 text

scala> val pq = quote { | query[Person].map(_.id).filter(_ > 1) | } scala> val aq = quote { | query[Address].map(_.fk).filter(_.exists(_ > 1)) | } scala> pprint.pprintln(pq.ast) scala> pprint.pprintln(aq.ast)

Slide 57

Slide 57 text

Filter( Map(Entity("Person", List()), Ident("x1"), Property(Ident("x1"), "id")), Ident("x2"), BinaryOperation(Ident("x2"), >, Constant(1)) ) Filter( Map(Entity("Address", List()), Ident("x1"), Property(Ident("x1"), "fk")), Ident("x2"), OptionExists(Ident("x2"), Ident("x3"), BinaryOperation(Ident("x3"), >, Constant(1))) )

Slide 58

Slide 58 text

scala> pprint.pprintln(ctx.run(pq).string) "SELECT x1.id FROM Person x1 WHERE x1.id > 1" scala> pprint.pprintln(ctx.run(aq).string) "SELECT x1.fk FROM Address x1 WHERE x1.fk > 1"

Slide 59

Slide 59 text

A beta reduction is the process of calculating a result from the application of a function to an expression. https://wiki.haskell.org/Beta_reduction

Slide 60

Slide 60 text

// FlattenOptionOperation.scala override def apply(ast: Ast): Ast = ast match { // ... case OptionExists(ast, alias, body) => if (containsNonFallthroughElement(body)) { val reduction = BetaReduction(body, alias -> ast) apply((IsNotNullCheck(ast) +&&+ reduction): Ast) } else { uncheckedReduction(ast, alias, body) } // ... }

Slide 61

Slide 61 text

// BetaReduction.scala override def apply(o: OptionOperation): OptionOperation = o match { // ... case OptionMap(a, b, c) => OptionMap(apply(a), b, BetaReduction(replacements - b)(c)) case OptionForall(a, b, c) => OptionForall(apply(a), b, BetaReduction(replacements - b)(c)) case OptionExists(a, b, c) => OptionExists(apply(a), b, BetaReduction(replacements - b)(c)) case other => super.apply(other) }

Slide 62

Slide 62 text

Optimizations

Slide 63

Slide 63 text

scala> case class Person(id: Int, name: String, age: Int) scala> val q = quote { | (value: String) => | if (value == "drinks") | query[Person].filter(_.age >= 21) | else | query[Person] | } scala> pprint.pprintln(q.ast)

Slide 64

Slide 64 text

Function( List(Ident("value")), If( BinaryOperation(Ident("value"), ==, Constant("drinks")), Filter( Entity("Person", List()), Ident("x1"), BinaryOperation( Property(Ident("x1"), "age"), >=, Constant(21)) ), Entity("Person", List()) ) )

Slide 65

Slide 65 text

scala> pprint.pprintln(q("drinks").ast) If( BinaryOperation(Constant("drinks"), ==, Constant("drinks")), Filter( Entity("Person", List()), Ident("x1"), BinaryOperation( Property(Ident("x1"), "age"), >=, Constant(21)) ), Entity("Person", List()) )

Slide 66

Slide 66 text

> sbt -Dquill.transform.reducetrivial=true . // TriviallyCheckable.scala def unapply(ast: Ast): Option[Ast] = ast match { // ... case TriviallyCheckable(one) +==+ TriviallyCheckable(two) if (one == two) => Some(Constant(true)) case TriviallyCheckable(one) +==+ TriviallyCheckable(two) if (one != two) => Some(Constant(false)) // ... } https://github.com/getquill/quill/pull/1693

Slide 67

Slide 67 text

> sbt -Dquill.transform.reducetrivial=true . // BetaReduction.scala override def apply(ast: Ast): Ast = ast match { // ... case If(TriviallyTrueCondition(), thenClause, _) if (Messages.reduceTrivials) => apply(thenClause) case If(TriviallyFalseCondition(), _, elseClause) if (Messages.reduceTrivials) => apply(elseClause) // ... } https://github.com/getquill/quill/pull/1693

Slide 68

Slide 68 text

scala> pprint.pprintln(q("drinks").ast) Filter( Entity("Person", List()), Ident("x1"), BinaryOperation( Property(Ident("x1"), "age"), >=, Constant(21)) ) scala> pprint.pprintln(ctx.run(q("drinks")).string) "SELECT x1.id, x1.name, x1.age FROM Person x1 WHERE x1.age >= 21"

Slide 69

Slide 69 text

scala> pprint.pprintln(q("whatever").ast) Entity("Person", List()) scala> pprint.pprintln(ctx.run(q("whatever")).string) "SELECT x1.id, x1.name, x1.age FROM Person x1"

Slide 70

Slide 70 text

Can we optimize lifted values?

Slide 71

Slide 71 text

scala> val v = "other" scala> pprint.pprintln(q(lift(v)).ast) If( BinaryOperation( ScalarValueLift("$line16.$read.$iw.$iw.$iw.$iw.$iw.$iw.v","other", MirrorEncoder()), ==, Constant("drinks") ), Filter( Entity("Person", List()), Ident("x1"), BinaryOperation(Property(Ident("x1"), "age"), >=, Constant(21)) ), Map( Entity("Person", List()), Ident("x2"), Property(Ident("x2"), "name") ) )

Slide 72

Slide 72 text

Conclusion

Slide 73

Slide 73 text

Quill is a "Domain Speciﬁc Compiler" Deﬁne/manipulate your own domain Abstract Syntax Trees Enforce correctness Type checking Transform and build outputs Normalization Improve how code works! Optimization

Slide 74

Slide 74 text

https://getquill.io "This is the project you are looking for"

Slide 75

Slide 75 text

Questions?

Slide 76

Slide 76 text

Juliano Alves @vonjuliano juliano-alves.com Manipulating Abstract Syntax Trees (ASTs) to generate safe SQL Queries with Quill. Thank you!