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.

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.

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.



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.


  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


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

    1. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

      Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.

      Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

      The Nodejs Projects Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

  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']

    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

    class QueryBuilder {
    String spreadsheet

    String worksheet
    String query
    String order

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

  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.

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

  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

  6. Hello ^^
    The season when the car winds are back.
    Are you all working hard to prepare for your visit to Korea? Beware of colds and beware of corona
    I came in to see your site and I liked a lot of useful and good content!
    I also run a site, so will you come and see it?
    I think I would be very grateful for your feedback. Bye^^
    My site address: 토토사이트

  7. Easily, the article is actually the best topic on this registry related issue. I fit in with your 먹튀검증사이트 and will eagerly look forward to your next updates. Just saying thanks will not just be sufficient, for the fantastic lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates

  8. I just want to let you know that I just check out your site and I find it very interesting and informative. 먹튀검증

  9. Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work 메이저사이트

  10. It is a completely interesting blog publish.I often visit your posts for my project's help about Diwali Bumper Lottery and your super writing capabilities genuinely go away me taken aback.Thank you a lot for this publish. 토토사이트