[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 | |
---|
| 7 | BEGIN; |
---|
| 8 | SELECT check_db_revision(1,1,11); |
---|
| 9 | |
---|
| 10 | -- Add a column to the collection table to allow us to mark collections |
---|
| 11 | -- as publicly readable |
---|
| 12 | ALTER TABLE collection ADD COLUMN publicly_readable BOOLEAN DEFAULT FALSE; |
---|
| 13 | |
---|
| 14 | -- Add a numeric dav_id to link the caldav_data and calendar_item tables |
---|
| 15 | ALTER TABLE caldav_data ADD COLUMN dav_id INT8; |
---|
| 16 | ALTER TABLE calendar_item ADD COLUMN dav_id INT8; |
---|
| 17 | CREATE SEQUENCE caldav_data_dav_id_seq; |
---|
| 18 | GRANT SELECT,UPDATE ON caldav_data_dav_id_seq TO general; |
---|
| 19 | |
---|
| 20 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS ' |
---|
| 21 | DECLARE |
---|
| 22 | BEGIN |
---|
| 23 | |
---|
| 24 | IF TG_OP = ''DELETE'' THEN |
---|
| 25 | -- Just let the ON DELETE CASCADE handle this case |
---|
| 26 | RETURN OLD; |
---|
| 27 | END IF; |
---|
| 28 | |
---|
| 29 | IF NEW.dav_id IS NULL THEN |
---|
| 30 | NEW.dav_id = nextval(''caldav_data_dav_id_seq''); |
---|
| 31 | END IF; |
---|
| 32 | |
---|
| 33 | IF TG_OP = ''UPDATE'' THEN |
---|
| 34 | IF OLD.dav_id = NEW.dav_id THEN |
---|
| 35 | -- Nothing to do |
---|
| 36 | RETURN NEW; |
---|
| 37 | END IF; |
---|
| 38 | END IF; |
---|
| 39 | |
---|
| 40 | IF TG_RELNAME = ''caldav_data'' THEN |
---|
| 41 | UPDATE calendar_item SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
| 42 | ELSE |
---|
| 43 | UPDATE caldav_data SET dav_id = NEW.dav_id WHERE user_no = NEW.user_no AND dav_name = NEW.dav_name; |
---|
| 44 | END IF; |
---|
| 45 | |
---|
| 46 | RETURN NEW; |
---|
| 47 | |
---|
| 48 | END |
---|
| 49 | ' LANGUAGE 'plpgsql'; |
---|
| 50 | |
---|
| 51 | CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
| 52 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
| 53 | |
---|
| 54 | CREATE TRIGGER calendar_item_sync_dav_id AFTER INSERT OR UPDATE ON calendar_item |
---|
| 55 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
| 56 | |
---|
| 57 | -- Now, using the trigger, magically assign dav_id to all rows in caldav_data and calendar_item |
---|
| 58 | UPDATE caldav_data SET dav_id = dav_id; |
---|
| 59 | |
---|
| 60 | ALTER TABLE caldav_data ALTER COLUMN dav_id SET DEFAULT nextval('caldav_data_dav_id_seq'); |
---|
| 61 | ALTER TABLE caldav_data ALTER COLUMN dav_id SET NOT NULL; |
---|
| 62 | ALTER TABLE caldav_data ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id); |
---|
| 63 | |
---|
| 64 | ALTER TABLE calendar_item ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id); |
---|
| 65 | |
---|
| 66 | SELECT new_db_revision(1,1,12, 'December' ); |
---|
| 67 | |
---|
| 68 | COMMIT; |
---|
| 69 | ROLLBACK; |
---|
| 70 | |
---|