Author: spadkins
Date: Fri Nov 14 13:02:39 2008
New Revision: 12071
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
p5ee/trunk/App-Repository/t/DBI-select-join.t
p5ee/trunk/App-Repository/t/DBI-select-ora.t
p5ee/trunk/App-Repository/t/DBI-select.t
Log:
Passes all required functional tests for Oracle
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 Fri Nov 14 13:02:39 2008
@@ -624,8 +624,17 @@
&App::sub_entry if ($App::trace);
my ($self, $sth, $startrow, $endrow, $slice) = @_;
$slice = [] if (! defined $slice);
- $startrow = 0 if (!defined $startrow);
+
+ $startrow = 1 if (!$startrow || $startrow < 1);
$endrow = 0 if (!defined $endrow);
+ # if we used an offset in the query, we don't need to skip rows in the
result set ...
+ if (!$self->{select_without_offset}) { # i.e. we used an offset
+ if ($startrow > 1) {
+ $endrow -= ($startrow-1) if ($endrow && $endrow >= $startrow);
+ $startrow = 1;
+ }
+ }
+
my $mode = ref $slice;
my @rows;
my $rownum = 0;
@@ -2435,7 +2444,6 @@
$self->explain_sql($sql);
}
if (defined $dbh) {
- $self->{sql} = $sql;
my $continue = 1;
my $tries = 1;
while ($continue) {
Modified: p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm Fri Nov 14
13:02:39 2008
@@ -91,13 +91,51 @@
my ($self, $table, $options) = @_;
my $suffix = "";
$options = {} if (!$options);
- if ($options->{endrow}) {
- $suffix = "limit $options->{endrow}\n";
+
+ my ($limit, $offset);
+ my $startrow = $options->{startrow} || 0;
+ my $endrow = $options->{endrow} || 0;
+ if ($startrow > 1 && !$self->{select_without_offset}) {
+ $offset = $startrow - 1;
+ }
+ if ($endrow) {
+ if ($endrow < $startrow) {
+ $limit = 0;
+ }
+ elsif ($offset) {
+ $limit = $endrow - $offset;
+ }
+ else {
+ $limit = $endrow;
+ }
+ }
+
+ if ($offset) {
+ if ($limit) {
+ $suffix = "limit $limit offset $offset\n";
+ }
+ elsif (!defined $limit) {
+ $suffix = "limit 999999999 offset $offset\n";
+ }
+ else {
+ $suffix = "limit 0\n";
+ }
}
+ else {
+ if (defined $limit) {
+ $suffix = "limit $limit\n";
+ }
+ else {
+ # do nothing
+ }
+ }
+ #print STDERR "startrow=[$startrow] endrow=[$endrow] limit=[$limit]
offset=[$offset] suffix=[$suffix]\n";
+
&App::sub_exit($suffix) if ($App::trace);
return($suffix);
}
+# Clean out unnecessary startrow/endrow entries (i.e. startrow => 1) and
compute limit/offset
# $insert_sql = $rep->_mk_insert_row_sql ($table, [EMAIL PROTECTED], [EMAIL
PROTECTED]);
sub _mk_insert_row_sql {
&App::sub_entry if ($App::trace);
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 Fri Nov 14
13:02:39 2008
@@ -98,16 +98,87 @@
return($attr);
}
+sub _mk_select_sql {
+ my ($self, $table, $params, $cols, $options) = @_;
+ return($self->_mk_select_sql_with_rownum("SUPER::_mk_select_sql", $table,
$params, $cols, $options));
+}
+
+sub _mk_select_joined_sql {
+ my ($self, $table, $params, $cols, $options) = @_;
+ return($self->_mk_select_sql_with_rownum("SUPER::_mk_select_joined_sql",
$table, $params, $cols, $options));
+}
+
+sub _mk_select_sql_with_rownum {
+ &App::sub_entry if ($App::trace);
+ my ($self, $method, $table, $params, $cols, $options) = @_;
+
+ my ($sql);
+ my $startrow = $options->{startrow} || 0;
+ my $endrow = $options->{endrow} || 0;
+ if (!$startrow && !$endrow) {
+ $sql = $self->$method($table, $params, $cols, $options);
+ }
+ else {
+ my ($offset);
+ $options = $options ? { %$options } : {}; # make a copy
+ if ($startrow > 1 && !$self->{select_without_offset}) {
+ $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})) {
+ $options->{use_rownum_on_query} = 1;
+ }
+
+ $sql = $self->$method($table, $params, $cols, $options);
+
+ if (! $options->{use_rownum_on_query}) {
+
+ #
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
+ #
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064
+ # In Oracle8i, release 8.1 -- yes.
+ #select *
+ # from ( select a.*, rownum rnum
+ # from ( YOUR_QUERY_GOES_HERE -- including the order by
) a
+ # where rownum <= MAX_ROWS )
+ # where rnum >= MIN_ROWS
+ #/
+ # that'll do it. It will *not* work in 8.0 or before.
+
+ if ($offset) {
+ if ($endrow) {
+ $sql = "select * from (select a.*, rownum x_rownum
from\n($sql) a where rownum <= $endrow) where x_rownum >= $startrow\n";
+ }
+ else {
+ $sql = "select * from (select a.*, rownum x_rownum
from\n($sql) a) where x_rownum >= $startrow\n";
+ }
+ }
+ else {
+ if ($endrow) {
+ $sql = "select a.* from\n($sql) a where rownum <=
$endrow\n";
+ }
+ else {
+ # do nothing (should never happen)
+ }
+ }
+ }
+ #print STDERR "startrow=[$startrow] endrow=[$endrow] limit=[$limit]
offset=[$offset] suffix=[$suffix]\n";
+ }
+ &App::sub_exit($sql) if ($App::trace);
+ return($sql);
+}
+
sub _mk_where_clause {
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $options) = @_;
$options = {} if (!$options);
my @where = $self->SUPER::_mk_where_clause($table, $params, $options);
- if ($options->{startrow}) {
- push(@where, "rownum >= $options->{startrow}");
+ my $startrow = $options->{startrow};
+ my $endrow = $options->{endrow};
+ if ($startrow && $endrow && $endrow < $startrow) {
+ push(@where, "rownum <= 0");
}
- if ($options->{endrow}) {
- push(@where, "rownum <= $options->{endrow}");
+ elsif ($options->{use_rownum_on_query} && $endrow) {
+ push(@where, "rownum <= $endrow");
}
if (wantarray) {
&App::sub_exit(@where) if ($App::trace);
Modified: p5ee/trunk/App-Repository/t/DBI-select-join.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-join.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-join.t Fri Nov 14 13:02:39 2008
@@ -194,8 +194,6 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): person.country_nm");
&check_select($sql,7);
-exit;
-
#$sql = $rep->_mk_select_joined_sql("test_person",{},["age"]);
#is($sql, $expect_sql, "_mk_select_joined_sql(): 1 col as array, no params");
#&check_select($sql,7);
@@ -603,7 +601,6 @@
p.age
from test_person p
where p.age >= 37
- and rownum >= 1
and rownum <= 1
EOF
}
Modified: p5ee/trunk/App-Repository/t/DBI-select-ora.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select-ora.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select-ora.t Fri Nov 14 13:02:39 2008
@@ -522,7 +522,6 @@
age
from test_person
where age >= 37
- and rownum >= 1
and rownum <= 1
EOF
$sql = $rep->_mk_select_sql("test_person",{"age.ge" =>
37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
Modified: p5ee/trunk/App-Repository/t/DBI-select.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select.t Fri Nov 14 13:02:39 2008
@@ -66,7 +66,7 @@
&create_table_test_person($rep);
sub check_select {
- my ($sql, $expected_rows, $debug) = @_;
+ my ($sql, $expected_rows, $id0, $idn, $debug) = @_;
my ($rows, $reprows);
eval {
@@ -84,6 +84,24 @@
if (defined $expected_rows) {
is(($#$rows + 1), $expected_rows, "num rows $expected_rows");
}
+
+ if (defined $id0) {
+ if ($#$rows == -1) {
+ ok(0, "got no rows when searching for a starting ID");
+ }
+ else {
+ is($rows->[0][0], $id0, "first row id was $id0");
+ }
+ }
+
+ if (defined $idn) {
+ if ($#$rows == -1) {
+ ok(0, "got no rows when searching for a final ID");
+ }
+ else {
+ is($rows->[$#$rows][0], $idn, "last row id was $idn");
+ }
+ }
}
# &test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no
params","test_person",{},"age");
@@ -514,7 +532,6 @@
age
from test_person
where age >= 37
- and rownum >= 1
and rownum <= 1
EOF
}
@@ -523,44 +540,6 @@
is($sql, $expect_sql, "_mk_select_sql(): cols, endrow");
&check_select($sql,0);
-#{
-# my $expect_sql_template = <<EOF;
-#select
-# person_id
-#from test_personWHERE
-#order by person_idLIMIT
-#EOF
-# my @options = (
-# { startrow => 1, endrow => 1, eff_startrow => 1, eff_endrow => 1, },
-# );
-# my ($options);
-# for (my $i = 0; $i <= $#options; $i++) {
-# $options = {};
-# $options->{startrow} = $options[$i]{startrow} if (exists
$options[$i]{startrow});
-# $options->{endrow} = $options[$i]{endrow} if (exists
$options[$i]{endrow});
-# $expect_sql = $expect_sql_template;
-# if ($dbtype eq 'mysql') {
-# $expect_sql =~ s/WHERE//;
-# $expect_sql =~ s/LIMIT/\nlimit /;
-# }
-# }
-# if ($dbtype eq 'oracle') {
-# $expect_sql = <<EOF;
-#select
-# first_name,
-# last_name,
-# age
-#from test_person
-#where age >= 37
-# and rownum >= 1
-# and rownum <= 1
-#EOF
-# }
-# $sql = $rep->_mk_select_sql("test_person",{"age.ge" =>
37},["first_name","last_name","age"],{startrow => 1, endrow => 1});
-# is($sql, $expect_sql, "_mk_select_sql(): cols, endrow");
-# &check_select($sql,0);
-#}
-
$expect_sql = <<EOF;
select
first_name,
