Class-DBI-Sweet
view release on metacpan or search on metacpan
- I mean, v3.0.12 ;-)
- Also, /ge
0.07 Mon Dec 12 10:37:00 2005
- Updated to work properly with Class::DBI v3.1.2
0.06
- Moved UUID check to only die if user has selected uuid as their
sequence
- Provide a way to use named custom SQL fragments and which
bits of SQL are replaced in the "%s" substitutions. Useful
for aggregate and GROUP BY queries. New "attributes" added:
sql_method : select the sql fragment used for COUNT(*)/SELECT
statement_order : define which sql chunks and order are used
in the "%s" substitution string.
0.05
- No longer require Data::UUID/Win32API::GUID in Build.PL.
- Multi-step joins added for search
0.04 Wed Jul 6 04:10:00 2005
- Test fixes (Andrew Taylor)
- Bugfix to prefetch (Chia-liang Kao)
(which it normally only uses as a fallback mechanism). Useful for
testing or for causing the entire query to be retrieved initially
when the resultset cache is used.
This is also useful when using custom SQL via "set_sql" and setting
"sql_method" (see below) where a COUNT(*) may not make sense (i.e.
when the COUNT(*) might be as expensive as just running the full
query and just slicing the iterator).
sql_method
This sets the name of the sql fragment to use as previously set by a
"set_sql" call. The default name is "Join_Retrieve" and the
associated default sql fragment set in this class is:
__PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
SELECT __ESSENTIAL(me)__%s
FROM %s
WHERE %s
SQL
You may override this in your table or base class using the same
name and CDBI::Sweet will use your custom fragment, instead.
If you need to use more than one sql fragment in a given class you
may create a new sql fragment and then specify its name using the
"sql_method" attribute.
The %s strings are replaced by sql parts as described in Ima::DBI.
See "statement_order" for the sql part that replaces each instance
of %s.
In addition, the associated statment for COUNT(*) statement has
"_Count" appended to the sql_method name. Only "from" and "where"
are passed to the sprintf function.
The default sql fragment used for "Join_Retrieve" is:
__PACKAGE__->set_sql( Join_Retrieve_Count => <<'SQL' );
SELECT COUNT(*)
FROM %s
WHERE %s
SQL
If you create a custom sql method (and set the "sql_method"
attribute) then you will likely need to also create an associated
_Count fragment. If you do not have an associated _Count, and wish
to call the "page" method, then set "disable_sql_paging" to true and
your result set from the select will be spliced to return the page
you request.
Here's an example.
Assume a CD has_a Artist (and thus Artists have_many CDs), and you
wish to return a list of artists and how many CDs each have:
In package MyDB::Artist
sql_method => 'count_by_cd',
statement_order => [qw/ from where limit order_by / ],
disable_sql_paging => 1,
order_by => 'cd_count desc',
rows => 10,
page => 1,
} );
statement_order
Specifies a list reference of SQL parts that are replaced in the SQL
fragment (which is defined with "sql_method" above). The available
SQL parts are:
prefetch_cols from where order_by limit sql prefetch_names
The "sql" part is shortcut notation for these three combined:
where order_by limit
Prefecch_cols are the columns selected when a prefetch is speccified
-- use in the SELECT. Prefetch_names are just the column names for
lib/Class/DBI/Sweet.pm view on Meta::CPAN
uses as a fallback mechanism). Useful for testing or for causing the entire
query to be retrieved initially when the resultset cache is used.
This is also useful when using custom SQL via C<set_sql> and setting
C<sql_method> (see below) where a COUNT(*) may not make sense (i.e. when
the COUNT(*) might be as expensive as just running the full query and just slicing
the iterator).
=item sql_method
This sets the name of the sql fragment to use as previously set by a
C<set_sql> call. The default name is "Join_Retrieve" and the associated
default sql fragment set in this class is:
__PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
SELECT __ESSENTIAL(me)__%s
FROM %s
WHERE %s
SQL
You may override this in your table or base class using the same name and CDBI::Sweet
will use your custom fragment, instead.
If you need to use more than one sql fragment in a given class you may create a new
sql fragment and then specify its name using the C<sql_method> attribute.
The %s strings are replaced by sql parts as described in L<Ima::DBI>. See
"statement_order" for the sql part that replaces each instance of %s.
In addition, the associated statment for COUNT(*) statement has "_Count"
appended to the sql_method name. Only "from" and "where" are passed to the sprintf
function.
The default sql fragment used for "Join_Retrieve" is:
__PACKAGE__->set_sql( Join_Retrieve_Count => <<'SQL' );
SELECT COUNT(*)
FROM %s
WHERE %s
SQL
If you create a custom sql method (and set the C<sql_method> attribute) then
you will likely need to also create an associated _Count fragment. If you do
not have an associated _Count, and wish to call the C<page> method, then set
C<disable_sql_paging> to true and your result set from the select will be spliced
to return the page you request.
Here's an example.
Assume a CD has_a Artist (and thus Artists have_many CDs), and you wish to
return a list of artists and how many CDs each have:
In package MyDB::Artist
lib/Class/DBI/Sweet.pm view on Meta::CPAN
statement_order => [qw/ from where limit order_by / ],
disable_sql_paging => 1,
order_by => 'cd_count desc',
rows => 10,
page => 1,
} );
=item statement_order
Specifies a list reference of SQL parts that are replaced in the SQL fragment (which
is defined with "sql_method" above). The available SQL parts are:
prefetch_cols from where order_by limit sql prefetch_names
The "sql" part is shortcut notation for these three combined:
where order_by limit
Prefecch_cols are the columns selected when a prefetch is speccified -- use in the SELECT.
Prefetch_names are just the column names for use in GROUP BY.
t/cdbi-t-ocache/10-mysql.t view on Meta::CPAN
is @all, 3, "3 films";
ok $all[2]->title gt $all[1]->title && $all[1]->title gt $all[0]->title,
"sorted by title";
}
{
package Class::DBI::Search::Test::Limited;
use base 'Class::DBI::Search::Basic';
sub fragment {
my $self = shift;
my $frag = $self->SUPER::fragment;
if (defined(my $limit = $self->opt('limit'))) {
$frag .= " LIMIT $limit";
}
return $frag;
}
package main;
MyFilm->add_searcher(search => "Class::DBI::Search::Test::Limited");
my @common = map MyFilm->insert({ title => "Common Title" }), 1 .. 3;
{
my @ltd = MyFilm->search(
title => "Common Title",
t/cdbi-t-rescache/10-mysql.t view on Meta::CPAN
is @all, 3, "3 films";
ok $all[2]->title gt $all[1]->title && $all[1]->title gt $all[0]->title,
"sorted by title";
}
{
package Class::DBI::Search::Test::Limited;
use base 'Class::DBI::Search::Basic';
sub fragment {
my $self = shift;
my $frag = $self->SUPER::fragment;
if (defined(my $limit = $self->opt('limit'))) {
$frag .= " LIMIT $limit";
}
return $frag;
}
package main;
MyFilm->add_searcher(search => "Class::DBI::Search::Test::Limited");
my @common = map MyFilm->insert({ title => "Common Title" }), 1 .. 3;
{
my @ltd = MyFilm->search(
title => "Common Title",
t/cdbi-t/10-mysql.t view on Meta::CPAN
is @all, 3, "3 films";
ok $all[2]->title gt $all[1]->title && $all[1]->title gt $all[0]->title,
"sorted by title";
}
{
package Class::DBI::Search::Test::Limited;
use base 'Class::DBI::Search::Basic';
sub fragment {
my $self = shift;
my $frag = $self->SUPER::fragment;
if (defined(my $limit = $self->opt('limit'))) {
$frag .= " LIMIT $limit";
}
return $frag;
}
package main;
MyFilm->add_searcher(search => "Class::DBI::Search::Test::Limited");
my @common = map MyFilm->insert({ title => "Common Title" }), 1 .. 3;
{
my @ltd = MyFilm->search(
title => "Common Title",
( run in 1.786 second using v1.01-cache-2.11-cpan-364913b4093 )