Re: Using CDBI with an ORDBMS

[prev] [thread] [next] [Date index for 2005/04/30]

>>>>> "David" == David Christensen <dwc@xxxx.xxx> writes:

David> If you are trying to use your inherited or base tables in a
David> foreign-key relationship, there are a few wrinkles you should know
David> about:

David> 1) Even if your id fields are declared unique/primary key, there is no
David> mechanism enforcing uniqueness across tables.  I.e., in the example
David> schema you gave you could have 3 records with id = 1 (one each for
David> Bookmark, Folder, and URI), and when you do a SELECT * from URI, you
David> get three entries with id = 1.  You can work around this somewhat with
David> a good sequence generator, but if you ever have someone manually
David> inserting or updating data this can cause some unexpected headaches.

One way to cheat would be to use "oid" as your "primary key".  CDBI
already ensures that you can't update the primary key.  You'd just
have to ensure that the oid column was undef during the before_create
hook.

Then, it doesn't matter if you select from the parent table or any of
the child tables, since the oid column is guaranteed unique across the
database.

The problem is that you can't use this as a foreign-key mapping, so
you have to work out something else other than has_a.  Also, when the
row is updated in any way, it gets a brand new oid (thanks to MVCC).
Not sure how CDBI would handle that.

        -- 
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@xxxxxxxxxx.xxx> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!