メインコンテンツへスキップ
見出し画像

ソシャゲのサーバーを作る。テーブル設計とマスターデータの投入(2)

    前回はサーバーを立てて、jsonを返すところまでやりました。

    今回は、データベース周りの準備をしていこうと思います。
    データベースとは何か的なところはすでにたくさんの情報であふれかえっていますのでそちらを参考にしてみてください。

    前回の作業で箱はできたので、今回は中身を入れていきます。
    テーブルを設計して、マスターデータを流し込むところまでですね。

    今回もローカルのdocker環境で動かす前提で進めます。
    外部に公開する構成やセキュリティまわりは扱いませんので。
    あとコンソールに出てくるパスワードは伏せ字にしています。

    その前に、データには2種類ある

    いきなりCREATE TABLEを書き始めてもいいのですが、
    先に整理しておいたほうがいい話です。

    ゲームのデータは、大きく2つに分かれます。

    マスターデータ

    ゲームのルールが入っているデータです。
    ヒーローの名前、ステータス、レアリティ、ガチャの確率、イベントの期間、ショップの商品などなど。
    ゲームで使われるデータを指します。
    全プレイヤーに共通で、ゲーム中に書き換わることはありません。

    ユーザーデータ

    プレイヤーが遊んだ結果できるデータです。 誰がどのキャラを持っているか、レベルはいくつか、所持金はいくらか。
    プレイヤーごとに違って、遊ぶたびに増えていきます。

    この2つ、扱い方がほぼ正反対です。

    マスターデータは、更新するときは全部消して入れ直します。
    キャラを1体調整しただけでも、テーブルごと作り直して全件入れ直す。
    差分更新というのもありますが、こちらの方が安全です。
    というのも差分が大量にあったらどうするか…..alter面倒ですね。
    変更漏れがあったらまずいです。

    ユーザーデータは、文字通りユーザーが作ったデータです。
    ユーザーの行動によって作って書き換えてというデータです。
    このデータには例えばキャラの所持状況やレベル、もちろん課金したものやプロフィールなんかも入ります。
    ロストするとまずい案件です。

    他にもゲームのメンテナンス状態や、お知らせ、ランキングなど、いわゆるゲームの設定を管理するシステムデータがあります。
    これもマスターデータと言えなくもないですが、スタティックなデータではなく書き換わっていくものなので、今回は独立して持たせることにしました。

    同じ「データ」なのに、片方は消していいもの、もう片方は絶対に消せないもの。 この2つを同じ場所に置いておくと、いつか間違えます。

    なので、データベースを分けます

    今回は3つに分けました。

    api_game_master  マスターデータ
    api_game_user    ユーザーデータ
    api_game_system  メンテナンス情報、お知らせなど

    物理的には1つのMySQLの中に3つ作るだけです。
    本番だとuserだけ別のクラスタに載せる、みたいなことをやりますが、そこは規模の話なので今は同居で問題ありません。

    ユーザー数が多くなると水平・垂直分割なんてのも出てきますが、このソシャゲでは単一のユーザーデータで進めます。

    分けておくと、まず操作を間違えにくいです。
    「masterを全消しして入れ直す」作業と
    「userを触る」作業が、そもそも別のデータベースに対する操作になるので。

    もうひとつ、あとから分けられます。
    最初から分けておけば、userだけ別サーバーに引っ越すときも接続先を変えるだけで済みますね。

    データベースを作る

    rootで入ります。

    $ docker compose exec db mysql -uroot -p****
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.

    3つ作ります。

    CREATE DATABASE api_game_master DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    
    CREATE DATABASE api_game_user   DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    
    CREATE DATABASE api_game_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    前回.envで作ったgameapiは使わなくなるので、消しておきます。

    DROP DATABASE gameapi;

    そしてアプリが使うユーザーに権限を付けます。

    GRANT ALL PRIVILEGES ON `api\_game\_%`.* TO 'gameuser'@'%';
    FLUSH PRIVILEGES;

    アンダースコアをエスケープしているのは、MySQLの権限指定では_が1文字ワイルドカードとして解釈されるからですね。

    エスケープしないとapi1game2masterのようなデータベースにも権限が付いてしまいます。
    実害が出ることはまず無いですが、意図した範囲だけに付けておきたいので。

    確認しておきます。

    mysql> SHOW GRANTS FOR 'gameuser'@'%';
    +------------------------------------------------------------+
    | Grants for gameuser@%                                      |
    +------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `gameuser`@`%`                       |
    | GRANT ALL PRIVILEGES ON `gameapi`.* TO `gameuser`@`%`      |
    | GRANT ALL PRIVILEGES ON `api\_game\_%`.* TO `gameuser`@`%` |
    +------------------------------------------------------------+

    ……gameapiが残ってますね。

    データベースは消したのに権限だけ残っています。
    害があるわけではないですが、後で同じ名前のデータベースを作ったときに、見えてしまうので消しておきます。
    まあ大体ほったらかして後で気づくというパターンですね。

    REVOKE ALL PRIVILEGES ON `gameapi`.* FROM 'gameuser'@'%';

    ゲーム用アカウントで確認する

    rootで作った結果を見ても、ゲームサーバー側から見えるかは分かりません。
    gameuserで入り直します。

    $ docker compose exec db mysql -ugameuser -p****
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | api_game_master    |
    | api_game_system    |
    | api_game_user      |
    | information_schema |
    | performance_schema |
    +--------------------+
    5 rows in set (0.00 sec)
    

    3つ見えていますね。
    mysqlやsysが見えていないのは、権限が絞れている証拠です。

    docker-compose.ymlを更新する

    接続先が3つになったので、appのenvironmentを直します。

        environment:
          TZ: Asia/Tokyo
          PORT: 3000
          DB_HOST: db
          DB_PORT: 3306
          DB_USER: ${MYSQL_USER}
          DB_PASSWORD: ${MYSQL_PASSWORD}
          DB_NAME_MASTER: api_game_master
          DB_NAME_USER: api_game_user
          DB_NAME_SYSTEM: api_game_system

    前回のDB_NAME: ${MYSQL_DATABASE}は消します。

    データベース名を.envに置かず直接書いたのは、環境によって変わらない値だからですね。
    ホスト名やパスワードは開発と本番で違いますが、データベース名は同じです。
    変わるものは外に出す、変わらないものは書いてしまう、くらいの区別でいいと思います。

    テーブルを設計する

    マスターデータは結構な数になるのですが、
    今回はキャラクターのマスタを作ります。
    題材が「ヒーローコレクション」なので、テーブル名はheroです。

    DROP TABLE IF EXISTS `hero`;
    CREATE TABLE `hero` (
      `id` BIGINT NOT NULL,
      `name` VARCHAR(64) NOT NULL,
      `rarity_code` VARCHAR(4) NOT NULL,
      `role` INT NOT NULL,
      `element` INT NOT NULL,
      `base_hp` INT NOT NULL,
      `base_atk` INT NOT NULL,
      `base_def` INT NOT NULL,
      `base_spd` INT NOT NULL,
      `normal_skill_id` BIGINT NOT NULL,
      `ultimate_skill_id` BIGINT NOT NULL,
      `max_level` SMALLINT NOT NULL,
      `is_playable` TINYINT(1) NOT NULL,
      `sort_no` INT NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    キャラ1種類でこれだけの列が要ります。
    多いと感じるかもしれませんが、実際のタイトルだともっと増えます。
    というかもっと細分化したマスターデータが増えていく感じです。
    1項目につき1つの別のマスターデータがある、なんてことも。

    IDを自動採番にしない

    AUTO_INCREMENTを付けていません。
    マスターデータのIDは基本的に自動で割り振るのではなく、
    作る側が「何番はこれ」「その番号を使ってこの番号が決まる」みたいに分かってて振るのでデータベースに決めさせてはいけません。
    IDは投入した順番ではありません。
    開発環境と本番でIDがずれたら、クライアントが持っている画像やテキストと紐付かなくなったりすることもあります。

    ユーザーデータのほうは逆で、そちらは自動採番に任せます。
    誰が決めてもいい代わりに絶対に重複してはいけないので、データベースに任せるのが確実ですね。

    外部キー制約は張らない

    ゲームの開発では、外部キーをあまり使わないんですね。(会社によるかもですが)
    hero.rarity_codeからレアリティのマスタへ制約を張る、というのが教科書的な作りですが。

    マスタは毎回DROPして入れ直しますし、テーブルを別のデータベースに分けたら張れなくなります。

    つまり、存在しないコードが入っていないことをデータベースは保証してくれません。 そこは投入する前に、ツールなりアプリなりが確かめる役目になります。

    今は数値で持っているもの

    roleとelementが数値なのは、別のマスタがある前提だからですね。数字の意味はマスタが持つもので、
    コードにif (role == 3)とは書きません。
    今回は省略しています。

    nameも、いずれこの列から消えます。
    多言語対応するとテキストは別のマスターに逃がすので。

    rarity_codeも同じで、'N'や'SR'という文字列が入っていますが、レアリティのマスタ自体は別にあります。
    倍率や表示名はそちらが持つので、heroはコードだけ持っている形ですね。

    さすがに文字列でレアリティは持たないので、後で数値に変えます。
    マスターなので作っては壊しての繰り返しです(笑)

    今は全部同じ値の列

    max_levelが全キャラ60で揃っています。
    無駄に見えますが、これはマスタに出しておくのが正解です。
    定数としてコードに書くと、変えたい日にビルドが必要になりますから。
    「今は全部同じだけど、いつかキャラごとに変わるかもしれないもの」はデータ側ですね。

    is_playableも同じ発想です。
    これがあるから、未実装のキャラをデータだけ先に入れておけます。
    運用が始まってからは、こういうフラグでリリース日を制御することになります。

    マスターデータを投入する

    ヒーローテーブルと、テーブルに入れるデータを作りました。

    DROP TABLE IF EXISTS `hero`;
    
    CREATE TABLE `hero` (
      `id` BIGINT NOT NULL,
      `name` VARCHAR(64) NOT NULL,
      `rarity_code` VARCHAR(4) NOT NULL,
      `role` INT NOT NULL,
      `element` INT NOT NULL,
      `base_hp` INT NOT NULL,
      `base_atk` INT NOT NULL,
      `base_def` INT NOT NULL,
      `base_spd` INT NOT NULL,
      `normal_skill_id` BIGINT NOT NULL,
      `ultimate_skill_id` BIGINT NOT NULL,
      `max_level` SMALLINT NOT NULL,
      `is_playable` TINYINT(1) NOT NULL,
      `sort_no` INT NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `hero` (`id`, `name`, `rarity_code`, `role`, `element`, `base_hp`, `base_atk`, `base_def`, `base_spd`, `normal_skill_id`, `ultimate_skill_id`, `max_level`, `is_playable`, `sort_no`) VALUES
    (1, '見習い剣士', 'N', 1, 1, 115, 28, 13, 12, 1, 2, 60, 1, 1),
    (2, '疾風の剣士', 'R', 1, 3, 125, 31, 12, 14, 3, 4, 60, 1, 2),
    (3, '村の弓手', 'N', 2, 3, 92, 30, 8, 15, 5, 6, 60, 1, 3),
    (4, '蒼弓の狙撃手', 'R', 2, 2, 100, 33, 9, 16, 7, 8, 60, 1, 4),
    (5, '紅蓮の魔道士', 'R', 3, 1, 88, 35, 8, 13, 9, 10, 60, 1, 5),
    (6, '焔帝ヴァルカ', 'SR', 3, 1, 95, 40, 9, 14, 11, 12, 60, 1, 6),
    (7, '新米僧侶', 'N', 4, 2, 105, 15, 12, 12, 13, 14, 60, 1, 7),
    (8, '聖光の大巫女', 'SR', 4, 2, 118, 18, 14, 13, 15, 16, 60, 1, 8),
    (9, '旅の吟遊詩人', 'R', 5, 3, 100, 22, 11, 15, 17, 18, 60, 1, 9),
    (10, '氷嵐の歌姫リーゼ', 'SR', 5, 2, 108, 26, 12, 16, 19, 20, 60, 1, 10);

    消して、作って、入れる。
    さっきの「全消しして入れ直す」ですね。

    このデータベースのテーブルやデータを入れる作業ですが、
    マスターデータエディタがあると楽です。

    プロンプト載せているのでよかったらどうぞ。

    そして流します。

    docker compose exec -T db mysql -uroot -p**** api_game_master < hero.sql
    mysql: [Warning] Using a password on the command line interface can be insecure.

    確認します。

    mysql> select * from hero;
    +----+--------------------------+-------------+------+---------+---------+----------+----------+----------+-----------------+-------------------+-----------+-------------+---------+
    | id | name                     | rarity_code | role | element | base_hp | base_atk | base_def | base_spd | normal_skill_id | ultimate_skill_id | max_level | is_playable | sort_no |
    +----+--------------------------+-------------+------+---------+---------+----------+----------+----------+-----------------+-------------------+-----------+-------------+---------+
    |  1 | 見習い剣士          | N           |    1 |       1 |     115 |       28 |       13 |       12 |               1 |                 2 |        60 |           1 |       1 |
    |  2 | 疾風の剣士          | R           |    1 |       3 |     125 |       31 |       12 |       14 |               3 |                 4 |        60 |           1 |       2 |
    |  3 | 村の弓手             | N           |    2 |       3 |      92 |       30 |        8 |       15 |               5 |                 6 |        60 |           1 |       3 |
    |  4 | 蒼弓の狙撃手       | R           |    2 |       2 |     100 |       33 |        9 |       16 |               7 |                 8 |        60 |           1 |       4 |
    |  5 | 紅蓮の魔道士       | R           |    3 |       1 |      88 |       35 |        8 |       13 |               9 |                10 |        60 |           1 |       5 |
    |  6 | 焔帝ヴァルカ       | SR          |    3 |       1 |      95 |       40 |        9 |       14 |              11 |                12 |        60 |           1 |       6 |
    |  7 | 新米僧侶             | N           |    4 |       2 |     105 |       15 |       12 |       12 |              13 |                14 |        60 |           1 |       7 |
    |  8 | 聖光の大巫女       | SR          |    4 |       2 |     118 |       18 |       14 |       13 |              15 |                16 |        60 |           1 |       8 |
    |  9 | 旅の吟遊詩人       | R           |    5 |       3 |     100 |       22 |       11 |       15 |              17 |                18 |        60 |           1 |       9 |
    | 10 | 氷嵐の歌姫リーゼ | SR          |    5 |       2 |     108 |       26 |       12 |       16 |              19 |                20 |        60 |           1 |      10 |
    +----+--------------------------+-------------+------+---------+---------+----------+----------+----------+-----------------+-------------------+-----------+-------------+---------+
    10 rows in set (0.00 sec)

    入っているのを確認できました。
    結構表示はズレまくってますが。

    実はソシャゲ的なゲームからマスターを作った

    今回のマスターデータ、元は前回作ったソシャゲ的なゲームのコードに直接書いてありました。
    配列で持っていたやつですね。

    さすがに手打ちはなと思ってClaudeでマスターデータエディタで使える形に変換させて、それを使いました。

    入力や変換は量が多くなると手作業はしんどいですね。
    ゲームを作るためのツール作りもゲーム作りの一部です(笑)

    今回はここまで

    • データベースを3つに分けた

    • ヒーローのテーブルを設計した

    • マスターデータを投入した

    ヒーローのデータは入りました。
    ただ、この状態ではまだ「誰がどのキャラを持っているか」が分かりません。

    次回はユーザーデータのほうを作って、APIから返すところまでやろうと思います。

    【最後まで読んでいただきありがとうございます!】 面白かったと感じた方は、ぜひ「スキ」と「フォロー」をお願いします!

     
     
    泥臭い現場のリアルを語る現役エンジニア ゲーム開発歴30年。会社員・フリーランスの両方で修羅場をくぐり抜けてきた50代現役サーバプログラマです

    あなたへのおすすめ