Scala Parser Combinators - SQL Parser Example

Scala Parser Combinators: https://github.com/scala/scala-parser-combinators

Scala Parser Combinators is basically a parsing framework for extracting data when there is a pattern in the given input. This framework provides a more statically typed, functional way of extracting instead of using regex expression which can get hard to read.

In this post, lets build a SQL parser where given a valid sql statement we can identify the "table" name, "column" names and other sql properties. Following are some fundamental functions, operations that Scala combinator provides which would help in parsing:

• " | ": says “succeed if either the left or right operand parse successfully”
• " ~ ": says “succeed if the left operand parses successfully, and then the right parses successfully on the remaining input”
• " ~> ": says “succeed if the left operand parses successfully followed by the right, but do not include the left content in the result”
• " <~ ": is the reverse, “succeed if the left operand is parsed successfully followed by the right, but do not include the right content in the result”
• " ^^ ": says “if the left operand parses successfully, transform the result using the function on the right”
• " ^^^ ": says “if the left operand parses successfully, ignore the result and use the value from the right”
• " rep(fn) ": says "parse the given input using the parser function fn"
• " repsep(ident, char) ": says "parse the given input and split the input using the given 'char'"

Lets start out with a set of SQL statements and it's associated Parser code

select * from users

?

1 2 3 4 5 6 7 8

import scala.util.parsing.combinator._ import scala.util.parsing.combinator.syntactical._   case class Select(val fields: String*) case class From(val table: String)   def selectAll: Parser[Select] = "select" ~ "*" ^^^ (Select("*")) //output: Select("*") def from: Parser[From] = "from" ~> ident ^^ (From(_)) //output: From("users")

select name,age from users

?

1 2 3 4

def select: Parser[Select] = "select" ~ repsep(ident, ",") ^^ {     case "select" ~ f => Select(f: _*)   } //output: Select(List[String]("name", "age"))

select count(name) from users

?

1 2 3 4 5 6

case class Count(val field: String)     def count: Parser[Count] = "select" ~ "count" ~> "(" ~> ident <~ ")" ^^ {      case exp => Count(exp)   } //output: Count("name")

select * from users order by age desc

?

1 2 3 4 5 6 7 8 9 10 11

abstract class Direction   case class Asc(field: String*) extends Direction   case class Desc(field: String*) extends Direction     def order: Parser[Direction] = {     "order" ~> "by" ~> ident ~ ("asc" | "desc") ^^ {       case f ~ "asc" => Asc(f)       case f ~ "desc" => Desc(f)     }   } //output: Desc("age")

select * from users order by name, age desc

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

abstract class Direction   case class Asc(field: String*) extends Direction   case class Desc(field: String*) extends Direction     def order: Parser[Direction] = {     ("order" ~> "by" ~> ident ~ ("asc" | "desc") ^^ {       case f ~ "asc" => Asc(f)       case f ~ "desc" => Desc(f)     }) | ("order" ~> "by" ~> repsep(ident, ",") ~ ("asc" | "desc") ^^ {       case f ~ "asc" => Asc(f: _*)       case f ~ "desc" => Desc(f: _*)     })   } //output: Desc("name", "age")

select age from users where age>30

?

1 2 3 4 5 6 7 8

def where: Parser[Where] = "where" ~> rep(predicate) ^^ (Where(_: _*))       def predicate = (      ident ~ "=" ~ wholeNumber ^^ { case f ~ "=" ~ i => NumberEquals(f, i.toInt) }      | ident ~ "<" ~ wholeNumber ^^ { case f ~ "<" ~ i => LessThan(f, i.toInt) }      | ident ~ ">" ~ wholeNumber ^^ { case f ~ ">" ~ i => GreaterThan(f, i.toInt) })   //output: GreaterThan("age", 30)