Using SQL Views With Grails

The Problem
In a recent grails backend project for a conference management system I had a complex synchronization where the initial sync could be quite large. As the clients were iPads and could have connection and bandwidth constraints so the huge initial sync must happen in chunks. So if a client due to connectivity problems fails to receive a chunk it can request it again and not too much of data has to be transferred again. As the iPad knows about it's connection quality the app could even adjust the chunk size to its needs. Large chunks (causing less overhead) when the connection is good and smaller chunks (less data to resend) when the connection is poor. The idea was to use SQL views to create a snapshot of the database and use HQL queries against the views with all it's paging convenience for simple chunk handling code.

The problem was that GORM has no support for views. If you query a view GORM cannot map it to anything as there is no mapping defined. Moreover as the views must have different names for each sync and GORM's mapping of domain classes to tables is defined static but I needed the mapping to be dynamic. That was a hard one to solve.

Creating SQL Views in Grails
That was rather something simple as groovy has support for SQL. So I just take a client ID to make the view's name unique.
The business logic (controller/service)

def dataSource // the Spring-Bean "dataSource" is auto-injected

@Secured([Role.ROLE_RECEPTIONIST])
def syncUsers = {
    def clientId = params.client
    long syncpoint = params.syncpoint ? params.syncpoint : 0) as Long)

    def roleAttendee = Role.findByAuthority(Role.ROLE_ATTENDEE)

    def db = new Sql(dataSource)
    def query = "CREATE VIEW syncview_user_"+clientId+" \
        AS SELECT u.* FROM user AS u, user_role AS ur \
        WHERE ur.user_id = u.id AND ur.role_id = $roleAttendee.id"
    db.execute(query)
}   

The hibernate interceptor (src/java)

import org.hibernate.EmptyInterceptor
import org.springframework.web.context.request.RequestContextHolder

public class DynamicTableNameInterceptor extends EmptyInterceptor {
 
 public String onPrepareStatement(String sql) {
  if(RequestContextHolder.getRequestAttributes()) {
   def params = RequestContextHolder.currentRequestAttributes().params
   println ">>>> $sql"
   println params
   //params.each { println it}
   sql = sql.replaceAll("from user ", "from syncview_user_"+params.client+" ")
   println sql
   println "------"
  }
  return sql //.replaceAll('_SCHEMA_', schema)
 }
}

resources.groovy:

beans = {
 entityInterceptor(DynamicTableNameInterceptor)
}