OpenGamma release ElSql tool

While developing its open source financial analytics platform, OpenGamma has created a number of other standalone tools and libraries. The company has released its latest standalone tool, ElSql, a Java library designed to manage SQL, which takes an alternative approach to the task of interfacing Java code to SQL relational databases.

OpenGamma's Stephen Colebourne introduced the new library in a blog post where he explains that the typical approaches have either been to go down the path of using Object Relational Mappers (ORMs) such as Hibernate or JPA, or going with a lightweight approach such as fluent API libraries to compose commands or even simple string concatenation.

ElSql takes the problem and externalises it with the creation of ElSql files which contain definitions of SQL commands that can be called up, written in a DSL (domain-specific language). The command definition .elsql file can be called up from within the similarly named .java code file and the commands instantiated, populated and executed. For situations where specific changes are needed, for example, for a change of backend database, an override file can also be created which overrides the default .elsql file. ElSql tackles issues such as the differences between SQL's LIKE and "=" where wildcards are involved, the dynamic construction of WHERE/AND clauses, and the paging of results. An example of the ElSql DSL is below:

 -- an example comment
 @NAME(SelectBlogs)
   @PAGING(:paging_offset,:paging_fetch)
     SELECT @INCLUDE(CommonFields)
     FROM blogs
     WHERE id = :id
       @AND(:date)
         date > :date
       @AND(:active)
         active = :active
     ORDER BY title, author
@NAME(CommonFields)
   title, author, content 

This example includes two named blocks, set up for paged results and a progressively constructed WHERE/AND clause. A plugin configuration for the underlying database can take care of the database differences and currently supports basic SQL, PostgreSQL, HSQL, MySQL, SQL Server 2008 and Vertica.

The developers point out that ElSql does require that developers know SQL and write the logic to convert objects to and from JDBC. With that in mind, it is not a solution for everyone, but they suggest that developers who want to fully control their SQL, potentially over multiple platforms, may find it offers a solution to their problems. ElSql is Apache 2.0 licensed and is available from the project's GitHub repository or via Maven "com.opengamma/elsql".

(djwm)