Author: spadkins
Date: Fri Nov 17 20:36:07 2006
New Revision: 8085
Modified:
p5ee/trunk/App-Repository/lib/App/Repository.pm
Log:
summarize_rows() and appropriate changes for extend_columns
Modified: p5ee/trunk/App-Repository/lib/App/Repository.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository.pm Fri Nov 17 20:36:07 2006
@@ -864,7 +864,12 @@
#}
}
if ($contains_expr) {
- $cols = $self->extend_columns($table, $cols);
+ my $new_cols = $self->extend_columns($table, $cols);
+ # the caller wanted his column list extended
+ if ($#$new_cols > $#$cols && $options->{extend_columns}) {
+ @$cols = @$new_cols; # so copy the columns
+ }
+ $cols = $new_cols; # then point to the new columns regardless
}
$rows = $self->_get_rows($table, $params, $cols, $options);
@@ -896,7 +901,7 @@
# case, we need to add them.
sub extend_columns {
&App::sub_entry if ($App::trace);
- my ($self, $table, $cols) = @_;
+ my ($self, $table, $cols, $options) = @_;
my (%colidx, $expr_columns, $expr, $extended, $col);
# Take note of which columns are alread in the list of requested columns.
for (my $i = 0; $i <= $#$cols; $i++) {
@@ -928,8 +933,10 @@
foreach my $expr_col (@$expr_columns) {
if (! defined $colidx{$expr_col}) {
if (!$extended) {
- $cols = [ @$cols ]; # make a copy. don't extend original.
$extended = 1;
+ if (!$options->{extend_columns}) {
+ $cols = [ @$cols ]; # make a copy. don't extend original.
+ }
}
push(@$cols, $expr_col); # extend the column list.
$colidx{$expr_col} = $#$cols;
@@ -942,6 +949,22 @@
return($cols);
}
+sub _contains_expr {
+ my ($self, $table, $columns) = @_;
+ my $contains_expr = 0;
+ my ($column);
+ my $column_defs = $self->{table}{$table}{column};
+ for (my $i = 0; $i <= $#$columns; $i++) {
+ $column = $columns->[$i];
+ if ($column_defs->{$column}{expr}) {
+ $contains_expr = 1;
+ last;
+ }
+ }
+ &App::sub_exit($contains_expr) if ($App::trace);
+ return($contains_expr);
+}
+
#############################################################################
# set_rows()
#############################################################################
@@ -2632,15 +2655,16 @@
=cut
#############################################################################
-# summarize()
+# summarize_rows()
#############################################################################
-=head2 summarize()
+=head2 summarize_rows()
- * Signature: $summarized_rows = $rep->summarize($rows, $columns, $summcolidx, $formulas);
+ * Signature: $summarized_rows = $rep->summarize_rows($table, $rows, $columns, $summary_keys, $options);
+ * Param: $table string
* Param: $rows [][]
* Param: $columns []
- * Param: $summcolidx []
+ * Param: $summary_keys []
* Param: $formulas {}
* Return: $summarized_rows []
* Throws: App::Exception::Repository
@@ -2657,90 +2681,147 @@
[ 1, "Ben", "Blue", 22.6, 195, ],
);
@columns = ( "id", "name", "team", "rating", "score" );
- @summcolidx = ( 2 ); # "team"
- %formulas = (
- rating => "{sum(rating)}/{count(rating)}",
+ @summary_keys = ( "team" );
+
+ $summarized_rows = $rep->summarize_rows(\@rows, \@columns, \@summary_keys, \%formulas);
+
+ @rows = (
+ { id=>5, "Jim", "Green", 13.5, 320, },
+ { id=>3, "Bob", "Green", 4.2, 230, },
+ { id=>9, "Ken", "Green", 27.4, 170, },
+ { id=>2, "Kim", "Blue", 11.7, 440, },
+ { id=>7, "Jan", "Blue", 55.1, 90, },
+ { id=>1, "Ben", "Blue", 22.6, 195, },
);
+ @columns = ( "rating", "score" ); # summarize a subset of the columns
+ @summary_keys = ( "team" );
+ %options = ();
- $summarized_rows = $rep->summarize(\@rows, \@columns, \@summcolidx, \%formulas);
+ $summarized_rows = $rep->summarize_rows(\@rows, \@columns, \@summary_keys, \%options);
=cut
-sub summarize {
+sub summarize_rows {
&App::sub_entry if ($App::trace);
- my ($self, $rows, $columns, $summcolidx, $formulas) = @_;
+ my ($self, $table, $rows, $columns, $summary_keys, $options) = @_;
- my (@summary_rows, $summary_row, $create_summary);
- my ($key, $nextkey, $row, $rowidx, $colidx, $numcols, $column, $elem);
- my (%total, $formula);
+ $summary_keys = [] if (!$summary_keys);
- $numcols = $#$columns + 1;
+ my $column_def = $self->{table}{$table}{column};
- for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
- $row = $rows->[$rowidx];
+ my (@summary_rows, $summary_row, %summary_row);
+ my ($key, $row, $hash_rows, $hash_row, $i, $rowidx, $colidx, $column, $value);
+ my $row_type = "ARRAY";
- $key = "Total";
- $key = join(",", @{$row->[$rowidx]}[@$summcolidx])
- if (defined $summcolidx);
+ if (!$rows || $#$rows == -1) {
+ # do nothing
+ }
+ else {
+ $row_type = (ref($rows->[0]) eq "ARRAY") ? "ARRAY" : "HASH";
- # accumulate totals
- for ($colidx = 0; $colidx < $numcols; $colidx++) {
- $column = $columns->[$colidx];
- $elem = $rows->[$rowidx][$colidx];
- if (defined $elem && $elem ne "") {
- if (defined $total{$column}) {
- $total{"sum($column)"} += ($elem+0);
+ # if we are summarizing HASH rows, convert to ARRAY
+ if ($row_type eq "HASH") {
+ $row = $rows->[0];
+ if (!$columns) {
+ $columns = [ ];
+ foreach $column (sort keys %$row) {
+ push(@$columns, $column) if (defined $column_def->{$column});
+ }
+ }
+ $hash_rows = $rows;
+ $rows = [];
+ foreach $hash_row (@$hash_rows) {
+ $row = [ @{$hash_row}{@$columns} ];
+ push(@$rows, $row);
+ }
+ }
+
+ # find the indexes for each of the columns
+ my (%colidx);
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $colidx{$columns->[$i]} = $i;
+ }
+
+ # get the indexes for the summary keys
+ my (@summary_key_idx, @summary_key_values);
+ if ($summary_keys) {
+ for ($i = 0; $i <= $#$summary_keys; $i++) {
+ $colidx = $colidx{$summary_keys->[$i]};
+ push(@summary_key_idx, $colidx) if (defined $colidx);
+ }
+ }
+
+ # determine which columns should be summable and which have expressions
+ my $sum_column_idx = [];
+ my $expr_column_idx = [];
+ my $contains_expr = 0;
+ $row = $rows->[0];
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $column = $columns->[$i];
+ $value = $row->[$i];
+ if ($column_def->{$column}{expr}) {
+ push(@$expr_column_idx, $i);
+ $contains_expr = 1;
+ }
+ elsif ($column_def->{$column}{is_key}) {
+ # do nothing
+ }
+ elsif (defined $value && $value =~ /^-?[0-9\.]+$/) {
+ push(@$sum_column_idx, $i);
+ }
+ }
+
+ # accumulate the sums of the summable columns
+ for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
+ $row = $rows->[$rowidx];
+ $key = ($#summary_key_idx > -1) ? join("|", @{$row}[@summary_key_idx]) : "";
+ $summary_row = $summary_row{$key};
+ if (!$summary_row) {
+ $summary_row = [];
+ if ($#summary_key_idx > -1) {
+ foreach $i (@summary_key_idx) {
+ $summary_row->[$i] = $row->[$i];
+ }
}
- else {
- $total{"sum($column)"} = ($elem+0);
+ foreach $i (@$sum_column_idx) {
+ $summary_row->[$i] = 0;
}
- $total{"$column"} = $elem;
+ $summary_row{$key} = $summary_row;
+ push(@summary_key_values, $key);
+ }
+ foreach $i (@$sum_column_idx) {
+ $summary_row->[$i] += $row->[$i];
}
- }
- if (defined $total{"count(*)"}) {
- $total{"count(*)"} ++;
- }
- else {
- $total{"count(*)"} = 1;
}
- # look ahead to see if we need to produce a summary row yet
- $create_summary = 0;
- if ($rowidx == $#$rows) {
- $create_summary = 1;
+ # put the summarized rows in the results array
+ foreach $key (@summary_key_values) {
+ push(@summary_rows, $summary_row{$key});
}
- else {
- $nextkey = "Total";
- $nextkey = join(",", @{$rows->[$rowidx+1]}[@$summcolidx])
- if (defined $summcolidx);
- $create_summary = 1 if ($nextkey ne $key);
- }
-
- if ($create_summary) {
- $summary_row = [];
+ # evaluate the expressions of the summarized rows (if they exist)
+ if ($contains_expr) {
+ my $params = {};
+ $self->evaluate_expressions($table, $params, $columns, \@summary_rows, $options);
+ }
- for ($colidx = 0; $colidx < $numcols; $colidx++) {
- $column = $columns->[$colidx];
- $formula = $formulas->{$column};
-
- $elem = "";
- if (defined $formula) { # match {
- $formula =~ s/\{([^\}]+)\}/\$total{"$1"}/g;
- $formula = "\$elem = $formula;";
- eval $formula;
- #$elem = "[$formula] $@" if ($@);
- }
- else {
- $elem = $total{"sum($column)"};
+ # if we started out summarizing HASH rows, convert back from ARRAY to HASH
+ if ($row_type eq "HASH") {
+ $rows = [ @summary_rows ];
+ $hash_rows = [];
+ @summary_rows = ();
+ foreach $row (@$rows) {
+ $hash_row = {};
+ for ($i = 0; $i <= $#$columns; $i++) {
+ $hash_row->{$columns->[$i]} = $row->[$i];
}
+ push(@summary_rows, $hash_row);
}
-
- push (@summary_rows, $summary_row);
}
}
+
&App::sub_exit(\@summary_rows) if ($App::trace);
- \@summary_rows;
+ return(\@summary_rows);
}
#############################################################################
@@ -3550,3 +3631,60 @@
1;
+__END__
+
+ if (0) { # HASH (or object of some type)
+ # determine which columns should be summable and which have expressions
+ my $agg_columns = [];
+ my $sum_columns = [];
+ my $expr_columns = [];
+ my $contains_expr = 0;
+ my (@summary_keys);
+ $row = $rows->[0];
+ foreach $column (keys %$row) {
+ $value = $row->{$column};
+ if ($column_def->{$column}{expr}) {
+ push(@$agg_columns, $column);
+ push(@$expr_columns, $column);
+ $contains_expr = 1;
+ }
+ elsif ($column_def->{$column}{is_key}) {
+ # do nothing
+ }
+ elsif (defined $value && $value =~ /^-?[0-9\.]+$/) {
+ push(@$agg_columns, $column);
+ push(@$sum_columns, $column);
+ }
+ }
+ # accumulate the sums of the summable columns
+ for ($rowidx = 0; $rowidx <= $#$rows; $rowidx++) {
+ $row = $rows->[$rowidx];
+ $key = ($#$summary_keys > -1) ? join("|", @{$row}{@$summary_keys}) : "";
+ $summary_row = $summary_row{$key};
+ if (!$summary_row) {
+ $summary_row = {};
+ if ($#$summary_keys > -1) {
+ foreach $column (@$summary_keys) {
+ $summary_row->{$column} = $row->{$column};
+ }
+ foreach $column (@$sum_columns) {
+ $summary_row->{$column} = 0;
+ }
+ }
+ $summary_row{$key} = $summary_row;
+ push(@summary_keys, $key);
+ }
+ foreach $column (@$sum_columns) {
+ $summary_row->{$column} += $row->{$column};
+ }
+ }
+ # put the summarized rows in the results array
+ foreach $key (@summary_keys) {
+ push(@summary_rows, $summary_row{$key});
+ }
+ # evaluate the expressions of the summarized rows (if they exist)
+ if ($contains_expr) {
+ my $params = {};
+ $self->evaluate_expressions($table, $params, $columns, \@summary_rows, $options);
+ }
+ }
lmpx.com only provides a reader for public news (NNTP) servers. It is not
affiliated with the servers or forums shown here and is not responsible for
the content of articles, which is written by their respective authors.