Forum OpenACS Q&A: How to create a group?

<p>
OK, here's what I think I've discovered so far:
</p>
<pre>
Object Type                Group Type
Created Date              Created Date for group
Creation IP Address        IP of person creating group
Last Modified On          Last modified date for group
Modifying IP Address      IP of person modifying last
Creation user              User ID of user creating group
Context ID                ?? I picked 0 because I did not
                          want this group to inherit permissions
                          from any other group
Email Address              Email address of group (?)
URL                        URL for group
Group name                Name of group
---
Now these I think (from looking at the code) look like fields for creating the relation between this group and some other group.
Object Type                Object type of group relation
                          (I picked "membership_rel")
Created Date              ?
Creation IP Address        ?
Last Modified On          ?
Modifying IP Address      ?
Creation user              I picked my userid
Context ID                0 again.
</pre>
<p>
So I successfully create a group (so I think), but when I go back to the group admin screen and click on the group I get
</p>
<pre>
ora8.c:3930:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00904: invalid column name

SQL:

        select *
          from (SELECT groups.group_id,
      groups.group_id as object_id,
      groups.object_type,
      groups.creation_date,
      groups.creation_ip,
      groups.last_modified,
      groups.modifying_ip,
      groups.creation_user,
      groups.context_id,
      groups.email,
      groups !>>>!url,
      groups.group_name
  FROM groups
)
        where object_id = :object_id


    while executing
"ns_ora select nsdb0 {

        select *
          from (SELECT groups.group_id,
      groups.group_id as object_id,
      groups.object_type,..."
    ("uplevel" body line 1)
    invoked from within
"uplevel $ulevel [list ns_ora $type $db $sql] $args"
    invoked from within
"db_exec select $db $full_statement_name $sql"
    invoked from within
"set selection [db_exec select $db $full_statement_name $sql]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
    set selection [db_exec select $db $full_statement_name $sql]

    set counter 0
    while { [db_getrow $db $selection] } {
        incr co..."
    (procedure "db_foreach" line 35)
    invoked from within
"db_foreach attribute_select "
        select *
          from ($package_object_view)
        where object_id = :object_id
    " {
        foreach key $at..."
    (procedure "attribute::multirow" line 25)
    invoked from within
"attribute::multirow  -start_with group  -datasource_name attributes  -object_type $group_type  $group_id"
    ("uplevel" body line 57)
    invoked from within
"uplevel {
          # /packages/acs-subsite/www/admin/groups/one.tcl

ad_page_contract {
    View one group.

    @author Michael Bryzek (mailto:mbryzek@arsdigi...";
    (procedure "code::tcl::/home/cro/wd/ncacasi/packages/acs-subsite/www/adm..." line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    invoked from within
"uplevel {

    if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init t..."
    (procedure "adp_prepare" line 3)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    invoked from within
"$handler"
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
    $handler
      } ad_script_abort val {
    # do nothing
      }"
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
    rp_serve_abstract_file "$root/$path"
    set tcl_url2file([ad_conn url]) [ad_conn file]
    set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."
</pre>

<p>
I'm so confused.  Suggestions appreciated.
</p>
<pre>

<em>but it looks like this code if fundamentally hosed. </em>

<p>Eeew.</p>
<p>
Thanks for the response.  Well, if you can direct me to an example of some tcl/plsql code somewhere in the system that successfully creates group types and groups I'll see what might be missing.
</p>
<p>
That's a pretty massive thing to have been left out, no?
</p>

OK, now I'm really confused.

When I create a group, I'm creating an object that has "Party" as it's supertype, which in turn has acs_object as it's supertype. The automatically generated form shows all the attributes of the group, party, and acs_object because of the inheritance tree.

I can confirm this by executing the query that goes with the proc "package_create_attribute_list" (which is called in the course of constructing the form). I get this:

ATTR_TABLE_NAME	ATTR_COLUMN_NAME	ANCESTOR_TYPE
GROUPS	        GROUP_NAME	        group
PARTIES	        EMAIL	                party
PARTIES	        URL	                party
ACS_OBJECTS	OBJECT_TYPE	        acs_object
ACS_OBJECTS	CREATION_DATE	        acs_object
ACS_OBJECTS	CREATION_IP	        acs_object
ACS_OBJECTS	LAST_MODIFIED	        acs_object
ACS_OBJECTS	MODIFYING_IP	        acs_object
ACS_OBJECTS	CREATION_USER	        acs_object
ACS_OBJECTS	CONTEXT_ID	        acs_object

Ah! I think, this is why the form on the Oracle side doesn't match the one on the Pg side--the Pg query must be returning something different. But nope, that's not the case. They return the same stuff.

So I'm stumped trying to debug this.

<p>I think I found the problem.
</p>
<p>
In packages/acs-subsite/www/admin/groups/one.tcl there is a call to attribute::multirow, which calls package_object_view to get the list of attributes associated with the object.  That function calls a helper function, which executes this query on Oracle:

<pre>
select a.attribute_id,
      nvl(t.table_name, a.table_name) as table_name,
      nvl(a.column_name, a.attribute_name) as attribute_name,
      a.pretty_name,
      a.datatype,
      case when a.min_n_values = 0 then 'f' else 't' end as required_p,
              a.default_value,
              t.table_name as object_type_table_name,
              t.id_column as object_type_id_column
          from acs_object_type_attributes a,
              (select t.object_type, t.table_name, t.id_column, level as type_level
                  from acs_object_types t
                start with t.object_type='acs_object'
              connect by prior t.object_type = t.supertype) t
        where a.object_type = 'group'
          and t.object_type = a.object_type
        order by type_level
</pre>
<p>
(I inserted the values for the bind variables that should be there when someone is querying for a group)
</p>
This returns
<pre>
ATTRIBUTE_ID    TABLE_NAME    ATTRIBUTE_NAME    PRETTY_NAME    DATATYPE    REQUIRED_P    DEFAULT_VALUE    OBJECT_TYPE_TABLE_NAME    OBJECT_TYPE_ID_COLUMN
1    groups    object_type    Object Type    string    t        groups    group_id
2    groups    creation_date    Created Date    date    t        groups    group_id
3    groups    creation_ip    Creation IP Address    string    t        groups    group_id
4    groups    last_modified    Last Modified On    date    t        groups    group_id
5    groups    modifying_ip    Modifying IP Address    string    t        groups    group_id
6    groups    creation_user    Creation user    integer    f        groups    group_id
7    groups    context_id    Context ID    integer    f        groups    group_id
8    groups    email    Email Address    string    f        groups    group_id
9    groups    url    URL    string    f        groups    group_id
12    groups    group_name    Group name    string    t        groups    group_id
</pre>
<p>
on my Oracle 8.1.7.2 installation.  Obviously this is wrong, and it's the reason for the request error above, but I've been staring at the query for an hour and I can't see why this is the result.  This is with a fresh load of the data model.
</p>
<p>
The subselect returns the correct data (e.g. the OBJECT_TYPE goes with the TABLE_NAME)
</p>
<p>On my Pg 7.1.3 installation, the result is correct (e.g. the TABLE_NAME column contains the column that actually holds the attribute in question).
</p>
<p>
Can someone run this query against an Oracle instance of the data model and tell me if they get the same result?  If Oracle users get the correct result, does that mean my Oracle installation is corrupt somehow?
</p>
<p>We are on Oracle 8.1.7.2 (Linux)</p>

Steve,

Yes, that seems to fix it.  It also fixes the issue that started this whole thread where I was seeing all the attributes for the group itself when I tried to create it.

Did you submit this as a patch or should I?

Ok, I checked it in and mailed Ben.