object-relational Perl programming: best practice or compromise?

[metaperl]

Object-relational database programming involves making use of relational database from an object programming perspective. This contrasts with both the database and programming language being object-oriented as well as with both the database and language being procedural.

A very popular object-relational technology for Perl is Class::DBI. A popular purely procedural technology for Perl is DBIx::Recordset. A popular (yay, perhaps the only) object-oriented database and Perl module is Ace.

If you had a database-driven project to do from scratch which of these 3 routes would you take and why? I guess my thinking on the matter is that the object-relational approach is a compromise: if you wanted to be object-oriented in the language, why not in the database as well? On the other hand, perhaps what I see as a mismatch is really a synergy: perhaps there is something powerful to be gained from the marriage of apparently different paradigms of thought. Perhaps object-relational mismatch is not as big a deal as the gains.

Comments appreciated.

[Corion]

I see two uses for "object-relational database programming" - one as a simple storage solution - I have seen people claim "we store everything in the database", and meaning "we have a huge blob field in the database where we serialize our objects into", and one as a thin layer above the database tables. The first approach doesn't necessarily need any database as backend, and to me, the second approach only makes sense if it makes sense to think about your data per row, instead of thinking about your data as sets and manipulating whole sets.

Personally, I use Class::DBI mostly in the first sense, as a simple persistence solution, but as Class::DBI allows me to use SQL statements directly, I can switch between a row-based view of my data and the set based view that SQL allows easily. There is Class::DBI::IsA, which implements "inheritance" for Class::DBI objects in the sense that you can have different tables that add attributes to some of the rows without the clumsyness of actually needing to check for the existence of the row in the secondary table, which to me is the main point of the so-called "object databases".

Upon rereading the parent, I see that you want a comparison between DBIx::Recordset and Ace - I only heard of the former and never even heard of the latter. I don't like DBIx::Recordset, because I don't like needing to remember the order of my update statements and always ended up writing a specific class interface in the Class::DBI style for my row-based transactions. I have no experience in object oriented databases, but I haven't needed them yet. I think that the approach taken by Class::DBI::IsA and the Everything engine is "good enough" for all my inheritance needs.

[davorg]

If I'm writing programs that interface with a database then I'll almost always use Class::DBI. My reasons are largely pragmatic. Class::DBI has a large community of users and the support mailing list is full of clever people who are very happy to help out whenever I have problems.

Of course, it's not always true that the most popular option is necessarily the best, but when it comes to the selection of Perl modules I generally find that you don't go far wrong if you look at what the smart people at the heart of the community are using.

I don't know anyone who is using DBIx::Recordset or Ace, but I know lots of people who are using Class::DBI.

[pg]

I also choose Class::DBI.

I work for a Java shop, where c# is getting its way into this small peaceful world. It seems to me very normal to go Class:DBI. In my shop (or in the Java world), when we access database through Java, all data are wrapped in DCO (Data Container Object, and obviously they have bunch of getters and setters, for you to retrieve each column), and all access methods (select/update...) are wrapped in DAO (Data Access Object) object. Class::DBI has a very close idea behind, and makes great sense to me.

[geektron]

i have one large project at my place of employ that was written using DBIx::Recordset, and it is probably the biggest maintenance nightmare i have.

it may not all be due to DBIx::Recordset, but every time something goes goofy, i do curse DBIx::Recordset ....

[Ovid]

To expand on the comments I made to you on the mailing list:

OO databases have been around for over a decade but the technology is still rather moribund. As for our company, such a database would be a perfect fit for our data needs but there's no way in heck we would use one. Object Query Language was never standardized (well, it was, but vendors ignored it) and frankly, we have been in the position where we have to switch backends so having a standard query language is critical.

As for Class::DBI and DBIx::RecordSet, I don't think there's much of a choice there for me. For a pre-existing application, many people already have a well-defined object system in place and a natural migration to Class::DBI or something similar is a straightforward refactor as opposed to a redesign. I know which I would prefer.

DBIx::RecordSet, by contrast, uses a procedural interface, encourages the use of typeglobs and has no test suite (its "test.pl" does not count.) There's no way I'm going to tell my boss that we should base our fortunes on that. That's not to say it isn't a good piece of software. Maybe DBIx::RecordSet would be a nice tool to experiment with on a new, small app that's not mission critical, but that's all I would use it on. Give me a solid test suite that plays well with Test::Harness and I'll reconsider.

