schemaspyでER図を生成

# はじめに

テーブル定義とかER図とかを資料にするのが面倒で、何かいいのないかと調べたところ[schemaspy](https://schemaspy.org/)を見つけました。
これでいい感じにDB仕様書がつくってみます。

自分でDBやテーブル定義を用意するのが面倒だったのでisuconの勉強も兼ねて[private-isu](https://github.com/catatsuy/private-isu)をお借りしました。

# private-isuを立ち上げる

前準備としてprivate-isuを立ち上げます。
立て方と後片付けのやり方は

https://qiita.com/masayasviel/items/353024f7f2e292572942

を参考にしてください。

また、上記に加えてschemaspyでmysqlへのアクセスを行うため、インバウンドルールを追加で設定する必要があります。

**MYSQL/Auroraをインバウンドルールに追加しましょう**

下記記事が参考になります。

https://qiita.com/nao-otsu/items/8cabd447d73b621cd07d

躓きポイントなのでしっかりやっておきましょう。
私はインバウンドルールが認識の外にあったので躓きました。

# インスタンスとmysqlに入る

SSH接続かなんかで上記で立てたインスタンスに入ります。

```sh
chmod 400 /path/to/my-key-pair.pem
ssh -i /path/to/my-key-pair.pem ubuntu@ec2-xxx-xxx-xxx-xxx.ap-northeast-1.compute.amazonaws.com
```

権限考えるの面倒なんでroot権限で入ります。普通に良くないのでやめた方がいいです。

```sh
sudo su -
```

mysqlもrootユーザで入ります。
入るのに必要な情報は[このへん](https://github.com/catatsuy/memo_isucon#mysql)を見ます。

```sh
mysql -u isuconp -p
```

ポートとホストを確認。

```sql
-- host
show variables like 'hostname';
-- port
show variables like 'port';
```

DBを確認。

```sql
show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| isuconp            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set
```

`isuconp` (今回schemaspyでER図を作るDB) も一応確認。

```sql
show tables from isuconp;

+-------------------+
| Tables_in_isuconp |
+-------------------+
| comments          |
| posts             |
| users             |
+-------------------+
3 rows in set
```

良さげではないでしょうか。

# ユーザーの作成とアクセス権限の付与

MySQL Serverに外部から接続するために、外部接続用ユーザの作成と権限付与を行います。
下記のようにクエリを叩くと、権限がlocalhostしかないことがわかります。
これでは外部からのアクセスができません！

```sql
select user, host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| isuconp          | localhost |
| root             | localhost |
| ...etc           | localhost |
+------------------+-----------+
x rows in set
```

なので作ります。

```sql
create user "schemaspy"@"%" identified by "%password%";

select user, host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| schemaspy        | %         |
| isuconp          | localhost |
| root             | localhost |
| ...etc           | localhost |
+------------------+-----------+
x rows in set
```

権限の付与もするぞ！

```sql
grant all privileges on *.* to "schemaspy"@"%";
```

できたらmysqlの外に出て、mysqld.cnfを編集します。
下記2項目をIPフリーにします。SG側で設定しているから大丈夫でしょ(適当)

```
bind-address		= 0.0.0.0
mysqlx-bind-address	= 0.0.0.0
```

リスタートします。

```sh
sudo service mysql restart
```

この章は下記記事を参考にしました。

https://qiita.com/tocomi/items/0c009d7299584df49378

# schemaspy

schemaspyを動かします。
ここからは上記と異なり、EC2インスタンス状ではなく、手元で動かします。
下記記事を参考にして[schemaspy/schemaspy](https://github.com/schemaspy/schemaspy)から最新のものをzipで落としてきます。

https://qiita.com/hiro5963/items/c2a73158a85c50a05714

私はdockerで動かしたので下記のコマンドを叩きました。

```sh
docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot \
 -t mysql -host %EC2インスタンスのパブリックIP%:3306 -db isuconp -u schemaspy -p %password% -connprops useSSL\\=false -s isuconp
```

上記を叩いて

```
Public Key Retrieval is not allowed schemaspy
```

と出るようなら、 `connprops` に `allowPublicKeyRetrieval\\=false` を加えます。(もしかしたらtrueかも)

https://qiita.com/noy__/items/f53e9e6210e02233d414

https://qiita.com/ucan-lab/items/76316c2723bdcce6eee1

すると `path/to/schemaspy-x.y.z/schema` に色々出来上がるので、その中のindex.htmlを見てみます。

```sh
open ./schemaspy-x.y.z/schema/index.html
```

やったぜ！

![スクリーンショット 2024-07-27 17.50.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/299962/45b77e6d-e382-3bc8-6200-d89f3bcfe49f.png)

初期状態では下記の通りリレーションがないので、ER図もへったくれもありません。

```sql
DESC isuconp.posts;
+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| id         | int         | NO   | PRI | NULL              | auto_increment    |
| user_id    | int         | NO   |     | NULL              |                   |
| mime       | varchar(64) | NO   |     | NULL              |                   |
| imgdata    | mediumblob  | NO   |     | NULL              |                   |
| body       | text        | NO   |     | NULL              |                   |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-------------+------+-----+-------------------+-------------------+
```

リレーションを追加してみましょう。

# リレーションを追加する

再びEC2インスタンスに戻ります。

alter tableに時間がかかるので、ここではpostsにだけ外部キーを貼ります。

```sql
use isuconp;
ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users (id);
DESC posts;

+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| id         | int         | NO   | PRI | NULL              | auto_increment    |
| user_id    | int         | NO   | MUL | NULL              |                   |
| mime       | varchar(64) | NO   |     | NULL              |                   |
| imgdata    | mediumblob  | NO   |     | NULL              |                   |
| body       | text        | NO   |     | NULL              |                   |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-------------+------+-----+-------------------+-------------------+
```

# 再びschemaspy

手元に戻って再びschemaspyを動かします。

```sh
docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot \
 -t mysql -host %EC2インスタンスのパブリックIP%:3306 -db isuconp -u schemaspy -p %password% -connprops allowPublicKeyRetrieval\\=false useSSL\\=false -s isuconp
```

index.htmlをみます。

```sh
open ./schemaspy-x.y.z/schema/index.html
```

user_idに外部キーが載ってる！

![スクリーンショット 2024-07-27 19.26.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/299962/0148811d-9e82-38cc-9eb8-456568573dfb.png)

Foooooooo!!　勝ち！！

![スクリーンショット 2024-07-27 19.27.29.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/299962/f9957fcc-5aad-62b5-6c48-6581abf8a8de.png)

# おわりに

schemaspyでDB仕様書をいい感じに生成できました！
あとはschem配下をgh-pagesかなにかに公開すればみんなで確認できます。私は[gh-pages](https://www.npmjs.com/package/gh-pages)を使ってデプロイしました。
そしてAWSリソースはお金がどんどん飛んでいくのでちゃんと後片付けをしましょう。
読んでいただきありがとうございました。

# memo

https://www.npmjs.com/package/gh-pages
を用いたデプロイ設定

```package.json
{
  "homepage": "https://{username}.github.io/{repository-name}",
  "scripts": {
    "deploy": "gh-pages -d schemaspy-x.y.z/schema -b docs"
  },
}
```