groovy-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MG <mg...@arscreat.com>
Subject Re: High Level DSL for Database Querying
Date Sun, 14 Oct 2018 17:44:53 GMT
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
>


Mime
View raw message