Forum OpenACS Q&A: content_type__create_type for both item and revision table?

1: content_type__create_type for both item and revision table?

Posted by James Thornton on 06/09/04 09:18 PM

How should you create content types so that attributes for both the item and revision table are included in the generated views?

For example, I have two tables named ec2_product and ec2_product_revision, and until now I have only created a content_revision type named ec2_product_revision so attributes from ec2_product have not been included in ec2_productx. I tried creating a content_item type named ec2_product, but it's giving an error saying that tree_sortkey is duplicated in the generated view.

2: Re: content_type__create_type for both item and revision table? (response to 1)

Posted by Ola Hansson on 06/09/04 10:04 PM

You're not supposed to extend the attributes in cr_items, only cr_revisions. Here's what the docs say about creating attribute tables:

"Note that your extended attribute table must reference the cr_revisions table, not cr_items. As mentioned above, this allows you to maintain multiple revisions of the attribute data in tandem with revisions of the content object itself."

Hopefully somebody knows a way around this, as I can't think of one ...

3: Re: content_type__create_type for both item and revision table? (response to 1)

Posted by Dave Bauer on 06/09/04 10:15 PM

Ola is correct.

The most straightforward way to extend a content type is to subtype cr_revision. The additional complexity of extending the cr_item is not worth the storage savings or having some non-versioned extedned attributes in most cases.

So the simple solution is to store all of your type specific attributes in the subtype of content revision.

4: Re: content_type__create_type for both item and revision table? (response to 1)

Posted by Jade Rubick on 06/09/04 10:24 PM

However, if you do want to have non-revisioned extension (to cr_items), you can do so. As Dave points out, often it is not worth it, but it perfectly possible. I describe this in the CR tutorial, which I've put into the docs for acs-content-repository on HEAD. It's also on http://rubick.com/openacs

5: Re: content_type__create_type for both item and revision table? (response to 3)

Posted by James Thornton on 06/09/04 11:13 PM

If you store all attributes as a content_revision, how do you specify unique constraints?

6: Re: content_type__create_type for both item and revision table? (response to 4)

Posted by Jeff Davis on 06/09/04 11:32 PM

Bug tracker is an example of something that extends both cr_items and cr_revisions, although only having looked at the sql it looks like it does not in fact extend cr_items (even though th bt_bugs table pk references cr_items) since it does:

select acs_object_type__create_type (
    'bt_bug',
    'Bug',
    'Bugs',
    'acs_object',
    'bt_bugs',
    'bug_id',
    null,
    'f',
    null,
    'bt_bug__name'
);

I guess Lars could comment on whether that is simply a bug or is some sort of shortcoming with subclassing cr_item.

7: Re: content_type__create_type for both item and revision table? (response to 5)

Posted by Jeff Davis on 06/09/04 11:41 PM

James, funny you should mention it...we were talking about it just today on irc.

You could enforce uniqueness in a revision table with a partial index.

You can do something similiar with a functional index on oracle (there is an example in chapter 7 in Tom Kyte's Effective Oracle by Design titled selective uniqueness).

8: Re: content_type__create_type for both item and revision table? (response to 1)

Posted by Ola Hansson on 06/09/04 11:42 PM

James, you can create the extension table manually and define the constraints there. That is, don't let content_type__create_attribute add the column(s).

However I believe Jade has a trick up his sleeve which actually lets content_type__create_attribute work its magic to full extent. See his guide, if I'm not misstaken.

9: Re: content_type__create_type for both item and revision table? (response to 1)

Posted by Jade Rubick on 06/10/04 12:58 AM

Actually, I recommend doing it the way Ola recommends: if you need non-revisioned items, extend cr_items by sub-classing it, like I describe in my tutorial.

If you need foreign key references on versioned items, you can add that in as a part of the definition. I also describe this on my tutorial page.

Jeff's partial index trick is a very nice tool to have in your toolbox.

Have we answered your question?