Recap

In ScalaQuery 0.9, there is a simple relationship between the types of queries and the types of values they return: A Query[ColumnBase[T]] selects values of type T, so e.g. calling .list on such a query gives you a List[T]. You can create Query objects of types other than ColumnBase[T] forSome { type T } but the implicit conversion to a StatementInvoker (which allows you to call methods such as .list) is not available for them.

Let’s look at the different subtypes of ColumnBase that we can use in queries. First of all, all tables extend ColumnBase, so that Table[T] <: ColumnBase[T].

  object MyTable extends Table[(Int, String, String)]("MY_TABLE") {
    def a = column[Int]("A")
    def b = column[String]("B")
    def c = column[String]("C")
    def * = a ~ b ~ c
  }

  val q1 = Query(MyTable)
  // q1: Query[MyTable]
  val res1 = q1.list
  // res1: List[(Int, String, String)]

And, as implied by the name ColumnBase, Column[T] <: ColumnBase[T], so we can query for individual columns:

  val q2 = for {
    t <- MyTable // automatically lifted to a Query
  } yield t.a
  // q2: Query[Column[Int]]
  val res2 = q2.list
  // res2: List[Int]

So what about selecting multiple columns? The most natural notation would be the same as when you’re querying a Scala collection instead of a database:

  val q3 = for {
    t <- MyTable
  } yield (t.a, t.b)
  // q3: Query[(Column[Int], Column[String])]

But this won’t work. We get a Query[(ColumnBase[A], ColumnBase[B])] but what we need is a Query[ColumnBase[(A, B)]] — wrapped in the opposite order! This ColumnBase[Tuple2[A,B]] is called a Projection2[A,B] in ScalaQuery (actually, a Projection2[A,B] is both a ColumnBase[(A,B)] and a (Column[A], Column[B])). You construct it with the ~ operator:

  val q3b = for {
    t <- MyTable
  } yield t.a ~ t.b
  // q3b: Query[Projection2[Int, String]]
  val res3 = q3b.list
  // res3: List[(Int, String)]

Type-Level Transformation

For the sake of simplicity, instead of the implicit conversions and Invokers we will assume a method list() which takes a Query and returns a list of the results. At the moment, it would look like this:

  def list[T](q: Query[ColumnBase[T]]): List[T]

We need to generalize it to:

  def list[T, U](q: Query[T]): List[U]

where U is a transformed version of T, which we will write as T =>> U. So what kinds of transformations do we need? The existing functionality is covered by:

  ColumnBase[T] =>> T

For tuples, as used in q3, we need another rule:

  (T1, T2) =>> (U1, U2) where T1 =>> U1 and T2 =>> U2

Actually, we need one of those for all arities from 2 to 22:

  (T1, T2, T3) =>> (U1, U2, U3) where T1 =>> U1 and T2 =>> U2 and T3 =>> U3
  ...and so on

Note that these rules are recursive. They enable the transformation of arbitrarily nested tuples.

Just one more rule before we’re done: ScalaQuery’s Projection classes can only wrap Columns, so any primitive value is lifted implicitly:

  t.a ~ t.b ~ 42
  // : Projection3[Int, String, Int]
  //   == (Column[Int], Column[String], Column[Int])

If we allow the use of Tuples instead of Projections, we do not get this implicit lifting anymore, so we need to allow primitive values, too:

  T =>> T where a TypeMapper[T] is available

We can use functional dependencies to have the Scala compiler do the required transformation by turning =>> into a trait, every “where” condition into an implicit parameter, and every rule into an implicit method:

  trait =>> [-T, U]

  implicit def unpackColumnBase[T]: ColumnBase[T] =>> T = null
  implicit def unpackTuple2[T1, T2, U2, U2](implicit u1: T1 =>> U1, u2: T2 =>> U2): (T1, T2) =>> (U1, U2) = null
  ...
  implicit def unpack[T : TypeMapper]: T =>> T = null

