변경 이력 모델

USE board_lab;

DROP TABLE IF EXISTS post_delta_bad;
DROP TABLE IF EXISTS post_current_bad;

CREATE TABLE post_current_bad (
  post_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  PRIMARY KEY (post_id)
) ENGINE=InnoDB;

CREATE TABLE post_delta_bad (
  delta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id BIGINT UNSIGNED NOT NULL,
  column_name VARCHAR(40) NOT NULL,
  old_value VARCHAR(255) NULL,
  changed_at DATETIME(6) NOT NULL,
  PRIMARY KEY (delta_id)
) ENGINE=InnoDB;

INSERT INTO post_current_bad VALUES
  (7001, 'B+Tree 초안', 60, 'DRAFT', '2026-07-15 09:00:00');

INSERT INTO post_delta_bad
  (post_id, column_name, old_value, changed_at)
VALUES (7001, 'title', 'B+Tree 초안', '2026-07-15 10:00:00');
UPDATE post_current_bad
SET title = 'B+Tree와 covering index',
    view_count = 90,
    updated_at = '2026-07-15 10:00:00'
WHERE post_id = 7001;

UPDATE post_current_bad
SET view_count = 75,
    status = 'PUBLISHED',
    updated_at = '2026-07-15 11:00:00'
WHERE post_id = 7001;

SELECT * FROM post_current_bad;
SELECT column_name, old_value FROM post_delta_bad ORDER BY delta_id;

post_id | title                   | view_count | status    | updated_at
7001    | B+Tree와 covering index | 75         | PUBLISHED | 2026-07-15 11:00:00.000000

column_name | old_value
title       | B+Tree 초안

DROP TABLE IF EXISTS post_tracking_demo;
CREATE TABLE post_tracking_demo (
  post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  updated_by BIGINT UNSIGNED NOT NULL,
  update_reason VARCHAR(240) NOT NULL,
  source_system VARCHAR(40) NOT NULL
) ENGINE = InnoDB;

INSERT INTO post_tracking_demo VALUES (
  7001, 'B+Tree 초안', 60,
  '2026-07-15 09:00:00', 1,
  '2026-07-15 09:00:00', 1,
  '최초 등록', 'WEB'
);

UPDATE post_tracking_demo
SET title = 'B+Tree와 covering index',
    view_count = 90,
    updated_at = '2026-07-15 10:00:00',
    updated_by = 2,
    update_reason = '게시글 조회수 정정',
    source_system = 'ADMIN'
WHERE post_id = 7001;

SELECT title, view_count, updated_at,
       updated_by, update_reason, source_system
FROM post_tracking_demo;

title                   | view_count | updated_at                 | updated_by | update_reason      | source_system
B+Tree와 covering index | 90         | 2026-07-15 10:00:00.000000 | 2          | 게시글 조회수 정정 | ADMIN

DROP PROCEDURE IF EXISTS revise_post_revision;
DROP TABLE IF EXISTS post_revision;

CREATE TABLE post_revision (
  post_id BIGINT UNSIGNED NOT NULL,
  version_no INT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  request_key VARCHAR(64) NOT NULL,
  title VARCHAR(160) NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  published_at DATETIME(6) NULL,
  valid_from DATETIME(6) NOT NULL,
  valid_to DATETIME(6) NULL,
  change_reason VARCHAR(240) NOT NULL,
  current_slot TINYINT GENERATED ALWAYS AS
    (IF(valid_to IS NULL, 1, NULL)) STORED,
  PRIMARY KEY (post_id, version_no),
  CONSTRAINT uq_post_revision_current UNIQUE (post_id, current_slot),
  CONSTRAINT fk_post_revision_current FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE RESTRICT,
  CONSTRAINT chk_post_revision_window
    CHECK (valid_to IS NULL OR valid_to > valid_from),
  CONSTRAINT chk_post_revision_views
    CHECK (view_count <= 1000000000),
  INDEX ix_post_revision_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);

