views:

315

answers:

3
+5  Q: 

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);        
    implicit 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

+1  A: 

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

MatthieuF
Thanks for the pointer. If I understand the video correctly the SQL integration is based on source pre-parsing. I am searching for an approach without any preparsing.
Timo Westkämper
+1  A: 

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

Thomas Mueller
@Thomas I'ts not a hack and enables one to use native operators, but I think it is much more difficult and the results of the decompilation might be dependent on the used compiler.
Timo Westkämper
+1  A: 

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.

Crosbie Smith
Thanks for your elaborate answer. I believe Thomas Mueller has done something like this in H2 / JaQu. I am not sure if Boolean etc in Scala can be extended. This is an interesting approach, but really laborous.
Timo Westkämper