$30 off During Our Annual Pro Sale. View Details »

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

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.

Juliano Alves

April 04, 2020
Tweet

More Decks by Juliano Alves

Other Decks in Programming

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