[CDBI] Dealing with non-required primary keys

Matt S Trout dbix-class at trout.me.uk
Sun Sep 4 13:12:19 BST 2005


On Sat, Sep 03, 2005 at 09:20:55PM -0400, Rusty Phillips wrote:
> 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.

If you aren't using a sequence, Classs::DBI will automatically fill this
out by getting the last insert id appropriately (*not* using
$dbh->last_insert_id, mind, because this generally doesn't work). Class::DBI
itself can handle MySQL and SQLite out of the box, and the db-specific
subclasses by and large handle it pretty well themselves.

DBIx::Class takes a slightly different approach and provides a collection
of components that each do the job for a particular database - see

http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/lib/DBIx/Class/PK/Auto/

for a list of the ones currently available (and note that in some cases these
do a slightly better job than the Class::DBI::* subclasses due to our
faster development cycles and IMO cleaner architecture).

If you don't have existing Class::DBI code, I'd recommend evaluating
DBIx::Class as a possible alternative. Note *evaluating* - in spite of being
the project founder for DBIx::Class, I'm not a religious war kinda guy and
would much prefer people to pick the one that works best for them.

-- 
     Matt S Trout       Specialists in perl consulting, web development, and
  Technical Director    UNIX/Linux systems architecture and automation. Mail
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +




More information about the ClassDBI mailing list