Squeryl: Run query explicitly

Question

When I create a query in squeryl, it returns a Query[T] object. The query was not yet executed and will be, when I iterate over the Query object (Query[T] extends Iterable[T]).

Around the execution of a query there has to be either a transaction{} or a inTransaction{} block.

I'm just speaking of SELECT queries and transactions wouldn't be necessary, but the squeryl framework needs them.

I'd like to create a query in the model of my application and pass it directly to the view where a view helper in the template iterates over it and presents the data. This is only possible when putting the transaction{} block in the controller (the controller includes the call of the template, so the template which does the iteration is also inside). It's not possible to put the transaction{} block in the model, because the model doesn't really execute the query.

But in my understanding the transaction has nothing to do with the controller. It's a decision of the model which database framework to use, how to use it and where to use transactions. So I want the transaction{} block to be in the model.

I know that I can - instead of returning the Query[T] instance - call Iterable[T].toList on this Query[T] object and then return the created list. Then the whole query is executed in the model and everything is fine. But I don't like this approach, because all the data requested from the database has to be cached in this list. I'd prefer a way where this data is directly passed to the view. I like the MySql feature of streaming the result set when it's large.

Is there any possibility? Maybe something like a function Query[T].executeNow() which sends the request to the database, is able to close the transaction, but still uses the MySQL streaming feature and receives the rest of the (selected and therefore fixed) result set when it's accessed? Because the result set is fixed in the moment of the query, closing the transaction shouldn't be a problem.

Answer 1

The general problem that I see here is that you try to combine the following two ideas:

• lazy computation of data; here: database results

• hiding the need for a post-processing action that must be triggered when the computation is done; here: hiding from your controller or view that the database session must be closed

Since your computation is lazy and since you are not obliged to perform it to the very end (here: to iterate over the whole result set), there is no obvious hook that could trigger the post-processing step.

Your suggestion of invoking Query[T].toList does not exhibit this problem, since the computation is performed to the very end, and requesting the last element of the result set can be used as a trigger for closing the session.

That said, the best I could come up with is the following, which is an adaptation of the code inside org.squeryl.dsl.QueryDsl._using:

class IterableQuery[T](val q: Query[T]) extends Iterable[T] {
  private var lifeCycleState: Int = 0
  private var session: Session = null
  private var prevSession: Option[Session] = None

  def start() {
    assert(lifeCycleState == 0, "Queries may not be restarted.")
    lifeCycleState = 1

    /* Create a new session for this query. */
    session = SessionFactory.newSession

    /* Store and unbind a possibly existing session. */
    val prevSession = Session.currentSessionOption
    if(prevSession != None) prevSession.get.unbindFromCurrentThread

    /* Bind newly created session. */
    session.bindToCurrentThread
  }

  def iterator = {
    assert(lifeCycleState == 1, "Query is not active.")
    q.toStream.iterator
  }

  def stop() {
    assert(lifeCycleState == 1, "Query is not active.")
    lifeCycleState = 2

    /* Unbind session and close it. */
    session.unbindFromCurrentThread
    session.close

    /* Re-bind previous session, if it existed. */
    if(prevSession != None) prevSession.get.bindToCurrentThread
  }
}

Clients can use the query wrapper as follows:

var manualIt = new IterableQuery(booksQuery)
manualIt.start()
manualIt.foreach(println)
manualIt.stop()
//      manualIt.foreach(println) /* Fails, as expected */

manualIt = new IterableQuery(booksQuery) /* Queries can be reused */
manualIt.start()
manualIt.foreach(b => println("Book: " + b))
manualIt.stop()

The invocation of manualIt.start() could already be done when the object is created, i.e., inside the constructor of IterableQuery, or before the object is passed to the controller.

However, working with resources (files, database connections, etc.) in such a way is very fragile, because the post-processing is not triggered in case of exceptions. If you look at the implementation of org.squeryl.dsl.QueryDsl._using you will see a couple of try ... finally blocks that are missing from IterableQuery.