I'm not sure that I entirely agree with that. It's legitimate to have another unique identifier. You just don't want to be using it as a foreign key in another table. The argument for having surrogate primary keys is that they are meaningless as anything except a row identifier, having no semantic meaning as far as your data is concerned, and thus are non-volatile and consequently safe to use for linking objects/rows together.

I agree with your assertion that subclassing is the right thing to do. I just don't think that the primary key has to be the only unique identifier. For example, think of a user table... The user id is going to be unique. It would be a bad idea to use it as a foreign key in link tables, but it would be unique all the same.

Anyway, I'm not sure what I'm arguing anymore... I didn't get enough sleep last night and am not entirely coherent. :-)

For example, think of a user table... The user id is going to be unique. It would be a bad idea to use it as a foreign key in link tables, but it would be unique all the same

Quite true, but that is what I mean about "another uniqueness constraint". Personally, I usually call this "user_name" and have "user_id" be a auto increment primary key :).

The only reason I keep pushing this idea is that I have been bitten by it in the past. And in a OO-relational mapping class as well (although it was a custom class, and not a full system like yours). In the end I wrote a DBI wrapper which handled the differences between MySQL's 'last_insert_id' and PostgreSQL's 'pg_oid_status' which gave me a basic level of DBD agnosticism (well at least the DBD's I cared about that is).

Anyway, I'm not sure what I'm arguing anymore... I didn't get enough sleep last night and am not entirely coherent. :-)

Two words: Mountain Dew :)

-stvn

The only reason I keep pushing this idea is that I have been bitten by it in the past. And in a OO-relational mapping class as well (although it was a custom class, and not a full system like yours). In the end I wrote a DBI wrapper which handled the differences between MySQL's 'last_insert_id' and PostgreSQL's 'pg_oid_status' which gave me a basic level of DBD agnosticism (well at least the DBD's I cared about that is).

I'm wondering how much stuff from my Whatever::Object::save method needs to be pushed into the subclass. One possibility is just to have each subclass define an "extract_insert_id" method that gets invoked from inside Whatever::Object::save and takes the statement handle as an argument, returning $sth->{whatever} as appropriate to the DBD in question. This, of course, makes the assumption that auto-incrementation will be available in all databases, and that it will be provided via the statement handle. Another possibility is that the entire Whatever::Object::save method should be pushed into subclasses, since there is the possibility that I'll have to generate random primary keys and specify them as part of an insert, in the case of databases that don't do auto-increment.

I guess its a question of how much I care... What databases don't support auto-increment fields? Alas, my experience is limited solely to MySQL.

On a related thread, I'm wondering what is the most elegant way to organize the classes with which I am doing subclassing with respect to locating the code and namespacing. Under whatever namespace I eventually use, I have an Object class (we'll call it Whatever::Object) that lives in an Object.pm file. I also have classes Whatever::Object::Query and Whatever::Object::ResultSet, which live respectively in the files Query.pm and ResultSet.pm in a sub-directory called Object. So, the directory structure is something like...

lib/
  Whatever/
    Object.pm
    Object/
      Query.pm
      ResultSet.pm  

Now, relevant to our original thread, the Object class has a save method that either inserts the object into the database, or updates an existing record. The method, in a very non-DBD-agnostic way, currently grabs the mysql_insertid from the statement handle. What I want to do is change my Object constructor to bless the thingy into a subclass, e.g. Whatever::Object::MySQL, and that class will provide a save method that will deal with the assignment of surrogate primary key values in a DBD-specific fashion.

From the perspective of a class relationship hierarchy, it's clear in my mind. What is not clear to me, however, is where the subclass really ought to live. I suppose it could just live in the Object directory, e.g. in a file called MySQL.pm, along with Query and ResultSet, but that feels weird to me... It feels odd because in the case of Query and ResultSet you have the .pm files living somewhere simply because of namespacing, whereas in the case of the Object subclass for MySQL, it's living there because Object is its base class. Is it right for me to feel that this would be weird, or is this the standard way to it? The only alternative that I can fathom would be to put the MySQL class definition inside the Object.pm file. Would that be better or worse, and is there any other alternative?

It feels odd because in the case of Query and ResultSet you have the .pm files living somewhere simply because of namespacing,

Maybe that is not the best place to put Query and ResultSet then? If I were to guess, I would have thought they were more kind of like "inner-classes", meaning classes that Object used internally, OR that they were subclasses of Object. However, if they are actually on the same "level" as Object, I would recommend mimicing that in your directory/namspace structure. Keep in mind that you are adding a top level namespace Whatever::, which maybe can hold Object, Query and ResultSet for you?

