test/sql/davical.sql

   1 --
   2 -- PostgreSQL database dump
   3 --
   4
   5 SET statement_timeout = 0;
   6 SET client_encoding = 'UTF8';
   7 SET standard_conforming_strings = on;
   8 SET check_function_bodies = false;
   9 SET client_min_messages = warning;
  10
  11 --
  12 -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
  13 --
  14
  15 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
  16
  17
  18 --
  19 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
  20 --
  21
  22 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
  23
  24
  25 SET search_path = public, pg_catalog;
  26
  27 --
  28 -- Name: rrule_instance; Type: TYPE; Schema: public; Owner: postgres
  29 --
  30
  31 CREATE TYPE rrule_instance AS (
  32         dtstart timestamp with time zone,
  33         rrule text,
  34         instance timestamp with time zone
  35 );
  36
  37
  38 ALTER TYPE public.rrule_instance OWNER TO postgres;
  39
  40 --
  41 -- Name: rrule_parts; Type: TYPE; Schema: public; Owner: postgres
  42 --
  43
  44 CREATE TYPE rrule_parts AS (
  45         base timestamp with time zone,
  46         until timestamp with time zone,
  47         freq text,
  48         count integer,
  49         "interval" integer,
  50         bysecond integer[],
  51         byminute integer[],
  52         byhour integer[],
  53         bymonthday integer[],
  54         byyearday integer[],
  55         byweekno integer[],
  56         byday text[],
  57         bymonth integer[],
  58         bysetpos integer[],
  59         wkst text
  60 );
  61
  62
  63 ALTER TYPE public.rrule_parts OWNER TO postgres;
  64
  65 --
  66 -- Name: alarm_changed(); Type: FUNCTION; Schema: public; Owner: postgres
  67 --
  68
  69 CREATE FUNCTION alarm_changed() RETURNS trigger
  70     LANGUAGE plpgsql
  71     AS $$
  72 DECLARE
  73   oldcomponent TEXT;
  74   newcomponent TEXT;
  75 BEGIN
  76   -- in case we trigger on other events in future
  77   IF TG_OP = 'UPDATE' THEN
  78     IF NEW.component != OLD.component THEN
  79       UPDATE caldav_data
  80          SET caldav_data = replace( caldav_data, OLD.component, NEW.component ),
  81              dav_etag = md5(replace( caldav_data, OLD.component, NEW.component ))
  82        WHERE caldav_data.dav_id = NEW.dav_id;
  83     END IF;
  84   END IF;
  85   RETURN NEW;
  86 END;
  87 $$;
  88
  89
  90 ALTER FUNCTION public.alarm_changed() OWNER TO postgres;
  91
  92 --
  93 -- Name: apply_month_byday(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
  94 --
  95
  96 CREATE FUNCTION apply_month_byday(timestamp with time zone, text) RETURNS timestamp with time zone
  97     LANGUAGE plpgsql IMMUTABLE STRICT
  98     AS $_$
  99 DECLARE
 100   in_time ALIAS FOR $1;
 101   byday ALIAS FOR $2;
 102   weeks INT;
 103   dow INT;
 104   temp_txt TEXT;
 105   dd INT;
 106   mm INT;
 107   yy INT;
 108   our_dow INT;
 109   our_answer TIMESTAMP WITH TIME ZONE;
 110 BEGIN
 111   dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2;
 112   temp_txt   := substring(byday from '([0-9]+)');
 113   weeks      := temp_txt::int;
 114
 115   -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt;
 116
 117   IF substring(byday for 1) = '-' THEN
 118     -- Last XX of month, or possibly second-to-last, but unlikely
 119     mm := extract( 'month' from in_time);
 120     yy := extract( 'year' from in_time);
 121
 122     -- Start with the last day of the month
 123     our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval;
 124     dd := extract( 'dow' from our_answer);
 125     dd := dd - dow;
 126     IF dd < 0 THEN
 127       dd := dd + 7;
 128     END IF;
 129
 130     -- Having calculated the right day of the month, we now apply that back to in_time
 131     -- which contains the otherwise-unobtainable timezone detail (and the time)
 132     our_answer = our_answer - (dd::text || 'days')::interval;
 133     dd := extract( 'day' from our_answer) - extract( 'day' from in_time);
 134     our_answer := in_time + (dd::text || 'days')::interval;
 135
 136     IF weeks > 1 THEN
 137       weeks := weeks - 1;
 138       our_answer := our_answer - (weeks::text || 'weeks')::interval;
 139     END IF;
 140
 141   ELSE
 142
 143     -- Shift our date to the correct day of week..
 144     our_dow := extract( 'dow' from in_time);
 145     our_dow := our_dow - dow;
 146     dd := extract( 'day' from in_time);
 147     IF our_dow >= dd THEN
 148       our_dow := our_dow - 7;
 149     END IF;
 150     our_answer := in_time - (our_dow::text || 'days')::interval;
 151     dd = extract( 'day' from our_answer);
 152
 153     -- Shift the date to the correct week...
 154     dd := weeks - ((dd+6) / 7);
 155     IF dd != 0 THEN
 156       our_answer := our_answer + ((dd::text || 'weeks')::interval);
 157     END IF;
 158
 159   END IF;
 160
 161   RETURN our_answer;
 162
 163 END;
 164 $_$;
 165
 166
 167 ALTER FUNCTION public.apply_month_byday(timestamp with time zone, text) OWNER TO postgres;
 168
 169 --
 170 -- Name: bits_to_legacy_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
 171 --
 172
 173 CREATE FUNCTION bits_to_legacy_privilege(bit) RETURNS text
 174     LANGUAGE plpgsql IMMUTABLE STRICT
 175     AS $_$
 176 DECLARE
 177   in_bits ALIAS FOR $1;
 178   out_priv TEXT;
 179 BEGIN
 180   out_priv := '';
 181   IF in_bits = (~ 0::BIT(24)) THEN
 182     out_priv = 'A';
 183     RETURN out_priv;
 184   END IF;
 185
 186   -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
 187   --    1 DAV:read
 188   --  512 CalDAV:read-free-busy
 189   -- 4096 CALDAV:schedule-query-freebusy
 190   IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN
 191     IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
 192       out_priv := 'R';
 193     ELSE
 194       out_priv := 'F';
 195     END IF;
 196   END IF;
 197
 198   -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
 199   --    2 DAV:write-properties
 200   --    4 DAV:write-content
 201   --   64 DAV:bind
 202   --  128 DAV:unbind
 203   IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
 204     IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN
 205       out_priv := out_priv || 'W';
 206     ELSE
 207       IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
 208         out_priv := out_priv || 'B';
 209       END IF;
 210       IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
 211         out_priv := out_priv || 'U';
 212       END IF;
 213     END IF;
 214   END IF;
 215
 216   RETURN out_priv;
 217 END
 218 $_$;
 219
 220
 221 ALTER FUNCTION public.bits_to_legacy_privilege(bit) OWNER TO postgres;
 222
 223 --
 224 -- Name: bits_to_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres
 225 --
 226
 227 CREATE FUNCTION bits_to_privilege(bit) RETURNS text[]
 228     LANGUAGE plpgsql IMMUTABLE STRICT
 229     AS $_$
 230 DECLARE
 231   in_bits ALIAS FOR $1;
 232   out_priv TEXT[];
 233 BEGIN
 234   IF in_bits = (~ 0::BIT(24)) THEN
 235     out_priv := out_priv || ARRAY['DAV:all'];
 236   END IF;
 237
 238   IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN
 239     IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN
 240       out_priv := out_priv || ARRAY['DAV:read'];
 241     END IF;
 242     IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN
 243       out_priv := out_priv || ARRAY['caldav:read-free-busy'];
 244     END IF;
 245   END IF;
 246
 247   IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN
 248     IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN
 249       out_priv := out_priv || ARRAY['DAV:write'];
 250     ELSE
 251       IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN
 252         out_priv := out_priv || ARRAY['DAV:write-properties'];
 253       END IF;
 254       IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN
 255         out_priv := out_priv || ARRAY['DAV:write-content'];
 256       END IF;
 257       IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN
 258         out_priv := out_priv || ARRAY['DAV:bind'];
 259       END IF;
 260       IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN
 261         out_priv := out_priv || ARRAY['DAV:unbind'];
 262       END IF;
 263     END IF;
 264   END IF;
 265
 266   IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN
 267     out_priv := out_priv || ARRAY['DAV:unlock'];
 268   END IF;
 269
 270   IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN
 271     out_priv := out_priv || ARRAY['DAV:read-acl'];
 272   END IF;
 273
 274   IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN
 275     out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set'];
 276   END IF;
 277
 278   IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN
 279     out_priv := out_priv || ARRAY['DAV:write-acl'];
 280   END IF;
 281
 282   IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN
 283     IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN
 284       out_priv := out_priv || ARRAY['caldav:schedule-deliver'];
 285     ELSE
 286       IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN
 287         out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite'];
 288       END IF;
 289       IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN
 290         out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply'];
 291       END IF;
 292       IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN
 293         out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy'];
 294       END IF;
 295     END IF;
 296   END IF;
 297
 298   IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN
 299     IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN
 300       out_priv := out_priv || ARRAY['caldav:schedule-send'];
 301     ELSE
 302       IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN
 303         out_priv := out_priv || ARRAY['caldav:schedule-send-invite'];
 304       END IF;
 305       IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN
 306         out_priv := out_priv || ARRAY['caldav:schedule-send-reply'];
 307       END IF;
 308       IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN
 309         out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy'];
 310       END IF;
 311     END IF;
 312   END IF;
 313
 314   RETURN out_priv;
 315 END
 316 $_$;
 317
 318
 319 ALTER FUNCTION public.bits_to_privilege(bit) OWNER TO postgres;
 320
 321 --
 322 -- Name: calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
 323 --
 324
 325 CREATE FUNCTION calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) RETURNS timestamp with time zone
 326     LANGUAGE plpgsql IMMUTABLE STRICT
 327     AS $_$
 328 DECLARE
 329   earliest ALIAS FOR $1;
 330   basedate ALIAS FOR $2;
 331   repeatrule ALIAS FOR $3;
 332   frequency TEXT;
 333   temp_txt TEXT;
 334   length INT;
 335   count INT;
 336   byday TEXT;
 337   bymonthday INT;
 338   basediff INTERVAL;
 339   past_repeats INT8;
 340   units TEXT;
 341   dow TEXT;
 342   our_answer TIMESTAMP WITH TIME ZONE;
 343   loopcount INT;
 344 BEGIN
 345   IF basedate > earliest THEN
 346     RETURN basedate;
 347   END IF;
 348
 349   temp_txt   := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
 350   IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN
 351     RETURN NULL;
 352   END IF;
 353
 354   frequency  := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
 355   IF frequency IS NULL THEN
 356     RETURN NULL;
 357   END IF;
 358
 359   past_repeats = 0;
 360   length = 1;
 361   temp_txt   := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)');
 362   IF temp_txt IS NOT NULL THEN
 363     length     := temp_txt::int;
 364     basediff   := earliest - basedate;
 365
 366     -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff;
 367
 368     -- Calculate the number of past periods between our base date and our earliest date
 369     IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN
 370       past_repeats := extract('epoch' from basediff)::INT8 / 86400;
 371       -- RAISE NOTICE 'Days: %', past_repeats;
 372       IF frequency = 'WEEKLY' THEN
 373         past_repeats := past_repeats / 7;
 374       END IF;
 375     ELSE
 376       past_repeats = extract( 'years' from basediff );
 377       IF frequency = 'MONTHLY' THEN
 378         past_repeats = (past_repeats *12) + extract( 'months' from basediff );
 379       END IF;
 380     END IF;
 381     IF length IS NOT NULL THEN
 382       past_repeats = (past_repeats / length) + 1;
 383     END IF;
 384   END IF;
 385
 386   -- Check that we have not exceeded the COUNT= limit
 387   temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
 388   IF temp_txt IS NOT NULL THEN
 389     count := temp_txt::int;
 390     -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length;
 391     IF ( count <= past_repeats ) THEN
 392       RETURN NULL;
 393     END IF;
 394   ELSE
 395     count := NULL;
 396   END IF;
 397
 398   temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)');
 399   byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)');
 400   IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN
 401     -- Since this could move the date around a month we go back one
 402     -- period just to be extra sure.
 403     past_repeats = past_repeats - 1;
 404
 405     IF temp_txt IS NOT NULL THEN
 406       -- Crudely hack the BYSETPOS onto the front of BYDAY.  While this
 407       -- is not as per rfc2445, RRULE syntax is so complex and overblown
 408       -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and
 409       -- certainly not within a MONTHLY RRULE.
 410       byday := temp_txt || byday;
 411     END IF;
 412   END IF;
 413
 414   past_repeats = past_repeats * length;
 415
 416   units := CASE
 417     WHEN frequency = 'DAILY' THEN 'days'
 418     WHEN frequency = 'WEEKLY' THEN 'weeks'
 419     WHEN frequency = 'MONTHLY' THEN 'months'
 420     WHEN frequency = 'YEARLY' THEN 'years'
 421   END;
 422
 423   temp_txt   := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)');
 424   bymonthday := temp_txt::int;
 425
 426   -- With all of the above calculation, this date should be close to (but less than)
 427   -- the target, and we should only loop once or twice.
 428   our_answer := basedate + (past_repeats::text || units)::interval;
 429
 430   IF our_answer IS NULL THEN
 431     RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units;
 432   END IF;
 433
 434
 435   loopcount := 500;  -- Desirable to stop an infinite loop if there is something we cannot handle
 436   LOOP
 437     -- RAISE NOTICE 'Testing date: %', our_answer;
 438     IF frequency = 'DAILY' THEN
 439       IF byday IS NOT NULL THEN
 440         LOOP
 441           dow = substring( to_char( our_answer, 'DY' ) for 2);
 442           EXIT WHEN byday ~* dow;
 443           -- Increment for our next time through the loop...
 444           our_answer := our_answer + (length::text || units)::interval;
 445         END LOOP;
 446       END IF;
 447     ELSIF frequency = 'WEEKLY' THEN
 448       -- Weekly repeats are only on specific days
 449       -- This is really not right, since a WEEKLY on MO,WE,FR should
 450       -- occur three times each week and this will only be once a week.
 451       dow = substring( to_char( our_answer, 'DY' ) for 2);
 452     ELSIF frequency = 'MONTHLY' THEN
 453       IF byday IS NOT NULL THEN
 454         -- This works fine, except that maybe there are multiple BYDAY
 455         -- components.  e.g. 1TU,3TU might be 1st & 3rd tuesdays.
 456         our_answer := apply_month_byday( our_answer, byday );
 457       ELSE
 458         -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month
 459         our_answer := our_answer + '1 month'::interval;
 460       END IF;
 461     ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN
 462       -- We do not deal with this situation at present
 463       RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.';
 464     END IF;
 465
 466     EXIT WHEN our_answer >= earliest;
 467
 468     -- Give up if we have exceeded the count
 469     IF ( count IS NOT NULL AND past_repeats > count ) THEN
 470       RETURN NULL;
 471     ELSE
 472       past_repeats := past_repeats + 1;
 473     END IF;
 474
 475     loopcount := loopcount - 1;
 476     IF loopcount < 0 THEN
 477       RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest;
 478       RETURN NULL;
 479     END IF;
 480
 481     -- Increment for our next time through the loop...
 482     our_answer := our_answer + (length::text || units)::interval;
 483
 484   END LOOP;
 485
 486   RETURN our_answer;
 487
 488 END;
 489 $_$;
 490
 491
 492 ALTER FUNCTION public.calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) OWNER TO postgres;
 493
 494 --
 495 -- Name: caldav_data_modified(); Type: FUNCTION; Schema: public; Owner: postgres
 496 --
 497
 498 CREATE FUNCTION caldav_data_modified() RETURNS trigger
 499     LANGUAGE plpgsql
 500     AS $$
 501 DECLARE
 502   coll_id caldav_data.collection_id%TYPE;
 503 BEGIN
 504   IF TG_OP = 'UPDATE' THEN
 505     IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN
 506       -- Nothing for us to do
 507       RETURN NEW;
 508     END IF;
 509   END IF;
 510
 511   IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
 512     -- On insert or update modified, we set the NEW collection tag to the md5 of the
 513     -- etag of the updated row which gives us something predictable for our regression
 514     -- tests, but something different from the actual etag of the new event.
 515     UPDATE collection
 516        SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag)
 517      WHERE collection_id = NEW.collection_id;
 518     IF TG_OP = 'INSERT' THEN
 519       RETURN NEW;
 520     END IF;
 521   END IF;
 522
 523   IF TG_OP = 'DELETE' THEN
 524     -- On delete we set the OLD collection tag to the md5 of the old path & the old
 525     -- etag, which again gives us something predictable for our regression tests.
 526     UPDATE collection
 527        SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
 528      WHERE collection_id = OLD.collection_id;
 529     RETURN OLD;
 530   END IF;
 531
 532   IF NEW.collection_id != OLD.collection_id THEN
 533     -- If we've switched the collection_id of this event, then we also need to update
 534     -- the etag of the old collection - as we do for delete.
 535     UPDATE collection
 536        SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag)
 537      WHERE collection_id = OLD.collection_id;
 538   END IF;
 539   RETURN NEW;
 540 END;
 541 $$;
 542
 543
 544 ALTER FUNCTION public.caldav_data_modified() OWNER TO postgres;
 545
 546 --
 547 -- Name: check_db_revision(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
 548 --
 549
 550 CREATE FUNCTION check_db_revision(integer, integer, integer) RETURNS boolean
 551     LANGUAGE plpgsql
 552     AS $_$
 553    DECLARE
 554       major ALIAS FOR $1;
 555       minor ALIAS FOR $2;
 556       patch ALIAS FOR $3;
 557       matching INT;
 558    BEGIN
 559       SELECT COUNT(*) INTO matching FROM awl_db_revision
 560              WHERE (schema_major = major AND schema_minor = minor AND schema_patch > patch)
 561                 OR (schema_major = major AND schema_minor > minor)
 562                 OR (schema_major > major)
 563              ;
 564       IF matching >= 1 THEN
 565         RAISE EXCEPTION 'Database revisions after %.%.% have already been applied.', major, minor, patch;
 566         RETURN FALSE;
 567       END IF;
 568       SELECT COUNT(*) INTO matching FROM awl_db_revision
 569                       WHERE schema_major = major AND schema_minor = minor AND schema_patch = patch;
 570       IF matching >= 1 THEN
 571         RETURN TRUE;
 572       END IF;
 573       RAISE EXCEPTION 'Database has not been upgraded to %.%.%', major, minor, patch;
 574       RETURN FALSE;
 575    END;
 576 $_$;
 577
 578
 579 ALTER FUNCTION public.check_db_revision(integer, integer, integer) OWNER TO postgres;
 580
 581 --
 582 -- Name: collection_modified(); Type: FUNCTION; Schema: public; Owner: postgres
 583 --
 584
 585 CREATE FUNCTION collection_modified() RETURNS trigger
 586     LANGUAGE plpgsql
 587     AS $$
 588 DECLARE
 589 BEGIN
 590   -- in case we trigger on other events in future
 591   IF TG_OP = 'UPDATE' THEN
 592     IF NEW.dav_name != OLD.dav_name THEN
 593       UPDATE caldav_data
 594         SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name),
 595             user_no = NEW.user_no
 596       WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name;
 597     END IF;
 598   END IF;
 599   RETURN NEW;
 600 END;
 601 $$;
 602
 603
 604 ALTER FUNCTION public.collection_modified() OWNER TO postgres;
 605
 606 --
 607 -- Name: collections_within(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
 608 --
 609
 610 CREATE FUNCTION collections_within(integer, integer) RETURNS SETOF integer
 611     LANGUAGE plpgsql
 612     AS $_$
 613 DECLARE
 614     in_collection_id ALIAS FOR $1;
 615     in_depth ALIAS FOR $2;
 616     resource_id INT;
 617     found_some BOOLEAN;
 618 BEGIN
 619     in_depth := in_depth - 1;
 620     found_some = FALSE;
 621         FOR resource_id IN SELECT b.bound_source_id FROM dav_binding b
 622                         JOIN collection pc ON (b.parent_container = pc.dav_name)
 623                         WHERE pc.collection_id = in_collection_id
 624     LOOP
 625         found_some = TRUE;
 626         RETURN NEXT resource_id;
 627         IF in_depth > 0 THEN
 628             FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
 629                 RETURN NEXT resource_id;
 630             END LOOP;
 631         END IF;
 632     END LOOP;
 633     FOR resource_id IN SELECT c.collection_id FROM collection c
 634                         JOIN collection pc ON (c.parent_container = pc.dav_name)
 635                         WHERE pc.collection_id = in_collection_id
 636     LOOP
 637         found_some = TRUE;
 638         RETURN NEXT resource_id;
 639         IF in_depth > 0 THEN
 640             FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
 641                 RETURN NEXT resource_id;
 642             END LOOP;
 643         END IF;
 644     END LOOP;
 645     IF found_some THEN
 646         RETURN;
 647     END IF;
 648     FOR resource_id IN SELECT c.collection_id FROM collection c
 649                         JOIN dav_principal pc ON (c.parent_container = pc.dav_name)
 650                         WHERE pc.principal_id = in_collection_id
 651     LOOP
 652         RETURN NEXT resource_id;
 653         IF in_depth > 0 THEN
 654             FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP
 655                 RETURN NEXT resource_id;
 656             END LOOP;
 657         END IF;
 658     END LOOP;
 659 END;
 660 $_$;
 661
 662
 663 ALTER FUNCTION public.collections_within(integer, integer) OWNER TO postgres;
 664
 665 --
 666 -- Name: daily_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
 667 --
 668
 669 CREATE FUNCTION daily_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
 670     LANGUAGE plpgsql IMMUTABLE STRICT
 671     AS $_$
 672 DECLARE
 673   after ALIAS FOR $1;
 674   rrule ALIAS FOR $2;
 675 BEGIN
 676
 677   IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN
 678     RETURN;
 679   END IF;
 680
 681   IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN
 682     RETURN;
 683   END IF;
 684
 685   IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN
 686     RETURN;
 687   END IF;
 688
 689   IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN
 690     RETURN;
 691   END IF;
 692
 693   IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN
 694     RETURN;
 695   END IF;
 696
 697   -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial
 698   RETURN NEXT after;
 699
 700 END;
 701 $_$;
 702
 703
 704 ALTER FUNCTION public.daily_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
 705
 706 --
 707 -- Name: event_has_exceptions(text); Type: FUNCTION; Schema: public; Owner: postgres
 708 --
 709
 710 CREATE FUNCTION event_has_exceptions(text) RETURNS boolean
 711     LANGUAGE sql IMMUTABLE STRICT
 712     AS $_$
 713   SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?'
 714 $_$;
 715
 716
 717 ALTER FUNCTION public.event_has_exceptions(text) OWNER TO postgres;
 718
 719 --
 720 -- Name: event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
 721 --
 722
 723 CREATE FUNCTION event_instances(timestamp with time zone, text) RETURNS SETOF timestamp with time zone
 724     LANGUAGE plpgsql IMMUTABLE STRICT
 725     AS $_$
 726 DECLARE
 727   basedate ALIAS FOR $1;
 728   repeatrule ALIAS FOR $2;
 729   maxdate TIMESTAMP WITH TIME ZONE;
 730 BEGIN
 731   maxdate := current_date + '10 years'::interval;
 732   RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d;
 733 END;
 734 $_$;
 735
 736
 737 ALTER FUNCTION public.event_instances(timestamp with time zone, text) OWNER TO postgres;
 738
 739 --
 740 -- Name: expand_members(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
 741 --
 742
 743 CREATE FUNCTION expand_members(bigint, integer) RETURNS SETOF bigint
 744     LANGUAGE sql STABLE STRICT
 745     AS $_$
 746   SELECT member_id FROM group_member WHERE group_id = $1
 747       UNION
 748   SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id
 749                                FROM group_member WHERE group_id = $1) AS expanded
 750                        WHERE expanded.m_id IS NOT NULL;
 751 $_$;
 752
 753
 754 ALTER FUNCTION public.expand_members(bigint, integer) OWNER TO postgres;
 755
 756 --
 757 -- Name: expand_memberships(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
 758 --
 759
 760 CREATE FUNCTION expand_memberships(bigint, integer) RETURNS SETOF bigint
 761     LANGUAGE sql STABLE STRICT
 762     AS $_$
 763   SELECT group_id FROM group_member WHERE member_id = $1
 764       UNION
 765   SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id
 766                                FROM group_member WHERE member_id = $1) AS expanded
 767                        WHERE expanded.g_id IS NOT NULL;
 768 $_$;
 769
 770
 771 ALTER FUNCTION public.expand_memberships(bigint, integer) OWNER TO postgres;
 772
 773 --
 774 -- Name: get_group_role_no(); Type: FUNCTION; Schema: public; Owner: postgres
 775 --
 776
 777 CREATE FUNCTION get_group_role_no() RETURNS integer
 778     LANGUAGE sql IMMUTABLE
 779     AS $$
 780   SELECT role_no FROM roles WHERE role_name = 'Group'
 781 $$;
 782
 783
 784 ALTER FUNCTION public.get_group_role_no() OWNER TO postgres;
 785
 786 --
 787 -- Name: get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
 788 --
 789
 790 CREATE FUNCTION get_permissions(integer, integer) RETURNS text
 791     LANGUAGE plpgsql IMMUTABLE STRICT
 792     AS $_$
 793 DECLARE
 794   in_from ALIAS FOR $1;
 795   in_to   ALIAS FOR $2;
 796   out_confers TEXT;
 797   bit_confers BIT(24);
 798   group_role_no INT;
 799   tmp_txt TEXT;
 800   dbg TEXT DEFAULT '';
 801   r RECORD;
 802   counter INT;
 803 BEGIN
 804   -- Self can always have full access
 805   IF in_from = in_to THEN
 806     RETURN 'A';
 807   END IF;
 808
 809   -- dbg := 'S-';
 810   SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1
 811                     WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
 812   IF FOUND THEN
 813     RETURN dbg || out_confers;
 814   END IF;
 815   -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
 816
 817   SELECT bit_or(r1.confers & r2.confers) INTO bit_confers
 818               FROM relationship r1
 819               JOIN relationship r2 ON r1.to_user=r2.from_user
 820          WHERE r1.from_user=in_from AND r2.to_user=in_to
 821            AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group');
 822   IF bit_confers != 0::BIT(24) THEN
 823     RETURN dbg || bits_to_legacy_privilege(bit_confers);
 824   END IF;
 825
 826   RETURN '';
 827   -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
 828
 829   SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 LEFT OUTER JOIN relationship r2 ON(r1.to_user = r2.to_user)
 830        WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user
 831          AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ;
 832
 833   IF FOUND THEN
 834     -- dbg := 'H-';
 835     -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
 836     RETURN dbg || out_confers;
 837   END IF;
 838
 839   -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
 840
 841   RETURN '';
 842 END;
 843 $_$;
 844
 845
 846 ALTER FUNCTION public.get_permissions(integer, integer) OWNER TO postgres;
 847
 848 --
 849 -- Name: get_usr_setting(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
 850 --
 851
 852 CREATE FUNCTION get_usr_setting(integer, text) RETURNS text
 853     LANGUAGE sql
 854     AS $_$SELECT setting_value FROM usr_setting
 855             WHERE usr_setting.user_no = $1
 856             AND usr_setting.setting_name = $2 $_$;
 857
 858
 859 ALTER FUNCTION public.get_usr_setting(integer, text) OWNER TO postgres;
 860
 861 --
 862 -- Name: grants_modified(); Type: FUNCTION; Schema: public; Owner: postgres
 863 --
 864
 865 CREATE FUNCTION grants_modified() RETURNS trigger
 866     LANGUAGE plpgsql
 867     AS $$
 868 DECLARE
 869   old_to_principal INT8;
 870   new_is_group BOOL;
 871 BEGIN
 872   -- in case we trigger on other events in future
 873   IF TG_OP = 'INSERT' THEN
 874     old_to_principal := NULL;
 875   ELSE
 876     old_to_principal := OLD.to_principal;
 877   END IF;
 878   IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN
 879     SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal;
 880     IF NEW.is_group != new_is_group THEN
 881       NEW.is_group := new_is_group;
 882     END IF;
 883   END IF;
 884   RETURN NEW;
 885 END;
 886 $$;
 887
 888
 889 ALTER FUNCTION public.grants_modified() OWNER TO postgres;
 890
 891 --
 892 -- Name: grants_proxy_access_from_p(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
 893 --
 894
 895 CREATE FUNCTION grants_proxy_access_from_p(bigint, integer) RETURNS SETOF bigint
 896     LANGUAGE sql STABLE STRICT
 897     AS $_$
 898   SELECT DISTINCT by_principal
 899     FROM grants
 900    WHERE by_collection IS NULL AND by_principal != $1
 901      AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1)
 902    ;
 903 $_$;
 904
 905
 906 ALTER FUNCTION public.grants_proxy_access_from_p(bigint, integer) OWNER TO postgres;
 907
 908 --
 909 -- Name: has_legacy_privilege(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
 910 --
 911
 912 CREATE FUNCTION has_legacy_privilege(integer, text, integer) RETURNS boolean
 913     LANGUAGE plpgsql IMMUTABLE STRICT
 914     AS $_$
 915 DECLARE
 916   in_from ALIAS FOR $1;
 917   in_legacy_privilege ALIAS FOR $2;
 918   in_to   ALIAS FOR $3;
 919   in_confers BIT(24);
 920   group_role_no INT;
 921 BEGIN
 922   -- Self can always have full access
 923   IF in_from = in_to THEN
 924     RETURN TRUE;
 925   END IF;
 926
 927   SELECT get_group_role_no() INTO group_role_no;
 928   SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers;
 929
 930   IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to
 931                       AND (in_confers & confers) = in_confers
 932                       AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN
 933     -- A direct relationship from A to B that grants sufficient
 934     -- RAISE NOTICE 'Permissions directly granted';
 935     RETURN TRUE;
 936   END IF;
 937
 938   IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user
 939          WHERE (in_confers & r1.confers & r2.confers) = in_confers
 940            AND r1.from_user=in_from AND r2.to_user=in_to
 941            AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN
 942     -- An indirect relationship from A to B via group G that grants sufficient
 943     -- RAISE NOTICE 'Permissions mediated via group';
 944     RETURN TRUE;
 945   END IF;
 946
 947   IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user
 948          WHERE (in_confers & r1.confers & r2.confers) = in_confers
 949            AND r1.from_user=in_from AND r2.from_user=in_to
 950            AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no)
 951            AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN
 952     -- An indirect reflexive relationship from both A & B to group G which grants sufficient
 953     -- RAISE NOTICE 'Permissions to shared group';
 954     RETURN TRUE;
 955   END IF;
 956
 957   -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
 958
 959   RETURN FALSE;
 960 END;
 961 $_$;
 962
 963
 964 ALTER FUNCTION public.has_legacy_privilege(integer, text, integer) OWNER TO postgres;
 965
 966 --
 967 -- Name: has_members_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
 968 --
 969
 970 CREATE FUNCTION has_members_list(bigint) RETURNS text
 971     LANGUAGE plpgsql STRICT
 972     AS $_$
 973 DECLARE
 974   in_member_id ALIAS FOR $1;
 975   m RECORD;
 976   mlist TEXT;
 977 BEGIN
 978   mlist := '';
 979   FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id)
 980                           WHERE group_id = in_member_id
 981   LOOP
 982     mlist := mlist
 983              || CASE WHEN mlist = '' THEN '' ELSE ', ' END
 984              || COALESCE( m.displayname, m.group_id::text);
 985   END LOOP;
 986   RETURN mlist;
 987 END;
 988 $_$;
 989
 990
 991 ALTER FUNCTION public.has_members_list(bigint) OWNER TO postgres;
 992
 993 --
 994 -- Name: icalendar_interval_to_sql(text); Type: FUNCTION; Schema: public; Owner: postgres
 995 --
 996
 997 CREATE FUNCTION icalendar_interval_to_sql(text) RETURNS interval
 998     LANGUAGE sql IMMUTABLE STRICT
 999     AS $_$
1000   SELECT CASE WHEN substring($1,1,1) = '-' THEN -1 ELSE 1 END * regexp_replace( regexp_replace($1, '[PT-]', '', 'g'), '([A-Z])', E'\\1 ', 'g')::interval;
1001 $_$;
1002
1003
1004 ALTER FUNCTION public.icalendar_interval_to_sql(text) OWNER TO postgres;
1005
1006 --
1007 -- Name: is_member_of_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1008 --
1009
1010 CREATE FUNCTION is_member_of_list(bigint) RETURNS text
1011     LANGUAGE plpgsql STRICT
1012     AS $_$
1013 DECLARE
1014   in_member_id ALIAS FOR $1;
1015   m RECORD;
1016   mlist TEXT;
1017 BEGIN
1018   mlist := '';
1019   FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id)
1020                           WHERE member_id = in_member_id
1021   LOOP
1022     mlist := mlist
1023              || CASE WHEN mlist = '' THEN '' ELSE ', ' END
1024              || COALESCE( m.displayname, m.group_id::text);
1025   END LOOP;
1026   RETURN mlist;
1027 END;
1028 $_$;
1029
1030
1031 ALTER FUNCTION public.is_member_of_list(bigint) OWNER TO postgres;
1032
1033 --
1034 -- Name: legacy_get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
1035 --
1036
1037 CREATE FUNCTION legacy_get_permissions(integer, integer) RETURNS text
1038     LANGUAGE plpgsql IMMUTABLE STRICT
1039     AS $_$
1040 DECLARE
1041   in_from ALIAS FOR $1;
1042   in_to   ALIAS FOR $2;
1043   out_confers TEXT;
1044   tmp_confers1 TEXT;
1045   tmp_confers2 TEXT;
1046   tmp_txt TEXT;
1047   dbg TEXT DEFAULT '';
1048   r RECORD;
1049   counter INT;
1050 BEGIN
1051   -- Self can always have full access
1052   IF in_from = in_to THEN
1053     RETURN 'A';
1054   END IF;
1055
1056   -- dbg := 'S-';
1057   SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id )
1058                     WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group');
1059   IF FOUND THEN
1060     RETURN dbg || out_confers;
1061   END IF;
1062   -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to;
1063
1064   out_confers := '';
1065   FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id)
1066               JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id
1067          WHERE r1.from_user=in_from AND r2.to_user=in_to
1068            AND EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.to_user AND roles.role_name='Group')
1069            AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r2.to_user AND roles.role_name='Group')
1070            AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.from_user AND roles.role_name='Group')
1071   LOOP
1072     -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2;
1073     -- FIXME: This is an oversimplification
1074     -- dbg := 'C-';
1075     tmp_confers1 := r.r1;
1076     tmp_confers2 := r.r2;
1077     IF tmp_confers1 != tmp_confers2 THEN
1078       IF tmp_confers1 ~* 'A' THEN
1079         -- Ensure that A is expanded to all supported privs before being used as a mask
1080         tmp_confers1 := 'AFBRWU';
1081       END IF;
1082       IF tmp_confers2 ~* 'A' THEN
1083         -- Ensure that A is expanded to all supported privs before being used as a mask
1084         tmp_confers2 := 'AFBRWU';
1085       END IF;
1086       -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2;
1087       tmp_txt = '';
1088       FOR counter IN 1 .. length(tmp_confers2) LOOP
1089         IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN
1090           tmp_txt := tmp_txt || substring(tmp_confers2,counter,1);
1091         END IF;
1092       END LOOP;
1093       tmp_confers2 := tmp_txt;
1094     END IF;
1095     FOR counter IN 1 .. length(tmp_confers2) LOOP
1096       IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN
1097         out_confers := out_confers || substring(tmp_confers2,counter,1);
1098       END IF;
1099     END LOOP;
1100   END LOOP;
1101   IF out_confers ~* 'A' OR (out_confers ~* 'B' AND out_confers ~* 'F' AND out_confers ~* 'R' AND out_confers ~* 'W' AND out_confers ~* 'U') THEN
1102     out_confers := 'A';
1103   END IF;
1104   IF out_confers != '' THEN
1105     RETURN dbg || out_confers;
1106   END IF;
1107
1108   -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to;
1109
1110   SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id )
1111               LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id )
1112        WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user AND r1.to_user = r2.to_user
1113          AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user )
1114           AND usr_is_role(r1.to_user,'Group');
1115
1116   IF FOUND THEN
1117     -- dbg := 'H-';
1118     -- RAISE NOTICE 'Permissions to shared group % ', out_confers;
1119     RETURN dbg || out_confers;
1120   END IF;
1121
1122   -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to;
1123
1124   RETURN '';
1125 END;
1126 $_$;
1127
1128
1129 ALTER FUNCTION public.legacy_get_permissions(integer, integer) OWNER TO postgres;
1130
1131 --
1132 -- Name: legacy_privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1133 --
1134
1135 CREATE FUNCTION legacy_privilege_to_bits(text) RETURNS bit
1136     LANGUAGE plpgsql IMMUTABLE STRICT
1137     AS $_$
1138 DECLARE
1139   in_priv ALIAS FOR $1;
1140   out_bits BIT(24);
1141 BEGIN
1142   out_bits := 0::BIT(24);
1143   IF in_priv ~* 'A' THEN
1144     out_bits = ~ out_bits;
1145     RETURN out_bits;
1146   END IF;
1147
1148   -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege.
1149   --    1 DAV:read
1150   --  512 CalDAV:read-free-busy
1151   -- 4096 CALDAV:schedule-query-freebusy
1152   IF in_priv ~* 'R' THEN
1153     out_bits := out_bits | 4609::BIT(24);
1154   END IF;
1155
1156   -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content
1157   --    2 DAV:write-properties
1158   --    4 DAV:write-content
1159   --   64 DAV:bind
1160   --  128 DAV:unbind
1161   IF in_priv ~* 'W' THEN
1162     out_bits := out_bits |   198::BIT(24);
1163   END IF;
1164
1165   --   64 DAV:bind
1166   IF in_priv ~* 'B' THEN
1167     out_bits := out_bits | 64::BIT(24);
1168   END IF;
1169
1170   --  128 DAV:unbind
1171   IF in_priv ~* 'U' THEN
1172     out_bits := out_bits | 128::BIT(24);
1173   END IF;
1174
1175   --  512 CalDAV:read-free-busy
1176   -- 4096 CALDAV:schedule-query-freebusy
1177   IF in_priv ~* 'F' THEN
1178     out_bits := out_bits | 4608::BIT(24);
1179   END IF;
1180
1181   RETURN out_bits;
1182 END
1183 $_$;
1184
1185
1186 ALTER FUNCTION public.legacy_privilege_to_bits(text) OWNER TO postgres;
1187
1188 --
1189 -- Name: max_roles(); Type: FUNCTION; Schema: public; Owner: postgres
1190 --
1191
1192 CREATE FUNCTION max_roles() RETURNS integer
1193     LANGUAGE sql
1194     AS $$SELECT max(role_no) FROM roles$$;
1195
1196
1197 ALTER FUNCTION public.max_roles() OWNER TO postgres;
1198
1199 --
1200 -- Name: max_session(); Type: FUNCTION; Schema: public; Owner: postgres
1201 --
1202
1203 CREATE FUNCTION max_session() RETURNS integer
1204     LANGUAGE sql
1205     AS $$SELECT max(session_id) FROM session$$;
1206
1207
1208 ALTER FUNCTION public.max_session() OWNER TO postgres;
1209
1210 --
1211 -- Name: max_usr(); Type: FUNCTION; Schema: public; Owner: postgres
1212 --
1213
1214 CREATE FUNCTION max_usr() RETURNS integer
1215     LANGUAGE sql
1216     AS $$SELECT max(user_no) FROM usr$$;
1217
1218
1219 ALTER FUNCTION public.max_usr() OWNER TO postgres;
1220
1221 --
1222 -- Name: monthly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
1223 --
1224
1225 CREATE FUNCTION monthly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
1226     LANGUAGE plpgsql IMMUTABLE STRICT
1227     AS $_$
1228 DECLARE
1229   after ALIAS FOR $1;
1230   rrule ALIAS FOR $2;
1231   valid_date TIMESTAMP WITH TIME ZONE;
1232   curse REFCURSOR;
1233   setpos INT;
1234   i INT;
1235 BEGIN
1236
1237   /**
1238   * Need to investigate whether it is legal to set both of these, and whether
1239   * we are correct to UNION the results, or whether we should INTERSECT them.
1240   * So at this point, we refer to the specification, which grants us this
1241   * wonderfully enlightening vision:
1242   *
1243   *     If multiple BYxxx rule parts are specified, then after evaluating the
1244   *     specified FREQ and INTERVAL rule parts, the BYxxx rule parts are
1245   *     applied to the current set of evaluated occurrences in the following
1246   *     order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR,
1247   *     BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated.
1248   *
1249   * My guess is that this means 'INTERSECT'
1250   */
1251   IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN
1252     OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r
1253                 INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r
1254                     ORDER BY 1;
1255   ELSIF rrule.bymonthday IS NOT NULL THEN
1256     OPEN curse SCROLL FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1;
1257   ELSE
1258     OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1;
1259   END IF;
1260
1261   RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
1262
1263 END;
1264 $_$;
1265
1266
1267 ALTER FUNCTION public.monthly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
1268
1269 --
1270 -- Name: new_db_revision(integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
1271 --
1272
1273 CREATE FUNCTION new_db_revision(integer, integer, integer, text) RETURNS void
1274     LANGUAGE plpgsql
1275     AS $_$
1276    DECLARE
1277       major ALIAS FOR $1;
1278       minor ALIAS FOR $2;
1279       patch ALIAS FOR $3;
1280       blurb ALIAS FOR $4;
1281       new_id INT;
1282    BEGIN
1283       SELECT MAX(schema_id) + 1 INTO new_id FROM awl_db_revision;
1284       IF NOT FOUND OR new_id IS NULL THEN
1285         new_id := 1;
1286       END IF;
1287       INSERT INTO awl_db_revision (schema_id, schema_major, schema_minor, schema_patch, schema_name)
1288                     VALUES( new_id, major, minor, patch, blurb );
1289       RETURN;
1290    END;
1291 $_$;
1292
1293
1294 ALTER FUNCTION public.new_db_revision(integer, integer, integer, text) OWNER TO postgres;
1295
1296 --
1297 -- Name: new_sync_token(bigint, bigint); Type: FUNCTION; Schema: public; Owner: postgres
1298 --
1299
1300 CREATE FUNCTION new_sync_token(bigint, bigint) RETURNS bigint
1301     LANGUAGE plpgsql STRICT
1302     AS $_$
1303 DECLARE
1304   in_old_sync_token ALIAS FOR $1;
1305   in_collection_id ALIAS FOR $2;
1306   tmp_int INT8;
1307   new_token sync_tokens.sync_token%TYPE;
1308   old_modification_time sync_tokens.modification_time%TYPE;
1309 BEGIN
1310   IF in_old_sync_token > 0 THEN
1311     SELECT modification_time INTO old_modification_time FROM sync_tokens
1312            WHERE sync_token = in_old_sync_token AND collection_id = in_collection_id;
1313     IF NOT FOUND THEN
1314       -- They are in an inconsistent state: we return NULL so they can re-start the process
1315       RETURN NULL;
1316     END IF;
1317   END IF;
1318
1319   -- Find the most recent sync_token
1320   SELECT sync_token, modification_time INTO new_token, old_modification_time FROM sync_tokens
1321          WHERE collection_id = in_collection_id ORDER BY modification_time DESC LIMIT 1;
1322   IF FOUND THEN
1323     SELECT 1 INTO tmp_int FROM sync_changes WHERE collection_id = in_collection_id AND sync_time > old_modification_time LIMIT 1;
1324     IF NOT FOUND THEN
1325       -- Return the latest sync_token we have for this collection, since there are no changes.
1326       RETURN new_token;
1327     END IF;
1328   END IF;
1329
1330   -- Looks like we need a new sync_token for this collection...
1331   SELECT nextval('sync_tokens_sync_token_seq') INTO new_token;
1332   INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, new_token );
1333
1334   -- Having created our new token we do some clean-up of old tokens
1335   SELECT modification_time, sync_token INTO old_modification_time, tmp_int FROM sync_tokens
1336                 WHERE collection_id = in_collection_id AND modification_time < (current_timestamp - '7 days'::interval)
1337                 ORDER BY collection_id, modification_time DESC;
1338   DELETE FROM sync_changes WHERE collection_id = in_collection_id AND sync_time < old_modification_time;
1339   DELETE FROM sync_tokens WHERE collection_id = in_collection_id AND sync_token < tmp_int;
1340
1341   -- Returning the new token
1342   RETURN new_token;
1343 END
1344 $_$;
1345
1346
1347 ALTER FUNCTION public.new_sync_token(bigint, bigint) OWNER TO postgres;
1348
1349 --
1350 -- Name: p_has_proxy_access_to(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1351 --
1352
1353 CREATE FUNCTION p_has_proxy_access_to(bigint, integer) RETURNS SETOF bigint
1354     LANGUAGE sql STABLE STRICT
1355     AS $_$
1356   SELECT by_principal
1357     FROM (
1358       SELECT by_principal FROM grants
1359            WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2))
1360              AND (privileges & 5::BIT(24)) != 0::BIT(24)
1361              AND by_collection IS NULL
1362              AND by_principal != $1
1363       UNION
1364       SELECT principal_id AS by_principal FROM principal
1365            WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24)
1366              AND principal_id != $1
1367     ) subquery;
1368 $_$;
1369
1370
1371 ALTER FUNCTION public.p_has_proxy_access_to(bigint, integer) OWNER TO postgres;
1372
1373 --
1374 -- Name: parse_rrule_parts(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1375 --
1376
1377 CREATE FUNCTION parse_rrule_parts(timestamp with time zone, text) RETURNS rrule_parts
1378     LANGUAGE plpgsql IMMUTABLE STRICT
1379     AS $_$
1380 DECLARE
1381   basedate   ALIAS FOR $1;
1382   repeatrule ALIAS FOR $2;
1383   result rrule_parts%ROWTYPE;
1384   tempstr TEXT;
1385 BEGIN
1386   result.base       := basedate;
1387   result.until      := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)');
1388   result.freq       := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)');
1389   result.count      := substring(repeatrule from 'COUNT=([0-9]+)(;|$)');
1390   result.interval   := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1);
1391   result.wkst       := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)');
1392
1393   result.byday      := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ',');
1394
1395   result.byyearday  := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ',');
1396   result.byweekno   := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ',');
1397   result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ',');
1398   result.bymonth    := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ',');
1399   result.bysetpos   := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ',');
1400
1401   result.bysecond   := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ',');
1402   result.byminute   := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ',');
1403   result.byhour     := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ',');
1404
1405   RETURN result;
1406 END;
1407 $_$;
1408
1409
1410 ALTER FUNCTION public.parse_rrule_parts(timestamp with time zone, text) OWNER TO postgres;
1411
1412 --
1413 -- Name: path_privs(bigint, text, integer); Type: FUNCTION; Schema: public; Owner: postgres
1414 --
1415
1416 CREATE FUNCTION path_privs(bigint, text, integer) RETURNS bit
1417     LANGUAGE plpgsql STABLE STRICT
1418     AS $_$
1419 DECLARE
1420   in_accessor ALIAS FOR $1;
1421   in_path  ALIAS FOR $2;
1422   in_depth    ALIAS FOR $3;
1423
1424   alt1_path TEXT;
1425   alt2_path TEXT;
1426   grantor_collection    INT8;
1427   grantor_principal     INT8;
1428   collection_path       TEXT;
1429   collection_privileges BIT(24);
1430   out_conferred         BIT(24);
1431 BEGIN
1432   out_conferred := 0::BIT(24);
1433
1434   IF in_path ~ '^/?$' THEN
1435     -- RAISE NOTICE 'Collection is root: Collection: %', in_path;
1436     RETURN 1; -- basic read privileges on root directory
1437   END IF;
1438
1439   -- We need to canonicalise the path, so:
1440   -- If it matches '/' + some characters (+ optional '/')  => a principal URL
1441   IF in_path ~ '^/[^/]+/?$' THEN
1442     alt1_path := replace(in_path, '/', '');
1443     SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
1444     -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1445     RETURN out_conferred;
1446   END IF;
1447
1448   -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one.
1449   alt1_path := in_path;
1450   IF alt1_path ~ E'\\.ics$' THEN
1451     alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
1452   END IF;
1453   alt2_path := regexp_replace( in_path, '[^/]*$', '');
1454   SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges
1455     INTO grantor_collection, grantor_principal, collection_path, collection_privileges
1456                       FROM collection JOIN principal grantor USING (user_no)
1457                       WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
1458                       ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;
1459
1460   -- Self will always need full access to their own collections!
1461   IF grantor_principal = in_accessor THEN
1462     -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path;
1463     RETURN ~ out_conferred;
1464   END IF;
1465
1466   SELECT bit_or(privileges) INTO out_conferred FROM grants
1467                    WHERE by_collection = grantor_collection
1468                      AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)));
1469
1470   IF out_conferred IS NULL THEN
1471     IF collection_privileges IS NULL THEN
1472       IF grantor_principal IS NULL THEN
1473         alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/');
1474         SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred;
1475         -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1476       ELSE
1477         SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred;
1478         -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1479       END IF;
1480     ELSE
1481       out_conferred := collection_privileges;
1482       -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred;
1483     END IF;
1484   END IF;
1485
1486   RETURN out_conferred;
1487 END;
1488 $_$;
1489
1490
1491 ALTER FUNCTION public.path_privs(bigint, text, integer) OWNER TO postgres;
1492
1493 --
1494 -- Name: pprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
1495 --
1496
1497 CREATE FUNCTION pprivs(bigint, bigint, integer) RETURNS bit
1498     LANGUAGE plpgsql STABLE STRICT
1499     AS $_$
1500 DECLARE
1501   in_accessor ALIAS FOR $1;
1502   in_grantor  ALIAS FOR $2;
1503   in_depth    ALIAS FOR $3;
1504   out_conferred BIT(24);
1505 BEGIN
1506   out_conferred := 0::BIT(24);
1507   -- Self can always have full access
1508   IF in_grantor = in_accessor THEN
1509     RETURN ~ out_conferred;
1510   END IF;
1511
1512   SELECT bit_or(subquery.privileges) INTO out_conferred FROM
1513     (
1514       SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL
1515                                           AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth)))
1516             UNION
1517       SELECT bit_or(sq2.privileges) FROM
1518       (
1519           SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor
1520                         UNION
1521           SELECT default_privileges AS privileges FROM principal WHERE principal_id = in_grantor
1522       ) AS sq2
1523     ) AS subquery ;
1524
1525   IF out_conferred IS NULL THEN
1526     SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
1527   END IF;
1528
1529   RETURN out_conferred;
1530 END;
1531 $_$;
1532
1533
1534 ALTER FUNCTION public.pprivs(bigint, bigint, integer) OWNER TO postgres;
1535
1536 --
1537 -- Name: principal_modified(); Type: FUNCTION; Schema: public; Owner: postgres
1538 --
1539
1540 CREATE FUNCTION principal_modified() RETURNS trigger
1541     LANGUAGE plpgsql
1542     AS $$
1543 DECLARE
1544 BEGIN
1545   -- in case we trigger on other events in future
1546   IF TG_OP = 'UPDATE' THEN
1547     IF NEW.type_id != OLD.type_id THEN
1548       UPDATE grants
1549         SET is_group = (NEW.type_id = 3)
1550       WHERE grants.to_principal = NEW.principal_id;
1551     END IF;
1552   END IF;
1553   RETURN NEW;
1554 END;
1555 $$;
1556
1557
1558 ALTER FUNCTION public.principal_modified() OWNER TO postgres;
1559
1560 --
1561 -- Name: privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres
1562 --
1563
1564 CREATE FUNCTION privilege_to_bits(text) RETURNS bit
1565     LANGUAGE plpgsql IMMUTABLE STRICT
1566     AS $_$
1567 DECLARE
1568   raw_priv ALIAS FOR $1;
1569   in_priv TEXT;
1570 BEGIN
1571   in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
1572   IF in_priv = 'all' THEN
1573     RETURN ~ 0::BIT(24);
1574   END IF;
1575
1576   RETURN (CASE
1577             WHEN in_priv = 'read'                            THEN  4609 -- 1 + 512 + 4096
1578             WHEN in_priv = 'write'                           THEN   198 -- 2 + 4 + 64 + 128
1579             WHEN in_priv = 'write-properties'                THEN     2
1580             WHEN in_priv = 'write-content'                   THEN     4
1581             WHEN in_priv = 'unlock'                          THEN     8
1582             WHEN in_priv = 'read-acl'                        THEN    16
1583             WHEN in_priv = 'read-current-user-privilege-set' THEN    32
1584             WHEN in_priv = 'bind'                            THEN    64
1585             WHEN in_priv = 'unbind'                          THEN   128
1586             WHEN in_priv = 'write-acl'                       THEN   256
1587             WHEN in_priv = 'read-free-busy'                  THEN  4608 --  512 + 4096
1588             WHEN in_priv = 'schedule-deliver'                THEN  7168 -- 1024 + 2048 + 4096
1589             WHEN in_priv = 'schedule-deliver-invite'         THEN  1024
1590             WHEN in_priv = 'schedule-deliver-reply'          THEN  2048
1591             WHEN in_priv = 'schedule-query-freebusy'         THEN  4096
1592             WHEN in_priv = 'schedule-send'                   THEN 57344 -- 8192 + 16384 + 32768
1593             WHEN in_priv = 'schedule-send-invite'            THEN  8192
1594             WHEN in_priv = 'schedule-send-reply'             THEN 16384
1595             WHEN in_priv = 'schedule-send-freebusy'          THEN 32768
1596           ELSE 0 END)::BIT(24);
1597 END
1598 $_$;
1599
1600
1601 ALTER FUNCTION public.privilege_to_bits(text) OWNER TO postgres;
1602
1603 --
1604 -- Name: privilege_to_bits(text[]); Type: FUNCTION; Schema: public; Owner: postgres
1605 --
1606
1607 CREATE FUNCTION privilege_to_bits(text[]) RETURNS bit
1608     LANGUAGE plpgsql IMMUTABLE STRICT
1609     AS $_$
1610 DECLARE
1611   raw_privs ALIAS FOR $1;
1612   in_priv TEXT;
1613   out_bits BIT(24);
1614   i INT;
1615   all_privs BIT(24);
1616   start INT;
1617   finish INT;
1618 BEGIN
1619   out_bits := 0::BIT(24);
1620   all_privs := ~ out_bits;
1621   SELECT array_lower(raw_privs,1) INTO start;
1622   SELECT array_upper(raw_privs,1) INTO finish;
1623   FOR i IN start .. finish  LOOP
1624     SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
1625     IF out_bits = 65535::BIT(24) THEN
1626       RETURN all_privs;
1627     END IF;
1628   END LOOP;
1629   RETURN out_bits;
1630 END
1631 $_$;
1632
1633
1634 ALTER FUNCTION public.privilege_to_bits(text[]) OWNER TO postgres;
1635
1636 --
1637 -- Name: privileges_list(bit); Type: FUNCTION; Schema: public; Owner: postgres
1638 --
1639
1640 CREATE FUNCTION privileges_list(bit) RETURNS text
1641     LANGUAGE plpgsql IMMUTABLE STRICT
1642     AS $_$
1643 DECLARE
1644   in_privileges ALIAS FOR $1;
1645   privileges TEXT[];
1646   plist TEXT;
1647   start INT;
1648   finish INT;
1649   i INT;
1650 BEGIN
1651   plist := '';
1652
1653   privileges := bits_to_privilege(in_privileges);
1654   SELECT array_lower(privileges,1) INTO start;
1655   IF start IS NOT NULL THEN
1656     SELECT array_upper(privileges,1) INTO finish;
1657     FOR i IN start .. finish  LOOP
1658       plist := plist
1659               || CASE WHEN plist = '' THEN '' ELSE ', ' END
1660               || privileges[i];
1661     END LOOP;
1662   END IF;
1663   RETURN plist;
1664 END;
1665 $_$;
1666
1667
1668 ALTER FUNCTION public.privileges_list(bit) OWNER TO postgres;
1669
1670 --
1671 -- Name: real_path_exists(text); Type: FUNCTION; Schema: public; Owner: postgres
1672 --
1673
1674 CREATE FUNCTION real_path_exists(text) RETURNS boolean
1675     LANGUAGE plpgsql
1676     AS $_$
1677 DECLARE
1678   in_path ALIAS FOR $1;
1679   tmp BOOLEAN;
1680 BEGIN
1681   IF in_path = '/' THEN
1682     RETURN TRUE;
1683   END IF;
1684   IF in_path ~ '^/[^/]+/$' THEN
1685     SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
1686     IF FOUND THEN
1687       RETURN TRUE;
1688     END IF;
1689   ELSE
1690     IF in_path ~ '^/.*/$' THEN
1691       SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
1692       IF FOUND THEN
1693         RETURN TRUE;
1694       END IF;
1695     END IF;
1696   END IF;
1697   RETURN FALSE;
1698 END;
1699 $_$;
1700
1701
1702 ALTER FUNCTION public.real_path_exists(text) OWNER TO postgres;
1703
1704 --
1705 -- Name: relationship_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres
1706 --
1707
1708 CREATE FUNCTION relationship_list(bigint) RETURNS text
1709     LANGUAGE plpgsql
1710     AS $_$
1711 DECLARE
1712   user ALIAS FOR $1;
1713   r RECORD;
1714   rlist TEXT;
1715 BEGIN
1716   rlist := '';
1717   FOR r IN SELECT rt_name, fullname FROM relationship
1718                           LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no
1719                           WHERE from_user = user
1720   LOOP
1721     rlist := rlist
1722              || CASE WHEN rlist = '' THEN '' ELSE ', ' END
1723              || r.rt_name || '(' || r.fullname || ')';
1724   END LOOP;
1725   RETURN rlist;
1726 END;
1727 $_$;
1728
1729
1730 ALTER FUNCTION public.relationship_list(bigint) OWNER TO postgres;
1731
1732 --
1733 -- Name: rrule_bysetpos_filter(refcursor, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
1734 --
1735
1736 CREATE FUNCTION rrule_bysetpos_filter(refcursor, integer[]) RETURNS SETOF timestamp with time zone
1737     LANGUAGE plpgsql IMMUTABLE
1738     AS $_$
1739 DECLARE
1740   curse ALIAS FOR $1;
1741   bysetpos ALIAS FOR $2;
1742   valid_date TIMESTAMP WITH TIME ZONE;
1743   i INT;
1744 BEGIN
1745
1746   IF bysetpos IS NULL THEN
1747     LOOP
1748       FETCH curse INTO valid_date;
1749       EXIT WHEN NOT FOUND;
1750       RETURN NEXT valid_date;
1751     END LOOP;
1752   ELSE
1753     FOR i IN 1..366 LOOP
1754       EXIT WHEN bysetpos[i] IS NULL;
1755       IF bysetpos[i] > 0 THEN
1756         FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date;
1757       ELSE
1758         MOVE LAST IN curse;
1759         FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date;
1760       END IF;
1761       IF valid_date IS NOT NULL THEN
1762         RETURN NEXT valid_date;
1763       END IF;
1764     END LOOP;
1765   END IF;
1766   CLOSE curse;
1767 END;
1768 $_$;
1769
1770
1771 ALTER FUNCTION public.rrule_bysetpos_filter(refcursor, integer[]) OWNER TO postgres;
1772
1773 --
1774 -- Name: rrule_event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres
1775 --
1776
1777 CREATE FUNCTION rrule_event_instances(timestamp with time zone, text) RETURNS SETOF rrule_instance
1778     LANGUAGE plpgsql IMMUTABLE STRICT
1779     AS $_$
1780 DECLARE
1781   basedate ALIAS FOR $1;
1782   repeatrule ALIAS FOR $2;
1783   maxdate TIMESTAMP WITH TIME ZONE;
1784   current TIMESTAMP WITH TIME ZONE;
1785   result rrule_instance%ROWTYPE;
1786 BEGIN
1787   maxdate := current_date + '10 years'::interval;
1788
1789   result.dtstart := basedate;
1790   result.rrule   := repeatrule;
1791
1792   FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP
1793     result.instance := current;
1794     RETURN NEXT result;
1795   END LOOP;
1796
1797 END;
1798 $_$;
1799
1800
1801 ALTER FUNCTION public.rrule_event_instances(timestamp with time zone, text) OWNER TO postgres;
1802
1803 --
1804 -- Name: rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: public; Owner: postgres
1805 --
1806
1807 CREATE FUNCTION rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) RETURNS SETOF timestamp with time zone
1808     LANGUAGE plpgsql IMMUTABLE STRICT
1809     AS $_$
1810 DECLARE
1811   basedate ALIAS FOR $1;
1812   repeatrule ALIAS FOR $2;
1813   mindate ALIAS FOR $3;
1814   maxdate ALIAS FOR $4;
1815   max_count ALIAS FOR $5;
1816   loopmax INT;
1817   loopcount INT;
1818   base_day TIMESTAMP WITH TIME ZONE;
1819   current_base TIMESTAMP WITH TIME ZONE;
1820   current TIMESTAMP WITH TIME ZONE;
1821   rrule rrule_parts%ROWTYPE;
1822 BEGIN
1823   loopcount := 0;
1824
1825   SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule );
1826
1827   IF rrule.count IS NOT NULL THEN
1828     loopmax := rrule.count;
1829   ELSE
1830     -- max_count is pretty arbitrary, so we scale it somewhat here depending on the frequency.
1831     IF rrule.freq = 'DAILY' THEN
1832       loopmax := max_count * 20;
1833     ELSIF rrule.freq = 'WEEKLY' THEN
1834       loopmax := max_count * 10;
1835     ELSE
1836       loopmax := max_count;
1837     END IF;
1838   END IF;
1839
1840   current_base := basedate;
1841   base_day := date_trunc('day',basedate);
1842   WHILE loopcount < loopmax AND current_base <= maxdate LOOP
1843     IF rrule.freq = 'DAILY' THEN
1844       FOR current IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP
1845 --        IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN
1846           EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1847           IF current >= mindate THEN
1848             RETURN NEXT current;
1849           END IF;
1850           loopcount := loopcount + 1;
1851           EXIT WHEN loopcount >= loopmax;
1852 --        END IF;
1853       END LOOP;
1854       current_base := current_base + (rrule.interval::text || ' days')::interval;
1855     ELSIF rrule.freq = 'WEEKLY' THEN
1856       FOR current IN SELECT w FROM weekly_set(current_base,rrule) w WHERE w >= base_day LOOP
1857         IF test_byyearday_rule(current,rrule.byyearday)
1858                AND test_bymonthday_rule(current,rrule.bymonthday)
1859                AND test_bymonth_rule(current,rrule.bymonth)
1860         THEN
1861           EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1862           IF current >= mindate THEN
1863             RETURN NEXT current;
1864           END IF;
1865           loopcount := loopcount + 1;
1866           EXIT WHEN loopcount >= loopmax;
1867         END IF;
1868       END LOOP;
1869       current_base := current_base + (rrule.interval::text || ' weeks')::interval;
1870     ELSIF rrule.freq = 'MONTHLY' THEN
1871       FOR current IN SELECT m FROM monthly_set(current_base,rrule) m WHERE m >= base_day LOOP
1872 --        IF /* test_byyearday_rule(current,rrule.byyearday)
1873 --               AND */ test_bymonth_rule(current,rrule.bymonth)
1874 --        THEN
1875           EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1876           IF current >= mindate THEN
1877             RETURN NEXT current;
1878           END IF;
1879           loopcount := loopcount + 1;
1880           EXIT WHEN loopcount >= loopmax;
1881 --        END IF;
1882       END LOOP;
1883       current_base := current_base + (rrule.interval::text || ' months')::interval;
1884     ELSIF rrule.freq = 'YEARLY' THEN
1885       FOR current IN SELECT y FROM yearly_set(current_base,rrule) y WHERE y >= base_day LOOP
1886         EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1887         IF current >= mindate THEN
1888           RETURN NEXT current;
1889         END IF;
1890         loopcount := loopcount + 1;
1891         EXIT WHEN loopcount >= loopmax;
1892       END LOOP;
1893       current_base := current_base + (rrule.interval::text || ' years')::interval;
1894     ELSE
1895       RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq;
1896       RETURN;
1897     END IF;
1898     EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
1899   END LOOP;
1900   -- RETURN QUERY;
1901 END;
1902 $_$;
1903
1904
1905 ALTER FUNCTION public.rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO postgres;
1906
1907 --
1908 -- Name: rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
1909 --
1910
1911 CREATE FUNCTION rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) RETURNS boolean
1912     LANGUAGE plpgsql IMMUTABLE
1913     AS $_$
1914 DECLARE
1915   dtstart ALIAS FOR $1;
1916   dtend ALIAS FOR $2;
1917   repeatrule ALIAS FOR $3;
1918   in_mindate ALIAS FOR $4;
1919   in_maxdate ALIAS FOR $5;
1920   base_date TIMESTAMP WITH TIME ZONE;
1921   mindate TIMESTAMP WITH TIME ZONE;
1922   maxdate TIMESTAMP WITH TIME ZONE;
1923 BEGIN
1924
1925   IF dtstart IS NULL THEN
1926     RETURN NULL;
1927   END IF;
1928   IF dtend IS NULL THEN
1929     base_date := dtstart;
1930   ELSE
1931     base_date := dtend;
1932   END IF;
1933
1934   IF in_mindate IS NULL THEN
1935     mindate := current_date - '10 years'::interval;
1936   ELSE
1937     mindate := in_mindate;
1938   END IF;
1939
1940   IF in_maxdate IS NULL THEN
1941     maxdate := current_date + '10 years'::interval;
1942   ELSE
1943     -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range.
1944     maxdate := in_maxdate + (base_date - dtstart);
1945   END IF;
1946
1947   IF repeatrule IS NULL THEN
1948     RETURN (dtstart <= maxdate AND base_date >= mindate);
1949   END IF;
1950
1951   SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1;
1952   RETURN FOUND;
1953
1954 END;
1955 $_$;
1956
1957
1958 ALTER FUNCTION public.rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) OWNER TO postgres;
1959
1960 --
1961 -- Name: rrule_month_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
1962 --
1963
1964 CREATE FUNCTION rrule_month_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
1965     LANGUAGE plpgsql IMMUTABLE
1966     AS $_$
1967 DECLARE
1968   in_time ALIAS FOR $1;
1969   byday ALIAS FOR $2;
1970   dayrule TEXT;
1971   i INT;
1972   dow INT;
1973   index INT;
1974   first_dow INT;
1975   each_day TIMESTAMP WITH TIME ZONE;
1976   this_month INT;
1977   results TIMESTAMP WITH TIME ZONE[];
1978 BEGIN
1979
1980   IF byday IS NULL THEN
1981     -- We still return the single date as a SET
1982     RETURN NEXT in_time;
1983     RETURN;
1984   END IF;
1985
1986   i := 1;
1987   dayrule := byday[i];
1988   WHILE dayrule IS NOT NULL LOOP
1989     dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2;
1990     each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval;
1991     this_month := date_part( 'month', in_time );
1992     first_dow := date_part( 'dow', each_day );
1993
1994     -- Coerce each_day to be the first 'dow' of the month
1995     each_day := each_day - ( first_dow::text || 'days')::interval
1996                         + ( dow::text || 'days')::interval
1997                         + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END;
1998
1999     -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow;
2000     IF length(dayrule) > 2 THEN
2001       index := (substring(dayrule from '^[0-9-]+'))::int;
2002
2003       IF index = 0 THEN
2004         RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule;
2005       ELSIF index > 0 THEN
2006         -- The simplest case, such as 2MO for the second monday
2007         each_day := each_day + ((index - 1)::text || ' weeks')::interval;
2008       ELSE
2009         each_day := each_day + '5 weeks'::interval;
2010         WHILE date_part('month', each_day) != this_month LOOP
2011           each_day := each_day - '1 week'::interval;
2012         END LOOP;
2013         -- Note that since index is negative, (-2 + 1) == -1, for example
2014         index := index + 1;
2015         IF index < 0 THEN
2016           each_day := each_day + (index::text || ' weeks')::interval ;
2017         END IF;
2018       END IF;
2019
2020       -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months
2021       IF date_part('month', each_day) = this_month THEN
2022         results[date_part('day',each_day)] := each_day;
2023         -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
2024       END IF;
2025
2026     ELSE
2027       -- Return all such days that are within the given month
2028       WHILE date_part('month', each_day) = this_month LOOP
2029         results[date_part('day',each_day)] := each_day;
2030         each_day := each_day + '1 week'::interval;
2031         -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule;
2032       END LOOP;
2033     END IF;
2034
2035     i := i + 1;
2036     dayrule := byday[i];
2037   END LOOP;
2038
2039   FOR i IN 1..31 LOOP
2040     IF results[i] IS NOT NULL THEN
2041       RETURN NEXT results[i];
2042     END IF;
2043   END LOOP;
2044
2045   RETURN;
2046
2047 END;
2048 $_$;
2049
2050
2051 ALTER FUNCTION public.rrule_month_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
2052
2053 --
2054 -- Name: rrule_month_bymonthday_set(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2055 --
2056
2057 CREATE FUNCTION rrule_month_bymonthday_set(timestamp with time zone, integer[]) RETURNS SETOF timestamp with time zone
2058     LANGUAGE plpgsql IMMUTABLE STRICT
2059     AS $_$
2060 DECLARE
2061   in_time ALIAS FOR $1;
2062   bymonthday ALIAS FOR $2;
2063   month_start TIMESTAMP WITH TIME ZONE;
2064   daysinmonth INT;
2065   i INT;
2066 BEGIN
2067
2068   month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval;
2069   daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' );
2070
2071   FOR i IN 1..31 LOOP
2072     EXIT WHEN bymonthday[i] IS NULL;
2073
2074     CONTINUE WHEN bymonthday[i] > daysinmonth;
2075     CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth);
2076
2077     IF bymonthday[i] > 0 THEN
2078       RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval;
2079     ELSIF bymonthday[i] < 0 THEN
2080       RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval;
2081     ELSE
2082       RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i];
2083     END IF;
2084   END LOOP;
2085
2086   RETURN;
2087
2088 END;
2089 $_$;
2090
2091
2092 ALTER FUNCTION public.rrule_month_bymonthday_set(timestamp with time zone, integer[]) OWNER TO postgres;
2093
2094 --
2095 -- Name: rrule_week_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2096 --
2097
2098 CREATE FUNCTION rrule_week_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone
2099     LANGUAGE plpgsql IMMUTABLE
2100     AS $_$
2101 DECLARE
2102   in_time ALIAS FOR $1;
2103   byday ALIAS FOR $2;
2104   dayrule TEXT;
2105   dow INT;
2106   our_day TIMESTAMP WITH TIME ZONE;
2107   i INT;
2108 BEGIN
2109
2110   IF byday IS NULL THEN
2111     -- We still return the single date as a SET
2112     RETURN NEXT in_time;
2113     RETURN;
2114   END IF;
2115
2116   our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval;
2117
2118   i := 1;
2119   dayrule := byday[i];
2120   WHILE dayrule IS NOT NULL LOOP
2121     dow := position(dayrule in 'SUMOTUWETHFRSA') / 2;
2122     RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval;
2123     i := i + 1;
2124     dayrule := byday[i];
2125   END LOOP;
2126
2127   RETURN;
2128
2129 END;
2130 $_$;
2131
2132
2133 ALTER FUNCTION public.rrule_week_byday_set(timestamp with time zone, text[]) OWNER TO postgres;
2134
2135 --
2136 -- Name: rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2137 --
2138
2139 CREATE FUNCTION rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2140     LANGUAGE plpgsql IMMUTABLE STRICT
2141     AS $_$
2142 DECLARE
2143   after ALIAS FOR $1;
2144   rrule ALIAS FOR $2;
2145   current_base TIMESTAMP WITH TIME ZONE;
2146   rr rrule_parts;
2147   i INT;
2148 BEGIN
2149
2150   IF rrule.bymonth IS NOT NULL THEN
2151     -- Ensure we don't pass BYSETPOS down
2152     rr := rrule;
2153     rr.bysetpos := NULL;
2154     FOR i IN 1..12 LOOP
2155       EXIT WHEN rr.bymonth[i] IS NULL;
2156       current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval;
2157       RETURN QUERY SELECT r FROM monthly_set(current_base,rr) r;
2158     END LOOP;
2159   ELSE
2160     -- We don't yet implement byweekno, byblah
2161     RETURN NEXT after;
2162   END IF;
2163
2164 END;
2165 $_$;
2166
2167
2168 ALTER FUNCTION public.rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2169
2170 --
2171 -- Name: set_dav_property(text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres
2172 --
2173
2174 CREATE FUNCTION set_dav_property(text, integer, text, text) RETURNS boolean
2175     LANGUAGE plpgsql STRICT
2176     AS $_$
2177 DECLARE
2178   path ALIAS FOR $1;
2179   change_user ALIAS FOR $2;
2180   key ALIAS FOR $3;
2181   value ALIAS FOR $4;
2182 BEGIN
2183   -- Check that there is either a resource, collection or user at this location.
2184   IF NOT EXISTS(        SELECT 1 FROM caldav_data WHERE dav_name = path
2185                   UNION SELECT 1 FROM collection WHERE dav_name = path
2186                   UNION SELECT 1 FROM dav_principal WHERE dav_name = path
2187                   UNION SELECT 1 FROM dav_binding WHERE dav_name = path
2188                ) THEN
2189     RETURN FALSE;
2190   END IF;
2191   PERFORM true FROM property WHERE dav_name = path AND property_name = key;
2192   IF FOUND THEN
2193     UPDATE property SET changed_by=change_user::integer, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key;
2194   ELSE
2195     INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, change_user::integer, current_timestamp, key, value );
2196   END IF;
2197   RETURN TRUE;
2198 END;
2199 $_$;
2200
2201
2202 ALTER FUNCTION public.set_dav_property(text, integer, text, text) OWNER TO postgres;
2203
2204 --
2205 -- Name: sync_dav_id(); Type: FUNCTION; Schema: public; Owner: postgres
2206 --
2207
2208 CREATE FUNCTION sync_dav_id() RETURNS trigger
2209     LANGUAGE plpgsql
2210     AS $$
2211   DECLARE
2212   BEGIN
2213
2214     IF TG_OP = 'DELETE' THEN
2215       -- Just let the ON DELETE CASCADE handle this case
2216       RETURN OLD;
2217     END IF;
2218
2219     IF NEW.dav_id IS NULL THEN
2220       NEW.dav_id = nextval('dav_id_seq');
2221     END IF;
2222
2223     IF TG_OP = 'UPDATE' THEN
2224       IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
2225                  OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
2226         UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
2227                         collection_id = NEW.collection_id, dav_name = NEW.dav_name
2228             WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
2229       END IF;
2230       RETURN NEW;
2231     END IF;
2232
2233     UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
2234                     collection_id = NEW.collection_id, dav_name = NEW.dav_name
2235           WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
2236
2237     RETURN NEW;
2238
2239   END
2240 $$;
2241
2242
2243 ALTER FUNCTION public.sync_dav_id() OWNER TO postgres;
2244
2245 --
2246 -- Name: test_byday_rule(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres
2247 --
2248
2249 CREATE FUNCTION test_byday_rule(timestamp with time zone, text[]) RETURNS boolean
2250     LANGUAGE plpgsql IMMUTABLE
2251     AS $_$
2252 DECLARE
2253   testme ALIAS FOR $1;
2254   byday ALIAS FOR $2;
2255 BEGIN
2256   -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended
2257   -- so don't call it that way...
2258   IF byday IS NOT NULL THEN
2259     RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) );
2260   END IF;
2261   RETURN TRUE;
2262 END;
2263 $_$;
2264
2265
2266 ALTER FUNCTION public.test_byday_rule(timestamp with time zone, text[]) OWNER TO postgres;
2267
2268 --
2269 -- Name: test_bymonth_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2270 --
2271
2272 CREATE FUNCTION test_bymonth_rule(timestamp with time zone, integer[]) RETURNS boolean
2273     LANGUAGE plpgsql IMMUTABLE
2274     AS $_$
2275 DECLARE
2276   testme ALIAS FOR $1;
2277   bymonth ALIAS FOR $2;
2278 BEGIN
2279   IF bymonth IS NOT NULL THEN
2280     RETURN ( date_part( 'month', testme) = ANY (bymonth) );
2281   END IF;
2282   RETURN TRUE;
2283 END;
2284 $_$;
2285
2286
2287 ALTER FUNCTION public.test_bymonth_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2288
2289 --
2290 -- Name: test_bymonthday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2291 --
2292
2293 CREATE FUNCTION test_bymonthday_rule(timestamp with time zone, integer[]) RETURNS boolean
2294     LANGUAGE plpgsql IMMUTABLE
2295     AS $_$
2296 DECLARE
2297   testme ALIAS FOR $1;
2298   bymonthday ALIAS FOR $2;
2299 BEGIN
2300   IF bymonthday IS NOT NULL THEN
2301     RETURN ( date_part( 'day', testme) = ANY (bymonthday) );
2302   END IF;
2303   RETURN TRUE;
2304 END;
2305 $_$;
2306
2307
2308 ALTER FUNCTION public.test_bymonthday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2309
2310 --
2311 -- Name: test_byyearday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres
2312 --
2313
2314 CREATE FUNCTION test_byyearday_rule(timestamp with time zone, integer[]) RETURNS boolean
2315     LANGUAGE plpgsql IMMUTABLE
2316     AS $_$
2317 DECLARE
2318   testme ALIAS FOR $1;
2319   byyearday ALIAS FOR $2;
2320 BEGIN
2321   IF byyearday IS NOT NULL THEN
2322     RETURN ( date_part( 'doy', testme) = ANY (byyearday) );
2323   END IF;
2324   RETURN TRUE;
2325 END;
2326 $_$;
2327
2328
2329 ALTER FUNCTION public.test_byyearday_rule(timestamp with time zone, integer[]) OWNER TO postgres;
2330
2331 --
2332 -- Name: to_ical_utc(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres
2333 --
2334
2335 CREATE FUNCTION to_ical_utc(timestamp with time zone) RETURNS text
2336     LANGUAGE sql IMMUTABLE STRICT
2337     AS $_$
2338   SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' )
2339 $_$;
2340
2341
2342 ALTER FUNCTION public.to_ical_utc(timestamp with time zone) OWNER TO postgres;
2343
2344 --
2345 -- Name: uprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres
2346 --
2347
2348 CREATE FUNCTION uprivs(bigint, bigint, integer) RETURNS bit
2349     LANGUAGE plpgsql STABLE STRICT
2350     AS $_$
2351 DECLARE
2352   in_accessor ALIAS FOR $1;
2353   in_grantor  ALIAS FOR $2;
2354   in_depth    ALIAS FOR $3;
2355   out_conferred BIT(24);
2356 BEGIN
2357   out_conferred := 0::BIT(24);
2358   -- Self can always have full access
2359   IF in_grantor = in_accessor THEN
2360     RETURN ~ out_conferred;
2361   END IF;
2362
2363   SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred
2364           FROM principal p1, principal p2
2365           WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor;
2366
2367   RETURN out_conferred;
2368 END;
2369 $_$;
2370
2371
2372 ALTER FUNCTION public.uprivs(bigint, bigint, integer) OWNER TO postgres;
2373
2374 --
2375 -- Name: usr_is_role(integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2376 --
2377
2378 CREATE FUNCTION usr_is_role(integer, text) RETURNS boolean
2379     LANGUAGE sql IMMUTABLE STRICT
2380     AS $_$
2381   SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 )
2382 $_$;
2383
2384
2385 ALTER FUNCTION public.usr_is_role(integer, text) OWNER TO postgres;
2386
2387 --
2388 -- Name: usr_modified(); Type: FUNCTION; Schema: public; Owner: postgres
2389 --
2390
2391 CREATE FUNCTION usr_modified() RETURNS trigger
2392     LANGUAGE plpgsql
2393     AS $$
2394 DECLARE
2395   oldpath TEXT;
2396   newpath TEXT;
2397 BEGIN
2398   -- in case we trigger on other events in future
2399   IF TG_OP = 'UPDATE' THEN
2400     IF NEW.username != OLD.username THEN
2401       oldpath := '/' || OLD.username || '/';
2402       newpath := '/' || NEW.username || '/';
2403       UPDATE collection
2404         SET parent_container = replace( parent_container, oldpath, newpath),
2405             dav_name = replace( dav_name, oldpath, newpath)
2406       WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath;
2407     END IF;
2408   END IF;
2409   RETURN NEW;
2410 END;
2411 $$;
2412
2413
2414 ALTER FUNCTION public.usr_modified() OWNER TO postgres;
2415
2416 --
2417 -- Name: weekly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2418 --
2419
2420 CREATE FUNCTION weekly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2421     LANGUAGE plpgsql IMMUTABLE STRICT
2422     AS $_$
2423 DECLARE
2424   after ALIAS FOR $1;
2425   rrule ALIAS FOR $2;
2426   valid_date TIMESTAMP WITH TIME ZONE;
2427   curse REFCURSOR;
2428   weekno INT;
2429   i INT;
2430 BEGIN
2431
2432   IF rrule.byweekno IS NOT NULL THEN
2433     weekno := date_part('week',after);
2434     IF NOT weekno = ANY ( rrule.byweekno ) THEN
2435       RETURN;
2436     END IF;
2437   END IF;
2438
2439   OPEN curse SCROLL FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r;
2440   RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
2441
2442 END;
2443 $_$;
2444
2445
2446 ALTER FUNCTION public.weekly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2447
2448 --
2449 -- Name: write_sync_change(bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
2450 --
2451
2452 CREATE FUNCTION write_sync_change(bigint, integer, text) RETURNS boolean
2453     LANGUAGE plpgsql STRICT
2454     AS $_$
2455 DECLARE
2456   in_collection_id ALIAS FOR $1;
2457   in_status ALIAS FOR $2;
2458   in_dav_name ALIAS FOR $3;
2459   tmp_int INT8;
2460 BEGIN
2461   SELECT 1 INTO tmp_int FROM sync_tokens
2462            WHERE collection_id = in_collection_id
2463            LIMIT 1;
2464   IF NOT FOUND THEN
2465     RETURN FALSE;
2466   END IF;
2467   SELECT dav_id INTO tmp_int FROM caldav_data WHERE dav_name = in_dav_name;
2468   INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name)
2469                      VALUES( in_collection_id, in_status, tmp_int, in_dav_name);
2470   RETURN TRUE;
2471 END
2472 $_$;
2473
2474
2475 ALTER FUNCTION public.write_sync_change(bigint, integer, text) OWNER TO postgres;
2476
2477 --
2478 -- Name: yearly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres
2479 --
2480
2481 CREATE FUNCTION yearly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone
2482     LANGUAGE plpgsql IMMUTABLE STRICT
2483     AS $_$
2484 DECLARE
2485   after ALIAS FOR $1;
2486   rrule ALIAS FOR $2;
2487   current_base TIMESTAMP WITH TIME ZONE;
2488   curse REFCURSOR;
2489   curser REFCURSOR;
2490   i INT;
2491 BEGIN
2492
2493   IF rrule.bymonth IS NOT NULL THEN
2494     OPEN curse SCROLL FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r;
2495     FOR current_base IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP
2496       current_base := date_trunc( 'day', current_base ) + (after::time)::interval;
2497       RETURN NEXT current_base;
2498     END LOOP;
2499   ELSE
2500     -- We don't yet implement byweekno, byblah
2501     RETURN NEXT after;
2502   END IF;
2503 END;
2504 $_$;
2505
2506
2507 ALTER FUNCTION public.yearly_set(timestamp with time zone, rrule_parts) OWNER TO postgres;
2508
2509 SET default_tablespace = '';
2510
2511 SET default_with_oids = false;
2512
2513 --
2514 -- Name: access_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2515 --
2516
2517 CREATE TABLE access_ticket (
2518     ticket_id text NOT NULL,
2519     dav_owner_id bigint NOT NULL,
2520     privileges bit(24),
2521     target_collection_id bigint NOT NULL,
2522     target_resource_id bigint,
2523     expires timestamp without time zone
2524 );
2525
2526
2527 ALTER TABLE public.access_ticket OWNER TO postgres;
2528
2529 --
2530 -- Name: addressbook_address_adr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2531 --
2532
2533 CREATE TABLE addressbook_address_adr (
2534     dav_id bigint NOT NULL,
2535     type text,
2536     box_no text,
2537     unit_no text,
2538     street_address text,
2539     locality text,
2540     region text,
2541     postcode text,
2542     country text,
2543     property text
2544 );
2545
2546
2547 ALTER TABLE public.addressbook_address_adr OWNER TO postgres;
2548
2549 --
2550 -- Name: addressbook_address_email; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2551 --
2552
2553 CREATE TABLE addressbook_address_email (
2554     dav_id bigint NOT NULL,
2555     type text,
2556     email text,
2557     property text
2558 );
2559
2560
2561 ALTER TABLE public.addressbook_address_email OWNER TO postgres;
2562
2563 --
2564 -- Name: addressbook_address_tel; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2565 --
2566
2567 CREATE TABLE addressbook_address_tel (
2568     dav_id bigint NOT NULL,
2569     type text,
2570     tel text,
2571     property text
2572 );
2573
2574
2575 ALTER TABLE public.addressbook_address_tel OWNER TO postgres;
2576
2577 --
2578 -- Name: addressbook_resource; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2579 --
2580
2581 CREATE TABLE addressbook_resource (
2582     dav_id bigint NOT NULL,
2583     version text,
2584     uid text,
2585     nickname text,
2586     fn text,
2587     n text,
2588     note text,
2589     org text,
2590     url text,
2591     fburl text,
2592     caladruri text,
2593     caluri text
2594 );
2595
2596
2597 ALTER TABLE public.addressbook_resource OWNER TO postgres;
2598
2599 --
2600 -- Name: awl_db_revision; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2601 --
2602
2603 CREATE TABLE awl_db_revision (
2604     schema_id integer,
2605     schema_major integer,
2606     schema_minor integer,
2607     schema_patch integer,
2608     schema_name text,
2609     applied_on timestamp with time zone DEFAULT now()
2610 );
2611
2612
2613 ALTER TABLE public.awl_db_revision OWNER TO postgres;
2614
2615 --
2616 -- Name: dav_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2617 --
2618
2619 CREATE SEQUENCE dav_id_seq
2620     START WITH 1
2621     INCREMENT BY 1
2622     NO MINVALUE
2623     NO MAXVALUE
2624     CACHE 1;
2625
2626
2627 ALTER TABLE public.dav_id_seq OWNER TO postgres;
2628
2629 --
2630 -- Name: caldav_data; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2631 --
2632
2633 CREATE TABLE caldav_data (
2634     user_no integer NOT NULL,
2635     dav_name text NOT NULL,
2636     dav_etag text,
2637     created timestamp with time zone,
2638     modified timestamp with time zone,
2639     caldav_data text,
2640     caldav_type text,
2641     logged_user integer,
2642     dav_id bigint DEFAULT nextval('dav_id_seq'::regclass),
2643     collection_id bigint,
2644     weak_etag text
2645 );
2646
2647
2648 ALTER TABLE public.caldav_data OWNER TO postgres;
2649
2650 --
2651 -- Name: calendar_alarm; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2652 --
2653
2654 CREATE TABLE calendar_alarm (
2655     dav_id bigint NOT NULL,
2656     action text,
2657     trigger text,
2658     summary text,
2659     description text,
2660     next_trigger timestamp with time zone,
2661     component text,
2662     trigger_state character(1) DEFAULT 'N'::bpchar
2663 );
2664
2665
2666 ALTER TABLE public.calendar_alarm OWNER TO postgres;
2667
2668 --
2669 -- Name: calendar_attendee; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2670 --
2671
2672 CREATE TABLE calendar_attendee (
2673     dav_id bigint NOT NULL,
2674     status text,
2675     partstat text,
2676     cn text,
2677     attendee text NOT NULL,
2678     role text,
2679     rsvp boolean,
2680     property text,
2681     attendee_state text,
2682     weak_etag text
2683 );
2684
2685
2686 ALTER TABLE public.calendar_attendee OWNER TO postgres;
2687
2688 --
2689 -- Name: calendar_item; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2690 --
2691
2692 CREATE TABLE calendar_item (
2693     user_no integer NOT NULL,
2694     dav_name text NOT NULL,
2695     dav_etag text,
2696     uid text,
2697     created timestamp without time zone,
2698     last_modified timestamp without time zone,
2699     dtstamp timestamp without time zone,
2700     dtstart timestamp with time zone,
2701     dtend timestamp with time zone,
2702     due timestamp with time zone,
2703     summary text,
2704     location text,
2705     description text,
2706     priority integer,
2707     class text,
2708     transp text,
2709     rrule text,
2710     url text,
2711     percent_complete numeric(7,2),
2712     tz_id text,
2713     status text,
2714     completed timestamp with time zone,
2715     dav_id bigint,
2716     collection_id bigint,
2717     first_instance_start timestamp without time zone,
2718     last_instance_end timestamp without time zone
2719 );
2720
2721
2722 ALTER TABLE public.calendar_item OWNER TO postgres;
2723
2724 --
2725 -- Name: collection; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2726 --
2727
2728 CREATE TABLE collection (
2729     user_no integer,
2730     parent_container text,
2731     dav_name text,
2732     dav_etag text,
2733     dav_displayname text,
2734     is_calendar boolean,
2735     created timestamp with time zone,
2736     modified timestamp with time zone,
2737     public_events_only boolean DEFAULT false NOT NULL,
2738     publicly_readable boolean DEFAULT false NOT NULL,
2739     collection_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2740     default_privileges bit(24),
2741     is_addressbook boolean DEFAULT false,
2742     resourcetypes text DEFAULT '<DAV::collection/>'::text,
2743     schedule_transp text DEFAULT 'opaque'::text,
2744     timezone text,
2745     description text DEFAULT ''::text
2746 );
2747
2748
2749 ALTER TABLE public.collection OWNER TO postgres;
2750
2751 --
2752 -- Name: dav_binding; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2753 --
2754
2755 CREATE TABLE dav_binding (
2756     bind_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2757     bound_source_id bigint,
2758     access_ticket_id text,
2759     dav_owner_id bigint NOT NULL,
2760     parent_container text NOT NULL,
2761     dav_name text NOT NULL,
2762     dav_displayname text,
2763     external_url text,
2764     type text,
2765     CONSTRAINT dav_name_does_not_exist CHECK ((NOT real_path_exists(dav_name)))
2766 );
2767
2768
2769 ALTER TABLE public.dav_binding OWNER TO postgres;
2770
2771 --
2772 -- Name: principal; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2773 --
2774
2775 CREATE TABLE principal (
2776     principal_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL,
2777     type_id bigint NOT NULL,
2778     user_no bigint,
2779     displayname text,
2780     default_privileges bit(24)
2781 );
2782
2783
2784 ALTER TABLE public.principal OWNER TO postgres;
2785
2786 --
2787 -- Name: usr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2788 --
2789
2790 CREATE TABLE usr (
2791     user_no integer NOT NULL,
2792     active boolean DEFAULT true,
2793     email_ok timestamp with time zone,
2794     joined timestamp with time zone DEFAULT now(),
2795     updated timestamp with time zone,
2796     last_used timestamp with time zone,
2797     username text NOT NULL,
2798     password text,
2799     fullname text,
2800     email text,
2801     config_data text,
2802     date_format_type text DEFAULT 'E'::text,
2803     locale text
2804 );
2805
2806
2807 ALTER TABLE public.usr OWNER TO postgres;
2808
2809 --
2810 -- Name: dav_principal; Type: VIEW; Schema: public; Owner: postgres
2811 --
2812
2813 CREATE VIEW dav_principal AS
2814     SELECT principal.user_no, usr.active AS user_active, usr.joined AS created, usr.updated AS modified, usr.username, usr.password, usr.fullname, usr.email, usr.email_ok, usr.date_format_type, usr.locale, principal.principal_id, principal.type_id, principal.displayname, principal.default_privileges, true AS is_principal, false AS is_calendar, principal.principal_id AS collection_id, false AS is_addressbook, (('/'::text || usr.username) || '/'::text) AS dav_name, '<DAV::collection/><DAV::principal/>'::text AS resourcetypes FROM (usr JOIN principal USING (user_no));
2815
2816
2817 ALTER TABLE public.dav_principal OWNER TO postgres;
2818
2819 --
2820 -- Name: freebusy_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2821 --
2822
2823 CREATE TABLE freebusy_ticket (
2824     ticket_id text NOT NULL,
2825     user_no integer NOT NULL,
2826     created timestamp with time zone DEFAULT now() NOT NULL
2827 );
2828
2829
2830 ALTER TABLE public.freebusy_ticket OWNER TO postgres;
2831
2832 --
2833 -- Name: grants; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2834 --
2835
2836 CREATE TABLE grants (
2837     by_principal bigint,
2838     by_collection bigint,
2839     to_principal bigint,
2840     privileges bit(24),
2841     is_group boolean
2842 );
2843
2844
2845 ALTER TABLE public.grants OWNER TO postgres;
2846
2847 --
2848 -- Name: group_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2849 --
2850
2851 CREATE TABLE group_member (
2852     group_id bigint,
2853     member_id bigint
2854 );
2855
2856
2857 ALTER TABLE public.group_member OWNER TO postgres;
2858
2859 --
2860 -- Name: locks; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2861 --
2862
2863 CREATE TABLE locks (
2864     dav_name text,
2865     opaquelocktoken text NOT NULL,
2866     type text,
2867     scope text,
2868     depth integer,
2869     owner text,
2870     timeout interval,
2871     start timestamp without time zone DEFAULT now()
2872 );
2873
2874
2875 ALTER TABLE public.locks OWNER TO postgres;
2876
2877 --
2878 -- Name: principal_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2879 --
2880
2881 CREATE TABLE principal_type (
2882     principal_type_id integer NOT NULL,
2883     principal_type_desc text
2884 );
2885
2886
2887 ALTER TABLE public.principal_type OWNER TO postgres;
2888
2889 --
2890 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2891 --
2892
2893 CREATE SEQUENCE principal_type_principal_type_id_seq
2894     START WITH 1
2895     INCREMENT BY 1
2896     NO MINVALUE
2897     NO MAXVALUE
2898     CACHE 1;
2899
2900
2901 ALTER TABLE public.principal_type_principal_type_id_seq OWNER TO postgres;
2902
2903 --
2904 -- Name: principal_type_principal_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2905 --
2906
2907 ALTER SEQUENCE principal_type_principal_type_id_seq OWNED BY principal_type.principal_type_id;
2908
2909
2910 --
2911 -- Name: property; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2912 --
2913
2914 CREATE TABLE property (
2915     dav_name text NOT NULL,
2916     property_name text NOT NULL,
2917     property_value text,
2918     changed_on timestamp without time zone DEFAULT now(),
2919     changed_by integer
2920 );
2921
2922
2923 ALTER TABLE public.property OWNER TO postgres;
2924
2925 --
2926 -- Name: relationship; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2927 --
2928
2929 CREATE TABLE relationship (
2930     from_user integer NOT NULL,
2931     to_user integer NOT NULL,
2932     rt_id integer NOT NULL,
2933     confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
2934 );
2935
2936
2937 ALTER TABLE public.relationship OWNER TO postgres;
2938
2939 --
2940 -- Name: relationship_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2941 --
2942
2943 CREATE TABLE relationship_type (
2944     rt_id integer NOT NULL,
2945     rt_name text,
2946     rt_togroup boolean,
2947     confers text DEFAULT 'RW'::text,
2948     rt_fromgroup boolean,
2949     bit_confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text])
2950 );
2951
2952
2953 ALTER TABLE public.relationship_type OWNER TO postgres;
2954
2955 --
2956 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2957 --
2958
2959 CREATE SEQUENCE relationship_type_rt_id_seq
2960     START WITH 1
2961     INCREMENT BY 1
2962     NO MINVALUE
2963     NO MAXVALUE
2964     CACHE 1;
2965
2966
2967 ALTER TABLE public.relationship_type_rt_id_seq OWNER TO postgres;
2968
2969 --
2970 -- Name: relationship_type_rt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
2971 --
2972
2973 ALTER SEQUENCE relationship_type_rt_id_seq OWNED BY relationship_type.rt_id;
2974
2975
2976 --
2977 -- Name: role_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2978 --
2979
2980 CREATE TABLE role_member (
2981     role_no integer,
2982     user_no integer
2983 );
2984
2985
2986 ALTER TABLE public.role_member OWNER TO postgres;
2987
2988 --
2989 -- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
2990 --
2991
2992 CREATE TABLE roles (
2993     role_no integer NOT NULL,
2994     role_name text
2995 );
2996
2997
2998 ALTER TABLE public.roles OWNER TO postgres;
2999
3000 --
3001 -- Name: roles_role_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3002 --
3003
3004 CREATE SEQUENCE roles_role_no_seq
3005     START WITH 1
3006     INCREMENT BY 1
3007     NO MINVALUE
3008     NO MAXVALUE
3009     CACHE 1;
3010
3011
3012 ALTER TABLE public.roles_role_no_seq OWNER TO postgres;
3013
3014 --
3015 -- Name: roles_role_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3016 --
3017
3018 ALTER SEQUENCE roles_role_no_seq OWNED BY roles.role_no;
3019
3020
3021 --
3022 -- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3023 --
3024
3025 CREATE TABLE session (
3026     session_id integer NOT NULL,
3027     user_no integer,
3028     session_start timestamp with time zone DEFAULT now(),
3029     session_end timestamp with time zone DEFAULT now(),
3030     session_key text,
3031     session_config text
3032 );
3033
3034
3035 ALTER TABLE public.session OWNER TO postgres;
3036
3037 --
3038 -- Name: session_session_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3039 --
3040
3041 CREATE SEQUENCE session_session_id_seq
3042     START WITH 1
3043     INCREMENT BY 1
3044     NO MINVALUE
3045     NO MAXVALUE
3046     CACHE 1;
3047
3048
3049 ALTER TABLE public.session_session_id_seq OWNER TO postgres;
3050
3051 --
3052 -- Name: session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3053 --
3054
3055 ALTER SEQUENCE session_session_id_seq OWNED BY session.session_id;
3056
3057
3058 --
3059 -- Name: supported_locales; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3060 --
3061
3062 CREATE TABLE supported_locales (
3063     locale text NOT NULL,
3064     locale_name_en text,
3065     locale_name_locale text
3066 );
3067
3068
3069 ALTER TABLE public.supported_locales OWNER TO postgres;
3070
3071 --
3072 -- Name: sync_changes; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3073 --
3074
3075 CREATE TABLE sync_changes (
3076     sync_time timestamp with time zone DEFAULT now(),
3077     collection_id bigint,
3078     sync_status integer,
3079     dav_id bigint,
3080     dav_name text
3081 );
3082
3083
3084 ALTER TABLE public.sync_changes OWNER TO postgres;
3085
3086 --
3087 -- Name: sync_tokens; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3088 --
3089
3090 CREATE TABLE sync_tokens (
3091     sync_token integer NOT NULL,
3092     collection_id bigint,
3093     modification_time timestamp with time zone DEFAULT now()
3094 );
3095
3096
3097 ALTER TABLE public.sync_tokens OWNER TO postgres;
3098
3099 --
3100 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3101 --
3102
3103 CREATE SEQUENCE sync_tokens_sync_token_seq
3104     START WITH 1
3105     INCREMENT BY 1
3106     NO MINVALUE
3107     NO MAXVALUE
3108     CACHE 1;
3109
3110
3111 ALTER TABLE public.sync_tokens_sync_token_seq OWNER TO postgres;
3112
3113 --
3114 -- Name: sync_tokens_sync_token_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3115 --
3116
3117 ALTER SEQUENCE sync_tokens_sync_token_seq OWNED BY sync_tokens.sync_token;
3118
3119
3120 --
3121 -- Name: timezones; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3122 --
3123
3124 CREATE TABLE timezones (
3125     our_tzno integer NOT NULL,
3126     tzid text NOT NULL,
3127     olson_name text,
3128     active boolean,
3129     last_modified timestamp without time zone DEFAULT now(),
3130     etag text,
3131     vtimezone text
3132 );
3133
3134
3135 ALTER TABLE public.timezones OWNER TO postgres;
3136
3137 --
3138 -- Name: timezones_our_tzno_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3139 --
3140
3141 CREATE SEQUENCE timezones_our_tzno_seq
3142     START WITH 1
3143     INCREMENT BY 1
3144     NO MINVALUE
3145     NO MAXVALUE
3146     CACHE 1;
3147
3148
3149 ALTER TABLE public.timezones_our_tzno_seq OWNER TO postgres;
3150
3151 --
3152 -- Name: timezones_our_tzno_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3153 --
3154
3155 ALTER SEQUENCE timezones_our_tzno_seq OWNED BY timezones.our_tzno;
3156
3157
3158 --
3159 -- Name: tmp_password; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3160 --
3161
3162 CREATE TABLE tmp_password (
3163     user_no integer,
3164     password text,
3165     valid_until timestamp with time zone DEFAULT (now() + '1 day'::interval)
3166 );
3167
3168
3169 ALTER TABLE public.tmp_password OWNER TO postgres;
3170
3171 --
3172 -- Name: tz_aliases; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3173 --
3174
3175 CREATE TABLE tz_aliases (
3176     our_tzno bigint,
3177     tzalias text NOT NULL
3178 );
3179
3180
3181 ALTER TABLE public.tz_aliases OWNER TO postgres;
3182
3183 --
3184 -- Name: tz_localnames; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3185 --
3186
3187 CREATE TABLE tz_localnames (
3188     our_tzno bigint,
3189     locale text NOT NULL,
3190     localised_name text NOT NULL,
3191     preferred boolean DEFAULT true
3192 );
3193
3194
3195 ALTER TABLE public.tz_localnames OWNER TO postgres;
3196
3197 --
3198 -- Name: usr_setting; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
3199 --
3200
3201 CREATE TABLE usr_setting (
3202     user_no integer NOT NULL,
3203     setting_name text NOT NULL,
3204     setting_value text
3205 );
3206
3207
3208 ALTER TABLE public.usr_setting OWNER TO postgres;
3209
3210 --
3211 -- Name: usr_user_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres
3212 --
3213
3214 CREATE SEQUENCE usr_user_no_seq
3215     START WITH 1
3216     INCREMENT BY 1
3217     NO MINVALUE
3218     NO MAXVALUE
3219     CACHE 1;
3220
3221
3222 ALTER TABLE public.usr_user_no_seq OWNER TO postgres;
3223
3224 --
3225 -- Name: usr_user_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
3226 --
3227
3228 ALTER SEQUENCE usr_user_no_seq OWNED BY usr.user_no;
3229
3230
3231 --
3232 -- Name: principal_type_id; Type: DEFAULT; Schema: public; Owner: postgres
3233 --
3234
3235 ALTER TABLE ONLY principal_type ALTER COLUMN principal_type_id SET DEFAULT nextval('principal_type_principal_type_id_seq'::regclass);
3236
3237
3238 --
3239 -- Name: rt_id; Type: DEFAULT; Schema: public; Owner: postgres
3240 --
3241
3242 ALTER TABLE ONLY relationship_type ALTER COLUMN rt_id SET DEFAULT nextval('relationship_type_rt_id_seq'::regclass);
3243
3244
3245 --
3246 -- Name: role_no; Type: DEFAULT; Schema: public; Owner: postgres
3247 --
3248
3249 ALTER TABLE ONLY roles ALTER COLUMN role_no SET DEFAULT nextval('roles_role_no_seq'::regclass);
3250
3251
3252 --
3253 -- Name: session_id; Type: DEFAULT; Schema: public; Owner: postgres
3254 --
3255
3256 ALTER TABLE ONLY session ALTER COLUMN session_id SET DEFAULT nextval('session_session_id_seq'::regclass);
3257
3258
3259 --
3260 -- Name: sync_token; Type: DEFAULT; Schema: public; Owner: postgres
3261 --
3262
3263 ALTER TABLE ONLY sync_tokens ALTER COLUMN sync_token SET DEFAULT nextval('sync_tokens_sync_token_seq'::regclass);
3264
3265
3266 --
3267 -- Name: our_tzno; Type: DEFAULT; Schema: public; Owner: postgres
3268 --
3269
3270 ALTER TABLE ONLY timezones ALTER COLUMN our_tzno SET DEFAULT nextval('timezones_our_tzno_seq'::regclass);
3271
3272
3273 --
3274 -- Name: user_no; Type: DEFAULT; Schema: public; Owner: postgres
3275 --
3276
3277 ALTER TABLE ONLY usr ALTER COLUMN user_no SET DEFAULT nextval('usr_user_no_seq'::regclass);
3278
3279
3280 --
3281 -- Name: access_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3282 --
3283
3284 ALTER TABLE ONLY access_ticket
3285     ADD CONSTRAINT access_ticket_pkey PRIMARY KEY (ticket_id);
3286
3287
3288 --
3289 -- Name: addressbook_resource_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3290 --
3291
3292 ALTER TABLE ONLY addressbook_resource
3293     ADD CONSTRAINT addressbook_resource_pkey PRIMARY KEY (dav_id);
3294
3295
3296 --
3297 -- Name: caldav_data_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3298 --
3299
3300 ALTER TABLE ONLY caldav_data
3301     ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id);
3302
3303
3304 --
3305 -- Name: caldav_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3306 --
3307
3308 ALTER TABLE ONLY caldav_data
3309     ADD CONSTRAINT caldav_data_pkey PRIMARY KEY (user_no, dav_name);
3310
3311
3312 --
3313 -- Name: calendar_attendee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3314 --
3315
3316 ALTER TABLE ONLY calendar_attendee
3317     ADD CONSTRAINT calendar_attendee_pkey PRIMARY KEY (dav_id, attendee);
3318
3319
3320 --
3321 -- Name: calendar_item_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3322 --
3323
3324 ALTER TABLE ONLY calendar_item
3325     ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id);
3326
3327
3328 --
3329 -- Name: calendar_item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3330 --
3331
3332 ALTER TABLE ONLY calendar_item
3333     ADD CONSTRAINT calendar_item_pkey PRIMARY KEY (user_no, dav_name);
3334
3335
3336 --
3337 -- Name: collection_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3338 --
3339
3340 ALTER TABLE ONLY collection
3341     ADD CONSTRAINT collection_pkey PRIMARY KEY (collection_id);
3342
3343
3344 --
3345 -- Name: collection_user_no_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3346 --
3347
3348 ALTER TABLE ONLY collection
3349     ADD CONSTRAINT collection_user_no_dav_name_key UNIQUE (user_no, dav_name);
3350
3351
3352 --
3353 -- Name: dav_binding_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3354 --
3355
3356 ALTER TABLE ONLY dav_binding
3357     ADD CONSTRAINT dav_binding_dav_name_key UNIQUE (dav_name);
3358
3359
3360 --
3361 -- Name: dav_binding_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3362 --
3363
3364 ALTER TABLE ONLY dav_binding
3365     ADD CONSTRAINT dav_binding_pkey PRIMARY KEY (bind_id);
3366
3367
3368 --
3369 -- Name: freebusy_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3370 --
3371
3372 ALTER TABLE ONLY freebusy_ticket
3373     ADD CONSTRAINT freebusy_ticket_pkey PRIMARY KEY (ticket_id);
3374
3375
3376 --
3377 -- Name: locks_opaquelocktoken_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3378 --
3379
3380 ALTER TABLE ONLY locks
3381     ADD CONSTRAINT locks_opaquelocktoken_key UNIQUE (opaquelocktoken);
3382
3383
3384 --
3385 -- Name: principal_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3386 --
3387
3388 ALTER TABLE ONLY principal
3389     ADD CONSTRAINT principal_pkey PRIMARY KEY (principal_id);
3390
3391
3392 --
3393 -- Name: principal_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3394 --
3395
3396 ALTER TABLE ONLY principal_type
3397     ADD CONSTRAINT principal_type_pkey PRIMARY KEY (principal_type_id);
3398
3399
3400 --
3401 -- Name: property_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3402 --
3403
3404 ALTER TABLE ONLY property
3405     ADD CONSTRAINT property_pkey PRIMARY KEY (dav_name, property_name);
3406
3407
3408 --
3409 -- Name: relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3410 --
3411
3412 ALTER TABLE ONLY relationship
3413     ADD CONSTRAINT relationship_pkey PRIMARY KEY (from_user, to_user, rt_id);
3414
3415
3416 --
3417 -- Name: relationship_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3418 --
3419
3420 ALTER TABLE ONLY relationship_type
3421     ADD CONSTRAINT relationship_type_pkey PRIMARY KEY (rt_id);
3422
3423
3424 --
3425 -- Name: roles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3426 --
3427
3428 ALTER TABLE ONLY roles
3429     ADD CONSTRAINT roles_pkey PRIMARY KEY (role_no);
3430
3431
3432 --
3433 -- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3434 --
3435
3436 ALTER TABLE ONLY session
3437     ADD CONSTRAINT session_pkey PRIMARY KEY (session_id);
3438
3439
3440 --
3441 -- Name: supported_locales_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3442 --
3443
3444 ALTER TABLE ONLY supported_locales
3445     ADD CONSTRAINT supported_locales_pkey PRIMARY KEY (locale);
3446
3447
3448 --
3449 -- Name: sync_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3450 --
3451
3452 ALTER TABLE ONLY sync_tokens
3453     ADD CONSTRAINT sync_tokens_pkey PRIMARY KEY (sync_token);
3454
3455
3456 --
3457 -- Name: timezones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3458 --
3459
3460 ALTER TABLE ONLY timezones
3461     ADD CONSTRAINT timezones_pkey PRIMARY KEY (our_tzno);
3462
3463
3464 --
3465 -- Name: timezones_tzid_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3466 --
3467
3468 ALTER TABLE ONLY timezones
3469     ADD CONSTRAINT timezones_tzid_key UNIQUE (tzid);
3470
3471
3472 --
3473 -- Name: unique_path; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3474 --
3475
3476 ALTER TABLE ONLY collection
3477     ADD CONSTRAINT unique_path UNIQUE (dav_name);
3478
3479
3480 --
3481 -- Name: unique_user; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3482 --
3483
3484 ALTER TABLE ONLY principal
3485     ADD CONSTRAINT unique_user UNIQUE (user_no);
3486
3487
3488 --
3489 -- Name: usr_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3490 --
3491
3492 ALTER TABLE ONLY usr
3493     ADD CONSTRAINT usr_pkey PRIMARY KEY (user_no);
3494
3495
3496 --
3497 -- Name: usr_setting_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
3498 --
3499
3500 ALTER TABLE ONLY usr_setting
3501     ADD CONSTRAINT usr_setting_pkey PRIMARY KEY (user_no, setting_name);
3502
3503
3504 --
3505 -- Name: caldav_data_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3506 --
3507
3508 CREATE INDEX caldav_data_collection_id_fkey ON caldav_data USING btree (collection_id);
3509
3510
3511 --
3512 -- Name: calendar_item_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3513 --
3514
3515 CREATE INDEX calendar_item_collection_id_fkey ON calendar_item USING btree (collection_id);
3516
3517
3518 --
3519 -- Name: grants_pk1; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3520 --
3521
3522 CREATE UNIQUE INDEX grants_pk1 ON grants USING btree (by_principal, to_principal);
3523
3524
3525 --
3526 -- Name: grants_pk2; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3527 --
3528
3529 CREATE UNIQUE INDEX grants_pk2 ON grants USING btree (by_collection, to_principal);
3530
3531
3532 --
3533 -- Name: group_member_pk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3534 --
3535
3536 CREATE UNIQUE INDEX group_member_pk ON group_member USING btree (group_id, member_id);
3537
3538
3539 --
3540 -- Name: group_member_sk; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3541 --
3542
3543 CREATE INDEX group_member_sk ON group_member USING btree (member_id);
3544
3545
3546 --
3547 -- Name: locks_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3548 --
3549
3550 CREATE INDEX locks_dav_name_idx ON locks USING btree (dav_name);
3551
3552
3553 --
3554 -- Name: properties_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3555 --
3556
3557 CREATE INDEX properties_dav_name_idx ON property USING btree (dav_name);
3558
3559
3560 --
3561 -- Name: sync_processing_index; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3562 --
3563
3564 CREATE INDEX sync_processing_index ON sync_changes USING btree (collection_id, dav_id, sync_time);
3565
3566
3567 --
3568 -- Name: usr_sk1_unique_username; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
3569 --
3570
3571 CREATE UNIQUE INDEX usr_sk1_unique_username ON usr USING btree (lower(username));
3572
3573
3574 --
3575 -- Name: dav_principal_delete; Type: RULE; Schema: public; Owner: postgres
3576 --
3577
3578 CREATE RULE dav_principal_delete AS ON DELETE TO dav_principal DO INSTEAD (DELETE FROM usr WHERE (usr.user_no = old.user_no); DELETE FROM principal WHERE (principal.principal_id = old.principal_id); );
3579
3580
3581 --
3582 -- Name: dav_principal_insert; Type: RULE; Schema: public; Owner: postgres
3583 --
3584
3585 CREATE RULE dav_principal_insert AS ON INSERT TO dav_principal DO INSTEAD (INSERT INTO usr (user_no, active, joined, updated, username, password, fullname, email, email_ok, date_format_type, locale) VALUES (COALESCE(new.user_no, nextval('usr_user_no_seq'::regclass)), COALESCE(new.user_active, true), COALESCE(new.created, now()), COALESCE(new.modified, now()), new.username, new.password, COALESCE(new.fullname, new.displayname), new.email, new.email_ok, COALESCE(new.date_format_type, 'E'::text), new.locale); INSERT INTO principal (user_no, principal_id, type_id, displayname, default_privileges) VALUES (COALESCE(new.user_no, currval('usr_user_no_seq'::regclass)), COALESCE(new.principal_id, nextval('dav_id_seq'::regclass)), new.type_id, COALESCE(new.displayname, new.fullname), COALESCE(new.default_privileges, (0)::bit(24))); );
3586
3587
3588 --
3589 -- Name: dav_principal_update; Type: RULE; Schema: public; Owner: postgres
3590 --
3591
3592 CREATE RULE dav_principal_update AS ON UPDATE TO dav_principal DO INSTEAD (UPDATE usr SET user_no = new.user_no, active = new.user_active, updated = now(), username = new.username, password = new.password, fullname = new.fullname, email = new.email, email_ok = new.email_ok, date_format_type = new.date_format_type, locale = new.locale WHERE (usr.user_no = old.user_no); UPDATE principal SET principal_id = new.principal_id, type_id = new.type_id, displayname = new.displayname, default_privileges = new.default_privileges WHERE (principal.principal_id = old.principal_id); );
3593
3594
3595 --
3596 -- Name: alarm_changed; Type: TRIGGER; Schema: public; Owner: postgres
3597 --
3598
3599 CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm FOR EACH ROW EXECUTE PROCEDURE alarm_changed();
3600
3601
3602 --
3603 -- Name: caldav_data_modified; Type: TRIGGER; Schema: public; Owner: postgres
3604 --
3605
3606 CREATE TRIGGER caldav_data_modified AFTER INSERT OR DELETE OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified();
3607
3608
3609 --
3610 -- Name: caldav_data_sync_dav_id; Type: TRIGGER; Schema: public; Owner: postgres
3611 --
3612
3613 CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
3614
3615
3616 --
3617 -- Name: collection_modified; Type: TRIGGER; Schema: public; Owner: postgres
3618 --
3619
3620 CREATE TRIGGER collection_modified AFTER UPDATE ON collection FOR EACH ROW EXECUTE PROCEDURE collection_modified();
3621
3622
3623 --
3624 -- Name: grants_modified; Type: TRIGGER; Schema: public; Owner: postgres
3625 --
3626
3627 CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants FOR EACH ROW EXECUTE PROCEDURE grants_modified();
3628
3629
3630 --
3631 -- Name: principal_modified; Type: TRIGGER; Schema: public; Owner: postgres
3632 --
3633
3634 CREATE TRIGGER principal_modified AFTER UPDATE ON principal FOR EACH ROW EXECUTE PROCEDURE principal_modified();
3635
3636
3637 --
3638 -- Name: usr_modified; Type: TRIGGER; Schema: public; Owner: postgres
3639 --
3640
3641 CREATE TRIGGER usr_modified AFTER UPDATE ON usr FOR EACH ROW EXECUTE PROCEDURE usr_modified();
3642
3643
3644 --
3645 -- Name: access_ticket_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3646 --
3647
3648 ALTER TABLE ONLY access_ticket
3649     ADD CONSTRAINT access_ticket_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE;
3650
3651
3652 --
3653 -- Name: access_ticket_target_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3654 --
3655
3656 ALTER TABLE ONLY access_ticket
3657     ADD CONSTRAINT access_ticket_target_collection_id_fkey FOREIGN KEY (target_collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
3658
3659
3660 --
3661 -- Name: access_ticket_target_resource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3662 --
3663
3664 ALTER TABLE ONLY access_ticket
3665     ADD CONSTRAINT access_ticket_target_resource_id_fkey FOREIGN KEY (target_resource_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3666
3667
3668 --
3669 -- Name: addressbook_address_adr_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3670 --
3671
3672 ALTER TABLE ONLY addressbook_address_adr
3673     ADD CONSTRAINT addressbook_address_adr_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3674
3675
3676 --
3677 -- Name: addressbook_address_email_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3678 --
3679
3680 ALTER TABLE ONLY addressbook_address_email
3681     ADD CONSTRAINT addressbook_address_email_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3682
3683
3684 --
3685 -- Name: addressbook_address_tel_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3686 --
3687
3688 ALTER TABLE ONLY addressbook_address_tel
3689     ADD CONSTRAINT addressbook_address_tel_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3690
3691
3692 --
3693 -- Name: addressbook_resource_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3694 --
3695
3696 ALTER TABLE ONLY addressbook_resource
3697     ADD CONSTRAINT addressbook_resource_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3698
3699
3700 --
3701 -- Name: caldav_data_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3702 --
3703
3704 ALTER TABLE ONLY caldav_data
3705     ADD CONSTRAINT caldav_data_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3706
3707
3708 --
3709 -- Name: caldav_data_logged_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3710 --
3711
3712 ALTER TABLE ONLY caldav_data
3713     ADD CONSTRAINT caldav_data_logged_user_fkey FOREIGN KEY (logged_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE;
3714
3715
3716 --
3717 -- Name: caldav_data_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3718 --
3719
3720 ALTER TABLE ONLY caldav_data
3721     ADD CONSTRAINT caldav_data_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3722
3723
3724 --
3725 -- Name: caldav_exists; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3726 --
3727
3728 ALTER TABLE ONLY calendar_item
3729     ADD CONSTRAINT caldav_exists FOREIGN KEY (user_no, dav_name) REFERENCES caldav_data(user_no, dav_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3730
3731
3732 --
3733 -- Name: calendar_alarm_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3734 --
3735
3736 ALTER TABLE ONLY calendar_alarm
3737     ADD CONSTRAINT calendar_alarm_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3738
3739
3740 --
3741 -- Name: calendar_attendee_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3742 --
3743
3744 ALTER TABLE ONLY calendar_attendee
3745     ADD CONSTRAINT calendar_attendee_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE;
3746
3747
3748 --
3749 -- Name: calendar_item_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3750 --
3751
3752 ALTER TABLE ONLY calendar_item
3753     ADD CONSTRAINT calendar_item_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3754
3755
3756 --
3757 -- Name: calendar_item_tz_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3758 --
3759
3760 ALTER TABLE ONLY calendar_item
3761     ADD CONSTRAINT calendar_item_tz_id_fkey FOREIGN KEY (tz_id) REFERENCES timezones(tzid);
3762
3763
3764 --
3765 -- Name: calendar_item_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3766 --
3767
3768 ALTER TABLE ONLY calendar_item
3769     ADD CONSTRAINT calendar_item_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3770
3771
3772 --
3773 -- Name: collection_timezone_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3774 --
3775
3776 ALTER TABLE ONLY collection
3777     ADD CONSTRAINT collection_timezone_fkey FOREIGN KEY (timezone) REFERENCES timezones(tzid) ON UPDATE CASCADE ON DELETE SET NULL;
3778
3779
3780 --
3781 -- Name: collection_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3782 --
3783
3784 ALTER TABLE ONLY collection
3785     ADD CONSTRAINT collection_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3786
3787
3788 --
3789 -- Name: dav_binding_access_ticket_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3790 --
3791
3792 ALTER TABLE ONLY dav_binding
3793     ADD CONSTRAINT dav_binding_access_ticket_id_fkey FOREIGN KEY (access_ticket_id) REFERENCES access_ticket(ticket_id) ON UPDATE CASCADE ON DELETE SET NULL;
3794
3795
3796 --
3797 -- Name: dav_binding_bound_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3798 --
3799
3800 ALTER TABLE ONLY dav_binding
3801     ADD CONSTRAINT dav_binding_bound_source_id_fkey FOREIGN KEY (bound_source_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
3802
3803
3804 --
3805 -- Name: dav_binding_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3806 --
3807
3808 ALTER TABLE ONLY dav_binding
3809     ADD CONSTRAINT dav_binding_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE;
3810
3811
3812 --
3813 -- Name: freebusy_ticket_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3814 --
3815
3816 ALTER TABLE ONLY freebusy_ticket
3817     ADD CONSTRAINT freebusy_ticket_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
3818
3819
3820 --
3821 -- Name: grants_by_collection_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3822 --
3823
3824 ALTER TABLE ONLY grants
3825     ADD CONSTRAINT grants_by_collection_fkey FOREIGN KEY (by_collection) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3826
3827
3828 --
3829 -- Name: grants_by_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3830 --
3831
3832 ALTER TABLE ONLY grants
3833     ADD CONSTRAINT grants_by_principal_fkey FOREIGN KEY (by_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3834
3835
3836 --
3837 -- Name: grants_to_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3838 --
3839
3840 ALTER TABLE ONLY grants
3841     ADD CONSTRAINT grants_to_principal_fkey FOREIGN KEY (to_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3842
3843
3844 --
3845 -- Name: group_member_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3846 --
3847
3848 ALTER TABLE ONLY group_member
3849     ADD CONSTRAINT group_member_group_id_fkey FOREIGN KEY (group_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3850
3851
3852 --
3853 -- Name: group_member_member_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3854 --
3855
3856 ALTER TABLE ONLY group_member
3857     ADD CONSTRAINT group_member_member_id_fkey FOREIGN KEY (member_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3858
3859
3860 --
3861 -- Name: principal_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3862 --
3863
3864 ALTER TABLE ONLY principal
3865     ADD CONSTRAINT principal_type_id_fkey FOREIGN KEY (type_id) REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE;
3866
3867
3868 --
3869 -- Name: principal_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3870 --
3871
3872 ALTER TABLE ONLY principal
3873     ADD CONSTRAINT principal_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;
3874
3875
3876 --
3877 -- Name: property_changed_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3878 --
3879
3880 ALTER TABLE ONLY property
3881     ADD CONSTRAINT property_changed_by_fkey FOREIGN KEY (changed_by) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT;
3882
3883
3884 --
3885 -- Name: relationship_from_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3886 --
3887
3888 ALTER TABLE ONLY relationship
3889     ADD CONSTRAINT relationship_from_user_fkey FOREIGN KEY (from_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
3890
3891
3892 --
3893 -- Name: relationship_rt_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3894 --
3895
3896 ALTER TABLE ONLY relationship
3897     ADD CONSTRAINT relationship_rt_id_fkey FOREIGN KEY (rt_id) REFERENCES relationship_type(rt_id) ON UPDATE CASCADE ON DELETE CASCADE;
3898
3899
3900 --
3901 -- Name: relationship_to_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3902 --
3903
3904 ALTER TABLE ONLY relationship
3905     ADD CONSTRAINT relationship_to_user_fkey FOREIGN KEY (to_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE;
3906
3907
3908 --
3909 -- Name: role_member_role_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3910 --
3911
3912 ALTER TABLE ONLY role_member
3913     ADD CONSTRAINT role_member_role_no_fkey FOREIGN KEY (role_no) REFERENCES roles(role_no);
3914
3915
3916 --
3917 -- Name: role_member_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3918 --
3919
3920 ALTER TABLE ONLY role_member
3921     ADD CONSTRAINT role_member_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
3922
3923
3924 --
3925 -- Name: session_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3926 --
3927
3928 ALTER TABLE ONLY session
3929     ADD CONSTRAINT session_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
3930
3931
3932 --
3933 -- Name: sync_changes_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3934 --
3935
3936 ALTER TABLE ONLY sync_changes
3937     ADD CONSTRAINT sync_changes_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
3938
3939
3940 --
3941 -- Name: sync_tokens_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3942 --
3943
3944 ALTER TABLE ONLY sync_tokens
3945     ADD CONSTRAINT sync_tokens_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE;
3946
3947
3948 --
3949 -- Name: tmp_password_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3950 --
3951
3952 ALTER TABLE ONLY tmp_password
3953     ADD CONSTRAINT tmp_password_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no);
3954
3955
3956 --
3957 -- Name: tz_aliases_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3958 --
3959
3960 ALTER TABLE ONLY tz_aliases
3961     ADD CONSTRAINT tz_aliases_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno);
3962
3963
3964 --
3965 -- Name: tz_localnames_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3966 --
3967
3968 ALTER TABLE ONLY tz_localnames
3969     ADD CONSTRAINT tz_localnames_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno);
3970
3971
3972 --
3973 -- Name: usr_setting_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3974 --
3975
3976 ALTER TABLE ONLY usr_setting
3977     ADD CONSTRAINT usr_setting_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE;
3978
3979
3980 --
3981 -- Name: public; Type: ACL; Schema: -; Owner: postgres
3982 --
3983
3984 REVOKE ALL ON SCHEMA public FROM PUBLIC;
3985 REVOKE ALL ON SCHEMA public FROM postgres;
3986 GRANT ALL ON SCHEMA public TO postgres;
3987 GRANT ALL ON SCHEMA public TO PUBLIC;
3988
3989
3990 --
3991 -- Name: access_ticket; Type: ACL; Schema: public; Owner: postgres
3992 --
3993
3994 REVOKE ALL ON TABLE access_ticket FROM PUBLIC;
3995 REVOKE ALL ON TABLE access_ticket FROM postgres;
3996 GRANT ALL ON TABLE access_ticket TO postgres;
3997
3998
3999 --
4000 -- Name: addressbook_address_adr; Type: ACL; Schema: public; Owner: postgres
4001 --
4002
4003 REVOKE ALL ON TABLE addressbook_address_adr FROM PUBLIC;
4004 REVOKE ALL ON TABLE addressbook_address_adr FROM postgres;
4005 GRANT ALL ON TABLE addressbook_address_adr TO postgres;
4006
4007
4008 --
4009 -- Name: addressbook_address_email; Type: ACL; Schema: public; Owner: postgres
4010 --
4011
4012 REVOKE ALL ON TABLE addressbook_address_email FROM PUBLIC;
4013 REVOKE ALL ON TABLE addressbook_address_email FROM postgres;
4014 GRANT ALL ON TABLE addressbook_address_email TO postgres;
4015
4016
4017 --
4018 -- Name: addressbook_address_tel; Type: ACL; Schema: public; Owner: postgres
4019 --
4020
4021 REVOKE ALL ON TABLE addressbook_address_tel FROM PUBLIC;
4022 REVOKE ALL ON TABLE addressbook_address_tel FROM postgres;
4023 GRANT ALL ON TABLE addressbook_address_tel TO postgres;
4024
4025
4026 --
4027 -- Name: addressbook_resource; Type: ACL; Schema: public; Owner: postgres
4028 --
4029
4030 REVOKE ALL ON TABLE addressbook_resource FROM PUBLIC;
4031 REVOKE ALL ON TABLE addressbook_resource FROM postgres;
4032 GRANT ALL ON TABLE addressbook_resource TO postgres;
4033
4034
4035 --
4036 -- Name: awl_db_revision; Type: ACL; Schema: public; Owner: postgres
4037 --
4038
4039 REVOKE ALL ON TABLE awl_db_revision FROM PUBLIC;
4040 REVOKE ALL ON TABLE awl_db_revision FROM postgres;
4041 GRANT ALL ON TABLE awl_db_revision TO postgres;
4042
4043
4044 --
4045 -- Name: dav_id_seq; Type: ACL; Schema: public; Owner: postgres
4046 --
4047
4048 REVOKE ALL ON SEQUENCE dav_id_seq FROM PUBLIC;
4049 REVOKE ALL ON SEQUENCE dav_id_seq FROM postgres;
4050 GRANT ALL ON SEQUENCE dav_id_seq TO postgres;
4051
4052
4053 --
4054 -- Name: caldav_data; Type: ACL; Schema: public; Owner: postgres
4055 --
4056
4057 REVOKE ALL ON TABLE caldav_data FROM PUBLIC;
4058 REVOKE ALL ON TABLE caldav_data FROM postgres;
4059 GRANT ALL ON TABLE caldav_data TO postgres;
4060
4061
4062 --
4063 -- Name: calendar_alarm; Type: ACL; Schema: public; Owner: postgres
4064 --
4065
4066 REVOKE ALL ON TABLE calendar_alarm FROM PUBLIC;
4067 REVOKE ALL ON TABLE calendar_alarm FROM postgres;
4068 GRANT ALL ON TABLE calendar_alarm TO postgres;
4069
4070
4071 --
4072 -- Name: calendar_attendee; Type: ACL; Schema: public; Owner: postgres
4073 --
4074
4075 REVOKE ALL ON TABLE calendar_attendee FROM PUBLIC;
4076 REVOKE ALL ON TABLE calendar_attendee FROM postgres;
4077 GRANT ALL ON TABLE calendar_attendee TO postgres;
4078
4079
4080 --
4081 -- Name: calendar_item; Type: ACL; Schema: public; Owner: postgres
4082 --
4083
4084 REVOKE ALL ON TABLE calendar_item FROM PUBLIC;
4085 REVOKE ALL ON TABLE calendar_item FROM postgres;
4086 GRANT ALL ON TABLE calendar_item TO postgres;
4087
4088
4089 --
4090 -- Name: collection; Type: ACL; Schema: public; Owner: postgres
4091 --
4092
4093 REVOKE ALL ON TABLE collection FROM PUBLIC;
4094 REVOKE ALL ON TABLE collection FROM postgres;
4095 GRANT ALL ON TABLE collection TO postgres;
4096
4097
4098 --
4099 -- Name: dav_binding; Type: ACL; Schema: public; Owner: postgres
4100 --
4101
4102 REVOKE ALL ON TABLE dav_binding FROM PUBLIC;
4103 REVOKE ALL ON TABLE dav_binding FROM postgres;
4104 GRANT ALL ON TABLE dav_binding TO postgres;
4105
4106
4107 --
4108 -- Name: principal; Type: ACL; Schema: public; Owner: postgres
4109 --
4110
4111 REVOKE ALL ON TABLE principal FROM PUBLIC;
4112 REVOKE ALL ON TABLE principal FROM postgres;
4113 GRANT ALL ON TABLE principal TO postgres;
4114
4115
4116 --
4117 -- Name: usr; Type: ACL; Schema: public; Owner: postgres
4118 --
4119
4120 REVOKE ALL ON TABLE usr FROM PUBLIC;
4121 REVOKE ALL ON TABLE usr FROM postgres;
4122 GRANT ALL ON TABLE usr TO postgres;
4123
4124
4125 --
4126 -- Name: dav_principal; Type: ACL; Schema: public; Owner: postgres
4127 --
4128
4129 REVOKE ALL ON TABLE dav_principal FROM PUBLIC;
4130 REVOKE ALL ON TABLE dav_principal FROM postgres;
4131 GRANT ALL ON TABLE dav_principal TO postgres;
4132
4133
4134 --
4135 -- Name: freebusy_ticket; Type: ACL; Schema: public; Owner: postgres
4136 --
4137
4138 REVOKE ALL ON TABLE freebusy_ticket FROM PUBLIC;
4139 REVOKE ALL ON TABLE freebusy_ticket FROM postgres;
4140 GRANT ALL ON TABLE freebusy_ticket TO postgres;
4141
4142
4143 --
4144 -- Name: grants; Type: ACL; Schema: public; Owner: postgres
4145 --
4146
4147 REVOKE ALL ON TABLE grants FROM PUBLIC;
4148 REVOKE ALL ON TABLE grants FROM postgres;
4149 GRANT ALL ON TABLE grants TO postgres;
4150
4151
4152 --
4153 -- Name: group_member; Type: ACL; Schema: public; Owner: postgres
4154 --
4155
4156 REVOKE ALL ON TABLE group_member FROM PUBLIC;
4157 REVOKE ALL ON TABLE group_member FROM postgres;
4158 GRANT ALL ON TABLE group_member TO postgres;
4159
4160
4161 --
4162 -- Name: locks; Type: ACL; Schema: public; Owner: postgres
4163 --
4164
4165 REVOKE ALL ON TABLE locks FROM PUBLIC;
4166 REVOKE ALL ON TABLE locks FROM postgres;
4167 GRANT ALL ON TABLE locks TO postgres;
4168
4169
4170 --
4171 -- Name: principal_type; Type: ACL; Schema: public; Owner: postgres
4172 --
4173
4174 REVOKE ALL ON TABLE principal_type FROM PUBLIC;
4175 REVOKE ALL ON TABLE principal_type FROM postgres;
4176 GRANT ALL ON TABLE principal_type TO postgres;
4177
4178
4179 --
4180 -- Name: principal_type_principal_type_id_seq; Type: ACL; Schema: public; Owner: postgres
4181 --
4182
4183 REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM PUBLIC;
4184 REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM postgres;
4185 GRANT ALL ON SEQUENCE principal_type_principal_type_id_seq TO postgres;
4186
4187
4188 --
4189 -- Name: property; Type: ACL; Schema: public; Owner: postgres
4190 --
4191
4192 REVOKE ALL ON TABLE property FROM PUBLIC;
4193 REVOKE ALL ON TABLE property FROM postgres;
4194 GRANT ALL ON TABLE property TO postgres;
4195
4196
4197 --
4198 -- Name: relationship; Type: ACL; Schema: public; Owner: postgres
4199 --
4200
4201 REVOKE ALL ON TABLE relationship FROM PUBLIC;
4202 REVOKE ALL ON TABLE relationship FROM postgres;
4203 GRANT ALL ON TABLE relationship TO postgres;
4204
4205
4206 --
4207 -- Name: relationship_type; Type: ACL; Schema: public; Owner: postgres
4208 --
4209
4210 REVOKE ALL ON TABLE relationship_type FROM PUBLIC;
4211 REVOKE ALL ON TABLE relationship_type FROM postgres;
4212 GRANT ALL ON TABLE relationship_type TO postgres;
4213
4214
4215 --
4216 -- Name: relationship_type_rt_id_seq; Type: ACL; Schema: public; Owner: postgres
4217 --
4218
4219 REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM PUBLIC;
4220 REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM postgres;
4221 GRANT ALL ON SEQUENCE relationship_type_rt_id_seq TO postgres;
4222
4223
4224 --
4225 -- Name: role_member; Type: ACL; Schema: public; Owner: postgres
4226 --
4227
4228 REVOKE ALL ON TABLE role_member FROM PUBLIC;
4229 REVOKE ALL ON TABLE role_member FROM postgres;
4230 GRANT ALL ON TABLE role_member TO postgres;
4231
4232
4233 --
4234 -- Name: roles; Type: ACL; Schema: public; Owner: postgres
4235 --
4236
4237 REVOKE ALL ON TABLE roles FROM PUBLIC;
4238 REVOKE ALL ON TABLE roles FROM postgres;
4239 GRANT ALL ON TABLE roles TO postgres;
4240
4241
4242 --
4243 -- Name: roles_role_no_seq; Type: ACL; Schema: public; Owner: postgres
4244 --
4245
4246 REVOKE ALL ON SEQUENCE roles_role_no_seq FROM PUBLIC;
4247 REVOKE ALL ON SEQUENCE roles_role_no_seq FROM postgres;
4248 GRANT ALL ON SEQUENCE roles_role_no_seq TO postgres;
4249
4250
4251 --
4252 -- Name: session; Type: ACL; Schema: public; Owner: postgres
4253 --
4254
4255 REVOKE ALL ON TABLE session FROM PUBLIC;
4256 REVOKE ALL ON TABLE session FROM postgres;
4257 GRANT ALL ON TABLE session TO postgres;
4258
4259
4260 --
4261 -- Name: session_session_id_seq; Type: ACL; Schema: public; Owner: postgres
4262 --
4263
4264 REVOKE ALL ON SEQUENCE session_session_id_seq FROM PUBLIC;
4265 REVOKE ALL ON SEQUENCE session_session_id_seq FROM postgres;
4266 GRANT ALL ON SEQUENCE session_session_id_seq TO postgres;
4267
4268
4269 --
4270 -- Name: supported_locales; Type: ACL; Schema: public; Owner: postgres
4271 --
4272
4273 REVOKE ALL ON TABLE supported_locales FROM PUBLIC;
4274 REVOKE ALL ON TABLE supported_locales FROM postgres;
4275 GRANT ALL ON TABLE supported_locales TO postgres;
4276
4277
4278 --
4279 -- Name: sync_changes; Type: ACL; Schema: public; Owner: postgres
4280 --
4281
4282 REVOKE ALL ON TABLE sync_changes FROM PUBLIC;
4283 REVOKE ALL ON TABLE sync_changes FROM postgres;
4284 GRANT ALL ON TABLE sync_changes TO postgres;
4285
4286
4287 --
4288 -- Name: sync_tokens; Type: ACL; Schema: public; Owner: postgres
4289 --
4290
4291 REVOKE ALL ON TABLE sync_tokens FROM PUBLIC;
4292 REVOKE ALL ON TABLE sync_tokens FROM postgres;
4293 GRANT ALL ON TABLE sync_tokens TO postgres;
4294
4295
4296 --
4297 -- Name: sync_tokens_sync_token_seq; Type: ACL; Schema: public; Owner: postgres
4298 --
4299
4300 REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM PUBLIC;
4301 REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM postgres;
4302 GRANT ALL ON SEQUENCE sync_tokens_sync_token_seq TO postgres;
4303
4304
4305 --
4306 -- Name: timezones; Type: ACL; Schema: public; Owner: postgres
4307 --
4308
4309 REVOKE ALL ON TABLE timezones FROM PUBLIC;
4310 REVOKE ALL ON TABLE timezones FROM postgres;
4311 GRANT ALL ON TABLE timezones TO postgres;
4312
4313
4314 --
4315 -- Name: timezones_our_tzno_seq; Type: ACL; Schema: public; Owner: postgres
4316 --
4317
4318 REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM PUBLIC;
4319 REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM postgres;
4320 GRANT ALL ON SEQUENCE timezones_our_tzno_seq TO postgres;
4321
4322
4323 --
4324 -- Name: tmp_password; Type: ACL; Schema: public; Owner: postgres
4325 --
4326
4327 REVOKE ALL ON TABLE tmp_password FROM PUBLIC;
4328 REVOKE ALL ON TABLE tmp_password FROM postgres;
4329 GRANT ALL ON TABLE tmp_password TO postgres;
4330
4331
4332 --
4333 -- Name: tz_aliases; Type: ACL; Schema: public; Owner: postgres
4334 --
4335
4336 REVOKE ALL ON TABLE tz_aliases FROM PUBLIC;
4337 REVOKE ALL ON TABLE tz_aliases FROM postgres;
4338 GRANT ALL ON TABLE tz_aliases TO postgres;
4339
4340
4341 --
4342 -- Name: tz_localnames; Type: ACL; Schema: public; Owner: postgres
4343 --
4344
4345 REVOKE ALL ON TABLE tz_localnames FROM PUBLIC;
4346 REVOKE ALL ON TABLE tz_localnames FROM postgres;
4347 GRANT ALL ON TABLE tz_localnames TO postgres;
4348
4349
4350 --
4351 -- Name: usr_setting; Type: ACL; Schema: public; Owner: postgres
4352 --
4353
4354 REVOKE ALL ON TABLE usr_setting FROM PUBLIC;
4355 REVOKE ALL ON TABLE usr_setting FROM postgres;
4356 GRANT ALL ON TABLE usr_setting TO postgres;
4357
4358
4359 --
4360 -- Name: usr_user_no_seq; Type: ACL; Schema: public; Owner: postgres
4361 --
4362
4363 REVOKE ALL ON SEQUENCE usr_user_no_seq FROM PUBLIC;
4364 REVOKE ALL ON SEQUENCE usr_user_no_seq FROM postgres;
4365 GRANT ALL ON SEQUENCE usr_user_no_seq TO postgres;
4366
4367
4368 --
4369 -- PostgreSQL database dump complete
4370 --
4371