Team,
As suggested by Jochen Theodorou in this
<http://groovy.329449.n5.nabble.com/New-DSLs-in-the-groovy-platform-itself-for-more-script-like-use-cases-td5750522.html>
post, I have created this topic for `Database DSL`
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
}
printAsTable
}
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: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
