# 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};
}
# 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}/;
}
}
$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 ];
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;
}
for my $ch ($self->_order_by_chunks ($inner_order)) {
$ch = $ch->[0] if ref $ch eq 'ARRAY';
- $ch =~ s/\s+ ( ASC|DESC ) \s* $//ix;
- my $dir = uc ($1||'ASC');
- push @out_chunks, \join (' ', $ch, $dir eq 'ASC' ? 'DESC' : 'ASC' );
+ ($ch, my $is_desc) = $self->_split_order_chunk($ch);
+
+ # !NOTE! outside chunks come in reverse order ( !$is_desc )
+ push @out_chunks, { ($is_desc ? '-asc' : '-desc') => \$ch };
}
$sq_attrs->{order_by_middle} = $self->_order_by (\@out_chunks);
# 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});
return $sql;
}
-=head2 RowCountOrGenericSubQ
-
-This is not exactly a limit dialect, but more of a proxy for B<Sybase ASE>.
-If no $offset is supplied the limit is simply performed as:
-
- SET ROWCOUNT $limit
- SELECT ...
- SET ROWCOUNT 0
-
-Otherwise we fall back to L</GenericSubQ>
-
-=cut
-
-sub _RowCountOrGenericSubQ {
- my $self = shift;
- my ($sql, $rs_attrs, $rows, $offset) = @_;
-
- return $self->_GenericSubQ(@_) if $offset;
-
- return sprintf <<"EOF", $rows, $sql;
-SET ROWCOUNT %d
-%s
-SET ROWCOUNT 0
-EOF
-}
-
=head2 GenericSubQ
SELECT * FROM (
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</RowNum>-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<excruciatingly
-slow>.
+column.
+
+Also note that this technique can be and often is B<excruciatingly slow>. You
+may have much better luck using L<DBIx::Class::ResultSet/software_limit>
+instead.
Currently used by B<Sybase ASE>, due to lack of any other option.
my ($self, $sql, $rs_attrs, $rows, $offset) = @_;
my $root_rsrc = $rs_attrs->{_rsroot_rsrc};
- my $root_tbl_name = $root_rsrc->name;
- my ($first_order_by) = do {
- local $self->{quote_char};
- 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, '
- . 'unique-column order criteria.'
+ # Explicitly require an order_by
+ # GenSubQ is slow enough as it is, just emulating things
+ # like in other cases is not wise - make the user work
+ # to shoot their DBA in the foot
+ my $supplied_order = delete $rs_attrs->{order_by} or $self->throw_exception (
+ 'Generic Subquery Limit does not work on resultsets without an order. Provide a stable, '
+ . 'root-table-based order criteria.'
);
- $first_order_by =~ s/\s+ ( ASC|DESC ) \s* $//ix;
- my $direction = lc ($1 || 'asc');
+ my $usable_order_ci = $root_rsrc->storage->_main_source_order_by_portion_is_stable(
+ $root_rsrc,
+ $supplied_order,
+ $rs_attrs->{where},
+ ) or $self->throw_exception(
+ 'Generic Subquery Limit can not work with order criteria based on sources other than the current one'
+ );
- my ($first_ord_alias, $first_ord_col) = $first_order_by =~ /^ (?: ([^\.]+) \. )? ([^\.]+) $/x;
+###
+###
+### we need to know the directions after we figured out the above - reextract *again*
+### this is eyebleed - trying to get it to work at first
+ my @order_bits = do {
+ local $self->{quote_char};
+ local $self->{order_bind};
+ map { ref $_ ? $_->[0] : $_ } $self->_order_by_chunks ($supplied_order)
+ };
- $self->throw_exception(sprintf
- "Generic Subquery Limit order criteria can be only based on the root-source '%s'"
- . " (aliased as '%s')", $root_rsrc->source_name, $rs_attrs->{alias},
- ) if ($first_ord_alias and $first_ord_alias ne $rs_attrs->{alias});
+ # truncate to what we'll use
+ $#order_bits = ( (keys %$usable_order_ci) - 1 );
- $first_ord_alias ||= $rs_attrs->{alias};
+ # @order_bits likely will come back quoted (due to how the prefetch
+ # rewriter operates
+ # Hence supplement the column_info lookup table with quoted versions
+ if ($self->quote_char) {
+ $usable_order_ci->{$self->_quote($_)} = $usable_order_ci->{$_}
+ for keys %$usable_order_ci;
+ }
- $self->throw_exception(
- "Generic Subquery Limit first order criteria '$first_ord_col' must be unique"
- ) unless $root_rsrc->_identifying_column_set([$first_ord_col]);
+# calculate the condition
+ my $count_tbl_alias = 'rownum__emulation';
+ my $root_alias = $rs_attrs->{alias};
+ my $root_tbl_name = $root_rsrc->name;
- my $sq_attrs = $self->_subqueried_limit_attrs ($sql, $rs_attrs);
+ my (@unqualified_names, @qualified_names, @is_desc, @new_order_by);
- my $cmp_op = $direction eq 'desc' ? '>' : '<';
- my $count_tbl_alias = 'rownum__emulation';
+ for my $bit (@order_bits) {
- my $order_sql = $self->_order_by (delete $rs_attrs->{order_by});
- my $group_having_sql = $self->_parse_rs_attrs($rs_attrs);
+ ($bit, my $is_desc) = $self->_split_order_chunk($bit);
- my $in_sel = $sq_attrs->{selection_inner};
+ push @is_desc, $is_desc;
+ push @unqualified_names, $usable_order_ci->{$bit}{-colname};
+ push @qualified_names, $usable_order_ci->{$bit}{-fq_colname};
- # add the order supplement (if any) as this is what will be used for the outer WHERE
- $in_sel .= ", $_" for keys %{$sq_attrs->{order_supplement}};
+ push @new_order_by, { ($is_desc ? '-desc' : '-asc') => $usable_order_ci->{$bit}{-fq_colname} };
+ };
+
+ my (@where_cond, @skip_colpair_stack);
+ for my $i (0 .. $#order_bits) {
+ my $ci = $usable_order_ci->{$order_bits[$i]};
+
+ my ($subq_col, $main_col) = map { "$_.$ci->{-colname}" } ($count_tbl_alias, $root_alias);
+ my $cur_cond = { $subq_col => { ($is_desc[$i] ? '>' : '<') => { -ident => $main_col } } };
+
+ push @skip_colpair_stack, [
+ { $main_col => { -ident => $subq_col } },
+ ];
+
+ # we can trust the nullability flag because
+ # we already used it during _id_col_set resolution
+ #
+ if ($ci->{is_nullable}) {
+ push @{$skip_colpair_stack[-1]}, { $main_col => undef, $subq_col=> undef };
+
+ $cur_cond = [
+ {
+ ($is_desc[$i] ? $subq_col : $main_col) => { '!=', undef },
+ ($is_desc[$i] ? $main_col : $subq_col) => undef,
+ },
+ {
+ $subq_col => { '!=', undef },
+ $main_col => { '!=', undef },
+ -and => $cur_cond,
+ },
+ ];
+ }
+
+ push @where_cond, { '-and', => [ @skip_colpair_stack[0..$i-1], $cur_cond ] };
+ }
+# reuse the sqlmaker WHERE, this will not be returning binds
+ my $counted_where = do {
+ local $self->{where_bind};
+ $self->where(\@where_cond);
+ };
+
+# construct the rownum condition by hand
my $rownum_cond;
if ($offset) {
$rownum_cond = 'BETWEEN ? AND ?';
-
push @{$self->{limit_bind}},
[ $self->__offset_bindtype => $offset ],
[ $self->__total_bindtype => $offset + $rows - 1]
}
else {
$rownum_cond = '< ?';
-
push @{$self->{limit_bind}},
[ $self->__rows_bindtype => $rows ]
;
}
+# and what we will order by inside
+ my $inner_order_sql = do {
+ local $self->{order_bind};
+
+ my $s = $self->_order_by (\@new_order_by);
+
+ $self->throw_exception('Inner gensubq order may not contain binds... something went wrong')
+ if @{$self->{order_bind}};
+
+ $s;
+ };
+
+### resume originally scheduled programming
+###
+###
+
+ # we need to supply the order for the supplements to be properly calculated
+ my $sq_attrs = $self->_subqueried_limit_attrs (
+ $sql, { %$rs_attrs, order_by => \@new_order_by }
+ );
+
+ my $in_sel = $sq_attrs->{selection_inner};
+
+ # add the order supplement (if any) as this is what will be used for the outer WHERE
+ $in_sel .= ", $_" for sort keys %{$sq_attrs->{order_supplement}};
+
+ my $group_having_sql = $self->_parse_rs_attrs($rs_attrs);
+
+
return sprintf ("
SELECT $sq_attrs->{selection_outer}
FROM (
SELECT $in_sel $sq_attrs->{query_leftover}${group_having_sql}
) %s
-WHERE ( SELECT COUNT(*) FROM %s %s WHERE %s $cmp_op %s ) $rownum_cond
-$order_sql
+WHERE ( SELECT COUNT(*) FROM %s %s $counted_where ) $rownum_cond
+$inner_order_sql
", map { $self->_quote ($_) } (
$rs_attrs->{alias},
$root_tbl_name,
$count_tbl_alias,
- "$count_tbl_alias.$first_ord_col",
- "$first_ord_alias.$first_ord_col",
));
}
my $sql_alias = (ref $s) eq 'HASH' ? $s->{-as} : undef;
push @sel, {
+ arg => $s,
sql => $sql_sel,
unquoted_sql => do {
local $self->{quote_char};
,
};
- $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
# 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} =~ / (?<! ^ $re_alias ) \. /x
or
$node->{unquoted_sql} =~ / (?<! ^ $re_alias ) $re_sep /x
}
else {
push @{$sel->{inner}}, $node->{sql};
- push @{$sel->{outer}}, $self->_quote ($node->{as});
+ push @{$sel->{outer}}, $self->_quote (ref $node->{arg} ? $node->{as} : $node->{arg});
}
}
for my $chunk ($self->_order_by_chunks ($rs_attrs->{order_by})) {
# order with bind
$chunk = $chunk->[0] if (ref $chunk) eq 'ARRAY';
- $chunk =~ s/\s+ (?: ASC|DESC ) \s* $//ix;
+ ($chunk) = $self->_split_order_chunk($chunk);
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||{}}
);
}
projects / dbsrgits/DBIx-Class.git / blobdiff