FirebirdのDIY制作カレンダー

こんにちは、私の名前はデニスです。私は情報システム開発者です。FirebirdDBMSに関する記事やドキュメントを書いています。この記事では、Firebird DBMSを使用した本番カレンダーの実装について説明します。



habrに関する同様の記事が私にこの記事を書くように促しました：PostgreSQLとMS SQLを使用した運用カレンダー。私は混合アプローチを使用することにしました。一方では、日付の例外のみを保存して「オンザフライ」でカレンダーを生成できます。他方、そのようなカレンダーは永続的なテーブルに保存して、日付やその他の属性ですばやく検索できます。



開発にはFirebird 3.0を使用します。以前のバージョンと比較してPSQL機能が大幅に拡張されています。カレンダーを操作するためのすべてのプロシージャと関数は、DATE_UTILSパッケージにカプセル化されます。



最初に、標準の休日の日付を格納するためのテーブルを作成します。

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;

このようなテーブルは、毎週休日を週末として追加しないように、カレンダーへの入力プロセスを自動化するのに役立ちます。



次に、例外を格納するテーブルを作成しましょう。週末になった平日と就業日に割り当てられた週末の両方が保存されます。



さらに、任意の日付の任意のメモを残すことができます。

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

フィールドDAY_TYPEは、日付のタイプを示します：0-営業日。1-休日、2-休日。



例外テーブルを操作するために、2つのストアドプロシージャを作成し、それらをDATE_UTILSパッケージ内に配置します。

  
  --       
  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は、PostgreSQLとは異なり、一連の値を生成するための特別な関数がありません。このような生成は再帰CTEを使用して実行できますが、この場合、再帰の深さによって制限されます。少し簡単に、カスタムの選択的ストアドプロシージャを記述して一連の日付を生成し、それをDATE_UTILSパッケージ内に配置します。

  
  --   
  --   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

このプロシージャは、最小の日付が最大の日付よりも大きい場合にループから保護し、例外E_MIN_DATE_EXCEEDSがスローされます。これは次のように定義されています。

CREATE EXCEPTION E_MIN_DATE_EXCEEDS '   ';

次に、オンザフライでカレンダーを生成します。日付が例外テーブルに含まれている場合、日付タイプと例外テーブルからのメモが推測されます。例外の表に日付がなく、休日の表に存在する場合は、休日の表のメモを表示します。週末は曜日の数によって決定され、残りの日付は稼働日です。説明されているアルゴリズムは、次のクエリによって実装されます

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

このクエリを選択的ストアドプロシージャに保存し、いくつかの追加列の出力を追加します

--  
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

ロシア語で曜日、月名、日付タイプを表示する関数をいくつか追加します。

--     
  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

これで、次のクエリを使用してカレンダーを表示できます。

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>

一部の日付を休日または平日としてマークする必要がある場合は、次のクエリを使用します。

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

除外リストから日付を削除するには、クエリを実行する必要があります

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

次に、制作カレンダーを格納するテーブルを作成し、それを設定するプロシージャを記述します。

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

カレンダーを保存するためのテーブルを埋める手順は、日付がすでに存在する場合に、除外テーブルで変更が行われた場合、または日付が除外リストから削除された場合にのみ更新されるように設計されています。



例外テーブルの変更をすぐにカレンダーテーブルに反映するために、SET_DATE_NOTEおよびUNSET_DATE_NOTEプロシージャを少し変更します。最初の変更は非常に簡単です。CALENDARテーブルのノートと日付タイプを更新する別のリクエストをプロシージャに追加するだけです。

  --       
  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

日付の注釈を削除することは、日付が除外される前の日付の注釈を返す必要があるため、少しトリッキーです。これを行うには、GET_CALENDARプロシージャですでに使用されている日付タイプとメモを決定するために同じロジックを使用します。

  --    
  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

次のクエリを使用して、テーブルからカレンダーを表示できます。

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>

それで全部です。オンザフライで本番カレンダーを生成し、日付の例外を管理し、カレンダーをテーブルに保存して、日付をすばやく検索することができました。テーブルとカレンダーパッケージを作成するためのスクリプトはここにあります。