1 | |
---|
2 | -- This database update provides new tables for the Principal, for |
---|
3 | -- a consistent dav_resource which a principal, collection or calendar_item |
---|
4 | -- all inherit from. |
---|
5 | |
---|
6 | BEGIN; |
---|
7 | SELECT check_db_revision(1,1,12); |
---|
8 | |
---|
9 | -- Rename the caldav_data_dav_id_seq to dav_id_seq because we will use it |
---|
10 | -- for more tables than just caldav_data |
---|
11 | CREATE SEQUENCE dav_id_seq; |
---|
12 | SELECT setval('dav_id_seq', nextval('caldav_data_dav_id_seq')); |
---|
13 | ALTER TABLE caldav_data ALTER COLUMN dav_id SET DEFAULT nextval('dav_id_seq'); |
---|
14 | ALTER TABLE calendar_item ALTER COLUMN dav_id SET DEFAULT nextval('dav_id_seq'); |
---|
15 | |
---|
16 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS ' |
---|
17 | DECLARE |
---|
18 | BEGIN |
---|
19 | |
---|
20 | IF TG_OP = ''DELETE'' THEN |
---|
21 | -- Just let the ON DELETE CASCADE handle this case |
---|
22 | RETURN OLD; |
---|
23 | END IF; |
---|
24 | |
---|
25 | IF NEW.dav_id IS NULL THEN |
---|
26 | NEW.dav_id = nextval(''dav_id_seq''); |
---|
27 | END IF; |
---|
28 | |
---|
29 | IF TG_OP = ''UPDATE'' THEN |
---|
30 | IF OLD.dav_id = NEW.dav_id THEN |
---|
31 | -- Nothing to do |
---|
32 | RETURN NEW; |
---|
33 | END IF; |
---|
34 | END IF; |
---|
35 | |
---|
36 | IF TG_RELNAME = ''caldav_data'' THEN |
---|
37 | UPDATE calendar_item SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
38 | ELSE |
---|
39 | UPDATE caldav_data SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
40 | END IF; |
---|
41 | |
---|
42 | RETURN NEW; |
---|
43 | |
---|
44 | END |
---|
45 | ' LANGUAGE 'plpgsql'; |
---|
46 | |
---|
47 | -- CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
48 | -- FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
49 | |
---|
50 | -- CREATE TRIGGER calendar_item_sync_dav_id AFTER INSERT OR UPDATE ON calendar_item |
---|
51 | -- FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
52 | |
---|
53 | |
---|
54 | -- Add a numeric collection_id to collection |
---|
55 | ALTER TABLE collection ADD COLUMN collection_id INT8; |
---|
56 | UPDATE collection SET collection_id = nextval('dav_id_seq'); |
---|
57 | ALTER TABLE collection ALTER COLUMN collection_id SET DEFAULT nextval('dav_id_seq'); |
---|
58 | ALTER TABLE collection DROP CONSTRAINT collection_pkey CASCADE; |
---|
59 | ALTER TABLE collection ADD UNIQUE (user_no,dav_name); |
---|
60 | ALTER TABLE collection ADD CONSTRAINT collection_pkey PRIMARY KEY (collection_id); |
---|
61 | |
---|
62 | ALTER TABLE calendar_item ADD COLUMN collection_id INT8; |
---|
63 | INSERT INTO collection ( user_no, parent_container, dav_name, dav_etag, dav_displayname, is_calendar, created, modified) |
---|
64 | SELECT user_no, '/'||username||'/', '/'||username||'/home/', md5(user_no::text||'/'||username||'/home/'), |
---|
65 | fullname, TRUE, current_timestamp, current_timestamp |
---|
66 | FROM usr |
---|
67 | WHERE NOT EXISTS (SELECT 1 FROM collection WHERE dav_name ~ ('^/'||username||'/')); |
---|
68 | |
---|
69 | UPDATE caldav_data SET dav_name = (select collection.dav_name FROM collection WHERE collection.user_no = caldav_data.user_no limit 1) |
---|
70 | || regexp_replace( caldav_data.dav_name, '^.*/([^/]+)$', E'ex-\\1') |
---|
71 | WHERE dav_name ~ '^/[^/]+/[^/]+$'; |
---|
72 | UPDATE calendar_item SET collection_id = collection.collection_id |
---|
73 | FROM collection WHERE collection.dav_name = regexp_replace( calendar_item.dav_name, '/[^/]+$', '/'); |
---|
74 | ALTER TABLE calendar_item ALTER COLUMN collection_id SET NOT NULL; |
---|
75 | ALTER TABLE calendar_item ADD CONSTRAINT |
---|
76 | calendar_item_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; |
---|
77 | CREATE INDEX calendar_item_collection_id_fkey ON calendar_item(collection_id); |
---|
78 | |
---|
79 | ALTER TABLE caldav_data ADD COLUMN collection_id INT8; |
---|
80 | UPDATE caldav_data SET collection_id = collection.collection_id |
---|
81 | FROM collection WHERE collection.dav_name = regexp_replace( caldav_data.dav_name, '/[^/]+$', '/'); |
---|
82 | ALTER TABLE caldav_data ALTER COLUMN collection_id SET NOT NULL; |
---|
83 | ALTER TABLE caldav_data ADD CONSTRAINT |
---|
84 | caldav_data_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; |
---|
85 | CREATE INDEX caldav_data_collection_id_fkey ON caldav_data(collection_id); |
---|
86 | |
---|
87 | SELECT new_db_revision(1,2,1, 'Janvier' ); |
---|
88 | |
---|
89 | COMMIT; |
---|
90 | ROLLBACK; |
---|
91 | |
---|