Skip to content

Commit

Permalink
Fix tests
Browse files Browse the repository at this point in the history
  • Loading branch information
@volkanunsal
volkanunsal committed May 6, 2019
1 parent 7391921 commit 77401dd
Show file tree
Hide file tree
Showing 5 changed files with 190 additions and 100 deletions.
184 changes: 143 additions & 41 deletions postgres-rrule.sql
View file
Original file line number Diff line number Diff line change
Expand Up @@ -9,10 +9,13 @@ CREATE TYPE _rrule.FREQ AS ENUM (
'YEARLY',
'MONTHLY',
'WEEKLY',
'DAILY',
'HOURLY',
'MINUTELY',
'SECONDLY'
'DAILY'

-- NOTE: Disabled due to performance concerns.

-- 'HOURLY',
-- 'MINUTELY',
-- 'SECONDLY'
);


Expand Down Expand Up @@ -187,7 +190,7 @@ RETURNS BOOLEAN AS $$
SELECT _rrule.interval_contains(
_rrule.build_interval($1),
_rrule.build_interval($2)
) AND $1."wkst" = $2."wkst";
) AND COALESCE($1."wkst" = $2."wkst", true);
$$ LANGUAGE SQL IMMUTABLE STRICT;


Expand All @@ -199,51 +202,154 @@ $$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION _rrule.until("rrule" _rrule.RRULE, "dtstart" TIMESTAMP)
RETURNS TIMESTAMP AS $$

SELECT min("until")
FROM (
SELECT "rrule"."until"
UNION
SELECT "dtstart" + _rrule.build_interval("rrule"."interval", "rrule"."freq") * "rrule"."count" AS "until"
SELECT "dtstart" + _rrule.build_interval("rrule"."interval", "rrule"."freq") * COALESCE("rrule"."count", CASE WHEN "rrule"."until" IS NOT NULL THEN NULL ELSE 1 END) AS "until"
) "until" GROUP BY ();

$$ LANGUAGE SQL IMMUTABLE STRICT;
COMMENT ON FUNCTION _rrule.until(_rrule.RRULE, TIMESTAMP) IS 'The calculated "until"" timestamp for the given rrule+dtstart';

-- STARTS
--
CREATE OR REPLACE FUNCTION generate_recurrences(freq _rrule.FREQ, start_date TIMESTAMP,
end_date TIMESTAMP) RETURNS setof TIMESTAMP AS $$
DECLARE
next_date TIMESTAMP := start_date;
duration INTERVAL;
day INTERVAL;
c TEXT;
BEGIN
IF freq = 'WEEKLY' THEN
duration := '1 week'::interval;
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
END LOOP;
ELSIF freq = 'DAILY' THEN
duration := '1 day'::interval;
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
END LOOP;
ELSIF freq = 'MONTHLY' THEN
duration := '27 days'::interval;
day := '1 day'::interval;
c := to_char(start_date, 'DD');
WHILE next_date <= end_date LOOP
RETURN NEXT next_date;
next_date := next_date + duration;
WHILE to_char(next_date, 'DD') <> c LOOP
next_date := next_date + day;
END LOOP;
END LOOP;
ELSE
RAISE EXCEPTION 'Recurrence % not supported', freq::text USING HINT = 'Please check your recurrence';
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION _rrule.to_DAY("ts" TIMESTAMP) RETURNS _rrule.DAY AS $$
SELECT CAST(CASE to_char("ts", 'DY')
WHEN 'MON' THEN 'MO'
WHEN 'TUE' THEN 'TU'
WHEN 'WED' THEN 'WE'
WHEN 'THU' THEN 'TH'
WHEN 'FRI' THEN 'FR'
WHEN 'SAT' THEN 'SA'
WHEN 'SUN' THEN 'SU'
END as _rrule.DAY);
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION num_days(year integer, month integer) RETURNS integer AS $$
SELECT DATE_PART('days',
DATE_TRUNC('month', make_timestamp(year, month, 1, 1, 1, 1))
+ '1 MONTH'::INTERVAL
- '1 DAY'::INTERVAL
)::integer
$$ LANGUAGE SQL IMMUTABLE;

