2 * PostgreSQL Functions for CalDAV handling
6 * @author Andrew McMillan <andrew@catalyst.net.nz>
7 * @copyright Catalyst IT Ltd
8 * @license http://gnu.org/copyleft/gpl.html GNU GPL v2
11 CREATE or REPLACE FUNCTION apply_month_byday( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$
22 our_answer TIMESTAMP WITH TIME ZONE;
24 dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2;
25 temp_txt := substring(byday from '([0-9]+)');
26 weeks := temp_txt::int;
28 -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
30 IF substring(byday for 1) = '-' THEN
31 -- Last XX of month, or possibly second-to-last, but unlikely
32 mm := extract( 'month' from in_time);
33 yy := extract( 'year' from in_time);
35 -- Start with the last day of the month
36 our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval;
37 dd := extract( 'dow' from our_answer);
43 -- Having calculated the right day of the month, we now apply that back to in_time
44 -- which contains the otherwise-unobtainable timezone detail (and the time)
45 our_answer = our_answer - (dd::text || 'days')::interval;
46 dd := extract( 'day' from our_answer) - extract( 'day' from in_time);
47 our_answer := in_time + (dd::text || 'days')::interval;
51 our_answer := our_answer - (weeks::text || 'weeks')::interval;
56 -- Shift our date to the correct day of week..
57 our_dow := extract( 'dow' from in_time);
58 our_dow := our_dow - dow;
59 dd := extract( 'day' from in_time);
61 our_dow := our_dow - 7;
63 our_answer := in_time - (our_dow::text || 'days')::interval;
64 dd = extract( 'day' from our_answer);
66 -- Shift the date to the correct week...
67 dd := weeks - ((dd+6) / 7);
69 our_answer := our_answer + ((dd::text || 'weeks')::interval);
77 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
80 CREATE or REPLACE FUNCTION calculate_later_timestamp( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$
82 earliest ALIAS FOR $1;
83 basedate ALIAS FOR $2;
84 repeatrule ALIAS FOR $3;
95 our_answer TIMESTAMP WITH TIME ZONE;
98 IF basedate > earliest THEN
102 temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
103 IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN
107 frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
108 IF frequency IS NULL THEN
114 temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)');
115 IF temp_txt IS NOT NULL THEN
116 length := temp_txt::int;
117 basediff := earliest - basedate;
119 -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
121 -- Calculate the number of past periods between our base date and our earliest date
122 IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN
123 past_repeats := extract('epoch' from basediff)::INT8 / 86400;
124 -- RAISE NOTICE 'Days: %', past_repeats;
125 IF frequency = 'WEEKLY' THEN
126 past_repeats := past_repeats / 7;
129 past_repeats = extract( 'years' from basediff );
130 IF frequency = 'MONTHLY' THEN
131 past_repeats = (past_repeats *12) + extract( 'months' from basediff );
134 IF length IS NOT NULL THEN
135 past_repeats = (past_repeats / length) + 1;
139 -- Check that we have not exceeded the COUNT= limit
140 temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
141 IF temp_txt IS NOT NULL THEN
142 count := temp_txt::int;
143 -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length;
144 IF ( count <= past_repeats ) THEN
151 temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)');
152 byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)');
153 IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN
154 -- Since this could move the date around a month we go back one
155 -- period just to be extra sure.
156 past_repeats = past_repeats - 1;
158 IF temp_txt IS NOT NULL THEN
159 -- Crudely hack the BYSETPOS onto the front of BYDAY. While this
160 -- is not as per rfc2445, RRULE syntax is so complex and overblown
161 -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and
162 -- certainly not within a MONTHLY RRULE.
163 byday := temp_txt || byday;
167 past_repeats = past_repeats * length;
170 WHEN frequency = 'DAILY' THEN 'days'
171 WHEN frequency = 'WEEKLY' THEN 'weeks'
172 WHEN frequency = 'MONTHLY' THEN 'months'
173 WHEN frequency = 'YEARLY' THEN 'years'
176 temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)');
177 bymonthday := temp_txt::int;
179 -- With all of the above calculation, this date should be close to (but less than)
180 -- the target, and we should only loop once or twice.
181 our_answer := basedate + (past_repeats::text || units)::interval;
183 IF our_answer IS NULL THEN
184 RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units;
188 loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle
190 -- RAISE NOTICE 'Testing date: %', our_answer;
191 IF frequency = 'DAILY' THEN
192 IF byday IS NOT NULL THEN
194 dow = substring( to_char( our_answer, 'DY' ) for 2);
195 EXIT WHEN byday ~* dow;
196 -- Increment for our next time through the loop...
197 our_answer := our_answer + (length::text || units)::interval;
200 ELSIF frequency = 'WEEKLY' THEN
201 -- Weekly repeats are only on specific days
202 -- This is really not right, since a WEEKLY on MO,WE,FR should
203 -- occur three times each week and this will only be once a week.
204 dow = substring( to_char( our_answer, 'DY' ) for 2);
205 ELSIF frequency = 'MONTHLY' THEN
206 IF byday IS NOT NULL THEN
207 -- This works fine, except that maybe there are multiple BYDAY
208 -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays.
209 our_answer := apply_month_byday( our_answer, byday );
211 -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month
212 our_answer := our_answer + '1 month'::interval;
214 ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN
215 -- We do not deal with this situation at present
216 RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.';
219 EXIT WHEN our_answer >= earliest;
221 -- Give up if we have exceeded the count
222 IF ( count IS NOT NULL AND past_repeats > count ) THEN
225 past_repeats := past_repeats + 1;
228 loopcount := loopcount - 1;
229 IF loopcount < 0 THEN
230 RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest;
234 -- Increment for our next time through the loop...
235 our_answer := our_answer + (length::text || units)::interval;
242 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
245 CREATE or REPLACE FUNCTION usr_is_role( INT, TEXT ) RETURNS BOOLEAN AS $$
246 SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 )
247 $$ LANGUAGE 'sql' IMMUTABLE STRICT;
249 CREATE or REPLACE FUNCTION legacy_get_permissions( INT, INT ) RETURNS TEXT AS $$
251 in_from ALIAS FOR $1;
261 -- Self can always have full access
262 IF in_from = in_to THEN
267 SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id )
268 WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
270 RETURN dbg || out_confers;
272 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
275 FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id)
276 JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id
277 WHERE r1.from_user=in_from AND r2.to_user=in_to
278 AND EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.to_user AND roles.role_name='Group')
279 AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r2.to_user AND roles.role_name='Group')
280 AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.from_user AND roles.role_name='Group')
282 -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
283 -- FIXME: This is an oversimplification
285 tmp_confers1 := r.r1;
286 tmp_confers2 := r.r2;
287 IF tmp_confers1 != tmp_confers2 THEN
288 IF tmp_confers1 ~* 'A' THEN
289 -- Ensure that A is expanded to all supported privs before being used as a mask
290 tmp_confers1 := 'AFBRWU';
292 IF tmp_confers2 ~* 'A' THEN
293 -- Ensure that A is expanded to all supported privs before being used as a mask
294 tmp_confers2 := 'AFBRWU';
296 -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
298 FOR counter IN 1 .. length(tmp_confers2) LOOP
299 IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN
300 tmp_txt := tmp_txt || substring(tmp_confers2,counter,1);
303 tmp_confers2 := tmp_txt;
305 FOR counter IN 1 .. length(tmp_confers2) LOOP
306 IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN
307 out_confers := out_confers || substring(tmp_confers2,counter,1);
311 IF out_confers ~* 'A' OR (out_confers ~* 'B' AND out_confers ~* 'F' AND out_confers ~* 'R' AND out_confers ~* 'W' AND out_confers ~* 'U') THEN
314 IF out_confers != '' THEN
315 RETURN dbg || out_confers;
318 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
320 SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id )
321 LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id )
322 WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user AND r1.to_user = r2.to_user
323 AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user )
324 AND usr_is_role(r1.to_user,'Group');
328 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
329 RETURN dbg || out_confers;
332 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
336 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
339 -- Function to convert a PostgreSQL date into UTC + the format used by iCalendar
340 CREATE or REPLACE FUNCTION to_ical_utc( TIMESTAMP WITH TIME ZONE ) RETURNS TEXT AS $$
341 SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' )
342 $$ LANGUAGE 'sql' IMMUTABLE STRICT;
344 -- Function to set an arbitrary DAV property
345 CREATE or REPLACE FUNCTION set_dav_property( TEXT, INTEGER, TEXT, TEXT ) RETURNS BOOLEAN AS $$
348 change_user ALIAS FOR $2;
352 -- Check that there is either a resource, collection or user at this location.
353 IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path
354 UNION SELECT 1 FROM collection WHERE dav_name = path
355 UNION SELECT 1 FROM dav_principal WHERE dav_name = path
356 UNION SELECT 1 FROM dav_binding WHERE dav_name = path
360 PERFORM true FROM property WHERE dav_name = path AND property_name = key;
362 UPDATE property SET changed_by=change_user::integer, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key;
364 INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, change_user::integer, current_timestamp, key, value );
368 $$ LANGUAGE 'plpgsql' STRICT;
370 -- List a user's relationships as a text string
371 CREATE or REPLACE FUNCTION relationship_list( INT8 ) RETURNS TEXT AS $$
378 FOR r IN SELECT rt_name, fullname FROM relationship
379 LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no
380 WHERE from_user = user
383 || CASE WHEN rlist = '' THEN '' ELSE ', ' END
384 || r.rt_name || '(' || r.fullname || ')';
388 $$ LANGUAGE 'plpgsql';
390 DROP FUNCTION rename_davical_user( TEXT, TEXT );
391 DROP TRIGGER usr_modified ON usr CASCADE;
392 CREATE or REPLACE FUNCTION usr_modified() RETURNS TRIGGER AS $$
397 -- in case we trigger on other events in future
398 IF TG_OP = 'UPDATE' THEN
399 IF NEW.username != OLD.username THEN
400 oldpath := '/' || OLD.username || '/';
401 newpath := '/' || NEW.username || '/';
403 SET parent_container = replace( parent_container, oldpath, newpath),
404 dav_name = replace( dav_name, oldpath, newpath)
405 WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath;
411 CREATE TRIGGER usr_modified AFTER UPDATE ON usr
412 FOR EACH ROW EXECUTE PROCEDURE usr_modified();
415 DROP TRIGGER collection_modified ON collection CASCADE;
416 CREATE or REPLACE FUNCTION collection_modified() RETURNS TRIGGER AS $$
419 -- in case we trigger on other events in future
420 IF TG_OP = 'UPDATE' THEN
421 IF NEW.dav_name != OLD.dav_name THEN
423 SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name),
424 user_no = NEW.user_no
425 WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name;
431 CREATE TRIGGER collection_modified AFTER UPDATE ON collection
432 FOR EACH ROW EXECUTE PROCEDURE collection_modified();
435 DROP TRIGGER caldav_data_modified ON caldav_data CASCADE;
436 CREATE or REPLACE FUNCTION caldav_data_modified() RETURNS TRIGGER AS $$
438 coll_id caldav_data.collection_id%TYPE;
440 IF TG_OP = 'UPDATE' THEN
441 IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN
442 -- Nothing for us to do
447 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
448 -- On insert or update modified, we set the NEW collection tag to the md5 of the
449 -- etag of the updated row which gives us something predictable for our regression
450 -- tests, but something different from the actual etag of the new event.
452 SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag)
453 WHERE collection_id = NEW.collection_id;
454 IF TG_OP = 'INSERT' THEN
459 IF TG_OP = 'DELETE' THEN
460 -- On delete we set the OLD collection tag to the md5 of the old path & the old
461 -- etag, which again gives us something predictable for our regression tests.
463 SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
464 WHERE collection_id = OLD.collection_id;
468 IF NEW.collection_id != OLD.collection_id THEN
469 -- If we've switched the collection_id of this event, then we also need to update
470 -- the etag of the old collection - as we do for delete.
472 SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
473 WHERE collection_id = OLD.collection_id;
478 CREATE TRIGGER caldav_data_modified AFTER INSERT OR UPDATE OR DELETE ON caldav_data
479 FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified();
482 DROP TRIGGER caldav_data_sync_dav_id ON caldav_data CASCADE;
483 DROP TRIGGER calendar_item_sync_dav_id ON calendar_item CASCADE;
484 CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$
488 IF TG_OP = 'DELETE' THEN
489 -- Just let the ON DELETE CASCADE handle this case
493 IF NEW.dav_id IS NULL THEN
494 NEW.dav_id = nextval('dav_id_seq');
497 IF TG_OP = 'UPDATE' THEN
498 IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
499 OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
500 UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
501 collection_id = NEW.collection_id, dav_name = NEW.dav_name
502 WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
507 UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
508 collection_id = NEW.collection_id, dav_name = NEW.dav_name
509 WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
514 $$ LANGUAGE 'plpgsql';
515 CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data
516 FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
522 CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$
524 in_priv ALIAS FOR $1;
527 out_bits := 0::BIT(24);
528 IF in_priv ~* 'A' THEN
529 out_bits = ~ out_bits;
533 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
535 -- 512 CalDAV:read-free-busy
536 -- 4096 CALDAV:schedule-query-freebusy
537 IF in_priv ~* 'R' THEN
538 out_bits := out_bits | 4609::BIT(24);
541 -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
542 -- 2 DAV:write-properties
543 -- 4 DAV:write-content
546 IF in_priv ~* 'W' THEN
547 out_bits := out_bits | 198::BIT(24);
551 IF in_priv ~* 'B' THEN
552 out_bits := out_bits | 64::BIT(24);
556 IF in_priv ~* 'U' THEN
557 out_bits := out_bits | 128::BIT(24);
560 -- 512 CalDAV:read-free-busy
561 -- 4096 CALDAV:schedule-query-freebusy
562 IF in_priv ~* 'F' THEN
563 out_bits := out_bits | 4608::BIT(24);
569 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
571 -- This legacy conversion function will eventually be removed, once all logic
572 -- has been converted to use bitmaps, or to use the bits_to_priv() output.
574 -- NOTE: Round-trip through this and then back through legacy_privilege_to_bits
575 -- function is lossy! Through legacy_privilege_to_bits() and back through
578 CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$
580 in_bits ALIAS FOR $1;
584 IF in_bits = (~ 0::BIT(24)) THEN
589 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
591 -- 512 CalDAV:read-free-busy
592 -- 4096 CALDAV:schedule-query-freebusy
593 IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN
594 IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
601 -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
602 -- 2 DAV:write-properties
603 -- 4 DAV:write-content
606 IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
607 IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN
608 out_priv := out_priv || 'W';
610 IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
611 out_priv := out_priv || 'B';
613 IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
614 out_priv := out_priv || 'U';
622 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
624 CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$
626 in_from ALIAS FOR $1;
636 -- Self can always have full access
637 IF in_from = in_to THEN
642 SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1
643 WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
645 RETURN dbg || out_confers;
647 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
649 SELECT bit_or(r1.confers & r2.confers) INTO bit_confers
651 JOIN relationship r2 ON r1.to_user=r2.from_user
652 WHERE r1.from_user=in_from AND r2.to_user=in_to
653 AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group');
654 IF bit_confers != 0::BIT(24) THEN
655 RETURN dbg || bits_to_legacy_privilege(bit_confers);
659 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
661 SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 LEFT OUTER JOIN relationship r2 ON(r1.to_user = r2.to_user)
662 WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user
663 AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ;
667 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
668 RETURN dbg || out_confers;
671 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
675 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
678 CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$
679 SELECT role_no FROM roles WHERE role_name = 'Group'
680 $$ LANGUAGE 'SQL' IMMUTABLE;
682 CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$
684 in_from ALIAS FOR $1;
685 in_legacy_privilege ALIAS FOR $2;
690 -- Self can always have full access
691 IF in_from = in_to THEN
695 SELECT get_group_role_no() INTO group_role_no;
696 SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers;
698 IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to
699 AND (in_confers & confers) = in_confers
700 AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN
701 -- A direct relationship from A to B that grants sufficient
702 -- RAISE NOTICE 'Permissions directly granted';
706 IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user
707 WHERE (in_confers & r1.confers & r2.confers) = in_confers
708 AND r1.from_user=in_from AND r2.to_user=in_to
709 AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN
710 -- An indirect relationship from A to B via group G that grants sufficient
711 -- RAISE NOTICE 'Permissions mediated via group';
715 IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user
716 WHERE (in_confers & r1.confers & r2.confers) = in_confers
717 AND r1.from_user=in_from AND r2.from_user=in_to
718 AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no)
719 AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN
720 -- An indirect reflexive relationship from both A & B to group G which grants sufficient
721 -- RAISE NOTICE 'Permissions to shared group';
725 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
729 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
732 -- Given a verbose DAV: or CalDAV: privilege name return the bitmask
733 CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$
735 raw_priv ALIAS FOR $1;
738 in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
739 IF in_priv = 'all' THEN
744 WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096
745 WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128
746 WHEN in_priv = 'write-properties' THEN 2
747 WHEN in_priv = 'write-content' THEN 4
748 WHEN in_priv = 'unlock' THEN 8
749 WHEN in_priv = 'read-acl' THEN 16
750 WHEN in_priv = 'read-current-user-privilege-set' THEN 32
751 WHEN in_priv = 'bind' THEN 64
752 WHEN in_priv = 'unbind' THEN 128
753 WHEN in_priv = 'write-acl' THEN 256
754 WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096
755 WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096
756 WHEN in_priv = 'schedule-deliver-invite' THEN 1024
757 WHEN in_priv = 'schedule-deliver-reply' THEN 2048
758 WHEN in_priv = 'schedule-query-freebusy' THEN 4096
759 WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768
760 WHEN in_priv = 'schedule-send-invite' THEN 8192
761 WHEN in_priv = 'schedule-send-reply' THEN 16384
762 WHEN in_priv = 'schedule-send-freebusy' THEN 32768
763 ELSE 0 END)::BIT(24);
766 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
769 -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask
770 CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$
772 raw_privs ALIAS FOR $1;
780 out_bits := 0::BIT(24);
781 all_privs := ~ out_bits;
782 SELECT array_lower(raw_privs,1) INTO start;
783 SELECT array_upper(raw_privs,1) INTO finish;
784 FOR i IN start .. finish LOOP
785 SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
786 IF out_bits = 65535::BIT(24) THEN
793 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
796 -- NOTE: Round-trip through this and then back through privilege_to_bits
797 -- function is lossy! Through privilege_to_bits() and back through
800 CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$
802 in_bits ALIAS FOR $1;
805 IF in_bits = (~ 0::BIT(24)) THEN
806 out_priv := out_priv || ARRAY['DAV:all'];
809 IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN
810 IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
811 out_priv := out_priv || ARRAY['DAV:read'];
813 IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN
814 out_priv := out_priv || ARRAY['caldav:read-free-busy'];
818 IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
819 IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN
820 out_priv := out_priv || ARRAY['DAV:write'];
822 IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN
823 out_priv := out_priv || ARRAY['DAV:write-properties'];
825 IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN
826 out_priv := out_priv || ARRAY['DAV:write-content'];
828 IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
829 out_priv := out_priv || ARRAY['DAV:bind'];
831 IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
832 out_priv := out_priv || ARRAY['DAV:unbind'];
837 IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN
838 out_priv := out_priv || ARRAY['DAV:unlock'];
841 IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN
842 out_priv := out_priv || ARRAY['DAV:read-acl'];
845 IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN
846 out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set'];
849 IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN
850 out_priv := out_priv || ARRAY['DAV:write-acl'];
853 IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN
854 IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN
855 out_priv := out_priv || ARRAY['caldav:schedule-deliver'];
857 IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN
858 out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite'];
860 IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN
861 out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply'];
863 IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN
864 out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy'];
869 IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN
870 IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN
871 out_priv := out_priv || ARRAY['caldav:schedule-send'];
873 IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN
874 out_priv := out_priv || ARRAY['caldav:schedule-send-invite'];
876 IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN
877 out_priv := out_priv || ARRAY['caldav:schedule-send-reply'];
879 IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN
880 out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy'];
888 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
891 -- Expanded group memberships out to some depth
892 CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$
893 SELECT group_id FROM group_member WHERE member_id = $1
895 SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
896 FROM group_member WHERE member_id = $1) AS expanded
897 WHERE expanded.g_id IS NOT NULL;
898 $$ LANGUAGE 'SQL' STABLE STRICT;
900 -- Expanded group members out to some depth
901 CREATE or REPLACE FUNCTION expand_members( INT8, INT ) RETURNS SETOF INT8 AS $$
902 SELECT member_id FROM group_member WHERE group_id = $1
904 SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id
905 FROM group_member WHERE group_id = $1) AS expanded
906 WHERE expanded.m_id IS NOT NULL;
907 $$ LANGUAGE 'SQL' STABLE STRICT;
912 -- Privileges from accessor to grantor, by principal_id
913 CREATE or REPLACE FUNCTION pprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$
915 in_accessor ALIAS FOR $1;
916 in_grantor ALIAS FOR $2;
917 in_depth ALIAS FOR $3;
918 out_conferred BIT(24);
920 out_conferred := 0::BIT(24);
921 -- Self can always have full access
922 IF in_grantor = in_accessor THEN
923 RETURN ~ out_conferred;
926 SELECT bit_or(subquery.privileges) INTO out_conferred FROM
928 SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL
929 AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)))
931 SELECT bit_or(sq2.privileges) FROM
933 SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor
935 SELECT default_privileges AS privileges FROM principal WHERE principal_id = in_grantor
939 IF out_conferred IS NULL THEN
940 SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
943 RETURN out_conferred;
945 $$ LANGUAGE 'plpgsql' STABLE STRICT;
948 -- Privileges from accessor to grantor, by user_no
949 CREATE or REPLACE FUNCTION uprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$
951 in_accessor ALIAS FOR $1;
952 in_grantor ALIAS FOR $2;
953 in_depth ALIAS FOR $3;
954 out_conferred BIT(24);
956 out_conferred := 0::BIT(24);
957 -- Self can always have full access
958 IF in_grantor = in_accessor THEN
959 RETURN ~ out_conferred;
962 SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred
963 FROM principal p1, principal p2
964 WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor;
966 RETURN out_conferred;
968 $$ LANGUAGE 'plpgsql' STABLE STRICT;
971 -- Privileges from accessor (by principal_id) to path
972 CREATE or REPLACE FUNCTION path_privs( INT8, TEXT, INT ) RETURNS BIT(24) AS $$
974 in_accessor ALIAS FOR $1;
975 in_path ALIAS FOR $2;
976 in_depth ALIAS FOR $3;
980 grantor_collection INT8;
981 grantor_principal INT8;
982 collection_path TEXT;
983 collection_privileges BIT(24);
984 out_conferred BIT(24);
986 out_conferred := 0::BIT(24);
988 IF in_path ~ '^/?$' THEN
989 -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
990 RETURN 1; -- basic read privileges on root directory
993 -- We need to canonicalise the path, so:
994 -- If it matches '/' + some characters (+ optional '/') => a principal URL
995 IF in_path ~ '^/[^/]+/?$' THEN
996 alt1_path := replace(in_path, '/', '');
997 SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
998 -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
999 RETURN out_conferred;
1002 -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one.
1003 alt1_path := in_path;
1004 IF alt1_path ~ E'\\.ics$' THEN
1005 alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
1007 alt2_path := regexp_replace( in_path, '[^/]*$', '');
1008 SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges
1009 INTO grantor_collection, grantor_principal, collection_path, collection_privileges
1010 FROM collection JOIN principal grantor USING (user_no)
1011 WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
1012 ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;
1014 -- Self will always need full access to their own collections!
1015 IF grantor_principal = in_accessor THEN
1016 -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path;
1017 RETURN ~ out_conferred;
1020 SELECT bit_or(privileges) INTO out_conferred FROM grants
1021 WHERE by_collection = grantor_collection
1022 AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)));
1024 IF out_conferred IS NULL THEN
1025 IF collection_privileges IS NULL THEN
1026 IF grantor_principal IS NULL THEN
1027 alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/');
1028 SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred;
1029 -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1031 SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred;
1032 -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1035 out_conferred := collection_privileges;
1036 -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1040 RETURN out_conferred;
1042 $$ LANGUAGE 'plpgsql' STABLE STRICT;
1045 -- List a user's memberships as a text string
1046 CREATE or REPLACE FUNCTION is_member_of_list( INT8 ) RETURNS TEXT AS $$
1048 in_member_id ALIAS FOR $1;
1053 FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id)
1054 WHERE member_id = in_member_id
1057 || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1058 || COALESCE( m.displayname, m.group_id::text);
1062 $$ LANGUAGE 'plpgsql' STRICT;
1065 -- List a user's members as a text string
1066 CREATE or REPLACE FUNCTION has_members_list( INT8 ) RETURNS TEXT AS $$
1068 in_member_id ALIAS FOR $1;
1073 FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id)
1074 WHERE group_id = in_member_id
1077 || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1078 || COALESCE( m.displayname, m.group_id::text);
1082 $$ LANGUAGE 'plpgsql' STRICT;
1085 -- List the privileges as a text string
1086 CREATE or REPLACE FUNCTION privileges_list( BIT(24) ) RETURNS TEXT AS $$
1088 in_privileges ALIAS FOR $1;
1097 privileges := bits_to_privilege(in_privileges);
1098 SELECT array_lower(privileges,1) INTO start;
1099 IF start IS NOT NULL THEN
1100 SELECT array_upper(privileges,1) INTO finish;
1101 FOR i IN start .. finish LOOP
1103 || CASE WHEN plist = '' THEN '' ELSE ', ' END
1109 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
1112 DROP TRIGGER principal_modified ON principal CASCADE;
1113 CREATE or REPLACE FUNCTION principal_modified() RETURNS TRIGGER AS $$
1116 -- in case we trigger on other events in future
1117 IF TG_OP = 'UPDATE' THEN
1118 IF NEW.type_id != OLD.type_id THEN
1120 SET is_group = (NEW.type_id = 3)
1121 WHERE grants.to_principal = NEW.principal_id;
1126 $$ LANGUAGE plpgsql;
1127 CREATE TRIGGER principal_modified AFTER UPDATE ON principal
1128 FOR EACH ROW EXECUTE PROCEDURE principal_modified();
1131 DROP TRIGGER grants_modified ON grants CASCADE;
1132 CREATE or REPLACE FUNCTION grants_modified() RETURNS TRIGGER AS $$
1134 old_to_principal INT8;
1137 -- in case we trigger on other events in future
1138 IF TG_OP = 'INSERT' THEN
1139 old_to_principal := NULL;
1141 old_to_principal := OLD.to_principal;
1143 IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN
1144 SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal;
1145 IF NEW.is_group != new_is_group THEN
1146 NEW.is_group := new_is_group;
1151 $$ LANGUAGE plpgsql;
1152 CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants
1153 FOR EACH ROW EXECUTE PROCEDURE grants_modified();
1157 -- An expanded list of the grants this principal has access to
1158 CREATE or REPLACE FUNCTION p_has_proxy_access_to( INT8, INT ) RETURNS SETOF INT8 AS $$
1161 SELECT by_principal FROM grants
1162 WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2))
1163 AND (privileges & 5::BIT(24)) != 0::BIT(24)
1164 AND by_collection IS NULL
1165 AND by_principal != $1
1167 SELECT principal_id AS by_principal FROM principal
1168 WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24)
1169 AND principal_id != $1
1171 $$ LANGUAGE 'SQL' STABLE STRICT;
1174 -- A list of the principals who can proxy to this principal
1175 CREATE or REPLACE FUNCTION grants_proxy_access_from_p( INT8, INT ) RETURNS SETOF INT8 AS $$
1176 SELECT DISTINCT by_principal
1178 WHERE by_collection IS NULL AND by_principal != $1
1179 AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1)
1181 $$ LANGUAGE 'SQL' STABLE STRICT;
1187 CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$
1189 in_collection_id ALIAS FOR $1;
1190 in_status ALIAS FOR $2;
1191 in_dav_name ALIAS FOR $3;
1194 SELECT 1 INTO tmp_int FROM sync_tokens
1195 WHERE collection_id = in_collection_id
1200 SELECT dav_id INTO tmp_int FROM caldav_data WHERE dav_name = in_dav_name;
1201 INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name)
1202 VALUES( in_collection_id, in_status, tmp_int, in_dav_name);
1205 $$ LANGUAGE 'PlPgSQL' VOLATILE STRICT;
1208 CREATE or REPLACE FUNCTION new_sync_token( INT8, INT8 ) RETURNS INT8 AS $$
1210 in_old_sync_token ALIAS FOR $1;
1211 in_collection_id ALIAS FOR $2;
1213 new_token sync_tokens.sync_token%TYPE;
1214 old_modification_time sync_tokens.modification_time%TYPE;
1216 IF in_old_sync_token > 0 THEN
1217 SELECT modification_time INTO old_modification_time FROM sync_tokens
1218 WHERE sync_token = in_old_sync_token AND collection_id = in_collection_id;
1220 -- They are in an inconsistent state: we return NULL so they can re-start the process
1225 -- Find the most recent sync_token
1226 SELECT sync_token, modification_time INTO new_token, old_modification_time FROM sync_tokens
1227 WHERE collection_id = in_collection_id ORDER BY modification_time DESC LIMIT 1;
1229 SELECT 1 INTO tmp_int FROM sync_changes WHERE collection_id = in_collection_id AND sync_time > old_modification_time LIMIT 1;
1231 -- Return the latest sync_token we have for this collection, since there are no changes.
1236 -- Looks like we need a new sync_token for this collection...
1237 SELECT nextval('sync_tokens_sync_token_seq') INTO new_token;
1238 INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, new_token );
1240 -- Having created our new token we do some clean-up of old tokens
1241 SELECT modification_time, sync_token INTO old_modification_time, tmp_int FROM sync_tokens
1242 WHERE collection_id = in_collection_id AND modification_time < (current_timestamp - '7 days'::interval)
1243 ORDER BY collection_id, modification_time DESC;
1244 DELETE FROM sync_changes WHERE collection_id = in_collection_id AND sync_time < old_modification_time;
1245 DELETE FROM sync_tokens WHERE collection_id = in_collection_id AND sync_token < tmp_int;
1247 -- Returning the new token
1250 $$ LANGUAGE 'PlPgSQL' STRICT;
1253 DROP TRIGGER alarm_changed ON calendar_alarm CASCADE;
1254 CREATE or REPLACE FUNCTION alarm_changed() RETURNS TRIGGER AS $$
1259 -- in case we trigger on other events in future
1260 IF TG_OP = 'UPDATE' THEN
1261 IF NEW.component != OLD.component THEN
1263 SET caldav_data = replace( caldav_data, OLD.component, NEW.component ),
1264 dav_etag = md5(replace( caldav_data, OLD.component, NEW.component ))
1265 WHERE caldav_data.dav_id = NEW.dav_id;
1270 $$ LANGUAGE plpgsql;
1271 CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm
1272 FOR EACH ROW EXECUTE PROCEDURE alarm_changed();
1274 CREATE or REPLACE FUNCTION real_path_exists( TEXT ) RETURNS BOOLEAN AS $$
1276 in_path ALIAS FOR $1;
1279 IF in_path = '/' THEN
1282 IF in_path ~ '^/[^/]+/$' THEN
1283 SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
1288 IF in_path ~ '^/.*/$' THEN
1289 SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
1297 $$ LANGUAGE plpgsql ;
1301 CREATE or REPLACE FUNCTION collections_within( INT, INT ) RETURNS SETOF INT AS $$
1303 in_collection_id ALIAS FOR $1;
1304 in_depth ALIAS FOR $2;
1308 in_depth := in_depth - 1;
1310 FOR resource_id IN SELECT b.bound_source_id FROM dav_binding b
1311 JOIN collection pc ON (b.parent_container = pc.dav_name)
1312 WHERE pc.collection_id = in_collection_id
1315 RETURN NEXT resource_id;
1316 IF in_depth > 0 THEN
1317 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
1318 RETURN NEXT resource_id;
1322 FOR resource_id IN SELECT c.collection_id FROM collection c
1323 JOIN collection pc ON (c.parent_container = pc.dav_name)
1324 WHERE pc.collection_id = in_collection_id
1327 RETURN NEXT resource_id;
1328 IF in_depth > 0 THEN
1329 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
1330 RETURN NEXT resource_id;
1337 FOR resource_id IN SELECT c.collection_id FROM collection c
1338 JOIN dav_principal pc ON (c.parent_container = pc.dav_name)
1339 WHERE pc.principal_id = in_collection_id
1341 RETURN NEXT resource_id;
1342 IF in_depth > 0 THEN
1343 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
1344 RETURN NEXT resource_id;
1349 $$ LANGUAGE plpgsql ;