INSERT INTO post_revision
  (post_id, version_no, author_id, request_key, title,
   view_count, status, created_at, published_at,
   valid_from, valid_to, change_reason)
SELECT id, 1, author_id, request_key, title,
       view_count, status, created_at, published_at,
       '2026-07-15 00:00:00.000000', NULL, 'post revision 시작'
FROM posts
WHERE id = @post_id;

DELIMITER //
CREATE PROCEDURE revise_post_revision (
  IN p_post_id BIGINT UNSIGNED,
  IN p_effective_at DATETIME(6),
  IN p_title VARCHAR(160),
  IN p_view_count BIGINT UNSIGNED,
  IN p_reason VARCHAR(240)
)
BEGIN
  DECLARE v_version INT UNSIGNED;
  DECLARE v_opened_at DATETIME(6);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SELECT version_no + 1, valid_from
    INTO v_version, v_opened_at
  FROM post_revision
  WHERE post_id = p_post_id AND valid_to IS NULL
  FOR UPDATE;

  IF v_opened_at IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'open snapshot not found';
  END IF;
  IF p_effective_at <= v_opened_at OR p_view_count > 1000000000 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'invalid snapshot boundary';
  END IF;

  UPDATE post_revision
  SET valid_to = p_effective_at
  WHERE post_id = p_post_id AND valid_to IS NULL;

  UPDATE posts
  SET title = p_title, view_count = p_view_count
  WHERE id = p_post_id;

  INSERT INTO post_revision
    (post_id, version_no, author_id, request_key, title,
     view_count, status, created_at, published_at,
     valid_from, valid_to, change_reason)
  SELECT id, v_version, author_id, request_key, title,
         view_count, status, created_at, published_at,
         p_effective_at, NULL, p_reason
  FROM posts
  WHERE id = p_post_id;
  COMMIT;
END//
DELIMITER ;

CALL revise_post_revision(
  @post_id, '2026-07-15 12:00:00.000000',
  'snapshot 이력 실습', 100, '영수증과 대조해 제목과 조회수를 정정'
);

SELECT version_no, title, view_count,
       valid_from, valid_to, current_slot
FROM post_revision
WHERE post_id = @post_id
ORDER BY version_no;

version_no | title                 | view_count | valid_from                 | valid_to                   | current_slot
1          | 회원 게시판 최종 모델 | 0          | 2026-07-15 00:00:00.000000 | 2026-07-15 12:00:00.000000 | NULL
2          | snapshot 이력 실습    | 100        | 2026-07-15 12:00:00.000000 | NULL                       | 1

SET @as_of = '2026-07-15 10:30:00.000000';

SELECT version_no, title, view_count, status
FROM post_revision
WHERE post_id = @post_id
  AND valid_from <= @as_of
  AND (valid_to IS NULL OR @as_of < valid_to);

SELECT p.id AS post_id
FROM posts AS p
LEFT JOIN post_revision AS h
  ON h.post_id = p.id AND h.valid_to IS NULL
WHERE p.id = @post_id
GROUP BY p.id
HAVING COUNT(h.version_no) <> 1;

SELECT a.post_id, a.version_no, b.version_no
FROM post_revision AS a
JOIN post_revision AS b
  ON b.post_id = a.post_id AND b.version_no > a.version_no
WHERE (b.valid_to IS NULL OR a.valid_from < b.valid_to)
  AND (a.valid_to IS NULL OR b.valid_from < a.valid_to);

SELECT s.id AS post_id
FROM posts AS s
JOIN post_revision AS h
  ON h.post_id = s.id AND h.valid_to IS NULL
WHERE NOT (s.title <=> h.title)
   OR NOT (s.view_count <=> h.view_count)
   OR NOT (s.status <=> h.status)
   OR NOT (s.published_at <=> h.published_at);

version_no | title                 | view_count | status
1          | 회원 게시판 최종 모델 | 0          | PUBLISHED