In order to avoid ambiguities, we need to prioritize the implicits. We also add a nice error message in case a bad query type is used:

  @implicitNotFound(msg = "Don't know how to unpack ${T} (to ${U})")
  trait =>> [-T, U]

  object =>> extends LowPriority_=>> {
    implicit def unpackColumnBase[T]: ColumnBase[T] =>> T = null
  }

  trait LowPriority_=>> {
    implicit def unpackTuple2[T1, T2, U2, U2](implicit u1: T1 =>> U1, u2: T2 =>> U2): (T1, T2) =>> (U1, U2) = null
    ...
    implicit def unpack[T : TypeMapper]: T =>> T = null
  }

And finally, the list() method gets an implicit parameter to compute the U type.

  def list[T, U](q: Query[T])(implicit u: T =>> U): List[U]

That’s it, basically. We can now call list() with a Query of any nesting of tuples and columns and it will remove all ColumnBase type constructors for the result type. We still need to extract a value of the right type from the query result at run-time. That can be done with a method in an implementation of =>> in a straight-forward way. For the purpose of computing the type at compile-time, the implementation does not matter, so we just used null above.

Another Transformation

There’s still a catch though. Take a look at the following queries:

  val q4a = for {
    a ~ b ~ c <- MyTable.where(_.a === 1).map(t => t.a ~ t.b ~ 4) unionAll
                 MyTable.where(_.a === 2).map(t => t.a ~ t.b ~ 5)
  } yield a ~ b ~ (c*2)

  val q4b = for {
    (a, b, c) <- MyTable.where(_.a === 1).map(t => (t.a, t.b, 4)) unionAll
                 MyTable.where(_.a === 2).map(t => (t.a, t.b, 5))
  } yield a ~ b ~ (c*2)

A unionAll operation takes multiple Queries of the same type and produces another Query of that type which represents their union. The values that come out of this operation need to encode the union as a ScalaQuery AST node. In q4a (old style), this value has the type Projection3[Int, String, Int] which is (Column[Int], Column[String], Column[Int]). The literal values 4 and 5 have been lifted to ConstColumns and the union operation can be properly encoded into the three resulting Columns.

In q4b (new style), the unionAll produces a (Column[String], Column[Int], Int) tuple and we have no way of encoding the union operation into the Int value! To solve this problem, union operations need to return a different type than that of their operands: One where all primitive values have been lifted to Columns, i.e. the opposite of the =>> transformation:

  @implicitNotFound(msg = "Don't know how to reify ${Packed} (to ${Reified})")
  sealed trait Reify[-Packed, Reified]

  object Reify extends ReifyLowPriority {
    implicit final def reifyColumnBase[T <: ColumnBase[_]]: Reify[T, T] = null
  }

  trait ReifyLowPriority {
    implicit final def reifyPrimitive[T](implicit tm: TypeMapper[T]): Reify[T, ConstColumn[T]] = null
    implicit final def reifyTuple2[T1,T2, U1,U2, R1,R2](implicit u1: Reify[T1, R1], u2: Reify[T2, R2]): Reify[(T1,T2), (R1,R2)] = null
    ...
  }

Limitations

The actual reification at run-time is done in =>>, so it is essential that the =>> and Reify implementation match. It would be nicer to encode the reification directly in =>> but this fails due to a limitation in Scala’s type inferencer. Compare the unpacking and reification for ColumnBase:

  implicit def unpackColumnBase[T]: ColumnBase[T] =>> T
  implicit final def reifyColumnBase[T <: ColumnBase[_]]: Reify[T, T]

In both cases, we get a type like A <: ColumnBase[B] but one operation has to capture the A and the other one the B (we need the actual sub-type of ColumnBase for the reification to preserve table types). There is no way (that I know of) of doing both together. Exposing the type parameter through a type alias in ColumnBase does not help. It allows us to write the unpacking in the desired style but T#Alias is always inferred as Any:

  implicit def unpackColumnBase[T <: ColumnBase[_]]: T =>> T#Alias

