SQLの練習用Perlスクリプト

SQL Perl

今ではSQL Server Express EditionなんていうのもあってSQLを練習するための環境は結構簡単に用意できるのですが、ちょっとSQLの練習をしたいというときにそんな重たいソフトを起動するのも面倒ですし、テーブルを作ったりテストデータを登録したりというのもなかなか面倒なので、SQLの練習のためのPerlスクリプトを作りました。

DBD::SQLiteを使っているのでお手軽です。最新のActive Perlを入れていれば特に追加のモジュールなどはなくても大丈夫なはず。


こんなスクリプトを書きます。

use strict;
use warnings;

use FindBin qw($Bin);
use lib "$Bin";

use Common;

# JOINのサンプル
my $test_tables = [
q{
people
no number,name varchar,sex number
1,松井 咲,1
2,境 なぎさ,1
3,鈴木 昴,0
4,岩井 麻由子,1
5,岩佐 春樹,0
},

q{
sex
no number,caption varchar
0,男
1,女
},
];
my $sql_execute = q{
select
  p.no as '番号',
  p.name as '氏名',
  s.caption as '性別'
from
  people as p
    join
  sex as s
    on p.sex = s.no
where p.sex = 0
};

Common::execute(
    $test_tables,
    $sql_execute,
);

$test_tablesでテーブルの定義とテストデータの用意を行います。一つのテーブルにつき一つの文字列としています。

文字列は、先頭が空行(見やすくするため)で、次がテーブル名、その次がカラムの定義、それ以降がテストデータです。

$sql_executeには実行したいSQLを書きます。

このスクリプトを実行するとこんな結果が返ってきます。

no name sex
1 松井 咲 1
2 境 なぎさ 1
3 鈴木 昴 0
4 岩井 麻由子 1
5 岩佐 春樹 0

no caption
0 男
1 女 

select
  p.no as '番号',
  p.name as '氏名',
  s.caption as '性別'
from
  people as p
    join
  sex as s
    on p.sex = s.no
where p.sex = 0

番号 氏名 性別
3 鈴木 昴 男
5 岩佐 春樹 男

出力内容は今後調整していきます。

メインとなっているCommon.pm(安易に名前を付けすぎた)はこうなっています。

package Common;

use strict;
use warnings;
use DBI;

my $Class_Data = 'data';
my $Class_Result = 'result';

my $Db_Name = 'test.db';
my $Db_String = 'dbi:SQLite:dbname=' . $Db_Name;

init();
sub init {
    unlink $Db_Name;
}

sub execute {
    my $test_tables = shift;
    my $sql_execute = shift;

    my $dbh = get_connect();
    foreach my $test_data (@$test_tables) {
        my @data = split /\n/, $test_data;
        shift @data;
        my ($table, $headers, $items) = get_data(@data);
        my $columns = get_columns($headers);
    
        my $sql_create = get_sql_for_create($table, $headers);
        my $sql_insert = get_sql_for_insert($table, $columns);
    
        table_create($dbh, $sql_create);

        show_data($columns, $items, $Class_Data);
        insert_data($dbh, $items, $sql_insert);
    }
    show_sql($sql_execute);
    my ($result_columns, $result) = select_data($dbh, $sql_execute);
    show_data($result_columns, $result, $Class_Result);
    
    $dbh->disconnect;
}

sub get_columns {
    my $headers = shift;

    return [map {(split /\s/, $_)[0]} @$headers];
}

sub get_sql_for_create {
    my $table = shift;
    my $columns = shift;

    return sprintf "create table %s (%s)", $table, join(', ', @$columns);
}

sub get_sql_for_insert {
    my $table = shift;
    my $columns = shift;

    my $columns_num = @$columns;
    return sprintf "insert into %s (%s) values (%s)",
        $table,
        join(', ', @$columns),
        join(', ', ('?') x $columns_num);
}

sub get_connect {
    my $dbh = DBI->connect($Db_String, undef, undef, {
        AutoCommit => 0,
        RaiseError => 1,
    });
    return $dbh;
}

sub table_create {
    my $dbh = shift;
    my $sql = shift;

    $dbh->do($sql);
    #$dbh->commit;
}

sub get_data {
    my @data = @_;

    my $table = shift @data;
    my $top = shift @data;
    my @headers = split /,/, $top;

    my @items = ();
    foreach my $line (@data) {
        push @items , [split /,/, $line];
    }
    return ($table, \@headers, \@items);
}

sub show_data {
    my $headers = shift;
    my $items = shift;
    my $class = shift;

    print qq{<table class="$class">\n};
    print "<tr>\n";
    foreach my $item (@$headers) {
        printf "  <th>%s</th>\n", $item;
    }
    print "</tr>\n";
    foreach my $row (@$items) {
        print "<tr>\n";
        foreach my $item (@$row) {
            printf "  <td>%s</td>\n", $item;
        }
        print "</tr>\n";
    }
    print "</table>\n";
    print "\n";
}

sub show_sql {
    my $sql = shift;
    print $sql, "\n";
}

sub insert_data {
    my $dbh = shift;
    my $items = shift;
    my $sql = shift;

    my $sth = $dbh->prepare($sql);
    foreach my $row (@$items) {
        $sth->execute(@$row);
    }
    #$dbh->commit;
}

sub select_data {
    my $dbh = shift;
    my $sql = shift;

    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $columns = $sth->{NAME};
    my $result = $sth->fetchall_arrayref;
    return ($columns, $result);
}
 
1;

とりあえず動くものということでざっと書きました。$dbhをいちいち引数で渡しているのがいまいちですかね。クラスを作ってメンバに持たせるとか、いっそのことパッケージグローバルにしてしまえば良いのでしょうけど、もうこれであまり直すこともなさそうだからそのままでも良いかと思ってもみたり。

今のところ出力内容の調整の他には、出力データのエスケープ処理をやらないと駄目だなと思っています。HTML形式で出力してブラウザで見ようとしているので。

たいした量ではないのですが、これでも2時間ちょっとかかっています。

今回はEeePCで書きました。キーを結構押し込まないといけなくて、しょっちゅう入力に失敗していました。あとは右手小指が担当している部分のキーが他よりも小さくてよく打ち間違えます。慣れが必要ですね。