szeiger.de

It’s been three weeks already since my last post about ScalaQuery so I’d like to provide an update on some recent changes.

Profiles & Drivers

The biggest news is that ScalaQuery now supports database engine-specific features. Feature sets can be bundled in profiles which are then implemented by drivers (Of course, a driver can also add driver-specific features which are not part of a profile). All previously existing features are now part of the BasicProfile which should work on all SQL databases with little or no customization. The BasicProfile provides:

• An object called Implicit with all implicit conversions which are required to use the profile’s features (including an implicit value of type BasicProfile which points to the driver implementing the profile).
• Some methods for creating various statements and query templates. They have default implementations provided by the profile but can be overridden by other profiles or drivers. You do not usually call them directly.
• A number of TypeMapperDelegate objects for the basic types supported by the profile. You still use TypeMappers defined outside the profile but they do not implement the actual type mapping any more. Instead they ask the profile for a delegate implementation. (Alternatively, I could have moved the existing TypeMapper objects entirely into the profile but mappers for some basic types like Int and Boolean are used internally at many places. They would need to be threaded through several methods and constructors, thus complicating the implementation unnecessarily.)

The BasicProfile is implemented by the BasicDriver.

There is also a new ExtendedProfile for features which are expected to be available in every commonly used SQL database system, albeit with a non-standard syntax. It currently provides string concatenation with the ++ operator (plus startsWith and endsWith methods which can be implemented with LIKE and string concatenation) and the take and drop methods needed for pagination.

If you only need to support a single database engine in your application, you can forget about profiles and just import a specific driver’s implicit conversions. Supporting multiple drivers is almost as simple: Give your DAO class (or whatever you have in your application design) a parameter of the required profile type and import this profile’s implicits. You can then call it with any driver which implements the profile. For example:

  def test(profile: ExtendedProfile) {
    import profile.Implicit._
    println("Using driver: "+profile.getClass.getName)
    val q1 = Users.where(_.name startsWith "quote ' and backslash \\").take(5)
    println(q1.selectStatement)
    val q2 = Users.where(_.name startsWith "St".bind).drop(10).take(5)
    println(q2.selectStatement)
    val q3 = Query(42 ~ "foo")
    println(q3.selectStatement)
    println
  }

  def main(args: Array[String]) {
    test(H2Driver)
    test(OracleDriver)
    test(MySQLDriver)
  }

This prints the different statements required for H2, Oracle and MySQL:

Using driver: com.novocode.squery.combinator.extended.H2Driver$
SELECT t1.name FROM users t1 WHERE (t1.name like 'quote '' and backslash \%') LIMIT 5
SELECT t1.name FROM users t1 WHERE (t1.name like (? || '%')) LIMIT 5 OFFSET 10
SELECT 42,'foo'

Using driver: com.novocode.squery.combinator.extended.OracleDriver$
SELECT * FROM (SELECT t1.name FROM users t1 WHERE (t1.name like 'quote '' and backslash \%')) WHERE ROWNUM <= 5
SELECT * FROM (SELECT t0.*, ROWNUM ROWNUM_O FROM (t1.name,ROWNUM ROWNUM_I FROM users t1 WHERE (t1.name like (? || '%'))) t0) WHERE ROWNUM_O BETWEEN (1+10) AND (10+5) ORDER BY ROWNUM_I
SELECT 42,'foo' FROM DUAL

Using driver: com.novocode.squery.combinator.extended.MySQLDriver$
SELECT t1.name FROM users t1 WHERE (t1.name like 'quote \' and backslash \\%') LIMIT 5
SELECT t1.name FROM users t1 WHERE (t1.name like concat(?,'%')) LIMIT 10,5
SELECT 42,'foo' FROM DUAL

Updates

You can now take a simple query (returning only named columns from a single table; no modifiers except WHERE restrictions) and call it as an UPDATE statement:

  val q = for(u <- Users if u.id is 42) yield u.first ~ u.last
  q.update("foo", "bar")

Filtering

You may already have noticed in my previous post that the Query class now has a filter method which works like where for functions returning a Column[Boolean] or Column[Option[Boolean]], so you can use Scala’s standard if clauses in for-comprehensions on queries. Unlike where, filter also accepts functions returning a plain Boolean value to enable the use of refutable patterns in queries (e.g. when destructuring a Join).

Invokers

Result type remapping and parameter application are now available for all Invokers. These features were pushed down from the statement invokers for monadic queries into the invoker framework. Query templates are parameterized invokers now. They can be applied like any other invoker.