DIY production calendar in Firebird

Hello, my name is Denis, I will be a developer of information systems, I write articles and documentation on the Firebird DBMS. In this article I want to talk about the implementation of the production calendar using the Firebird DBMS.



I was prompted to write this article by similar articles on Habré: a production calendar using PostgreSQL and MS SQL . I decided to use a mixed approach. On the one hand, you can store only exceptions for dates and generate a calendar "on the fly", on the other hand, such a calendar can be saved to a permanent table and quickly searched by date or other attributes.



We will use Firebird 3.0 for development, it has significantly expanded the capabilities of PSQL compared to previous versions. All procedures and functions for working with the calendar will be encapsulated in the DATE_UTILS package.



The first step is to create a table to store the standard holiday dates.



CREATE TABLE HOLIDAYS (
    ID      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    AMONTH  SMALLINT NOT NULL,
    ADAY    SMALLINT NOT NULL,
    REMARK  VARCHAR(255) NOT NULL,
    CONSTRAINT PK_HOLIDAYS PRIMARY KEY (ID),
    CONSTRAINT UNQ_HOLIDAYS UNIQUE (AMONTH, ADAY
);

INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (1, 1, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (2, 1, 7, '');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (3, 2, 23, '  ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (4, 3, 8, '  ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (5, 5, 1, '   ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (6, 5, 9, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (7, 6, 12, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
              VALUES (8, 11, 4, '  ');

COMMIT;


Such a table will help us automate the process of filling the calendar so as not to add holidays every time as a weekend.



Now let's create a table to store exceptions. It will store both weekdays, which have become weekends, and vice versa, weekends that are designated as working days.



In addition, you can leave an arbitrary note for any date.



CREATE TABLE CALENDAR_NOTES (
    BYDATE    DATE NOT NULL,
    DAY_TYPE  SMALLINT NOT NULL,
    REMARK    VARCHAR(255),
    CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);


The DAY_TYPE field indicates the type of date: 0 - business day. 1 - day off, 2 - holiday.



To work with the exception table, we will create 2 stored procedures and place them inside the DATE_UTILS package.



  
  --       
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
  END

  --    
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;
  END


Firebird, unlike PostgreSQL, lacks a special function for generating series of values. Such generation can be done using a recursive CTE, but in this case, we will be limited by the recursion depth. We’ll do it a bit easier, write a special selective stored procedure for generating a sequence of dates and place it inside the DATE_UTILS package.



  
  --   
  --   1 
  PROCEDURE GENERATE_SERIES (
      MIN_DATE DATE,
      MAX_DATE DATE)
  RETURNS (
      BYDATE DATE)
  AS
  BEGIN
    IF (MIN_DATE > MAX_DATE) THEN
      EXCEPTION E_MIN_DATE_EXCEEDS;
    BYDATE = MIN_DATE;
    WHILE (BYDATE <= MAX_DATE) DO
    BEGIN
      SUSPEND;
      BYDATE = BYDATE + 1;
    END
  END


The procedure provides protection against looping; if the minimum date is greater than the maximum, an exception E_MIN_DATE_EXCEEDS will be thrown, which is defined as follows:



CREATE EXCEPTION E_MIN_DATE_EXCEEDS '   ';


Now let's move on to generating the calendar on the fly. If the date is contained in the exception table, then the date type and note from the exception table will be inferred. If the date is not in the table of exceptions, but it is present in the table with holiday dates, then we display a note from the table of holidays. Weekends are determined by the number of the day of the week, the rest of the dates are working days. The described algorithm is implemented by the following query



SELECT
    D.BYDATE,
    CASE
        WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
        WHEN HOLIDAYS.ID IS NOT NULL THEN 2
        WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
        ELSE 0
    END AS DATE_TYPE,
    COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
    LEFT JOIN HOLIDAYS 
      ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
         HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
    LEFT JOIN CALENDAR_NOTES NOTES 
      ON NOTES.BYDATE = D.BYDATE


Let's save this query to a selective stored procedure and add the output of some additional columns




--  
PROCEDURE GET_CALENDAR (
    MIN_DATE DATE,
    MAX_DATE DATE)
RETURNS (
    BYDATE     DATE,
    YEAR_OF    SMALLINT,
    MONTH_OF   SMALLINT,
    DAY_OF     SMALLINT,
    WEEKDAY_OF SMALLINT,
    DATE_TYPE  SMALLINT,
    REMARK     VARCHAR(255))
AS
BEGIN
    FOR
      SELECT
          D.BYDATE,
          EXTRACT(YEAR FROM d.BYDATE) AS YEAR_OF,
          EXTRACT(MONTH FROM d.BYDATE) AS MONTH_OF,
          EXTRACT(DAY FROM d.BYDATE) AS DAY_OF,
          EXTRACT(WEEKDAY FROM d.BYDATE) AS WEEKDAY_OF,
          CASE
            WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
      FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
          LEFT JOIN HOLIDAYS
            ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
               HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
          LEFT JOIN CALENDAR_NOTES NOTES
            ON NOTES.BYDATE = D.BYDATE
      INTO BYDATE,
           YEAR_OF,
           MONTH_OF,
           DAY_OF,
           WEEKDAY_OF,
           DATE_TYPE,
           REMARK
    DO
      SUSPEND;
END


Let's add several functions for displaying days of the week, names of months and date type in Russian.




--     
  FUNCTION GET_WEEKDAY_NAME(AWEEKDAY SMALLINT) RETURNS CHAR(2)
  AS
  BEGIN
    RETURN CASE AWEEKDAY
      WHEN 1 THEN ''
      WHEN 2 THEN ''
      WHEN 3 THEN ''
      WHEN 4 THEN ''
      WHEN 5 THEN ''
      WHEN 6 THEN ''
      WHEN 0 THEN ''
    END;
  END

  --   
  FUNCTION GET_MONTH_NAME(AMONTH SMALLINT) RETURNS VARCHAR(10)
  AS
  BEGIN
    RETURN CASE AMONTH
      WHEN 1 THEN ''
      WHEN 2 THEN ''
      WHEN 3 THEN ''
      WHEN 4 THEN ''
      WHEN 5 THEN ''
      WHEN 6 THEN ''
      WHEN 7 THEN ''
      WHEN 8 THEN ''
      WHEN 9 THEN ''
      WHEN 10 THEN ''
      WHEN 11 THEN ''
      WHEN 12 THEN ''
    END;
  END

  --    
  FUNCTION GET_DAY_TYPE_NAME(ADAY_TYPE SMALLINT) RETURNS VARCHAR(11)
  AS
  BEGIN
    RETURN CASE ADAY_TYPE
      WHEN 0 THEN ''
      WHEN 1 THEN ''
      WHEN 2 THEN ''
    END;
  END


We can now display the calendar using the following query:



SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM DATE_UTILS.GET_CALENDAR(DATE '01.05.2019', DATE '31.05.2019') D


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019               1                
2019-05-02     2019               2                 
2019-05-03     2019               3                 
2019-05-04     2019               4                 
2019-05-05     2019               5                 
2019-05-06     2019               6                 <null>
2019-05-07     2019               7                 <null>
2019-05-08     2019               8                 <null>
2019-05-09     2019               9              
2019-05-10     2019              10                 
2019-05-11     2019              11                <null>
2019-05-12     2019              12                <null>
2019-05-13     2019              13                 <null>
2019-05-14     2019              14                 <null>
2019-05-15     2019              15                 <null>
2019-05-16     2019              16                 <null>
2019-05-17     2019              17                 <null>
2019-05-18     2019              18                <null>
2019-05-19     2019              19                <null>
2019-05-20     2019              20                 <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019              21                 <null>
2019-05-22     2019              22                 <null>
2019-05-23     2019              23                 <null>
2019-05-24     2019              24                 <null>
2019-05-25     2019              25                <null>
2019-05-26     2019              26                <null>
2019-05-27     2019              27                 <null>
2019-05-28     2019              28                 <null>
2019-05-29     2019              29                 <null>
2019-05-30     2019              30                 <null>
2019-05-31     2019              31                 <null>


If you need to mark some date as a day off or weekday, use the following query:




EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, ' ');


To remove a date from the list of exclusions, you need to run a query




EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');


Now create a table to store the production calendar, and write a procedure to fill it.



CREATE TABLE CALENDAR (
    BYDATE      DATE NOT NULL,
    YEAR_OF     SMALLINT NOT NULL,
    MONTH_OF    SMALLINT NOT NULL,
    DAY_OF      SMALLINT NOT NULL,
    WEEKDAY_OF  SMALLINT NOT NULL,
    DATE_TYPE   SMALLINT NOT NULL,
    REMARK      VARCHAR(255),
    CONSTRAINT PK_CALENDAR PRIMARY KEY (BYDATE)
);

  -- /  
  PROCEDURE FILL_CALENDAR (
      MIN_DATE DATE,
      MAX_DATE DATE)
  AS
  BEGIN
    MERGE INTO CALENDAR
    USING (
      SELECT
        BYDATE,
        YEAR_OF,
        MONTH_OF,
        DAY_OF,
        WEEKDAY_OF,
        DATE_TYPE,
        REMARK
      FROM DATE_UTILS.GET_CALENDAR(:MIN_DATE, :MAX_DATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN NOT MATCHED THEN
    INSERT (
      BYDATE,
      YEAR_OF,
      MONTH_OF,
      DAY_OF,
      WEEKDAY_OF,
      DATE_TYPE,
      REMARK
    )
    VALUES (
      S.BYDATE,
      S.YEAR_OF,
      S.MONTH_OF,
      S.DAY_OF,
      S.WEEKDAY_OF,
      S.DATE_TYPE,
      S.REMARK
    )
    WHEN MATCHED AND
      (CALENDAR.DATE_TYPE <> S.DATE_TYPE OR 
       CALENDAR.REMARK <> S.REMARK) THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END


The procedure for filling the table for storing the calendar is designed in such a way that if a date already exists in it, then the date and note type will be updated only if changes have occurred in the exclusion table or the date has been removed from the exclusion list.



In order for the changes in the exception table to be immediately reflected in the calendar table, we will slightly modify the SET_DATE_NOTE and UNSET_DATE_NOTE procedures. The first change is pretty trivial, we just add another request to the procedure to update the note and date type in the CALENDAR table.



  --       
  PROCEDURE SET_DATE_NOTE (
      ADATE     DATE,
      ADAY_TYPE SMALLINT,
      AREMARK   VARCHAR(255))
  AS
  BEGIN
    UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
    VALUES (:ADATE, :ADAY_TYPE, :AREMARK);

    --        
    UPDATE CALENDAR
    SET DATE_TYPE = :ADAY_TYPE,
        REMARK = :AREMARK
    WHERE BYDATE = :ADATE
      AND (DATE_TYPE <> :ADAY_TYPE OR REMARK <> :AREMARK);
  END


Removing the annotation for a date is a little trickier because we have to return the annotation that the date had before it was excluded. To do this, we use the same logic for determining the type of date and comments that were already used in the GET_CALENDAR procedure.




  --    
  PROCEDURE UNSET_DATE_NOTE (
      ADATE DATE)
  AS
  BEGIN
    DELETE FROM CALENDAR_NOTES
    WHERE BYDATE = :ADATE;

    --        
    MERGE INTO CALENDAR
    USING (
      SELECT
          :ADATE AS BYDATE,
          CASE
            WHEN HOLIDAYS.ID IS NOT NULL THEN 2
            WHEN EXTRACT(WEEKDAY FROM :ADATE) IN (0, 6) THEN 1
            ELSE 0
          END AS DATE_TYPE,
          HOLIDAYS.REMARK AS REMARK
      FROM RDB$DATABASE
      LEFT JOIN HOLIDAYS ON
        HOLIDAYS.AMONTH = EXTRACT(MONTH FROM :ADATE) AND
        HOLIDAYS.ADAY = EXTRACT(DAY FROM :ADATE)
    ) S
    ON CALENDAR.BYDATE = S.BYDATE
    WHEN MATCHED THEN
    UPDATE SET
      DATE_TYPE = S.DATE_TYPE,
      REMARK = S.REMARK;
  END


You can display a calendar from a table using the following query:



SELECT
    D.BYDATE AS BYDATE,
    D.YEAR_OF,
    DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
    D.DAY_OF,
    DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
    DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
    D.REMARK AS REMARK
FROM CALENDAR D
WHERE D.BYDATE BETWEEN DATE '01.05.2019' AND DATE '31.05.2019'




BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01     2019               1                
2019-05-02     2019               2                 
2019-05-03     2019               3                 
2019-05-04     2019               4                 
2019-05-05     2019               5                 
2019-05-06     2019               6                 <null>
2019-05-07     2019               7                 <null>
2019-05-08     2019               8                 <null>
2019-05-09     2019               9              
2019-05-10     2019              10                 
2019-05-11     2019              11                <null>
2019-05-12     2019              12                <null>
2019-05-13     2019              13                 <null>
2019-05-14     2019              14                 <null>
2019-05-15     2019              15                 <null>
2019-05-16     2019              16                 <null>
2019-05-17     2019              17                 <null>
2019-05-18     2019              18                <null>
2019-05-19     2019              19                <null>
2019-05-20     2019              20                 <null>


BYDATE      YEAR_OF MONTH_NAME  DAY_OF WEEKDAY_NAME DATE_TYPE   REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21     2019              21                 <null>
2019-05-22     2019              22                 <null>
2019-05-23     2019              23                 <null>
2019-05-24     2019              24                 <null>
2019-05-25     2019              25                <null>
2019-05-26     2019              26                <null>
2019-05-27     2019              27                 <null>
2019-05-28     2019              28                 <null>
2019-05-29     2019              29                 <null>
2019-05-30     2019              30                 <null>
2019-05-31     2019              31                 <null>


That's all. We were able to generate a production calendar on the fly, manage date exceptions, and save the calendar in a table for quick date lookups. The script for creating tables and calendar packages can be found here .



All Articles