データベース入門 - PerlでSQLite

本章では、データのソートと検索について解説します。
日本語のソートと検索にあたっては、文字コードを UTF-8にしておきます。 これは、SQLiteが正規にサポートする日本語の文字コードが UTF-8であるためです。 Windows標準のShift-JISでもできるようですが、場合によっては文字化けや誤作動等の報告が挙がっているようです。 文字コードへの対処内容は2点あります。 1点目は、「Perlプログラム」自身の文字コードをUTF-8にすること。 2点目は、保存する「データ」の文字コードをUTF-8にすることです。

文字コードの変更や使用にあたっては、テキストエディターは必須です。
Windows附属の「メモ帳」でもUTF-8は使用できますが、保存時にBOMと呼ばれる余計なコードを（強制的に）付加するため、これがプログラムの実行に悪影響を与えてしまいます。UTF-8を利用するPerlプログラムやデータファイルへの編集には、「メモ帳」は使用しないようにしてください。
また、他の専用テキストエディターを利用する際には、ファイルの保存時には、「BOM無し」で保存するように注意してください。

「コマンドプロンプト」は、Shift-JISを前提としていますので、そのままUTF-8のPerlプログラムを実行すると、文字化けしてしまいます。
そこで、「コマンドプロンプト」をUTF-8モードにする必要があります。

上記の設定で、コマンドプロンプトがUTF-8モードになります。
元に戻す場合には、コマンドプロンプトから「chcp 932」と入力すれば、従前のShift-JISモードへ戻ります。

ただし、上記の方法は不完全なようで、筆者のWindows7環境では、Perlをコマンド実行した場合には出力文字の一部が二重に表示されてしまいます（typeコマンドでUTF-8テキストを表示することは正常です）。
恐らく、Windowsのバグではないかと思われますが（推測）、現時点では有効な代替案がないため、上記の方法で対応せざるを得ません。 以上のとおり、SQLiteで日本語をソート・検索する場合の文字コードの注意点でした。

以後は、文字コードはすべてUTF-8ということを前提に解説を進めます。

SQLiteにおけるソート機能について解説をします。
ここでは、データベースからデータを抽出するときに、カラム単位でソートしてから表示させる方法を考えてみましょう。
ソート機能については、次のような構文が用意されています。asc は昇順、desc は降順に並び替えます。 サンプルのテーブルとして、次のような名簿を用意します。 この名簿のテーブル名は「member」とします。

■ 名簿テーブル (member)

ID	name	kana
1004	伊東	いとう
1003	阿部	あべ
1005	大野	おおの
1001	伊藤	いとう
1002	岡田	おかだ

まず、この名簿テーブルをID順（昇順）に並び替えてみましょう。
id をキーにして、昇順にソートするには、次のようなSQL文になります。

select * from member order by id asc;

テーブルの「新規作成」と、ID順の「ソート表示」を一緒に実行してみます。 コードは次のとおりです。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブル定義
$dbh->do("create table member (id,name,kana);");

# データ定義
$dbh->do("insert into member (id,name,kana) values (1004, '伊東', 'いとう');");
$dbh->do("insert into member (id,name,kana) values (1003, '阿部', 'あべ');");
$dbh->do("insert into member (id,name,kana) values (1005, '大野', 'おおの');");
$dbh->do("insert into member (id,name,kana) values (1001, '伊藤', 'いとう');");
$dbh->do("insert into member (id,name,kana) values (1002, '岡田', 'おかだ');");

# テーブルのソート抽出命令
my $sth = $dbh->prepare("select * from member order by id asc;");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1001 伊藤 いとう
>  1002 岡田 おかだ
>  1003 阿部 あべ
>  1004 伊東 いとう
>  1005 大野 おおの

テーブルの作成とその表示処理については、ID順（昇順）に正しくソートされました。

次は、ふりがな順に並び替えてみましょう。 日本語をアイウエオ順に並び替えることになります。
名簿をふりがな順にソート抽出するには、kana をキーにしてソートします。
テーブル (member) に対して、次のコードを実行してみましょう。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブルの kana ソート抽出命令
my $sth = $dbh->prepare("select * from member order by kana asc;");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1003 阿部 あべ
>  1004 伊東 いとう
>  1001 伊藤 いとう
>  1005 大野 おおの
>  1002 岡田 おかだ

アイウエ順に正しくソートすることができました。

