Forum OpenACS Q&A: Content repository with ACS5.0.2

Collapse
1: Content repository with ACS5.0.2
Posted by Sung Hong on 02/18/04 12:37 AM
I am trying to use the content repository and acs_objects table is missing a column tree_sortkey.  Is the content repository data model for ACS5.0.2 complete or is the above column must be defined by the developer?

select o.object_id,
              o.object_type,
              o.context_id,
              o.security_inherit_p,
              o.creation_user,
              to_char(o.creation_date, 'YYYY-MM-DD HH24:MI:SS') as creation_date_ansi,
              o.creation_ip,
              to_char(o.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified_ansi,
              o.modifying_user,
              o.modifying_ip,
              o !>>>!tree_sortkey,
              acs_object.name(o.object_id) as object_name
        from  acs_objects o
        where  o.object_id = :object_id

Collapse
2: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Sung Hong on 02/18/04 12:57 AM
The above code is located in /packages/acs-tcl/tcl/object-procs-oracle.xql
Collapse
3: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Jade Rubick on 02/18/04 09:27 PM
You're working on Oracle, right?

I'm not sure if Oracle needed the tree_sortkey code. What does 'describe acs_objects' say about the acs_objects table?

The CR does have very good support for hierarchical queries. I'm not sure if you'd use tree_sortkey, or use CONNECT BY. I just don't know enough details about how it is implemented.

Collapse
4: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Don Baccus on 02/18/04 10:07 PM
In the Oracle version we use CONNECT BY to implement hierarchical queries.

PostgreSQL does not have an equivalent feature, so we added tree_sortkeys to acs_objects and certain other object types.  These sortkeys allow us to construct hierarchical queries in PostgreSQL.

You should study some of the hierarchical queries that exist in the Oracle version to figure out how to write your query correctly.  Be warned that CONNECT BY in 8i has some annoying and confusing restrictions (some of which have been removed in 9i).

Collapse
5: Re: Content repository with ACS5.0.2 (response to 4)
Posted by Jade Rubick on 02/18/04 10:20 PM
Don, is the context_id used to build the hierarchy in Oracle?

I'll put a note in the documentation (the advanced tutorial) that tree_sortkey is only in the Postgres version of acs_objects.

If you clarify this, I'll put it in the docs.

Collapse
6: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Don Baccus on 02/18/04 11:41 PM
The content repository uses the content_item's parent_id to build the hierarchy.

Packages built on base acs_objects use and/or abuse context_id inconsistently in regard to using it to maintain a phsyical (as opposed to permissions inheritence) hierarchy.

Collapse
7: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Sung Hong on 02/19/04 12:16 AM
Thank you all but I want to remind that the code that is breaking is in the kernel of acs in /packages/acs-tcl/tcl/object-procs-oracle.xql  If CONNECT BY should be used for ORACLE, the above file should be changed.
Collapse
8: Re: Content repository with ACS5.0.2 (response to 7)
Posted by Jade Rubick on 02/19/04 07:18 PM
What proc is broken? If it's a bug, definitely post it in bug-tracker.
Collapse
9: Re: Content repository with ACS5.0.2 (response to 1)
Posted by Don Baccus on 02/19/04 11:34 PM
That wasn't at all clear from your first post, Sung!  Yes, the query's in error.  I've committed a patch to HEAD.  It's not worth fixing in the 5.0 branch because no code uses it (I checked).