Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>第五章掌握Model 第二节 DBIx::Class::Schema入门

第五章掌握Model 第二节 DBIx::Class::Schema入门

时间：2007-04-19 来源：xiaoshengcaicai

这一节将以mysql数据库为例.

创建数据库,名为X.初步创建5个表:
book: 字段有 id, author_id, name
author: 字段有 id, name, is_male
chapter: 字段有id, name, book_id, subject, content
type: 字段有id, name
book_type: 字段有id, book_id, type_id

一个author有多个book, 一个book有多个chapter,
type指book的类别
一本book可能属于多个类别, book 跟type的关系存在book_type这张关系表里.

建数据库语句如下:

CREATE DATABASE `X` CHARACTER SET utf8;

USE `X`;

CREATE TABLE `book` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `author_id` int(10) unsigned default NULL,
  `name` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `is_male` tinyint(1) default NULL,
  `name` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `chapter` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `book_id` int(10) unsigned default NULL,
  `name` varchar(255) default NULL,
  `subject` varchar(255) default NULL,
  `content` text default NULL,
  PRIMARY KEY (`id`),
  KEY `x_book_id` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `type` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `book_type` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `book_id` int(10) unsigned default NULL,
  `type_id` int(10) unsigned default NULL,
  PRIMARY KEY (`id`),
  KEY `x_book_id` (`book_id`),
  KEY `x_type_id` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

首先创建一个MySchema.pm,使之从DBIx::Class::Schema继承,如下:

package MySchema;
use base 'DBIx::Class::Schema';
use strict;
use warnings;

__PACKAGE__->load_classes();

1;

接下来为每个表创建一个class,这种class称为DBIx::Class::ResultSource.

建立MySchema/Book.pm, MySchema/Author.pm, MySchema/Type.pm, MySchema/Chapter.pm, MySchema/BookType.pm,如下:

package MySchema::Book;
use base 'DBIx::Class';
use strict;
use warnings;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('book');
__PACKAGE__->add_columns(qw/id name author_id/);
__PACKAGE__->set_primary_key('id');
1;
package MySchema::Author;
use base 'DBIx::Class';
use strict;
use warnings;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('author');
__PACKAGE__->add_columns(qw/id name is_male /);
__PACKAGE__->set_primary_key('id');
1;

package MySchema::Chapter;
use base 'DBIx::Class';
use strict;
use warnings;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('chapter');
__PACKAGE__->add_columns(qw/id name book_id subject content/);
__PACKAGE__->set_primary_key('id');
1;

package MySchema::Type;
use base 'DBIx::Class';
use strict;
use warnings;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('type');
__PACKAGE__->add_columns(qw/id name/);
__PACKAGE__->set_primary_key('id');
1;

package MySchema::BookType;
use base 'DBIx::Class';
use strict;
use warnings;
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('book_type');
__PACKAGE__->add_columns(qw/id book_id author_id/);
__PACKAGE__->set_primary_key('id');
1;

MySchema.pm它继承自DBIx::Class::Schema, 它代表的是对数据库的连接, 在MySchema.pm里面的这行代码:
__PACKAGE__->load_classes();
表示它会去加载MySchema::*.pm, 也就是5个表对应的5个ResultSource.

现在我们已经可以通过MySchema来访问数据库了,下面我们写一个测试脚本:

#!perl
use strict;
use MySchema;
my $schema = MySchema->connect(
    'dbi:mysql:x;localhost',
    'root',
    '',
    { AutoCommit => 1 },
);
$schema->storage->debug(1);
$schema->resultset('Book')->find(1);

MySchema->connect 内部其实就是DBI->connect, 连接数据库.
$schema->storage->debug(1) 把DBIC的DEBUG模式打开, 这样的话, 下面的DBIC语句如果执行了SQL操作,将会把执行的SQL语句打印出来.
$schema->resultset('Book')->find(1); 这行代码执行后, 屏幕上会打印出这样的SQL语句:

SELECT me.id, me.name, me.author_id FROM book me WHERE ( ( me.id = ? ) ): '1'

这行SQL实际上就是 SELECT * FROM book WHERE id=1
$schema->resultset('Book') 它实际上是SELECT * FROM book 的一个结果集.

