Groovy 1.8 DSL: Google Spreadsheet Query Language

The development in AppSatori is generally all about integrating applications with some Google API. One of your current project is about exporting Google Spreadsheet contents as XML. The XML part is easy thanks to Groovy MarkupBuilder. Fetching the data from Google Spreadsheet is much more cumbersome because standard API is a little bit verbose. So the decision was made to wrap the API with SQL-like declarative domain specific language (DSL).
Creating DSL in Groovy is very easy since 1.8 release where GEP 3 - Command Expression based DSL was introduced. No more commas as method accessors are needed if you alternate method calls with method parameters. Following code snippet shows the DSL in action the difference between Groovy 1.7 and 1.8 release.
GSQLExample

First thing you must think about when creating DSL is the grammar. Our desired Google Spreadsheet Query Language wraps Google's ListQuery class and could be defined by following grammar:

select [all|'<column name>'*|<position>*]
from 'spreadsheet' [ sheet 'worksheet']
[where 'spreadsheet query']
[contains 'fulltext']
[order by column [<position>|'<column name>]]
[sort [asc|desc]]
[limit <limit>]
[offeset <offset>]

The second step is to write tests for all supported use cases. Here is one example:
Order by column

The next step is to create the entry point to start building the query. In the case of GSQL it is the select method of GSQL class which is defined statically in GSQL class and star-imported to any class or script which wants to use the DSL. Also some keywords like all or desc are defined as static properties of the GSQL class for the same reasons.

GSQL

As you could already mention from previous snippets the query is just ordinary POGO which collects the query parameters. In fact there are three of them to force the order of the commands. The only DSL magic is to add keyword(value) method to the query builder class. You should notice that these methods also constraints the expected value by type. The querying itself happens in the build method of the QueryBuilder class.
SelectBuilder

QueryBuilder

OrderBuilder

The GSQL is still work-in-progress. In future it might support also some data manipulation language for executing updated etc. There are also few things which must be done unless the business users could use the language safely. The most important one is to create secured shell which will prevent writing malicious code in the queries.