[Catalyst] DBIC and RDBO compared (was: Choosing the right ORM)

[John Siracusa]

On 11/28/05 6:47 PM, Uwe Voelker wrote:
>  On Mon, 2005-11-28 at 14:00 +0100, catalyst at augensalat.de wrote:
>> There are other ORMs available, but only one, DBIx::Class gets  
>> some attention here.
>> How about Alzabo or Rose::DB::Object or maybe others?
>
> John and Matt, can you please give a feature comparison of RDBO and  
> DBIC?

Sure, here's a summary.  I wrote it and Matt provided some feedback  
and corrections.

Let's start with the common features.  Both DBIx::Class (DBIC) and  
Rose::DB::Object (RDBO) can do the following.

---

COMMON FEATURES

* Insert, select, update, and delete individual rows based on a  
primary key.

* Multi-column primary keys are supported.

* Auto-increment and sequence-based primary keys are supported.

* Relate rows in one table to rows in another table through foreign  
keys and other relationships (1 to N, N to 1, and N to N), going  
through a linking table for "N to N" relationships.

* Multi-column foreign keys are supported.

* Queries support arbitrarily nested boolean logic.

* Fetch multiple rows, optionally from more than one table in a  
single query via one-to-one or many-to-one relationships using SQL  
JOINs.

* Support for both "all at once" and iterator styles of queries.   
Iterators fetch from the database only as needed.

* Limit/offset supported where possible, and emulated where not.  A  
"page"-based interface is also provided.

* Column values may be "inflated" and "deflated" as needed.

* Column values maybe "lazily loaded," meaning that are fetched from  
the database at the last possible moment rather then being loaded up  
front.

* Column aliasing for column names that conflict with reserved method  
names.

* A "loader" can automatically build a family of classes based on a  
collection of tables by extracting the relevant information from the  
database: column names, primary keys, and foreign keys.  N-to-1 and 1- 
to-N relationships will be set up automatically based on foreign key  
information.

* Support for "client-side" cascaded delete.

* Support for MySQL, Postgres, and SQLite databases.

---

Next, features that are unique to each module.  (That is, "unique" as  
compared to each other, not necessarily "unique among all Perl ORM  
modules.")

---

UNIQUE DBIC FEATURES

* Class::DBI compatibility layer.

* ADO support in the loader.

* MSSQL and Oracle primary key detection and configuration.

* Supports arbitrary-depth explicit joins.

* Supports arbitrary-depth client-side cascaded delete.

* The list of columns fetched during a SELECT (with or without JOINs)  
can be be specified exactly.

---

UNIQUE RDBO FEATURES

* Support for Informix.

* Objects may be loaded based on unique keys.

* Extensive column metadata: data types, size limits, default values.

* Bundled set of column classes for most commonly used data types,  
with integrated inflate/deflate, validation, and other convenience  
functions.

* The bundled column classes also support some esoteric column types:  
Informix SET columns, Postgres CHKPASS, BIT, and array columns.  
(Inflate/deflate is done to/from Perl arrays and Bit::Vector objects  
as appropriate; Postgres arrays and bitfields are emulated in other  
databases.)

* In addition to inflate/deflate, column triggers can also be applied  
to get/set (user data) and load/save (to/from database) events.

* Can auto-join via one-to-many and many-to-many relationships,  
fetching all data in a single query, preserving custom sort orders  
for sub-objects.

* Queries accept "rich" values as arguments (e.g., DateTime and  
Bit::Vector objects)

* Bulk update and delete methods, which also work with "rich" query  
arguments.

* By default, when an object's related sub-objects are added or  
updated through that object, all changes are delayed until the  
"parent" object is saved, at which time all outstanding changes occur  
within a single transaction.  ("Immediate" actions are also supported.)

* Support for "SELECT DISTINCT ..." queries.

* Lazy loading can be overridden on a per-class basis during single  
and multi-table queries.

* Database migration: copying objects from one database to another by  
trading "db" (Rose::DB) objects.  Works across database types (e.g.,  
Postgres to MySQL) provided that all column data types exist (or are  
emulated) in both places.

* In addition to extracting column names, primary keys, and foreign  
keys, the RDBO loader can also do the following:

- Configure column data types, size limits, and default values.
- Extract and configure unique keys.
- Set up all relationship types, including many-to-many relationships.

* All automatic behavior and naming conventions are encapsulated in a  
"convention manager" object.  Using different convention managers  
will alter the behavior of the loader and other automatic processes.   
(e.g., A "Rails" convention manager could enforce the Ruby on Rails  
naming conventions for classes and tables.)

---

DBIC and RDBO have different recommended methods of extension.

--

EXTENDING DBIC

DBIC uses multiple inheritance and the Class::C3 module.  Behavior is  
modified by inheriting from a certain set of classes, or  
"components."  New behavior is added by creating new classes which  
can be added to the inheritance tree.

EXTENDING RDBO

RDBO uses traditional subclassing and delegation.  Each component of  
the system can be swapped out for a custom subclass or a new class  
that implements the expected API.  Single inheritance is expected,  
but not enforced.

--

The design goals differ in the following ways.

--

DESIGN GOALS

* DBIC is designed to support storage systems other than DBI.  RDBO  
is only designed to support DBI.

* Performance is an important goal of RDBO.  It's currently the  
fastest Perl ORM module among those tested in this benchmark:

     http://rose.sourceforge.net/wiki/index.php/RDBO/Benchmark

DBIC is the second fastest in the benchmark, but performance is a  
less important goal for DBIC than for RDBO.

---

-John