This sounds pretty interesting, and frankly very reminiscent of the project that originally got me interested in perl. I was working on an order entry system in VB with a Sybase database as the back end. I was designing the OE class model as VB COM objects and didnt want to have to do all the DB work by hand as well. Enter Perl, parsing VB class modules and automatically generating a DB, as well as modifying the class model to add DB interaction. I completed my model, but not long after the project was killed (turned out it was just one of like 10 other similar projects in the company, and eventually they were all put on hold so that a better plan could be developed :-) I was happy as it meant I didnt have to work in VB anymore, and it was the last project where I have had to do more the maintenance work on VB. A later project was in pure perl and involved essentially the reverse, using a DB design tool that produced XML we developed a XSLT stylesheet that generated DB aware classes from the model. That one was live for a year, fulfilled its purpose and was shut down when it is was no longer needed. (It was the only interim solution that ive seen that actually was interim :-)

The reason I say all this is because my conclusions drawn from them is that tools like this rapidly become unsuitable for serious work. They artificially tie your hands to a bunch of presuppositions that sound good at the time, but dont grow well. So im very suspicious of tools and frameworks that work like this. I tend to view them as wasted efforts that while sounding good in theory dont actually work out as useful as they should. Dont get me wrong, Im not saying this is a wasted effort. At least one respondant has replied very positively, and even if only they and you derive benefit from this tool then you've achieved something positive. Im just trying to point out a few things that I observed in the projects I mention.

With regard to naming conventions, I think a system like this becomes very dependent on the power and expressivness of naming conventions (this assumes that as you said you dont want to use more robust structural discovery techniques). You touch on the problem yourself with the naming convention you mention. Primary keys are represented by the table name, and foreign keys are represented by the table name refered to. Personally I have never designed or used a DB that conformed to either of these assumptions. :-)

First off, my table names are often long. Second off, how do you represent a table that has two references to another table this way? For instance a DB that represents a family relationships. A "person" table may have two fields "mother" and "father" and then perhaps we may have a second table "guardians" that contains two references, one to the guardian one to the gardee (hmm, not sure the word for that :-), How do we represent either of these structures using your naming convention? How do you represent composite primary keys? How do you represent complex integrity constriants? Now as one respondant already pointed out, we dont need to _always_ use your code, but if its to be really useful I think you need to resolve some of these problems. The double reference one is very common in my experience.

Naturally I have some ideas on the matter. :-) One thing that we did was use a more complex naming scheme. In our scheme every table had an associated prefix, normally generated from the first letter in each word in the table name, or lacking multiple words for truly base table names some other easily remembers abbreviation (such as cs or cust for customer). Then we used suffixes to represent foreign key or primary key status. So a table customer_relationship that has an identity primary key would have that column called "cr_id_pk". If there is also a type table called "customer_relationship_type" then the reference to it in customer_relationship would be called "cr_crt_id_fk". The automatic tools can then use some rudimentary rules to figure out whats going on.

In the case I mentioned earlier of the family trees, we might have a table "people" (I chose this now, as it abbrreviates nicely) whose prefix is ppl. Now we might have fields "ppl_id_pk", "ppl_father_ppl_id_fk", "ppl_mother_ppl_id_fk", etc. When the discovery mechanism runs it identifys the fields ending in fk, pulls off the tables own prefix, and then keeps removing words from the front until it matches a primary key it knows about. This approach covers cases where you have composite primary keys, multiple references to the same table, foreign keys that are part of a composite primary key (its not hard to work out a way to handle this using the suffix pk).

Does all of the above start sounding nasty? It should. And its the reason why I steer clear of stuff like this nowadays. Anyway, I hope you find some of this useful to your efforts, while I am a cynic, Im not totally negative. :-)

Thanks for your comments, demerphq ;). I wonder if I didn't make my goals very clear. I'm not so out of touch that I really want to build a complete autodetecting replacement for Class::DBI. I want to make the "simple" things automatic, and try to make the definition of "simple" encompass as much as is reasonable. Integrity constraints, composite primary keys -- these are not in my definition of "simple" at the moment, and you are right, a naming-based system like this will start getting nasty (read: unreasonable) if I tried. I don't want the whole kitchen sink.. I want a self-loading and self-emptying dishwasher ;)

