source: contrib/davical/dba/caldav_functions.sql @ 3733

Revision 3733, 42.2 KB checked in by gabriel.malheiros, 13 years ago (diff)

Ticket #1541 - <Davical customizado para o Expresso.Utiliza Caldav e CardDav?>
Line 
1/**
2* PostgreSQL Functions for CalDAV handling
3*
4* @package rscds
5* @subpackage database
6* @author Andrew McMillan <andrew@catalyst.net.nz>
7* @copyright Catalyst IT Ltd
8* @license   http://gnu.org/copyleft/gpl.html GNU GPL v2
9*/
10
11CREATE or REPLACE FUNCTION apply_month_byday( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$
12DECLARE
13  in_time ALIAS FOR $1;
14  byday ALIAS FOR $2;
15  weeks INT;
16  dow INT;
17  temp_txt TEXT;
18  dd INT;
19  mm INT;
20  yy INT;
21  our_dow INT;
22  our_answer TIMESTAMP WITH TIME ZONE;
23BEGIN
24  dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2;
25  temp_txt   := substring(byday from '([0-9]+)');
26  weeks      := temp_txt::int;
27
28  -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
29
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);
34
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);
38    dd := dd - dow;
39    IF dd < 0 THEN
40      dd := dd + 7;
41    END IF;
42
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;
48
49    IF weeks > 1 THEN
50      weeks := weeks - 1;
51      our_answer := our_answer - (weeks::text || 'weeks')::interval;
52    END IF;
53
54  ELSE
55
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);
60    IF our_dow >= dd THEN
61      our_dow := our_dow - 7;
62    END IF;
63    our_answer := in_time - (our_dow::text || 'days')::interval;
64    dd = extract( 'day' from our_answer);
65
66    -- Shift the date to the correct week...
67    dd := weeks - ((dd+6) / 7);
68    IF dd != 0 THEN
69      our_answer := our_answer + ((dd::text || 'weeks')::interval);
70    END IF;
71
72  END IF;
73
74  RETURN our_answer;
75
76END;
77$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
78
79
80CREATE or REPLACE FUNCTION calculate_later_timestamp( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$
81DECLARE
82  earliest ALIAS FOR $1;
83  basedate ALIAS FOR $2;
84  repeatrule ALIAS FOR $3;
85  frequency TEXT;
86  temp_txt TEXT;
87  length INT;
88  count INT;
89  byday TEXT;
90  bymonthday INT;
91  basediff INTERVAL;
92  past_repeats INT8;
93  units TEXT;
94  dow TEXT;
95  our_answer TIMESTAMP WITH TIME ZONE;
96  loopcount INT;
97BEGIN
98  IF basedate > earliest THEN
99    RETURN basedate;
100  END IF;
101
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
104    RETURN NULL;
105  END IF;
106
107  frequency  := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
108  IF frequency IS NULL THEN
109    RETURN NULL;
110  END IF;
111
112  past_repeats = 0;
113  length = 1;
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;
118
119    -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
120
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;
127      END IF;
128    ELSE
129      past_repeats = extract( 'years' from basediff );
130      IF frequency = 'MONTHLY' THEN
131        past_repeats = (past_repeats *12) + extract( 'months' from basediff );
132      END IF;
133    END IF;
134    IF length IS NOT NULL THEN
135      past_repeats = (past_repeats / length) + 1;
136    END IF;
137  END IF;
138
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
145      RETURN NULL;
146    END IF;
147  ELSE
148    count := NULL;
149  END IF;
150
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;
157
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;
164    END IF;
165  END IF;
166
167  past_repeats = past_repeats * length;
168
169  units := CASE
170    WHEN frequency = 'DAILY' THEN 'days'
171    WHEN frequency = 'WEEKLY' THEN 'weeks'
172    WHEN frequency = 'MONTHLY' THEN 'months'
173    WHEN frequency = 'YEARLY' THEN 'years'
174  END;
175
176  temp_txt   := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)');
177  bymonthday := temp_txt::int;
178
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;
182
183  IF our_answer IS NULL THEN
184    RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units;
185  END IF;
186
187
188  loopcount := 500;  -- Desirable to stop an infinite loop if there is something we cannot handle
189  LOOP
190    -- RAISE NOTICE 'Testing date: %', our_answer;
191    IF frequency = 'DAILY' THEN
192      IF byday IS NOT NULL THEN
193        LOOP
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;
198        END LOOP;
199      END IF;
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 );
210      ELSE
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;
213      END IF;
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.';
217    END IF;
218
219    EXIT WHEN our_answer >= earliest;
220
221    -- Give up if we have exceeded the count
222    IF ( count IS NOT NULL AND past_repeats > count ) THEN
223      RETURN NULL;
224    ELSE
225      past_repeats := past_repeats + 1;
226    END IF;
227
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;
231      RETURN NULL;
232    END IF;
233
234    -- Increment for our next time through the loop...
235    our_answer := our_answer + (length::text || units)::interval;
236
237  END LOOP;
238
239  RETURN our_answer;
240
241END;
242$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
243
244
245CREATE 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;
248
249CREATE or REPLACE FUNCTION legacy_get_permissions( INT, INT ) RETURNS TEXT AS $$
250DECLARE
251  in_from ALIAS FOR $1;
252  in_to   ALIAS FOR $2;
253  out_confers TEXT;
254  tmp_confers1 TEXT;
255  tmp_confers2 TEXT;
256  tmp_txt TEXT;
257  dbg TEXT DEFAULT '';
258  r RECORD;
259  counter INT;
260BEGIN
261  -- Self can always have full access
262  IF in_from = in_to THEN
263    RETURN 'A';
264  END IF;
265
266  -- dbg := 'S-';
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');
269  IF FOUND THEN
270    RETURN dbg || out_confers;
271  END IF;
272  -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
273
274  out_confers := '';
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')
281  LOOP
282    -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
283    -- FIXME: This is an oversimplification
284    -- dbg := 'C-';
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';
291      END IF;
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';
295      END IF;
296      -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
297      tmp_txt = '';
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);
301        END IF;
302      END LOOP;
303      tmp_confers2 := tmp_txt;
304    END IF;
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);
308      END IF;
309    END LOOP;
310  END LOOP;
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
312    out_confers := 'A';
313  END IF;
314  IF out_confers != '' THEN
315    RETURN dbg || out_confers;
316  END IF;
317
318  -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
319
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');
325
326  IF FOUND THEN
327    -- dbg := 'H-';
328    -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
329    RETURN dbg || out_confers;
330  END IF;
331
332  -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
333
334  RETURN '';
335END;
336$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
337
338
339-- Function to convert a PostgreSQL date into UTC + the format used by iCalendar
340CREATE 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;
343
344-- Function to set an arbitrary DAV property
345CREATE or REPLACE FUNCTION set_dav_property( TEXT, INTEGER, TEXT, TEXT ) RETURNS BOOLEAN AS $$
346DECLARE
347  path ALIAS FOR $1;
348  user ALIAS FOR $2;
349  key ALIAS FOR $3;
350  value ALIAS FOR $4;
351  tmp_int INT;
352BEGIN
353  -- Check that there is either a resource, collection or user at this location.
354  IF NOT EXISTS(        SELECT 1 FROM caldav_data WHERE dav_name = path
355                  UNION SELECT 1 FROM collection WHERE dav_name = path
356                  UNION SELECT 1 FROM dav_principal WHERE dav_name = path
357                  UNION SELECT 1 FROM dav_binding WHERE dav_name = path
358               ) THEN
359    RETURN FALSE;
360  END IF;
361  SELECT changed_by INTO tmp_int FROM property WHERE dav_name = path AND property_name = key;
362  IF FOUND THEN
363    UPDATE property SET changed_by=user, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key;
364  ELSE
365    INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, user, current_timestamp, key, value );
366  END IF;
367  RETURN TRUE;
368END;
369$$ LANGUAGE 'plpgsql' STRICT;
370
371-- List a user's relationships as a text string
372CREATE or REPLACE FUNCTION relationship_list( INT8 ) RETURNS TEXT AS $$
373DECLARE
374  user ALIAS FOR $1;
375  r RECORD;
376  rlist TEXT;
377BEGIN
378  rlist := '';
379  FOR r IN SELECT rt_name, fullname FROM relationship
380                          LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no
381                          WHERE from_user = user
382  LOOP
383    rlist := rlist
384             || CASE WHEN rlist = '' THEN '' ELSE ', ' END
385             || r.rt_name || '(' || r.fullname || ')';
386  END LOOP;
387  RETURN rlist;
388END;
389$$ LANGUAGE 'plpgsql';
390
391DROP FUNCTION rename_davical_user( TEXT, TEXT );
392DROP TRIGGER usr_modified ON usr CASCADE;
393CREATE or REPLACE FUNCTION usr_modified() RETURNS TRIGGER AS $$
394DECLARE
395  oldpath TEXT;
396  newpath TEXT;
397BEGIN
398  -- in case we trigger on other events in future
399  IF TG_OP = 'UPDATE' THEN
400    IF NEW.username != OLD.username THEN
401      oldpath := '/' || OLD.username || '/';
402      newpath := '/' || NEW.username || '/';
403      UPDATE collection
404        SET parent_container = replace( parent_container, oldpath, newpath),
405            dav_name = replace( dav_name, oldpath, newpath)
406      WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath;
407    END IF;
408  END IF;
409  RETURN NEW;
410END;
411$$ LANGUAGE plpgsql;
412CREATE TRIGGER usr_modified AFTER UPDATE ON usr
413    FOR EACH ROW EXECUTE PROCEDURE usr_modified();
414
415
416DROP TRIGGER collection_modified ON collection CASCADE;
417CREATE or REPLACE FUNCTION collection_modified() RETURNS TRIGGER AS $$
418DECLARE
419BEGIN
420  -- in case we trigger on other events in future
421  IF TG_OP = 'UPDATE' THEN
422    IF NEW.dav_name != OLD.dav_name THEN
423      UPDATE caldav_data
424        SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name),
425            user_no = NEW.user_no
426      WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name;
427    END IF;
428  END IF;
429  RETURN NEW;
430END;
431$$ LANGUAGE plpgsql;
432CREATE TRIGGER collection_modified AFTER UPDATE ON collection
433    FOR EACH ROW EXECUTE PROCEDURE collection_modified();
434
435
436DROP TRIGGER caldav_data_modified ON caldav_data CASCADE;
437CREATE or REPLACE FUNCTION caldav_data_modified() RETURNS TRIGGER AS $$
438DECLARE
439  coll_id caldav_data.collection_id%TYPE;
440BEGIN
441  IF TG_OP = 'UPDATE' THEN
442    IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN
443      -- Nothing for us to do
444      RETURN NEW;
445    END IF;
446  END IF;
447
448  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
449    -- On insert or update modified, we set the NEW collection tag to the md5 of the
450    -- etag of the updated row which gives us something predictable for our regression
451    -- tests, but something different from the actual etag of the new event.
452    UPDATE collection
453       SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag)
454     WHERE collection_id = NEW.collection_id;
455    IF TG_OP = 'INSERT' THEN
456      RETURN NEW;
457    END IF;
458  END IF;
459
460  IF TG_OP = 'DELETE' THEN
461    -- On delete we set the OLD collection tag to the md5 of the old path & the old
462    -- etag, which again gives us something predictable for our regression tests.
463    UPDATE collection
464       SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
465     WHERE collection_id = OLD.collection_id;
466    RETURN OLD;
467  END IF;
468
469  IF NEW.collection_id != OLD.collection_id THEN
470    -- If we've switched the collection_id of this event, then we also need to update
471    -- the etag of the old collection - as we do for delete.
472    UPDATE collection
473       SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
474     WHERE collection_id = OLD.collection_id;
475  END IF;
476  RETURN NEW;
477END;
478$$ LANGUAGE plpgsql;
479CREATE TRIGGER caldav_data_modified AFTER INSERT OR UPDATE OR DELETE ON caldav_data
480    FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified();
481
482
483DROP TRIGGER caldav_data_sync_dav_id ON caldav_data CASCADE;
484DROP TRIGGER calendar_item_sync_dav_id ON calendar_item CASCADE;
485CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$
486  DECLARE
487  BEGIN
488
489    IF TG_OP = 'DELETE' THEN
490      -- Just let the ON DELETE CASCADE handle this case
491      RETURN OLD;
492    END IF;
493
494    IF NEW.dav_id IS NULL THEN
495      NEW.dav_id = nextval('dav_id_seq');
496    END IF;
497
498    IF TG_OP = 'UPDATE' THEN
499      IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
500                 OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
501        UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
502                        collection_id = NEW.collection_id, dav_name = NEW.dav_name
503            WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
504      END IF;
505      RETURN NEW;
506    END IF;
507
508    UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
509                    collection_id = NEW.collection_id, dav_name = NEW.dav_name
510          WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
511
512    RETURN NEW;
513
514  END
515$$ LANGUAGE 'plpgsql';
516CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data
517    FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
518
519
520
521-- New in 1.2.6
522
523CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$
524DECLARE
525  in_priv ALIAS FOR $1;
526  out_bits BIT(24);
527BEGIN
528  out_bits := 0::BIT(24);
529  IF in_priv ~* 'A' THEN
530    out_bits = ~ out_bits;
531    RETURN out_bits;
532  END IF;
533
534  -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
535  --    1 DAV:read
536  --  512 CalDAV:read-free-busy
537  -- 4096 CALDAV:schedule-query-freebusy
538  IF in_priv ~* 'R' THEN
539    out_bits := out_bits | 4609::BIT(24);
540  END IF;
541
542  -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
543  --    2 DAV:write-properties
544  --    4 DAV:write-content
545  --   64 DAV:bind
546  --  128 DAV:unbind
547  IF in_priv ~* 'W' THEN
548    out_bits := out_bits |   198::BIT(24);
549  END IF;
550
551  --   64 DAV:bind
552  IF in_priv ~* 'B' THEN
553    out_bits := out_bits | 64::BIT(24);
554  END IF;
555
556  --  128 DAV:unbind
557  IF in_priv ~* 'U' THEN
558    out_bits := out_bits | 128::BIT(24);
559  END IF;
560
561  --  512 CalDAV:read-free-busy
562  -- 4096 CALDAV:schedule-query-freebusy
563  IF in_priv ~* 'F' THEN
564    out_bits := out_bits | 4608::BIT(24);
565  END IF;
566
567  RETURN out_bits;
568END
569$$
570LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
571
572-- This legacy conversion function will eventually be removed, once all logic
573-- has been converted to use bitmaps, or to use the bits_to_priv() output.
574--
575-- NOTE: Round-trip through this and then back through legacy_privilege_to_bits
576--       function is lossy!  Through legacy_privilege_to_bits() and back through
577--       this one is not.
578--
579CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$
580DECLARE
581  in_bits ALIAS FOR $1;
582  out_priv TEXT;
583BEGIN
584  out_priv := '';
585  IF in_bits = (~ 0::BIT(24)) THEN
586    out_priv = 'A';
587    RETURN out_priv;
588  END IF;
589
590  -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
591  --    1 DAV:read
592  --  512 CalDAV:read-free-busy
593  -- 4096 CALDAV:schedule-query-freebusy
594  IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN
595    IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
596      out_priv := 'R';
597    ELSE
598      out_priv := 'F';
599    END IF;
600  END IF;
601
602  -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
603  --    2 DAV:write-properties
604  --    4 DAV:write-content
605  --   64 DAV:bind
606  --  128 DAV:unbind
607  IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
608    IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN
609      out_priv := out_priv || 'W';
610    ELSE
611      IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
612        out_priv := out_priv || 'B';
613      END IF;
614      IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
615        out_priv := out_priv || 'U';
616      END IF;
617    END IF;
618  END IF;
619
620  RETURN out_priv;
621END
622$$
623LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
624
625CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$
626DECLARE
627  in_from ALIAS FOR $1;
628  in_to   ALIAS FOR $2;
629  out_confers TEXT;
630  bit_confers BIT(24);
631  group_role_no INT;
632  tmp_txt TEXT;
633  dbg TEXT DEFAULT '';
634  r RECORD;
635  counter INT;
636BEGIN
637  -- Self can always have full access
638  IF in_from = in_to THEN
639    RETURN 'A';
640  END IF;
641
642  -- dbg := 'S-';
643  SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1
644                    WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
645  IF FOUND THEN
646    RETURN dbg || out_confers;
647  END IF;
648  -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
649
650  SELECT bit_or(r1.confers & r2.confers) INTO bit_confers
651              FROM relationship r1
652              JOIN relationship r2 ON r1.to_user=r2.from_user
653         WHERE r1.from_user=in_from AND r2.to_user=in_to
654           AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group');
655  IF bit_confers != 0::BIT(24) THEN
656    RETURN dbg || bits_to_legacy_privilege(bit_confers);
657  END IF;
658
659  RETURN '';
660  -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
661
662  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)
663       WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user
664         AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ;
665
666  IF FOUND THEN
667    -- dbg := 'H-';
668    -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
669    RETURN dbg || out_confers;
670  END IF;
671
672  -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
673
674  RETURN '';
675END;
676$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
677
678
679CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$
680  SELECT role_no FROM roles WHERE role_name = 'Group'
681$$ LANGUAGE 'SQL' IMMUTABLE;
682
683CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$
684DECLARE
685  in_from ALIAS FOR $1;
686  in_legacy_privilege ALIAS FOR $2;
687  in_to   ALIAS FOR $3;
688  in_confers BIT(24);
689  group_role_no INT;
690BEGIN
691  -- Self can always have full access
692  IF in_from = in_to THEN
693    RETURN TRUE;
694  END IF;
695
696  SELECT get_group_role_no() INTO group_role_no;
697  SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers;
698
699  IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to
700                      AND (in_confers & confers) = in_confers
701                      AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN
702    -- A direct relationship from A to B that grants sufficient
703    -- RAISE NOTICE 'Permissions directly granted';
704    RETURN TRUE;
705  END IF;
706
707  IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user
708         WHERE (in_confers & r1.confers & r2.confers) = in_confers
709           AND r1.from_user=in_from AND r2.to_user=in_to
710           AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN
711    -- An indirect relationship from A to B via group G that grants sufficient
712    -- RAISE NOTICE 'Permissions mediated via group';
713    RETURN TRUE;
714  END IF;
715
716  IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user
717         WHERE (in_confers & r1.confers & r2.confers) = in_confers
718           AND r1.from_user=in_from AND r2.from_user=in_to
719           AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no)
720           AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN
721    -- An indirect reflexive relationship from both A & B to group G which grants sufficient
722    -- RAISE NOTICE 'Permissions to shared group';
723    RETURN TRUE;
724  END IF;
725
726  -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
727
728  RETURN FALSE;
729END;
730$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
731
732
733-- Given a verbose DAV: or CalDAV: privilege name return the bitmask
734CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$
735DECLARE
736  raw_priv ALIAS FOR $1;
737  in_priv TEXT;
738BEGIN
739  in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
740  IF in_priv = 'all' THEN
741    RETURN ~ 0::BIT(24);
742  END IF;
743
744  RETURN (CASE
745            WHEN in_priv = 'read'                            THEN  4609 -- 1 + 512 + 4096
746            WHEN in_priv = 'write'                           THEN   198 -- 2 + 4 + 64 + 128
747            WHEN in_priv = 'write-properties'                THEN     2
748            WHEN in_priv = 'write-content'                   THEN     4
749            WHEN in_priv = 'unlock'                          THEN     8
750            WHEN in_priv = 'read-acl'                        THEN    16
751            WHEN in_priv = 'read-current-user-privilege-set' THEN    32
752            WHEN in_priv = 'bind'                            THEN    64
753            WHEN in_priv = 'unbind'                          THEN   128
754            WHEN in_priv = 'write-acl'                       THEN   256
755            WHEN in_priv = 'read-free-busy'                  THEN  4608 --  512 + 4096
756            WHEN in_priv = 'schedule-deliver'                THEN  7168 -- 1024 + 2048 + 4096
757            WHEN in_priv = 'schedule-deliver-invite'         THEN  1024
758            WHEN in_priv = 'schedule-deliver-reply'          THEN  2048
759            WHEN in_priv = 'schedule-query-freebusy'         THEN  4096
760            WHEN in_priv = 'schedule-send'                   THEN 57344 -- 8192 + 16384 + 32768
761            WHEN in_priv = 'schedule-send-invite'            THEN  8192
762            WHEN in_priv = 'schedule-send-reply'             THEN 16384
763            WHEN in_priv = 'schedule-send-freebusy'          THEN 32768
764          ELSE 0 END)::BIT(24);
765END
766$$
767LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
768
769
770-- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask
771CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$
772DECLARE
773  raw_privs ALIAS FOR $1;
774  in_priv TEXT;
775  out_bits BIT(24);
776  i INT;
777  allprivs BIT(24);
778  start INT;
779  finish INT;
780BEGIN
781  out_bits := 0::BIT(24);
782  allprivs := ~ out_bits;
783  SELECT array_lower(raw_privs,1) INTO start;
784  SELECT array_upper(raw_privs,1) INTO finish;
785  FOR i IN start .. finish  LOOP
786    SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
787    IF out_bits = allprivs THEN
788      RETURN allprivs;
789    END IF;
790  END LOOP;
791  RETURN out_bits;
792END
793$$
794LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
795
796
797-- NOTE: Round-trip through this and then back through privilege_to_bits
798--       function is lossy!  Through privilege_to_bits() and back through
799--       this one is not.
800--
801CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$
802DECLARE
803  in_bits ALIAS FOR $1;
804  out_priv TEXT[];
805BEGIN
806  IF in_bits = (~ 0::BIT(24)) THEN
807    out_priv := out_priv || ARRAY['DAV:all'];
808  END IF;
809
810  IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN
811    IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
812      out_priv := out_priv || ARRAY['DAV:read'];
813    END IF;
814    IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN
815      out_priv := out_priv || ARRAY['caldav:read-free-busy'];
816    END IF;
817  END IF;
818
819  IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
820    IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN
821      out_priv := out_priv || ARRAY['DAV:write'];
822    ELSE
823      IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN
824        out_priv := out_priv || ARRAY['DAV:write-properties'];
825      END IF;
826      IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN
827        out_priv := out_priv || ARRAY['DAV:write-content'];
828      END IF;
829      IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
830        out_priv := out_priv || ARRAY['DAV:bind'];
831      END IF;
832      IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
833        out_priv := out_priv || ARRAY['DAV:unbind'];
834      END IF;
835    END IF;
836  END IF;
837
838  IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN
839    out_priv := out_priv || ARRAY['DAV:unlock'];
840  END IF;
841
842  IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN
843    out_priv := out_priv || ARRAY['DAV:read-acl'];
844  END IF;
845
846  IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN
847    out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set'];
848  END IF;
849
850  IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN
851    out_priv := out_priv || ARRAY['DAV:write-acl'];
852  END IF;
853
854  IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN
855    IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN
856      out_priv := out_priv || ARRAY['caldav:schedule-deliver'];
857    ELSE
858      IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN
859        out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite'];
860      END IF;
861      IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN
862        out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply'];
863      END IF;
864      IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN
865        out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy'];
866      END IF;
867    END IF;
868  END IF;
869
870  IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN
871    IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN
872      out_priv := out_priv || ARRAY['caldav:schedule-send'];
873    ELSE
874      IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN
875        out_priv := out_priv || ARRAY['caldav:schedule-send-invite'];
876      END IF;
877      IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN
878        out_priv := out_priv || ARRAY['caldav:schedule-send-reply'];
879      END IF;
880      IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN
881        out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy'];
882      END IF;
883    END IF;
884  END IF;
885
886  RETURN out_priv;
887END
888$$
889LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
890
891
892-- Expanded group memberships out to some depth
893CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$
894  SELECT group_id FROM group_member WHERE member_id = $1
895      UNION
896  SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
897                               FROM group_member WHERE member_id = $1) AS expanded
898                       WHERE expanded.g_id IS NOT NULL;
899$$ LANGUAGE 'SQL' STABLE STRICT;
900
901-- Expanded group members out to some depth
902CREATE or REPLACE FUNCTION expand_members( INT8, INT ) RETURNS SETOF INT8 AS $$
903  SELECT member_id FROM group_member WHERE group_id = $1
904      UNION
905  SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id
906                               FROM group_member WHERE group_id = $1) AS expanded
907                       WHERE expanded.m_id IS NOT NULL;
908$$ LANGUAGE 'SQL' STABLE STRICT;
909
910
911
912
913-- Privileges from accessor to grantor, by principal_id
914CREATE or REPLACE FUNCTION pprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$
915DECLARE
916  in_accessor ALIAS FOR $1;
917  in_grantor  ALIAS FOR $2;
918  in_depth    ALIAS FOR $3;
919  out_conferred BIT(24);
920BEGIN
921  out_conferred := 0::BIT(24);
922  -- Self can always have full access
923  IF in_grantor = in_accessor THEN
924    RETURN ~ out_conferred;
925  END IF;
926
927  SELECT bit_or(subquery.privileges) INTO out_conferred FROM
928    (
929      SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL
930                                          AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)))
931            UNION
932      SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor
933    ) AS subquery ;
934
935  IF out_conferred IS NULL THEN
936    SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
937  END IF;
938
939  RETURN out_conferred;
940END;
941$$ LANGUAGE 'plpgsql' STABLE STRICT;
942
943
944-- Privileges from accessor to grantor, by user_no
945CREATE or REPLACE FUNCTION uprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$
946DECLARE
947  in_accessor ALIAS FOR $1;
948  in_grantor  ALIAS FOR $2;
949  in_depth    ALIAS FOR $3;
950  out_conferred BIT(24);
951BEGIN
952  out_conferred := 0::BIT(24);
953  -- Self can always have full access
954  IF in_grantor = in_accessor THEN
955    RETURN ~ out_conferred;
956  END IF;
957
958  SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred
959          FROM principal p1, principal p2
960          WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor;
961
962  RETURN out_conferred;
963END;
964$$ LANGUAGE 'plpgsql' STABLE STRICT;
965
966
967-- Privileges from accessor (by principal_id) to path
968CREATE or REPLACE FUNCTION path_privs( INT8, TEXT, INT ) RETURNS BIT(24) AS $$
969DECLARE
970  in_accessor ALIAS FOR $1;
971  in_path  ALIAS FOR $2;
972  in_depth    ALIAS FOR $3;
973
974  alt1_path TEXT;
975  alt2_path TEXT;
976  grantor_collection    INT8;
977  grantor_principal     INT8;
978  collection_path       TEXT;
979  collection_privileges BIT(24);
980  out_conferred         BIT(24);
981BEGIN
982  out_conferred := 0::BIT(24);
983
984  IF in_path ~ '^/?$' THEN
985    -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
986    RETURN 1; -- basic read privileges on root directory
987  END IF;
988
989  -- We need to canonicalise the path, so:
990  -- If it matches '/' + some characters (+ optional '/')  => a principal URL
991  IF in_path ~ '^/[^/]+/?$' THEN
992    alt1_path := replace(in_path, '/', '');
993    SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
994    -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
995    RETURN out_conferred;
996  END IF;
997
998  -- 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.
999  alt1_path := in_path;
1000  IF alt1_path ~ E'\\.ics$' THEN
1001    alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
1002  END IF;
1003  alt2_path := regexp_replace( in_path, '[^/]*$', '');
1004  SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges
1005    INTO grantor_collection, grantor_principal, collection_path, collection_privileges
1006                      FROM collection JOIN principal grantor USING (user_no)
1007                      WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
1008                      ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;
1009
1010  -- Self will always need full access to their own collections!
1011  IF grantor_principal = in_accessor THEN
1012    -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path;
1013    RETURN ~ out_conferred;
1014  END IF;
1015
1016  SELECT privileges INTO out_conferred FROM grants
1017                   WHERE by_collection = grantor_collection
1018                     AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)));
1019
1020  IF out_conferred IS NULL THEN
1021    IF collection_privileges IS NULL THEN
1022      IF grantor_principal IS NULL THEN
1023        alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/');
1024        SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred;
1025        -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1026      ELSE
1027        SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred;
1028        -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1029      END IF;
1030    ELSE
1031      out_conferred := collection_privileges;
1032      -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1033    END IF;
1034  END IF;
1035
1036  RETURN out_conferred;
1037END;
1038$$ LANGUAGE 'plpgsql' STABLE STRICT;
1039
1040
1041-- List a user's memberships as a text string
1042CREATE or REPLACE FUNCTION is_member_of_list( INT8 ) RETURNS TEXT AS $$
1043DECLARE
1044  in_member_id ALIAS FOR $1;
1045  m RECORD;
1046  mlist TEXT;
1047BEGIN
1048  mlist := '';
1049  FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id)
1050                          WHERE member_id = in_member_id
1051  LOOP
1052    mlist := mlist
1053             || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1054             || COALESCE( m.displayname, m.group_id::text);
1055  END LOOP;
1056  RETURN mlist;
1057END;
1058$$ LANGUAGE 'plpgsql' STRICT;
1059
1060
1061-- List a user's members as a text string
1062CREATE or REPLACE FUNCTION has_members_list( INT8 ) RETURNS TEXT AS $$
1063DECLARE
1064  in_member_id ALIAS FOR $1;
1065  m RECORD;
1066  mlist TEXT;
1067BEGIN
1068  mlist := '';
1069  FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id)
1070                          WHERE group_id = in_member_id
1071  LOOP
1072    mlist := mlist
1073             || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1074             || COALESCE( m.displayname, m.group_id::text);
1075  END LOOP;
1076  RETURN mlist;
1077END;
1078$$ LANGUAGE 'plpgsql' STRICT;
1079
1080
1081-- List the privileges as a text string
1082CREATE or REPLACE FUNCTION privileges_list( BIT(24) ) RETURNS TEXT AS $$
1083DECLARE
1084  in_privileges ALIAS FOR $1;
1085  privileges TEXT[];
1086  plist TEXT;
1087  start INT;
1088  finish INT;
1089  i INT;
1090BEGIN
1091  plist := '';
1092
1093  privileges := bits_to_privilege(in_privileges);
1094  SELECT array_lower(privileges,1) INTO start;
1095  IF start IS NOT NULL THEN
1096    SELECT array_upper(privileges,1) INTO finish;
1097    FOR i IN start .. finish  LOOP
1098      plist := plist
1099              || CASE WHEN plist = '' THEN '' ELSE ', ' END
1100              || privileges[i];
1101    END LOOP;
1102  END IF;
1103  RETURN plist;
1104END;
1105$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
1106
1107
1108DROP TRIGGER principal_modified ON principal CASCADE;
1109CREATE or REPLACE FUNCTION principal_modified() RETURNS TRIGGER AS $$
1110DECLARE
1111BEGIN
1112  -- in case we trigger on other events in future
1113  IF TG_OP = 'UPDATE' THEN
1114    IF NEW.type_id != OLD.type_id THEN
1115      UPDATE grants
1116        SET is_group = (NEW.type_id = 3)
1117      WHERE grants.to_principal = NEW.principal_id;
1118    END IF;
1119  END IF;
1120  RETURN NEW;
1121END;
1122$$ LANGUAGE plpgsql;
1123CREATE TRIGGER principal_modified AFTER UPDATE ON principal
1124    FOR EACH ROW EXECUTE PROCEDURE principal_modified();
1125
1126
1127DROP TRIGGER grants_modified ON grants CASCADE;
1128CREATE or REPLACE FUNCTION grants_modified() RETURNS TRIGGER AS $$
1129DECLARE
1130  old_to_principal INT8;
1131  new_is_group BOOL;
1132BEGIN
1133  -- in case we trigger on other events in future
1134  IF TG_OP = 'INSERT' THEN
1135    old_to_principal := NULL;
1136  ELSE
1137    old_to_principal := OLD.to_principal;
1138  END IF;
1139  IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN
1140    SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal;
1141    IF NEW.is_group != new_is_group THEN
1142      NEW.is_group := new_is_group;
1143    END IF;
1144  END IF;
1145  RETURN NEW;
1146END;
1147$$ LANGUAGE plpgsql;
1148CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants
1149    FOR EACH ROW EXECUTE PROCEDURE grants_modified();
1150
1151
1152
1153-- An expanded list of the grants this principal has access to
1154CREATE or REPLACE FUNCTION p_has_proxy_access_to( INT8, INT ) RETURNS SETOF INT8 AS $$
1155  SELECT by_principal
1156    FROM (
1157      SELECT by_principal FROM grants
1158           WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2))
1159             AND (privileges & 5::BIT(24)) != 0::BIT(24)
1160             AND by_collection IS NULL
1161             AND by_principal != $1
1162      UNION
1163      SELECT principal_id AS by_principal FROM principal
1164           WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24)
1165             AND principal_id != $1
1166    ) subquery;
1167$$ LANGUAGE 'SQL' STABLE STRICT;
1168
1169
1170-- A list of the principals who can proxy to this principal
1171CREATE or REPLACE FUNCTION grants_proxy_access_from_p( INT8, INT ) RETURNS SETOF INT8 AS $$
1172  SELECT DISTINCT by_principal
1173    FROM grants
1174   WHERE by_collection IS NULL AND by_principal != $1
1175     AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1)
1176   ;
1177$$ LANGUAGE 'SQL' STABLE STRICT;
1178
1179
1180
1181-- New in 1.2.7
1182
1183CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$
1184DECLARE
1185  in_collection_id ALIAS FOR $1;
1186  in_status ALIAS FOR $2;
1187  in_dav_name ALIAS FOR $3;
1188  tmp_int INT8;
1189BEGIN
1190  SELECT 1 INTO tmp_int FROM sync_tokens
1191           WHERE collection_id = in_collection_id
1192           LIMIT 1;
1193  IF NOT FOUND THEN
1194    RETURN FALSE;
1195  END IF;
1196  SELECT dav_id INTO tmp_int FROM calendar_item WHERE dav_name = in_dav_name;
1197  INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name)
1198                     VALUES( in_collection_id, in_status, tmp_int, in_dav_name);
1199  RETURN TRUE;
1200END
1201$$ LANGUAGE 'PlPgSQL' VOLATILE STRICT;
1202
1203
1204CREATE or REPLACE FUNCTION new_sync_token( INT8, INT8 ) RETURNS INT8 AS $$
1205DECLARE
1206  in_old_sync_token ALIAS FOR $1;
1207  in_collection_id ALIAS FOR $2;
1208  tmp_int INT8;
1209BEGIN
1210  IF in_old_sync_token > 0 THEN
1211    SELECT 1 INTO tmp_int FROM sync_changes
1212            WHERE collection_id = in_collection_id
1213              AND sync_time > (SELECT modification_time FROM sync_tokens WHERE sync_token = in_old_sync_token)
1214            LIMIT 1;
1215    IF NOT FOUND THEN
1216      RETURN in_old_sync_token;
1217    END IF;
1218  END IF;
1219  SELECT nextval('sync_tokens_sync_token_seq') INTO tmp_int;
1220  INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, tmp_int );
1221  RETURN tmp_int;
1222END
1223$$ LANGUAGE 'PlPgSQL' STRICT;
1224
1225
1226DROP TRIGGER alarm_changed ON calendar_alarm CASCADE;
1227CREATE or REPLACE FUNCTION alarm_changed() RETURNS TRIGGER AS $$
1228DECLARE
1229  oldcomponent TEXT;
1230  newcomponent TEXT;
1231BEGIN
1232  -- in case we trigger on other events in future
1233  IF TG_OP = 'UPDATE' THEN
1234    IF NEW.component != OLD.component THEN
1235      UPDATE caldav_data
1236         SET caldav_data = replace( caldav_data, OLD.component, NEW.component ),
1237             dav_etag = md5(replace( caldav_data, OLD.component, NEW.component ))
1238       WHERE caldav_data.dav_id = NEW.dav_id;
1239    END IF;
1240  END IF;
1241  RETURN NEW;
1242END;
1243$$ LANGUAGE plpgsql;
1244CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm
1245    FOR EACH ROW EXECUTE PROCEDURE alarm_changed();
Note: See TracBrowser for help on using the repository browser.