-- Given a start time, returns a set of all possible start values for a recurrence rule.
-- For example, a YEARLY rule that repeats on first and third month have 2 start values.

-- TODO: If we have a bymonthday, but no bymonth, we need to expand to all months.
-- TODO: If we have a byday, we need to expand to all days of the weeks.


CREATE OR REPLACE FUNCTION _rrule.all_starts(
"rrule" _rrule.RRULE,
"dtstart" TIMESTAMP
) RETURNS SETOF TIMESTAMP AS $$
DECLARE
months int[];
hour int := EXTRACT(HOUR FROM "dtstart")::integer;
minute int := EXTRACT(MINUTE FROM "dtstart")::integer;
second double precision := EXTRACT(SECOND FROM "dtstart");
day int := EXTRACT(DAY FROM "dtstart")::integer;
month int := EXTRACT(MONTH FROM "dtstart")::integer;
year int := EXTRACT(YEAR FROM "dtstart")::integer;
year_start timestamp := make_timestamp(year, 1, 1, hour, minute, second);
year_end timestamp := make_timestamp(year, 12, 31, hour, minute, second);
interv INTERVAL := build_interval("rrule");
BEGIN
RETURN QUERY WITH
"year" as (SELECT EXTRACT(YEAR FROM "dtstart")::integer AS "year"),
A10 as (
SELECT
make_timestamp(
"year"."year",
COALESCE("bymonth", EXTRACT(MONTH FROM "dtstart")::integer),
COALESCE("bymonthday", EXTRACT(DAY FROM "dtstart")::integer),
COALESCE("byhour", EXTRACT(HOUR FROM "dtstart")::integer),
COALESCE("byminute", EXTRACT(MINUTE FROM "dtstart")::integer),
COALESCE("bysecond", EXTRACT(SECOND FROM "dtstart"))
COALESCE("bymonth", month),
COALESCE("bymonthday", day),
COALESCE("byhour", hour),
COALESCE("byminute", minute),
COALESCE("bysecond", second)
) as "ts"
FROM "year"
LEFT OUTER JOIN unnest(("rrule")."bymonth") AS "bymonth" ON (true)
LEFT OUTER JOIN unnest(("rrule")."bymonthday") as "bymonthday" ON (true)
LEFT OUTER JOIN unnest(("rrule")."byhour") AS "byhour" ON (true)
LEFT OUTER JOIN unnest(("rrule")."byminute") AS "byminute" ON (true)
LEFT OUTER JOIN unnest(("rrule")."bysecond") AS "bysecond" ON (true)
),
A11 as (
SELECT DISTINCT "ts"
FROM A10
UNION
SELECT "ts" FROM (
SELECT "ts"
FROM generate_series("dtstart", year_end, INTERVAL '1 day') "ts"
WHERE (
_rrule.to_DAY("ts") = ANY("rrule"."byday")
)
AND "ts" <= ("dtstart" + INTERVAL '7 days')
) as "ts"
UNION
SELECT "ts" FROM (
SELECT "ts"
FROM generate_series("dtstart", year_end, INTERVAL '1 day') "ts"
WHERE (
EXTRACT(DAY FROM "ts") = ANY("rrule"."bymonthday")
)
AND "ts" <= ("dtstart" + INTERVAL '2 months')
) as "ts"
UNION
SELECT "ts" FROM (
SELECT "ts"
FROM generate_series("dtstart", "dtstart" + INTERVAL '1 year', INTERVAL '1 month') "ts"
WHERE (
EXTRACT(MONTH FROM "ts") = ANY("rrule"."bymonth")
)
) as "ts"
)
SELECT DISTINCT "ts"
FROM A11
WHERE (
"rrule"."byday" IS NULL OR _rrule.to_DAY("ts") = ANY("rrule"."byday")
)
AND (
"rrule"."bymonth" IS NULL OR EXTRACT(MONTH FROM "ts") = ANY("rrule"."bymonth")
)
AND (
"rrule"."bymonthday" IS NULL OR EXTRACT(DAY FROM "ts") = ANY("rrule"."bymonthday")
)
SELECT "ts" FROM A10;
ORDER BY "ts";

