http://qs1969.pair.com?node_id=504724

[Note: I considered posting this in the Module Reviews, but decided against doing so because it’s a comparison of multiple modules, which does not seem to fit the format of that section.]

Initially, I purposely avoided using any helper or abstractor modules on top of DBI, because I wanted to see what real problems I would encounter with its use – if any. After a couple of projects with it under my belt, I can say I have: I’m going crosseyed at all the (select|fetch)(all|row|col)_(array|hash)(ref)? methods in my code (some of them additionally modulated by passing an empty hash to indicate that I want an AoH rather than an AoA). I have been fighting with an increasingly uncomfortable feeling of programming by coincidence because I can never tell at a glance what data structure any piece of my code will return. It’s killing my productivity because I’ve lost my confidence and I keep getting jarred out of the flow.

So I struck out to find what DBIx::* modules from the CPAN might be applicable, and to evaluate how well they’d address this particular need of mine.

Most of the modules that made it into my first broad selection have a very different focus. Closest among the differently-focussed modules are DBIx::DWIW and DBIx::Easy, which were in the race until the penultimate round. Those mainly allow you to avoid some of the more menial SQL labouring, but they come up short in terms of options for simplified fetching of results. Since I already have a lot of SQL queries written, and because the things I ask of my databases are usually not complicated, but still non-trivial, I do not stand to gain much from the major offerings of these modules.

Thus, the last two modules standing were DBIx::ContextualFetch and DBIx::Simple. In this shootout, DBIx::ContextualFetch clearly loses. It does not address fetching results as flattened structures at all, and all of its handful of methods have quite similar names, much like in DBI. Its main aim seems to be to keep with DBI tradition. In contrast, DBIx::Simple offers methods for pulling out query results in every possible data structure you might prefer, and the methods are named distinctly and memorably, with a natural and consistent scheme for distinguishing between fetching only one row or all of them. It is exactly what I need.

It’s additionally nice for integrating SQL::Abstract, DBIx::XHTML_Table and Text::Table smoothly, without putting any pressure on the user to buy into these modules. I have no need for DBIx::XHTML_Table or Text::Table right now, and I currently avoid SQL::Abstract, since I ran into limitations with it in the past. But should its limitations be adequately addressed in a future version, or should I have a need for what the other modules offer, then I have a very easy growth vector. That’s quite cool, and reflects the same virtues as Perl itself or other great modules like the Template Toolkit.

Overall, after my survey of the landscape of DBI usability layer modules on CPAN, I look forward to spending more time with DBIx::Simple. Only experience will show if it actually delivers on its promises, of course, but I am quite positive about that.

Makeshifts last the longest.

Replies are listed 'Best First'.
Re: A brief survey of the DBI usability layer modules on the CPAN
by samtregar (Abbot) on Nov 01, 2005 at 20:42 UTC
    I thought you'd lost your mind when you recommended DBIx::Simple. Then I realized you weren't talking about DBI::Simple, which I had the misfortune to meet earlier today. What a difference an 'x' makes.

    -sam

Re: A brief survey of the DBI usability layer modules on the CPAN
by perrin (Chancellor) on Nov 01, 2005 at 21:24 UTC
    I feel like a lot of these are obsolete now that DBI has selectall_arrayref() and selectall_hashref().

      Sure, I use selectall_arrayref( $sql, { Slice => {} }, $foo, $bar, $baz ) most of the time. Which is longwinded. And the Slice => {} bit constantly chafes me. And often enough I only need one row, or I need a completely flat list made up of all rows. I can either standardise on one longwinded method and litter my code with repetitve data structure munging, or I need to keep using different methods that follow an extremly unhelpful naming convention.

      Or I can use someone else’s code which is standardised on one method and does the data munging for me.

      Really, I made an effort to work with just plain DBI. I didn’t declare it too low-level from the get go or some such notion, which I’ve seen occasionally. I did in fact not expect to have to use anything else on top. And it is indeed very close to being perfectly adequate. If a new release of DBI offers DBIx::Simplesque result fetching methods in addition to its current set, I’ll gladly go back to vanilla DBI.

      But in the meantime I need to be able to look at my code and see what’s going on without having to stop and think hard for a third of a second at every other method, or I’ll never get anything done.

      Makeshifts last the longest.

        But in the meantime I need to be able to look at my code and see what’s going on without having to stop and think hard for a third of a second at every other method, or I’ll never get anything done.
        It sounds like your code is too DWIS instead of DWIM. Your top-level code should just be do_this, do_that which no hint of what model is doing what for you.
Re: A brief survey of the DBI usability layer modules on the CPAN
by fokat (Deacon) on Nov 02, 2005 at 05:19 UTC

    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

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

        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

      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.
