As part of my DBIx::SQLEngine distribution, I've put together a brief document that lists some of the other DBI wrapper modules and highlights a few broad categories of features. I was aiming for something broader and more shallow than the Poop Group comparison, reviewing more modules but going into less depth.

I've tried to populate this list with modules I've seen others discuss, as well as some I just found on CPAN, but for the many modules I haven't used, I've simply based my evaluation on a few minutes reading the documentation.

Could those of my fellow monks who use one of these modules take a moment to check whether I've classified it appropriately, or let me know if I've omitted something useful in this DBI wrapper/assistant arena?

I examined each module to see if it had the following features:
DBI Wrapper: Does the module maintain a connection to DBI handle?
One Call Query: Does it provide combined prepare/fetch/finish methods?
Data-driven SQL: Does it generate SQL queries from DWIM-ish data structures?
Schema Access: Does it detect or create tables and other structures?
Named Configs: Does it handle a library of named queries or connection parameters?
Portability: Does it provide a consistent interface that hides idiomatic DBMS implementations?
Object Mapping: Does it provide classes to treat rows of data as objects?

I know that just counting tick marks doesn't tell you which module is better, but I found it helpful to see which modules had related functionality; for example, I think I had previously overlooked DBIx::SearchProfiles because I didn't realize what it did.

Package DBI Wrapper One Call Data SQL Named Config Schema Access DBMS Portability Object Mapping
 
DBIx::Abstract Y Y Y - - - -
DBIx::AbstractLite Y - Y - - - -
DBIx::Broker Y Y Y - - - -
DBIx::Browse Y Y Y - - - -
DBIx::DWIW Y Y - - - Y/- -
DBIx::Easy Y Y Y - - Y/- -
DBIx::Schema Y Y Y - - - -
DBIx::Simple Y Y - - - - -
DBIx::Wrap Y Y Y - - - -
DBIx::YAWM Y Y Y - - - -
MySQL::Easy Y Y - - - - -
SQL::AnyDBD Y - Y - - Y -
Sql::Simple Y Y Y - - - -
 
DBIx::Librarian Y - - Y - - -
DBIx::PhraseBook Y Y Y Y/- - - -
DBIx::SearchProfiles Y Y Y Y - - -
SQL::Catalog Y - - Y - - -
 
Class::Phrasebook::SQL - - - Y - - -
SQL::Abstract - - Y - - - -
SQL::Generator - - Y Y - -
SQL::Library - - - Y - ? -
 
DBIx::Admin::TableInfo Y - - - Y - -
DBIx::DataSource - - - - Y Y -
DBIx::SystemCatalog Y - - - Y Y -
 
Alzabo Y Y Y -? Y Y Y
Class::DBI Y Y Y Y? ? Y Y
DBIx::RecordSet Y ? Y -? Y Y Y
DBIx::SearchBuilder Y Y Y - - Y Y?
MKDoc::SQL Y Y Y -? ? Y/- Y
Rosetta Y? -? Y? -? Y? Y? Y?
SPOPS Y Y Y - Y Y Y
Tangram Y Y Y - Y? Y Y
 
DBIx::SQLEngine Y Y Y Y Y Y Y
DBI Wrapper One Call Data SQL Named Config Schema Access DBMS Portability Object Mapping

I typically didn't evaluate whether or not the implementation looked like it was high-kwalitee or not, but I would be interested in feedback on this aspect as well.

Replies are listed 'Best First'.
Re: DBI Wrapper Feature Comparison
by samtregar (Abbot) on Apr 17, 2004 at 06:45 UTC
    Perhaps you should add a column "Worth The Trouble", defined as whether the module is worth bothering with if you're comfortable writing SQL. I could give you a list of all of them I've looked at so you can fill in the "N"s!

    -sam

[reply]
      I've been coding SQL for over two decades (off and on). Sure, I could hand-code all this stuff for each new project, but I've found the leverage of Class::DBI to be well worth the trouble to code the "boring" SQL and leave me to do the "exotic" SQL. After all, aren't we supposed to let the computers do the boring parts? {grin}

      There's a lot of good activity in the Class::DBI community as of late as well. Definitely something to check in on.

      -- Randal L. Schwartz, Perl hacker
      Be sure to read my standard disclaimer if this is a reply.

[reply]
[reply]
      ... whether the module is worth bothering with if you're comfortable writing SQL. ... all ... "N"s!

      Is cross-platform typically not an issue for your projects, or do you just target the lowest common denominator supported by the DBMS types you're targeting?

      For example, the syntax for selecting a subset of rows with limit and offset varies from one database server to another -- do you just not use it in your applications, or do you have some other way of managing this issue?

      If you're building a fixed-site application for a single customer, this isn't really an issue, but if you're building something for widespread reuse, it's nice to be able to say fetch_select( ..., limit => 10, offset => 40 ) and have it automatically use "limit 10 offset 40" when on MySQL, or "ROWNUM" when on Oracle.

