Alzabo
view release on metacpan or search on metacpan
? $opts{root}
: %Alzabo::Config::CONFIG
? Alzabo::Config::root_dir()
: find_possible_root()
);
return $root_dir if $opts{automated};
print <<'EOF';
Please select a root directory for Alzabo (schema files will be stored
under this root.
EOF
return Module::Build->prompt( ' Alzabo root?', $root_dir );
}
sub find_possible_root
{
my @dirs;
0.92
BUG FIXES:
- Reverse engineering with MySQL broke when the tables were returned
_without_ the schema name.
- Enable subselects for MySQL, though there's still no good API for
subselects.
- Pass any unknown command line options through to Module::Build, for
example things like "--install-base". Reported by Lars Dieckow. RT
Ticket #28410.
- Fix a possible bug in Alzabo::Config::available_schemas. Reported by
Otto Hirr. RT Ticket #28312
0.91 Mar 25, 2007
0.89 Jun 20, 2006
ENHANCEMENTS:
- Improved schema diffs for Postgres, particularly in the area of
comparing two columns. Now we attempt to determine if two columns
are logically equivalent, even if they might have minor variations
(INT vs INTEGER type name, 'f' versus 'false' for BOOLEAN default,
etc.).
- Added Alzabo::SQLMaker->distinct_requires_order_by_in_select for the
benefit of Pg, which requires that anything in the ORDER BY clause
show up in the SELECT when you SELECT DISTINCT. This change is
experimental, and may go away in future versions.
- Removed support for passing order_by and group_by as a hash
reference. This was deprecated in 0.59.
BUG FIXES:
- When reverse engineering a Postgres schema, Alzabo did not look for
Previously, this could only be done as a double array reference, like:
[ [ left_outer_join => $table_A, $table_B ] ]
- Various doc fixes and rewriting, most notably in Alzabo.pm.
BUG FIXES:
- A join using multiple aliases to the same table would fail with an
error message like "Cannot use column (Foo.bar_id) in select unless
its table is included in the FROM clause".
- Remove the long-ago deprecated next_row() and next_rows() methods.
- Postgres 7.3 allows identifiers to be up to 63 characters. This
broke the code that handled sequenced columns for Postgres. Patch
by Josh Jore.
- If you tried to create a relationship between two tables, and the
"table_to" table already had a column of the same name as the
Identifiers are always quoted when using Alzabo::Create::* with
Postgres, however.
- Did a fair amount of profiling in order to optimize Alzabo's data
fetching. In general, Alzabo::Runtime::* operations should be
faster.
- Added Alzabo::Runtime::Column->alias which is useful when executing
queries via the Alzabo::Runtime::Schema and Alzabo::Runtime::Table
->select methods.
BUG FIXES:
- Alzabo::MethodMake generated "lookup column/table" methods will
return if there is no matching entry in the related table, which is
important when the two tables are independent. Previously it would
have been a runtime error (attempting to call a method on an
undefined value).
- Fix warning from Row->update. Patch by Ilya Martynov.
Alzabo::Config::root_dir() every time you load Alzabo. An attempt
to load a schema without first defining the root_dir will throw an
exception. Based on a patch from Ilya Martynov.
BUG FIXES:
- Allow UNIQUE as a column attribute for Postgres. Reported by Dan
Martinez.
- Add DISTINCT back as an exportable function from the SQLMaker
subclasses. It may be useful when calling ->select and ->function.
- Fixed a bug that prevented things from being deleted from the cache
storage.
- Fixed a variety of problems related to handling Postgres tables and
columns with mixed or upper case names. This included a bug that
prevented them from being reverse engineered properly. Reported by
Terrence Brannon.
- Fixed a bug when altering a Postgres column name that caused Alzabo
choosing them yourself. For most relationships, Alzabo will simply
do the right thing, adding a column to one of the tables as needed.
- The problems running the tests with Postgres should now be fixed.
- Fix stupid and inefficient internal handling of "SELECT DISTINCT"
queries. Now Alzabo simply lets the database handle this, the way
it should have in the first place.
- The Alzabo::Runtime::Schema and Alzabo::Runtime::Table ->function
and ->select methods now allow you to select scalars so you can do
things like SELECT 1 FROM Foo WHERE ... in order to test for the
existence of a row.
- Added Alzabo::Table->primary_key_size method, which indicates how
many columns participate in the table's primary key.
- Added Alzabo::Runtime::Schema->row_count. Suggested by Daniel
Gaspani.
- Alzabo now detects older versions of schemas and transparently
BUG FIXES:
- Adding a column that is not-nullable or has a default to a table
under Postgres was causing an error with Postgres 7.2.1. It seems
likely that with earlier versions of Postgres, this was simply
failing silently. Patch by Daniel Gaspani.
- 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
- Fix detection of primary key changes for schema diffs.
- Handle NOT IN for where conditions.
---------------------------------------------------------------------------
0.63 Feb 18, 2002
ENHANCEMENTS:
- Calling Alzabo::Runtime::Row->select or
Alzabo::Runtime::Row->select_hash with no arguments returns the
values for all of the columns in the table. Suggested by Jeremy
R. Semeiks.
- The Alzabo::Runtime::Row->id method has been renamed to id_as_string
for the benefit of those crazy people who like to use "id" as a
column name and want Alzabo::MethodMaker to be able to create such a
method. Suggested by Alexei Barantsev.
- Changed the Alzabo::Create::Schema->sync_backend method so that if
there was no corresponding schema in the RDBMS, then it will
- 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:
- A call to the schema class's select or function methods that had
both an order_by and group_by parameter would fail because it tried
to process the order by clause before the group by clause.
- When thawing potential row objects, Alzabo was trying to stick them
into the cache, which may have worked before but not now, and should
be avoided anyway.
- The parent and children methods created by Alzabo::MethodMaker were
incorrect (and unfortunately the tests of this feature were hosed
too).
- When passing order_by specifications, it is now possible to do this:
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
order_by => { columns => ..., sort => ... }
has been deprecated in favor of a simpler syntax.
---------------------------------------------------------------------------
0.59 Nov 17, 2001
ENHANCEMENTS:
- 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
database. They are created via the new
Alzabo::Runtime::Table->potential_row method. Thanks to Ilya
Martynov for suggestions and code for this feature.
- Added Alzabo::Runtime::Row->schema method. Suggested by Ilya
Martynov.
- Alzabo was not handling the BETWEEN operator in where clauses
properly. Patch by Eric Hillman.
- Passing in something like this to rows_where:
( where => [ $col_foo, '=', 1,
$col_bar, '=', 2 ] )
worked when it shouldn't.
- Trying to do a select that involved a group by and a limit was not
being allowed.
INCOMPATIBILITIES:
- Got rid of the post_select_hash hook and combined it with
post_select, which now receives a hash reference.
---------------------------------------------------------------------------
0.58 Oct 18, 2001
ENHANCEMENTS:
- Added new insert_hooks, update_hooks, select_hooks, and delete_hooks
options to Alzabo::MethodMaker. Suggested by Ilya Martynov.
- Moved all the important document for the object caching system into
Alzabo::ObjectCache, including the import options for all of the
various modules.
- Added Alzabo::ObjectCache::Sync::RDBMS &
Alzabo::ObjectCache::Store::RDBMS. The former finally allows
synchronization of multiple processes across multiple machines!
- The way cardinality and dependency was being represented in the
schema graphs was sometimes backward and sometimes just broken.
- Fixed Alzabo::ObjectCache::Store::BerkeleyDB->clear, which was not
actually doing anything. Added tests that catch this.
- The lookup_tables option, which was deprecated in 0.57, was not
being allowed at all.
- 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
deprecated. They have been replaced by the new insert_hooks and
update_hooks options, along with new select_hooks and delete_hooks
options.
INCOMPATIBILITIES:
- If you specify give the 'all' parameter to MethodMaker, 'insert' and
'update' are no longer included.
---------------------------------------------------------------------------
0.57 Oct 9, 2001
- You can now use SQL functions pretty much anywhere you would want
(in inserts, updates, where clauses, etc). See the "Using SQL
Functions" section in the Alzabo.pm docs for more details.
- As a corollary to the above, the Alzabo::Runtime::Table->function
method has been created to replace the old
Alzabo::Runtime::Table->func method. This new method takes
advantage of the new system for using SQL functions and is simpler
and more flexible. It allows you to perform all sorts of aggregate
selects.
- Added the Alzabo::Runtime::Row->select_hash method. Requested by
Dana Powers.
DEPRECATIONS:
- The Alzabo::Runtime::Table->func method has been deprecated.
BUG FIXES:
- When adding an AUTO_INCREMENT column to an existing MySQL table, the
SQL generated would cause an error. This has been fixed. However,
Alzabo::ObjectCache::MemoryStore => Alzabo::ObjectCache::Store::Memory
Alzabo::ObjectCache::DBMSync => Alzabo::ObjectCache::Sync::DB_File
Alzabo::ObjectCache::IPCSync => Alzabo::ObjectCache::Sync::IPC.pm
Alzabo::ObjectCache::NullSync => Alzabo::ObjectCache::Sync::Null.pm
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
then another row was inserted with the same primary key. Note to
self: premature optimization is the root of all evil.
---------------------------------------------------------------------------
0.33 Feb 21, 2001
- The linking table methods generated by Alzabo::MethodMaker were
broken. Fixed this.
- Changed how order by clauses can be passed to select operations.
Also changed the docs, which were way out of sync with the changes
in this area.
- Attempting to update more than one value at once was broken. Fixed
this.
- Added Alzabo::Runtime::Table->func method to allow arbitrary column
aggregate functions like MAX, MIN, AVG, etc.
- Fixed schema creator bug. It was not possible to change a column's
However, the new DBMSync module will probably scale better, and
performance should be about the same for smaller applications. To
use it, do:
use Alzabo::ObjectCache( store => 'Alzabo::ObjectCache::MemoryStore',
sync => 'Alzabo::ObjectCache::DBMSync' );
4. If you run without any caching at all then the
Alzabo::Runtime::Row class's behavior has changed somewhat. In
particular, selects or updates against a deleted object will always
throw an Alzabo::Exception::NoSuchRow exception. Before, the
behavior wasn't very well defined.
Please read the section on clearing the cache in the
Alzabo::ObjectCache module, as this is an important concept. By
default, the caching and syncing modules will just grow unchecked.
You need to clear at the appropriate points (usually your
application's entry points) in order to keep them under control.
---------------------------------------------------------------------------
- Doesn't fail on install for Mason components if no Mason component
extension was given. Thanks _again_ to Randal for working with me
on this in IRC late at night.
---------------------------------------------------------------------------
0.10_4 Oct 10, 2000
- Fix Makefile.PL bug
- Auto select a column when adding a relation (if there is a logical
one to select).
---------------------------------------------------------------------------
0.10_3
- Fix bug with deleting foreign key objects from tables.
---------------------------------------------------------------------------
0.10
the construct $table->rows_where( where => { foo => undef } )
properly.
---------------------------------------------------------------------------
0.08
- Lazy column evaluation had made it possible to create an
Alzabo::Runtime::Row object that did not correspond to any data in
the database if its table object did specify any rows to prefetch.
This would have only been discovered later by calling the select
method on a non-primary key column. This hole was plugged.
- As a corollary to the above change methods in Alzabo::Runtime::Table
that produce rows now always return an empty list or undef when the
rows cannot be made because the specified primary key doesn't exist.
Previously, the rows_by_where_clause method did this while others
would cause an exception either during the object creation or later,
depending upon the situation described above.
- GENERAL NOTE: I probably used exceptions too much, as in the above
lib/Alzabo/SQLMaker/MySQL.pm
lib/Alzabo/SQLMaker/PostgreSQL.pm
lib/Alzabo/Table.pm
lib/Alzabo/Utils.pm
LICENSE
Makefile.PL
MANIFEST This list of files
mason/widgets/edit_field_checkbox
mason/widgets/edit_field_text_input
mason/widgets/edit_field_textarea
mason/widgets/fk_to_one_select
mason/widgets/insert
mason/widgets/insert_or_update
mason/widgets/update
META.yml
README
t/01-compile.t
t/01-driver.t
t/02-create.t
t/03-runtime.t
t/04-rev-engineer.t
SHA1 9286403266901d80a226476bb43cf0c1f10826b1 lib/Alzabo/Runtime/UniqueRowCache.pm
SHA1 ce71db21e316956a7a2cd98824c0a7d18dc1ab83 lib/Alzabo/SQLMaker.pm
SHA1 2bc532caa834986aa22ab277454f9231d2066731 lib/Alzabo/SQLMaker/MySQL.pm
SHA1 7ada47ae40a0606442200d4cfd9640f846f44c23 lib/Alzabo/SQLMaker/PostgreSQL.pm
SHA1 ac979e736d929e412e119e8271eeb3693046acfa lib/Alzabo/Schema.pm
SHA1 9f63a5bdc1342935eb5a78e2eec25b76ae05b923 lib/Alzabo/Table.pm
SHA1 8b97a915e10af7ac222b88107286a5d577f43e81 lib/Alzabo/Utils.pm
SHA1 f847fb97ccb893a2d49081016b4d26837d7e118a mason/widgets/edit_field_checkbox
SHA1 1ec65e465abc63eef84dbd78b6b26b53de69c230 mason/widgets/edit_field_text_input
SHA1 2a39cb0dacfa7c5bd3f4b0192e2034125283d9fb mason/widgets/edit_field_textarea
SHA1 a7a0e78c774733204a23ac2ba71e7faf6433ce7e mason/widgets/fk_to_one_select
SHA1 6c5c2cbe53d95d8a38d7164f0351bcb808ea677c mason/widgets/insert
SHA1 316becaea6da6008a0aa54350344e22f47ddb1c5 mason/widgets/insert_or_update
SHA1 ef79ea559a1a6cb262d5245cfa596fbf28a18986 mason/widgets/update
SHA1 eff25867aade830f62008e9c5ab0479ed30b8d69 t/01-compile.t
SHA1 94036f06e5f7a955326e8da71488f01c53b1f215 t/01-driver.t
SHA1 313c1ce15ce1b3720d5704ffbd823a43e0f5e833 t/02-create.t
SHA1 7eec7b4cd0bdccecf807ae241def67a1970f2a20 t/03-runtime.t
SHA1 0a68944ae8d4231f82b9873bc34436e41f7ec909 t/04-rev-engineer.t
SHA1 d22a8f365a9cc9853d7f690a62660c0892940b6e t/05a-rules-mysql.t
SHA1 cf1c5704e531920b7235a403e1c079c177b40803 t/05b-rules-pg.t
lib/Alzabo/Design.pod view on Meta::CPAN
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
other configuration information.
lib/Alzabo/Driver.pm view on Meta::CPAN
=back
=head2 rows
Returns an array of array references containing the data requested.
=head2 rows_hashref
Returns an array of hash references containing the data requested.
The hash reference keys are the columns being selected. All the key
names are in uppercase.
=head2 one_row
Returns an array or scalar containing the data returned, depending on
context.
=head2 one_row_hash
Returns a hash containing the data requested. The hash keys are the
columns being selected. All the key names are in uppercase.
=head2 column
Returns an array containing the values for the first column of each
row returned.
=head2 do
Use this for non-SELECT SQL statements.
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
# 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 =
$credit->rows_by_foreign_key
( foreign_key =>
$credit_t->foreign_keys_by_table($job_t) )->next;
print $movie->select('title'), " released in ", $movie->select('release_year'), "\n";
print ' ', $job->('job'), "\n";
}
A more sophisticated version of this code would take into account that
a person can do more than one job in the same movie.
The method names are quite verbose, so let's redo the example using
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>:
# Assume that the method_namer() subroutine pluralizes things as one
lib/Alzabo/Intro.pod view on Meta::CPAN
if ( $data{parent_location_id} )
{
my $parent_location_id = $data{parent_location_id};
my $location_t = $schema->table('Location');
while ( my $location =
$location_t->row_by_pk( pk => $parent_location_id ) )
{
die "Insert into location would create loop"
if $location->select('parent_location_id') == $data{location_id};
$parent_location_id = $location->select('parent_location_id');
}
}
}
Once again, let's rewrite the code to use
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>:
sub update_location
{
my $self = shift; # this is the row object
lib/Alzabo/Intro.pod view on Meta::CPAN
}
}
}
=head2 Using SQL functions
Each subclass of Alzabo::SQLMaker is capable of exporting functions
that allow you to use all the SQL functions that your RDBMS provides.
These functions are normal Perl functions. They take as arguments
normal scalars (strings and numbers), C<Alzabo::Column> objects, or
the return value of another SQL function. They may be used to select
data via the C<select()> and C<function()> methods in both the
L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table/"function and
select"> and
L<C<Alzabo::Runtime::Schema>|Alzabo::Runtime::Schema/"function and
select"> classes. They may also be used as part of updates, inserts,
and where clauses in any place that is valid SQL.
Examples:
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() ] );
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
linking_tables
lookup_columns
row_columns
self_relations
tables
table_columns
insert_hooks
update_hooks
select_hooks
delete_hooks
);
sub import
{
my $class = shift;
validate( @_, { schema => { type => SCALAR },
class_root => { type => SCALAR,
optional => 1 },
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
if ( $self->{opts}{row_columns} )
{
$self->make_row_column_methods($t);
}
if ( grep { $self->{opts}{$_} } qw( foreign_keys linking_tables lookup_columns ) )
{
$self->make_foreign_key_methods($t);
}
foreach ( qw( insert update select delete ) )
{
if ( $self->{opts}{"$_\_hooks"} )
{
$self->make_hooks($t, $_);
}
}
}
}
sub eval_schema_class
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
my $name = $self->_make_method
( type => 'row_column',
class => $self->{row_class},
returns => 'scalar value/takes new value',
code => sub { my $self = shift;
if (@_)
{
$self->update( $col_name => $_[0] );
}
return $self->select($col_name); },
column => $c,
) or next;
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
group => 'Methods that update/return a column value',
spec => [ { type => SCALAR } ],
description =>
"returns the value of the " . $c->name . " column for a row. Given a value, it will also update the row first.",
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
@reverse_pairs = map { [ $_->[0], $_->[1]->name ] } $fk->column_pairs;
}
my $table = $fk->table_from;
my $name = $self->_make_method
( type => 'self_relation',
class => $self->{row_class},
returns => 'single row',
code => sub { my $self = shift;
my @where = map { [ $_->[0], '=', $self->select( $_->[1] ) ] } @pairs;
return $table->one_row( where => \@where, @_ ); },
foreign_key => $fk,
parent => 1,
) or last;
if ($name)
{
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
) );
}
$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};
delete $p{where};
}
return $table->rows_where( where => \@where,
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
# table are part of the link.
return unless $fk->table_to->primary_key_size == $fk->table_to->columns;
# Not a linking table unless the PK in the middle table is the
# same size as the sum of the two table's PK sizes
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 ) ];
}
return $s->join( tables => [[@t, $fk_2]],
select => $select,
%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 =>
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
my $col_name = $_->name;
my $name = $self->_make_method
( type => 'lookup_columns',
class => $self->{row_class},
returns => 'scalar value of column',
code =>
sub { my $self = shift;
my $row = $self->rows_by_foreign_key( foreign_key => $fk, @_ );
return unless $row;
return $row->select($col_name) },
foreign_key => $fk,
column => $_,
) or next;
$self->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name,
group => 'Methods that follow a lookup table',
description =>
"returns the value of " . (join '.', $fk->table_to->name, $col_name) . " for the given row by following the foreign key relationship",
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
my $hooks =
$self->_hooks_doc_string( $self->{row_class}, 'pre_update', 'post_update' );
$self->{row_class}->add_class_docs
( Alzabo::ClassDocs->new
( group => 'Hooks',
description => "$hooks",
) );
}
sub make_select_hooks
{
my $self = shift;
my ($pre, $post) = ('', '');
$pre = " \$s->pre_select(\\\@cols);\n"
if $self->{row_class}->can('pre_update');
$post = " \$s->post_select(\\\%r);\n"
if $self->{row_class}->can('post_update');
eval <<"EOF";
{
package $self->{row_class};
sub select
{
my \$s = shift;
my \@cols = \@_;
return \$s->schema->run_in_transaction( sub {
$pre
my \@r;
my %r;
if (wantarray)
{
\@r{ \@cols } = \$s->SUPER::select(\@cols);
}
else
{
\$r{ \$cols[0] } = (scalar \$s->SUPER::select(\$cols[0]));
}
$post
return wantarray ? \@r{\@cols} : \$r{ \$cols[0] };
} );
}
sub select_hash
{
my \$s = shift;
my \@cols = \@_;
return \$s->schema->run_in_transaction( sub {
$pre
my \%r = \$s->SUPER::select_hash(\@cols);
$post
return \%r;
} );
}
}
EOF
Alzabo::Exception::Eval->throw( error => $@ ) if $@;
my $hooks =
$self->_hooks_doc_string( $self->{row_class}, 'pre_select', 'post_select' );
$self->{row_class}->add_class_docs
( Alzabo::ClassDocs->new
( group => 'Hooks',
description => "$hooks",
) );
}
sub make_delete_hooks
{
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
=item * update_hooks => $bool
Look for hooks to wrap around the C<update> method in
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row>. See L<Loading
Classes> for more details. You have to define a C<pre_update()>
and/or C<post_update()> method for the generated row class or this
parameter will not do anything. See the L<HOOKS|/"HOOKS"> section for
more details.
=item * select_hooks => $bool
Look for hooks to wrap around the C<select> method in
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row>. See L<Loading
Classes> for more details. You have to define either a
C<pre_select()> and/or C<post_select()> method for the generated row
class or this parameter will not do anything. See the
L<HOOKS|/"HOOKS"> section for more details.
=item * delete_hooks => $bool
Look for hooks to wrap around the C<delete> method in
L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row>. See L<Loading
Classes> for more details. You have to define either a
C<pre_delete()> and/or C<post_delete()> method for the generated row
class or this parameter will not do anything. See the
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
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
format.
All hooks are inside a transaction which is rolled back if any part of
the process fails.
It should be noted that Alzabo uses both the C<<
Alzabo::Runtime::Row->select >> and C<< Alzabo::Runtime::Row->delete >>
methods internally. If their behavior is radically altered through
the use of hooks, then some of Alzabo's functionality may be broken.
Given this, it may be safer to create new methods to fetch and massage
data rather than to create post-select hooks that alter data.
Each of these hooks receives different parameters, documented below:
=head2 Insert Hooks
=over 4
=item * pre_insert
This method receives a hash reference of all the parameters that are
lib/Alzabo/MethodMaker.pm view on Meta::CPAN
=item * post_update
This method receives the same parameters as C<pre_update()>
=back
=head2 Select Hooks
=over 4
=item * pre_select
This method receives an array reference containing the names of the
requested columns. This is called when either the L<C<<
Alzabo::Runtime::Row->select() >>|Alzabo::Runtime::Row/select> or
L<C<< Alzabo::Runtime::Row->select_hash()
>>|Alzabo::Runtime::Row/select_hash> methods are called.
=item * post_select
This method is called after the L<C<< Alzabo::Runtime::Row->select()
>>|Alzabo::Runtime::Row/select> or L<C<<
Alzabo::Runtime::Row->select_hash()
>>|Alzabo::Runtime::Row/select_hash> methods. It receives a hash
containing the name and values returned from the revelant method,
which it may modify. If the values of this hash reference are
modified, then this will be seen by the original caller.
=back
=head2 Delete hooks
=over 4
lib/Alzabo/MySQL.pod view on Meta::CPAN
=item *
Alzabo supports the ability to specify prefixes when adding an index.
Prefixes are required when attempting to index any sort of text or
blob column.
=item *
Alzabo supports the creation of fulltext indexes and their use in
SELECT and WHERE clauses. This includes the ability to get back the
score given for a match as part of a select, using the C<function> or
C<select> methods of either table or schema objects.
=back
=head2 Reverse Engineering
=over 4
=item *
When reverse engineering a schema, Alzabo knows that MySQL has
lib/Alzabo/QuickRef.pod view on Meta::CPAN
=for html_docs type=object
Fetch rows from one or more tables based on a table join. Returns
either a L<C<Alzabo::Runtime::RowCursor>|Alzabo::Runtime::RowCursor>
or L<C<Alzabo::Runtime::JoinCursor>|Alzabo::Runtime::JoinCursor>
object.
=for html_docs link=L<More|Alzabo::Runtime::Schema/join>
=item * function/select
=for html_docs type=object
Allows you to execute arbitrary column aggregate SQL functions such as
C<AVG> or C<MAX> with a multi-table join.
=for html_docs link=L<More|Alzabo::Runtime::Schema/function and select>
=item * table ($name)
=for html_docs type=object
Returns an L<C<Alzabo::Runtime::Table>|Alzabo::Runtime::Table> object.
This is important because most of the row fetching operations are
table object methods.
=for html_docs link=L<More|Alzabo::Runtime::Schema/table ($name)>
lib/Alzabo/QuickRef.pod view on Meta::CPAN
=for html_docs link=L<More|Alzabo::Runtime::Table/rows_where>
=item * all_rows
=for html_docs type=object
Retrieves all the rows in the table.
=for html_docs link=L<More|Alzabo::Runtime::Table/all_rows>
=item * function/select
=for html_docs type=object
Allows you to execute arbitrary column aggregate SQL functions such as
C<AVG> or C<MAX>.
=for html_docs link=L<More|Alzabo::Runtime::Table/function and select>
=item * potential_row
=for html_docs type=object
Make a new L<C<Alzabo::Runtime::Row>|Alzabo::Runtime::Row> in the
"potential" state.
=for html_docs link=L<More|Alzabo::Runtime::Table/potential_row>
=back
=head3 Alzabo::Runtime::Row
Objects in this class represent a single row of data. You can
retrieve the actual column values from it, update it, or delete it.
=over 4
=item * select (@list_of_column_names)
=for html_docs type=object
Given a list of column names, this method returns the values for those
columns.
=for html_docs link=L<More|Alzabo::Runtime::Row/select (@list_of_column_names)>
=item * update (%hash_of_columns_and_values)
=for html_docs type=object
Given a hash of columns and values, this method will update the
database and the object to match those values.
=for html_docs link=L<More|Alzabo::Runtime::Row/update (%hash_of_columns_and_values)>
lib/Alzabo/Runtime/Column.pm view on Meta::CPAN
Takes the following parameters:
=over 4
=item * as => $name
=back
This method returns an object that can be used in calls to the table
and schema C<select()> methods in order to change the name given to
the column if C<next_as_hash()> is called on the
L<C<Alzabo::DriverStatement>|Alzabo::Driver/Alzabo::DriverStatment>
returned by the aforementioned C<select()> method.
=head1 AUTHOR
Dave Rolsky, <autarch@urth.org>
=cut
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)
{
lib/Alzabo/Runtime/JoinCursor.pm view on Meta::CPAN
=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>.
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
Params::Validate::validation_options
( on_fail => sub { params_exception join '', @_ } );
use Storable ();
$VERSION = 2.0;
BEGIN
{
no strict 'refs';
foreach my $meth ( qw( select select_hash update refresh delete
id_as_string is_live is_potential is_deleted ) )
{
*{ __PACKAGE__ . "::$meth" } =
sub { my $s = shift;
$s->{state}->$meth( $s, @_ ) };
}
}
use constant NEW_SPEC => { table => { isa => 'Alzabo::Runtime::Table' },
pk => { type => SCALAR | HASHREF,
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
);
my $fk = delete $p{foreign_key};
if ($p{where})
{
$p{where} = [ $p{where} ] unless Alzabo::Utils::is_arrayref( $p{where}[0] );
}
push @{ $p{where} },
map { [ $_->[1], '=', $self->select( $_->[0]->name ) ] } $fk->column_pairs;
# if the relationship is not 1..n, then only one row can be
# returned (or referential integrity has been hosed in the
# database).
return $fk->is_one_to_many ? $fk->table_to->rows_where(%p) : $fk->table_to->one_row(%p);
}
# class method
sub id_as_string_ext
{
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
=head1 NAME
Alzabo::Runtime::Row - Row objects
=head1 SYNOPSIS
use Alzabo::Runtime::Row;
my $row = $table->row_by_pk( pk => 1 );
$row->select('foo');
$row->update( bar => 5 );
$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
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
Finally, there is an "in cache" state, which is identical to the
"live" state, except that it is used for object's that are cached via
the
L<C<Alzabo::Runtime::UniqueRowCache>|Alzabo::Runtime::UniqueRowCache>
class.
=head1 METHODS
Row objects offer the following methods:
=head2 select (@list_of_column_names)
Returns a list of values matching the specified columns in a list
context. In scalar context it returns only a single value (the first
column specified).
If no columns are specified, it will return the values for all of the
columns in the table, in the order that are returned by
L<C<Alzabo::Runtime::Table-E<gt>columns>|Alzabo::Runtime::Table/columns>.
This method throws an
L<C<Alzabo::Runtime::NoSuchRowException>|Alzabo::Exceptions> if called
on a deleted row.
=head2 select_hash (@list_of_column_names)
Returns a hash of column names to values matching the specified
columns.
If no columns are specified, it will return the values for all of the
columns in the table.
This method throws an
L<C<Alzabo::Runtime::NoSuchRowException>|Alzabo::Exceptions> if called
on a deleted row.
lib/Alzabo/Runtime/Row.pm view on Meta::CPAN
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
to the database.
These objects are not cached.
Once L<C<make_live()>|/make_live> is called, the object's state
becomes "live".
Potential rows have looser constraints for column values than regular
rows. When creating a new potential row, it is ok if none of the
columns are defined. If a column has a default, and a value for that
lib/Alzabo/Runtime/RowCursor.pm view on Meta::CPAN
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
lib/Alzabo/Runtime/RowState/Deleted.pm view on Meta::CPAN
package Alzabo::Runtime::RowState::Deleted;
use strict;
use Alzabo::Runtime;
BEGIN
{
no strict 'refs';
foreach my $meth ( qw( select select_hash refresh update delete id_as_string ) )
{
*{__PACKAGE__ . "::$meth"} =
sub { $_[1]->_no_such_row_error };
}
}
sub is_potential { 0 }
sub is_live { 0 }
lib/Alzabo/Runtime/RowState/InCache.pm view on Meta::CPAN
package Alzabo::Runtime::RowState::InCache;
use strict;
use base qw(Alzabo::Runtime::RowState::Live);
BEGIN
{
no strict 'refs';
foreach my $meth ( qw( select select_hash ) )
{
my $super = "SUPER::$meth";
*{__PACKAGE__ . "::$meth"} =
sub { my $s = shift;
$s->refresh(@_) unless $s->_in_cache(@_);
$s->$super(@_);
};
}
lib/Alzabo/Runtime/RowState/Live.pm view on Meta::CPAN
return if isa_alzabo_exception( $e, 'Alzabo::Exception::NoSuchRow' );
rethrow_exception $e;
}
}
unless ( keys %{ $row->{data} } > keys %{ $row->{pk} } )
{
# Need to try to fetch something to confirm that this row exists!
my $sql = ( $row->schema->sqlmaker->
select( ($row->table->primary_key)[0] )->
from( $row->table ) );
$class->_where($row, $sql);
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return
unless defined $row->schema->driver->one_row( sql => $sql->sql,
bind => $sql->bind );
lib/Alzabo/Runtime/RowState/Live.pm view on Meta::CPAN
$class->_get_data( $row, @pre );
}
sub _get_data
{
my $class = shift;
my $row = shift;
my %data;
my @select;
foreach my $col (@_)
{
if ( exists $row->{data}{$col} )
{
$data{$col} = $row->{data}{$col};
}
else
{
push @select, $col;
}
}
return %data unless @select;
my $sql = ( $row->schema->sqlmaker->
select( $row->table->columns(@select) )->
from( $row->table ) );
$class->_where($row, $sql);
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
my %d;
@d{@select} =
$row->schema->driver->one_row( sql => $sql->sql,
bind => $sql->bind )
or $row->_no_such_row_error;
while ( my( $k, $v ) = each %d )
{
$row->{data}{$k} = $data{$k} = $v;
}
return %data;
lib/Alzabo/Runtime/RowState/Live.pm view on Meta::CPAN
my $row = shift;
my %p = @_;
return $row->{id_string} if exists $row->{id_string};
$row->{id_string} = $row->id_as_string_ext( pk => $row->{pk},
table => $row->table );
return $row->{id_string};
}
sub select
{
my $class = shift;
my $row = shift;
my @cols = @_ ? @_ : map { $_->name } $row->table->columns;
my %data = $class->_get_data( $row, @cols );
return wantarray ? @data{@cols} : $data{ $cols[0] };
}
sub select_hash
{
my $class = shift;
my $row = shift;
my @cols = @_ ? @_ : map { $_->name } $row->table->columns;
return $class->_get_data( $row, @cols );
}
sub update
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
sub one_row
{
# could be replaced with something potentially more efficient
return shift->join(@_)->next;
}
use constant JOIN_SPEC => { join => { type => ARRAYREF | OBJECT,
optional => 1 },
tables => { type => ARRAYREF | OBJECT,
optional => 1 },
select => { type => ARRAYREF | OBJECT,
optional => 1 },
where => { type => ARRAYREF,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
distinct => { type => ARRAYREF | OBJECT,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
if ( $p{order_by} )
{
$p{order_by} =
Alzabo::Utils::is_arrayref( $p{order_by} )
? $p{order_by}
: $p{order_by}
? [ $p{order_by} ]
: undef;
}
# We go in this order: $p{select}, $p{distinct}, @tables
my @select_tables = ( $p{select} ?
( Alzabo::Utils::is_arrayref( $p{select} ) ?
@{ $p{select} } : $p{select} ) :
$p{distinct} ?
@{ $p{distinct} } :
@tables );
my $sql = Alzabo::Runtime::sqlmaker( $self, \%p );
my @select_cols;
if ( $p{distinct} )
{
my %distinct = map { $_ => 1 } @{ $p{distinct} };
# hack so distinct is not treated as a function, just a
# bareword in the SQL
@select_cols = ( 'DISTINCT',
map { ( $_->primary_key,
$_->prefetch ?
$_->columns( $_->prefetch ) :
() ) }
@{ $p{distinct} }
);
foreach my $t (@select_tables)
{
next if $distinct{$t};
push @select_cols, $t->primary_key;
push @select_cols, $t->columns( $t->prefetch ) if $t->prefetch;
}
if ( $p{order_by} && $sql->distinct_requires_order_by_in_select )
{
my %select_cols = map { $_ => 1 } @select_cols;
push @select_cols, grep { ref } @{ $p{order_by} };
}
@select_tables = ( @{ $p{distinct} }, grep { ! $distinct{$_} } @select_tables );
}
else
{
@select_cols =
( map { ( $_->primary_key,
$_->prefetch ?
$_->columns( $_->prefetch ) :
() ) }
@select_tables );
}
$sql->select(@select_cols);
$self->_join_all_tables( sql => $sql,
join => $p{join} );
Alzabo::Runtime::process_where_clause( $sql, $p{where} ) if exists $p{where};
Alzabo::Runtime::process_order_by_clause( $sql, $p{order_by} )
if $p{order_by};
$sql->limit( ref $p{limit} ? @{ $p{limit} } : $p{limit} ) if $p{limit};
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
my $statement = $self->driver->statement( sql => $sql->sql,
bind => $sql->bind );
if (@select_tables == 1)
{
return Alzabo::Runtime::RowCursor->new
( statement => $statement,
table => $select_tables[0]->real_table,
);
}
else
{
return Alzabo::Runtime::JoinCursor->new
( statement => $statement,
tables => [ map { $_->real_table } @select_tables ],
);
}
}
sub row_count
{
my $self = shift;
my %p = @_;
return $self->function( select => Alzabo::Runtime::sqlmaker( $self, \%p )->COUNT('*'),
%p,
);
}
sub function
{
my $self = shift;
my %p = @_;
my $sql = $self->_select_sql(%p);
my $method =
Alzabo::Utils::is_arrayref( $p{select} ) && @{ $p{select} } > 1 ? 'rows' : 'column';
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->driver->$method( sql => $sql->sql,
bind => $sql->bind );
}
sub select
{
my $self = shift;
my $sql = $self->_select_sql(@_);
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->driver->statement( sql => $sql->sql,
bind => $sql->bind );
}
use constant _SELECT_SQL_SPEC => { join => { type => ARRAYREF | OBJECT,
optional => 1 },
tables => { type => ARRAYREF | OBJECT,
optional => 1 },
select => { type => SCALAR | ARRAYREF | OBJECT,
optional => 1 },
where => { type => ARRAYREF,
optional => 1 },
group_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
having => { type => ARRAYREF,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
optional => 1 },
};
sub _select_sql
{
my $self = shift;
my %p = validate( @_, _SELECT_SQL_SPEC );
$p{join} ||= delete $p{tables};
$p{join} = [ $p{join} ] unless Alzabo::Utils::is_arrayref( $p{join} );
my @tables;
if ( Alzabo::Utils::is_arrayref( $p{join}->[0] ) )
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
# tables
@tables = values %{ { map { $_ => $_ }
grep { Alzabo::Utils::safe_isa( 'Alzabo::Table', $_ ) }
map { @$_ } @{ $p{join} } } };
}
else
{
@tables = grep { Alzabo::Utils::safe_isa( 'Alzabo::Table', $_ ) } @{ $p{join} };
}
my @funcs = Alzabo::Utils::is_arrayref( $p{select} ) ? @{ $p{select} } : $p{select};
my $sql = ( Alzabo::Runtime::sqlmaker( $self, \%p )->
select(@funcs) );
$self->_join_all_tables( sql => $sql,
join => $p{join} );
Alzabo::Runtime::process_where_clause( $sql, $p{where} )
if exists $p{where};
Alzabo::Runtime::process_group_by_clause( $sql, $p{group_by} )
if exists $p{group_by};
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
SELECT ... FROM table_A
LEFT OUTER JOIN table_B ON ...
AND (table_B.size > 2 AND table_B.name != 'Foo')
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
returned. If this parameter is not given, then the "distinct" or
"join" parameter will be used instead, with the "distinct" parameter
taking precedence.
This can be either a single table or an array reference of table
objects.
=item * distinct => C<Alzabo::Runtime::Table> object or objects (optional)
If this parameter is given, it indicates that results from the join
should never contain repeated rows.
This can be used in place of the "select" parameter to indicate from
which tables you want rows returned. The "select" parameter, if
given, supercedes this parameter.
For some databases (notably Postgres), if you want to do a "SELECT
DISTINCT" query then all of the columns mentioned in your "ORDER BY"
clause must also be in your SELECT clause. Alzabo will make sure this
is the case, but it may cause more rows to be returned than you
expected, though this depends on the query.
B<NOTE:> The adding of columns to the SELECT clause from the ORDER BY
clause is considered experimental, because it can change the expected
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
Parameters>.
=item * limit (optional)
See the L<documentation on limit clauses for the
Alzabo::Runtime::Table class|Alzabo::Runtime::Table/Common
Parameters>.
=back
If the "select" parameter specified that more than one table is
desired, then this method will return n
L<JoinCursor|Alzabo::Runtime::JoinCursor> object
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.
They both take the following parameters:
=over 4
=item * select => $function or [ scalars, SQL functions and/or C<Alzabo::Column> objects ]
If you pass an array reference for this parameter, it may contain
scalars, SQL functions, or column objects. For example:
$schema->function( select =>
[ 1,
$foo->column('name'),
LENGTH( $foo->column('name') ) ],
join => [ $foo, $bar_table ],
);
This is equivalent to the following SQL:
SELECT 1, foo.name, LENGTH( foo.name )
FROM foo, bar
lib/Alzabo/Runtime/Schema.pm view on Meta::CPAN
=item * limit
See the L<documentation on limit clauses for the
Alzabo::Runtime::Table class|Alzabo::Runtime::Table/Common
Parameters>.
=back
These methods are used to call arbitrary SQL functions such as 'AVG'
or 'MAX', and to select data from individual columns. The function
(or functions) should be the return values from the functions exported
by the SQLMaker subclass that you are using. Please see L<Using SQL
functions|Alzabo::Intro/"Using SQL functions"> for more details.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head3 function() return values
The return value of this method is highly context sensitive.
If you only requested a single element in your "select" parameter,
such as "DISTINCT(foo)", then it returns the first value in scalar
context and all the values as an array in list context.
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>
=head2 prefetch_all
This method will set all the tables in the schema to prefetch all
their columns. See the L<lazy column
loading|Alzabo::Runtime::Table/LAZY COLUMN LOADING> section in
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
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;
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
);
}
sub row_count
{
my $self = shift;
my %p = @_;
my $count = Alzabo::Runtime::sqlmaker( $self->schema, \%p )->COUNT('*');
return $self->function( select => $count, %p );
}
sub function
{
my $self = shift;
my %p = @_;
my $sql = $self->_select_sql(%p);
my $method =
Alzabo::Utils::is_arrayref( $p{select} ) && @{ $p{select} } > 1 ? 'rows' : 'column';
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->schema->driver->$method( sql => $sql->sql,
bind => $sql->bind );
}
sub select
{
my $self = shift;
my $sql = $self->_select_sql(@_);
$sql->debug(\*STDERR) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self->schema->driver->statement( sql => $sql->sql,
bind => $sql->bind );
}
use constant
_SELECT_SQL_SPEC => { select => { type => SCALAR | ARRAYREF | OBJECT },
where => { type => ARRAYREF | OBJECT,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
group_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
having => { type => ARRAYREF,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
optional => 1 },
};
sub _select_sql
{
my $self = shift;
my %p = validate( @_, _SELECT_SQL_SPEC );
my @funcs = Alzabo::Utils::is_arrayref( $p{select} ) ? @{ $p{select} } : $p{select};
my $sql = Alzabo::Runtime::sqlmaker( $self->schema, \%p )->select(@funcs)->from($self);
Alzabo::Runtime::process_where_clause( $sql, $p{where} )
if exists $p{where};
Alzabo::Runtime::process_group_by_clause( $sql, $p{group_by} )
if exists $p{group_by};
Alzabo::Runtime::process_having_clause( $sql, $p{having} )
if exists $p{having};
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
This parameter can take one of two different values. The simplest
form is to just give it a single column object or SQL function.
Alternatively, you can give it an array reference to a list of column
objects, SQL functions and strings like this:
order_by => [ $col1, COUNT('*'), $col2, 'DESC', $col3, 'ASC' ]
It is important to note that you cannot simply use any arbitrary SQL
function as part of your order by clause. You need to use a function
that is exactly the same as one that was given as part of the "select"
parameter.
=item * group_by => see below
This parameter can take either a single column object or an array of
column objects.
=item * having => same as "where"
This parameter is specified in the same way as the "where" parameter.
lib/Alzabo/Runtime/Table.pm view on Meta::CPAN
=over 4
=item * where
=back
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.
They both take the following parameters:
=over 4
=item * select => $function or [ scalars, SQL functions and/or C<Alzabo::Column> objects ]
If you pass an array reference for this parameter, it may contain
scalars, SQL functions, or column objects. For example:
$table->function( select =>
[ 1,
$foo->column('name'),
LENGTH( $foo->column('name') ) ] );
This is equivalent to the following SQL:
SELECT 1, foo.name, LENGTH( foo.name )
FROM foo
=item * where
=item * order_by
=item * group_by
=item * limit
=back
This method is used to call arbitrary SQL functions such as 'AVG' or
'MAX', or to select arbitrary column data. The function (or
functions) should be the return values from the functions exported by
the SQLMaker subclass that you are using. Please see L<Using SQL
functions|Alzabo/Using SQL functions> for more details.
Throws: L<C<Alzabo::Exception::Logic>|Alzabo::Exceptions>,
L<C<Alzabo::Exception::Params>|Alzabo::Exceptions>
=head3 function() return values
The return value of this method is highly context sensitive.
If you only requested a single element in your "select" parameter,
such as "DISTINCT(foo)", then it returns the first value in scalar
context and all the values as an array in list context.
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
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
# this just needs to be some unique thing that won't ever look like a
# valid bound parameter
my $placeholder = do { my $x = 1; bless \$x, 'Alzabo::SQLMaker::Placeholder' };
sub placeholder { $placeholder }
sub last_op
{
return shift->{last_op};
}
sub select
{
my $self = shift;
Alzabo::Exception::Params->throw( error => "The select method requires at least one parameter" )
unless @_;
$self->{sql} .= 'SELECT ';
if ( lc $_[0] eq 'distinct' )
{
$self->{sql} .= ' DISTINCT ';
shift;
}
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
push @sql, $string;
}
}
elsif ( ! ref $elt )
{
push @sql, $elt;
}
else
{
Alzabo::Exception::SQL->throw
( error => 'Arguments to select must be either column objects,' .
' table objects, function objects, or plain scalars' );
}
}
$self->{sql} .= join ', ', @sql;
$self->{type} = 'select';
$self->{last_op} = 'select';
return $self;
}
sub from
{
my $self = shift;
$self->_assert_last_op( qw( select delete function ) );
my $spec =
$self->{last_op} eq 'select' ? { type => OBJECT | ARRAYREF } : { can => 'alias_name' };
validate_pos( @_, ( $spec ) x @_ );
$self->{sql} .= ' FROM ';
if ( $self->{last_op} eq 'delete' )
{
$self->{sql} .=
join ', ', map { ( $self->{quote_identifiers} ?
$self->{driver}->quote_identifier( $_->name ) :
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
{
$sql .= $elt->name . ' AS ' . $elt->alias_name;
}
$self->{tables}{$elt} = 1;
}
$self->{sql} .= $sql;
}
if ($self->{type} eq 'select')
{
foreach my $t ( keys %{ $self->{column_tables} } )
{
unless ( $self->{tables}{$t} )
{
my $err = 'Cannot select column ';
$err .= 'unless its table is included in the FROM clause';
Alzabo::Exception::SQL->throw( error => $err );
}
}
}
$self->{last_op} = 'from';
return $self;
}
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
if ( $comp eq 'BETWEEN' )
{
Alzabo::Exception::SQL->throw
( error => "The BETWEEN comparison operator requires an additional argument" )
unless @_ == 1;
my $rhs2 = shift;
Alzabo::Exception::SQL->throw
( error => "The BETWEEN comparison operator cannot accept a subselect" )
if grep { Alzabo::Utils::safe_isa( $_, 'Alzabo::SQLMaker' ) } $rhs, $rhs2;
$self->{sql} .= ' BETWEEN ';
$self->{sql} .= $self->_rhs($rhs);
$self->{sql} .= " AND ";
$self->{sql} .= $self->_rhs($rhs2);
return;
}
if ( $comp eq 'IN' || $comp eq 'NOT IN' )
{
$self->{sql} .= " $comp (";
$self->{sql} .=
join ', ', map { Alzabo::Utils::safe_isa( $_, 'Alzabo::SQLMaker' )
? '(' . $self->_subselect($_) . ')'
: $self->_rhs($_) } $rhs, @_;
$self->{sql} .= ')';
return;
}
Alzabo::Exception::Params->throw
( error => 'Too many parameters to Alzabo::SQLMaker->condition method' )
if @_;
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
Alzabo::Exception::SQL->throw
( error => "Cannot compare a column to a NULL with '$comp'" );
}
}
elsif ( ref $rhs )
{
$self->{sql} .= " $comp ";
if( $rhs->isa('Alzabo::SQLMaker') )
{
$self->{sql} .= '(';
$self->{sql} .= $self->_subselect($rhs);
$self->{sql} .= ')';
}
else
{
$self->{sql} .= $self->_rhs($rhs);
}
}
}
sub _rhs
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
return ( $self->{quote_identifiers} ?
$self->{driver}->quote_identifier( $rhs->table->alias_name, $rhs->name ) :
$rhs->table->alias_name . '.' . $rhs->name );
}
else
{
return $self->_bind_val($rhs);
}
}
sub _subselect
{
my $self = shift;
my $sql = shift;
push @{ $self->{bind} }, @{ $sql->bind };
return $sql->sql;
}
sub order_by
{
my $self = shift;
$self->_assert_last_op( qw( select from condition group_by ) );
Alzabo::Exception::SQL->throw
( error => "Cannot use order by in a '$self->{type}' statement" )
unless $self->{type} eq 'select';
validate_pos( @_, ( { type => SCALAR | OBJECT,
callbacks =>
{ 'column_or_function_or_sort' =>
sub { Alzabo::Utils::safe_can( $_[0], 'table' ) ||
Alzabo::Utils::safe_isa( $_[0], 'Alzabo::SQLMaker::Function' ) ||
$_[0] =~ /^(?:ASC|DESC)$/i } } }
) x @_ );
$self->{sql} .= ' ORDER BY ';
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
$self->{last_op} = 'order_by';
return $self;
}
sub group_by
{
my $self = shift;
$self->_assert_last_op( qw( select from condition ) );
Alzabo::Exception::SQL->throw
( error => "Cannot use group by in a '$self->{type}' statement" )
unless $self->{type} eq 'select';
validate_pos( @_, ( { can => 'table' } ) x @_ );
foreach my $c (@_)
{
unless ( $self->{tables}{ $c->table } )
{
my $err = 'Cannot use column (';
$err .= join '.', $c->table->name, $c->name;
$err .= ") in $self->{type} unless its table is included in the FROM clause";
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
sub get_limit
{
shift()->_virtual;
}
sub sqlmaker_id
{
shift()->_virtual;
}
sub distinct_requires_order_by_in_select { 0 }
sub _virtual
{
my $self = shift;
my $sub = (caller(1))[3];
$sub =~ s/.*::(.*?)$/$1/;
Alzabo::Exception::VirtualMethod->throw( error =>
"$sub is a virtual method and must be subclassed in " . ref $self );
}
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
=item * quote_identifiers
A boolean value indicating whether or not identifiers should be
quoted. This defaults to false.
=back
=head1 GENERATING SQL
This class can be used to generate SQL by calling methods that are the
same as those used in SQL (C<select()>, C<update()>, etc.) in
sequence, with the appropriate parameters.
There are four entry point methods, L<C<select()>|"select
(Alzabo::Table and/or Alzabo::Column objects)">,
L<C<insert()>|"insert">, L<C<update()>|"update (Alzabo::Table)">, and
L<C<delete()>|"delete">. Attempting to call any other method without
first calling one of these is an error.
=head2 Entry Points
These methods are called as class methods and return a new object.
=head2 select (C<Alzabo::Table> and/or C<Alzabo::Column> objects)
This begins a select. The columns to be selected are the column(s)
passed in, and/or the columns of the table(s) passed in as arguments.
Followed by:
=over 4
L<C<from()>|"from (Alzabo::Table object, ...)">
L<C<** function>|"** function (Alzabo::Table object(s) and/or $string(s))">
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
L<C<from()>|"from (Alzabo::Table object, ...)">
=back
=head2 Other Methods
All of these methods return the object itself, making it possible to
chain together method calls such as:
Alzabo::SQLMaker->select($column)->from($table)->where($other_column, '>', 2);
=head2 from (C<Alzabo::Table> object, ...)
The table(s) from which we are selecting data.
Follows:
=over 4
L<C<select()>|"select (Alzabo::Table and/or Alzabo::Column objects)">
L<C<** function>|"** function (Alzabo::Table object(s) and/or $string(s))">
L<C<delete()>|"delete">
=back
Followed by:
=over 4
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
=back
Throws: L<C<Alzabo::Exception::SQL>|Alzabo::Exceptions>
=head2 where <see below>
The first parameter to where must be an C<Alzabo::Column> object or
SQL function. The second is a comparison operator of some sort, given
as a string. The third argument can be an C<Alzabo::Column> object, a
value (a number or string), or an C<Alzabo::SQLMaker> object. The
latter is treated as a subselect.
Values given as parameters will be properly quoted and escaped.
Some comparison operators allow additional parameters.
The C<BETWEEN> comparison operator requires a fourth argument. This
must be either an C<Alzabo::Column> object or a value.
The C<IN> and <NOT IN> operators allow any number of additional
parameters, which may be C<Alzabo::Column> objects, values, or
lib/Alzabo/SQLMaker.pm view on Meta::CPAN
the SQL statement.
=head1 SUBCLASSING Alzabo::SQLMaker
To create a subclass of C<Alzabo::SQLMaker> for your particular RDBMS
requires only that the L<virtual methods|"Virtual Methods"> listed
below be implemented.
In addition, you may choose to override any of the other methods
described in this documentation. For example, the MySQL subclass
override the L<C<_subselect()>|"_subselect"> method because MySQL
cannot support sub-selects.
Subclasses are also expected to offer for export various sets of
functions matching SQL functions. See the C<Alzabo::SQLMaker::MySQL>
subclass implementation for details.
=head1 VIRTUAL METHODS
The following methods must be implemented by the subclass:
=head2 limit
lib/Alzabo/SQLMaker/MySQL.pm view on Meta::CPAN
);
$MADE_FUNCTIONS = 1;
}
sub init
{
1;
}
sub select
{
my $self = shift;
#
# Special check for [ MATCH( $foo_col, $bar_col ), AGAINST('foo bar') ]
# IN_BOOLEAN_MODE is optional
#
for ( my $i = 0; $i <= $#_; $i++ )
{
if ( Alzabo::Utils::safe_isa( $_[$i], 'Alzabo::SQLMaker::Function' ) &&
lib/Alzabo/SQLMaker/MySQL.pm view on Meta::CPAN
Alzabo::Utils::safe_isa( $_[ $i + 1 ], 'Alzabo::SQLMaker::Function' ) &&
$_[ $i + 1 ]->as_string( $self->{driver}, $self->{quote_identifiers} ) =~
/^\s*IN BOOLEAN MODE/i )
{
$_[$i] .= ' ' . $_[$i + 1]->as_string( $self->{driver}, $self->{quote_identifiers} );
splice @_, $i + 1, 1;
}
}
}
$self->SUPER::select(@_);
}
sub condition
{
my $self = shift;
#
# Special check for [ MATCH( $foo_col, $bar_col ), AGAINST('foo bar') ]
# IN_BOOLEAN_MODE is optional
#
lib/Alzabo/SQLMaker/MySQL.pm view on Meta::CPAN
=head1 SYNOPSIS
use Alzabo::SQLMaker;
my $sql = Alzabo::SQLMaker->new( sql => 'MySQL' );
=head1 DESCRIPTION
This class implementes MySQL-specific SQL creation. MySQL does not
allow subselects. Any attempt to use a subselect (by passing an
C<Alzabo::SQMaker> object in as parameter to a method) will result in
an L<C<Alzabo::Exception::SQL>|Alzabo::Exceptions> error.
=head1 METHODS
Almost all of the functionality inherited from Alzabo::SQLMaker is
used as is. The only overridden methods are C<limit()> and
C<get_limit()>, as MySQL does allow for a C<LIMIT> clause in its SQL.
=head1 EXPORTED SQL FUNCTIONS
lib/Alzabo/SQLMaker/PostgreSQL.pm view on Meta::CPAN
$self->{last_op} = 'limit';
return $self;
}
sub get_limit
{
return undef;
}
sub distinct_requires_order_by_in_select { 1 }
sub sqlmaker_id
{
return 'PostgreSQL';
}
1;
__END__
mason/widgets/edit_field_checkbox view on Meta::CPAN
$row => undef
$class => $m->base_comp->attr_if_exists('checkbox_class_default')
</%args>
<%init>
my $true;
my $col_name = ref $column ? $column->name : $column;
if (defined $row)
{
$true = $row->select( $col_name );
}
else
{
$true = $column->default;
}
</%init>
mason/widgets/edit_field_text_input view on Meta::CPAN
$size => $m->base_comp->attr_if_exists('text_input_size_default') || 30
$maxlength => $size
</%args>
<%init>
my $val;
my $col_name = ref $column ? $column->name : $column;
if (defined $row)
{
$val = $row->select( $col_name );
}
$val = '' unless defined $val;
$maxlength =
$column->length && $column->length < $maxlength ? $column->length :
( ! $column->is_character ? 10 : $maxlength );
if ( $maxlength > $size && exists $ARGS{size} )
{
mason/widgets/edit_field_textarea view on Meta::CPAN
$cols => $m->base_comp->attr_if_exists('textarea_cols_default') || 40
$wrap => $m->base_comp->attr_if_exists('textarea_wrap_default') || 'multiple'
</%args>
<%init>
my $val;
my $col_name = ref $column ? $column->name : $column;
if (defined $row)
{
$val = $row->select( $col_name );
}
$val = '' unless defined $val;
</%init>
mason/widgets/fk_to_one_select view on Meta::CPAN
<%doc>
=pod
=head1 NAME
fk_to_one_select
=head1 DESCRIPTION
Given a foreign key and an optional row, this component produces a select
form element for that relationship.
If a row is given, then its value will be used as the default value
for the form element.
=head1 PARAMETERS
=over 4
=item * fk (required)
An C<Alzabo::ForeignKey> object representing the relationship.
=item * row (optional)
An Alzabo row object.
=item * class (optional)
This defaults to C<<
$m->base_comp->attr_if_exists('fk_to_one_select_class_default') >>.
=item * foreign_rows (optional)
This allows you to pre-select the rows in the foreign table that will
be used for the select options. Otherwise, the component simple grabs
all the available rows from the foreign table.
=item * display_column (optional)
This is the column in the foreign table which should be used as the
visible value of each option. This defaults to the column involved in
the foreign key relationship.
=back
=cut
</%doc>
<select name="<% $col_from->name | h %>" class="<% $class %>">
% unless ( $fk->from_is_dependent ) {
<option value=""></option>
% }
% foreach my $item (@foreign_rows) {
<option value="<% $item->select( $col_to->name ) %>" <% defined $current && $item->select( $col_to->name ) == $current ? 'selected="selected"' : '' %>><% $item->select( $display_column->name ) | h %></option>
% }
</select>
<%args>
$fk
$row => undef
@foreign_rows => ()
$class => $m->base_comp->attr_if_exists('fk_to_one_select_class_default')
$display_column => undef
</%args>
<%init>
# I'm a wuss. Don't know how to handle this.
my @col_from = $fk->columns_from;
return if @col_from > 1;
my $col_from = shift @col_from;
my $col_to = $fk->columns_to;
@foreign_rows = $col_to->table->all_rows->all_rows unless @foreign_rows;
my $current;
$current = $row->select( $col_from->name ) if $row;
$display_column = $col_to unless defined $display_column;
</%init>
t/03-runtime.t view on Meta::CPAN
my $emp_t = $s->table('employee');
my $dep_t = $s->table('department');
my $proj_t = $s->table('project');
my $emp_proj_t = $s->table('employee_project');
my %dep;
eval_ok( sub { $dep{borg} = $dep_t->insert( values => { name => 'borging' } ) },
"Insert borging row into department table" );
is( $dep{borg}->select('name'), 'borging',
"The borg department name should be 'borging'" );
{
my @all = $dep{borg}->select;
is( @all, 3,
"select with no columns should return all the values" );
is( $all[1], 'borging',
"The second value should be the department name" );
my %all = $dep{borg}->select_hash;
is( keys %all, 3,
"select_hash with no columns should return two keys" );
ok( exists $all{department_id},
"The returned hash should have a department_id key" );
ok( exists $all{name},
"The returned hash should have a department_id key" );
is( $all{name}, 'borging',
"The value of the name key be the department name" );
}
$dep{lying} = $dep_t->insert( values => { name => 'lying to the public' } );
my $borg_id = $dep{borg}->select('department_id');
delete $dep{borg};
eval_ok( sub { $dep{borg} = $dep_t->row_by_pk( pk => $borg_id ) },
"Retrieve borg department row via row_by_pk method" );
isa_ok( $dep{borg}, 'Alzabo::Runtime::Row',
"Borg department" );
is( $dep{borg}->select('name'), 'borging',
"Department's name should be 'borging'" );
eval { $dep_t->insert( values => { name => 'will break',
manager_id => 1 } ); };
my $e = $@;
isa_ok( $e, 'Alzabo::Exception::ReferentialIntegrity',
"Exception thrown from attempt to insert a non-existent manager_id into department" );
my %emp;
eval_ok( sub { $emp{bill} = $emp_t->insert( values => { name => 'Big Bill',
dep_id => $borg_id,
smell => 'robotic',
cash => 20.2,
} ) },
"Insert Big Bill into employee table" );
my %data = $emp{bill}->select_hash( 'name', 'smell' );
is( $data{name}, 'Big Bill',
"select_hash - check name key" );
is( $data{smell}, 'robotic',
"select_hash - check smell key" );
is( $emp{bill}->is_live, 1,
"->is_live should be true for real row" );
eval { $emp_t->insert( values => { name => undef,
dep_id => $borg_id,
smell => 'robotic',
cash => 20.2,
} ); };
t/03-runtime.t view on Meta::CPAN
eval { $emp{bill}->update( dep_id => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception thrown from attempt to update dep_id to NULL for an employee" );
{
my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' );
ok( $updated, 'update() did change values' );
ok( ! defined $emp{bill}->select('cash'),
"Bill has no cash" );
}
{
my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' );
ok( ! $updated, 'update() did not change values' );
}
ok( $emp{bill}->select('smell') eq 'hello!',
"smell for bill should be 'hello!'" );
eval { $emp{bill}->update( name => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NotNullable',
"Exception thrown from attempt to update a non-nullable column to NULL" );
eval_ok( sub { $dep{borg}->update( manager_id => $emp{bill}->select('employee_id') ) },
"Set manager_id column for borg department" );
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 )
{
t/03-runtime.t view on Meta::CPAN
$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 ] ) },
"Retrieve 'unit 2' employee via one_row method" );
is( $row->select('name'), 'unit 2',
"Check that the single row returned has the name 'unit 2'" );
}
{
my $row;
eval_ok( sub { $row =
$emp_t->one_row
( where =>
[ $emp_t->column('employee_id'), '=', $emp2_id ],
quote_identifiers => 1,
) },
"Retrieve 'unit 2' employee via one_row method with quote_identifiers" );
is( $row->select('name'), 'unit 2',
"Check that the single row returned has the name 'unit 2'" );
}
my %proj;
$proj{extend} = $proj_t->insert( values => { name => 'Extend',
department_id => $dep{borg}->select('department_id') } );
$proj{embrace} = $proj_t->insert( values => { name => 'Embrace',
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,
t/03-runtime.t view on Meta::CPAN
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" );
}
t/03-runtime.t view on Meta::CPAN
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" );
t/03-runtime.t view on Meta::CPAN
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',
t/03-runtime.t view on Meta::CPAN
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 ],
[ $emp_proj_t, $proj_t ],
[ $s->tables( 'outer_1', 'outer_2' ) ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Logic',
"Exception thrown from join with table map that does not connect" );
eval_ok( sub { @rows = $s->join( join => $emp_t,
where => [ $emp_t->column('employee_id'), '=', 1 ] )->all_rows },
"Join with a single table" );
is( @rows, 1,
"Only one row should be returned" );
is( $rows[0]->select('employee_id'), 1,
"Returned employee should be employee number one" );
{
$s->table('outer_2')->insert( values => { outer_2_name => 'will match something',
outer_2_pk => 1 },
);
$s->table('outer_2')->insert( values => { outer_2_name => 'will match nothing',
outer_2_pk => 99 },
t/03-runtime.t view on Meta::CPAN
);
$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;
}
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]->select('outer_2_name'), 'will match something',
"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;
}
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]->select('outer_2_name'), 'will match something',
"The second row in the first set should have the name 'will match something'" );
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;
}
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]->select('outer_2_name'), 'will match something',
"The second row in the first set should have the name 'will match something'" );
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 $id = $emp{bill}->select('employee_id');
$emp{bill}->delete;
eval { $emp{bill}->select('name'); };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NoSuchRow',
"Exception thrown from attempt to select from deleted row object" );
{
my $row =
$emp_proj_t->row_by_pk
( pk =>
{ employee_id => $id,
project_id => $proj{extend}->select('project_id') } );
is( $row, undef,
"make sure row was deleted by cascading delte" );
}
is( $dep{borg}->select('manager_id'), 1,
"The manager_id for the borg department will be 1 because the object does not the database was changed" );
$dep{borg}->refresh;
my $dep_id = $dep{borg}->select('department_id');
$emp_t->insert( values => { name => 'bob', smell => 'awful', dep_id => $dep_id } );
$emp_t->insert( values => { name => 'rachel', smell => 'horrid', dep_id => $dep_id } );
$emp_t->insert( values => { name => 'al', smell => 'bad', dep_id => $dep_id } );
{
my @emps;
eval_ok ( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('name') ] )->all_rows },
"Select all employee rows with arrayref to order_by" );
is( scalar @emps, 4,
"There should be 4 rows in the employee table" );
is( $emps[0]->select('name'), 'al',
"First row name should be al" );
is( $emps[1]->select('name'), 'bob',
"Second row name should be bob" );
is( $emps[2]->select('name'), 'rachel',
"Third row name should be rachel" );
is( $emps[3]->select('name'), 'unit 2',
"Fourth row name should be 'unit 2'" );
}
{
my @emps;
eval_ok ( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('name') ],
quote_identifiers => 1,
)->all_rows },
"Select all employee rows with arrayref to order_by with quote_identifiers" );
is( scalar @emps, 4,
"There should be 4 rows in the employee table" );
is( $emps[0]->select('name'), 'al',
"First row name should be al" );
is( $emps[1]->select('name'), 'bob',
"Second row name should be bob" );
is( $emps[2]->select('name'), 'rachel',
"Third row name should be rachel" );
is( $emps[3]->select('name'), 'unit 2',
"Fourth row name should be 'unit 2'" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by => $emp_t->column('name') )->all_rows },
"Select all employee rows with column obj to order_by" );
is( scalar @emps, 4,
"There should be 4 rows in the employee table" );
is( $emps[0]->select('name'), 'al',
"First row name should be al" );
is( $emps[1]->select('name'), 'bob',
"Second row name should be bob" );
is( $emps[2]->select('name'), 'rachel',
"Third row name should be rachel" );
is( $emps[3]->select('name'), 'unit 2',
"Fourth row name should be 'unit 2'" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by => [ $emp_t->column('name') ] )->all_rows },
"Select all employee rows with arrayref to order_by" );
is( scalar @emps, 4,
"There should be 4 rows in the employee table" );
is( $emps[0]->select('name'), 'al',
"First row name should be al" );
is( $emps[1]->select('name'), 'bob',
"Second row name should be bob" );
is( $emps[2]->select('name'), 'rachel',
"Third row name should be rachel" );
is( $emps[3]->select('name'), 'unit 2',
"Fourth row name should be 'unit 2'" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('smell') ] )->all_rows },
"Select all employee rows with arrayref to order_by (by smell)" );
is( scalar @emps, 4,
"There should be 4 rows in the employee table" );
is( $emps[0]->select('name'), 'bob',
"First row name should be bob" );
is( $emps[1]->select('name'), 'al',
"Second row name should be al" );
is( $emps[2]->select('name'), 'unit 2',
"Third row name should be 'unit 2'" );
is( $emps[3]->select('name'), 'rachel',
"Fourth row name should be rachel" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('smell'), 'desc' ] )->all_rows },
"Select all employee rows order by smell (descending)" );
is( $emps[0]->select('name'), 'rachel',
"First row name should be rachel" );
is( $emps[1]->select('name'), 'unit 2',
"Second row name should be 'unit 2'" );
is( $emps[2]->select('name'), 'al',
"Third row name should be al" );
is( $emps[3]->select('name'), 'bob',
"Fourth row name should be bob" );
}
eval_ok( sub { $count = $emp_t->row_count },
"Call row_count for employee table" );
is( $count, 4,
"The count should be 4" );
eval_ok( sub { $count = $emp_t->function( select => COUNT( $emp_t->column('employee_id') ) ) },
"Get row count via ->function method" );
is( $count, 4,
"There should still be just 4 rows" );
{
my $one;
eval_ok( sub { $one = $emp_t->function( select => 1 ) },
"Get '1' via ->function method" );
is( $one, 1,
"Getting '1' via ->function should return 1" );
}
{
my $statement;
eval_ok( sub { $statement = $emp_t->select( select => COUNT( $emp_t->column('employee_id') ) ) },
"Get row count via even spiffier new ->select method" );
isa_ok( $statement, 'Alzabo::DriverStatement',
"Return value from Table->select method" );
$count = $statement->next;
is( $count, 4,
"There should still be just 4 rows" );
}
{
my $st;
eval_ok( sub { $st = $emp_t->select( select => 1 ) },
"Get '1' via ->select method" );
is( $st->next, 1,
"Getting '1' via ->select should return 1" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('smell'), 'desc' ],
limit => 2 )->all_rows },
"Get all employee rows with ORDER BY and LIMIT" );
is( scalar @emps, 2,
"This should only return 2 rows" );
is( $emps[0]->select('name'), 'rachel',
"First row should be rachel" );
is( $emps[1]->select('name'), 'unit 2',
"Second row is 'unit 2'" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('smell'), 'desc' ],
limit => [2, 2] )->all_rows },
"Get all employee rows with ORDER BY and LIMIT (with offset)" );
is( scalar @emps, 2,
"This should only return 2 rows" );
is( $emps[0]->select('name'), 'al',
"First row should be al" );
is( $emps[1]->select('name'), 'bob',
"Second row is bob" );
}
$emp_t->set_prefetch( $emp_t->columns( qw( name smell ) ) );
my @p = $emp_t->prefetch;
is( scalar @p, 2,
"Prefetch method should return 2 column names" );
is( scalar ( grep { $_ eq 'name' } @p ), 1,
"One column should be 'name'" );
t/03-runtime.t view on Meta::CPAN
"And the other should be 'smell'" );
is( $emp_t->row_count, 4,
"employee table should have 4 rows" );
{
my @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('smell'), 'desc' ],
limit => [2, 2] )->all_rows;
my $smell = $emps[0]->select('smell');
is( $emp_t->row_count( where => [ $emp_t->column('smell'), '=', $smell ] ), 1,
"Call row_count method with where parameter." );
$emps[0]->delete;
eval { $emps[0]->update( smell => 'kaboom' ); };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NoSuchRow',
"Exception thrown from attempt to update a deleted row" );
my $row_id = $emps[1]->id_as_string;
t/03-runtime.t view on Meta::CPAN
smell => 'c',
dep_id => $dep_id } );
my $eid_c = $emp_t->column('employee_id');
{
my @emps = $emp_t->rows_where( where => [ [ $eid_c, '=', 9000 ],
'or',
[ $eid_c, '=', 9002 ] ] )->all_rows;
@emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps;
is( @emps, 2,
"Do a query with 'or' and count the rows" );
is( $emps[0]->select('employee_id'), 9000,
"First row returned should be employee id 9000" );
is( $emps[1]->select('employee_id'), 9002,
"Second row returned should be employee id 9002" );
}
{
my @emps = $emp_t->rows_where( where => [ [ $emp_t->column('smell'), '!=', 'c' ],
'and',
(
'(',
[ $eid_c, '=', 9000 ],
'or',
[ $eid_c, '=', 9002 ],
')',
),
] )->all_rows;
is( @emps, 1,
"Do another complex query with 'or' and subgroups" );
is( $emps[0]->select('employee_id'), 9000,
"The row returned should be employee id 9000" );
}
{
my @emps = $emp_t->rows_where( where => [ (
'(',
[ $eid_c, '=', 9000 ],
'and',
[ $eid_c, '=', 9000 ],
')',
t/03-runtime.t view on Meta::CPAN
'(',
[ $eid_c, '=', 9000 ],
'and',
[ $eid_c, '=', 9000 ],
')',
),
] )->all_rows;
is( @emps, 1,
"Do another complex query with 'or', 'and' and subgroups" );
is( $emps[0]->select('employee_id'), 9000,
"The row returned should be employee id 9000" );
}
{
my @emps = $emp_t->rows_where( where => [ $eid_c, 'between', 9000, 9002 ] )->all_rows;
@emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps;
is( @emps, 3,
"Select using between should return 3 rows" );
is( $emps[0]->select('employee_id'), 9000,
"First row returned should be employee id 9000" );
is( $emps[1]->select('employee_id'), 9001,
"Second row returned should be employee id 9001" );
is( $emps[2]->select('employee_id'), 9002,
"Third row returned should be employee id 9002" );
}
{
my @emps;
eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(', '(',
[ $eid_c, '=', 9000 ],
')', ')'
] )->all_rows },
"Nested subgroups should be allowed" );
is( @emps, 1,
"Query with nested subgroups should return 1 row" );
is( $emps[0]->select('employee_id'), 9000,
"The row returned should be employee id 9000" );
}
$emp_t->insert( values => { name => 'Smelly',
smell => 'a',
dep_id => $dep_id,
} );
{
my @emps = eval { $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ] )->all_rows };
t/03-runtime.t view on Meta::CPAN
my @emps;
eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(',
[ $emp_t->column('employee_id'), '=', 9000 ],
')',
],
order_by => $emp_t->column('employee_id') )->all_rows },
"Query with subgroup followed by order by" );
is( @emps, 1,
"Query with subgroup followed by order by should return 1 row" );
is( $emps[0]->select('employee_id'), 9000,
"The row returned should be employee id 9000" );
}
my @smells = $emp_t->function( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ],
group_by => $emp_t->column('smell') );
# map smell to count
my %smells = map { $_->[0] => $_->[1] } @smells;
is( @smells, 6,
"Query with group by should return 6 values" );
is( $smells{a}, 2,
"Check count of smell = 'a'" );
is( $smells{b}, 1,
"Check count of smell = 'b'" );
is( $smells{c}, 1,
"Check count of smell = 'c'" );
is( $smells{awful}, 1,
"Check count of smell = 'awful'" );
is( $smells{good}, 1,
"Check count of smell = 'good'" );
is( $smells{horrid}, 1,
"Check count of smell = 'horrid'" );
{
my $statement = $emp_t->select( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ],
group_by => $emp_t->column('smell') );
my @smells = $statement->all_rows;
# map smell to count
%smells = map { $_->[0] => $_->[1] } @smells;
is( @smells, 6,
"Query with group by should return 6 values - via ->select" );
is( $smells{a}, 2,
"Check count of smell = 'a' - via ->select" );
is( $smells{b}, 1,
"Check count of smell = 'b' - via ->select" );
is( $smells{c}, 1,
"Check count of smell = 'c' - via ->select" );
is( $smells{awful}, 1,
"Check count of smell = 'awful' - via ->select" );
is( $smells{good}, 1,
"Check count of smell = 'good' - via ->select" );
is( $smells{horrid}, 1,
"Check count of smell = 'horrid' - via ->select" );
}
@rows = $emp_t->function( select => $emp_t->column('smell'),
where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
order_by => $emp_t->column('smell') );
is( @rows, 4,
"There should only be four rows which have a single character smell" );
is( $rows[0], 'a',
"First smell should be 'a'" );
is( $rows[1], 'a',
"Second smell should be 'a'" );
is( $rows[2], 'b',
"Third smell should be 'b'" );
is( $rows[3], 'c',
"Fourth smell should be 'c'" );
{
my $statement = $emp_t->select( select => $emp_t->column('smell'),
where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
order_by => $emp_t->column('smell') );
my @rows = $statement->all_rows;
is( @rows, 4,
"There should only be four rows which have a single character smell - via ->select" );
is( $rows[0], 'a',
"First smell should be 'a' - via ->select" );
is( $rows[1], 'a',
"Second smell should be 'a' - via ->select" );
is( $rows[2], 'b',
"Third smell should be 'b' - via ->select" );
is( $rows[3], 'c',
"Fourth smell should be 'c' - via ->select" );
}
@rows = $emp_t->function( select => $emp_t->column('smell'),
where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
order_by => $emp_t->column('smell'),
limit => 2,
);
is( @rows, 2,
"There should only be two rows which have a single character smell - with limit" );
is( $rows[0], 'a',
"First smell should be 'a' - with limit" );
is( $rows[1], 'a',
"Second smell should be 'a' - with limit" );
{
my $statement = $emp_t->select( select => $emp_t->column('smell'),
where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
order_by => $emp_t->column('smell'),
limit => 2,
);
my @rows = $statement->all_rows;
is( @rows, 2,
"There should only be two rows which have a single character smell - with limit via ->select" );
is( $rows[0], 'a',
"First smell should be 'a' - with limit via ->select" );
is( $rows[1], 'a',
"Second smell should be 'a' - with limit via ->select" );
}
my $extend_id = $proj{extend}->select('project_id');
my $embrace_id = $proj{embrace}->select('project_id');
foreach ( [ 9000, $extend_id ], [ 9000, $embrace_id ],
[ 9001, $extend_id ], [ 9002, $extend_id ] )
{
$emp_proj_t->insert( values => { employee_id => $_->[0],
project_id => $_->[1] } );
}
# find staffed projects
@rows = $s->function( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name') );
is( @rows, 2,
"Only two projects should be returned from schema->function" );
is( $rows[0][0], 'Embrace',
"First project should be Embrace" );
is( $rows[1][0], 'Extend',
"Second project should be Extend" );
is( $rows[0][1], 1,
"First project should have 1 employee" );
is( $rows[1][1], 3,
"Second project should have 3 employees" );
{
my $statement = $s->select( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name') );
my @rows = $statement->all_rows;
is( @rows, 2,
"Only two projects should be returned from schema->select" );
is( $rows[0][0], 'Embrace',
"First project should be Embrace - via ->select" );
is( $rows[1][0], 'Extend',
"Second project should be Extend - via ->select" );
is( $rows[0][1], 1,
"First project should have 1 employee - via ->select" );
is( $rows[1][1], 3,
"Second project should have 3 employees - via ->select" );
}
@rows = $s->function( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name'),
limit => [1, 1],
);
is( @rows, 1,
"Only one projects should be returned from schema->function - with limit" );
is( $rows[0][0], 'Extend',
"First project should be Extend - with limit" );
is( $rows[0][1], 3,
"First project should have 3 employees - with limit" );
{
my $statement = $s->select( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name'),
limit => [1, 1],
);
my @rows = $statement->all_rows;
is( @rows, 1,
"Only one projects should be returned from schema->select - with limit via ->select" );
is( $rows[0][0], 'Extend',
"First project should be Extend - with limit via ->select" );
is( $rows[0][1], 3,
"First project should have 3 employees - with limit via ->select" );
}
{
my @rows = $s->function( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name'),
order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ] );
is( @rows, 2,
"Only two projects should be returned from schema->function ordered by COUNT(*)" );
is( $rows[0][0], 'Extend',
"First project should be Extend" );
is( $rows[1][0], 'Embrace',
"Second project should be Embrace" );
is( $rows[0][1], 3,
"First project should have 3 employee" );
is( $rows[1][1], 1,
"Second project should have 1 employees" );
}
{
my @rows = $s->function( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name'),
order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ],
having => [ COUNT( $proj_t->column('name') ), '>', 2 ],
);
is( @rows, 1,
"Only one project should be returned from schema->function ordered by COUNT(*) HAVING COUNT(*) > 2" );
is( $rows[0][0], 'Extend',
"First project should be Extend" );
is( $rows[0][1], 3,
"First project should have 3 employee" );
}
{
my @rows;
eval_ok( sub { @rows = $s->function( select => 1,
join => [ $emp_proj_t, $proj_t ],
) },
"Call schema->function with scalar select" );
is( @rows, 4,
"Should return four rows" );
}
{
my $st;
eval_ok( sub { $st = $s->select( select => 1,
join => [ $emp_proj_t, $proj_t ],
) },
"Call schema->select with scalar select" );
my @rows = $st->all_rows;
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 ) ],
t/03-runtime.t view on Meta::CPAN
{
$emp_t->insert( values => { employee_id => $start_id++,
name => $_->[0],
smell => $_->[1],
dep_id => $dep_id } );
}
@rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN',
999_990, 999_996 ],
order_by => [ $emp_t->columns( 'name', 'smell' ) ] )->all_rows;
is( $rows[0]->select('name'), 'OB1',
"First row name should be OB1" );
is( $rows[0]->select('smell'), 'bad',
"First row smell should be bad" );
is( $rows[1]->select('name'), 'OB1',
"Second row name should be OB1" );
is( $rows[1]->select('smell'), 'worse',
"Second row smell should be bad" );
is( $rows[2]->select('name'), 'OB2',
"Third row name should be OB2" );
is( $rows[2]->select('smell'), 'bad',
"Third row smell should be bad" );
is( $rows[3]->select('name'), 'OB2',
"Fourth row name should be OB2" );
is( $rows[3]->select('smell'), 'worse',
"Fourth row smell should be worse" );
is( $rows[4]->select('name'), 'OB3',
"Fifth row name should be OB3" );
is( $rows[4]->select('smell'), 'awful',
"Fifth row smell should be awful" );
is( $rows[5]->select('name'), 'OB3',
"Sixth row name should be OB3" );
is( $rows[5]->select('smell'), 'bad',
"Sixth row smell should be bad" );
@rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN',
999_990, 999_996 ],
order_by => [ $emp_t->column('name'), 'desc', $emp_t->column('smell'), 'asc' ] )->all_rows;
is( $rows[0]->select('name'), 'OB3',
"First row name should be OB3" );
is( $rows[0]->select('smell'), 'awful',
"First row smell should be awful" );
is( $rows[1]->select('name'), 'OB3',
"Second row name should be OB3" );
is( $rows[1]->select('smell'), 'bad',
"Second row smell should be bad" );
is( $rows[2]->select('name'), 'OB2',
"Third row name should be OB2" );
is( $rows[2]->select('smell'), 'bad',
"Third row smell should be bad" );
is( $rows[3]->select('name'), 'OB2',
"Fourth row name should be OB2" );
is( $rows[3]->select('smell'), 'worse',
"Fourth row smell should be worse" );
is( $rows[4]->select('name'), 'OB1',
"Fifth row name should be OB1" );
is( $rows[4]->select('smell'), 'bad',
"Fifth row smell should be bad" );
is( $rows[5]->select('name'), 'OB1',
"Sixth row name should be OB1" );
is( $rows[5]->select('smell'), 'worse',
"Sixth row smell should be worse" );
if ( $rdbms eq 'mysql' )
{
my $emp;
eval_ok( sub { $emp = $emp_t->insert( values => { name => UNIX_TIMESTAMP(),
dep_id => $dep_id } ) },
"Insert using SQL function UNIX_TIMESTAMP()" );
like( $emp->select('name'), qr/\d+/,
"Name should be all digits (unix timestamp)" );
eval_ok( sub { $emp->update( name => LOWER('FOO') ) },
"Do update using SQL function LOWER()" );
is( $emp->select('name'), 'foo',
"Name should be 'foo'" );
eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp->select('name'), 'FooFooFoo',
"Name should be 'FooFooFoo'" );
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');
@rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('abathraspus') ] )->all_rows;
is( @rows, 0,
"Make sure that fulltext search doesn't give a false positive" );
@rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] )->all_rows;
is( @rows, 1,
"Make sure that fulltext search for snuffleupagus returns 1 row" );
is( $rows[0]->select('employee_id'), $snuffle_id,
"Make sure that the returned row is snuffleupagus" );
my $rows = $emp_t->function( select => [ $emp_t->column('employee_id'), MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ],
where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] );
my ($id, $score) = @$rows;
is( $id, $snuffle_id,
"Returned row should still be snuffleupagus" );
like( $score, qr/\d+(?:\.\d+)?/,
"Returned score should be some sort of number (integer or floating point)" );
ok( $score > 0,
"The score should be greater than 0 because the match was successful" );
eval_ok( sub { @rows = $emp_t->all_rows( order_by => [ IF( 'employee_id < 100',
$emp_t->column('employee_id'),
$emp_t->column('smell') ),
$emp_t->column('employee_id'),
],
)->all_rows },
"Order by IF() function" );
is( @rows, 16,
"Seventeen rows should have been returned" );
is( $rows[0]->select('employee_id'), 3,
"First row should be id 3" );
is( $rows[-1]->select('employee_id'), 999993,
"Last row should be id 999993" );
eval_ok( sub { @rows = $emp_t->all_rows( order_by => RAND() )->all_rows },
"order by RAND()" );
is ( @rows, 16,
"This should return 16 rows" );
}
elsif ( $rdbms eq 'pg' )
{
my $emp;
eval_ok( sub { $emp = $emp_t->insert( values => { name => NOW(),
dep_id => $dep_id } ) },
"Do insert using SQL function NOW()" );
like( $emp->select('name'), qr/\d+/,
"Name should be all digits (Postgres timestamp)" );
eval_ok( sub { $emp->update( name => LOWER('FOO') ) },
"Do update using SQL function LOWER()" );
is( $emp->select('name'), 'foo',
"Name should be 'foo'" );
eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp->select('name'), 'FooFooFoo',
"Name should be 'FooFooFoo'" );
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");
is( $p_emp->is_live, 0,
"potential_row should ! ->is_live" );
is( $p_emp->select('smell'), 'grotesque',
"Potential Employee should have default smell, 'grotesque'" );
{
my $updated = $p_emp->update( cash => undef, smell => 'hello!' );
ok( $updated, 'update() did change values' );
ok( ! defined $p_emp->select('cash'),
"Potential Employee cash column is not defined" );
}
{
my $updated = $p_emp->update( cash => undef, smell => 'hello!' );
ok( ! $updated, 'update() did not change values' );
}
is( $p_emp->select('smell'), 'hello!',
"smell for employee should be 'hello!' after update" );
$p_emp->update( name => 'Ilya' );
is( $p_emp->select('name'), 'Ilya',
"New employee got a name" );
$p_emp->update( dep_id => $dep_id );
is( $p_emp->select('dep_id'), $dep_id,
"New employee got a department" );
eval { $p_emp->update( wrong => 'column' ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception thrown from attempt to update a column which doesn't exist" );
eval { $p_emp->update( name => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NotNullable',
"Exception thrown from attempt to update a non-NULLable column in a potential row to null" );
eval_ok( sub { $p_emp->make_live( values => { smell => 'cottony' } ) },
"Make potential row live");
is( $p_emp->select('name'), 'Ilya',
"Formerly potential employee row object should have same name as before" );
is( $p_emp->select('smell'), 'cottony',
"Formerly potential employee row object should have new smell of 'cottony'" );
eval_ok ( sub { $p_emp->delete },
"Delete new employee" );
eval_ok( sub { $p_emp = $emp_t->potential_row( values => { cash => 100 } ) },
"Create potential row object and set some fields ");
is( $p_emp->select('cash'), 100,
"Employee cash should be 100" );
eval { $emp_t->rows_where( where => [ $eid_c, '=', 9000,
$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
t/03-runtime.t view on Meta::CPAN
$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'");
}
{
my $foo = $emp_t->column('employee_id')->alias( as => 'foo' );
my $st = $emp_t->select( select => $foo );
my %h = $st->next_as_hash;
is( exists $h{foo}, 1,
"next_as_hash should return a hash with a 'foo' key" );
}
$s->disconnect;
}
t/07-methodmaker.t view on Meta::CPAN
eval { $s->Location_t->insert( values => { location_id => 666,
location => 'post_die' } ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by post_insert" );
is( $e->error, 'POST INSERT TEST',
"pre_insert error message should be POST INSERT TEST" );
my $tweaked = $s->Location_t->insert( values => { location_id => 54321,
location => 'insert tweak me' } );
is ( $tweaked->select('location'), 'insert tweaked',
"pre_insert should change the value of location to 'insert tweaked'" );
eval { $tweaked->update( location => 'pre_die' ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown from pre_update" );
is( $e->error, 'PRE UPDATE TEST',
"pre_update error message should be PRE UPDATE TEST" );
eval { $tweaked->update( location => 'post_die' ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by post_update" );
is( $e->error, 'POST UPDATE TEST',
"post_update error message should be POST UPDATE TEST" );
$tweaked->update( location => 'update tweak me' );
is ( $tweaked->select('location'), 'update tweaked',
"pre_update should change the value of location to 'update tweaked'" );
eval { $tweaked->select('pre_sel_die') };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by pre_select" );
is( $e->error, 'PRE SELECT TEST',
"pre_select error message should be PRE SELECT TEST" );
$tweaked->update( location => 'post_sel_die' );
eval { $tweaked->select('location') };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by post_select" );
is( $e->error, 'POST SELECT TEST',
"post_select error message should be POST SELECT TEST" );
eval { $tweaked->select_hash('location') };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by post_select" );
is( $e->error, 'POST SELECT TEST',
"post_select error message should be POST SELECT TEST" );
$tweaked->update( location => 'select tweak me' );
is( $tweaked->select('location'), 'select tweaked',
"post_select should change the value of location to 'select tweaked'" );
my %d = $tweaked->select_hash('location');
is( $d{location}, 'select tweaked',
"post_select_hash should change the value of location to 'select tweaked'" );
$s->ToiletType_t->insert( values => { toilet_type_id => 1,
material => 'porcelain',
quality => 5 } );
my $t = $s->Toilet_t->insert( values => { toilet_id => 1,
toilet_type_id => 1 } );
is( $t->material, 'porcelain',
"New toilet's material method should return 'porcelain'" );
is( $t->quality, 5,
t/07-methodmaker.t view on Meta::CPAN
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by pre_update" );
eval { $p_row->update( location => 'post_die' ); };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by post_update" );
$p_row->update( location => 'update tweak me' );
is ( $p_row->select('location'), 'update tweaked',
"pre_update should change the value of location to 'update tweaked'" );
eval { $p_row->select('pre_sel_die') };
$e = $@;
isa_ok( $e, 'Alzabo::Exception',
"Exception thrown by pre_select" );
$p_row->update( location => 'select tweak me' );
is( $p_row->select('location'), 'select tweaked',
"post_select should change the value of location to 'select tweaked'" );
%d = $p_row->select_hash('location');
is( $d{location}, 'select tweaked',
"post_select_hash should change the value of location to 'select tweaked'" );
$p_row->make_live;
is( $p_row->location_id, 999,
"Check that live row has same location id" );
my $alias = $s->Toilet_t->alias;
can_ok( $alias, 'toilet_id_c' );
is( $alias->toilet_id_c->name, $s->Toilet_t->toilet_id_c->name,
"Alias column has the same name as real table's column" );
t/07-methodmaker.t view on Meta::CPAN
my $p = shift;
Alzabo::Exception->throw( error => "PRE INSERT TEST" ) if $p->{values}->{location} eq 'pre_die';
$p->{values}->{location} = 'insert tweaked' if $p->{values}->{location} eq 'insert tweak me';
}
sub post_insert
{
my $self = shift;
my $p = shift;
Alzabo::Exception->throw( error => "POST INSERT TEST" ) if $p->{row}->select('location') eq 'post_die';
}
}
{
package Alzabo::MM::Test::Row::Location;
sub pre_update
{
my $self = shift;
my $p = shift;
Alzabo::Exception->throw( error => "PRE UPDATE TEST" ) if $p->{location} && $p->{location} eq 'pre_die';
t/07-methodmaker.t view on Meta::CPAN
$p->{location} = 'update tweaked' if $p->{location} && $p->{location} eq 'update tweak me';
}
sub post_update
{
my $self = shift;
my $p = shift;
Alzabo::Exception->throw( error => "POST UPDATE TEST" ) if $p->{location} && $p->{location} eq 'post_die';
}
sub pre_select
{
my $self = shift;
my $cols = shift;
Alzabo::Exception->throw( error => "PRE SELECT TEST" ) if grep { $_ eq 'pre_sel_die' } @$cols;
}
sub post_select
{
my $self = shift;
my $data = shift;
Alzabo::Exception->throw( error => "POST SELECT TEST" ) if exists $data->{location} && $data->{location} eq 'post_sel_die';
$data->{location} = 'select tweaked' if exists $data->{location} && $data->{location} eq 'select tweak me';
}
sub pre_delete
{
my $self = shift;
Alzabo::Exception->throw( error => "PRE DELETE TEST" ) if $self->select('location') eq 'pre_del_die';
}
sub post_delete
{
my $self = shift;
# Alzabo::Exception->throw( error => "POST DELETE TEST" );
}
}
1;
t/09-storable.t view on Meta::CPAN
smell => 'bb',
dep_id => 1 } );
my $ser;
eval_ok( sub { my $row = $emp_t->row_by_pk( pk => 98765 );
$ser = Storable::freeze($row) },
"Freeze employee" );
my $eid;
eval_ok( sub { my $row = Storable::thaw($ser);
$eid = $row->select('employee_id') },
"Thaw employee" );
is( $eid, 98765,
"Employee survived freeze & thaw" );
eval_ok( sub { my $row = $emp_t->row_by_pk( pk => 98765 );
$ser = Storable::nfreeze($row) },
"NFreeze employee" );
my $smell;
eval_ok( sub { my $row = Storable::thaw($ser);
$smell = $row->select('smell') },
"Thaw employee" );
is( $smell, 'bb',
"Employee survived nfreeze & thaw" );
eval_ok( sub { my $p_row = $emp_t->potential_row( values => { name => 'Alice' } );
$ser = Storable::freeze($p_row) },
"Freeze potential employee" );
my $name;
eval_ok( sub { my $p_row = Storable::thaw($ser);
$name = $p_row->select('name') },
"Thaw potential employee" );
is( $name, 'Alice',
"Potential employee survived freeze & thaw" );
}
( run in 1.294 second using v1.01-cache-2.11-cpan-49f99fa48dc )