类::DBI
Perl.com上的一些文章，包括最近关于短语簿设计模式的文章，讨论了编写与数据库交互的Perl代码时所面临的问题。Terrence Brannon的DBIx::Recordset文章试图展示如何使处理数据库的代码变得更加简单和易于维护。在这篇文章中，我将尝试展示如何使Class::DBI使这一切变得更加简单。
Class::DBI推崇懒惰和简单。其目标是使简单的数据库交互变得简单，同时仍然允许处理复杂的数据库交互。对于许多简单的应用程序，它可以完全替代编写SQL的需求。另一方面，它不会强迫你构建复杂的数据结构来指定复杂的查询；如果你真的需要原始SQL的强大功能或表达力，那么它就会让路，让你回到使用原始SQL。
查看Class::DBI的最佳方式是使用它构建一个简单的应用程序。在这篇文章中，我将构建一个用于分析电话账单的工具。
Data::BT::PhoneBill（从CPAN获取），提供了一个简单接口，用于处理从BT网站下载的电话账单。因此，有了这个模块和一些最近的电话账单，我们将这些详细信息存储到数据库中，并查看如何从中提取有用信息。
Class::DBI基于这样一个假设：你的数据库中的每个表都有一个相应的类。尽管每个类都可以设置自己的连接信息，但将连接封装在一个类中，并让其他所有类从该类继承是一个更好的主意。因此，我们设置了我们的数据库，并为我们的应用程序创建了一个基类
package My::PhoneBill::DBI;
use base 'Class::DBI';
__PACKAGE__->set_db('Main', 'dbi:mysql:phonebill', 'u/n', 'p/w');
1;
我们简单地从Class::DBI继承，并使用‘set_db’方法设置数据库的连接信息。现在这个类中我们只需要这些，所以接下来我们设置我们的表来存储电话通话信息
CREATE TABLE call (
callid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20) NOT NULL,
destination VARCHAR(255) NOT NULL,
calldate DATE NOT NULL,
calltime TIME NOT NULL,
type VARCHAR(50) NOT NULL,
duration SMALLINT UNSIGNED NOT NULL,
cost FLOAT(8,1)
);
为此，我们设置了一个相应的类
package My::PhoneBill::Call;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('call');
__PACKAGE__->columns(All =>
qw/callid number destination calldate calltime type duration cost/);
1;
我们从基类继承连接信息，然后指定我们正在处理的表以及它的列。现在我们有足够的信息来填充表。
因此，我们创建了一个简单的“populate_phone_bill”脚本
#!/usr/bin/perl
use Data::BT::PhoneBill;
use My::PhoneBill::Call;
my $file = shift or die "Need a phone bill file";
my $bill = Data::BT::PhoneBill->new($file) or die "Can't parse bill";
while (my $call = $bill->next_call) {
My::PhoneBill::Call->create({
number => $call->number,
calldate => $call->date,
calltime => $call->time,
destination => $call->destination,
duration => $call->duration,
type => $call->type,
cost => $call->cost,
});
}
create()调用运行SQL来执行每条记录的INSERT操作。由于我们使用Class::DBI，并且已定义我们的主键列是AUTO_INCREMENT，所以我们不需要为该列指定值。在支持序列的数据库中，我们还可以通知Class::DBI应该使用哪个序列来提供主键。
现在，我们已经填充了一个包含通话记录的表，我们可以开始运行针对它的查询。让我们编写一个简单的脚本，报告指定号码的所有通话
#!/usr/bin/perl
use My::PhoneBill::Call;
my $number = shift or die "Usage: $0 <number>";
my @calls = My::PhoneBill::Call->search(number => $number);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s %s - %d secs, %.1f pence\n",
$call->calldate, $call->calltime, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;
在这里，我们可以看到Class::DBI为我们提供了一个‘search’方法。我们提供一个列/值对的散列，并返回所有匹配的记录。每个结果都是Call类的一个实例，每个实例都有一个与每列相对应的访问器方法。（它也是一个修改器方法，因此我们可以更新该记录，但我们目前只进行报告）。
因此，如果我们想查看我们经常给报时器打电话的频率，我们可以运行以下代码
> perl calls_to 123
2002-09-17 11:06:00 - 5 secs, 8.5 pence
2002-10-19 21:20:00 - 8 secs, 8.5 pence
Total: 2 calls, 17 pence
同样，如果我们想查看特定日期的所有通话，我们可以有一个‘calls_on’脚本
#!/usr/bin/perl
use My::PhoneBill::Call;
my $date = shift or die "Usage: $0 <date>";
my @calls = My::PhoneBill::Call->search(calldate => $date);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s) %s - %d secs, %.1f pence\n",
$call->calltime, $call->number, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;
运行它得到以下结果
> perl calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 secs, 4.2 pence
21:20:00) 123 - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pence
如我们所承诺的，我们编写了一个数据库应用程序，而没有编写任何SQL。好吧，我们还没有做任何事情非常复杂，但即使对于这种简单的使用，Class::DBI也使我们的生活变得更加容易。
构建电话簿
我以前对电话号码有很好的记忆力。但是诺基亚、爱立信等等，它们联合起来对付我。它们给我的手机内置了通讯录，确保了我负责记住10到11位数字的那部分大脑逐渐萎缩。现在，当我查看‘calls_on’的输出时，我不知道“028 9024 4222”代表谁。所以，让我们构建一个可以存储这些信息的电话簿，然后更改我们的报告以使用它。
我们应该做的第一件事是更好地组织我们的信息。我们将取number和destination列，并将它们移动到“recipient”表中，我们将为此表添加一个name列。“Destination”与号码相关联时没有太多的意义，而不是与呼叫相关联，所以我们将它重命名为“location”。
CREATE TABLE recipient (
recipid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20) NOT NULL,
location VARCHAR(255) NOT NULL,
name VARCHAR(255),
KEY (number)
);
然后我们为这个表创建相关的类
package My::PhoneBill::Recipient;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('recipient');
__PACKAGE__->columns(All => qw/recipid number location name/);
1;
我们还需要修改Call表
CREATE TABLE call (
callid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
* recipient MEDIUMINT UNSIGNED NOT NULL,
calldate DATE NOT NULL,
calltime TIME NOT NULL,
type VARCHAR(50) NOT NULL,
duration SMALLINT UNSIGNED NOT NULL,
cost FLOAT(8,1),
* KEY (recipient)
);
及其关联的类
package My::PhoneBill::Call;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('call');
__PACKAGE__->columns(All =>
* qw/callid recipient calldate calltime type duration cost/);
1;
然后我们可以修改我们的数据库填充脚本
#!/usr/bin/perl
use Data::BT::PhoneBill;
use My::PhoneBill::Call;
*use My::PhoneBill::Recipient;
my $file = shift or die "Need a phone bill file";
my $bill = Data::BT::PhoneBill->new($file) or die "Can't parse bill";
*while (my $call = $bill->next_call) {
* my $recipient = My::PhoneBill::Recipient->find_or_create({
* number => $call->number,
* location => $call->destination,
* });
* My::PhoneBill::Call->create({
* recipient => $recipient->id,
calldate => $call->date,
calltime => $call->time,
duration => $call->duration,
type => $call->type,
cost => $call->cost,
});
}
这次我们需要先创建Recipient，这样我们就可以从Call中链接到它。但我们不希望为每个呼叫创建一个新的Recipient - 如果我们曾经给过这个人打电话，收件人表中已经有了条目：所以我们使用find_or_create来返回现有的条目，如果它在的话，否则创建一个新的。
在表重新填充后，我们可以回到我们的报告脚本。
我们的calls_on脚本现在失败了，因为我们不能要求一个呼叫提供其‘number’。所以我们将其更改为
#!/usr/bin/perl
use My::PhoneBill::Call;
my $date = shift or die "Usage: $0 <date>";
my @calls = My::PhoneBill::Call->search(calldate => $date);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s) %s - %d secs, %.1f pence\n",
* $call->calltime, $call->recipient, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;
然而，运行它并没有真正给我们想要的结果
> perl calls_on 2002-10-19
...
18:36:00) 67 - 41 secs, 4.2 pence
21:20:00) 47 - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pence
现在，我们得到的不是电话号码，而是来自收件人表的ID，这只是一个自动增长的值。
要将这个变成一个有意义的值，我们向Call类添加以下行
__PACKAGE__->has_a(recipient => 'My::PhoneBill::Recipient');
这告诉它，收件人方法不仅返回一个简单的值，而且这个值应该自动转换成Recipient类的实例。
当然，calls_on仍然不正确
> perl calls_on 2002-10-19
...
18:36:00) My::PhoneBill::Recipient=HASH(0x835b6b8) - 41 secs, 4.2 pence
21:20:00) My::PhoneBill::Recipient=HASH(0x835a210) - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pence
但现在只需要对脚本进行一点小的调整
printf "%s) %s - %d secs, %.1f pence\n",
$call->calltime, $call->recipient->number, $call->duration, $call->cost;
现在一切又运行得完美了
> perl calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 secs, 4.2 pence
21:20:00) 123 - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pence
calls_to脚本稍微复杂一些，因为初始搜索现在是针对收件人而不是呼叫。
所以，我们将初始搜索更改为
my ($recipient) = My::PhoneBill::Recipient->search(number => $number)
or die "No calls to $number\n";
然后我们需要获取所有指向该收件人的呼叫。为此，我们需要通知Recipient与呼叫的关系。与我们在Call类中设置的has_a不同，收件人表不存储任何与呼叫表相关的值，这些值可以在需要时膨胀。相反，我们需要在Recipient中添加一个has_many声明
__PACKAGE__->has_many(calls => 'My::PhoneBill::Call');
这为每个收件人对象创建了一个新的方法calls，返回一个包含Call对象的列表，其中recipient方法是我们的主键。
所以，在calls_to脚本中找到我们的收件人后，我们可以简单地询问
my @calls = $recipient->calls;
现在脚本工作得和以前一样
#!/usr/bin/perl
use My::PhoneBill::Recipient;
my $number = shift or die "Usage: $0 <number>";
my ($recipient) = My::PhoneBill::Recipient->search(number => $number)
or die "No calls to $number\n";
my @calls = $recipient->calls;
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s %s - %d secs, %.1f pence\n",
$call->calldate, $call->calltime, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;
现在我们又能得到旧的结果了
> perl calls_to 123
2002-09-17 11:06:00 - 5 secs, 8.5 pence
2002-10-19 21:20:00 - 8 secs, 8.5 pence
Total: 2 calls, 17 pence
接下来我们需要一个脚本来给地址簿中的号码命名
#!/usr/bin/perl
use My::PhoneBill::Recipient;
my($number, $name) = @ARGV;
die "Usage $0 <number> <name>\n" unless $number and $name;
my $recip = My::PhoneBill::Recipient->find_or_create({number => $number});
my $old_name = $recip->name;
$recip->name($name);
$recip->commit;
if ($old_name) {
print "OK. $number changed from $old_name to $name\n";
} else {
print "OK. $number is $name\n";
}
这让我们可以将一个号码与一个名字关联起来
> perl add_phone_number 123 "Speaking Clock"
OK. 123 is Speaking Clock
现在，通过对我们calls_on脚本的微小更改，我们可以输出已知的名字
printf "%s) %s - %d secs, %.1f pence\n",
$call->calltime, $call->recipient->name || $call->recipient->number,
$call->duration, $call->cost;
> perl calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 secs, 4.2 pence
21:20:00) Speaking Clock - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pence
为了使calls_to脚本能够在给出名字或号码时工作，我们可以使用
my $recipient = My::PhoneBill::Recipient->search(name => $number)
|| My::PhoneBill::Recipient->search(number => $number)
|| die "No calls to $number\n";
然而，因为我们是在标量上下文中调用搜索，而不是正常的列表上下文，所以我们得到的是一个迭代器，而不是单个Recipient对象。由于一个名字可能映射到多个号码，我们需要依次遍历这些号码
my @calls;
while (my $recip = $recipient->next) {
push @calls, $recip->calls;
}
（为每个号码打印单独的总额留作读者练习。）
> perl calls_to "Speaking Clock"
2002-09-17 11:06:00 - 5 secs, 8.5 pence
2002-10-19 21:20:00 - 8 secs, 8.5 pence
Total: 2 calls, 17 pence
与其他模块一起工作
有时我们存储在数据库中的信息可以用来与非 Class::DBI 模块一起工作。例如，如果我们想以不同的方式格式化我们的通话日期，我们可能想将它们转换为 Date::Simple 对象。同样，Class::DBI 使这一点变得容易。
在 Call 类中，我们再次使用 has_a 来声明这种关系
__PACKAGE__->has_a(recipient => 'My::PhoneBill::Recipient');
*__PACKAGE__->has_a(calldate => 'Date::Simple');
现在，当我们获取 calldate 时，它将自动膨胀为 Date::Simple 对象。因此，我们可以改变 calls_to 的输出，以更美观的格式打印日期
printf "%s %s - %d secs, %.1f pence\n",
$call->calldate->format("%d %b"), $call->calltime,
$call->duration, $call->cost;
> perl calls_to "Speaking Clock"
17 Sep 11:06:00 - 5 secs, 8.5 pence
19 Oct 21:20:00 - 8 secs, 8.5 pence
Total: 2 calls, 17 pence
Class::DBI 假设任何非 Class::DBI 类都是通过 new 方法膨胀的，并且可以通过字符串化来收缩。由于这两者都对 Date::Simple 成立，我们不需要做更多的事情。如果情况不是这样——例如，如果您想使用 Time::Piece 而不是 Date::Simple——您需要告诉 Class::DBI 如何在值进入和离开数据库时进行膨胀和收缩。
__PACKAGE__->has_a(calldate => 'Time::Piece',
inflate => sub { Time::Piece->strptime(shift, "%Y-%m-%d") },
deflate => 'ymd'
);
将 Time::Piece 对象收缩回适用于 MySQL 的 ISO 日期字符串非常简单：您只需调用其 ymd() 方法。因此，我们可以将其指定为字符串。膨胀更困难，因为它需要一个带有两个参数的 strptime() 调用。因此，我们需要将其指定为子例程引用。膨胀时，它将使用数据库中的值作为其唯一的参数。因此，我们可以将其传递给 Time::Piece 的 strptime 方法，指定实例化的格式。
使用 Time::Piece 而不是 Date::Time 需要对我们输出脚本进行一项进一步的更改
printf "%s %s - %d secs, %.1f pence\n",
* $call->calldate->strftime("%d %b"), $call->calltime,
$call->duration, $call->cost;
最常拨打的电话号码
BT 提供一项服务，允许您在您选择的 10 个号码上节省金钱。因此，如果我们能够找出我们花费最多金钱拨打的号码将很有用。我们将假设我们只拨打过一个号码的号码不值得添加到我们的列表中，因为它可能是一个一次性通话。因此，我们希望有超过一个通话的最高消费的 10 个号码。
正如我们之前所说，Class::DBI 并不尝试提供任何任意 SQL 的语法，因此无法直接请求此类信息。相反，我们将尝试一种简单的方法。
首先，我们将向 Recipient 类添加一个方法，以告诉我们我们对该号码的通话总花费
use List::Util 'sum';
sub total_spend {
my $self = shift;
return sum map $_->cost, $self->calls;
}
然后我们可以创建一个 top_ten 脚本
#!/usr/bin/perl
use My::PhoneBill::Recipient;
my @recipients = My::PhoneBill::Recipient->retrieve_all;
my @regulars = grep $_->calls > 1, @recipients;
my @sorted = sort { $b->total_spend <=> $a->total_spend } @regulars;
foreach my $recip (@sorted[0 .. 9]) {
printf "%s - %d calls = %d pence\n",
$recip->name || $recip->number,
scalar $recip->calls,
$recip->total_spend;
}
但是，一旦数据库中存储了数百个通话，这就会变得非常慢。这主要是因为我们是根据方法调用进行排序的。用 Schwartzian Transform 替换上述排序可以显著加快速度
my @sorted = map $_->[0],
sort { $b->[1] <=> $a->[1] }
map [ $_, $_->total_spend ], @regulars;
现在，直到数据库变得非常大，这可能已经足够快了——特别是您可能不会经常运行此脚本。
然而，如果这还不够，我们总是可以退回到 SQL。当然，当我们需要针对速度进行优化时，我们通常会失去其他一些东西——在这个例子中，是可移植性。在这个例子中，我们正在使用 MySQL，所以我会将相关的 MySQL 特定查询添加到 Recipient.pm
__PACKAGE__->set_sql(top_ten => qq{
SELECT recipient.recipid,
SUM(call.cost) AS spend,
COUNT(call.callid) AS calls
FROM recipient, call
WHERE recipient.recipid = call.recipient
GROUP BY recipient.recipid
HAVING calls > 1
ORDER BY spend DESC
LIMIT 10
});
然后我们可以设置一个方法来返回这些相关的对象
sub top_ten {
my $class = shift;
my $sth = $class->sql_top_ten;
$sth->execute;
return $class->sth_to_objects($sth);
}
使用 set_sql 设置的任何 SQL 都可以通过在其名称前面加上 sql_ 来作为预先准备好的 DBI 语句句柄检索——因此，我们可以使用 my $sth = $class->sql_top_ten 来获取这个 top_ten。
尽管我们可以愉快地执行这个命令，然后调用任何传统的DBI命令（如fetchrow_array等），但我们也可以走捷径。因为我们的查询返回的列之一是收件人的主键，所以我们可以直接将结果输入到Class::DBI的底层方法中，该方法允许搜索返回对象列表的sth_to_objects。
因此，我们的脚本就变得非常简单
foreach my $recip (My::PhoneBill::Recipient->top_ten) {
printf "%s - %d calls = %d pence\n",
$recip->name || $recip->number,
scalar $recip->calls,
$recip->total_spend;
}
正如我们所见，Class::DBI使得许多常见的数据库任务变得非常简单，无需编写任何SQL代码。但是，当您真正需要时，编写并执行所需的SQL语句也是非常容易的。
标签
反馈
这篇文章有什么问题吗？请通过在GitHub上打开一个问题或拉取请求来帮助我们。
