Switch to the Affero GPL3 license.
[mailshears.git] / test / sql / davical.sql
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET statement_timeout = 0;
6 SET client_encoding = 'UTF8';
7 SET standard_conforming_strings = on;
8 SET check_function_bodies = false;
9 SET client_min_messages = warning;
10
11 --
12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
13 --
14
15 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
16
17
18 --
19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
20 --
21
22 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
23
24
25 SET search_path = public, pg_catalog;
26
27 --
28 -- Name: rrule_instance; Type: TYPE; Schema: public; Owner: postgres
29 --
30
31 CREATE TYPE rrule_instance AS (
32 dtstart timestamp with time zone,
33 rrule text,
34 instance timestamp with time zone
35 );
36
37
38 ALTER TYPE public.rrule_instance OWNER TO postgres;
39
40 --
41 -- Name: rrule_parts; Type: TYPE; Schema: public; Owner: postgres
42 --
43
44 CREATE TYPE rrule_parts AS (
45 base timestamp with time zone,
46 until timestamp with time zone,
47 freq text,
48 count integer,
49 "interval" integer,
50 bysecond integer[],
51 byminute integer[],
52 byhour integer[],
53 bymonthday integer[],
54 byyearday integer[],
55 byweekno integer[],
56 byday text[],
57 bymonth integer[],
58 bysetpos integer[],
59 wkst text
60 );
61
62
63 ALTER TYPE public.rrule_parts OWNER TO postgres;
64
65 --
66 -- Name: alarm_changed(); Type: FUNCTION; Schema: public; Owner: postgres
67 --
68
69 CREATE FUNCTION alarm_changed() RETURNS trigger
70 LANGUAGE plpgsql
71 AS $$
72 DECLARE
73 oldcomponent TEXT;
74 newcomponent TEXT;
75 BEGIN
76 -- in case we trigger on other events in future
77 IF TG_OP = 'UPDATE' THEN
78 IF NEW.component != OLD.component THEN
79 UPDATE caldav_data
80 SET caldav_data = replace( caldav_data, OLD.component, NEW.component ),
81 dav_etag = md5(replace( caldav_data, OLD.component, NEW.component ))
82 WHERE caldav_data.dav_id = NEW.dav_id;
83 END IF;
84 END IF;
85 RETURN NEW;
86 END;
87 $$;
88
89
90 ALTER FUNCTION public.alarm_changed() OWNER TO postgres;
91
92 --
93 -- Name: apply_month_byday(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
94 --
95
96 CREATE FUNCTION apply_month_byday(timestamp with time zone, text) RETURNS timestamp with time zone
97 LANGUAGE plpgsql IMMUTABLE STRICT
98 AS $_$
99 DECLARE
100 in_time ALIAS FOR $1;
101 byday ALIAS FOR $2;
102 weeks INT;
103 dow INT;
104 temp_txt TEXT;
105 dd INT;
106 mm INT;
107 yy INT;
108 our_dow INT;
109 our_answer TIMESTAMP WITH TIME ZONE;
110 BEGIN
111 dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2;
112 temp_txt := substring(byday from '([0-9]+)');
113 weeks := temp_txt::int;
114
115 -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
116
117 IF substring(byday for 1) = '-' THEN
118 -- Last XX of month, or possibly second-to-last, but unlikely
119 mm := extract( 'month' from in_time);
120 yy := extract( 'year' from in_time);
121
122 -- Start with the last day of the month
123 our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval;
124 dd := extract( 'dow' from our_answer);
125 dd := dd - dow;
126 IF dd < 0 THEN
127 dd := dd + 7;
128 END IF;
129
130 -- Having calculated the right day of the month, we now apply that back to in_time
131 -- which contains the otherwise-unobtainable timezone detail (and the time)
132 our_answer = our_answer - (dd::text || 'days')::interval;
133 dd := extract( 'day' from our_answer) - extract( 'day' from in_time);
134 our_answer := in_time + (dd::text || 'days')::interval;
135
136 IF weeks > 1 THEN
137 weeks := weeks - 1;
138 our_answer := our_answer - (weeks::text || 'weeks')::interval;
139 END IF;
140
141 ELSE
142
143 -- Shift our date to the correct day of week..
144 our_dow := extract( 'dow' from in_time);
145 our_dow := our_dow - dow;
146 dd := extract( 'day' from in_time);
147 IF our_dow >= dd THEN
148 our_dow := our_dow - 7;
149 END IF;
150 our_answer := in_time - (our_dow::text || 'days')::interval;
151 dd = extract( 'day' from our_answer);
152
153 -- Shift the date to the correct week...
154 dd := weeks - ((dd+6) / 7);
155 IF dd != 0 THEN
156 our_answer := our_answer + ((dd::text || 'weeks')::interval);
157 END IF;
158
159 END IF;
160
161 RETURN our_answer;
162
163 END;
164 $_$;
165
166
167 ALTER FUNCTION public.apply_month_byday(timestamp with time zone, text) OWNER TO postgres;
168
169 --
170 -- Name: bits_to_legacy_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
171 --
172
173 CREATE FUNCTION bits_to_legacy_privilege(bit) RETURNS text
174 LANGUAGE plpgsql IMMUTABLE STRICT
175 AS $_$
176 DECLARE
177 in_bits ALIAS FOR $1;
178 out_priv TEXT;
179 BEGIN
180 out_priv := '';
181 IF in_bits = (~ 0::BIT(24)) THEN
182 out_priv = 'A';
183 RETURN out_priv;
184 END IF;
185
186 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
187 -- 1 DAV:read
188 -- 512 CalDAV:read-free-busy
189 -- 4096 CALDAV:schedule-query-freebusy
190 IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN
191 IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
192 out_priv := 'R';
193 ELSE
194 out_priv := 'F';
195 END IF;
196 END IF;
197
198 -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
199 -- 2 DAV:write-properties
200 -- 4 DAV:write-content
201 -- 64 DAV:bind
202 -- 128 DAV:unbind
203 IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
204 IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN
205 out_priv := out_priv || 'W';
206 ELSE
207 IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
208 out_priv := out_priv || 'B';
209 END IF;
210 IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
211 out_priv := out_priv || 'U';
212 END IF;
213 END IF;
214 END IF;
215
216 RETURN out_priv;
217 END
218 $_$;
219
220
221 ALTER FUNCTION public.bits_to_legacy_privilege(bit) OWNER TO postgres;
222
223 --
224 -- Name: bits_to_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
225 --
226
227 CREATE FUNCTION bits_to_privilege(bit) RETURNS text[]
228 LANGUAGE plpgsql IMMUTABLE STRICT
229 AS $_$
230 DECLARE
231 in_bits ALIAS FOR $1;
232 out_priv TEXT[];
233 BEGIN
234 IF in_bits = (~ 0::BIT(24)) THEN
235 out_priv := out_priv || ARRAY['DAV:all'];
236 END IF;
237
238 IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN
239 IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
240 out_priv := out_priv || ARRAY['DAV:read'];
241 END IF;
242 IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN
243 out_priv := out_priv || ARRAY['caldav:read-free-busy'];
244 END IF;
245 END IF;
246
247 IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
248 IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN
249 out_priv := out_priv || ARRAY['DAV:write'];
250 ELSE
251 IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN
252 out_priv := out_priv || ARRAY['DAV:write-properties'];
253 END IF;
254 IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN
255 out_priv := out_priv || ARRAY['DAV:write-content'];
256 END IF;
257 IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
258 out_priv := out_priv || ARRAY['DAV:bind'];
259 END IF;
260 IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
261 out_priv := out_priv || ARRAY['DAV:unbind'];
262 END IF;
263 END IF;
264 END IF;
265
266 IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN
267 out_priv := out_priv || ARRAY['DAV:unlock'];
268 END IF;
269
270 IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN
271 out_priv := out_priv || ARRAY['DAV:read-acl'];
272 END IF;
273
274 IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN
275 out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set'];
276 END IF;
277
278 IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN
279 out_priv := out_priv || ARRAY['DAV:write-acl'];
280 END IF;
281
282 IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN
283 IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN
284 out_priv := out_priv || ARRAY['caldav:schedule-deliver'];
285 ELSE
286 IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN
287 out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite'];
288 END IF;
289 IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN
290 out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply'];
291 END IF;
292 IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN
293 out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy'];
294 END IF;
295 END IF;
296 END IF;
297
298 IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN
299 IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN
300 out_priv := out_priv || ARRAY['caldav:schedule-send'];
301 ELSE
302 IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN
303 out_priv := out_priv || ARRAY['caldav:schedule-send-invite'];
304 END IF;
305 IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN
306 out_priv := out_priv || ARRAY['caldav:schedule-send-reply'];
307 END IF;
308 IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN
309 out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy'];
310 END IF;
311 END IF;
312 END IF;
313
314 RETURN out_priv;
315 END
316 $_$;
317
318
319 ALTER FUNCTION public.bits_to_privilege(bit) OWNER TO postgres;
320
321 --
322 -- Name: calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
323 --
324
325 CREATE FUNCTION calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) RETURNS timestamp with time zone
326 LANGUAGE plpgsql IMMUTABLE STRICT
327 AS $_$
328 DECLARE
329 earliest ALIAS FOR $1;
330 basedate ALIAS FOR $2;
331 repeatrule ALIAS FOR $3;
332 frequency TEXT;
333 temp_txt TEXT;
334 length INT;
335 count INT;
336 byday TEXT;
337 bymonthday INT;
338 basediff INTERVAL;
339 past_repeats INT8;
340 units TEXT;
341 dow TEXT;
342 our_answer TIMESTAMP WITH TIME ZONE;
343 loopcount INT;
344 BEGIN
345 IF basedate > earliest THEN
346 RETURN basedate;
347 END IF;
348
349 temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
350 IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN
351 RETURN NULL;
352 END IF;
353
354 frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
355 IF frequency IS NULL THEN
356 RETURN NULL;
357 END IF;
358
359 past_repeats = 0;
360 length = 1;
361 temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)');
362 IF temp_txt IS NOT NULL THEN
363 length := temp_txt::int;
364 basediff := earliest - basedate;
365
366 -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
367
368 -- Calculate the number of past periods between our base date and our earliest date
369 IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN
370 past_repeats := extract('epoch' from basediff)::INT8 / 86400;
371 -- RAISE NOTICE 'Days: %', past_repeats;
372 IF frequency = 'WEEKLY' THEN
373 past_repeats := past_repeats / 7;
374 END IF;
375 ELSE
376 past_repeats = extract( 'years' from basediff );
377 IF frequency = 'MONTHLY' THEN
378 past_repeats = (past_repeats *12) + extract( 'months' from basediff );
379 END IF;
380 END IF;
381 IF length IS NOT NULL THEN
382 past_repeats = (past_repeats / length) + 1;
383 END IF;
384 END IF;
385
386 -- Check that we have not exceeded the COUNT= limit
387 temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
388 IF temp_txt IS NOT NULL THEN
389 count := temp_txt::int;
390 -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length;
391 IF ( count <= past_repeats ) THEN
392 RETURN NULL;
393 END IF;
394 ELSE
395 count := NULL;
396 END IF;
397
398 temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)');
399 byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)');
400 IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN
401 -- Since this could move the date around a month we go back one
402 -- period just to be extra sure.
403 past_repeats = past_repeats - 1;
404
405 IF temp_txt IS NOT NULL THEN
406 -- Crudely hack the BYSETPOS onto the front of BYDAY. While this
407 -- is not as per rfc2445, RRULE syntax is so complex and overblown
408 -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and
409 -- certainly not within a MONTHLY RRULE.
410 byday := temp_txt || byday;
411 END IF;
412 END IF;
413
414 past_repeats = past_repeats * length;
415
416 units := CASE
417 WHEN frequency = 'DAILY' THEN 'days'
418 WHEN frequency = 'WEEKLY' THEN 'weeks'
419 WHEN frequency = 'MONTHLY' THEN 'months'
420 WHEN frequency = 'YEARLY' THEN 'years'
421 END;
422
423 temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)');
424 bymonthday := temp_txt::int;
425
426 -- With all of the above calculation, this date should be close to (but less than)
427 -- the target, and we should only loop once or twice.
428 our_answer := basedate + (past_repeats::text || units)::interval;
429
430 IF our_answer IS NULL THEN
431 RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units;
432 END IF;
433
434
435 loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle
436 LOOP
437 -- RAISE NOTICE 'Testing date: %', our_answer;
438 IF frequency = 'DAILY' THEN
439 IF byday IS NOT NULL THEN
440 LOOP
441 dow = substring( to_char( our_answer, 'DY' ) for 2);
442 EXIT WHEN byday ~* dow;
443 -- Increment for our next time through the loop...
444 our_answer := our_answer + (length::text || units)::interval;
445 END LOOP;
446 END IF;
447 ELSIF frequency = 'WEEKLY' THEN
448 -- Weekly repeats are only on specific days
449 -- This is really not right, since a WEEKLY on MO,WE,FR should
450 -- occur three times each week and this will only be once a week.
451 dow = substring( to_char( our_answer, 'DY' ) for 2);
452 ELSIF frequency = 'MONTHLY' THEN
453 IF byday IS NOT NULL THEN
454 -- This works fine, except that maybe there are multiple BYDAY
455 -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays.
456 our_answer := apply_month_byday( our_answer, byday );
457 ELSE
458 -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month
459 our_answer := our_answer + '1 month'::interval;
460 END IF;
461 ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN
462 -- We do not deal with this situation at present
463 RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.';
464 END IF;
465
466 EXIT WHEN our_answer >= earliest;
467
468 -- Give up if we have exceeded the count
469 IF ( count IS NOT NULL AND past_repeats > count ) THEN
470 RETURN NULL;
471 ELSE
472 past_repeats := past_repeats + 1;
473 END IF;
474
475 loopcount := loopcount - 1;
476 IF loopcount < 0 THEN
477 RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest;
478 RETURN NULL;
479 END IF;
480
481 -- Increment for our next time through the loop...
482 our_answer := our_answer + (length::text || units)::interval;
483
484 END LOOP;
485
486 RETURN our_answer;
487
488 END;
489 $_$;
490
491
492 ALTER FUNCTION public.calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) OWNER TO postgres;
493
494 --
495 -- Name: caldav_data_modified(); Type: FUNCTION; Schema: public; Owner: postgres
496 --
497
498 CREATE FUNCTION caldav_data_modified() RETURNS trigger
499 LANGUAGE plpgsql
500 AS $$
501 DECLARE
502 coll_id caldav_data.collection_id%TYPE;
503 BEGIN
504 IF TG_OP = 'UPDATE' THEN
505 IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN
506 -- Nothing for us to do
507 RETURN NEW;
508 END IF;
509 END IF;
510
511 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
512 -- On insert or update modified, we set the NEW collection tag to the md5 of the
513 -- etag of the updated row which gives us something predictable for our regression
514 -- tests, but something different from the actual etag of the new event.
515 UPDATE collection
516 SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag)
517 WHERE collection_id = NEW.collection_id;
518 IF TG_OP = 'INSERT' THEN
519 RETURN NEW;
520 END IF;
521 END IF;
522
523 IF TG_OP = 'DELETE' THEN
524 -- On delete we set the OLD collection tag to the md5 of the old path & the old
525 -- etag, which again gives us something predictable for our regression tests.
526 UPDATE collection
527 SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
528 WHERE collection_id = OLD.collection_id;
529 RETURN OLD;
530 END IF;
531
532 IF NEW.collection_id != OLD.collection_id THEN
533 -- If we've switched the collection_id of this event, then we also need to update
534 -- the etag of the old collection - as we do for delete.
535 UPDATE collection
536 SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
537 WHERE collection_id = OLD.collection_id;
538 END IF;
539 RETURN NEW;
540 END;
541 $$;
542
543
544 ALTER FUNCTION public.caldav_data_modified() OWNER TO postgres;
545
546 --
547 -- Name: check_db_revision(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
548 --
549
550 CREATE FUNCTION check_db_revision(integer, integer, integer) RETURNS boolean
551 LANGUAGE plpgsql
552 AS $_$
553 DECLARE
554 major ALIAS FOR $1;
555 minor ALIAS FOR $2;
556 patch ALIAS FOR $3;
557 matching INT;
558 BEGIN
559 SELECT COUNT(*) INTO matching FROM awl_db_revision
560 WHERE (schema_major = major AND schema_minor = minor AND schema_patch > patch)
561 OR (schema_major = major AND schema_minor > minor)
562 OR (schema_major > major)
563 ;
564 IF matching >= 1 THEN
565 RAISE EXCEPTION 'Database revisions after %.%.% have already been applied.', major, minor, patch;
566 RETURN FALSE;
567 END IF;
568 SELECT COUNT(*) INTO matching FROM awl_db_revision
569 WHERE schema_major = major AND schema_minor = minor AND schema_patch = patch;
570 IF matching >= 1 THEN
571 RETURN TRUE;
572 END IF;
573 RAISE EXCEPTION 'Database has not been upgraded to %.%.%', major, minor, patch;
574 RETURN FALSE;
575 END;
576 $_$;
577
578
579 ALTER FUNCTION public.check_db_revision(integer, integer, integer) OWNER TO postgres;
580
581 --
582 -- Name: collection_modified(); Type: FUNCTION; Schema: public; Owner: postgres
583 --
584
585 CREATE FUNCTION collection_modified() RETURNS trigger
586 LANGUAGE plpgsql
587 AS $$
588 DECLARE
589 BEGIN
590 -- in case we trigger on other events in future
591 IF TG_OP = 'UPDATE' THEN
592 IF NEW.dav_name != OLD.dav_name THEN
593 UPDATE caldav_data
594 SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name),
595 user_no = NEW.user_no
596 WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name;
597 END IF;
598 END IF;
599 RETURN NEW;
600 END;
601 $$;
602
603
604 ALTER FUNCTION public.collection_modified() OWNER TO postgres;
605
606 --
607 -- Name: collections_within(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
608 --
609
610 CREATE FUNCTION collections_within(integer, integer) RETURNS SETOF integer
611 LANGUAGE plpgsql
612 AS $_$
613 DECLARE
614 in_collection_id ALIAS FOR $1;
615 in_depth ALIAS FOR $2;
616 resource_id INT;
617 found_some BOOLEAN;
618 BEGIN
619 in_depth := in_depth - 1;
620 found_some = FALSE;
621 FOR resource_id IN SELECT b.bound_source_id FROM dav_binding b
622 JOIN collection pc ON (b.parent_container = pc.dav_name)
623 WHERE pc.collection_id = in_collection_id
624 LOOP
625 found_some = TRUE;
626 RETURN NEXT resource_id;
627 IF in_depth > 0 THEN
628 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
629 RETURN NEXT resource_id;
630 END LOOP;
631 END IF;
632 END LOOP;
633 FOR resource_id IN SELECT c.collection_id FROM collection c
634 JOIN collection pc ON (c.parent_container = pc.dav_name)
635 WHERE pc.collection_id = in_collection_id
636 LOOP
637 found_some = TRUE;
638 RETURN NEXT resource_id;
639 IF in_depth > 0 THEN
640 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
641 RETURN NEXT resource_id;
642 END LOOP;
643 END IF;
644 END LOOP;
645 IF found_some THEN
646 RETURN;
647 END IF;
648 FOR resource_id IN SELECT c.collection_id FROM collection c
649 JOIN dav_principal pc ON (c.parent_container = pc.dav_name)
650 WHERE pc.principal_id = in_collection_id
651 LOOP
652 RETURN NEXT resource_id;
653 IF in_depth > 0 THEN
654 FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
655 RETURN NEXT resource_id;
656 END LOOP;
657 END IF;
658 END LOOP;
659 END;
660 $_$;
661
662
663 ALTER FUNCTION public.collections_within(integer, integer) OWNER TO postgres;
664
665 --
666 -- Name: daily_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
667 --
668
669 CREATE FUNCTION daily_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
670 LANGUAGE plpgsql IMMUTABLE STRICT
671 AS $_$
672 DECLARE
673 after ALIAS FOR $1;
674 rrule ALIAS FOR $2;
675 BEGIN
676
677 IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN
678 RETURN;
679 END IF;
680
681 IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN
682 RETURN;
683 END IF;
684
685 IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN
686 RETURN;
687 END IF;
688
689 IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN
690 RETURN;
691 END IF;
692
693 IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN
694 RETURN;
695 END IF;
696
697 -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial
698 RETURN NEXT after;
699
700 END;
701 $_$;
702
703
704 ALTER FUNCTION public.daily_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
705
706 --
707 -- Name: event_has_exceptions(text); Type: FUNCTION; Schema: public; Owner: postgres
708 --
709
710 CREATE FUNCTION event_has_exceptions(text) RETURNS boolean
711 LANGUAGE sql IMMUTABLE STRICT
712 AS $_$
713 SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?'
714 $_$;
715
716
717 ALTER FUNCTION public.event_has_exceptions(text) OWNER TO postgres;
718
719 --
720 -- Name: event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
721 --
722
723 CREATE FUNCTION event_instances(timestamp with time zone, text) RETURNS SETOF timestamp with time zone
724 LANGUAGE plpgsql IMMUTABLE STRICT
725 AS $_$
726 DECLARE
727 basedate ALIAS FOR $1;
728 repeatrule ALIAS FOR $2;
729 maxdate TIMESTAMP WITH TIME ZONE;
730 BEGIN
731 maxdate := current_date + '10 years'::interval;
732 RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d;
733 END;
734 $_$;
735
736
737 ALTER FUNCTION public.event_instances(timestamp with time zone, text) OWNER TO postgres;
738
739 --
740 -- Name: expand_members(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
741 --
742
743 CREATE FUNCTION expand_members(bigint, integer) RETURNS SETOF bigint
744 LANGUAGE sql STABLE STRICT
745 AS $_$
746 SELECT member_id FROM group_member WHERE group_id = $1
747 UNION
748 SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id
749 FROM group_member WHERE group_id = $1) AS expanded
750 WHERE expanded.m_id IS NOT NULL;
751 $_$;
752
753
754 ALTER FUNCTION public.expand_members(bigint, integer) OWNER TO postgres;
755
756 --
757 -- Name: expand_memberships(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
758 --
759
760 CREATE FUNCTION expand_memberships(bigint, integer) RETURNS SETOF bigint
761 LANGUAGE sql STABLE STRICT
762 AS $_$
763 SELECT group_id FROM group_member WHERE member_id = $1
764 UNION
765 SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
766 FROM group_member WHERE member_id = $1) AS expanded
767 WHERE expanded.g_id IS NOT NULL;
768 $_$;
769
770
771 ALTER FUNCTION public.expand_memberships(bigint, integer) OWNER TO postgres;
772
773 --
774 -- Name: get_group_role_no(); Type: FUNCTION; Schema: public; Owner: postgres
775 --
776
777 CREATE FUNCTION get_group_role_no() RETURNS integer
778 LANGUAGE sql IMMUTABLE
779 AS $$
780 SELECT role_no FROM roles WHERE role_name = 'Group'
781 $$;
782
783
784 ALTER FUNCTION public.get_group_role_no() OWNER TO postgres;
785
786 --
787 -- Name: get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
788 --
789
790 CREATE FUNCTION get_permissions(integer, integer) RETURNS text
791 LANGUAGE plpgsql IMMUTABLE STRICT
792 AS $_$
793 DECLARE
794 in_from ALIAS FOR $1;
795 in_to ALIAS FOR $2;
796 out_confers TEXT;
797 bit_confers BIT(24);
798 group_role_no INT;
799 tmp_txt TEXT;
800 dbg TEXT DEFAULT '';
801 r RECORD;
802 counter INT;
803 BEGIN
804 -- Self can always have full access
805 IF in_from = in_to THEN
806 RETURN 'A';
807 END IF;
808
809 -- dbg := 'S-';
810 SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1
811 WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
812 IF FOUND THEN
813 RETURN dbg || out_confers;
814 END IF;
815 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
816
817 SELECT bit_or(r1.confers & r2.confers) INTO bit_confers
818 FROM relationship r1
819 JOIN relationship r2 ON r1.to_user=r2.from_user
820 WHERE r1.from_user=in_from AND r2.to_user=in_to
821 AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group');
822 IF bit_confers != 0::BIT(24) THEN
823 RETURN dbg || bits_to_legacy_privilege(bit_confers);
824 END IF;
825
826 RETURN '';
827 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
828
829 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)
830 WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user
831 AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ;
832
833 IF FOUND THEN
834 -- dbg := 'H-';
835 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
836 RETURN dbg || out_confers;
837 END IF;
838
839 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
840
841 RETURN '';
842 END;
843 $_$;
844
845
846 ALTER FUNCTION public.get_permissions(integer, integer) OWNER TO postgres;
847
848 --
849 -- Name: get_usr_setting(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
850 --
851
852 CREATE FUNCTION get_usr_setting(integer, text) RETURNS text
853 LANGUAGE sql
854 AS $_$SELECT setting_value FROM usr_setting
855 WHERE usr_setting.user_no = $1
856 AND usr_setting.setting_name = $2 $_$;
857
858
859 ALTER FUNCTION public.get_usr_setting(integer, text) OWNER TO postgres;
860
861 --
862 -- Name: grants_modified(); Type: FUNCTION; Schema: public; Owner: postgres
863 --
864
865 CREATE FUNCTION grants_modified() RETURNS trigger
866 LANGUAGE plpgsql
867 AS $$
868 DECLARE
869 old_to_principal INT8;
870 new_is_group BOOL;
871 BEGIN
872 -- in case we trigger on other events in future
873 IF TG_OP = 'INSERT' THEN
874 old_to_principal := NULL;
875 ELSE
876 old_to_principal := OLD.to_principal;
877 END IF;
878 IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN
879 SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal;
880 IF NEW.is_group != new_is_group THEN
881 NEW.is_group := new_is_group;
882 END IF;
883 END IF;
884 RETURN NEW;
885 END;
886 $$;
887
888
889 ALTER FUNCTION public.grants_modified() OWNER TO postgres;
890
891 --
892 -- Name: grants_proxy_access_from_p(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
893 --
894
895 CREATE FUNCTION grants_proxy_access_from_p(bigint, integer) RETURNS SETOF bigint
896 LANGUAGE sql STABLE STRICT
897 AS $_$
898 SELECT DISTINCT by_principal
899 FROM grants
900 WHERE by_collection IS NULL AND by_principal != $1
901 AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1)
902 ;
903 $_$;
904
905
906 ALTER FUNCTION public.grants_proxy_access_from_p(bigint, integer) OWNER TO postgres;
907
908 --
909 -- Name: has_legacy_privilege(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
910 --
911
912 CREATE FUNCTION has_legacy_privilege(integer, text, integer) RETURNS boolean
913 LANGUAGE plpgsql IMMUTABLE STRICT
914 AS $_$
915 DECLARE
916 in_from ALIAS FOR $1;
917 in_legacy_privilege ALIAS FOR $2;
918 in_to ALIAS FOR $3;
919 in_confers BIT(24);
920 group_role_no INT;
921 BEGIN
922 -- Self can always have full access
923 IF in_from = in_to THEN
924 RETURN TRUE;
925 END IF;
926
927 SELECT get_group_role_no() INTO group_role_no;
928 SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers;
929
930 IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to
931 AND (in_confers & confers) = in_confers
932 AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN
933 -- A direct relationship from A to B that grants sufficient
934 -- RAISE NOTICE 'Permissions directly granted';
935 RETURN TRUE;
936 END IF;
937
938 IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user
939 WHERE (in_confers & r1.confers & r2.confers) = in_confers
940 AND r1.from_user=in_from AND r2.to_user=in_to
941 AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN
942 -- An indirect relationship from A to B via group G that grants sufficient
943 -- RAISE NOTICE 'Permissions mediated via group';
944 RETURN TRUE;
945 END IF;
946
947 IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user
948 WHERE (in_confers & r1.confers & r2.confers) = in_confers
949 AND r1.from_user=in_from AND r2.from_user=in_to
950 AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no)
951 AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN
952 -- An indirect reflexive relationship from both A & B to group G which grants sufficient
953 -- RAISE NOTICE 'Permissions to shared group';
954 RETURN TRUE;
955 END IF;
956
957 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
958
959 RETURN FALSE;
960 END;
961 $_$;
962
963
964 ALTER FUNCTION public.has_legacy_privilege(integer, text, integer) OWNER TO postgres;
965
966 --
967 -- Name: has_members_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
968 --
969
970 CREATE FUNCTION has_members_list(bigint) RETURNS text
971 LANGUAGE plpgsql STRICT
972 AS $_$
973 DECLARE
974 in_member_id ALIAS FOR $1;
975 m RECORD;
976 mlist TEXT;
977 BEGIN
978 mlist := '';
979 FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id)
980 WHERE group_id = in_member_id
981 LOOP
982 mlist := mlist
983 || CASE WHEN mlist = '' THEN '' ELSE ', ' END
984 || COALESCE( m.displayname, m.group_id::text);
985 END LOOP;
986 RETURN mlist;
987 END;
988 $_$;
989
990
991 ALTER FUNCTION public.has_members_list(bigint) OWNER TO postgres;
992
993 --
994 -- Name: icalendar_interval_to_sql(text); Type: FUNCTION; Schema: public; Owner: postgres
995 --
996
997 CREATE FUNCTION icalendar_interval_to_sql(text) RETURNS interval
998 LANGUAGE sql IMMUTABLE STRICT
999 AS $_$
1000 SELECT CASE WHEN substring($1,1,1) = '-' THEN -1 ELSE 1 END * regexp_replace( regexp_replace($1, '[PT-]', '', 'g'), '([A-Z])', E'\\1 ', 'g')::interval;
1001 $_$;
1002
1003
1004 ALTER FUNCTION public.icalendar_interval_to_sql(text) OWNER TO postgres;
1005
1006 --
1007 -- Name: is_member_of_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1008 --
1009
1010 CREATE FUNCTION is_member_of_list(bigint) RETURNS text
1011 LANGUAGE plpgsql STRICT
1012 AS $_$
1013 DECLARE
1014 in_member_id ALIAS FOR $1;
1015 m RECORD;
1016 mlist TEXT;
1017 BEGIN
1018 mlist := '';
1019 FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id)
1020 WHERE member_id = in_member_id
1021 LOOP
1022 mlist := mlist
1023 || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1024 || COALESCE( m.displayname, m.group_id::text);
1025 END LOOP;
1026 RETURN mlist;
1027 END;
1028 $_$;
1029
1030
1031 ALTER FUNCTION public.is_member_of_list(bigint) OWNER TO postgres;
1032
1033 --
1034 -- Name: legacy_get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
1035 --
1036
1037 CREATE FUNCTION legacy_get_permissions(integer, integer) RETURNS text
1038 LANGUAGE plpgsql IMMUTABLE STRICT
1039 AS $_$
1040 DECLARE
1041 in_from ALIAS FOR $1;
1042 in_to ALIAS FOR $2;
1043 out_confers TEXT;
1044 tmp_confers1 TEXT;
1045 tmp_confers2 TEXT;
1046 tmp_txt TEXT;
1047 dbg TEXT DEFAULT '';
1048 r RECORD;
1049 counter INT;
1050 BEGIN
1051 -- Self can always have full access
1052 IF in_from = in_to THEN
1053 RETURN 'A';
1054 END IF;
1055
1056 -- dbg := 'S-';
1057 SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id )
1058 WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
1059 IF FOUND THEN
1060 RETURN dbg || out_confers;
1061 END IF;
1062 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
1063
1064 out_confers := '';
1065 FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id)
1066 JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id
1067 WHERE r1.from_user=in_from AND r2.to_user=in_to
1068 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')
1069 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')
1070 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')
1071 LOOP
1072 -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
1073 -- FIXME: This is an oversimplification
1074 -- dbg := 'C-';
1075 tmp_confers1 := r.r1;
1076 tmp_confers2 := r.r2;
1077 IF tmp_confers1 != tmp_confers2 THEN
1078 IF tmp_confers1 ~* 'A' THEN
1079 -- Ensure that A is expanded to all supported privs before being used as a mask
1080 tmp_confers1 := 'AFBRWU';
1081 END IF;
1082 IF tmp_confers2 ~* 'A' THEN
1083 -- Ensure that A is expanded to all supported privs before being used as a mask
1084 tmp_confers2 := 'AFBRWU';
1085 END IF;
1086 -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
1087 tmp_txt = '';
1088 FOR counter IN 1 .. length(tmp_confers2) LOOP
1089 IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN
1090 tmp_txt := tmp_txt || substring(tmp_confers2,counter,1);
1091 END IF;
1092 END LOOP;
1093 tmp_confers2 := tmp_txt;
1094 END IF;
1095 FOR counter IN 1 .. length(tmp_confers2) LOOP
1096 IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN
1097 out_confers := out_confers || substring(tmp_confers2,counter,1);
1098 END IF;
1099 END LOOP;
1100 END LOOP;
1101 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
1102 out_confers := 'A';
1103 END IF;
1104 IF out_confers != '' THEN
1105 RETURN dbg || out_confers;
1106 END IF;
1107
1108 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
1109
1110 SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id )
1111 LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id )
1112 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
1113 AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user )
1114 AND usr_is_role(r1.to_user,'Group');
1115
1116 IF FOUND THEN
1117 -- dbg := 'H-';
1118 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
1119 RETURN dbg || out_confers;
1120 END IF;
1121
1122 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
1123
1124 RETURN '';
1125 END;
1126 $_$;
1127
1128
1129 ALTER FUNCTION public.legacy_get_permissions(integer, integer) OWNER TO postgres;
1130
1131 --
1132 -- Name: legacy_privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1133 --
1134
1135 CREATE FUNCTION legacy_privilege_to_bits(text) RETURNS bit
1136 LANGUAGE plpgsql IMMUTABLE STRICT
1137 AS $_$
1138 DECLARE
1139 in_priv ALIAS FOR $1;
1140 out_bits BIT(24);
1141 BEGIN
1142 out_bits := 0::BIT(24);
1143 IF in_priv ~* 'A' THEN
1144 out_bits = ~ out_bits;
1145 RETURN out_bits;
1146 END IF;
1147
1148 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
1149 -- 1 DAV:read
1150 -- 512 CalDAV:read-free-busy
1151 -- 4096 CALDAV:schedule-query-freebusy
1152 IF in_priv ~* 'R' THEN
1153 out_bits := out_bits | 4609::BIT(24);
1154 END IF;
1155
1156 -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
1157 -- 2 DAV:write-properties
1158 -- 4 DAV:write-content
1159 -- 64 DAV:bind
1160 -- 128 DAV:unbind
1161 IF in_priv ~* 'W' THEN
1162 out_bits := out_bits | 198::BIT(24);
1163 END IF;
1164
1165 -- 64 DAV:bind
1166 IF in_priv ~* 'B' THEN
1167 out_bits := out_bits | 64::BIT(24);
1168 END IF;
1169
1170 -- 128 DAV:unbind
1171 IF in_priv ~* 'U' THEN
1172 out_bits := out_bits | 128::BIT(24);
1173 END IF;
1174
1175 -- 512 CalDAV:read-free-busy
1176 -- 4096 CALDAV:schedule-query-freebusy
1177 IF in_priv ~* 'F' THEN
1178 out_bits := out_bits | 4608::BIT(24);
1179 END IF;
1180
1181 RETURN out_bits;
1182 END
1183 $_$;
1184
1185
1186 ALTER FUNCTION public.legacy_privilege_to_bits(text) OWNER TO postgres;
1187
1188 --
1189 -- Name: max_roles(); Type: FUNCTION; Schema: public; Owner: postgres
1190 --
1191
1192 CREATE FUNCTION max_roles() RETURNS integer
1193 LANGUAGE sql
1194 AS $$SELECT max(role_no) FROM roles$$;
1195
1196
1197 ALTER FUNCTION public.max_roles() OWNER TO postgres;
1198
1199 --
1200 -- Name: max_session(); Type: FUNCTION; Schema: public; Owner: postgres
1201 --
1202
1203 CREATE FUNCTION max_session() RETURNS integer
1204 LANGUAGE sql
1205 AS $$SELECT max(session_id) FROM session$$;
1206
1207
1208 ALTER FUNCTION public.max_session() OWNER TO postgres;
1209
1210 --
1211 -- Name: max_usr(); Type: FUNCTION; Schema: public; Owner: postgres
1212 --
1213
1214 CREATE FUNCTION max_usr() RETURNS integer
1215 LANGUAGE sql
1216 AS $$SELECT max(user_no) FROM usr$$;
1217
1218
1219 ALTER FUNCTION public.max_usr() OWNER TO postgres;
1220
1221 --
1222 -- Name: monthly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
1223 --
1224
1225 CREATE FUNCTION monthly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
1226 LANGUAGE plpgsql IMMUTABLE STRICT
1227 AS $_$
1228 DECLARE
1229 after ALIAS FOR $1;
1230 rrule ALIAS FOR $2;
1231 valid_date TIMESTAMP WITH TIME ZONE;
1232 curse REFCURSOR;
1233 setpos INT;
1234 i INT;
1235 BEGIN
1236
1237 /**
1238 * Need to investigate whether it is legal to set both of these, and whether
1239 * we are correct to UNION the results, or whether we should INTERSECT them.
1240 * So at this point, we refer to the specification, which grants us this
1241 * wonderfully enlightening vision:
1242 *
1243 * If multiple BYxxx rule parts are specified, then after evaluating the
1244 * specified FREQ and INTERVAL rule parts, the BYxxx rule parts are
1245 * applied to the current set of evaluated occurrences in the following
1246 * order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR,
1247 * BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated.
1248 *
1249 * My guess is that this means 'INTERSECT'
1250 */
1251 IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN
1252 OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r
1253 INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r
1254 ORDER BY 1;
1255 ELSIF rrule.bymonthday IS NOT NULL THEN
1256 OPEN curse SCROLL FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1;
1257 ELSE
1258 OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1;
1259 END IF;
1260
1261 RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
1262
1263 END;
1264 $_$;
1265
1266
1267 ALTER FUNCTION public.monthly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
1268
1269 --
1270 -- Name: new_db_revision(integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
1271 --
1272
1273 CREATE FUNCTION new_db_revision(integer, integer, integer, text) RETURNS void
1274 LANGUAGE plpgsql
1275 AS $_$
1276 DECLARE
1277 major ALIAS FOR $1;
1278 minor ALIAS FOR $2;
1279 patch ALIAS FOR $3;
1280 blurb ALIAS FOR $4;
1281 new_id INT;
1282 BEGIN
1283 SELECT MAX(schema_id) + 1 INTO new_id FROM awl_db_revision;
1284 IF NOT FOUND OR new_id IS NULL THEN
1285 new_id := 1;
1286 END IF;
1287 INSERT INTO awl_db_revision (schema_id, schema_major, schema_minor, schema_patch, schema_name)
1288 VALUES( new_id, major, minor, patch, blurb );
1289 RETURN;
1290 END;
1291 $_$;
1292
1293
1294 ALTER FUNCTION public.new_db_revision(integer, integer, integer, text) OWNER TO postgres;
1295
1296 --
1297 -- Name: new_sync_token(bigint, bigint); Type: FUNCTION; Schema: public; Owner: postgres
1298 --
1299
1300 CREATE FUNCTION new_sync_token(bigint, bigint) RETURNS bigint
1301 LANGUAGE plpgsql STRICT
1302 AS $_$
1303 DECLARE
1304 in_old_sync_token ALIAS FOR $1;
1305 in_collection_id ALIAS FOR $2;
1306 tmp_int INT8;
1307 new_token sync_tokens.sync_token%TYPE;
1308 old_modification_time sync_tokens.modification_time%TYPE;
1309 BEGIN
1310 IF in_old_sync_token > 0 THEN
1311 SELECT modification_time INTO old_modification_time FROM sync_tokens
1312 WHERE sync_token = in_old_sync_token AND collection_id = in_collection_id;
1313 IF NOT FOUND THEN
1314 -- They are in an inconsistent state: we return NULL so they can re-start the process
1315 RETURN NULL;
1316 END IF;
1317 END IF;
1318
1319 -- Find the most recent sync_token
1320 SELECT sync_token, modification_time INTO new_token, old_modification_time FROM sync_tokens
1321 WHERE collection_id = in_collection_id ORDER BY modification_time DESC LIMIT 1;
1322 IF FOUND THEN
1323 SELECT 1 INTO tmp_int FROM sync_changes WHERE collection_id = in_collection_id AND sync_time > old_modification_time LIMIT 1;
1324 IF NOT FOUND THEN
1325 -- Return the latest sync_token we have for this collection, since there are no changes.
1326 RETURN new_token;
1327 END IF;
1328 END IF;
1329
1330 -- Looks like we need a new sync_token for this collection...
1331 SELECT nextval('sync_tokens_sync_token_seq') INTO new_token;
1332 INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, new_token );
1333
1334 -- Having created our new token we do some clean-up of old tokens
1335 SELECT modification_time, sync_token INTO old_modification_time, tmp_int FROM sync_tokens
1336 WHERE collection_id = in_collection_id AND modification_time < (current_timestamp - '7 days'::interval)
1337 ORDER BY collection_id, modification_time DESC;
1338 DELETE FROM sync_changes WHERE collection_id = in_collection_id AND sync_time < old_modification_time;
1339 DELETE FROM sync_tokens WHERE collection_id = in_collection_id AND sync_token < tmp_int;
1340
1341 -- Returning the new token
1342 RETURN new_token;
1343 END
1344 $_$;
1345
1346
1347 ALTER FUNCTION public.new_sync_token(bigint, bigint) OWNER TO postgres;
1348
1349 --
1350 -- Name: p_has_proxy_access_to(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1351 --
1352
1353 CREATE FUNCTION p_has_proxy_access_to(bigint, integer) RETURNS SETOF bigint
1354 LANGUAGE sql STABLE STRICT
1355 AS $_$
1356 SELECT by_principal
1357 FROM (
1358 SELECT by_principal FROM grants
1359 WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2))
1360 AND (privileges & 5::BIT(24)) != 0::BIT(24)
1361 AND by_collection IS NULL
1362 AND by_principal != $1
1363 UNION
1364 SELECT principal_id AS by_principal FROM principal
1365 WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24)
1366 AND principal_id != $1
1367 ) subquery;
1368 $_$;
1369
1370
1371 ALTER FUNCTION public.p_has_proxy_access_to(bigint, integer) OWNER TO postgres;
1372
1373 --
1374 -- Name: parse_rrule_parts(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1375 --
1376
1377 CREATE FUNCTION parse_rrule_parts(timestamp with time zone, text) RETURNS rrule_parts
1378 LANGUAGE plpgsql IMMUTABLE STRICT
1379 AS $_$
1380 DECLARE
1381 basedate ALIAS FOR $1;
1382 repeatrule ALIAS FOR $2;
1383 result rrule_parts%ROWTYPE;
1384 tempstr TEXT;
1385 BEGIN
1386 result.base := basedate;
1387 result.until := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
1388 result.freq := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
1389 result.count := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
1390 result.interval := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1);
1391 result.wkst := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)');
1392
1393 result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ',');
1394
1395 result.byyearday := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ',');
1396 result.byweekno := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ',');
1397 result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ',');
1398 result.bymonth := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ',');
1399 result.bysetpos := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ',');
1400
1401 result.bysecond := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ',');
1402 result.byminute := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ',');
1403 result.byhour := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ',');
1404
1405 RETURN result;
1406 END;
1407 $_$;
1408
1409
1410 ALTER FUNCTION public.parse_rrule_parts(timestamp with time zone, text) OWNER TO postgres;
1411
1412 --
1413 -- Name: path_privs(bigint, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
1414 --
1415
1416 CREATE FUNCTION path_privs(bigint, text, integer) RETURNS bit
1417 LANGUAGE plpgsql STABLE STRICT
1418 AS $_$
1419 DECLARE
1420 in_accessor ALIAS FOR $1;
1421 in_path ALIAS FOR $2;
1422 in_depth ALIAS FOR $3;
1423
1424 alt1_path TEXT;
1425 alt2_path TEXT;
1426 grantor_collection INT8;
1427 grantor_principal INT8;
1428 collection_path TEXT;
1429 collection_privileges BIT(24);
1430 out_conferred BIT(24);
1431 BEGIN
1432 out_conferred := 0::BIT(24);
1433
1434 IF in_path ~ '^/?$' THEN
1435 -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
1436 RETURN 1; -- basic read privileges on root directory
1437 END IF;
1438
1439 -- We need to canonicalise the path, so:
1440 -- If it matches '/' + some characters (+ optional '/') => a principal URL
1441 IF in_path ~ '^/[^/]+/?$' THEN
1442 alt1_path := replace(in_path, '/', '');
1443 SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
1444 -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1445 RETURN out_conferred;
1446 END IF;
1447
1448 -- 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.
1449 alt1_path := in_path;
1450 IF alt1_path ~ E'\\.ics$' THEN
1451 alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
1452 END IF;
1453 alt2_path := regexp_replace( in_path, '[^/]*$', '');
1454 SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges
1455 INTO grantor_collection, grantor_principal, collection_path, collection_privileges
1456 FROM collection JOIN principal grantor USING (user_no)
1457 WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
1458 ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;
1459
1460 -- Self will always need full access to their own collections!
1461 IF grantor_principal = in_accessor THEN
1462 -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path;
1463 RETURN ~ out_conferred;
1464 END IF;
1465
1466 SELECT bit_or(privileges) INTO out_conferred FROM grants
1467 WHERE by_collection = grantor_collection
1468 AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)));
1469
1470 IF out_conferred IS NULL THEN
1471 IF collection_privileges IS NULL THEN
1472 IF grantor_principal IS NULL THEN
1473 alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/');
1474 SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred;
1475 -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1476 ELSE
1477 SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred;
1478 -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1479 END IF;
1480 ELSE
1481 out_conferred := collection_privileges;
1482 -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1483 END IF;
1484 END IF;
1485
1486 RETURN out_conferred;
1487 END;
1488 $_$;
1489
1490
1491 ALTER FUNCTION public.path_privs(bigint, text, integer) OWNER TO postgres;
1492
1493 --
1494 -- Name: pprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1495 --
1496
1497 CREATE FUNCTION pprivs(bigint, bigint, integer) RETURNS bit
1498 LANGUAGE plpgsql STABLE STRICT
1499 AS $_$
1500 DECLARE
1501 in_accessor ALIAS FOR $1;
1502 in_grantor ALIAS FOR $2;
1503 in_depth ALIAS FOR $3;
1504 out_conferred BIT(24);
1505 BEGIN
1506 out_conferred := 0::BIT(24);
1507 -- Self can always have full access
1508 IF in_grantor = in_accessor THEN
1509 RETURN ~ out_conferred;
1510 END IF;
1511
1512 SELECT bit_or(subquery.privileges) INTO out_conferred FROM
1513 (
1514 SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL
1515 AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)))
1516 UNION
1517 SELECT bit_or(sq2.privileges) FROM
1518 (
1519 SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor
1520 UNION
1521 SELECT default_privileges AS privileges FROM principal WHERE principal_id = in_grantor
1522 ) AS sq2
1523 ) AS subquery ;
1524
1525 IF out_conferred IS NULL THEN
1526 SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
1527 END IF;
1528
1529 RETURN out_conferred;
1530 END;
1531 $_$;
1532
1533
1534 ALTER FUNCTION public.pprivs(bigint, bigint, integer) OWNER TO postgres;
1535
1536 --
1537 -- Name: principal_modified(); Type: FUNCTION; Schema: public; Owner: postgres
1538 --
1539
1540 CREATE FUNCTION principal_modified() RETURNS trigger
1541 LANGUAGE plpgsql
1542 AS $$
1543 DECLARE
1544 BEGIN
1545 -- in case we trigger on other events in future
1546 IF TG_OP = 'UPDATE' THEN
1547 IF NEW.type_id != OLD.type_id THEN
1548 UPDATE grants
1549 SET is_group = (NEW.type_id = 3)
1550 WHERE grants.to_principal = NEW.principal_id;
1551 END IF;
1552 END IF;
1553 RETURN NEW;
1554 END;
1555 $$;
1556
1557
1558 ALTER FUNCTION public.principal_modified() OWNER TO postgres;
1559
1560 --
1561 -- Name: privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1562 --
1563
1564 CREATE FUNCTION privilege_to_bits(text) RETURNS bit
1565 LANGUAGE plpgsql IMMUTABLE STRICT
1566 AS $_$
1567 DECLARE
1568 raw_priv ALIAS FOR $1;
1569 in_priv TEXT;
1570 BEGIN
1571 in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
1572 IF in_priv = 'all' THEN
1573 RETURN ~ 0::BIT(24);
1574 END IF;
1575
1576 RETURN (CASE
1577 WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096
1578 WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128
1579 WHEN in_priv = 'write-properties' THEN 2
1580 WHEN in_priv = 'write-content' THEN 4
1581 WHEN in_priv = 'unlock' THEN 8
1582 WHEN in_priv = 'read-acl' THEN 16
1583 WHEN in_priv = 'read-current-user-privilege-set' THEN 32
1584 WHEN in_priv = 'bind' THEN 64
1585 WHEN in_priv = 'unbind' THEN 128
1586 WHEN in_priv = 'write-acl' THEN 256
1587 WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096
1588 WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096
1589 WHEN in_priv = 'schedule-deliver-invite' THEN 1024
1590 WHEN in_priv = 'schedule-deliver-reply' THEN 2048
1591 WHEN in_priv = 'schedule-query-freebusy' THEN 4096
1592 WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768
1593 WHEN in_priv = 'schedule-send-invite' THEN 8192
1594 WHEN in_priv = 'schedule-send-reply' THEN 16384
1595 WHEN in_priv = 'schedule-send-freebusy' THEN 32768
1596 ELSE 0 END)::BIT(24);
1597 END
1598 $_$;
1599
1600
1601 ALTER FUNCTION public.privilege_to_bits(text) OWNER TO postgres;
1602
1603 --
1604 -- Name: privilege_to_bits(text[]); Type: FUNCTION; Schema: public; Owner: postgres
1605 --
1606
1607 CREATE FUNCTION privilege_to_bits(text[]) RETURNS bit
1608 LANGUAGE plpgsql IMMUTABLE STRICT
1609 AS $_$
1610 DECLARE
1611 raw_privs ALIAS FOR $1;
1612 in_priv TEXT;
1613 out_bits BIT(24);
1614 i INT;
1615 all_privs BIT(24);
1616 start INT;
1617 finish INT;
1618 BEGIN
1619 out_bits := 0::BIT(24);
1620 all_privs := ~ out_bits;
1621 SELECT array_lower(raw_privs,1) INTO start;
1622 SELECT array_upper(raw_privs,1) INTO finish;
1623 FOR i IN start .. finish LOOP
1624 SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
1625 IF out_bits = 65535::BIT(24) THEN
1626 RETURN all_privs;
1627 END IF;
1628 END LOOP;
1629 RETURN out_bits;
1630 END
1631 $_$;
1632
1633
1634 ALTER FUNCTION public.privilege_to_bits(text[]) OWNER TO postgres;
1635
1636 --
1637 -- Name: privileges_list(bit); Type: FUNCTION; Schema: public; Owner: postgres
1638 --
1639
1640 CREATE FUNCTION privileges_list(bit) RETURNS text
1641 LANGUAGE plpgsql IMMUTABLE STRICT
1642 AS $_$
1643 DECLARE
1644 in_privileges ALIAS FOR $1;
1645 privileges TEXT[];
1646 plist TEXT;
1647 start INT;
1648 finish INT;
1649 i INT;
1650 BEGIN
1651 plist := '';
1652
1653 privileges := bits_to_privilege(in_privileges);
1654 SELECT array_lower(privileges,1) INTO start;
1655 IF start IS NOT NULL THEN
1656 SELECT array_upper(privileges,1) INTO finish;
1657 FOR i IN start .. finish LOOP
1658 plist := plist
1659 || CASE WHEN plist = '' THEN '' ELSE ', ' END
1660 || privileges[i];
1661 END LOOP;
1662 END IF;
1663 RETURN plist;
1664 END;
1665 $_$;
1666
1667
1668 ALTER FUNCTION public.privileges_list(bit) OWNER TO postgres;
1669
1670 --
1671 -- Name: real_path_exists(text); Type: FUNCTION; Schema: public; Owner: postgres
1672 --
1673
1674 CREATE FUNCTION real_path_exists(text) RETURNS boolean
1675 LANGUAGE plpgsql
1676 AS $_$
1677 DECLARE
1678 in_path ALIAS FOR $1;
1679 tmp BOOLEAN;
1680 BEGIN
1681 IF in_path = '/' THEN
1682 RETURN TRUE;
1683 END IF;
1684 IF in_path ~ '^/[^/]+/$' THEN
1685 SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
1686 IF FOUND THEN
1687 RETURN TRUE;
1688 END IF;
1689 ELSE
1690 IF in_path ~ '^/.*/$' THEN
1691 SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
1692 IF FOUND THEN
1693 RETURN TRUE;
1694 END IF;
1695 END IF;
1696 END IF;
1697 RETURN FALSE;
1698 END;
1699 $_$;
1700
1701
1702 ALTER FUNCTION public.real_path_exists(text) OWNER TO postgres;
1703
1704 --
1705 -- Name: relationship_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1706 --
1707
1708 CREATE FUNCTION relationship_list(bigint) RETURNS text
1709 LANGUAGE plpgsql
1710 AS $_$
1711 DECLARE
1712 user ALIAS FOR $1;
1713 r RECORD;
1714 rlist TEXT;
1715 BEGIN
1716 rlist := '';
1717 FOR r IN SELECT rt_name, fullname FROM relationship
1718 LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no
1719 WHERE from_user = user
1720 LOOP
1721 rlist := rlist
1722 || CASE WHEN rlist = '' THEN '' ELSE ', ' END
1723 || r.rt_name || '(' || r.fullname || ')';
1724 END LOOP;
1725 RETURN rlist;
1726 END;
1727 $_$;
1728
1729
1730 ALTER FUNCTION public.relationship_list(bigint) OWNER TO postgres;
1731
1732 --
1733 -- Name: rrule_bysetpos_filter(refcursor, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
1734 --
1735
1736 CREATE FUNCTION rrule_bysetpos_filter(refcursor, integer[]) RETURNS SETOF timestamp with time zone
1737 LANGUAGE plpgsql IMMUTABLE
1738 AS $_$
1739 DECLARE
1740 curse ALIAS FOR $1;
1741 bysetpos ALIAS FOR $2;
1742 valid_date TIMESTAMP WITH TIME ZONE;
1743 i INT;
1744 BEGIN
1745
1746 IF bysetpos IS NULL THEN
1747 LOOP
1748 FETCH curse INTO valid_date;
1749 EXIT WHEN NOT FOUND;
1750 RETURN NEXT valid_date;
1751 END LOOP;
1752 ELSE
1753 FOR i IN 1..366 LOOP
1754 EXIT WHEN bysetpos[i] IS NULL;
1755 IF bysetpos[i] > 0 THEN
1756 FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date;
1757 ELSE
1758 MOVE LAST IN curse;
1759 FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date;
1760 END IF;
1761 IF valid_date IS NOT NULL THEN
1762 RETURN NEXT valid_date;
1763 END IF;
1764 END LOOP;
1765 END IF;
1766 CLOSE curse;
1767 END;
1768 $_$;
1769
1770
1771 ALTER FUNCTION public.rrule_bysetpos_filter(refcursor, integer[]) OWNER TO postgres;
1772
1773 --
1774 -- Name: rrule_event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1775 --
1776
1777 CREATE FUNCTION rrule_event_instances(timestamp with time zone, text) RETURNS SETOF rrule_instance
1778 LANGUAGE plpgsql IMMUTABLE STRICT
1779 AS $_$
1780 DECLARE
1781 basedate ALIAS FOR $1;
1782 repeatrule ALIAS FOR $2;
1783 maxdate TIMESTAMP WITH TIME ZONE;
1784 current TIMESTAMP WITH TIME ZONE;
1785 result rrule_instance%ROWTYPE;
1786 BEGIN
1787 maxdate := current_date + '10 years'::interval;
1788
1789 result.dtstart := basedate;
1790 result.rrule := repeatrule;
1791
1792 FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP
1793 result.instance := current;
1794 RETURN NEXT result;
1795 END LOOP;
1796
1797 END;
1798 $_$;
1799
1800
1801 ALTER FUNCTION public.rrule_event_instances(timestamp with time zone, text) OWNER TO postgres;
1802
1803 --
1804 -- Name: rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: public; Owner: postgres
1805 --
1806
1807 CREATE FUNCTION rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) RETURNS SETOF timestamp with time zone
1808 LANGUAGE plpgsql IMMUTABLE STRICT
1809 AS $_$
1810 DECLARE
1811 basedate ALIAS FOR $1;
1812 repeatrule ALIAS FOR $2;
1813 mindate ALIAS FOR $3;
1814 maxdate ALIAS FOR $4;
1815 max_count ALIAS FOR $5;
1816 loopmax INT;
1817 loopcount INT;
1818 base_day TIMESTAMP WITH TIME ZONE;
1819 current_base TIMESTAMP WITH TIME ZONE;
1820 current TIMESTAMP WITH TIME ZONE;
1821 rrule rrule_parts%ROWTYPE;
1822 BEGIN
1823 loopcount := 0;
1824
1825 SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule );
1826
1827 IF rrule.count IS NOT NULL THEN
1828 loopmax := rrule.count;
1829 ELSE
1830 -- max_count is pretty arbitrary, so we scale it somewhat here depending on the frequency.
1831 IF rrule.freq = 'DAILY' THEN
1832 loopmax := max_count * 20;
1833 ELSIF rrule.freq = 'WEEKLY' THEN
1834 loopmax := max_count * 10;
1835 ELSE
1836 loopmax := max_count;
1837 END IF;
1838 END IF;
1839
1840 current_base := basedate;
1841 base_day := date_trunc('day',basedate);
1842 WHILE loopcount < loopmax AND current_base <= maxdate LOOP
1843 IF rrule.freq = 'DAILY' THEN
1844 FOR current IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP
1845 -- IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN
1846 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1847 IF current >= mindate THEN
1848 RETURN NEXT current;
1849 END IF;
1850 loopcount := loopcount + 1;
1851 EXIT WHEN loopcount >= loopmax;
1852 -- END IF;
1853 END LOOP;
1854 current_base := current_base + (rrule.interval::text || ' days')::interval;
1855 ELSIF rrule.freq = 'WEEKLY' THEN
1856 FOR current IN SELECT w FROM weekly_set(current_base,rrule) w WHERE w >= base_day LOOP
1857 IF test_byyearday_rule(current,rrule.byyearday)
1858 AND test_bymonthday_rule(current,rrule.bymonthday)
1859 AND test_bymonth_rule(current,rrule.bymonth)
1860 THEN
1861 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1862 IF current >= mindate THEN
1863 RETURN NEXT current;
1864 END IF;
1865 loopcount := loopcount + 1;
1866 EXIT WHEN loopcount >= loopmax;
1867 END IF;
1868 END LOOP;
1869 current_base := current_base + (rrule.interval::text || ' weeks')::interval;
1870 ELSIF rrule.freq = 'MONTHLY' THEN
1871 FOR current IN SELECT m FROM monthly_set(current_base,rrule) m WHERE m >= base_day LOOP
1872 -- IF /* test_byyearday_rule(current,rrule.byyearday)
1873 -- AND */ test_bymonth_rule(current,rrule.bymonth)
1874 -- THEN
1875 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1876 IF current >= mindate THEN
1877 RETURN NEXT current;
1878 END IF;
1879 loopcount := loopcount + 1;
1880 EXIT WHEN loopcount >= loopmax;
1881 -- END IF;
1882 END LOOP;
1883 current_base := current_base + (rrule.interval::text || ' months')::interval;
1884 ELSIF rrule.freq = 'YEARLY' THEN
1885 FOR current IN SELECT y FROM yearly_set(current_base,rrule) y WHERE y >= base_day LOOP
1886 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1887 IF current >= mindate THEN
1888 RETURN NEXT current;
1889 END IF;
1890 loopcount := loopcount + 1;
1891 EXIT WHEN loopcount >= loopmax;
1892 END LOOP;
1893 current_base := current_base + (rrule.interval::text || ' years')::interval;
1894 ELSE
1895 RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq;
1896 RETURN;
1897 END IF;
1898 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1899 END LOOP;
1900 -- RETURN QUERY;
1901 END;
1902 $_$;
1903
1904
1905 ALTER FUNCTION public.rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO postgres;
1906
1907 --
1908 -- Name: rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
1909 --
1910
1911 CREATE FUNCTION rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) RETURNS boolean
1912 LANGUAGE plpgsql IMMUTABLE
1913 AS $_$
1914 DECLARE
1915 dtstart ALIAS FOR $1;
1916 dtend ALIAS FOR $2;
1917 repeatrule ALIAS FOR $3;
1918 in_mindate ALIAS FOR $4;
1919 in_maxdate ALIAS FOR $5;
1920 base_date TIMESTAMP WITH TIME ZONE;
1921 mindate TIMESTAMP WITH TIME ZONE;
1922 maxdate TIMESTAMP WITH TIME ZONE;
1923 BEGIN
1924
1925 IF dtstart IS NULL THEN
1926 RETURN NULL;
1927 END IF;
1928 IF dtend IS NULL THEN
1929 base_date := dtstart;
1930 ELSE
1931 base_date := dtend;
1932 END IF;
1933
1934 IF in_mindate IS NULL THEN
1935 mindate := current_date - '10 years'::interval;
1936 ELSE
1937 mindate := in_mindate;
1938 END IF;
1939
1940 IF in_maxdate IS NULL THEN
1941 maxdate := current_date + '10 years'::interval;
1942 ELSE
1943 -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range.
1944 maxdate := in_maxdate + (base_date - dtstart);
1945 END IF;
1946
1947 IF repeatrule IS NULL THEN
1948 RETURN (dtstart <= maxdate AND base_date >= mindate);
1949 END IF;
1950
1951 SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1;
1952 RETURN FOUND;
1953
1954 END;
1955 $_$;
1956
1957
1958 ALTER FUNCTION public.rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) OWNER TO postgres;
1959
1960 --
1961 -- Name: rrule_month_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
1962 --
1963
1964 CREATE FUNCTION rrule_month_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
1965 LANGUAGE plpgsql IMMUTABLE
1966 AS $_$
1967 DECLARE
1968 in_time ALIAS FOR $1;
1969 byday ALIAS FOR $2;
1970 dayrule TEXT;
1971 i INT;
1972 dow INT;
1973 index INT;
1974 first_dow INT;
1975 each_day TIMESTAMP WITH TIME ZONE;
1976 this_month INT;
1977 results TIMESTAMP WITH TIME ZONE[];
1978 BEGIN
1979
1980 IF byday IS NULL THEN
1981 -- We still return the single date as a SET
1982 RETURN NEXT in_time;
1983 RETURN;
1984 END IF;
1985
1986 i := 1;
1987 dayrule := byday[i];
1988 WHILE dayrule IS NOT NULL LOOP
1989 dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2;
1990 each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval;
1991 this_month := date_part( 'month', in_time );
1992 first_dow := date_part( 'dow', each_day );
1993
1994 -- Coerce each_day to be the first 'dow' of the month
1995 each_day := each_day - ( first_dow::text || 'days')::interval
1996 + ( dow::text || 'days')::interval
1997 + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END;
1998
1999 -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow;
2000 IF length(dayrule) > 2 THEN
2001 index := (substring(dayrule from '^[0-9-]+'))::int;
2002
2003 IF index = 0 THEN
2004 RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule;
2005 ELSIF index > 0 THEN
2006 -- The simplest case, such as 2MO for the second monday
2007 each_day := each_day + ((index - 1)::text || ' weeks')::interval;
2008 ELSE
2009 each_day := each_day + '5 weeks'::interval;
2010 WHILE date_part('month', each_day) != this_month LOOP
2011 each_day := each_day - '1 week'::interval;
2012 END LOOP;
2013 -- Note that since index is negative, (-2 + 1) == -1, for example
2014 index := index + 1;
2015 IF index < 0 THEN
2016 each_day := each_day + (index::text || ' weeks')::interval ;
2017 END IF;
2018 END IF;
2019
2020 -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months
2021 IF date_part('month', each_day) = this_month THEN
2022 results[date_part('day',each_day)] := each_day;
2023 -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
2024 END IF;
2025
2026 ELSE
2027 -- Return all such days that are within the given month
2028 WHILE date_part('month', each_day) = this_month LOOP
2029 results[date_part('day',each_day)] := each_day;
2030 each_day := each_day + '1 week'::interval;
2031 -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
2032 END LOOP;
2033 END IF;
2034
2035 i := i + 1;
2036 dayrule := byday[i];
2037 END LOOP;
2038
2039 FOR i IN 1..31 LOOP
2040 IF results[i] IS NOT NULL THEN
2041 RETURN NEXT results[i];
2042 END IF;
2043 END LOOP;
2044
2045 RETURN;
2046
2047 END;
2048 $_$;
2049
2050
2051 ALTER FUNCTION public.rrule_month_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
2052
2053 --
2054 -- Name: rrule_month_bymonthday_set(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2055 --
2056
2057 CREATE FUNCTION rrule_month_bymonthday_set(timestamp with time zone, integer[]) RETURNS SETOF timestamp with time zone
2058 LANGUAGE plpgsql IMMUTABLE STRICT
2059 AS $_$
2060 DECLARE
2061 in_time ALIAS FOR $1;
2062 bymonthday ALIAS FOR $2;
2063 month_start TIMESTAMP WITH TIME ZONE;
2064 daysinmonth INT;
2065 i INT;
2066 BEGIN
2067
2068 month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval;
2069 daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' );
2070
2071 FOR i IN 1..31 LOOP
2072 EXIT WHEN bymonthday[i] IS NULL;
2073
2074 CONTINUE WHEN bymonthday[i] > daysinmonth;
2075 CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth);
2076
2077 IF bymonthday[i] > 0 THEN
2078 RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval;
2079 ELSIF bymonthday[i] < 0 THEN
2080 RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval;
2081 ELSE
2082 RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i];
2083 END IF;
2084 END LOOP;
2085
2086 RETURN;
2087
2088 END;
2089 $_$;
2090
2091
2092 ALTER FUNCTION public.rrule_month_bymonthday_set(timestamp with time zone, integer[]) OWNER TO postgres;
2093
2094 --
2095 -- Name: rrule_week_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2096 --
2097
2098 CREATE FUNCTION rrule_week_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
2099 LANGUAGE plpgsql IMMUTABLE
2100 AS $_$
2101 DECLARE
2102 in_time ALIAS FOR $1;
2103 byday ALIAS FOR $2;
2104 dayrule TEXT;
2105 dow INT;
2106 our_day TIMESTAMP WITH TIME ZONE;
2107 i INT;
2108 BEGIN
2109
2110 IF byday IS NULL THEN
2111 -- We still return the single date as a SET
2112 RETURN NEXT in_time;
2113 RETURN;
2114 END IF;
2115
2116 our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval;
2117
2118 i := 1;
2119 dayrule := byday[i];
2120 WHILE dayrule IS NOT NULL LOOP
2121 dow := position(dayrule in 'SUMOTUWETHFRSA') / 2;
2122 RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval;
2123 i := i + 1;
2124 dayrule := byday[i];
2125 END LOOP;
2126
2127 RETURN;
2128
2129 END;
2130 $_$;
2131
2132
2133 ALTER FUNCTION public.rrule_week_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
2134
2135 --
2136 -- Name: rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2137 --
2138
2139 CREATE FUNCTION rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2140 LANGUAGE plpgsql IMMUTABLE STRICT
2141 AS $_$
2142 DECLARE
2143 after ALIAS FOR $1;
2144 rrule ALIAS FOR $2;
2145 current_base TIMESTAMP WITH TIME ZONE;
2146 rr rrule_parts;
2147 i INT;
2148 BEGIN
2149
2150 IF rrule.bymonth IS NOT NULL THEN
2151 -- Ensure we don't pass BYSETPOS down
2152 rr := rrule;
2153 rr.bysetpos := NULL;
2154 FOR i IN 1..12 LOOP
2155 EXIT WHEN rr.bymonth[i] IS NULL;
2156 current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval;
2157 RETURN QUERY SELECT r FROM monthly_set(current_base,rr) r;
2158 END LOOP;
2159 ELSE
2160 -- We don't yet implement byweekno, byblah
2161 RETURN NEXT after;
2162 END IF;
2163
2164 END;
2165 $_$;
2166
2167
2168 ALTER FUNCTION public.rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2169
2170 --
2171 -- Name: set_dav_property(text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres
2172 --
2173
2174 CREATE FUNCTION set_dav_property(text, integer, text, text) RETURNS boolean
2175 LANGUAGE plpgsql STRICT
2176 AS $_$
2177 DECLARE
2178 path ALIAS FOR $1;
2179 change_user ALIAS FOR $2;
2180 key ALIAS FOR $3;
2181 value ALIAS FOR $4;
2182 BEGIN
2183 -- Check that there is either a resource, collection or user at this location.
2184 IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path
2185 UNION SELECT 1 FROM collection WHERE dav_name = path
2186 UNION SELECT 1 FROM dav_principal WHERE dav_name = path
2187 UNION SELECT 1 FROM dav_binding WHERE dav_name = path
2188 ) THEN
2189 RETURN FALSE;
2190 END IF;
2191 PERFORM true FROM property WHERE dav_name = path AND property_name = key;
2192 IF FOUND THEN
2193 UPDATE property SET changed_by=change_user::integer, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key;
2194 ELSE
2195 INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, change_user::integer, current_timestamp, key, value );
2196 END IF;
2197 RETURN TRUE;
2198 END;
2199 $_$;
2200
2201
2202 ALTER FUNCTION public.set_dav_property(text, integer, text, text) OWNER TO postgres;
2203
2204 --
2205 -- Name: sync_dav_id(); Type: FUNCTION; Schema: public; Owner: postgres
2206 --
2207
2208 CREATE FUNCTION sync_dav_id() RETURNS trigger
2209 LANGUAGE plpgsql
2210 AS $$
2211 DECLARE
2212 BEGIN
2213
2214 IF TG_OP = 'DELETE' THEN
2215 -- Just let the ON DELETE CASCADE handle this case
2216 RETURN OLD;
2217 END IF;
2218
2219 IF NEW.dav_id IS NULL THEN
2220 NEW.dav_id = nextval('dav_id_seq');
2221 END IF;
2222
2223 IF TG_OP = 'UPDATE' THEN
2224 IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
2225 OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
2226 UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
2227 collection_id = NEW.collection_id, dav_name = NEW.dav_name
2228 WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
2229 END IF;
2230 RETURN NEW;
2231 END IF;
2232
2233 UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
2234 collection_id = NEW.collection_id, dav_name = NEW.dav_name
2235 WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
2236
2237 RETURN NEW;
2238
2239 END
2240 $$;
2241
2242
2243 ALTER FUNCTION public.sync_dav_id() OWNER TO postgres;
2244
2245 --
2246 -- Name: test_byday_rule(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2247 --
2248
2249 CREATE FUNCTION test_byday_rule(timestamp with time zone, text[]) RETURNS boolean
2250 LANGUAGE plpgsql IMMUTABLE
2251 AS $_$
2252 DECLARE
2253 testme ALIAS FOR $1;
2254 byday ALIAS FOR $2;
2255 BEGIN
2256 -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended
2257 -- so don't call it that way...
2258 IF byday IS NOT NULL THEN
2259 RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) );
2260 END IF;
2261 RETURN TRUE;
2262 END;
2263 $_$;
2264
2265
2266 ALTER FUNCTION public.test_byday_rule(timestamp with time zone, text[]) OWNER TO postgres;
2267
2268 --
2269 -- Name: test_bymonth_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2270 --
2271
2272 CREATE FUNCTION test_bymonth_rule(timestamp with time zone, integer[]) RETURNS boolean
2273 LANGUAGE plpgsql IMMUTABLE
2274 AS $_$
2275 DECLARE
2276 testme ALIAS FOR $1;
2277 bymonth ALIAS FOR $2;
2278 BEGIN
2279 IF bymonth IS NOT NULL THEN
2280 RETURN ( date_part( 'month', testme) = ANY (bymonth) );
2281 END IF;
2282 RETURN TRUE;
2283 END;
2284 $_$;
2285
2286
2287 ALTER FUNCTION public.test_bymonth_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2288
2289 --
2290 -- Name: test_bymonthday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2291 --
2292
2293 CREATE FUNCTION test_bymonthday_rule(timestamp with time zone, integer[]) RETURNS boolean
2294 LANGUAGE plpgsql IMMUTABLE
2295 AS $_$
2296 DECLARE
2297 testme ALIAS FOR $1;
2298 bymonthday ALIAS FOR $2;
2299 BEGIN
2300 IF bymonthday IS NOT NULL THEN
2301 RETURN ( date_part( 'day', testme) = ANY (bymonthday) );
2302 END IF;
2303 RETURN TRUE;
2304 END;
2305 $_$;
2306
2307
2308 ALTER FUNCTION public.test_bymonthday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2309
2310 --
2311 -- Name: test_byyearday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2312 --
2313
2314 CREATE FUNCTION test_byyearday_rule(timestamp with time zone, integer[]) RETURNS boolean
2315 LANGUAGE plpgsql IMMUTABLE
2316 AS $_$
2317 DECLARE
2318 testme ALIAS FOR $1;
2319 byyearday ALIAS FOR $2;
2320 BEGIN
2321 IF byyearday IS NOT NULL THEN
2322 RETURN ( date_part( 'doy', testme) = ANY (byyearday) );
2323 END IF;
2324 RETURN TRUE;
2325 END;
2326 $_$;
2327
2328
2329 ALTER FUNCTION public.test_byyearday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2330
2331 --
2332 -- Name: to_ical_utc(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
2333 --
2334
2335 CREATE FUNCTION to_ical_utc(timestamp with time zone) RETURNS text
2336 LANGUAGE sql IMMUTABLE STRICT
2337 AS $_$
2338 SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' )
2339 $_$;
2340
2341
2342 ALTER FUNCTION public.to_ical_utc(timestamp with time zone) OWNER TO postgres;
2343
2344 --
2345 -- Name: uprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
2346 --
2347
2348 CREATE FUNCTION uprivs(bigint, bigint, integer) RETURNS bit
2349 LANGUAGE plpgsql STABLE STRICT
2350 AS $_$
2351 DECLARE
2352 in_accessor ALIAS FOR $1;
2353 in_grantor ALIAS FOR $2;
2354 in_depth ALIAS FOR $3;
2355 out_conferred BIT(24);
2356 BEGIN
2357 out_conferred := 0::BIT(24);
2358 -- Self can always have full access
2359 IF in_grantor = in_accessor THEN
2360 RETURN ~ out_conferred;
2361 END IF;
2362
2363 SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred
2364 FROM principal p1, principal p2
2365 WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor;
2366
2367 RETURN out_conferred;
2368 END;
2369 $_$;
2370
2371
2372 ALTER FUNCTION public.uprivs(bigint, bigint, integer) OWNER TO postgres;
2373
2374 --
2375 -- Name: usr_is_role(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2376 --
2377
2378 CREATE FUNCTION usr_is_role(integer, text) RETURNS boolean
2379 LANGUAGE sql IMMUTABLE STRICT
2380 AS $_$
2381 SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 )
2382 $_$;
2383
2384
2385 ALTER FUNCTION public.usr_is_role(integer, text) OWNER TO postgres;
2386
2387 --
2388 -- Name: usr_modified(); Type: FUNCTION; Schema: public; Owner: postgres
2389 --
2390
2391 CREATE FUNCTION usr_modified() RETURNS trigger
2392 LANGUAGE plpgsql
2393 AS $$
2394 DECLARE
2395 oldpath TEXT;
2396 newpath TEXT;
2397 BEGIN
2398 -- in case we trigger on other events in future
2399 IF TG_OP = 'UPDATE' THEN
2400 IF NEW.username != OLD.username THEN
2401 oldpath := '/' || OLD.username || '/';
2402 newpath := '/' || NEW.username || '/';
2403 UPDATE collection
2404 SET parent_container = replace( parent_container, oldpath, newpath),
2405 dav_name = replace( dav_name, oldpath, newpath)
2406 WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath;
2407 END IF;
2408 END IF;
2409 RETURN NEW;
2410 END;
2411 $$;
2412
2413
2414 ALTER FUNCTION public.usr_modified() OWNER TO postgres;
2415
2416 --
2417 -- Name: weekly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2418 --
2419
2420 CREATE FUNCTION weekly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2421 LANGUAGE plpgsql IMMUTABLE STRICT
2422 AS $_$
2423 DECLARE
2424 after ALIAS FOR $1;
2425 rrule ALIAS FOR $2;
2426 valid_date TIMESTAMP WITH TIME ZONE;
2427 curse REFCURSOR;
2428 weekno INT;
2429 i INT;
2430 BEGIN
2431
2432 IF rrule.byweekno IS NOT NULL THEN
2433 weekno := date_part('week',after);
2434 IF NOT weekno = ANY ( rrule.byweekno ) THEN
2435 RETURN;
2436 END IF;
2437 END IF;
2438
2439 OPEN curse SCROLL FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r;
2440 RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
2441
2442 END;
2443 $_$;
2444
2445
2446 ALTER FUNCTION public.weekly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2447
2448 --
2449 -- Name: write_sync_change(bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2450 --
2451
2452 CREATE FUNCTION write_sync_change(bigint, integer, text) RETURNS boolean
2453 LANGUAGE plpgsql STRICT
2454 AS $_$
2455 DECLARE
2456 in_collection_id ALIAS FOR $1;
2457 in_status ALIAS FOR $2;
2458 in_dav_name ALIAS FOR $3;
2459 tmp_int INT8;
2460 BEGIN
2461 SELECT 1 INTO tmp_int FROM sync_tokens
2462 WHERE collection_id = in_collection_id
2463 LIMIT 1;
2464 IF NOT FOUND THEN
2465 RETURN FALSE;
2466 END IF;
2467 SELECT dav_id INTO tmp_int FROM caldav_data WHERE dav_name = in_dav_name;
2468 INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name)
2469 VALUES( in_collection_id, in_status, tmp_int, in_dav_name);
2470 RETURN TRUE;
2471 END
2472 $_$;
2473
2474
2475 ALTER FUNCTION public.write_sync_change(bigint, integer, text) OWNER TO postgres;
2476
2477 --
2478 -- Name: yearly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2479 --
2480
2481 CREATE FUNCTION yearly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2482 LANGUAGE plpgsql IMMUTABLE STRICT
2483 AS $_$
2484 DECLARE
2485 after ALIAS FOR $1;
2486 rrule ALIAS FOR $2;
2487 current_base TIMESTAMP WITH TIME ZONE;
2488 curse REFCURSOR;
2489 curser REFCURSOR;
2490 i INT;
2491 BEGIN
2492
2493 IF rrule.bymonth IS NOT NULL THEN
2494 OPEN curse SCROLL FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r;
2495 FOR current_base IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP
2496 current_base := date_trunc( 'day', current_base ) + (after::time)::interval;
2497 RETURN NEXT current_base;
2498 END LOOP;
2499 ELSE
2500 -- We don't yet implement byweekno, byblah
2501 RETURN NEXT after;
2502 END IF;
2503 END;
2504 $_$;
2505
2506
2507 ALTER FUNCTION public.yearly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2508
2509 SET default_tablespace = '';
2510
2511 SET default_with_oids = false;
2512
2513 --
2514 -- Name: access_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2515 --
2516
2517 CREATE TABLE access_ticket (
2518 ticket_id text NOT NULL,
2519 dav_owner_id bigint NOT NULL,
2520 privileges bit(24),
2521 target_collection_id bigint NOT NULL,
2522 target_resource_id bigint,
2523 expires timestamp without time zone
2524 );
2525
2526
2527 ALTER TABLE public.access_ticket OWNER TO postgres;
2528
2529 --
2530 -- Name: addressbook_address_adr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2531 --
2532
2533 CREATE TABLE addressbook_address_adr (
2534 dav_id bigint NOT NULL,
2535 type text,
2536 box_no text,
2537 unit_no text,
2538 street_address text,
2539 locality text,
2540 region text,
2541 postcode text,
2542 country text,
2543 property text
2544 );
2545
2546
2547 ALTER TABLE public.addressbook_address_adr OWNER TO postgres;
2548
2549 --
2550 -- Name: addressbook_address_email; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2551 --
2552
2553 CREATE TABLE addressbook_address_email (
2554 dav_id bigint NOT NULL,
2555 type text,
2556 email text,
2557 property text
2558 );
2559
2560
2561 ALTER TABLE public.addressbook_address_email OWNER TO postgres;
2562
2563 --
2564 -- Name: addressbook_address_tel; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2565 --
2566
2567 CREATE TABLE addressbook_address_tel (
2568 dav_id bigint NOT NULL,
2569 type text,
2570 tel text,
2571 property text
2572 );
2573
2574
2575 ALTER TABLE public.addressbook_address_tel OWNER TO postgres;
2576
2577 --
2578 -- Name: addressbook_resource; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2579 --
2580
2581 CREATE TABLE addressbook_resource (
2582 dav_id bigint NOT NULL,
2583 version text,
2584 uid text,
2585 nickname text,
2586 fn text,
2587 n text,
2588 note text,
2589 org text,
2590 url text,
2591 fburl text,
2592 caladruri text,
2593 caluri text
2594 );
2595
2596
2597 ALTER TABLE public.addressbook_resource OWNER TO postgres;
2598
2599 --
2600 -- Name: awl_db_revision; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2601 --
2602
2603 CREATE TABLE awl_db_revision (
2604 schema_id integer,
2605 schema_major integer,
2606 schema_minor integer,
2607 schema_patch integer,
2608 schema_name text,
2609 applied_on timestamp with time zone DEFAULT now()
2610 );
2611
2612
2613 ALTER TABLE public.awl_db_revision OWNER TO postgres;
2614
2615 --
2616 -- Name: dav_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2617 --
2618
2619 CREATE SEQUENCE dav_id_seq
2620 START WITH 1
2621 INCREMENT BY 1
2622 NO MINVALUE
2623 NO MAXVALUE
2624 CACHE 1;
2625
2626
2627 ALTER TABLE public.dav_id_seq OWNER TO postgres;
2628
2629 --
2630 -- Name: caldav_data; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2631 --
2632
2633 CREATE TABLE caldav_data (
2634 user_no integer NOT NULL,
2635 dav_name text NOT NULL,
2636 dav_etag text,
2637 created timestamp with time zone,
2638 modified timestamp with time zone,
2639 caldav_data text,
2640 caldav_type text,
2641 logged_user integer,
2642 dav_id bigint DEFAULT nextval('dav_id_seq'::regclass),
2643 collection_id bigint,
2644 weak_etag text
2645 );
2646
2647
2648 ALTER TABLE public.caldav_data OWNER TO postgres;
2649
2650 --
2651 -- Name: calendar_alarm; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2652 --
2653
2654 CREATE TABLE calendar_alarm (
2655 dav_id bigint NOT NULL,
2656 action text,
2657 trigger text,
2658 summary text,
2659 description text,
2660 next_trigger timestamp with time zone,
2661 component text,
2662 trigger_state character(1) DEFAULT 'N'::bpchar
2663 );
2664
2665
2666 ALTER TABLE public.calendar_alarm OWNER TO postgres;
2667
2668 --
2669 -- Name: calendar_attendee; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2670 --
2671
2672 CREATE TABLE calendar_attendee (
2673 dav_id bigint NOT NULL,
2674 status text,
2675 partstat text,
2676 cn text,
2677 attendee text NOT NULL,
2678 role text,
2679 rsvp boolean,
2680 property text,
2681 attendee_state text,
2682 weak_etag text
2683 );
2684
2685
2686 ALTER TABLE public.calendar_attendee OWNER TO postgres;
2687
2688 --
2689 -- Name: calendar_item; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2690 --
2691
2692 CREATE TABLE calendar_item (
2693 user_no integer NOT NULL,
2694 dav_name text NOT NULL,
2695 dav_etag text,
2696 uid text,
2697 created timestamp without time zone,
2698 last_modified timestamp without time zone,
2699 dtstamp timestamp without time zone,
2700 dtstart timestamp with time zone,
2701 dtend timestamp with time zone,
2702 due timestamp with time zone,
2703 summary text,
2704 location text,
2705 description text,
2706 priority integer,
2707 class text,
2708 transp text,
2709 rrule text,
2710 url text,
2711 percent_complete numeric(7,2),
2712 tz_id text,
2713 status text,
2714 completed timestamp with time zone,
2715 dav_id bigint,
2716 collection_id bigint,
2717 first_instance_start timestamp without time zone,
2718 last_instance_end timestamp without time zone
2719 );
2720
2721
2722 ALTER TABLE public.calendar_item OWNER TO postgres;
2723
2724 --
2725 -- Name: collection; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2726 --
2727
2728 CREATE TABLE collection (
2729 user_no integer,
2730 parent_container text,
2731 dav_name text,
2732 dav_etag text,
2733 dav_displayname text,
2734 is_calendar boolean,
2735 created timestamp with time zone,
2736 modified timestamp with time zone,
2737 public_events_only boolean DEFAULT false NOT NULL,
2738 publicly_readable boolean DEFAULT false NOT NULL,
2739 collection_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2740 default_privileges bit(24),
2741 is_addressbook boolean DEFAULT false,
2742 resourcetypes text DEFAULT '<DAV::collection/>'::text,
2743 schedule_transp text DEFAULT 'opaque'::text,
2744 timezone text,
2745 description text DEFAULT ''::text
2746 );
2747
2748
2749 ALTER TABLE public.collection OWNER TO postgres;
2750
2751 --
2752 -- Name: dav_binding; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2753 --
2754
2755 CREATE TABLE dav_binding (
2756 bind_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2757 bound_source_id bigint,
2758 access_ticket_id text,
2759 dav_owner_id bigint NOT NULL,
2760 parent_container text NOT NULL,
2761 dav_name text NOT NULL,
2762 dav_displayname text,
2763 external_url text,
2764 type text,
2765 CONSTRAINT dav_name_does_not_exist CHECK ((NOT real_path_exists(dav_name)))
2766 );
2767
2768
2769 ALTER TABLE public.dav_binding OWNER TO postgres;
2770
2771 --
2772 -- Name: principal; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2773 --
2774
2775 CREATE TABLE principal (
2776 principal_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2777 type_id bigint NOT NULL,
2778 user_no bigint,
2779 displayname text,
2780 default_privileges bit(24)
2781 );
2782
2783
2784 ALTER TABLE public.principal OWNER TO postgres;
2785
2786 --
2787 -- Name: usr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2788 --
2789
2790 CREATE TABLE usr (
2791 user_no integer NOT NULL,
2792 active boolean DEFAULT true,
2793 email_ok timestamp with time zone,
2794 joined timestamp with time zone DEFAULT now(),
2795 updated timestamp with time zone,
2796 last_used timestamp with time zone,
2797 username text NOT NULL,
2798 password text,
2799 fullname text,
2800 email text,
2801 config_data text,
2802 date_format_type text DEFAULT 'E'::text,
2803 locale text
2804 );
2805
2806
2807 ALTER TABLE public.usr OWNER TO postgres;
2808
2809 --
2810 -- Name: dav_principal; Type: VIEW; Schema: public; Owner: postgres
2811 --
2812
2813 CREATE VIEW dav_principal AS
2814 SELECT principal.user_no, usr.active AS user_active, usr.joined AS created, usr.updated AS modified, usr.username, usr.password, usr.fullname, usr.email, usr.email_ok, usr.date_format_type, usr.locale, principal.principal_id, principal.type_id, principal.displayname, principal.default_privileges, true AS is_principal, false AS is_calendar, principal.principal_id AS collection_id, false AS is_addressbook, (('/'::text || usr.username) || '/'::text) AS dav_name, '<DAV::collection/><DAV::principal/>'::text AS resourcetypes FROM (usr JOIN principal USING (user_no));
2815
2816
2817 ALTER TABLE public.dav_principal OWNER TO postgres;
2818
2819 --
2820 -- Name: freebusy_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2821 --
2822
2823 CREATE TABLE freebusy_ticket (
2824 ticket_id text NOT NULL,
2825 user_no integer NOT NULL,
2826 created timestamp with time zone DEFAULT now() NOT NULL
2827 );
2828
2829
2830 ALTER TABLE public.freebusy_ticket OWNER TO postgres;
2831
2832 --
2833 -- Name: grants; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2834 --
2835
2836 CREATE TABLE grants (
2837 by_principal bigint,
2838 by_collection bigint,
2839 to_principal bigint,
2840 privileges bit(24),
2841 is_group boolean
2842 );
2843
2844
2845 ALTER TABLE public.grants OWNER TO postgres;
2846
2847 --
2848 -- Name: group_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2849 --
2850
2851 CREATE TABLE group_member (
2852 group_id bigint,
2853 member_id bigint
2854 );
2855
2856
2857 ALTER TABLE public.group_member OWNER TO postgres;
2858
2859 --
2860 -- Name: locks; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2861 --
2862
2863 CREATE TABLE locks (
2864 dav_name text,
2865 opaquelocktoken text NOT NULL,
2866 type text,
2867 scope text,
2868 depth integer,
2869 owner text,
2870 timeout interval,
2871 start timestamp without time zone DEFAULT now()
2872 );
2873
2874
2875 ALTER TABLE public.locks OWNER TO postgres;
2876
2877 --
2878 -- Name: principal_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2879 --
2880
2881 CREATE TABLE principal_type (
2882 principal_type_id integer NOT NULL,
2883 principal_type_desc text
2884 );
2885
2886
2887 ALTER TABLE public.principal_type OWNER TO postgres;
2888
2889 --
2890 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2891 --
2892
2893 CREATE SEQUENCE principal_type_principal_type_id_seq
2894 START WITH 1
2895 INCREMENT BY 1
2896 NO MINVALUE
2897 NO MAXVALUE
2898 CACHE 1;
2899
2900
2901 ALTER TABLE public.principal_type_principal_type_id_seq OWNER TO postgres;
2902
2903 --
2904 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2905 --
2906
2907 ALTER SEQUENCE principal_type_principal_type_id_seq OWNED BY principal_type.principal_type_id;
2908
2909
2910 --
2911 -- Name: property; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2912 --
2913
2914 CREATE TABLE property (
2915 dav_name text NOT NULL,
2916 property_name text NOT NULL,
2917 property_value text,
2918 changed_on timestamp without time zone DEFAULT now(),
2919 changed_by integer
2920 );
2921
2922
2923 ALTER TABLE public.property OWNER TO postgres;
2924
2925 --
2926 -- Name: relationship; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2927 --
2928
2929 CREATE TABLE relationship (
2930 from_user integer NOT NULL,
2931 to_user integer NOT NULL,
2932 rt_id integer NOT NULL,
2933 confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
2934 );
2935
2936
2937 ALTER TABLE public.relationship OWNER TO postgres;
2938
2939 --
2940 -- Name: relationship_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2941 --
2942
2943 CREATE TABLE relationship_type (
2944 rt_id integer NOT NULL,
2945 rt_name text,
2946 rt_togroup boolean,
2947 confers text DEFAULT 'RW'::text,
2948 rt_fromgroup boolean,
2949 bit_confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
2950 );
2951
2952
2953 ALTER TABLE public.relationship_type OWNER TO postgres;
2954
2955 --
2956 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2957 --
2958
2959 CREATE SEQUENCE relationship_type_rt_id_seq
2960 START WITH 1
2961 INCREMENT BY 1
2962 NO MINVALUE
2963 NO MAXVALUE
2964 CACHE 1;
2965
2966
2967 ALTER TABLE public.relationship_type_rt_id_seq OWNER TO postgres;
2968
2969 --
2970 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2971 --
2972
2973 ALTER SEQUENCE relationship_type_rt_id_seq OWNED BY relationship_type.rt_id;
2974
2975
2976 --
2977 -- Name: role_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2978 --
2979
2980 CREATE TABLE role_member (
2981 role_no integer,
2982 user_no integer
2983 );
2984
2985
2986 ALTER TABLE public.role_member OWNER TO postgres;
2987
2988 --
2989 -- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2990 --
2991
2992 CREATE TABLE roles (
2993 role_no integer NOT NULL,
2994 role_name text
2995 );
2996
2997
2998 ALTER TABLE public.roles OWNER TO postgres;
2999
3000 --
3001 -- Name: roles_role_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3002 --
3003
3004 CREATE SEQUENCE roles_role_no_seq
3005 START WITH 1
3006 INCREMENT BY 1
3007 NO MINVALUE
3008 NO MAXVALUE
3009 CACHE 1;
3010
3011
3012 ALTER TABLE public.roles_role_no_seq OWNER TO postgres;
3013
3014 --
3015 -- Name: roles_role_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3016 --
3017
3018 ALTER SEQUENCE roles_role_no_seq OWNED BY roles.role_no;
3019
3020
3021 --
3022 -- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3023 --
3024
3025 CREATE TABLE session (
3026 session_id integer NOT NULL,
3027 user_no integer,
3028 session_start timestamp with time zone DEFAULT now(),
3029 session_end timestamp with time zone DEFAULT now(),
3030 session_key text,
3031 session_config text
3032 );
3033
3034
3035 ALTER TABLE public.session OWNER TO postgres;
3036
3037 --
3038 -- Name: session_session_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3039 --
3040
3041 CREATE SEQUENCE session_session_id_seq
3042 START WITH 1
3043 INCREMENT BY 1
3044 NO MINVALUE
3045 NO MAXVALUE
3046 CACHE 1;
3047
3048
3049 ALTER TABLE public.session_session_id_seq OWNER TO postgres;
3050
3051 --
3052 -- Name: session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3053 --
3054
3055 ALTER SEQUENCE session_session_id_seq OWNED BY session.session_id;
3056
3057
3058 --
3059 -- Name: supported_locales; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3060 --
3061
3062 CREATE TABLE supported_locales (
3063 locale text NOT NULL,
3064 locale_name_en text,
3065 locale_name_locale text
3066 );
3067
3068
3069 ALTER TABLE public.supported_locales OWNER TO postgres;
3070
3071 --
3072 -- Name: sync_changes; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3073 --
3074
3075 CREATE TABLE sync_changes (
3076 sync_time timestamp with time zone DEFAULT now(),
3077 collection_id bigint,
3078 sync_status integer,
3079 dav_id bigint,
3080 dav_name text
3081 );
3082
3083
3084 ALTER TABLE public.sync_changes OWNER TO postgres;
3085
3086 --
3087 -- Name: sync_tokens; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3088 --
3089
3090 CREATE TABLE sync_tokens (
3091 sync_token integer NOT NULL,
3092 collection_id bigint,
3093 modification_time timestamp with time zone DEFAULT now()
3094 );
3095
3096
3097 ALTER TABLE public.sync_tokens OWNER TO postgres;
3098
3099 --
3100 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3101 --
3102
3103 CREATE SEQUENCE sync_tokens_sync_token_seq
3104 START WITH 1
3105 INCREMENT BY 1
3106 NO MINVALUE
3107 NO MAXVALUE
3108 CACHE 1;
3109
3110
3111 ALTER TABLE public.sync_tokens_sync_token_seq OWNER TO postgres;
3112
3113 --
3114 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3115 --
3116
3117 ALTER SEQUENCE sync_tokens_sync_token_seq OWNED BY sync_tokens.sync_token;
3118
3119
3120 --
3121 -- Name: timezones; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3122 --
3123
3124 CREATE TABLE timezones (
3125 our_tzno integer NOT NULL,
3126 tzid text NOT NULL,
3127 olson_name text,
3128 active boolean,
3129 last_modified timestamp without time zone DEFAULT now(),
3130 etag text,
3131 vtimezone text
3132 );
3133
3134
3135 ALTER TABLE public.timezones OWNER TO postgres;
3136
3137 --
3138 -- Name: timezones_our_tzno_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3139 --
3140
3141 CREATE SEQUENCE timezones_our_tzno_seq
3142 START WITH 1
3143 INCREMENT BY 1
3144 NO MINVALUE
3145 NO MAXVALUE
3146 CACHE 1;
3147
3148
3149 ALTER TABLE public.timezones_our_tzno_seq OWNER TO postgres;
3150
3151 --
3152 -- Name: timezones_our_tzno_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3153 --
3154
3155 ALTER SEQUENCE timezones_our_tzno_seq OWNED BY timezones.our_tzno;
3156
3157
3158 --
3159 -- Name: tmp_password; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3160 --
3161
3162 CREATE TABLE tmp_password (
3163 user_no integer,
3164 password text,
3165 valid_until timestamp with time zone DEFAULT (now() + '1 day'::interval)
3166 );
3167
3168
3169 ALTER TABLE public.tmp_password OWNER TO postgres;
3170
3171 --
3172 -- Name: tz_aliases; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3173 --
3174
3175 CREATE TABLE tz_aliases (
3176 our_tzno bigint,
3177 tzalias text NOT NULL
3178 );
3179
3180
3181 ALTER TABLE public.tz_aliases OWNER TO postgres;
3182
3183 --
3184 -- Name: tz_localnames; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3185 --
3186
3187 CREATE TABLE tz_localnames (
3188 our_tzno bigint,
3189 locale text NOT NULL,
3190 localised_name text NOT NULL,
3191 preferred boolean DEFAULT true
3192 );
3193
3194
3195 ALTER TABLE public.tz_localnames OWNER TO postgres;
3196
3197 --
3198 -- Name: usr_setting; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3199 --
3200
3201 CREATE TABLE usr_setting (
3202 user_no integer NOT NULL,
3203 setting_name text NOT NULL,
3204 setting_value text
3205 );
3206
3207
3208 ALTER TABLE public.usr_setting OWNER TO postgres;
3209
3210 --
3211 -- Name: usr_user_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3212 --
3213
3214 CREATE SEQUENCE usr_user_no_seq
3215 START WITH 1
3216 INCREMENT BY 1
3217 NO MINVALUE
3218 NO MAXVALUE
3219 CACHE 1;
3220
3221
3222 ALTER TABLE public.usr_user_no_seq OWNER TO postgres;
3223
3224 --
3225 -- Name: usr_user_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3226 --
3227
3228 ALTER SEQUENCE usr_user_no_seq OWNED BY usr.user_no;
3229
3230
3231 --
3232 -- Name: principal_type_id; Type: DEFAULT; Schema: public; Owner: postgres
3233 --
3234
3235 ALTER TABLE ONLY principal_type ALTER COLUMN principal_type_id SET DEFAULT nextval('principal_type_principal_type_id_seq'::regclass);
3236
3237
3238 --
3239 -- Name: rt_id; Type: DEFAULT; Schema: public; Owner: postgres
3240 --
3241
3242 ALTER TABLE ONLY relationship_type ALTER COLUMN rt_id SET DEFAULT nextval('relationship_type_rt_id_seq'::regclass);
3243
3244
3245 --
3246 -- Name: role_no; Type: DEFAULT; Schema: public; Owner: postgres
3247 --
3248
3249 ALTER TABLE ONLY roles ALTER COLUMN role_no SET DEFAULT nextval('roles_role_no_seq'::regclass);
3250
3251
3252 --
3253 -- Name: session_id; Type: DEFAULT; Schema: public; Owner: postgres
3254 --
3255
3256 ALTER TABLE ONLY session ALTER COLUMN session_id SET DEFAULT nextval('session_session_id_seq'::regclass);
3257
3258
3259 --
3260 -- Name: sync_token; Type: DEFAULT; Schema: public; Owner: postgres
3261 --
3262
3263 ALTER TABLE ONLY sync_tokens ALTER COLUMN sync_token SET DEFAULT nextval('sync_tokens_sync_token_seq'::regclass);
3264
3265
3266 --
3267 -- Name: our_tzno; Type: DEFAULT; Schema: public; Owner: postgres
3268 --
3269
3270 ALTER TABLE ONLY timezones ALTER COLUMN our_tzno SET DEFAULT nextval('timezones_our_tzno_seq'::regclass);
3271
3272
3273 --
3274 -- Name: user_no; Type: DEFAULT; Schema: public; Owner: postgres
3275 --
3276
3277 ALTER TABLE ONLY usr ALTER COLUMN user_no SET DEFAULT nextval('usr_user_no_seq'::regclass);
3278
3279
3280 --
3281 -- Name: access_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3282 --
3283
3284 ALTER TABLE ONLY access_ticket
3285 ADD CONSTRAINT access_ticket_pkey PRIMARY KEY (ticket_id);
3286
3287
3288 --
3289 -- Name: addressbook_resource_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3290 --
3291
3292 ALTER TABLE ONLY addressbook_resource
3293 ADD CONSTRAINT addressbook_resource_pkey PRIMARY KEY (dav_id);
3294
3295
3296 --
3297 -- Name: caldav_data_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3298 --
3299
3300 ALTER TABLE ONLY caldav_data
3301 ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id);
3302
3303
3304 --
3305 -- Name: caldav_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3306 --
3307
3308 ALTER TABLE ONLY caldav_data
3309 ADD CONSTRAINT caldav_data_pkey PRIMARY KEY (user_no, dav_name);
3310
3311
3312 --
3313 -- Name: calendar_attendee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3314 --
3315
3316 ALTER TABLE ONLY calendar_attendee
3317 ADD CONSTRAINT calendar_attendee_pkey PRIMARY KEY (dav_id, attendee);
3318
3319
3320 --
3321 -- Name: calendar_item_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3322 --
3323
3324 ALTER TABLE ONLY calendar_item
3325 ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id);
3326
3327
3328 --
3329 -- Name: calendar_item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3330 --
3331
3332 ALTER TABLE ONLY calendar_item
3333 ADD CONSTRAINT calendar_item_pkey PRIMARY KEY (user_no, dav_name);
3334
3335
3336 --
3337 -- Name: collection_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3338 --
3339
3340 ALTER TABLE ONLY collection
3341 ADD CONSTRAINT collection_pkey PRIMARY KEY (collection_id);
3342
3343
3344 --
3345 -- Name: collection_user_no_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3346 --
3347
3348 ALTER TABLE ONLY collection
3349 ADD CONSTRAINT collection_user_no_dav_name_key UNIQUE (user_no, dav_name);
3350
3351
3352 --
3353 -- Name: dav_binding_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3354 --
3355
3356 ALTER TABLE ONLY dav_binding
3357 ADD CONSTRAINT dav_binding_dav_name_key UNIQUE (dav_name);
3358
3359
3360 --
3361 -- Name: dav_binding_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3362 --
3363
3364 ALTER TABLE ONLY dav_binding
3365 ADD CONSTRAINT dav_binding_pkey PRIMARY KEY (bind_id