Class::DBI may be a lot of things, it's certainly not an object-relational mapping. It's an object-table mapping, which, by design, does not abstract the database layout away. In fact, it makes the database layout a focal point.

Out of the three you mention, I go for the "clean and tidy" approach. I prefer to write the SQL myself - I've yet to find any generic database layer that doesn't generate inefficient code (by virtue of it being generic, and having no application specific knowledge), or doesn't do "smart things" that breaks my code. Whether that comes from CPAN, or was written "in house". So, I typically write a set of modules that contain the SQL code, and call functions in those modules. Ideally, said SQL code only calls procedures in the database, and never touches tables directly, but in too many $WORK places people are afraid of stored procedures, and the policy is "no stored procedures". :(