Alzabo
view release on metacpan or search on metacpan
- Added support for HAVING in queries.
BUG FIXES:
- Exceptions did not include a stack trace.
- Trying to create a Postgres schema with foreign keys defined caused
an exception. Reported by Josh Jore.
- Fetching rows from the cursor for a join with multiple outer joins
could fail if data was being prefetched.
DEPRECATIONS:
- Alzabo::DriverStatement->next_hash method has been renamed
next_as_hash.
---------------------------------------------------------------------------
0.70 November 21, 2002
- Added Alzabo::Runtime::*Row->is_live method to easily distinguish
between real and potential rows.
- Did some profiling of data retrieval (Alzabo::Runtime bits) and
optimized some of the most heavily used pieces of Alzabo.
- Added the Alzabo::Runtime::Schema->prefetch_all and
Alzabo::Runtime::Schema->prefetch_all_but_blobs convenience methods.
- Added a ->count method to the cursor classes.
- Added ->is_integer, ->is_floating_point, ->is_date, ->is_datetime,
->is_time, and ->generic_type methods to column objects.
- The Alzabo::Driver->schemas method now takes connection parameters.
See your specific driver subclass for details on which. Bug report
by Ilya Martynov.
- Added Alzabo::Runtime::Schema->disconnect method. Patch by Ilya
Martynov.
- Fixed buggy handling of joins that had a table with a multi-column
primary key as the "distinct" parameter.
- Calling the Alzabo::Runtime::Schema->join method with no 'select'
parameter and a 'join' parameter that was an array reference of
array references would fail.
- Avoid an uninit value in Alzabo::MethodMaker. Reported by Daniel
Gaspani.
- If you created a cursor inside an eval{} block, the cursor contained
an object whose DESTROY method would overwrite $@ as it went out of
scope when the eval block exited. This could basically make it look
like an exception had disappeared. Thanks to Brad Bowman for an
excellent bug report.
- Loading a schema failed in taint mode. This was reported ages ago
by Raul Nohea Goodness and dropped on the floor by me. My bad.
- The schema creator's exception handling was a little bit buggered up
when handling Alzabo::Exception::Driver exceptions.
- Added potentially useful script, alzabo_to_ascii, in eg/ dir.
- Ask for port when setting up tests.
- Turn on stacktraces for all Alzabo::Exception objects.
- Removed the deprecated "lookup_tables" option from
Alzabo::MethodMaker.
- Removed the deprecated next_row methods from the various cursor
classes.
- Removed the deprecated Alzabo::Runtime::Table->func method.
- Major changes to how joins are done. It is now possible to mix
together various sorts of outer joins in a single query. In
addition, it is now possible to specify a foreign key that should be
used when joining two tables.
- The "tables" parameter has been renamed as "join".
- Document that Alzabo supports COALESCE and NULLIF for Postgres.
- Added Alzabo::ObjectCache::Sync::Mmap which uses Cache::Mmap. This
is just slightly slower than using SDBM_File.
- New table alias feature for making queries that join against a table
more than once. An example:
my $foo_alias = $foo_tab->alias;
my $cursor = $schema->join( select => $foo_tab,
tables => [ $foo_tab, $bar_tab, $foo_alias ],
where => [ [ $bar_tab->column('baz'), '=', 10 ],
[ $foo_alias->column('quux'), '=', 100 ] ],
order_by => $foo_alias->column('briz') );
In this query, we want to get all the entries in the foo table based
on a join between foo and bar with certain conditions. However, we
want to order the results by a _different_ criteria than that used
for the join. This doesn't necessarily happen often, but when it
does its nice to be able to do it. In SQL, this query would look
0.61 Dec 25, 2001
ENHANCEMENTS:
- Improve documentation for new Alzabo::Create::Schema->sync_backend
method and note its caveats.
- It is now possible to use SQL functions as part of order_by clauses.
For example:
my $cursor = $schema->select( select => [ COUNT('*'), $id_col ],
tables => [ $foo_tab, $bar_tab ],
group_by => $id_col,
order_by => [ COUNT('*'), 'DESC' ] );
- Allow a call to Alzabo::Runtime::Table->insert without a values
parameter. This is potentially useful for tables where the primary
key is sequenced and the other columns have defaults or are
NULLable. Patch by Ilya Martynov.
BUG FIXES:
order_by => [ $col1, $col2, 'DESC', $col3, 'ASC' ]
which allow for multiple levels of sorting as well as being much
simpler to remember.
- It is now possible to do something like
$table->select( select => [ 1, $column ] ... );
and have it work. In this case, every row returned by the cursor
will have 1 as its first element.
- Added Alzabo::MySQL and Alzabo::PostgreSQL POD pages. These pages
document how Alzabo does (or does not) support various RDBMS
specific features.
- Remove Alzabo::Util. Use Class::Factory::Util from CPAN instead.
Class::Factory::Util is a slight revision of Alzabo::Util that has
been separated from the Alzabo core code by Terrence Brannon.
Thanks Terrence.
- Got rid of the post_select_hash hook and combined it with
post_select, which now receives a hash reference. Suggested by Ilya
Martynov.
- Run all hooks inside Alzabo::Schema->run_in_transaction method to
ensure database integrity in cases where your hooks might
update/delete/insert data. Suggested by Ilya Martynov.
- Added new Alzabo::Runtime::Table->select method. This is just like
the existing ->function method, but returns a cursor instead of the
entire result set.
- Added a 'limit' parameter to the ->function method (also works for
the ->select method).
- Added new Alzabo::Runtime::Schema->select method. This is like the
method of the same name in the table class but it allows for joins.
- Added new potential rows, which are objects with (largely) the same
interface as regular rows, but which are not (yet) inserted into the
- Calls to select_hash on cached rows were not going through the cache
checking routines, possibly returning expired data. Added tests for
this.
- Eliminate race condition in Alzabo::ObjectCache::Sync::BerkeleyDB.
- The Alzabo::Runtime::Row->rows_by_foreign_key method wasn't doing
quite what it said. In cases where there was a 1..1 or n..1
relationship to columns that were not the table's primary key, a
cursor would be returned instead of a single row. Reported by Ilya
Martynov.
- Alzabo::MethoMaker could generate 'subroutine foo redefined'
warnings . Reported by Ilya Martynov.
- Fixed clear method for all Alzabo::ObjectCache::Store::* modules.
DEPRECATIONS:
- The insert and update options for Alzabo::MethodMaker have been
ENHANCEMENTS:
- Document order by clauses for joins.
- Document limit clauses for joins and single table selects.
- Expand options for where clauses to allow 'OR' conditionals as well
as subgroupings of conditional clauses.
- If you set prefetch columns for a table, these are now fetched along
with other data for the table in a cursor, reducing the number of
database SELECTs being done.
- Added Alzabo::Create::Schema->clone method. This allows you to
clone a schema object (except for the name, which must be changed as
part of the cloning process).
- Using the profiler, I have improved some of the hot spots in the
code. I am not sure how noticeable these improvements are but I
plan to do a lot more of this type of work in the future.
be released soon so I won't have to think about this). Otherwise,
the support is about at the same level as MySQL support, though less
mature.
- Added Alzabo::MethodMaker module. This can be used to auto-generate
useful methods for your schema/table/row objects based on the
properties of your objects themselves.
- Reworking/expanding/clarifying/editing of the docs.
- Add order_by and limit options whenever creating a cursor.
- Method documentation POD from the Alzabo::* modules is merged into
the relevant Alzabo::Create::* and Alzabo::Runtime::* modules during
install. This should make it easier to find what you need since the
average user will only need to look at a few modules in
Alzabo::Runtime::*.
- Reworked exceptions so they are all now
Alzabo::Exception::Something.
---------------------------------------------------------------------------
0.10
**FIRST BETA VERSION**
- Doc bug fixes in Alzabo::Runtime::Schema.
- Fix fact that Alzabo::Runtime::Row rows_by_foreign_key method could
return either a Row _or_ RowCursor object. Now it always returns a
cursor object.
- Fix fact that no_cache parameter was not propagated through the
RowCursor object to the rows it created.
- Add all all_rows method to Alzabo::Runtime::RowCursor.
- Add ability to reset instantiation flag in schema creation
interface.
- Updated INSTALL to mention how to get the schema creator and data
of the referential integrity code work correctly 100% of the time.
- Added new class Alzabo::ObjectCache to make sure that objects stay
in sync after referential integrity operations happen. This is now
the default caching class. Please make sure to read the docs for
this new module, particularly if you're running Alzabo under a
persistent environment where this module can be quite the memory hog
if not used properly (clear the cache!).
- Fixed breakage in maintenance of referential integrity caused by
switch to cursors (and me not fixing all the code that expected row
objects).
- Added Alzabo::Runtime::Cursor base class.
- Added join method to Alzabo::Runtime::Schema. *EXPERIMENTAL*
- Added Alzabo::Runtime::JoinCursor class. *EXPERIMENTAL*
- Began conversion of all classes from pseudohash to hash.
lib/Alzabo.pm view on Meta::CPAN
L<The Alzabo::Runtime::Table docs|Alzabo::Runtime::Table> - This
contains most of the methods used to fetch rows from the database, as
well as the L<C<insert()>|Alzabo::Runtime::Table/insert> method.
L<The Alzabo::Runtime::Row docs|Alzabo::Runtime::Row> - The row
objects contain the methods used to update, delete, and retrieve data
from the database.
L<The Alzabo::Runtime::RowCursor docs|Alzabo::Runtime::RowCursor> - A
cursor object that returns only a single row.
L<The Alzabo::Runtime::JoinCursor docs|Alzabo::Runtime::JoinCursor> -
A cursor object that returns multiple rows at once.
L<The Alzabo::MethodMaker docs|Alzabo::MethodMaker> - One of the most
useful parts of Alzabo. This module can be used to auto-generate
methods based on the structure of your schema.
L<The Alzabo::Runtime::UniqueRowCache
docs|Alzabo::Runtime::UniqueRowCache> - This describes the simple
caching system included with Alzabo.
L<The Alzabo::Debug docs|Alzabo::Debug> - How to turn on various kinds
lib/Alzabo/Design.pod view on Meta::CPAN
L<C<Alzabo::Runtime::JoinCursor>|Alzabo::Runtime::JoinCursor> objects.
It is the sole interface by which actual data is retrieved, updated,
or deleted in a table.
The various C<RowState> classes are used in order to change a row's
behavior depending on whether it is live, live and cached, potential,
or deleted.
=item * C<Alzabo::Runtime::JoinCursor> and C<Alzabo::Runtime::RowCursor>
These objects are cursor that returns row objects. Using a cursor
saves a lot of memory for big selects.
=item * C<Alzabo::Runtime::UniqueRowCache>
Loading this class turns on Alzabo's simple row caching mechanism.
=item * C<Alzabo::Config>
This class is generated by Makefile.PL during installation and
contains information such as what directory contains saved schemas and
lib/Alzabo/Design.pod view on Meta::CPAN
write an application using that schema. At the simplest level, they
would only need to learn how to instantiate C<Alzabo::Runtime::Row>
objects and how that class's methods work. For more sophisticated
users, they can still avoid having to ever look at documentation on
methods that alter the schema and its contained objects.
=back
=head1 RATIONALE FOR CURSORS
Using cursors is definitely more complicated. However, there are two
excellent reasons for using them: speed and memory savings. As an
example, I did a test with the old code (which returned all its
objects at once) against a table with about 8,000 rows using the
L<C<Alzabo::Runtime::Table-E<gt>all_rows>
method|Alzabo::Runtime::Table/all_rows>. Under the old
implementation, it took significantly longer to return the first row.
Even more importantly than that, the old implementation used up about
10MB of memory versus about 4MB! Now imagine that with a 1,000,000
row table.
Thus Alzabo uses cursors so it can scale better. This is a
particularly big win in the case where you are working through a long
list of rows and may stop before the end is reached. With cursors,
Alzabo creates only as many rows as you need. Plus the start up time
on your loop is much, much quicker. In the end, your program is
quicker and less of a memory hog. This is good.
=head1 AUTHOR
Dave Rolsky, <autarch@urth.org>
=cut
lib/Alzabo/Driver.pm view on Meta::CPAN
For backwards compatibility, this is also available as C<next_hash()>.
Returns a hash containing the next row of data for statement or an
empty list if no more data is available. All the keys of the hash
will be lowercased.
Throws: L<C<Alzabo::Exception::Driver>|Alzabo::Exceptions>
=head2 all_rows
If the select for which this statement is cursor was for a single
column (or aggregate value), then this method returns an array
containing each B<remaining> value from the database.
Otherwise, it returns an array of array references, each one
containing a returned row from the database.
Throws: L<C<Alzabo::Exception::Driver>|Alzabo::Exceptions>
=head2 all_rows_hash
lib/Alzabo/Intro.pod view on Meta::CPAN
location varchar(200) -- 'New York City' or 'USA'
parent_location_id tinyint -- foreign key to location
=head2 Fetching data
In Alzabo, data is returned in the form of a L<row
object|Alzabo::Runtime::Row>. This object can be used to access the
data for an individual row.
Unless you are retrieving a row via a unique identifier (usually its
primary key), you will be given a L<cursor|Alzabo::Runtime::RowCursor>
object. This is quite similar to how C<DBI> uses statement handles
and is done for similar reasons.
First of all, let's do something simple. Let's assume I have a
person_id value and I want to find all the movies that they were in
and print the title, year of release, and the job they did in the
movie. Here's what it looks like:
my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );
my $person_t = $schema->table('Person');
my $credit_t = $schema->table('Credit');
my $movie_t = $schema->table('Movie');
my $job_t = $schema->table('Job');
# returns a row representing this person.
my $person = $person_t->row_by_pk( pk => 42 );
# all the rows in the credit table that have the person_id of 42.
my $cursor =
$person->rows_by_foreign_key
( foreign_key =>
$person_t->foreign_keys_by_table($credit_t) );
print $person->select('name'), " was in the following films:\n\n";
while (my $credit = $cursor->next)
{
# rows_by_foreign_key returns a RowCursor object. We immediately
# call its next method, knowing it will only have one row (if
# it doesn't then our referential integrity is in trouble!)
my $movie =
$credit->rows_by_foreign_key
( foreign_key =>
$credit_t->foreign_keys_by_table($movie_t) )->next;
my $job =
lib/Alzabo/Intro.pod view on Meta::CPAN
use Alzabo::MethodMaker( schema => 'movies',
all => 1,
name_maker => \&method_namer );
my $schema = Alzabo::Runtime::Schema->load_from_file( name => 'movies' );
# instantiates a row representing this person.
my $person = $schema->Person->row_by_pk( pk => 42 );
# all the rows in the credit table that have the person_id of 42.
my $cursor = $person->Credits;
print $person->name, " was in the following films:\n\n";
while (my $credit = $cursor->next)
{
my $movie = $credit->Movie;
my $job = $credit->Job;
print $movie->title, " released in ", $movie->release_year, "\n";
print ' ', $job->job, "\n";
}
=head2 Updating data
lib/Alzabo/Intro.pod view on Meta::CPAN
use Alzabo::SQLMaker::MySQL qw(MAX NOW PI);
my $max =
$table->function( select => MAX( $table->column('budget') ),
where => [ $table->column('country'), '=', 'USA' ] );
$table->insert( values => { create_date => NOW() } );
$row->update( pi => PI() );
my $cursor =
$table->rows_where( where =>
[ $table->column('expire_date'), '<=', NOW() ] );
my $cursor =
$table->rows_where( where =>
[ LENGTH( $table->column('password') ), '<=', 5 ] );
The documentation for the Alzabo::SQLMaker subclass for your RDBMS
will contain a detailed list of all exportable functions.
=head2 Row Objects Not in the Database
Sometimes you'll want to create an object with the row object API, but
which does not represent a row in the database. See the
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
$self->make_lookup_columns_methods($fk)
if $self->{opts}{lookup_columns};
return unless $self->{opts}{foreign_keys};
if ($fk->is_one_to_many)
{
my $name = $self->_make_method
( type => 'foreign_key',
class => $self->{row_class},
returns => 'row cursor',
code => sub { my $self = shift;
return $self->rows_by_foreign_key( foreign_key => $fk, @_ ); },
foreign_key => $fk,
plural => 1,
) or return;
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
group => 'Methods that return cursors for foreign keys',
description =>
"returns a cursor containing related rows from the " . $fk->table_to->name . " table",
spec => 'same as Alzabo::Runtime::Table->rows_where',
) );
}
# Singular method name
else
{
my $name = $self->_make_method
( type => 'foreign_key',
class => $self->{row_class},
returns => 'single row',
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
group => 'Methods that return a parent row',
description =>
"a single parent row from the same table",
spec => 'same as Alzabo::Runtime::Table->one_row',
) );
}
$name = $self->_make_method
( type => 'self_relation',
class => $self->{row_class},
returns => 'row cursor',
code =>
sub { my $self = shift;
my %p = @_;
my @where = map { [ $_->[0], '=', $self->select( $_->[1] ) ] } @reverse_pairs;
if ( $p{where} )
{
@where = ( '(', @where, ')' );
push @where,
Alzabo::Utils::is_arrayref( $p{where}->[0] ) ? @{ $p{where} } : $p{where};
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
%p ); },
foreign_key => $fk,
parent => 0,
) or return;
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
group => 'Methods that return child rows',
description =>
"a row cursor of child rows from the same table",
spec => 'same as Alzabo::Runtime::Table->rows_where',
) );
}
sub make_linking_table_method
{
my $self = shift;
my $fk = shift;
return unless $fk->table_to->primary_key_size == 2;
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
return unless ( $fk->table_to->primary_key_size ==
( $fk->table_from->primary_key_size + $fk_2->table_to->primary_key_size ) );
my $s = $fk->table_to->schema;
my @t = ( $fk->table_to, $fk_2->table_to );
my $select = [ $t[1] ];
my $name = $self->_make_method
( type => 'linking_table',
class => $self->{row_class},
returns => 'row cursor',
code =>
sub { my $self = shift;
my %p = @_;
if ( $p{where} )
{
$p{where} = [ $p{where} ] unless Alzabo::Utils::is_arrayref( $p{where}[0] );
}
foreach my $pair ( $fk->column_pairs )
{
push @{ $p{where} }, [ $pair->[1], '=', $self->select( $pair->[0]->name ) ];
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
%p ); },
foreign_key => $fk,
foreign_key_2 => $fk_2,
) or return;
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
group => 'Methods that follow a linking table',
description =>
"a row cursor of related rows from the " . $fk_2->table_to->name . " table, " .
"via the " . $fk->table_to->name . " linking table",
spec => 'same as Alzabo::Runtime::Table->rows_where',
) );
}
sub make_lookup_columns_methods
{
my $self = shift;
my $fk = shift;
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
For exa
Movie Credit
--------- --------
movie_id movie_id
title person_id
role_name
This would create a method for Movie row objects called C<Credit()>
which would return a cursor for the associated Credit table rows.
Similarly, Credit row objects would have a method called C<Movie()>
which would return the associated Movie row object.
=item * linking_tables => $bool
A linking table, as defined here, is a table with a two column primary
key, with each column being a foreign key to another table's primary
key. These tables exist to facilitate n..n logical relationships. If
both C<foreign_keys> and C<linking_tables> are true, then methods will
be created that skip the intermediate linking tables.
For example, with the following tables:
User UserGroup Group
------- --------- --------
user_id user_id group_id
user_name group_id group_name
The "UserGroup" table exists solely to facilitate the n..n
relationship between "User" and "Group". User row objects will have a
C<Group()> method, which returns a row cursor of Group row objects.
And Group row objects will have a C<User()> method which returns a row
cursor of User row objects.
=item * lookup_columns => $bool
Lookup columns are columns in foreign tables to which a table has a
many-to-one or one-to-one relationship to the foreign table's primary
key. For example, given the tables below:
Restaurant Cuisine
--------- --------
restaurant_id cuisine_id
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
location_id
location_name
parent_location_id
NOTE: If the relationship has a cardinality of 1..1 then no methods
will be created, as this option is really intended for parent/child
relationships. This may change in the future.
In this case, Location row objects will have both C<parent()> and
C<children()> methods. The parent method returns a single row, while
the C<children()> method returns a row cursor of Location rows.
=back
=head1 HOOKS
As was mentioned previously, it is possible to create pre- and
post-execution hooks to wrap around a number of methods. This allows
you to do data validation on inserts and updates as well as giving you
a chance to filter incoming or outgoing data as needed. For example,
this can be used to convert dates to and from a specific RDBMS
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
for such relationships, so your naming sub may need to take this into
account.
When the type is "foreign_key":
=over 4
=item * plural => $bool
This indicates whether or not the method that is being created will
return a cursor object (true) or a row object (false).
=back
When the type is "linking_table":
=over 4
=item * foreign_key_2 => Alzabo::ForeignKey object
When making a linking table method, two foreign keys are used. The
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
return $p{column}->name . '_c' if $p{type} eq 'table_column';
# If I have a row object, I can get at the columns via their
# names, for example $user->username;
return $p{column}->name if $p{type} eq 'row_column';
# This manipulates the table names a bit to generate names. For
# example, if I have a table called UserRating and a 1..n
# relationship from User to UserRating, I'll end up with a method
# on rows in the User table called ->Ratings which returns a row
# cursor of rows from the UserRating table.
if ( $p{type} eq 'foreign_key' )
{
my $name = $p{foreign_key}->table_to->name;
my $from = $p{foreign_key}->table_from->name;
$name =~ s/$from//;
if ($p{plural})
{
return my_PL( $name );
}
else
{
return $name;
}
}
# This is very similar to how foreign keys are handled. Assume
# we have the tables Restaurant, Cuisine, and RestaurantCuisine.
# If we are generating a method for the link from Restaurant
# through to Cuisine, we'll have a method on Restaurant table
# rows called ->Cuisines, which will return a cursor of rows from
# the Cuisine table.
#
# Note: this will generate a bad name if given a linking table
# that links a table to itself.
if ( $p{type} eq 'linking_table' )
{
my $method = $p{foreign_key}->table_to->name;
my $tname = $p{foreign_key}->table_from->name;
$method =~ s/$tname//;
lib/Alzabo/QuickRef.pod view on Meta::CPAN
All methods that return multiple rows return an
L<C<Alzabo::Runtime::RowCursor>|Alzabo::Runtime::RowCursor> object.
All methods that return rows can be given the C<no_cache> parameter,
which ensures that the row(s) returned will not be cached. Rows
obtained in this manner should not be updated or deleted, as this will
play havoc with the caching system. See the
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row> documentation for more
details.
All methods that return multiple rows in the form of a cursor object
can take an C<order_by> parameter. See the
L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table> documentation for
more details.
=over 4
=item * insert
=for html_docs type=object
lib/Alzabo/QuickRef.pod view on Meta::CPAN
value of the relevant column(s) in the current row.
This method can also take a C<no_cache> and/or C<order_by> parameter.
=for html_docs link=L<More|Alzabo::Runtime::Row/rows_by_foreign_key>
=back
=head3 Alzabo::Runtime::RowCursor
Objects in this class are used to return multiple rows as a cursor,
rather than as a list. This is much more efficient, at the expense of
a few extra lines in your code.
=over 4
=item * next
=for html_docs type=object
Returns the next L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row>
lib/Alzabo/Runtime/Cursor.pm view on Meta::CPAN
my @next = $self->next or return;
return map { defined $_ ? ( $_->table->name => $_ ) : () } @next;
}
__END__
=head1 NAME
Alzabo::Runtime::Cursor - Base class for Alzabo cursors
=head1 SYNOPSIS
use Alzabo::Runtime::Cursor;
=head1 DESCRIPTION
This is the base class for cursors.
=head1 METHODS
=head2 new
Virtual method.
=head2 all_rows
Virtual method.
=head2 reset
Resets the cursor so that the next C<next> call will return the first
row of the set.
=head2 count
Returns the number of rows returned by the cursor so far.
=head2 next_as_hash
Returns the next row or rows in a hash, where the hash key is the
table name and the hash value is the row object.
=head1 AUTHOR
Dave Rolsky, <autarch@urth.org>
lib/Alzabo/Runtime/ForeignKey.pm view on Meta::CPAN
my $self = shift;
my $row = shift;
my @update = grep { $_->nullable } $self->columns_to;
return unless $self->to_is_dependent || @update;
# Find the rows in the other table that are related to the row
# being deleted.
my @where = map { [ $_->[1], '=', $row->select( $_->[0]->name ) ] } $self->column_pairs;
my $cursor = $self->table_to->rows_where( where => \@where );
while ( my $related_row = $cursor->next )
{
# This is a class variable so that multiple foreign key
# objects don't try to delete the same rows
next if $DELETED{ $related_row->id_as_string };
if ($self->to_is_dependent)
{
local %DELETED = %DELETED;
$DELETED{ $related_row->id_as_string } = 1;
# dependent relationship so delete other row (may begin a
lib/Alzabo/Runtime/JoinCursor.pm view on Meta::CPAN
__END__
=head1 NAME
Alzabo::Runtime::JoinCursor - Cursor that returns arrays of C<Alzabo::Runtime::Row> objects
=head1 SYNOPSIS
use Alzabo::Runtime::JoinCursor;
my $cursor = $schema->join( tables => [ $foo, $bar ],
where => [ $foo->column('foo_id'), '=', 1 ] );
while ( my @rows = $cursor->next )
{
print $rows[0]->select('foo'), "\n";
print $rows[1]->select('bar'), "\n";
}
=head1 DESCRIPTION
Objects in this class are used to return arrays of
Alzabo::Runtime::Row objects when requested. The cursor does not
preload objects but rather creates them on demand, which is much more
efficient. For more details on the rational please see L<the
RATIONALE FOR CURSORS section in
Alzabo::Design|Alzabo::Design/RATIONALE FOR CURSORS>.
=head1 INHERITS FROM
L<C<Alzabo::Runtime::Cursor>|Alzabo::Runtime::Cursor>
=head1 METHODS
lib/Alzabo/Runtime/JoinCursor.pm view on Meta::CPAN
onwards. This means that if there are five set of rows that will be
returned when the object is created and you call C<next()> twice,
calling C<all_rows()> after it will only return three sets.
The return value is an array of array references. Each of these
references represents a single set of rows as they would be returned
from the C<next> method.
=head2 reset
Resets the cursor so that the next L<C<next()>|next> call will return
the first row of the set.
=head2 count
Returns the number of rowsets returned by the cursor so far.
=head2 next_as_hash
Returns the next rows in a hash, where the hash keys are the table
names and the hash values are the row object. If a table has been
included in the join via an outer join, then it is only included in
the hash if there is a row for that table.
=head1 AUTHOR
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
$row->delete;
=head1 DESCRIPTION
These objects represent actual rows from the database containing
actual data. In general, you will want to use the
L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table> object to retrieve
rows. The L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table> object
can return either single rows or L<row
cursors|Alzabo::Runtime::RowCursor>.
=head1 ROW STATES
Row objects can have a variety of states. Most row objects are
"live", which means they represent an actual row object. A row can be
changed to the "deleted" state by calling its C<delete()> method.
This is a row that no longer exists in the database. Most method
calls on rows in this state cause an exception.
There is also a "potential" state, for objects which do not represent
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
It takes the following parameters:
=over 4
=item * foreign_key => C<Alzabo::Runtime::ForeignKey> object
=back
Given a foreign key object, this method returns either a row object or
a row cursor object the row(s) in the table to which the relationship
exist.
The type of object returned is based on the cardinality of the
relationship. If the relationship says that there could only be one
matching row, then a row object is returned, otherwise it returns a
cursor.
=head1 POTENTIAL ROWS
The "potential" row state is used for rows which do not yet exist in
the database. These are created via the L<C<<
Alzabo::Runtime::Table->potential_row
>>|Alzabo::Runtime::Table/potential_row> method.
They are useful when you need a placeholder object which you can
update and select from, but you don't actually want to commit the data
lib/Alzabo/Runtime/RowCursor.pm view on Meta::CPAN
__END__
=head1 NAME
Alzabo::Runtime::RowCursor - Cursor that returns C<Alzabo::Runtime::Row> objects
=head1 SYNOPSIS
use Alzabo::Runtime::RowCursor;
my $cursor = $schema->table('foo')->all_rows;
while ( my $row = $cursor->next )
{
print $row->select('foo'), "\n";
}
=head1 DESCRIPTION
Objects in this class are used to return
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row> objects for queries.
The cursor does not preload objects but creates them on demand, which
is much more efficient. For more details on the rational please see
L<the RATIONALE FOR CURSORS section in
Alzabo::Design|Alzabo::Design/RATIONALE FOR
CURSORS>.
=head1 INHERITS FROM
L<C<Alzabo::Runtime::Cursor>|Alzabo::Runtime::Cursor>
=head1 METHODS
lib/Alzabo/Runtime/RowCursor.pm view on Meta::CPAN
=head2 all_rows
Returns all the rows available from the current point onwards. This
means that if there are five rows that will be returned when the
object is created and you call C<next> twice, calling all_rows
after it will only return three.
=head2 reset
Resets the cursor so that the next L<C<next>|next> call will
return the first row of the set.
=head2 count
Returns the number of rows returned by the cursor so far.
=head2 next_as_hash
Return the next row in a hash, where the hash key is the table name
and the hash value is the row object.
=head1 AUTHOR
Dave Rolsky, <autarch@urth.org>
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
These restrictions are only allowed when performing an outer join,
since there is no point in using them for regular inner joins. An
inner join restriction has the same effect when included in the
"WHERE" clause.
If the more multiple array reference of specifying tables is used and
no "select" parameter is provided, then the order of the rows returned
from calling L<C<< Alzabo::Runtime::JoinCursor->next()
>>|Alzabo::Runtime::JoinCursor/next> is not guaranteed. In other
words, the array that the cursor returns will contain a row from each
table involved in the join, but the which row belongs to which table
cannot be determined except by examining the objects. The order will
be the same every time L<C<< Alzabo::Runtime::JoinCursor->next()
>>|Alzabo::Runtime::JoinCursor/next> is called, however. It may be
easier to use the L<C<< Alzabo::Runtime::JoinCursor->next_as_hash()
>>|Alzabo::Runtime::JoinCursor/next_as_hash> method in this case.
=item * select => C<Alzabo::Runtime::Table> object or objects (optional)
This parameter specifies from which tables you would like rows
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
representing the results of the join. Otherwise, the method returns
a L<RowCursor|Alzabo::Runtime::RowCursor> object.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head2 one_row
This method takes the exact same parameters as the
L<C<join()>|Alzabo::Runtime::table/join> method but instead of
returning a cursor, it returns a single array of row objects. These
will be the rows representing the first row (a set of one or more
table's primary keys) that is returned by the database.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head2 function and select
These two methods differ only in their return values.
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
If you requested multiple functions such as "AVG(foo), MAX(foo)", then
it returns a single array reference, the first row of values, in
scalar context and a list of array references in list context.
=head3 select() return values
This method always returns a new
L<C<Alzabo::DriverStatement>|Alzabo::Driver/Alzabo::DriverStatement>
object containing the results of the query. This object has an
interface very similar to the Alzabo cursor interface, and has methods
such as C<next()>, C<next_as_hash()>, etc.
=head2 row_count
This method is simply a shortcut to get the result of COUNT('*') for a
join. It equivalent to calling C<function()> with a "select"
parameter of C<COUNT('*')>.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
The object returned from the table functions more or less exactly like
a table object. When using this table to set where clause or order by
(or any other) conditions, it is important that the column objects for
these conditions be retrieved from the alias object.
For example:
my $foo_alias = $foo->alias;
my $cursor = $schema->join( select => $foo,
join => [ $foo, $bar, $foo_alias ],
where => [ [ $bar->column('baz'), '=', 10 ],
[ $foo_alias->column('quux'), '=', 100 ] ],
order_by => $foo_alias->column('briz') );
If we were to use the C<$foo> object to retrieve the 'quux' and
'briz' columns then the join would simply not work as expected.
It is also possible to use multiple aliases of the same table in a
join, so that this will work properly:
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
my $self = shift;
my %p = @_;
my $sql = $self->_make_sql(%p);
Alzabo::Runtime::process_where_clause( $sql, $p{where} ) if exists $p{where};
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->_cursor_by_sql( %p, sql => $sql );
}
sub one_row
{
my $self = shift;
my %p = @_;
my $sql = $self->_make_sql(%p);
Alzabo::Runtime::process_where_clause( $sql, $p{where} ) if exists $p{where};
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
sub all_rows
{
my $self = shift;
my $sql = $self->_make_sql;
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->_cursor_by_sql( @_, sql => $sql );
}
sub _make_sql
{
my $self = shift;
my %p = @_;
logic_exception "Can't make rows for tables without a primary key"
unless $self->primary_key;
my $sql = ( Alzabo::Runtime::sqlmaker( $self->schema, \%p )->
select( $self->primary_key,
$self->prefetch ? $self->columns( $self->prefetch ) : () )->
from( $self ) );
return $sql;
}
sub _cursor_by_sql
{
my $self = shift;
my %p = @_;
validate( @_, { sql => { isa => 'Alzabo::SQLMaker' },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
( map { $_ => { optional => 1 } } keys %p ) } );
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
=head1 NAME
Alzabo::Runtime::Table - Table objects
=head1 SYNOPSIS
my $table = $schema->table('foo');
my $row = $table->row_by_pk( pk => 1 );
my $row_cursor =
$table->rows_where
( where =>
[ Alzabo::Column object, '=', 5 ] );
=head1 DESCRIPTION
This object is able to create rows, either by making objects based on
existing data or inserting new data to make new rows.
This object also implements a method of lazy column evaluation that
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
=head2 Methods that return an C<Alzabo::Runtime::RowCursor> object
The C<rows_where()> and C<all_rows()> methods both return an
L<C<Alzabo::Runtime::RowCursor>|Alzabo::Runtime::RowCursor> object
representing the results of the query. This is the case even for
queries that end up returning one or zero rows, because Alzabo cannot
know in advance how many rows these queries will return.
=head2 rows_where
This method provides a simple way to retrieve a row cursor based on
one or more colum values.
It takes the following parameters, all of which were described in the
L<Common Parameters|Alzabo::Runtime::Table/Common Parameters> section.
=over 4
=item * where
=item * order_by
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
L<C<Alzabo::Runtime::RowCursor>|Alzabo::Runtime::RowCursor> object
representing the query.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head2 one_row
This method takes the exact same parameters as the
L<C<rows_where()>|Alzabo::Runtime::table/rows_where> method but
instead of returning a cursor, it returns a single row. This row
represents the first row returned by the database.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head2 potential_row
This method is used to create a new
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row> object, in the
"potential" state.
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
If you requested multiple functions such as "AVG(foo), MAX(foo)", then
it returns a single array reference, the first row of values, in
scalar context and a list of array references in list context.
=head3 select() return values
This method always returns a new
L<C<Alzabo::DriverStatement>|Alzabo::Driver/Alzabo::DriverStatement>
object containing the results of the query. This object has an
interface very similar to the Alzabo cursor interface, and has methods
such as C<next()>, C<next_as_hash()>, etc.
=head2 alias
This returns an object which can be used in joins to allow a
particular table to be involved in the join under multiple aliases.
This allows for self-joins as well as more complex joins involving
multiple aliases to a given table.
The object returned by this method is more or less identical to a
lib/Alzabo/Runtime/UniqueRowCache.pm view on Meta::CPAN
=item * write_to_cache( $row_object )
Given a row object, this method stores it in the cache.
=back
=head1 AVOIDING THE CACHE
If you want to not cache a row, then you can pass the "no_cache"
parameter to any table or schema method that creates a new row object
or a cursor, such as C<< Alzabo::Runtime::Table->insert() >>, C<<
Alzabo::Runtime::Table->rows_where() >>.
=head1 AUTHOR
Dave Rolsky, <autarch@urth.org>
=cut
t/03-runtime.t view on Meta::CPAN
eval_ok( sub { $emp{2} = $emp_t->insert( values =>
{ name => 'unit 2',
smell => 'good',
dep_id => $dep{lying}->select('department_id') } ) },
"Create employee 'unit 2'" );
my $emp2_id = $emp{2}->select('employee_id');
delete $emp{2};
my $cursor;
my $x = 0;
eval_ok( sub { $cursor =
$emp_t->rows_where
( where => [ $emp_t->column('employee_id'), '=', $emp2_id ] );
while ( my $row = $cursor->next )
{
$x++;
$emp{2} = $row;
}
},
"Retrieve 'unit 2' employee via rows_where method and cursor" );
is( $x, 1,
"Check count of rows found where employee_id == $emp2_id" );
is( $cursor->count, 1,
"Make sure cursor's count() is accurate" );
is( $emp{2}->select('name'), 'unit 2',
"Check that row found has name of 'unit 2'" );
{
my $row;
eval_ok( sub { $row =
$emp_t->one_row
( where =>
[ $emp_t->column('employee_id'), '=', $emp2_id ] ) },
t/03-runtime.t view on Meta::CPAN
department_id => $dep{borg}->select('department_id') } );
$emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'),
project_id => $proj{extend}->select('project_id') } );
$emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'),
project_id => $proj{embrace}->select('project_id') } );
my $fk = $emp_t->foreign_keys_by_table($emp_proj_t);
my @emp_proj;
my @cursor_counts;
eval_ok( sub { $cursor = $emp{bill}->rows_by_foreign_key( foreign_key => $fk );
while ( my $row = $cursor->next )
{
push @emp_proj, $row;
push @cursor_counts, $cursor->count;
} },
"Fetch rows via ->rows_by_foreign_key method (expect cursor)" );
is( scalar @emp_proj, 2,
"Check that only two rows were returned" );
is( $emp_proj[0]->select('employee_id'), $emp{bill}->select('employee_id'),
"Check that employee_id in employee_project is same as bill's" );
is( $emp_proj[0]->select('project_id'), $proj{extend}->select('project_id'),
"Check that project_id in employee_project is same as extend project" );
foreach (1..2)
{
is( $cursor_counts[$_ - 1], $_,
"cursor->count should be 1..2" );
}
my $emp_proj = $emp_proj[0];
$fk = $emp_proj_t->foreign_keys_by_table($emp_t);
my $emp;
eval_ok( sub { $emp = $emp_proj->rows_by_foreign_key( foreign_key => $fk ) },
"Fetch rows via ->rows_by_foreign_key method (expect row)" );
is( $emp->select('employee_id'), $emp_proj->select('employee_id'),
"The returned row should have bill's employee_id" );
$x = 0;
my @rows;
eval_ok( sub { $cursor = $emp_t->all_rows;
$x++ while $cursor->next
},
"Fetch all rows from employee table" );
is( $x, 2,
"Only 2 rows should be found" );
$cursor->reset;
my $count = $cursor->all_rows;
is( $x, 2,
"Only 2 rows should be found after cursor reset" );
{
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where =>
[ $emp_t->column('employee_id'), '=',
$emp{bill}->select('employee_id') ],
order_by => $proj_t->column('project_id'),
quote_identifiers => 1,
) },
"Join employee, employee_project, and project tables where employee_id = bill's employee id with quote_identifiers" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
my $first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
my $second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id < $second_proj_id,
"Order by clause should cause project rows to come back" .
" in ascending order of project id" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where =>
[ [ $proj_t->column('project_id'), '=',
$proj{extend}->select('project_id') ],
'or',
[ $proj_t->column('project_id'), '=',
$proj{embrace}->select('project_id') ],
],
order_by => $proj_t->column('project_id') ) },
"Join employee, employee_project, and project tables with OR in where clause" );
1 while $cursor->next;
is( $cursor->count, 2,
"join with OR in where clause should return two sets of rows" );
}
# Alias code
{
my $e_alias;
eval_ok( sub { $e_alias = $emp_t->alias },
"Create an alias object for the employee table" );
my $p_alias;
eval_ok( sub { $p_alias = $proj_t->alias },
"Create an alias object for the project table" );
eval_ok( sub { $cursor =
$s->join( join => [ $e_alias, $emp_proj_t, $p_alias ],
where => [ $e_alias->column('employee_id'), '=', 1 ],
order_by => $p_alias->column('project_id'),
) },
"Join employee, employee_project, and project tables where" .
" employee_id = 1 using aliases" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
}
# Alias code & multiple joins to the same table
{
my $p_alias = $proj_t->alias;
eval_ok( sub { $cursor = $s->join( select => [ $p_alias, $proj_t ],
join => [ $p_alias, $emp_proj_t, $proj_t ],
where => [ [ $p_alias->column('project_id'), '=', 1 ],
[ $proj_t->column('project_id'), '=', 1 ] ],
) },
"Join employee_project and project table (twice) using aliases" );
my @rows = $cursor->next;
is( scalar @rows, 2,
"2 rows per cursor ->next call" );
is( $rows[0]->table->name, 'project',
"First row is from project table" );
is( $rows[1]->table->name, 'project',
"Second row is from project table" );
is( $rows[0]->table, $rows[1]->table,
"The two rows should share the same table object (the alias should be gone at this point)" );
}
{
my @rows;
t/03-runtime.t view on Meta::CPAN
"Join employee, employee_project, and project tables where employee_id = 1 using one_row method" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
}
$cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 1 ],
order_by => [ $proj_t->column('project_id'), 'desc' ] );
@rows = $cursor->next;
my $first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
my $second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id > $second_proj_id,
"Order by clause should cause project rows to come back in descending order of project id" );
$cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 1 ],
order_by => [ $proj_t->column('project_id'), 'desc' ] );
@rows = $cursor->next;
$first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
$second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id > $second_proj_id,
"Order by clause (alternate form) should cause project rows to come back in descending order of project id" );
eval_ok( sub { $cursor = $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ],
join => [ [ $emp_t, $emp_proj_t ],
[ $emp_proj_t, $proj_t ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) },
"Join with join as arrayref of arrayrefs" );
@rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
{
my $cursor;
eval_ok( sub { $cursor = $s->join( join => [ [ $emp_t, $emp_proj_t ],
[ $emp_proj_t, $proj_t ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) },
"Same join with no select parameter" );
my @rows = $cursor->next;
@rows = sort { $a->table->name cmp $b->table->name } @rows;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( ( grep { $_->table->name eq 'employee' } @rows ), 1,
"First row is from employee table" );
is( ( grep { $_->table->name eq 'employee_project' } @rows ), 1,
"Second row is from employee_project table" );
is( ( grep { $_->table->name eq 'project' } @rows ), 1,
"Third row is from project table" );
}
eval { $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ],
join => [ [ $emp_t, $emp_proj_t ],
t/03-runtime.t view on Meta::CPAN
$s->table('outer_1')->insert( values => { outer_1_name => 'test1 (has matching join row)',
outer_2_pk => 1 },
);
$s->table('outer_1')->insert( values => { outer_1_name => 'test2 (has no matching join row)',
outer_2_pk => undef },
);
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ) ]
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[1] )
{
my $set = shift @sets;
push @sets, $set;
}
t/03-runtime.t view on Meta::CPAN
"The second row in the first set should have the name 'will match something'" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test12 (has no matching join row)'" );
ok( ! defined $sets[1]->[1],
"The second row in the second set should not be defined" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ),
[ $s->table('outer_2')->column( 'outer_2_pk' ),
'!=', 1 ],
] ],
order_by =>
$s->table('outer_1')->column('outer_1_name')
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1], undef,
"The second row in the first set should be undef" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets[1]->[1], undef,
"The second row in the second set should be undef" );
}
{
my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') );
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ),
$fk,
[ $s->table('outer_2')->column( 'outer_2_pk' ),
'!=', 1 ],
] ],
order_by =>
$s->table('outer_1')->column('outer_1_name')
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1], undef,
"The second row in the first set should be undef" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets[1]->[1], undef,
"The second row in the second set should be undef" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s->tables( 'outer_1', 'outer_2' ) ] ]
) },
"Attempt a right outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Right outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[0] )
{
my $set = shift @sets;
push @sets, $set;
}
t/03-runtime.t view on Meta::CPAN
ok( ! defined $sets[1]->[0],
"The first row in the second set should not be defined" );
is( $sets[1]->[1]->select('outer_2_name'), 'will match nothing',
"The second row in the second set should have the name 'test12 (has no matching join row)'" );
}
{
my $cursor;
# do the same join, but with specified foreign key
my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') );
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s->tables( 'outer_1', 'outer_2' ), $fk ] ]
) },
"Attempt a right outer join, with explicit foreign key" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Right outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[0] )
{
my $set = shift @sets;
push @sets, $set;
}
t/03-runtime.t view on Meta::CPAN
is( @rows, 4,
"Should return four rows" );
}
my $p1 = $proj_t->insert( values => { name => 'P1',
department_id => $dep_id,
} );
my $p2 = $proj_t->insert( values => { name => 'P2',
department_id => $dep_id,
} );
eval_ok( sub { $cursor = $s->join( distinct => $dep_t,
join => [ $dep_t, $proj_t ],
where => [ $proj_t->column('project_id'), 'in',
map { $_->select('project_id') } $p1, $p2 ],
) },
"Do a join with distinct parameter set" );
@rows = $cursor->all_rows;
is( scalar @rows, 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows[0]->select('department_id'), $dep_id,
"Returned row's department_id should be $dep_id" );
{
eval_ok( sub { $cursor =
$s->join( distinct => $emp_proj_t,
join => [ $emp_t, $emp_proj_t ],
where => [ $emp_t->column('employee_id'), 'in', 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor->all_rows;
is( scalar @rows, 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows[0]->select('employee_id'), 9001,
"Returned row's employee_id should be 9001" );
}
{
eval_ok( sub { $cursor =
$s->join
( distinct => [ $emp_t, $emp_proj_t ],
join => [ $emp_t, $emp_proj_t ],
where =>
[ $emp_t->column('employee_id'), 'in', 9000, 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor->all_rows;
is( scalar @rows, 3,
"Setting distinct should cause only three rows to be returned" );
ok( ( grep { $_->[0]->select('employee_id') == 9000 } @rows ),
"Returned rows should include employee_id 9000" );
ok( ( grep { $_->[0]->select('employee_id') == 9001 } @rows ),
"Returned rows should include employee_id 9001" );
}
{
$proj_t->insert( values => { name => 'P99',
department_id => $dep{lying}->select('department_id'),
} );
eval_ok( sub { $cursor = $s->join( distinct => $dep_t,
join => [ $dep_t, $proj_t ],
order_by => $proj_t->column('name'),
) },
"Do a join with distinct and order_by not in select" );
@rows = $cursor->all_rows;
if ( $rdbms eq 'pg' )
{
is( scalar @rows, 5, "distinct should cause only five rows to be returned" );
}
else
{
is( scalar @rows, 2, "distinct should cause only two rows to be returned" );
}
is( $rows[0]->select('department_id'), $dep{borg}->select('department_id'),
'first row is borg department' );
is( $rows[-1]->select('department_id'), $dep{lying}->select('department_id'),
'last row is lying department' );
# Prevents a warning later about destroying a DBI handle with
# active statement handles.
undef $cursor;
}
# insert rows used to test order by with multiple columns
my $start_id = 999_990;
foreach ( [ qw( OB1 bad ) ],
[ qw( OB1 worse ) ],
[ qw( OB2 bad ) ],
[ qw( OB2 worse ) ],
[ qw( OB3 awful ) ],
[ qw( OB3 bad ) ],
t/03-runtime.t view on Meta::CPAN
eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp->select('name'), 'FOOFOOFOO',
"Name should be 'FOOFOOFOO'" );
$emp_t->insert( values => { name => 'Timestamp',
dep_id => $dep_id,
tstamp => time - 100_000 } );
my $cursor;
eval_ok( sub { $cursor =
$emp_t->rows_where( where =>
[ [ $emp_t->column('tstamp'), '!=', undef ],
[ $emp_t->column('tstamp'), '<', UNIX_TIMESTAMP() ] ] ) },
"Do select with where condition that uses SQL function UNIX_TIMESTAMP()" );
my @rows = $cursor->all_rows;
is( scalar @rows, 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows[0]->select('name'), 'Timestamp',
"That row should be named Timestamp" );
# Fulltext support tests
my $snuffle_id = $emp_t->insert( values => { name => 'snuffleupagus',
smell => 'invisible',
dep_id => $dep_id } )->select('employee_id');
t/03-runtime.t view on Meta::CPAN
eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp->select('name'), 'FOOFOOFOO',
"Name should be 'FOOFOOFOO'" );
$emp_t->insert( values => { name => 'Timestamp',
dep_id => $dep_id,
tstamp => time - 100_000 } );
my $cursor;
eval_ok( sub { $cursor =
$emp_t->rows_where( where =>
[ [ $emp_t->column('tstamp'), '!=', undef ],
[ $emp_t->column('tstamp'), '<', NOW() ] ] ) },
"Do select with where condition that uses SQL function NOW()" );
my @rows = $cursor->all_rows;
is( scalar @rows, 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows[0]->select('name'), 'Timestamp',
"That row should be named Timestamp" );
}
# Potential rows
my $p_emp;
eval_ok( sub { $p_emp = $emp_t->potential_row },
"Create potential row object");
t/03-runtime.t view on Meta::CPAN
$eid_c, '=', 9002 ] ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception from where clause as single arrayref with <>3 elements" );
{
# test that DriverStatement objects going out of scope leave
# $@ alone!
eval
{
my $cursor = $emp_t->all_rows;
die "ok\n";
};
is( $@, "ok\n",
"\$\@ should be 'ok'" );
}
{
my $row;
t/03-runtime.t view on Meta::CPAN
{
$s->prefetch_none;
is( scalar $proj_t->prefetch, 0,
"Check that schema->prefetch_none works" );
}
{
$s->prefetch_all;
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 9001 ] ) },
"Join with join as arrayref of arrayrefs" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( ( grep { defined } @rows ), 3,
"Make sure all rows are defined" );
is( $rows[0]->select('employee_id'), 9001,
"First rows should have employee_id == 9001" );
is( $rows[0]->select('name'), 'bob9001',
"First rows should have employee with name eq 'bob9001'" );
is( $rows[2]->select('name'), 'Extend',
"First rows should have project with name eq 'Extend'");
}
( run in 0.347 second using v1.01-cache-2.11-cpan-4d50c553e7e )