[3733] | 1 | /** |
---|
| 2 | * PostgreSQL Functions for RRULE handling |
---|
| 3 | * |
---|
| 4 | * @package rscds |
---|
| 5 | * @subpackage database |
---|
| 6 | * @author Andrew McMillan <andrew@morphoss.com> |
---|
| 7 | * @copyright Morphoss Ltd - http://www.morphoss.com/ |
---|
| 8 | * @license http://gnu.org/copyleft/gpl.html GNU GPL v2 or later |
---|
| 9 | * |
---|
| 10 | * Coverage of this function set |
---|
| 11 | * - COUNT & UNTIL are handled, generally |
---|
| 12 | * - DAILY frequency, including BYDAY, BYMONTH, BYMONTHDAY, BYWEEKNO, BYMONTHDAY |
---|
| 13 | * - WEEKLY frequency, including BYDAY, BYMONTH, BYMONTHDAY, BYWEEKNO, BYSETPOS |
---|
| 14 | * - MONTHLY frequency, including BYDAY, BYMONTH, BYSETPOS |
---|
| 15 | * - YEARLY frequency, including BYMONTH, BYMONTHDAY, BYSETPOS, BYDAY |
---|
| 16 | * |
---|
| 17 | * Not covered as yet |
---|
| 18 | * - DAILY: BYYEARDAY, BYSETPOS* |
---|
| 19 | * - WEEKLY: BYYEARDAY |
---|
| 20 | * - MONTHLY: BYYEARDAY, BYMONTHDAY, BYWEEKNO |
---|
| 21 | * - YEARLY: BYYEARDAY |
---|
| 22 | * - SECONDLY |
---|
| 23 | * - MINUTELY |
---|
| 24 | * - HOURLY |
---|
| 25 | * |
---|
| 26 | */ |
---|
| 27 | |
---|
| 28 | -- Create a composite type for the parts of the RRULE. |
---|
| 29 | DROP TYPE rrule_parts CASCADE; |
---|
| 30 | CREATE TYPE rrule_parts AS ( |
---|
| 31 | base TIMESTAMP WITH TIME ZONE, |
---|
| 32 | until TIMESTAMP WITH TIME ZONE, |
---|
| 33 | freq TEXT, |
---|
| 34 | count INT, |
---|
| 35 | interval INT, |
---|
| 36 | bysecond INT[], |
---|
| 37 | byminute INT[], |
---|
| 38 | byhour INT[], |
---|
| 39 | bymonthday INT[], |
---|
| 40 | byyearday INT[], |
---|
| 41 | byweekno INT[], |
---|
| 42 | byday TEXT[], |
---|
| 43 | bymonth INT[], |
---|
| 44 | bysetpos INT[], |
---|
| 45 | wkst TEXT |
---|
| 46 | ); |
---|
| 47 | |
---|
| 48 | |
---|
| 49 | -- Create a function to parse the RRULE into it's composite type |
---|
| 50 | CREATE or REPLACE FUNCTION parse_rrule_parts( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS rrule_parts AS $$ |
---|
| 51 | DECLARE |
---|
| 52 | basedate ALIAS FOR $1; |
---|
| 53 | repeatrule ALIAS FOR $2; |
---|
| 54 | result rrule_parts%ROWTYPE; |
---|
| 55 | tempstr TEXT; |
---|
| 56 | BEGIN |
---|
| 57 | result.base := basedate; |
---|
| 58 | result.until := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); |
---|
| 59 | result.freq := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); |
---|
| 60 | result.count := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); |
---|
| 61 | result.interval := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1); |
---|
| 62 | result.wkst := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)'); |
---|
| 63 | |
---|
| 64 | result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ','); |
---|
| 65 | |
---|
| 66 | result.byyearday := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ','); |
---|
| 67 | result.byweekno := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ','); |
---|
| 68 | result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ','); |
---|
| 69 | result.bymonth := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ','); |
---|
| 70 | result.bysetpos := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ','); |
---|
| 71 | |
---|
| 72 | result.bysecond := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ','); |
---|
| 73 | result.byminute := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ','); |
---|
| 74 | result.byhour := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ','); |
---|
| 75 | |
---|
| 76 | RETURN result; |
---|
| 77 | END; |
---|
| 78 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 79 | |
---|
| 80 | |
---|
| 81 | -- Return a SETOF dates within the month of a particular date which match a string of BYDAY rule specifications |
---|
| 82 | CREATE or REPLACE FUNCTION rrule_month_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 83 | DECLARE |
---|
| 84 | in_time ALIAS FOR $1; |
---|
| 85 | byday ALIAS FOR $2; |
---|
| 86 | dayrule TEXT; |
---|
| 87 | i INT; |
---|
| 88 | dow INT; |
---|
| 89 | index INT; |
---|
| 90 | first_dow INT; |
---|
| 91 | each_day TIMESTAMP WITH TIME ZONE; |
---|
| 92 | this_month INT; |
---|
| 93 | results TIMESTAMP WITH TIME ZONE[]; |
---|
| 94 | BEGIN |
---|
| 95 | |
---|
| 96 | IF byday IS NULL THEN |
---|
| 97 | -- We still return the single date as a SET |
---|
| 98 | RETURN NEXT in_time; |
---|
| 99 | RETURN; |
---|
| 100 | END IF; |
---|
| 101 | |
---|
| 102 | -- It seems that the array needs to be initialised to non-null values in PostgreSQL 8.1 |
---|
| 103 | -- The date chosen is the earliest valid date in PostgreSQL 8.1, and nobody should be |
---|
| 104 | -- scheduling appointments for then! |
---|
| 105 | FOR i IN 1..31 LOOP |
---|
| 106 | results[i] := '4713-01-01 00:00:00 BC'::timestamp; |
---|
| 107 | END LOOP; |
---|
| 108 | |
---|
| 109 | i := 1; |
---|
| 110 | dayrule := byday[i]; |
---|
| 111 | WHILE dayrule IS NOT NULL LOOP |
---|
| 112 | dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2; |
---|
| 113 | each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval; |
---|
| 114 | this_month := date_part( 'month', in_time ); |
---|
| 115 | first_dow := date_part( 'dow', each_day ); |
---|
| 116 | |
---|
| 117 | -- Coerce each_day to be the first 'dow' of the month |
---|
| 118 | each_day := each_day - ( first_dow::text || 'days')::interval |
---|
| 119 | + ( dow::text || 'days')::interval |
---|
| 120 | + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END; |
---|
| 121 | |
---|
| 122 | -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow; |
---|
| 123 | IF length(dayrule) > 2 THEN |
---|
| 124 | index := (substring(dayrule from '^[0-9-]+'))::int; |
---|
| 125 | |
---|
| 126 | IF index = 0 THEN |
---|
| 127 | RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule; |
---|
| 128 | ELSIF index > 0 THEN |
---|
| 129 | -- The simplest case, such as 2MO for the second monday |
---|
| 130 | each_day := each_day + ((index - 1)::text || ' weeks')::interval; |
---|
| 131 | ELSE |
---|
| 132 | each_day := each_day + '5 weeks'::interval; |
---|
| 133 | WHILE date_part('month', each_day) != this_month LOOP |
---|
| 134 | each_day := each_day - '1 week'::interval; |
---|
| 135 | END LOOP; |
---|
| 136 | -- Note that since index is negative, (-2 + 1) == -1, for example |
---|
| 137 | index := index + 1; |
---|
| 138 | IF index < 0 THEN |
---|
| 139 | each_day := each_day + (index::text || ' weeks')::interval ; |
---|
| 140 | END IF; |
---|
| 141 | END IF; |
---|
| 142 | |
---|
| 143 | -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months |
---|
| 144 | IF date_part('month', each_day) = this_month THEN |
---|
| 145 | results[date_part('day',each_day)] := each_day; |
---|
| 146 | -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; |
---|
| 147 | END IF; |
---|
| 148 | |
---|
| 149 | ELSE |
---|
| 150 | -- Return all such days that are within the given month |
---|
| 151 | WHILE date_part('month', each_day) = this_month LOOP |
---|
| 152 | index := date_part('day',each_day); |
---|
| 153 | -- RAISE NOTICE 'Adding "%" to list[%] for %', each_day, index, dayrule; |
---|
| 154 | results[index] := each_day; |
---|
| 155 | -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; |
---|
| 156 | each_day := each_day + '1 week'::interval; |
---|
| 157 | END LOOP; |
---|
| 158 | END IF; |
---|
| 159 | |
---|
| 160 | i := i + 1; |
---|
| 161 | dayrule := byday[i]; |
---|
| 162 | END LOOP; |
---|
| 163 | |
---|
| 164 | FOR i IN 1..31 LOOP |
---|
| 165 | IF results[i] > '4713-01-01 00:00:00 BC'::timestamp THEN |
---|
| 166 | RETURN NEXT results[i]; |
---|
| 167 | END IF; |
---|
| 168 | END LOOP; |
---|
| 169 | |
---|
| 170 | RETURN; |
---|
| 171 | |
---|
| 172 | END; |
---|
| 173 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 174 | |
---|
| 175 | |
---|
| 176 | -- Return a SETOF dates within the month of a particular date which match a string of BYDAY rule specifications |
---|
| 177 | CREATE or REPLACE FUNCTION rrule_month_bymonthday_set( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 178 | DECLARE |
---|
| 179 | in_time ALIAS FOR $1; |
---|
| 180 | bymonthday ALIAS FOR $2; |
---|
| 181 | month_start TIMESTAMP WITH TIME ZONE; |
---|
| 182 | daysinmonth INT; |
---|
| 183 | i INT; |
---|
| 184 | BEGIN |
---|
| 185 | |
---|
| 186 | month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval; |
---|
| 187 | daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' ); |
---|
| 188 | |
---|
| 189 | FOR i IN 1..31 LOOP |
---|
| 190 | EXIT WHEN bymonthday[i] IS NULL; |
---|
| 191 | |
---|
| 192 | CONTINUE WHEN bymonthday[i] > daysinmonth; |
---|
| 193 | CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth); |
---|
| 194 | |
---|
| 195 | IF bymonthday[i] > 0 THEN |
---|
| 196 | RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval; |
---|
| 197 | ELSIF bymonthday[i] < 0 THEN |
---|
| 198 | RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval; |
---|
| 199 | ELSE |
---|
| 200 | RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i]; |
---|
| 201 | END IF; |
---|
| 202 | END LOOP; |
---|
| 203 | |
---|
| 204 | RETURN; |
---|
| 205 | |
---|
| 206 | END; |
---|
| 207 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 208 | |
---|
| 209 | |
---|
| 210 | -- Return a SETOF dates within the week of a particular date which match a single BYDAY rule specification |
---|
| 211 | CREATE or REPLACE FUNCTION rrule_week_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 212 | DECLARE |
---|
| 213 | in_time ALIAS FOR $1; |
---|
| 214 | byday ALIAS FOR $2; |
---|
| 215 | dayrule TEXT; |
---|
| 216 | dow INT; |
---|
| 217 | our_day TIMESTAMP WITH TIME ZONE; |
---|
| 218 | i INT; |
---|
| 219 | BEGIN |
---|
| 220 | |
---|
| 221 | IF byday IS NULL THEN |
---|
| 222 | -- We still return the single date as a SET |
---|
| 223 | RETURN NEXT in_time; |
---|
| 224 | RETURN; |
---|
| 225 | END IF; |
---|
| 226 | |
---|
| 227 | our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval; |
---|
| 228 | |
---|
| 229 | i := 1; |
---|
| 230 | dayrule := byday[i]; |
---|
| 231 | WHILE dayrule IS NOT NULL LOOP |
---|
| 232 | dow := position(dayrule in 'SUMOTUWETHFRSA') / 2; |
---|
| 233 | RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval; |
---|
| 234 | i := i + 1; |
---|
| 235 | dayrule := byday[i]; |
---|
| 236 | END LOOP; |
---|
| 237 | |
---|
| 238 | RETURN; |
---|
| 239 | |
---|
| 240 | END; |
---|
| 241 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 242 | |
---|
| 243 | |
---|
| 244 | CREATE or REPLACE FUNCTION event_has_exceptions( TEXT ) RETURNS BOOLEAN AS $$ |
---|
| 245 | SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?' |
---|
| 246 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
| 247 | |
---|
| 248 | |
---|
| 249 | ------------------------------------------------------------------------------------------------------ |
---|
| 250 | -- Test the weekday of this date against the array of weekdays from the BYDAY rule (FREQ=WEEKLY or less) |
---|
| 251 | ------------------------------------------------------------------------------------------------------ |
---|
| 252 | CREATE or REPLACE FUNCTION test_byday_rule( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS BOOLEAN AS $$ |
---|
| 253 | DECLARE |
---|
| 254 | testme ALIAS FOR $1; |
---|
| 255 | byday ALIAS FOR $2; |
---|
| 256 | BEGIN |
---|
| 257 | -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended |
---|
| 258 | -- so don't call it that way... |
---|
| 259 | IF byday IS NOT NULL THEN |
---|
| 260 | RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) ); |
---|
| 261 | END IF; |
---|
| 262 | RETURN TRUE; |
---|
| 263 | END; |
---|
| 264 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 265 | |
---|
| 266 | |
---|
| 267 | ------------------------------------------------------------------------------------------------------ |
---|
| 268 | -- Test the month of this date against the array of months from the rule |
---|
| 269 | ------------------------------------------------------------------------------------------------------ |
---|
| 270 | CREATE or REPLACE FUNCTION test_bymonth_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
| 271 | DECLARE |
---|
| 272 | testme ALIAS FOR $1; |
---|
| 273 | bymonth ALIAS FOR $2; |
---|
| 274 | BEGIN |
---|
| 275 | IF bymonth IS NOT NULL THEN |
---|
| 276 | RETURN ( date_part( 'month', testme) = ANY (bymonth) ); |
---|
| 277 | END IF; |
---|
| 278 | RETURN TRUE; |
---|
| 279 | END; |
---|
| 280 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 281 | |
---|
| 282 | |
---|
| 283 | ------------------------------------------------------------------------------------------------------ |
---|
| 284 | -- Test the day in month of this date against the array of monthdays from the rule |
---|
| 285 | ------------------------------------------------------------------------------------------------------ |
---|
| 286 | CREATE or REPLACE FUNCTION test_bymonthday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
| 287 | DECLARE |
---|
| 288 | testme ALIAS FOR $1; |
---|
| 289 | bymonthday ALIAS FOR $2; |
---|
| 290 | BEGIN |
---|
| 291 | IF bymonthday IS NOT NULL THEN |
---|
| 292 | RETURN ( date_part( 'day', testme) = ANY (bymonthday) ); |
---|
| 293 | END IF; |
---|
| 294 | RETURN TRUE; |
---|
| 295 | END; |
---|
| 296 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 297 | |
---|
| 298 | |
---|
| 299 | ------------------------------------------------------------------------------------------------------ |
---|
| 300 | -- Test the day in year of this date against the array of yeardays from the rule |
---|
| 301 | ------------------------------------------------------------------------------------------------------ |
---|
| 302 | CREATE or REPLACE FUNCTION test_byyearday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
| 303 | DECLARE |
---|
| 304 | testme ALIAS FOR $1; |
---|
| 305 | byyearday ALIAS FOR $2; |
---|
| 306 | BEGIN |
---|
| 307 | IF byyearday IS NOT NULL THEN |
---|
| 308 | RETURN ( date_part( 'doy', testme) = ANY (byyearday) ); |
---|
| 309 | END IF; |
---|
| 310 | RETURN TRUE; |
---|
| 311 | END; |
---|
| 312 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 313 | |
---|
| 314 | |
---|
| 315 | ------------------------------------------------------------------------------------------------------ |
---|
| 316 | -- Given a cursor into a set, process the set returning the subset matching the BYSETPOS |
---|
| 317 | ------------------------------------------------------------------------------------------------------ |
---|
| 318 | CREATE or REPLACE FUNCTION rrule_bysetpos_filter( REFCURSOR, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 319 | DECLARE |
---|
| 320 | curse ALIAS FOR $1; |
---|
| 321 | bysetpos ALIAS FOR $2; |
---|
| 322 | valid_date TIMESTAMP WITH TIME ZONE; |
---|
| 323 | i INT; |
---|
| 324 | setsize INT; |
---|
| 325 | ourset TIMESTAMP WITH TIME ZONE[]; |
---|
| 326 | BEGIN |
---|
| 327 | |
---|
| 328 | IF bysetpos IS NULL THEN |
---|
| 329 | LOOP |
---|
| 330 | FETCH curse INTO valid_date; |
---|
| 331 | EXIT WHEN NOT FOUND; |
---|
| 332 | RETURN NEXT valid_date; |
---|
| 333 | END LOOP; |
---|
| 334 | ELSE |
---|
| 335 | setsize := 0; |
---|
| 336 | FOR i IN 1..366 LOOP |
---|
| 337 | FETCH curse INTO valid_date; |
---|
| 338 | EXIT WHEN NOT FOUND; |
---|
| 339 | ourset[i] := valid_date; |
---|
| 340 | setsize = setsize + 1; |
---|
| 341 | END LOOP; |
---|
| 342 | -- RAISE NOTICE 'We have % in our set.', setsize; |
---|
| 343 | FOR i IN 1..366 LOOP |
---|
| 344 | EXIT WHEN bysetpos[i] IS NULL; |
---|
| 345 | IF bysetpos[i] > 0 THEN |
---|
| 346 | valid_date := ourset[bysetpos[i]]; |
---|
| 347 | -- RAISE NOTICE 'Extracted % from position %.', valid_date, bysetpos[i]; |
---|
| 348 | ELSE |
---|
| 349 | valid_date := ourset[ setsize + bysetpos[i] + 1 ]; |
---|
| 350 | -- RAISE NOTICE 'Extracted % from position (% % + 1) = %.', valid_date, setsize, bysetpos[i], setsize + bysetpos[i] + 1; |
---|
| 351 | END IF; |
---|
| 352 | IF valid_date IS NOT NULL THEN |
---|
| 353 | RETURN NEXT valid_date; |
---|
| 354 | END IF; |
---|
| 355 | END LOOP; |
---|
| 356 | END IF; |
---|
| 357 | CLOSE curse; |
---|
| 358 | END; |
---|
| 359 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 360 | |
---|
| 361 | |
---|
| 362 | ------------------------------------------------------------------------------------------------------ |
---|
| 363 | -- Return another day's worth of events: i.e. one day that matches the criteria, since we don't |
---|
| 364 | -- currently implement sub-day scheduling. |
---|
| 365 | -- |
---|
| 366 | -- This is cheeky: The incrementing by a day is done outside the call, so we either return the |
---|
| 367 | -- empty set (if the input date fails our filters) or we return a set containing the input date. |
---|
| 368 | ------------------------------------------------------------------------------------------------------ |
---|
| 369 | CREATE or REPLACE FUNCTION daily_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 370 | DECLARE |
---|
| 371 | after ALIAS FOR $1; |
---|
| 372 | rrule ALIAS FOR $2; |
---|
| 373 | BEGIN |
---|
| 374 | |
---|
| 375 | IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN |
---|
| 376 | RETURN; |
---|
| 377 | END IF; |
---|
| 378 | |
---|
| 379 | IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN |
---|
| 380 | RETURN; |
---|
| 381 | END IF; |
---|
| 382 | |
---|
| 383 | IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN |
---|
| 384 | RETURN; |
---|
| 385 | END IF; |
---|
| 386 | |
---|
| 387 | IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN |
---|
| 388 | RETURN; |
---|
| 389 | END IF; |
---|
| 390 | |
---|
| 391 | IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN |
---|
| 392 | RETURN; |
---|
| 393 | END IF; |
---|
| 394 | |
---|
| 395 | -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial |
---|
| 396 | RETURN NEXT after; |
---|
| 397 | |
---|
| 398 | END; |
---|
| 399 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 400 | |
---|
| 401 | |
---|
| 402 | ------------------------------------------------------------------------------------------------------ |
---|
| 403 | -- Return another week's worth of events |
---|
| 404 | -- |
---|
| 405 | -- Doesn't handle truly obscure and unlikely stuff like BYWEEKNO=5;BYMONTH=1;BYDAY=WE,TH,FR;BYSETPOS=-2 |
---|
| 406 | -- Imagine that. |
---|
| 407 | ------------------------------------------------------------------------------------------------------ |
---|
| 408 | CREATE or REPLACE FUNCTION weekly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 409 | DECLARE |
---|
| 410 | after ALIAS FOR $1; |
---|
| 411 | rrule ALIAS FOR $2; |
---|
| 412 | rowvar RECORD; |
---|
| 413 | curse REFCURSOR; |
---|
| 414 | weekno INT; |
---|
| 415 | i INT; |
---|
| 416 | BEGIN |
---|
| 417 | |
---|
| 418 | IF rrule.byweekno IS NOT NULL THEN |
---|
| 419 | weekno := date_part('week',after); |
---|
| 420 | IF NOT weekno = ANY ( rrule.byweekno ) THEN |
---|
| 421 | RETURN; |
---|
| 422 | END IF; |
---|
| 423 | END IF; |
---|
| 424 | |
---|
| 425 | OPEN curse FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r; |
---|
| 426 | FOR rowvar IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP |
---|
| 427 | RETURN NEXT rowvar.d; |
---|
| 428 | END LOOP; |
---|
| 429 | |
---|
| 430 | END; |
---|
| 431 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 432 | |
---|
| 433 | |
---|
| 434 | ------------------------------------------------------------------------------------------------------ |
---|
| 435 | -- Return another month's worth of events |
---|
| 436 | ------------------------------------------------------------------------------------------------------ |
---|
| 437 | CREATE or REPLACE FUNCTION monthly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 438 | DECLARE |
---|
| 439 | after ALIAS FOR $1; |
---|
| 440 | rrule ALIAS FOR $2; |
---|
| 441 | rowvar RECORD; |
---|
| 442 | curse REFCURSOR; |
---|
| 443 | setpos INT; |
---|
| 444 | i INT; |
---|
| 445 | BEGIN |
---|
| 446 | |
---|
| 447 | /** |
---|
| 448 | * Need to investigate whether it is legal to set both of these, and whether |
---|
| 449 | * we are correct to UNION the results, or whether we should INTERSECT them. |
---|
| 450 | * So at this point, we refer to the specification, which grants us this |
---|
| 451 | * wonderfully enlightening vision: |
---|
| 452 | * |
---|
| 453 | * If multiple BYxxx rule parts are specified, then after evaluating the |
---|
| 454 | * specified FREQ and INTERVAL rule parts, the BYxxx rule parts are |
---|
| 455 | * applied to the current set of evaluated occurrences in the following |
---|
| 456 | * order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, |
---|
| 457 | * BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated. |
---|
| 458 | * |
---|
| 459 | * My guess is that this means 'INTERSECT' |
---|
| 460 | */ |
---|
| 461 | IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN |
---|
| 462 | OPEN curse FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r |
---|
| 463 | INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r |
---|
| 464 | ORDER BY 1; |
---|
| 465 | ELSIF rrule.bymonthday IS NOT NULL THEN |
---|
| 466 | OPEN curse FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1; |
---|
| 467 | ELSE |
---|
| 468 | OPEN curse FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1; |
---|
| 469 | END IF; |
---|
| 470 | |
---|
| 471 | FOR rowvar IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP |
---|
| 472 | RETURN NEXT rowvar.d; |
---|
| 473 | END LOOP; |
---|
| 474 | |
---|
| 475 | END; |
---|
| 476 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 477 | |
---|
| 478 | |
---|
| 479 | ------------------------------------------------------------------------------------------------------ |
---|
| 480 | -- If this is YEARLY;BYMONTH, abuse MONTHLY;BYMONTH for everything except the BYSETPOS |
---|
| 481 | -- FIXME: it is wrong to abuse MONTHLY;BYMONTH in this way. We should write YEARLY;BYMONTH properly. |
---|
| 482 | ------------------------------------------------------------------------------------------------------ |
---|
| 483 | CREATE or REPLACE FUNCTION rrule_yearly_bymonth_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 484 | DECLARE |
---|
| 485 | after ALIAS FOR $1; |
---|
| 486 | rrule ALIAS FOR $2; |
---|
| 487 | current_base TIMESTAMP WITH TIME ZONE; |
---|
| 488 | rowvar RECORD; |
---|
| 489 | rr rrule_parts; |
---|
| 490 | i INT; |
---|
| 491 | BEGIN |
---|
| 492 | |
---|
| 493 | IF rrule.bymonth IS NOT NULL THEN |
---|
| 494 | -- Ensure we don't pass BYSETPOS down |
---|
| 495 | rr := rrule; |
---|
| 496 | rr.bysetpos := NULL; |
---|
| 497 | FOR i IN 1..12 LOOP |
---|
| 498 | EXIT WHEN rr.bymonth[i] IS NULL; |
---|
| 499 | current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval; |
---|
| 500 | FOR rowvar IN SELECT d FROM monthly_set(current_base,rr) d LOOP |
---|
| 501 | RETURN NEXT rowvar.d; |
---|
| 502 | END LOOP; |
---|
| 503 | END LOOP; |
---|
| 504 | ELSE |
---|
| 505 | -- We don't yet implement byweekno, byblah |
---|
| 506 | RETURN NEXT after; |
---|
| 507 | END IF; |
---|
| 508 | |
---|
| 509 | END; |
---|
| 510 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 511 | |
---|
| 512 | |
---|
| 513 | ------------------------------------------------------------------------------------------------------ |
---|
| 514 | -- Return another year's worth of events |
---|
| 515 | ------------------------------------------------------------------------------------------------------ |
---|
| 516 | CREATE or REPLACE FUNCTION yearly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 517 | DECLARE |
---|
| 518 | after ALIAS FOR $1; |
---|
| 519 | rrule ALIAS FOR $2; |
---|
| 520 | current_base TIMESTAMP WITH TIME ZONE; |
---|
| 521 | rowvar RECORD; |
---|
| 522 | curse REFCURSOR; |
---|
| 523 | curser REFCURSOR; |
---|
| 524 | i INT; |
---|
| 525 | BEGIN |
---|
| 526 | |
---|
| 527 | IF rrule.bymonth IS NOT NULL THEN |
---|
| 528 | OPEN curse FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r; |
---|
| 529 | FOR rowvar IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP |
---|
| 530 | current_base := date_trunc( 'day', rowvar.d ) + (after::time)::interval; |
---|
| 531 | RETURN NEXT current_base; |
---|
| 532 | END LOOP; |
---|
| 533 | ELSE |
---|
| 534 | -- We don't yet implement byweekno, byblah |
---|
| 535 | RETURN NEXT after; |
---|
| 536 | END IF; |
---|
| 537 | END; |
---|
| 538 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 539 | |
---|
| 540 | |
---|
| 541 | ------------------------------------------------------------------------------------------------------ |
---|
| 542 | -- Combine all of that into something which we can use to generate a series from an arbitrary DTSTART/RRULE |
---|
| 543 | ------------------------------------------------------------------------------------------------------ |
---|
| 544 | CREATE or REPLACE FUNCTION rrule_event_instances_range( TIMESTAMP WITH TIME ZONE, TEXT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INT ) |
---|
| 545 | RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 546 | DECLARE |
---|
| 547 | basedate ALIAS FOR $1; |
---|
| 548 | repeatrule ALIAS FOR $2; |
---|
| 549 | mindate ALIAS FOR $3; |
---|
| 550 | maxdate ALIAS FOR $4; |
---|
| 551 | max_count ALIAS FOR $5; |
---|
| 552 | loopmax INT; |
---|
| 553 | loopcount INT; |
---|
| 554 | base_day TIMESTAMP WITH TIME ZONE; |
---|
| 555 | current_base TIMESTAMP WITH TIME ZONE; |
---|
| 556 | current TIMESTAMP WITH TIME ZONE; |
---|
| 557 | rowvar RECORD; |
---|
| 558 | rrule rrule_parts%ROWTYPE; |
---|
| 559 | BEGIN |
---|
| 560 | loopcount := 0; |
---|
| 561 | |
---|
| 562 | SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule ); |
---|
| 563 | IF rrule.count IS NOT NULL THEN |
---|
| 564 | loopmax := rrule.count; |
---|
| 565 | ELSE |
---|
| 566 | loopmax := max_count; |
---|
| 567 | END IF; |
---|
| 568 | |
---|
| 569 | current_base := basedate; |
---|
| 570 | base_day := date_trunc('day',basedate); |
---|
| 571 | WHILE loopcount < loopmax AND current_base <= maxdate LOOP |
---|
| 572 | IF rrule.freq = 'DAILY' THEN |
---|
| 573 | FOR rowvar IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP |
---|
| 574 | current := rowvar.d; |
---|
| 575 | -- IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN |
---|
| 576 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
| 577 | IF current >= mindate THEN |
---|
| 578 | RETURN NEXT current; |
---|
| 579 | END IF; |
---|
| 580 | loopcount := loopcount + 1; |
---|
| 581 | EXIT WHEN loopcount >= loopmax; |
---|
| 582 | -- END IF; |
---|
| 583 | END LOOP; |
---|
| 584 | current_base := current_base + (rrule.interval::text || ' days')::interval; |
---|
| 585 | ELSIF rrule.freq = 'WEEKLY' THEN |
---|
| 586 | FOR rowvar IN SELECT d FROM weekly_set(current_base,rrule) d WHERE d >= base_day LOOP |
---|
| 587 | current := rowvar.d; |
---|
| 588 | IF test_byyearday_rule(current,rrule.byyearday) |
---|
| 589 | AND test_bymonthday_rule(current,rrule.bymonthday) |
---|
| 590 | AND test_bymonth_rule(current,rrule.bymonth) |
---|
| 591 | THEN |
---|
| 592 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
| 593 | IF current >= mindate THEN |
---|
| 594 | RETURN NEXT current; |
---|
| 595 | END IF; |
---|
| 596 | loopcount := loopcount + 1; |
---|
| 597 | EXIT WHEN loopcount >= loopmax; |
---|
| 598 | END IF; |
---|
| 599 | END LOOP; |
---|
| 600 | current_base := current_base + (rrule.interval::text || ' weeks')::interval; |
---|
| 601 | ELSIF rrule.freq = 'MONTHLY' THEN |
---|
| 602 | FOR rowvar IN SELECT d FROM monthly_set(current_base,rrule) d WHERE d >= base_day LOOP |
---|
| 603 | current := rowvar.d; |
---|
| 604 | -- IF /* test_byyearday_rule(current,rrule.byyearday) |
---|
| 605 | -- AND */ test_bymonth_rule(current,rrule.bymonth) |
---|
| 606 | -- THEN |
---|
| 607 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
| 608 | IF current >= mindate THEN |
---|
| 609 | RETURN NEXT current; |
---|
| 610 | END IF; |
---|
| 611 | loopcount := loopcount + 1; |
---|
| 612 | EXIT WHEN loopcount >= loopmax; |
---|
| 613 | -- END IF; |
---|
| 614 | END LOOP; |
---|
| 615 | current_base := current_base + (rrule.interval::text || ' months')::interval; |
---|
| 616 | ELSIF rrule.freq = 'YEARLY' THEN |
---|
| 617 | FOR rowvar IN SELECT d FROM yearly_set(current_base,rrule) d WHERE d >= base_day LOOP |
---|
| 618 | current := rowvar.d; |
---|
| 619 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
| 620 | IF current >= mindate THEN |
---|
| 621 | RETURN NEXT current; |
---|
| 622 | END IF; |
---|
| 623 | loopcount := loopcount + 1; |
---|
| 624 | EXIT WHEN loopcount >= loopmax; |
---|
| 625 | END LOOP; |
---|
| 626 | current_base := current_base + (rrule.interval::text || ' years')::interval; |
---|
| 627 | ELSE |
---|
| 628 | RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq; |
---|
| 629 | RETURN; |
---|
| 630 | END IF; |
---|
| 631 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
| 632 | END LOOP; |
---|
| 633 | -- RETURN QUERY; |
---|
| 634 | END; |
---|
| 635 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 636 | |
---|
| 637 | |
---|
| 638 | ------------------------------------------------------------------------------------------------------ |
---|
| 639 | -- A simplified DTSTART/RRULE only interface which applies some performance assumptions |
---|
| 640 | ------------------------------------------------------------------------------------------------------ |
---|
| 641 | CREATE or REPLACE FUNCTION event_instances( TIMESTAMP WITH TIME ZONE, TEXT ) |
---|
| 642 | RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
| 643 | DECLARE |
---|
| 644 | basedate ALIAS FOR $1; |
---|
| 645 | repeatrule ALIAS FOR $2; |
---|
| 646 | maxdate TIMESTAMP WITH TIME ZONE; |
---|
| 647 | rowvar RECORD; |
---|
| 648 | BEGIN |
---|
| 649 | maxdate := current_date + '10 years'::interval; |
---|
| 650 | FOR rowvar IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP |
---|
| 651 | RETURN NEXT rowvar.d; |
---|
| 652 | END LOOP; |
---|
| 653 | END; |
---|
| 654 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
| 655 | |
---|
| 656 | |
---|
| 657 | ------------------------------------------------------------------------------------------------------ |
---|
| 658 | -- In most cases we just want to know if there *is* an event overlapping the range, so we have a |
---|
| 659 | -- specific function for that. Note that this is *not* strict, and can be called with NULLs. |
---|
| 660 | ------------------------------------------------------------------------------------------------------ |
---|
| 661 | CREATE or REPLACE FUNCTION rrule_event_overlaps( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE ) |
---|
| 662 | RETURNS BOOLEAN AS $$ |
---|
| 663 | DECLARE |
---|
| 664 | dtstart ALIAS FOR $1; |
---|
| 665 | dtend ALIAS FOR $2; |
---|
| 666 | repeatrule ALIAS FOR $3; |
---|
| 667 | in_mindate ALIAS FOR $4; |
---|
| 668 | in_maxdate ALIAS FOR $5; |
---|
| 669 | base_date TIMESTAMP WITH TIME ZONE; |
---|
| 670 | mindate TIMESTAMP WITH TIME ZONE; |
---|
| 671 | maxdate TIMESTAMP WITH TIME ZONE; |
---|
| 672 | BEGIN |
---|
| 673 | |
---|
| 674 | IF dtstart IS NULL THEN |
---|
| 675 | RETURN NULL; |
---|
| 676 | END IF; |
---|
| 677 | IF dtend IS NULL THEN |
---|
| 678 | base_date := dtstart; |
---|
| 679 | ELSE |
---|
| 680 | base_date := dtend; |
---|
| 681 | END IF; |
---|
| 682 | |
---|
| 683 | IF in_mindate IS NULL THEN |
---|
| 684 | mindate := current_date - '10 years'::interval; |
---|
| 685 | ELSE |
---|
| 686 | mindate := in_mindate; |
---|
| 687 | END IF; |
---|
| 688 | |
---|
| 689 | IF in_maxdate IS NULL THEN |
---|
| 690 | maxdate := current_date + '10 years'::interval; |
---|
| 691 | ELSE |
---|
| 692 | -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range. |
---|
| 693 | maxdate := in_maxdate + (base_date - dtstart); |
---|
| 694 | END IF; |
---|
| 695 | |
---|
| 696 | IF repeatrule IS NULL THEN |
---|
| 697 | RETURN (dtstart <= maxdate AND base_date >= mindate); |
---|
| 698 | END IF; |
---|
| 699 | |
---|
| 700 | SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1; |
---|
| 701 | RETURN FOUND; |
---|
| 702 | |
---|
| 703 | END; |
---|
| 704 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
| 705 | |
---|
| 706 | |
---|
| 707 | CREATE or REPLACE FUNCTION icalendar_interval_to_SQL( TEXT ) RETURNS interval AS $function$ |
---|
| 708 | SELECT CASE WHEN substring($1,1,1) = '-' THEN -1 ELSE 1 END * regexp_replace( regexp_replace($1, '[PT-]', '', 'g'), '([A-Z])', E'\\1 ', 'g')::interval; |
---|
| 709 | $function$ LANGUAGE 'SQL' IMMUTABLE STRICT; |
---|