Author: spadkins
Date: Tue Feb 17 20:51:26 2009
New Revision: 12510
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
p5ee/trunk/App-Repository/t/DBI-select-limit.t
Log:
handle implied summaries with respect to startrow/endrow
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Tue Feb 17 20:51:26 2009
@@ -1209,13 +1209,7 @@
############################################################
# accumulate group-by columns and whether grouping reqd
############################################################
- if (($dbexpr =~ /sum *\(/i) ||
- ($dbexpr =~ /min *\(/i) ||
- ($dbexpr =~ /max *\(/i) ||
- ($dbexpr =~ /avg *\(/i) ||
- ($dbexpr =~ /std *\(/i) ||
- ($dbexpr =~ /stddev *\(/i) || # Oracle extension (supported by
MySQL)
- ($dbexpr =~ /count *\(/i)) {
+ if ($dbexpr =~ /(sum|count|min|max|avg|std|stddev) *\(/i) {
$group_reqd = 1;
}
else {
@@ -1398,6 +1392,32 @@
return("");
}
+sub _assess_implied_summaries {
+ &App::sub_entry if ($App::trace);
+ my ($self, $table, $columns, $options) = @_;
+
+ my $table_def = $self->get_table_def($table);
+ my $is_summary_implied = 0;
+ my $summary_key_exists = 0;
+ my ($column, $column_def, $dbexpr);
+ for (my $idx = 0; $idx <= $#$columns; $idx++) {
+ $column = $columns->[$idx];
+ $column_def = $table_def->{column}{$column};
+ next if (!defined $column_def);
+ $dbexpr = $column_def->{dbexpr};
+ $dbexpr ||= $column;
+ if ($dbexpr =~ /(sum|count|min|max|avg|std|stddev) *\(/i) {
+ $is_summary_implied = 1;
+ }
+ else {
+ $summary_key_exists = 1;
+ }
+ last if ($is_summary_implied && $summary_key_exists);
+ }
+ &App::sub_exit($is_summary_implied, $summary_key_exists) if ($App::trace);
+ return($is_summary_implied, $summary_key_exists);
+}
+
sub _require_tables {
&App::sub_entry;
&App::sub_entry if ($App::trace >= 3);
Modified: p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm Tue Feb 17
20:51:26 2009
@@ -131,9 +131,28 @@
$offset = $startrow - 1;
}
#print STDERR "use_rownum_on_query? startrow=[$startrow]
endrow=[$endrow] offset=[$offset] order_by=[$options->{order_by}]\n";
- if (($startrow && $endrow && $endrow < $startrow) || ($endrow &&
!$offset && !$options->{order_by})) {
+ if ($startrow && $endrow && $endrow < $startrow) {
$options->{use_rownum_on_query} = 1;
}
+ elsif ($endrow && !$offset && !$options->{order_by}) {
+ my $is_summary = 0;
+ if ($options->{group_by}) {
+ $is_summary = 1;
+ }
+ else {
+ my ($is_summary_implied, $summary_key_exists) =
$self->_assess_implied_summaries($table, $cols);
+ if ($is_summary_implied) {
+ $is_summary = 1;
+ }
+ if (!$summary_key_exists) {
+ delete $options->{endrow};
+ $endrow = 0;
+ }
+ }
+ if (!$is_summary) {
+ $options->{use_rownum_on_query} = 1;
+ }
+ }
$sql = $self->$method($table, $params, $cols, $options);
Modified: p5ee/trunk/App-Repository/t/DBI-select-limit.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-limit.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-limit.t Tue Feb 17 20:51:26 2009
@@ -391,6 +391,65 @@
&test_get_rows($expect_sql,$nrows,$id0,$idn,$msg,"test_person",{},["person_id"],$options);
}
}
+ if ($dbtype eq "oracle") {
+ my ($row);
+ $expect_sql = <<EOF;
+select
+ count(*) as count_
+from test_person p
+EOF
+ $row = $rep->get_row("test_person", {}, ["count(*)"]);
+ $sql = $rep->{sql};
+ is($sql, $expect_sql, "select count(*) limit 1 (Oracle) got
right sql");
+ ok($row && ref($row) eq "ARRAY", "select count(*) limit 1 (Oracle) got
one row");
+
+ $expect_sql = <<EOF;
+select a.* from
+(select
+ p.gender as gnd,
+ count(*) as count_
+from test_person p
+group by
+ p.gender
+) a where rownum <= 1
+EOF
+ $row = $rep->get_row("test_person", {}, ["gender", "count(*)"]);
+ $sql = $rep->{sql};
+ is($sql, $expect_sql, "select gender, count(*) limit 1
(Oracle) got right sql");
+ ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1
(Oracle) got one row");
+
+ $expect_sql = <<EOF;
+select a.* from
+(select
+ p.gender as gnd,
+ count(*) as count_
+from test_person p
+group by
+ p.gender
+) a where rownum <= 1
+EOF
+ $row = $rep->get_row("test_person", {}, ["gender", "count(*)"],
{group_by => ["gender"]});
+ $sql = $rep->{sql};
+ is($sql, $expect_sql, "select gender, count(*) limit 1
(Oracle) got right sql");
+ ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1
(Oracle) got one row");
+
+ $expect_sql = <<EOF;
+select a.* from
+(select
+ p.gender as gnd,
+ count(*) as count_
+from test_person p
+group by
+ p.gender
+order by
+ gnd
+) a where rownum <= 1
+EOF
+ $row = $rep->get_row("test_person", {}, ["gender", "count(*)"],
{order_by => ["gender"]});
+ $sql = $rep->{sql};
+ is($sql, $expect_sql, "select gender, count(*) limit 1
(Oracle) got right sql");
+ ok($row && ref($row) eq "ARRAY", "select gender, count(*) limit 1
(Oracle) got one row");
+ }
}
exit 0;
