半角カナでの登録を拒否するために、自動で全角カナに変換したいときってあるじゃないですか。アプリケーション側で、AOPやFilterといった仕組みで変換してあげる方がスマートだと思ってるんだけど、DB側でやらないといけないケースもあるでしょう。

ということで、こんな関数を用意してみました。

CREATE OR REPLACE FUNCTION h2z_kana(text) 
  RETURNS text AS
$BODY$
    DECLARE
        zenkaku alias FOR $1;
        result text;
        i int;


        zt varchar[] = ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
        ht varchar[] = ARRAY['ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ', 'ｳﾞ'];
    BEGIN


        result = zenkaku;


        -- 2バイトで変換
        FOR i IN 1..26 LOOP
            result = replace(result, ht[i], zt[i]);
        END LOOP;


        -- 1バイトで変換
        result = translate(result,
                   ' ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮﾜｲｴｶｹｰ､｡･｣｢ﾞ,<.>/?_}]*:+;{[~@|\\`^=-)(&%$#"!',
                   '　アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛，＜．＞／？＿｝］＊：＋；｛［￣＠｜￥｀＾＝－）（＆％＄＃”！'
              );


        RETURN result;
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

ガやザのような濁音のものは、半角カナになるとカと濁点の合計2バイトになる。まずはじめに変換テーブルを用いてそれらを置換してから、残る半角カナを一括して置換してます。記号の置換処理のところは、不要なら除けばOK。でも、大体のケースであっても困らないと思いますけど。

あとは、この関数を使って UPDATE するトリガーを、INSERT と UPDATE 時に発動するように定義してあげれば、冒頭の件は対応できると思います。

DBから値を引っ張ってきて、そのままメール送信するようなケースで、半角カナが含まれてたらイヤンなときにも活用できるかな。そのケースでは、機種依存文字全般の対応もあるので、送信プログラム側でもろもろを処理した方が、スッキリするかしら？

ま、ひとつの参考までに。

1対N（OneToMany）の関係にある2つのエンティティについて、N個の文字列を集約する必要があって、方法を考えてみた。

次のような部署マスタと社員マスタを例にとって、説明します。

これに対して、次のような表を出すという要件。

「2つのエンティティを JOIN すれば良いじゃん」という声もあるけど、単純に結合しちゃうと、結果セットから取り出すとき、もしくは表示時点でプログラムでごにょごにょやらないと、うまくいかない。所属社員の一覧を、改行区切りの文字列として、ひとつの変数に格納できれば、らくちん。要するに、DBから値を取得する時点で、表示するときの形まで持っていきたいという話。

ここの方法については賛否両論あるかもしれないけど、SQLはプログラムが最もシンプルで済むような結果が得られるように書くべきだと思ってます。

話が逸れたけど、じゃあ、それをどうやってやるか、という話ですよ。

続きを読む "PostgreSQLの配列を使って文字列を集約をする方法"

PostgreSQLで、同一イベントに複数のトリガーがある場合に、どういう順番で実行されるのか気になったので調べてみた。

トリガ動作の概要 同一リレーション、同一イベントに対して1つ以上のトリガが定義された場合、トリガはその名前のアルファベット順に発生します。 BEFOREトリガの場合では、各トリガで返される、変更された可能性がある行が次のトリガの入力となります。もし、あるBEFOREトリガがNULLを返したら、操作はその行で中断し、残りのトリガは発生しません。

アルファベット順、これは予想外だった。以前は順番を保証することさえなかったみたいだから、それに比べたら方法があるだけマシなのかもしれないけど、トリガ名を考えないといけないということか。なんか明示的な制御の方法はないのかな。

メモ。

C:\>bcp DB.dbo.TABLE out c:\test.csv -c -t "," -S localhost\sqlexpress -U sa

こんな感じ？

でも、これだと単純にデータ間にカンマを入れるだけなので、厳密なCSVではないんだよなあ。データ中にカンマが入るような場合だと、カラムの区切り文字と区別つかなくなっちゃうから、本来はカラムごとに引用符付きで出力したいんだけど、bcp ユーティリティでは方法が分からず。フォーマットとか使えば良いのかもしれないけど、テーブルごとに用意するのでは、面倒っちいです。

やっぱり、自分でDBを読み込んでCSVで書き出すようなプログラムを書かないとダメですかね。うーん、Windows は苦手なので逡巡します。

