These SQL->OOP mappers need to make their create/new subs private (_new, _create) in some way shape or form; yet still be subclassable. Breaking new() for the consumer is a sin in my book since it's the most common creation idiom out there. Using a mapper should NOT decrease my options in terms of what public subs I want in my API.

I agree about keeping new() as simple as possible. It's a lot easier to decrease API granularity in your subclass than it is to increase it. Here's an example using a different RDBMS-OO mapper that has a finer API granularity than Class::DBI when it comes to object construction.

Set up abstracted data source:

package MyApp::DB;

use Rose::DB;
our @ISA = qw(Rose::DB);

MyApp::DB->register_db(
  domain   => 'default',
  type     => 'default',
  driver   => 'mysql',
  database => 'mydb',
  host     => 'localhost',
  username => 'someuser',
  password => 'mysecret');

1;
[download]

Set up base class for all DB-backed objects:

package MyApp::DB::Object;

use Rose::DB::Object;
our @ISA = qw(Rose::DB::Object);

sub init_db { MyApp::DB->new }

1;
[download]

Finally, the example gallery object class with the customized constructor:

package MyApp::DB::Gallery;

use MyApp::DB::Object;
our @ISA = qw(MyApp::DB::Object);

__PACKAGE__->meta->table('galleries');
__PACKAGE__->meta->columns(...);

sub new
{
  my($class, %args) = @_;

  my $dir = $args{'directory'};

  if (-e $dir) {
      die "directory '$dir' exists";
  }

  mkdir $dir or die "mkdir($dir) - $!";

  # The following is roughly equivalent to find_or_create()
  my $self = $class->SUPER::new(%args);
  $self->load(speculative => 1); # Find...
  
  if($self->not_found) {
      $self->save; # ...or create
  }

  return $self;
}

1;
[download]

A little different than what you're used to, perhaps, but there it is.

Now, how does the consumer of your module add legs to a table? That's right, the CDBI specific method ->add_to_tablelegs({}). That's silly. What if I change the DB layer? You're stuck with a cruddy API, even if you map add_to_tablelegs to SomeOtherDBILayer::add_child_object(). MyApp::SomeTable should just have an add() method to abstract that out. How many people do that that use CDBI? Who knows. The same goes for find_or_create, search_like, and all the others. They're convenience methods for the module writer, but should NEVER be exposed to the module consumer.

I'm not a fan of "add related object" APIs that are auto-created by default. Having method generators available for those abilities is fine, but it's rare that an RDBMS-OO mapper has enough information to correctly guess when and how to do such things--let alone what to name the methods. Your example illustrates this.

What I would probably do is write the method myself, since it's so simple:

package MyApp::Table;
...
sub add_leg
{
  my($self, $leg) = @_;
  $leg->table_id($self->id); # tie leg to this table
  $leg->save;
  push(@{$self->{'legs'}}, $leg);
}
[download]

...or maybe it's not so simple, depending on how you want add_leg() to act, which kind of further proves the point. The existence of a relationship between two tables does not dictate (or even suggest, really) anything about how to "correctly" add rows to a related table.

Oh sure, you can know which columns point to which other columns and other technical details, but semantically, when it comes to adding rows to related tables, a lot is unknown. Is there a limit on the number of related rows that should exist? Should related rows be saved immediately or only saved when the "master" row is saved? Is it even permissible to add rows to a particular related table? There are too many questions for a "safe" add-related-rows method to be implicitly created by default, IMO.

Anyway, no matter what you do, if you want to support multiple RDBMS-OO mappers on the back-end, you're going to have to make some sort of "adapter" layer to map from your API to the DB layer's API.

That's a lot of work, and it seems to me that the cost/benefit ratio is hard to justify. I'd say just pick an RDBMS-OO mapper you like (or even none at all, and use straight DBI) and then stick with it. If you really need to tear it out later and replace it, then you can create the required adapter layer in order to maintain your API. But if you never have to do that, then you just saved yourself a lot of work.