유효 기간 모델

DROP TABLE IF EXISTS post_visibility_period_bad;
CREATE TABLE post_visibility_period_bad (
  policy_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  display_priority SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (policy_id),
  UNIQUE KEY uq_visibility_bad_start (post_id, valid_from),
  CHECK (valid_to > valid_from)
) ENGINE=InnoDB;

INSERT INTO post_visibility_period_bad
  (post_id, valid_from, valid_to, display_priority)
VALUES
  (10, '2026-07-01', '2026-08-01', 60),
  (10, '2026-07-15', '2026-09-01', 90);

SELECT policy_id, display_priority
FROM post_visibility_period_bad
WHERE post_id = 10
  AND '2026-07-20' >= valid_from
  AND '2026-07-20' < valid_to;

SELECT a.policy_id AS left_policy, b.policy_id AS right_policy
FROM post_visibility_period_bad AS a
JOIN post_visibility_period_bad AS b
  ON b.post_id = a.post_id AND b.policy_id > a.policy_id
WHERE a.valid_from < b.valid_to AND b.valid_from < a.valid_to;

as-of rows for 2026-07-20: 2
priorities returned: 60, 90
overlap pairs: (1, 2)
unique violations: 0
check violations: 0

DROP PROCEDURE IF EXISTS add_post_visibility_period;
DROP TABLE IF EXISTS post_visibility_period;

CREATE TABLE post_visibility_period (
  policy_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  valid_from DATE NOT NULL,
  valid_to DATE NOT NULL,
  display_priority SMALLINT UNSIGNED NOT NULL,
  policy_reason VARCHAR(240) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (policy_id),
  CONSTRAINT uq_post_visibility_start UNIQUE (post_id, valid_from),
  CONSTRAINT fk_post_visibility_post FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE RESTRICT,
  CONSTRAINT chk_post_visibility_period CHECK (valid_to > valid_from),
  CONSTRAINT chk_post_visibility_priority CHECK (display_priority BETWEEN 1 AND 100),
  INDEX ix_post_visibility_asof (post_id, valid_from, valid_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);

DELIMITER //
CREATE PROCEDURE add_post_visibility_period (
  IN p_post_id BIGINT UNSIGNED,
  IN p_valid_from DATE,
  IN p_valid_to DATE,
  IN p_display_priority SMALLINT UNSIGNED,
  IN p_reason VARCHAR(240)
)
BEGIN
  DECLARE v_post BIGINT UNSIGNED;
  DECLARE v_overlap BIGINT UNSIGNED;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SELECT id INTO v_post
  FROM posts
  WHERE id = p_post_id
  FOR UPDATE;

  IF v_post IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'post not found';
  END IF;
  IF p_valid_to <= p_valid_from THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'period must be non-empty';
  END IF;

  SELECT policy_id INTO v_overlap
  FROM post_visibility_period
  WHERE post_id = p_post_id
    AND valid_from < p_valid_to
    AND p_valid_from < valid_to
  ORDER BY valid_from
  LIMIT 1;

  IF v_overlap IS NOT NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'visibility period overlaps existing period';
  END IF;

  INSERT INTO post_visibility_period
    (post_id, valid_from, valid_to, display_priority, policy_reason)
  VALUES
    (p_post_id, p_valid_from, p_valid_to, p_display_priority, p_reason);
  COMMIT;
END//
DELIMITER ;

CALL add_post_visibility_period(
  @post_id, '2026-07-01', '2026-08-01', 60,
  '7월 공지 게시글 기본 노출 우선순위'
);
CALL add_post_visibility_period(
  @post_id, '2026-08-01', '2026-09-01', 90,
  '8월 캠페인에 따른 노출 우선순위 상향'
);

policies inserted: 2
overlap pairs: 0
rows at 2026-07-31: 1, priority=60
rows at 2026-08-01: 1, priority=90
overlapping insert: SIGNAL 45000

SELECT policy_id, display_priority
FROM post_visibility_period
WHERE post_id = @post_id
  AND '2026-07-31' >= valid_from
  AND '2026-07-31' < valid_to;

SELECT policy_id, display_priority
FROM post_visibility_period
WHERE post_id = @post_id
  AND '2026-08-01' >= valid_from
  AND '2026-08-01' < valid_to;

CALL add_post_visibility_period(
  @post_id, '2026-06-01', '2026-10-01', 30,
  '기존 두 기간을 감싸는 거절 fixture'
);

SELECT a.post_id, a.policy_id, b.policy_id
FROM post_visibility_period AS a
JOIN post_visibility_period AS b
  ON b.post_id = a.post_id
 AND b.policy_id > a.policy_id
WHERE a.valid_from < b.valid_to
  AND b.valid_from < a.valid_to;

EXPLAIN ANALYZE
SELECT display_priority
FROM post_visibility_period
WHERE post_id = @post_id
  AND '2026-08-12' >= valid_from
  AND '2026-08-12' < valid_to;