スプラトゥーン3のER図を考える【キャラクター編】のスタイル側のSQL

splatoon_style.sql

/* プレイヤー情報 */
CREATE TABLE IF NOT EXISTS players (
  player_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  name_changed_at TIMESTAMP NOT NULL,
  last_loggined_at TIMESTAMP NOT NULL
);

INSERT INTO players (name, created_at, updated_at, name_changed_at, last_loggined_at) VALUES ('ぎょうざ', current_timestamp, current_timestamp, current_timestamp, current_timestamp);
INSERT INTO players (name, created_at, updated_at, name_changed_at, last_loggined_at) VALUES ('ちゃーはん', current_timestamp, current_timestamp, current_timestamp, current_timestamp);
INSERT INTO players (name, created_at, updated_at, name_changed_at, last_loggined_at) VALUES ('レバニラ', current_timestamp, current_timestamp, current_timestamp, current_timestamp);


/* 目の色 */
CREATE TABLE IF NOT EXISTS eye_colors (
  eye_color_id SERIAL PRIMARY KEY,
  data_path TEXT NOT NULL
);

INSERT INTO eye_colors (data_path) VALUES ('eye_colors/1');
INSERT INTO eye_colors (data_path) VALUES ('eye_colors/2');
INSERT INTO eye_colors (data_path) VALUES ('eye_colors/3');


/* 肌の色 */
CREATE TABLE IF NOT EXISTS skin_colors (
  skin_color_id SERIAL PRIMARY KEY,
  data_path TEXT NOT NULL
);

INSERT INTO skin_colors (data_path) VALUES ('skin_colors/1');
INSERT INTO skin_colors (data_path) VALUES ('skin_colors/2');
INSERT INTO skin_colors (data_path) VALUES ('skin_colors/3');


/* ヘアスタイル */
/* gender: 0(どちらでも), 1(ボーイ), 2(ガール) */
/* creature_type: 0(どちらでも), 1(イカ), 2(タコ) */
CREATE TABLE IF NOT EXISTS hair_styles (
  hair_id SERIAL PRIMARY KEY,
  gender SMALLINT NOT NULL,
  creature_type SMALLINT NOT NULL,
  data_path TEXT NOT NULL
);

/* イカボーイ専用 */
INSERT INTO hair_styles (gender, creature_type, data_path) VALUES (1, 1, 'hair_styles/1');

/* イカガール専用 */
INSERT INTO hair_styles (gender, creature_type, data_path) VALUES (2, 1, 'hair_styles/2');

/* タコボーイ専用 */
INSERT INTO hair_styles (gender, creature_type, data_path) VALUES (1, 2, 'hair_styles/3');

/* タコガール専用 */
INSERT INTO hair_styles (gender, creature_type, data_path) VALUES (2, 2, 'hair_styles/4');

/* 誰でも */
INSERT INTO hair_styles (gender, creature_type, data_path) VALUES (0, 0, 'hair_styles/5');


/* ヘアスタイル */
/* gender: 0(どちらでも), 1(ボーイ), 2(ガール) */
/* creature_type: 0(どちらでも), 1(イカ), 2(タコ) */
CREATE TABLE IF NOT EXISTS eye_brows (
  eye_brow_id SERIAL PRIMARY KEY,
  gender SMALLINT NOT NULL,
  creature_type SMALLINT NOT NULL,
  data_path TEXT NOT NULL
);

/* イカボーイ専用 */
INSERT INTO eye_brows (gender, creature_type, data_path) VALUES (1, 1, 'eye_brows/1');

/* イカガール専用 */
INSERT INTO eye_brows (gender, creature_type, data_path) VALUES (2, 1, 'eye_brows/2');

/* タコボーイ専用 */
INSERT INTO eye_brows (gender, creature_type, data_path) VALUES (1, 2, 'eye_brows/3');

/* タコガール専用 */
INSERT INTO eye_brows (gender, creature_type, data_path) VALUES (2, 2, 'eye_brows/4');

/* 誰でも */
INSERT INTO eye_brows (gender, creature_type, data_path) VALUES (0, 0, 'eye_brows/5');


/* ボトムス */
/* gender: 0(どちらでも), 1(ボーイ), 2(ガール) */
CREATE TABLE IF NOT EXISTS bottoms (
  bottom_id SERIAL PRIMARY KEY,
  gender SMALLINT NOT NULL,
  parent_id INTEGER,
  data_path TEXT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES bottoms(bottom_id)
);

INSERT INTO bottoms (gender, parent_id, data_path) VALUES (0, NULL, 'bottoms/1');
INSERT INTO bottoms (gender, parent_id, data_path) VALUES (1, NULL, 'bottoms/2');
INSERT INTO bottoms (gender, parent_id, data_path) VALUES (2, NULL, 'bottoms/3');
INSERT INTO bottoms (gender, parent_id, data_path) VALUES (0, 1, 'bottoms/1');


