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

splatoon_equipments.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
;


/* アタマ */
CREATE TABLE IF NOT EXISTS heads (
  head_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  data_path TEXT NOT NULL
);

INSERT INTO heads (name, data_path) VALUES ('ギョウザヘッド', 'heads/1');
INSERT INTO heads (name, data_path) VALUES ('チャーハンマスク', 'heads/2');
INSERT INTO heads (name, data_path) VALUES ('レバニラヘッドホン', 'heads/3');


/* フク */
CREATE TABLE IF NOT EXISTS clothes (
  clothes_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  data_path TEXT NOT NULL
);

INSERT INTO clothes (name, data_path) VALUES ('ギョウザT', 'clothes/1');
INSERT INTO clothes (name, data_path) VALUES ('チャーハンホワイト', 'clothes/2');
INSERT INTO clothes (name, data_path) VALUES ('レバニラローブ', 'clothes/3');


/* クツ */
CREATE TABLE IF NOT EXISTS shoes (
  shoes_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  data_path TEXT NOT NULL
);

INSERT INTO shoes (name, data_path) VALUES ('ギョウザサンダル', 'shoes/1');
INSERT INTO shoes (name, data_path) VALUES ('チャーハンサポーター', 'shoes/2');
INSERT INTO shoes (name, data_path) VALUES ('レバニラブーツ', 'shoes/3');


/* ブキ */
/* kind: 1(シューター), ... */
/* sub_weapon: 1(スプラッシュボム), ... */
/* special: 1(グレートバリア), ... */
CREATE TABLE IF NOT EXISTS weapons (
  weapon_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  kind SMALLINT NOT NULL,
  sub_weapon SMALLINT NOT NULL,
  special SMALLINT NOT NULL
);

INSERT INTO weapons (name, kind, sub_weapon, special) VALUES ('わかばシューター', 1, 1, 1);
INSERT INTO weapons (name, kind, sub_weapon, special) VALUES ('スプラシューター', 1, 1, 1);
INSERT INTO weapons (name, kind, sub_weapon, special) VALUES ('.52ガロン', 1, 1, 1);


/* gear */
/* only_part: 1(アタマ), 2(フク), 3(クツ) */
CREATE TABLE IF NOT EXISTS gears (
  gear_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  effect INTEGER NOT NULL,
  only_main BOOLEAN NOT NULL,
  only_part SMALLINT
);

INSERT INTO gears (name, effect, only_main, only_part) VALUES ('イカ速', 2, FALSE, NULL);
INSERT INTO gears (name, effect, only_main, only_part) VALUES ('サブインク効率', 3, FALSE, NULL);
INSERT INTO gears (name, effect, only_main, only_part) VALUES ('イカニンジャ', 4, TRUE, 1);


/* equipment_commons */
CREATE TABLE IF NOT EXISTS equipment_commons (
  equipment_common_id SERIAL PRIMARY KEY,
  rarity SMALLINT NOT NULL DEFAULT 1,
  main_gear INTEGER NOT NULL REFERENCES gears(gear_id),
  gear1 INTEGER REFERENCES gears(gear_id),
  gear2 INTEGER REFERENCES gears(gear_id),
  gear3 INTEGER REFERENCES gears(gear_id)
);

INSERT INTO equipment_commons (main_gear, gear1, gear2, gear3) VALUES (1, 1, 1, 1);
INSERT INTO equipment_commons (main_gear, gear1, gear2, gear3) VALUES (3, 1, 1, 2);
INSERT INTO equipment_commons (main_gear, gear1, gear2, gear3) VALUES (2, 2, 2, 2);


/* got_heads */
CREATE TABLE IF NOT EXISTS got_heads (
  got_head_id SERIAL PRIMARY KEY,
  player_id INTEGER NOT NULL REFERENCES players(player_id),
  head_id INTEGER NOT NULL REFERENCES heads(head_id),
  equipment_common_id INTEGER NOT NULL REFERENCES equipment_commons(equipment_common_id)
);

INSERT INTO got_heads (
  player_id,
  head_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT head_id FROM heads WHERE head_id = 1),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 1)
);

INSERT INTO got_heads (
  player_id,
  head_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT head_id FROM heads WHERE head_id = 2),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 2)
);

INSERT INTO got_heads (
  player_id,
  head_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 2),
  (SELECT head_id FROM heads WHERE head_id = 3),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 3)
);


/* got_clothes */
CREATE TABLE IF NOT EXISTS got_clothes (
  got_clothes_id SERIAL PRIMARY KEY,
  player_id INTEGER NOT NULL REFERENCES players(player_id),
  clothes_id INTEGER NOT NULL REFERENCES clothes(clothes_id),
  equipment_common_id INTEGER NOT NULL REFERENCES equipment_commons(equipment_common_id)
);

INSERT INTO got_clothes (
  player_id,
  clothes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT clothes_id FROM clothes WHERE clothes_id = 1),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 1)
);

INSERT INTO got_clothes (
  player_id,
  clothes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT clothes_id FROM clothes WHERE clothes_id = 2),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 2)
);

