2 -- PostgreSQL database dump
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
;
12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
15 CREATE EXTENSION
IF NOT EXISTS plpgsql
WITH SCHEMA pg_catalog
;
19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
22 COMMENT ON EXTENSION plpgsql
IS 'PL/pgSQL procedural language';
25 SET search_path
= public, pg_catalog
;
28 -- Name: rrule_instance; Type: TYPE; Schema: public; Owner: postgres
31 CREATE TYPE rrule_instance
AS (
32 dtstart
timestamp with time zone,
34 instance timestamp with time zone
38 ALTER TYPE public.rrule_instance
OWNER TO postgres
;
41 -- Name: rrule_parts; Type: TYPE; Schema: public; Owner: postgres
44 CREATE TYPE rrule_parts
AS (
45 base
timestamp with time zone,
46 until timestamp with time zone,
63 ALTER TYPE public.rrule_parts
OWNER TO postgres
;
66 -- Name: alarm_changed(); Type: FUNCTION; Schema: public; Owner: postgres
69 CREATE FUNCTION alarm_changed() RETURNS trigger
76 -- in case we trigger on other events in future
77 IF TG_OP
= 'UPDATE' THEN
78 IF NEW.component
!= OLD.component
THEN
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
;
90 ALTER FUNCTION public.
alarm_changed() OWNER TO postgres
;
93 -- Name: apply_month_byday(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
96 CREATE FUNCTION apply_month_byday(timestamp with time zone, text) RETURNS timestamp with time zone
97 LANGUAGE plpgsql
IMMUTABLE STRICT
100 in_time
ALIAS FOR $
1;
109 our_answer
TIMESTAMP WITH TIME ZONE;
111 dow
:= position(substring( byday
from '..$') in 'SUMOTUWETHFRSA') / 2;
112 temp_txt
:= substring(byday
from '([0-9]+)');
113 weeks
:= temp_txt
::int;
115 -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
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
);
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
);
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;
138 our_answer
:= our_answer
- (weeks
::text ||
'weeks')::interval;
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;
150 our_answer
:= in_time
- (our_dow
::text ||
'days')::interval;
151 dd
= extract( 'day' from our_answer
);
153 -- Shift the date to the correct week...
154 dd
:= weeks
- ((dd
+6) / 7);
156 our_answer
:= our_answer
+ ((dd
::text ||
'weeks')::interval);
167 ALTER FUNCTION public.
apply_month_byday(timestamp with time zone, text) OWNER TO postgres
;
170 -- Name: bits_to_legacy_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
173 CREATE FUNCTION bits_to_legacy_privilege(bit) RETURNS text
174 LANGUAGE plpgsql
IMMUTABLE STRICT
177 in_bits
ALIAS FOR $
1;
181 IF in_bits
= (~
0::BIT(24)) THEN
186 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
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
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
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';
207 IF (in_bits
& 64::BIT(24)) != 0::BIT(24) THEN
208 out_priv
:= out_priv ||
'B';
210 IF (in_bits
& 128::BIT(24)) != 0::BIT(24) THEN
211 out_priv
:= out_priv ||
'U';
221 ALTER FUNCTION public.
bits_to_legacy_privilege(bit) OWNER TO postgres
;
224 -- Name: bits_to_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
227 CREATE FUNCTION bits_to_privilege(bit) RETURNS text[]
228 LANGUAGE plpgsql
IMMUTABLE STRICT
231 in_bits
ALIAS FOR $
1;
234 IF in_bits
= (~
0::BIT(24)) THEN
235 out_priv
:= out_priv ||
ARRAY['DAV:all'];
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'];
242 IF (in_bits
& 512::BIT(24)) != 0::BIT(24) THEN
243 out_priv
:= out_priv ||
ARRAY['caldav:read-free-busy'];
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'];
251 IF (in_bits
& 2::BIT(24)) != 0::BIT(24) THEN
252 out_priv
:= out_priv ||
ARRAY['DAV:write-properties'];
254 IF (in_bits
& 4::BIT(24)) != 0::BIT(24) THEN
255 out_priv
:= out_priv ||
ARRAY['DAV:write-content'];
257 IF (in_bits
& 64::BIT(24)) != 0::BIT(24) THEN
258 out_priv
:= out_priv ||
ARRAY['DAV:bind'];
260 IF (in_bits
& 128::BIT(24)) != 0::BIT(24) THEN
261 out_priv
:= out_priv ||
ARRAY['DAV:unbind'];
266 IF (in_bits
& 8::BIT(24)) != 0::BIT(24) THEN
267 out_priv
:= out_priv ||
ARRAY['DAV:unlock'];
270 IF (in_bits
& 16::BIT(24)) != 0::BIT(24) THEN
271 out_priv
:= out_priv ||
ARRAY['DAV:read-acl'];
274 IF (in_bits
& 32::BIT(24)) != 0::BIT(24) THEN
275 out_priv
:= out_priv ||
ARRAY['DAV:read-current-user-privilege-set'];
278 IF (in_bits
& 256::BIT(24)) != 0::BIT(24) THEN
279 out_priv
:= out_priv ||
ARRAY['DAV:write-acl'];
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'];
286 IF (in_bits
& 1024::BIT(24)) != 0::BIT(24) THEN
287 out_priv
:= out_priv ||
ARRAY['caldav:schedule-deliver-invite'];
289 IF (in_bits
& 2048::BIT(24)) != 0::BIT(24) THEN
290 out_priv
:= out_priv ||
ARRAY['caldav:schedule-deliver-reply'];
292 IF (in_bits
& 4096::BIT(24)) != 0::BIT(24) THEN
293 out_priv
:= out_priv ||
ARRAY['caldav:schedule-query-freebusy'];
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'];
302 IF (in_bits
& 8192::BIT(24)) != 0::BIT(24) THEN
303 out_priv
:= out_priv ||
ARRAY['caldav:schedule-send-invite'];
305 IF (in_bits
& 16384::BIT(24)) != 0::BIT(24) THEN
306 out_priv
:= out_priv ||
ARRAY['caldav:schedule-send-reply'];
308 IF (in_bits
& 32768::BIT(24)) != 0::BIT(24) THEN
309 out_priv
:= out_priv ||
ARRAY['caldav:schedule-send-freebusy'];
319 ALTER FUNCTION public.
bits_to_privilege(bit) OWNER TO postgres
;
322 -- Name: calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
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
329 earliest
ALIAS FOR $
1;
330 basedate
ALIAS FOR $
2;
331 repeatrule
ALIAS FOR $
3;
342 our_answer
TIMESTAMP WITH TIME ZONE;
345 IF basedate
> earliest
THEN
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
354 frequency
:= substring(repeatrule
from 'FREQ=([A-Z]+)(;|$)');
355 IF frequency
IS NULL THEN
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
;
366 -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
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;
376 past_repeats
= extract( 'years' from basediff
);
377 IF frequency
= 'MONTHLY' THEN
378 past_repeats
= (past_repeats
*12) + extract( 'months' from basediff
);
381 IF length IS NOT NULL THEN
382 past_repeats
= (past_repeats
/ length) + 1;
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
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;
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
;
414 past_repeats
= past_repeats
* length;
417 WHEN frequency
= 'DAILY' THEN 'days'
418 WHEN frequency
= 'WEEKLY' THEN 'weeks'
419 WHEN frequency
= 'MONTHLY' THEN 'months'
420 WHEN frequency
= 'YEARLY' THEN 'years'
423 temp_txt
:= substring(repeatrule
from 'BYMONTHDAY=([0-9,]+)(;|$)');
424 bymonthday
:= temp_txt
::int;
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;
430 IF our_answer
IS NULL THEN
431 RAISE
EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate
, past_repeats
, units
;
435 loopcount
:= 500; -- Desirable to stop an infinite loop if there is something we cannot handle
437 -- RAISE NOTICE 'Testing date: %', our_answer;
438 IF frequency
= 'DAILY' THEN
439 IF byday
IS NOT NULL THEN
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;
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
);
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;
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.';
466 EXIT
WHEN our_answer
>= earliest
;
468 -- Give up if we have exceeded the count
469 IF ( count IS NOT NULL AND past_repeats
> count ) THEN
472 past_repeats
:= past_repeats
+ 1;
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
;
481 -- Increment for our next time through the loop...
482 our_answer
:= our_answer
+ (length::text || units
)::interval;
492 ALTER FUNCTION public.
calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) OWNER TO postgres
;
495 -- Name: caldav_data_modified(); Type: FUNCTION; Schema: public; Owner: postgres
498 CREATE FUNCTION caldav_data_modified() RETURNS trigger
502 coll_id caldav_data.collection_id
%TYPE;
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
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.
516 SET modified
= current_timestamp, dav_etag
= md5(NEW.dav_etag
)
517 WHERE collection_id
= NEW.collection_id
;
518 IF TG_OP
= 'INSERT' THEN
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.
527 SET modified
= current_timestamp, dav_etag
= md5(OLD.dav_name
::text||
OLD.dav_etag
)
528 WHERE collection_id
= OLD.collection_id
;
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.
536 SET modified
= current_timestamp, dav_etag
= md5(OLD.dav_name
::text||
OLD.dav_etag
)
537 WHERE collection_id
= OLD.collection_id
;
544 ALTER FUNCTION public.
caldav_data_modified() OWNER TO postgres
;
547 -- Name: check_db_revision(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
550 CREATE FUNCTION check_db_revision(integer, integer, integer) RETURNS boolean
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
)
564 IF matching
>= 1 THEN
565 RAISE
EXCEPTION 'Database revisions after %.%.% have already been applied.', major
, minor
, patch
;
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
573 RAISE
EXCEPTION 'Database has not been upgraded to %.%.%', major
, minor
, patch
;
579 ALTER FUNCTION public.
check_db_revision(integer, integer, integer) OWNER TO postgres
;
582 -- Name: collection_modified(); Type: FUNCTION; Schema: public; Owner: postgres
585 CREATE FUNCTION collection_modified() RETURNS trigger
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
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
;
604 ALTER FUNCTION public.
collection_modified() OWNER TO postgres
;
607 -- Name: collections_within(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
610 CREATE FUNCTION collections_within(integer, integer) RETURNS SETOF integer
614 in_collection_id
ALIAS FOR $
1;
615 in_depth
ALIAS FOR $
2;
619 in_depth
:= in_depth
- 1;
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
626 RETURN NEXT resource_id
;
628 FOR resource_id
IN SELECT * FROM collections_within( resource_id
, in_depth
) LOOP
629 RETURN NEXT resource_id
;
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
638 RETURN NEXT resource_id
;
640 FOR resource_id
IN SELECT * FROM collections_within( resource_id
, in_depth
) LOOP
641 RETURN NEXT resource_id
;
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
652 RETURN NEXT resource_id
;
654 FOR resource_id
IN SELECT * FROM collections_within( resource_id
, in_depth
) LOOP
655 RETURN NEXT resource_id
;
663 ALTER FUNCTION public.
collections_within(integer, integer) OWNER TO postgres
;
666 -- Name: daily_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
669 CREATE FUNCTION daily_set(timestamp with time zone, rrule_parts
) RETURNS SETOF timestamp with time zone
670 LANGUAGE plpgsql
IMMUTABLE STRICT
677 IF rrule.bymonth
IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth
) THEN
681 IF rrule.byweekno
IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno
) THEN
685 IF rrule.byyearday
IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday
) THEN
689 IF rrule.bymonthday
IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday
) THEN
693 IF rrule.byday
IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday
) THEN
697 -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial
704 ALTER FUNCTION public.
daily_set(timestamp with time zone, rrule_parts
) OWNER TO postgres
;
707 -- Name: event_has_exceptions(text); Type: FUNCTION; Schema: public; Owner: postgres
710 CREATE FUNCTION event_has_exceptions(text) RETURNS boolean
711 LANGUAGE sql IMMUTABLE STRICT
713 SELECT $
1 ~ E
'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?'
717 ALTER FUNCTION public.
event_has_exceptions(text) OWNER TO postgres
;
720 -- Name: event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
723 CREATE FUNCTION event_instances(timestamp with time zone, text) RETURNS SETOF timestamp with time zone
724 LANGUAGE plpgsql
IMMUTABLE STRICT
727 basedate
ALIAS FOR $
1;
728 repeatrule
ALIAS FOR $
2;
729 maxdate
TIMESTAMP WITH TIME ZONE;
731 maxdate
:= current_date + '10 years'::interval;
732 RETURN QUERY
SELECT d
FROM rrule_event_instances_range( basedate
, repeatrule
, basedate
, maxdate
, 300 ) d
;
737 ALTER FUNCTION public.
event_instances(timestamp with time zone, text) OWNER TO postgres
;
740 -- Name: expand_members(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
743 CREATE FUNCTION expand_members(bigint, integer) RETURNS SETOF bigint
744 LANGUAGE sql STABLE STRICT
746 SELECT member_id
FROM group_member
WHERE group_id
= $
1
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;
754 ALTER FUNCTION public.
expand_members(bigint, integer) OWNER TO postgres
;
757 -- Name: expand_memberships(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
760 CREATE FUNCTION expand_memberships(bigint, integer) RETURNS SETOF bigint
761 LANGUAGE sql STABLE STRICT
763 SELECT group_id
FROM group_member
WHERE member_id
= $
1
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;
771 ALTER FUNCTION public.
expand_memberships(bigint, integer) OWNER TO postgres
;
774 -- Name: get_group_role_no(); Type: FUNCTION; Schema: public; Owner: postgres
777 CREATE FUNCTION get_group_role_no() RETURNS integer
778 LANGUAGE sql IMMUTABLE
780 SELECT role_no
FROM roles
WHERE role_name
= 'Group'
784 ALTER FUNCTION public.
get_group_role_no() OWNER TO postgres
;
787 -- Name: get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
790 CREATE FUNCTION get_permissions(integer, integer) RETURNS text
791 LANGUAGE plpgsql
IMMUTABLE STRICT
794 in_from
ALIAS FOR $
1;
804 -- Self can always have full access
805 IF in_from
= in_to
THEN
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');
813 RETURN dbg || out_confers
;
815 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
817 SELECT bit_or(r1.confers
& r2.confers
) INTO bit_confers
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
);
827 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
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
) ;
835 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
836 RETURN dbg || out_confers
;
839 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
846 ALTER FUNCTION public.
get_permissions(integer, integer) OWNER TO postgres
;
849 -- Name: get_usr_setting(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
852 CREATE FUNCTION get_usr_setting(integer, text) RETURNS text
854 AS $_$
SELECT setting_value
FROM usr_setting
855 WHERE usr_setting.user_no
= $
1
856 AND usr_setting.setting_name
= $
2 $_$
;
859 ALTER FUNCTION public.
get_usr_setting(integer, text) OWNER TO postgres
;
862 -- Name: grants_modified(); Type: FUNCTION; Schema: public; Owner: postgres
865 CREATE FUNCTION grants_modified() RETURNS trigger
869 old_to_principal INT8
;
872 -- in case we trigger on other events in future
873 IF TG_OP
= 'INSERT' THEN
874 old_to_principal
:= NULL;
876 old_to_principal
:= OLD.to_principal
;
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
;
889 ALTER FUNCTION public.
grants_modified() OWNER TO postgres
;
892 -- Name: grants_proxy_access_from_p(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
895 CREATE FUNCTION grants_proxy_access_from_p(bigint, integer) RETURNS SETOF bigint
896 LANGUAGE sql STABLE STRICT
898 SELECT DISTINCT by_principal
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)
906 ALTER FUNCTION public.
grants_proxy_access_from_p(bigint, integer) OWNER TO postgres
;
909 -- Name: has_legacy_privilege(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
912 CREATE FUNCTION has_legacy_privilege(integer, text, integer) RETURNS boolean
913 LANGUAGE plpgsql
IMMUTABLE STRICT
916 in_from
ALIAS FOR $
1;
917 in_legacy_privilege
ALIAS FOR $
2;
922 -- Self can always have full access
923 IF in_from
= in_to
THEN
927 SELECT get_group_role_no() INTO group_role_no
;
928 SELECT legacy_privilege_to_bits(in_legacy_privilege
) INTO in_confers
;
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';
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';
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';
957 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
964 ALTER FUNCTION public.
has_legacy_privilege(integer, text, integer) OWNER TO postgres
;
967 -- Name: has_members_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
970 CREATE FUNCTION has_members_list(bigint) RETURNS text
971 LANGUAGE plpgsql
STRICT
974 in_member_id
ALIAS FOR $
1;
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
983 ||
CASE WHEN mlist
= '' THEN '' ELSE ', ' END
984 ||
COALESCE( m.displayname
, m.group_id
::text);
991 ALTER FUNCTION public.
has_members_list(bigint) OWNER TO postgres
;
994 -- Name: icalendar_interval_to_sql(text); Type: FUNCTION; Schema: public; Owner: postgres
997 CREATE FUNCTION icalendar_interval_to_sql(text) RETURNS interval
998 LANGUAGE sql IMMUTABLE STRICT
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;
1004 ALTER FUNCTION public.
icalendar_interval_to_sql(text) OWNER TO postgres
;
1007 -- Name: is_member_of_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1010 CREATE FUNCTION is_member_of_list(bigint) RETURNS text
1011 LANGUAGE plpgsql
STRICT
1014 in_member_id
ALIAS FOR $
1;
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
1023 ||
CASE WHEN mlist
= '' THEN '' ELSE ', ' END
1024 ||
COALESCE( m.displayname
, m.group_id
::text);
1031 ALTER FUNCTION public.
is_member_of_list(bigint) OWNER TO postgres
;
1034 -- Name: legacy_get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
1037 CREATE FUNCTION legacy_get_permissions(integer, integer) RETURNS text
1038 LANGUAGE plpgsql
IMMUTABLE STRICT
1041 in_from
ALIAS FOR $
1;
1047 dbg
TEXT DEFAULT '';
1051 -- Self can always have full access
1052 IF in_from
= in_to
THEN
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');
1060 RETURN dbg || out_confers
;
1062 -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
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')
1072 -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
1073 -- FIXME: This is an oversimplification
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';
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';
1086 -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
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);
1093 tmp_confers2
:= tmp_txt
;
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);
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
1104 IF out_confers
!= '' THEN
1105 RETURN dbg || out_confers
;
1108 -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
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');
1118 -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
1119 RETURN dbg || out_confers
;
1122 -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
1129 ALTER FUNCTION public.
legacy_get_permissions(integer, integer) OWNER TO postgres
;
1132 -- Name: legacy_privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1135 CREATE FUNCTION legacy_privilege_to_bits(text) RETURNS bit
1136 LANGUAGE plpgsql
IMMUTABLE STRICT
1139 in_priv
ALIAS FOR $
1;
1142 out_bits
:= 0::BIT(24);
1143 IF in_priv ~
* 'A' THEN
1144 out_bits
= ~ out_bits
;
1148 -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
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);
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
1161 IF in_priv ~
* 'W' THEN
1162 out_bits
:= out_bits |
198::BIT(24);
1166 IF in_priv ~
* 'B' THEN
1167 out_bits
:= out_bits |
64::BIT(24);
1171 IF in_priv ~
* 'U' THEN
1172 out_bits
:= out_bits |
128::BIT(24);
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);
1186 ALTER FUNCTION public.
legacy_privilege_to_bits(text) OWNER TO postgres
;
1189 -- Name: max_roles(); Type: FUNCTION; Schema: public; Owner: postgres
1192 CREATE FUNCTION max_roles() RETURNS integer
1194 AS $$
SELECT max(role_no
) FROM roles$$
;
1197 ALTER FUNCTION public.
max_roles() OWNER TO postgres
;
1200 -- Name: max_session(); Type: FUNCTION; Schema: public; Owner: postgres
1203 CREATE FUNCTION max_session() RETURNS integer
1205 AS $$
SELECT max(session_id
) FROM session$$
;
1208 ALTER FUNCTION public.
max_session() OWNER TO postgres
;
1211 -- Name: max_usr(); Type: FUNCTION; Schema: public; Owner: postgres
1214 CREATE FUNCTION max_usr() RETURNS integer
1216 AS $$
SELECT max(user_no
) FROM usr$$
;
1219 ALTER FUNCTION public.
max_usr() OWNER TO postgres
;
1222 -- Name: monthly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
1225 CREATE FUNCTION monthly_set(timestamp with time zone, rrule_parts
) RETURNS SETOF timestamp with time zone
1226 LANGUAGE plpgsql
IMMUTABLE STRICT
1231 valid_date
TIMESTAMP WITH TIME ZONE;
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:
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.
1249 * My guess is that this means 'INTERSECT'
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
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;
1258 OPEN curse
SCROLL FOR SELECT r
FROM rrule_month_byday_set(after, rrule.byday
) r
ORDER BY 1;
1261 RETURN QUERY
SELECT d
FROM rrule_bysetpos_filter(curse
,rrule.bysetpos
) d
;
1267 ALTER FUNCTION public.
monthly_set(timestamp with time zone, rrule_parts
) OWNER TO postgres
;
1270 -- Name: new_db_revision(integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
1273 CREATE FUNCTION new_db_revision(integer, integer, integer, text) RETURNS void
1283 SELECT MAX(schema_id
) + 1 INTO new_id
FROM awl_db_revision
;
1284 IF NOT FOUND OR new_id
IS NULL THEN
1287 INSERT INTO awl_db_revision (schema_id
, schema_major
, schema_minor
, schema_patch
, schema_name)
1288 VALUES( new_id
, major
, minor
, patch
, blurb
);
1294 ALTER FUNCTION public.
new_db_revision(integer, integer, integer, text) OWNER TO postgres
;
1297 -- Name: new_sync_token(bigint, bigint); Type: FUNCTION; Schema: public; Owner: postgres
1300 CREATE FUNCTION new_sync_token(bigint, bigint) RETURNS bigint
1301 LANGUAGE plpgsql
STRICT
1304 in_old_sync_token
ALIAS FOR $
1;
1305 in_collection_id
ALIAS FOR $
2;
1307 new_token sync_tokens.sync_token
%TYPE;
1308 old_modification_time sync_tokens.modification_time
%TYPE;
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
;
1314 -- They are in an inconsistent state: we return NULL so they can re-start the process
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;
1323 SELECT 1 INTO tmp_int
FROM sync_changes
WHERE collection_id
= in_collection_id
AND sync_time
> old_modification_time
LIMIT 1;
1325 -- Return the latest sync_token we have for this collection, since there are no changes.
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
);
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
;
1341 -- Returning the new token
1347 ALTER FUNCTION public.
new_sync_token(bigint, bigint) OWNER TO postgres
;
1350 -- Name: p_has_proxy_access_to(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1353 CREATE FUNCTION p_has_proxy_access_to(bigint, integer) RETURNS SETOF bigint
1354 LANGUAGE sql STABLE STRICT
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
1364 SELECT principal_id
AS by_principal
FROM principal
1365 WHERE (default_privileges
& 5::BIT(24)) != 0::BIT(24)
1366 AND principal_id
!= $
1
1371 ALTER FUNCTION public.
p_has_proxy_access_to(bigint, integer) OWNER TO postgres
;
1374 -- Name: parse_rrule_parts(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1377 CREATE FUNCTION parse_rrule_parts(timestamp with time zone, text) RETURNS rrule_parts
1378 LANGUAGE plpgsql
IMMUTABLE STRICT
1381 basedate
ALIAS FOR $
1;
1382 repeatrule
ALIAS FOR $
2;
1383 result rrule_parts
%ROWTYPE
;
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)(;|$)');
1393 result.byday
:= string_to_array( substring(repeatrule
from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ',');
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},?)+)(;|$)'), ',');
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,]+)(;|$)'), ',');
1410 ALTER FUNCTION public.
parse_rrule_parts(timestamp with time zone, text) OWNER TO postgres
;
1413 -- Name: path_privs(bigint, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
1416 CREATE FUNCTION path_privs(bigint, text, integer) RETURNS bit
1417 LANGUAGE plpgsql
STABLE STRICT
1420 in_accessor
ALIAS FOR $
1;
1421 in_path
ALIAS FOR $
2;
1422 in_depth
ALIAS FOR $
3;
1426 grantor_collection INT8
;
1427 grantor_principal INT8
;
1428 collection_path
TEXT;
1429 collection_privileges
BIT(24);
1430 out_conferred
BIT(24);
1432 out_conferred
:= 0::BIT(24);
1434 IF in_path ~
'^/?$' THEN
1435 -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
1436 RETURN 1; -- basic read privileges on root directory
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
;
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) ||
'/';
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;
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
;
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
)));
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;
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;
1481 out_conferred
:= collection_privileges
;
1482 -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1486 RETURN out_conferred
;
1491 ALTER FUNCTION public.
path_privs(bigint, text, integer) OWNER TO postgres
;
1494 -- Name: pprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1497 CREATE FUNCTION pprivs(bigint, bigint, integer) RETURNS bit
1498 LANGUAGE plpgsql
STABLE STRICT
1501 in_accessor
ALIAS FOR $
1;
1502 in_grantor
ALIAS FOR $
2;
1503 in_depth
ALIAS FOR $
3;
1504 out_conferred
BIT(24);
1506 out_conferred
:= 0::BIT(24);
1507 -- Self can always have full access
1508 IF in_grantor
= in_accessor
THEN
1509 RETURN ~ out_conferred
;
1512 SELECT bit_or(subquery.
privileges) INTO out_conferred
FROM
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
)))
1517 SELECT bit_or(sq2.
privileges) FROM
1519 SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor
,in_depth
) WHERE expand_memberships
= in_grantor
1521 SELECT default_privileges
AS privileges FROM principal
WHERE principal_id
= in_grantor
1525 IF out_conferred
IS NULL THEN
1526 SELECT default_privileges
INTO out_conferred
FROM principal
WHERE principal_id
= in_grantor
;
1529 RETURN out_conferred
;
1534 ALTER FUNCTION public.
pprivs(bigint, bigint, integer) OWNER TO postgres
;
1537 -- Name: principal_modified(); Type: FUNCTION; Schema: public; Owner: postgres
1540 CREATE FUNCTION principal_modified() RETURNS trigger
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
1549 SET is_group
= (NEW.type_id
= 3)
1550 WHERE grants.to_principal
= NEW.principal_id
;
1558 ALTER FUNCTION public.
principal_modified() OWNER TO postgres
;
1561 -- Name: privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1564 CREATE FUNCTION privilege_to_bits(text) RETURNS bit
1565 LANGUAGE plpgsql
IMMUTABLE STRICT
1568 raw_priv
ALIAS FOR $
1;
1571 in_priv
:= trim(lower(regexp_replace(raw_priv
, '^.*:', '')));
1572 IF in_priv
= 'all' THEN
1573 RETURN ~
0::BIT(24);
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);
1601 ALTER FUNCTION public.
privilege_to_bits(text) OWNER TO postgres
;
1604 -- Name: privilege_to_bits(text[]); Type: FUNCTION; Schema: public; Owner: postgres
1607 CREATE FUNCTION privilege_to_bits(text[]) RETURNS bit
1608 LANGUAGE plpgsql
IMMUTABLE STRICT
1611 raw_privs
ALIAS FOR $
1;
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
1634 ALTER FUNCTION public.
privilege_to_bits(text[]) OWNER TO postgres
;
1637 -- Name: privileges_list(bit); Type: FUNCTION; Schema: public; Owner: postgres
1640 CREATE FUNCTION privileges_list(bit) RETURNS text
1641 LANGUAGE plpgsql
IMMUTABLE STRICT
1644 in_privileges
ALIAS FOR $
1;
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
1659 ||
CASE WHEN plist
= '' THEN '' ELSE ', ' END
1668 ALTER FUNCTION public.
privileges_list(bit) OWNER TO postgres
;
1671 -- Name: real_path_exists(text); Type: FUNCTION; Schema: public; Owner: postgres
1674 CREATE FUNCTION real_path_exists(text) RETURNS boolean
1678 in_path
ALIAS FOR $
1;
1681 IF in_path
= '/' THEN
1684 IF in_path ~
'^/[^/]+/$' THEN
1685 SELECT TRUE INTO tmp
FROM usr
WHERE username
= substring( in_path
from 2 for length(in_path
) - 2);
1690 IF in_path ~
'^/.*/$' THEN
1691 SELECT TRUE INTO tmp
FROM collection
WHERE dav_name
= in_path
;
1702 ALTER FUNCTION public.
real_path_exists(text) OWNER TO postgres
;
1705 -- Name: relationship_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1708 CREATE FUNCTION relationship_list(bigint) RETURNS text
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
1722 ||
CASE WHEN rlist
= '' THEN '' ELSE ', ' END
1723 || r.rt_name ||
'(' || r.fullname ||
')';
1730 ALTER FUNCTION public.
relationship_list(bigint) OWNER TO postgres
;
1733 -- Name: rrule_bysetpos_filter(refcursor, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
1736 CREATE FUNCTION rrule_bysetpos_filter(refcursor
, integer[]) RETURNS SETOF timestamp with time zone
1737 LANGUAGE plpgsql
IMMUTABLE
1741 bysetpos
ALIAS FOR $
2;
1742 valid_date
TIMESTAMP WITH TIME ZONE;
1746 IF bysetpos
IS NULL THEN
1748 FETCH curse
INTO valid_date
;
1749 EXIT
WHEN NOT FOUND;
1750 RETURN NEXT valid_date
;
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
;
1759 FETCH RELATIVE (bysetpos
[i
] + 1) FROM curse
INTO valid_date
;
1761 IF valid_date
IS NOT NULL THEN
1762 RETURN NEXT valid_date
;
1771 ALTER FUNCTION public.
rrule_bysetpos_filter(refcursor
, integer[]) OWNER TO postgres
;
1774 -- Name: rrule_event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1777 CREATE FUNCTION rrule_event_instances(timestamp with time zone, text) RETURNS SETOF rrule_instance
1778 LANGUAGE plpgsql
IMMUTABLE STRICT
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
;
1787 maxdate
:= current_date + '10 years'::interval;
1789 result.dtstart
:= basedate
;
1790 result.rrule
:= repeatrule
;
1792 FOR current IN SELECT d
FROM rrule_event_instances_range( basedate
, repeatrule
, basedate
, maxdate
, 300 ) d LOOP
1793 result.
instance := current;
1801 ALTER FUNCTION public.
rrule_event_instances(timestamp with time zone, text) OWNER TO postgres
;
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
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
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;
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
;
1825 SELECT * INTO rrule
FROM parse_rrule_parts( basedate
, repeatrule
);
1827 IF rrule.
count IS NOT NULL THEN
1828 loopmax
:= rrule.
count;
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;
1836 loopmax
:= max_count
;
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;
1850 loopcount
:= loopcount
+ 1;
1851 EXIT
WHEN loopcount
>= loopmax
;
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
)
1861 EXIT
WHEN rrule.
until IS NOT NULL AND current > rrule.
until;
1862 IF current >= mindate
THEN
1863 RETURN NEXT current;
1865 loopcount
:= loopcount
+ 1;
1866 EXIT
WHEN loopcount
>= loopmax
;
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)
1875 EXIT
WHEN rrule.
until IS NOT NULL AND current > rrule.
until;
1876 IF current >= mindate
THEN
1877 RETURN NEXT current;
1879 loopcount
:= loopcount
+ 1;
1880 EXIT
WHEN loopcount
>= loopmax
;
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;
1890 loopcount
:= loopcount
+ 1;
1891 EXIT
WHEN loopcount
>= loopmax
;
1893 current_base
:= current_base
+ (rrule.
interval::text ||
' years')::interval;
1895 RAISE NOTICE
'A frequency of "%" is not handled', rrule.freq
;
1898 EXIT
WHEN rrule.
until IS NOT NULL AND current > rrule.
until;
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
;
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
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
1915 dtstart
ALIAS FOR $
1;
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;
1925 IF dtstart
IS NULL THEN
1928 IF dtend
IS NULL THEN
1929 base_date
:= dtstart
;
1934 IF in_mindate
IS NULL THEN
1935 mindate
:= current_date - '10 years'::interval;
1937 mindate
:= in_mindate
;
1940 IF in_maxdate
IS NULL THEN
1941 maxdate
:= current_date + '10 years'::interval;
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
);
1947 IF repeatrule
IS NULL THEN
1948 RETURN (dtstart
<= maxdate
AND base_date
>= mindate
);
1951 SELECT d
INTO mindate
FROM rrule_event_instances_range( base_date
, repeatrule
, mindate
, maxdate
, 60 ) d
LIMIT 1;
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
;
1961 -- Name: rrule_month_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
1964 CREATE FUNCTION rrule_month_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
1965 LANGUAGE plpgsql
IMMUTABLE
1968 in_time
ALIAS FOR $
1;
1975 each_day
TIMESTAMP WITH TIME ZONE;
1977 results
TIMESTAMP WITH TIME ZONE[];
1980 IF byday
IS NULL THEN
1981 -- We still return the single date as a SET
1982 RETURN NEXT in_time
;
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
);
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;
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;
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;
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;
2013 -- Note that since index is negative, (-2 + 1) == -1, for example
2016 each_day
:= each_day
+ (index::text ||
' weeks')::interval ;
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;
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;
2036 dayrule
:= byday
[i
];
2040 IF results
[i
] IS NOT NULL THEN
2041 RETURN NEXT results
[i
];
2051 ALTER FUNCTION public.
rrule_month_byday_set(timestamp with time zone, text[]) OWNER TO postgres
;
2054 -- Name: rrule_month_bymonthday_set(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2057 CREATE FUNCTION rrule_month_bymonthday_set(timestamp with time zone, integer[]) RETURNS SETOF timestamp with time zone
2058 LANGUAGE plpgsql
IMMUTABLE STRICT
2061 in_time
ALIAS FOR $
1;
2062 bymonthday
ALIAS FOR $
2;
2063 month_start
TIMESTAMP WITH TIME ZONE;
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' );
2072 EXIT
WHEN bymonthday
[i
] IS NULL;
2074 CONTINUE WHEN bymonthday
[i
] > daysinmonth
;
2075 CONTINUE WHEN bymonthday
[i
] < (-1 * daysinmonth
);
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;
2082 RAISE NOTICE
'Ignored invalid BYMONTHDAY part "%".', bymonthday
[i
];
2092 ALTER FUNCTION public.
rrule_month_bymonthday_set(timestamp with time zone, integer[]) OWNER TO postgres
;
2095 -- Name: rrule_week_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2098 CREATE FUNCTION rrule_week_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
2099 LANGUAGE plpgsql
IMMUTABLE
2102 in_time
ALIAS FOR $
1;
2106 our_day
TIMESTAMP WITH TIME ZONE;
2110 IF byday
IS NULL THEN
2111 -- We still return the single date as a SET
2112 RETURN NEXT in_time
;
2116 our_day
:= date_trunc( 'week', in_time
) + (in_time
::time)::interval;
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;
2124 dayrule
:= byday
[i
];
2133 ALTER FUNCTION public.
rrule_week_byday_set(timestamp with time zone, text[]) OWNER TO postgres
;
2136 -- Name: rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2139 CREATE FUNCTION rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts
) RETURNS SETOF timestamp with time zone
2140 LANGUAGE plpgsql
IMMUTABLE STRICT
2145 current_base
TIMESTAMP WITH TIME ZONE;
2150 IF rrule.bymonth
IS NOT NULL THEN
2151 -- Ensure we don't pass BYSETPOS down
2153 rr.bysetpos
:= NULL;
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
;
2160 -- We don't yet implement byweekno, byblah
2168 ALTER FUNCTION public.
rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts
) OWNER TO postgres
;
2171 -- Name: set_dav_property(text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres
2174 CREATE FUNCTION set_dav_property(text, integer, text, text) RETURNS boolean
2175 LANGUAGE plpgsql
STRICT
2179 change_user
ALIAS FOR $
2;
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
2191 PERFORM
true FROM property
WHERE dav_name
= path AND property_name
= key;
2193 UPDATE property
SET changed_by
=change_user
::integer, changed_on
=current_timestamp, property_value
=value WHERE dav_name
= path AND property_name
= key;
2195 INSERT INTO property ( dav_name
, changed_by
, changed_on
, property_name
, property_value
) VALUES( path, change_user
::integer, current_timestamp, key, value );
2202 ALTER FUNCTION public.
set_dav_property(text, integer, text, text) OWNER TO postgres
;
2205 -- Name: sync_dav_id(); Type: FUNCTION; Schema: public; Owner: postgres
2208 CREATE FUNCTION sync_dav_id() RETURNS trigger
2214 IF TG_OP
= 'DELETE' THEN
2215 -- Just let the ON DELETE CASCADE handle this case
2219 IF NEW.dav_id
IS NULL THEN
2220 NEW.dav_id
= nextval('dav_id_seq');
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
;
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
;
2243 ALTER FUNCTION public.
sync_dav_id() OWNER TO postgres
;
2246 -- Name: test_byday_rule(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2249 CREATE FUNCTION test_byday_rule(timestamp with time zone, text[]) RETURNS boolean
2250 LANGUAGE plpgsql
IMMUTABLE
2253 testme
ALIAS FOR $
1;
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
) );
2266 ALTER FUNCTION public.
test_byday_rule(timestamp with time zone, text[]) OWNER TO postgres
;
2269 -- Name: test_bymonth_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2272 CREATE FUNCTION test_bymonth_rule(timestamp with time zone, integer[]) RETURNS boolean
2273 LANGUAGE plpgsql
IMMUTABLE
2276 testme
ALIAS FOR $
1;
2277 bymonth
ALIAS FOR $
2;
2279 IF bymonth
IS NOT NULL THEN
2280 RETURN ( date_part( 'month', testme
) = ANY (bymonth
) );
2287 ALTER FUNCTION public.
test_bymonth_rule(timestamp with time zone, integer[]) OWNER TO postgres
;
2290 -- Name: test_bymonthday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2293 CREATE FUNCTION test_bymonthday_rule(timestamp with time zone, integer[]) RETURNS boolean
2294 LANGUAGE plpgsql
IMMUTABLE
2297 testme
ALIAS FOR $
1;
2298 bymonthday
ALIAS FOR $
2;
2300 IF bymonthday
IS NOT NULL THEN
2301 RETURN ( date_part( 'day', testme
) = ANY (bymonthday
) );
2308 ALTER FUNCTION public.
test_bymonthday_rule(timestamp with time zone, integer[]) OWNER TO postgres
;
2311 -- Name: test_byyearday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2314 CREATE FUNCTION test_byyearday_rule(timestamp with time zone, integer[]) RETURNS boolean
2315 LANGUAGE plpgsql
IMMUTABLE
2318 testme
ALIAS FOR $
1;
2319 byyearday
ALIAS FOR $
2;
2321 IF byyearday
IS NOT NULL THEN
2322 RETURN ( date_part( 'doy', testme
) = ANY (byyearday
) );
2329 ALTER FUNCTION public.
test_byyearday_rule(timestamp with time zone, integer[]) OWNER TO postgres
;
2332 -- Name: to_ical_utc(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
2335 CREATE FUNCTION to_ical_utc(timestamp with time zone) RETURNS text
2336 LANGUAGE sql IMMUTABLE STRICT
2338 SELECT to_char( $
1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' )
2342 ALTER FUNCTION public.
to_ical_utc(timestamp with time zone) OWNER TO postgres
;
2345 -- Name: uprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
2348 CREATE FUNCTION uprivs(bigint, bigint, integer) RETURNS bit
2349 LANGUAGE plpgsql
STABLE STRICT
2352 in_accessor
ALIAS FOR $
1;
2353 in_grantor
ALIAS FOR $
2;
2354 in_depth
ALIAS FOR $
3;
2355 out_conferred
BIT(24);
2357 out_conferred
:= 0::BIT(24);
2358 -- Self can always have full access
2359 IF in_grantor
= in_accessor
THEN
2360 RETURN ~ out_conferred
;
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
;
2367 RETURN out_conferred
;
2372 ALTER FUNCTION public.
uprivs(bigint, bigint, integer) OWNER TO postgres
;
2375 -- Name: usr_is_role(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2378 CREATE FUNCTION usr_is_role(integer, text) RETURNS boolean
2379 LANGUAGE sql IMMUTABLE STRICT
2381 SELECT EXISTS( SELECT 1 FROM role_member
JOIN roles
USING(role_no
) WHERE role_member.user_no
=$
1 AND roles.role_name
=$
2 )
2385 ALTER FUNCTION public.
usr_is_role(integer, text) OWNER TO postgres
;
2388 -- Name: usr_modified(); Type: FUNCTION; Schema: public; Owner: postgres
2391 CREATE FUNCTION usr_modified() RETURNS trigger
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 ||
'/';
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
;
2414 ALTER FUNCTION public.
usr_modified() OWNER TO postgres
;
2417 -- Name: weekly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2420 CREATE FUNCTION weekly_set(timestamp with time zone, rrule_parts
) RETURNS SETOF timestamp with time zone
2421 LANGUAGE plpgsql
IMMUTABLE STRICT
2426 valid_date
TIMESTAMP WITH TIME ZONE;
2432 IF rrule.byweekno
IS NOT NULL THEN
2433 weekno
:= date_part('week',after);
2434 IF NOT weekno
= ANY ( rrule.byweekno
) THEN
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
;
2446 ALTER FUNCTION public.
weekly_set(timestamp with time zone, rrule_parts
) OWNER TO postgres
;
2449 -- Name: write_sync_change(bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2452 CREATE FUNCTION write_sync_change(bigint, integer, text) RETURNS boolean
2453 LANGUAGE plpgsql
STRICT
2456 in_collection_id
ALIAS FOR $
1;
2457 in_status
ALIAS FOR $
2;
2458 in_dav_name
ALIAS FOR $
3;
2461 SELECT 1 INTO tmp_int
FROM sync_tokens
2462 WHERE collection_id
= in_collection_id
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
);
2475 ALTER FUNCTION public.
write_sync_change(bigint, integer, text) OWNER TO postgres
;
2478 -- Name: yearly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2481 CREATE FUNCTION yearly_set(timestamp with time zone, rrule_parts
) RETURNS SETOF timestamp with time zone
2482 LANGUAGE plpgsql
IMMUTABLE STRICT
2487 current_base
TIMESTAMP WITH TIME ZONE;
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
;
2500 -- We don't yet implement byweekno, byblah
2507 ALTER FUNCTION public.
yearly_set(timestamp with time zone, rrule_parts
) OWNER TO postgres
;
2509 SET default_tablespace
= '';
2511 SET default_with_oids
= false;
2514 -- Name: access_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2517 CREATE TABLE access_ticket (
2518 ticket_id
text NOT NULL,
2519 dav_owner_id
bigint NOT NULL,
2521 target_collection_id
bigint NOT NULL,
2522 target_resource_id
bigint,
2523 expires
timestamp without time zone
2527 ALTER TABLE public.access_ticket
OWNER TO postgres
;
2530 -- Name: addressbook_address_adr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2533 CREATE TABLE addressbook_address_adr (
2534 dav_id
bigint NOT NULL,
2538 street_address
text,
2547 ALTER TABLE public.addressbook_address_adr
OWNER TO postgres
;
2550 -- Name: addressbook_address_email; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2553 CREATE TABLE addressbook_address_email (
2554 dav_id
bigint NOT NULL,
2561 ALTER TABLE public.addressbook_address_email
OWNER TO postgres
;
2564 -- Name: addressbook_address_tel; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2567 CREATE TABLE addressbook_address_tel (
2568 dav_id
bigint NOT NULL,
2575 ALTER TABLE public.addressbook_address_tel
OWNER TO postgres
;
2578 -- Name: addressbook_resource; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2581 CREATE TABLE addressbook_resource (
2582 dav_id
bigint NOT NULL,
2597 ALTER TABLE public.addressbook_resource
OWNER TO postgres
;
2600 -- Name: awl_db_revision; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2603 CREATE TABLE awl_db_revision (
2605 schema_major
integer,
2606 schema_minor
integer,
2607 schema_patch
integer,
2609 applied_on
timestamp with time zone DEFAULT now()
2613 ALTER TABLE public.awl_db_revision
OWNER TO postgres
;
2616 -- Name: dav_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2619 CREATE SEQUENCE dav_id_seq
2627 ALTER TABLE public.dav_id_seq
OWNER TO postgres
;
2630 -- Name: caldav_data; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2633 CREATE TABLE caldav_data (
2634 user_no
integer NOT NULL,
2635 dav_name
text NOT NULL,
2637 created
timestamp with time zone,
2638 modified
timestamp with time zone,
2641 logged_user
integer,
2642 dav_id
bigint DEFAULT nextval('dav_id_seq'::regclass),
2643 collection_id
bigint,
2648 ALTER TABLE public.caldav_data
OWNER TO postgres
;
2651 -- Name: calendar_alarm; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2654 CREATE TABLE calendar_alarm (
2655 dav_id
bigint NOT NULL,
2660 next_trigger
timestamp with time zone,
2662 trigger_state
character(1) DEFAULT 'N'::bpchar
2666 ALTER TABLE public.calendar_alarm
OWNER TO postgres
;
2669 -- Name: calendar_attendee; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2672 CREATE TABLE calendar_attendee (
2673 dav_id
bigint NOT NULL,
2677 attendee
text NOT NULL,
2681 attendee_state
text,
2686 ALTER TABLE public.calendar_attendee
OWNER TO postgres
;
2689 -- Name: calendar_item; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2692 CREATE TABLE calendar_item (
2693 user_no
integer NOT NULL,
2694 dav_name
text NOT NULL,
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,
2711 percent_complete
numeric(7,2),
2714 completed
timestamp with time zone,
2716 collection_id
bigint,
2717 first_instance_start
timestamp without time zone,
2718 last_instance_end
timestamp without time zone
2722 ALTER TABLE public.calendar_item
OWNER TO postgres
;
2725 -- Name: collection; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2728 CREATE TABLE collection (
2730 parent_container
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,
2745 description
text DEFAULT ''::text
2749 ALTER TABLE public.collection
OWNER TO postgres
;
2752 -- Name: dav_binding; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
2765 CONSTRAINT dav_name_does_not_exist
CHECK ((NOT real_path_exists(dav_name
)))
2769 ALTER TABLE public.dav_binding
OWNER TO postgres
;
2772 -- Name: principal; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2775 CREATE TABLE principal (
2776 principal_id
bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2777 type_id
bigint NOT NULL,
2780 default_privileges
bit(24)
2784 ALTER TABLE public.principal
OWNER TO postgres
;
2787 -- Name: usr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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,
2802 date_format_type
text DEFAULT 'E'::text,
2807 ALTER TABLE public.usr
OWNER TO postgres
;
2810 -- Name: dav_principal; Type: VIEW; Schema: public; Owner: postgres
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
));
2817 ALTER TABLE public.dav_principal
OWNER TO postgres
;
2820 -- Name: freebusy_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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
2830 ALTER TABLE public.freebusy_ticket
OWNER TO postgres
;
2833 -- Name: grants; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2836 CREATE TABLE grants (
2837 by_principal
bigint,
2838 by_collection
bigint,
2839 to_principal
bigint,
2845 ALTER TABLE public.grants
OWNER TO postgres
;
2848 -- Name: group_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2851 CREATE TABLE group_member (
2857 ALTER TABLE public.group_member
OWNER TO postgres
;
2860 -- Name: locks; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2863 CREATE TABLE locks (
2865 opaquelocktoken
text NOT NULL,
2871 start timestamp without time zone DEFAULT now()
2875 ALTER TABLE public.locks
OWNER TO postgres
;
2878 -- Name: principal_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2881 CREATE TABLE principal_type (
2882 principal_type_id
integer NOT NULL,
2883 principal_type_desc
text
2887 ALTER TABLE public.principal_type
OWNER TO postgres
;
2890 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2893 CREATE SEQUENCE principal_type_principal_type_id_seq
2901 ALTER TABLE public.principal_type_principal_type_id_seq
OWNER TO postgres
;
2904 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2907 ALTER SEQUENCE principal_type_principal_type_id_seq
OWNED BY principal_type.principal_type_id
;
2911 -- Name: property; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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(),
2923 ALTER TABLE public.property
OWNER TO postgres
;
2926 -- Name: relationship; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
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])
2937 ALTER TABLE public.relationship
OWNER TO postgres
;
2940 -- Name: relationship_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2943 CREATE TABLE relationship_type (
2944 rt_id
integer NOT NULL,
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])
2953 ALTER TABLE public.relationship_type
OWNER TO postgres
;
2956 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2959 CREATE SEQUENCE relationship_type_rt_id_seq
2967 ALTER TABLE public.relationship_type_rt_id_seq
OWNER TO postgres
;
2970 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2973 ALTER SEQUENCE relationship_type_rt_id_seq
OWNED BY relationship_type.rt_id
;
2977 -- Name: role_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2980 CREATE TABLE role_member (
2986 ALTER TABLE public.role_member
OWNER TO postgres
;
2989 -- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2992 CREATE TABLE roles (
2993 role_no
integer NOT NULL,
2998 ALTER TABLE public.roles
OWNER TO postgres
;
3001 -- Name: roles_role_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3004 CREATE SEQUENCE roles_role_no_seq
3012 ALTER TABLE public.roles_role_no_seq
OWNER TO postgres
;
3015 -- Name: roles_role_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3018 ALTER SEQUENCE roles_role_no_seq
OWNED BY roles.role_no
;
3022 -- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3025 CREATE TABLE session (
3026 session_id
integer NOT NULL,
3028 session_start
timestamp with time zone DEFAULT now(),
3029 session_end
timestamp with time zone DEFAULT now(),
3035 ALTER TABLE public.
session OWNER TO postgres
;
3038 -- Name: session_session_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3041 CREATE SEQUENCE session_session_id_seq
3049 ALTER TABLE public.session_session_id_seq
OWNER TO postgres
;
3052 -- Name: session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3055 ALTER SEQUENCE session_session_id_seq
OWNED BY session.session_id
;
3059 -- Name: supported_locales; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3062 CREATE TABLE supported_locales (
3063 locale
text NOT NULL,
3064 locale_name_en
text,
3065 locale_name_locale
text
3069 ALTER TABLE public.supported_locales
OWNER TO postgres
;
3072 -- Name: sync_changes; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3075 CREATE TABLE sync_changes (
3076 sync_time
timestamp with time zone DEFAULT now(),
3077 collection_id
bigint,
3078 sync_status
integer,
3084 ALTER TABLE public.sync_changes
OWNER TO postgres
;
3087 -- Name: sync_tokens; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3090 CREATE TABLE sync_tokens (
3091 sync_token
integer NOT NULL,
3092 collection_id
bigint,
3093 modification_time
timestamp with time zone DEFAULT now()
3097 ALTER TABLE public.sync_tokens
OWNER TO postgres
;
3100 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3103 CREATE SEQUENCE sync_tokens_sync_token_seq
3111 ALTER TABLE public.sync_tokens_sync_token_seq
OWNER TO postgres
;
3114 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3117 ALTER SEQUENCE sync_tokens_sync_token_seq
OWNED BY sync_tokens.sync_token
;
3121 -- Name: timezones; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3124 CREATE TABLE timezones (
3125 our_tzno
integer NOT NULL,
3129 last_modified
timestamp without time zone DEFAULT now(),
3135 ALTER TABLE public.timezones
OWNER TO postgres
;
3138 -- Name: timezones_our_tzno_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3141 CREATE SEQUENCE timezones_our_tzno_seq
3149 ALTER TABLE public.timezones_our_tzno_seq
OWNER TO postgres
;
3152 -- Name: timezones_our_tzno_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3155 ALTER SEQUENCE timezones_our_tzno_seq
OWNED BY timezones.our_tzno
;
3159 -- Name: tmp_password; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3162 CREATE TABLE tmp_password (
3165 valid_until
timestamp with time zone DEFAULT (now() + '1 day'::interval)
3169 ALTER TABLE public.tmp_password
OWNER TO postgres
;
3172 -- Name: tz_aliases; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3175 CREATE TABLE tz_aliases (
3177 tzalias
text NOT NULL
3181 ALTER TABLE public.tz_aliases
OWNER TO postgres
;
3184 -- Name: tz_localnames; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3187 CREATE TABLE tz_localnames (
3189 locale
text NOT NULL,
3190 localised_name
text NOT NULL,
3191 preferred
boolean DEFAULT true
3195 ALTER TABLE public.tz_localnames
OWNER TO postgres
;
3198 -- Name: usr_setting; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3201 CREATE TABLE usr_setting (
3202 user_no
integer NOT NULL,
3203 setting_name
text NOT NULL,
3208 ALTER TABLE public.usr_setting
OWNER TO postgres
;
3211 -- Name: usr_user_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3214 CREATE SEQUENCE usr_user_no_seq
3222 ALTER TABLE public.usr_user_no_seq
OWNER TO postgres
;
3225 -- Name: usr_user_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3228 ALTER SEQUENCE usr_user_no_seq
OWNED BY usr.user_no
;
3232 -- Name: principal_type_id; Type: DEFAULT; Schema: public; Owner: postgres
3235 ALTER TABLE ONLY principal_type
ALTER COLUMN principal_type_id
SET DEFAULT nextval('principal_type_principal_type_id_seq'::regclass);
3239 -- Name: rt_id; Type: DEFAULT; Schema: public; Owner: postgres
3242 ALTER TABLE ONLY relationship_type
ALTER COLUMN rt_id
SET DEFAULT nextval('relationship_type_rt_id_seq'::regclass);
3246 -- Name: role_no; Type: DEFAULT; Schema: public; Owner: postgres
3249 ALTER TABLE ONLY roles
ALTER COLUMN role_no
SET DEFAULT nextval('roles_role_no_seq'::regclass);
3253 -- Name: session_id; Type: DEFAULT; Schema: public; Owner: postgres
3256 ALTER TABLE ONLY session ALTER COLUMN session_id
SET DEFAULT nextval('session_session_id_seq'::regclass);
3260 -- Name: sync_token; Type: DEFAULT; Schema: public; Owner: postgres
3263 ALTER TABLE ONLY sync_tokens
ALTER COLUMN sync_token
SET DEFAULT nextval('sync_tokens_sync_token_seq'::regclass);
3267 -- Name: our_tzno; Type: DEFAULT; Schema: public; Owner: postgres
3270 ALTER TABLE ONLY timezones
ALTER COLUMN our_tzno
SET DEFAULT nextval('timezones_our_tzno_seq'::regclass);
3274 -- Name: user_no; Type: DEFAULT; Schema: public; Owner: postgres
3277 ALTER TABLE ONLY usr
ALTER COLUMN user_no
SET DEFAULT nextval('usr_user_no_seq'::regclass);
3281 -- Name: access_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3284 ALTER TABLE ONLY access_ticket
3285 ADD CONSTRAINT access_ticket_pkey
PRIMARY KEY (ticket_id
);
3289 -- Name: addressbook_resource_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3292 ALTER TABLE ONLY addressbook_resource
3293 ADD CONSTRAINT addressbook_resource_pkey
PRIMARY KEY (dav_id
);
3297 -- Name: caldav_data_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3300 ALTER TABLE ONLY caldav_data
3301 ADD CONSTRAINT caldav_data_dav_id_key
UNIQUE (dav_id
);
3305 -- Name: caldav_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3308 ALTER TABLE ONLY caldav_data
3309 ADD CONSTRAINT caldav_data_pkey
PRIMARY KEY (user_no
, dav_name
);
3313 -- Name: calendar_attendee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3316 ALTER TABLE ONLY calendar_attendee
3317 ADD CONSTRAINT calendar_attendee_pkey
PRIMARY KEY (dav_id
, attendee
);
3321 -- Name: calendar_item_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3324 ALTER TABLE ONLY calendar_item
3325 ADD CONSTRAINT calendar_item_dav_id_key
UNIQUE (dav_id
);
3329 -- Name: calendar_item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3332 ALTER TABLE ONLY calendar_item
3333 ADD CONSTRAINT calendar_item_pkey
PRIMARY KEY (user_no
, dav_name
);
3337 -- Name: collection_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3340 ALTER TABLE ONLY collection
3341 ADD CONSTRAINT collection_pkey
PRIMARY KEY (collection_id
);
3345 -- Name: collection_user_no_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3348 ALTER TABLE ONLY collection
3349 ADD CONSTRAINT collection_user_no_dav_name_key
UNIQUE (user_no
, dav_name
);
3353 -- Name: dav_binding_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3356 ALTER TABLE ONLY dav_binding
3357 ADD CONSTRAINT dav_binding_dav_name_key
UNIQUE (dav_name
);
3361 -- Name: dav_binding_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3364 ALTER TABLE ONLY dav_binding
3365 ADD CONSTRAINT dav_binding_pkey
PRIMARY KEY (bind_id
);
3369 -- Name: freebusy_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3372 ALTER TABLE ONLY freebusy_ticket
3373 ADD CONSTRAINT freebusy_ticket_pkey
PRIMARY KEY (ticket_id
);
3377 -- Name: locks_opaquelocktoken_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3380 ALTER TABLE ONLY locks
3381 ADD CONSTRAINT locks_opaquelocktoken_key
UNIQUE (opaquelocktoken
);
3385 -- Name: principal_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3388 ALTER TABLE ONLY principal
3389 ADD CONSTRAINT principal_pkey
PRIMARY KEY (principal_id
);
3393 -- Name: principal_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3396 ALTER TABLE ONLY principal_type
3397 ADD CONSTRAINT principal_type_pkey
PRIMARY KEY (principal_type_id
);
3401 -- Name: property_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3404 ALTER TABLE ONLY property
3405 ADD CONSTRAINT property_pkey
PRIMARY KEY (dav_name
, property_name
);
3409 -- Name: relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3412 ALTER TABLE ONLY relationship
3413 ADD CONSTRAINT relationship_pkey
PRIMARY KEY (from_user
, to_user
, rt_id
);
3417 -- Name: relationship_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3420 ALTER TABLE ONLY relationship_type
3421 ADD CONSTRAINT relationship_type_pkey
PRIMARY KEY (rt_id
);
3425 -- Name: roles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3428 ALTER TABLE ONLY roles
3429 ADD CONSTRAINT roles_pkey
PRIMARY KEY (role_no
);
3433 -- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3436 ALTER TABLE ONLY session
3437 ADD CONSTRAINT session_pkey
PRIMARY KEY (session_id
);
3441 -- Name: supported_locales_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3444 ALTER TABLE ONLY supported_locales
3445 ADD CONSTRAINT supported_locales_pkey
PRIMARY KEY (locale
);
3449 -- Name: sync_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3452 ALTER TABLE ONLY sync_tokens
3453 ADD CONSTRAINT sync_tokens_pkey
PRIMARY KEY (sync_token
);
3457 -- Name: timezones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3460 ALTER TABLE ONLY timezones
3461 ADD CONSTRAINT timezones_pkey
PRIMARY KEY (our_tzno
);
3465 -- Name: timezones_tzid_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3468 ALTER TABLE ONLY timezones
3469 ADD CONSTRAINT timezones_tzid_key
UNIQUE (tzid
);
3473 -- Name: unique_path; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3476 ALTER TABLE ONLY collection
3477 ADD CONSTRAINT unique_path
UNIQUE (dav_name
);
3481 -- Name: unique_user; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3484 ALTER TABLE ONLY principal
3485 ADD CONSTRAINT unique_user
UNIQUE (user_no
);
3489 -- Name: usr_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3492 ALTER TABLE ONLY usr
3493 ADD CONSTRAINT usr_pkey
PRIMARY KEY (user_no
);
3497 -- Name: usr_setting_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3500 ALTER TABLE ONLY usr_setting
3501 ADD CONSTRAINT usr_setting_pkey
PRIMARY KEY (user_no
, setting_name
);
3505 -- Name: caldav_data_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3508 CREATE INDEX caldav_data_collection_id_fkey
ON caldav_data
USING btree (collection_id
);
3512 -- Name: calendar_item_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3515 CREATE INDEX calendar_item_collection_id_fkey
ON calendar_item
USING btree (collection_id
);
3519 -- Name: grants_pk1; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3522 CREATE UNIQUE INDEX grants_pk1
ON grants
USING btree (by_principal
, to_principal
);
3526 -- Name: grants_pk2; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3529 CREATE UNIQUE INDEX grants_pk2
ON grants
USING btree (by_collection
, to_principal
);
3533 -- Name: group_member_pk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3536 CREATE UNIQUE INDEX group_member_pk
ON group_member
USING btree (group_id
, member_id
);
3540 -- Name: group_member_sk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3543 CREATE INDEX group_member_sk
ON group_member
USING btree (member_id
);
3547 -- Name: locks_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3550 CREATE INDEX locks_dav_name_idx
ON locks
USING btree (dav_name
);
3554 -- Name: properties_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3557 CREATE INDEX properties_dav_name_idx
ON property
USING btree (dav_name
);
3561 -- Name: sync_processing_index; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3564 CREATE INDEX sync_processing_index
ON sync_changes
USING btree (collection_id
, dav_id
, sync_time
);
3568 -- Name: usr_sk1_unique_username; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3571 CREATE UNIQUE INDEX usr_sk1_unique_username
ON usr
USING btree (lower(username
));
3575 -- Name: dav_principal_delete; Type: RULE; Schema: public; Owner: postgres
3578 CREATE RULE dav_principal_delete
AS ON DELETE TO dav_principal
DO INSTEAD (DELETE FROM usr
WHERE (usr.user_no
= old.user_no
); DELETE FROM principal
WHERE (principal.principal_id
= old.principal_id
); );
3582 -- Name: dav_principal_insert; Type: RULE; Schema: public; Owner: postgres
3585 CREATE RULE dav_principal_insert
AS ON INSERT TO dav_principal
DO INSTEAD (INSERT INTO usr (user_no
, active
, joined
, updated
, username
, password, fullname
, email
, email_ok
, date_format_type
, locale
) VALUES (COALESCE(new.user_no
, nextval('usr_user_no_seq'::regclass)), COALESCE(new.user_active
, true), COALESCE(new.created
, now()), COALESCE(new.modified
, now()), new.username
, new.
password, COALESCE(new.fullname
, new.displayname
), new.email
, new.email_ok
, COALESCE(new.date_format_type
, 'E'::text), new.locale
); INSERT INTO principal (user_no
, principal_id
, type_id
, displayname
, default_privileges
) VALUES (COALESCE(new.user_no
, currval('usr_user_no_seq'::regclass)), COALESCE(new.principal_id
, nextval('dav_id_seq'::regclass)), new.type_id
, COALESCE(new.displayname
, new.fullname
), COALESCE(new.default_privileges
, (0)::bit(24))); );
3589 -- Name: dav_principal_update; Type: RULE; Schema: public; Owner: postgres
3592 CREATE RULE dav_principal_update
AS ON UPDATE TO dav_principal
DO INSTEAD (UPDATE usr
SET user_no
= new.user_no
, active
= new.user_active
, updated
= now(), username
= new.username
, password = new.
password, fullname
= new.fullname
, email
= new.email
, email_ok
= new.email_ok
, date_format_type
= new.date_format_type
, locale
= new.locale
WHERE (usr.user_no
= old.user_no
); UPDATE principal
SET principal_id
= new.principal_id
, type_id
= new.type_id
, displayname
= new.displayname
, default_privileges
= new.default_privileges
WHERE (principal.principal_id
= old.principal_id
); );
3596 -- Name: alarm_changed; Type: TRIGGER; Schema: public; Owner: postgres
3599 CREATE TRIGGER alarm_changed
AFTER UPDATE ON calendar_alarm
FOR EACH ROW EXECUTE PROCEDURE alarm_changed();
3603 -- Name: caldav_data_modified; Type: TRIGGER; Schema: public; Owner: postgres
3606 CREATE TRIGGER caldav_data_modified
AFTER INSERT OR DELETE OR UPDATE ON caldav_data
FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified();
3610 -- Name: caldav_data_sync_dav_id; Type: TRIGGER; Schema: public; Owner: postgres
3613 CREATE TRIGGER caldav_data_sync_dav_id
AFTER INSERT OR UPDATE ON caldav_data
FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
3617 -- Name: collection_modified; Type: TRIGGER; Schema: public; Owner: postgres
3620 CREATE TRIGGER collection_modified
AFTER UPDATE ON collection
FOR EACH ROW EXECUTE PROCEDURE collection_modified();
3624 -- Name: grants_modified; Type: TRIGGER; Schema: public; Owner: postgres
3627 CREATE TRIGGER grants_modified
AFTER INSERT OR UPDATE ON grants
FOR EACH ROW EXECUTE PROCEDURE grants_modified();
3631 -- Name: principal_modified; Type: TRIGGER; Schema: public; Owner: postgres
3634 CREATE TRIGGER principal_modified
AFTER UPDATE ON principal
FOR EACH ROW EXECUTE PROCEDURE principal_modified();
3638 -- Name: usr_modified; Type: TRIGGER; Schema: public; Owner: postgres
3641 CREATE TRIGGER usr_modified
AFTER UPDATE ON usr
FOR EACH ROW EXECUTE PROCEDURE usr_modified();
3645 -- Name: access_ticket_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3648 ALTER TABLE ONLY access_ticket
3649 ADD CONSTRAINT access_ticket_dav_owner_id_fkey
FOREIGN KEY (dav_owner_id
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3653 -- Name: access_ticket_target_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3656 ALTER TABLE ONLY access_ticket
3657 ADD CONSTRAINT access_ticket_target_collection_id_fkey
FOREIGN KEY (target_collection_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3661 -- Name: access_ticket_target_resource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3664 ALTER TABLE ONLY access_ticket
3665 ADD CONSTRAINT access_ticket_target_resource_id_fkey
FOREIGN KEY (target_resource_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3669 -- Name: addressbook_address_adr_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3672 ALTER TABLE ONLY addressbook_address_adr
3673 ADD CONSTRAINT addressbook_address_adr_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3677 -- Name: addressbook_address_email_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3680 ALTER TABLE ONLY addressbook_address_email
3681 ADD CONSTRAINT addressbook_address_email_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3685 -- Name: addressbook_address_tel_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3688 ALTER TABLE ONLY addressbook_address_tel
3689 ADD CONSTRAINT addressbook_address_tel_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3693 -- Name: addressbook_resource_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3696 ALTER TABLE ONLY addressbook_resource
3697 ADD CONSTRAINT addressbook_resource_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3701 -- Name: caldav_data_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3704 ALTER TABLE ONLY caldav_data
3705 ADD CONSTRAINT caldav_data_collection_id_fkey
FOREIGN KEY (collection_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3709 -- Name: caldav_data_logged_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3712 ALTER TABLE ONLY caldav_data
3713 ADD CONSTRAINT caldav_data_logged_user_fkey
FOREIGN KEY (logged_user
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE;
3717 -- Name: caldav_data_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3720 ALTER TABLE ONLY caldav_data
3721 ADD CONSTRAINT caldav_data_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3725 -- Name: caldav_exists; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3728 ALTER TABLE ONLY calendar_item
3729 ADD CONSTRAINT caldav_exists
FOREIGN KEY (user_no
, dav_name
) REFERENCES caldav_data(user_no
, dav_name
) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3733 -- Name: calendar_alarm_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3736 ALTER TABLE ONLY calendar_alarm
3737 ADD CONSTRAINT calendar_alarm_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3741 -- Name: calendar_attendee_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3744 ALTER TABLE ONLY calendar_attendee
3745 ADD CONSTRAINT calendar_attendee_dav_id_fkey
FOREIGN KEY (dav_id
) REFERENCES caldav_data(dav_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3749 -- Name: calendar_item_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3752 ALTER TABLE ONLY calendar_item
3753 ADD CONSTRAINT calendar_item_collection_id_fkey
FOREIGN KEY (collection_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3757 -- Name: calendar_item_tz_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3760 ALTER TABLE ONLY calendar_item
3761 ADD CONSTRAINT calendar_item_tz_id_fkey
FOREIGN KEY (tz_id
) REFERENCES timezones(tzid
);
3765 -- Name: calendar_item_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3768 ALTER TABLE ONLY calendar_item
3769 ADD CONSTRAINT calendar_item_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3773 -- Name: collection_timezone_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3776 ALTER TABLE ONLY collection
3777 ADD CONSTRAINT collection_timezone_fkey
FOREIGN KEY (timezone
) REFERENCES timezones(tzid
) ON UPDATE CASCADE ON DELETE SET NULL;
3781 -- Name: collection_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3784 ALTER TABLE ONLY collection
3785 ADD CONSTRAINT collection_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3789 -- Name: dav_binding_access_ticket_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3792 ALTER TABLE ONLY dav_binding
3793 ADD CONSTRAINT dav_binding_access_ticket_id_fkey
FOREIGN KEY (access_ticket_id
) REFERENCES access_ticket(ticket_id
) ON UPDATE CASCADE ON DELETE SET NULL;
3797 -- Name: dav_binding_bound_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3800 ALTER TABLE ONLY dav_binding
3801 ADD CONSTRAINT dav_binding_bound_source_id_fkey
FOREIGN KEY (bound_source_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3805 -- Name: dav_binding_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3808 ALTER TABLE ONLY dav_binding
3809 ADD CONSTRAINT dav_binding_dav_owner_id_fkey
FOREIGN KEY (dav_owner_id
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3813 -- Name: freebusy_ticket_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3816 ALTER TABLE ONLY freebusy_ticket
3817 ADD CONSTRAINT freebusy_ticket_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE;
3821 -- Name: grants_by_collection_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3824 ALTER TABLE ONLY grants
3825 ADD CONSTRAINT grants_by_collection_fkey
FOREIGN KEY (by_collection
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3829 -- Name: grants_by_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3832 ALTER TABLE ONLY grants
3833 ADD CONSTRAINT grants_by_principal_fkey
FOREIGN KEY (by_principal
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3837 -- Name: grants_to_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3840 ALTER TABLE ONLY grants
3841 ADD CONSTRAINT grants_to_principal_fkey
FOREIGN KEY (to_principal
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3845 -- Name: group_member_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3848 ALTER TABLE ONLY group_member
3849 ADD CONSTRAINT group_member_group_id_fkey
FOREIGN KEY (group_id
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3853 -- Name: group_member_member_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3856 ALTER TABLE ONLY group_member
3857 ADD CONSTRAINT group_member_member_id_fkey
FOREIGN KEY (member_id
) REFERENCES principal(principal_id
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3861 -- Name: principal_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3864 ALTER TABLE ONLY principal
3865 ADD CONSTRAINT principal_type_id_fkey
FOREIGN KEY (type_id
) REFERENCES principal_type(principal_type_id
) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE;
3869 -- Name: principal_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3872 ALTER TABLE ONLY principal
3873 ADD CONSTRAINT principal_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3877 -- Name: property_changed_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3880 ALTER TABLE ONLY property
3881 ADD CONSTRAINT property_changed_by_fkey
FOREIGN KEY (changed_by
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE SET DEFAULT;
3885 -- Name: relationship_from_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3888 ALTER TABLE ONLY relationship
3889 ADD CONSTRAINT relationship_from_user_fkey
FOREIGN KEY (from_user
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE;
3893 -- Name: relationship_rt_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3896 ALTER TABLE ONLY relationship
3897 ADD CONSTRAINT relationship_rt_id_fkey
FOREIGN KEY (rt_id
) REFERENCES relationship_type(rt_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3901 -- Name: relationship_to_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3904 ALTER TABLE ONLY relationship
3905 ADD CONSTRAINT relationship_to_user_fkey
FOREIGN KEY (to_user
) REFERENCES usr(user_no
) ON UPDATE CASCADE ON DELETE CASCADE;
3909 -- Name: role_member_role_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3912 ALTER TABLE ONLY role_member
3913 ADD CONSTRAINT role_member_role_no_fkey
FOREIGN KEY (role_no
) REFERENCES roles(role_no
);
3917 -- Name: role_member_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3920 ALTER TABLE ONLY role_member
3921 ADD CONSTRAINT role_member_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON DELETE CASCADE;
3925 -- Name: session_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3928 ALTER TABLE ONLY session
3929 ADD CONSTRAINT session_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON DELETE CASCADE;
3933 -- Name: sync_changes_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3936 ALTER TABLE ONLY sync_changes
3937 ADD CONSTRAINT sync_changes_collection_id_fkey
FOREIGN KEY (collection_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3941 -- Name: sync_tokens_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3944 ALTER TABLE ONLY sync_tokens
3945 ADD CONSTRAINT sync_tokens_collection_id_fkey
FOREIGN KEY (collection_id
) REFERENCES collection(collection_id
) ON UPDATE CASCADE ON DELETE CASCADE;
3949 -- Name: tmp_password_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3952 ALTER TABLE ONLY tmp_password
3953 ADD CONSTRAINT tmp_password_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
);
3957 -- Name: tz_aliases_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3960 ALTER TABLE ONLY tz_aliases
3961 ADD CONSTRAINT tz_aliases_our_tzno_fkey
FOREIGN KEY (our_tzno
) REFERENCES timezones(our_tzno
);
3965 -- Name: tz_localnames_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3968 ALTER TABLE ONLY tz_localnames
3969 ADD CONSTRAINT tz_localnames_our_tzno_fkey
FOREIGN KEY (our_tzno
) REFERENCES timezones(our_tzno
);
3973 -- Name: usr_setting_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3976 ALTER TABLE ONLY usr_setting
3977 ADD CONSTRAINT usr_setting_user_no_fkey
FOREIGN KEY (user_no
) REFERENCES usr(user_no
) ON DELETE CASCADE;
3981 -- Name: public; Type: ACL; Schema: -; Owner: postgres
3984 REVOKE ALL ON SCHEMA public FROM PUBLIC;
3985 REVOKE ALL ON SCHEMA public FROM postgres
;
3986 GRANT ALL ON SCHEMA public TO postgres
;
3987 GRANT ALL ON SCHEMA public TO PUBLIC;
3991 -- Name: access_ticket; Type: ACL; Schema: public; Owner: postgres
3994 REVOKE ALL ON TABLE access_ticket
FROM PUBLIC;
3995 REVOKE ALL ON TABLE access_ticket
FROM postgres
;
3996 GRANT ALL ON TABLE access_ticket
TO postgres
;
4000 -- Name: addressbook_address_adr; Type: ACL; Schema: public; Owner: postgres
4003 REVOKE ALL ON TABLE addressbook_address_adr
FROM PUBLIC;
4004 REVOKE ALL ON TABLE addressbook_address_adr
FROM postgres
;
4005 GRANT ALL ON TABLE addressbook_address_adr
TO postgres
;
4009 -- Name: addressbook_address_email; Type: ACL; Schema: public; Owner: postgres
4012 REVOKE ALL ON TABLE addressbook_address_email
FROM PUBLIC;
4013 REVOKE ALL ON TABLE addressbook_address_email
FROM postgres
;
4014 GRANT ALL ON TABLE addressbook_address_email
TO postgres
;
4018 -- Name: addressbook_address_tel; Type: ACL; Schema: public; Owner: postgres
4021 REVOKE ALL ON TABLE addressbook_address_tel
FROM PUBLIC;
4022 REVOKE ALL ON TABLE addressbook_address_tel
FROM postgres
;
4023 GRANT ALL ON TABLE addressbook_address_tel
TO postgres
;
4027 -- Name: addressbook_resource; Type: ACL; Schema: public; Owner: postgres
4030 REVOKE ALL ON TABLE addressbook_resource
FROM PUBLIC;
4031 REVOKE ALL ON TABLE addressbook_resource
FROM postgres
;
4032 GRANT ALL ON TABLE addressbook_resource
TO postgres
;
4036 -- Name: awl_db_revision; Type: ACL; Schema: public; Owner: postgres
4039 REVOKE ALL ON TABLE awl_db_revision
FROM PUBLIC;
4040 REVOKE ALL ON TABLE awl_db_revision
FROM postgres
;
4041 GRANT ALL ON TABLE awl_db_revision
TO postgres
;
4045 -- Name: dav_id_seq; Type: ACL; Schema: public; Owner: postgres
4048 REVOKE ALL ON SEQUENCE dav_id_seq
FROM PUBLIC;
4049 REVOKE ALL ON SEQUENCE dav_id_seq
FROM postgres
;
4050 GRANT ALL ON SEQUENCE dav_id_seq
TO postgres
;
4054 -- Name: caldav_data; Type: ACL; Schema: public; Owner: postgres
4057 REVOKE ALL ON TABLE caldav_data
FROM PUBLIC;
4058 REVOKE ALL ON TABLE caldav_data
FROM postgres
;
4059 GRANT ALL ON TABLE caldav_data
TO postgres
;
4063 -- Name: calendar_alarm; Type: ACL; Schema: public; Owner: postgres
4066 REVOKE ALL ON TABLE calendar_alarm
FROM PUBLIC;
4067 REVOKE ALL ON TABLE calendar_alarm
FROM postgres
;
4068 GRANT ALL ON TABLE calendar_alarm
TO postgres
;
4072 -- Name: calendar_attendee; Type: ACL; Schema: public; Owner: postgres
4075 REVOKE ALL ON TABLE calendar_attendee
FROM PUBLIC;
4076 REVOKE ALL ON TABLE calendar_attendee
FROM postgres
;
4077 GRANT ALL ON TABLE calendar_attendee
TO postgres
;
4081 -- Name: calendar_item; Type: ACL; Schema: public; Owner: postgres
4084 REVOKE ALL ON TABLE calendar_item
FROM PUBLIC;
4085 REVOKE ALL ON TABLE calendar_item
FROM postgres
;
4086 GRANT ALL ON TABLE calendar_item
TO postgres
;
4090 -- Name: collection; Type: ACL; Schema: public; Owner: postgres
4093 REVOKE ALL ON TABLE collection
FROM PUBLIC;
4094 REVOKE ALL ON TABLE collection
FROM postgres
;
4095 GRANT ALL ON TABLE collection
TO postgres
;
4099 -- Name: dav_binding; Type: ACL; Schema: public; Owner: postgres
4102 REVOKE ALL ON TABLE dav_binding
FROM PUBLIC;
4103 REVOKE ALL ON TABLE dav_binding
FROM postgres
;
4104 GRANT ALL ON TABLE dav_binding
TO postgres
;
4108 -- Name: principal; Type: ACL; Schema: public; Owner: postgres
4111 REVOKE ALL ON TABLE principal
FROM PUBLIC;
4112 REVOKE ALL ON TABLE principal
FROM postgres
;
4113 GRANT ALL ON TABLE principal
TO postgres
;
4117 -- Name: usr; Type: ACL; Schema: public; Owner: postgres
4120 REVOKE ALL ON TABLE usr
FROM PUBLIC;
4121 REVOKE ALL ON TABLE usr
FROM postgres
;
4122 GRANT ALL ON TABLE usr
TO postgres
;
4126 -- Name: dav_principal; Type: ACL; Schema: public; Owner: postgres
4129 REVOKE ALL ON TABLE dav_principal
FROM PUBLIC;
4130 REVOKE ALL ON TABLE dav_principal
FROM postgres
;
4131 GRANT ALL ON TABLE dav_principal
TO postgres
;
4135 -- Name: freebusy_ticket; Type: ACL; Schema: public; Owner: postgres
4138 REVOKE ALL ON TABLE freebusy_ticket
FROM PUBLIC;
4139 REVOKE ALL ON TABLE freebusy_ticket
FROM postgres
;
4140 GRANT ALL ON TABLE freebusy_ticket
TO postgres
;
4144 -- Name: grants; Type: ACL; Schema: public; Owner: postgres
4147 REVOKE ALL ON TABLE grants
FROM PUBLIC;
4148 REVOKE ALL ON TABLE grants
FROM postgres
;
4149 GRANT ALL ON TABLE grants
TO postgres
;
4153 -- Name: group_member; Type: ACL; Schema: public; Owner: postgres
4156 REVOKE ALL ON TABLE group_member
FROM PUBLIC;
4157 REVOKE ALL ON TABLE group_member
FROM postgres
;
4158 GRANT ALL ON TABLE group_member
TO postgres
;
4162 -- Name: locks; Type: ACL; Schema: public; Owner: postgres
4165 REVOKE ALL ON TABLE locks
FROM PUBLIC;
4166 REVOKE ALL ON TABLE locks
FROM postgres
;
4167 GRANT ALL ON TABLE locks
TO postgres
;
4171 -- Name: principal_type; Type: ACL; Schema: public; Owner: postgres
4174 REVOKE ALL ON TABLE principal_type
FROM PUBLIC;
4175 REVOKE ALL ON TABLE principal_type
FROM postgres
;
4176 GRANT ALL ON TABLE principal_type
TO postgres
;
4180 -- Name: principal_type_principal_type_id_seq; Type: ACL; Schema: public; Owner: postgres
4183 REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq
FROM PUBLIC;
4184 REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq
FROM postgres
;
4185 GRANT ALL ON SEQUENCE principal_type_principal_type_id_seq
TO postgres
;
4189 -- Name: property; Type: ACL; Schema: public; Owner: postgres
4192 REVOKE ALL ON TABLE property
FROM PUBLIC;
4193 REVOKE ALL ON TABLE property
FROM postgres
;
4194 GRANT ALL ON TABLE property
TO postgres
;
4198 -- Name: relationship; Type: ACL; Schema: public; Owner: postgres
4201 REVOKE ALL ON TABLE relationship
FROM PUBLIC;
4202 REVOKE ALL ON TABLE relationship
FROM postgres
;
4203 GRANT ALL ON TABLE relationship
TO postgres
;
4207 -- Name: relationship_type; Type: ACL; Schema: public; Owner: postgres
4210 REVOKE ALL ON TABLE relationship_type
FROM PUBLIC;
4211 REVOKE ALL ON TABLE relationship_type
FROM postgres
;
4212 GRANT ALL ON TABLE relationship_type
TO postgres
;
4216 -- Name: relationship_type_rt_id_seq; Type: ACL; Schema: public; Owner: postgres
4219 REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq
FROM PUBLIC;
4220 REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq
FROM postgres
;
4221 GRANT ALL ON SEQUENCE relationship_type_rt_id_seq
TO postgres
;
4225 -- Name: role_member; Type: ACL; Schema: public; Owner: postgres
4228 REVOKE ALL ON TABLE role_member
FROM PUBLIC;
4229 REVOKE ALL ON TABLE role_member
FROM postgres
;
4230 GRANT ALL ON TABLE role_member
TO postgres
;
4234 -- Name: roles; Type: ACL; Schema: public; Owner: postgres
4237 REVOKE ALL ON TABLE roles
FROM PUBLIC;
4238 REVOKE ALL ON TABLE roles
FROM postgres
;
4239 GRANT ALL ON TABLE roles
TO postgres
;
4243 -- Name: roles_role_no_seq; Type: ACL; Schema: public; Owner: postgres
4246 REVOKE ALL ON SEQUENCE roles_role_no_seq
FROM PUBLIC;
4247 REVOKE ALL ON SEQUENCE roles_role_no_seq
FROM postgres
;
4248 GRANT ALL ON SEQUENCE roles_role_no_seq
TO postgres
;
4252 -- Name: session; Type: ACL; Schema: public; Owner: postgres
4255 REVOKE ALL ON TABLE session FROM PUBLIC;
4256 REVOKE ALL ON TABLE session FROM postgres
;
4257 GRANT ALL ON TABLE session TO postgres
;
4261 -- Name: session_session_id_seq; Type: ACL; Schema: public; Owner: postgres
4264 REVOKE ALL ON SEQUENCE session_session_id_seq
FROM PUBLIC;
4265 REVOKE ALL ON SEQUENCE session_session_id_seq
FROM postgres
;
4266 GRANT ALL ON SEQUENCE session_session_id_seq
TO postgres
;
4270 -- Name: supported_locales; Type: ACL; Schema: public; Owner: postgres
4273 REVOKE ALL ON TABLE supported_locales
FROM PUBLIC;
4274 REVOKE ALL ON TABLE supported_locales
FROM postgres
;
4275 GRANT ALL ON TABLE supported_locales
TO postgres
;
4279 -- Name: sync_changes; Type: ACL; Schema: public; Owner: postgres
4282 REVOKE ALL ON TABLE sync_changes
FROM PUBLIC;
4283 REVOKE ALL ON TABLE sync_changes
FROM postgres
;
4284 GRANT ALL ON TABLE sync_changes
TO postgres
;
4288 -- Name: sync_tokens; Type: ACL; Schema: public; Owner: postgres
4291 REVOKE ALL ON TABLE sync_tokens
FROM PUBLIC;
4292 REVOKE ALL ON TABLE sync_tokens
FROM postgres
;
4293 GRANT ALL ON TABLE sync_tokens
TO postgres
;
4297 -- Name: sync_tokens_sync_token_seq; Type: ACL; Schema: public; Owner: postgres
4300 REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq
FROM PUBLIC;
4301 REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq
FROM postgres
;
4302 GRANT ALL ON SEQUENCE sync_tokens_sync_token_seq
TO postgres
;
4306 -- Name: timezones; Type: ACL; Schema: public; Owner: postgres
4309 REVOKE ALL ON TABLE timezones
FROM PUBLIC;
4310 REVOKE ALL ON TABLE timezones
FROM postgres
;
4311 GRANT ALL ON TABLE timezones
TO postgres
;
4315 -- Name: timezones_our_tzno_seq; Type: ACL; Schema: public; Owner: postgres
4318 REVOKE ALL ON SEQUENCE timezones_our_tzno_seq
FROM PUBLIC;
4319 REVOKE ALL ON SEQUENCE timezones_our_tzno_seq
FROM postgres
;
4320 GRANT ALL ON SEQUENCE timezones_our_tzno_seq
TO postgres
;
4324 -- Name: tmp_password; Type: ACL; Schema: public; Owner: postgres
4327 REVOKE ALL ON TABLE tmp_password
FROM PUBLIC;
4328 REVOKE ALL ON TABLE tmp_password
FROM postgres
;
4329 GRANT ALL ON TABLE tmp_password
TO postgres
;
4333 -- Name: tz_aliases; Type: ACL; Schema: public; Owner: postgres
4336 REVOKE ALL ON TABLE tz_aliases
FROM PUBLIC;
4337 REVOKE ALL ON TABLE tz_aliases
FROM postgres
;
4338 GRANT ALL ON TABLE tz_aliases
TO postgres
;
4342 -- Name: tz_localnames; Type: ACL; Schema: public; Owner: postgres
4345 REVOKE ALL ON TABLE tz_localnames
FROM PUBLIC;
4346 REVOKE ALL ON TABLE tz_localnames
FROM postgres
;
4347 GRANT ALL ON TABLE tz_localnames
TO postgres
;
4351 -- Name: usr_setting; Type: ACL; Schema: public; Owner: postgres
4354 REVOKE ALL ON TABLE usr_setting
FROM PUBLIC;
4355 REVOKE ALL ON TABLE usr_setting
FROM postgres
;
4356 GRANT ALL ON TABLE usr_setting
TO postgres
;
4360 -- Name: usr_user_no_seq; Type: ACL; Schema: public; Owner: postgres
4363 REVOKE ALL ON SEQUENCE usr_user_no_seq
FROM PUBLIC;
4364 REVOKE ALL ON SEQUENCE usr_user_no_seq
FROM postgres
;
4365 GRANT ALL ON SEQUENCE usr_user_no_seq
TO postgres
;
4369 -- PostgreSQL database dump complete