Excel で作成したデータを DB に取り込むシーンって、意外とあるんだけど、これが厄介。

そういうとき自分は、Excel データを csv 形式で出力してから、それを postgresql に COPY で取り込むような流れでやってるんですが、

COPY test FROM '/home/postgres/test.csv' WITH delimiter ',' null '';

こういう風にすると、データ中に「"aaa,bbb"」みたいな項目があると、そのカンマにも反応しちゃってカラムが区切られてしまうので、

ERROR:  extra data after last expected column

なんて怒られてしまう。

どうしたもんかなーと思って調べていたら、いつの間にやら COPY は CSV 対応している模様。

COPY test FROM '/home/postgres/test.csv' WITH CSV;

これでOKでした。

CSV への対応は PostgreSQL 8系以降でのサポートっぽい。ちょっとしたことですが、これは便利。

集合を返す関数 generate_series

今さら知ったんですが、こんな便利な関数があったんですね。PostgreSQL8.0から追加された関数らしく、これを使えば連番やカレンダーテーブルを簡単に作成することができる。うわー、もっと早く知っておきたかった！朝から大興奮！

例えば、1～10までの項番を作成する場合。

SELECT * FROM generate_series(1,10);


 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

今週の日曜～土曜の一覧を生成する場合。

SELECT
    current_date + s.a AS date
FROM
    generate_series(
        (SELECT 0-extract(dow FROM current_date))::int,
        (SELECT 6-extract(dow FROM current_date))::int
    ) AS s(a);


    date
------------
 2008-04-13
 2008-04-14
 2008-04-15
 2008-04-16
 2008-04-17
 2008-04-18
 2008-04-19
(7 rows)

これまでプログラムでごりごり頑張っていた部分のいくつかは、generate_series を使うことで、SQLだけで書けるようになる。プログラムで SQL の足りないところを補おうとすると、非常にややこしや～なことになりがちなので、AP と DB の関係を疎にするアイデアとして、generate_series は良いと思います。PostgreSQL の方言というのが、たまにキズですが…。

plpgsql で SELECT 文を発行して、その結果を変数に代入したいときは、SELECT INTO 構文を使用する。

CREATRE OR REPLACE FUNCTION test() RETURNS int AS
'
DECLARE
    ct int;
BEGIN
    SELECT INTO ct count(*) FROM table;
    return ct;
END
'
language 'plpgsql';

このように書くと、count(*) の値が、宣言済み変数 ct に代入できる。

複数の結果をそれぞれ変数に代入したいときは、

DECLARE
    one int;
    two int;
BEGIN
    SELECT INTO one,two count(one),count(two) FROM table;
    RETURN one * two;
END

といった感じで並べればOK。

まあ、取り扱うカラム数が多い場合には、不向き。そういう場合は、RECORD で受け取ったりすると良いのかな。

さーて、もりもり CREATE FUNCTION でもするかと思ったら、こんなエラー。

ERROR:  language "plpgsql" does not exist

PostgreSQL を標準インストールすると、plpgsql は入らないんだろうか。plpgsql を後から追加するときは、コマンドラインから次のようにするらしい。

$ /usr/local/pgsql-8.3.0/bin/createlang -d database_name plpgsql

これで無事に CREATE FUNCTION できました。わーい。忘れそうなので、メモ。

Oracleでシーケンスを取得する際には

SELECT sequence.nextval FROM dual;

というように、DUAL表を使うのだけど、同じことを PostgreSQL で行う場合には、

SELECT nextval('sequence');

というように、FROM句なしで事足りてしまう。

両者の差は、方言だということで納得するしかないんだけど、やっぱりFROM句が無いというのは、違和感がある。もちろんこれはシーケンスの取得に限った話じゃなくて、全般的に。論理的な意味合いとして、「FROM句あってのSELECTじゃないの？」という気分。まあ、DUAL表にも意味はないわけで、「ムダなもんは排除しようよ」っていう流れは分かる。

まあ、あまりDBのコアな仕様を把握していないので、あまり尊大なことは言えない。FROM句の有無というインタフェースの問題以上に、内部では大きな差があるような気もするなあ。

テーブルのカラム数が増えていくと、DB性能が悪くなる気がする。

そんなメンバーの声を聴いて、そういえば自分も実体験として、100カラムほどあるテーブルの性能問題にぶつかったことがあったことを思い出した。やたらとSELECTが重い。とは言え、その原因がカラム数にあるというのは眉唾。