A second problem is caused by Scala issue SI-3346: Fundep implicits do not work on implicit conversions. I have been able to work around this in some cases but in others the situation looks hopeless. As long is this issue is not resolved, some extra methods calls for explicit unpacking might be needed.

You can find the work in progress in ScalaQuery’s nested-tuples-query2 branch.

1. Get the official OTA update from http://android.clients.google.com/packages/passion/signed-passion-ota-42745.dc39ca1f.zip, rename it to update.zip and put it into the root folder of the SD card in your N1. The MD5 checksum should be 94f103b38c726fe17a5ff8e7676a737b.
2. Unfortunately, the standard recovery image on a European N1 will not be able to verify this update, so you need to flash the stock US image first. This requires an unlocked bootloader. If you haven’t unlocked your N1 yet, you need to do it now. This will void your warranty and wipe all data on your phone! You can find various guides on how to this on the internet, e.g. here. Be sure to wipe manually after unlocking. All data gets wiped anyway but it’s possible that Froyo won’t boot if you don’t do a manual wipe, too.
3. Now get the stock recovery image from here and install it (as described on that page).
4. Reboot while holding the volume down button and select Recovery from the boot menu.
5. When the screen with the android and the exclamation mark appears, press volume up and power for a moment to get into the recovery menu. You may have to try this combination several times before you get the menu.
6. Select Apply SDcard:update.zip and wait for the update to be installed. The phone will reboot automatically (which can take a few minutes on the first reboot).

Do this at your own risk! Back up all data and settings first!

The Problem

Take this piece of XML (an Eclipse .project file):

<projectDescription>
  <name>scala-query</name>
  <comment></comment>
  <projects></projects>
  <buildSpec>
    <buildCommand>
      <name>ch.epfl.lamp.sdt.core.scalabuilder</name>
      <arguments></arguments>
    </buildCommand>
  </buildSpec>
  <natures>
    <nature>ch.epfl.lamp.sdt.core.scalanature</nature>
    <nature>org.eclipse.jdt.core.javanature</nature>
  </natures>
</projectDescription>

Its scala.xml model looks like this:

(In reality, a pretty-printed document contains a bunch of additional text nodes with all the whitespace between the elements, but we can safely ignore them and work with a simplified model.)

Imagine you have a reference to the first “nature” element which we will call the context. The only other node reachable from this context is the text node below it. You cannot navigate to its parent or sibling without starting at the root element and searching for the right spot.

An obvious solution to this problem is to add a reference from each node to its parent but that means giving up persistence (the sharing of data between different versions of the tree).

Let’s look at the situation without the parent reference first and assume you wanted to add a third “nature” element before the two existing ones. Since you cannot modify the “natures” element in this immutable data structure, you have to make a copy of it which contains the two original children plus the new one. Now that you have a new “natures” element, you make a copy of the “projectDescription” root element above it which contains all the original children except for the “natures” element which is replaced with the new one. This gives you two separate trees (the original and the modified one) which share most of their nodes:

(For the “natures” element we can even reuse the list of its children because we only prepend an element at the beginning. We need to copy the list of the root element’s children though because we replace its last element.)

Now try the same on a model with parent references. You can create the new “nature” element and a copy of the “natures” element above it. But what about the other two “nature” elements? You cannot reuse them because they contain a reference to the original “natures” element, so you have to create copies of them which point up to their new parent. You cannot even reuse the text nodes below them because they also reference their original parents. And now that you have copied the entire “natures” sub-tree, you face the same problem with the root element. All its children point up to the old root element, so you have to copy them recursively, too. In the end you have copied the tree entirely!

The Zipper

We can do better with the Zipper. The basic idea is to take an immutable data structure for the actual data (in this case XML documents in scala.xml models) and add a representation for a context that allows you to navigate through the data in all directions and modify parts of it (in the same way that you modified a document in the previous section — by creating a new document that shares most of the data).

