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

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.

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

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.

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
If you store all attributes as a content_revision, how do you specify unique constraints?
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 (
I guess Lars could comment on whether that is simply a bug or is some sort of shortcoming with subclassing cr_item.
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).

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.

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?