ptr128 30 дек 2023 в 20:48

Производственный календарь в PostgreSQL

Простой

6 мин

13K

Туториал

Новый Год уже совсем на носу, а значит нужен свежий производственный календарь в базе данных PostgreSQL. Но как совершенно обленившийся IT-шник, заводить его руками не хочется. Хочется, чтобы вызовом одной функции он сразу появился. Ну а уж из этой функции можно его сохранить в табличку и спокойно использовать до следующего Нового Года. А тогда опять просто вызвать функцию и с чистой совестью отрапортовать о выполненной работе. Цель статьи - показать возможности COPY ... FROM PROGRAM и простейшие приемы парсинга XML в PostgreSQL.

Для начала пришлось поискать, где же его взять в наиболее удобном для обработки виде. Нашел!

Ставить какие-либо расширения на PostgreSQL не хотелось, поэтому ограничился прямым вызовом wget через COPY. Для этого сначала создаем временную таблицу:

DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (
  ID  serial PRIMARY KEY,
  res text NOT NULL);

ID нам нужен исключительно для сохранения порядка строк, полученных от wget. Теперь мы вполне можем заполнить эту табличку и даже посмотреть на результат:

COPY tmp_tmp (res) FROM PROGRAM
  '/usr/bin/wget -qO - https://xmlcalendar.ru/data/ru/2024/calendar.xml'
  WITH (FORMAT text);
SELECT res FROM tmp_tmp ORDER BY ID;

Результат запроса

<?xml version="1.0" encoding="UTF-8"?>
<calendar year="2024" lang="ru" date="2023.09.30">
    <holidays>
        <holiday id="1" title="Новогодние каникулы"/>
        <holiday id="2" title="Рождество Христово"/>
        <holiday id="3" title="День защитника Отечества"/>
        <holiday id="4" title="Международный женский день"/>
        <holiday id="5" title="Праздник Весны и Труда"/>
        <holiday id="6" title="День Победы"/>
        <holiday id="7" title="День России"/>
        <holiday id="8" title="День народного единства"/>
    </holidays>
    <days>
        <day d="01.01" t="1" h="1"/>
        <day d="01.02" t="1" h="1"/>
        <day d="01.03" t="1" h="1"/>
        <day d="01.04" t="1" h="1"/>
        <day d="01.05" t="1" h="1"/>
        <day d="01.06" t="1" h="1"/>
        <day d="01.07" t="1" h="2"/>
        <day d="01.08" t="1" h="1"/>
        <day d="02.22" t="2"/>
        <day d="02.23" t="1" h="3"/>
        <day d="03.07" t="2"/>
        <day d="03.08" t="1" h="4"/>
        <day d="04.27" t="3" />
        <day d="04.29" t="1" f="04.27"/>
        <day d="04.30" t="1" f="11.02"/>
        <day d="05.01" t="1" h="5"/>
        <day d="05.08" t="2"/>
        <day d="05.09" t="1" h="6"/>
        <day d="05.10" t="1" f="01.06"/>
        <day d="06.11" t="2"/>
        <day d="06.12" t="1" h="7"/>
        <day d="11.02" t="2"/>
        <day d="11.04" t="1" h="8"/>
        <day d="12.28" t="3"/>
        <day d="12.30" t="1" f="12.28"/>
        <day d="12.31" t="1" f="01.07"/>
    </days>
</calendar>

Теперь нам нужно из полученных текстовых строк получить XML

SELECT string_agg(res,'' ORDER BY ID)::xml AS res
FROM tmp_tmp

Для проверки при помощи xmltable() распарсим заголовочную строку вида <calendar year="2024" lang="ru" date="2023.09.30">, содержащую год, страну, для которой этот календарь, и дату его последнего изменения.

WITH CTE AS (
  SELECT string_agg(res,'' ORDER BY ID)::xml AS res
  FROM tmp_tmp ),
CheckYear AS (
  SELECT Y.for_year, Y.for_country, Y.create_date
  FROM CTE C
  CROSS JOIN xmltable('//calendar' PASSING C.res COLUMNS
      for_year smallint PATH '@year',
      for_country varchar PATH '@lang',
      create_date date PATH '@date') Y
  WHERE Y.for_year=2024::smallint )
SELECT * FROM CheckYear

В дальнейшем я буду просто использовать имена общих табличных выражений, которые уже показал, не повторяя их код.

Теперь распарсим таблицу праздников. Она содержит только внутренний числовой идентификатор праздника и его полное наименование в строках вида <holiday id="6" title="День Победы"/>

WITH [...]
Holidays AS (  
  SELECT H.id, H.title
  FROM CheckYear Y
  CROSS JOIN CTE C
  CROSS JOIN xmltable('//calendar/holidays/holiday' PASSING C.res COLUMNS
      id smallint PATH '@id',
      title varchar PATH '@title') H )
SELECT * FROM Holidays;

Затем распарсим основную часть, содержащую строки вида <day d="03.08" t="1" h="4"/> или <day d="04.29" t="1" f="04.27"/>. Тут требуются пояснения. Под тегом d скрывается дата в формате ММ.ДД. Тег t определяет тип записи: 1 - выходной день, 2 - рабочий и сокращенный (может быть использован для любого дня недели), 3 - рабочий день (суббота/воскресенье). Тег h является ссылкой на идентификатор праздника из предыдущего запроса. А тег f - дата с которой был перенесен выходной день тоже в формате ММ.ДД. При этом суббота и воскресенье считаются выходными, если нет тегов day с атрибутом t=2 и t=3 за этот день.

