Author: spadkins
Date: Fri Jan 16 13:52:28 2009
New Revision: 12452
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/t/DBI-getset.t
p5ee/trunk/App-Repository/t/DBI-select-join.t
p5ee/trunk/App-Repository/t/DBI-update.t
Log:
test case for by_expression. support for table_def->{param} section including
{criteria} and {method} cases
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 Jan 16 13:52:28 2009
@@ -755,9 +755,10 @@
return wantarray ? () : "";
}
- for my $param (@$param_order) {
+ foreach my $param (@$param_order) {
next if (!defined $param || $param eq "");
- next if ($paramdefs->{$param}{method}); # this was already processed
+ next if ($paramdefs->{$param} && $paramdefs->{$param}{method}); #
this was already processed
+ my $no_auto_in_param = $paramdefs->{$param} ?
$paramdefs->{$param}{no_auto_in_param} : undef;
my $column = $param;
$sql_op = "=";
@@ -792,16 +793,19 @@
$column_def = $column_defs->{$column};
if (!defined $column_def) {
- if ($param =~ /^(begin|end)_(.*)/) {
- $column = $2;
- $sql_op = {begin=>">=", end=>"<="}->{$1};
- $inferred_op = 0;
- }
- $column_def = $column_defs->{$column};
- }
- elsif ($paramdefs && $paramdefs->{$param}) {
- if ($paramdefs->{$param}{criteria}) {
- push(@where_conditions,
$self->substitute($paramdefs->{$param}{criteria}, $params));
+ if ($paramdefs && $paramdefs->{$param}) {
+ if ($paramdefs->{$param}{criteria}) {
+ my $where_cond =
$self->substitute($paramdefs->{$param}{criteria}, $params);
+ push(@where_conditions, $where_cond);
+ }
+ }
+ else {
+ if ($param =~ /^(begin|end)_(.*)/) {
+ $column = $2;
+ $sql_op = {begin=>">=", end=>"<="}->{$1};
+ $inferred_op = 0;
+ }
+ $column_def = $column_defs->{$column};
}
}
@@ -826,8 +830,8 @@
next;
}
- next if (defined $table_def->{param}{$param}{all_value} &&
- $value eq $table_def->{param}{$param}{all_value});
+ next if (defined $paramdefs->{$param} && defined
$paramdefs->{$param}{all_value} &&
+ $value eq $paramdefs->{$param}{all_value});
next if ($inferred_op && $value eq "ALL");
@@ -841,8 +845,7 @@
$quoted = 0;
}
else {
- my $c = ($value =~ /,/ &&
- !$table_def->{param}{$param}{no_auto_in_param}) ? ',' : '';
+ my $c = ($value =~ /,/ && !$no_auto_in_param) ? ',' : '';
$quoted = (defined $column_def->{quoted}) ?
$column_def->{quoted} : ($value !~ /^-?[0-9.$c]+$/);
@@ -861,19 +864,15 @@
my $include_null = 0;
if ($rep_op eq "contains" || $rep_op eq "not_contains") {
-#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [CONTAINS]\n";
$value = $dbh->quote("%" . $value . "%");
}
elsif ($rep_op eq "matches" || $rep_op eq "not_matches") {
-#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [MATCHES]\n";
$value = $dbh->quote($value);
$value =~ s/_/\\_/g;
$value =~ tr/*?/%_/;
}
elsif (my ($not) = $sql_op =~ m"(not|) ?in") {
-#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [IN]\n";
if (! defined $value || $value eq "NULL") {
-#print STDERR "2. COLUMN: $column: value=[$value] sql_op=[$sql_op]\n";
$sql_op = $not ? "is $not" : "is";
$value = "null";
}
@@ -882,7 +881,7 @@
$include_null = 1;
}
- if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ if ($value =~ /,/ && ! $no_auto_in_param) {
if ($dbexpr_update) {
my (@vals);
foreach my $val (split(/,/, $value)) {
@@ -891,14 +890,12 @@
}
$value = join(",", @vals);
$dbexpr_update_applied = 1;
-#print STDERR "2b COLUMN: $column : $dbexpr $sql_op $value
[IN-multi-dbexpr=$dbexpr_update]\n";
}
else {
if ($quoted) {
$value = $dbh->quote($value);
$value =~ s/,/','/g;
}
-#print STDERR "2c COLUMN: $column : $dbexpr $sql_op $value
[IN-multi-dbexpr=$dbexpr_update]\n";
}
$value = "($value)";
$sql_op = $not ? "$not in" : "in";
@@ -910,12 +907,10 @@
$dbexpr_update_applied = 1;
}
$sql_op = $not ? "!=" : "=";
-#print STDERR "2d COLUMN: $column : $dbexpr $sql_op $value
[IN-nomulti-dbexpr=$dbexpr_update]\n";
}
}
}
elsif ($quoted) {
-#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [QUOTED]\n";
$value = $dbh->quote($value);
if ($dbexpr_update) {
$value = sprintf($dbexpr_update, $value);
@@ -923,14 +918,12 @@
}
}
else {
-#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [NOTHING]\n";
}
if (!$options->{single_table} && $dbexpr && $options->{reqd_tables} &&
$tablealiashref) {
$self->_require_tables($dbexpr, $options->{reqd_tables},
$tablealiashref, 2);
}
if ($dbexpr && !$dbexpr_update_applied) {
-#print STDERR "3. COLUMN: $column : $dbexpr $sql_op $value\n";
$column = $dbexpr;
$column =~ s/\b$alias\.//g if ($options->{single_table});
}
@@ -1030,52 +1023,6 @@
my (@where_conditions, $value);
- # ADD ANY DEFAULT PARAMS
- my $paramdefs = $table_def->{param};
- my $params_modified = 0;
- if ($paramdefs) {
- # make a copy.
- # This is necessary if {default} is supplied (see the few lines above)
- # or if the {method} is called to modify the $params.
- my %params = %$params;
- $params = \%params;
-
- foreach $param (keys %$paramdefs) {
- if (! exists $params->{$param} && $paramdefs->{$param}{default}) {
- $params->{$param} = $paramdefs->{$param}{default};
- $params_modified = 1;
- }
- elsif (exists $params->{$param} && $paramdefs->{$param}{method}) {
- my $method = $paramdefs->{$param}{method};
- my $where_condition = $self->$method($params, $param, $table);
- if ($where_condition) {
- push(@where_conditions, $where_condition);
- }
- $params_modified = 1;
- }
- }
- }
-
-# # DETERMINE THE ORDER TO PROCESS THE PARAMS
-# $param_order = $params->{"_order"};
-# if (!defined $param_order) {
-# $param_order = [ (keys %$params) ];
-# }
-# elsif ($params_modified) {
-# # merge the added params with the predetermined ordered params
-# my (%param_used);
-# $param_order = [ @$param_order ];
-# foreach my $param (@$param_order) {
-# $param_used{$param} = 1;
-# }
-# foreach my $param (keys %$params) {
-# if (!$param_used{$param}) {
-# push(@$param_order, $param);
-# $param_used{$param} = 1;
-# }
-# }
-# }
-
my $startrow = $options->{startrow} || 0;
my $endrow = $options->{endrow} || 0;
my $auto_extend = $options->{auto_extend} || 0;
Modified: p5ee/trunk/App-Repository/t/DBI-getset.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-getset.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-getset.t Fri Jan 16 13:52:28 2009
@@ -216,14 +216,13 @@
$years_older = $rep->get("test_person", {person_id => 1, base_age => 20},
"years_older");
is($years_older, 21, "get() years_older [$years_older] base_age = 20");
-exit(0);
#####################################################################
# $rep->set_rows($table, undef, \...@cols, $rows, \%options);
#####################################################################
-eval {
- $nrows = $rep->set_rows("test_person", undef, $columns, $rows);
-};
-is($nrows, 7, "set_rows() [test_person]");
+# eval {
+# $nrows = $rep->set_rows("test_person", undef, $columns, $rows);
+# };
+# is($nrows, 7, "set_rows() [test_person]");
# $value = $rep->get ($table, \%params, $col, \%options);
# @row = $rep->get ($table, \%params, \...@cols, \%options);
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 Jan 16 13:52:28 2009
@@ -12,6 +12,75 @@
},
);
+########################################################################
+# Create a Subclassed repository
+########################################################################
+package App::Repository::Test;
+
+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::MySQL;
+use App::Repository::Oracle;
+...@isa = ($App::options{dbclass});
+
+use Date::Format;
+use Date::Parse;
+
+$App::options{dbclass} = "App::Repository::Test";
+
+sub enumerate_dates {
+ my ($self, $params, $param, $table) = @_;
+ my $date_ranges = $params->{$param} || "0";
+ my $base_dt = $params->{base_dt} || $params->{hist_dt} ||
time2str("%Y-%m-%d", time());
+ my $base_time = (str2time($base_dt) + 2*3600);
+
+ my ($begin_dt, $end_dt);
+
+ my $base_param = $param;
+ $base_param =~ s/_spec$//;
+
+ my @date_ranges = split(/,/, $date_ranges);
+
+ if ($date_ranges =~ /^(-?[0-9]+)-(-?[0-9]+)$/) {
+ $begin_dt = $1;
+ $end_dt = $2;
+ if ($begin_dt =~ m!^-?[0-9]{1,3}$!) {
+ $begin_dt = time2str("%Y-%m-%d", $base_time + $begin_dt*24*3600);
+ #print STDERR "begin_dt = [$begin_dt]\n";
+ }
+ elsif ($begin_dt =~ /^(....)(..)(..)$/) {
+ $begin_dt = "$1-$2-$3";
+ #print STDERR "begin_dt = [$begin_dt]\n";
+ }
+ if ($end_dt =~ m!^-?[0-9]{1,3}$!) {
+ $end_dt = time2str("%Y-%m-%d", $base_time + $end_dt*24*3600);
+ #print STDERR "end_dt = [$end_dt]\n";
+ }
+ elsif ($end_dt =~ /^(....)(..)(..)$/) {
+ $end_dt = "$1-$2-$3";
+ #print STDERR "end_dt = [$end_dt]\n";
+ }
+ delete $params->{$param};
+ delete $params->{base_dt};
+ $params->{"begin_$base_param"} = $begin_dt;
+ $params->{"end_$base_param"} = $end_dt;
+ my @param_order = sort keys %$params;
+ $params->{_order} = \...@param_order;
+ }
+ return(undef);
+}
+
+########################################################################
+# back to the main test script
+########################################################################
+package main;
+
use Test::More qw(no_plan);
use lib "../App-Context/lib";
use lib "../../App-Context/lib";
@@ -83,6 +152,14 @@
joincriteria => "ctyctry.country = c.country",
},
},
+ param => {
+ hist_dt => {
+ criteria => "p.birth_dt <= '{hist_dt}'",
+ },
+ birth_dt_spec => {
+ method => "enumerate_dates", # call
$db->enumerate_dates($params, "birth_dt_spec", "test_person");
+ },
+ },
column => {
country_nm => { dbexpr => "ctry.country_nm", },
city_country_nm => { dbexpr => "ctyctry.country_nm", },
@@ -943,6 +1020,47 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): ensure that joins are in
order");
&check_select($sql,0);
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+where p.birth_dt <= '1960-01-01'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): use param (hist_dt)
which is not a column",
+ "test_person",
+ { hist_dt => '1960-01-01' },
+ ["first_name","last_name","city","state","age"]);
+$sql = $rep->_mk_select_joined_sql("test_person",
+ { hist_dt => '1960-01-01' },
+ ["first_name","last_name","city","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): use param (hist_dt) which is
not a column");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ p.first_name,
+ p.last_name,
+ p.city,
+ p.state,
+ p.age
+from test_person p
+where p.birth_dt >= '2008-12-16'
+ and p.birth_dt <= '2008-12-17'
+EOF
+&test_get_rows($expect_sql, 0, "_mk_select_joined_sql(): use param
(birth_dt_spec) which invokes a method",
+ "test_person",
+ { birth_dt_spec => "-31--30", base_dt => "2009-01-16" },
+ ["first_name","last_name","city","state","age"]);
+$sql = $rep->_mk_select_joined_sql("test_person",
+ { birth_dt_spec => "-31--30", base_dt => "2009-01-16" },
+ ["first_name","last_name","city","state","age"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): use param (birth_dt_spec)
which invokes a method");
+&check_select($sql,0);
+
exit(0); # XXX REMOVE EXIT HERE XXX
###########################################################################
Modified: p5ee/trunk/App-Repository/t/DBI-update.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-update.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-update.t Fri Jan 16 13:52:28 2009
@@ -138,6 +138,19 @@
is($sql, $expect_sql, "_mk_update_sql(): proper quoting for \\'");
&check_exec($sql,1);
+######################################################################################3
+# { by_expression => 1 }
+######################################################################################3
+$expect_sql = <<EOF;
+update test_person set
+ age = age + 1,
+ state = coalesce(state,'GA')
+where person_id = 4
+EOF
+$sql = $rep->_mk_update_sql("test_person",4,["age","state"],["age + 1",
"coalesce(state,'GA')"], {by_expression => 1});
+is($sql, $expect_sql, "_mk_update_sql(): by_expression");
+&check_exec($sql,1);
+
# This doesn't work yet
#$expect_sql = <<EOF;
#update test_person set
