くろねこ。の実験室

LaravelのDB構造をリファクタしてみた。

Date: 2022/06/18 18:46

Category: none

はじめに

副業で2年くらい前にLaravelで作ったWebサイトがある。本当はLaravelを使う必要はほとんどないのだが、わがままを言ってLaravelで作成させていただいた。そのサイト専用のCMSを作ると言う点ではなかなか良い勉強になった。

ただし、その頃はLaravelを勉強しながらサイトを作っていたし、実業務でもあまりテーブル設計やテストコードの実装などをやっていなかったのもあり、メンテナンスの観点ではまだまだ未熟なものである。

たとえば、今回は全体的にテストコードを拡充し、Laravelのバージョンアップの準備を進めようと思っていたのだが、そこにあったのはテストを書くのも一苦労するようなControllerに処理が直書きされているようなコードだった。
このControllerのテストを書きながら、Ajaxで記事をリクエストする処理がイケてないことに気付きリファクタを始めた。(どの記事を見ながら書いたのか忘れたが、ControllerでURLを生成し、それをテンプレートに埋め込まれたjsへ渡し、再検索をかけるためajaxエンドポイントにgetでリクエストしているようなコードだ... 自分で書いておきながら恐ろしいコードである...)

そのように非同期での記事検索を本来あるべきajaxでの実装に置き換えたとき、既存の機能のうち動かない部分を見つけた。そのサイトの記事にはキーワードを表すタグが付いているのだが、そのタグの付いている記事を正しく検索できなくなっていたのだ。

そのサイトではタグの文字列を用いて検索するようにしていたが、POSTエンドポイントとはいえど入力される値の形式は整えておきたいとのことでそのタグを管理するIDで検索するように変更していた。だがどうやらそのタグのIDが正しく取得できていないと言うことが判明し、はじめてDBをみることにしたのだった...

table:tags

id	name	column_id
1	タグ1	1
2	タグ1	2
3	タグ2	2
4	タグ3	2

上記のようなテーブル構造をしていた。おそらく作成当初はタグを使う場所なんてあんまりないし、完全な関係テーブルにする必要はないと判断したのかもしれない... (それでも完全な関係テーブルにしておくべきだっただろう...)

このテーブルにおいてIDはnameとcolumn_idのペアに対するものであり、本来のtagに対してのものではない。そのため、nameではなくidで単純に検索をかけると、同じタグ文字列なのに別なIDでヒットするというものになってしまっている。タグ一覧の画面で3つのタグだけが表示されることを期待したのに、nameが同じでもidが異なるので4つのタグが表示されるという間抜けな状態になってしまった。

そこでこの問題を解消するために、tagsとcolumnsという2つのテーブルの関係テーブル tag_columnsに分割することにした。

やること

中途半端に実装されてしまった関係テーブルもどきを以下のように完全な関係テーブルにする。

table:tags

id	name	created_at	updated_at
1	タグ1
2	タグ2
3	タグ3

table:tag_columns

id	tag_id	column_id
1	1	1
2	1	2
3	2	2
4	3	2

やったこと

まず元のテーブルをリネームし、完全に参照用のテーブルにした。

1-- rename table
2ALTER TABLE tags RENAME TO tags_bk;
3

新しくtags(タグの情報しか持ってない本来の意味のtags)とtagsとcolumnsの関係だけを定義した関係テーブル tag_columnsをそれぞれ定義する。(SequelProで新しいテーブルを作成しカラムを追加してから、TableInfoをコピペしたものが以下のもの)

