dba/davical.sql

   1 -- DAViCal CalDAV Server - Database Schema
   2 --
   3
   4
   5 -- Given a verbose DAV: or CalDAV: privilege name return the bitmask
   6 CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$
   7 DECLARE
   8   raw_priv ALIAS FOR $1;
   9   in_priv TEXT;
  10 BEGIN
  11   in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', '')));
  12   IF in_priv = 'all' THEN
  13     RETURN ~ 0::BIT(24);
  14   END IF;
  15
  16   RETURN (CASE
  17             WHEN in_priv = 'read'                            THEN  4609 -- 1 + 512 + 4096
  18             WHEN in_priv = 'write'                           THEN   198 -- 2 + 4 + 64 + 128
  19             WHEN in_priv = 'write-properties'                THEN     2
  20             WHEN in_priv = 'write-content'                   THEN     4
  21             WHEN in_priv = 'unlock'                          THEN     8
  22             WHEN in_priv = 'read-acl'                        THEN    16
  23             WHEN in_priv = 'read-current-user-privilege-set' THEN    32
  24             WHEN in_priv = 'bind'                            THEN    64
  25             WHEN in_priv = 'unbind'                          THEN   128
  26             WHEN in_priv = 'write-acl'                       THEN   256
  27             WHEN in_priv = 'read-free-busy'                  THEN  4608 --  512 + 4096
  28             WHEN in_priv = 'schedule-deliver'                THEN  7168 -- 1024 + 2048 + 4096
  29             WHEN in_priv = 'schedule-deliver-invite'         THEN  1024
  30             WHEN in_priv = 'schedule-deliver-reply'          THEN  2048
  31             WHEN in_priv = 'schedule-query-freebusy'         THEN  4096
  32             WHEN in_priv = 'schedule-send'                   THEN 57344 -- 8192 + 16384 + 32768
  33             WHEN in_priv = 'schedule-send-invite'            THEN  8192
  34             WHEN in_priv = 'schedule-send-reply'             THEN 16384
  35             WHEN in_priv = 'schedule-send-freebusy'          THEN 32768
  36           ELSE 0 END)::BIT(24);
  37 END
  38 $$
  39 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
  40
  41 -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask
  42 CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$
  43 DECLARE
  44   raw_privs ALIAS FOR $1;
  45   in_priv TEXT;
  46   out_bits BIT(24);
  47   i INT;
  48   allprivs BIT(24);
  49   start INT;
  50   finish INT;
  51 BEGIN
  52   out_bits := 0::BIT(24);
  53   allprivs := ~ out_bits;
  54   SELECT array_lower(raw_privs,1) INTO start;
  55   SELECT array_upper(raw_privs,1) INTO finish;
  56   FOR i IN start .. finish  LOOP
  57     SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits;
  58     IF out_bits = allprivs THEN
  59       RETURN allprivs;
  60     END IF;
  61   END LOOP;
  62   RETURN out_bits;
  63 END
  64 $$
  65 LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;
  66
  67
  68 -- This sequence is used in a number of places so that any DAV resource will have a unique ID
  69 CREATE SEQUENCE dav_id_seq;
  70
  71
  72 -- Used by the timezone server, but we also load it with random timezones
  73 -- from events, if we don't already have those definitions.
  74 CREATE TABLE timezones (
  75   our_tzno SERIAL PRIMARY KEY,
  76   tzid TEXT UNIQUE NOT NULL,
  77   olson_name TEXT,
  78   active BOOLEAN,
  79   last_modified TIMESTAMP DEFAULT current_timestamp,
  80   etag TEXT,
  81   vtimezone TEXT
  82 );
  83
  84 CREATE TABLE tz_aliases (
  85   our_tzno INT8 REFERENCES timezones(our_tzno),
  86   tzalias TEXT NOT NULL
  87 );
  88
  89 CREATE TABLE tz_localnames (
  90   our_tzno INT8 REFERENCES timezones(our_tzno),
  91   locale TEXT NOT NULL,
  92   localised_name TEXT NOT NULL,
  93   preferred BOOLEAN DEFAULT TRUE
  94 );
  95
  96
  97 -- Something that can look like a filesystem hierarchy where we store stuff
  98 CREATE TABLE collection (
  99   user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 100   parent_container TEXT,
 101   dav_name TEXT,
 102   dav_etag TEXT,
 103   dav_displayname TEXT,
 104   is_calendar BOOLEAN,
 105   created TIMESTAMP WITH TIME ZONE,
 106   modified TIMESTAMP WITH TIME ZONE,
 107   public_events_only BOOLEAN NOT NULL DEFAULT FALSE,
 108   publicly_readable BOOLEAN NOT NULL DEFAULT FALSE,
 109   collection_id INT8 PRIMARY KEY DEFAULT nextval('dav_id_seq'),
 110   default_privileges BIT(24),
 111   is_addressbook BOOLEAN DEFAULT FALSE,
 112   resourcetypes TEXT DEFAULT '<DAV::collection/>',
 113   schedule_transp TEXT DEFAULT 'opaque',
 114   timezone TEXT REFERENCES timezones(tzid) ON DELETE SET NULL ON UPDATE CASCADE,
 115   description TEXT DEFAULT '',
 116   UNIQUE(user_no,dav_name)
 117 );
 118
 119 ALTER TABLE collection ADD CONSTRAINT unique_path UNIQUE (dav_name);
 120
 121 -- The main event.  Where we store the things the calendar throws at us.
 122 CREATE TABLE caldav_data (
 123   user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 124   dav_name TEXT,
 125   dav_etag TEXT,
 126   created TIMESTAMP WITH TIME ZONE,
 127   modified TIMESTAMP WITH TIME ZONE,
 128   caldav_data TEXT,
 129   caldav_type TEXT,
 130   logged_user INT references usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE,
 131   dav_id INT8 UNIQUE DEFAULT nextval('dav_id_seq'),
 132   collection_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 133   weak_etag TEXT DEFAULT NULL,
 134
 135   PRIMARY KEY ( user_no, dav_name )
 136 );
 137 CREATE INDEX caldav_data_collection_id_fkey ON caldav_data(collection_id);
 138
 139 -- The parsed calendar item.  Here we have pulled those events/todos/journals apart somewhat.
 140 CREATE TABLE calendar_item (
 141   user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 142   dav_name TEXT,
 143   dav_etag TEXT,
 144
 145   -- Extracted vEvent/vTodo data
 146   uid TEXT,
 147   created TIMESTAMP,
 148   last_modified TIMESTAMP,
 149   dtstamp TIMESTAMP,
 150   dtstart TIMESTAMP WITH TIME ZONE,
 151   dtend TIMESTAMP WITH TIME ZONE,
 152   due TIMESTAMP WITH TIME ZONE,
 153   summary TEXT,
 154   location TEXT,
 155   description TEXT,
 156   priority INT,
 157   class TEXT,
 158   transp TEXT,
 159   rrule TEXT,
 160   url TEXT,
 161   percent_complete NUMERIC(7,2),
 162   tz_id TEXT REFERENCES timezones( tzid ),
 163   status TEXT,
 164   completed TIMESTAMP WITH TIME ZONE,
 165   dav_id INT8 UNIQUE,
 166   collection_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 167   first_instance_start TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
 168   last_instance_end    TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
 169
 170   -- Cascade updates / deletes from the caldav_data table
 171   CONSTRAINT caldav_exists FOREIGN KEY ( user_no, dav_name )
 172                 REFERENCES caldav_data ( user_no, dav_name )
 173                 MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
 174
 175   PRIMARY KEY ( user_no, dav_name )
 176 );
 177 CREATE INDEX calendar_item_collection_id_fkey ON calendar_item(collection_id);
 178
 179
 180
 181 -- Each user can be related to each other user.  This mechanism can also
 182 -- be used to define groups of users, since some relationships are transitive.
 183 CREATE TABLE relationship_type (
 184   rt_id SERIAL PRIMARY KEY,
 185   rt_name TEXT,
 186   rt_togroup BOOLEAN,
 187   confers TEXT DEFAULT 'RW',
 188   rt_fromgroup BOOLEAN,
 189   bit_confers BIT(24) DEFAULT privilege_to_bits(ARRAY['DAV::read','DAV::write'])
 190 );
 191
 192
 193 CREATE TABLE relationship (
 194   from_user INT REFERENCES usr (user_no) ON UPDATE CASCADE ON DELETE CASCADE,
 195   to_user INT REFERENCES usr (user_no) ON UPDATE CASCADE ON DELETE CASCADE,
 196   rt_id INT REFERENCES relationship_type (rt_id) ON UPDATE CASCADE ON DELETE CASCADE,
 197   confers BIT(24) DEFAULT privilege_to_bits(ARRAY['DAV::read','DAV::write']),
 198
 199   PRIMARY KEY ( from_user, to_user, rt_id )
 200 );
 201
 202
 203 CREATE TABLE locks (
 204   dav_name TEXT,
 205   opaquelocktoken TEXT UNIQUE NOT NULL,
 206   type TEXT,
 207   scope TEXT,
 208   depth INT,
 209   owner TEXT,
 210   timeout INTERVAL,
 211   start TIMESTAMP DEFAULT current_timestamp
 212 );
 213 CREATE INDEX locks_dav_name_idx ON locks(dav_name);
 214
 215
 216 CREATE TABLE property (
 217   dav_name TEXT,
 218   property_name TEXT,
 219   property_value TEXT,
 220   changed_on TIMESTAMP DEFAULT current_timestamp,
 221   changed_by INT REFERENCES usr ( user_no ) ON UPDATE CASCADE ON DELETE SET DEFAULT,
 222   PRIMARY KEY ( dav_name, property_name )
 223 );
 224 CREATE INDEX properties_dav_name_idx ON property(dav_name);
 225
 226
 227 CREATE TABLE freebusy_ticket (
 228   ticket_id TEXT NOT NULL PRIMARY KEY,
 229   user_no integer NOT NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE,
 230   created timestamp with time zone DEFAULT current_timestamp NOT NULL
 231 );
 232
 233
 234
 235 CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$
 236   DECLARE
 237   BEGIN
 238
 239     IF TG_OP = 'DELETE' THEN
 240       -- Just let the ON DELETE CASCADE handle this case
 241       RETURN OLD;
 242     END IF;
 243
 244     IF NEW.dav_id IS NULL THEN
 245       NEW.dav_id = nextval('dav_id_seq');
 246     END IF;
 247
 248     IF TG_OP = 'UPDATE' THEN
 249       IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id
 250                  OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN
 251         UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
 252                         collection_id = NEW.collection_id, dav_name = NEW.dav_name
 253             WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id;
 254       END IF;
 255       RETURN NEW;
 256     END IF;
 257
 258     UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no,
 259                     collection_id = NEW.collection_id, dav_name = NEW.dav_name
 260           WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id;
 261
 262     RETURN NEW;
 263
 264   END
 265 $$ LANGUAGE 'plpgsql';
 266 CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data
 267     FOR EACH ROW EXECUTE PROCEDURE sync_dav_id();
 268
 269
 270 -- Only needs SELECT access by website.
 271 CREATE TABLE principal_type (
 272   principal_type_id SERIAL PRIMARY KEY,
 273   principal_type_desc TEXT
 274 );
 275
 276
 277 -- web needs SELECT,INSERT,UPDATE,DELETE
 278 CREATE TABLE principal (
 279   principal_id INT8 DEFAULT nextval('dav_id_seq') PRIMARY KEY,
 280   type_id INT8 NOT NULL REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE,
 281   user_no INT8 NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 282   displayname TEXT,
 283   default_privileges BIT(24)
 284 );
 285
 286 ALTER TABLE principal ADD CONSTRAINT unique_user UNIQUE (user_no);
 287
 288
 289 -- Allowing identification of group members.
 290 CREATE TABLE group_member (
 291   group_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 292   member_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
 293 );
 294 CREATE UNIQUE INDEX group_member_pk ON group_member(group_id,member_id);
 295 CREATE INDEX group_member_sk ON group_member(member_id);
 296
 297
 298 CREATE TABLE grants (
 299   by_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 300   by_collection INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 301   to_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
 302   privileges BIT(24),
 303   is_group BOOLEAN
 304 ) WITHOUT OIDS;
 305 CREATE UNIQUE INDEX grants_pk1 ON grants(by_principal,to_principal);
 306 CREATE UNIQUE INDEX grants_pk2 ON grants(by_collection,to_principal);
 307
 308
 309 CREATE TABLE sync_tokens (
 310   sync_token SERIAL PRIMARY KEY,
 311   collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE,
 312   modification_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
 313 );
 314
 315 CREATE TABLE sync_changes (
 316   sync_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
 317   collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE,
 318   sync_status INT,
 319   dav_id INT8, -- can't REFERENCES calendar_item(dav_id) ON DELETE SET NULL ON UPDATE RESTRICT
 320   dav_name TEXT
 321 );
 322 CREATE INDEX sync_processing_index ON sync_changes( collection_id, dav_id, sync_time );
 323
 324 -- Revision 1.2.7 endeth here.
 325
 326 CREATE TABLE access_ticket (
 327   ticket_id TEXT PRIMARY KEY,
 328   dav_owner_id INT8 NOT NULL REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE,
 329   privileges BIT(24),
 330   target_collection_id INT8 NOT NULL REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE,
 331   target_resource_id INT8 REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 332   expires TIMESTAMP
 333 );
 334
 335
 336 -- At this point we only support binding collections
 337 CREATE TABLE dav_binding (
 338   bind_id INT8 DEFAULT nextval('dav_id_seq') PRIMARY KEY,
 339   bound_source_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE,
 340   access_ticket_id TEXT REFERENCES access_ticket(ticket_id) ON UPDATE CASCADE ON DELETE SET NULL,
 341   dav_owner_id INT8 NOT NULL REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE,
 342   parent_container TEXT NOT NULL,
 343   dav_name TEXT UNIQUE NOT NULL,
 344   dav_displayname TEXT,
 345   external_url TEXT,
 346   type TEXT
 347 );
 348
 349
 350 CREATE TABLE addressbook_resource (
 351   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE PRIMARY KEY,
 352   version TEXT,
 353   uid TEXT,
 354   nickname TEXT,
 355   fn TEXT, -- fullname
 356   n TEXT, -- Name Surname;First names
 357   note TEXT,
 358   org TEXT,
 359   url TEXT,
 360   fburl TEXT,
 361   caladruri TEXT,
 362   caluri TEXT
 363 );
 364
 365 CREATE TABLE addressbook_address_adr (
 366   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 367   type TEXT,
 368   box_no TEXT,
 369   unit_no TEXT,
 370   street_address TEXT,
 371   locality TEXT,
 372   region TEXT,
 373   postcode TEXT,
 374   country TEXT,
 375   property TEXT -- The full text of the property
 376 );
 377
 378 CREATE TABLE addressbook_address_tel (
 379   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 380   type TEXT,
 381   tel TEXT,
 382   property TEXT -- The full text of the property
 383 );
 384
 385 CREATE TABLE addressbook_address_email (
 386   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 387   type TEXT,
 388   email TEXT,
 389   property TEXT -- The full text of the property
 390 );
 391
 392
 393 CREATE TABLE calendar_alarm (
 394   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 395   action TEXT,
 396   trigger TEXT,
 397   summary TEXT,
 398   description TEXT,
 399   next_trigger TIMESTAMP WITH TIME ZONE,
 400   component TEXT, -- The full text of the component
 401   trigger_state CHAR DEFAULT 'N' -- 'N' => 'New/Needs setting', 'A' = 'Active', 'O' = 'Old'
 402 );
 403
 404 CREATE TABLE calendar_attendee (
 405   dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE,
 406   status TEXT,
 407   partstat TEXT,
 408   cn TEXT,
 409   attendee TEXT,
 410   role TEXT,
 411   rsvp BOOLEAN,
 412   property TEXT, -- The full text of the property
 413   attendee_state TEXT, -- Internal DAViCal processing state
 414   weak_etag TEXT, -- The week_etag applying for this attendee state
 415   PRIMARY KEY ( dav_id, attendee )
 416 );
 417
 418
 419 CREATE or REPLACE FUNCTION real_path_exists( TEXT ) RETURNS BOOLEAN AS $$
 420 DECLARE
 421   in_path ALIAS FOR $1;
 422   tmp BOOLEAN;
 423 BEGIN
 424   IF in_path = '/' THEN
 425     RETURN TRUE;
 426   END IF;
 427   IF in_path ~ '^/[^/]+/$' THEN
 428     SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
 429     IF FOUND THEN
 430       RETURN TRUE;
 431     END IF;
 432   ELSE
 433     IF in_path ~ '^/.*/$' THEN
 434       SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
 435       IF FOUND THEN
 436         RETURN TRUE;
 437       END IF;
 438     END IF;
 439   END IF;
 440   RETURN FALSE;
 441 END;
 442 $$ LANGUAGE plpgsql ;
 443
 444 ALTER TABLE dav_binding ADD CONSTRAINT "dav_name_does_not_exist"
 445                 CHECK (NOT real_path_exists(dav_name));
 446
 447 SELECT new_db_revision(1,2,11, 'Novembre' );