[3733] | 1 | /** |
---|
| 2 | * PostgreSQL Functions for CalDAV handling |
---|
| 3 | * |
---|
| 4 | * @package rscds |
---|
| 5 | * @subpackage database |
---|
| 6 | * @author Andrew McMillan <andrew@catalyst.net.nz> |
---|
| 7 | * @copyright Catalyst IT Ltd |
---|
| 8 | * @license http://gnu.org/copyleft/gpl.html GNU GPL v2 |
---|
| 9 | */ |
---|
| 10 | |
---|
| 11 | CREATE or REPLACE FUNCTION apply_month_byday( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 12 | DECLARE |
---|
| 13 | in_time ALIAS FOR $1; |
---|
| 14 | byday ALIAS FOR $2; |
---|
| 15 | weeks INT; |
---|
| 16 | dow INT; |
---|
| 17 | temp_txt TEXT; |
---|
| 18 | dd INT; |
---|
| 19 | mm INT; |
---|
| 20 | yy INT; |
---|
| 21 | our_dow INT; |
---|
| 22 | our_answer TIMESTAMP WITH TIME ZONE; |
---|
| 23 | BEGIN |
---|
| 24 | dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2; |
---|
| 25 | temp_txt := substring(byday from '([0-9]+)'); |
---|
| 26 | weeks := temp_txt::int; |
---|
| 27 | |
---|
| 28 | -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt; |
---|
| 29 | |
---|
| 30 | IF substring(byday for 1) = '-' THEN |
---|
| 31 | -- Last XX of month, or possibly second-to-last, but unlikely |
---|
| 32 | mm := extract( 'month' from in_time); |
---|
| 33 | yy := extract( 'year' from in_time); |
---|
| 34 | |
---|
| 35 | -- Start with the last day of the month |
---|
| 36 | our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval; |
---|
| 37 | dd := extract( 'dow' from our_answer); |
---|
| 38 | dd := dd - dow; |
---|
| 39 | IF dd < 0 THEN |
---|
| 40 | dd := dd + 7; |
---|
| 41 | END IF; |
---|
| 42 | |
---|
| 43 | -- Having calculated the right day of the month, we now apply that back to in_time |
---|
| 44 | -- which contains the otherwise-unobtainable timezone detail (and the time) |
---|
| 45 | our_answer = our_answer - (dd::text || 'days')::interval; |
---|
| 46 | dd := extract( 'day' from our_answer) - extract( 'day' from in_time); |
---|
| 47 | our_answer := in_time + (dd::text || 'days')::interval; |
---|
| 48 | |
---|
| 49 | IF weeks > 1 THEN |
---|
| 50 | weeks := weeks - 1; |
---|
| 51 | our_answer := our_answer - (weeks::text || 'weeks')::interval; |
---|
| 52 | END IF; |
---|
| 53 | |
---|
| 54 | ELSE |
---|
| 55 | |
---|
| 56 | -- Shift our date to the correct day of week.. |
---|
| 57 | our_dow := extract( 'dow' from in_time); |
---|
| 58 | our_dow := our_dow - dow; |
---|
| 59 | dd := extract( 'day' from in_time); |
---|
| 60 | IF our_dow >= dd THEN |
---|
| 61 | our_dow := our_dow - 7; |
---|
| 62 | END IF; |
---|
| 63 | our_answer := in_time - (our_dow::text || 'days')::interval; |
---|
| 64 | dd = extract( 'day' from our_answer); |
---|
| 65 | |
---|
| 66 | -- Shift the date to the correct week... |
---|
| 67 | dd := weeks - ((dd+6) / 7); |
---|
| 68 | IF dd != 0 THEN |
---|
| 69 | our_answer := our_answer + ((dd::text || 'weeks')::interval); |
---|
| 70 | END IF; |
---|
| 71 | |
---|
| 72 | END IF; |
---|
| 73 | |
---|
| 74 | RETURN our_answer; |
---|
| 75 | |
---|
| 76 | END; |
---|
| 77 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 78 | |
---|
| 79 | |
---|
| 80 | CREATE or REPLACE FUNCTION calculate_later_timestamp( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 81 | DECLARE |
---|
| 82 | earliest ALIAS FOR $1; |
---|
| 83 | basedate ALIAS FOR $2; |
---|
| 84 | repeatrule ALIAS FOR $3; |
---|
| 85 | frequency TEXT; |
---|
| 86 | temp_txt TEXT; |
---|
| 87 | length INT; |
---|
| 88 | count INT; |
---|
| 89 | byday TEXT; |
---|
| 90 | bymonthday INT; |
---|
| 91 | basediff INTERVAL; |
---|
| 92 | past_repeats INT8; |
---|
| 93 | units TEXT; |
---|
| 94 | dow TEXT; |
---|
| 95 | our_answer TIMESTAMP WITH TIME ZONE; |
---|
| 96 | loopcount INT; |
---|
| 97 | BEGIN |
---|
| 98 | IF basedate > earliest THEN |
---|
| 99 | RETURN basedate; |
---|
| 100 | END IF; |
---|
| 101 | |
---|
| 102 | temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); |
---|
| 103 | IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN |
---|
| 104 | RETURN NULL; |
---|
| 105 | END IF; |
---|
| 106 | |
---|
| 107 | frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); |
---|
| 108 | IF frequency IS NULL THEN |
---|
| 109 | RETURN NULL; |
---|
| 110 | END IF; |
---|
| 111 | |
---|
| 112 | past_repeats = 0; |
---|
| 113 | length = 1; |
---|
| 114 | temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)'); |
---|
| 115 | IF temp_txt IS NOT NULL THEN |
---|
| 116 | length := temp_txt::int; |
---|
| 117 | basediff := earliest - basedate; |
---|
| 118 | |
---|
| 119 | -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff; |
---|
| 120 | |
---|
| 121 | -- Calculate the number of past periods between our base date and our earliest date |
---|
| 122 | IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN |
---|
| 123 | past_repeats := extract('epoch' from basediff)::INT8 / 86400; |
---|
| 124 | -- RAISE NOTICE 'Days: %', past_repeats; |
---|
| 125 | IF frequency = 'WEEKLY' THEN |
---|
| 126 | past_repeats := past_repeats / 7; |
---|
| 127 | END IF; |
---|
| 128 | ELSE |
---|
| 129 | past_repeats = extract( 'years' from basediff ); |
---|
| 130 | IF frequency = 'MONTHLY' THEN |
---|
| 131 | past_repeats = (past_repeats *12) + extract( 'months' from basediff ); |
---|
| 132 | END IF; |
---|
| 133 | END IF; |
---|
| 134 | IF length IS NOT NULL THEN |
---|
| 135 | past_repeats = (past_repeats / length) + 1; |
---|
| 136 | END IF; |
---|
| 137 | END IF; |
---|
| 138 | |
---|
| 139 | -- Check that we have not exceeded the COUNT= limit |
---|
| 140 | temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); |
---|
| 141 | IF temp_txt IS NOT NULL THEN |
---|
| 142 | count := temp_txt::int; |
---|
| 143 | -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length; |
---|
| 144 | IF ( count <= past_repeats ) THEN |
---|
| 145 | RETURN NULL; |
---|
| 146 | END IF; |
---|
| 147 | ELSE |
---|
| 148 | count := NULL; |
---|
| 149 | END IF; |
---|
| 150 | |
---|
| 151 | temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)'); |
---|
| 152 | byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)'); |
---|
| 153 | IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN |
---|
| 154 | -- Since this could move the date around a month we go back one |
---|
| 155 | -- period just to be extra sure. |
---|
| 156 | past_repeats = past_repeats - 1; |
---|
| 157 | |
---|
| 158 | IF temp_txt IS NOT NULL THEN |
---|
| 159 | -- Crudely hack the BYSETPOS onto the front of BYDAY. While this |
---|
| 160 | -- is not as per rfc2445, RRULE syntax is so complex and overblown |
---|
| 161 | -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and |
---|
| 162 | -- certainly not within a MONTHLY RRULE. |
---|
| 163 | byday := temp_txt || byday; |
---|
| 164 | END IF; |
---|
| 165 | END IF; |
---|
| 166 | |
---|
| 167 | past_repeats = past_repeats * length; |
---|
| 168 | |
---|
| 169 | units := CASE |
---|
| 170 | WHEN frequency = 'DAILY' THEN 'days' |
---|
| 171 | WHEN frequency = 'WEEKLY' THEN 'weeks' |
---|
| 172 | WHEN frequency = 'MONTHLY' THEN 'months' |
---|
| 173 | WHEN frequency = 'YEARLY' THEN 'years' |
---|
| 174 | END; |
---|
| 175 | |
---|
| 176 | temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)'); |
---|
| 177 | bymonthday := temp_txt::int; |
---|
| 178 | |
---|
| 179 | -- With all of the above calculation, this date should be close to (but less than) |
---|
| 180 | -- the target, and we should only loop once or twice. |
---|
| 181 | our_answer := basedate + (past_repeats::text || units)::interval; |
---|
| 182 | |
---|
| 183 | IF our_answer IS NULL THEN |
---|
| 184 | RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units; |
---|
| 185 | END IF; |
---|
| 186 | |
---|
| 187 | |
---|
| 188 | loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle |
---|
| 189 | LOOP |
---|
| 190 | -- RAISE NOTICE 'Testing date: %', our_answer; |
---|
| 191 | IF frequency = 'DAILY' THEN |
---|
| 192 | IF byday IS NOT NULL THEN |
---|
| 193 | LOOP |
---|
| 194 | dow = substring( to_char( our_answer, 'DY' ) for 2); |
---|
| 195 | EXIT WHEN byday ~* dow; |
---|
| 196 | -- Increment for our next time through the loop... |
---|
| 197 | our_answer := our_answer + (length::text || units)::interval; |
---|
| 198 | END LOOP; |
---|
| 199 | END IF; |
---|
| 200 | ELSIF frequency = 'WEEKLY' THEN |
---|
| 201 | -- Weekly repeats are only on specific days |
---|
| 202 | -- This is really not right, since a WEEKLY on MO,WE,FR should |
---|
| 203 | -- occur three times each week and this will only be once a week. |
---|
| 204 | dow = substring( to_char( our_answer, 'DY' ) for 2); |
---|
| 205 | ELSIF frequency = 'MONTHLY' THEN |
---|
| 206 | IF byday IS NOT NULL THEN |
---|
| 207 | -- This works fine, except that maybe there are multiple BYDAY |
---|
| 208 | -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays. |
---|
| 209 | our_answer := apply_month_byday( our_answer, byday ); |
---|
| 210 | ELSE |
---|
| 211 | -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month |
---|
| 212 | our_answer := our_answer + '1 month'::interval; |
---|
| 213 | END IF; |
---|
| 214 | ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN |
---|
| 215 | -- We do not deal with this situation at present |
---|
| 216 | RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.'; |
---|
| 217 | END IF; |
---|
| 218 | |
---|
| 219 | EXIT WHEN our_answer >= earliest; |
---|
| 220 | |
---|
| 221 | -- Give up if we have exceeded the count |
---|
| 222 | IF ( count IS NOT NULL AND past_repeats > count ) THEN |
---|
| 223 | RETURN NULL; |
---|
| 224 | ELSE |
---|
| 225 | past_repeats := past_repeats + 1; |
---|
| 226 | END IF; |
---|
| 227 | |
---|
| 228 | loopcount := loopcount - 1; |
---|
| 229 | IF loopcount < 0 THEN |
---|
| 230 | RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest; |
---|
| 231 | RETURN NULL; |
---|
| 232 | END IF; |
---|
| 233 | |
---|
| 234 | -- Increment for our next time through the loop... |
---|
| 235 | our_answer := our_answer + (length::text || units)::interval; |
---|
| 236 | |
---|
| 237 | END LOOP; |
---|
| 238 | |
---|
| 239 | RETURN our_answer; |
---|
| 240 | |
---|
| 241 | END; |
---|
| 242 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 243 | |
---|
| 244 | |
---|
| 245 | CREATE or REPLACE FUNCTION usr_is_role( INT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
| 246 | SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 ) |
---|
| 247 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
| 248 | |
---|
| 249 | CREATE or REPLACE FUNCTION legacy_get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
| 250 | DECLARE |
---|
| 251 | in_from ALIAS FOR $1; |
---|
| 252 | in_to ALIAS FOR $2; |
---|
| 253 | out_confers TEXT; |
---|
| 254 | tmp_confers1 TEXT; |
---|
| 255 | tmp_confers2 TEXT; |
---|
| 256 | tmp_txt TEXT; |
---|
| 257 | dbg TEXT DEFAULT ''; |
---|
| 258 | r RECORD; |
---|
| 259 | counter INT; |
---|
| 260 | BEGIN |
---|
| 261 | -- Self can always have full access |
---|
| 262 | IF in_from = in_to THEN |
---|
| 263 | RETURN 'A'; |
---|
| 264 | END IF; |
---|
| 265 | |
---|
| 266 | -- dbg := 'S-'; |
---|
| 267 | SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id ) |
---|
| 268 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
| 269 | IF FOUND THEN |
---|
| 270 | RETURN dbg || out_confers; |
---|
| 271 | END IF; |
---|
| 272 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
| 273 | |
---|
| 274 | out_confers := ''; |
---|
| 275 | FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id) |
---|
| 276 | JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id |
---|
| 277 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
| 278 | AND EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.to_user AND roles.role_name='Group') |
---|
| 279 | AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r2.to_user AND roles.role_name='Group') |
---|
| 280 | AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.from_user AND roles.role_name='Group') |
---|
| 281 | LOOP |
---|
| 282 | -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2; |
---|
| 283 | -- FIXME: This is an oversimplification |
---|
| 284 | -- dbg := 'C-'; |
---|
| 285 | tmp_confers1 := r.r1; |
---|
| 286 | tmp_confers2 := r.r2; |
---|
| 287 | IF tmp_confers1 != tmp_confers2 THEN |
---|
| 288 | IF tmp_confers1 ~* 'A' THEN |
---|
| 289 | -- Ensure that A is expanded to all supported privs before being used as a mask |
---|
| 290 | tmp_confers1 := 'AFBRWU'; |
---|
| 291 | END IF; |
---|
| 292 | IF tmp_confers2 ~* 'A' THEN |
---|
| 293 | -- Ensure that A is expanded to all supported privs before being used as a mask |
---|
| 294 | tmp_confers2 := 'AFBRWU'; |
---|
| 295 | END IF; |
---|
| 296 | -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2; |
---|
| 297 | tmp_txt = ''; |
---|
| 298 | FOR counter IN 1 .. length(tmp_confers2) LOOP |
---|
| 299 | IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN |
---|
| 300 | tmp_txt := tmp_txt || substring(tmp_confers2,counter,1); |
---|
| 301 | END IF; |
---|
| 302 | END LOOP; |
---|
| 303 | tmp_confers2 := tmp_txt; |
---|
| 304 | END IF; |
---|
| 305 | FOR counter IN 1 .. length(tmp_confers2) LOOP |
---|
| 306 | IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN |
---|
| 307 | out_confers := out_confers || substring(tmp_confers2,counter,1); |
---|
| 308 | END IF; |
---|
| 309 | END LOOP; |
---|
| 310 | END LOOP; |
---|
| 311 | IF out_confers ~* 'A' OR (out_confers ~* 'B' AND out_confers ~* 'F' AND out_confers ~* 'R' AND out_confers ~* 'W' AND out_confers ~* 'U') THEN |
---|
| 312 | out_confers := 'A'; |
---|
| 313 | END IF; |
---|
| 314 | IF out_confers != '' THEN |
---|
| 315 | RETURN dbg || out_confers; |
---|
| 316 | END IF; |
---|
| 317 | |
---|
| 318 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
| 319 | |
---|
| 320 | SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id ) |
---|
| 321 | LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id ) |
---|
| 322 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user AND r1.to_user = r2.to_user |
---|
| 323 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) |
---|
| 324 | AND usr_is_role(r1.to_user,'Group'); |
---|
| 325 | |
---|
| 326 | IF FOUND THEN |
---|
| 327 | -- dbg := 'H-'; |
---|
| 328 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
| 329 | RETURN dbg || out_confers; |
---|
| 330 | END IF; |
---|
| 331 | |
---|
| 332 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
| 333 | |
---|
| 334 | RETURN ''; |
---|
| 335 | END; |
---|
| 336 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 337 | |
---|
| 338 | |
---|
| 339 | -- Function to convert a PostgreSQL date into UTC + the format used by iCalendar |
---|
| 340 | CREATE or REPLACE FUNCTION to_ical_utc( TIMESTAMP WITH TIME ZONE ) RETURNS TEXT AS $$ |
---|
| 341 | SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' ) |
---|
| 342 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
| 343 | |
---|
| 344 | -- Function to set an arbitrary DAV property |
---|
| 345 | CREATE or REPLACE FUNCTION set_dav_property( TEXT, INTEGER, TEXT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
| 346 | DECLARE |
---|
| 347 | path ALIAS FOR $1; |
---|
| 348 | user ALIAS FOR $2; |
---|
| 349 | key ALIAS FOR $3; |
---|
| 350 | value ALIAS FOR $4; |
---|
| 351 | tmp_int INT; |
---|
| 352 | BEGIN |
---|
| 353 | -- Check that there is either a resource, collection or user at this location. |
---|
| 354 | IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path |
---|
| 355 | UNION SELECT 1 FROM collection WHERE dav_name = path |
---|
| 356 | UNION SELECT 1 FROM dav_principal WHERE dav_name = path |
---|
| 357 | UNION SELECT 1 FROM dav_binding WHERE dav_name = path |
---|
| 358 | ) THEN |
---|
| 359 | RETURN FALSE; |
---|
| 360 | END IF; |
---|
| 361 | SELECT changed_by INTO tmp_int FROM property WHERE dav_name = path AND property_name = key; |
---|
| 362 | IF FOUND THEN |
---|
| 363 | UPDATE property SET changed_by=user, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key; |
---|
| 364 | ELSE |
---|
| 365 | INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, user, current_timestamp, key, value ); |
---|
| 366 | END IF; |
---|
| 367 | RETURN TRUE; |
---|
| 368 | END; |
---|
| 369 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
| 370 | |
---|
| 371 | -- List a user's relationships as a text string |
---|
| 372 | CREATE or REPLACE FUNCTION relationship_list( INT8 ) RETURNS TEXT AS $$ |
---|
| 373 | DECLARE |
---|
| 374 | user ALIAS FOR $1; |
---|
| 375 | r RECORD; |
---|
| 376 | rlist TEXT; |
---|
| 377 | BEGIN |
---|
| 378 | rlist := ''; |
---|
| 379 | FOR r IN SELECT rt_name, fullname FROM relationship |
---|
| 380 | LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no |
---|
| 381 | WHERE from_user = user |
---|
| 382 | LOOP |
---|
| 383 | rlist := rlist |
---|
| 384 | || CASE WHEN rlist = '' THEN '' ELSE ', ' END |
---|
| 385 | || r.rt_name || '(' || r.fullname || ')'; |
---|
| 386 | END LOOP; |
---|
| 387 | RETURN rlist; |
---|
| 388 | END; |
---|
| 389 | $$ LANGUAGE 'plpgsql'; |
---|
| 390 | |
---|
| 391 | DROP FUNCTION rename_davical_user( TEXT, TEXT ); |
---|
| 392 | DROP TRIGGER usr_modified ON usr CASCADE; |
---|
| 393 | CREATE or REPLACE FUNCTION usr_modified() RETURNS TRIGGER AS $$ |
---|
| 394 | DECLARE |
---|
| 395 | oldpath TEXT; |
---|
| 396 | newpath TEXT; |
---|
| 397 | BEGIN |
---|
| 398 | -- in case we trigger on other events in future |
---|
| 399 | IF TG_OP = 'UPDATE' THEN |
---|
| 400 | IF NEW.username != OLD.username THEN |
---|
| 401 | oldpath := '/' || OLD.username || '/'; |
---|
| 402 | newpath := '/' || NEW.username || '/'; |
---|
| 403 | UPDATE collection |
---|
| 404 | SET parent_container = replace( parent_container, oldpath, newpath), |
---|
| 405 | dav_name = replace( dav_name, oldpath, newpath) |
---|
| 406 | WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath; |
---|
| 407 | END IF; |
---|
| 408 | END IF; |
---|
| 409 | RETURN NEW; |
---|
| 410 | END; |
---|
| 411 | $$ LANGUAGE plpgsql; |
---|
| 412 | CREATE TRIGGER usr_modified AFTER UPDATE ON usr |
---|
| 413 | FOR EACH ROW EXECUTE PROCEDURE usr_modified(); |
---|
| 414 | |
---|
| 415 | |
---|
| 416 | DROP TRIGGER collection_modified ON collection CASCADE; |
---|
| 417 | CREATE or REPLACE FUNCTION collection_modified() RETURNS TRIGGER AS $$ |
---|
| 418 | DECLARE |
---|
| 419 | BEGIN |
---|
| 420 | -- in case we trigger on other events in future |
---|
| 421 | IF TG_OP = 'UPDATE' THEN |
---|
| 422 | IF NEW.dav_name != OLD.dav_name THEN |
---|
| 423 | UPDATE caldav_data |
---|
| 424 | SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name), |
---|
| 425 | user_no = NEW.user_no |
---|
| 426 | WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name; |
---|
| 427 | END IF; |
---|
| 428 | END IF; |
---|
| 429 | RETURN NEW; |
---|
| 430 | END; |
---|
| 431 | $$ LANGUAGE plpgsql; |
---|
| 432 | CREATE TRIGGER collection_modified AFTER UPDATE ON collection |
---|
| 433 | FOR EACH ROW EXECUTE PROCEDURE collection_modified(); |
---|
| 434 | |
---|
| 435 | |
---|
| 436 | DROP TRIGGER caldav_data_modified ON caldav_data CASCADE; |
---|
| 437 | CREATE or REPLACE FUNCTION caldav_data_modified() RETURNS TRIGGER AS $$ |
---|
| 438 | DECLARE |
---|
| 439 | coll_id caldav_data.collection_id%TYPE; |
---|
| 440 | BEGIN |
---|
| 441 | IF TG_OP = 'UPDATE' THEN |
---|
| 442 | IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN |
---|
| 443 | -- Nothing for us to do |
---|
| 444 | RETURN NEW; |
---|
| 445 | END IF; |
---|
| 446 | END IF; |
---|
| 447 | |
---|
| 448 | IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN |
---|
| 449 | -- On insert or update modified, we set the NEW collection tag to the md5 of the |
---|
| 450 | -- etag of the updated row which gives us something predictable for our regression |
---|
| 451 | -- tests, but something different from the actual etag of the new event. |
---|
| 452 | UPDATE collection |
---|
| 453 | SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag) |
---|
| 454 | WHERE collection_id = NEW.collection_id; |
---|
| 455 | IF TG_OP = 'INSERT' THEN |
---|
| 456 | RETURN NEW; |
---|
| 457 | END IF; |
---|
| 458 | END IF; |
---|
| 459 | |
---|
| 460 | IF TG_OP = 'DELETE' THEN |
---|
| 461 | -- On delete we set the OLD collection tag to the md5 of the old path & the old |
---|
| 462 | -- etag, which again gives us something predictable for our regression tests. |
---|
| 463 | UPDATE collection |
---|
| 464 | SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) |
---|
| 465 | WHERE collection_id = OLD.collection_id; |
---|
| 466 | RETURN OLD; |
---|
| 467 | END IF; |
---|
| 468 | |
---|
| 469 | IF NEW.collection_id != OLD.collection_id THEN |
---|
| 470 | -- If we've switched the collection_id of this event, then we also need to update |
---|
| 471 | -- the etag of the old collection - as we do for delete. |
---|
| 472 | UPDATE collection |
---|
| 473 | SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) |
---|
| 474 | WHERE collection_id = OLD.collection_id; |
---|
| 475 | END IF; |
---|
| 476 | RETURN NEW; |
---|
| 477 | END; |
---|
| 478 | $$ LANGUAGE plpgsql; |
---|
| 479 | CREATE TRIGGER caldav_data_modified AFTER INSERT OR UPDATE OR DELETE ON caldav_data |
---|
| 480 | FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified(); |
---|
| 481 | |
---|
| 482 | |
---|
| 483 | DROP TRIGGER caldav_data_sync_dav_id ON caldav_data CASCADE; |
---|
| 484 | DROP TRIGGER calendar_item_sync_dav_id ON calendar_item CASCADE; |
---|
| 485 | CREATE or REPLACE FUNCTION sync_dav_id ( ) RETURNS TRIGGER AS $$ |
---|
| 486 | DECLARE |
---|
| 487 | BEGIN |
---|
| 488 | |
---|
| 489 | IF TG_OP = 'DELETE' THEN |
---|
| 490 | -- Just let the ON DELETE CASCADE handle this case |
---|
| 491 | RETURN OLD; |
---|
| 492 | END IF; |
---|
| 493 | |
---|
| 494 | IF NEW.dav_id IS NULL THEN |
---|
| 495 | NEW.dav_id = nextval('dav_id_seq'); |
---|
| 496 | END IF; |
---|
| 497 | |
---|
| 498 | IF TG_OP = 'UPDATE' THEN |
---|
| 499 | IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id |
---|
| 500 | OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN |
---|
| 501 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
| 502 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
| 503 | WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id; |
---|
| 504 | END IF; |
---|
| 505 | RETURN NEW; |
---|
| 506 | END IF; |
---|
| 507 | |
---|
| 508 | UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, |
---|
| 509 | collection_id = NEW.collection_id, dav_name = NEW.dav_name |
---|
| 510 | WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id; |
---|
| 511 | |
---|
| 512 | RETURN NEW; |
---|
| 513 | |
---|
| 514 | END |
---|
| 515 | $$ LANGUAGE 'plpgsql'; |
---|
| 516 | CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data |
---|
| 517 | FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); |
---|
| 518 | |
---|
| 519 | |
---|
| 520 | |
---|
| 521 | -- New in 1.2.6 |
---|
| 522 | |
---|
| 523 | CREATE or REPLACE FUNCTION legacy_privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
| 524 | DECLARE |
---|
| 525 | in_priv ALIAS FOR $1; |
---|
| 526 | out_bits BIT(24); |
---|
| 527 | BEGIN |
---|
| 528 | out_bits := 0::BIT(24); |
---|
| 529 | IF in_priv ~* 'A' THEN |
---|
| 530 | out_bits = ~ out_bits; |
---|
| 531 | RETURN out_bits; |
---|
| 532 | END IF; |
---|
| 533 | |
---|
| 534 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
| 535 | -- 1 DAV:read |
---|
| 536 | -- 512 CalDAV:read-free-busy |
---|
| 537 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 538 | IF in_priv ~* 'R' THEN |
---|
| 539 | out_bits := out_bits | 4609::BIT(24); |
---|
| 540 | END IF; |
---|
| 541 | |
---|
| 542 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
| 543 | -- 2 DAV:write-properties |
---|
| 544 | -- 4 DAV:write-content |
---|
| 545 | -- 64 DAV:bind |
---|
| 546 | -- 128 DAV:unbind |
---|
| 547 | IF in_priv ~* 'W' THEN |
---|
| 548 | out_bits := out_bits | 198::BIT(24); |
---|
| 549 | END IF; |
---|
| 550 | |
---|
| 551 | -- 64 DAV:bind |
---|
| 552 | IF in_priv ~* 'B' THEN |
---|
| 553 | out_bits := out_bits | 64::BIT(24); |
---|
| 554 | END IF; |
---|
| 555 | |
---|
| 556 | -- 128 DAV:unbind |
---|
| 557 | IF in_priv ~* 'U' THEN |
---|
| 558 | out_bits := out_bits | 128::BIT(24); |
---|
| 559 | END IF; |
---|
| 560 | |
---|
| 561 | -- 512 CalDAV:read-free-busy |
---|
| 562 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 563 | IF in_priv ~* 'F' THEN |
---|
| 564 | out_bits := out_bits | 4608::BIT(24); |
---|
| 565 | END IF; |
---|
| 566 | |
---|
| 567 | RETURN out_bits; |
---|
| 568 | END |
---|
| 569 | $$ |
---|
| 570 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 571 | |
---|
| 572 | -- This legacy conversion function will eventually be removed, once all logic |
---|
| 573 | -- has been converted to use bitmaps, or to use the bits_to_priv() output. |
---|
| 574 | -- |
---|
| 575 | -- NOTE: Round-trip through this and then back through legacy_privilege_to_bits |
---|
| 576 | -- function is lossy! Through legacy_privilege_to_bits() and back through |
---|
| 577 | -- this one is not. |
---|
| 578 | -- |
---|
| 579 | CREATE or REPLACE FUNCTION bits_to_legacy_privilege( BIT(24) ) RETURNS TEXT AS $$ |
---|
| 580 | DECLARE |
---|
| 581 | in_bits ALIAS FOR $1; |
---|
| 582 | out_priv TEXT; |
---|
| 583 | BEGIN |
---|
| 584 | out_priv := ''; |
---|
| 585 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
| 586 | out_priv = 'A'; |
---|
| 587 | RETURN out_priv; |
---|
| 588 | END IF; |
---|
| 589 | |
---|
| 590 | -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. |
---|
| 591 | -- 1 DAV:read |
---|
| 592 | -- 512 CalDAV:read-free-busy |
---|
| 593 | -- 4096 CALDAV:schedule-query-freebusy |
---|
| 594 | IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN |
---|
| 595 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
| 596 | out_priv := 'R'; |
---|
| 597 | ELSE |
---|
| 598 | out_priv := 'F'; |
---|
| 599 | END IF; |
---|
| 600 | END IF; |
---|
| 601 | |
---|
| 602 | -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content |
---|
| 603 | -- 2 DAV:write-properties |
---|
| 604 | -- 4 DAV:write-content |
---|
| 605 | -- 64 DAV:bind |
---|
| 606 | -- 128 DAV:unbind |
---|
| 607 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
| 608 | IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN |
---|
| 609 | out_priv := out_priv || 'W'; |
---|
| 610 | ELSE |
---|
| 611 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
| 612 | out_priv := out_priv || 'B'; |
---|
| 613 | END IF; |
---|
| 614 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
| 615 | out_priv := out_priv || 'U'; |
---|
| 616 | END IF; |
---|
| 617 | END IF; |
---|
| 618 | END IF; |
---|
| 619 | |
---|
| 620 | RETURN out_priv; |
---|
| 621 | END |
---|
| 622 | $$ |
---|
| 623 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 624 | |
---|
| 625 | CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$ |
---|
| 626 | DECLARE |
---|
| 627 | in_from ALIAS FOR $1; |
---|
| 628 | in_to ALIAS FOR $2; |
---|
| 629 | out_confers TEXT; |
---|
| 630 | bit_confers BIT(24); |
---|
| 631 | group_role_no INT; |
---|
| 632 | tmp_txt TEXT; |
---|
| 633 | dbg TEXT DEFAULT ''; |
---|
| 634 | r RECORD; |
---|
| 635 | counter INT; |
---|
| 636 | BEGIN |
---|
| 637 | -- Self can always have full access |
---|
| 638 | IF in_from = in_to THEN |
---|
| 639 | RETURN 'A'; |
---|
| 640 | END IF; |
---|
| 641 | |
---|
| 642 | -- dbg := 'S-'; |
---|
| 643 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 |
---|
| 644 | WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); |
---|
| 645 | IF FOUND THEN |
---|
| 646 | RETURN dbg || out_confers; |
---|
| 647 | END IF; |
---|
| 648 | -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; |
---|
| 649 | |
---|
| 650 | SELECT bit_or(r1.confers & r2.confers) INTO bit_confers |
---|
| 651 | FROM relationship r1 |
---|
| 652 | JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
| 653 | WHERE r1.from_user=in_from AND r2.to_user=in_to |
---|
| 654 | AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group'); |
---|
| 655 | IF bit_confers != 0::BIT(24) THEN |
---|
| 656 | RETURN dbg || bits_to_legacy_privilege(bit_confers); |
---|
| 657 | END IF; |
---|
| 658 | |
---|
| 659 | RETURN ''; |
---|
| 660 | -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; |
---|
| 661 | |
---|
| 662 | SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 LEFT OUTER JOIN relationship r2 ON(r1.to_user = r2.to_user) |
---|
| 663 | WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user |
---|
| 664 | AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ; |
---|
| 665 | |
---|
| 666 | IF FOUND THEN |
---|
| 667 | -- dbg := 'H-'; |
---|
| 668 | -- RAISE NOTICE 'Permissions to shared group % ', out_confers; |
---|
| 669 | RETURN dbg || out_confers; |
---|
| 670 | END IF; |
---|
| 671 | |
---|
| 672 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
| 673 | |
---|
| 674 | RETURN ''; |
---|
| 675 | END; |
---|
| 676 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 677 | |
---|
| 678 | |
---|
| 679 | CREATE or REPLACE FUNCTION get_group_role_no() RETURNS INT AS $$ |
---|
| 680 | SELECT role_no FROM roles WHERE role_name = 'Group' |
---|
| 681 | $$ LANGUAGE 'SQL' IMMUTABLE; |
---|
| 682 | |
---|
| 683 | CREATE or REPLACE FUNCTION has_legacy_privilege( INT, TEXT, INT ) RETURNS BOOLEAN AS $$ |
---|
| 684 | DECLARE |
---|
| 685 | in_from ALIAS FOR $1; |
---|
| 686 | in_legacy_privilege ALIAS FOR $2; |
---|
| 687 | in_to ALIAS FOR $3; |
---|
| 688 | in_confers BIT(24); |
---|
| 689 | group_role_no INT; |
---|
| 690 | BEGIN |
---|
| 691 | -- Self can always have full access |
---|
| 692 | IF in_from = in_to THEN |
---|
| 693 | RETURN TRUE; |
---|
| 694 | END IF; |
---|
| 695 | |
---|
| 696 | SELECT get_group_role_no() INTO group_role_no; |
---|
| 697 | SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers; |
---|
| 698 | |
---|
| 699 | IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to |
---|
| 700 | AND (in_confers & confers) = in_confers |
---|
| 701 | AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN |
---|
| 702 | -- A direct relationship from A to B that grants sufficient |
---|
| 703 | -- RAISE NOTICE 'Permissions directly granted'; |
---|
| 704 | RETURN TRUE; |
---|
| 705 | END IF; |
---|
| 706 | |
---|
| 707 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user |
---|
| 708 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
| 709 | AND r1.from_user=in_from AND r2.to_user=in_to |
---|
| 710 | AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN |
---|
| 711 | -- An indirect relationship from A to B via group G that grants sufficient |
---|
| 712 | -- RAISE NOTICE 'Permissions mediated via group'; |
---|
| 713 | RETURN TRUE; |
---|
| 714 | END IF; |
---|
| 715 | |
---|
| 716 | IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user |
---|
| 717 | WHERE (in_confers & r1.confers & r2.confers) = in_confers |
---|
| 718 | AND r1.from_user=in_from AND r2.from_user=in_to |
---|
| 719 | AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) |
---|
| 720 | AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN |
---|
| 721 | -- An indirect reflexive relationship from both A & B to group G which grants sufficient |
---|
| 722 | -- RAISE NOTICE 'Permissions to shared group'; |
---|
| 723 | RETURN TRUE; |
---|
| 724 | END IF; |
---|
| 725 | |
---|
| 726 | -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; |
---|
| 727 | |
---|
| 728 | RETURN FALSE; |
---|
| 729 | END; |
---|
| 730 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 731 | |
---|
| 732 | |
---|
| 733 | -- Given a verbose DAV: or CalDAV: privilege name return the bitmask |
---|
| 734 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT ) RETURNS BIT(24) AS $$ |
---|
| 735 | DECLARE |
---|
| 736 | raw_priv ALIAS FOR $1; |
---|
| 737 | in_priv TEXT; |
---|
| 738 | BEGIN |
---|
| 739 | in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', ''))); |
---|
| 740 | IF in_priv = 'all' THEN |
---|
| 741 | RETURN ~ 0::BIT(24); |
---|
| 742 | END IF; |
---|
| 743 | |
---|
| 744 | RETURN (CASE |
---|
| 745 | WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096 |
---|
| 746 | WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128 |
---|
| 747 | WHEN in_priv = 'write-properties' THEN 2 |
---|
| 748 | WHEN in_priv = 'write-content' THEN 4 |
---|
| 749 | WHEN in_priv = 'unlock' THEN 8 |
---|
| 750 | WHEN in_priv = 'read-acl' THEN 16 |
---|
| 751 | WHEN in_priv = 'read-current-user-privilege-set' THEN 32 |
---|
| 752 | WHEN in_priv = 'bind' THEN 64 |
---|
| 753 | WHEN in_priv = 'unbind' THEN 128 |
---|
| 754 | WHEN in_priv = 'write-acl' THEN 256 |
---|
| 755 | WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096 |
---|
| 756 | WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096 |
---|
| 757 | WHEN in_priv = 'schedule-deliver-invite' THEN 1024 |
---|
| 758 | WHEN in_priv = 'schedule-deliver-reply' THEN 2048 |
---|
| 759 | WHEN in_priv = 'schedule-query-freebusy' THEN 4096 |
---|
| 760 | WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768 |
---|
| 761 | WHEN in_priv = 'schedule-send-invite' THEN 8192 |
---|
| 762 | WHEN in_priv = 'schedule-send-reply' THEN 16384 |
---|
| 763 | WHEN in_priv = 'schedule-send-freebusy' THEN 32768 |
---|
| 764 | ELSE 0 END)::BIT(24); |
---|
| 765 | END |
---|
| 766 | $$ |
---|
| 767 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 768 | |
---|
| 769 | |
---|
| 770 | -- Given an array of verbose DAV: or CalDAV: privilege names return the bitmask |
---|
| 771 | CREATE or REPLACE FUNCTION privilege_to_bits( TEXT[] ) RETURNS BIT(24) AS $$ |
---|
| 772 | DECLARE |
---|
| 773 | raw_privs ALIAS FOR $1; |
---|
| 774 | in_priv TEXT; |
---|
| 775 | out_bits BIT(24); |
---|
| 776 | i INT; |
---|
| 777 | allprivs BIT(24); |
---|
| 778 | start INT; |
---|
| 779 | finish INT; |
---|
| 780 | BEGIN |
---|
| 781 | out_bits := 0::BIT(24); |
---|
| 782 | allprivs := ~ out_bits; |
---|
| 783 | SELECT array_lower(raw_privs,1) INTO start; |
---|
| 784 | SELECT array_upper(raw_privs,1) INTO finish; |
---|
| 785 | FOR i IN start .. finish LOOP |
---|
| 786 | SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits; |
---|
| 787 | IF out_bits = allprivs THEN |
---|
| 788 | RETURN allprivs; |
---|
| 789 | END IF; |
---|
| 790 | END LOOP; |
---|
| 791 | RETURN out_bits; |
---|
| 792 | END |
---|
| 793 | $$ |
---|
| 794 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 795 | |
---|
| 796 | |
---|
| 797 | -- NOTE: Round-trip through this and then back through privilege_to_bits |
---|
| 798 | -- function is lossy! Through privilege_to_bits() and back through |
---|
| 799 | -- this one is not. |
---|
| 800 | -- |
---|
| 801 | CREATE or REPLACE FUNCTION bits_to_privilege( BIT(24) ) RETURNS TEXT[] AS $$ |
---|
| 802 | DECLARE |
---|
| 803 | in_bits ALIAS FOR $1; |
---|
| 804 | out_priv TEXT[]; |
---|
| 805 | BEGIN |
---|
| 806 | IF in_bits = (~ 0::BIT(24)) THEN |
---|
| 807 | out_priv := out_priv || ARRAY['DAV:all']; |
---|
| 808 | END IF; |
---|
| 809 | |
---|
| 810 | IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN |
---|
| 811 | IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN |
---|
| 812 | out_priv := out_priv || ARRAY['DAV:read']; |
---|
| 813 | END IF; |
---|
| 814 | IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN |
---|
| 815 | out_priv := out_priv || ARRAY['caldav:read-free-busy']; |
---|
| 816 | END IF; |
---|
| 817 | END IF; |
---|
| 818 | |
---|
| 819 | IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN |
---|
| 820 | IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN |
---|
| 821 | out_priv := out_priv || ARRAY['DAV:write']; |
---|
| 822 | ELSE |
---|
| 823 | IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN |
---|
| 824 | out_priv := out_priv || ARRAY['DAV:write-properties']; |
---|
| 825 | END IF; |
---|
| 826 | IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN |
---|
| 827 | out_priv := out_priv || ARRAY['DAV:write-content']; |
---|
| 828 | END IF; |
---|
| 829 | IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN |
---|
| 830 | out_priv := out_priv || ARRAY['DAV:bind']; |
---|
| 831 | END IF; |
---|
| 832 | IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN |
---|
| 833 | out_priv := out_priv || ARRAY['DAV:unbind']; |
---|
| 834 | END IF; |
---|
| 835 | END IF; |
---|
| 836 | END IF; |
---|
| 837 | |
---|
| 838 | IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN |
---|
| 839 | out_priv := out_priv || ARRAY['DAV:unlock']; |
---|
| 840 | END IF; |
---|
| 841 | |
---|
| 842 | IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN |
---|
| 843 | out_priv := out_priv || ARRAY['DAV:read-acl']; |
---|
| 844 | END IF; |
---|
| 845 | |
---|
| 846 | IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN |
---|
| 847 | out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set']; |
---|
| 848 | END IF; |
---|
| 849 | |
---|
| 850 | IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN |
---|
| 851 | out_priv := out_priv || ARRAY['DAV:write-acl']; |
---|
| 852 | END IF; |
---|
| 853 | |
---|
| 854 | IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN |
---|
| 855 | IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN |
---|
| 856 | out_priv := out_priv || ARRAY['caldav:schedule-deliver']; |
---|
| 857 | ELSE |
---|
| 858 | IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN |
---|
| 859 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite']; |
---|
| 860 | END IF; |
---|
| 861 | IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN |
---|
| 862 | out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply']; |
---|
| 863 | END IF; |
---|
| 864 | IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN |
---|
| 865 | out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy']; |
---|
| 866 | END IF; |
---|
| 867 | END IF; |
---|
| 868 | END IF; |
---|
| 869 | |
---|
| 870 | IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN |
---|
| 871 | IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN |
---|
| 872 | out_priv := out_priv || ARRAY['caldav:schedule-send']; |
---|
| 873 | ELSE |
---|
| 874 | IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN |
---|
| 875 | out_priv := out_priv || ARRAY['caldav:schedule-send-invite']; |
---|
| 876 | END IF; |
---|
| 877 | IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN |
---|
| 878 | out_priv := out_priv || ARRAY['caldav:schedule-send-reply']; |
---|
| 879 | END IF; |
---|
| 880 | IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN |
---|
| 881 | out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy']; |
---|
| 882 | END IF; |
---|
| 883 | END IF; |
---|
| 884 | END IF; |
---|
| 885 | |
---|
| 886 | RETURN out_priv; |
---|
| 887 | END |
---|
| 888 | $$ |
---|
| 889 | LANGUAGE 'PlPgSQL' IMMUTABLE STRICT; |
---|
| 890 | |
---|
| 891 | |
---|
| 892 | -- Expanded group memberships out to some depth |
---|
| 893 | CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
| 894 | SELECT group_id FROM group_member WHERE member_id = $1 |
---|
| 895 | UNION |
---|
| 896 | SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id |
---|
| 897 | FROM group_member WHERE member_id = $1) AS expanded |
---|
| 898 | WHERE expanded.g_id IS NOT NULL; |
---|
| 899 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
| 900 | |
---|
| 901 | -- Expanded group members out to some depth |
---|
| 902 | CREATE or REPLACE FUNCTION expand_members( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
| 903 | SELECT member_id FROM group_member WHERE group_id = $1 |
---|
| 904 | UNION |
---|
| 905 | SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id |
---|
| 906 | FROM group_member WHERE group_id = $1) AS expanded |
---|
| 907 | WHERE expanded.m_id IS NOT NULL; |
---|
| 908 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
| 909 | |
---|
| 910 | |
---|
| 911 | |
---|
| 912 | |
---|
| 913 | -- Privileges from accessor to grantor, by principal_id |
---|
| 914 | CREATE or REPLACE FUNCTION pprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$ |
---|
| 915 | DECLARE |
---|
| 916 | in_accessor ALIAS FOR $1; |
---|
| 917 | in_grantor ALIAS FOR $2; |
---|
| 918 | in_depth ALIAS FOR $3; |
---|
| 919 | out_conferred BIT(24); |
---|
| 920 | BEGIN |
---|
| 921 | out_conferred := 0::BIT(24); |
---|
| 922 | -- Self can always have full access |
---|
| 923 | IF in_grantor = in_accessor THEN |
---|
| 924 | RETURN ~ out_conferred; |
---|
| 925 | END IF; |
---|
| 926 | |
---|
| 927 | SELECT bit_or(subquery.privileges) INTO out_conferred FROM |
---|
| 928 | ( |
---|
| 929 | SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL |
---|
| 930 | AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))) |
---|
| 931 | UNION |
---|
| 932 | SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor |
---|
| 933 | ) AS subquery ; |
---|
| 934 | |
---|
| 935 | IF out_conferred IS NULL THEN |
---|
| 936 | SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor; |
---|
| 937 | END IF; |
---|
| 938 | |
---|
| 939 | RETURN out_conferred; |
---|
| 940 | END; |
---|
| 941 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
| 942 | |
---|
| 943 | |
---|
| 944 | -- Privileges from accessor to grantor, by user_no |
---|
| 945 | CREATE or REPLACE FUNCTION uprivs( INT8, INT8, INT ) RETURNS BIT(24) AS $$ |
---|
| 946 | DECLARE |
---|
| 947 | in_accessor ALIAS FOR $1; |
---|
| 948 | in_grantor ALIAS FOR $2; |
---|
| 949 | in_depth ALIAS FOR $3; |
---|
| 950 | out_conferred BIT(24); |
---|
| 951 | BEGIN |
---|
| 952 | out_conferred := 0::BIT(24); |
---|
| 953 | -- Self can always have full access |
---|
| 954 | IF in_grantor = in_accessor THEN |
---|
| 955 | RETURN ~ out_conferred; |
---|
| 956 | END IF; |
---|
| 957 | |
---|
| 958 | SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred |
---|
| 959 | FROM principal p1, principal p2 |
---|
| 960 | WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor; |
---|
| 961 | |
---|
| 962 | RETURN out_conferred; |
---|
| 963 | END; |
---|
| 964 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
| 965 | |
---|
| 966 | |
---|
| 967 | -- Privileges from accessor (by principal_id) to path |
---|
| 968 | CREATE or REPLACE FUNCTION path_privs( INT8, TEXT, INT ) RETURNS BIT(24) AS $$ |
---|
| 969 | DECLARE |
---|
| 970 | in_accessor ALIAS FOR $1; |
---|
| 971 | in_path ALIAS FOR $2; |
---|
| 972 | in_depth ALIAS FOR $3; |
---|
| 973 | |
---|
| 974 | alt1_path TEXT; |
---|
| 975 | alt2_path TEXT; |
---|
| 976 | grantor_collection INT8; |
---|
| 977 | grantor_principal INT8; |
---|
| 978 | collection_path TEXT; |
---|
| 979 | collection_privileges BIT(24); |
---|
| 980 | out_conferred BIT(24); |
---|
| 981 | BEGIN |
---|
| 982 | out_conferred := 0::BIT(24); |
---|
| 983 | |
---|
| 984 | IF in_path ~ '^/?$' THEN |
---|
| 985 | -- RAISE NOTICE 'Collection is root: Collection: %', in_path; |
---|
| 986 | RETURN 1; -- basic read privileges on root directory |
---|
| 987 | END IF; |
---|
| 988 | |
---|
| 989 | -- We need to canonicalise the path, so: |
---|
| 990 | -- If it matches '/' + some characters (+ optional '/') => a principal URL |
---|
| 991 | IF in_path ~ '^/[^/]+/?$' THEN |
---|
| 992 | alt1_path := replace(in_path, '/', ''); |
---|
| 993 | SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path; |
---|
| 994 | -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
| 995 | RETURN out_conferred; |
---|
| 996 | END IF; |
---|
| 997 | |
---|
| 998 | -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one. |
---|
| 999 | alt1_path := in_path; |
---|
| 1000 | IF alt1_path ~ E'\\.ics$' THEN |
---|
| 1001 | alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/'; |
---|
| 1002 | END IF; |
---|
| 1003 | alt2_path := regexp_replace( in_path, '[^/]*$', ''); |
---|
| 1004 | SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges |
---|
| 1005 | INTO grantor_collection, grantor_principal, collection_path, collection_privileges |
---|
| 1006 | FROM collection JOIN principal grantor USING (user_no) |
---|
| 1007 | WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path |
---|
| 1008 | ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1; |
---|
| 1009 | |
---|
| 1010 | -- Self will always need full access to their own collections! |
---|
| 1011 | IF grantor_principal = in_accessor THEN |
---|
| 1012 | -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path; |
---|
| 1013 | RETURN ~ out_conferred; |
---|
| 1014 | END IF; |
---|
| 1015 | |
---|
| 1016 | SELECT privileges INTO out_conferred FROM grants |
---|
| 1017 | WHERE by_collection = grantor_collection |
---|
| 1018 | AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))); |
---|
| 1019 | |
---|
| 1020 | IF out_conferred IS NULL THEN |
---|
| 1021 | IF collection_privileges IS NULL THEN |
---|
| 1022 | IF grantor_principal IS NULL THEN |
---|
| 1023 | alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/'); |
---|
| 1024 | SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred; |
---|
| 1025 | -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
| 1026 | ELSE |
---|
| 1027 | SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred; |
---|
| 1028 | -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
| 1029 | END IF; |
---|
| 1030 | ELSE |
---|
| 1031 | out_conferred := collection_privileges; |
---|
| 1032 | -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; |
---|
| 1033 | END IF; |
---|
| 1034 | END IF; |
---|
| 1035 | |
---|
| 1036 | RETURN out_conferred; |
---|
| 1037 | END; |
---|
| 1038 | $$ LANGUAGE 'plpgsql' STABLE STRICT; |
---|
| 1039 | |
---|
| 1040 | |
---|
| 1041 | -- List a user's memberships as a text string |
---|
| 1042 | CREATE or REPLACE FUNCTION is_member_of_list( INT8 ) RETURNS TEXT AS $$ |
---|
| 1043 | DECLARE |
---|
| 1044 | in_member_id ALIAS FOR $1; |
---|
| 1045 | m RECORD; |
---|
| 1046 | mlist TEXT; |
---|
| 1047 | BEGIN |
---|
| 1048 | mlist := ''; |
---|
| 1049 | FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id) |
---|
| 1050 | WHERE member_id = in_member_id |
---|
| 1051 | LOOP |
---|
| 1052 | mlist := mlist |
---|
| 1053 | || CASE WHEN mlist = '' THEN '' ELSE ', ' END |
---|
| 1054 | || COALESCE( m.displayname, m.group_id::text); |
---|
| 1055 | END LOOP; |
---|
| 1056 | RETURN mlist; |
---|
| 1057 | END; |
---|
| 1058 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
| 1059 | |
---|
| 1060 | |
---|
| 1061 | -- List a user's members as a text string |
---|
| 1062 | CREATE or REPLACE FUNCTION has_members_list( INT8 ) RETURNS TEXT AS $$ |
---|
| 1063 | DECLARE |
---|
| 1064 | in_member_id ALIAS FOR $1; |
---|
| 1065 | m RECORD; |
---|
| 1066 | mlist TEXT; |
---|
| 1067 | BEGIN |
---|
| 1068 | mlist := ''; |
---|
| 1069 | FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id) |
---|
| 1070 | WHERE group_id = in_member_id |
---|
| 1071 | LOOP |
---|
| 1072 | mlist := mlist |
---|
| 1073 | || CASE WHEN mlist = '' THEN '' ELSE ', ' END |
---|
| 1074 | || COALESCE( m.displayname, m.group_id::text); |
---|
| 1075 | END LOOP; |
---|
| 1076 | RETURN mlist; |
---|
| 1077 | END; |
---|
| 1078 | $$ LANGUAGE 'plpgsql' STRICT; |
---|
| 1079 | |
---|
| 1080 | |
---|
| 1081 | -- List the privileges as a text string |
---|
| 1082 | CREATE or REPLACE FUNCTION privileges_list( BIT(24) ) RETURNS TEXT AS $$ |
---|
| 1083 | DECLARE |
---|
| 1084 | in_privileges ALIAS FOR $1; |
---|
| 1085 | privileges TEXT[]; |
---|
| 1086 | plist TEXT; |
---|
| 1087 | start INT; |
---|
| 1088 | finish INT; |
---|
| 1089 | i INT; |
---|
| 1090 | BEGIN |
---|
| 1091 | plist := ''; |
---|
| 1092 | |
---|
| 1093 | privileges := bits_to_privilege(in_privileges); |
---|
| 1094 | SELECT array_lower(privileges,1) INTO start; |
---|
| 1095 | IF start IS NOT NULL THEN |
---|
| 1096 | SELECT array_upper(privileges,1) INTO finish; |
---|
| 1097 | FOR i IN start .. finish LOOP |
---|
| 1098 | plist := plist |
---|
| 1099 | || CASE WHEN plist = '' THEN '' ELSE ', ' END |
---|
| 1100 | || privileges[i]; |
---|
| 1101 | END LOOP; |
---|
| 1102 | END IF; |
---|
| 1103 | RETURN plist; |
---|
| 1104 | END; |
---|
| 1105 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 1106 | |
---|
| 1107 | |
---|
| 1108 | DROP TRIGGER principal_modified ON principal CASCADE; |
---|
| 1109 | CREATE or REPLACE FUNCTION principal_modified() RETURNS TRIGGER AS $$ |
---|
| 1110 | DECLARE |
---|
| 1111 | BEGIN |
---|
| 1112 | -- in case we trigger on other events in future |
---|
| 1113 | IF TG_OP = 'UPDATE' THEN |
---|
| 1114 | IF NEW.type_id != OLD.type_id THEN |
---|
| 1115 | UPDATE grants |
---|
| 1116 | SET is_group = (NEW.type_id = 3) |
---|
| 1117 | WHERE grants.to_principal = NEW.principal_id; |
---|
| 1118 | END IF; |
---|
| 1119 | END IF; |
---|
| 1120 | RETURN NEW; |
---|
| 1121 | END; |
---|
| 1122 | $$ LANGUAGE plpgsql; |
---|
| 1123 | CREATE TRIGGER principal_modified AFTER UPDATE ON principal |
---|
| 1124 | FOR EACH ROW EXECUTE PROCEDURE principal_modified(); |
---|
| 1125 | |
---|
| 1126 | |
---|
| 1127 | DROP TRIGGER grants_modified ON grants CASCADE; |
---|
| 1128 | CREATE or REPLACE FUNCTION grants_modified() RETURNS TRIGGER AS $$ |
---|
| 1129 | DECLARE |
---|
| 1130 | old_to_principal INT8; |
---|
| 1131 | new_is_group BOOL; |
---|
| 1132 | BEGIN |
---|
| 1133 | -- in case we trigger on other events in future |
---|
| 1134 | IF TG_OP = 'INSERT' THEN |
---|
| 1135 | old_to_principal := NULL; |
---|
| 1136 | ELSE |
---|
| 1137 | old_to_principal := OLD.to_principal; |
---|
| 1138 | END IF; |
---|
| 1139 | IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN |
---|
| 1140 | SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal; |
---|
| 1141 | IF NEW.is_group != new_is_group THEN |
---|
| 1142 | NEW.is_group := new_is_group; |
---|
| 1143 | END IF; |
---|
| 1144 | END IF; |
---|
| 1145 | RETURN NEW; |
---|
| 1146 | END; |
---|
| 1147 | $$ LANGUAGE plpgsql; |
---|
| 1148 | CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants |
---|
| 1149 | FOR EACH ROW EXECUTE PROCEDURE grants_modified(); |
---|
| 1150 | |
---|
| 1151 | |
---|
| 1152 | |
---|
| 1153 | -- An expanded list of the grants this principal has access to |
---|
| 1154 | CREATE or REPLACE FUNCTION p_has_proxy_access_to( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
| 1155 | SELECT by_principal |
---|
| 1156 | FROM ( |
---|
| 1157 | SELECT by_principal FROM grants |
---|
| 1158 | WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2)) |
---|
| 1159 | AND (privileges & 5::BIT(24)) != 0::BIT(24) |
---|
| 1160 | AND by_collection IS NULL |
---|
| 1161 | AND by_principal != $1 |
---|
| 1162 | UNION |
---|
| 1163 | SELECT principal_id AS by_principal FROM principal |
---|
| 1164 | WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24) |
---|
| 1165 | AND principal_id != $1 |
---|
| 1166 | ) subquery; |
---|
| 1167 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
| 1168 | |
---|
| 1169 | |
---|
| 1170 | -- A list of the principals who can proxy to this principal |
---|
| 1171 | CREATE or REPLACE FUNCTION grants_proxy_access_from_p( INT8, INT ) RETURNS SETOF INT8 AS $$ |
---|
| 1172 | SELECT DISTINCT by_principal |
---|
| 1173 | FROM grants |
---|
| 1174 | WHERE by_collection IS NULL AND by_principal != $1 |
---|
| 1175 | AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1) |
---|
| 1176 | ; |
---|
| 1177 | $$ LANGUAGE 'SQL' STABLE STRICT; |
---|
| 1178 | |
---|
| 1179 | |
---|
| 1180 | |
---|
| 1181 | -- New in 1.2.7 |
---|
| 1182 | |
---|
| 1183 | CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$ |
---|
| 1184 | DECLARE |
---|
| 1185 | in_collection_id ALIAS FOR $1; |
---|
| 1186 | in_status ALIAS FOR $2; |
---|
| 1187 | in_dav_name ALIAS FOR $3; |
---|
| 1188 | tmp_int INT8; |
---|
| 1189 | BEGIN |
---|
| 1190 | SELECT 1 INTO tmp_int FROM sync_tokens |
---|
| 1191 | WHERE collection_id = in_collection_id |
---|
| 1192 | LIMIT 1; |
---|
| 1193 | IF NOT FOUND THEN |
---|
| 1194 | RETURN FALSE; |
---|
| 1195 | END IF; |
---|
| 1196 | SELECT dav_id INTO tmp_int FROM calendar_item WHERE dav_name = in_dav_name; |
---|
| 1197 | INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name) |
---|
| 1198 | VALUES( in_collection_id, in_status, tmp_int, in_dav_name); |
---|
| 1199 | RETURN TRUE; |
---|
| 1200 | END |
---|
| 1201 | $$ LANGUAGE 'PlPgSQL' VOLATILE STRICT; |
---|
| 1202 | |
---|
| 1203 | |
---|
| 1204 | CREATE or REPLACE FUNCTION new_sync_token( INT8, INT8 ) RETURNS INT8 AS $$ |
---|
| 1205 | DECLARE |
---|
| 1206 | in_old_sync_token ALIAS FOR $1; |
---|
| 1207 | in_collection_id ALIAS FOR $2; |
---|
| 1208 | tmp_int INT8; |
---|
| 1209 | BEGIN |
---|
| 1210 | IF in_old_sync_token > 0 THEN |
---|
| 1211 | SELECT 1 INTO tmp_int FROM sync_changes |
---|
| 1212 | WHERE collection_id = in_collection_id |
---|
| 1213 | AND sync_time > (SELECT modification_time FROM sync_tokens WHERE sync_token = in_old_sync_token) |
---|
| 1214 | LIMIT 1; |
---|
| 1215 | IF NOT FOUND THEN |
---|
| 1216 | RETURN in_old_sync_token; |
---|
| 1217 | END IF; |
---|
| 1218 | END IF; |
---|
| 1219 | SELECT nextval('sync_tokens_sync_token_seq') INTO tmp_int; |
---|
| 1220 | INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, tmp_int ); |
---|
| 1221 | RETURN tmp_int; |
---|
| 1222 | END |
---|
| 1223 | $$ LANGUAGE 'PlPgSQL' STRICT; |
---|
| 1224 | |
---|
| 1225 | |
---|
| 1226 | DROP TRIGGER alarm_changed ON calendar_alarm CASCADE; |
---|
| 1227 | CREATE or REPLACE FUNCTION alarm_changed() RETURNS TRIGGER AS $$ |
---|
| 1228 | DECLARE |
---|
| 1229 | oldcomponent TEXT; |
---|
| 1230 | newcomponent TEXT; |
---|
| 1231 | BEGIN |
---|
| 1232 | -- in case we trigger on other events in future |
---|
| 1233 | IF TG_OP = 'UPDATE' THEN |
---|
| 1234 | IF NEW.component != OLD.component THEN |
---|
| 1235 | UPDATE caldav_data |
---|
| 1236 | SET caldav_data = replace( caldav_data, OLD.component, NEW.component ), |
---|
| 1237 | dav_etag = md5(replace( caldav_data, OLD.component, NEW.component )) |
---|
| 1238 | WHERE caldav_data.dav_id = NEW.dav_id; |
---|
| 1239 | END IF; |
---|
| 1240 | END IF; |
---|
| 1241 | RETURN NEW; |
---|
| 1242 | END; |
---|
| 1243 | $$ LANGUAGE plpgsql; |
---|
| 1244 | CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm |
---|
| 1245 | FOR EACH ROW EXECUTE PROCEDURE alarm_changed(); |
---|