-
package SQL::Abstract;
=head1 NAME
use Carp;
use strict;
-our $VERSION = '1.22';
+our $VERSION = '1.23';
+#XXX don't understand this below, leaving it for someone else. did bump the $VERSION --groditi
our $REVISION = '$Id$';
our $AUTOLOAD;
=head2 update($table, \%fieldvals, \%where)
This takes a table, hashref of field/value pairs, and an optional
-hashref WHERE clause. It returns an SQL UPDATE function and a list
+hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
of bind values.
=cut
=head2 select($table, \@fields, \%where, \@order)
This takes a table, arrayref of fields (or '*'), optional hashref
-WHERE clause, and optional arrayref order by, and returns the
+L<WHERE clause|/WHERE CLAUSES>, and optional array or hash ref L<ORDER BY clause|/ORDER BY CLAUSES>, and returns the
corresponding SQL SELECT statement and list of bind values.
=cut
=head2 delete($table, \%where)
-This takes a table name and optional hashref WHERE clause.
+This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
It returns an SQL DELETE statement and list of bind values.
=cut
sub _order_by {
my $self = shift;
- my $ref = ref $_[0];
-
- my @vals = $ref eq 'ARRAY' ? @{$_[0]} :
- $ref eq 'SCALAR' ? $_[0] :
- $ref eq '' ? $_[0] :
- puke "Unsupported data struct $ref for ORDER BY";
+ my $ref = ref $_[0] || '';
+
+ my $_order_hash = sub {
+ local *__ANON__ = '_order_by_hash';
+ my ($col, $order);
+ my $hash = shift; # $_ was failing in some cases for me --groditi
+ if ( $col = $hash->{'-desc'} ) {
+ $order = 'DESC'
+ } elsif ( $col = $hash->{'-asc'} ) {
+ $order = 'ASC';
+ } else {
+ puke "Hash must have a key of '-desc' or '-asc' for ORDER BY";
+ }
+ return $self->_quote($col) . " $order";
+
+ };
+
+ my @vals;
+ if ($ref eq 'ARRAY') {
+ foreach (@{ $_[0] }) {
+ my $ref = ref $_;
+ if (!$ref || $ref eq 'SCALAR') {
+ push @vals, $self->_quote($_);
+ } elsif ($ref eq 'HASH') {
+ push @vals, $_order_hash->($_);
+ } else {
+ puke "Unsupported nested data struct $ref for ORDER BY";
+ }
+ }
+ } elsif ($ref eq 'HASH') {
+ push @vals, $_order_hash->($_[0]);
+ } elsif (!$ref || $ref eq 'SCALAR') {
+ push @vals, $self->_quote($_[0]);
+ } else {
+ puke "Unsupported data struct $ref for ORDER BY";
+ }
- my $val = join ', ', map { $self->_quote($_) } @vals;
+ my $val = join ', ', @vals;
return $val ? $self->_sqlcase(' order by')." $val" : '';
}
operator which adds an additional set of parens, to create a subquery.
For example, to get something like this:
- $stmt = WHERE user = ? AND ( workhrs > ? OR geo = ? )
+ $stmt = "WHERE user = ? AND ( workhrs > ? OR geo = ? )";
@bind = ('nwiger', '20', 'ASIA');
You would do:
dynamically-generating SQL and could just hardwire it into your
script.
+=head1 ORDER BY CLAUSES
+
+Some functions take an order by clause. This can either be a scalar (just a
+column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
+or an array of either of the two previous forms. Examples:
+
+ Given | Will Generate
+ ----------------------------------------------------------
+ \'colA DESC' | ORDER BY colA DESC
+ 'colA' | ORDER BY colA
+ [qw/colA colB/] | ORDER BY colA, colB
+ {-asc => 'colA'} | ORDER BY colA ASC
+ {-desc => 'colB'} | ORDER BY colB DESC
+ [ |
+ {-asc => 'colA'}, | ORDER BY colA ASC, colB DESC
+ {-desc => 'colB'} |
+ ] |
+ [colA => {-asc => 'colB'}] | ORDER BY colA, colB ASC
+ ==========================================================
+
=head1 PERFORMANCE
Thanks to some benchmarking by Mark Stosberg, it turns out that
this module. Unfortunately, most of them submitted bugs via CPAN
so I have no idea who they are! But the people I do know are:
+ Ash Berlin (order_by hash term support)
+ Matt Trout (DBIx::Class support)
Mark Stosberg (benchmarking)
Chas Owens (initial "IN" operator support)
Philip Collins (per-field SQL functions)
Eric Kolve (hashref "AND" support)
Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
Dan Kubb (support for "quote_char" and "name_sep")
- Matt Trout (DBIx::Class support)
+ Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
Thanks!
-=head1 BUGS
-
-If found, please DO NOT submit anything via C<rt.cpan.org> - that
-just causes me a ton of work. Email me a patch (or script demonstrating
-the problem) to the below address, and include the VERSION you're using.
-
=head1 SEE ALSO
-L<DBIx::Abstract>, L<DBI|DBI>, L<CGI::FormBuilder>, L<HTML::QuickTable>
+L<DBIx::Class>, L<DBIx::Abstract>, L<CGI::FormBuilder>, L<HTML::QuickTable>.
=head1 AUTHOR
-Copyright (c) 2001-2006 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
+Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
+
+This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
For support, your best bet is to try the C<DBIx::Class> users mailing list.
While not an official support venue, C<DBIx::Class> makes heavy use of
projects / scpubgit/Q-Branch.git / blobdiff