[3733] | 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; |
---|