[CDBI] Dealing with non-required primary keys

01:20 on 04 Sep 2005

There's a fairly common situation that comes up in databases that I
can't seem to deal with in CDBI.

Consider that you have a database like this (this is in MySQL, btw)
CREATE DATABASE CHEESE 
( typeid INTEGER AUTO_INCREMENT
, typename TEXT
, UNIQUE(typename)
, PRIMARY KEY(typeid)
)

This is done fairly often;  the result is that you get an index built
off of "typeid", but you can change the name (and size of the name, for
that matter) without too many problems.  It also means that if you
decide not to make names unique, you can do that too without a problem.

It's not really important to add a constraint to Class::DBI to handle
the typename; the RDBMS will take care of that.  What is important is
how to deal with the typeid.

I've thought of a couple of solutions, but none of them work very well:
1)  In Class::DBI, you can declare a sequence for "typeid" that matches
the one autogenerated by your RDBMS, but that's not really a good
solution.  You're locking yourself into a single database type, since
what the sequence is called changes from DBMS to DBMS; and you have to
figure out what the sequence is called inside each one you want to use.
I'm writing a module that uses Class::DBI, so I want it to be generic.

2)  You can assume that "set_up_table" from the subclass of Class::DBI
(such as, for example, Class::DBI::mysql) will take care of it.
Unfortunately, these modules are very far from working correctly with
the current version of most RDBMSs - at least the postgresql, mysql, and
MSSQL modules are (and the mysql one isn't portable; it won't run on
Windows).  They can't be depended on for big things like that.  It's
also kind of unnecessary, since the database will use the sequence
entirely without the help of Class::DBI.  Plus, you're limiting yourself
to only those databases that have subclasses in Class::DBI.  All that is
needful is that insertion is allowed without the primary key by
Class::DBI.

So what would you recommend?  Is this a technical feat that can't be
accomplished in Class::DBI?  I should mention that "don't use primary
keys" isn't really an option.  I need to use primary keys because I'm
using quite a few "has_a" relationships in other modules that use the
primary keys I'm using.

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi