Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Database Abstractions 9 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • The benefits of database abstractions such as Class::DBI are obvious, but the downsides of these technologies can hamper scalability. On one hand, with the CDBI approach, we have a clear mapping of classes to tables, but the databases for which these have been designed are relational when classes tend to be heirarchical. This not only forces developers to accept awkward design constraints (subclassing a table can be an act of supreme masochism), but with the wrong design, it can also limit scalability.

    Consider a hypothetical case when the person writing the code needs to get a list of all customers who shop in a particular region. First the programmer gets a list of all stores in the region, then perhaps a list of salesmen in the region and ultimately a list of customers for each salesperson (naturally, an adhoc example.) This simple idea can involve three queries to the database (or more, if things are really fouled up) even though conceptually it's just one query. Abstractions like Tangram can reduce this to a single query, but at the expense of creating a scheme that fewer people understand (and I understand that modifying things after the fact can be a pain.)

    But wait! Programmers point out to me that it's easy to create a custom SQL snippet in the Region class and fetch the salespeople, but then we're starting to slip away from the abstraction goal we were shooting for. As systems get larger, we start needing more and more ad hoc queries. Writing custom SQL for each one becomes problematic. People tell me I'm being silly because it's CDBI and friends are just lightweight tools and we programmers have to fill in the blanks, but I think that's the wrong way to look at it. Persistent data is crippled and CDBI and friends are such wonderful crutches that I think many people forget about walking. The abstraction starts out great and it's certainly better than what we've been doing, but we still need to figure out a better way of approaching the situation. I've never played with OO features in databases, but perhaps those can help (and multi-value databases might help, but they're not well known and it's just a hunch on my part, anyway.) I actually have some code ideas that I've wanted to explore and that might help but I never seem to find the tuits :(

    • I'm sure object-oriented databases are (at least in some design cases) worth looking at. Too bad there is no (to my knowledge) good OSS implementation of an OODMBS.

    • Dang. I seemed a bit crabby in that, didn't I? I think I made the situation sound worse than it really is.

      • I don't think that was overly crabby. It just highlights that there's a solution to this problem that is simple, obvious and wrong.

        I haven't used Class::DBI in very many projects, but it does make the process of writing an abstraction for a simple database downright painless. I've also written a couple of thin database frameworks, so I've got an idea of what the problem domain is about.

        The situation you described feels like a heavyweight OODB mapping. I don't think that's an accurate description

        • Well, one thing that might help is to implement a "one table" interface. Imagine that your boss wants a report of the average sales per salesperson. If everything was in one big table, you could write:

          SELECT   salesman_name, avg(revenue)
          FROM     the_table
          GROUP BY salesman_name
          ORDER BY salesman_name

          Of course, such a database would be impossible to manage, but if you treat it like that, you can drop the FROM clause:

          SELECT   salesman_name, avg(revenue)
          GROUP BY salesma

    • AFAIK, Tangram can't help you with this, nor can any other abstraction layer. Things that try, like Hibernate, just end up making you write your query in their query language rather than SQL. I prefer to use SQL for that.

      I think people who try to use Class::DBI and friends to isolate them from SQL are chasing the wrong goal. I don't need isolation from SQL -- SQL is easy and works great! All I want is for the busywork code that does all the obvious work to be automated for me, which is what Class::DBI

    • > As systems get larger, we start needing more and
      > more ad hoc queries. Writing custom SQL for each
      > one becomes problematic

      Why is this problematic? If you have to write non-trivial queries to get information out of a database, how else would you expect to be able to do that?

      This isn't meant to be a snippy response - I'm truly interested in what other sorts of ways this sort of thing could be done.

      Tony
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.