SQL DSL for Scala

I am struggling to create a SQL DSL for Scala. The DSL is an extension to Querydsl, which is a popular Query abstraction layer for Java.

I am struggling now with really simple expressions like the following

user.firstName == "Bob" || user.firstName == "Ann"

As Querydsl supports already an expression model which can be used here I decided to provide conversions from Proxy objects to Querydsl expressions. In order to use the proxies I create an instance like this

import com.mysema.query.alias.Alias._

var user = alias(classOf[User])

With the following implicit conversions I can convert proxy instances and proxy property call chains into Querydsl expressions

import com.mysema.query.alias.Alias._
import com.mysema.query.types.expr._
import com.mysema.query.types.path._

object Conversions {        
    def not(b: EBoolean): EBoolean = b.not()        
    implicit def booleanPath(b: Boolean): PBoolean = $(b);        
    implicit def stringPath(s: String): PString = $(s);        
    impl开发者_Go百科icit def datePath(d: java.sql.Date): PDate[java.sql.Date] = $(d);        
    implicit def dateTimePath(d: java.util.Date): PDateTime[java.util.Date] = $(d);        
    implicit def timePath(t: java.sql.Time): PTime[java.sql.Time] = $(t);            
    implicit def comparablePath(c: Comparable[_]): PComparable[_] = $(c);        
    implicit def simplePath(s: Object): PSimple[_] = $(s);        
}

Now I can construct expressions like this

import com.mysema.query.alias.Alias._
import com.mysema.query.scala.Conversions._

var user = alias(classOf[User])
var predicate = (user.firstName like "Bob") or (user.firstName like "Ann")

I am struggling with the following problem.

eq and ne are already available as methods in Scala, so the conversions aren't triggered when they are used

This problem can be generalized as the following. When using method names that are already available in Scala types such as eq, ne, startsWith etc one needs to use some kind of escaping to trigger the implicit conversions.

I am considering the following

Uppercase

var predicate = (user.firstName LIKE "Bob") OR (user.firstName LIKE "Ann")

This is for example the approach in Circumflex ORM, a very powerful ORM framework for Scala with similar DSL aims. But this approach would be inconsistent with the query keywords (select, from, where etc), which are lowercase in Querydsl.

Some prefix

var predicate = (user.firstName :like "Bob") :or (user.firstName :like "Ann")

The context of the predicate usage is something like this

var user = alias(classOf[User])

query().from(user)
    .where( 
      (user.firstName like "Bob") or (user.firstName like "Ann"))
    .orderBy(user.firstName asc)
    .list(user);

Do you see better options or a different approach for SQL DSL construction for Scala?

So the question basically boils down to two cases

• Is it possible to trigger an implicit type conversion when using a method that exists in the super class (e.g. eq)

• If it is not possible, what would be the most Scalaesque syntax to use for methods like eq, ne.

EDIT

We got Scala support in Querydsl working by using alias instances and a $-prefix based escape syntax. Here is a blog post on the results : http://blog.mysema.com/2010/09/querying-with-scala.html

There was a very good talk at Scala Days: Type-safe SQL embedded in Scala by Christoph Wulf.

See the video here: Type-safe SQL embedded in Scala by Christoph Wulf

Mr Westkämper - I was pondering this problem, and I wondered if would be possible to use 'tracer' objects, where the basic data types such as Int and String would be extended such that they contained source information, and the results of combining them would likewise hold within themselves their sources and the nature of the combination.

For example, your user.firstName method would return a TracerString, which extends String, but which also indicates that the String corresponds to a column in a relation. The == method would be overwritten such that it returns an EqualityTracerBoolean which extends Boolean. This would preserve the standard Scala semantics. However, the constructor for EqualityTracerBoolean would record the fact that the result of the expression was derived by comparing a column in a relation to a string constant. Your 'where' method could then analyse the EqualityTracerBoolean object returned by the conditional expression evaluated over a dummy argument in order to derive the expression used to create it.

There would have to be override defs for inequality operators, as well as plus and minus, for Ints, and whatever else you wished to represent from sql, and corresponding tracer classes for each of these. It would be a bit of a project!

Anyway, I decided not to bother, and use squeryl instead.

I didn't have the exact same problem with jOOQ, as I'm using a bit more verbose operator names: equal, notEqual, etc instead of eq, ne. On the other hand, there is a val operator in jOOQ for explicitly creating bind values, which I had to overload with value, as val is a keyword in Scala. Is overloading operators an option for you? I documented my attempts of running jOOQ in Scala here:

http://lukaseder.wordpress.com/2011/12/11/the-ultimate-sql-dsl-jooq-in-scala/

Just like you, I had also thought about capitalising all keywords in a major release (including SELECT, FROM, etc). But that will leave an open question about whether "compound" keywords should be split in two method calls, or connected by an underscore: GROUP().BY() or GROUP_BY(). WHEN().MATCHED().THEN().UPDATE() or WHEN_MATCHED_THEN_UPDATE(). Since the result is not really satisfying, I guess it's not worth to break backwards-compatibility for such a fix, even if the two-method-call option would look very very nice in Scala, as . and () can be omitted. So maybe, jOOQ and QueryDSL should both be "wrapped" (as opposed to "extended") by a dedicated Scala-API?

What about decompiling the bytecode at runtime? I started to write such a tool:

http://h2database.com/html/jaqu.html#natural_syntax

I know it's a hack, so please don't vote -1 :-) I just wanted to mentioned it. It's a relatively novel approach. Instead of decompiling at runtime, it might be possible to do it at compile time using an annotation processor, not sure if that's possible using Scala (and not sure if it's really possible with Java, but Project Lombok seems to do something like that).