ecrire-ses-tests-unitaires-en-sql

is_public_holiday.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;
$$;

test.sql

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;