Scala collection-like SQL support as in LINQ

Question

As far as I understand the only thing LINQ supports, which Scala currently doesn't with its collection library, is the integration with a SQL Database.

As far as I understand LINQ can "accumulate" various operations and can give "the whole" statement to the database when queried to process it there, preventing that a simple SELECT first copies the whole table into data structures of the VM.

If I'm wrong, I would be happy to be corrected.

If not, what is necessary to support the same in Scala?

Wouldn't it possible to write a library which implements the collection interface, but doesn't have any data structures backing it but a String which gets assembled with following collection into the required Database statement?

Or am I completely wrong with my observations?

Answer 1

As the author of ScalaQuery, I don't have much to add to Stilgar's explanation. The part of LINQ which is missing in Scala is indeed the expression trees. That is the reason why ScalaQuery performs all its computations on Column and Table types instead of the basic types of those entities.

You declare a table as a Table object with a projection (tuple) of its columns, e.g.:

class User extends Table[(Int, String)] {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def * = id ~ name
}

User.id and User.name are now of type Column[Int] and Column[String] respectively. All computations are performed in the Query monad (which is a more natural representation of database queries than the SQL statements that have to be created from it). Take the following query:

val q = for(u <- User if u.id < 5) yield u.name

After some implicit conversions and desugaring this translates to:

val q:Query[String] =
  Query[User.type](User).filter(u => u.id < ConstColumn[Int](5)).map(u => u.name)

The filter and map methods do not have to inspect their arguments as expression trees in order to build the query, they just run them. As you can see from the types, what looks superficially like "u.id:Int < 5:Int" is actually "u.id:Column[Int] < u.id:Column[Int]". Running this expression results in a query AST like Operator.Relational("<", NamedColumn("user", "id"), ConstColumn(5)). Similarly, the "filter" and "map" methods of the Query monad do not actually perform filtering and mapping but instead build up an AST that describes these operations.

The QueryBuilder then uses this AST to construct the actual SQL statement for the database (with a DBMS-specific syntax).

An alternative approach has been taken by ScalaQL which uses a compiler plugin to work directly with expression trees, ensure that they only contain the language subset which is allowed in database queries, and construct the queries statically.

Answer 2

I should mention that Scala does have experimental support for expression trees. If you pass an anonymous function as an argument to a method expecting a parameter of type scala.reflect.Code[A], you get an AST.

scala> import scala.reflect.Code      
import scala.reflect.Code 
scala> def codeOf[A](code: Code[A]) = code
codeOf: [A](code:scala.reflect.Code[A])scala.reflect.Code[A]
scala> codeOf((x: Int) => x * x).tree 
res8: scala.reflect.Tree=Function(List(LocalValue(NoSymbol,x,PrefixedType(ThisType(Class(scala)),Class(scala.Int)))),Apply(Select(Ident(LocalValue(NoSymbol,x,PrefixedType(ThisType(Class(scala)),Class(scala.Int)))),Method(scala.Int.$times,MethodType(List(LocalValue(NoSymbol,x$1,PrefixedType(ThisType(Class(scala)),Class(scala.Int)))),PrefixedType(ThisType(Class(scala)),Class(scala.Int))))),List(Ident(LocalValue(NoSymbol,x,PrefixedType(ThisType(Class(scala)),Class(scala.Int)))))))

This has been used in the bytecode generation library 'Mnemonics', which was presented by its author Johannes Rudolph at Scala Days 2010.