--- /dev/null
+--
+-- PostgreSQL database dump
+--
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+
+--
+-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
+--
+
+CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
+
+
+--
+-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
+--
+
+COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
+
+
+SET search_path = public, pg_catalog;
+
+--
+-- Name: rrule_instance; Type: TYPE; Schema: public; Owner: postgres
+--
+
+CREATE TYPE rrule_instance AS (
+ dtstart timestamp with time zone,
+ rrule text,
+ instance timestamp with time zone
+);
+
+
+ALTER TYPE public.rrule_instance OWNER TO postgres;
+
+--
+-- Name: rrule_parts; Type: TYPE; Schema: public; Owner: postgres
+--
+
+CREATE TYPE rrule_parts AS (
+ base timestamp with time zone,
+ until timestamp with time zone,
+ freq text,
+ count integer,
+ "interval" integer,
+ bysecond integer[],
+ byminute integer[],
+ byhour integer[],
+ bymonthday integer[],
+ byyearday integer[],
+ byweekno integer[],
+ byday text[],
+ bymonth integer[],
+ bysetpos integer[],
+ wkst text
+);
+
+
+ALTER TYPE public.rrule_parts OWNER TO postgres;
+
+--
+-- Name: alarm_changed(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION alarm_changed() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ oldcomponent TEXT;
+ newcomponent TEXT;
+BEGIN
+ -- in case we trigger on other events in future
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.component != OLD.component THEN
+ UPDATE caldav_data
+ SET caldav_data = replace( caldav_data, OLD.component, NEW.component ),
+ dav_etag = md5(replace( caldav_data, OLD.component, NEW.component ))
+ WHERE caldav_data.dav_id = NEW.dav_id;
+ END IF;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.alarm_changed() OWNER TO postgres;
+
+--
+-- Name: apply_month_byday(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION apply_month_byday(timestamp with time zone, text) RETURNS timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_time ALIAS FOR $1;
+ byday ALIAS FOR $2;
+ weeks INT;
+ dow INT;
+ temp_txt TEXT;
+ dd INT;
+ mm INT;
+ yy INT;
+ our_dow INT;
+ our_answer TIMESTAMP WITH TIME ZONE;
+BEGIN
+ dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2;
+ temp_txt := substring(byday from '([0-9]+)');
+ weeks := temp_txt::int;
+
+ -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
+
+ IF substring(byday for 1) = '-' THEN
+ -- Last XX of month, or possibly second-to-last, but unlikely
+ mm := extract( 'month' from in_time);
+ yy := extract( 'year' from in_time);
+
+ -- Start with the last day of the month
+ our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval;
+ dd := extract( 'dow' from our_answer);
+ dd := dd - dow;
+ IF dd < 0 THEN
+ dd := dd + 7;
+ END IF;
+
+ -- Having calculated the right day of the month, we now apply that back to in_time
+ -- which contains the otherwise-unobtainable timezone detail (and the time)
+ our_answer = our_answer - (dd::text || 'days')::interval;
+ dd := extract( 'day' from our_answer) - extract( 'day' from in_time);
+ our_answer := in_time + (dd::text || 'days')::interval;
+
+ IF weeks > 1 THEN
+ weeks := weeks - 1;
+ our_answer := our_answer - (weeks::text || 'weeks')::interval;
+ END IF;
+
+ ELSE
+
+ -- Shift our date to the correct day of week..
+ our_dow := extract( 'dow' from in_time);
+ our_dow := our_dow - dow;
+ dd := extract( 'day' from in_time);
+ IF our_dow >= dd THEN
+ our_dow := our_dow - 7;
+ END IF;
+ our_answer := in_time - (our_dow::text || 'days')::interval;
+ dd = extract( 'day' from our_answer);
+
+ -- Shift the date to the correct week...
+ dd := weeks - ((dd+6) / 7);
+ IF dd != 0 THEN
+ our_answer := our_answer + ((dd::text || 'weeks')::interval);
+ END IF;
+
+ END IF;
+
+ RETURN our_answer;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.apply_month_byday(timestamp with time zone, text) OWNER TO postgres;
+
+--
+-- Name: bits_to_legacy_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION bits_to_legacy_privilege(bit) RETURNS text
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_bits ALIAS FOR $1;
+ out_priv TEXT;
+BEGIN
+ out_priv := '';
+ IF in_bits = (~ 0::BIT(24)) THEN
+ out_priv = 'A';
+ RETURN out_priv;
+ END IF;
+
+ -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
+ -- 1 DAV:read
+ -- 512 CalDAV:read-free-busy
+ -- 4096 CALDAV:schedule-query-freebusy
+ IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
+ out_priv := 'R';
+ ELSE
+ out_priv := 'F';
+ END IF;
+ END IF;
+
+ -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
+ -- 2 DAV:write-properties
+ -- 4 DAV:write-content
+ -- 64 DAV:bind
+ -- 128 DAV:unbind
+ IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || 'W';
+ ELSE
+ IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || 'B';
+ END IF;
+ IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || 'U';
+ END IF;
+ END IF;
+ END IF;
+
+ RETURN out_priv;
+END
+$_$;
+
+
+ALTER FUNCTION public.bits_to_legacy_privilege(bit) OWNER TO postgres;
+
+--
+-- Name: bits_to_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION bits_to_privilege(bit) RETURNS text[]
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_bits ALIAS FOR $1;
+ out_priv TEXT[];
+BEGIN
+ IF in_bits = (~ 0::BIT(24)) THEN
+ out_priv := out_priv || ARRAY['DAV:all'];
+ END IF;
+
+ IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:read'];
+ END IF;
+ IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:read-free-busy'];
+ END IF;
+ END IF;
+
+ IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:write'];
+ ELSE
+ IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:write-properties'];
+ END IF;
+ IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:write-content'];
+ END IF;
+ IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:bind'];
+ END IF;
+ IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:unbind'];
+ END IF;
+ END IF;
+ END IF;
+
+ IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:unlock'];
+ END IF;
+
+ IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:read-acl'];
+ END IF;
+
+ IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set'];
+ END IF;
+
+ IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['DAV:write-acl'];
+ END IF;
+
+ IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-deliver'];
+ ELSE
+ IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite'];
+ END IF;
+ IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply'];
+ END IF;
+ IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy'];
+ END IF;
+ END IF;
+ END IF;
+
+ IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN
+ IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-send'];
+ ELSE
+ IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-send-invite'];
+ END IF;
+ IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-send-reply'];
+ END IF;
+ IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN
+ out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy'];
+ END IF;
+ END IF;
+ END IF;
+
+ RETURN out_priv;
+END
+$_$;
+
+
+ALTER FUNCTION public.bits_to_privilege(bit) OWNER TO postgres;
+
+--
+-- Name: calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) RETURNS timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ earliest ALIAS FOR $1;
+ basedate ALIAS FOR $2;
+ repeatrule ALIAS FOR $3;
+ frequency TEXT;
+ temp_txt TEXT;
+ length INT;
+ count INT;
+ byday TEXT;
+ bymonthday INT;
+ basediff INTERVAL;
+ past_repeats INT8;
+ units TEXT;
+ dow TEXT;
+ our_answer TIMESTAMP WITH TIME ZONE;
+ loopcount INT;
+BEGIN
+ IF basedate > earliest THEN
+ RETURN basedate;
+ END IF;
+
+ temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
+ IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN
+ RETURN NULL;
+ END IF;
+
+ frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
+ IF frequency IS NULL THEN
+ RETURN NULL;
+ END IF;
+
+ past_repeats = 0;
+ length = 1;
+ temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)');
+ IF temp_txt IS NOT NULL THEN
+ length := temp_txt::int;
+ basediff := earliest - basedate;
+
+ -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
+
+ -- Calculate the number of past periods between our base date and our earliest date
+ IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN
+ past_repeats := extract('epoch' from basediff)::INT8 / 86400;
+ -- RAISE NOTICE 'Days: %', past_repeats;
+ IF frequency = 'WEEKLY' THEN
+ past_repeats := past_repeats / 7;
+ END IF;
+ ELSE
+ past_repeats = extract( 'years' from basediff );
+ IF frequency = 'MONTHLY' THEN
+ past_repeats = (past_repeats *12) + extract( 'months' from basediff );
+ END IF;
+ END IF;
+ IF length IS NOT NULL THEN
+ past_repeats = (past_repeats / length) + 1;
+ END IF;
+ END IF;
+
+ -- Check that we have not exceeded the COUNT= limit
+ temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
+ IF temp_txt IS NOT NULL THEN
+ count := temp_txt::int;
+ -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length;
+ IF ( count <= past_repeats ) THEN
+ RETURN NULL;
+ END IF;
+ ELSE
+ count := NULL;
+ END IF;
+
+ temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)');
+ byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)');
+ IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN
+ -- Since this could move the date around a month we go back one
+ -- period just to be extra sure.
+ past_repeats = past_repeats - 1;
+
+ IF temp_txt IS NOT NULL THEN
+ -- Crudely hack the BYSETPOS onto the front of BYDAY. While this
+ -- is not as per rfc2445, RRULE syntax is so complex and overblown
+ -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and
+ -- certainly not within a MONTHLY RRULE.
+ byday := temp_txt || byday;
+ END IF;
+ END IF;
+
+ past_repeats = past_repeats * length;
+
+ units := CASE
+ WHEN frequency = 'DAILY' THEN 'days'
+ WHEN frequency = 'WEEKLY' THEN 'weeks'
+ WHEN frequency = 'MONTHLY' THEN 'months'
+ WHEN frequency = 'YEARLY' THEN 'years'
+ END;
+
+ temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)');
+ bymonthday := temp_txt::int;
+
+ -- With all of the above calculation, this date should be close to (but less than)
+ -- the target, and we should only loop once or twice.
+ our_answer := basedate + (past_repeats::text || units)::interval;
+
+ IF our_answer IS NULL THEN
+ RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units;
+ END IF;
+
+
+ loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle
+ LOOP
+ -- RAISE NOTICE 'Testing date: %', our_answer;
+ IF frequency = 'DAILY' THEN
+ IF byday IS NOT NULL THEN
+ LOOP
+ dow = substring( to_char( our_answer, 'DY' ) for 2);
+ EXIT WHEN byday ~* dow;
+ -- Increment for our next time through the loop...
+ our_answer := our_answer + (length::text || units)::interval;
+ END LOOP;
+ END IF;
+ ELSIF frequency = 'WEEKLY' THEN
+ -- Weekly repeats are only on specific days
+ -- This is really not right, since a WEEKLY on MO,WE,FR should
+ -- occur three times each week and this will only be once a week.
+ dow = substring( to_char( our_answer, 'DY' ) for 2);
+ ELSIF frequency = 'MONTHLY' THEN
+ IF byday IS NOT NULL THEN
+ -- This works fine, except that maybe there are multiple BYDAY
+ -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays.
+ our_answer := apply_month_byday( our_answer, byday );
+ ELSE
+ -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month
+ our_answer := our_answer + '1 month'::interval;
+ END IF;
+ ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN
+ -- We do not deal with this situation at present
+ RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.';
+ END IF;
+
+ EXIT WHEN our_answer >= earliest;
+
+ -- Give up if we have exceeded the count
+ IF ( count IS NOT NULL AND past_repeats > count ) THEN
+ RETURN NULL;
+ ELSE
+ past_repeats := past_repeats + 1;
+ END IF;
+
+ loopcount := loopcount - 1;
+ IF loopcount < 0 THEN
+ RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest;
+ RETURN NULL;
+ END IF;
+
+ -- Increment for our next time through the loop...
+ our_answer := our_answer + (length::text || units)::interval;
+
+ END LOOP;
+
+ RETURN our_answer;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) OWNER TO postgres;
+
+--
+-- Name: caldav_data_modified(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION caldav_data_modified() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ coll_id caldav_data.collection_id%TYPE;
+BEGIN
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN
+ -- Nothing for us to do
+ RETURN NEW;
+ END IF;
+ END IF;
+
+ IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
+ -- On insert or update modified, we set the NEW collection tag to the md5 of the
+ -- etag of the updated row which gives us something predictable for our regression
+ -- tests, but something different from the actual etag of the new event.
+ UPDATE collection
+ SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag)
+ WHERE collection_id = NEW.collection_id;
+ IF TG_OP = 'INSERT' THEN
+ RETURN NEW;
+ END IF;
+ END IF;
+
+ IF TG_OP = 'DELETE' THEN
+ -- On delete we set the OLD collection tag to the md5 of the old path & the old
+ -- etag, which again gives us something predictable for our regression tests.
+ UPDATE collection
+ SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
+ WHERE collection_id = OLD.collection_id;
+ RETURN OLD;
+ END IF;
+
+ IF NEW.collection_id != OLD.collection_id THEN
+ -- If we've switched the collection_id of this event, then we also need to update
+ -- the etag of the old collection - as we do for delete.
+ UPDATE collection
+ SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
+ WHERE collection_id = OLD.collection_id;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.caldav_data_modified() OWNER TO postgres;
+
+--
+-- Name: check_db_revision(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION check_db_revision(integer, integer, integer) RETURNS boolean
+ LANGUAGE plpgsql
+ AS $_$
+ DECLARE
+ major ALIAS FOR $1;
+ minor ALIAS FOR $2;
+ patch ALIAS FOR $3;
+ matching INT;
+ BEGIN
+ SELECT COUNT(*) INTO matching FROM awl_db_revision
+ WHERE (schema_major = major AND schema_minor = minor AND schema_patch > patch)
+ OR (schema_major = major AND schema_minor > minor)
+ OR (schema_major > major)
+ ;
+ IF matching >= 1 THEN
+ RAISE EXCEPTION 'Database revisions after %.%.% have already been applied.', major, minor, patch;
+ RETURN FALSE;
+ END IF;
+ SELECT COUNT(*) INTO matching FROM awl_db_revision
+ WHERE schema_major = major AND schema_minor = minor AND schema_patch = patch;
+ IF matching >= 1 THEN
+ RETURN TRUE;
+ END IF;
+ RAISE EXCEPTION 'Database has not been upgraded to %.%.%', major, minor, patch;
+ RETURN FALSE;
+ END;
+$_$;
+
+
+ALTER FUNCTION public.check_db_revision(integer, integer, integer) OWNER TO postgres;
+
+--
+-- Name: collection_modified(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION collection_modified() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+BEGIN
+ -- in case we trigger on other events in future
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.dav_name != OLD.dav_name THEN
+ UPDATE caldav_data
+ SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name),
+ user_no = NEW.user_no
+ WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name;
+ END IF;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.collection_modified() OWNER TO postgres;
+
+--
+-- Name: collections_within(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION collections_within(integer, integer) RETURNS SETOF integer
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ in_collection_id ALIAS FOR $1;
+ in_depth ALIAS FOR $2;
+ resource_id INT;
+ found_some BOOLEAN;
+BEGIN
+ in_depth := in_depth - 1;
+ found_some = FALSE;
+ FOR resource_id IN SELECT b.bound_source_id FROM dav_binding b
+ JOIN collection pc ON (b.parent_container = pc.dav_name)
+ WHERE pc.collection_id = in_collection_id
+ LOOP
+ found_some = TRUE;
+ RETURN NEXT resource_id;
+ IF in_depth > 0 THEN
+ FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
+ RETURN NEXT resource_id;
+ END LOOP;
+ END IF;
+ END LOOP;
+ FOR resource_id IN SELECT c.collection_id FROM collection c
+ JOIN collection pc ON (c.parent_container = pc.dav_name)
+ WHERE pc.collection_id = in_collection_id
+ LOOP
+ found_some = TRUE;
+ RETURN NEXT resource_id;
+ IF in_depth > 0 THEN
+ FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
+ RETURN NEXT resource_id;
+ END LOOP;
+ END IF;
+ END LOOP;
+ IF found_some THEN
+ RETURN;
+ END IF;
+ FOR resource_id IN SELECT c.collection_id FROM collection c
+ JOIN dav_principal pc ON (c.parent_container = pc.dav_name)
+ WHERE pc.principal_id = in_collection_id
+ LOOP
+ RETURN NEXT resource_id;
+ IF in_depth > 0 THEN
+ FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
+ RETURN NEXT resource_id;
+ END LOOP;
+ END IF;
+ END LOOP;
+END;
+$_$;
+
+
+ALTER FUNCTION public.collections_within(integer, integer) OWNER TO postgres;
+
+--
+-- Name: daily_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION daily_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ after ALIAS FOR $1;
+ rrule ALIAS FOR $2;
+BEGIN
+
+ IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN
+ RETURN;
+ END IF;
+
+ IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN
+ RETURN;
+ END IF;
+
+ IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN
+ RETURN;
+ END IF;
+
+ IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN
+ RETURN;
+ END IF;
+
+ IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN
+ RETURN;
+ END IF;
+
+ -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial
+ RETURN NEXT after;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.daily_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
+
+--
+-- Name: event_has_exceptions(text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION event_has_exceptions(text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT
+ AS $_$
+ SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?'
+$_$;
+
+
+ALTER FUNCTION public.event_has_exceptions(text) OWNER TO postgres;
+
+--
+-- Name: event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION event_instances(timestamp with time zone, text) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ basedate ALIAS FOR $1;
+ repeatrule ALIAS FOR $2;
+ maxdate TIMESTAMP WITH TIME ZONE;
+BEGIN
+ maxdate := current_date + '10 years'::interval;
+ RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d;
+END;
+$_$;
+
+
+ALTER FUNCTION public.event_instances(timestamp with time zone, text) OWNER TO postgres;
+
+--
+-- Name: expand_members(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION expand_members(bigint, integer) RETURNS SETOF bigint
+ LANGUAGE sql STABLE STRICT
+ AS $_$
+ SELECT member_id FROM group_member WHERE group_id = $1
+ UNION
+ SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id
+ FROM group_member WHERE group_id = $1) AS expanded
+ WHERE expanded.m_id IS NOT NULL;
+$_$;
+
+
+ALTER FUNCTION public.expand_members(bigint, integer) OWNER TO postgres;
+
+--
+-- Name: expand_memberships(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION expand_memberships(bigint, integer) RETURNS SETOF bigint
+ LANGUAGE sql STABLE STRICT
+ AS $_$
+ SELECT group_id FROM group_member WHERE member_id = $1
+ UNION
+ SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
+ FROM group_member WHERE member_id = $1) AS expanded
+ WHERE expanded.g_id IS NOT NULL;
+$_$;
+
+
+ALTER FUNCTION public.expand_memberships(bigint, integer) OWNER TO postgres;
+
+--
+-- Name: get_group_role_no(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION get_group_role_no() RETURNS integer
+ LANGUAGE sql IMMUTABLE
+ AS $$
+ SELECT role_no FROM roles WHERE role_name = 'Group'
+$$;
+
+
+ALTER FUNCTION public.get_group_role_no() OWNER TO postgres;
+
+--
+-- Name: get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION get_permissions(integer, integer) RETURNS text
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_from ALIAS FOR $1;
+ in_to ALIAS FOR $2;
+ out_confers TEXT;
+ bit_confers BIT(24);
+ group_role_no INT;
+ tmp_txt TEXT;
+ dbg TEXT DEFAULT '';
+ r RECORD;
+ counter INT;
+BEGIN
+ -- Self can always have full access
+ IF in_from = in_to THEN
+ RETURN 'A';
+ END IF;
+
+ -- dbg := 'S-';
+ SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1
+ WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
+ IF FOUND THEN
+ RETURN dbg || out_confers;
+ END IF;
+ -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
+
+ SELECT bit_or(r1.confers & r2.confers) INTO bit_confers
+ FROM relationship r1
+ JOIN relationship r2 ON r1.to_user=r2.from_user
+ WHERE r1.from_user=in_from AND r2.to_user=in_to
+ AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group');
+ IF bit_confers != 0::BIT(24) THEN
+ RETURN dbg || bits_to_legacy_privilege(bit_confers);
+ END IF;
+
+ RETURN '';
+ -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
+
+ 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)
+ WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user
+ AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ;
+
+ IF FOUND THEN
+ -- dbg := 'H-';
+ -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
+ RETURN dbg || out_confers;
+ END IF;
+
+ -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
+
+ RETURN '';
+END;
+$_$;
+
+
+ALTER FUNCTION public.get_permissions(integer, integer) OWNER TO postgres;
+
+--
+-- Name: get_usr_setting(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION get_usr_setting(integer, text) RETURNS text
+ LANGUAGE sql
+ AS $_$SELECT setting_value FROM usr_setting
+ WHERE usr_setting.user_no = $1
+ AND usr_setting.setting_name = $2 $_$;
+
+
+ALTER FUNCTION public.get_usr_setting(integer, text) OWNER TO postgres;
+
+--
+-- Name: grants_modified(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION grants_modified() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ old_to_principal INT8;
+ new_is_group BOOL;
+BEGIN
+ -- in case we trigger on other events in future
+ IF TG_OP = 'INSERT' THEN
+ old_to_principal := NULL;
+ ELSE
+ old_to_principal := OLD.to_principal;
+ END IF;
+ IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN
+ SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal;
+ IF NEW.is_group != new_is_group THEN
+ NEW.is_group := new_is_group;
+ END IF;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.grants_modified() OWNER TO postgres;
+
+--
+-- Name: grants_proxy_access_from_p(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION grants_proxy_access_from_p(bigint, integer) RETURNS SETOF bigint
+ LANGUAGE sql STABLE STRICT
+ AS $_$
+ SELECT DISTINCT by_principal
+ FROM grants
+ WHERE by_collection IS NULL AND by_principal != $1
+ AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1)
+ ;
+$_$;
+
+
+ALTER FUNCTION public.grants_proxy_access_from_p(bigint, integer) OWNER TO postgres;
+
+--
+-- Name: has_legacy_privilege(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION has_legacy_privilege(integer, text, integer) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_from ALIAS FOR $1;
+ in_legacy_privilege ALIAS FOR $2;
+ in_to ALIAS FOR $3;
+ in_confers BIT(24);
+ group_role_no INT;
+BEGIN
+ -- Self can always have full access
+ IF in_from = in_to THEN
+ RETURN TRUE;
+ END IF;
+
+ SELECT get_group_role_no() INTO group_role_no;
+ SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers;
+
+ IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to
+ AND (in_confers & confers) = in_confers
+ AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN
+ -- A direct relationship from A to B that grants sufficient
+ -- RAISE NOTICE 'Permissions directly granted';
+ RETURN TRUE;
+ END IF;
+
+ IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user
+ WHERE (in_confers & r1.confers & r2.confers) = in_confers
+ AND r1.from_user=in_from AND r2.to_user=in_to
+ AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN
+ -- An indirect relationship from A to B via group G that grants sufficient
+ -- RAISE NOTICE 'Permissions mediated via group';
+ RETURN TRUE;
+ END IF;
+
+ IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user
+ WHERE (in_confers & r1.confers & r2.confers) = in_confers
+ AND r1.from_user=in_from AND r2.from_user=in_to
+ AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no)
+ AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN
+ -- An indirect reflexive relationship from both A & B to group G which grants sufficient
+ -- RAISE NOTICE 'Permissions to shared group';
+ RETURN TRUE;
+ END IF;
+
+ -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
+
+ RETURN FALSE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.has_legacy_privilege(integer, text, integer) OWNER TO postgres;
+
+--
+-- Name: has_members_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION has_members_list(bigint) RETURNS text
+ LANGUAGE plpgsql STRICT
+ AS $_$
+DECLARE
+ in_member_id ALIAS FOR $1;
+ m RECORD;
+ mlist TEXT;
+BEGIN
+ mlist := '';
+ FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id)
+ WHERE group_id = in_member_id
+ LOOP
+ mlist := mlist
+ || CASE WHEN mlist = '' THEN '' ELSE ', ' END
+ || COALESCE( m.displayname, m.group_id::text);
+ END LOOP;
+ RETURN mlist;
+END;
+$_$;
+
+
+ALTER FUNCTION public.has_members_list(bigint) OWNER TO postgres;
+
+--
+-- Name: icalendar_interval_to_sql(text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION icalendar_interval_to_sql(text) RETURNS interval
+ LANGUAGE sql IMMUTABLE STRICT
+ AS $_$
+ 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;
+$_$;
+
+
+ALTER FUNCTION public.icalendar_interval_to_sql(text) OWNER TO postgres;
+
+--
+-- Name: is_member_of_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION is_member_of_list(bigint) RETURNS text
+ LANGUAGE plpgsql STRICT
+ AS $_$
+DECLARE
+ in_member_id ALIAS FOR $1;
+ m RECORD;
+ mlist TEXT;
+BEGIN
+ mlist := '';
+ FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id)
+ WHERE member_id = in_member_id
+ LOOP
+ mlist := mlist
+ || CASE WHEN mlist = '' THEN '' ELSE ', ' END
+ || COALESCE( m.displayname, m.group_id::text);
+ END LOOP;
+ RETURN mlist;
+END;
+$_$;
+
+
+ALTER FUNCTION public.is_member_of_list(bigint) OWNER TO postgres;
+
+--
+-- Name: legacy_get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION legacy_get_permissions(integer, integer) RETURNS text
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_from ALIAS FOR $1;
+ in_to ALIAS FOR $2;
+ out_confers TEXT;
+ tmp_confers1 TEXT;
+ tmp_confers2 TEXT;
+ tmp_txt TEXT;
+ dbg TEXT DEFAULT '';
+ r RECORD;
+ counter INT;
+BEGIN
+ -- Self can always have full access
+ IF in_from = in_to THEN
+ RETURN 'A';
+ END IF;
+
+ -- dbg := 'S-';
+ SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id )
+ WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
+ IF FOUND THEN
+ RETURN dbg || out_confers;
+ END IF;
+ -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
+
+ out_confers := '';
+ FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id)
+ JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id
+ WHERE r1.from_user=in_from AND r2.to_user=in_to
+ 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')
+ 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')
+ 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')
+ LOOP
+ -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
+ -- FIXME: This is an oversimplification
+ -- dbg := 'C-';
+ tmp_confers1 := r.r1;
+ tmp_confers2 := r.r2;
+ IF tmp_confers1 != tmp_confers2 THEN
+ IF tmp_confers1 ~* 'A' THEN
+ -- Ensure that A is expanded to all supported privs before being used as a mask
+ tmp_confers1 := 'AFBRWU';
+ END IF;
+ IF tmp_confers2 ~* 'A' THEN
+ -- Ensure that A is expanded to all supported privs before being used as a mask
+ tmp_confers2 := 'AFBRWU';
+ END IF;
+ -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
+ tmp_txt = '';
+ FOR counter IN 1 .. length(tmp_confers2) LOOP
+ IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN
+ tmp_txt := tmp_txt || substring(tmp_confers2,counter,1);
+ END IF;
+ END LOOP;
+ tmp_confers2 := tmp_txt;
+ END IF;
+ FOR counter IN 1 .. length(tmp_confers2) LOOP
+ IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN
+ out_confers := out_confers || substring(tmp_confers2,counter,1);
+ END IF;
+ END LOOP;
+ END LOOP;
+ 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
+ out_confers := 'A';
+ END IF;
+ IF out_confers != '' THEN
+ RETURN dbg || out_confers;
+ END IF;
+
+ -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
+
+ SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id )
+ LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id )
+ 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
+ AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user )
+ AND usr_is_role(r1.to_user,'Group');
+
+ IF FOUND THEN
+ -- dbg := 'H-';
+ -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
+ RETURN dbg || out_confers;
+ END IF;
+
+ -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
+
+ RETURN '';
+END;
+$_$;
+
+
+ALTER FUNCTION public.legacy_get_permissions(integer, integer) OWNER TO postgres;
+
+--
+-- Name: legacy_privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION legacy_privilege_to_bits(text) RETURNS bit
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_priv ALIAS FOR $1;
+ out_bits BIT(24);
+BEGIN
+ out_bits := 0::BIT(24);
+ IF in_priv ~* 'A' THEN
+ out_bits = ~ out_bits;
+ RETURN out_bits;
+ END IF;
+
+ -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
+ -- 1 DAV:read
+ -- 512 CalDAV:read-free-busy
+ -- 4096 CALDAV:schedule-query-freebusy
+ IF in_priv ~* 'R' THEN
+ out_bits := out_bits | 4609::BIT(24);
+ END IF;
+
+ -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
+ -- 2 DAV:write-properties
+ -- 4 DAV:write-content
+ -- 64 DAV:bind
+ -- 128 DAV:unbind
+ IF in_priv ~* 'W' THEN
+ out_bits := out_bits | 198::BIT(24);
+ END IF;
+
+ -- 64 DAV:bind
+ IF in_priv ~* 'B' THEN
+ out_bits := out_bits | 64::BIT(24);
+ END IF;
+
+ -- 128 DAV:unbind
+ IF in_priv ~* 'U' THEN
+ out_bits := out_bits | 128::BIT(24);
+ END IF;
+
+ -- 512 CalDAV:read-free-busy
+ -- 4096 CALDAV:schedule-query-freebusy
+ IF in_priv ~* 'F' THEN
+ out_bits := out_bits | 4608::BIT(24);
+ END IF;
+
+ RETURN out_bits;
+END
+$_$;
+
+
+ALTER FUNCTION public.legacy_privilege_to_bits(text) OWNER TO postgres;
+
+--
+-- Name: max_roles(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION max_roles() RETURNS integer
+ LANGUAGE sql
+ AS $$SELECT max(role_no) FROM roles$$;
+
+
+ALTER FUNCTION public.max_roles() OWNER TO postgres;
+
+--
+-- Name: max_session(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION max_session() RETURNS integer
+ LANGUAGE sql
+ AS $$SELECT max(session_id) FROM session$$;
+
+
+ALTER FUNCTION public.max_session() OWNER TO postgres;
+
+--
+-- Name: max_usr(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION max_usr() RETURNS integer
+ LANGUAGE sql
+ AS $$SELECT max(user_no) FROM usr$$;
+
+
+ALTER FUNCTION public.max_usr() OWNER TO postgres;
+
+--
+-- Name: monthly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION monthly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ after ALIAS FOR $1;
+ rrule ALIAS FOR $2;
+ valid_date TIMESTAMP WITH TIME ZONE;
+ curse REFCURSOR;
+ setpos INT;
+ i INT;
+BEGIN
+
+ /**
+ * Need to investigate whether it is legal to set both of these, and whether
+ * we are correct to UNION the results, or whether we should INTERSECT them.
+ * So at this point, we refer to the specification, which grants us this
+ * wonderfully enlightening vision:
+ *
+ * If multiple BYxxx rule parts are specified, then after evaluating the
+ * specified FREQ and INTERVAL rule parts, the BYxxx rule parts are
+ * applied to the current set of evaluated occurrences in the following
+ * order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR,
+ * BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated.
+ *
+ * My guess is that this means 'INTERSECT'
+ */
+ IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN
+ OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r
+ INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r
+ ORDER BY 1;
+ ELSIF rrule.bymonthday IS NOT NULL THEN
+ OPEN curse SCROLL FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1;
+ ELSE
+ OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1;
+ END IF;
+
+ RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.monthly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
+
+--
+-- Name: new_db_revision(integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION new_db_revision(integer, integer, integer, text) RETURNS void
+ LANGUAGE plpgsql
+ AS $_$
+ DECLARE
+ major ALIAS FOR $1;
+ minor ALIAS FOR $2;
+ patch ALIAS FOR $3;
+ blurb ALIAS FOR $4;
+ new_id INT;
+ BEGIN
+ SELECT MAX(schema_id) + 1 INTO new_id FROM awl_db_revision;
+ IF NOT FOUND OR new_id IS NULL THEN
+ new_id := 1;
+ END IF;
+ INSERT INTO awl_db_revision (schema_id, schema_major, schema_minor, schema_patch, schema_name)
+ VALUES( new_id, major, minor, patch, blurb );
+ RETURN;
+ END;
+$_$;
+
+
+ALTER FUNCTION public.new_db_revision(integer, integer, integer, text) OWNER TO postgres;
+
+--
+-- Name: new_sync_token(bigint, bigint); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION new_sync_token(bigint, bigint) RETURNS bigint
+ LANGUAGE plpgsql STRICT
+ AS $_$
+DECLARE
+ in_old_sync_token ALIAS FOR $1;
+ in_collection_id ALIAS FOR $2;
+ tmp_int INT8;
+ new_token sync_tokens.sync_token%TYPE;
+ old_modification_time sync_tokens.modification_time%TYPE;
+BEGIN
+ IF in_old_sync_token > 0 THEN
+ SELECT modification_time INTO old_modification_time FROM sync_tokens
+ WHERE sync_token = in_old_sync_token AND collection_id = in_collection_id;
+ IF NOT FOUND THEN
+ -- They are in an inconsistent state: we return NULL so they can re-start the process
+ RETURN NULL;
+ END IF;
+ END IF;
+
+ -- Find the most recent sync_token
+ SELECT sync_token, modification_time INTO new_token, old_modification_time FROM sync_tokens
+ WHERE collection_id = in_collection_id ORDER BY modification_time DESC LIMIT 1;
+ IF FOUND THEN
+ SELECT 1 INTO tmp_int FROM sync_changes WHERE collection_id = in_collection_id AND sync_time > old_modification_time LIMIT 1;
+ IF NOT FOUND THEN
+ -- Return the latest sync_token we have for this collection, since there are no changes.
+ RETURN new_token;
+ END IF;
+ END IF;
+
+ -- Looks like we need a new sync_token for this collection...
+ SELECT nextval('sync_tokens_sync_token_seq') INTO new_token;
+ INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, new_token );
+
+ -- Having created our new token we do some clean-up of old tokens
+ SELECT modification_time, sync_token INTO old_modification_time, tmp_int FROM sync_tokens
+ WHERE collection_id = in_collection_id AND modification_time < (current_timestamp - '7 days'::interval)
+ ORDER BY collection_id, modification_time DESC;
+ DELETE FROM sync_changes WHERE collection_id = in_collection_id AND sync_time < old_modification_time;
+ DELETE FROM sync_tokens WHERE collection_id = in_collection_id AND sync_token < tmp_int;
+
+ -- Returning the new token
+ RETURN new_token;
+END
+$_$;
+
+
+ALTER FUNCTION public.new_sync_token(bigint, bigint) OWNER TO postgres;
+
+--
+-- Name: p_has_proxy_access_to(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION p_has_proxy_access_to(bigint, integer) RETURNS SETOF bigint
+ LANGUAGE sql STABLE STRICT
+ AS $_$
+ SELECT by_principal
+ FROM (
+ SELECT by_principal FROM grants
+ WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2))
+ AND (privileges & 5::BIT(24)) != 0::BIT(24)
+ AND by_collection IS NULL
+ AND by_principal != $1
+ UNION
+ SELECT principal_id AS by_principal FROM principal
+ WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24)
+ AND principal_id != $1
+ ) subquery;
+$_$;
+
+
+ALTER FUNCTION public.p_has_proxy_access_to(bigint, integer) OWNER TO postgres;
+
+--
+-- Name: parse_rrule_parts(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION parse_rrule_parts(timestamp with time zone, text) RETURNS rrule_parts
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ basedate ALIAS FOR $1;
+ repeatrule ALIAS FOR $2;
+ result rrule_parts%ROWTYPE;
+ tempstr TEXT;
+BEGIN
+ result.base := basedate;
+ result.until := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
+ result.freq := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
+ result.count := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
+ result.interval := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1);
+ result.wkst := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)');
+
+ result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ',');
+
+ result.byyearday := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ',');
+ result.byweekno := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ',');
+ result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ',');
+ result.bymonth := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ',');
+ result.bysetpos := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ',');
+
+ result.bysecond := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ',');
+ result.byminute := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ',');
+ result.byhour := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ',');
+
+ RETURN result;
+END;
+$_$;
+
+
+ALTER FUNCTION public.parse_rrule_parts(timestamp with time zone, text) OWNER TO postgres;
+
+--
+-- Name: path_privs(bigint, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION path_privs(bigint, text, integer) RETURNS bit
+ LANGUAGE plpgsql STABLE STRICT
+ AS $_$
+DECLARE
+ in_accessor ALIAS FOR $1;
+ in_path ALIAS FOR $2;
+ in_depth ALIAS FOR $3;
+
+ alt1_path TEXT;
+ alt2_path TEXT;
+ grantor_collection INT8;
+ grantor_principal INT8;
+ collection_path TEXT;
+ collection_privileges BIT(24);
+ out_conferred BIT(24);
+BEGIN
+ out_conferred := 0::BIT(24);
+
+ IF in_path ~ '^/?$' THEN
+ -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
+ RETURN 1; -- basic read privileges on root directory
+ END IF;
+
+ -- We need to canonicalise the path, so:
+ -- If it matches '/' + some characters (+ optional '/') => a principal URL
+ IF in_path ~ '^/[^/]+/?$' THEN
+ alt1_path := replace(in_path, '/', '');
+ SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
+ -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
+ RETURN out_conferred;
+ END IF;
+
+ -- 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.
+ alt1_path := in_path;
+ IF alt1_path ~ E'\\.ics$' THEN
+ alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
+ END IF;
+ alt2_path := regexp_replace( in_path, '[^/]*$', '');
+ SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges
+ INTO grantor_collection, grantor_principal, collection_path, collection_privileges
+ FROM collection JOIN principal grantor USING (user_no)
+ WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
+ ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;
+
+ -- Self will always need full access to their own collections!
+ IF grantor_principal = in_accessor THEN
+ -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path;
+ RETURN ~ out_conferred;
+ END IF;
+
+ SELECT bit_or(privileges) INTO out_conferred FROM grants
+ WHERE by_collection = grantor_collection
+ AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)));
+
+ IF out_conferred IS NULL THEN
+ IF collection_privileges IS NULL THEN
+ IF grantor_principal IS NULL THEN
+ alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/');
+ SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred;
+ -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
+ ELSE
+ SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred;
+ -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
+ END IF;
+ ELSE
+ out_conferred := collection_privileges;
+ -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
+ END IF;
+ END IF;
+
+ RETURN out_conferred;
+END;
+$_$;
+
+
+ALTER FUNCTION public.path_privs(bigint, text, integer) OWNER TO postgres;
+
+--
+-- Name: pprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION pprivs(bigint, bigint, integer) RETURNS bit
+ LANGUAGE plpgsql STABLE STRICT
+ AS $_$
+DECLARE
+ in_accessor ALIAS FOR $1;
+ in_grantor ALIAS FOR $2;
+ in_depth ALIAS FOR $3;
+ out_conferred BIT(24);
+BEGIN
+ out_conferred := 0::BIT(24);
+ -- Self can always have full access
+ IF in_grantor = in_accessor THEN
+ RETURN ~ out_conferred;
+ END IF;
+
+ SELECT bit_or(subquery.privileges) INTO out_conferred FROM
+ (
+ SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL
+ AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)))
+ UNION
+ SELECT bit_or(sq2.privileges) FROM
+ (
+ SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor
+ UNION
+ SELECT default_privileges AS privileges FROM principal WHERE principal_id = in_grantor
+ ) AS sq2
+ ) AS subquery ;
+
+ IF out_conferred IS NULL THEN
+ SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
+ END IF;
+
+ RETURN out_conferred;
+END;
+$_$;
+
+
+ALTER FUNCTION public.pprivs(bigint, bigint, integer) OWNER TO postgres;
+
+--
+-- Name: principal_modified(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION principal_modified() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+BEGIN
+ -- in case we trigger on other events in future
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.type_id != OLD.type_id THEN
+ UPDATE grants
+ SET is_group = (NEW.type_id = 3)
+ WHERE grants.to_principal = NEW.principal_id;
+ END IF;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.principal_modified() OWNER TO postgres;
+
+--
+-- Name: privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION privilege_to_bits(text) RETURNS bit
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ raw_priv ALIAS FOR $1;
+ in_priv TEXT;
+BEGIN
+ in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
+ IF in_priv = 'all' THEN
+ RETURN ~ 0::BIT(24);
+ END IF;
+
+ RETURN (CASE
+ WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096
+ WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128
+ WHEN in_priv = 'write-properties' THEN 2
+ WHEN in_priv = 'write-content' THEN 4
+ WHEN in_priv = 'unlock' THEN 8
+ WHEN in_priv = 'read-acl' THEN 16
+ WHEN in_priv = 'read-current-user-privilege-set' THEN 32
+ WHEN in_priv = 'bind' THEN 64
+ WHEN in_priv = 'unbind' THEN 128
+ WHEN in_priv = 'write-acl' THEN 256
+ WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096
+ WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096
+ WHEN in_priv = 'schedule-deliver-invite' THEN 1024
+ WHEN in_priv = 'schedule-deliver-reply' THEN 2048
+ WHEN in_priv = 'schedule-query-freebusy' THEN 4096
+ WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768
+ WHEN in_priv = 'schedule-send-invite' THEN 8192
+ WHEN in_priv = 'schedule-send-reply' THEN 16384
+ WHEN in_priv = 'schedule-send-freebusy' THEN 32768
+ ELSE 0 END)::BIT(24);
+END
+$_$;
+
+
+ALTER FUNCTION public.privilege_to_bits(text) OWNER TO postgres;
+
+--
+-- Name: privilege_to_bits(text[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION privilege_to_bits(text[]) RETURNS bit
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ raw_privs ALIAS FOR $1;
+ in_priv TEXT;
+ out_bits BIT(24);
+ i INT;
+ all_privs BIT(24);
+ start INT;
+ finish INT;
+BEGIN
+ out_bits := 0::BIT(24);
+ all_privs := ~ out_bits;
+ SELECT array_lower(raw_privs,1) INTO start;
+ SELECT array_upper(raw_privs,1) INTO finish;
+ FOR i IN start .. finish LOOP
+ SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
+ IF out_bits = 65535::BIT(24) THEN
+ RETURN all_privs;
+ END IF;
+ END LOOP;
+ RETURN out_bits;
+END
+$_$;
+
+
+ALTER FUNCTION public.privilege_to_bits(text[]) OWNER TO postgres;
+
+--
+-- Name: privileges_list(bit); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION privileges_list(bit) RETURNS text
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_privileges ALIAS FOR $1;
+ privileges TEXT[];
+ plist TEXT;
+ start INT;
+ finish INT;
+ i INT;
+BEGIN
+ plist := '';
+
+ privileges := bits_to_privilege(in_privileges);
+ SELECT array_lower(privileges,1) INTO start;
+ IF start IS NOT NULL THEN
+ SELECT array_upper(privileges,1) INTO finish;
+ FOR i IN start .. finish LOOP
+ plist := plist
+ || CASE WHEN plist = '' THEN '' ELSE ', ' END
+ || privileges[i];
+ END LOOP;
+ END IF;
+ RETURN plist;
+END;
+$_$;
+
+
+ALTER FUNCTION public.privileges_list(bit) OWNER TO postgres;
+
+--
+-- Name: real_path_exists(text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION real_path_exists(text) RETURNS boolean
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ in_path ALIAS FOR $1;
+ tmp BOOLEAN;
+BEGIN
+ IF in_path = '/' THEN
+ RETURN TRUE;
+ END IF;
+ IF in_path ~ '^/[^/]+/$' THEN
+ SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
+ IF FOUND THEN
+ RETURN TRUE;
+ END IF;
+ ELSE
+ IF in_path ~ '^/.*/$' THEN
+ SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
+ IF FOUND THEN
+ RETURN TRUE;
+ END IF;
+ END IF;
+ END IF;
+ RETURN FALSE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.real_path_exists(text) OWNER TO postgres;
+
+--
+-- Name: relationship_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION relationship_list(bigint) RETURNS text
+ LANGUAGE plpgsql
+ AS $_$
+DECLARE
+ user ALIAS FOR $1;
+ r RECORD;
+ rlist TEXT;
+BEGIN
+ rlist := '';
+ FOR r IN SELECT rt_name, fullname FROM relationship
+ LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no
+ WHERE from_user = user
+ LOOP
+ rlist := rlist
+ || CASE WHEN rlist = '' THEN '' ELSE ', ' END
+ || r.rt_name || '(' || r.fullname || ')';
+ END LOOP;
+ RETURN rlist;
+END;
+$_$;
+
+
+ALTER FUNCTION public.relationship_list(bigint) OWNER TO postgres;
+
+--
+-- Name: rrule_bysetpos_filter(refcursor, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_bysetpos_filter(refcursor, integer[]) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ curse ALIAS FOR $1;
+ bysetpos ALIAS FOR $2;
+ valid_date TIMESTAMP WITH TIME ZONE;
+ i INT;
+BEGIN
+
+ IF bysetpos IS NULL THEN
+ LOOP
+ FETCH curse INTO valid_date;
+ EXIT WHEN NOT FOUND;
+ RETURN NEXT valid_date;
+ END LOOP;
+ ELSE
+ FOR i IN 1..366 LOOP
+ EXIT WHEN bysetpos[i] IS NULL;
+ IF bysetpos[i] > 0 THEN
+ FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date;
+ ELSE
+ MOVE LAST IN curse;
+ FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date;
+ END IF;
+ IF valid_date IS NOT NULL THEN
+ RETURN NEXT valid_date;
+ END IF;
+ END LOOP;
+ END IF;
+ CLOSE curse;
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_bysetpos_filter(refcursor, integer[]) OWNER TO postgres;
+
+--
+-- Name: rrule_event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_event_instances(timestamp with time zone, text) RETURNS SETOF rrule_instance
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ basedate ALIAS FOR $1;
+ repeatrule ALIAS FOR $2;
+ maxdate TIMESTAMP WITH TIME ZONE;
+ current TIMESTAMP WITH TIME ZONE;
+ result rrule_instance%ROWTYPE;
+BEGIN
+ maxdate := current_date + '10 years'::interval;
+
+ result.dtstart := basedate;
+ result.rrule := repeatrule;
+
+ FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP
+ result.instance := current;
+ RETURN NEXT result;
+ END LOOP;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_event_instances(timestamp with time zone, text) OWNER TO postgres;
+
+--
+-- 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
+--
+
+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
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ basedate ALIAS FOR $1;
+ repeatrule ALIAS FOR $2;
+ mindate ALIAS FOR $3;
+ maxdate ALIAS FOR $4;
+ max_count ALIAS FOR $5;
+ loopmax INT;
+ loopcount INT;
+ base_day TIMESTAMP WITH TIME ZONE;
+ current_base TIMESTAMP WITH TIME ZONE;
+ current TIMESTAMP WITH TIME ZONE;
+ rrule rrule_parts%ROWTYPE;
+BEGIN
+ loopcount := 0;
+
+ SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule );
+
+ IF rrule.count IS NOT NULL THEN
+ loopmax := rrule.count;
+ ELSE
+ -- max_count is pretty arbitrary, so we scale it somewhat here depending on the frequency.
+ IF rrule.freq = 'DAILY' THEN
+ loopmax := max_count * 20;
+ ELSIF rrule.freq = 'WEEKLY' THEN
+ loopmax := max_count * 10;
+ ELSE
+ loopmax := max_count;
+ END IF;
+ END IF;
+
+ current_base := basedate;
+ base_day := date_trunc('day',basedate);
+ WHILE loopcount < loopmax AND current_base <= maxdate LOOP
+ IF rrule.freq = 'DAILY' THEN
+ FOR current IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP
+-- IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN
+ EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
+ IF current >= mindate THEN
+ RETURN NEXT current;
+ END IF;
+ loopcount := loopcount + 1;
+ EXIT WHEN loopcount >= loopmax;
+-- END IF;
+ END LOOP;
+ current_base := current_base + (rrule.interval::text || ' days')::interval;
+ ELSIF rrule.freq = 'WEEKLY' THEN
+ FOR current IN SELECT w FROM weekly_set(current_base,rrule) w WHERE w >= base_day LOOP
+ IF test_byyearday_rule(current,rrule.byyearday)
+ AND test_bymonthday_rule(current,rrule.bymonthday)
+ AND test_bymonth_rule(current,rrule.bymonth)
+ THEN
+ EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
+ IF current >= mindate THEN
+ RETURN NEXT current;
+ END IF;
+ loopcount := loopcount + 1;
+ EXIT WHEN loopcount >= loopmax;
+ END IF;
+ END LOOP;
+ current_base := current_base + (rrule.interval::text || ' weeks')::interval;
+ ELSIF rrule.freq = 'MONTHLY' THEN
+ FOR current IN SELECT m FROM monthly_set(current_base,rrule) m WHERE m >= base_day LOOP
+-- IF /* test_byyearday_rule(current,rrule.byyearday)
+-- AND */ test_bymonth_rule(current,rrule.bymonth)
+-- THEN
+ EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
+ IF current >= mindate THEN
+ RETURN NEXT current;
+ END IF;
+ loopcount := loopcount + 1;
+ EXIT WHEN loopcount >= loopmax;
+-- END IF;
+ END LOOP;
+ current_base := current_base + (rrule.interval::text || ' months')::interval;
+ ELSIF rrule.freq = 'YEARLY' THEN
+ FOR current IN SELECT y FROM yearly_set(current_base,rrule) y WHERE y >= base_day LOOP
+ EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
+ IF current >= mindate THEN
+ RETURN NEXT current;
+ END IF;
+ loopcount := loopcount + 1;
+ EXIT WHEN loopcount >= loopmax;
+ END LOOP;
+ current_base := current_base + (rrule.interval::text || ' years')::interval;
+ ELSE
+ RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq;
+ RETURN;
+ END IF;
+ EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
+ END LOOP;
+ -- RETURN QUERY;
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO postgres;
+
+--
+-- 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
+--
+
+CREATE FUNCTION rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ dtstart ALIAS FOR $1;
+ dtend ALIAS FOR $2;
+ repeatrule ALIAS FOR $3;
+ in_mindate ALIAS FOR $4;
+ in_maxdate ALIAS FOR $5;
+ base_date TIMESTAMP WITH TIME ZONE;
+ mindate TIMESTAMP WITH TIME ZONE;
+ maxdate TIMESTAMP WITH TIME ZONE;
+BEGIN
+
+ IF dtstart IS NULL THEN
+ RETURN NULL;
+ END IF;
+ IF dtend IS NULL THEN
+ base_date := dtstart;
+ ELSE
+ base_date := dtend;
+ END IF;
+
+ IF in_mindate IS NULL THEN
+ mindate := current_date - '10 years'::interval;
+ ELSE
+ mindate := in_mindate;
+ END IF;
+
+ IF in_maxdate IS NULL THEN
+ maxdate := current_date + '10 years'::interval;
+ ELSE
+ -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range.
+ maxdate := in_maxdate + (base_date - dtstart);
+ END IF;
+
+ IF repeatrule IS NULL THEN
+ RETURN (dtstart <= maxdate AND base_date >= mindate);
+ END IF;
+
+ SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1;
+ RETURN FOUND;
+
+END;
+$_$;
+
+
+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;
+
+--
+-- Name: rrule_month_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_month_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ in_time ALIAS FOR $1;
+ byday ALIAS FOR $2;
+ dayrule TEXT;
+ i INT;
+ dow INT;
+ index INT;
+ first_dow INT;
+ each_day TIMESTAMP WITH TIME ZONE;
+ this_month INT;
+ results TIMESTAMP WITH TIME ZONE[];
+BEGIN
+
+ IF byday IS NULL THEN
+ -- We still return the single date as a SET
+ RETURN NEXT in_time;
+ RETURN;
+ END IF;
+
+ i := 1;
+ dayrule := byday[i];
+ WHILE dayrule IS NOT NULL LOOP
+ dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2;
+ each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval;
+ this_month := date_part( 'month', in_time );
+ first_dow := date_part( 'dow', each_day );
+
+ -- Coerce each_day to be the first 'dow' of the month
+ each_day := each_day - ( first_dow::text || 'days')::interval
+ + ( dow::text || 'days')::interval
+ + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END;
+
+ -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow;
+ IF length(dayrule) > 2 THEN
+ index := (substring(dayrule from '^[0-9-]+'))::int;
+
+ IF index = 0 THEN
+ RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule;
+ ELSIF index > 0 THEN
+ -- The simplest case, such as 2MO for the second monday
+ each_day := each_day + ((index - 1)::text || ' weeks')::interval;
+ ELSE
+ each_day := each_day + '5 weeks'::interval;
+ WHILE date_part('month', each_day) != this_month LOOP
+ each_day := each_day - '1 week'::interval;
+ END LOOP;
+ -- Note that since index is negative, (-2 + 1) == -1, for example
+ index := index + 1;
+ IF index < 0 THEN
+ each_day := each_day + (index::text || ' weeks')::interval ;
+ END IF;
+ END IF;
+
+ -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months
+ IF date_part('month', each_day) = this_month THEN
+ results[date_part('day',each_day)] := each_day;
+ -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
+ END IF;
+
+ ELSE
+ -- Return all such days that are within the given month
+ WHILE date_part('month', each_day) = this_month LOOP
+ results[date_part('day',each_day)] := each_day;
+ each_day := each_day + '1 week'::interval;
+ -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
+ END LOOP;
+ END IF;
+
+ i := i + 1;
+ dayrule := byday[i];
+ END LOOP;
+
+ FOR i IN 1..31 LOOP
+ IF results[i] IS NOT NULL THEN
+ RETURN NEXT results[i];
+ END IF;
+ END LOOP;
+
+ RETURN;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_month_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
+
+--
+-- Name: rrule_month_bymonthday_set(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_month_bymonthday_set(timestamp with time zone, integer[]) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ in_time ALIAS FOR $1;
+ bymonthday ALIAS FOR $2;
+ month_start TIMESTAMP WITH TIME ZONE;
+ daysinmonth INT;
+ i INT;
+BEGIN
+
+ month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval;
+ daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' );
+
+ FOR i IN 1..31 LOOP
+ EXIT WHEN bymonthday[i] IS NULL;
+
+ CONTINUE WHEN bymonthday[i] > daysinmonth;
+ CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth);
+
+ IF bymonthday[i] > 0 THEN
+ RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval;
+ ELSIF bymonthday[i] < 0 THEN
+ RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval;
+ ELSE
+ RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i];
+ END IF;
+ END LOOP;
+
+ RETURN;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_month_bymonthday_set(timestamp with time zone, integer[]) OWNER TO postgres;
+
+--
+-- Name: rrule_week_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_week_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ in_time ALIAS FOR $1;
+ byday ALIAS FOR $2;
+ dayrule TEXT;
+ dow INT;
+ our_day TIMESTAMP WITH TIME ZONE;
+ i INT;
+BEGIN
+
+ IF byday IS NULL THEN
+ -- We still return the single date as a SET
+ RETURN NEXT in_time;
+ RETURN;
+ END IF;
+
+ our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval;
+
+ i := 1;
+ dayrule := byday[i];
+ WHILE dayrule IS NOT NULL LOOP
+ dow := position(dayrule in 'SUMOTUWETHFRSA') / 2;
+ RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval;
+ i := i + 1;
+ dayrule := byday[i];
+ END LOOP;
+
+ RETURN;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_week_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
+
+--
+-- Name: rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ after ALIAS FOR $1;
+ rrule ALIAS FOR $2;
+ current_base TIMESTAMP WITH TIME ZONE;
+ rr rrule_parts;
+ i INT;
+BEGIN
+
+ IF rrule.bymonth IS NOT NULL THEN
+ -- Ensure we don't pass BYSETPOS down
+ rr := rrule;
+ rr.bysetpos := NULL;
+ FOR i IN 1..12 LOOP
+ EXIT WHEN rr.bymonth[i] IS NULL;
+ current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval;
+ RETURN QUERY SELECT r FROM monthly_set(current_base,rr) r;
+ END LOOP;
+ ELSE
+ -- We don't yet implement byweekno, byblah
+ RETURN NEXT after;
+ END IF;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
+
+--
+-- Name: set_dav_property(text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION set_dav_property(text, integer, text, text) RETURNS boolean
+ LANGUAGE plpgsql STRICT
+ AS $_$
+DECLARE
+ path ALIAS FOR $1;
+ change_user ALIAS FOR $2;
+ key ALIAS FOR $3;
+ value ALIAS FOR $4;
+BEGIN
+ -- Check that there is either a resource, collection or user at this location.
+ IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path
+ UNION SELECT 1 FROM collection WHERE dav_name = path
+ UNION SELECT 1 FROM dav_principal WHERE dav_name = path
+ UNION SELECT 1 FROM dav_binding WHERE dav_name = path
+ ) THEN
+ RETURN FALSE;
+ END IF;
+ PERFORM true FROM property WHERE dav_name = path AND property_name = key;
+ IF FOUND THEN
+ UPDATE property SET changed_by=change_user::integer, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key;
+ ELSE
+ INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, change_user::integer, current_timestamp, key, value );
+ END IF;
+ RETURN TRUE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.set_dav_property(text, integer, text, text) OWNER TO postgres;
+
+--
+-- Name: sync_dav_id(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION sync_dav_id() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+ DECLARE
+ BEGIN
+
+ IF TG_OP = 'DELETE' THEN
+ -- Just let the ON DELETE CASCADE handle this case
+ RETURN OLD;
+ END IF;
+
+ IF NEW.dav_id IS NULL THEN
+ NEW.dav_id = nextval('dav_id_seq');
+ END IF;
+
+ IF TG_OP = 'UPDATE' THEN
+ IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
+ OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
+ UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
+ collection_id = NEW.collection_id, dav_name = NEW.dav_name
+ WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
+ END IF;
+ RETURN NEW;
+ END IF;
+
+ UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
+ collection_id = NEW.collection_id, dav_name = NEW.dav_name
+ WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
+
+ RETURN NEW;
+
+ END
+$$;
+
+
+ALTER FUNCTION public.sync_dav_id() OWNER TO postgres;
+
+--
+-- Name: test_byday_rule(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION test_byday_rule(timestamp with time zone, text[]) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ testme ALIAS FOR $1;
+ byday ALIAS FOR $2;
+BEGIN
+ -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended
+ -- so don't call it that way...
+ IF byday IS NOT NULL THEN
+ RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) );
+ END IF;
+ RETURN TRUE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.test_byday_rule(timestamp with time zone, text[]) OWNER TO postgres;
+
+--
+-- Name: test_bymonth_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION test_bymonth_rule(timestamp with time zone, integer[]) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ testme ALIAS FOR $1;
+ bymonth ALIAS FOR $2;
+BEGIN
+ IF bymonth IS NOT NULL THEN
+ RETURN ( date_part( 'month', testme) = ANY (bymonth) );
+ END IF;
+ RETURN TRUE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.test_bymonth_rule(timestamp with time zone, integer[]) OWNER TO postgres;
+
+--
+-- Name: test_bymonthday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION test_bymonthday_rule(timestamp with time zone, integer[]) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ testme ALIAS FOR $1;
+ bymonthday ALIAS FOR $2;
+BEGIN
+ IF bymonthday IS NOT NULL THEN
+ RETURN ( date_part( 'day', testme) = ANY (bymonthday) );
+ END IF;
+ RETURN TRUE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.test_bymonthday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
+
+--
+-- Name: test_byyearday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION test_byyearday_rule(timestamp with time zone, integer[]) RETURNS boolean
+ LANGUAGE plpgsql IMMUTABLE
+ AS $_$
+DECLARE
+ testme ALIAS FOR $1;
+ byyearday ALIAS FOR $2;
+BEGIN
+ IF byyearday IS NOT NULL THEN
+ RETURN ( date_part( 'doy', testme) = ANY (byyearday) );
+ END IF;
+ RETURN TRUE;
+END;
+$_$;
+
+
+ALTER FUNCTION public.test_byyearday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
+
+--
+-- Name: to_ical_utc(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION to_ical_utc(timestamp with time zone) RETURNS text
+ LANGUAGE sql IMMUTABLE STRICT
+ AS $_$
+ SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' )
+$_$;
+
+
+ALTER FUNCTION public.to_ical_utc(timestamp with time zone) OWNER TO postgres;
+
+--
+-- Name: uprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION uprivs(bigint, bigint, integer) RETURNS bit
+ LANGUAGE plpgsql STABLE STRICT
+ AS $_$
+DECLARE
+ in_accessor ALIAS FOR $1;
+ in_grantor ALIAS FOR $2;
+ in_depth ALIAS FOR $3;
+ out_conferred BIT(24);
+BEGIN
+ out_conferred := 0::BIT(24);
+ -- Self can always have full access
+ IF in_grantor = in_accessor THEN
+ RETURN ~ out_conferred;
+ END IF;
+
+ SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred
+ FROM principal p1, principal p2
+ WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor;
+
+ RETURN out_conferred;
+END;
+$_$;
+
+
+ALTER FUNCTION public.uprivs(bigint, bigint, integer) OWNER TO postgres;
+
+--
+-- Name: usr_is_role(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION usr_is_role(integer, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE STRICT
+ AS $_$
+ SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 )
+$_$;
+
+
+ALTER FUNCTION public.usr_is_role(integer, text) OWNER TO postgres;
+
+--
+-- Name: usr_modified(); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION usr_modified() RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ oldpath TEXT;
+ newpath TEXT;
+BEGIN
+ -- in case we trigger on other events in future
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.username != OLD.username THEN
+ oldpath := '/' || OLD.username || '/';
+ newpath := '/' || NEW.username || '/';
+ UPDATE collection
+ SET parent_container = replace( parent_container, oldpath, newpath),
+ dav_name = replace( dav_name, oldpath, newpath)
+ WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath;
+ END IF;
+ END IF;
+ RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.usr_modified() OWNER TO postgres;
+
+--
+-- Name: weekly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION weekly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ after ALIAS FOR $1;
+ rrule ALIAS FOR $2;
+ valid_date TIMESTAMP WITH TIME ZONE;
+ curse REFCURSOR;
+ weekno INT;
+ i INT;
+BEGIN
+
+ IF rrule.byweekno IS NOT NULL THEN
+ weekno := date_part('week',after);
+ IF NOT weekno = ANY ( rrule.byweekno ) THEN
+ RETURN;
+ END IF;
+ END IF;
+
+ OPEN curse SCROLL FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r;
+ RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
+
+END;
+$_$;
+
+
+ALTER FUNCTION public.weekly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
+
+--
+-- Name: write_sync_change(bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION write_sync_change(bigint, integer, text) RETURNS boolean
+ LANGUAGE plpgsql STRICT
+ AS $_$
+DECLARE
+ in_collection_id ALIAS FOR $1;
+ in_status ALIAS FOR $2;
+ in_dav_name ALIAS FOR $3;
+ tmp_int INT8;
+BEGIN
+ SELECT 1 INTO tmp_int FROM sync_tokens
+ WHERE collection_id = in_collection_id
+ LIMIT 1;
+ IF NOT FOUND THEN
+ RETURN FALSE;
+ END IF;
+ SELECT dav_id INTO tmp_int FROM caldav_data WHERE dav_name = in_dav_name;
+ INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name)
+ VALUES( in_collection_id, in_status, tmp_int, in_dav_name);
+ RETURN TRUE;
+END
+$_$;
+
+
+ALTER FUNCTION public.write_sync_change(bigint, integer, text) OWNER TO postgres;
+
+--
+-- Name: yearly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
+--
+
+CREATE FUNCTION yearly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
+ LANGUAGE plpgsql IMMUTABLE STRICT
+ AS $_$
+DECLARE
+ after ALIAS FOR $1;
+ rrule ALIAS FOR $2;
+ current_base TIMESTAMP WITH TIME ZONE;
+ curse REFCURSOR;
+ curser REFCURSOR;
+ i INT;
+BEGIN
+
+ IF rrule.bymonth IS NOT NULL THEN
+ OPEN curse SCROLL FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r;
+ FOR current_base IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP
+ current_base := date_trunc( 'day', current_base ) + (after::time)::interval;
+ RETURN NEXT current_base;
+ END LOOP;
+ ELSE
+ -- We don't yet implement byweekno, byblah
+ RETURN NEXT after;
+ END IF;
+END;
+$_$;
+
+
+ALTER FUNCTION public.yearly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: access_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE access_ticket (
+ ticket_id text NOT NULL,
+ dav_owner_id bigint NOT NULL,
+ privileges bit(24),
+ target_collection_id bigint NOT NULL,
+ target_resource_id bigint,
+ expires timestamp without time zone
+);
+
+
+ALTER TABLE public.access_ticket OWNER TO postgres;
+
+--
+-- Name: addressbook_address_adr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE addressbook_address_adr (
+ dav_id bigint NOT NULL,
+ type text,
+ box_no text,
+ unit_no text,
+ street_address text,
+ locality text,
+ region text,
+ postcode text,
+ country text,
+ property text
+);
+
+
+ALTER TABLE public.addressbook_address_adr OWNER TO postgres;
+
+--
+-- Name: addressbook_address_email; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE addressbook_address_email (
+ dav_id bigint NOT NULL,
+ type text,
+ email text,
+ property text
+);
+
+
+ALTER TABLE public.addressbook_address_email OWNER TO postgres;
+
+--
+-- Name: addressbook_address_tel; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE addressbook_address_tel (
+ dav_id bigint NOT NULL,
+ type text,
+ tel text,
+ property text
+);
+
+
+ALTER TABLE public.addressbook_address_tel OWNER TO postgres;
+
+--
+-- Name: addressbook_resource; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE addressbook_resource (
+ dav_id bigint NOT NULL,
+ version text,
+ uid text,
+ nickname text,
+ fn text,
+ n text,
+ note text,
+ org text,
+ url text,
+ fburl text,
+ caladruri text,
+ caluri text
+);
+
+
+ALTER TABLE public.addressbook_resource OWNER TO postgres;
+
+--
+-- Name: awl_db_revision; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE awl_db_revision (
+ schema_id integer,
+ schema_major integer,
+ schema_minor integer,
+ schema_patch integer,
+ schema_name text,
+ applied_on timestamp with time zone DEFAULT now()
+);
+
+
+ALTER TABLE public.awl_db_revision OWNER TO postgres;
+
+--
+-- Name: dav_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE dav_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.dav_id_seq OWNER TO postgres;
+
+--
+-- Name: caldav_data; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE caldav_data (
+ user_no integer NOT NULL,
+ dav_name text NOT NULL,
+ dav_etag text,
+ created timestamp with time zone,
+ modified timestamp with time zone,
+ caldav_data text,
+ caldav_type text,
+ logged_user integer,
+ dav_id bigint DEFAULT nextval('dav_id_seq'::regclass),
+ collection_id bigint,
+ weak_etag text
+);
+
+
+ALTER TABLE public.caldav_data OWNER TO postgres;
+
+--
+-- Name: calendar_alarm; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE calendar_alarm (
+ dav_id bigint NOT NULL,
+ action text,
+ trigger text,
+ summary text,
+ description text,
+ next_trigger timestamp with time zone,
+ component text,
+ trigger_state character(1) DEFAULT 'N'::bpchar
+);
+
+
+ALTER TABLE public.calendar_alarm OWNER TO postgres;
+
+--
+-- Name: calendar_attendee; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE calendar_attendee (
+ dav_id bigint NOT NULL,
+ status text,
+ partstat text,
+ cn text,
+ attendee text NOT NULL,
+ role text,
+ rsvp boolean,
+ property text,
+ attendee_state text,
+ weak_etag text
+);
+
+
+ALTER TABLE public.calendar_attendee OWNER TO postgres;
+
+--
+-- Name: calendar_item; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE calendar_item (
+ user_no integer NOT NULL,
+ dav_name text NOT NULL,
+ dav_etag text,
+ uid text,
+ created timestamp without time zone,
+ last_modified timestamp without time zone,
+ dtstamp timestamp without time zone,
+ dtstart timestamp with time zone,
+ dtend timestamp with time zone,
+ due timestamp with time zone,
+ summary text,
+ location text,
+ description text,
+ priority integer,
+ class text,
+ transp text,
+ rrule text,
+ url text,
+ percent_complete numeric(7,2),
+ tz_id text,
+ status text,
+ completed timestamp with time zone,
+ dav_id bigint,
+ collection_id bigint,
+ first_instance_start timestamp without time zone,
+ last_instance_end timestamp without time zone
+);
+
+
+ALTER TABLE public.calendar_item OWNER TO postgres;
+
+--
+-- Name: collection; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE collection (
+ user_no integer,
+ parent_container text,
+ dav_name text,
+ dav_etag text,
+ dav_displayname text,
+ is_calendar boolean,
+ created timestamp with time zone,
+ modified timestamp with time zone,
+ public_events_only boolean DEFAULT false NOT NULL,
+ publicly_readable boolean DEFAULT false NOT NULL,
+ collection_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
+ default_privileges bit(24),
+ is_addressbook boolean DEFAULT false,
+ resourcetypes text DEFAULT '<DAV::collection/>'::text,
+ schedule_transp text DEFAULT 'opaque'::text,
+ timezone text,
+ description text DEFAULT ''::text
+);
+
+
+ALTER TABLE public.collection OWNER TO postgres;
+
+--
+-- Name: dav_binding; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE dav_binding (
+ bind_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
+ bound_source_id bigint,
+ access_ticket_id text,
+ dav_owner_id bigint NOT NULL,
+ parent_container text NOT NULL,
+ dav_name text NOT NULL,
+ dav_displayname text,
+ external_url text,
+ type text,
+ CONSTRAINT dav_name_does_not_exist CHECK ((NOT real_path_exists(dav_name)))
+);
+
+
+ALTER TABLE public.dav_binding OWNER TO postgres;
+
+--
+-- Name: principal; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE principal (
+ principal_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
+ type_id bigint NOT NULL,
+ user_no bigint,
+ displayname text,
+ default_privileges bit(24)
+);
+
+
+ALTER TABLE public.principal OWNER TO postgres;
+
+--
+-- Name: usr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE usr (
+ user_no integer NOT NULL,
+ active boolean DEFAULT true,
+ email_ok timestamp with time zone,
+ joined timestamp with time zone DEFAULT now(),
+ updated timestamp with time zone,
+ last_used timestamp with time zone,
+ username text NOT NULL,
+ password text,
+ fullname text,
+ email text,
+ config_data text,
+ date_format_type text DEFAULT 'E'::text,
+ locale text
+);
+
+
+ALTER TABLE public.usr OWNER TO postgres;
+
+--
+-- Name: dav_principal; Type: VIEW; Schema: public; Owner: postgres
+--
+
+CREATE VIEW dav_principal AS
+ 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));
+
+
+ALTER TABLE public.dav_principal OWNER TO postgres;
+
+--
+-- Name: freebusy_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE freebusy_ticket (
+ ticket_id text NOT NULL,
+ user_no integer NOT NULL,
+ created timestamp with time zone DEFAULT now() NOT NULL
+);
+
+
+ALTER TABLE public.freebusy_ticket OWNER TO postgres;
+
+--
+-- Name: grants; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE grants (
+ by_principal bigint,
+ by_collection bigint,
+ to_principal bigint,
+ privileges bit(24),
+ is_group boolean
+);
+
+
+ALTER TABLE public.grants OWNER TO postgres;
+
+--
+-- Name: group_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE group_member (
+ group_id bigint,
+ member_id bigint
+);
+
+
+ALTER TABLE public.group_member OWNER TO postgres;
+
+--
+-- Name: locks; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE locks (
+ dav_name text,
+ opaquelocktoken text NOT NULL,
+ type text,
+ scope text,
+ depth integer,
+ owner text,
+ timeout interval,
+ start timestamp without time zone DEFAULT now()
+);
+
+
+ALTER TABLE public.locks OWNER TO postgres;
+
+--
+-- Name: principal_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE principal_type (
+ principal_type_id integer NOT NULL,
+ principal_type_desc text
+);
+
+
+ALTER TABLE public.principal_type OWNER TO postgres;
+
+--
+-- Name: principal_type_principal_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE principal_type_principal_type_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.principal_type_principal_type_id_seq OWNER TO postgres;
+
+--
+-- Name: principal_type_principal_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE principal_type_principal_type_id_seq OWNED BY principal_type.principal_type_id;
+
+
+--
+-- Name: property; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE property (
+ dav_name text NOT NULL,
+ property_name text NOT NULL,
+ property_value text,
+ changed_on timestamp without time zone DEFAULT now(),
+ changed_by integer
+);
+
+
+ALTER TABLE public.property OWNER TO postgres;
+
+--
+-- Name: relationship; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE relationship (
+ from_user integer NOT NULL,
+ to_user integer NOT NULL,
+ rt_id integer NOT NULL,
+ confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
+);
+
+
+ALTER TABLE public.relationship OWNER TO postgres;
+
+--
+-- Name: relationship_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE relationship_type (
+ rt_id integer NOT NULL,
+ rt_name text,
+ rt_togroup boolean,
+ confers text DEFAULT 'RW'::text,
+ rt_fromgroup boolean,
+ bit_confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
+);
+
+
+ALTER TABLE public.relationship_type OWNER TO postgres;
+
+--
+-- Name: relationship_type_rt_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE relationship_type_rt_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.relationship_type_rt_id_seq OWNER TO postgres;
+
+--
+-- Name: relationship_type_rt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE relationship_type_rt_id_seq OWNED BY relationship_type.rt_id;
+
+
+--
+-- Name: role_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE role_member (
+ role_no integer,
+ user_no integer
+);
+
+
+ALTER TABLE public.role_member OWNER TO postgres;
+
+--
+-- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE roles (
+ role_no integer NOT NULL,
+ role_name text
+);
+
+
+ALTER TABLE public.roles OWNER TO postgres;
+
+--
+-- Name: roles_role_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE roles_role_no_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.roles_role_no_seq OWNER TO postgres;
+
+--
+-- Name: roles_role_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE roles_role_no_seq OWNED BY roles.role_no;
+
+
+--
+-- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE session (
+ session_id integer NOT NULL,
+ user_no integer,
+ session_start timestamp with time zone DEFAULT now(),
+ session_end timestamp with time zone DEFAULT now(),
+ session_key text,
+ session_config text
+);
+
+
+ALTER TABLE public.session OWNER TO postgres;
+
+--
+-- Name: session_session_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE session_session_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.session_session_id_seq OWNER TO postgres;
+
+--
+-- Name: session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE session_session_id_seq OWNED BY session.session_id;
+
+
+--
+-- Name: supported_locales; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE supported_locales (
+ locale text NOT NULL,
+ locale_name_en text,
+ locale_name_locale text
+);
+
+
+ALTER TABLE public.supported_locales OWNER TO postgres;
+
+--
+-- Name: sync_changes; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE sync_changes (
+ sync_time timestamp with time zone DEFAULT now(),
+ collection_id bigint,
+ sync_status integer,
+ dav_id bigint,
+ dav_name text
+);
+
+
+ALTER TABLE public.sync_changes OWNER TO postgres;
+
+--
+-- Name: sync_tokens; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE sync_tokens (
+ sync_token integer NOT NULL,
+ collection_id bigint,
+ modification_time timestamp with time zone DEFAULT now()
+);
+
+
+ALTER TABLE public.sync_tokens OWNER TO postgres;
+
+--
+-- Name: sync_tokens_sync_token_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE sync_tokens_sync_token_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.sync_tokens_sync_token_seq OWNER TO postgres;
+
+--
+-- Name: sync_tokens_sync_token_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE sync_tokens_sync_token_seq OWNED BY sync_tokens.sync_token;
+
+
+--
+-- Name: timezones; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE timezones (
+ our_tzno integer NOT NULL,
+ tzid text NOT NULL,
+ olson_name text,
+ active boolean,
+ last_modified timestamp without time zone DEFAULT now(),
+ etag text,
+ vtimezone text
+);
+
+
+ALTER TABLE public.timezones OWNER TO postgres;
+
+--
+-- Name: timezones_our_tzno_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE timezones_our_tzno_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.timezones_our_tzno_seq OWNER TO postgres;
+
+--
+-- Name: timezones_our_tzno_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE timezones_our_tzno_seq OWNED BY timezones.our_tzno;
+
+
+--
+-- Name: tmp_password; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE tmp_password (
+ user_no integer,
+ password text,
+ valid_until timestamp with time zone DEFAULT (now() + '1 day'::interval)
+);
+
+
+ALTER TABLE public.tmp_password OWNER TO postgres;
+
+--
+-- Name: tz_aliases; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE tz_aliases (
+ our_tzno bigint,
+ tzalias text NOT NULL
+);
+
+
+ALTER TABLE public.tz_aliases OWNER TO postgres;
+
+--
+-- Name: tz_localnames; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE tz_localnames (
+ our_tzno bigint,
+ locale text NOT NULL,
+ localised_name text NOT NULL,
+ preferred boolean DEFAULT true
+);
+
+
+ALTER TABLE public.tz_localnames OWNER TO postgres;
+
+--
+-- Name: usr_setting; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE TABLE usr_setting (
+ user_no integer NOT NULL,
+ setting_name text NOT NULL,
+ setting_value text
+);
+
+
+ALTER TABLE public.usr_setting OWNER TO postgres;
+
+--
+-- Name: usr_user_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
+--
+
+CREATE SEQUENCE usr_user_no_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1;
+
+
+ALTER TABLE public.usr_user_no_seq OWNER TO postgres;
+
+--
+-- Name: usr_user_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
+--
+
+ALTER SEQUENCE usr_user_no_seq OWNED BY usr.user_no;
+
+
+--
+-- Name: principal_type_id; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY principal_type ALTER COLUMN principal_type_id SET DEFAULT nextval('principal_type_principal_type_id_seq'::regclass);
+
+
+--
+-- Name: rt_id; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY relationship_type ALTER COLUMN rt_id SET DEFAULT nextval('relationship_type_rt_id_seq'::regclass);
+
+
+--
+-- Name: role_no; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY roles ALTER COLUMN role_no SET DEFAULT nextval('roles_role_no_seq'::regclass);
+
+
+--
+-- Name: session_id; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY session ALTER COLUMN session_id SET DEFAULT nextval('session_session_id_seq'::regclass);
+
+
+--
+-- Name: sync_token; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY sync_tokens ALTER COLUMN sync_token SET DEFAULT nextval('sync_tokens_sync_token_seq'::regclass);
+
+
+--
+-- Name: our_tzno; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY timezones ALTER COLUMN our_tzno SET DEFAULT nextval('timezones_our_tzno_seq'::regclass);
+
+
+--
+-- Name: user_no; Type: DEFAULT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY usr ALTER COLUMN user_no SET DEFAULT nextval('usr_user_no_seq'::regclass);
+
+
+--
+-- Name: access_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY access_ticket
+ ADD CONSTRAINT access_ticket_pkey PRIMARY KEY (ticket_id);
+
+
+--
+-- Name: addressbook_resource_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY addressbook_resource
+ ADD CONSTRAINT addressbook_resource_pkey PRIMARY KEY (dav_id);
+
+
+--
+-- Name: caldav_data_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY caldav_data
+ ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id);
+
+
+--
+-- Name: caldav_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY caldav_data
+ ADD CONSTRAINT caldav_data_pkey PRIMARY KEY (user_no, dav_name);
+
+
+--
+-- Name: calendar_attendee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY calendar_attendee
+ ADD CONSTRAINT calendar_attendee_pkey PRIMARY KEY (dav_id, attendee);
+
+
+--
+-- Name: calendar_item_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY calendar_item
+ ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id);
+
+
+--
+-- Name: calendar_item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY calendar_item
+ ADD CONSTRAINT calendar_item_pkey PRIMARY KEY (user_no, dav_name);
+
+
+--
+-- Name: collection_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY collection
+ ADD CONSTRAINT collection_pkey PRIMARY KEY (collection_id);
+
+
+--
+-- Name: collection_user_no_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY collection
+ ADD CONSTRAINT collection_user_no_dav_name_key UNIQUE (user_no, dav_name);
+
+
+--
+-- Name: dav_binding_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY dav_binding
+ ADD CONSTRAINT dav_binding_dav_name_key UNIQUE (dav_name);
+
+
+--
+-- Name: dav_binding_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY dav_binding
+ ADD CONSTRAINT dav_binding_pkey PRIMARY KEY (bind_id);
+
+
+--
+-- Name: freebusy_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY freebusy_ticket
+ ADD CONSTRAINT freebusy_ticket_pkey PRIMARY KEY (ticket_id);
+
+
+--
+-- Name: locks_opaquelocktoken_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY locks
+ ADD CONSTRAINT locks_opaquelocktoken_key UNIQUE (opaquelocktoken);
+
+
+--
+-- Name: principal_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY principal
+ ADD CONSTRAINT principal_pkey PRIMARY KEY (principal_id);
+
+
+--
+-- Name: principal_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY principal_type
+ ADD CONSTRAINT principal_type_pkey PRIMARY KEY (principal_type_id);
+
+
+--
+-- Name: property_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY property
+ ADD CONSTRAINT property_pkey PRIMARY KEY (dav_name, property_name);
+
+
+--
+-- Name: relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY relationship
+ ADD CONSTRAINT relationship_pkey PRIMARY KEY (from_user, to_user, rt_id);
+
+
+--
+-- Name: relationship_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY relationship_type
+ ADD CONSTRAINT relationship_type_pkey PRIMARY KEY (rt_id);
+
+
+--
+-- Name: roles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY roles
+ ADD CONSTRAINT roles_pkey PRIMARY KEY (role_no);
+
+
+--
+-- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY session
+ ADD CONSTRAINT session_pkey PRIMARY KEY (session_id);
+
+
+--
+-- Name: supported_locales_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY supported_locales
+ ADD CONSTRAINT supported_locales_pkey PRIMARY KEY (locale);
+
+
+--
+-- Name: sync_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY sync_tokens
+ ADD CONSTRAINT sync_tokens_pkey PRIMARY KEY (sync_token);
+
+
+--
+-- Name: timezones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY timezones
+ ADD CONSTRAINT timezones_pkey PRIMARY KEY (our_tzno);
+
+
+--
+-- Name: timezones_tzid_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY timezones
+ ADD CONSTRAINT timezones_tzid_key UNIQUE (tzid);
+
+
+--
+-- Name: unique_path; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY collection
+ ADD CONSTRAINT unique_path UNIQUE (dav_name);
+
+
+--
+-- Name: unique_user; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY principal
+ ADD CONSTRAINT unique_user UNIQUE (user_no);
+
+
+--
+-- Name: usr_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY usr
+ ADD CONSTRAINT usr_pkey PRIMARY KEY (user_no);
+
+
+--
+-- Name: usr_setting_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
+--
+
+ALTER TABLE ONLY usr_setting
+ ADD CONSTRAINT usr_setting_pkey PRIMARY KEY (user_no, setting_name);
+
+
+--
+-- Name: caldav_data_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX caldav_data_collection_id_fkey ON caldav_data USING btree (collection_id);
+
+
+--
+-- Name: calendar_item_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX calendar_item_collection_id_fkey ON calendar_item USING btree (collection_id);
+
+
+--
+-- Name: grants_pk1; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE UNIQUE INDEX grants_pk1 ON grants USING btree (by_principal, to_principal);
+
+
+--
+-- Name: grants_pk2; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE UNIQUE INDEX grants_pk2 ON grants USING btree (by_collection, to_principal);
+
+
+--
+-- Name: group_member_pk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE UNIQUE INDEX group_member_pk ON group_member USING btree (group_id, member_id);
+
+
+--
+-- Name: group_member_sk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX group_member_sk ON group_member USING btree (member_id);
+
+
+--
+-- Name: locks_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX locks_dav_name_idx ON locks USING btree (dav_name);
+
+
+--
+-- Name: properties_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX properties_dav_name_idx ON property USING btree (dav_name);
+
+
+--
+-- Name: sync_processing_index; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE INDEX sync_processing_index ON sync_changes USING btree (collection_id, dav_id, sync_time);
+
+
+--
+-- Name: usr_sk1_unique_username; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
+--
+
+CREATE UNIQUE INDEX usr_sk1_unique_username ON usr USING btree (lower(username));
+
+
+--
+-- Name: dav_principal_delete; Type: RULE; Schema: public; Owner: postgres
+--
+
+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); );
+
+
+--
+-- Name: dav_principal_insert; Type: RULE; Schema: public; Owner: postgres
+--
+
+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))); );
+
+
+--
+-- Name: dav_principal_update; Type: RULE; Schema: public; Owner: postgres
+--
+
+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); );
+
+
+--
+-- Name: alarm_changed; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm FOR EACH ROW EXECUTE PROCEDURE alarm_changed();
+
+
+--
+-- Name: caldav_data_modified; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER caldav_data_modified AFTER INSERT OR DELETE OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified();
+
+
+--
+-- Name: caldav_data_sync_dav_id; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
+
+
+--
+-- Name: collection_modified; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER collection_modified AFTER UPDATE ON collection FOR EACH ROW EXECUTE PROCEDURE collection_modified();
+
+
+--
+-- Name: grants_modified; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants FOR EACH ROW EXECUTE PROCEDURE grants_modified();
+
+
+--
+-- Name: principal_modified; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER principal_modified AFTER UPDATE ON principal FOR EACH ROW EXECUTE PROCEDURE principal_modified();
+
+
+--
+-- Name: usr_modified; Type: TRIGGER; Schema: public; Owner: postgres
+--
+
+CREATE TRIGGER usr_modified AFTER UPDATE ON usr FOR EACH ROW EXECUTE PROCEDURE usr_modified();
+
+
+--
+-- Name: access_ticket_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY access_ticket
+ ADD CONSTRAINT access_ticket_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: access_ticket_target_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY access_ticket
+ ADD CONSTRAINT access_ticket_target_collection_id_fkey FOREIGN KEY (target_collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: access_ticket_target_resource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY access_ticket
+ ADD CONSTRAINT access_ticket_target_resource_id_fkey FOREIGN KEY (target_resource_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: addressbook_address_adr_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY addressbook_address_adr
+ ADD CONSTRAINT addressbook_address_adr_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: addressbook_address_email_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY addressbook_address_email
+ ADD CONSTRAINT addressbook_address_email_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: addressbook_address_tel_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY addressbook_address_tel
+ ADD CONSTRAINT addressbook_address_tel_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: addressbook_resource_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY addressbook_resource
+ ADD CONSTRAINT addressbook_resource_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: caldav_data_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY caldav_data
+ ADD CONSTRAINT caldav_data_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: caldav_data_logged_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY caldav_data
+ ADD CONSTRAINT caldav_data_logged_user_fkey FOREIGN KEY (logged_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE;
+
+
+--
+-- Name: caldav_data_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY caldav_data
+ ADD CONSTRAINT caldav_data_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: caldav_exists; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_item
+ 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;
+
+
+--
+-- Name: calendar_alarm_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_alarm
+ ADD CONSTRAINT calendar_alarm_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: calendar_attendee_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_attendee
+ ADD CONSTRAINT calendar_attendee_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: calendar_item_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_item
+ ADD CONSTRAINT calendar_item_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: calendar_item_tz_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_item
+ ADD CONSTRAINT calendar_item_tz_id_fkey FOREIGN KEY (tz_id) REFERENCES timezones(tzid);
+
+
+--
+-- Name: calendar_item_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY calendar_item
+ ADD CONSTRAINT calendar_item_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: collection_timezone_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY collection
+ ADD CONSTRAINT collection_timezone_fkey FOREIGN KEY (timezone) REFERENCES timezones(tzid) ON UPDATE CASCADE ON DELETE SET NULL;
+
+
+--
+-- Name: collection_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY collection
+ ADD CONSTRAINT collection_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: dav_binding_access_ticket_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY dav_binding
+ 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;
+
+
+--
+-- Name: dav_binding_bound_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY dav_binding
+ ADD CONSTRAINT dav_binding_bound_source_id_fkey FOREIGN KEY (bound_source_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: dav_binding_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY dav_binding
+ ADD CONSTRAINT dav_binding_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: freebusy_ticket_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY freebusy_ticket
+ ADD CONSTRAINT freebusy_ticket_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: grants_by_collection_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY grants
+ ADD CONSTRAINT grants_by_collection_fkey FOREIGN KEY (by_collection) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: grants_by_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY grants
+ ADD CONSTRAINT grants_by_principal_fkey FOREIGN KEY (by_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: grants_to_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY grants
+ ADD CONSTRAINT grants_to_principal_fkey FOREIGN KEY (to_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: group_member_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY group_member
+ ADD CONSTRAINT group_member_group_id_fkey FOREIGN KEY (group_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: group_member_member_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY group_member
+ ADD CONSTRAINT group_member_member_id_fkey FOREIGN KEY (member_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: principal_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY principal
+ ADD CONSTRAINT principal_type_id_fkey FOREIGN KEY (type_id) REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE;
+
+
+--
+-- Name: principal_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY principal
+ ADD CONSTRAINT principal_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
+
+
+--
+-- Name: property_changed_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY property
+ ADD CONSTRAINT property_changed_by_fkey FOREIGN KEY (changed_by) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT;
+
+
+--
+-- Name: relationship_from_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY relationship
+ ADD CONSTRAINT relationship_from_user_fkey FOREIGN KEY (from_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: relationship_rt_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY relationship
+ ADD CONSTRAINT relationship_rt_id_fkey FOREIGN KEY (rt_id) REFERENCES relationship_type(rt_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: relationship_to_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY relationship
+ ADD CONSTRAINT relationship_to_user_fkey FOREIGN KEY (to_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: role_member_role_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY role_member
+ ADD CONSTRAINT role_member_role_no_fkey FOREIGN KEY (role_no) REFERENCES roles(role_no);
+
+
+--
+-- Name: role_member_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY role_member
+ ADD CONSTRAINT role_member_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
+
+
+--
+-- Name: session_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY session
+ ADD CONSTRAINT session_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
+
+
+--
+-- Name: sync_changes_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY sync_changes
+ ADD CONSTRAINT sync_changes_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: sync_tokens_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY sync_tokens
+ ADD CONSTRAINT sync_tokens_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: tmp_password_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY tmp_password
+ ADD CONSTRAINT tmp_password_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no);
+
+
+--
+-- Name: tz_aliases_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY tz_aliases
+ ADD CONSTRAINT tz_aliases_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno);
+
+
+--
+-- Name: tz_localnames_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY tz_localnames
+ ADD CONSTRAINT tz_localnames_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno);
+
+
+--
+-- Name: usr_setting_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
+--
+
+ALTER TABLE ONLY usr_setting
+ ADD CONSTRAINT usr_setting_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
+
+
+--
+-- Name: public; Type: ACL; Schema: -; Owner: postgres
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
+-- Name: access_ticket; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE access_ticket FROM PUBLIC;
+REVOKE ALL ON TABLE access_ticket FROM postgres;
+GRANT ALL ON TABLE access_ticket TO postgres;
+
+
+--
+-- Name: addressbook_address_adr; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE addressbook_address_adr FROM PUBLIC;
+REVOKE ALL ON TABLE addressbook_address_adr FROM postgres;
+GRANT ALL ON TABLE addressbook_address_adr TO postgres;
+
+
+--
+-- Name: addressbook_address_email; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE addressbook_address_email FROM PUBLIC;
+REVOKE ALL ON TABLE addressbook_address_email FROM postgres;
+GRANT ALL ON TABLE addressbook_address_email TO postgres;
+
+
+--
+-- Name: addressbook_address_tel; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE addressbook_address_tel FROM PUBLIC;
+REVOKE ALL ON TABLE addressbook_address_tel FROM postgres;
+GRANT ALL ON TABLE addressbook_address_tel TO postgres;
+
+
+--
+-- Name: addressbook_resource; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE addressbook_resource FROM PUBLIC;
+REVOKE ALL ON TABLE addressbook_resource FROM postgres;
+GRANT ALL ON TABLE addressbook_resource TO postgres;
+
+
+--
+-- Name: awl_db_revision; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE awl_db_revision FROM PUBLIC;
+REVOKE ALL ON TABLE awl_db_revision FROM postgres;
+GRANT ALL ON TABLE awl_db_revision TO postgres;
+
+
+--
+-- Name: dav_id_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE dav_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE dav_id_seq FROM postgres;
+GRANT ALL ON SEQUENCE dav_id_seq TO postgres;
+
+
+--
+-- Name: caldav_data; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE caldav_data FROM PUBLIC;
+REVOKE ALL ON TABLE caldav_data FROM postgres;
+GRANT ALL ON TABLE caldav_data TO postgres;
+
+
+--
+-- Name: calendar_alarm; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE calendar_alarm FROM PUBLIC;
+REVOKE ALL ON TABLE calendar_alarm FROM postgres;
+GRANT ALL ON TABLE calendar_alarm TO postgres;
+
+
+--
+-- Name: calendar_attendee; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE calendar_attendee FROM PUBLIC;
+REVOKE ALL ON TABLE calendar_attendee FROM postgres;
+GRANT ALL ON TABLE calendar_attendee TO postgres;
+
+
+--
+-- Name: calendar_item; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE calendar_item FROM PUBLIC;
+REVOKE ALL ON TABLE calendar_item FROM postgres;
+GRANT ALL ON TABLE calendar_item TO postgres;
+
+
+--
+-- Name: collection; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE collection FROM PUBLIC;
+REVOKE ALL ON TABLE collection FROM postgres;
+GRANT ALL ON TABLE collection TO postgres;
+
+
+--
+-- Name: dav_binding; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE dav_binding FROM PUBLIC;
+REVOKE ALL ON TABLE dav_binding FROM postgres;
+GRANT ALL ON TABLE dav_binding TO postgres;
+
+
+--
+-- Name: principal; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE principal FROM PUBLIC;
+REVOKE ALL ON TABLE principal FROM postgres;
+GRANT ALL ON TABLE principal TO postgres;
+
+
+--
+-- Name: usr; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE usr FROM PUBLIC;
+REVOKE ALL ON TABLE usr FROM postgres;
+GRANT ALL ON TABLE usr TO postgres;
+
+
+--
+-- Name: dav_principal; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE dav_principal FROM PUBLIC;
+REVOKE ALL ON TABLE dav_principal FROM postgres;
+GRANT ALL ON TABLE dav_principal TO postgres;
+
+
+--
+-- Name: freebusy_ticket; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE freebusy_ticket FROM PUBLIC;
+REVOKE ALL ON TABLE freebusy_ticket FROM postgres;
+GRANT ALL ON TABLE freebusy_ticket TO postgres;
+
+
+--
+-- Name: grants; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE grants FROM PUBLIC;
+REVOKE ALL ON TABLE grants FROM postgres;
+GRANT ALL ON TABLE grants TO postgres;
+
+
+--
+-- Name: group_member; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE group_member FROM PUBLIC;
+REVOKE ALL ON TABLE group_member FROM postgres;
+GRANT ALL ON TABLE group_member TO postgres;
+
+
+--
+-- Name: locks; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE locks FROM PUBLIC;
+REVOKE ALL ON TABLE locks FROM postgres;
+GRANT ALL ON TABLE locks TO postgres;
+
+
+--
+-- Name: principal_type; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE principal_type FROM PUBLIC;
+REVOKE ALL ON TABLE principal_type FROM postgres;
+GRANT ALL ON TABLE principal_type TO postgres;
+
+
+--
+-- Name: principal_type_principal_type_id_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM postgres;
+GRANT ALL ON SEQUENCE principal_type_principal_type_id_seq TO postgres;
+
+
+--
+-- Name: property; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE property FROM PUBLIC;
+REVOKE ALL ON TABLE property FROM postgres;
+GRANT ALL ON TABLE property TO postgres;
+
+
+--
+-- Name: relationship; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE relationship FROM PUBLIC;
+REVOKE ALL ON TABLE relationship FROM postgres;
+GRANT ALL ON TABLE relationship TO postgres;
+
+
+--
+-- Name: relationship_type; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE relationship_type FROM PUBLIC;
+REVOKE ALL ON TABLE relationship_type FROM postgres;
+GRANT ALL ON TABLE relationship_type TO postgres;
+
+
+--
+-- Name: relationship_type_rt_id_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM postgres;
+GRANT ALL ON SEQUENCE relationship_type_rt_id_seq TO postgres;
+
+
+--
+-- Name: role_member; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE role_member FROM PUBLIC;
+REVOKE ALL ON TABLE role_member FROM postgres;
+GRANT ALL ON TABLE role_member TO postgres;
+
+
+--
+-- Name: roles; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE roles FROM PUBLIC;
+REVOKE ALL ON TABLE roles FROM postgres;
+GRANT ALL ON TABLE roles TO postgres;
+
+
+--
+-- Name: roles_role_no_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE roles_role_no_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE roles_role_no_seq FROM postgres;
+GRANT ALL ON SEQUENCE roles_role_no_seq TO postgres;
+
+
+--
+-- Name: session; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE session FROM PUBLIC;
+REVOKE ALL ON TABLE session FROM postgres;
+GRANT ALL ON TABLE session TO postgres;
+
+
+--
+-- Name: session_session_id_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE session_session_id_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE session_session_id_seq FROM postgres;
+GRANT ALL ON SEQUENCE session_session_id_seq TO postgres;
+
+
+--
+-- Name: supported_locales; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE supported_locales FROM PUBLIC;
+REVOKE ALL ON TABLE supported_locales FROM postgres;
+GRANT ALL ON TABLE supported_locales TO postgres;
+
+
+--
+-- Name: sync_changes; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE sync_changes FROM PUBLIC;
+REVOKE ALL ON TABLE sync_changes FROM postgres;
+GRANT ALL ON TABLE sync_changes TO postgres;
+
+
+--
+-- Name: sync_tokens; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE sync_tokens FROM PUBLIC;
+REVOKE ALL ON TABLE sync_tokens FROM postgres;
+GRANT ALL ON TABLE sync_tokens TO postgres;
+
+
+--
+-- Name: sync_tokens_sync_token_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM postgres;
+GRANT ALL ON SEQUENCE sync_tokens_sync_token_seq TO postgres;
+
+
+--
+-- Name: timezones; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE timezones FROM PUBLIC;
+REVOKE ALL ON TABLE timezones FROM postgres;
+GRANT ALL ON TABLE timezones TO postgres;
+
+
+--
+-- Name: timezones_our_tzno_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM postgres;
+GRANT ALL ON SEQUENCE timezones_our_tzno_seq TO postgres;
+
+
+--
+-- Name: tmp_password; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE tmp_password FROM PUBLIC;
+REVOKE ALL ON TABLE tmp_password FROM postgres;
+GRANT ALL ON TABLE tmp_password TO postgres;
+
+
+--
+-- Name: tz_aliases; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE tz_aliases FROM PUBLIC;
+REVOKE ALL ON TABLE tz_aliases FROM postgres;
+GRANT ALL ON TABLE tz_aliases TO postgres;
+
+
+--
+-- Name: tz_localnames; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE tz_localnames FROM PUBLIC;
+REVOKE ALL ON TABLE tz_localnames FROM postgres;
+GRANT ALL ON TABLE tz_localnames TO postgres;
+
+
+--
+-- Name: usr_setting; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON TABLE usr_setting FROM PUBLIC;
+REVOKE ALL ON TABLE usr_setting FROM postgres;
+GRANT ALL ON TABLE usr_setting TO postgres;
+
+
+--
+-- Name: usr_user_no_seq; Type: ACL; Schema: public; Owner: postgres
+--
+
+REVOKE ALL ON SEQUENCE usr_user_no_seq FROM PUBLIC;
+REVOKE ALL ON SEQUENCE usr_user_no_seq FROM postgres;
+GRANT ALL ON SEQUENCE usr_user_no_seq TO postgres;
+
+
+--
+-- PostgreSQL database dump complete
+--
+
projects / mailshears.git / commitdiff