Is it right for me to feel that this would be weird, or is this the standard way to it?

Trust your feelings young skyknight. Maybe you should try lowering the blast sheild. A true Perl Jedi does not need his eyes :P

Oh yeah, and no, there is no standard way of doing it, at least that anyone has told me :)

The only alternative that I can fathom would be to put the MySQL class definition inside the Object.pm file.

Yuk. Besides this will only temporarily solve your problem since someday you (or someone else) will need to write a PostgreSQL/Oracle/MSSQLServer/etc subclass for this and you will have to deal with it then.

Now, based on my still limited knowledge of your code, I would suggest moving Query and ResultSet up to the root namespace (since as you say they are only in Object for the namespace). This will allow you to easily place all subclasses in subdirectories. And while right now all you can see a need for subclassing is Object, you never know if at some point you may need to subclass Query or ResultSet as well. Doing it this way leaves that door open just in case.

lib/
  Whatever/
     Object.pm
     Object/
         MySQL.pm
         PostgreSQL.pm
         Oracle.pm
     Query.pm
     Query/
         MySQL.pm
         Oracle.pm
     ResultSet.pm
     ResultSet/
         Interbase.pm
[download]

-stvn

Maybe that is not the best place to put Query and ResultSet then? If I were to guess, I would have thought they were more kind of like "inner-classes", meaning classes that Object used internally, OR that they were subclasses of Object. However, if they are actually on the same "level" as Object, I would recommend mimicing that in your directory/namspace structure. Keep in mind that you are adding a top level namespace Whatever::, which maybe can hold Object, Query and ResultSet for you?

They are not really suited to being inner classes. Users create and use them directly. If you want to query a collection of objects, you create an instance of Whatever::Object::Query, and invoke its execute method, which returns an instance of Whatever::Object::ResultSet. You then repeatedly invoke that object's get_next_result method which returns instances of Whatever::Object subclasses.

At first glance, your suggestion to put Query and ResultSet at the top level sounds good, but upon looking at my larger collection of classes, you'll realize that there is a snag. I also have Whatever::Link, Whatever::Link::Query, and Whatever::Link::ResultSet. These two objects are used in two scenarios: you have an object and want to do lazy loading of relatives at some point after it was loaded; you are loading a large collection of objects and want them loaded with one or more sets of relatives so as to keep the number of queries down.

I suppose I could bring these things top-level by squashing their namespaces, e.g. Whatever::Object::Query becomes Whatever::ObjectQuery and Whatever::Link::Query becomes Whatever::LinkQuery. I don't like that, though...

Now that I think about it, maybe I have the components of my name spacing all backwards! This all might make a lot more sense if instead of Whatever::Link::Query and Whatever::Object::Query I had Whatever::Query::Link and Whatever::Query::Object. In fact, I think that's perfect... Each of those could probably benefit (though I'm not yet sure how) from a common Whatever::Query base class. Furthermore, I could then use the Object subdirectory strictly for subclasses of Whatever::Object. This fixes all of my quandaries.

Now, however, I've thought of a problem that I'm going to encounter when trying to make DBD specific subclasses of Whatever::Object, e.g. Whatever::Object::MySQL. The general paradigm of my system already involves users creating subclasses of Whatever::Object, with the stipulation that the subclass provides a get_table_name method which is used both for for schema querying and object loading/saving. For example, if you wanted to have a Foo object, you'd have something like...

package Foo;

use strict;
use base qw/Whatever::Object/;

# decorator methods go here

1;
[download]

... and to create a Foo object you would just do

my $foo = new Foo();
[download]

... which would cause the "new" method in Whatever::Object to be invoked which would bless a thingy into class Foo.

As you can probably see by now, this makes things a little sticky... If I'm running in a MySQL environment, then what I really want Foo to be subclassing is Whatever::Object::MySQL, but certainly Foo should be DBD agnostic, knowing only about Whatever::Object and not its DBD specific subclasses. The only thing that jumps to mind right now is to have Whatever::Object::new to go ahead and bless the thingy into Foo, but to also reach into Foo's namespace and mangle its ISA array to have not Whatever::Object, but Whatever::Object::MySQL (or whatever DBD we're using). Am I crazy for even considering this? Is there a more elegant way of doing what I want to do?