Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
By writing the SQL myself I can write a single query that gives me exactly the results I need for almost anything. Class::DBI hits the database much harder and makes it shovel a lot more data back to the application for the same effect.

Here's the sweet spot that I've settled on. I use an RDBMS-OO mapper for all the simple-to-medium-complexity things. This covers a lot, IME...say, 90%. For the more complex operations, I use custom SQL encapsulated by methods that sit right alongside my RDBMS-OO mapper's multi-object manipulation methods. In them, I pull all the table and column metadata from the RDBMS-OO mapper classes where it's already stored.

Here's what it looks like in action:

# CRUD stuff: $p = Product->new(id => 123); $p->load; $p->release_date->add(days => 1); $p->save; $p->delete; # Multi-object operations # Triple-join: one inner and two outer $products = Product::Manager->get_products( require_objects => [ 'vendor' ], with_objects => [ 'colors', 'categories' ] query => [ name => { like => '%foo%' }, 'vendor.billing_date' => { lt => DateTime->new(...) }, ], limit => 10, offset => 50); $num_deleted = Product::Manager->delete_products(where => [ id => { gt => 100 } ]); $num_updated = Product::Manager->update_free_products(set => { price => 0.01 }); # Custom SQL operation $num_pruned = Product::Manager->prune_products(type => 'all'); # Server-side SPL $products = Product::Manager->get_popular_products(vendor_id => 123);

Without the comments, it's difficult to tell which operations are supported by the RDBMS-OO mapper, which required custom SQL under the covers, and which merely call through to server-side stored procedures.

And that's the point: to hide the implementation details behind a uniform interface to all database operations. There's also no SQL whatsoever in "end-user" code, and all the table and column names exist in a single place in the entire code base.

In all cases, I create the expected (although possibly sparsely populated) RDBMS-OO mapper objects before returning from the Manager methods. The number and nature of the db queries are almost always the limiting factors, so creating objects is not a big deal once all the data is available.

Each time a new database-manipulation operation needs to be defined, I have a choice. I can use my RDBMS-OO mapper directly, I can write some custom SQL, or I can write it in the database using SPL. No matter which I choose, the interface is the same. And I'm free to change my mind down the road, swapping implementations in the Manager as needed.

I find this approach vastly preferable to a series of DBI-style calls, even accounting for convenient modules like DBIx::Simple. YMMV, of course :)


In reply to Re^3: A brief survey of the DBI usability layer modules on the CPAN by siracusa
in thread A brief survey of the DBI usability layer modules on the CPAN by Aristotle

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!
  • 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, 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, 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.
  • Log In?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others having an uproarious good time at the Monastery: (9)
    As of 2015-07-31 01:30 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









      Results (274 votes), past polls