WITH [...]
SpecialDays AS (  
  SELECT ('2024-'
      ||left(D.d,2)||'-'
      ||right(D.d,2))::date AS special_date,
    CASE WHEN D.t=1 THEN 'Holiday'
         WHEN D.t=2 THEN 'Shortened'
         WHEN D.t=3 THEN 'Working'
         ELSE NULL END AS day_type,
    ('2024-'
      ||left(D.f,2)||'-'
      ||right(D.f,2))::date AS from_date, 
    H.title AS holiday_name
  FROM CheckYear Y
  CROSS JOIN CTE C
  CROSS JOIN xmltable('//calendar/days/day' PASSING C.res COLUMNS
      d varchar(5) PATH '@d',
      t smallint PATH '@t',
      h smallint PATH '@h',
      f varchar(5) PATH '@f') D  
  LEFT JOIN Holidays H ON H.id=D.h )
SELECT * FROM SpecialDays;

Для наглядности я заменил числовые типы на краткое их строковое описание. Даты же из формата MM.ДД преобразовал в формат ISO ГГГГ-ММ-ДД, а затем уже в тип date.

Теперь осталось только сгенерировать календарь за год и для каждого дня указать количество рабочих часов для 40-часовой рабочей недели.

  SELECT C.d::date AS pk_date,
    CASE WHEN S.day_type='Shortened' THEN 7
         WHEN S.day_type='Working' THEN 8
         WHEN S.day_type='Holiday' THEN 0
         WHEN extract(DOW FROM C.d) IN (0,6) THEN 0
         ELSE 8 END::smallint AS working_hours,
    S.from_date, S.holiday_name
  FROM generate_series(('2024-01-01')::timestamp,
         ('2024-12-31')::timestamp,
         '1 day'::interval) C(d)
  LEFT JOIN SpecialDays S ON S.special_date=C.d;

Сокращенные предпраздничные дни получили по 7 часов. Рабочие дни в выходные - 8. Праздники - 0. Воскресенье и суббота (в PostgreSQL нулевой и шестой дни недели соответственно) - 0. Ну а остальные дни считаются рабочими по 8 часов.

После всего этого осталось только создать функцию

CREATE OR REPLACE FUNCTION get_working_calendar(
  calendar_year smallint,
  calendar_lang varchar(2)='ru')
  RETURNS TABLE (
    pk_date       date,
    working_house smallint,
    from_date     date,
    holiday_name  varchar
  ) AS $function$
<<func>>
DECLARE
  sql_cmd varchar='COPY tmp_tmp (res) FROM PROGRAM $$'
    ||$$/usr/bin/wget -qO - https://xmlcalendar.ru/data/$$
    ||calendar_lang||$$/$$
    ||calendar_year::text
    ||$$/calendar.xml --no-check-certificate$$
    ||'$$ WITH (FORMAT text);';
BEGIN
DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (
  ID  serial PRIMARY KEY,
  res text NOT NULL);
EXECUTE func.sql_cmd;

RETURN QUERY WITH CTE AS (
  SELECT string_agg(res,'' ORDER BY ID)::xml AS res
  FROM tmp_tmp ),
CheckYear AS (
  SELECT Y.for_year, Y.for_country, Y.create_date
  FROM CTE C
  CROSS JOIN xmltable('//calendar' PASSING C.res COLUMNS
      for_year smallint PATH '@year',
      for_country varchar PATH '@lang',
      create_date date PATH '@date') Y
  WHERE Y.for_year=calendar_year ),
Holidays AS (  
  SELECT H.id, H.title
  FROM CheckYear Y
  CROSS JOIN CTE C
  CROSS JOIN xmltable('//calendar/holidays/holiday'
      PASSING C.res COLUMNS
      id smallint PATH '@id',
      title varchar PATH '@title') H ),
SpecialDays AS (  
  SELECT (calendar_year::text||'-'
      ||left(D.d,2)||'-'
      ||right(D.d,2))::date AS special_date,
    CASE WHEN D.t=1 THEN 'Holiday'
         WHEN D.t=2 THEN 'Shortened'
         WHEN D.t=3 THEN 'Working'
         ELSE NULL END AS day_type,
    (calendar_year::text||'-'
      ||left(D.f,2)||'-'
      ||right(D.f,2))::date AS from_date, 
    H.title AS holiday_name
  FROM CheckYear Y
  CROSS JOIN CTE C
  CROSS JOIN xmltable('//calendar/days/day' PASSING C.res COLUMNS
      d varchar(5) PATH '@d',
      t smallint PATH '@t',
      h smallint PATH '@h',
      f varchar(5) PATH '@f') D  
  LEFT JOIN Holidays H ON H.id=D.h )
SELECT C.d::date AS pk_date,
  CASE WHEN S.day_type='Shortened' THEN 7
       WHEN S.day_type='Working' THEN 8
       WHEN S.day_type='Holiday' THEN 0
       WHEN extract(DOW FROM C.d) IN (0,6) THEN 0
       ELSE 8 END::smallint AS working_hours,
  S.from_date, S.holiday_name
FROM CheckYear Y
CROSS JOIN generate_series((calendar_year::text||'-01-01')::timestamp,
       (calendar_year::text||'-12-31')::timestamp,
       '1 day'::interval) C(d)
LEFT JOIN SpecialDays S ON S.special_date=C.d;
END; $function$  LANGUAGE plpgsql;

И убедиться, что производственный календарь успешно загружается и парсится:

SELECT *
FROM get_working_calendar(2024::smallint);

Спасибо за внимание. С наступающим Новым Годом вас!

P.S. Забыл указать, что доступны производственные календари с 2015 года для России (ru) Белоруссии (by), Украины (ua), Казахстана (kz) и Узбекистана (uz)

Теги:

Хабы:

Производственный календарь в PostgreSQL

Публикации

Ближайшие события