Hi,
with regards to any moves to this topic it might be good to keep me in
the loop, sinc the Groovy framework I have developed (and are working on
getting open sourced) at my organization supplies partially overlapping
/ partially orthogonal functionality to what is being proposed here.
It's main focus is convenient/batch creation of parametrized SQL /
PL/SQL code, but it also contains a part based on (Groovy Sql class)
that simplifies communication with the database.
Simple (constructed) example:
class PERSON_Table {
@Lazy static final it = new PERSON_Table('PERSON','pe')
static PERSON_Table getPERSON() { it }
final ID = colThis('ID', SqlTypes.NUMBER_ID)
final FIRST_NAME = colThis('FIRST_NAME', SqlTypes.VARCHAR(32))
final LAST_NAME = colThis('LAST_NAME', SqlTypes.VARCHAR(64))
final SOURCE_ID = colThis('SOURCE_ID', SqlTypes.NUMBER_ID)
}
final p0 = Table.instance(PERSON) // statically imported
final p1 = instance(p0) // statically imported
final selectCols = (p0.ID + p0.FIRST_NAME + p0.LAST_NAME +
p.SOURCE_ID.val(BindValue.bindVal(1234567))).sorted
final personsWithChangedLastNameSql = "select $selectCols from $p0
where exists (select 1 from $p1 where ${p1.ID.isEqualTo(p0.ID)}) and
$p1.LAST_NAME <> $p0.LAST_NAME) "
final sqe = SqlExecutor.create(Schemas.PROD)
final maxNrRows = 5
sqe.eachRow(personsWithChangedLastNameSql, 0, maxNrRows) { println
"Last Name: ${p0.LAST_NAME.getString(it)}" }
assert sqe.numberRowsInResultSet(personsWithChangedLastNameSql) < 30
assert sqe.numberRowsInTable(p0) > 10000
final personId = sqe.singleValue("select $p0.ID from $p0 where
$p0.ZUNAME = ${bindVal(100000)}"))
A short overview of relevant classes:
Table: Represents a tabular database object (TABLE or VIEW), with Column
member fields.
Column: A Table column member. Used for writing SQL / PL/SQL code using
Table instances.
SqlBuilder: Supplies SQL constructs for a sepcific RDBMS (currently Oracle)
SqlExecutor: Encapsulates a database connection (Groovy Sql object) +
methods to execute queries on the RDBMS. Can be created from DataSource,
Schema, etc instances.
Database: An abstract representation of an RDBMS: name, domain, login
information. Note: SqlExecutor objects are used to issue
commands/queries against the RDBMS.
BindValue: BindValue instances embedded in a SQL GString will be treated
as bind values when issuing queries through a SqlExecutor, all other
embedded objects will be converted to their string representation.
Schema: A schema within a Database.
SqlCommandsContainer: Convenience class to construct formatted SQL /
PL/SQL outside of using Groovy multiline GStrings. Supplies some DSL
features, such as supplying PL/SQL loops or if-statement bodies inside
of a Groovy Closure-block.
PlSqlCommandsContainer: A PL/SQL commands block wrapping
SqlCommandsContainer head and body members, with supprt for Variable
object creation.
View: Represents a database view. Based on Table, adding functionality
to supply a query and autocreate the CREATE VIEW statement from that.
Function / Procedure: Database functions and procedures,
Package: An Oracle package.
Variable: A variable in a Function / Procedure / Package.
Parameter: A parameter for a Function or Procedure.
In addition Synonym, DatabaseLink, etc.
Cheers,
mg
On 12.10.2018 20:55, adithyank wrote:
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`
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
community!
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
