szeiger.de

I have recently made a change to ScalaQuery which enables the use of any type for a column without needing specific Column classes and factory methods on Table for it. For this I used an approach which I had not considered earlier and which I wasn’t even sure would work.

Scala (at least in version 2.7) only considers single function calls for implicit conversions. Take the following definitions for example:

class A
class B(a: A)
class C(b: B)

implicit def aToB(a: A) = new B(a)
implicit def bToC(b: B) = new C(b)

val a = new A

def useB(b: B) = ...
def useC(c: C) = ...

You can call useB(a) because the compiler finds the implicit conversion aToB(a) but you cannot call useC(a) — the compiler does not try the chained call bToC(aToB(c)).

But this does not mean that an implicit conversion may not rely on an implicit value! The following works just fine:

trait Column[T]
class ConstColumn[T](value: T, tm: TypeMapper[T]) extends Column[T]

implicit def valueToColumn[T](value: T)(implicit tm: TypeMapper[T]) =
  new ConstColumn(value, tm)

trait TypeMapper[T]
implicit object IntTypeMapper extends TypeMapper[Int]

val c1: Column[_] = 42

The Scala compiler recognizes that valueToColumn[Int] provides the desired conversion from Int to Column[_] and then looks for the required implicit TypeMapper[Int] value, which is available through the implicit IntTypeMapper object.

In ScalaQuery, the real TypeMapper contains only a few methods and there are predefined TypeMappers for most of the basic types used by JDBC. That’s nice because it removes some redundancy from my code base but the better news is that it allows you to write your own TypeMappers. For example, if you wanted to get the java.lang.Integer columns back which I removed in favor of Int and Option[Int], you could add this implicit object to your code:

implicit object IntegerTypeMapper extends TypeMapper[java.lang.Integer] {
  def zero = null
  def sqlType = java.sql.Types.INTEGER
  def setValue(v: java.lang.Integer, p: PositionedParameters) =
    if(v eq null) p.setIntOption(None) else p.setIntOption(Some(v.intValue))
  def setOption(v: Option[java.lang.Integer], p: PositionedParameters) = v match {
    case Some(null) => p.setIntOption(None)
    case Some(i) => p.setIntOption(Some(i.intValue))
    case None => p.setIntOption(None)
  }
  def nextValue(r: PositionedResult) = r.nextIntOption match {
    case None => null
    case Some(i) => java.lang.Integer.valueOf(i)
  }
}

The same should work for any database engine- or domain-specific types.

Previously, ScalaQuery used nullable Java types for all columns. This is straight-forward for reference types like java.lang.String which are mapped 1:1 to Scala types but not for Scala’s value types like scala.Int or scala.Boolean. Although they are often implemented in terms of Java types like java.lang.Integer and java.lang.Boolean, there is no 1:1 mapping and there are no aliases for those wrapper types in Scala. Most of the time this is not a problem because you can use Scala’s value types everywhere and have the compiler generate optimized code with primitive types where applicable.

Except there is one thing you cannot do with Scala’s value types: They may never be null. The reference types are nullable but that’s probably not a good idea, either. Scala needs nullable reference types for interfacing with Java code but for native Scala APIs, the Option type is the preferred solution. It makes None values explicit and thus prevents NullPointerExceptions at runtime when a null value is used in a place where it shouldn’t be allowed.

SQL databases also use NULL values for various things (e.g. missing values, undefined values, horrid abominations) so this should work nicely with Scala’s Option type. I rejected Options for database results in ScalaQuery at first because most columns are not nullable (so you wouldn’t want to have all columns return an Option instead of just the nullable ones) yet some operations like outer joins need to convert non-nullable columns to nullable ones (which can probably not be typed in Scala's type system, so you'd have to make all columns return an Option).

On the other hand, having nullable java.lang.Integer and other Java wrapper types in a Scala API is rather ugly, so I finally removed them in favor of a more practical yet null-free solution. All columns now use proper Scala types (scala.Int, scala.Predef.String, etc.). NULLs from the database are returned as a default value (0 for Int, an empty string for String) which can be changed with the orElse method, e.g.:

  for(u <- Users)
    yield u.first.orElse("no first name")
        ~ u.last.orElse("no last name")

Note that this value is only used when extracting rows from the result set on the client side. It does not change the handling of NULLs inside the database server!

For nullable types like scala.Predef.String you can use orElse(null) to get the old behaviour back. The argument of orElse() is passed by name, so you can also run an arbitrary block of code or throw an exception when a NULL value is encountered in the result set. There is a convencience method called orFail which does just that. Maybe this should be the default instead of returning a zero value?

The preferred solution for distinguishing between regular values and NULL for any type is to use the ? method to turn a SimpleColumn of type T into one of type Option[T]:

  for(u <- Users) yield u.first.? ~ u.last.?

Currently there are no operations defined on option columns so it does not make sense to declare an option column directly as part of a table. Ideally, columns of types T and Option[T] should be fully interoperable because the database server uses three-valued logic anyway.

I have just pushed the new code to the master branch and updated the stable branch to include the previously redesigned AST.

The type-safe database query library/DSL for Scala which I introduced in this post is now available in a public GIT repository at GitHub: http://github.com/szeiger/scala-query/tree

I have renamed it to ScalaQuery because SQuery and all other short names I could think of were already used by numerous other projects.

There are several changes compared to the version I used in the introduction:

• The combinator/monad-based query classes are now in their own package com.novocode.squery.combinator.
• The invokers (which execute a typed query through JDBC) of the combinator queries and the simple SQL queries (in com.novocode.squery.simple) have been unified.
• Some minor renaming and refactoring.

When the topic of JDBC wrappers came up on the Scala mailing list a few weeks ago, I mentioned that…

I’d prefer a type-safe DSL for database queries but that’s more
complicated. I’m still experimenting with that, too

I think those experiments worked rather well and the code is now in a state where the major features are usable and I can show some example code which compiles and runs successfully as a proof of concept. Many features (like more SQL operators, more data types and column aliasing) are missing but at least I am confident that it is possible to implement them on this basis.

Just like HaskellDB and LINQ to SQL, I am using a query monad which collects the projections and restrictions on database tables in a composable way to build an SQL statement for a query while producing the correct type for that statement’s result in Scala’s type system. Don’t worry if that sounds too abstract, there will be plenty of example code. (more…)