Re: Using CDBI with an ORDBMS

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

Michael,

(Sorry if I drift off-topic for a bit...)

A quick word of caution about the object-relational features of Pg. 
(disclaimer: I don't know if this is still the case with postgres 8; 
these are my experiences based on 7.4)

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

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

2) Other tables which reference either the base tables or the inherited 
tables only see the records which have been explicitly entered into the 
specific table.

If I have another table:

CREATE TABLE User_prefs (user_id, uri references URI, comments);

Then the "uri" field *must* exist in the URI table, and not one of its 
derived tables.  (I believe this is because object-relational features 
are essentially a view joining two unrelated tables---we're using a 
lower-level mechanism to emulate this higher-level idea.)

I worked around this for a while by having an insert trigger which 
stuck the id of the newly generated records into a table whose sole 
purpose was to hold ids for the base table and all of its subtables, 
but it got to be a mess when you wanted to have actions on delete, etc. 
as it was not the actual table which held the data.

There are workarounds for both of the above, and some of the cases will 
be non-issues if you are using CDBI exclusively to interact with your 
data, but in my case I found inheritance not to be quite there yet.  
(It should be known that these are documented shortcomings of the 
ORDBMS system, so more of a "gotcha" than bugs...)

Hope this helps,

David Christensen

On Apr 29, 2005, at 7:16 PM, Michael Nachbaur wrote:

> I'm designing a database schema that uses the ORDBMS table inheritance 
> features of PostgreSQL to make two tables inherit from a common third 
> table.
>
> I wanted to know if there was any way within Class::DBI to express 
> that relationship, so it will DWIM.  Consider the following 
> pseudo-SQL:
>
> CREATE TABLE Bookmark (target) INHERITS (URI);
> CREATE TABLE Folder (entries) INHERITS (URI);
> CREATE TABLE URI (id, name, description);
>
> If I insert a record into either tables Bookmark or Folder, it will 
> show up in URI as well, though only with the columns defined within 
> URI.
>
> Therefore, I wanted to have Class::DBI return the appropriate object 
> type when I query from the URI table.  For instance:
>
> my $obj = URI->retrieve(10);
>
> If ID 10 is a Folder, I would expect it to return a Class::DBI object 
> for the folder class, giving me the columns "id", "name", 
> "description", and "entries".
>
> Is there a way of doing this currently with Class::DBI, or do I need 
> to create a new module to accomodate this?
>
> Thanks.
>
> -- 
> Michael Nachbaur <mike@xxxxxxxx.xxx>
> http://nachbaur.com/pgpkey.asc
>