In the Ideology section of the DBIx::Perlish docs, Anton Berezin lists 3 ways that Perl wrestles with SQL.

He says:

SQL sprinkling approach
One puts queries wherever one needs to do something with the database, so bits and pieces of SQL are intermixed with the program logic. This approach can easily become an incomprehensible mess that is difficult to read and maintain.
Clean and tidy approach
Everything database-related is put into a separate module, or into a collection of modules. Wherever database access is required, a corresponding sub or method from such a module is called from the main program. Whenever something is needed that the DB module does not already provide, a new sub or method is added into it.
Object-relational mapping
One carefully designs the database schema and an associated collection of classes, then formulates the design in terms of any of the existing object-relational mapper modules like Class::DBI, DBIx::Class or Tangram, then uses objects which perform all necessary queries under the hood. This approach is even cleaner than "clean and tidy" above, but it has other issues. Some schemas do not map well into the OO space. Typically, the resulting performance is an issue as well. The performance issues can in some cases be alleviated by adding hand-crafted SQL in strategic places, so in this regard the object-relational mapping approach can resemble the "clean and tidy" approach.

a 4th way

For completeness, I'd like to mention Gurdjieff, oops, I mean a 4th way: converting data structures to SQL. This is the approach of DBIx::Recordset (R.I.P.), SQL::Abstract, and DBIx::SearchBuilder.. please list others if you know of them.
  • Comment on arranging code that works with SQL databases in Perl

Replies are listed 'Best First'.
Re: arranging code that works with SQL databases in Perl
by Corion (Patriarch) on Jul 24, 2009 at 16:04 UTC

    There are other approaches:

    • The approach by DBIx::Perlish, which converts Perl code to SQL, so you don't write SQL at all.
    • The approach of Data::Phrasebook, which puts all SQL into a separate file/DB and has the code only using it.
    • The Oracle approach of putting most of the logic into PL/SQL and using whatever outside language to fetch from stored procedures or views.
[reply]

      Putting everything into Oracle procedures is the approach the project I'm working on right now has taken. Any result-set to be retrieved from the database is obtained by calling a function which returns a reference cursor. The user connected to Oracle has no select, insert, update, delete privileges at all but the packages containing the procs/funcs are created with definer rights instead of executor rights so whilst running something in the package they can update/delete/select/insert. Works well and the only SQL outside of Oracle is a "begin ? := func_or_proc(parameters)" which we have of course put into a single perl module.

[reply]
        Yes, the thing is how much SQL manipulation do you need to do? For instance, do you sometimes need to add LIMIT clauses? Or count rows instead of just returning them?

        Or maybe the columns you need back change dynamically?

        Stored procedures and phrasebooks share some similar issues that dave mentions here

[reply]
        Putting everything into Oracle procedures is the approach the project I'm working on right now has taken.
        Can you comment on how fast ORacle stored procs are compared to SQL? I know that the query optimizer has no access to stored functions in MySQL and that things often run many times slower.

        Also, how easy is it to dynamically generate SQL with stored procedures? What if you had a search form with an number of optional fields and various comparison operators? Could you build up the SQL piece-wise and then evaluate it after constructing it?

        Finally, I find the stored procedure language quite a bit more wordy than Perl. It would seem that if the database knew the relations of your tables that it could generate common things you would want:

        1. given this id, get the related info in all tables which use this id as a foreign key
        2. instead of returning a cursor to a result set, give me the count of the result set
        3. paginate the result set: given an offset and row count, point the return cursor there

        Now, the SP approach does have its advantages, but I'm just exploring some potential cons.

[reply]
[reply]
Re: arranging code that works with SQL databases in Perl
by JavaFan (Canon) on Jul 25, 2009 at 16:03 UTC
    Class::DBI may be a lot of things, it's certainly not an object-relational mapping. It's an object-table mapping, which, by design, does not abstract the database layout away. In fact, it makes the database layout a focal point.

    Out of the three you mention, I go for the "clean and tidy" approach. I prefer to write the SQL myself - I've yet to find any generic database layer that doesn't generate inefficient code (by virtue of it being generic, and having no application specific knowledge), or doesn't do "smart things" that breaks my code. Whether that comes from CPAN, or was written "in house". So, I typically write a set of modules that contain the SQL code, and call functions in those modules. Ideally, said SQL code only calls procedures in the database, and never touches tables directly, but in too many $WORK places people are afraid of stored procedures, and the policy is "no stored procedures". :(

[reply]
even more approaches
by metaperl (Curate) on Jul 25, 2009 at 20:54 UTC
[reply]
Re: arranging code that works with SQL databases in Perl
by metaperl (Curate) on Aug 24, 2009 at 10:19 UTC
    Another emerging approach is the "method maker approach" of Fey and DBIx::Skinny: 
    
my $rs = Your::Model->resultset(
        select => [qw/id name/],
        from   => [qw/user/],
    );
    $rs->add_where('name' => {op => 'like', value => "%neko%"});
    $rs->limit(10);
    $rs->offset(10);
    $rs->order({ column => 'id', desc => 'DESC' });
    my $itr = $rs->retrieve;

    [download]
[reply]
[d/l]

Back to Meditations