Author: jzucker
Date: Thu Mar 4 18:53:06 2004
New Revision: 186
Modified:
dbi/trunk/lib/DBI/SQL/Nano.pm
Log:
Nano now supports ORDER BY. Locking is next, then the book, I swear, the book is next
:-)
Modified: dbi/trunk/lib/DBI/SQL/Nano.pm
==============================================================================
--- dbi/trunk/lib/DBI/SQL/Nano.pm (original)
+++ dbi/trunk/lib/DBI/SQL/Nano.pm Thu Mar 4 18:53:06 2004
@@ -72,7 +72,14 @@
$self->{column_names} = parse_comma_list($1) if $1;
die "Can't find columns!\n" unless $self->{column_names};
$self->{table_name} = $2;
- $self->{where_clause} = $self->parse_where_clause($4) if $4;
+ if ( my $clauses = $4) {
+ if ($clauses =~ /^(.*)\s+ORDER\s+BY\s+(.*)$/is) {
+ $clauses = $1;
+ $self->{order_clause} = $self->parse_order_clause($2);
+ }
+ $self->{where_clause}=$self->parse_where_clause($clauses)
+ if $clauses;
+ }
};
/^\s*INSERT\s+INTO\s+(\S+)\s*(\((.*?)\))?\s*VALUES\s*\((.+)\)/is
&&do{
@@ -100,6 +107,16 @@
and $self->{table_name};
return $self;
}
+sub parse_order_clause {
+ my($self,$str) = @_;
+ my @clause = split /\s+/,$str;
+ return { $clause[0] => 'ASC' } if @clause == 1;
+ die "Bad ORDER BY clause '$str'!\n" if @clause > 2;
+ $clause[1] ||= '';
+ return { $clause[0] => uc $clause[1] } if $clause[1] =~ /^ASC$/i
+ or $clause[1] =~ /^DESC$/i;
+ die "Bad ORDER BY clause '$clause[1]'!\n";
+}
sub parse_coldef_list { # check column definitions
my @col_defs;
for ( split',',shift ) {
@@ -290,6 +307,41 @@
if $self->{fetched_from_key};
}
}
+ if ( $self->{order_clause} ) {
+ my( $sort_col, $desc ) = each %{$self->{order_clause}};
+ undef $desc unless $desc eq 'DESC';
+ my @sortCols = ( $self->column_nums($table,$sort_col) );
+ my($c, $d, $colNum);
+ my $sortFunc = sub {
+ my $result;
+ $i = 0;
+ do {
+ $colNum = $sortCols[$i++];
+ # $desc = $sortCols[$i++];
+ $c = $a->[$colNum];
+ $d = $b->[$colNum];
+ if (!defined($c)) {
+ $result = defined $d ? -1 : 0;
+ } elsif (!defined($d)) {
+ $result = 1;
+ } elsif ( DBI::looks_like_number($c,$d) ) {
+ $result = ($c <=> $d);
+ } else {
+ if ($self->{"case_fold"}) {
+ $result = lc $c cmp lc $d || $c cmp $d;
+ }
+ else {
+ $result = $c cmp $d;
+ }
+ }
+ if ($desc) {
+ $result = -$result;
+ }
+ } while (!$result && $i < @sortCols);
+ $result;
+ };
+ @rows = (sort $sortFunc @rows);
+ }
(scalar(@rows), scalar @{$self->{column_names}}, [EMAIL PROTECTED]);
}
sub UPDATE ($$$) {
@@ -544,6 +596,7 @@
| DELETE FROM <table_name> [<where_clause>]
| UPDATE <table_name> SET <set_clause> <where_clause>
| SELECT <select_col_list> FROM <table_name> [<where_clause>]
+ [<order_clause>]
the optional IF EXISTS clause ::=
* similar to MySQL - prevents errors when trying to drop
@@ -604,6 +657,11 @@
< > >= <= = <> LIKE CLIKE IS
* CLIKE is a case insensitive LIKE
+ order_clause ::= column_name [ASC|DESC]
+ * a single column optional ORDER BY clause is supported
+ * as in standard SQL, if neither ASC (ascending) nor
+ DESC (descending) is specified, ASC becomes the default
+
=head1 ACKNOWLEDGEMENTS
Tim Bunce provided the original idea for this module, helped me out of the tangled
trap of namespace, and provided help and advice all along the way. Although I wrote
it from the ground up, it is based on Jochen Weidmann's orignal design of
SQL::Statement, so much of the credit for the API goes to him.
