Author: spadkins
Date: Tue Nov 18 13:29:01 2008
New Revision: 12087
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/t/DBI-select-join.t
Log:
add back use of paramdefs and require_tables() on where criteria and join
criteria
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 Nov 18 13:29:01 2008
@@ -674,13 +674,12 @@
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $options) = @_;
my ($value,$rep_op, $sql_op, $column_def, $quoted);
- my ($table_def, $column_defs, $alias);
- my $dbh = $self->{dbh};
-
- $table_def = $self->get_table_def($table, $options);
- $alias = $table_def->{alias};
- $column_defs = $table_def->{column};
+ my $dbh = $self->{dbh};
+ my $table_def = $self->get_table_def($table, $options);
+ my $alias = $table_def->{alias};
+ my $column_defs = $table_def->{column};
+ my $tablealiashref = $table_def->{tablealias};
# %rep_op : defines the operation implied when a VALUE begins with a
certain string
my %rep_op = (
@@ -716,18 +715,49 @@
"not_in" => "not in",
);
+ # ADD ANY DEFAULT PARAMS
+ my (@where_conditions);
$params = {} if (!$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} (see way below) is called to modify the $params.
+ my %params = %$params;
+ $params = \%params;
+
+ foreach my $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 $param_clause = $self->$method($params, $param, $table);
+ if ($param_clause) {
+ push(@where_conditions, $param_clause);
+ }
+ $params_modified = 1;
+ }
+ }
+ }
+
+ # DETERMINE THE ORDER TO PROCESS THE PARAMS
my $param_order = $params->{"_order"};
if (!defined $param_order && ref($params) eq "HASH") {
- $param_order = [ (keys %$params) ];
+ $param_order = [ keys %$params ];
+ }
+ elsif ($params_modified) {
+ # TODO: go into a merge between added params and predetermined ordered
params
}
if (!(defined $param_order && $#$param_order > -1)) {
return wantarray ? () : "";
}
- my (@where_conditions);
for my $param (@$param_order) {
next if (!defined $param || $param eq "");
+ next if ($paramdefs->{$param}{method}); # this was already processed
my $column = $param;
$sql_op = "=";
@@ -769,15 +799,11 @@
}
$column_def = $column_defs->{$column};
}
-
-#[[ From other copy:
-# TODO: Remove comment block
-# elsif ($paramdefs && $paramdefs->{$param}) {
-# if ($paramdefs->{$param}{criteria}) {
-# push(@where_conditions,
$self->substitute($paramdefs->{$param}{criteria}, $params));
-# }
-# }
-#]]
+ elsif ($paramdefs && $paramdefs->{$param}) {
+ if ($paramdefs->{$param}{criteria}) {
+ push(@where_conditions,
$self->substitute($paramdefs->{$param}{criteria}, $params));
+ }
+ }
next if (!defined $column_def); # skip if the column is unknown
@@ -788,7 +814,7 @@
if (! defined $value) {
# $value = "?";
# TODO: make this work with the "contains/matches" operators
- if ($options->{no_aliases}) {
+ if ($options->{single_table}) {
$dbexpr = $column;
}
if (!$sql_op || $sql_op eq "=") {
@@ -899,17 +925,14 @@
else {
#print STDERR "2. COLUMN: $column : $dbexpr $sql_op $value [NOTHING]\n";
}
- #[[ from other copy:
- # TODO: use option hash {multitable} to decide on whether to execute
this code
- # if (defined $dbexpr && $dbexpr ne "") {
- # $self->_require_tables($dbexpr, \%reqd_tables,
$tablealiashref, 2);
- # }
- #]]
+ 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->{no_aliases});
+ $column =~ s/\b$alias\.//g if ($options->{single_table});
}
if ($include_null) {
@@ -946,7 +969,7 @@
$params = $self->_key_to_params($table,$params) if (!$params ||
!ref($params)); # $params is undef/scalar => $key
$cols = [$cols] if (!ref($cols));
- $options = $options ? { %$options, no_aliases => 1 } : { no_aliases => 1 };
+ $options = $options ? { %$options, single_table => 1 } : { single_table =>
1 };
my ($sql, $order_by, $direction, $col, $colnum, $dir);
$order_by = $options->{order_by} || $options->{ordercols} || []; #
{ordercols} is deprecated
@@ -997,7 +1020,7 @@
$params = $self->_key_to_params($table,$params); # $params is
undef/scalar => $key
}
$cols = [$cols] if (!ref($cols));
- $options = {} if (!$options);
+ $options = $options ? { %$options } : {};
my ($order_by, $direction, $param, $col, $dir);
$order_by = $options->{order_by} || $options->{ordercols} || []; #
{ordercols} is deprecated
@@ -1099,6 +1122,7 @@
my (%dbexpr, @select_phrase, $group_reqd, @group_dbexpr, %reqd_tables);
my (@keycolidx, $primary_key, $primary_table);
my ($is_summary, %is_summary_key, $summaryexpr, @group_summarykeys);
+ $options->{reqd_tables} = \%reqd_tables;
$is_summary = (defined $group_by && $#$group_by >= 0);
@@ -1628,7 +1652,7 @@
}
}
elsif (ref($params) eq "HASH") {
- $where = $self->_mk_where_clause($table, $params, {no_aliases => 1});
+ $where = $self->_mk_where_clause($table, $params, {single_table => 1});
}
elsif (ref($params) eq "ARRAY") {
die "_mk_update_sql() can't update with no indexes/columns in params"
if ($#$params == -1);
@@ -1770,7 +1794,7 @@
}
}
elsif (ref($params) eq "HASH") {
- $where = $self->_mk_where_clause($table, $params, {no_aliases => 1});
+ $where = $self->_mk_where_clause($table, $params, {single_table => 1});
}
elsif (ref($params) eq "ARRAY") {
die "_mk_delete_sql() can't delete with no indexes/columns in params"
if ($#$params == -1);
@@ -1865,7 +1889,7 @@
sub _mk_delete_rows_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $options) = @_;
- $options = $options ? { %$options, no_aliases => 1 } : { no_aliases => 1 };
+ $options = $options ? { %$options, single_table => 1 } : { single_table =>
1 };
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
my ($sql);
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 Tue Nov 18 13:29:01 2008
@@ -872,6 +872,48 @@
$sql = $rep->_mk_select_joined_sql("test_person", { age => undef, },
["gender"]);
is($sql, $expect_sql, "_mk_select_joined_sql(): is null (by undef)");
&check_select($sql,0);
+
+###########################################################################
+# MULTI-TABLE JOINS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ ctry.country_nm,
+ c.city_nm
+from test_city c
+ inner join test_country ctry on ctry.country = c.country
+where c.city_cd = 'ATL'
+EOF
+&test_get_rows($expect_sql, 1, "_mk_select_joined_sql(): city->country. ATL",
+ "test_city",
+ {city_cd => "ATL"},
+ ["country_nm","city_nm"]);
+$sql = $rep->_mk_select_joined_sql(
+ "test_city",
+ {city_cd => "ATL"},
+ ["country_nm","city_nm"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): city->country. ATL - sql
correct");
+&check_select($sql,1);
+
+$expect_sql = <<EOF;
+select
+ c.city_nm
+from test_city c
+ inner join test_country ctry on ctry.country = c.country
+where ctry.country_nm = 'JAPAN'
+EOF
+&test_get_rows($expect_sql, 2, "_mk_select_joined_sql(): city->country. where
country_nm=JAPAN",
+ "test_city",
+ {country_nm => "JAPAN"},
+ ["city_nm"]);
+$sql = $rep->_mk_select_joined_sql(
+ "test_city",
+ {country_nm => "JAPAN"},
+ ["city_nm"]);
+is($sql, $expect_sql, "_mk_select_joined_sql(): city->country. where
country_nm=JAPAN");
+&check_select($sql,2);
+
exit(0); # XXX REMOVE EXIT HERE XXX
###########################################################################
@@ -1398,6 +1440,7 @@
is($sql, $expect_sql, "_mk_select_joined_sql(): verbatim (boo. hiss. evil.)");
&check_select($sql,0);
+#################################################################################################
# &drop_table($rep, "test_person");
# &drop_table($rep, "test_country");
# &drop_table($rep, "test_city");
