y4shiro commented 2 years ago

[x] DB 設計
[x] Supabase セッティング
[x] テーブル作成
- [x] card_name
- [x] card_rarity
- [x] card_type
- [x] charactors
- [x] cards
- [x] effects
- [x] unique_effects
- [x] card_effects
- [x] card_unique_effects
- [x] skills
- [x] card_event_skills
- [x] card_training_skills
- [x] status_gain
- [x] card_training_status_gain
[x] Supabase からデータ取得テスト

y4shiro commented 2 years ago

DB 設計

DB 設計や SQL の知識が必要だったので、事前に下記書籍を一通りした

ER 図

α版なので、後ほど修正する可能性あり unique_effects テーブルが必要か否かが不明、effects テーブルに一本化出来る可能性ある

uma_card drawio

cards と card_name / card_rarity / card_types / charactors はそれぞれ一対一 cards に対して card_effects と card_event_skills は一対多で必ず1つ以上は存在するが、 cards に対する card_unique_effects と card_training_skills は一対多だが存在しない場合もある

データソース

PC 版のウマ娘アプリは、ゲーム内のデータが master.mdb というファイルである程度管理されている
このファイルは暗号化などがされておらず、SQLite として読み込みが可能

y4shiro commented 2 years ago

Supabase セッティング

アカウント登録とプロジェクト作成の手順はここでは省略後ほど API key などが必要になるので控えておく

y4shiro commented 2 years ago

テーブル作成

ER 図の通りに作成し、単純なテーブルに関しては詳細を省略する

`card_name`

サポカの二つ名とキャラ名を結合した文字列を保存、このテーブルは cards テーブルから参照されるレアリティ R のサポカはほぼ共通の二つ名で、SR と SSR はユニークな物が設定されている

二つ名のみの値は master.mdb の text_data テーブル、category = 76 に存在している二つ名とキャラ名を結合した値は category = 75

select scd.id,
       card_name.name as value
  from support_card_data as scd
  join (select "index" as id,
               "text" as name
          from text_data
         where category = 75) as card_name
    on scd.id = card_name.id;

2022/09/04 現在、サポカは 238 種類実装されているのでレコード数もそれに準じている

`card_rarity`

現在 R / SR / SSR の 3 種類のみなので手打ちした

`card_types`

現在 7 種類なのでこちらも手打ち Speed / Stamina / Power / Guts / Wisdom / Friends / Group

`charactors`

カラムの構成はキャラクターの id と名前のみ master.mdb から抽出した

select "index" as id,
       "text" as name
  from text_data
 where category = 6;

`cards`

master.mdb 内の support_card_data テーブルをベースに、名称などは text_data テーブルを結合する

select scd.id,
       card_name.name as name,
       chara_name.name as charactor_name,
       case
         when scd.rarity = 1 then 'R'
         when scd.rarity = 2 then 'SR'
         when scd.rarity = 3 then 'SSR'
       end as rarity,
       case
         when scd.support_card_type = 1 then
           case
             when scd.command_id = 101 then 'Speed'
             when scd.command_id = 102 then 'Power'
             when scd.command_id = 103 then 'Guts'
             when scd.command_id = 105 then 'Stamina'
             when scd.command_id = 106 then 'Wisdom'
             else ''
           end
         when scd.support_card_type = 2 then 'Friends'
         when scd.support_card_type = 3 then 'Group'
         else ''
       end as "type",
       NULL as 'img_path'
  from support_card_data as scd
  join (select "index" as id,
               "text" as name
          from text_data
         where category = 75) as card_name
    on scd.id = card_name.id
  join (select "index" as id,
               "text" as name
          from text_data
         where category = 6) as chara_name
    on scd.chara_id = chara_name.id;

この SQL の結果を整形し Supabase に Bulk Insert した

`effects`

サポート効果一覧を格納するテーブル現在 31 種類あり、master.mdb から下記 SQL で抽出した

select "index", "text", NULL as img_path
  from text_data
 where category = 151;

img_path はアイコンの画像パスを後ほど挿入予定

`unique_effects`

固有サポート効果の一覧を格納予定だが、通常のサポート効果 effects と統合できる可能性が浮上した調査して分ける必要があれば改めて作成するが、一旦後回し

`card_effects`

cards と effects の中間テーブルサポート効果がサポートカード毎に複数あり、同じサポート効果でもカードごとに値が違うため、 card_id / effect_id の 2 つを PK とするレコードの生成が必要

これらの値自体は support_card_effect_table に記載されているが、少し癖のあるデータ構造をしているため実際の値を再現するには別途変換・補完を行う必要がある今回のデータソースは、Swagger で master.mdb の各テーブルを参照できる API を提供している方がいらっしゃったので、利用させて頂いた https://www.tracenacademy.com/index.html https://www.tracenacademy.com/api/supportcardeffecttable

データの変換・補完処理と SQL Query 生成は次のファイルで行っている uma-card-deck-tools/calcSupportEffectValue.ts at feature/#14-create-json-format-for-support-card · y4shiro/uma-card-deck-tools

`card_unique_effects`

cards と unique_effects の中間テーブル初期のサポートカードは effects テーブルと完全にリレーションがあったが、最新のサポカは effects テーブルに無い独自のサポート効果を持つようになったので再現が難しくなった unique_effects テーブル同様後回し

`skills`

master.mdb の複数テーブルを結合する必要がある

