Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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:

  • SQL-Tables (here "Table") are realized as packages
  • These packages are limited to the scope of the surrounding code-block such that no namespace pollution occurs (a common problem with DSLs)
  • The Columns (here user) are realized as constants in this namespace returning "SQL::DSL::Column" objects
  • The operators are overloaded for Column objects and return "SQL::DSL::Operator" objects with nested Operand objects
  • Literal operands (like 20) are identified because they are readonly
  • Variable operands are identified and can be replaced by ? placeholders at render-time
  • actual values of the placeholders can be captured as variable references from the closure-vars and can be bound to the DBI->execute() later
  • "higher order" operations on nested operations just return the nested objects in a higher blessed container augmenting the AST
  • the rendering happens by walking the generated AST and calling a render methods on the encountered objects
  • the whole algorithm might look slow but we only need to run it once and memoize the result for later executions.
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


In reply to RFC: A DSL for SQL (part 1) by LanX

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2024-03-19 02:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found