Forum OpenACS Development: Response to ACS 4.x won't scale (I hope I am wrong)

Collapse
Posted by Dave Hwang on

I modified Jon's upgraded acs_permission.permission_p() code and got some significant performance improvements by making two changes. First, I ditched using DECODE and WHERE EXISTS, in favor of simply using COUNT() and using sub-selects instead of joins in the group related sections. Secondly, I changed the order of precedence in the permissions check to Public, direct, group, and group_rels.

create or replace package body acs_permission 
as 
  procedure grant_permission ( 
    object_id    acs_permissions.object_id%TYPE, 
    grantee_id   acs_permissions.grantee_id%TYPE, 
    privilege    acs_permissions.privilege%TYPE 
  ) 
  as 
  begin 
    insert into acs_permissions 
      (object_id, grantee_id, privilege) 
    values 
      (object_id, grantee_id, privilege); 
  exception 
    when dup_val_on_index then 
      return; 
  end grant_permission; 
  -- 
  procedure revoke_permission ( 
    object_id    acs_permissions.object_id%TYPE, 
    grantee_id   acs_permissions.grantee_id%TYPE, 
    privilege    acs_permissions.privilege%TYPE 
  ) 
  as 
  begin 
    delete from acs_permissions 
    where object_id = revoke_permission.object_id 
    and grantee_id = revoke_permission.grantee_id 
    and privilege = revoke_permission.privilege; 
  end revoke_permission; 
  -- 
  function permission_p ( 
    object_id    acs_objects.object_id%TYPE, 
    party_id     parties.party_id%TYPE, 
    privilege    acs_privileges.privilege%TYPE 
  ) return char 
  as 
    v_count     INTEGER; 
  begin 
    -- Check public permissions 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id = -1; 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- Check direct permissions 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id = permission_p.party_id; 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- Check group permmissions 
    SELECT COUNT(*) INTO v_count
    FROM acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id IN (SELECT DISTINCT group_id 
                         FROM   group_approved_member_map 
                         WHERE  member_id = permission_p.party_id); 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    -- relational segment approved group 
    SELECT COUNT(*) INTO v_count 
    FROM   acs_object_grantee_priv_map 
    WHERE object_id = permission_p.object_id 
    AND   privilege = permission_p.privilege 
    AND   grantee_id IN (SELECT DISTINCT segment_id 
                         FROM   rel_seg_approved_member_map 
                         WHERE  member_id = permission_p.party_id); 
    if v_count > 0 then 
        return 't'; 
    end if; 
    -- 
    return 'f'; 
  end; 
  -- 
end acs_permission; 
/ 
show errors 

The big bottleneck was the performance of the group and group-rels related permission checks, which is now much better. Everything seems to work great now, but am I missing something?