source: contrib/davical/dba/rrule_functions.sql @ 3733

Revision 3733, 25.5 KB checked in by gabriel.malheiros, 13 years ago (diff)

Ticket #1541 - <Davical customizado para o Expresso.Utiliza Caldav e CardDav?>
Line 
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.
29DROP TYPE rrule_parts CASCADE;
30CREATE 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
50CREATE or REPLACE FUNCTION parse_rrule_parts( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS rrule_parts AS $$
51DECLARE
52  basedate   ALIAS FOR $1;
53  repeatrule ALIAS FOR $2;
54  result rrule_parts%ROWTYPE;
55  tempstr TEXT;
56BEGIN
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;
77END;
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
82CREATE or REPLACE FUNCTION rrule_month_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
83DECLARE
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[];
94BEGIN
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
163END;
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
168CREATE or REPLACE FUNCTION rrule_month_bymonthday_set( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
169DECLARE
170  in_time ALIAS FOR $1;
171  bymonthday ALIAS FOR $2;
172  month_start TIMESTAMP WITH TIME ZONE;
173  daysinmonth INT;
174  i INT;
175BEGIN
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
197END;
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
202CREATE or REPLACE FUNCTION rrule_week_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
203DECLARE
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;
210BEGIN
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
231END;
232$$ LANGUAGE 'plpgsql' IMMUTABLE;
233
234
235CREATE 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------------------------------------------------------------------------------------------------------
243CREATE or REPLACE FUNCTION test_byday_rule( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS BOOLEAN AS $$
244DECLARE
245  testme ALIAS FOR $1;
246  byday ALIAS FOR $2;
247BEGIN
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;
254END;
255$$ LANGUAGE 'plpgsql' IMMUTABLE;
256
257
258------------------------------------------------------------------------------------------------------
259-- Test the month of this date against the array of months from the rule
260------------------------------------------------------------------------------------------------------
261CREATE or REPLACE FUNCTION test_bymonth_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$
262DECLARE
263  testme ALIAS FOR $1;
264  bymonth ALIAS FOR $2;
265BEGIN
266  IF bymonth IS NOT NULL THEN
267    RETURN ( date_part( 'month', testme) = ANY (bymonth) );
268  END IF;
269  RETURN TRUE;
270END;
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------------------------------------------------------------------------------------------------------
277CREATE or REPLACE FUNCTION test_bymonthday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$
278DECLARE
279  testme ALIAS FOR $1;
280  bymonthday ALIAS FOR $2;
281BEGIN
282  IF bymonthday IS NOT NULL THEN
283    RETURN ( date_part( 'day', testme) = ANY (bymonthday) );
284  END IF;
285  RETURN TRUE;
286END;
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------------------------------------------------------------------------------------------------------
293CREATE or REPLACE FUNCTION test_byyearday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$
294DECLARE
295  testme ALIAS FOR $1;
296  byyearday ALIAS FOR $2;
297BEGIN
298  IF byyearday IS NOT NULL THEN
299    RETURN ( date_part( 'doy', testme) = ANY (byyearday) );
300  END IF;
301  RETURN TRUE;
302END;
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------------------------------------------------------------------------------------------------------
313CREATE or REPLACE FUNCTION rrule_bysetpos_filter( REFCURSOR, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
314DECLARE
315  curse ALIAS FOR $1;
316  bysetpos ALIAS FOR $2;
317  valid_date TIMESTAMP WITH TIME ZONE;
318  i INT;
319BEGIN
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;
342END;
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------------------------------------------------------------------------------------------------------
353CREATE or REPLACE FUNCTION daily_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
354DECLARE
355  after ALIAS FOR $1;
356  rrule ALIAS FOR $2;
357BEGIN
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
382END;
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------------------------------------------------------------------------------------------------------
392CREATE or REPLACE FUNCTION weekly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
393DECLARE
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;
400BEGIN
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
412END;
413$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
414
415
416------------------------------------------------------------------------------------------------------
417-- Return another month's worth of events
418------------------------------------------------------------------------------------------------------
419CREATE or REPLACE FUNCTION monthly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
420DECLARE
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;
427BEGIN
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
455END;
456$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
457
458
459------------------------------------------------------------------------------------------------------
460-- If this is YEARLY;BYMONTH, abuse MONTHLY;BYMONTH for everything except the BYSETPOS
461------------------------------------------------------------------------------------------------------
462CREATE or REPLACE FUNCTION rrule_yearly_bymonth_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
463DECLARE
464  after ALIAS FOR $1;
465  rrule ALIAS FOR $2;
466  current_base TIMESTAMP WITH TIME ZONE;
467  rr rrule_parts;
468  i INT;
469BEGIN
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
485END;
486$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
487
488
489------------------------------------------------------------------------------------------------------
490-- Return another year's worth of events
491------------------------------------------------------------------------------------------------------
492CREATE or REPLACE FUNCTION yearly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
493DECLARE
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;
500BEGIN
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;
512END;
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------------------------------------------------------------------------------------------------------
519CREATE 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 $$
521DECLARE
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;
533BEGIN
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;
612END;
613$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
614
615
616------------------------------------------------------------------------------------------------------
617-- A simplified DTSTART/RRULE only interface which applies some performance assumptions
618------------------------------------------------------------------------------------------------------
619CREATE or REPLACE FUNCTION event_instances( TIMESTAMP WITH TIME ZONE, TEXT )
620                                         RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
621DECLARE
622  basedate ALIAS FOR $1;
623  repeatrule ALIAS FOR $2;
624  maxdate TIMESTAMP WITH TIME ZONE;
625BEGIN
626  maxdate := current_date + '10 years'::interval;
627  RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d;
628END;
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------------------------------------------------------------------------------------------------------
636CREATE 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 $$
638DECLARE
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;
647BEGIN
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
678END;
679$$ LANGUAGE 'plpgsql' IMMUTABLE;
680
681
682-- Create a composite type for the parts of the RRULE.
683DROP TYPE rrule_instance CASCADE;
684CREATE TYPE rrule_instance AS (
685  dtstart TIMESTAMP WITH TIME ZONE,
686  rrule TEXT,
687  instance TIMESTAMP WITH TIME ZONE
688);
689
690CREATE or REPLACE FUNCTION rrule_event_instances( TIMESTAMP WITH TIME ZONE, TEXT )
691                                         RETURNS SETOF rrule_instance AS $$
692DECLARE
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;
698BEGIN
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
709END;
710$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
711
712
713CREATE 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
Note: See TracBrowser for help on using the repository browser.