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; |
---|