I suppose my target audience would be anyone who wants to quickly write some glue code where the data is to stored in a "simple" DB. Maybe even this is unreasonable by your definition of "serious work", but in my experience I've written a lot of small projects where the database schema really is this simple. If you start out a project and decide your data needs composite keys, integrity constraints, whatever.. by all means don't use this! In the case where an application starts out within the scope of my module but grows to be too complex, I'd like to think that the OO interface being not entirely dissimilar to Class::DBI, a migration wouldn't be totally unreasonable. You give a really good example about projects growing beyond the scope of this type of thing, but I hope that the possibility of that happening doesn't make it a useless project, or scare people from at least trying it.

One thing you bring up that I really do want to have a plan for is the mother/father relation example. I've run into this when trying to represent a tree in the database (using a parent column). It's a situation that's common enough that I should have a plan for it. The biggest problem is that MySQL not only ingores, but doesn't even store foreign key metadata on a column (Update: Oops: except on InnoDB tables, thanks perrin ;)). With Postgres or some other "real" RDBMS, I could autodetect these with the foreign key info in the metadata. Maybe this will be the thing to finally convince me to switch (although my webhost still has only MySQL). The only problem might be automating a good name for the reverse relation. The relation is "parent" in one direction and "child" in another, which might not be easy to automate (although the idea of using some Lingua modules to do it intelligently is very exciting).

blokhead

MySQL supports foreign keys when you use InnoDB tables, and the information is accessible.

• Reads the schema from a database
• Auto-generates classes from that schema and provides them for you
• Auto-generates FK relationships, based on the assumption of a well-normalized and well-named schema
• Allows you to treat the database as a true OO store.

That sounds like Class::DBI::Psychic to me, but no-one will use that. So, call it Class::DBI::Simple and have done with it. :-)

And, yes, I want this code. Because, I use your interface for the easy stuff and I can still pass the $dbh some complex SQL, should I need it. It makes the hard (or annoying) things easy and still lets me do the impossible.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

It's pretty well established at this point that calling a module Class::DBI::* means it has something to do with the Class::DBI module. Class::DBI is a specific module, not a generic term. Things like "Persistence" or "Object-Relational" are generic terms, but not Class::DBI.

So, XML::Parser::Lite has to do with XML::Parser? I don't think so ... this module is one that provides a Class::DBI-like interface, but does it in a simple manner. Hence, Class::DBI::Simple.

Now, I can understand your hesitation because XML::Parser can be dropped in for XML::Parser::Lite with no changes. This module cannot be replaced directly with Class::DBI without a problem. Interesting problem ...

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Would you say that your module had advantages over things like Tie::DBI or Tie::Table ?

As for a name. Maybe Class::Tables?

I would say that simply having an OO interface is a big advantage. The popularity of Class::DBI seems to suggest that people enjoy accessing their relational data as objects.

There seem to be a few problems with the tied hash approach. For inflating foreign keys and other functions, Tie::Table is forced to mix OO/hashref metaphors (Tie::DBI doesn't seem to support foreign key inflation which really limits the comparison). From its POD:

  %company_14_users = %{ $company->{14}->user };
  ## [blokhead: it's not clear whether %company_14_users is magical or
+ not...]

  $company->{14}->{tax_num} = "111-22-3333";
  $company->{14}->write;
[download]

Its POD goes on to scare/confuse me with this:

  $company_14 = $company->{14};
  $company_14->{tax_num} = "123456";

  # Wrong example:
  # $company->{14}->{tax_num} = "123456"
  # This doesn't work, because it always create a new Row object,
[download]

I've never extensively used tied objects, but I'm wondering if this strange behavior is somehow related to the difficulty of nesting, returning tied objects? It's also not clear whether I can add other non-persistent attributes and methods to these objects. With OO, I can always add subclass data, helper methods, etc.