ソートについて、もう少し考えてみましょう。
前のコード実行での表示結果において、「いとう」さんが2人います。 同じふりがなの場合には、さらにID順にソートしたい場合はどうしたらいいでしょうか。
その場合には、SQL文でソート命令を2つ定義します。このとき、複数の定義を行うため、カラムへの「ソート」指示をカンマで区切ります。

そこで、SQL文を以下のように定義することで、「最初に、ふりがな kana でソートし、次に、会員番号 id でソート」するという命令になります。

select * from member order by kana asc, id asc;

これを念頭に、次のとおりコードを実行してみます。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブルの二重ソート命令
my $sth = $dbh->prepare("select * from member order by kana asc, id asc;");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1003 阿部 あべ
>  1001 伊藤 いとう
>  1004 伊東 いとう
>  1005 大野 おおの
>  1002 岡田 おかだ

ふりがな順にをソートされ、且つ同名の「いとう」さんはID順にソートされました。

SQLiteにて、データベースの「検索機能」について解説をします。 大量データの中から、条件に一致するデータをいかに高速に検索するかが、データベースの醍醐味ではないでしょうか。

データベース検索の方法には、大きく分けて次の2つのパターンがあります。
(1) 条件式による検索
(2) キーワードによる検索

まず最初に、「条件式」による検索方法についてです。
条件式については、当然ながら（Perlではなく）SQLiteが理解する条件式で指示することになります（つまり、SQL文）。
Perlの条件式にも共通している部分ですが、基本は次のとおりです。

■ 比較演算子

構文	意味
A = B	A と B が等しい場合は「真」
A <> B A != B	A と B が等しくない場合は「真」
A > B	A が B より大きい場合は「真」
A >= B	A が B 以上の場合は「真」
A < B	A が B より小さい場合は「真」
A <= B	A が B 以下の場合は「真」

■ 論理演算子

構文	意味
A and B	A と B の両方が真の場合は「真」
A or B	A と B のどちらかが真の場合は「真」
not A	A が真なら「偽」、A が偽なら「真」

以上の演算子を踏まえ、SQL文において、条件式による検索については、次のような構文が用意されています。 たとえば、4-2. の名簿テーブル (member) にて、ID番号が1001のデータを抽出するという条件式を考えてみます。

select * from member where id = 1001;

以下のコードを実行してみます。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブルの条件検索命令
my $sth = $dbh->prepare("select * from member where id = 1001;");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1001 伊藤 いとう

次に、複数の条件式を組み合わせてみましょう。 ID番号が1001から1003まで抽出するという条件式を考えてみます。
SQL文は次のとおりです。

select * from member where id >= 1001 and id <= 1003;

以下のコードを実行してみます。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブルの条件検索命令
my $sth = $dbh->prepare("select * from member where id >= 1001 and id <= 1003;");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1003 阿部 あべ
>  1001 伊藤 いとう
>  1002 岡田 おかだ

IDが1001から1003までの名簿を正しく抽出できました。

次は、「キーワードによる検索」方法です。
キーワードによる検索は、パターンマッチング（正規表現）を使用します。 基本は次のとおりです。

■ パターンマッチング（SQLiteの正規表現）

演算子	意味	備考
%	任意の0文字以上の文字列	Perlでは .* のこと（Perl正規表現）
_	任意の1文字	Perlでは . のこと（Perl正規表現）

これを、いくつかの例で考えてみましょう。

a%	[前方一致] aで始まるもの	ask, and, awk
%tea	[後方一致] 最後がteaで終わるもの	milktea, lemontea
%p%	[部分一致] pが含まれるもの	apple, pen, top
c__	c + 2文字	cup, cop
_a_	1文字 + a + 1文字	map, lap
___	任意の3文字	abc, xyz

パターンマッチングを使う場合の構文は、次のとおりです。 たとえば、名簿テーブル (member) の中から、ふりがな (kana) の中に「お」が含まれるデータを抽出するケースを考えてみます。 SQL文は次のようになります。

select * from member where kana like '%お%';

以下のコードを実行してみます。

use strict;
use DBI;

# 接続
my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");

# テーブルのパターン抽出
my $sth = $dbh->prepare("select * from member where kana like '%お%';");
$sth->execute;

# 各データを展開表示
while (my @row = $sth->fetchrow_array) {
	print "@row\n";
}
$sth->finish;
undef $sth;

# 切断
$dbh->disconnect;

>  1005 大野 おおの
>  1002 岡田 おかだ

ふりがなの中に「お」が含まれる名簿を正しく抽出することができました。