Internal DSLs with Scala

I’ve been playing around with Scala again lately. Writing a (internal) DSL or a fluent api was still on todo-list.

Instead of writing some arbitrary language for a made-up domain I decided to pick a language and a domain I know: SQL. Or, a rather small subset.

The first step was creating a model of the language (no, I didn’t start with this diagram):

As you can see a query object consists of:

• Operation (i.e. select, update, delete)
• From
• Where, containing various predicates and ways to combine predicates
• Optional ordering

In Scala my implementation of the model looks like this:

[code]
case class Query(val operation:Operation, val from: From, val where: Where, val order: Option[Direction] = None) {
def order(dir: Direction) = this.copy(order = Option(dir))
}

abstract class Operation {
def from(table: String) = From(this, table)
}
case class Select(val fields:String*) extends Operation

case class From(val operation:Operation, val table: String) {
def where(clauses: Clause*): Query = Query(operation, this, Where(clauses:_*))
}

case class Where(val clauses: Clause*)

abstract class Clause {
def and(otherField: Clause): Clause = And(this, otherField)
def or(otherField: Clause): Clause = Or(this, otherField)
}

case class StringEquals(val f: String, val value: String) extends Clause
case class NumberEquals(val f: String, val value: Number) extends Clause
case class BooleanEquals(val f: String, val value: Boolean) extends Clause
case class In(val field: String, val values: String*) extends Clause
case class And(val clauses: Clause*) extends Clause
case class Or(val clauses: Clause*) extends Clause

abstract class Direction
case class Asc(field: String) extends Direction
case class Desc(field: String) extends Direction
[/code]

As you can see the code is a straightforward implementation of the model, only using immutable values. I added some utility methods which will be used to ‘chain’ objects:

• Query#order – Clones the query object and overrides the order with the specified order
• Operation#from – creates a from clause from the operation object
• Clause#and / Clause#or – combines two clauses

Next to the model I created a QueryBuilder object which contains some implicit conversions and utility methods:

[code]
object QueryBuilder {
implicit def tuple2field(t: (String, String)): StringEquals = StringEquals(t._1, t._2)
implicit def tuple2field(t: (String, Int)): NumberEquals = NumberEquals(t._1, t._2)
implicit def tuple2field(t: (String, Boolean)): BooleanEquals = BooleanEquals(t._1, t._2)

/** entrypoint for starting a select query */
def select(fields:String*) = Select(fields:_*)
def in(field: String, values: String*) = In(field, values: _*)
}
[/code]

The implicit conversions allow tuples to be converted to typed case classes. With the above we can write Scala code which resembles SQL. I Wrote some tests (using ScalaTest) which demonstrate how it works. Example inputs for my tests include:

[code]
val q = select ("*") from ("user") where (("name","peter") and (("active", true) or ("role", "admin")))
val q = select ("*") from ("user") where (("name","p'eter"))
val q = select ("*") from ("user") where (("id", 100))
val q = select ("*") from ("user") where (in("name","pe'ter","petrus"))
val q = select ("*") from ("user") where (("name","peter")) order Desc("name")
[/code]

To generate a SQL String from a Query object I wrote a fairly basic generator and an implicit conversion to convert queries to SQL:

[code]
case class SQL(val sql:String)

object AnsiSqlRenderer {
implicit def query2sql(q:Query):SQL = SQL(sql(q))

def sql(q: Query): String = {
List(
expandOperation(q),
expandFrom(q),
expandWhere(q),
expandOrder(q)
).mkString(" ").trim
}

def expandOperation(q:Query):String = q.operation match {
case Select(fields) => "select %s".format(fields.mkString(","))
case _ => throw new IllegalArgumentException("Operation %s not implemented".format(q.operation))
}

def expandFrom(q: Query) = "from %s".format(q.from.table)
def expandWhere(q: Query) = "where %s".format(q.where.clauses.map(expandClause(_)).mkString(" "))

def expandClause(clause: Clause): String = clause match {
case StringEquals(field, value) => "%s = %s".format(field, quote(value))
case BooleanEquals(field, value) => "%s = %s".format(field, value)
case NumberEquals(field, value) => "%s = %s".format(field, value)
case in:In => "%s in (%s)".format(in.field, in.values.map(quote(_)).mkString(","))
case and:And => and.clauses.map(expandClause(_)).mkString("(", " and ", ")")
case or:Or => or.clauses.map(expandClause(_)).mkString("(", " or ", ")")
case _ => throw new IllegalArgumentException("Clause %s not implemented".format(clause))
}

def expandOrder(q: Query) = q.order match {
case Some(direction) => direction match {
case Asc(field) => "order by %s asc".format(field)
case Desc(field) => "order by %s desc".format(field)
}
case None => ""
}

def quote(value: String) = "'%s'".format(escape(value))
def escape(value: String) = value.replaceAll("'", "''")
}
[/code]

Most of the beef is in (recursively) expanding the where clause into a String. When using the implicit conversion you can now do the following:

[code]
scala> val q = select ("*") from ("user") where (("name","peter") and (("active", true) or ("role", "admin")))
scala> q.sql
res0: java.lang.String = select * from user where (name = 'peter' and (active = true or role = 'admin'))
[/code]

Apart from the parentheses (probably I’ll figure out how to get rid of some more of them one day) the two look very similar. But the first one creates a typesafe object graph which can be rendered/validated/manipulated in various ways!

The example could be extended to allow ‘greater/smaller then’ clauses (using operator overloading?) or joins in the from clause; feel free to clone my repository and do so!

Full sources can be found in the Scala-SQL-DSL github repository.