LP#1568046: Stamping upgrade script for eliminating connectby from functions
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0982.function.perm_functions_without_connectby.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('0982', :eg_version);
4
5 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
6         user_id    IN INTEGER,
7         perm_code  IN TEXT
8 )
9 RETURNS SETOF INTEGER AS $$
10 --
11 -- Return a set of all the org units for which a given user has a given
12 -- permission, granted directly (not through inheritance from a parent
13 -- org unit).
14 --
15 -- The permissions apply to a minimum depth of the org unit hierarchy,
16 -- for the org unit(s) to which the user is assigned.  (They also apply
17 -- to the subordinates of those org units, but we don't report the
18 -- subordinates here.)
19 --
20 -- For purposes of this function, the permission.usr_work_ou_map table
21 -- defines which users belong to which org units.  I.e. we ignore the
22 -- home_ou column of actor.usr.
23 --
24 -- The result set may contain duplicates, which should be eliminated
25 -- by a DISTINCT clause.
26 --
27 DECLARE
28         b_super       BOOLEAN;
29         n_perm        INTEGER;
30         n_min_depth   INTEGER; 
31         n_work_ou     INTEGER;
32         n_curr_ou     INTEGER;
33         n_depth       INTEGER;
34         n_curr_depth  INTEGER;
35 BEGIN
36         --
37         -- Check for superuser
38         --
39         SELECT INTO b_super
40                 super_user
41         FROM
42                 actor.usr
43         WHERE
44                 id = user_id;
45         --
46         IF NOT FOUND THEN
47                 return;                         -- No user?  No permissions.
48         ELSIF b_super THEN
49                 --
50                 -- Super user has all permissions everywhere
51                 --
52                 FOR n_work_ou IN
53                         SELECT
54                                 id
55                         FROM
56                                 actor.org_unit
57                         WHERE
58                                 parent_ou IS NULL
59                 LOOP
60                         RETURN NEXT n_work_ou; 
61                 END LOOP;
62                 RETURN;
63         END IF;
64         --
65         -- Translate the permission name
66         -- to a numeric permission id
67         --
68         SELECT INTO n_perm
69                 id
70         FROM
71                 permission.perm_list
72         WHERE
73                 code = perm_code;
74         --
75         IF NOT FOUND THEN
76                 RETURN;               -- No such permission
77         END IF;
78         --
79         -- Find the highest-level org unit (i.e. the minimum depth)
80         -- to which the permission is applied for this user
81         --
82         -- This query is modified from the one in permission.usr_perms().
83         --
84         SELECT INTO n_min_depth
85                 min( depth )
86         FROM    (
87                 SELECT depth 
88                   FROM permission.usr_perm_map upm
89                  WHERE upm.usr = user_id 
90                    AND (upm.perm = n_perm OR upm.perm = -1)
91                                 UNION
92                 SELECT  gpm.depth
93                   FROM  permission.grp_perm_map gpm
94                   WHERE (gpm.perm = n_perm OR gpm.perm = -1)
95                 AND gpm.grp IN (
96                            SELECT       (permission.grp_ancestors(
97                                         (SELECT profile FROM actor.usr WHERE id = user_id)
98                                 )).id
99                         )
100                                 UNION
101                 SELECT  p.depth
102                   FROM  permission.grp_perm_map p 
103                   WHERE (p.perm = n_perm OR p.perm = -1)
104                     AND p.grp IN (
105                                 SELECT (permission.grp_ancestors(m.grp)).id 
106                                 FROM   permission.usr_grp_map m
107                                 WHERE  m.usr = user_id
108                         )
109         ) AS x;
110         --
111         IF NOT FOUND THEN
112                 RETURN;                -- No such permission for this user
113         END IF;
114         --
115         -- Identify the org units to which the user is assigned.  Note that
116         -- we pay no attention to the home_ou column in actor.usr.
117         --
118         FOR n_work_ou IN
119                 SELECT
120                         work_ou
121                 FROM
122                         permission.usr_work_ou_map
123                 WHERE
124                         usr = user_id
125         LOOP            -- For each org unit to which the user is assigned
126                 --
127                 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
128                 -- We take it on faith that this depth agrees with the actual hierarchy
129                 -- defined in actor.org_unit.
130                 --
131                 SELECT INTO n_depth
132                     type.depth
133                 FROM
134                     actor.org_unit_type type
135                         INNER JOIN actor.org_unit ou
136                             ON ( ou.ou_type = type.id )
137                 WHERE
138                     ou.id = n_work_ou;
139                 --
140                 IF NOT FOUND THEN
141                         CONTINUE;        -- Maybe raise exception?
142                 END IF;
143                 --
144                 -- Compare the depth of the work org unit to the
145                 -- minimum depth, and branch accordingly
146                 --
147                 IF n_depth = n_min_depth THEN
148                         --
149                         -- The org unit is at the right depth, so return it.
150                         --
151                         RETURN NEXT n_work_ou;
152                 ELSIF n_depth > n_min_depth THEN
153                         --
154                         -- Traverse the org unit tree toward the root,
155                         -- until you reach the minimum depth determined above
156                         --
157                         n_curr_depth := n_depth;
158                         n_curr_ou := n_work_ou;
159                         WHILE n_curr_depth > n_min_depth LOOP
160                                 SELECT INTO n_curr_ou
161                                         parent_ou
162                                 FROM
163                                         actor.org_unit
164                                 WHERE
165                                         id = n_curr_ou;
166                                 --
167                                 IF FOUND THEN
168                                         n_curr_depth := n_curr_depth - 1;
169                                 ELSE
170                                         --
171                                         -- This can happen only if the hierarchy defined in
172                                         -- actor.org_unit is corrupted, or out of sync with
173                                         -- the depths defined in actor.org_unit_type.
174                                         -- Maybe we should raise an exception here, instead
175                                         -- of silently ignoring the problem.
176                                         --
177                                         n_curr_ou = NULL;
178                                         EXIT;
179                                 END IF;
180                         END LOOP;
181                         --
182                         IF n_curr_ou IS NOT NULL THEN
183                                 RETURN NEXT n_curr_ou;
184                         END IF;
185                 ELSE
186                         --
187                         -- The permission applies only at a depth greater than the work org unit.
188                         -- Use connectby() to find all dependent org units at the specified depth.
189                         --
190                         FOR n_curr_ou IN
191                                 SELECT id
192                                 FROM actor.org_unit_descendants_distance(n_work_ou)
193                                 WHERE
194                                         distance = n_min_depth - n_depth
195                         LOOP
196                                 RETURN NEXT n_curr_ou;
197                         END LOOP;
198                 END IF;
199                 --
200         END LOOP;
201         --
202         RETURN;
203         --
204 END;
205 $$ LANGUAGE 'plpgsql' ROWS 1;
206
207
208 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
209         user_id    IN INTEGER,
210         perm_code  IN TEXT
211 )
212 RETURNS SETOF INTEGER AS $$
213 --
214 -- Return a set of all the org units for which a given user has a given
215 -- permission, granted either directly or through inheritance from a parent
216 -- org unit.
217 --
218 -- The permissions apply to a minimum depth of the org unit hierarchy, and
219 -- to the subordinates of those org units, for the org unit(s) to which the
220 -- user is assigned.
221 --
222 -- For purposes of this function, the permission.usr_work_ou_map table
223 -- assigns users to org units.  I.e. we ignore the home_ou column of actor.usr.
224 --
225 -- The result set may contain duplicates, which should be eliminated
226 -- by a DISTINCT clause.
227 --
228 DECLARE
229         n_head_ou     INTEGER;
230         n_child_ou    INTEGER;
231 BEGIN
232         FOR n_head_ou IN
233                 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
234         LOOP
235                 --
236                 -- The permission applies only at a depth greater than the work org unit.
237                 --
238                 FOR n_child_ou IN
239             SELECT id
240             FROM actor.org_unit_descendants(n_head_ou)
241                 LOOP
242                         RETURN NEXT n_child_ou;
243                 END LOOP;
244         END LOOP;
245         --
246         RETURN;
247         --
248 END;
249 $$ LANGUAGE 'plpgsql' ROWS 1;
250
251 COMMIT;
252
253 \qecho The tablefunc database extension is no longer necessary for Evergreen.
254 \qecho Unless you use some of its functions in your own scripts, you may
255 \qecho want to run the following command in the database to drop it:
256 \qecho DROP EXTENSION tablefunc;