[reply]
        What do you mean by cross-platform?

        For instance the transactional semantics that Oracle uses differ from virtually any other database, do these modules correct for that?

        For instance DBD::Sybase doesn't allow you to use placeholders with 2 open statement handles on the same database handle at the same time (because of race conditions). Do you run into bugs from that?

        While I agree that it is nice to abstract away the details of the database, I'm also worried that the abstraction will leak. And in some ways an incompletely implemented feature is worse than a complete one because you rely on it - and get burned.

[reply]
        Is cross-platform typically not an issue for your projects, or do you just target the lowest common denominator supported by the DBMS types you're targeting?

        Cross-platform? Sure! Cross-DB? Not usually. Take, for example, Krang. It's proving to be very portable, due partly to the fact that MySQL is very portable. Installing MySQL is so easy that it's hardly a barrier to adoption.

        Supporting multiple databases in an application is very rarely worth the trouble, in my opinion. I say choose a good free database with wide portability and damn the rest.

        -sam

[reply]
      Yeah, good point. Hey, now that I think about it, I'm pretty comfortable with HTML, so I guess I can drop that silly templating tool I keep hauling out. Damn, my apps are gonna be fast if I just print() it directly from the code!
[reply]
        Hey, if I had a DBA to write all my SQL for me, I'm sure I'd be using some kind of SQL equivalent of HTML::Template. You know, separate my code from my SQL so that the SQL geek can work on it more easily. Would I be using an OO wrapper? No way! If I did that the DBA wouldn't be able to help me! But I don't work with a DBA and I doubt I will anytime soon. That means I have to write my own DB access code and I might as well do it in the simplest way possible, with DBI.

        I see these OO wrapper modules as more like using CGI.pm's HTML generation functions instead of writing webpages in HTML. They aren't about separating Perl and SQL. They're about replacing SQL with a new Perl OO notation. In my opinion that only makes sense if you're uncomfortable working with SQL. I haven't seen one yet that looked like it was worth the trouble. All the stuff they claim to make so much easier is already easy if you're fluent with SQL!

        -sam

[reply]
Re: DBI Wrapper Feature Comparison
by Juerd (Abbot) on Apr 17, 2004 at 20:57 UTC

    Nice additions to this table would be:

    • Interface. Object oriented or functional? Are the method or function names perlish?
    • DBD support. Are only certain DBDs supported or does it work with any existing DBD?
    • DSN abstraction. Is the DSN abstracted or does it use normal DBI DSNs?
    • Access to internals. So they wrap, but can you access the DBHs and STHs? If you can't access the STH directly, can you access its attributes?
    • Multiple connections. Does the interface allow you to use multiple databases? How is this done? (OO? select-ish? both?)
    • Multiple queries. Can multiple results from multiple queries be fetched or is there only one current STH? Can you use STHs from different databases, if multiple connections are supported?
    To answer these for DBIx::Simple: OO; any; DBI; Current version: no, 1.20 and newer: yes, yes; yes(OO); yes, yes.

    1.20 will be out Real Soon Now. (If you want to test the development version, let me know)

    Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

[reply]
Re: DBI Wrapper Feature Comparison
by simonm (Vicar) on Apr 17, 2004 at 15:20 UTC
    Thanks to blokhead for pointing out his Class::Tables; I don't know how I missed it before but will add it to the next version.
    Package DBI Wrapper One Call Data SQL Named Config Schema Access DBMS Portability Object Mapping
    Class::Table Y Y Y/- - Y Y Y
[reply]
DBIx::Recordset
by Arunbear (Prior) on Apr 17, 2004 at 19:41 UTC
    DBIx::Recordset does NOT do Object Mapping - it provides access to records via tied arrays and hashes but not via objects (whose methods map to columns).
[reply]
      Point taken; I'll make that clearer in the next version.
[reply]
Re: DBI Wrapper Feature Comparison
by jZed (Prior) on Apr 17, 2004 at 17:23 UTC
    Thanks for a most excellent post! Perhaps Tie::DBI belongs on the list.
[reply]
      Thanks for the addition jZed.

      A bit of poking around also revealed a few more tools that used tied interfaces:
      Package DBI Wrapper One Call Data SQL Named Config Schema Access DBMS Portability Object Mapping
      Tie::DBI Y Y ? - - - tie
      Tie::RDBM Y Y ? - Y/- - tie
      Tie::RDBM::Cached Y Y ? - Y/- - tie
      Tie::Table Y Y ? - - - tie

[reply]
Sql::Simple
by dextius (Monk) on Apr 18, 2004 at 00:39 UTC
    Table joins was the only thing that beat me down when I used to use Class::DBI (that and some instability issues when I used it under mod_perl).
    I've been asked to write more comparison related information in my documentation for Sql::Simple, not realizing how many people are trying to solve this irritating problem.

    Any sql module that lets you treat a database as a large data structure to do something wacky like any kind of transform is cool. (imho) Nice post.

    cheers,
    Ryan Dietrich (my friends call me dextius)
[reply]
Re: DBI Wrapper Feature Comparison
by water (Deacon) on Apr 17, 2004 at 19:15 UTC
    Obviously preaching to the choir here, but I'm still amazed by the TIMTOWTDI-ness of perl...

    this most helpful chart is one of the best illustrations of this (rich? crazy? fiercely independent? idiosyncratic? troubling? lovable?) facet of the perl community, indeed.

[reply]

Back to Meditations