実際に手元にあった PostgreSQL8.1 で試してみることにした。

続きを読む "カラム数が増えるとDB性能が劣化する？"

今度のプロジェクトのDB構成を考えていて、当初からMySQLという選択肢が頭を離れない。

これまでのプロジェクトを見ると、PostgreSQLで9割を占める。Oracleもあったけど、ライセンス高いから省略。一方、MySQLは触ったことがあるという程度。チューニングやSQL方言など、慣れないことが多いだけに、いつもその採用には二の足を踏んできた。

ただ、mixiやはてなといった大規模サイトでのMySQLの実績を横目に見て、それなりの規模のアプリケーションならMySQLを真剣に考える必要がありそう。こんだけ流行しているわけだから、ちゃんと触ってノウハウ蓄積したいという思いもあるし。

PostgreSQLに比べて、MySQLが良いなと思っているところ。

• レプリケーション機能が標準で付いている。
• Federated ストレージエンジンが使える。

日本語対応とか、スレッドとプロセスの違いとか、細かいところで違いはあるんだけど、そこはさておき。要は、スケーラビリティにおいてMySQLが優れているように思えて、気になっている。

以前は両者の性能差がもっとあったので、そんなに悩むことは無かったけど、もはやエンタープライズだからPostgreSQL、WebサービスだからMySQLっていう安易な選択もしちゃいけないんだと思う。なんなら両方が合体しちゃえば良いのに、とかトンチンカンなことを言ってしまいそうだ。

こんなわけでMySQLに後ろ髪ひかれる思いなんだけど、きっと今回はPostgreSQLになるんだろう。いまの自分の状況では、拡張性の要件を除いて、まだPostgreSQLに優位性がある。地道に検証を続けて、近いうちにMySQLで組めるようにもっていけるといいな。

postgresqlでスキーマを変更しようと、SQLを流したらエラー続出。

ERROR:  invalid byte sequence for encoding "UTF8": 0xba
ERROR:  invalid byte sequence for encoding "UTF8": 0xb4
ERROR:  invalid byte sequence for encoding "UTF8": 0xbc

調べてみると、このSQLインジェクションの脆弱性修正の影響のようだ。無効なマルチバイトは蹴られる、と。

ということで、PGCLIENTENCODINGを変更。

$ export PGCLIENTENCODING=SQL_ASCII

これでエラー無く流れた。おー。

普段はPGAdminなどのツールを使っているので気付かないけど、大量のDBのスキーマ変更しようと夜間バッチ流したりすると、こういうステキな問題に直面する。ぐあー、リハーサルで良かった。

SQLでカラムを超えて文字列を結合する場合は、|| 演算子を使えば良いけれども、レコードを超えて文字列を結合する場合はどうしたもんか。

イメージとしては、数値をSUMするような感じで、文字列を縦結合していく感じ。

しかし、sum(integer)はあっても、sum(text)は見当たらない。ということで、自分で作ってみた。初めてのオリジナル集約関数。わーい。(PostgreSQL限定)

続きを読む "SQLで文字列をsumするには"

PostgreSQL で文字列を連結させるには、|| 演算子を使うのだけど、これが一癖あって、連結するカラムの値が null だと、連結結果も null になってしまう。今日はじめて知った。

 sei | mei
-----+-----
 姓  | 名
 姓  | null


# select sei || mei as name from table;
 name 
------
 姓名 
 null 

null にしないためには、COALESCE で空文字を連結するように書いてあげれば大丈夫。

# select sei || COALESCE(mei, '') as name from table;
 name 
------
 姓名 
 姓 

こういう動きを見ると、|| 演算子が単純に文字を連結するだけの演算子のように思えないんだけど、SQL92の仕様なのか、それともPostgreSQLの方言なのかなあ。

だいぶ MySQL に苦戦中。

コマンドラインでの操作が、全然覚えられない。というか、気付くと「psql」と打ってしまっている自分にガッカリ。染み付きすぎ。欧米か。(言いたかっただけ)

MySQLでデータベースを切替える方法メモ。

(新しいDBを作成)
# mysqladmin -u root create new_db
(古いDBのダンプ)
# mysqldump -u root old_db >old_db.dump
(新しいDBにリストア)
# mysql -u root new_db < old_db.dump
(古いDBを削除)
# mysqladmin -u root drop old_db