select td."index" as id, 
       td."text" as name,
       pt.need_skill_point as skill_pt,
       NULL as img_path
  from text_data as td
  left join single_mode_skill_need_point as pt
    on td."index" = pt.id
 where td.category = 47;

注意点として、キャラ固有のスキル と キャラ固有スキルを継承したスキル は別物扱いスキルの効果や取得 pt が異なるが、データ上でも別々の id が割り当てられている例えば、スペシャルウィークのスキル シューティングスター は本家と継承版で以下のようになっている本家 / id: 100011 / skill_pt: NULL 継承 / id : 900011 / skill_pt: 200

`card_event_skills`

cards と skills の中間テーブルで、サポカの育成イベント内で取得できるスキルを格納するこのテーブルのみ、master.mdb 内の結合による生成が行えなかった story_id に紐付いている可能性までは追えたが、skill_id と story_id を紐付けている箇所が確認できなかった私が見落としているだけの可能性もあるので、master.mdb 内のテーブルで生成が可能なら後日生成用のコードを実装する

サポートカード 1 枚あたり 1 ~ 8 のイベント取得スキルがあり、全体で 600 レコードほど愚直に手打ちすることも可能だが、今回は企業 Wiki や同様の Web サービスを公開してる箇所からの JSON を参考にしたデータの変換・補完処理と SQL Query 生成は次のファイルで行っている uma-card-deck-tools/createCardEventSkillsSql.ts at feature/#14-create-json-format-for-support-card · y4shiro/uma-card-deck-tools

`card_training_skills`

cards と skills の中間テーブルで、サポカのトレーンング時に取得できるスキルを格納する master.mdb の single_mode_hint_gain テーブルにレコードが含まれているので SQL で抽出する

select support_card_id as card_id,
       hint_value_1 as skill_id
  from single_mode_hint_gain
 where hint_gain_type = 0
 order by support_card_id;

注意点としては、トレーニングで取得できるスキルとステータスが同じカラムに混在している点 hint_gain_type カラムで条件分けされているので、スキルのみの抽出は容易だったステータスは後述する status_gain と中間テーブルの card_training_status_gain に分割して管理する

`status_gain`

single_mode_hint_gain テーブルに格納されているデータで、トレーニング時にスキルではなくステータス(Speed/Stamina など)やスキル Pt を増やす card_training_skills テーブルとは中身が異なるので新たなテーブルに分割したこちらはレコード 6 つなので手打ち

1 : speed 2 : stamina 3 : power 4 : guts 5 : wisdom 30 : skill_pt

`card_training_status_gain`

cards と status_gain の中間テーブル master.mdb から結合結果を抽出する SQL Query は下記の通り

select support_card_id as card_id,
       hint_value_1 as status_gain_id,
       hint_value_2 as status_value
  from single_mode_hint_gain
 where hint_gain_type = 1
 order by support_card_id;

card_training_skills テーブルの結果を抽出する SQL Query と where の条件以外同じこの値を Insert Query に整形して流し込んだ

y4shiro commented 2 years ago

Supabase からデータ取得テスト

Web アプリから Supabase の DB へのアクセスはいくつか手段がある

API

Supabase はデフォでテーブルごとの API が生えている APIs | Supabase

API には 3 種類あり、好きな手法を選べる

REST
Realtime
GraphQL (ベータ)

今回は用途的に REST or GraphQL のどちらか JavaScript ベースなら Supabase Client でアクセス、curl などは HTTP リクエスト

Edge Functions

Edge Functions から Supabase Client を利用して DB にアクセスすることも出来る Edge Functions | Supabase

curl でアクセスできるかテスト

Next.js への組み込みは別途 Issue を建てて行いたいので、今回は curl で DB の値を取得出来るかテストする

DB へのアクセスは API Key が必要なので控えておく

curl '<SUPABASE_URL>/rest/v1/cards' \
-H "apikey: <SUPABASE_ANON_KEY>" \
-H "Authorization: Bearer <SUPABASE_ANON_KEY>" | jq

結果

[
  {
    "id": 10001,
    "name": "[トレセン学園]スペシャルウィーク",
    "charactor_name": "スペシャルウィーク",
    "rarity": "R",
    "type": "Guts",
    "img_path": null,
    "created_at": "2022-09-03T14:13:10.385372+00:00",
    "updated_at": "2022-09-03T14:13:10.385372+00:00"
  },
  {
    "id": 10002,
    "name": "[トレセン学園]サイレンススズカ",
    "charactor_name": "サイレンススズカ",
    "rarity": "R",
    "type": "Speed",
    "img_path": null,
    "created_at": "2022-09-03T14:13:10.385372+00:00",
    "updated_at": "2022-09-03T14:13:10.385372+00:00"
  },
// ...

無事取得できた

y4shiro commented 2 years ago

Merge 完了したので Close

y4shiro / uma-card-deck-tools

DB 設計と Supabase にてテーブル作成 #14

DB 設計

ER 図

データソース

Supabase セッティング

テーブル作成

`card_name`

`card_rarity`

`card_types`

`charactors`

`cards`

`effects`

`unique_effects`

`card_effects`

`card_unique_effects`

`skills`

`card_event_skills`

`card_training_skills`

`status_gain`

`card_training_status_gain`

Supabase からデータ取得テスト

API

Edge Functions

curl でアクセスできるかテスト