NAME

Book::Chinese::MasterPerlToday::DBIx-Class - DBIx-Class ORM

DESCRIPTION

本章主要描述如何组织你的 DBIx::Class ORM 的结构和一些用法。

声明

• DBIx::Class 是和 Catalyst 分开，两者各自独立。只是他们的作者之间有交集。

• DBIx::Class 不是为了性能而开发的。如果你的代码强烈要求性能的话，不建议你使用 DBIx::Class

• 如果你觉得 DBIx::Class 很难用，这可能有两个原因，一是你用错了，二是它不是你喜欢的类型。

例子

本章将参考 TheSchwartz::Moosified 的 schema/SQLite.sql 作为 database schema。（请参考 eg/DBIx-Class/schema.sql）

• db.sqlite

  第一步你需要创建一个 database，定义好你的 schema。例如：

  E:\Fayland\chinese-perl-book\eg\DBIx-Class>perl create_sqlite_db.pl

• 初始化 DBIx::Class

  这里有两种方式可供选择，一种是手工编写所有的 DBIx::Class pm, 另一种是使用 DBIx::Class::Schema::Loader 全自动生成。

  通常我会在第一次使用 DBIx::Class::Schema::Loader 的 make_schema_at 来生成，而在以后就手工增加和修改 pm 或再次 make_schema_at。

  eg/DBIx-Class/make_schema_at.pl

  use FindBin qw/$Bin/;
  use DBIx::Class::Schema::Loader qw/ make_schema_at /;
  
  my $dbname = "$Bin/db.sqlite";
  make_schema_at(
      'TheSchwartz::Schema',
      { debug => 1, dump_directory => './lib' },
      [ "dbi:SQLite:dbname=$dbname" ],
  );

