Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

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

by fokat (Deacon)
on Nov 02, 2005 at 05:19 UTC ( [id://504823]=note: print w/replies, xml ) Need Help??


in reply to A brief survey of the DBI usability layer modules on the CPAN

Dear Aristotle

It surprises me that this excellent study you shared with us (++) didn't take you to Class::DBI & friends. IMHO this approach is nicer because you simply do not see any more SQL mixed with your Perl. Granted, this may be a significant paradigm shift and perhaps there are things that get harder to do with this model.

The fact that no monks have mentioned this yet makes me think that there is something in your node that I missed :) But anyway, I would like to share my experience with the fellow monks.

With Class::DBI you write a (very simple) class hierarchy that describes your tables, columns, relationships. This can be even simpler if you accept to specify within those classes, which database you will be using. In this case you wouldn't even have to name your columns at all.

That class hierarchy really helps a lot with code reuse and encapsulation.

Then, all of a sudden, everything becomes nice, packaged objects with accessors that are mostly DWIM-compliant. There are additional classes that support complex query generation (even supplying your own SQL).

There are some wrinkles to iron yet, but in my experience, this family of modules has changed my way of working. I really like this style better and I have seen how cleaner your programs can be. I rewrote some production code (DNS::BL) so that it uses Class::DBI rather than hand rolled code, and I'm very happy with the results.

BTW, it also plays nice with Apache::DBI...

Best regards

-lem, but some call me fokat

Replies are listed 'Best First'.
Re^2: A brief survey of the DBI usability layer modules on the CPAN
by Aristotle (Chancellor) on Nov 02, 2005 at 06:11 UTC

    I have tried Class::DBI before. That’s a rant for another time.

    Briefly: I initially thought it was great. But when I started wanting to do the slightly more complex things that are possible with SQL, I ran into trouble; anything that involves multiple joins, f.ex., is at best difficult to express. I instead frequently found myself grepping and mapping through lists of result objects; which is just a waste. SQL can express the same things more directly, and I can avoid a whole lot of database roundtrips as well as oodles of object construction that happens in the Class::DBI guts. 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.

    I really wanted to like Class::DBI; but if you actually use your database relationally, it gets in the way too much.

    Instead, I found myself much more productive by writing higher-level abstractions than what Class::DBI can give me, but tailored to my particular needs. Eg. I have standardised on particular naming conventions for the POST parameters in my CGI scripts, and I wrote a generic CRUD function that uses these conventions, along with a caller-supplied list of field names, to do almost all of the CRUD stuff in my app. Whether this one function encapsulates a Class::DBI hierarchy or is written using uncircumphrased SQL queries really doesn’t make a lot of difference, but its existence has greatly reduced the amount of code I wrote elsewhere.

    In other words, I’ve found that for me, Class::DBI abstracts away the wrong things and actually makes the right ones harder.

    Okay, so this managed to turn into a long rant anyway… and I haven’t even explained what it is that Class::DBI does so badly. I guess there’ll be another root node coming sometime in the next while.

    Makeshifts last the longest.

      but if you actually use your database relationally, it gets in the way too much.

      I've heard that some people do that; just haven't seen much evidence of it around my neighbourhood... ;-)

      I agree 100% with your Class::DBI assessment, so thanks for pointing out DBIx::Simple as something that might help out.

      [Jon]

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

        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.

      I really wanted to like Class::DBI; but if you actually use your database relationally, it gets in the way too much.

      Yes, this is actually the biggest of the wrinkles I was talking about in my node. Complex operations end up being processed in the client instead of within the database, as it should be.

      (...) anything that involves multiple joins, f.ex., is at best difficult to express. I instead frequently found myself grepping and mapping through lists of result objects; which is just a waste.

      Precisely. And although I haven't looked into this yet, I am quite sure it is possible to express these relations and let the object hierarchy sort the proper SQL statements to generate so as to minimize the amount of data fetched.

      I have the impression that Class::DBI is a step in the right direction, but more control is needed. I think that improving the statement generation would be a big step.

      Best regards

      -lem, but some call me fokat

        I think that is better done with an approach such as SQL::Abstract’s. (Indeed, I’d be using it even now, if I hadn’t run into the previously mentioned limitations.) An object-relational mapper, as far as I can tell, will always suffer from trying to express queries with complex dependencies using an ill-suited model.

        Makeshifts last the longest.

Re^2: A brief survey of the DBI usability layer modules on the CPAN
by metaperl (Curate) on Nov 03, 2005 at 17:43 UTC
    Class::DBI & friends. IMHO this approach is nicer because you simply do not see any more SQL mixed with your Perl.
    DBIx::Simple has a sweet interface to SQL::Abstract if you haven't noticed.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others having an uproarious good time at the Monastery: (9)
As of 2024-03-28 14:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found