Re: A brief survey of the DBI usability layer modules on the CPAN
by johnnywang (Priest) on Nov 01, 2005 at 20:59 UTC
    Thanks for the survey, I also found the raw DBI pretty cumbersome for many simple tasks.

    How does DBIx::Simple play with connection pools such as connect_cached, prepare_cached and Apache::DBI?

      There’s nothing about any of that in the documentation.

      Since DBIx::Simple does nothing extraordinary, I assume it works with Apache::DBI no differently than any other piece of code that uses DBI naïvely.

      I can’t see that it uses connect_cached; it does expose the database handle via a getter, but there’s no way to set it or to supply your own. Supplying your own would make this and more things possible; that might make a good feature request. but you can supply your own database handle, which you can set up in any way you’d like.

      As for prepare_cached, it actually uses its own caching scheme for statement handles; but it does not use prepare_cached. I don’t know what the motivation behind this is, so there may or may not be a good feature request to be made here.

      Makeshifts last the longest.

        I can’t see that it uses connect_cached; it does expose the database handle via a getter, but there’s no way to set it or to supply your own. Supplying your own would make this and more things possible; that might make a good feature request.
        I haven't used DBIx::Simple (and am now looking in to it), but a quick glance at the docs has this constructor:

        DBIx::Simple->connect($dbh)

        I would assume this could be used to pass your own DB handle created via connect_cached.

        ++ for a good writeup. I'd definitely be interested on your thoughts on Class::DBI, and a comparision between it and DBIx::Simple.

        As for prepare_cached, it actually uses its own caching scheme for statement handles; but it does not use prepare_cached. I don’t know what the motivation behind this is

        prepare_cached caches sth objects not caring whether the sth has finished already. You might be executing a sth that was still in use somewhere else. This leads to all kinds of interesting bugs :)

        Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }

Re: A brief survey of the DBI usability layer modules on the CPAN
by metaperl (Curate) on Nov 01, 2005 at 22:23 UTC
    In contrast, DBIx::Simple offers methods for pulling out query results in every possible data structure you might prefer, and the methods are named distinctly and memorably, with a natural and consistent scheme for distinguishing between fetching only one row or all of them. It is exactly what I need.
    I second this wholeheartedly. Having been a staunch proponent of DBIx::Recordset, a pleased user of Class::DBI, and a recent user of Rose::DB::Object, I have the experience to say that DBIx::Simple rocks hard. And if it is not enough, it plays beautifully with SQL::Abstract
Re: A brief survey of the DBI usability layer modules on the CPAN
by vek (Prior) on Nov 02, 2005 at 00:32 UTC
    ...offers methods for pulling out query results in every possible data structure you might prefer, and the methods are named distinctly and memorably, with a natural and consistent scheme for distinguishing between fetching only one row or all of them. It is exactly what I need.

    I couldn't agree more. This is exactly why I gave DBIx::Simple a try back in 2003. After some playing around for a few weeks, I started using it in production code. Every single line of database code I've written since then has involved DBIx::Simple. It just feels right, does exactly what I want. Can't ask for me than that.


    -- vek --
Re: A brief survey of the DBI usability layer modules on the CPAN
by renodino (Curate) on Nov 02, 2005 at 04:18 UTC
    I’m going crosseyed at all the (select|fetch)(all|row|col)_(array|hash)(ref)? methods in my code ... I have been fighting with an increasingly uncomfortable feeling of programming by coincidence because I can never tell ... what data structure ... my code will return. It’s killing my productivity because I’ve lost my confidence ...

    If you're willing to mingle SQL with your perl, and don't mind source filters, perhaps SQL::Preproc would relieve your crosseyed productivity issues ? In most instances, you just write the SQL, inserting whatever Perl variable(s) you need to read from/write to as placeholders. No more "picking the right API call".

      Oh dear, source filters. Well, maybe in Perl6, but for now I’m not putting any of those in my production code, sorry. :-)

      Besides that non-specific objection, after a brief perusal of the docs, I have the same complaint as with DBIx::ContextualFetch: the choice of data structures is limited. I really do sometimes want a flattened list with the data from all rows.

      Makeshifts last the longest.

        As I understand it, SQL::PreProc makes quite controlled use of source filters - it specifies what it will and won't parse and mostly (or completely?) doesn't attempt to parse perl so it avoids the dangerous bits of source filters.
