Forum OpenACS Development: group_types: why not referenced in groups

But before denormalizing it's normal to evaluate whether or not doing the denormalization will improve performance considerably.  Will it?  Remember we will need an index on the referencing column in group_type, otherwise deleting object_types will do a sequential scan on group_types when the foreign key check is made so an error or cascade op can be triggered.

So if all this is being used for is to grab the group type's default join policy I'd question if the denormalization would be worth it ...

I guess what I was getting at overall is this:

• If the default join policy can be modified per group, then why do we need a whole separate table to hold this "convenience" information?
• Adding a column to groups with the group_type->object_type reference would make it really easy to get groups of a given type. You don't need to do this, but it would save a join.
• Maybe the join policy default could be inherited from the package instance under which the group is created. I noticed, with amazement that if you create groups via pl procedures they don't have a package_id associated with them, and they therefore don't show up in the subsite admin ui of any subsite, not even the one mounted on '/'. Othewise, a group with no associated package_id would have a default join policy of closed.
• There should be some admin interface, somewhere that actually looks at what is in the database instead of qualifying the query with things like package_ids and permissions to view. (That is what the permission check on the package_id is supposed to take care of in one simple check.) Queries like the following on group-types/one:

      select my_view.group_name, my_view.group_id
      from (select DISTINCT g.group_name, g.group_id
             from acs_objects o, groups g,
                  application_group_element_map app_group, 
                  all_object_party_privilege_map perm
            where perm.object_id = g.group_id
              and perm.party_id = :user_id
              and perm.privilege = 'read'
              and g.group_id = o.object_id
              and o.object_type = :group_type
              and app_group.package_id = :package_id
              and app_group.element_id = g.group_id
            order by g.group_name, g.group_id) my_view 
      limit 26
  

  Okay, that is a completely separate issue, sorry.

Question is: is any of this useful, or just a pain since things basically work anyway, and when would I get time to make changes that would probably affect vendor packages?

The Oracle Magazine May 2003 had an article on the slowness of PL/SQL functions in SQL functions. It can be found here: http://otn.oracle.com/oramag/oracle/03-may/o33asktom.html and it's the answer to the very question ("I created a PL/SQL function to compute the distance between two points, but it takes over one-half hour to execute when I call it from SQL. ")

Interesting enough: the slow PL/SQL function here doesn't access the database at all, the whole time gets waste by context switches between the SQL and the PL/SQL engine.