1 | |
---|
2 | -- This database update adds support for the draft webdav-sync specification |
---|
3 | -- as well as some initial support for addressbook collections which will |
---|
4 | -- be needed to support carddav. |
---|
5 | |
---|
6 | BEGIN; |
---|
7 | SELECT check_db_revision(1,2,6); |
---|
8 | |
---|
9 | CREATE TABLE sync_tokens ( |
---|
10 | sync_token SERIAL PRIMARY KEY, |
---|
11 | collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE, |
---|
12 | modification_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp |
---|
13 | ); |
---|
14 | |
---|
15 | CREATE TABLE sync_changes ( |
---|
16 | sync_time TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp, |
---|
17 | collection_id INT8 REFERENCES collection(collection_id) ON DELETE CASCADE ON UPDATE CASCADE, |
---|
18 | sync_status INT, |
---|
19 | dav_id INT8, -- can't REFERENCES calendar_item(dav_id) ON DELETE SET NULL ON UPDATE RESTRICT |
---|
20 | dav_name TEXT |
---|
21 | ); |
---|
22 | CREATE INDEX sync_processing_index ON sync_changes( collection_id, dav_id, sync_time ); |
---|
23 | |
---|
24 | ALTER TABLE collection ADD COLUMN is_addressbook BOOLEAN DEFAULT FALSE; |
---|
25 | ALTER TABLE collection ADD COLUMN resourcetypes TEXT DEFAULT '<DAV::collection/>'; |
---|
26 | ALTER TABLE collection ADD COLUMN in_freebusy_set BOOLEAN DEFAULT TRUE; |
---|
27 | ALTER TABLE collection ADD COLUMN schedule_transp TEXT DEFAULT 'opaque'; |
---|
28 | ALTER TABLE collection ADD COLUMN timezone TEXT REFERENCES time_zone(tz_id) ON DELETE SET NULL ON UPDATE CASCADE; |
---|
29 | ALTER TABLE collection ADD COLUMN description TEXT DEFAULT ''; |
---|
30 | |
---|
31 | UPDATE collection SET resourcetypes = '<DAV::collection/><urn:ietf:params:xml:ns:caldav:calendar/>' WHERE is_calendar; |
---|
32 | |
---|
33 | SELECT new_db_revision(1,2,7, 'Juillet' ); |
---|
34 | |
---|
35 | COMMIT; |
---|
36 | ROLLBACK; |
---|
37 | |
---|
38 | |
---|
39 | CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
40 | DECLARE |
---|
41 | in_collection_id ALIAS FOR $1; |
---|
42 | in_status ALIAS FOR $2; |
---|
43 | in_dav_name ALIAS FOR $3; |
---|
44 | tmp_int INT8; |
---|
45 | BEGIN |
---|
46 | SELECT 1 INTO tmp_int FROM sync_tokens |
---|
47 | WHERE collection_id = in_collection_id |
---|
48 | LIMIT 1; |
---|
49 | IF NOT FOUND THEN |
---|
50 | RETURN FALSE; |
---|
51 | END IF; |
---|
52 | SELECT dav_id INTO tmp_int FROM calendar_item WHERE dav_name = in_dav_name; |
---|
53 | INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name) |
---|
54 | VALUES( in_collection_id, in_status, tmp_int, in_dav_name); |
---|
55 | RETURN TRUE; |
---|
56 | END |
---|
57 | $$ LANGUAGE 'PlPgSQL' VOLATILE STRICT; |
---|
58 | |
---|
59 | |
---|
60 | CREATE or REPLACE FUNCTION new_sync_token( INT8, INT8 ) RETURNS INT8 AS $$ |
---|
61 | DECLARE |
---|
62 | in_old_sync_token ALIAS FOR $1; |
---|
63 | in_collection_id ALIAS FOR $2; |
---|
64 | tmp_int INT8; |
---|
65 | BEGIN |
---|
66 | IF in_old_sync_token > 0 THEN |
---|
67 | SELECT 1 INTO tmp_int FROM sync_changes |
---|
68 | WHERE collection_id = in_collection_id |
---|
69 | AND sync_time > (SELECT modification_time FROM sync_tokens WHERE sync_token = in_old_sync_token) |
---|
70 | LIMIT 1; |
---|
71 | IF NOT FOUND THEN |
---|
72 | RETURN in_old_sync_token; |
---|
73 | END IF; |
---|
74 | END IF; |
---|
75 | SELECT nextval('sync_tokens_sync_token_seq') INTO tmp_int; |
---|
76 | INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, tmp_int ); |
---|
77 | RETURN tmp_int; |
---|
78 | END |
---|
79 | $$ LANGUAGE 'PlPgSQL' STRICT; |
---|