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