Slash Boxes

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

  (email not shown publicly)
AOL IM: ovidperl (Add Buddy, Send Message)


Journal of Ovid (2709)

Wednesday March 26, 2003
01:28 PM

use strict 'sql';

[ #11251 ]

I just posted a discussion about fear-based development and how decoupling your application from the database implementation can avoid this. It's more of a discussion of a talk I'll be giving next month. I'd appreciate it if anyone would care to read and comment on it. The more info (particularly code examples!), the better.

More | Login | Reply
Loading... please wait.
  • by TeeJay (2309) on 2003.03.26 16:36 (#18411) Homepage Journal
    The perlmonks discussion doesn't mention phrasebooks.

    We have moved to using an SQL phrasebook (all the queries stored in an xml file, later to seperated into several xml files) which allows us to do stuff like my $results = Query->fetch/execute( $queryname, { inarg => $inarg, }, {outarg => \$outarg} );

    Combined with named column binding and placeholders, it provides all the power and flexibility of 'real' sql, while both abstracting the query out of the application and allowing the DBA to manage the queries and databases without having to dig into perl code.

    I think this is definately the way to go, you can always wrap it again in objects or whatever if you need to, but so far it has worked fine.

    We should have 50% of our SQL and PL/SQL in these classes by our next release and slowly move the rest in as we refactor code as we work.


    @JAPH = qw(Hacker Perl Another Just);
    print reverse @JAPH;
    • Oooh, I'd like to put a recipe about that into the Perl Cookbook. Are there modules for doing this, or are you rolling everything by hand?


    • I've been doing something similar to phrasebooks, though I'm now leaning toward your approach. XMLwith named parameters seems like a much better way to go.

      I've tried abstraction layers that generate SQL, but keep running into the need for a handful of fairly complicated queries (e.g., non-equi-outer joins) that I can craft by hand, but elude straightforward auto-generation.

      • Just remember the Pareto Rule: 80% of the results stem from 20% of the actions. If you have a system in place that removes the bulk of the grunt work, you can focus on hand-implementing the tough stuff. In one of our older applications, we have the following SQL:

          SELECT p.first_name+' '+p.last_name AS name, p.people_id
          FROM people p
          WHERE p.companyID = ? AND NOT EXISTS
            SELECT   1
            FROM product_c

        • This rule is key -- I never knew it had a name, BTW. I designed SPOPS to do *most* of the work for you, but nobody can write a system to do everything, although people certainly try. The trick is finding the right balance between configurability, features and complexity. So you make a list of the common operations (what to declare, what sort of relationships to deal with, how to allow field mapping and value transformations, etc.) and provide an easy way to take care of the rest as it comes along.
      • There is Class::Phrasebook, and a good article on about doing it, as well as example designs at some the perl patterns websites and wiki's.

        I disremember if we rolled our own - I suspect we did. I will check tomorrow. Its not hard though.

        There is no reason you can't have a nice module that provides a combination of dynamic and phrasebook based queries - the best of both worlds and you don't have to fit a hexagonal 'Object-Relational-Persistance' peg into square and round 'real world data structu


        @JAPH = qw(Hacker Perl Another Just);
        print reverse @JAPH;
    • I've rolled my own phrase book as well (a fair amount of Perl code I write has to do with databases and SQL)

      Not sure if I can publish the code (will check with my boss), but I load the SQL from a file into a phrasebook object and then the first time you called that SQL statement, I "prepared" it and returned the statement handle.. All subsequent calls, of course, just returned the statement handle that I had prepared previously. Worked pretty well, when you combine it with placeholders (as TeeJay mentioned

  • Now take this with a huge pinch of salt given that I've never actually used Class::DBI.

    But it seems to me that by using Class::DBI you pretty much tie yourself tighter to the underlying implementation (which is DBI, and you don't get to implement the SQL, so if/when things change, you can't adapt unless you adapt your class).

    Personally I prefer to write my own access layer classes (much to Dave Rolsky's dismay *grin*). I find this easy enough to do for each new project, because they all contain a simple b
    • Re:Tighter? (Score:3, Interesting)

      The thrust of my talk is going to be decoupling the application from the specific implementation of the database. Class::DBI is merely one way to handle this decoupling. At my work, we've added a base class that automatically handles much of the decoupling for you.

      package Foo::Company;
      use base 'Foo::Class';
      use strict;
      use warnings;

      my $map = {
        'code' => 'code',
        'name' => 'company_name',

      my $data = {
        id => 'company_id',
        table => 'com