Class::DBI

最新推荐文章于 2024-09-13 21:40:38 发布

hoowa

最新推荐文章于 2024-09-13 21:40:38 发布

阅读量1k

点赞数

文章标签： class 数据库 perl 脚本 null date

本文链接：https://blog.csdn.net/hoowa/article/details/255988

版权

Class::DBI

翻  译：Joe Jiang
出  处：中国Perl协会 FPC(Foundation of Perlchina)
原  名：Class::DBI
作  者：Tony Bowden
原  文：http://www.perl.com/pub/a/2002/11/27/classdbi.html
发  表：November 27, 2002
Perlchina提醒您：请保护作者的著作权,维护作者劳动的结晶。

最近perl.com上的几篇文章（包括Phasebook设计模式）都讨论了Perl代码和数据库打交道的问题。Terrence Brannon的DBIx::Recordset一文试图展示数据库相关的程序也可以更加简单和易于维护。这篇文章是要用Class::DBI来使得这个努力更进一步。

Class::DBI奖励懒惰和简单。目标是使简单的数据库操作几乎不用编程，同时使困难的变得有可能。对很多简单的数据库应用来说，它使我们完全不用编写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 
 
 
  
  ";
 
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 
 
 
  
  ";
 
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就能存取数据库。虽然还没有做什么非常复杂的事情，但是这个小例子也可以让我们的生活更加容易。

建立一个电话本

过去我总是对电话号码有很好的记性。但是诺基亚，爱立信这些公司密谋陷害我。我手机里面的内嵌电话本使我对10／11位数字的记忆能力降低了。现在我看到'calls_on'的输出的时候，已经没法知道"028 9024 4222"代表什么。现在我们需要一个存有联系人信息的电话本来解释这些报表中的数字。

第一步要做的是把我们的信息整理的更整洁点。我们将把号码和通话方两列移到"recipient"表，并增加一个名字列。"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，若我们已经打过电话给某人，那recipient表就会有一个记录。因此我们用find_or_create来取回可能已经存在的条目，或建立一个新条目。

表里面重新填充数据后，我们回到报表脚本。

我们的calls_on脚本现在会失败，因为我们现在不能直接获得通话的'number'了。下面我们得修改成：

#!/usr/bin/perl
 
use My::PhoneBill::Call;
 
my $date = shift or die "Usage: $0 
 
 
  
  ";
 
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

我们得到了recipient表中的ID而不是电话号码，ID不过是一个自增的值。

为了使这个值成为有意义的值，我们在Call类中增加下面的行：

__PACKAGE__->has_a(recipient => 'My::PhoneBill::Recipient');

这告诉它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脚本需要更多技巧，因为搜索的开始现在是recipent而不是call。

所以我们把搜索的开始改成：

my ($recipient) = My::PhoneBill::Recipient->search(number => $number)
  or die "No calls to $number/n";

然后我们需要获得打往那个recipient的所有通话。为了实现这个我们需要申明Recipient和Call之间的关系。和在Call类建立的has_a关系不同，recipient表并不存储和call表的每条通话记录相关的信息。对这种情况我们要为Recipient类添加has_many申明。

__PACKAGE__->has_many(calls => 'My::PhoneBill::Call');

这就为Recipient对象实例建立了一个新的名叫calls的方法，调用它会返回用recipient外键相关的所有Call对象。

这样在calls_to脚本里面已经找到了recipient的前提下，我们只要这样：

 my @calls = $recipient->calls;

这个脚本现在可以和以前一样工作了：

#!/usr/bin/perl
 
use My::PhoneBill::Recipient;
 
my $number = shift or die "Usage: $0 
 
 
  
  ";
 
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 
 
  
  
  
   
   /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";

然而，一个名字可能对应多个号码。因为我们在标量环境而不是列表环境里调用search方法，我们会得到一个iterator而不是每个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

和其他模块协作

有时我们需要在数据库里存储其他模块需要使用的数据。比如我们通话需要有个不同的日期类型，我们更喜欢使用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方法展开，通过stringification来压缩。因为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()方法。这样我们必须使用一个函数引用。这样我们可以告诉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个最常用号码并节省通话费的服务。这就使我们有必要看看那些号码上花了最多的钱。我们假定那些只打过一次的电话号码没必要分析。我们只关心头十个不只一次通话的花费最多的号码。

前面讲过，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，我们会在Recipient.pm里面增加MySQL才支持的查询：

__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等，也可以更进一步偷懒。既然我们的查询输出的列中有一个是Recipient的主键，我们就可以把查询结果喂给sth_to_objects，这个Class::DBI的底层方法使得查询可以返回对象实例列表。

这样我们的脚本就变的简单了：

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并执行。