恥ずかしながら本blogのシステムであるNucleusのアップグレードをさぼっておりました．
しかしまぁセキュリティ的なアレやコレやあるみたいなのでアップグレードを行いました．

手順は
・upgradesのスクリプトを実行してDB内容を更新
・overwritesを上書きして完了

簡単．

しかし見てみるとblog記事が盛大に文字化けしてる．
ということで原因調査．

■通常は大丈夫だがphpMyAdminで文字化け [Archive] - XREA&CORE SUPPORT BOARD

こちらの症状が当てはまります．
原因は昔に作ったMySQLのDBが文字コードlatin1だったため．
DBのエンコーディングがlatin1だと，中身がUTF-8でもSET NAMES utf-8;して扱うと文字化けします．

正しい状態では，DBもutf8指定，中身もUTF-8の文字列となっているはず．

しかし上記の記事にも「ただ、既に作成済みのデータについてはまた一から作り直すしかないかと思っているのですが・・・」とあります．
私も知る限りサクっとlatin1内のUTF-8文字列をutf8に持ってくる方法は知りません．

…じゃぁ自力でやるか．

そんなわけでPHPを書いて移行作業開始．
[Read More!]

文字列一致を見たいのだが，文字列には全角／半角，大文字／小文字，ひらがな／カタカナが含まれている．
これを一括で条件指定したい．
というときの解決法．
（PHP+ PostgreSQL/MySQLを想定）

■1.検索用に別カラムを持っておく
DBにあらかじめ mb_convert_encoding($str,'AcKV') 等で簡易に正規化(?)した文字列を持つためのカラムを作成する．
検索時は変換したカラムに対して指定する．

まぁ，これが楽か．
ただしINSERTもUPDATEも二度手間です．
そういうものだとして実装すれば良いわけですが．

■2.検索時にがんばる
2-1.translateする
translate(upper(str_column),'abc...','ａｂｃ...') LIKE '%str%'
ただしPostgreSQLにはtranslateがありますがMySQLには無いようです．
アルファベット26文字＋ひらがな50音＋α…面倒…

2-2.正規表現でがんばる
あらかじめPHPで '(s|S|ｓ|Ｓ)(t|T|ｔ|Ｔ)(r|R|ｒ|Ｒ)' みたいな文字列を作っておいて
str_column ~ '(s|S|ｓ|Ｓ)(t|T|ｔ|Ｔ)(r|R|ｒ|Ｒ)'
とする（MySQLの場合は ~ じゃなくて REGEXP）．

いずれにしろコスト高いなー．

■ということで
あらかじめわかっているなら1.のほうが良いですね．
あとはテキスト全文検索で正規化機能を使うとか（というかこれが本命?）．ただし標準で入ってないことも多いのでケースバイケース．

■新しい業界標準「SQL99」詳細解説
まさにこれの部品構成表的な話が出た．

PostgreSQL8.4は再帰SQLが使える．
MS SQLでも使えるようで．エライな．
OracleとPostgreSQL8.3までには代替手段あり．
MySQLだと使えないようだ．

■PostgreSQLの再帰SQL（1）　――再帰SQLの構文（1/2）：CodeZine
■再帰SQL — Let's Postgres

■SQLで木と階層構造のデータを扱う（１）―― 入れ子集合モデル
木構造のデータとして探るのもあり，か．

今回の問題の局面は既にMySQLで組まれた後．

結局
a・PostgreSQL8.4にする
b・深さを限定して複数回外部結合する
c・仕方なくSQL以外で処理する
という選択に．

深さを限定できないので，aかc．
aだと他に改修箇所が出てくるのでc．

うーん．しかしまぁ，仕方ないよなぁ．

「このカラム内，2～3～1の順番でソートして欲しいんですけど！」

2,3,1でそれぞれSELECTして最後にUNIONするしかないか，と思ったが，
MySQLの場合はORDER BY field({colname},{val}[,val...]) という感じで書けるらしい．

name	type
A	1
B	2
C	3
D	4

というテーブルordertestがあったとして

SELECT * FROM ordertest ORDER BY field(type,2,1,4,3);
B 2
A 1
D 4
C 3

という感じで行ける．
しかし中途半端に指定した場合はそこだけ有効になる．

SELECT * FROM ordertest ORDER BY field(type,4,2);
A 1
C 3
D 4
B 2

という感じ．
fieldに指定されなかった部分は不定のようです? アテにしないほうがよさげ．

ORDER BY filed(～),field(～) と指定することもできます．
これとCASE文の組み合わせで結構いろいろできますね．

postgresqlで全文検索！
ということで実験がてらインストール．

CentOS5.3＋pgdgからもってきたpostgresql8.3.7

■Install - Ludia Wiki

もうこの解説通りにすんなりと…いかねぇorz

■PostgreSQL 8.3.6 に ludia を入れた: 表参道ではたらくCTOのブログ

やはり同じくIndexBuildHeapScanの引数問題でひっかかる．
第6引数にfalseを指定して通してやる．


とりあえずこれで通りました．
たまにconnectionが切れちゃったりしたんだけど，おさまった…？
うーむ，まだ実験レベルだなぁ．