Skip to content

Instantly share code, notes, and snippets.

@fljdin
Last active August 6, 2020 07:39
Show Gist options
  • Save fljdin/4e4e5257667b3dca7278b05a31751fc3 to your computer and use it in GitHub Desktop.
Save fljdin/4e4e5257667b3dca7278b05a31751fc3 to your computer and use it in GitHub Desktop.
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