High Level DSL for Database Querying

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

High Level DSL for Database Querying

adithyank
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
Thanks,

Adithyan K
India
Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

adithyank
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
Thanks,

Adithyan K
India
MG
Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

MG
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
>

Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

adithyank
Hi MG,

The objective of my proposal is to make "Database querying and result
processing" more handy also for non developers. i.e., mostly people who can
write shell scripts or some simple perl scripts.

In that view, I proposed a wrapper DSL overy groovy's `Sql` class.

I feel that your idea is needs a bit more work for the script writers (may
be developers or just script writers) as it has some more concepts that
people have to understand.

Any thoughts on this ? How should I proceed now?

Thanks,

K Adithyan
India






--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
Thanks,

Adithyan K
India
MG
Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

MG
Hi K,

as I said, your proposal overlaps in some areas with what my framework
does, so I was suggesting you keep the functionality and naming
conventions I listed in my last mail in mind when designing the DSL.
My framework is more geared towards developers, who need to be able to
batch create RDBMS objects, such as e.g.
packages/function/procedures/views for different DB users (e.g. exposing
different columns or using different filter criteria in a select
statement depending on the need-to-know of the appliction behind the DB
user), and who want Intellisense support when writing SQL queries,
working with table/view column collections, etc.

Apart from that, if all you want to supply is a simple DSL over Groovy
Sql which is mostly for non-developers / scripting use (so no
Intellisense I assume), then I personally would suggest you flesh it
out, then ask for some feed back and then go ahead implementing a first
rough version :-)

Cheers,
mg


On 20.10.2018 20:38, adithyank wrote:

> Hi MG,
>
> The objective of my proposal is to make "Database querying and result
> processing" more handy also for non developers. i.e., mostly people who can
> write shell scripts or some simple perl scripts.
>
> In that view, I proposed a wrapper DSL overy groovy's `Sql` class.
>
> I feel that your idea is needs a bit more work for the script writers (may
> be developers or just script writers) as it has some more concepts that
> people have to understand.
>
> Any thoughts on this ? How should I proceed now?
>
> Thanks,
>
> K Adithyan
> India
>
>
>
>
>
>
> --
> Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
>

Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

paulk_asert
The other thing worth thinking about is comparing what you propose with what is available in existing Java libraries and directed at Java developers. Jooq comes to mind:


Be worth think about whether a tiny DSL (if needed) above that is a viable alternative to achieve what you are after.

Cheers, Paul.


On Mon, Oct 22, 2018 at 7:39 AM MG <[hidden email]> wrote:
Hi K,

as I said, your proposal overlaps in some areas with what my framework
does, so I was suggesting you keep the functionality and naming
conventions I listed in my last mail in mind when designing the DSL.
My framework is more geared towards developers, who need to be able to
batch create RDBMS objects, such as e.g.
packages/function/procedures/views for different DB users (e.g. exposing
different columns or using different filter criteria in a select
statement depending on the need-to-know of the appliction behind the DB
user), and who want Intellisense support when writing SQL queries,
working with table/view column collections, etc.

Apart from that, if all you want to supply is a simple DSL over Groovy
Sql which is mostly for non-developers / scripting use (so no
Intellisense I assume), then I personally would suggest you flesh it
out, then ask for some feed back and then go ahead implementing a first
rough version :-)

Cheers,
mg


On 20.10.2018 20:38, adithyank wrote:
> Hi MG,
>
> The objective of my proposal is to make "Database querying and result
> processing" more handy also for non developers. i.e., mostly people who can
> write shell scripts or some simple perl scripts.
>
> In that view, I proposed a wrapper DSL overy groovy's `Sql` class.
>
> I feel that your idea is needs a bit more work for the script writers (may
> be developers or just script writers) as it has some more concepts that
> people have to understand.
>
> Any thoughts on this ? How should I proceed now?
>
> Thanks,
>
> K Adithyan
> India
>
>
>
>
>
>
> --
> Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
>

MG
Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

MG
Isn't jOOQ no longer completely OS: http://www.jooq.org/legal/licensing#databases ?

Quote from their main page:
"Use this free edition with your favourite Open Source DB using the popular Apache Software License 2.0!"


On 22.10.2018 01:28, Paul King wrote:
The other thing worth thinking about is comparing what you propose with what is available in existing Java libraries and directed at Java developers. Jooq comes to mind:


Be worth think about whether a tiny DSL (if needed) above that is a viable alternative to achieve what you are after.

Cheers, Paul.


On Mon, Oct 22, 2018 at 7:39 AM MG <[hidden email]> wrote:
Hi K,

