PostgreSQL のサンプル DB dvdrental を日本語化した方法 SQL と CSV を併用して管理画面向けデータを作る
DVD レンタル管理アプリを日本語で使いやすくするために、今回は PostgreSQL のサンプル DB dvdrental をそのまま使うのではなく、画面に出るデータを段階的に日本語化しました。
やりたかったのは単純な一括置換ではありません。管理画面で見せる俳優名、顧客名、スタッフ名、住所、都市名、作品説明を、日本語 UI に合わせて段階的に置き換えていきました。
加えて、dvdrental の金額はドル前提のサンプルデータなので、日本語 UI に寄せるなら通貨も円利用へ合わせる必要がありました。文字列の翻訳だけでなく、金額列をドルから円へ変えたことも今回の重要なポイントです。
そのため、固定値として置き換えやすいマスタは SQL の case 式で処理し、件数が多い人名や住所、作品説明は CSV に書き出して翻訳し、最後に \copy で戻す、という 2 段階の方法を取りました。
つまり、PostgreSQL のサンプル DB dvdrental を日本語化する方法、PostgreSQL のデータを SQL と CSV で翻訳する方法、管理画面向けに dvdrental を日本語データへ置き換える方法を探している人向けの記事です。
今回ベースにした PostgreSQL のサンプル DB は、次のページから確認できます。
この記事で分かること
・dvdrental を日本語化するときに、どの列を翻訳対象にしたか
・固定的なマスタを SQL だけで翻訳した方法
・人手が必要なデータを CSV に出して翻訳した方法
・dvdrental の金額をドル前提から円利用へ変えた方法
・\copy と一時テーブルで安全に反映した方法
・日本語化済みデータを Docker の full dataset に反映した流れ
最初に決めたこと
最初に決めたのは、「画面に見せる自然言語だけを翻訳する」という方針です。
今回の対象は主に次の列です。
・俳優名
・顧客名
・スタッフ名
・住所
・地区名
・都市名
・国名
・カテゴリ名
・言語名
・映画タイトル
・映画説明
ここを最初に決めておくと、画面で見せたい自然言語を優先して日本語化しやすくなります。
さらに、文字列とは別に金額列もローカライズ対象として扱いました。film.rental_rate、film.replacement_cost、payment.amount はサンプル DB ではドル前提なので、そのまま 円 表示にするのではなく、円建ての値へ変換してから画面に載せる方針にしています。
方針は SQL だけで完結させなかった
やってみると、翻訳対象には 2 種類ありました。
- 件数が少なく、訳語がほぼ固定で決まるもの
- 件数が多く、文脈を見ながら訳したいもの
前者は SQL に直接書いた方が速く、後者は CSV に出して翻訳した方が圧倒的に扱いやすいです。
そのため、実際には次のように分けました。
・カテゴリ名、言語名、国名は SQL の case 式で更新
・俳優名、顧客名、住所、都市名、地区名、映画説明は CSV へ出力して翻訳
・スタッフ名は件数が少ないため個別の CSV 運用には分けず、sql/ja_localization_patch.sql の翻訳対象として扱える形にした
全体の流れは次のとおりです。
まずは固定マスタを SQL で翻訳した
カテゴリ名と言語名のように、件数が少なく訳語がぶれにくいものは sql/ja_localization_stage1.sql で処理しました。
やっていることはシンプルで、英語の値を case 式で日本語へ置き換えるだけです。
update public.category
set name = case name
when 'Action' then 'アクション'
when 'Animation' then 'アニメーション'
when 'Children' then '子ども向け'
when 'Comedy' then 'コメディ'
else name
end;
言語名も同じで、English を 英語、Japanese を 日本語 のように置き換えました。
この段階は「翻訳」というより「名称定義」に近いので、CSV に逃がすより SQL ファイルで管理した方が分かりやすいです。
国名は別 SQL に分けた
国名は件数が多く、case の行数も増えるため、sql/ja_localization_stage2_country.sql として別ファイルに分けました。
update public.country
set country = case country
when 'Japan' then '日本'
when 'United States' then 'アメリカ合衆国'
when 'France' then 'フランス'
else country
end;
1 ファイルに全部詰め込むより、カテゴリ・言語と国名を分けた方が見通しが良く、あとから見直しもしやすくなります。
件数の多いデータは CSV に出して翻訳した
俳優名、顧客名、スタッフ名、住所、都市名、映画説明のように件数が多いデータは、SQL の中に翻訳文を直接書く運用にしませんでした。
ここは次の流れで進めています。
- 対象データを
copy (...) to stdout with csv headerで出力する - CSV の空欄列に日本語を埋める
- UTF-8 の CSV として保存する
-
\copyで一時テーブルへ取り込む - translated 列が入っている行だけを update する
俳優名や顧客名、スタッフ名は ID 付きで扱った
たとえば俳優名は sql/export_actor_name_translation_targets.sql で、actor_id と英語名を CSV 出力しています。
copy (
select a.actor_id,
a.first_name as source_first_name,
a.last_name as source_last_name,
'' as translated_first_name,
'' as translated_last_name
from public.actor a
order by a.actor_id
) to stdout with csv header;
こうしておくと、翻訳作業側は translated_first_name と translated_last_name を埋めるだけで済みます。
反映時は sql/apply_actor_name_translation_from_csv.sql で一時テーブルを作り、actor_id をキーに更新しています。
\copy tmp_actor_name_translation (actor_id, source_first_name, source_last_name, translated_first_name, translated_last_name) from 'c:/Users/y_104/git/dvd-rental-admin/sql/actor_name_translation_work_utf8.csv' with (format csv, header true, encoding 'UTF8');
update public.actor a
set first_name = case
when t.translated_first_name is not null and btrim(t.translated_first_name) <> '' then t.translated_first_name
else a.first_name
end,
last_name = case
when t.translated_last_name is not null and btrim(t.translated_last_name) <> '' then t.translated_last_name
else a.last_name
end
from tmp_actor_name_translation t
where a.actor_id = t.actor_id;
空欄のまま残した列は更新しないようにしているので、途中まで翻訳した CSV でも安全に適用できます。
なお、staff.first_name と staff.last_name も考え方は同じです。スタッフは 2 件しかなかったため、俳優名や顧客名のように個別の CSV ファイルまでは切り出していませんが、sql/ja_localization_patch.sql では翻訳対象の抽出対象と update 対象に含めています。
住所と都市名は文脈付きで翻訳した
住所系は単独文字列だけで翻訳すると危険なので、sql/export_address_translation_targets.sql と sql/export_city_translation_targets.sql では文脈列も一緒に出しています。
住所の CSV には次のような情報を出しました。
・address_id
・source_address
・source_address2
・source_district
・source_city
・source_country
copy (
select a.address_id,
a.address as source_address,
coalesce(a.address2, '') as source_address2,
a.district as source_district,
ci.city as source_city,
co.country as source_country,
'' as translated_address,
'' as translated_address2,
'' as translated_district
from public.address a
join public.city ci on ci.city_id = a.city_id
join public.country co on co.country_id = ci.country_id
order by a.address_id
) to stdout with csv header;
都市名も国名付きで出しています。これで同名都市や似た表記の扱いを判断しやすくなります。
また、地区名は address テーブルの全行ごとではなく、重複をまとめた翻訳テーブルとして扱いたかったので、source_district を主キーにして更新する方式にしました。
映画説明は CSV を分割して翻訳した
いちばん量が多いのは film.description です。ここは 1 ファイルで処理すると作業しづらかったので、CSV を分割しています。
sql ディレクトリを見ると、次のように分割ファイルが並んでいます。
・film_description_translation_chunk1_utf8.csv
・film_description_translation_chunk2_utf8.csv
・film_description_translation_chunk3_utf8.csv
・...
・film_description_translation_chunk8_utf8.csv
翻訳作業自体は分割して進め、最終的には film_description_translation_work_utf8.csv にまとめて反映する形にしました。
反映側はとても単純で、film_id と翻訳済み説明だけを一時テーブルに入れて update しています。
\copy tmp_film_description_translation (film_id, translated_description) from 'c:/Users/y_104/git/dvd-rental-admin/sql/film_description_translation_work_utf8.csv' with (format csv, header true, encoding 'UTF8');
update public.film f
set description = t.translated_description
from tmp_film_description_translation t
where f.film_id = t.film_id
and t.translated_description is not null
and btrim(t.translated_description) <> '';
自由文はどうしても人手確認が必要なので、最初から chunk に分けたのは正解でした。
金額はドル前提のままにしなかった
今回の日本語化で、文字列の翻訳と同じくらい重要だったのが通貨の扱いです。
dvdrental はもともとドル建てのサンプル DB なので、film.rental_rate、film.replacement_cost、payment.amount などはその前提の値になっています。ここを英語データだけ日本語にしても、管理画面で 4.99 や 19.99 に対して 円 を付けるだけでは不自然です。
そのため、このリポジトリでは src/main/resources/db/migration/postgresql/V4__convert_currency_to_jpy.sql で金額列を円建てへ変換しています。
alter table film
alter column rental_rate type numeric(10,0) using ceil(rental_rate * 150),
alter column replacement_cost type numeric(10,0) using ceil(replacement_cost * 150),
alter column rental_rate set default 749,
alter column replacement_cost set default 2999;
alter table payment
alter column amount type numeric(10,0) using ceil(amount * 150);
1 ドルを 150 円として切り上げ変換しておくことで、サンプル DB の料金感を日本向けの管理画面で違和感なく扱えるようにしました。文字列の日本語化だけでなく、ドルから円への変換も合わせて入れたことで、一覧画面や詳細画面、集計画面の見え方がかなり自然になります。
一括管理用の日本語化パッチも用意した
途中で「翻訳対象をまとめて確認したい」という場面が出てきたので、sql/ja_localization_patch.sql も用意しました。
この SQL では一時テーブル tmp_ja_translation_map を作り、翻訳対象となる英語文字列をまとめて抽出しています。
create temporary table tmp_ja_translation_map (
table_name text not null,
column_name text not null,
source_text text not null,
translated_text text,
primary key (table_name, column_name, source_text)
);
そのうえで、カテゴリや言語の初期翻訳を update し、最後にベーステーブルへ反映する構成です。
このファイルは最終運用の中心というより、「どこを翻訳対象として扱うか」を一覧で確認しながら整理するための土台として役立ちました。
反映時に気をつけたこと
今回の日本語化で意識したのは、翻訳そのものよりも「壊さずに戻せること」です。
そのため、次の点を揃えました。
・元テーブルを直接いきなり書き換えず、一時テーブルを経由する
・翻訳列が空欄なら更新しない
・CSV は UTF-8 で統一する
・ID を持つものは ID ベースで更新する
・住所や都市のように文脈が必要なものは補助列を出す
この形にしておくと、翻訳作業が途中でも更新が壊れにくく、やり直しもしやすいです。
最後は日本語化済み DB を full dataset にした
日本語化したデータは、ローカル DB だけで終わらせず、最終的に docker/postgres/init/01-dvdrental-full.sql へ反映しています。
このリポジトリでは Docker 起動時にこの full dataset SQL を読み込むため、日本語化した状態をそのままアプリの初期データとして使えます。
つまり、流れとしては次のとおりです。
-
dvdrentalをベースに翻訳対象を整理する - SQL と CSV で日本語化する
- ローカル DB へ反映して確認する
-
pg_dumpで full dataset SQL を作り直す - Docker や AWS 初期化で同じデータを再利用する
この形にしておくと、日本語化が単発の加工で終わらず、開発環境と配布用データの両方に再利用できます。
まとめ
dvdrental の日本語化は、全部を 1 本の SQL で無理やり処理したわけではありません。
実際には、次のように分けて進めました。
・カテゴリ、言語、国名は SQL の case 式で翻訳
・俳優名、顧客名、スタッフ名、住所、都市名、地区名、映画説明を対象に整理した
・金額列はドル前提の値から円建てへ変換
・反映時は一時テーブルと \copy を使って安全に update
・最後に日本語化済み DB を full dataset として再利用
PostgreSQL のサンプル DB dvdrental を日本語化して管理画面アプリへ載せたい場合は、この「固定マスタは SQL、人手が必要な大量データは CSV」という分け方がかなり扱いやすかったです。
アプリ本体をどう作ったかは、こちらの記事にまとめています。
PostgreSQL のサンプル DB dvdrental をベースに Spring Boot + Thymeleaf で DVD レンタル管理アプリを作った話
AWS へ載せたときの構成や考え方は、こちらの記事にまとめています。
Spring Boot + Thymeleaf + PostgreSQL の管理画面アプリを AWS ECS/Fargate + RDS にデプロイするときの構成・運用・セキュリティ