ちなみに、バックアップを取りたい場合は、直接gzipにかませて圧縮すれば容量節約で良い感じ。mysqldumpのオプションでそういうのがあるわけじゃないのかあ…。

# mysqldump -u root db | gzip > db.dump

MySQLのお作法を習得するには、まだ時間がかかりそうだなあ。うーむ。

急遽 MySQL を使うことになったので、ざっくりインストール。

普段は PostgreSQL 使いで、MySQL は3回くらいしか触ったことがない。とにかく慣れないにもんで、わけも分からないまま、Google 先生に教えを請いながら何とか動作するに至る。人生いろいろ、DBもいろいろである。ふぅ。

今回は MySQL4.1系を採用。5.0系の方が良いんじゃないのかなーと思ったけど、前任SEの推奨環境が MySQL4.1系になっていたので、そこは長いものに巻かれていきます。基本、流れ重視です。

ということで、手順をメモメモ。

続きを読む "MySQLのインストール"

プロジェクトも終盤だというのに、ここに来てテーブル設計に凡ミスを発見。何とかその穴を埋めようと、ビューを定義したのだけれども、やけにパフォーマンスが悪い。なんでじゃ。

ビューの功罪
注意を要するケースがあります。それは、ビュー定義で集約操作を行なっているときです。具体的には、次のような演算が含まれる場合です。

AVG、COUNT、MAX などの集約関数
集約演算子（ UNION、INTERSECT、MINUS 等 )

おー、バッチリ嬉々として UNION ALL とかやっちゃってましたよ。縦結合の代わりに横結合するようにしたら、劇的に軽くなった。ビューが単なるSQLクエリの束であることを考えれば、当然なんだけど、盲点でした。

ちなみに、ビューはSQLクエリでしかないということで、元となる実テーブルのインデックスが使えます。さっき試してみて驚いたけど、よく考えたら当たり前じゃないか。ううう。

SQLでテーブルのデータに順位付けするのって、どうやるんだろう。ということで、ちょっとばかし考えてみた。

例として、次のような商品テーブルがあって、その価格が高い方から順に、1位、2位…と順位付けするケースを考えてみる。サンプルに深い意味はないです。

順位付けには、次のようなSQLを考えてみた。

SELECT 
    COALESCE(rank, 1) AS rank,
    name,
    price
FROM
    product
LEFT OUTER JOIN (
    SELECT
       (count(*) + 1) AS rank,
       p1.id
    FROM product AS p1
    CROSS JOIN product AS p2
    WHERE p2.price>p1.price
    GROUP BY p1.id
) AS rank_table
USING (id)
ORDER BY rank

もし価格が同じ商品があった場合は、順位タイということで、同じ順番が振られる。なので、ユニークな順位が求められるケースでは、ちょいと工夫が必要です。

できるにはできたんだけど、使うことは無さそうだなあ。相当数あるときは、CROSS JOIN でパフォーマンスが落ちそうだし、JavaなりPHPなり別言語でやった方がラクチンだし。もっと効率の良い方法があれば、嬉しいんだけど。

いつまでたっても覚えられない「COALESCE」の読み方。

社内でも、明確に発音を知っている人がいないようで、話題が出るたびに「コー何とかを使ってさー」となる。結構な頻度で使うし、口にも出すくせに、「コー何とか」である。読めずともDBには通じるし、いっそ「コー何とか」を社内言語にしてしまうのも手かと思ったけど、横着にもほどがあるので調べた。

coalesce

【自動】 合体｛がったい｝する、連合｛れんごう｝する、融合｛ゆうごう｝する
【発音！】ko`uэle's、【＠】コウアレス

via. スペースアルク

ということで、正解は「コウアレス」とのこと。どうか忘れませんように。

ORDER BY句と聞くと、整然と並べられたデータを取得できるイメージばかりが先行してしまう。でも、「ソート」という考え方を応用すると、実はぐちゃぐちゃな順列で取得することも可能。

// postgresql の場合
SELECT * FROM table ORDER BY random();


// MySQL の場合
SELECT * FROM table ORDER BY rand()

乱数でソートできるというのが、イカス！

でも、データをそんなブサイクに並べてどうするのか。たとえば、テーブルから任意のレコードを抽出したい場合に、この方法が使える。統計で無作為抽出するときなんかは、有用なんじゃないでしょうか。