在这样的一个结果集(resultset)上我们一般可以使用这些方法:

◆ find: 基于主键或者UNIQUE 键的搜索, 返回一条记录. (一条记录的对象称为RowObject.)
上例中id是BOOK表的主键, find(1)就是SELECT * FROM BOOK WHERE id=1.

next: 逐条迭代结果集中的记录, 比如要打印结果集中所有记录的id,可以这样用:
while(my $row_object = $resultset->next) {
print $row_object->id, "\n";
}

上面的这行代码：$row_object->id
对于某个RowObject,要得到它的某个字段的值，可以通过 $row_object->字段名()来调用，要修改它的某个字段的值，可以通过 $row_object->字段名( $new_value ); 来修改它的值。

◆ search: 最常用的数据库检索方法, 在标量上下文它返回检索过后的resultset,在列表上下文它返回所有记录对象的一个数组(也就是一个RowObject数组).
search方法它有2个参数, 2个参数都是HASH引用, 第一个用来构造SQL语句中WHERE的条件, 第二个用来产生SQL语句的其他属性: 比如order by, group by等, 具体请参考
http://search.cpan.org/~nwiger/SQL-Abstract-1.22/lib/SQL/Abstract.pm

◆ update: 更新结果集的一些字段.

◆ delete: 删除结果集的一些记录.

◆ create: 创建一条新记录

下面是一些DBIC语句跟它产生的SQL:

$schema->resultset('Book')->search(); 等同于$schema->resultset('Book')

SELECT *　FROM BOOK;

$schema->resultset('Book')->search(
    {
        id => 2,
        name => { 'like' => '%perl' }
    }
);
SELECT *　FROM BOOK　WHERE　id=2 AND name like '%perl';

$schema->resultset('Book')->search(
    {
        id => {'-in' => [1,2,3]},
    }
);
SELECT *　FROM BOOK WHERE id IN (1,2,3);

$schema->resultset('Book')->search(
    {
        '-or' => [
            id => 1,
            name => 'perl',
        ],
    }
);
SELECT *　FROM BOOK WHERE id=1 OR name='perl';

$schema->resultset('Book')->search(
    {
       id => { '>' => 10}
    },
    {
        order_by => 'id desc'
    }
);
SELECT *　FROM BOOK WHERE id>=10 order by id desc;

$schema->resultset('Book')->search(
    {
author => \"is not null",
    },
    {
        select => ['author', 'count(*)'],
        as => ['author', 'book_count'],
        group_by => 'author'
    }
);

SELECT author, count(*) FROM BOOK WHERE author is not null group by author;

$schema->resultset('Book')->update({ name => 'xiaosheng'});
UPDATE book SET name='xiaosheng';

$schema->resultset('Book')->search({ id => 2})->update({ name => 'xiaosheng'});
UPDATE book SET name='xiaosheng' WHERE id=2;

$schema->resultset('Book')->delete();
DELETE FROM book;

$schema->resultset('Book')->search( {name => 'perl'} )->delete();
DELETE FROM book WHERE name='perl';

$schema->resultset('Book')->create( {name => 'catalyst', author_id => 2} );
INSERT INTO book (author_id, name) values(2, 'catalyst');

现在大家可能会发现,上面的SQL全部是单表操作.
如果要实现从Book表join到author表, 那么首先要定义从Book到Author的关系.

DBIC常用的关系有:
◆ belongs_to: 比如我们这里的Book表的author_id实际上是Author表的主键,那么可以定义Book到Author的belongs_to关系. 同理可以定义从Chapter到Book的belongs_to关系
◆ has_many: 跟belongs_to的关系正好反过来, 这里我们可以定义Author到Book的has_many的关系,也可以定义Book到Chapter的has_many关系.
◆ 其他的关系: might_have, many_to_many, has_one等不在讨论范围内. 更详细的信息可参考CPAN文档.

注意我们如果要从Book表join Author表的话, 需要而且仅需要在Book里面定义belongs_to关系, 并不需要Author里面也定义关系.

那么现在我们在Book里面定义belongs_to：

__PACKAGE__->belongs_to( 'author', 'MySchema::Author', 'author_id' );
从这行代码里面，可以得知
MySchema::Book belongs_to MySchema::Author
'author'指的是这个关系的名字，
'author_id' 指book.author_id是一个外键，它是MySchema::Author的主键。

