[CDBI] Dealing with non-required primary keys

John Drago john.drago at data393.com
Sun Sep 4 02:35:53 BST 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)
> )

OK.

> 
> 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.

Right.

> 
> 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.

What do you mean - "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.

Is there something preventing you from doing it this way?


> 
> 
> _______________________________________________
> ClassDBI mailing list
> ClassDBI at lists.digitalcraftsmen.net
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi





More information about the ClassDBI mailing list