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