• 修改

  DBIx::Class::Schema::Loader v0.04006 创建 schema 的时候是用了 load_classes

  而从 DBIx::Class 0.081 开始，一个更推荐的做法是 load_namespaces。

  不排除以后 Loader 会改为 load_namespaces。所以以下修改可能很快就过时。

  将 TheSchwartz::Schema 里的 load_classes 改为 load_namespaces，将 lib/TheSchwartz/Schema/*.pm 移动到 lib/TheSchwartz/Schema/Result/*.pm 并修改 package 增加 Result::

  load_namespaces 将从 TheSchwartz::Schema::Result 下载入模块，从 TheSchwartz::Schema::ResultSet 下载入 ResultSet 模块。

• 编写 load.t

  为了验证所修改的是正确的，我们将使用一个 eg/DBIx-Class/t/00-load.t 文件来测试。

  #!/usr/bin/perl
  
  use strict;
  use warnings;
  use Test::More tests => 1;
  
  use_ok('TheSchwartz::Schema');
  
  1;

  运行 prove 来测试

  E:\Fayland\chinese-perl-book\eg\DBIx-Class>prove -l t/
  t\00-load.t .. ok
  All tests successful.
  Files=1, Tests=1,  2 wallclock secs ( 0.05 usr +  0.02 sys =  0.06 CPU)
  Result: PASS

  测试的重要性是再说一百次都不会过分。本章将从头到尾贯穿着不同的测试来验证自己所写的代码是自己所需的。

  如何编写测试将在 Book::Chinese::MasterPerlToday::BeACPANAuthor 中做简单介绍。

• 更多的测试代码

  仅仅使用 use_ok 是验证代码是否有语法错误。而我们从上面的测试中无法知道具体的位置移动到底对不对。

  eg/DBIx-Class/t/01-basic.t

  #!/usr/bin/perl
  
  use strict;
  use warnings;
  use Test::More tests => 5;
  use FindBin qw/$Bin/;
  use TheSchwartz::Schema;
  
  my $dbname = "$Bin/../db.sqlite";
  my $schema = TheSchwartz::Schema->connect(
      "dbi:SQLite:dbname=$dbname", '', '', {
      RaiseError => 1,
      PrintError => 0,
  } );
  isa_ok($schema, 'TheSchwartz::Schema');
  
  foreach ('Exitstatus', 'Job', 'Error', 'Funcmap') {
      can_ok( $schema->resultset($_), 'search' );
  }
  
  1;

  在这里我们创建了 TheSchwartz::Schema 的实例，用 ->connect 方法。然后判断了 4 个 resultset 都能使用 search 方法 (DBIx::Class::ResultSet 提供)，最后我们运行 prove

  E:\Fayland\chinese-perl-book\eg\DBIx-Class>prove -l t/
  t\00-load.t ... ok
  t\01-basic.t .. ok
  All tests successful.
  Files=2, Tests=10,  1 wallclock secs ( 0.05 usr +  0.05 sys =  0.09 CPU)
  Result: PASS

• ResultSet

  DBIx::Class 的正确使用方法就是用 ResultSet（最起码个人认为是正确的）。

  与某个 table 相关的代码放到相关的 ResultSet 里，再写一个测试文件。

  比如针对 TheSchwartz::Schema::Result::Error 我们写一个 TheSchwartz::Schema::ResultSet::Error, eg/DBIx-Class/lib/TheSchwartz/Schema/ResultSet/Error.pm

  package TheSchwartz::Schema::ResultSet::Error;
  
  use strict;
  use warnings;
  use base 'DBIx::Class::ResultSet';
  
  sub failure_log {
      my ( $self, $jobid ) = @_;
  
      my $rs = $self->search( {
          jobid => $jobid
      }, {
          columns => ['message']
      } );
  
      my @failures;
      while (my $r = $rs->next) {
          push @failures, $r->message;
      }
  
      return @failures;
  }
  
  sub failures {
      return scalar shift->failure_log(@_);
  }
  
  1;

  所有的 ResultSet 模块都应该 use base 'DBIx::Class::ResultSet' 或它的继承类。调用的方法可以参考 eg/DBIx-Class/t/02-error.t

  #!/usr/bin/perl
  
  use strict;
  use warnings;
  use Test::More tests => 5;
  use FindBin qw/$Bin/;
  use TheSchwartz::Schema;
  
  my $dbname = "$Bin/../db.sqlite";
  my $schema = TheSchwartz::Schema->connect(
      "dbi:SQLite:dbname=$dbname", '', '', {
      RaiseError => 1,
      PrintError => 0,
  } );
  
  my $dbh = $schema->storage->dbh;
  # truncate the table so we can run the tests again and again
  $dbh->do("DELETE FROM error");
  # insert some faked data
  my $sth = $dbh->prepare("INSERT INTO error (error_time, jobid, message, funcid) VALUES (?, ?, ?, ?)");
  $sth->execute(time(), 1, 'Message A', 1);
  $sth->execute(time(), 2, 'Message B', 1);
  $sth->execute(time(), 2, 'Message C', 1);
  
  # test failure_log/failures
  my @failures = $schema->resultset('Error')->failure_log( 2 );
  is scalar @failures, 2;
  ok( grep { $_ eq 'Message B' } @failures );
  ok( grep { $_ eq 'Message C' } @failures );
  
  my $failure_num = $schema->resultset('Error')->failures( 1 );
  is $failure_num, 1;
  $failure_num = $schema->resultset('Error')->failures( 2 );
  is $failure_num, 2;
  
  # truncate the table so it wouldn't effect others
  $dbh->do("DELETE FROM error");
  
  1;

  调用的方法跟普通的 ->search 之类的一样，$schema->resultset('Error')->failure_log, $schema->resultset('Error') 其实就是 TheSchwartz::Schema::ResultSet::Error

  在 TheSchwartz::Schema::ResultSet::Error 里，如果想调用其他的 resultset 或想用 dbh

  sub test {
      my ( $self, $args ) = @_;
      
      my $schema = $self->result_source->schema;
      my $dbh = $schema->storage->dbh;
      $schema->resultset('Job')->do_anything();

  所有的 ResultSet 之间可以用 $schema 相连。上述例子虽然没什么大用处，但是显示了基本的作法。

  E:\Fayland\chinese-perl-book\eg\DBIx-Class>prove -l t/02-error.t
  t/02-error.t .. ok
  All tests successful.
  Files=1, Tests=5,  1 wallclock secs ( 0.05 usr +  0.03 sys =  0.08 CPU)
  Result: PASS

FAQ

• 复杂的 search

  建议阅读 DBIx::Class::Manual::Cookbook 和 SQL::Abstract

  resultset('XXX')->search( {
      requestor => 'inna',
      worker => ['nwiger', 'rcwe', 'sfz'],
      status => { '!=', 'completed' }
  } );
  # SQL:
  # FROM xxx WHERE
  #   ( requestor = ? ) AND ( status != ? )
  #   AND ( worker = ? OR worker = ? OR worker = ? )
  
  resultset('XXX')->search( {
      date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
      date_expires => { '<' => \"now()" }
  } );
  # SQL:
  # FROM xxx WHERE
  #   date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()
  
  resultset('XXX')->search( {
      -or => [
          -and => [a => 1, b => 2],
          -or  => [c => 3, d => 4],
          e    => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
      ],
  } );
  # SQL:
  # FROM xxx WHERE
  #   ( (    ( a = ? AND b = ? ) 
  #       OR ( c = ? OR d = ? ) 
  #       OR ( e LIKE ? AND e LIKE ? ) ) )

• as_query

  如果你对你所写的 ->search 不太确定，你可以使用 ->as_query 来 debug

  $schema->resultset('user')->search( {
    user_id => { 'IN', [1, 2] },
    -or => [
      last_visit => { '>', time() - 86400 },
      last_update => { '>', time() - 86400 },
    ]
  }, {
    columns => ['username'],
    rows    => 1,
  } )->as_query;
  
  [ '(SELECT me.username FROM user me 
     WHERE ( ( ( last_visit > ?
              OR last_update > ? )
        AND user_id IN ( ?, ? ) ) )
     LIMIT 1)',
    [ 'last_visit', 1242290843 ]
  ... ]

小结

限于水平有限，只能简单介绍 DBIx::Class 到此。我会在接下来的 Catalyst 说再次写 DBIx::Class 例子。

至于其他的诸如 Relationship 之类的，只能是以后再说了。

AUTHOR

Fayland Lam, <fayland at gmail.com>

COPYRIGHT & LICENSE

Copyright (c) 2009 Fayland Lam

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.