URL を mysql に入れたり出したりする修行

大量の URL を調査するのにテキスト処理ツールではちょっと追いつかなくなってきたので RDBMS を導入してみる。CentOS 5.4 なマシンに yum でお手軽にインストールできた mysql 5.0。いいじゃないか MyISAM だって。

データベースの作成

デフォルトの character set を UTF-8 でデータベースを作る。

create database unau character set utf8;

テーブルの作成

調査の都合上、URL はそのまま格納するのではなく、スキーマ、ホスト名、パス、クエリーストリングに分割して格納する。とりあえずホスト名を格納するテーブル host と URL を格納するテーブル url を作る。
まずテーブル host。

drop table if exists host;
create table host (
  id int unsigned auto_increment primary key,
  name varchar(255) character set ascii unique,
  idn_flag tinyint not null,     -- 0 : not IDN, 1 : IDN                        
  idn_name varchar(255) binary unique,
  ip_id int unsigned      -- ip4address.id                                      
);

IDN なホスト名の場合、idn_flag が 1 になる。でもって、エンコード前のホスト名 (たとえば「小島丈幸.jp」) が idn_name に格納され、エンコード後のホスト名 (たとえば「xn--dhqu55a2taf8b.jp」) が name に格納される。IDN でない普通のホスト名の場合は idn_flag は 0、ホスト名がそのまま name に格納される (idn_name は null)。
ip_id には IP アドレスを格納したテーブル ip4address の主キーが入る。テーブル ip4address はまだ作ってないけど、ホスト名を DNS で引いたときの A レコードの値と、その値を逆引きしたときの値を入れておく予定。

次にテーブル url。

drop table if exists url;
create table url (
  id int unsigned auto_increment primary key,
  secure_flag tinyint not null,      -- 0 : http, 1 : https                     
  host_id int unsigned not null,      -- host.id                                
  path    varchar(255) character set ascii,
  qstring varchar(255) character set ascii,
  unique (secure_flag, host_id, path, qstring)
);

path や qstring は明示的に character set を ascii に指定しておく。そうでないと UNIQUE キーが作成できない。なぜかというと、UTF-8 のままだと varchar(255) のカラムが二つ分、510 文字は 255 * 3 * 2 = 1,530 bytes で、最大キー長 1,000 bytes を越えてしまうから。create table 時に次のようなエラーが発生する。

Specified key was too long; max key length is 1000 bytes

URL の再構成

分割されてしまった URL は次の SQL で再構成する。

select
  concat('http', case u.secure_flag when 1 then 's' else '' end, '://',
          h.name, '/', u.path,
          case u.qstring when '' then '' else concat('?', u.qstring) end
         ) as url
  from url u left join host h on u.host_id = h.id;

Punycode の encode/decode を mysql の stored procedue で書いている人はたぶんいないだろうなあ。

URL の格納スクリプト

DBI を使ったスクリプト。もっとスマートに書ける気がするけど、とりあえず。MyISAM だからトランザクション関係ないのにトランザクション処理書いてるのは、気が変わって別のデータベースエンジンとか使ったときもそのままで行けるように。と書いておこう。
ちょっと、いやだいぶ気になるのは、UNIQUE 制約の重複エラーの内部コードを 1062 と決め打ちしているところ。

use strict;
use warnings;

use DBI;
my $dsn = 'DBI:mysql:unau:localhost';
my $user = 'unau';
my $password = 'secret';
my $dbh = DBI->connect($dsn, $user, $password,
                   { RaiseError => 1, PrintError => 0, AutoCommit => 0 });

sub is_ascii {
    my $str = shift;
    foreach my $ch (split(q{}, $str)) {
        return 0 if ord($ch) > 127;
    }
    return 1;
}

my $IDN = qr{ xn--[a-zA-Z0-9\-]+ }xms;
my $MYSQL_ENTRY_DUPLICATION = 1062;
my %is_secure = ( http => 0, https => 1 );

sub _split_url {
    my $url = shift;
    my $i = index($url, '://');
    return if $i < 0;
    my $is_secure = $is_secure{substr($url, 0, $i)};
    return if ! defined $is_secure;
    my $rest = substr($url, $i + 3);
    my ($host, $path, $qs) = (q{}, q{}, q{});
    
    $i = index($rest, '/');
    if ($i > -1) {
        $host = substr($rest, 0, $i);
        $rest   = substr($rest, $i + 1);
        $i = index($rest, '?');
        if ($i > 0) {
            $path = substr($rest, 0, $i);
            $qs   = substr($rest, $i + 1);
        }
        else {
            $path = $rest;
        }
    }
    else {
        $host = $rest;
    }
    return ($is_secure, $host, $path, $qs);
}

