ecrire-ses-tests-unitaires-en-sql
-- Type month_day | |
DROP TYPE IF EXISTS month_day CASCADE; | |
CREATE TYPE month_day AS (month int, day int); | |
-- Function easter_date(date) | |
CREATE OR REPLACE FUNCTION easter_date(year int) | |
RETURNS date LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
g integer := year % 19; | |
c integer := year / 100; | |
h integer := (c - c/4 - (8*c+13)/25 + 19*g + 15) % 30; | |
i integer := h - h/28 * (1 - h/28 * (29/(h + 1)) * (21 - g)/11); | |
j integer := (year + year/4 + i + 2 - c + c/4) % 7; | |
l integer := i - j; | |
m integer := 3 + (l + 40)/44; | |
d integer := l + 28 - 31 * (m/4); | |
BEGIN | |
RETURN format('%s-%s-%s', year, m, d); | |
END; | |
$$; | |
-- Function is_public_holiday(date) | |
CREATE OR REPLACE FUNCTION is_public_holiday(day date) | |
RETURNS boolean LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
y int := extract(year from day); | |
m int := extract(month from day); | |
d int := extract(day from day); | |
h month_day; | |
easter date := easter_date(y); | |
holidays month_day[] := array[ | |
(1,1), (5,1), (5,8), (7,14), | |
(8,15), (11,1), (11,11), (12,25) | |
]; | |
BEGIN | |
FOR h IN ( | |
SELECT extract(month from easter+i) "month", | |
extract(day from easter+i) "day" | |
FROM unnest(array[1, 39, 50]) i | |
) LOOP | |
holidays := array_append(holidays, h); | |
END LOOP; | |
RETURN (m,d) = ANY (holidays); | |
END; | |
$$; |
BEGIN; | |
SELECT plan(34); | |
-- Contrôler la présence et bonne définition d'un type | |
SELECT has_type('month_day'); | |
SELECT col_type_is('month_day', 'month', 'integer'); | |
SELECT col_type_is('month_day', 'day', 'integer'); | |
SELECT has_function( | |
'is_public_holiday', | |
array[ 'date' ], | |
'Function is_public_holiday(date) should exist' | |
); | |
SELECT has_function( | |
'easter_date', | |
array[ 'int' ], | |
'Function easter_date(int) should exist' | |
); | |
SELECT is( | |
is_public_holiday('2020-05-01'::date), | |
true, | |
'2020-05-01 is a public holiday' | |
); | |
SELECT is( | |
is_public_holiday('2020-05-12'::date), | |
false, | |
'2020-05-12 is not a public holiday' | |
); | |
SELECT is( | |
is_public_holiday('2020-01-01'::date), | |
true, | |
'2020-01-01 is a public holiday' | |
); | |
SELECT is( | |
is_public_holiday(x::date), | |
true, | |
format('%s is as public holiday', x) | |
) FROM unnest(array[ | |
'2020-05-08', '2020-07-14', '2020-08-15', | |
'2020-11-01', '2020-11-11', '2020-12-25' | |
]) x; | |
SELECT is( | |
is_public_holiday(x::date), | |
true, | |
format('%s is an easter monday', x) | |
) FROM unnest(array[ | |
'1931-04-06', '1945-04-02', '1968-04-15', | |
'1989-03-27', '2000-04-24', '2020-04-13' | |
]) x; | |
SELECT is( | |
is_public_holiday(x::date), | |
true, | |
format('%s is an ascension day', x) | |
) FROM unnest(array[ | |
'1921-05-05', '1940-05-02', '1960-05-26', | |
'1998-05-21', '2011-06-02', '2020-05-21' | |
]) x; | |
SELECT is( | |
is_public_holiday(x::date), | |
true, | |
format('%s is pentecost', x) | |
) FROM unnest(array[ | |
'1910-05-16', '1928-05-28', '1955-05-30', | |
'1984-06-11', '2003-06-09', '2020-06-01' | |
]) x; | |
-- Contrôler qu'une contrainte est bien définie sur une table | |
CREATE TABLE t ( | |
id int, task varchar, | |
planned date check (not is_public_holiday(planned)) | |
); | |
SELECT col_has_check('t', 'planned'); | |
-- Contrôler qu'une erreur d'intégrité est bien levée | |
SELECT throws_ok( | |
$$insert into t values (1, 'travailler', '2020-05-21')$$, | |
23514 -- check_violation errcode | |
); | |
SELECT * FROM finish(); | |
ROLLBACK; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment