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 | -- Not particularly needed, perhaps, except as a way to collect |
---|
73 | -- a bunch of valid iCalendar time zone specifications... :-) |
---|
74 | CREATE TABLE time_zone ( |
---|
75 | tz_id TEXT PRIMARY KEY, |
---|
76 | tz_locn TEXT, |
---|
77 | tz_spec TEXT |
---|
78 | ); |
---|
79 | |
---|
80 | |
---|
81 | -- Something that can look like a filesystem hierarchy where we store stuff |
---|
82 | CREATE TABLE collection ( |
---|
83 | user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
84 | parent_container TEXT, |
---|
85 | dav_name TEXT, |
---|
86 | dav_etag TEXT, |
---|
87 | dav_displayname TEXT, |
---|
88 | is_calendar BOOLEAN, |
---|
89 | created TIMESTAMP WITH TIME ZONE, |
---|
90 | modified TIMESTAMP WITH TIME ZONE, |
---|
91 | public_events_only BOOLEAN NOT NULL DEFAULT FALSE, |
---|
92 | publicly_readable BOOLEAN NOT NULL DEFAULT FALSE, |
---|
93 | collection_id INT8 PRIMARY KEY DEFAULT nextval('dav_id_seq'), |
---|
94 | default_privileges BIT(24), |
---|
95 | is_addressbook BOOLEAN DEFAULT FALSE, |
---|
96 | resourcetypes TEXT DEFAULT '<DAV::collection/>', |
---|
97 | schedule_transp TEXT DEFAULT 'opaque', |
---|
98 | timezone TEXT REFERENCES time_zone(tz_id) ON DELETE SET NULL ON UPDATE CASCADE, |
---|
99 | description TEXT DEFAULT '', |
---|
100 | UNIQUE(user_no,dav_name) |
---|
101 | ); |
---|
102 | |
---|
103 | |
---|
104 | -- The main event. Where we store the things the calendar throws at us. |
---|
105 | CREATE TABLE caldav_data ( |
---|
106 | user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
107 | dav_name TEXT, |
---|
108 | dav_etag TEXT, |
---|
109 | created TIMESTAMP WITH TIME ZONE, |
---|
110 | modified TIMESTAMP WITH TIME ZONE, |
---|
111 | caldav_data TEXT, |
---|
112 | caldav_type TEXT, |
---|
113 | logged_user INT references usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE, |
---|
114 | dav_id INT8 UNIQUE DEFAULT nextval('dav_id_seq'), |
---|
115 | collection_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
116 | weak_etag TEXT DEFAULT NULL, |
---|
117 | |
---|
118 | PRIMARY KEY ( user_no, dav_name ) |
---|
119 | ); |
---|
120 | CREATE INDEX caldav_data_collection_id_fkey ON caldav_data(collection_id); |
---|
121 | |
---|
122 | -- The parsed calendar item. Here we have pulled those events/todos/journals apart somewhat. |
---|
123 | CREATE TABLE calendar_item ( |
---|
124 | user_no INT references usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
125 | dav_name TEXT, |
---|
126 | dav_etag TEXT, |
---|
127 | |
---|
128 | -- Extracted vEvent/vTodo data |
---|
129 | uid TEXT, |
---|
130 | created TIMESTAMP, |
---|
131 | last_modified TIMESTAMP, |
---|
132 | dtstamp TIMESTAMP, |
---|
133 | dtstart TIMESTAMP WITH TIME ZONE, |
---|
134 | dtend TIMESTAMP WITH TIME ZONE, |
---|
135 | due TIMESTAMP WITH TIME ZONE, |
---|
136 | summary TEXT, |
---|
137 | location TEXT, |
---|
138 | description TEXT, |
---|
139 | priority INT, |
---|
140 | class TEXT, |
---|
141 | transp TEXT, |
---|
142 | rrule TEXT, |
---|
143 | url TEXT, |
---|
144 | percent_complete NUMERIC(7,2), |
---|
145 | tz_id TEXT REFERENCES time_zone( tz_id ), |
---|
146 | status TEXT, |
---|
147 | completed TIMESTAMP WITH TIME ZONE, |
---|
148 | dav_id INT8 UNIQUE, |
---|
149 | collection_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
150 | |
---|
151 | -- Cascade updates / deletes from the caldav_data table |
---|
152 | CONSTRAINT caldav_exists FOREIGN KEY ( user_no, dav_name ) |
---|
153 | REFERENCES caldav_data ( user_no, dav_name ) |
---|
154 | MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE, |
---|
155 | |
---|
156 | PRIMARY KEY ( user_no, dav_name ) |
---|
157 | ); |
---|
158 | CREATE INDEX calendar_item_collection_id_fkey ON calendar_item(collection_id); |
---|
159 | |
---|
160 | |
---|
161 | |
---|
162 | -- Each user can be related to each other user. This mechanism can also |
---|
163 | -- be used to define groups of users, since some relationships are transitive. |
---|
164 | CREATE TABLE relationship_type ( |
---|
165 | rt_id SERIAL PRIMARY KEY, |
---|
166 | rt_name TEXT, |
---|
167 | rt_togroup BOOLEAN, |
---|
168 | confers TEXT DEFAULT 'RW', |
---|
169 | rt_fromgroup BOOLEAN, |
---|
170 | bit_confers BIT(24) DEFAULT privilege_to_bits(ARRAY['DAV::read','DAV::write']) |
---|
171 | ); |
---|
172 | |
---|
173 | |
---|
174 | CREATE TABLE relationship ( |
---|
175 | from_user INT REFERENCES usr (user_no) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
176 | to_user INT REFERENCES usr (user_no) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
177 | rt_id INT REFERENCES relationship_type (rt_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
178 | confers BIT(24) DEFAULT privilege_to_bits(ARRAY['DAV::read','DAV::write']), |
---|
179 | |
---|
180 | PRIMARY KEY ( from_user, to_user, rt_id ) |
---|
181 | ); |
---|
182 | |
---|
183 | |
---|
184 | CREATE TABLE locks ( |
---|
185 | dav_name TEXT, |
---|
186 | opaquelocktoken TEXT UNIQUE NOT NULL, |
---|
187 | type TEXT, |
---|
188 | scope TEXT, |
---|
189 | depth INT, |
---|
190 | owner TEXT, |
---|
191 | timeout INTERVAL, |
---|
192 | start TIMESTAMP DEFAULT current_timestamp |
---|
193 | ); |
---|
194 | CREATE INDEX locks_dav_name_idx ON locks(dav_name); |
---|
195 | |
---|
196 | |
---|
197 | CREATE TABLE property ( |
---|
198 | dav_name TEXT, |
---|
199 | property_name TEXT, |
---|
200 | property_value TEXT, |
---|
201 | changed_on TIMESTAMP DEFAULT current_timestamp, |
---|
202 | changed_by INT REFERENCES usr ( user_no ) ON UPDATE CASCADE ON DELETE SET DEFAULT, |
---|
203 | PRIMARY KEY ( dav_name, property_name ) |
---|
204 | ); |
---|
205 | CREATE INDEX properties_dav_name_idx ON property(dav_name); |
---|
206 | |
---|
207 | |
---|
208 | CREATE TABLE freebusy_ticket ( |
---|
209 | ticket_id TEXT NOT NULL PRIMARY KEY, |
---|
210 | user_no integer NOT NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
211 | created timestamp with time zone DEFAULT current_timestamp NOT NULL |
---|
212 | ); |
---|
213 | |
---|
214 | |
---|
215 | |
---|
216 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$ |
---|
217 | DECLARE |
---|
218 | BEGIN |
---|
219 | |
---|
220 | IF TG_OP = 'DELETE' THEN |
---|
221 | -- Just let the ON DELETE CASCADE handle this case |
---|
222 | RETURN OLD; |
---|
223 | END IF; |
---|
224 | |
---|
225 | IF NEW.dav_id IS NULL THEN |
---|
226 | NEW.dav_id = nextval('dav_id_seq'); |
---|
227 | END IF; |
---|
228 | |
---|
229 | IF TG_OP = 'UPDATE' THEN |
---|
230 | IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id |
---|
231 | OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN |
---|
232 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
233 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
234 | WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id; |
---|
235 | END IF; |
---|
236 | RETURN NEW; |
---|
237 | END IF; |
---|
238 | |
---|
239 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
240 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
241 | WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id; |
---|
242 | |
---|
243 | RETURN NEW; |
---|
244 | |
---|
245 | END |
---|
246 | $$ LANGUAGE 'plpgsql'; |
---|
247 | CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
248 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
249 | |
---|
250 | |
---|
251 | -- Only needs SELECT access by website. |
---|
252 | CREATE TABLE principal_type ( |
---|
253 | principal_type_id SERIAL PRIMARY KEY, |
---|
254 | principal_type_desc TEXT |
---|
255 | ); |
---|
256 | |
---|
257 | |
---|
258 | -- web needs SELECT,INSERT,UPDATE,DELETE |
---|
259 | CREATE TABLE principal ( |
---|
260 | principal_id INT8 DEFAULT nextval('dav_id_seq') PRIMARY KEY, |
---|
261 | type_id INT8 NOT NULL REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE, |
---|
262 | user_no INT8 NULL REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
263 | displayname TEXT, |
---|
264 | default_privileges BIT(24) |
---|
265 | ); |
---|
266 | |
---|
267 | |
---|
268 | |
---|
269 | -- Allowing identification of group members. |
---|
270 | CREATE TABLE group_member ( |
---|
271 | group_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
272 | member_id INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE |
---|
273 | ); |
---|
274 | CREATE UNIQUE INDEX group_member_pk ON group_member(group_id,member_id); |
---|
275 | CREATE INDEX group_member_sk ON group_member(member_id); |
---|
276 | |
---|
277 | |
---|
278 | CREATE TABLE grants ( |
---|
279 | by_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
280 | by_collection INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
281 | to_principal INT8 REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, |
---|
282 | privileges BIT(24), |
---|
283 | is_group BOOLEAN |
---|
284 | ) WITHOUT OIDS; |
---|
285 | CREATE UNIQUE INDEX grants_pk1 ON grants(by_principal,to_principal); |
---|
286 | CREATE UNIQUE INDEX grants_pk2 ON grants(by_collection,to_principal); |
---|
287 | |
---|
288 | |
---|
289 | CREATE TABLE sync_tokens ( |
---|
290 | sync_token SERIAL PRIMARY KEY, |
---|
291 | collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE, |
---|
292 | modification_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp |
---|
293 | ); |
---|
294 | |
---|
295 | CREATE TABLE sync_changes ( |
---|
296 | sync_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, |
---|
297 | collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE, |
---|
298 | sync_status INT, |
---|
299 | dav_id INT8, -- can't REFERENCES calendar_item(dav_id) ON DELETE SET NULL ON UPDATE RESTRICT |
---|
300 | dav_name TEXT |
---|
301 | ); |
---|
302 | CREATE INDEX sync_processing_index ON sync_changes( collection_id, dav_id, sync_time ); |
---|
303 | |
---|
304 | -- Revision 1.2.7 endeth here. |
---|
305 | |
---|
306 | CREATE TABLE access_ticket ( |
---|
307 | ticket_id TEXT PRIMARY KEY, |
---|
308 | dav_owner_id INT8 NOT NULL REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
309 | privileges BIT(24), |
---|
310 | target_collection_id INT8 NOT NULL REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
311 | target_resource_id INT8 REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
312 | expires TIMESTAMP |
---|
313 | ); |
---|
314 | |
---|
315 | |
---|
316 | -- At this point we only support binding collections |
---|
317 | CREATE TABLE dav_binding ( |
---|
318 | bind_id INT8 DEFAULT nextval('dav_id_seq') PRIMARY KEY, |
---|
319 | bound_source_id INT8 REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
320 | access_ticket_id TEXT REFERENCES access_ticket(ticket_id) ON UPDATE CASCADE ON DELETE SET NULL, |
---|
321 | dav_owner_id INT8 NOT NULL REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
322 | parent_container TEXT NOT NULL, |
---|
323 | dav_name TEXT UNIQUE NOT NULL, |
---|
324 | dav_displayname TEXT |
---|
325 | ); |
---|
326 | |
---|
327 | |
---|
328 | CREATE TABLE addressbook_resource ( |
---|
329 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE PRIMARY KEY, |
---|
330 | version TEXT, |
---|
331 | uid TEXT, |
---|
332 | nickname TEXT, |
---|
333 | fn TEXT, -- fullname |
---|
334 | n TEXT, -- Name Surname;First names |
---|
335 | note TEXT, |
---|
336 | org TEXT, |
---|
337 | url TEXT, |
---|
338 | fburl TEXT, |
---|
339 | caladruri TEXT, |
---|
340 | caluri TEXT |
---|
341 | ); |
---|
342 | |
---|
343 | CREATE TABLE addressbook_address_adr ( |
---|
344 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
345 | type TEXT, |
---|
346 | box_no TEXT, |
---|
347 | unit_no TEXT, |
---|
348 | street_address TEXT, |
---|
349 | locality TEXT, |
---|
350 | region TEXT, |
---|
351 | postcode TEXT, |
---|
352 | country TEXT, |
---|
353 | property TEXT -- The full text of the property |
---|
354 | ); |
---|
355 | |
---|
356 | CREATE TABLE addressbook_address_tel ( |
---|
357 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
358 | type TEXT, |
---|
359 | tel TEXT, |
---|
360 | property TEXT -- The full text of the property |
---|
361 | ); |
---|
362 | |
---|
363 | CREATE TABLE addressbook_address_email ( |
---|
364 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
365 | type TEXT, |
---|
366 | email TEXT, |
---|
367 | property TEXT -- The full text of the property |
---|
368 | ); |
---|
369 | |
---|
370 | |
---|
371 | CREATE TABLE calendar_alarm ( |
---|
372 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
373 | action TEXT, |
---|
374 | trigger TEXT, |
---|
375 | summary TEXT, |
---|
376 | description TEXT, |
---|
377 | next_trigger TIMESTAMP WITH TIME ZONE, |
---|
378 | component TEXT, -- The full text of the component |
---|
379 | trigger_state CHAR DEFAULT 'N' -- 'N' => 'New/Needs setting', 'A' = 'Active', 'O' = 'Old' |
---|
380 | ); |
---|
381 | |
---|
382 | CREATE TABLE calendar_attendee ( |
---|
383 | dav_id INT8 NOT NULL REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE, |
---|
384 | status TEXT, |
---|
385 | partstat TEXT, |
---|
386 | cn TEXT, |
---|
387 | attendee TEXT, |
---|
388 | role TEXT, |
---|
389 | rsvp BOOLEAN, |
---|
390 | property TEXT, -- The full text of the property |
---|
391 | attendee_state TEXT, -- Internal DAViCal processing state |
---|
392 | weak_etag TEXT, -- The week_etag applying for this attendee state |
---|
393 | PRIMARY KEY ( dav_id, attendee ) |
---|
394 | ); |
---|
395 | |
---|
396 | SELECT new_db_revision(1,2,9, 'Septembre' ); |
---|