ベジータです。先月コミックフェスタのシステムリニューアルをリリースしました。
見た目こそ変わりませんが、裏側では大きな変更を行いました。
今日は、リニューアルプロジェクトで躓いたDBの照合順序についてまとめてみたいと思います。
PostgreSQL v9.6.7の環境を前提に書いていきます。
照合順序とは、文字の大小関係のルールのことです。
「文字列の比較」や、「並び替え」でどのような基準に基づいて文字の大小関係を扱うかを設定します。
照合順序の設定は、DBの初期化時しか設定ができないため、慎重に検討する必要があります。
まずは、照合順序の設定確認方法です。次のSQLを発行することで照合順序の設定値を確認することができます。
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc_%'
| name | setting | context |
|---|---|---|
| lc_collate | C | internal |
| lc_ctype | C | internal |
| lc_messages | C | superuser |
| lc_monetary | C | user |
| lc_numeric | C | user |
| lc_time | C | user |
settingが「C」になっているのはCロケールといい、ロケールが未設定である状態を表しており、PostgreSQLのデフォルト設定となります。 以下では、それぞれの設定項目の意味について記載します。
lc_collate
lc_collateは、文字列の並び順の設定。
contextがinternalのため、データベース初期化時のみ設定可能。
| ロケール | |
|---|---|
| Cロケール | 文字のバイナリ値の順番 |
| jaロケール | 辞書順（清音→濁音→半濁音, カタカナ→ひらがな の順） |
lc_ctype
lc_ctypeは、文字の分類（大文字小文字の区別など）の設定。
contextがinternalのため、データベース初期化時のみ設定可能。
| ロケール | |
|---|---|
| Cロケール | 英半角アルファベットのみ変換 |
| jaロケール | 半角/全角共に変換 |
lc_messages
lc_messagesは、メッセージの言語の設定。 エラーメッセージは、英語の方がググりやすいので、個人的には英語設定のままで良いと思います。
| ロケール | |
|---|---|
| Cロケール | 英語 |
| jaロケール | 日本語 |
lc_monetary
lc_monetaryは、通貨の書式設定です。
| ロケール | SELECT '123456'::money;の結果 |
|---|---|
| Cロケール | $123,456.00 |
| jaロケール | ¥123,456 |
lc_numeric
lc_numericは、数字の書式設定。Cロケールとjaロケールによる違いはありませんが、国により小数点と区切り文字の表記が違うため、その設定を行います。
lc_time
lc_timeは、日付と時刻の書式設定です。
| ロケール | SELECT to_char('2018-01-01'::timestamp, 'YYYY-TMMonth-DD (TMDay)');の結果 |
|---|---|
| Cロケール | 2018-January-01 (Thursday) |
| jaロケール | 2018-1月-01 (木曜日) |
照合順序について調べているなかで、ロケール(国際化と地域化) | Let's Postgresに以下の記載を見つけました。
引用元: ロケール(国際化と地域化) | Let's Postgres
PostgreSQL ではロケール関連の処理を全てプラットフォームが用意する C ライブラリ (glibc, msvcrt 等) に任せているため、結果は環境依存で変化する場合があります。
環境依存ということは、サーバーの載せ替えなどでは注意しておく必要がありそうです。 PostgreSQLでは、日本語の照合順序は1種類ですが、SQL Serverでは、日本語の照合順序に、細かな設定ができたので、そのあたりの設定が環境依存なのかと思います。
おまけ：SQL Serverの照合順序
照合順序 – 文字の比較と並び順 (その 1) – Microsoft SQL Server Japan Support Team Blogによると、SQL Server 2012 SP1 では、日本語の照合順序だけでも138 個もの種類があるようです。 これは、大文字小文字、濁点半濁点の有無、ひらがなカタカナ、全角半角、などの区別をするか、しないかを柔軟に設定できるためです。 また、順序については、PostgreSQLで指定可能な「辞書順」以外に「部首画数順」も指定が可能です。
まとめ
リニューアルプロジェクトでは、デバッグ中に並び順が旧システムと一致しないことが判明したため、ロケールの設定漏れに気づきました。
もし、気づくのがリリース後だったら、lc_collateやlc_ctypeはDBの初期化時しか設定できないため、DB再構築のためにサービスを止める必要があり、損失に繋がるところでした。
今回のことで、初期設定は慎重に設定する必要があることを改めて実感しました。