as I said, your proposal overlaps in some areas with what my framework
does, so I was suggesting you keep the functionality and naming
conventions I listed in my last mail in mind when designing the DSL.
My framework is more geared towards developers, who need to be able to
batch create RDBMS objects, such as e.g.
packages/function/procedures/views for different DB users (e.g. exposing
different columns or using different filter criteria in a select
statement depending on the need-to-know of the appliction behind the DB
user), and who want Intellisense support when writing SQL queries,
working with table/view column collections, etc.

Apart from that, if all you want to supply is a simple DSL over Groovy
Sql which is mostly for non-developers / scripting use (so no
Intellisense I assume), then I personally would suggest you flesh it
out, then ask for some feed back and then go ahead implementing a first
rough version :-)

Cheers,
mg


On 20.10.2018 20:38, adithyank wrote:
> Hi MG,
>
> The objective of my proposal is to make "Database querying and result
> processing" more handy also for non developers. i.e., mostly people who can
> write shell scripts or some simple perl scripts.
>
> In that view, I proposed a wrapper DSL overy groovy's `Sql` class.
>
> I feel that your idea is needs a bit more work for the script writers (may
> be developers or just script writers) as it has some more concepts that
> people have to understand.
>
> Any thoughts on this ? How should I proceed now?
>
> Thanks,
>
> K Adithyan
> India
>
>
>
>
>
>
> --
> Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
>


Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

paulk_asert
I am not a lawyer but as far as I know they offer commercial support as well as completely free via ASL2. But yes, a little bit different to normal. In any case, I was suggesting that it as much a source of inspiration than the only alternative to look at.

Cheers, Paul.

On Mon, Oct 22, 2018 at 9:58 AM MG <[hidden email]> wrote:
Isn't jOOQ no longer completely OS: http://www.jooq.org/legal/licensing#databases ?

Quote from their main page:
"Use this free edition with your favourite Open Source DB using the popular Apache Software License 2.0!"


On 22.10.2018 01:28, Paul King wrote:
The other thing worth thinking about is comparing what you propose with what is available in existing Java libraries and directed at Java developers. Jooq comes to mind:


Be worth think about whether a tiny DSL (if needed) above that is a viable alternative to achieve what you are after.

Cheers, Paul.


On Mon, Oct 22, 2018 at 7:39 AM MG <[hidden email]> wrote:
Hi K,

as I said, your proposal overlaps in some areas with what my framework
does, so I was suggesting you keep the functionality and naming
conventions I listed in my last mail in mind when designing the DSL.
My framework is more geared towards developers, who need to be able to
batch create RDBMS objects, such as e.g.
packages/function/procedures/views for different DB users (e.g. exposing
different columns or using different filter criteria in a select
statement depending on the need-to-know of the appliction behind the DB
user), and who want Intellisense support when writing SQL queries,
working with table/view column collections, etc.

Apart from that, if all you want to supply is a simple DSL over Groovy
Sql which is mostly for non-developers / scripting use (so no
Intellisense I assume), then I personally would suggest you flesh it
out, then ask for some feed back and then go ahead implementing a first
rough version :-)

Cheers,
mg


On 20.10.2018 20:38, adithyank wrote:
> Hi MG,
>
> The objective of my proposal is to make "Database querying and result
> processing" more handy also for non developers. i.e., mostly people who can
> write shell scripts or some simple perl scripts.
>
> In that view, I proposed a wrapper DSL overy groovy's `Sql` class.
>
> I feel that your idea is needs a bit more work for the script writers (may
> be developers or just script writers) as it has some more concepts that
> people have to understand.
>
> Any thoughts on this ? How should I proceed now?
>
> Thanks,
>
> K Adithyan
> India
>
>
>
>
>
>
> --
> Sent from: http://groovy.329449.n5.nabble.com/Groovy-Dev-f372993.html
>


MG
Reply | Threaded
Open this post in threaded view
|

Re: High Level DSL for Database Querying

MG

When I evaluated what to base my framework on, I did not find much that was available and looked interesting, except jOOQ - which at the time started steering away from being pure OS (so was a no go for us).

 

The jOOQ webpage licensing FAQ has the following to say on the topic:

Can I use "jOOQ Open Source Edition" also in commercial software?
A: Yes, as any Apache-licensed software, jOOQ can be used in commercial software.

Q: Can I use "jOOQ Open Source Edition" also with commercial databases?
A: It is unlikely that you will get the "jOOQ Open Source Edition" to work properly with a commercial database that is not supported by the "jOOQ Open Source Edition". We strongly suggest purchasing commercial licenses.

 

Cheers,

mg