Class::DBI, by contrast, has a huge test suite, it's dead simple to use and for large applications slinging objects everywhere, it can frequently be used internally to make the code much easier to manage without the programmers working in the upper layers even noticing a difference. Is it perfect? Heck no, but for those applications where it fits, it fits quite nicely.

If you just hate Class::DBI, check out Tangram, Alzabo or some other well-tested software.

[metaperl]

DBIx::RecordSet, by contrast, uses a procedural interface, encourages the use of typeglobs and has no test suite (its "test.pl" does not count.)

I think you should look at the size and comprehensiveness of test.pl. It is not missing anything that the module documents as functionality --- I know, I maintained the module for one cycle. Further, you do not have to use typeglobs with Recordset... see the docs for detail. And finally, a procedural interface means that all of your object modelling and conceptualization is in one place and one place only: the database.

[Ovid]

Ignoring the other issues and focusing on the important one: test.pl does not count as "tests." I've read through it and it looks reasonably comprehensive (tough to say, though), but it doesn't play well with Test::Harness. That's why it has all of the test failures being reported. Why the author decided to ignore the standard way of reporting tests, I can't say, but this does not inspire any confidence in me. If it starts to use Test::More, Test::Class, or some other testing tool that reports its output to Test::Harness, I'll take another look. Right now, because this type of software would be foundational, I can't recommend it. That would be irresponsible of me. (By this token, I even admit that a lot of my older code falls into the "can't recommend" category.)

[stvn]

If you had a database-driven project to do from scratch which of these 3 routes would you take and why?

Well to be honest, I have never liked Class::DBI, it seems (from my feeble assessment, someone please correct me if I am wrong) that it is really just a thin layer over the database and not a lot more than a SQL generator coupled with a class generator. You are not really abstracting away the database, which is what Object-Relational Mapping is trying to acheive. Now, there is nothing wrong with this, it certainly can be a useful tool which can speed up development. But "Object-Relational", maybe not so much.

Real true object relational mapping is not just the one-object-per-row approach that many tools offer, but really a complex and transparent mapping of an class hierarchy to a relational storage format. This means that your class design comes first, and you figure out how to shoe-horn it into a relational database later (of course, as with all design, there will be some compromise too). Which means figuring out how all your class inheritance, delegation, aggregation, etc, etc, etc. should be represented relationally. Some tools try to do all this for you using class reflection and other such silliness. But, the problem I have always had with a lot of these ORM tools is that the DB design it ends up creating is a DBA's nightmare. And so my feeling has always been that a compromise needs to be struck based upon the requirements of the application. The result is that just about all the applications I have created of late use a combination of our in-house ORM tool for some parts, and DBI/raw-SQL for other parts. To use one or the other exclusively would have lead to either a lack of re-use in the parts that use the ORM, or a lack of performance in the parts that use the raw DBI/SQL, and I needed to have both to fill the requirements of those applications.

As for DBIx::RecordSet, I have never used it, so I cannot really say. But from a quick read of the docs, it too looks like your standard SQL-wrapped-in-an-object module. My guess is that Class::DBI would do much the same and give you more flexibility (althought maybe with a higher learning curve).

As for ACE, I have attempted to install it once or twice in hopes of playing around with it, but never managed to get it all working. It is an interesting looking thing for sure, but I can't say much more than that until I manage to get the time to install it right.

[pg]

"This contrasts with both the database and programming language being object-oriented as well as with both the database and language being procedural."

Sounds like there was a little bit misunderstanding of what the author said. As for database, I don't think he is talking about OO database, and most of the databases around us are not. As for language, he is still talking about OO.

His point is how to mapping database tables to objects.

We had some big discussion in our shop about this, and actually the talking is still going on... The problem you facing when you coding in a language like Java is that, the most obvious way of mapping is make each database table a DCO.

Now there is a big issue with this approach: when you create their DAO's, it becomes so unreal for you to enjoy the benefit of table joins, subqueries (we use Oracle, it supports a wide range of SQL statement, which magnified this issue).

We end up with adding two more concepts: ECO (Enterprise Data Container Object) and EAO (Enterprise Data Access Object). When DCO and DAO encapsulate the data and access mothods against a single table. ECO and EAO allow you to encapsulates data from multiple tables. This closely matches what the author called business object. Design wise, this approach allows you to more freely realize objects in its own way, and not held back by database normalization; Practical wise, it allows you to enjoy the power of a wide range of SQL statement, and your code makes more sense.