1-- create table tags
2CREATE TABLE `tags` (
3  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
4  `name` varchar(255) DEFAULT NULL,
5  `created_at` datetime DEFAULT NULL,
6  `updated_at` datetime DEFAULT NULL,
7  PRIMARY KEY (`id`)
8) ENGINE=InnoDB DEFAULT CHARSET=utf8;
9
10-- create table tag_columns
11CREATE TABLE `tag_columns` (
12  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
13  `tag_id` int(11) DEFAULT NULL,
14  `column_id` int(11) DEFAULT NULL,
15  `created_at` datetime DEFAULT NULL,
16  `updated_at` datetime DEFAULT NULL,
17  PRIMARY KEY (`id`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
19

もともとのテーブル(tags_bkにリネームされている)からtagsに入るべき情報だけを引っ張ってきてINSERTする。

1-- もともとのテーブルをtags_bkとしデータを入れ直す。
2INSERT INTO tags(name,created_at,updated_at)
3select distinct name,NOW(),NOW() from tags_bk;
4

関係テーブルも同様にtags_bkとtagsから入るはずの値を検索してきてINSERTした。

1-- 関係テーブルにINSERT
2INSERT INTO tag_columns(tag_id,column_id,created_at,updated_at)
3select tags.id,column_id,tags_bk.created_at,tags_bk.updated_at from tags_bk
4inner join tags ON tags.name = tags_bk.name;
5

最後にもう不要となったもとの中途半端な関係テーブルもどきを削除すれば完成。

1-- 以前のテーブル定義を削除する
2DROP TABLE tags_bk;
3

ここまでの流れをlaravelのmigrationでやる。
その前にtags, tag_columnsから tags_bkを復元するクエリも書き出しておく。(rollbackができるように)

1-- 中途半端な関係テーブルのテーブル定義
2CREATE TABLE `tags` (
3  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
4  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
5  `column_id` int NOT NULL,
6  `created_at` timestamp NULL DEFAULT NULL,
7  `updated_at` timestamp NULL DEFAULT NULL,
8  PRIMARY KEY (`id`)
9) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
10
11-- 中途半端な関係テーブルの復元
12INSERT INTO tags_bk(name, column_id, created_at, updated_at)
13select name,column_id, tag_columns.created_at, tag_columns.updated_at from tag_columns
14inner join tags ON tags.id = tag_columns.tag_id;
15
16-- 新しく作ったテーブルを削除する
17DROP TABLE tags, tag_columns;
18
19-- 復元したテーブルをrename
20ALTER TABLE tags_bk RENAME TO tags;
21

あとはひたすら laravelのmigrationファイルにこれらを書いていけばいい。

laravelでテーブルをrenameする時は、Schema::rename(from, to);を使えばできる。

Laravel 6.x データベース：マイグレーション

RenameTagsTagsBk.php
1<?php
2
3use Illuminate\Database\Migrations\Migration;
4use Illuminate\Database\Schema\Blueprint;
5use Illuminate\Support\Facades\Schema;
6
7/**
8 * 関係テーブル分割のためのテーブル名の一時変更migration
9 * tags -> tags_bk
10 */
11class RenameTagsTagsBk extends Migration
12{
13    /**
14     * Run the migrations.
15     *
16     * @return void
17     */
18    public function up()
19    {
20        Schema::rename('tags','tags_bk');
21    }
22
23    /**
24     * Reverse the migrations.
25     *
26     * @return void
27     */
28    public function down()
29    {
30        Schema::rename('tags_bk','tags');
31    }
32}
33
34

新しく作る tags, tag_columnsの定義に関しては以下のように定義してやればよい。
upのところでcreate文を書き、down(rollback時の処理)ではテーブルを削除(drop)している。

1    public function up()
2    {
3        Schema::create('tags', function (Blueprint $table) {
4            $table->bigIncrements('id');
5            $table->string('name', 255);
6            $table->timestamps();
7        });
8    }
9
10　　public function down()
11    {
12        Schema::dropIfExists('tags');
13    }
14
15

tagsやtag_columnsに値を流し込む作業もmigrationファイルに記述してしまって良いらしい。
tag_columnsの方では、tags_bkにtagsを INNER JOIN して取得した各項目を1行ずつforeachの中でINSERTする処理を書いている。これで既存データのお引っ越しは完了だ。
downの方ではtag_columnsをTRUNCATEしている。(tag_columnsに値がない状態にすればいいのでこれでOK)

1
2/**
3 * 関係テーブル tag_columnsにデータを流し込む
4 */
5class TransferTagsBkToTagColumns extends Migration
6{
7    /**
8     * Run the migrations.
9     *
10     * @return void
11     */
12    public function up()
13    {
14        $tag_columns = DB::table('tag_columns');
15        $originals = DB::table('tags_bk')
16            ->select('tags.id','column_id','tags_bk.created_at','tags_bk.updated_at')
17            ->join('tags','tags_bk.name','tags.name')->get();
18        foreach($originals as $row) {
19            $tag_columns->insert([
20                'tag_id' => $row->id,
21                'column_id' => $row->column_id,
22                'created_at' => $row->created_at,
23                'updated_at' => $row->updated_at,
24            ]);
25        }
26    }
27
28    /**
29     * Reverse the migrations.
30     *
31     * @return void
32     */
33    public function down()
34    {
35        $tags = DB::table('tag_columns');
36        $tags->truncate();
37    }
38}
39
40

最後にリネームしたtags_bkを削除(drop)すればOKである。
ただし、削除したものをrollbackするにはtags_bkを作ること、tags,tag_columnsから中身を作ってINSERTしてやることが必要になるので以下のように少し面倒だ。

1  public function down()
2    {
3        Schema::create('tags_bk', function (Blueprint $table) {
4            $table->bigIncrements('id');
5            $table->string('name', 255);
6            $table->Integer('column_id');
7            $table->timestamps();
8        });
9
10        $tags = DB::table('tags_bk');
11        $originals = DB::table('tag_columns')
12            ->select('tags.id','name','column_id','tag_columns.created_at','tag_columns.updated_at')
13            ->join('tags','tag_columns.tag_id','tags.id')->get();
14        foreach($originals as $row) {
15            $tags->insert([
16                'name' => $row->name,
17                'column_id' => $row->column_id,
18                'created_at' => $row->created_at,
19                'updated_at' => $row->updated_at,
20            ]);
21        }
22    }
23
24

というステップを踏んで、テーブル分割作業をSQLのクエリからmigrationファイルに書き起こすことができた。

まとめ

DBの構造がいけてないと後々の改修の際に苦労することになる。ただ、そんな時でも元のデータを保ちながらDB構造のリファクタをすることはできるようだ。
まず,クエリでどうすればリファクタできるかを考えてから、Laravelのmigrationファイルに書き起こす。migrationの中でデータの引越しも書いてしまえばいざと言う時にrollbackして元の構造に戻すこともできるし、変更したときの記録が残って良い。

というわけでテーブル構造のリファクタをしたのだが、少々面倒ではあったので、最初から適切にテーブルを設計しておくことの大切さを再認識させられた。
また、この変更によりLaravelのEloquentModelやロジックの修正は必要そうなのでおいおいやっていこうと思う。

まさき。です。PHPエンジニアをやってます。

自分の課題を技術で乗り越えるの好きかもしれないです。

フロントエンドは苦手ですが、少しでもできるようになれたらな、ということでNextJSでこのブログサイトを作りました。