The point of my presentation will not be to generate all SQL on the fly (though it's often good to do as much of this as possible), but to decouple the application from the the database. For example, imagine that you want to fetch a list of all customers along with their order numbers, if they've ever placed an order. How might this be implemented?

sub order_numbers {
    # assume, for the sake of argument, that order numbers
    # are optional
    my $class = shift;
    my $sql = <<'    END_SQL';
    SELECT c.last_name, c.first_name. o.order_num
    FROM   customers c
    LEFT OUTER JOIN orders o ON c.customer_id = o.cu_id
    END_SQL
    return $class->_fetch_records($sql);
}

[download]

That looks reasonable enough, but what happens if you (for example) want to rename orders.cu_id? Every place in your code that this is embedded will break. Here's a different approach.

sub order_numbers {
    my $class = shift;

    my $customers = $class->_table;
    my $orders    = Order->_table;

    my %cust  = map { $_ => $class->_column($_) }
        qw{ first_name last_name customer_id };
    my %order = map { $_ => Order->_column($_) }
        qw{ order_num cust_id customer_id };

    my $sql = <<"    END_SQL";
    SELECT $customers.$cust{last_name}, $customers.$cust{first_name}
           $orders.$order{order_num}
    FROM   $customers
    LEFT OUTER JOIN $orders 
        ON $customers.$cust{customer_id} = $orders.$order{customer_id}
    END_SQL
    return $class->_fetch_records($sql);
}

[download]

Now, you can change your column and table names at will, but you don't break your code so long as you update your field map in the object that handles the appropriate table. Admittedly, this is much more work, but it's more robust and the bulk of the SQL can still be auto-generated. In my experience, the amount of work you save by abstracting out the rest of your SQL easily offsets messes like the above snippet.

Naturally, since I expect to see a full test suite with all of that, I'll still catch the errors, if any, in said suite.

Cheers,
Ovid

New address of my CGI Course.
Silence is Evil (feel free to copy and distribute widely - note copyright text)

In reply to Re: Re: No Fear: decoupling your application from the database by Ovid
in thread No Fear: decoupling your application from the database by Ovid

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


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.