We start with a type for the context:

sealed case class NodeLoc(node: Node, path: NodePath)

It consists of the node (or sub-tree) which it wraps plus a path to the root element. A path can either be the top (root) of the tree or a hole, i.e. the location of a node without the node itself:

sealed trait NodePath
final case object Top extends NodePath
final case class Hole(left: List[Node],
  parent: NodeLoc, right: List[Node]) extends NodePath

For example, here is a NodeLoc for the first “nature” element:

You can see that the original tree (in grey and yellow) is still intact. There are two NodeLocs with holes which point up to a parent NodeLoc and contain lists of their preceding and following siblings. Note that the list of preceding siblings is in reverse order (compared to the corresponding list of children on the parent node) so that the first node in the list is the next element to the left of the hole.

We can now define the basic navigation methods on NodeLoc. In order to move to the left sibling, we create a new NodeLoc which contains the head of the “left” list as its node and a copy of the current hole with the remaining nodes on the “left” list and the current node appended to the “right” list:

  protected def create(node: Node, path: Hole) = NodeLoc(node, path)

  final def leftOpt = path match {
    case Hole(tl :: l, p, r) =>
      Some(create(tl, Hole(l, p, node :: r)))
    case _ => None
  }

Moving to the right is symmetrical to that:

  final def rightOpt = path match {
    case Hole(l, p, tr :: r) =>
      Some(create(tr, Hole(node :: l, p, r)))
    case _ => None
  }

Methods left and right which return a bare NodeLoc or throw an exception, and isFirst and isLast to ensure that you don’t run into such an exception, can be defined in the same way. I’m using Option here because it will be useful later on for creating the XPath axes.

Moving down to a child with the specified index position is accomplished by splitting the list of the current node’s children at the correct position, removing the child node and creating a Hole for the other children which points up to this NodeLoc:

  final def downOpt(idx: Int) = {
    val ch = node.child
    if(ch.isEmpty) None
    else Some(create(ch.head,
      Hole(ch.tail.take(idx).reverse.toList,
           this, ch.drop(idx+1).toList)))
  }

Mutable Yet Immutable

Replacing the node at the current location is extremely simple. We just create a NodeLoc with the new Node and the current path:

  final def set(n: Node) = NodeLoc(n, path)

There is no need to propagate the change up to the root element at this point. We could, for example, move through all siblings, replacing each one with a new node, all in constant time (per sibling). Parent elements are not copied until we move up and “unzip” the path into a new tree. This is done by constructing a new list of children from the current node and its left and right siblings, and creating a copy of the old parent with the new children:

  def upOpt = path match {
    case h : Hole =>
      val l = h.left.reverse ++ (node :: h.right)
      Some(NodeLoc(h.parent.node match {
        case e: Elem => e.copy(child = l)
        case _: Group => new Group(l)
      }, h.parent.path))
    case _ => None
  }

This is slightly messy with scala.xml because we have to match on the type of node and copy Elem and Group nodes (the only ones which can contain children) in different ways.

Always recreating the parent when moving up is a waste of time when no changes have been made to the current sub-tree. We can optimize for this case with a special CachedParentNodeLoc which returns the original parent when moving up:

final class CachedParentNodeLoc(node: Node, path: Hole)
extends NodeLoc(node, path) {
  override def upOpt = Some(path.parent)
  override protected def create(node: Node, path: Hole) =
    new CachedParentNodeLoc(node, path)
}

final class CachedTopNodeLoc(node: Node) extends NodeLoc(node, Top) {
  override def upOpt = None
  override protected def create(node: Node, path: Hole) =
    new CachedParentNodeLoc(node, path)
}

object NodeLoc {
  def apply(node: Node): NodeLoc = new CachedTopNodeLoc(node)
}

Note that the leftOpt, rightOpt and downOpt methods use create(...) instead of NodeLoc(...), so that navigating left, right or down from a cached location returns another cached location.

The XPath Axes

The XPath specification defines the following axes:

