이중 시간 모델

DROP TABLE IF EXISTS post_title_fact_bad;
CREATE TABLE post_title_fact_bad (
  post_id BIGINT UNSIGNED NOT NULL,
  effective_from DATETIME(6) NOT NULL,
  effective_to DATETIME(6) NOT NULL,
  title VARCHAR(160) NOT NULL,
  corrected_at DATETIME(6) NULL,
  PRIMARY KEY (post_id, effective_from)
) ENGINE=InnoDB;

INSERT INTO post_title_fact_bad VALUES
  (7001, '2026-07-15 09:00:00', '2026-07-15 12:00:00',
   '인덱스 개요', NULL);

-- 오후 2시 보고서는 이 값을 사용했다.
SELECT title FROM post_title_fact_bad
WHERE post_id = 7001
  AND '2026-07-15 10:00:00' >= effective_from
  AND '2026-07-15 10:00:00' < effective_to;

-- 오후 3시에 소급 정정하면서 이전 지식을 덮는다.
UPDATE post_title_fact_bad
SET title = 'B+Tree', corrected_at = '2026-07-15 15:00:00'
WHERE post_id = 7001 AND effective_from = '2026-07-15 09:00:00';

-- 오후 2시 당시 데이터베이스가 돌려준 값을 다시 묻는다.
SELECT title FROM post_title_fact_bad
WHERE post_id = 7001
  AND '2026-07-15 10:00:00' >= effective_from
  AND '2026-07-15 10:00:00' < effective_to;

title
인덱스 개요

title
B+Tree

DROP PROCEDURE IF EXISTS correct_post_fact_bitemporal;
DROP TABLE IF EXISTS post_title_fact;

CREATE TABLE post_title_fact (
  fact_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  valid_from DATETIME(6) NOT NULL,
  valid_to DATETIME(6) NOT NULL,
  system_from DATETIME(6) NOT NULL,
  system_to DATETIME(6) NOT NULL
    DEFAULT '9999-12-31 23:59:59',
  recorded_by BIGINT UNSIGNED NOT NULL,
  correction_reason VARCHAR(240) NOT NULL,
  open_system_slot TINYINT GENERATED ALWAYS AS (
    IF(system_to = '9999-12-31 23:59:59', 1, NULL)
  ) STORED,
  PRIMARY KEY (fact_id),
  CONSTRAINT uq_post_fact_open_version
    UNIQUE (post_id, valid_from, open_system_slot),
  CONSTRAINT fk_post_fact_post FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE RESTRICT,
  CONSTRAINT fk_post_fact_actor FOREIGN KEY (recorded_by)
    REFERENCES members (id) ON DELETE RESTRICT,
  CONSTRAINT chk_post_fact_valid CHECK (valid_to > valid_from),
  CONSTRAINT chk_post_fact_system CHECK (system_to > system_from),
  INDEX ix_post_fact_bitemporal
    (post_id, valid_from, valid_to, system_from, system_to)
) ENGINE=InnoDB;

SET @post_id = (SELECT id FROM posts ORDER BY id LIMIT 1);
SET @author_id = (SELECT author_id FROM posts WHERE id = @post_id);

INSERT INTO post_title_fact
  (post_id, title, valid_from, valid_to,
   system_from, system_to, recorded_by, correction_reason)
VALUES
  (@post_id, '인덱스 개요',
   '2026-07-15 09:00:00', '2026-07-15 12:00:00',
   '2026-07-15 09:05:00', '9999-12-31 23:59:59',
   @author_id, '최초 게시글 제목 입력');

DELIMITER //
CREATE PROCEDURE correct_post_fact_bitemporal (
  IN p_post_id BIGINT UNSIGNED,
  IN p_valid_at DATETIME(6),
  IN p_recorded_at DATETIME(6),
  IN p_new_title VARCHAR(160),
  IN p_actor_id BIGINT UNSIGNED,
  IN p_reason VARCHAR(240)
)
BEGIN
  DECLARE v_fact_id BIGINT UNSIGNED;
  DECLARE v_valid_from DATETIME(6);
  DECLARE v_valid_to DATETIME(6);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN ROLLBACK; RESIGNAL; END;

  START TRANSACTION;
  SELECT fact_id, valid_from, valid_to
    INTO v_fact_id, v_valid_from, v_valid_to
  FROM post_title_fact
  WHERE post_id = p_post_id
    AND p_valid_at >= valid_from
    AND p_valid_at < valid_to
    AND system_to = '9999-12-31 23:59:59'
  FOR UPDATE;

  IF v_fact_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'current belief not found';
  END IF;
  IF CHAR_LENGTH(TRIM(p_reason)) < 10 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'correction reason too short';
  END IF;

  UPDATE post_title_fact
  SET system_to = p_recorded_at
  WHERE fact_id = v_fact_id
    AND p_recorded_at > system_from;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'knowledge time must increase';
  END IF;

  INSERT INTO post_title_fact
    (post_id, title, valid_from, valid_to,
     system_from, system_to, recorded_by, correction_reason)
  VALUES
    (p_post_id, p_new_title, v_valid_from, v_valid_to,
     p_recorded_at, '9999-12-31 23:59:59',
     p_actor_id, p_reason);
  COMMIT;
END//
DELIMITER ;

CALL correct_post_fact_bitemporal(
  @post_id, '2026-07-15 10:00:00', '2026-07-15 15:00:00',
  'B+Tree', @author_id, '교재 원본을 대조해 오전 게시글 제목을 소급 정정'
);

SELECT fact_id, title, system_from, system_to, open_system_slot
FROM post_title_fact
WHERE post_id = @post_id
ORDER BY fact_id;

fact_id | title       | system_from                | system_to                  | open_system_slot
1       | 인덱스 개요 | 2026-07-15 09:05:00.000000 | 2026-07-15 15:00:00.000000 | NULL
2       | B+Tree      | 2026-07-15 15:00:00.000000 | 9999-12-31 23:59:59.000000 | 1

SET @valid_at = '2026-07-15 10:00:00.000000';

SELECT fact_id, title
FROM post_title_fact
WHERE post_id = @post_id
  AND @valid_at >= valid_from AND @valid_at < valid_to
  AND '2026-07-15 14:00:00' >= system_from
  AND '2026-07-15 14:00:00' < system_to;

SELECT fact_id, title
FROM post_title_fact
WHERE post_id = @post_id
  AND @valid_at >= valid_from AND @valid_at < valid_to
  AND '2026-07-15 16:00:00' >= system_from
  AND '2026-07-15 16:00:00' < system_to;

SELECT a.fact_id, b.fact_id
FROM post_title_fact AS a
JOIN post_title_fact AS b
  ON b.post_id = a.post_id AND b.fact_id > a.fact_id
WHERE a.system_to = '9999-12-31 23:59:59'
  AND b.system_to = '9999-12-31 23:59:59'
  AND a.valid_from < b.valid_to
  AND b.valid_from < a.valid_to;

SELECT a.fact_id, b.fact_id
FROM post_title_fact AS a
JOIN post_title_fact AS b
  ON b.post_id = a.post_id
 AND b.valid_from = a.valid_from
 AND b.valid_to = a.valid_to
 AND b.fact_id > a.fact_id
WHERE a.system_from < b.system_to
  AND b.system_from < a.system_to;

fact_id | title
1       | 인덱스 개요

fact_id | title
2       | B+Tree