조인 테이블 상속

DROP TABLE IF EXISTS joined_text_bad;
DROP TABLE IF EXISTS joined_image_bad;
DROP TABLE IF EXISTS joined_post_bad;
CREATE TABLE joined_post_bad (
  post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_type VARCHAR(16) NOT NULL,
  title VARCHAR(160) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE joined_text_bad (
  post_id BIGINT UNSIGNED PRIMARY KEY,
  body_chars INT NOT NULL,
  FOREIGN KEY (post_id) REFERENCES joined_post_bad (post_id)
) ENGINE=InnoDB;
CREATE TABLE joined_image_bad (
  post_id BIGINT UNSIGNED PRIMARY KEY,
  image_width_px INT NOT NULL,
  FOREIGN KEY (post_id) REFERENCES joined_post_bad (post_id)
) ENGINE=InnoDB;

INSERT INTO joined_post_bad (post_type, title) VALUES ('TEXT', '자식 없는 부모');
SET @bad_parent = LAST_INSERT_ID();
INSERT INTO joined_post_bad (post_type, title) VALUES ('TEXT', '자식 두 개인 부모');
SET @double_parent = LAST_INSERT_ID();
INSERT INTO joined_text_bad VALUES (@double_parent, 20);
INSERT INTO joined_image_bad VALUES (@double_parent, 1200);

SELECT p.post_id, p.post_type,
       (t.post_id IS NOT NULL) + (i.post_id IS NOT NULL) AS child_count
FROM joined_post_bad AS p
LEFT JOIN joined_text_bad AS t ON t.post_id = p.post_id
LEFT JOIN joined_image_bad AS i ON i.post_id = p.post_id
ORDER BY p.post_id;

post_id | post_type | child_count
1       | TEXT      | 0
2       | TEXT      | 2

DROP PROCEDURE IF EXISTS create_text_post;
DROP TABLE IF EXISTS member_post_joined;
DROP TABLE IF EXISTS link_post_detail;
DROP TABLE IF EXISTS image_post_detail;
DROP TABLE IF EXISTS text_post_detail;
DROP TABLE IF EXISTS posts_joined;

CREATE TABLE posts_joined (
  post_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_type VARCHAR(16) NOT NULL,
  title VARCHAR(160) NOT NULL,
  canonical_url VARCHAR(700) NOT NULL,
  is_complete BOOLEAN NOT NULL DEFAULT FALSE,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_joined_canonical_url (canonical_url),
  CONSTRAINT chk_posts_joined_type CHECK (post_type IN ('TEXT', 'IMAGE', 'LINK')),
  CHECK (is_complete IN (0, 1))
) ENGINE=InnoDB;
CREATE TABLE text_post_detail (
  post_id BIGINT UNSIGNED PRIMARY KEY,
  body_chars INT UNSIGNED NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts_joined (post_id) ON DELETE CASCADE,
  CHECK (body_chars BETWEEN 1 AND 1000000)
) ENGINE=InnoDB;
CREATE TABLE image_post_detail (
  post_id BIGINT UNSIGNED PRIMARY KEY,
  alt_text VARCHAR(255) NOT NULL,
  image_width_px INT UNSIGNED NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts_joined (post_id) ON DELETE CASCADE,
  CHECK (image_width_px BETWEEN 1 AND 20000)
) ENGINE=InnoDB;
CREATE TABLE link_post_detail (
  post_id BIGINT UNSIGNED PRIMARY KEY,
  link_kind VARCHAR(16) NOT NULL,
  target_url VARCHAR(1000) NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts_joined (post_id) ON DELETE CASCADE,
  CHECK (link_kind IN ('EXTERNAL', 'INTERNAL', 'DOWNLOAD'))
) ENGINE=InnoDB;
CREATE TABLE member_post_joined (
  member_id BIGINT UNSIGNED NOT NULL,
  post_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (member_id, post_id),
  FOREIGN KEY (member_id) REFERENCES members (id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES posts_joined (post_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

DELIMITER //
CREATE PROCEDURE create_text_post(
  IN p_title VARCHAR(160),
  IN p_url VARCHAR(700),
  IN p_body_chars INT UNSIGNED,
  OUT p_post_id BIGINT UNSIGNED
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
  START TRANSACTION;
  INSERT INTO posts_joined (post_type, title, canonical_url)
  VALUES ('TEXT', p_title, p_url);
  SET p_post_id = LAST_INSERT_ID();
  INSERT INTO text_post_detail VALUES (p_post_id, p_body_chars);
  UPDATE posts_joined SET is_complete = TRUE WHERE post_id = p_post_id;
  COMMIT;
END//
DELIMITER ;

SET @member_id = (SELECT id FROM members ORDER BY id LIMIT 1);
CALL create_text_post('joined 상속 읽기', 'https://example.com/joined', 35, @joined_text);
INSERT INTO member_post_joined VALUES (@member_id, @joined_text);

START TRANSACTION;
INSERT INTO posts_joined (post_type, title, canonical_url)
VALUES ('IMAGE', '잠금 구조 이미지', 'https://example.com/lock-image');
SET @joined_image = LAST_INSERT_ID();
INSERT INTO image_post_detail VALUES (@joined_image, '잠금 범위 구조', 2400);
UPDATE posts_joined SET is_complete = TRUE WHERE post_id = @joined_image;
COMMIT;

SELECT r.post_id, r.post_type, r.is_complete,
       (a.post_id IS NOT NULL) AS has_text,
       (v.post_id IS NOT NULL) AS has_image
FROM posts_joined AS r
LEFT JOIN text_post_detail AS a ON a.post_id = r.post_id
LEFT JOIN image_post_detail AS v ON v.post_id = r.post_id
ORDER BY r.post_id;

post_id | post_type | is_complete | has_text | has_image
1       | TEXT      | 1           | 1        | 0
2       | IMAGE     | 1           | 0        | 1

SELECT r.post_id, r.post_type,
       (a.post_id IS NOT NULL) + (v.post_id IS NOT NULL)
         + (l.post_id IS NOT NULL) AS child_count
FROM posts_joined AS r
LEFT JOIN text_post_detail AS a ON a.post_id = r.post_id
LEFT JOIN image_post_detail AS v ON v.post_id = r.post_id
LEFT JOIN link_post_detail AS l ON l.post_id = r.post_id
WHERE (a.post_id IS NOT NULL) + (v.post_id IS NOT NULL)
        + (l.post_id IS NOT NULL) <> 1;

SELECT r.post_id, r.post_type FROM posts_joined AS r
LEFT JOIN text_post_detail AS a ON a.post_id = r.post_id
LEFT JOIN image_post_detail AS v ON v.post_id = r.post_id
LEFT JOIN link_post_detail AS l ON l.post_id = r.post_id
WHERE (r.post_type = 'TEXT') <> (a.post_id IS NOT NULL)
   OR (r.post_type = 'IMAGE') <> (v.post_id IS NOT NULL)
   OR (r.post_type = 'LINK') <> (l.post_id IS NOT NULL);

SELECT post_id FROM posts_joined
WHERE is_complete = FALSE
  AND created_at < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;

SELECT x.member_id, x.post_id FROM member_post_joined AS x
JOIN posts_joined AS r ON r.post_id = x.post_id
WHERE r.is_complete = FALSE;