• the child axis contains the children of the context node
• the descendant axis contains the descendants of the context node; a descendant is a child or a child of a child and so on; thus the descendant axis never contains attribute or namespace
  nodes
• the parent axis contains the parent of the context node, if there is one
• the ancestor axis contains the ancestors of the context node; the ancestors of the context node consist of the parent of context node and the parent’s parent and so on; thus, the ancestor axis will always include the root node, unless the context node is the root node
• the following-sibling axis contains all the following siblings of the context node; if the context node is an attribute node or namespace node, the following-sibling axis is empty
• the preceding-sibling axis contains all the preceding siblings of the context node; if the context node is an attribute node or namespace node, the preceding-sibling
  axis is empty
• the following axis contains all nodes in the same document as the context node that are after the context node in document order, excluding any descendants and excluding attribute nodes and namespace nodes
• the preceding axis contains all nodes in the same document as the context node that are before the context node in document order, excluding any ancestors and excluding attribute nodes and namespace nodes
• the attribute axis contains the attributes of the context node; the axis will be empty unless the context node is an element
• the namespace axis contains the namespace nodes of the context node; the axis will be empty unless the context node is an element
• the self axis contains just the context node itself
• the descendant-or-self axis contains the context node and the descendants of the context node
• the ancestor-or-self axis contains the context node and the ancestors of the context node; thus, the ancestor axis will always include the root node

The attribute and namespace axes do not fit well into our model because scala.xml does not represent attributes and namespace declarations as Node values. All other axes can be defined as methods on NodeLoc.

We will represent an axis as an Iterator[NodeLoc]. The following helper method creates an iterator by starting with a given value and continually applying a function to it until it returns None. It is similar to Iterator.iterate in Scala 2.8 except the latter can only create infinite iterators.

  private final def iterate[T](start: Option[T])(f: T => Option[T]): Iterator[T] = new Iterator[T] {
    private[this] var acc = start
    def hasNext = acc.isDefined
    def next() = acc match {
      case None => throw new NoSuchElementException("next on empty iterator");
      case Some(x) => val res = x ; acc = f(x) ; res
    }
  }

We can use it like this to create the child axis: Start with the first child, then go right node by node until you reach the last child:

  final def childAxis = iterate(downOpt(0))(_.rightOpt)

The descendant-or-self axis consists of the current node plus the descendant-or-self axes of all children:

  final def descendantOrSelfAxis: Iterator[NodeLoc] =
    Iterator.single(this) ++ childAxis.flatMap(_.descendantOrSelfAxis)

And the descendant axis consists only of the descendant-or-self axes of all children, without the current node:

  final def descendantAxis = childAxis.flatMap(_.descendantOrSelfAxis)

The parent, ancestor-or-self and ancestor axes work similarly:

  final def parentAxis = upOpt.iterator

  final def ancestorOrSelfAxis: Iterator[NodeLoc] =
    Iterator.single(this) ++ upOpt.iterator.flatMap(_.ancestorOrSelfAxis)

  final def ancestorAxis = upOpt.iterator.flatMap(_.ancestorOrSelfAxis)

The following-sibling and preceding-sibling axes are straight-forward:

  final def followingSiblingAxis = iterate(rightOpt)(_.rightOpt)

  final def precedingSiblingAxis = iterate(leftOpt)(_.leftOpt)

For the following and preceding axes we need to define some helper methods for moving to the following or preceding node in document order:

  final def followingAxis = iterate(followingOpt)(_.followingOpt)

  final def followingOpt: Option[NodeLoc] =
    downOpt(0) orElse rightOutOpt

  private final def rightOutOpt: Option[NodeLoc] =
    rightOpt orElse upOpt.flatMap(_.rightOutOpt)

  final def precedingAxis = iterate(precedingOpt)(_.precedingOpt)

  final def precedingOpt: Option[NodeLoc] =
    leftOpt.map(n => n.downLastTransitiveOpt.getOrElse(n)) orElse upOpt

  private final def downLastTransitiveOpt: Option[NodeLoc] =
    downLastOpt.flatMap(_.downLastTransitiveOpt)

  final def downLastOpt = {
    val ch = node.child
    if(ch.isEmpty) None
    else Some(NodeLoc(ch.head, Hole(ch.reverse.toList.tail, this, Nil)))
  }

