groovy-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From adithyank <>
Subject Re: High Level DSL for Database Querying
Date Fri, 12 Oct 2018 18:55:26 GMT

As suggested by Jochen Theodorou in  this 
post, I have created this topic for `Database DSL` 

The methods given in this thread are some examples. We can either modify
them to generalize and also include more such methods, once accepted by the

To make groovy usable in the hands of non-developer community database 
querying is one of the important DSL use cases. 

With simple DSLs, they should be able to specify the work in declarative 
style, instead of sequence of lines of code that is comfortable for 
developers. Here, I have listed few simple DSL for DB querying. I am sure we 
will be able to add more such cases for very friendly programming 

DSL Script 

a. Simple Database querying 

Use Case : View the result or store as comma separated files. While storing, 
we can overload 'saveTo' method to enable configuring the field delimiter 
String, field enclosing String, pretty table formatting to have table like 
output (How query result are shown in mysql client), etc 

def db = rdbms(url, user, pwd) 

db.execute { 

   query "select........." 

   printAsTable //or 
   saveTo '/tmp/tableResult.csv' //couple of options can be given here to 
store with delimiter, field enclsoure, etc 

b. Simple Database querying & data transformation 

Use Case : After getting the query result, script writers may want to 
transform the data format of some column or they may want to store the 
inference instead of raw value. Such transformed results can be stored in 
file or displayed 

def db = rdbms(url, user, pwd) 

db.execute { 

    query "select totalmark from Marks" 

    transform { 

         forfield('total') { it > 50 ? 'PASS' : 'FAIL'} 

         forfield('updateTime') { new Date(it) } 

   printAsTable //or 
   saveTo '/tmp/tableResult.csv' 

c. Copy query result to another table 

Use Case : Such transformed query result data can be inserted to another 
table in another database or same database. I am aware that it can be easily 
done in the client of the database systems (i.e., SQLDeveloper or mysql 
client). But, we are bringing the Java/Groovy power in the data 
transformation area is what we bring to the table. 

def dbSource = rdbms(url1, user, pwd) 
def dbDestination = rdbms(url2, user, pwd) 

dbSource.execute { 

    query "select........." 

    transform { 

         forfield('total') { it > 50 ? 'PASS' : 'FAIL'} 

         forfield('updateTime') { new Date(it) } 

        //assuming table exists with required fields 
    copyTo(dbDestination, 'DEST_TABLE_NAME') {	

    batchSize 1000 


d. Delete rows from tables 

def db = rdbms(url, user, pwd) { 
def tables = ['Table1', 'Table2', ...] 

db.deleteRows(tables) //or 
db.deleteRows(new File('/tmp/tableNames.txt')) 

Implementation Detail 

1. rdbms(url, user, pwd, closure) method will be the `delegate` of that 
input closure and this method will return instance of `RDBMS` class. 
2. `RDBMS` class will have below methods 
    * query(String), query(String, closure) 
    * transform(Closure) : This closure will be delegated to 
TransformDelegate which will have 'forfield(fieldName, Closure 
transformFunction)' method 
    * copyTo(RDBMS dest, String toTableName, Closure copyToSpec) 
    * printAsTable 
    * printAsCsv, printAsTsv, printAsSv(delimiter), etc 
    * deleteRows(String... tableNames), deleteRows(File) 

Sent from:

View raw message