目次

1. はじめに
 2. 作ったアプリについて
 3. 開発の流れ
 4. プロジェクトを通して学んだこと

1. はじめに

Vtuberが歌枠で歌った曲を検索できる歌枠データベースを制作したのでその記録を記します。

作成したアプリはこちらのリンクから見ることができます。

2. 作ったアプリについて

2-1 アプリの概要
 2-2 アプリの3つの特徴
 2-3 アプリの画面

2-1 アプリの概要

各チャンネルごとに、今までに歌った曲の一覧を見ることができるようになっています。
曲名と歌手名で検索をする他、詳細検索からは配信日や配信のタイトル名で絞り込むことができるようになっています。

2-2 アプリの3つの特徴

1. メンテナンスフリー

本アプリでは、YoutubeAPIを用いて動画のコメント欄を取得し、OpenAIを用いて解析を行うことで、
配信で歌った曲のデータベースが自動で更新されるようにしています。

Youtubeの配信のコメント欄で、配信で歌われた曲(セトリ)を書いてくれている人がいるため、そのコメントを解析することで新しいセトリのデータが入るようにします。

詳細な手順は以下の通りです。

cron jobがRailsのrakeタスクを叩く
YoutubeAPIから新しい動画を取得
YoutubeAPIからコメントを取得
OpenAIでコメントを解析→歌った曲をjsonとして取得
更新したデータを反映

さらに、OpenAIでの解析はしばしば誤りが含まれていることがあるため、有志のユーザーがセトリを修正できる仕組みも搭載しています。歌情報の変更履歴を保存しておくことで、誤った情報を入力したユーザーがいても簡単に修正ができるようにしました。

2. 詳細な検索が可能

曲のタイトル名・歌手名に加え、配信日や配信のタイトルで絞り込みができるようにしました。
これによって探したい曲を素早く見つけることができます。

3. レスポンシブデザイン

本アプリはパソコンだけでなくスマートフォンからも見やすいUIを提供することにこだわりました。

既存の歌枠データベースはスプレッドシートで管理しているものが多く、スプレッドシートではスマートフォンから使うことができないという問題がありました。
レスポンシブなデザインにすることで、スマホからでも気軽に検索ができるようにしました。

2-3 アプリの画面

トップページ
チャンネルを選択します。

チャンネル画面
選択したチャンネルについて見ることができます。

左側にはこれまでに歌った曲の一覧が表示されます。検索欄にはタイトル/歌手名を入力して絞り込みをします。

右側にはこれまでの歌枠の一覧が表示されます。動画を選択すると、その動画の中で歌われた曲の一覧を見ることができます。

メンテナンス画面
左側から歌を1つ選択すると、その歌が歌われた時間・タイトル名・歌手名を編集できるようになっています。

編集フォームにはこれまでの修正履歴が表示されており、誤った情報が登録された場合は過去の状態に簡単に戻せるようになっています。

3. 開発の流れ

2023年3月〜5月で要件定義・開発を行いました。開発全体を通して設計・実装を全て1人で行いました。

時期	内容
3月	要件定義・設計
4・5月	開発

使用技術

バックエンド

Rails
テスト: Rspec

フロントエンド

Next.js(React.js)
Typescript
テスト: jest

4. プロジェクトを通して学んだこと

4-1. 履歴情報の保持

開発初期の頃は、下図のように1つの歌を表すsong_itemsテーブルと歌情報の変更履歴を表すsong_diffsテーブルを一対多の関係で持ち、歌のタイトル名や歌手名を参照する際はcreated_atが最新のsong_diffが持つタイトル名や歌手名を参照するという方法をとっていました。

しかし、この方法では以下のような問題が発生しました。

タイトル等の情報を取得するために毎回「created_atが最新であるsong_diffをselectする」というクエリを発行する必要があり、クエリが複雑になる
「歌の一覧」を取得するためには、各歌に対して「created_atが最新であるsong_diffをselectする」というクエリを発行する必要があり、N+1問題が生じる

そこで、この問題を解決するために、下図のようにsong_itemsテーブルにlatest_diff_idを追加するという修正を行いました。song_itemsは「最新のsong_diffのid」を保持しておき、song_diffをinsertする際にはトランザクションを張ってsong_itemのlatest_diffを必ず更新する、という運用を行うことにしました。

左: 変更前のER図, 右: 変更後のER図

この運用方法には次のようなメリット・デメリットがあります。

メリット
- 曲のタイトルや歌手名を参照する際に、単純なテーブルのjoinで取得が可能
- latest_diff_idを用いてテーブルをjoinすれば歌の一覧を取得する際にN+1問題が発生しない
デメリット
- song_diffをinsertする際に毎回song_itemを更新する必要があり、運用上バグが発生しやすい

今回は、N+1問題に起因するレスポンスの遅れが体感できるレベルだったため、デメリットを許容してこのような実装方式を採用しました。
履歴情報を保持するという仕組みを作るのは初めてだったため、いい勉強になりました。