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

前章までは、Perl + SQLiteの文法や操作方法について解説しました。
本章以降は、より実践的な学習の試みとして、SQLiteを使った「名簿検索システム」を作ってみることにします。
実用的なプログラムを意識すると同時に、ウェブサーバと連携するCGIプログラムとしてのPerlの面白さを体験しましょう。

名簿検索システムとして、以下の2パターンを CGI/Perl + SQLiteにて作成することにします。
パターン1は、ID順のソートと性別の抽出です。パターン2は、それに加えてキーワード検索機能をプラスします。

パターン1 （ID順のソート・性別抽出）

ソート	ID順（昇順） ID順（降順）
男女別	両方男性のみ女性のみ

パターン2 （ID順のソート・性別抽出・キーワード検索）

「名簿検索システム」の設置・動作までをテーマに進めるにあたって、全体に解説の流れについてご説明をします。

ソート	ID順（昇順） ID順（降順）
男女別	両方男性のみ女性のみ
キーワード

(1) テスト環境の構築

これまでのコマンド操作によるプログラムから、ウェブサーバと連携とするCGIプログラムについて動作させることになりますので、そのためのテスト環境を構築します。

(2) 名簿データの準備（CSVファイルで元データを作成）

名簿データとして、最初にExcelでCSV形式のデータファイルを作成します。

(3) 名簿データの変換（SQLiteデータベースへ変換）

変換プログラムを作成して、(2) で作成したCSVデータを、SQLiteデータベースに変換します。このとき、文字コードもShift-JISからUTF-8へ同時に変換します。

(4) 検索フォームを作成

名簿検索の操作は、ウェブブラウザによるため、HTMLで検索フォームを作成します。

(5) 検索プログラムを作成

検索プログラムを作成し、設置作業を行います。

(6) 検索プログラムを改造

検索プログラムに手を加え、機能強化を図ります。

前章までは、SQLiteをコマンドライン（コマンドプロンプトからの実行）で動作させていました。
本章では、ブラウザでの使用を前提とした名簿検索システムを作成します。そのため、ウェブサーバと連携することになり、CGIとして動作させることになります。

CGIの流れ	ブラウザ		(1) CGIへデータを送信		WWWサーバ		(2) CGIを実行
			(3) 結果をHTMLで返信

そこで、ご自分のパソコンの中に、WWWサーバ + CGI/Perlが動作する「CGIテスト環境」を作る必要があります。
テスト環境は、XAMPP とすることにします。
XAMPPは、CGI/Perl環境（SQLite含む）はもちろんのこと、WWWサーバを動かすApacheやメールサーバ等の「サーバ」ソフトウェア群をパックにしたものです。

お使いのパソコン (Windows) へ「XAMPP for windows」をインストールしましょう。XAMPPをインストールすれば、テスト環境に必要な、Apache + Perl + SQLite の全てが使用できます。
詳細なインストール方法については、以下のコンテンツを参考にしてください。

自宅マシンWWWサーバ化計画

ただし、既にお使いのパソコン (Windows) にインストールされている方は、もちろんその必要はありません。

まず、名簿データベースの元となるデータを用意します。
今回は以下のような名簿テーブル (member) で考えてみます。

id	name	kana	sex	addr
1001	山田太郎	やまだたろう	男	東京都
1002	飯田順子	いいだじゅんこ	女	千葉県
1003	桜井恵子	さくらいけいこ	女	神奈川県
1004	花山大吉	はなやまだいきち	男	埼玉県
1005	焼津半次	やいづはんじ	男	静岡県

元データをExcelで作成し、ファイル名を member.csv として保存します。
作成保存後、member.csvをテキストエディターで開くと、次のような中身になっているはずです。文字コードは、Windowsで作成しますので、Shift-JISです。

1001,山田太郎,やまだたろう,男,東京都
1002,飯田順子,いいだじゅんこ,女,千葉県
1003,桜井恵子,さくらいけいこ,女,神奈川県
1004,花山大吉,はなやまだいきち,男,埼玉県
1005,焼津半次,やいづはんじ,男,静岡県

上記のとおり、CSVファイルの準備ができたら、これをSQLiteのデータベースファイルへ変換します。
このとき気を付けることは、文字コードです。 CSVデータはShift-JISですので、これをUTF-8へ変換して、データベースファイルを作成することになります。

CSVデータから、SQLiteデータへの変換作業用に専用の変換プログラムを作りましょう。
この変換プログラムのファイル名を conv.plとします。
なお、文字コードの変換は、Encodeモジュールを使ってコード変換することにします。

変換プログラム (conv.pl) は次のとおりです。文字コードは、UTF-8で保存します。

# conv.pl
# created by kent

use strict;
use Encode;
use DBI;

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

# テーブルの作成
my $table = <<'EOM';
create table member (
	id integer,
	name,
	kana,
	sex,
	addr
)
EOM

# テーブル定義
$dbh->do($table);

# CSV読み込み
open(IN,"member.csv") or die;
while( my $csv = <IN> ) {
	chomp($csv);

	# コード変換 sjis → utf8
	Encode::from_to($csv, 'shiftjis', 'utf8');

	# 分割
	my ($id,$name,$kana,$sex,$addr) = split(/,/,$csv);

	# DBへ流し込み
	$dbh->do("insert into member (id,name,kana,sex,addr)
				values ($id,'$name','$kana','$sex','$addr');");
}
close(IN);

# DB切断
$dbh->disconnect;

# 完了
print "OK\n";

member.csv と conv.plを同一フォルダに置いて、コマンドプロンプトから、conv.plを実行します。
なお、コマンドプロンプトは、（日本語での表示出力を行わないため）特にUTF-8モードに切り替える必要はありません。

「コマンドプロンプト」よりコマンド実行
perl conv.pl

上記のコマンド実行後、画面に「OK」が表示されたら、変換作業は完了です。
フォルダの中に member.dbができているはずです。