X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FSQLMaker%2FLimitDialects.pm;h=76399887da4d66d04eb799420f9d4f9a52aba35c;hb=fcf32d045;hp=dd6bc6d27ebedb7b913039d15bf27ead2206ee5c;hpb=a66b662c77c825952732b8ff4af9d0d1a2ee996d;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/SQLMaker/LimitDialects.pm b/lib/DBIx/Class/SQLMaker/LimitDialects.pm index dd6bc6d..7639988 100644 --- a/lib/DBIx/Class/SQLMaker/LimitDialects.pm +++ b/lib/DBIx/Class/SQLMaker/LimitDialects.pm @@ -100,7 +100,11 @@ sub _RowNumberOver { # make up an order if none exists my $requested_order = (delete $rs_attrs->{order_by}) || $self->_rno_default_order; + + # the order binds (if any) will need to go at the end of the entire inner select + local $self->{order_bind}; my $rno_ord = $self->_order_by ($requested_order); + push @{$self->{select_bind}}, @{$self->{order_bind}}; # this is the order supplement magic my $mid_sel = $sq_attrs->{selection_outer}; @@ -117,10 +121,10 @@ sub _RowNumberOver { } # and this is order re-alias magic - for ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) { - for my $col (keys %$_) { + for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) { + for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}} ) { my $re_col = quotemeta ($col); - $rno_ord =~ s/$re_col/$_->{$col}/; + $rno_ord =~ s/$re_col/$map->{$col}/; } } @@ -275,7 +279,7 @@ EOS $rs_attrs->{order_by} and $rs_attrs->{_rsroot_rsrc}->storage->_order_by_is_stable( - $rs_attrs->{from}, $rs_attrs->{order_by} + @{$rs_attrs}{qw/from order_by where/} ) ) { push @{$self->{limit_bind}}, [ $self->__total_bindtype => $offset + $rows ], [ $self->__offset_bindtype => $offset + 1 ]; @@ -327,10 +331,11 @@ sub _prep_for_skimming_limit { if ($sq_attrs->{order_by_requested}) { $self->throw_exception ( 'Unable to safely perform "skimming type" limit with supplied unstable order criteria' - ) unless $rs_attrs->{_rsroot_rsrc}->schema->storage->_order_by_is_stable( + ) unless ($rs_attrs->{_rsroot_rsrc}->schema->storage->_order_by_is_stable( $rs_attrs->{from}, - $requested_order - ); + $requested_order, + $rs_attrs->{where}, + )); $inner_order = $requested_order; } @@ -378,23 +383,11 @@ sub _prep_for_skimming_limit { # Whatever order bindvals there are, they will be realiased and # reselected, and need to show up at end of the initial inner select push @{$self->{select_bind}}, @{$self->{order_bind}}; - - # if this is a part of something bigger, we need to add back all - # the extra order_by's, as they may be relied upon by the outside - # of a prefetch or something - if ($rs_attrs->{_is_internal_subuery}) { - $sq_attrs->{selection_outer} .= sprintf ", $extra_order_sel->{$_} AS $_" - for sort - { $extra_order_sel->{$a} cmp $extra_order_sel->{$b} } - grep { $_ !~ /[^\w\-]/ } # ignore functions - keys %$extra_order_sel - ; - } } # and this is order re-alias magic for my $map ($sq_attrs->{order_supplement}, $sq_attrs->{outer_renames}) { - for my $col (sort { $map->{$a} cmp $map->{$b} } keys %{$map||{}}) { + for my $col (sort { (length $b) <=> (length $a) } keys %{$map||{}}) { my $re_col = quotemeta ($col); $_ =~ s/$re_col/$map->{$col}/ for ($sq_attrs->{order_by_middle}, $sq_attrs->{order_by_requested}); @@ -445,8 +438,7 @@ sub _Top { $lim->{order_by_middle}, ) if $offset; - $sql = sprintf ('SELECT TOP %u %s FROM ( %s ) %s %s', - $rows, + $sql = sprintf ('SELECT %s FROM ( %s ) %s %s', $lim->{selection_outer}, $sql, $lim->{quoted_rs_alias}, @@ -502,12 +494,11 @@ sub _FetchFirst { ) if $offset; - $sql = sprintf ('SELECT %s FROM ( %s ) %s %s FETCH FIRST %u ROWS ONLY', + $sql = sprintf ('SELECT %s FROM ( %s ) %s %s', $lim->{selection_outer}, $sql, $lim->{quoted_rs_alias}, $lim->{order_by_requested}, - $rows, ) if $offset and ( $lim->{order_by_requested} or $lim->{selection_middle} ne $lim->{selection_outer} ); @@ -534,9 +525,9 @@ sub _RowCountOrGenericSubQ { return $self->_GenericSubQ(@_) if $offset; - return sprintf <<"EOF", $rows, $sql; + return sprintf <<"EOF", $rows, $sql, $self->_parse_rs_attrs( $rs_attrs ); SET ROWCOUNT %d -%s +%s %s SET ROWCOUNT 0 EOF } @@ -554,8 +545,11 @@ This is the most evil limit "dialect" (more of a hack) for I stupid databases. It works by ordering the set by some unique column, and calculating the amount of rows that have a less-er value (thus emulating a L-like index). Of course this implies the set can only be ordered by a single unique -column. Also note that this technique can be and often is B. +column. + +Also note that this technique can be and often is B. You +may have much better luck using L +instead. Currently used by B, due to lack of any other option. @@ -568,6 +562,7 @@ sub _GenericSubQ { my ($first_order_by) = do { local $self->{quote_char}; + local $self->{order_bind}; map { ref $_ ? $_->[0] : $_ } $self->_order_by_chunks ($rs_attrs->{order_by}) } or $self->throw_exception ( 'Generic Subquery Limit does not work on resultsets without an order. Provide a single, ' @@ -590,12 +585,21 @@ sub _GenericSubQ { "Generic Subquery Limit first order criteria '$first_ord_col' must be unique" ) unless $root_rsrc->_identifying_column_set([$first_ord_col]); - my $sq_attrs = $self->_subqueried_limit_attrs ($sql, $rs_attrs); + my $sq_attrs = do { + # perform the mangling only using the very first order crietria + # (the one we care about) + local $rs_attrs->{order_by} = $first_order_by; + $self->_subqueried_limit_attrs ($sql, $rs_attrs); + }; my $cmp_op = $direction eq 'desc' ? '>' : '<'; my $count_tbl_alias = 'rownum__emulation'; - my $order_sql = $self->_order_by (delete $rs_attrs->{order_by}); + my ($order_sql, @order_bind) = do { + local $self->{order_bind}; + my $s = $self->_order_by (delete $rs_attrs->{order_by}); + ($s, @{$self->{order_bind}}); + }; my $group_having_sql = $self->_parse_rs_attrs($rs_attrs); my $in_sel = $sq_attrs->{selection_inner}; @@ -620,6 +624,11 @@ sub _GenericSubQ { ; } + # even though binds in order_by make no sense here (the rs needs to be + # ordered by a unique column first) - pass whatever there may be through + # anyway + push @{$self->{limit_bind}}, @order_bind; + return sprintf (" SELECT $sq_attrs->{selection_outer} FROM ( @@ -685,6 +694,7 @@ sub _subqueried_limit_attrs { my $sql_alias = (ref $s) eq 'HASH' ? $s->{-as} : undef; push @sel, { + arg => $s, sql => $sql_sel, unquoted_sql => do { local $self->{quote_char}; @@ -699,7 +709,9 @@ sub _subqueried_limit_attrs { , }; - $in_sel_index->{$sql_sel}++; + # anything with a placeholder in it needs re-selection + $in_sel_index->{$sql_sel}++ unless $sql_sel =~ / (?: ^ | \W ) \? (?: \W | $ ) /x; + $in_sel_index->{$self->_quote ($sql_alias)}++ if $sql_alias; # record unqualified versions too, so we do not have @@ -715,11 +727,14 @@ sub _subqueried_limit_attrs { # unless we are dealing with the current source alias # (which will transcend the subqueries as it is necessary # for possible further chaining) + # same for anything we do not recognize my ($sel, $renamed); for my $node (@sel) { push @{$sel->{original}}, $node->{sql}; if ( + ! $in_sel_index->{$node->{sql}} + or $node->{as} =~ / (?{unquoted_sql} =~ / (?{inner}}, $node->{sql}; - push @{$sel->{outer}}, $self->_quote ($node->{as}); + push @{$sel->{outer}}, $self->_quote (ref $node->{arg} ? $node->{as} : $node->{arg}); } } @@ -746,7 +761,7 @@ sub _subqueried_limit_attrs { next if $in_sel_index->{$chunk}; $extra_order_sel->{$chunk} ||= $self->_quote ( - 'ORDER__BY__' . scalar keys %{$extra_order_sel||{}} + 'ORDER__BY__' . sprintf '%03d', scalar keys %{$extra_order_sel||{}} ); }