Re: A brief survey of the DBI usability layer modules on the CPAN
by Tanktalus (Canon) on Nov 01, 2005 at 22:06 UTC

    Which database are you using? It makes a difference - I started down the road of using one of them (don't recall which anymore - this was long before I found PM), then found it didn't work with DB2 - and there was no plans to support it. So I had to abandon that module. I've not had the time to go back and revisit that decision.

      Huh? Well, the project I’m using this on must run on MySQL (despite my own choices), and I have some code that uses SQLite. I was going to be using it with PostgreSQL at some point. I don’t see how any of that matters though, since the module leaves the queries entirely up to you.

      Makeshifts last the longest.

      Even though I've not tried DBIx::Simple with DB2 personally, it really shouldn't matter what database you are using as long as it's supported by DBI and has a DBD::* driver. FWIW I know it works with MySQL, Oracle, and Sybase.

      -- vek --
Re: A brief survey of the DBI usability layer modules on the CPAN
by tphyahoo (Vicar) on Nov 14, 2005 at 08:23 UTC
    Can anyone commont on DBIx::Class? I'm asking because this is one of the two main models supported by perl's shiny new web framework, Catalyst. (At least, it's featured as one of two on the Wiki.)

    Since Class::DBI, which is the basis of the other supported Catalyst model CDBI::Crud, got a lot of flack on this thread, I'm looking to get opinions on

    1) if you get most of the sweetness of DBIx::Simple with DBIx::Class

    2) Is DBIx:Class stable enough to use in your Catalyst dev (it's listed as experimental)

    If DBIx::Class is judged too immature, how much sacrifice would it be to use DBIx::Simple in your catalyst apps instead of DBIx::Class.

      DBIx::Class and Class::DBI are the same kind of thing – ORMs that layer an OO abstraction over the database. They try to create a model for you with minimal effort on your side, but in so doing they keep very close to the structure of the database, and yet make it difficult to formulate non-trivial queries.

      I prefer to write my model from scratch, talking SQL to the database directly. DBIx::Simple does not add any abstractions over using DBI directly – it just puts a cleaner interface on top of it, so you get to keep your sanity.

      Makeshifts last the longest.

        DBIx::Simple does not add any abstractions over using DBI directly

        Actually, it does. Instead of working with statement handles, they have been abstracted away and wrapped in result set objects. There are some fundamental (subtle but very natural) differences: you wouldn't re-execute a result set, for example. It does stick to DBI's iterative approach to fetching, even though many would expect to be able to re-use a result set object and get the same data from it twice. This has not been abstracted, because it would harm performance immensely, especially with databases that don't support rewinding cursors natively.

        Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }

Re: A brief survey of the DBI usability layer modules on the CPAN
by Juerd (Abbot) on Jun 13, 2007 at 21:41 UTC

    It was nice to find and read this old thread. I'm glad to see that my module is so popular.

    Overall, after my survey of the landscape of DBI usability layer modules on CPAN, I look forward to spending more time with DBIx::Simple. Only experience will show if it actually delivers on its promises, of course, but I am quite positive about that.

    As the module's author, I am very curious -- what has your experience shown? :)

    Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }

      Well, that sentence was mostly a disclaimer, because sometimes you find these great modules that turn out to be not so great once you try them in anger. I haven’t had such problems with DBIx::Simple – it turned out to be just as nice to use as expected.

      There is just one monkeypatch in my code that I’d like to see as part of the module itself:

      sub DBIx::Simple::Result::hash_array { scalar shift->hashes( @_ ) }

      This isn’t for any fault of DBIx::Simple’s, it’s just because of the fact that method calls in Template Toolkit are always in list context. DBIx::Class added a scalar-context-only variant of its search method called search_rs to deal with the same problem.

      Makeshifts last the longest.

        I'm glad to hear you're still happy with DBIx::Simple.

        About the TT issue: While I understand the problem and acknowledge that it is annoying, I strongly believe that this should not be fixed in the numerous modules that happen to be compatible with Template Toolkit, but in Template Toolkit itself. I might release a compatibility package, but I'm not willing to mess with DBIx::Simple itself for this.

        I find the name "hash_array" very confusing, by the way. I'd probably call it "array_of_hashes".

        The correct solution to this problem, if you want it in DBIx::Simple, is to use your own result class. Untested code follows.

        use strict; package DBIx::Simple::TT; use base 'DBIx::Simple'; sub connect { my $self = shift->SUPER::connect(@_); $self->result_class = 'DBIx::Simple::Result::ForcedScalar'; } package DBIx::Simple::Result::ForcedScalar; use base 'DBIx::Simple::Result'; my $stash = \%DBIx::Simple::Result::ForcedScalar::; our %list_methods; $list_methods{$_} = 1 for qw( arrays hashes list flat columns map map_hashes map_arrays ); for my $method (keys %list_methods) { $method =~ /^[a-z]\w+$/ or next; my $supermethod = "SUPER::$method"; $stash->{$method} = sub { scalar shift->$method(@_) }; }
        Do you think that this would solve your problem?

        Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }