Tuesday, June 21, 2011

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.

5 comments:

  1. Would be nice to see how you going to control that all expressions are in correct order. In the current implementation there's no difference between:

    select all from "Spreadsheet" sheet "Worksheet" limit 10 offset 5

    and

    select all limit 10 sheet "Worksheet" from "Spreadsheet" offset 5

    ReplyDelete
  2. Good point Sergey! You cannot write

    select all limit 10 sheet "Worksheet" from "Spreadsheet" offset 5

    because only available method of "SelectBuilder" is "from" so the "from" expression must be always present and always must be placed after "select" expression. Other expressions could be called in any order because there is no so strong reason to make the implementation more complex to restrict the order.

    If you want to do so in your DSL the most elegant way I thought about is to let each command specify what methods could be called next.

    def nextAllowed = [...]

    QueryBuilder sort(Sort ascOrDesc){
    if(!('sort' in nextAllowed)) { throw new IllegalStateException('Sort is not allowed to be called now') }
    reverse = ascOrDesc == Sort.DESC
    nextAllowed = ['limit','offset']
    this
    }


    It would be also great to create an AST transormation instead of writing all the code over and over for each method. The builder class could be shorten to something like

    @DSL(entryMethods="select",maxInvocation=1)
    class QueryBuilder {
    @Required
    @Keyword("from")
    @NextMethods("sheet","where","contains",
    "order","sort","limit","offset")
    String spreadsheet

    @Keyword("sheet")
    String worksheet
    String query
    String order
    ...

    OrderBuilder order(By by){
    new OrderBuilder(queryBuilder: this)
    }
    }

    ReplyDelete
  3. I visited this platform today the number of facts and ideas given here are quite considerable and up to the level to my considerations, I'm convinced to see it again in the future to have better results. See essay paper for best Papers.

    ReplyDelete
  4. hey guys i would like to share that amazon gift card codes generator is really working .

    ReplyDelete
  5. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
    http://www.jualobataborsiherbal.com/ obat aborsi
    http://caramenggugurkankandungan.info/ cara menggugurkan kandungan
    http://www.jualobataborsiherbal.com/cara-menggugurkan-kandungan/ cara menggugurkan kandungan
    http://obataborsi59.com/ obat aborsi
    http://obataborsi59.com/cara-menggugurkan-kandungan-dengan-cepat-dan-aman/ cara menggugurkan kandungan
    http://obattelatdatangbulan.info/ obat telat datang bulan
    http://klinikobataborsi.com/ jual obat aborsi
    http://jualobatpenggugurkandungan.net/ obat penggugur kandungan
    http://tandatandakehamilan.net/ tanda tanda kehamilan

    ReplyDelete