Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re^3: A brief survey of the DBI usability layer modules on the CPAN

by siracusa (Friar)
on Nov 03, 2005 at 01:49 UTC ( #505195=note: print w/ replies, xml ) Need Help??


in reply to Re^2: A brief survey of the DBI usability layer modules on the CPAN
in thread A brief survey of the DBI usability layer modules on the CPAN

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 :)


Comment on Re^3: A brief survey of the DBI usability layer modules on the CPAN
Download Code
Re^4: A brief survey of the DBI usability layer modules on the CPAN
by Aristotle (Chancellor) on Nov 03, 2005 at 15:15 UTC

    As I’ve said before in this thread, that is roughly what I already do. And as I’ve already responded to fokat, I find ORMs unhelpful for this approach as well.

    The primary advantage of an ORM would seem to be that it avoids leaking database queries into unrelated code all over the place. However, as you point out, despite appearances, that couples the code too closely with the database. So there should indeed be a higher-level abstraction layer than just the ORM.

    Of course, how this extra layer’s innards are written is not a concern to the calling code. It’s also clear that internally, this layer is inherently coupled strongly with the database. Fine, so you could use an ORM to simplify this code. But you can do that just as well using something like SQL::Abstract – and that way you also avoids the overhead and opacity introduced by an ORM.

    So I find that depending on the side from which you look at it, ORMs are either too much abstraction or too little, but never right.

    Makeshifts last the longest.

      Fine, so you could use an ORM to simplify this code. But you can do that just as well using something like SQL::Abstract and that way you also avoids the overhead and opacity introduced by an ORM.

      But then you miss out on the advantages of dealing with objects instead of just bare row and column data in the calling code. And if you decide to dress up the rows returned by DBI-style queries by using that data to initialize objects, well, you're basically writing your own mini ORM at that point.

      So I find that depending on the side from which you look at it, ORMs are either too much abstraction or too little, but never right.

      I find that they're a happy medium that I rarely need to stray far from in either direction. I've already described when I do when I need something lower-level. In the cases where a higher-level abstraction is needed, I tend to aggregate ORM objects inside the higher level object.

      Basically, the higher the abstraction, the more layers I want between me and plain DBI. I end up with something like HLO -> ORM -> DBI. (HLO: High Level Objects). Of course, in most cases, the ORM doesn't use straight DBI either, so it's really HLO -> ORM -> SQLH -> DBI. (SQLH: SQL Helper, like SQL::Abstract and friends.) Why so many layers? Because I want some well-defined API at each level that I'm likely to reach down to. A gulf of non-public code between HLO and DBI is not to my liking. HLO -> SQLH -> DBI is better, but I still like that extra ORM layer because it's the one I work directly with most often.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://505195]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (3)
As of 2014-09-18 01:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (103 votes), past polls