INSERT INTO got_clothes (
  player_id,
  clothes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 2),
  (SELECT clothes_id FROM clothes WHERE clothes_id = 3),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 1)
);


/* got_shoes */
CREATE TABLE IF NOT EXISTS got_shoes (
  got_shoes_id SERIAL PRIMARY KEY,
  player_id INTEGER NOT NULL REFERENCES players(player_id),
  shoes_id INTEGER NOT NULL REFERENCES shoes(shoes_id),
  equipment_common_id INTEGER NOT NULL REFERENCES equipment_commons(equipment_common_id)
);

INSERT INTO got_shoes (
  player_id,
  shoes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT shoes_id FROM shoes WHERE shoes_id = 1),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 1)
);

INSERT INTO got_shoes (
  player_id,
  shoes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT shoes_id FROM shoes WHERE shoes_id = 2),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 2)
);

INSERT INTO got_shoes (
  player_id,
  shoes_id,
  equipment_common_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 2),
  (SELECT shoes_id FROM shoes WHERE shoes_id = 3),
  (SELECT equipment_common_id FROM equipment_commons WHERE equipment_common_id = 1)
);


/* got_weapons */
CREATE TABLE IF NOT EXISTS got_weapons (
  got_weapon_id SERIAL PRIMARY KEY,
  player_id INTEGER NOT NULL REFERENCES players(player_id),
  weapon_id INTEGER NOT NULL REFERENCES weapons(weapon_id),
  skill SMALLINT NOT NULL DEFAULT 0,
  paint_point BIGINT NOT NULL DEFAULT 0,
  number_of_use INTEGER NOT NULL DEFAULT 0,
  number_of_win INTEGER NOT NULL DEFAULT 0
);

INSERT INTO got_weapons (
  player_id,
  weapon_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT weapon_id FROM weapons WHERE weapon_id = 1)
);

INSERT INTO got_weapons (
  player_id,
  weapon_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT weapon_id FROM weapons WHERE weapon_id = 2)
);

INSERT INTO got_weapons (
  player_id,
  weapon_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 2),
  (SELECT weapon_id FROM weapons WHERE weapon_id = 1)
);


/* equipment_statuses */
CREATE TABLE IF NOT EXISTS equipment_statuses (
  player_id INTEGER NOT NULL PRIMARY KEY REFERENCES players(player_id),
  selected_head_id INTEGER NOT NULL REFERENCES got_heads(got_head_id),
  selected_clothes_id INTEGER NOT NULL REFERENCES got_clothes(got_clothes_id),
  selected_shoes_id INTEGER NOT NULL REFERENCES got_shoes(got_shoes_id),
  selected_weapon_id INTEGER NOT NULL REFERENCES got_weapons(got_weapon_id)
);

INSERT INTO equipment_statuses (
  player_id,
  selected_head_id,
  selected_clothes_id,
  selected_shoes_id,
  selected_weapon_id
) VALUES (
  (SELECT player_id FROM players WHERE player_id = 1),
  (SELECT got_head_id FROM got_heads WHERE got_head_id = 1),
  (SELECT got_clothes_id FROM got_clothes WHERE got_clothes_id = 1),
  (SELECT got_shoes_id FROM got_shoes WHERE got_shoes_id = 1),
  (SELECT got_weapon_id FROM got_weapons WHERE got_weapon_id = 1)
);


/* player_id=1の装備情報 */
WITH JOINED_EQUIPMENT_STATUSES AS (
    SELECT
      player_id,
      h.name AS アタマ,
      c.name AS フク,
      s.name AS クツ,
      w.name AS ブキ
    FROM
      equipment_statuses AS et
    JOIN
      (SELECT got_head_id, name FROM got_heads JOIN heads ON got_heads.head_id = heads.head_id) AS h
    ON
      et.selected_head_id = h.got_head_id
    JOIN
      (SELECT got_clothes_id, name FROM got_clothes JOIN clothes ON got_clothes.clothes_id = clothes.clothes_id) AS c
    ON
      et.selected_clothes_id = c.got_clothes_id
    JOIN
      (SELECT got_shoes_id, name FROM got_shoes JOIN shoes ON got_shoes.shoes_id = shoes.shoes_id) AS s
    ON
      et.selected_shoes_id = s.got_shoes_id
    JOIN
      (SELECT got_weapon_id, name FROM got_weapons JOIN weapons ON got_weapons.weapon_id = weapons.weapon_id) AS w
    ON
      et.selected_shoes_id = w.got_weapon_id
  )

SELECT
  p.player_id AS プレイヤーID,
  p.name AS ナマエ,
  jes.アタマ,
  jes.フク,
  jes.クツ,
  jes.ブキ
FROM
  players AS p
JOIN
  JOINED_EQUIPMENT_STATUSES AS jes
ON
  p.player_id = jes.player_id
WHERE
  p.player_id = 1
;

/* result */
--  プレイヤーid |  ナマエ  |     アタマ     |   フク    |       クツ       |       ブキ       
-- --------------+----------+----------------+-----------+------------------+------------------
--             1 | ぎょうざ | ギョウザヘッド | ギョウザT | ギョウザサンダル | わかばシューター
-- (1 row)