SQLのストアドプロシージャー使ってCMS多言語化の設計を考えてみた

ここしばらくCMS評価をしてましたが、 
「多言語化がサードパーティのプラグインでサポートされてる」
場合が多いのが気になります。本体をバージョンアップしたら多言語化プラグインが対応してない場合、最悪はサイト全てがダウンするかも（試してないので分からないけど）。

ということで、CMSなどで多言語化に対応するためのデータベース設計について考えてみました。で、今回は初めてストアドプロシージャーを使ってみました。

なんて、実際に走らせてないので動きませんが、上手に使えば便利そうですね→ストアドプロシージャー。

◆単一言語のページデータ


まぁ次のような構造じゃないでしょうか？

page_contents
  page_id     serial PRIMARY KEY,
  lang_code   char(2),
  title       text,
  content     text,
  active      date

languages
  lang_code    char(2),
  lang_name    text,
  lang_default char(1)

各ページにつきひとつレコード作成。
レコード内にはタイトルとコンテンツ、そして言語コードも入れてみた。こういう設計だと、複数言語データを入れることは出来ても、ウェブサイトの動きは制限されるでしょう。

なので、title・contentといった文字が入るところを多言語化しようと思います。

◆多言語化したページデータ

pages
  page_id     serial PRIMARY KEY,
  title_id    int,
  content_id  int,
  active      date

nodes
  node_id     serial PRIMARY KEY,
  active      date

contents
  node_id     int,
  lang_code   char(2),
  contents    text,
  PRIMARY KEY( node_id, lang_code )

ここでpagesのタイトルとコンテンツは、それぞれtitle_idとcontent_idに変更してnode_idをさします。で、node_idと指定された言語コードから、contentsテーブル内にあるcontentsを取ってくることが出来ます。

この設計のいいところは、多言語化できる項目･テーブルに制限がないことです。たとえばコメントも多言語化しようと思ったら、たとえば次のように作れます。

comments
  comment_id  serial PRIMARY KEY,
  page_id     int,
  title_id    int,
  comment_id  int,
  active      date

◆ストアドプロシージャーで多言語データを読み書き修正

とはいえ、こんな設計のテーブルに書き込んだり読み込むのは大変。
そこでストアドプロシージャーを使ってみました。

-- 新しいページを書き込む
INSERT pages SET
  title_id  =new_contents( '...title...', '{l_code}' ),
  content_id=new_contents( '..content..', '{l_code}' ),
  active=CURR_DATE();

DELIMITER //
CREATE FUNCTION new_contents( content text, l_code char(2) ) 
RETURNS int
BEGIN
  DECLARE n_id int;

  IF l_code IS NULL THEN 
  SELECT lang_code INTO l_code 
    FROM languages
    WHERE lang_code='l_code'
    LIMIT 1;

  INSERT nodes; 
  SELECT last_insert_id() INTO n_id;

  INSERT contents SET
    contents='{content}',
    node_id ='n_id'    

  RETURN n_id;

END;
//

-- ページデータを読み込む
SELECT page_id, 
  get_contents( title_id,   '{l_code}' ) AS title, 
  get_contents( content_id, '{l_code}' ) AS contents
FROM pages 
WHERE page_id='{id}' 

DELIMITER //
CREATE FUNCTION get_contents( n_id int, l_code char(2) ) 
RETURNS text
BEGIN
  DECLARE text content;

  SELECT contents INTO content 
    FROM contents
    WHERE node_id='n_id' AND lang_code='l_code'
    LIMIT 1;

  IF content IS NOT NULL THEN RETURN contents;

  SELECT contents INTO content 
    FROM contents JOIN languages USING( lang_code )
    WHERE node_id='n_id' AND lang_default=TRUE
    LIMIT 1;

  RETURN contents;

END;
//

生まれた初めて書いたストアドプロシージャーと思う。
ちなみにMySQLのV5.1を参考にして書いてみた。

書込・読込のときに言語コード（l_code）を指定します。値がなかったら言語テーブルのデフォルト言語コードを使います。

nodesテーブルの構造が激しく簡単すぎるが･･･
もうちょっと改善できるかな？
そもそも動くのか？

まぁ動くと仮定して・・・
いや～、実は、昔から、 ストアドプロシージャーを使ってみようかなと思ってたんです。

SQL側の複雑さを上手に隠せば、使う側（つまりミドルウェア側）の負担を大きく減らせそうでいい感じです。

やはりSQLは「知っておいて損はない」と思った次第。