And finally the trivial self axis:

  final def selfAxis = Iterator.single(this)

Methods \ and \\ for finding all child or descendant elements with a given name can be defined by filtering the child and descendant axes:

  final def \ (Name: String) =
    for(n @ NodeLoc(Elem(_, Name, _, _, _*), _) <- childAxis) yield n

  final def \\ (Name: String) =
    for(n @ NodeLoc(Elem(_, Name, _, _, _*), _) <- descendantAxis) yield n

You can find the complete source code, including more convenience methods and a short demo here.

Build system

Builds against newer versions of Scala 2.8 have been going well thanks to sbt 0.6 which separates the Scala version used by the build system from the version used by the project being built. You need to use at least version 0.6.7 of the new xsbt launcher to build ScalaQuery. Other dependencies are downloaded automatically by sbt. I wrote an implementation of sbt’s test interface for JUnit which you can find here (binaries are here on scala-tools.org). It allows you to run ScalaQuery’s JUnit test cases from sbt with the test command.

All published artifacts now contain the Scala version in the artifact ID. You can find the latest ScalaQuery snapshot in scala-tools.org’s snapshots repository as:

groupId:

com.novocode

artifactId:

scala-query_2.8.0.Beta1-RC4

version:

1.0.0-SNAPSHOT

JDBC escape syntax

ScalaQuery now uses JDBC escape syntax and scalar functions where possible for better portability between DB engines:

• String column concatenation (with the ++ operator) and the startsWith and endsWith methods on String columns have been moved up to BasicProfile.
• Escape characters for LIKE expressions are added to the queries.
• You can add other functions to be called with the {fn ...} syntax through SimpleScalarFunction() and SimpleScalarFunction.nullary().
• Various scalar functions are supported: CONVERT, USER, DATABASE, CURDATE, CURTIME, PI, MOD, ABS, CEILING, FLOOR, SIGN, DEGREES, RADIANS, IFNULL, UCASE, LCASE, LTRIM, RTRIM.
• Literals for Date, Time and Timestamp values use the JDBC escape syntax.
• Explicit inner, left outer, right outer and full outer joins are supported. For example:

  val q = for {
    Join(c,p) <- Categories innerJoin Posts on (_.id is _.category)
    _ <- Query orderBy p.id
  } yield p.id ~ c.id ~ c.name ~ p.title

Complex inserts

Queries and columns can now be inserted into tables (using SQL’s INSERT...SELECT syntax). This allows you to use scalar functions when inserting individual records and to copy data produced by a query. For example:

  val q2 = for(s <- Src1 if s.id <= 2) yield s
  Dst2.insert(q2)

Various changes

• String columns are created as VARCHAR(254) by default (except in H2 where no size is needed).
• The operators === and != can be used instead of is and isNot. They have the proper precedence when used with other operators like && and ||.

]]> http://szeiger.de/blog/2009/12/21/new-scala-query-features/feed/ 2 http://szeiger.de/blog/2009/09/27/a-scala-query-update/ http://szeiger.de/blog/2009/09/27/a-scala-query-update/#comments Sun, 27 Sep 2009 00:03:30 +0000 Stefan Zeiger http://szeiger.de/?p=35 Since my last post about ScalaQuery one month ago I have wasted quite some time trying to implement the OptionMapper type (which is used to make operations on columns interoperable between base types and Option types) in a way which does not require 2^n implicit functions and 2*n+2 type parameters for operations with n operands. The composable OptionMapper itself is not hard to write (see NewOptionMapper here) but it requires more type fidelity than ScalaQuery offers at the moment: Either Projections need to preserve the subtypes of Columns they are storing, or the required types have to be reconstructed from implicit values where they are needed. You can find my failed attempts in the projection-types and new-option-mapper branches. The first one might actually work once Scala bug #2346 is resolved. The second one would require a much more powerful type inferencer. For now, I just added an OptionMapper3 for 3 parameters (which is needed by the BETWEEN operator).