/* player_styles */
/* gender: 0(どちらでも), 1(ボーイ), 2(ガール) */
/* creature_type: 0(どちらでも), 1(イカ), 2(タコ) */
CREATE TABLE IF NOT EXISTS player_styles (
  player_id INTEGER NOT NULL PRIMARY KEY,
  gender SMALLINT NOT NULL,
  creature_type SMALLINT NOT NULL,
  eye_color_id INTEGER NOT NULL,
  skin_color_id INTEGER NOT NULL,
  hair_id INTEGER NOT NULL,
  eye_brow_id INTEGER NOT NULL,
  bottom_id INTEGER NOT NULL,
  FOREIGN KEY (player_id) REFERENCES players(player_id),
  FOREIGN KEY (eye_color_id) REFERENCES eye_colors(eye_color_id),
  FOREIGN KEY (skin_color_id) REFERENCES skin_colors(skin_color_id),
  FOREIGN KEY (hair_id) REFERENCES hair_styles(hair_id),
  FOREIGN KEY (eye_brow_id) REFERENCES eye_brows(eye_brow_id),
  FOREIGN KEY (bottom_id) REFERENCES bottoms(bottom_id)
);

/* イカガール */
INSERT INTO player_styles (
  player_id,
  gender,
  creature_type,
  eye_color_id,
  skin_color_id,
  hair_id,
  eye_brow_id,
  bottom_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  1,
  1,
  (SELECT eye_color_id FROM eye_colors WHERE eye_color_id = 1),
  (SELECT skin_color_id FROM skin_colors WHERE skin_color_id = 1),
  (SELECT hair_id FROM hair_styles WHERE hair_id = 1),
  (SELECT eye_brow_id FROM eye_brows WHERE eye_brow_id = 1),
  (SELECT bottom_id FROM bottoms WHERE bottom_id = 1)
);

/* イカボーイ */
INSERT INTO player_styles (
  player_id,
  gender,
  creature_type,
  eye_color_id,
  skin_color_id,
  hair_id,
  eye_brow_id,
  bottom_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 2),
  1,
  2,
  (SELECT eye_color_id FROM eye_colors WHERE eye_color_id = 2),
  (SELECT skin_color_id FROM skin_colors WHERE skin_color_id = 2),
  (SELECT hair_id FROM hair_styles WHERE hair_id = 2),
  (SELECT eye_brow_id FROM eye_brows WHERE eye_brow_id = 2),
  (SELECT bottom_id FROM bottoms WHERE bottom_id = 2)
);

/* タコガール */
INSERT INTO player_styles (
  player_id,
  gender,
  creature_type,
  eye_color_id,
  skin_color_id,
  hair_id,
  eye_brow_id,
  bottom_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 3),
  2,
  1,
  (SELECT eye_color_id FROM eye_colors WHERE eye_color_id = 3),
  (SELECT skin_color_id FROM skin_colors WHERE skin_color_id = 3),
  (SELECT hair_id FROM hair_styles WHERE hair_id = 3),
  (SELECT eye_brow_id FROM eye_brows WHERE eye_brow_id = 3),
  (SELECT bottom_id FROM bottoms WHERE bottom_id = 3)
);

/* player_id=1のスタイル情報の取得 */
WITH JOINED_BOTTOMS AS (
  SELECT
    b.bottom_id,
    b.gender,
    b.parent_id,
    b.data_path,
    (SELECT bb.data_path FROM bottoms AS bb WHERE bb.bottom_id = b.parent_id) AS sub_data_path
  FROM
    bottoms AS b
  ), JOINED_PLAYER_STYLES AS (
  SELECT
    ps.player_id,
    ps.gender AS 性別,
    ps.creature_type AS 種別,
    ec.data_path AS 目の色,
    sc.data_path AS 肌の色,
    hs.data_path AS ヘアスタイル,
    eb.data_path AS マユゲ,
    b.data_path AS ボトムス,
    b.sub_data_path AS ボトムスサブ
  FROM
    player_styles AS ps
  JOIN
    eye_colors AS ec ON ps.eye_color_id = ec.eye_color_id
  JOIN
    skin_colors AS sc ON ps.skin_color_id = sc.skin_color_id
  JOIN
    hair_styles AS hs ON ps.hair_id = hs.hair_id
  JOIN
    eye_brows AS eb ON ps.eye_brow_id = eb.eye_brow_id
  JOIN
    JOINED_BOTTOMS AS b ON ps.bottom_id = b.bottom_id
  )

SELECT
  p.player_id AS プレイヤーID,
  p.name AS 名前,
  ps.性別,
  ps.種別,
  ps.目の色,
  ps.肌の色,
  ps.ヘアスタイル,
  ps.マユゲ,
  ps.ボトムス,
  ps.ボトムスサブ
FROM
  players AS p
JOIN
  JOINED_PLAYER_STYLES AS ps
ON
  p.player_id = ps.player_id
WHERE
  p.player_id = 1
;

/* result */
--  プレイヤーid |   名前   | 性別 | 種別 |    目の色    |    肌の色     | ヘアスタイル  |   マユゲ    | ボトムス  | ボトムスサブ 
-- --------------+----------+------+------+--------------+---------------+---------------+-------------+-----------+--------------
--             1 | ぎょうざ |    1 |    1 | eye_colors/1 | skin_colors/1 | hair_styles/1 | eye_brows/1 | bottoms/1 | 
-- (1 row)