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

[John Siracusa]

On 11/30/05 7:56 PM, Perrin Harkins wrote:
> I mentioned Oracle's tree extensions.  You can also do complex outer
> join scenarios, subselects, GROUP BY with HAVING clauses, full-text
> search extensions, etc.  There's also some use for managing locking with
> things like SELECT..FOR UPDATE grabbing a bunch of objects and holding
> read locks on them until the commit.  And there's really esoteric stuff
> like extensions for working with polygon data.
> 
> Databases are just too complicated to totally abstract away.  I want
> help with all the easy stuff, but sometimes the hard stuff requires
> direct SQL.

Well, like I said, for anything in the WHERE clause, there's no problem.  I
use sub-selects and weird comparison operators and such all the time in that
situation.

For GROUP BY, there is a bit of a a problem when using any ORM that requires
each object to have a primary key.  If you just want to use an object as a
temporary container for row data, it'll be okay, but it's still not a good
match.

SELECT...FOR UPDATE sounds like a good feature to add to the Manager.

For funky SQL in the FROM clause or the column list, then it's really raw
SQL time.  But in that situation, it's usually difficult or impossible for
an ORM to do anything sensible with the results.

Let's say I do a weird 6-table join, selecting columns from various (but not
all) tables, plus a bunch of derived values and sub-selected values and
function calls.  Doing fetchrow_hashref on that gives me a pretty opaque
hash.  Unless there's a ORM class that exactly matches the name, number, and
type of columns returned, there's not much that can be automated.

In these situations, what I tend to do is run the custom SQL using DBI and
then instantiate one or more RDBO objects manually from each row.  I know
which columns are meant for which objects, so it's straightforward.  Trying
to specify that information in an abstract is more work, not less.

When you use CDBI to do custom queries, do you make sure that the columns
returned match the expectations of your CDBI class?  If so, then my
commented-out get_objects_from_sql() method does the same thing.  If not,
then how do you handle mismatches between selected columns (possibly from
multiple tables) and ORM object attributes?

> By the way, did the new prepare_cached() work improve performance
> noticeably?

I only benched MySQL with MyISAM, which doesn't do anything interesting
during prepare() anyway.  I'll bench Pg later to see if it makes a
difference.  I made several other performance-related changes so it's hard
to attribute the improvements in 0.54 to any one thing without more testing.

Also, I only used prepare_cached() in the places where I expect the SQL to
be identical over time: things like save() and delete().  I don't use it at
all in the Manager, where each query can be different.  (And I don't use it
in load() because it causes inexplicably locking issues in Informix...blah.)

-John