[Dbix-class] first Muldis DB example code (was Re: SQL Generation with SQL::DB)

[Darren Duncan]

At 10:22 AM +0200 9/17/07, Mark Lawrence wrote:
>On Wed Sep 12, 2007 at 05:23:28PM +0100, Matt S Trout wrote:
>  > Sure. It's just a shame you didn't join this list earlier, you'd have seen
>>  the discussion where we started working on a common solution :)
>
>I've had a bit of a look but I can't find any outcomes of the
>discussions. Are there any design documents posted somewhere? Something
>about what the AST structure looks like? Or maybe Darren's Muldis::DB is
>intended to be the new 'it'? (By the way Darren, if you are listening,
>I've read the docs three times through and still don't quite get what
>'it' is. An Intro with _Examples_ is badly needed...)

Yes, I read this.  And the next release of Muldis DB (v0.4.0) will 
have the first on-CPAN examples, and they will actually work.  But 
most likely that version will be limited to doing relational queries 
on lexical variables, that is, local Perl variables that hold table 
data.  The subsequent release should have the first support for 
global DBMS variables, such as normal SQL tables.

Here's a very quick, loose, simple sample (it is 3am) of something 
you could do with version 0.4.0 (might contain bugs):

     use Muldis::DB::Interface;
    
     my $dbms = Muldis::DB::Interface::new_dbms({
             'engine_name' => 'Muldis::DB::Engine::Example',
             'dbms_config' => {},
         });
    
     my $r1_ast = [ 'Relation', 'sys.Core.Relation.Relation', [
         {
             'x' => [ 'PInt', 'perl_pint', 4 ],
             'y' => [ 'PInt', 'perl_pint', 7 ],
         },
         {
             'x' => [ 'PInt', 'perl_pint', 3 ],
             'y' => [ 'PInt', 'perl_pint', 2 ],
         },
     ] ];
     my $r1 = $dbms->new_var( $r1_ast );
    
     my $r2_ast = [ 'Relation', 'sys.Core.Relation.Relation', [
         {
             'y' => [ 'PInt', 'perl_pint', 5 ],
             'z' => [ 'PInt', 'perl_pint', 6 ],
         },
         {
             'y' => [ 'PInt', 'perl_pint', 2 ],
             'z' => [ 'PInt', 'perl_pint', 1 ],
         },
         {
             'y' => [ 'PInt', 'perl_pint', 2 ],
             'z' => [ 'PInt', 'perl_pint', 4 ],
         },
     ] ];
     my $r2 = $dbms->new_var( $r2_ast );
    
     my $r3 = $dbms->call_func( 'sys.Core.Relation.join', {
         'topic' => [ 'QuasiSet', 'sys.Core.Spec.QuasiSetOfRelation', [
             $r1, # or $r1_ast
             $r2, # or $r2_ast
         ],
     } );
     my $r3_ast = $r3->fetch();
    
     # Then $r3_ast contains:
     # [ 'Relation', 'sys.Core.Relation.Relation', [
     #     {
     #         'x' => [ 'PInt', 'perl_pint', 3 ],
     #         'y' => [ 'PInt', 'perl_pint', 2 ],
     #         'z' => [ 'PInt', 'perl_pint', 1 ],
     #     },
     #     {
     #         'x' => [ 'PInt', 'perl_pint', 3 ],
     #         'y' => [ 'PInt', 'perl_pint', 2 ],
     #         'z' => [ 'PInt', 'perl_pint', 4 ],
     #     },
     # ] ]

That example declares two Perl variables containing rowset/table 
data, then does a (N-ary) relational join (natural inner join) on 
them, producing a third Perl variable holding the rowset/table data 
of the result.

Note that the process for working with actual SQL tables is similar, 
but that the user would instead define a (probably application-space) 
Muldis D stored procedure which turns around and calls "join", and 
the Perl code would call the user's procedure instead, so to allow 
the join to be pushed to the DBMS.  Both the declaration of the 
stored procedure and its invocation would be separate 
"$dbms->call_proc(...)" calls, the first being to a data-definition 
proc, the second invoking the user's proc.  Any parameters of the 
user's proc are the bind parameters of its corresponding SQL 
statement(s).

The specs for how to do the above are split between Language::MuldisD 
and Muldis::DB, though some details aren't yet on CPAN.

Look forward to 0.4.0 mid this week, or thereabouts.

-- Darren Duncan