END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

Expand Down Expand Up @@ -302,7 +408,7 @@ WHERE "freq" IS NOT NULL
-- FREQ=YEARLY required if BYWEEKNO is provided
AND ("freq" = 'YEARLY' OR "byweekno" IS NULL)
-- Limits on FREQ if byyearday is selected
AND ("freq" IN ('YEARLY', 'HOURLY', 'MINUTELY', 'SECONDLY') OR "byyearday" IS NULL)
AND ("freq" IN ('YEARLY') OR "byyearday" IS NULL)
-- FREQ=WEEKLY is invalid when BYMONTHDAY is set
AND ("freq" <> 'WEEKLY' OR "bymonthday" IS NULL)
-- FREQ=DAILY is invalid when BYDAY is set
Expand All @@ -323,7 +429,6 @@ AND (
-- Either UNTIL or COUNT may appear in a 'recur', but
-- UNTIL and COUNT MUST NOT occur in the same 'recur'.
AND ("count" IS NULL OR "until" IS NULL)

AND ("interval" IS NULL OR "interval" > 0);

$$ LANGUAGE SQL IMMUTABLE STRICT;
Expand Down Expand Up @@ -394,11 +499,12 @@ RETURNS SETOF TIMESTAMP AS $$
FULL OUTER JOIN _rrule.build_interval($1) "interval" ON (true)
),
"generated" AS (
SELECT generate_series("start", COALESCE("until"), "interval") "occurrence"
FROM "params" FULL OUTER JOIN "starts" ON (true)
SELECT generate_series("start", "until", "interval") "occurrence"
FROM "params"
FULL OUTER JOIN "starts" ON (true)
),
"ordered" AS (
SELECT "occurrence"
SELECT DISTINCT "occurrence"
FROM "generated"
WHERE "occurrence" >= "dtstart"
ORDER BY "occurrence"
Expand All @@ -411,8 +517,8 @@ RETURNS SETOF TIMESTAMP AS $$
)
SELECT "occurrence"
FROM "tagged"
WHERE "row_number" <= ("rrule")."count"
OR ("rrule")."count" IS NULL
WHERE "row_number" <= "rrule"."count"
OR "rrule"."count" IS NULL
ORDER BY "occurrence";
$$ LANGUAGE SQL STRICT IMMUTABLE;

Expand Down Expand Up @@ -468,21 +574,16 @@ RETURNS SETOF TIMESTAMP AS $$
$$ LANGUAGE SQL STRICT IMMUTABLE;








CREATE OR REPLACE FUNCTION _rrule.first("rrule" _rrule.RRULE, "dtstart" TIMESTAMP)
RETURNS TIMESTAMP AS $$

SELECT "ts"
BEGIN
RETURN (SELECT "ts"
FROM _rrule.all_starts("rrule", "dtstart") "ts"
ORDER BY "ts"
LIMIT 1;

$$ LANGUAGE SQL STRICT IMMUTABLE;
WHERE "ts" >= "dtstart"
ORDER BY "ts" ASC
LIMIT 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION _rrule.first("rrule" TEXT, "dtstart" TIMESTAMP)
RETURNS TIMESTAMP AS $$
Expand All @@ -500,9 +601,10 @@ $$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION _rrule.last("rrule" _rrule.RRULE, "dtstart" TIMESTAMP)
RETURNS TIMESTAMP AS $$
SELECT occurrence
FROM _rrule.occurrences("rrule", "dtstart") occurrence
ORDER BY occurrence DESC LIMIT 1;
SELECT "ts"
FROM _rrule.occurrences("rrule", "dtstart") "ts"
ORDER BY "ts" DESC
LIMIT 1;
$$ LANGUAGE SQL STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION _rrule.last("rrule" TEXT, "dtstart" TIMESTAMP)
Expand Down
64 changes: 27 additions & 37 deletions tests/test_all_starts.sql
View file
Original file line number Diff line number Diff line change
@@ -1,6 +1,6 @@
BEGIN;

SELECT plan(8);
SELECT plan(6);

SET search_path TO public, _rrule;

Expand All @@ -10,34 +10,54 @@ SELECT results_eq(
'RRULE:FREQ=YEARLY'::TEXT::RRULE,
'19970105T083000'::TIMESTAMP
)$$,
'VALUES (''1997-01-05T08:30:00''::TIMESTAMP)',
$$ VALUES
('1997-01-05T08:30:00'::TIMESTAMP)
$$,
'Only one start with no modifiers.'
);

