2 -- Minor fix: Ensure the target of a binding is unique
3 -- Minor enhancement: Add columns for earliest start / latest end for improved pre-selection
4 -- Add columns to support remote binds
6 -- Just in case these constraints got added manually, so we won't fail
7 -- if there is an existing one.
8 ALTER TABLE principal DROP CONSTRAINT unique_user CASCADE;
9 ALTER TABLE collection DROP CONSTRAINT unique_path CASCADE;
11 CREATE or REPLACE FUNCTION real_path_exists( TEXT ) RETURNS BOOLEAN AS $$
19 IF in_path ~ '^/[^/]+/$' THEN
20 SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2);
25 IF in_path ~ '^/.*/$' THEN
26 SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path;
37 SELECT check_db_revision(1,2,9);
39 ALTER TABLE dav_binding ADD UNIQUE( dav_name );
41 -- New fields for Rob Ostensen's remote binding setup
42 ALTER TABLE dav_binding ADD COLUMN external_url TEXT;
43 ALTER TABLE dav_binding ADD COLUMN type TEXT;
45 ALTER TABLE principal ADD CONSTRAINT unique_user UNIQUE (user_no);
47 -- Ensure we don't refer to any newer, duplicated collections
48 UPDATE caldav_data SET collection_id = (SELECT min(c2.collection_id) FROM collection c1, collection c2
49 WHERE c1.dav_name = c2.dav_name AND c1.collection_id = caldav_data.collection_id)
50 WHERE collection_id > (SELECT min(c2.collection_id) FROM collection c1, collection c2
51 WHERE c1.dav_name = c2.dav_name AND c1.collection_id = caldav_data.collection_id);
52 -- Ensure the newer duplicated collections don't exist any longer
53 DELETE FROM collection WHERE collection_id > (SELECT min(collection_id) FROM collection c2 WHERE c2.dav_name = collection.dav_name);
54 -- Ensure we can't add more duplicates in the future
55 ALTER TABLE collection ADD CONSTRAINT unique_path UNIQUE (dav_name);
57 ALTER TABLE dav_binding ADD CONSTRAINT "dav_name_does_not_exist"
58 CHECK (NOT real_path_exists(dav_name));
60 -- We will use these to improve our selection criteria in future, but for now we will leave them null
61 ALTER TABLE calendar_item ADD COLUMN first_instance_start TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL;
62 ALTER TABLE calendar_item ADD COLUMN last_instance_end TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL;
64 SELECT new_db_revision(1,2,10, 'Octobre' );