现在来测试一下这个关系：

#!perl
use strict;
use MySchema;
my $schema = MySchema->connect(
    'dbi:mysql:x;localhost',
    'root',
    '',
    { AutoCommit => 1 },
);
$schema->storage->debug(1);
my @book_list = $schema->resultset('Book')->search(
    {},
    {
        join =>['author']
    }
);

＃打印出来的SQL:

SELECT me.id, me.name, me.author_id FROM book me JOIN author author ON ( author.id = me.author_id ):

假设book表有3条记录

id, author_id, name

1, 1, 'book1'

2, 1, 'book2'

3, 2, 'book3'

假设author表有2条记录
id, is_fale, name
1, 1, 'author1'
2, 0, 'author2'

继续执行下面的代码：

for my $row_object_book (@book_list) {
    print 'book: ', $row_object_book->id, ', author name is ';
    print $row_object_book->author->name;
    print "\n";
}

会打印出来的SQL：
SELECT me.id, me.name, me.is_male FROM author me WHERE ( ( ( me.id = ? ) ) ): '1'

SELECT me.id, me.name, me.is_male FROM author me WHERE ( ( ( me.id = ? ) ) ): '1'

SELECT me.id, me.name, me.is_male FROM author me WHERE ( ( ( me.id = ? ) ) ): '2'

我们发现每次执行$row_object_book->author->name 这行代码，就会产生一条SQL：
SELECT * FROM author WHERE id = ?
这条SQL只是为了得到author的name而已，其实我们可以在把book跟author join起来的时候就把author的name取出来了，无需到后来才一条一条去取，我们把上例中的join改成prefetch：

my @book_list = $schema->resultset('Book')->search(
    {},
    {
        prefetch =>['author']
    }
);

＃打印出来的SQL:

SELECT me.id, me.name, me.author_id, author.id, author.name, author.is_male FROM book me JOIN author author ON ( author.id = me.author_id )

继续执行下面的代码：

for my $row_object_book (@book_list) {
    print 'book: ', $row_object_book->id, ', author name is ';
    print $row_object_book->author->name;
    print "\n";
}

不会再打印出SQL，因为我们上面的prefetch已经把book跟author一起取了出来，所以

$row_object_book->author->name; 不会再进行数据库查询

上例中，
$row_object_book 是一本书的row object,
$row_object_book->author，是这本书的author的row object.
如果我们定义了从book到chapter的has_many关系（名为chapters），那么，通过
$row_object_book->chapter可以得到这个属于这本book的所有chapter。

见下例：

__PACKAGE__->has_many( 'chapters', 'MySchema::Chapter', 'book_id' );
1;

继续测试一下：

假设chapter表有3条记录

id, book_id, name

1, 1, 'chapter1'
2, 1, 'chapter2'

3, 1, 'chapter3'

my $book = $schema->resultset('Book')->find(1);

＃打印出来的SQL:

SELECT me.id, me.name, me.author_id FROM book me WHERE ( ( me.id = ? ) ): '1'

继续执行下面的代码：

my $rs_chapters = $book->chapters;

while(my $row_object = $rs_chapters->next) {
print $row_object->name, "\n";
}
＃while迭代之后打印出来的SQL:

SELECT me.id, me.name, me.book_id, me.subject, me.content FROM chapter me WHERE ( me.book_id = ? ): '1'

my @chapter_list = $book->chapters;

＃打印一样出来的SQL:

SELECT me.id, me.name, me.book_id, me.subject, me.content FROM chapter me WHERE ( me.book_id = ? ): '1'

#如果要迭代每一个chapter，要用for 循环

for my $row_object (@chapter_list) {

print $row_object->name, "\n";

}

上例中， $book->chapters 跟 resultset->search类似，它的返回值也是看上下文的，在标量上下文它返回一个resultset,在列表上下文它返回一个数组(也就是一个RowObject数组).

关于DBIx::Class::Schema的介绍到此为止，建议大家阅读CPAN上的DBIC文档。

第五章 掌握Model 第二节 DBIx::Class::Schema入门

第五章掌握Model 第二节 DBIx::Class::Schema入门