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

Quill (https://getquill.io/) is a LINQ for Scala, which transforms collection-like code into SQL queries in compile-time. In this session you will learn how Quill uses the compiler to generate safe SQL code, generating and parsing ASTs, making inferences and even performing code optimisations.

Transcript

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

   View Slide

2. 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
   

   View Slide

3. View Slide

4. View Slide

5. View Slide

6. View Slide

7. https://getquill.io
   

   View Slide

8. Quill Secret Sauce
   

   View Slide

9. Quill Secret Sauce
   

   View Slide

10. Quill Secret Sauce
    

    View Slide

11. What is an AST?
    

    View Slide

12. 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
    

    View Slide

13. Quill AST
    

    View Slide

14. Overview of Quill AST
    

    View Slide

15. A Query represents
    database/collections actions.
    

    View Slide

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

    View Slide

17. An Action is basically
    an statement.
    

    View Slide

18. Ordering defines the
    order.
    

    View Slide

19. A Value indicates the
    different contexts for
    values.
    

    View Slide

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

    View Slide

21. Iterable Operation
    handles collections
    ops
    

    View Slide

22. Option Operation knows
    EVERYTHING about options
    

    View Slide

23. Other elements
    

    View Slide

24. Complete-ish Quill AST
    

    View Slide

25. Show me the code
    

    View Slide

26. 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())
    

    View Slide

27. 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)
    )
    )
    

    View Slide

28. 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")
    )
    

    View Slide

29. 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)
    

    View Slide

30. 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"))
    )
    )
    )
    

    View Slide

31. Tokens and Tokenizers
    

    View Slide

32. "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, identifiers or operators are tokens.
    

    View Slide

33. // 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()})"
    }
    

    View Slide

34. // 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})"
    }
    

    View Slide

35. // 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
    }
    }
    

    View Slide

36. // 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
    

    View Slide

37. Let's tokenize!
    

    View Slide

38. // 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
    

    View Slide

39. // 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
    

    View Slide

40. // 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
    

    View Slide

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

    View Slide

42. // 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
    

    View Slide

43. // 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
    

    View Slide

44. // 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
    

    View Slide

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

    View Slide

46. // 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)
    

    View Slide

47. 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._
    

    View Slide

48. 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"
    

    View Slide

49. 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"
    

    View Slide

50. 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")
    )
    

    View Slide

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

    View Slide

52. 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)
    

    View Slide

53. 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"))
    )
    )
    )
    

    View Slide

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

    View Slide

55. Beta Reductions
    

    View Slide

56. 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)
    

    View Slide

57. 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)))
    )
    

    View Slide

58. 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"
    

    View Slide

59. 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
    

    View Slide

60. // 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)
    }
    // ...
    }
    

    View Slide

61. // 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)
    }
    

    View Slide

62. Optimizations
    

    View Slide

63. 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)
    

    View Slide

64. 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())
    )
    )
    

    View Slide

65. 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())
    )
    

    View Slide

66. > 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
    

    View Slide

67. > 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
    

    View Slide

68. 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"
    

    View Slide

69. 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"
    

    View Slide

70. Can we optimize lifted values?
    

    View Slide

71. 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")
    )
    )
    

    View Slide

72. Conclusion
    

    View Slide

73. Quill is a "Domain
    Specific Compiler"
    Define/manipulate your own domain
    Abstract Syntax Trees
    Enforce correctness
    Type checking
    Transform and build outputs
    Normalization
    Improve how code works!
    Optimization
    

    View Slide

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

    View Slide

75. Questions?
    

    View Slide

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

    View Slide