--
--


--
-- 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';



--
-- 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;



--
-- 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))); );


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;


--
--