There are also some interesting new features in ScalaQuery:

Mapped Projections

You can now create a bidirectional mapping of a Projection with the <> operator. This is especially useful for a table’s “*” projection. The <> operator takes a function from the projection tuple type T (either as a tuple or as multiple parameters) to the mapped type R, and a function from R back to Some[T]. The asymmetry with the unnecessary Some wrapper is deliberate, matching exactly the apply und unapply methods of a case class. For example, here is a simple User class and table:

  case class User(first: String, last: String)

  object Users extends Table[User]("users") {
    def first = column[String]("first", O NotNull)
    def last = column[String]("last", O NotNull)
    def * = first ~ last <> (User, User.unapply _)
  }

Whenever you perform an insert, update or query directly on such a table (instead of a projection of individual columns), you can use the mapped type:

  Users.insert(User("Stefan", "Zeiger"))
  val someUsers: List[User] = Users.where(_.first startsWith "S").list

Finders

The new convenience method Table.createFinderBy allows you to create a simple finder query template which selects values from a table by matching on a single column, e.g.:

  val findUserByFirstName = Users.createFinderBy(_.first)

This is equivalent to:

  val findUserByFirstName = for {
    first <- Parameters[String]
    u <- Users if u.first is first
  } yield u

Updatable ResultSets

JDBC allows you to modify a ResultSet which was created with the CONCUR_UPDATABLE result set concurrency. ScalaQuery now offers a high-level interface to this functionality with the mutate method, e.g.:

  val q1 = for(u <- Users if u.last.is("Simpson") || u.last.is("Bouvier")) yield u
  q1.mutate { m =>
    if(m()._3 == "Bouvier") m() = m().copy(_3 = "Simpson")
    else if(m()._2 == "Homer") m.delete()
    else if(m()._2 == "Bart") m.insert((None, "Lisa", "Simpson"))
  }

Statement Options

The new ResultSetType, ResultSetConcurrency and ResultSetHoldability classes allow you to request the corresponding JDBC options when ScalaQuery creates a PreparedStatement, e.g.:

  myDB withSession {
    ResultSetType.ScrollInsensitive {
      // All database calls in this block use TYPE_SCROLL_INSENSITIVE
    }
  }

Or if you don't want to use the implicit threadLocalSession:

  myDB withSession { s1 =>
    ResultSetType.ScrollInsensitive(s1) { s2 =>
      // All database calls on s2 use TYPE_SCROLL_INSENSITIVE
    }
  }

The default for all three options is Auto which gives you values suitable for the invoker method you are calling (e.g. ResultSetConcurrency.Updatable for mutate and ResultSetConcurrency.ReadOnly for all other methods).

Build System

I have rewritten most of the test classes as proper unit tests (using JUnit 4) with assertions. I'd like to investigate ScalaTest and specs further (possibly together with ScalaCheck) in the future to replace JUnit but the binary incompatibilities between different Scala 2.8 snapshot builds would only complicate the build process right now. If you build with sbt, you still need to download a suitable Scala 2.8 snapshot yourself and set the paths in the properties because the build is done in forking mode. This also means that you cannot use the "test" command to run the unit tests from sbt at the moment.

H2 and JUnit are declared as test dependencies in the sbt project definition and the Eclipse build path refers to the local copies in lib_managed. If you want to build ScalaQuery with Eclipse (as I usually do), just run "sbt update" once to pull in the required JARs.

