Author: spadkins
Date: Fri Nov 14 13:02:55 2008
New Revision: 12072
Added:
p5ee/trunk/App-Repository/t/DBI-select-limit.t (contents, props changed)
Log:
Passes all required functional tests for Oracle
Added: p5ee/trunk/App-Repository/t/DBI-select-limit.t
==============================================================================
--- (empty file)
+++ p5ee/trunk/App-Repository/t/DBI-select-limit.t Fri Nov 14 13:02:55 2008
@@ -0,0 +1,397 @@
+#!/usr/local/bin/perl -w
+
+use App::Options (
+ options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
+ option => {
+ dbclass => { default => "App::Repository::MySQL", },
+ dbdriver => { default => "mysql", },
+ dbhost => { default => "localhost", },
+ dbname => { default => "test", },
+ dbuser => { default => "", },
+ dbpass => { default => "", },
+ id => { description => "The number of the test to run", },
+ },
+);
+
+use Test::More qw(no_plan);
+use lib "../App-Context/lib";
+use lib "../../App-Context/lib";
+use lib "lib";
+use lib "../lib";
+use lib ".";
+use lib "t";
+
+use App;
+use App::Repository;
+use RepositoryTestUtils qw(create_table_test_person drop_table_test_person
populate_table_test_person);
+use strict;
+
+my $dbtype = $App::options{dbtype} || "mysql";
+
+if (!$App::options{dbuser}) {
+ ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy
to app.conf in 't' directory)");
+ exit(0);
+}
+
+my $context = App->context(
+ conf_file => "",
+ conf => {
+ Repository => {
+ default => {
+ class => $App::options{dbclass},
+ dbdriver => $App::options{dbdriver},
+ dbhost => $App::options{dbhost},
+ dbname => $App::options{dbname},
+ dbuser => $App::options{dbuser},
+ dbpass => $App::options{dbpass},
+ table => {
+ test_person => {
+ primary_key => ["person_id"],
+ alias => "p",
+ column => {
+ gender => {
+ alias => "gnd",
+ },
+ },
+ },
+ },
+ default_date_format => "YYYY-MM-DD HH24:MI:SS",
+ },
+ },
+ },
+ debug_sql => $App::options{debug_sql},
+ trace => $App::options{trace},
+);
+
+my $rep = $context->repository();
+&drop_table_test_person($rep);
+&create_table_test_person($rep);
+&populate_table_test_person($rep);
+
+sub check_select {
+ my ($sql, $expected_rows, $id0, $idn, $msg, $debug) = @_;
+
+ my ($rows, $reprows);
+ eval {
+ $rows = $rep->_do($sql);
+ };
+ is($@,"","$msg : sql executed ok");
+ if ($debug) {
+ print $sql;
+ print "ROWS [", ($#$rows + 1), "]\n";
+ foreach my $row (@$rows) {
+ print "ROW [", join("|", @$row), "]\n";
+ }
+ }
+
+ if (defined $expected_rows) {
+ is(($#$rows + 1), $expected_rows, "$msg : nrows=$expected_rows (from
raw select)");
+ }
+
+ if (defined $id0) {
+ if ($#$rows == -1) {
+ ok(0, "$msg : no rows when searching for a starting ID");
+ }
+ else {
+ is($rows->[0][0], $id0, "$msg : first row id was $id0");
+ }
+ }
+
+ if (defined $idn) {
+ if ($#$rows == -1) {
+ ok(0, "$msg : no rows when searching for a final ID");
+ }
+ else {
+ is($rows->[$#$rows][0], $idn, "$msg : last row id was $idn");
+ }
+ }
+}
+
+# &test_get_rows($expect_sql,0,"_mk_select_joined_sql(): 1 col, no
params","test_person",{},"age");
+sub test_get_rows {
+ my ($expected_sql, $nrows, $id0, $idn, $msg, $table, $params, $cols,
$options) = @_;
+
+ my ($rows, $sql);
+ eval {
+ $rows = $rep->get_rows($table, $params, $cols, $options);
+ };
+ is($@,"","$msg : get_rows() ok");
+ $sql = $rep->{sql};
+ is($sql, $expected_sql, "$msg : get_rows() sql ok");
+ is($#$rows + 1, $nrows, "$msg : nrows=$nrows (from get_rows())") if
(defined $nrows);
+
+ if (defined $id0) {
+ if ($#$rows == -1) {
+ ok(0, "$msg : no rows when searching for a starting ID");
+ }
+ else {
+ is($rows->[0][0], $id0, "$msg : first row id was $id0");
+ }
+ }
+
+ if (defined $idn) {
+ if ($#$rows == -1) {
+ ok(0, "$msg : no rows when searching for a final ID");
+ }
+ else {
+ is($rows->[$#$rows][0], $idn, "$msg : last row id was $idn");
+ }
+ }
+}
+
+my ($sql, $expect_sql);
+my @options = (
+ { startrow => 1, endrow => 1, nrows => 1, id0 => 1, idn => 1,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 1",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 1",
+ mysql_suffix_0 => "\nlimit 1",
+ mysql_suffix_1 => "\nlimit 1",
+ },
+ { startrow => 2, endrow => 2, nrows => 1, id0 => 2, idn => 2,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 2) where
x_rownum >= 2",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 2",
+ mysql_suffix_0 => "\nlimit 1 offset 1",
+ mysql_suffix_1 => "\nlimit 2",
+ },
+ { startrow => 3, endrow => 3, nrows => 1, id0 => 3, idn => 3,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 3) where
x_rownum >= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 3",
+ mysql_suffix_0 => "\nlimit 1 offset 2",
+ mysql_suffix_1 => "\nlimit 3",
+ },
+ { startrow => 3, nrows => 5, id0 => 3, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a) where x_rownum >= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "\nlimit 999999999 offset 2",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 3, endrow => 2, nrows => 0, id0 => undef, idn => undef,
+ oracle_where_0 => "\nwhere rownum <= 0", oracle_prefix_0 => "",
oracle_suffix_0 => "",
+ oracle_where_1 => "\nwhere rownum <= 0", oracle_prefix_1 => "",
oracle_suffix_1 => "",
+ mysql_suffix_0 => "\nlimit 0",
+ mysql_suffix_1 => "\nlimit 0",
+ },
+ { endrow => 3, nrows => 3, id0 => 1, idn => 3,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 3",
+ mysql_suffix_0 => "\nlimit 3",
+ mysql_suffix_1 => "\nlimit 3",
+ },
+ { startrow => 0, endrow => 3, nrows => 3, id0 => 1, idn => 3,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 3",
+ mysql_suffix_0 => "\nlimit 3",
+ mysql_suffix_1 => "\nlimit 3",
+ },
+ { startrow => 1, endrow => 3, nrows => 3, id0 => 1, idn => 3,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 3",
+ mysql_suffix_0 => "\nlimit 3",
+ mysql_suffix_0 => "\nlimit 3",
+ mysql_suffix_1 => "\nlimit 3",
+ },
+ { startrow => 2, endrow => 3, nrows => 2, id0 => 2, idn => 3,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 3) where
x_rownum >= 2",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 3",
+ mysql_suffix_0 => "\nlimit 2 offset 1",
+ mysql_suffix_1 => "\nlimit 3",
+ },
+ { endrow => 7, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 7",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 7",
+ mysql_suffix_0 => "\nlimit 7",
+ mysql_suffix_1 => "\nlimit 7",
+ },
+ { startrow => 0, endrow => 7, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 7",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 7",
+ mysql_suffix_0 => "\nlimit 7",
+ mysql_suffix_1 => "\nlimit 7",
+ },
+ { startrow => 1, endrow => 7, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 7",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 7",
+ mysql_suffix_0 => "\nlimit 7",
+ mysql_suffix_1 => "\nlimit 7",
+ },
+ { startrow => 3, endrow => 7, nrows => 5, id0 => 3, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 7) where
x_rownum >= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 7",
+ mysql_suffix_0 => "\nlimit 5 offset 2",
+ mysql_suffix_1 => "\nlimit 7",
+ },
+ { startrow => 7, endrow => 7, nrows => 1, id0 => 7, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 7) where
x_rownum >= 7",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 7",
+ mysql_suffix_0 => "\nlimit 1 offset 6",
+ mysql_suffix_1 => "\nlimit 7",
+ },
+ { endrow => 8, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 8",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 8",
+ mysql_suffix_0 => "\nlimit 8",
+ mysql_suffix_1 => "\nlimit 8",
+ },
+ { startrow => 0, endrow => 8, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 8",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 8",
+ mysql_suffix_0 => "\nlimit 8",
+ mysql_suffix_1 => "\nlimit 8",
+ },
+ { startrow => 1, endrow => 8, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select a.* from\n(",
oracle_suffix_0 => "\n) a where rownum <= 8",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 8",
+ mysql_suffix_0 => "\nlimit 8",
+ mysql_suffix_1 => "\nlimit 8",
+ },
+ { startrow => 3, endrow => 8, nrows => 5, id0 => 3, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 8) where
x_rownum >= 3",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 8",
+ mysql_suffix_0 => "\nlimit 6 offset 2",
+ mysql_suffix_1 => "\nlimit 8",
+ },
+ { startrow => 7, endrow => 8, nrows => 1, id0 => 7, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "select * from (select a.*,
rownum x_rownum from\n(", oracle_suffix_0 => "\n) a where rownum <= 8) where
x_rownum >= 7",
+ oracle_where_1 => "", oracle_prefix_1 => "select a.* from\n(",
oracle_suffix_1 => "\n) a where rownum <= 8",
+ mysql_suffix_0 => "\nlimit 2 offset 6",
+ mysql_suffix_1 => "\nlimit 8",
+ },
+ { startrow => 0, endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => undef, endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 0, endrow => undef, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => undef, endrow => undef, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ {
+ nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 1, endrow => undef, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 1, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 1, endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 0, endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+ { startrow => 0, endrow => 0, nrows => 7, id0 => 1, idn => 7,
+ oracle_where_0 => "", oracle_prefix_0 => "", oracle_suffix_0 => "",
+ oracle_where_1 => "", oracle_prefix_1 => "", oracle_suffix_1 => "",
+ mysql_suffix_0 => "",
+ mysql_suffix_1 => "",
+ },
+);
+
+{
+ my $expect_sql_template = <<EOF;
+PREFIXselect
+ person_id
+from test_personWHERE
+order by
+ person_idSUFFIX
+EOF
+ my ($options, $nrows, $id0, $idn, $msg, $swo_str, $value);
+ my $id = $App::options{id};
+ my ($select_nrows, $select_id0, $select_idn);
+ for (my $swo = 0; $swo <= 1; $swo++) {
+ $rep->{select_without_offset} = $swo;
+ $swo_str = $swo ? " (without offset)" : " (with offset)";
+ for (my $i = 0; $i <= $#options; $i++) {
+ next if (defined $id && $i != $id);
+ last if (!ref($options[$i]));
+ $options = { order_by => [ "person_id" ] };
+ $options->{startrow} = $options[$i]{startrow} if (exists
$options[$i]{startrow});
+ $options->{endrow} = $options[$i]{endrow} if (exists
$options[$i]{endrow});
+ $id0 = $options[$i]{id0};
+ $idn = $options[$i]{idn};
+
+ $expect_sql = $expect_sql_template;
+ #$expect_sql =~ s/\n$//;
+ $value = (exists $options[$i]{"${dbtype}_prefix_${swo}"}) ?
$options[$i]{"${dbtype}_prefix_${swo}"} : $options[$i]{"${dbtype}_prefix"};
+ $expect_sql =~ s/PREFIX/$value/;
+ $value = (exists $options[$i]{"${dbtype}_where_${swo}"}) ?
$options[$i]{"${dbtype}_where_${swo}"} : $options[$i]{"${dbtype}_where"};
+ $expect_sql =~ s/WHERE/$value/;
+ $value = (exists $options[$i]{"${dbtype}_suffix_${swo}"}) ?
$options[$i]{"${dbtype}_suffix_${swo}"} : $options[$i]{"${dbtype}_suffix"};
+ $expect_sql =~ s/SUFFIX/$value/;
+
+ $msg = "limit/offset [$i] startrow=[$options->{startrow}]
endrow=[$options->{endrow}]$swo_str";
+ if (defined $id0 && defined $idn) {
+ $select_nrows = $swo ? $idn : ($idn - $id0 + 1);
+ $select_id0 = $swo ? 1 : $id0;
+ $select_idn = $idn;
+ }
+ else {
+ $select_nrows = undef;
+ $select_id0 = undef;
+ $select_idn = undef;
+ }
+
+ $sql =
$rep->_mk_select_sql("test_person",{},["person_id"],$options);
+ is($sql, $expect_sql, "limit/offset [$i]
startrow=[$options->{startrow}] endrow=[$options->{endrow}]$swo_str :
_mk_select_sql() sql ok");
+ &check_select($sql,$select_nrows,$select_id0,$select_idn,$msg);
+
+ $expect_sql =~ s/test_person/test_person p/;
+ $expect_sql =~ s/person_id/p.person_id/g;
+
+ $sql =
$rep->_mk_select_joined_sql("test_person",{},["person_id"],$options);
+ is($sql, $expect_sql, "limit/offset [$i]
startrow=[$options->{startrow}] endrow=[$options->{endrow}]$swo_str :
_mk_select_joined_sql() sql ok");
+ &check_select($sql,$select_nrows,$select_id0,$select_idn,$msg);
+
+ $nrows = $options[$i]{nrows};
+ $nrows = 0 if (!defined $nrows);
+
&test_get_rows($expect_sql,$nrows,$id0,$idn,$msg,"test_person",{},["person_id"],$options);
+ }
+ }
+}
+
+exit 0;
+