-- 'BYMONTHDAY expands number of starts.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=YEARLY;COUNT=3;BYMONTHDAY=1,3'::TEXT,
'RRULE:FREQ=YEARLY;BYMONTHDAY=1,3'::TEXT,
'1997-09-01T09:00:00'::TIMESTAMP
) $$,
$$ VALUES
('1997-09-01T09:00:00'::TIMESTAMP),
('1997-09-03T09:00:00'::TIMESTAMP)
('1997-09-03T09:00:00'::TIMESTAMP),
('1997-10-01T09:00:00'::TIMESTAMP),
('1997-10-03T09:00:00'::TIMESTAMP),
('1997-11-01T09:00:00'::TIMESTAMP)
$$,
'BYMONTHDAY expands number of starts.'
);

-- 'BYDAY works.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=WEEKLY;BYDAY=TU;COUNT=2'::TEXT,
'2019-05-07T09:00:00'::TIMESTAMP
) $$,
$$ VALUES
('2019-05-07T09:00:00'::TIMESTAMP),
('2019-05-014T09:00:00'::TIMESTAMP)
$$,
'BYDAY works.'
);

-- 'Monthly BYMONTH with one value -> one start.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=MONTHLY;BYMONTH=2'::TEXT::RRULE,
'1997-01-01T00:00:00'::TIMESTAMP
)$$,
$$ VALUES ('1997-02-01T00:00:00'::TIMESTAMP)$$,
$$ VALUES
('1997-02-01T00:00:00'::TIMESTAMP)
$$,
'Monthly BYMONTH with one value -> one start.'
);

-- 'WEEKLY COUNT=1.'
-- -- 'WEEKLY COUNT=1.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=WEEKLY;COUNT=1'::TEXT::RRULE,
Expand All @@ -47,7 +67,7 @@ SELECT results_eq(
'WEEKLY COUNT=1.'
);

-- 'DAILY COUNT=1.'
-- -- 'DAILY COUNT=1.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=DAILY;COUNT=1'::TEXT::RRULE,
Expand All @@ -57,36 +77,6 @@ SELECT results_eq(
'DAILY COUNT=1.'
);

-- 'HOURLY COUNT=1.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=HOURLY;COUNT=1'::TEXT::RRULE,
'1997-01-01T00:00:00'::TIMESTAMP
)$$,
$$ VALUES ('1997-01-01T00:00:00'::TIMESTAMP)$$,
'HOURLY COUNT=1.'
);

-- 'MINUTELY COUNT=1.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=MINUTELY;COUNT=1'::TEXT::RRULE,
'1997-01-01T00:00:00'::TIMESTAMP
)$$,
$$ VALUES ('1997-01-01T00:00:00'::TIMESTAMP)$$,
'MINUTELY COUNT=1.'
);

-- 'SECONDLY COUNT=1.'
SELECT results_eq(
$$ SELECT _rrule.all_starts(
'RRULE:FREQ=SECONDLY;COUNT=1'::TEXT::RRULE,
'1997-01-01T00:00:00'::TIMESTAMP
)$$,
$$ VALUES ('1997-01-01T00:00:00'::TIMESTAMP)$$,
'SECONDLY COUNT=1.'
);

SELECT * FROM finish();


Expand Down
Loading

0 comments on commit 77401dd

Please sign in to comment.