[tall_man]

I agree with this approach. It is a recognition of the fact that in the real business world, database tables are usually the master of the data. Objects may be thought of as a temporary, programming-language-based, in-memory views of database query results. There may be other applications that derive entirely different "objects" from the same tables, and even report applications driven by straight SQL queries with no attempt at object orientation.

The Class::DBI module commits what C.J. Date calls the First Great Blunder, attempting to equate objects with rows in a relational table. Date advocates using objects only for user-defined types (things like geographical shapes), confined to single column types instead of row types.

[dragonchild]

I would first point you to OO concepts and relational databases, and other nodes referenced in that thread. The object-relational mismatch is a big deal, and the gains are small and temporary. The issue is that you're trying to shove a square peg (objects) into a round hole (set theory), and I think I'll explain further in this node.

The fundamental error most developers make - and this is the root mistake that CDBI makes¹ - is that they don't understand databases. A database of one table doesn't add any value over having that table in a BerkleyDB or something similar. A database of a few, unrelated tables is equally useless. The main reason is that you will have a ton of overhead and no benefits.

It is only when you start defining relationships between your tables that you start to realize the benefits of relational databases. RDBMSes don't exist to store and retrieve data - that's not the point. They exist to enforce relationships between data. That is the sole piece of value that Oracle, Sybase, DB2, MySQL, and the whole lot of them have². Otherwise, we would just be using huge trillion-record indexed files with a few ultra-optimized lookup strategies.

Those relationships, as I've argued before, are solely HAS-A relationships³. Most OO code is about IS-A relationships, quibbles aside about whether this is a good idea. And, the first place everything breaks down is in the cross-reference (xref) table. This is where you have Foos and Bars and each Foo can have multiple Bars and each Bar can have multiple Foos. This kind of N-to-N relationship is extremely common in most RDBMS schemas. But, I have yet to see a good way of doing it in OO-land. In fact, I think I can argue that OO-land doesn't have a way to express N-to-N relationships and that xref tables are a purely relational concept.

Now, let's say you can express N-to-N relationships in OO-land. Are you going to be able to express the relationship as an object? Because, I will have information about the relationship itself in the xref table. Here's a perfect example:

I work in a medical claims company. Normally, a given insurer will bear 100% of the responsability of a claim. This is about 98% of all our business. So, we should put a branch_id column on the claims table, right?

Well, we have that 2% situation where insurer A will pay 80% of a claim and insurer B will pay 20% of that claim. So, we have to have an xref table. But, we have to track more than just which branch a claim is being paid through. We also have to track

• how much that branch is paying
• is there a maximum that branch will pay on that claim
• what claim number does that branch use to describe that claim

How would you solve that in OO-land?

1. CDBI is an excellent module. And, in fact, I will be using it for an upcoming project of mine. That doesn't mean it doesn't have its issues.
2. For those who would say that BDB and similar solutions don't scale like Oracle or DB2 do ... well, if you had a billion dollars for R&D into scalability, you'd scale just fine. In fact, it's arguable that spending that same money in non-relational solutions would have resulted in petabyte solutions that are cheaper than the terabyte solutions we have now. Having to maintain referential integrity is expensive, and not just in terms of CPU or RAM.
3. They're actually HAS-A relationships in reverse, because the containing object doesn't know what it contains. It is the contained object that actually keeps track of its container. This is reversed from how most OO solutions do it.

[Anonymous Monk]

http://poop.sourceforge.net/

[stvn]

This link it often cited in converstations about this topic, but I think maybe it needs to be updated to really stay useful. I think the page footer says it all.

Generated from Id: poop-comparison.pod,v 1.35 2003/09/30 15:47:47 autarch Exp.

I have found the latest updated POD in the sourceforge CVS repository (only 8 hours old), however, it seems these revisions have not made it onto the main page.

There also does seem to be some spotted activity on the poop-group list. I haven't waded through it myself, but you might find some interesting stuff there.

[autarch]

The updates aren't on the main page because I was waiting for some clarification from Tony Bowden on Class::DBI, and waiting for a new version of Terrance's patch.

Updates would happen more often if people sent patches more often ;)

[Anonymous Monk]

Is just a new project, but is interesting and easy to use: DB persistence framework for Perl Classes