I am using FreeMarker templates and the FMPP tool to create repetitive code for the Projection and Parameters classes. This is not yet integrated into the sbt build process. You can use the supplied Eclipse launcher instead (after downloading FMPP and pointing the launcher to the correct path). The generated sources are checked in, so you only need to do this if you want to change the templates and rebuild the Scala sources.

]]> http://szeiger.de/blog/2009/09/27/a-scala-query-update/feed/ 14 http://szeiger.de/blog/2009/08/27/profiles-drivers-and-more/ http://szeiger.de/blog/2009/08/27/profiles-drivers-and-more/#comments Thu, 27 Aug 2009 15:07:47 +0000 Stefan Zeiger http://szeiger.de/?p=34 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.

]]> http://szeiger.de/blog/2009/08/27/profiles-drivers-and-more/feed/ 2 http://szeiger.de/blog/2009/08/11/removing-libraries-and-homegroup-icons-from-the-windows-7-desktop/ http://szeiger.de/blog/2009/08/11/removing-libraries-and-homegroup-icons-from-the-windows-7-desktop/#comments Tue, 11 Aug 2009 16:56:25 +0000 Stefan Zeiger http://szeiger.de/?p=33 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!

]]> http://szeiger.de/blog/2009/08/11/removing-libraries-and-homegroup-icons-from-the-windows-7-desktop/feed/ 18 http://szeiger.de/blog/2009/08/06/efficient-parameterized-queries-in-scala-query/ http://szeiger.de/blog/2009/08/06/efficient-parameterized-queries-in-scala-query/#comments Thu, 06 Aug 2009 17:22:04 +0000 Stefan Zeiger http://szeiger.de/?p=32 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.

]]> http://szeiger.de/blog/2009/08/06/efficient-parameterized-queries-in-scala-query/feed/ 4 http://szeiger.de/blog/2009/07/22/scala-query-for-different-scala-versions/ http://szeiger.de/blog/2009/07/22/scala-query-for-different-scala-versions/#comments Wed, 22 Jul 2009 20:12:11 +0000 Stefan Zeiger http://szeiger.de/?p=31 I have just created a new scala-2.7 branch for ScalaQuery. My original plan was to target only Scala 2.8 but since I’ve made lots of progress during the last few weeks and I’ve seen increased interest in ScalaQuery, I tried to build it with 2.7.5.

I had to change the semantics of SimpleFunction and SimpleBinaryOperator for 2.7 but I prefer the new version anyway, so it went into the main line. The code on the scala-2.7 branch is currently identical to the master branch, except for the test classes which are different in two regards:

• Although the Scala Language Specification mandates that the part left to the “<-” in a for comprehension is a pattern, the wildcard pattern “_” does not work in 2.7. I have changed it to a dummy variable named “__“.
• The type inferencer in 2.7 cannot infer the correct type for the implicit OptionMapper objects. OptionMapper[_,_,_,_] has four type parameters, the last one being used for the return type of functions which use the mapper, so it is not yet known when looking for an implicit mapper and gets inferred as Nothing. Scala 2.8 apparently knows that this type parameter is undetermined, finds the single matching implicit object for the other three parameters and then fills in the fourth. The type-correct interoperability of option and non-option types in ScalaQuery relies heavily on the improved type inferencer and I don’t see any way of making it work nicely with 2.7. The work-around is to add type annotations to the boolean operators, e.g. a && b might become a.&&[Boolean,Option[Boolean]](b). Yuck!

I’m still focused on Scala 2.8 as a target platform and will probably not spend much time integrating new features into the scala-2.7 branch but contributions are always welcome.

]]> http://szeiger.de/blog/2009/07/22/scala-query-for-different-scala-versions/feed/ 2 http://szeiger.de/blog/2009/07/17/implicits-on-implicits/ http://szeiger.de/blog/2009/07/17/implicits-on-implicits/#comments Thu, 16 Jul 2009 22:51:55 +0000 Stefan Zeiger http://szeiger.de/?p=30 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.

]]> http://szeiger.de/blog/2009/07/17/implicits-on-implicits/feed/ 0