1 | |
---|
2 | -- Add a numeric foreign key link between caldav_data and calendar_item to |
---|
3 | -- provide more efficient linking when the db has been initialised with a |
---|
4 | -- non POSIX collation. |
---|
5 | |
---|
6 | -- This alternative patch file is the same in/out revision as 1.1.12 but it works where |
---|
7 | -- for some reason some caldav_data rows do not have related calendar_item rows. |
---|
8 | |
---|
9 | BEGIN; |
---|
10 | SELECT check_db_revision(1,1,11); |
---|
11 | |
---|
12 | -- Add a column to the collection table to allow us to mark collections |
---|
13 | -- as publicly readable |
---|
14 | ALTER TABLE collection ADD COLUMN publicly_readable BOOLEAN DEFAULT FALSE; |
---|
15 | |
---|
16 | -- Add a numeric dav_id to link the caldav_data and calendar_item tables |
---|
17 | ALTER TABLE caldav_data ADD COLUMN dav_id INT8; |
---|
18 | ALTER TABLE calendar_item ADD COLUMN dav_id INT8; |
---|
19 | CREATE SEQUENCE caldav_data_dav_id_seq; |
---|
20 | GRANT SELECT,UPDATE ON caldav_data_dav_id_seq TO general; |
---|
21 | |
---|
22 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS ' |
---|
23 | DECLARE |
---|
24 | BEGIN |
---|
25 | |
---|
26 | IF TG_OP = ''DELETE'' THEN |
---|
27 | -- Just let the ON DELETE CASCADE handle this case |
---|
28 | RETURN OLD; |
---|
29 | END IF; |
---|
30 | |
---|
31 | IF NEW.dav_id IS NULL THEN |
---|
32 | NEW.dav_id = nextval(''caldav_data_dav_id_seq''); |
---|
33 | END IF; |
---|
34 | |
---|
35 | IF TG_OP = ''UPDATE'' THEN |
---|
36 | IF OLD.dav_id = NEW.dav_id THEN |
---|
37 | -- Nothing to do |
---|
38 | RETURN NEW; |
---|
39 | END IF; |
---|
40 | END IF; |
---|
41 | |
---|
42 | IF TG_RELNAME = ''caldav_data'' THEN |
---|
43 | UPDATE calendar_item SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
44 | ELSE |
---|
45 | UPDATE caldav_data SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
46 | END IF; |
---|
47 | |
---|
48 | RETURN NEW; |
---|
49 | |
---|
50 | END |
---|
51 | ' LANGUAGE 'plpgsql'; |
---|
52 | |
---|
53 | CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
54 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
55 | |
---|
56 | CREATE TRIGGER calendar_item_sync_dav_id AFTER INSERT OR UPDATE ON calendar_item |
---|
57 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
58 | |
---|
59 | -- Now, using the trigger, magically assign dav_id to all rows in caldav_data and calendar_item |
---|
60 | UPDATE caldav_data SET dav_id = dav_id; |
---|
61 | UPDATE caldav_data set dav_id = nextval('caldav_data_dav_id_seq') WHERE dav_id IS NULL; |
---|
62 | UPDATE calendar_item set dav_id = nextval('caldav_data_dav_id_seq') WHERE dav_id IS NULL; |
---|
63 | |
---|
64 | ALTER TABLE caldav_data ALTER COLUMN dav_id SET DEFAULT nextval('caldav_data_dav_id_seq'); |
---|
65 | ALTER TABLE caldav_data ALTER COLUMN dav_id SET NOT NULL; |
---|
66 | ALTER TABLE caldav_data ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id); |
---|
67 | |
---|
68 | ALTER TABLE calendar_item ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id); |
---|
69 | |
---|
70 | SELECT new_db_revision(1,1,12, 'December' ); |
---|
71 | |
---|
72 | COMMIT; |
---|
73 | ROLLBACK; |
---|
74 | |
---|