LP#1178377: Make bib source optional element from unapi.bre

[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0175.schema.permission.usr_has_perm_at_nd-EVERYTHING.sql

BEGIN;

INSERT INTO config.upgrade_log (version) VALUES ('0175'); -- From patch by Jason Stephenson (applied by miker)

CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
    user_id    IN INTEGER,
    perm_code  IN TEXT
)
RETURNS SETOF INTEGER AS $$
--
-- Return a set of all the org units for which a given user has a given
-- permission, granted directly (not through inheritance from a parent
-- org unit).
--
-- The permissions apply to a minimum depth of the org unit hierarchy,
-- for the org unit(s) to which the user is assigned.  (They also apply
-- to the subordinates of those org units, but we don't report the
-- subordinates here.)
--
-- For purposes of this function, the permission.usr_work_ou_map table
-- defines which users belong to which org units.  I.e. we ignore the
-- home_ou column of actor.usr.
--
-- The result set may contain duplicates, which should be eliminated
-- by a DISTINCT clause.
--
DECLARE
    b_super       BOOLEAN;
    n_perm        INTEGER;
    n_min_depth   INTEGER;
    n_work_ou     INTEGER;
    n_curr_ou     INTEGER;
    n_depth       INTEGER;
    n_curr_depth  INTEGER;
BEGIN
    --
    -- Check for superuser
    --
    SELECT INTO b_super
        super_user
    FROM
        actor.usr
    WHERE
        id = user_id;
    --
    IF NOT FOUND THEN
        return;             -- No user?  No permissions.
    ELSIF b_super THEN
        --
        -- Super user has all permissions everywhere
        --
        FOR n_work_ou IN
            SELECT
                id
            FROM
                actor.org_unit
            WHERE
                parent_ou IS NULL
        LOOP
            RETURN NEXT n_work_ou;
        END LOOP;
        RETURN;
    END IF;
    --
    -- Translate the permission name
    -- to a numeric permission id
    --
    SELECT INTO n_perm
        id
    FROM
        permission.perm_list
    WHERE
        code = perm_code;
    --
    IF NOT FOUND THEN
        RETURN;               -- No such permission
    END IF;
    --
    -- Find the highest-level org unit (i.e. the minimum depth)
    -- to which the permission is applied for this user
    --
    -- This query is modified from the one in permission.usr_perms().
    --
    SELECT INTO n_min_depth
        min( depth )
    FROM    (
        SELECT depth
          FROM permission.usr_perm_map upm
         WHERE upm.usr = user_id
           AND (upm.perm = n_perm OR upm.perm = -1)
                    UNION
        SELECT  gpm.depth
          FROM  permission.grp_perm_map gpm
          WHERE (gpm.perm = n_perm OR gpm.perm = -1)
            AND gpm.grp IN (
               SELECT   (permission.grp_ancestors(
                    (SELECT profile FROM actor.usr WHERE id = user_id)
                )).id
            )
                    UNION
        SELECT  p.depth
          FROM  permission.grp_perm_map p
          WHERE (p.perm = n_perm OR p.perm = -1)
            AND p.grp IN (
                SELECT (permission.grp_ancestors(m.grp)).id
                FROM   permission.usr_grp_map m
                WHERE  m.usr = user_id
            )
    ) AS x;
    --
    IF NOT FOUND THEN
        RETURN;                -- No such permission for this user
    END IF;
    --
    -- Identify the org units to which the user is assigned.  Note that
    -- we pay no attention to the home_ou column in actor.usr.
    --
    FOR n_work_ou IN
        SELECT
            work_ou
        FROM
            permission.usr_work_ou_map
        WHERE
            usr = user_id
    LOOP            -- For each org unit to which the user is assigned
        --
        -- Determine the level of the org unit by a lookup in actor.org_unit_type.
        -- We take it on faith that this depth agrees with the actual hierarchy
        -- defined in actor.org_unit.
        --
        SELECT INTO n_depth
            type.depth
        FROM
            actor.org_unit_type type
                INNER JOIN actor.org_unit ou
                    ON ( ou.ou_type = type.id )
        WHERE
            ou.id = n_work_ou;
        --
        IF NOT FOUND THEN
            CONTINUE;        -- Maybe raise exception?
        END IF;
        --
        -- Compare the depth of the work org unit to the
        -- minimum depth, and branch accordingly
        --
        IF n_depth = n_min_depth THEN
            --
            -- The org unit is at the right depth, so return it.
            --
            RETURN NEXT n_work_ou;
        ELSIF n_depth > n_min_depth THEN
            --
            -- Traverse the org unit tree toward the root,
            -- until you reach the minimum depth determined above
            --
            n_curr_depth := n_depth;
            n_curr_ou := n_work_ou;
            WHILE n_curr_depth > n_min_depth LOOP
                SELECT INTO n_curr_ou
                    parent_ou
                FROM
                    actor.org_unit
                WHERE
                    id = n_curr_ou;
                --
                IF FOUND THEN
                    n_curr_depth := n_curr_depth - 1;
                ELSE
                    --
                    -- This can happen only if the hierarchy defined in
                    -- actor.org_unit is corrupted, or out of sync with
                    -- the depths defined in actor.org_unit_type.
                    -- Maybe we should raise an exception here, instead
                    -- of silently ignoring the problem.
                    --
                    n_curr_ou = NULL;
                    EXIT;
                END IF;
            END LOOP;
            --
            IF n_curr_ou IS NOT NULL THEN
                RETURN NEXT n_curr_ou;
            END IF;
        ELSE
            --
            -- The permission applies only at a depth greater than the work org unit.
            -- Use connectby() to find all dependent org units at the specified depth.
            --
            FOR n_curr_ou IN
                SELECT ou::INTEGER
                FROM connectby(
                        'actor.org_unit',         -- table name
                        'id',                     -- key column
                        'parent_ou',              -- recursive foreign key
                        n_work_ou::TEXT,          -- id of starting point
                        (n_min_depth - n_depth)   -- max depth to search, relative
                    )                             --   to starting point
                    AS t(
                        ou text,            -- dependent org unit
                        parent_ou text,     -- (ignore)
                        level int           -- depth relative to starting point
                    )
                WHERE
                    level = n_min_depth - n_depth
            LOOP
                RETURN NEXT n_curr_ou;
            END LOOP;
        END IF;
        --
    END LOOP;
    --
    RETURN;
    --
END;
$$ LANGUAGE 'plpgsql';

COMMIT;