NAME
Class::DBI::View - Virtual table for Class::DBI
SYNOPSIS
package CD::Music::SalesRanking;
use base qw(CD::DBI); # your Class::DBI base class
use Class::DBI::View qw(TemporaryTable);
__PACKAGE__->columns(All => qw(id count));
__PACKAGE__->setup_view(<<SQL);
SELECT cd_id AS id, COUNT(*) AS count
FROM cd_sales
GROUP BY cd_id
ORDER BY count
LIMIT 1, 10
SQL
DESCRIPTION
Class::DBI::View is a Class::DBI wrapper to make virtual VIEWs.
METHODS
import
use Class::DBI::View qw(TemporaryTable);
use Class::DBI::View qw(SubQuery);
use Class::DBI::View qw(Having);
When use()ing this module, you should supply which strategy
(implmentation) you use to create virtual view, which is one of
'TemporaryTable', 'SubQuery' or 'Having'.
setup_view
$class->setup_view($sql [, %opt ]);
Setups virtual VIEW for $class. $sql should be a raw SQL statement
to build the VIEW.
%opt can be any of these:
cache_for_session
Caches temporary table per database connection. Only valid for
"TemporaryTable" implementation.
# creates tmp table once per session
__PACKAGE__->setup_view($sql, cache_for_session => 1);
TIPS AND TRAPS
You know Class::DBI's "retrieve" method wants value for primary key.
What if your view doesn't have primary column? Quick solution would be
making primary column by combining some columns like:
__PACKAGE__->columns(All => qw(id acc_id orgname sub_id productname));
__PACKAGE__->setup_view( <<SQL );
SELECT CONCAT(a.acc_id, '.', a.subs_id) AS id,
a.acc_id, a.orgname,
s.sub_id, s.productname
FROM accounts a, subscriptions s
WHERE a.acc_id = s.acc_id
SQL
NOTES
* Currently update/delete/insert-related methods (like "create") are
not supported. Supporting it would make things too complicated IMHO.
So only SELECT-related methods ("search" etc.) would be enough.
(Patches are welcome, off course)
AUTHOR
Tatsuhiko Miyagawa <miyagawa@bulknews.net> with feedbacks from:
Dominic Mitchell E<lt>dom@semantico.comE<gt>
Tim Bunce E<lt>Tim.bunce@pobox.comE<gt>
This library is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
SEE ALSO
the Class::DBI manpage