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.

After importing the settings from my XP installation into the freshly installed copy of Windows 7 RTM with the included Windows Easy Transfer tool, I suddenly had two additional icons on my desktop: HomeGroup and Libraries. And there is no obvious way of removing them. You cannot just delete them and they are not listed in the “Desktop Icon Settings” dialog, either.

A quick Google search (maybe I should have used Bing for this…) turned up one suggested solution which is mentioned in several forums: Use RegEdit to delete {B4FB3F98-C1EA-428d-A78A-D1F5659CBA93} and {031E4825-7B94-4dc3-B131-E946B44C8DD5} from HKLM\SOFWARE\Microsoft\Windows\CurrentVersion\Explorer\Desktop\NameSpace.

Well, don’t! This also removes HomeGroup and Libraries from the Explorer tree (and maybe other places). There must be a better solution. After all, the icons were not present on the desktop initially but they did show up in Explorer.

Under the assumption that all desktop icons (whether they are listed in “Desktop Icon Settings” or not) are managed in one place in the registry, I fired up RegEdit and Desktop Icon Settings, exported the registry, enabled the Control Panel icon on the desktop and then exported again. A diff of the two registry dumps showed one obvious change:

In HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\HideDesktopIcons\ClassicStartMenu a DWord entry named {5399E694-6CE5-4D6C-8FCE-1D8870FDCBA0} was changed from 1 to 0. The same entry was added with value 0 to HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\HideDesktopIcons\NewStartPanel.

I checked the same folders under HKLM. They contain more entries, including {B4FB3F98-C1EA-428d-A78A-D1F5659CBA93} and {031E4825-7B94-4dc3-B131-E946B44C8DD5}. Hey, those IDs look familiar! I created copies of them with value 1 unter HKCU\…\ClassicStartMenu and HKCU\…\NewStartPanel and voilà, the icons were gone from the desktop.

I hope you don’t have to waste as much time on this problem as I did. Disclaimer: Changing the registry can mess up your system. Do this at your own risk!

One question about ScalaQuery which keeps coming up is that of perfomance. The question is usually in the form “How does it compare to JDBC?” but that’s like comparing apples and, well, apple trees. After all, ScalaQuery is a layer on top of JDBC which provides mainly two things:

• A nicer, more Scala-like way of handling database connections, performing queries and reading result sets. This is not optional when you access a database in your application. You will wrap SQL statement execution and result set reading in some way or another to abstract from the low-level JDBC API. Anyway, the overhead here is quite low.
• A way of composing queries with an internal DSL based on a query monad and combinators. That’s the part I want to talk about in this post.

If you want to optimize the query generation away, you can always fall back to the StaticQuery and DynamicQuery classes. These work a bit like iBATIS, except your SQL code is embedded directly in your Scala code and not in some XML files. But you’re still writing SQL! That’s nice for the special cases which are not covered by the combinator queries and which do not need to be composable but it’s probably not the reason why you want to use ScalaQuery in the first place.

When you’re constructing a query in the query monad, you normally have some variables which are used in the query like this:

def userNameByID(id: Int) =
  for(u <- Users if u.id is id) yield u.first

This would insert the user ID directly into the SQL statement, thus requiring a new statement to be generated by ScalaQuery and parsed and optimized by the database server (which can be very expensive) for every invocation of the query. We can improve this by using a bind variable for the user ID:

def userNameByID(id: Int) =
  for(u <- Users if u.id is id.bind) yield u.first

Now the generated SQL statement is always the same (e.g. “SELECT t1.first FROM users t1 WHERE (t1.id=?)“), so the database server needs to calculate an execution plan for it only once and can reuse it on subsequent invocations. But the query is still constructed as a tree of dozens of objects and compiled to the same SQL statement every time you call userNameByID.

This can be remedied with query templates, a recent addition to ScalaQuery:

val userNameByID = for {
  id <- Parameters[Int]
  u <- Users if u.id is id
} yield u.first

If you recall how for-comprehensions are desugared, this gets translated to Parameters[Int].apply(...).flatMap(id => ...). The apply method on the Parameters object takes an implicit TypeMapper for each parameter type you specify and creates a Parameters instance. The flatMap method on Parameters takes a function which creates a Query and returns a QueryTemplate for it:

final class QueryTemplate[P, R](query: Query[ColumnBase[R]]) {
  def apply(param: P) = new AppliedQueryTemplate(built, param, query.value)
  lazy val built = QueryBuilder.buildSelect(query, NamingContext())
}

final class Parameters[P, C](c: C) {
  def flatMap[F](f: C => Query[ColumnBase[F]]): QueryTemplate[P, F] =
    new QueryTemplate[P, F](f(c))
  ...
}

object Parameters {
  def apply[P1](implicit tm1: TypeMapper[P1]) =
    new Parameters[P1, Column[P1]](new ParameterColumn(-1, tm1))
  ...
}

Note that, unlike Query, neither Parameters nor QueryTemplate is a monad. You cannot compose multiple parameter lists or query templates this way but by providing a suitable flatMap method, the parameters can be used as the first generator in a for-comprehension which otherwise operates in the Query monad.

Either one of the userNameByID functions/methods defined above can be used in the same way:

for(t <- userNameByID(3)) println(t)

When you apply the parameters to a QueryTemplate, you get an AppliedQueryTemplate which can be lifted to a suitable Invoker by an implicit conversion (just like a Query). The first time you do this, the SQL code gets generated and then cached in the QueryTemplate for further applications.

If you specify more than one type parameter, the Parameters generator gives you a Projection instead of a single Column. It can be unpacked either with the extractor on the “~” object:

val userNameByIDRangeAndProduct = for {
  min ~ max ~ product <- Parameters[Int, Int, String]
  u <- Users if u.id >= min &&
    u.id <= max &&
    Orders.where(o => (u.id is o.userID) && (o.product is product)).exists
} yield u.first

…or with the Projection extractor (which is slightly more efficient but not as nice to read):

val userNameByIDRangeAndProduct = for {
  Projection(min, max, product) <- Parameters[Int, Int, String]
  u <- Users if u.id >= min &&
    u.id <= max &&
    Orders.where(o => (u.id is o.userID) && (o.product is product)).exists
} yield u.first

ScalaQuery’s invoker framework provides methods for invoking queries with parameters but these are currently used by the simple queries only. I expect to integrate query templates with this system in the future.