The only thing I can see in favor of having objects as hashes is for quicker glueing with HTML::Template. Of course, I could always add a ->to_hash method to my objects as well ;)

Also, one of my key reasons for doing this module is so that I never have to tell some interface about how my tables are related. With those modules, you have to set up a tied hash for each table, telling it about your primary key and foreign keys. Obviously you could make a wrapper to scan your database and build the appropriate tied hashes, but if my module can do it out of the box, why not? I know there's nothing earth-shattering about this idea, but it's saved me a lot of time along the way.

blokhead

When I first read your description, I really liked the simplicity of what you described. Then I got to thinking about tables, and what you might want to do with them and it struck me that hashes seem like a fairly natural interface to them. Looking under cpan://Tie::*, I found the modules I listed and I scanned the pods and decided I couldn't tell from that whether they had everything your had or not, so I thought I would ask, and get the skinny straight from the horses mouth:)

I really like the OO-metaphore for accessing external data. I'm less concerned by the purely syntactic differences between the two interfaces than you are, both have a sufficiently OO-style for my tastes, and in many respects I prefer the somewhat simpler, more direct lvalue syntax of accessing the data. I don't see any reason why a tied interface couldn't make connections between tables automatically following a similar hueristic to that your module uses. It ought to also be possible to have the tied interface handle inflation properly, but that is speculation, and in any case, it would require modifying or subclassing those modules, and your already does it.

I've encountered, the 'problem' with nested ties before, and whilst I'm not positive that it can't be solved, I haven't done so myself, and I've seen it discussed other places, so maybe it cannot be (adequately) solved.

Your argument about tie requiring me to mix the interfaces for anything that goes beyond simple accesses to the data is a convincing one. That to used the mixed interface requires me to hold two handles to each object strengthens it.

I was going to ask if your module allowed me to "refresh" the interface mid-stream so that if a view was updated as a result of a triggered, stored procedure, the object you had built for that view would reflect the change--in which case, I think I would be totally sold on it--but MySQL doesn't support these features, so that's an academic argument.

As it is, I like the sound of your module. Anything that keeps SQL out of applications is a good idea in my book, and for the purposes you devised it, it sounds perfectly simple and useful.

---

Examine what is said, not who speaks.
"Efficiency is intelligent laziness." -David Dunham
"When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong." -Richard Buckminster Fuller
If I understand your problem, I can solve it! Of course, the same can be said for you.

Could you emphasize the differences between the features of your proposed module and the ones already available in Class::DBI::Loader?

Thanks

I knew once upon a time I saw this, but have never been able to relocate it again, so thanks for mentioning it. Now I can finally see what it does! It gets a list of tables from the DB and then calls the set_up_table method from the driver-specific parts of Class::DBI. Browsing the source of these, none of them seem to do much more than make a list of columns and figure out the primary key. They don't setup lazy loading or any foreign keys, but I think I know why. This approach doesn't seem to match the philosophy of Class::DBI -- it wants you to be able to name a foreign key relation however you like, and lazy load columns however you like (and as the all-purpose tool out there, it should). My module has a much lazier usage philosophy. It's willing to reduce your flexibility and give you some (I think) very reasonable defaults in order to get you up and running with the easy stuff in less time.

blokhead

i agree. and i like that approach. but unless this works with Oracle, i won't be using it. i would suggest that before you post to CPAN, you refactor to make the user interface seperate from the backend interface -- modularize the MySQL specific stuff, and allow other backends to be written. then i can write my own Oracle interface, and i might take a second look at your implementation.

~Particle *accelerates*

By the way, how do you clean up the hash that hold all the data in the superclass? Does it just grow forever, or do you have some way of doing reference counts to clean it up when objects go out of scope?

If I'm reading blokhead's description, his module also sets up the classes for you. Right now, you have a bunch of classes that are like 10 lines long. His removes that need by making assumptions about what those classes will generally look like. To me, that's a huge feature.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Class::DBI::Loader does the same thing, and SQL::Translator does the same but with actual files.