Author: spadkins
Date: Mon Sep 4 11:53:31 2006
New Revision: 6845
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
Log:
add default params and params that don't relate to column names. fix index
hints.
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 Mon Sep 4 11:53:31 2006
@@ -911,16 +911,31 @@
$cols = [$cols] if (!ref($cols));
$options = {} if (!$options);
- my ($order_by, $direction, $param_order, $col, $dir);
+ my ($order_by, $direction, $param_order, $param, $col, $dir);
$order_by = $options->{order_by} || $options->{ordercols} || []; #
{ordercols} is deprecated
$order_by = [$order_by] if (!ref($order_by));
$direction = $options->{direction} || $options->{directions}; #
{directions} is deprecated
my $modifier = $options->{distinct} ? " distinct" : "";
+ # DETERMINE THE ORDER TO PROCESS THE PARAMS
$param_order = $params->{"_order"};
- if (!defined $param_order && ref($params) eq "HASH") {
+ if (!defined $param_order) {
$param_order = [ (keys %$params) ];
}
+ # ADD ANY DEFAULT PARAMS
+ my $paramdefs = $self->{table}{$table}{param};
+ if ($paramdefs) {
+ my $params_modified = 0;
+ my %params = %$params;
+ foreach $param (keys %$paramdefs) {
+ if (! exists $params->{$param} && $paramdefs->{$param}{default}) {
+ $params{$param} = $paramdefs->{$param}{default};
+ push(@$param_order, $param);
+ $params_modified = 1;
+ }
+ }
+ $params = \%params if ($params_modified);
+ }
my $startrow = $options->{startrow} || 0;
my $endrow = $options->{endrow} || 0;
@@ -1215,7 +1230,7 @@
">" => "gt",
);
- my ($where_condition, @join_conditions, @criteria_conditions, $param,
$repop, $sqlop, $value);
+ my ($where_condition, @join_conditions, @criteria_conditions, $repop,
$sqlop, $value);
my ($include_null, $inferred_op);
for ($idx = 0; $idx <= $#$param_order; $idx++) {
@@ -1283,141 +1298,149 @@
$column_def = $table_def->{column}{$column};
}
- next if (!defined $column_def); # skip if the column is unknown
-
- $include_null = 0;
-
- if (! defined $value) {
- # $value = "?"; # TODO: make this work with the
"contains/matches" operators
- $sqlop = (!$sqlop || $sqlop eq "=") ? "is" : "is not";
- $value = "null";
- }
- else {
- next if (defined $table_def->{param}{$param}{all_value} &&
- $value eq $table_def->{param}{$param}{all_value});
-
- next if ($inferred_op && $value eq "ALL");
-
- if (ref($value) eq "ARRAY") {
- $value = join(",", @$value);
- }
+ if (defined $column_def) { # skip if the column is unknown
+ $include_null = 0;
- if ($value =~ s/[EMAIL PROTECTED](.*)\]$/$1/) { # new @[]
expressions replace !expr!
- $quoted = 0;
- }
- elsif ($value =~ s/[EMAIL PROTECTED](.*)\}$/$1/) { # new @{}
don't work.. perl interpolates... deprecate.
- $quoted = 0;
- }
- elsif ($value =~ s/^!expr!//) { # deprecated (ugh!)
- $quoted = 0;
- }
- elsif ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.,]+$/);
+ if (! defined $value) {
+ # $value = "?"; # TODO: make this work with the
"contains/matches" operators
+ $sqlop = (!$sqlop || $sqlop eq "=") ? "is" : "is not";
+ $value = "null";
}
else {
- $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.]+$/);
- }
+ next if (defined $table_def->{param}{$param}{all_value} &&
+ $value eq $table_def->{param}{$param}{all_value});
- next if ($inferred_op && !$quoted && $value eq "");
+ next if ($inferred_op && $value eq "ALL");
- if ($repop eq "contains" || $repop eq "not_contains") {
- $value =~ s/'/\\'/g;
- $value = "'%$value%'";
- }
- elsif ($repop eq "matches" || $repop eq "not_matches") {
- $value =~ s/_/\\_/g;
- $value =~ s/'/\\'/g;
- $value =~ s/\*/%/g;
- $value =~ s/\?/_/g;
- $value = "'$value'";
- }
- elsif ($sqlop eq "in" || ($inferred_op && $sqlop eq "=")) {
+ if (ref($value) eq "ARRAY") {
+ $value = join(",", @$value);
+ }
- if (! defined $value || $value eq "NULL") {
- $sqlop = "is";
- $value = "null";
+ if ($value =~ s/[EMAIL PROTECTED](.*)\]$/$1/) { # new @[]
expressions replace !expr!
+ $quoted = 0;
+ }
+ elsif ($value =~ s/[EMAIL PROTECTED](.*)\}$/$1/) { # new @{}
don't work.. perl interpolates... deprecate.
+ $quoted = 0;
+ }
+ elsif ($value =~ s/^!expr!//) { # deprecated (ugh!)
+ $quoted = 0;
+ }
+ elsif ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.,]+$/);
}
else {
- if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
- $include_null = 1;
- }
- if ($quoted) {
- $value =~ s/'/\\'/g;
- if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $value =~ s/,/','/g;
- $value = "('$value')";
- $sqlop = "in";
- }
- else {
- $value = "'$value'";
- $sqlop = "=";
- }
+ $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.]+$/);
+ }
+
+ next if ($inferred_op && !$quoted && $value eq "");
+
+ if ($repop eq "contains" || $repop eq "not_contains") {
+ $value =~ s/'/\\'/g;
+ $value = "'%$value%'";
+ }
+ elsif ($repop eq "matches" || $repop eq "not_matches") {
+ $value =~ s/_/\\_/g;
+ $value =~ s/'/\\'/g;
+ $value =~ s/\*/%/g;
+ $value =~ s/\?/_/g;
+ $value = "'$value'";
+ }
+ elsif ($sqlop eq "in" || ($inferred_op && $sqlop eq "=")) {
+
+ if (! defined $value || $value eq "NULL") {
+ $sqlop = "is";
+ $value = "null";
}
else {
- if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $value = "($value)";
- $sqlop = "in";
+ if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
+ $include_null = 1;
+ }
+ if ($quoted) {
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
+ $sqlop = "in";
+ }
+ else {
+ $value = "'$value'";
+ $sqlop = "=";
+ }
}
else {
- $sqlop = "=";
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value = "($value)";
+ $sqlop = "in";
+ }
+ else {
+ $sqlop = "=";
+ }
}
}
}
- }
- elsif ($sqlop eq "not in" || ($inferred_op && $sqlop eq "!=")) {
+ elsif ($sqlop eq "not in" || ($inferred_op && $sqlop eq "!="))
{
- if (! defined $value || $value eq "NULL") {
- $sqlop = "is not";
- $value = "null";
- }
- else {
- if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
- $include_null = 1;
- }
- if ($quoted) {
- $value =~ s/'/\\'/g;
- if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $value =~ s/,/','/g;
- $value = "('$value')";
- $sqlop = "not in";
- }
- else {
- $value = "'$value'";
- $sqlop = "!=";
- }
+ if (! defined $value || $value eq "NULL") {
+ $sqlop = "is not";
+ $value = "null";
}
else {
- if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $value = "($value)";
- $sqlop = "not in";
+ if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
+ $include_null = 1;
+ }
+ if ($quoted) {
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
+ $sqlop = "not in";
+ }
+ else {
+ $value = "'$value'";
+ $sqlop = "!=";
+ }
}
else {
- $sqlop = "!=";
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value = "($value)";
+ $sqlop = "not in";
+ }
+ else {
+ $sqlop = "!=";
+ }
}
}
}
+ elsif ($quoted) {
+ $value =~ s/'/\\'/g;
+ $value = "'$value'";
+ }
}
- elsif ($quoted) {
- $value =~ s/'/\\'/g;
- $value = "'$value'";
- }
- }
- $dbexpr = $column_def->{dbexpr};
- if (defined $dbexpr && $dbexpr ne "") {
- $self->_require_tables($dbexpr, \%reqd_tables, $tablealiashref, 2);
- if ($include_null) {
- if ($sqlop eq "not in" || $sqlop eq "!=") {
- push(@criteria_conditions, "($dbexpr $sqlop $value and
$dbexpr is not null)");
+ $dbexpr = $column_def->{dbexpr};
+ if (defined $dbexpr && $dbexpr ne "") {
+ $self->_require_tables($dbexpr, \%reqd_tables,
$tablealiashref, 2);
+ if ($include_null) {
+ if ($sqlop eq "not in" || $sqlop eq "!=") {
+ push(@criteria_conditions, "($dbexpr $sqlop $value and
$dbexpr is not null)");
+ }
+ else {
+ push(@criteria_conditions, "($dbexpr $sqlop $value or
$dbexpr is null)");
+ }
}
else {
- push(@criteria_conditions, "($dbexpr $sqlop $value or
$dbexpr is null)");
+ push(@criteria_conditions, "$dbexpr $sqlop $value");
}
}
- else {
- push(@criteria_conditions, "$dbexpr $sqlop $value");
+ }
+ elsif ($paramdefs && $paramdefs->{$param}) {
+ if ($paramdefs->{$param}{criteria}) {
+ push(@criteria_conditions,
$self->substitute($paramdefs->{$param}{criteria}, $params));
}
}
+ else {
+ # skip. not a known column or a known param of any other type.
+ }
}
# THIS IS DEAD CODE.
@@ -1501,8 +1524,8 @@
}
else {
push(@join_conditions, split(/ +and +/,$where_condition)) if
($where_condition);
- if ($options->{hint} && $self->{table}{$table}{alias} &&
$tablealias eq $self->{table}{$table}{alias}) {
- $tableref .= " $options->{hint}";
+ if ($tablealiashref->{$tablealias}{hint}) {
+ $tableref .= " $tablealiashref->{$tablealias}{hint}";
}
push(@from_tables, $tableref);
#print " $tablealias is [$dbtable] as [$tableref] where
[$where_condition]\n";
@@ -1513,8 +1536,8 @@
$tablealias = $tablealiases->[0];
$table = $tablealiashref->{$tablealias}{table};
$tableref = ($table) ? "$table $tablealias" : $tablealias;
- if ($options->{hint} && $self->{table}{$table}{alias} && $tablealias
eq $self->{table}{$table}{alias}) {
- $tableref .= " $options->{hint}";
+ if ($tablealiashref->{$tablealias}{hint}) {
+ $tableref .= " $tablealiashref->{$tablealias}{hint}";
}
push(@from_tables, $tableref);
}
