Hi

this is just a rough hack demonstrating a proof of concept for a "SQL::DSL".

(and a general pattern for designing complex domain specific languages)

I'm currently refactoring it out into clean sub-modules but wanted to show something already.

The demonstrated ideas here are already sufficiently complex to discuss.

(The implementation of named operators like BETWEEN and a JOIN mechanism are subject of threads to come)

given this input:

my ($user, $workhrs0, $geo0, $workhrs1, $geo1) = ('NWIGER', '20', 'ASIA', '50', 'EURO'); query { package Table; WHERE ( ANDS ( user == $user, ORS ( ANDS ( workhrs > $workhrs0 , geo == 20 ), ORS ( $workhrs1 < workhrs, geo == $geo1 ) ) ) ); };

will the function query return an AST (abstract syntax tree) of nested "SQL::DSL" objects, which can be rendered into a target dialect like MySQL, Oracle ... (or maybe even SQL::Abstract or DBIx code ).

Some basic ideas are:

Here the steps in the middle:

=== B::Deparse of the Code: { package Table; use warnings; use strict; use feature 'say'; WHERE(ANDS(user() == $user, ORS(ANDS(workhrs() > $workhrs0, geo() +== 20), ORS($workhrs1 < workhrs(), geo() == $geo1)))); } at d:/Users/lanx/vm_share/perl/Talks/DSL/2018_GPW/exp/SQL_abstract.p +l line 51. === Tidy of deparsed Perl-code: { package Table; use warnings; use strict; use feature 'say'; WHERE( ANDS( user() == $user, ORS( ANDS( workhrs() > $workhrs0, geo() == 20 ), ORS( $workhrs1 < workhrs(), geo() == $geo1 ) ) ) ); } === Abstract Syntax Tree (simplified): :'WHERE' is ::Clause :'ANDS' is ::Joiner :'=' is ::Infix :'user' is ::Column :'NWIGER' is ::Placeholder ["\n ", "NWIGER"] :'ORS' is ::Joiner :'ANDS' is ::Joiner :'>' is ::Infix :'workhrs' is ::Column :'20' is ::Placeholder ["\n ", 20] :'=' is ::Infix :'geo' is ::Column :'20' is SCALAR :'ORS' is ::Joiner :'<' is ::Infix :'50' is ::Placeholder ["\n ", 50] :'workhrs' is ::Column :'=' is ::Infix :'geo' is ::Column :'EURO' is ::Placeholder ["\n ", "EURO"] === Produced SQL: WHERE ( user = ? AND ( ( workhrs > ? AND geo = 20 ) OR ( ? < workhrs OR geo = ? ) ) ) at d:/Users/lanx/vm_share/perl/Talks/DSL/2018_GPW/exp/SQL_abstr +act.pl line 59.

here the code

Any comments so far? :)

Cheers Rolf
(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery FootballPerl is like chess, only without the dice