sub _insert_and_select_host {
    my $host = shift;
    my ($idn_flag, $raw_host, $idn_host) = 
        (! is_ascii($host)) ? (1, undef, $host) :
            ($host =~ $IDN ) ? (1, $host, undef) : (0, $host, undef);
    my $sql = 'insert into host values(null, ?, ?, ?, null);';
    my $sth = $dbh->prepare($sql);
    $sth->bind_param(1, $raw_host);
    $sth->bind_param(2, $idn_flag);
    $sth->bind_param(3, $idn_host);
    eval {
        $sth->execute;
    };
    my $host_id;
    if ($@) {
        die $@ if $DBI::err != $MYSQL_ENTRY_DUPLICATION;
        $sql = 'select id from host where name = ? or idn_name = ?';
        $sth = $dbh->prepare($sql);
        $sth->bind_param(1, $raw_host);
        $sth->bind_param(2, $idn_host);
        my $rv = $sth->execute;
        die "failed to select" if ! $rv;
        my $rc = $sth->bind_columns(\$host_id);
        die "failed to get host_id" if ! $rc;
        $rc = $sth->fetch;
        die "failed to get host_id" if ! $rc;
    }
    else {
        $sql = 'select last_insert_id()';
        $sth = $dbh->prepare($sql);
        my $rv = $sth->execute;
        die "failed to select" if ! $rv;
        my $rc = $sth->bind_columns(\$host_id);
        die "failed to get host_id" if ! $rc;
        $rc = $sth->fetch;
        die "failed to get host_id" if ! $rc;
    }
    return $host_id;
}

sub _insert_url {
    my ($host_id, $is_secure, $path, $qs) = @_;
    my $sql = 'insert into url values(null, ?, ?, ?, ?);';
    my $sth = $dbh->prepare($sql);
    $sth->bind_param(1, $is_secure);
    $sth->bind_param(2, $host_id);
    $sth->bind_param(3, $path);
    $sth->bind_param(4, $qs);
    eval {
        $sth->execute;
    };
    my $url_id;
    if ($@) {
        die $@ if $DBI::err != $MYSQL_ENTRY_DUPLICATION;
        warn $@;
        $sql = 'select id from url where secure_flag = ? and host_id = ? and path = ? and qstring = ?;';
        $sth = $dbh->prepare($sql);
        $sth->bind_param(1, $is_secure);
        $sth->bind_param(2, $host_id);
        $sth->bind_param(3, $path);
        $sth->bind_param(4, $qs);
        my $rv = $sth->execute;
        die "failed to select" if ! $rv;
        my $rc = $sth->bind_columns(\$url_id);
        die "failed to get url_id" if ! $rc;
        $rc = $sth->fetch;
        die "failed to get url_id" if ! $rc;
    }
    else {
        $sql = 'select last_insert_id()';
        $sth = $dbh->prepare($sql);
        my $rv = $sth->execute;
        die "failed to select" if ! $rv;
        my $rc = $sth->bind_columns(\$url_id);
        die "failed to get url_id" if ! $rc;
        $rc = $sth->fetch;
        die "failed to get url_id" if ! $rc;
    }
    return $url_id;
}

sub store {
    my $url = shift;
    my ($is_secure, $host, $path, $qs) = _split_url($url);
    my $host_id = _insert_and_select_host($host);
    my $url_id = _insert_url($host_id, $is_secure, $path, $qs);
}

eval {
    my $n = 0;
    while(my $line = <STDIN>) {
        chomp $line;
        store($line);
        print {*STDERR} '.' if $n % 10_000 == 0;
        $n++;
    }
    $dbh->commit;
    $dbh->disconnect;
};
if ($@) {
    $dbh->rollback;
    $dbh->disconnect;
    die $@;
}

__END__

追記 2009-12-02

300 万件のデータを食わしてみたところ、path 部分の長さが 255 文字を越える URL に遭遇してあえなく die。そこは warn にしておいて処理を続行しないとだなあ。