diff options
Diffstat (limited to 'extensions/UserProfile/lib/Util.pm')
| -rw-r--r-- | extensions/UserProfile/lib/Util.pm | 365 |
1 files changed, 178 insertions, 187 deletions
diff --git a/extensions/UserProfile/lib/Util.pm b/extensions/UserProfile/lib/Util.pm index 6b2eff098..54a9c2eea 100644 --- a/extensions/UserProfile/lib/Util.pm +++ b/extensions/UserProfile/lib/Util.pm @@ -13,44 +13,45 @@ use warnings; use base qw(Exporter); our @EXPORT = qw( update_statistics_by_user - tag_for_recount_from_bug - last_user_activity ); + tag_for_recount_from_bug + last_user_activity ); use Bugzilla; sub update_statistics_by_user { - my ($user_id) = @_; + my ($user_id) = @_; - # run all our queries on the slaves + # run all our queries on the slaves - my $dbh = Bugzilla->switch_to_shadow_db(); + my $dbh = Bugzilla->switch_to_shadow_db(); - my $now = $dbh->selectrow_array('SELECT LOCALTIMESTAMP(0)'); + my $now = $dbh->selectrow_array('SELECT LOCALTIMESTAMP(0)'); - # grab the current values + # grab the current values - my $last_statistics_ts = _get_last_statistics_ts($user_id); + my $last_statistics_ts = _get_last_statistics_ts($user_id); - my $statistics = _get_stats($user_id, 'profiles_statistics', 'name'); - my $by_status = _get_stats($user_id, 'profiles_statistics_status', 'status'); - my $by_product = _get_stats($user_id, 'profiles_statistics_products', 'product'); + my $statistics = _get_stats($user_id, 'profiles_statistics', 'name'); + my $by_status = _get_stats($user_id, 'profiles_statistics_status', 'status'); + my $by_product + = _get_stats($user_id, 'profiles_statistics_products', 'product'); - # bugs filed - _update_statistics($statistics, 'bugs_filed', [ $user_id ], <<EOF); + # bugs filed + _update_statistics($statistics, 'bugs_filed', [$user_id], <<EOF); SELECT COUNT(*) FROM bugs WHERE bugs.reporter = ? EOF - # comments made - _update_statistics($statistics, 'comments', [ $user_id ], <<EOF); + # comments made + _update_statistics($statistics, 'comments', [$user_id], <<EOF); SELECT COUNT(*) FROM longdescs WHERE who = ? EOF - # commented on - _update_statistics($statistics, 'commented_on', [ $user_id ], <<EOF); + # commented on + _update_statistics($statistics, 'commented_on', [$user_id], <<EOF); SELECT COUNT(*) FROM ( SELECT longdescs.bug_id FROM longdescs @@ -59,8 +60,9 @@ EOF ) AS temp EOF - # confirmed - _update_statistics($statistics, 'confirmed', [ $user_id, _field_id('bug_status') ], <<EOF); + # confirmed + _update_statistics($statistics, 'confirmed', + [$user_id, _field_id('bug_status')], <<EOF); SELECT COUNT(*) FROM bugs_activity WHERE who = ? @@ -69,8 +71,8 @@ EOF AND added = 'NEW' EOF - # patches submitted - _update_statistics($statistics, 'patches', [ $user_id ], <<EOF); + # patches submitted + _update_statistics($statistics, 'patches', [$user_id], <<EOF); SELECT COUNT(*) FROM attachments WHERE submitter_id = ? @@ -80,8 +82,8 @@ EOF 'text/x-phabricator-request')) EOF - # patches reviewed - _update_statistics($statistics, 'reviews', [ $user_id ], <<EOF); + # patches reviewed + _update_statistics($statistics, 'reviews', [$user_id], <<EOF); SELECT COUNT(*) FROM flags INNER JOIN attachments ON attachments.attach_id = flags.attach_id @@ -93,22 +95,22 @@ EOF AND status IN ('+', '-') EOF - # assigned to - _update_statistics($statistics, 'assigned', [ $user_id ], <<EOF); + # assigned to + _update_statistics($statistics, 'assigned', [$user_id], <<EOF); SELECT COUNT(*) FROM bugs WHERE assigned_to = ? EOF - # qa contact - _update_statistics($statistics, 'qa_contact', [ $user_id ], <<EOF); + # qa contact + _update_statistics($statistics, 'qa_contact', [$user_id], <<EOF); SELECT COUNT(*) FROM bugs WHERE qa_contact = ? EOF - # bugs touched - _update_statistics($statistics, 'touched', [ $user_id, $user_id], <<EOF); + # bugs touched + _update_statistics($statistics, 'touched', [$user_id, $user_id], <<EOF); SELECT COUNT(*) FROM ( SELECT bugs_activity.bug_id FROM bugs_activity @@ -122,42 +124,45 @@ EOF ) temp EOF - # activity by status/resolution, and product - _activity_by_status($by_status, $user_id); - _activity_by_product($by_product, $user_id); + # activity by status/resolution, and product + _activity_by_status($by_status, $user_id); + _activity_by_product($by_product, $user_id); - # if nothing is dirty, no need to do anything else - if ($last_statistics_ts) { - return unless _has_dirty($statistics) - || _has_dirty($by_status) - || _has_dirty($by_product); - } + # if nothing is dirty, no need to do anything else + if ($last_statistics_ts) { + return + unless _has_dirty($statistics) + || _has_dirty($by_status) + || _has_dirty($by_product); + } - # switch back to the main db for updating + # switch back to the main db for updating - $dbh = Bugzilla->switch_to_main_db(); - $dbh->bz_start_transaction(); + $dbh = Bugzilla->switch_to_main_db(); + $dbh->bz_start_transaction(); - # commit updated statistics + # commit updated statistics - _set_stats($statistics, $user_id, 'profiles_statistics', 'name') - if _has_dirty($statistics); - _set_stats($by_status, $user_id, 'profiles_statistics_status', 'status') - if _has_dirty($by_status); - _set_stats($by_product, $user_id, 'profiles_statistics_products', 'product') - if _has_dirty($by_product); + _set_stats($statistics, $user_id, 'profiles_statistics', 'name') + if _has_dirty($statistics); + _set_stats($by_status, $user_id, 'profiles_statistics_status', 'status') + if _has_dirty($by_status); + _set_stats($by_product, $user_id, 'profiles_statistics_products', 'product') + if _has_dirty($by_product); - # update the user's last_statistics_ts - _set_last_statistics_ts($user_id, $now); + # update the user's last_statistics_ts + _set_last_statistics_ts($user_id, $now); - $dbh->bz_commit_transaction(); + $dbh->bz_commit_transaction(); } sub tag_for_recount_from_bug { - my ($bug_id) = @_; - my $dbh = Bugzilla->dbh; - # get a list of all users associated with this bug - my $user_ids = $dbh->selectcol_arrayref(<<EOF, undef, $bug_id, _field_id('cc'), $bug_id); + my ($bug_id) = @_; + my $dbh = Bugzilla->dbh; + + # get a list of all users associated with this bug + my $user_ids + = $dbh->selectcol_arrayref(<<EOF, undef, $bug_id, _field_id('cc'), $bug_id); SELECT DISTINCT user_id FROM ( SELECT DISTINCT who AS user_id @@ -170,20 +175,22 @@ sub tag_for_recount_from_bug { WHERE bug_id = ? ) tmp EOF - # clear last_statistics_ts - $dbh->do( - "UPDATE profiles SET last_statistics_ts=NULL WHERE " . $dbh->sql_in('userid', $user_ids) - ); - foreach my $id (@$user_ids) { - Bugzilla->memcached->clear({ table => 'profiles', id => $id }); - } - return scalar(@$user_ids); + + # clear last_statistics_ts + $dbh->do("UPDATE profiles SET last_statistics_ts=NULL WHERE " + . $dbh->sql_in('userid', $user_ids)); + foreach my $id (@$user_ids) { + Bugzilla->memcached->clear({table => 'profiles', id => $id}); + } + return scalar(@$user_ids); } sub last_user_activity { - # last comment, or change to a bug (excluding CC changes) - my ($user_id) = @_; - return Bugzilla->dbh->selectrow_array(<<EOF, undef, $user_id, $user_id, _field_id('cc')); + + # last comment, or change to a bug (excluding CC changes) + my ($user_id) = @_; + return Bugzilla->dbh->selectrow_array( + <<EOF, undef, $user_id, $user_id, _field_id('cc')); SELECT MAX(bug_when) FROM ( SELECT MAX(bug_when) AS bug_when @@ -201,45 +208,39 @@ EOF # for performance reasons hit the db directly rather than using the user object sub _get_last_statistics_ts { - my ($user_id) = @_; - return Bugzilla->dbh->selectrow_array( - "SELECT last_statistics_ts FROM profiles WHERE userid = ?", - undef, $user_id - ); + my ($user_id) = @_; + return Bugzilla->dbh->selectrow_array( + "SELECT last_statistics_ts FROM profiles WHERE userid = ?", + undef, $user_id); } sub _set_last_statistics_ts { - my ($user_id, $timestamp) = @_; - Bugzilla->dbh->do( - "UPDATE profiles SET last_statistics_ts = ? WHERE userid = ?", - undef, - $timestamp, $user_id, - ); - Bugzilla->memcached->clear({ table => 'profiles', id => $user_id }); + my ($user_id, $timestamp) = @_; + Bugzilla->dbh->do("UPDATE profiles SET last_statistics_ts = ? WHERE userid = ?", + undef, $timestamp, $user_id,); + Bugzilla->memcached->clear({table => 'profiles', id => $user_id}); } sub _update_statistics { - my ($statistics, $name, $values, $sql) = @_; - my ($count) = Bugzilla->dbh->selectrow_array($sql, undef, @$values); - if (!exists $statistics->{$name}) { - $statistics->{$name} = { - id => 0, - count => $count, - dirty => 1, - }; - } elsif ($statistics->{$name}->{count} != $count) { - $statistics->{$name}->{count} = $count; - $statistics->{$name}->{dirty} = 1; - }; + my ($statistics, $name, $values, $sql) = @_; + my ($count) = Bugzilla->dbh->selectrow_array($sql, undef, @$values); + if (!exists $statistics->{$name}) { + $statistics->{$name} = {id => 0, count => $count, dirty => 1,}; + } + elsif ($statistics->{$name}->{count} != $count) { + $statistics->{$name}->{count} = $count; + $statistics->{$name}->{dirty} = 1; + } } sub _activity_by_status { - my ($by_status, $user_id) = @_; - my $dbh = Bugzilla->dbh; + my ($by_status, $user_id) = @_; + my $dbh = Bugzilla->dbh; - # we actually track both status and resolution changes as statuses - my @values = ($user_id, _field_id('bug_status'), $user_id, _field_id('resolution')); - my $rows = $dbh->selectall_arrayref(<<EOF, { Slice => {} }, @values); + # we actually track both status and resolution changes as statuses + my @values + = ($user_id, _field_id('bug_status'), $user_id, _field_id('resolution')); + my $rows = $dbh->selectall_arrayref(<<EOF, {Slice => {}}, @values); SELECT added AS status, COUNT(*) AS count FROM bugs_activity WHERE who = ? @@ -254,29 +255,26 @@ sub _activity_by_status { GROUP BY added EOF - foreach my $row (@$rows) { - my $status = $row->{status}; - if (!exists $by_status->{$status}) { - $by_status->{$status} = { - id => 0, - count => $row->{count}, - dirty => 1, - }; - } elsif ($by_status->{$status}->{count} != $row->{count}) { - $by_status->{$status}->{count} = $row->{count}; - $by_status->{$status}->{dirty} = 1; - } + foreach my $row (@$rows) { + my $status = $row->{status}; + if (!exists $by_status->{$status}) { + $by_status->{$status} = {id => 0, count => $row->{count}, dirty => 1,}; + } + elsif ($by_status->{$status}->{count} != $row->{count}) { + $by_status->{$status}->{count} = $row->{count}; + $by_status->{$status}->{dirty} = 1; } + } } sub _activity_by_product { - my ($by_product, $user_id) = @_; - my $dbh = Bugzilla->dbh; + my ($by_product, $user_id) = @_; + my $dbh = Bugzilla->dbh; - my %products; + my %products; - # changes - my $rows = $dbh->selectall_arrayref(<<EOF, { Slice => {} }, $user_id); + # changes + my $rows = $dbh->selectall_arrayref(<<EOF, {Slice => {}}, $user_id); SELECT products.name AS product, count(*) AS count FROM bugs_activity INNER JOIN bugs ON bugs.bug_id = bugs_activity.bug_id @@ -284,10 +282,10 @@ sub _activity_by_product { WHERE who = ? GROUP BY bugs.product_id EOF - map { $products{$_->{product}} += $_->{count} } @$rows; + map { $products{$_->{product}} += $_->{count} } @$rows; - # comments - $rows = $dbh->selectall_arrayref(<<EOF, { Slice => {} }, $user_id); + # comments + $rows = $dbh->selectall_arrayref(<<EOF, {Slice => {}}, $user_id); SELECT products.name AS product, count(*) AS count FROM longdescs INNER JOIN bugs ON bugs.bug_id = longdescs.bug_id @@ -295,96 +293,89 @@ EOF WHERE who = ? GROUP BY bugs.product_id EOF - map { $products{$_->{product}} += $_->{count} } @$rows; - - # store only the top 10 and 'other' (which is an empty string) - my @sorted = sort { $products{$b} <=> $products{$a} } keys %products; - my @other; - @other = splice(@sorted, 10) if scalar(@sorted) > 10; - map { $products{''} += $products{$_} } @other; - push @sorted, '' if $products{''}; - - # update by_product - foreach my $product (@sorted) { - if (!exists $by_product->{$product}) { - $by_product->{$product} = { - id => 0, - count => $products{$product}, - dirty => 1, - }; - } elsif ($by_product->{$product}->{count} != $products{$product}) { - $by_product->{$product}->{count} = $products{$product}; - $by_product->{$product}->{dirty} = 1; - } + map { $products{$_->{product}} += $_->{count} } @$rows; + + # store only the top 10 and 'other' (which is an empty string) + my @sorted = sort { $products{$b} <=> $products{$a} } keys %products; + my @other; + @other = splice(@sorted, 10) if scalar(@sorted) > 10; + map { $products{''} += $products{$_} } @other; + push @sorted, '' if $products{''}; + + # update by_product + foreach my $product (@sorted) { + if (!exists $by_product->{$product}) { + $by_product->{$product} = {id => 0, count => $products{$product}, dirty => 1,}; } - foreach my $product (keys %$by_product) { - if (!grep { $_ eq $product } @sorted) { - delete $by_product->{$product}; - } + elsif ($by_product->{$product}->{count} != $products{$product}) { + $by_product->{$product}->{count} = $products{$product}; + $by_product->{$product}->{dirty} = 1; } + } + foreach my $product (keys %$by_product) { + if (!grep { $_ eq $product } @sorted) { + delete $by_product->{$product}; + } + } } our $_field_id_cache; + sub _field_id { - my ($name) = @_; - if (!$_field_id_cache) { - my $rows = Bugzilla->dbh->selectall_arrayref("SELECT id, name FROM fielddefs"); - foreach my $row (@$rows) { - $_field_id_cache->{$row->[1]} = $row->[0]; - } + my ($name) = @_; + if (!$_field_id_cache) { + my $rows = Bugzilla->dbh->selectall_arrayref("SELECT id, name FROM fielddefs"); + foreach my $row (@$rows) { + $_field_id_cache->{$row->[1]} = $row->[0]; } - return $_field_id_cache->{$name}; + } + return $_field_id_cache->{$name}; } sub _get_stats { - my ($user_id, $table, $name_field) = @_; - my $result = {}; - my $rows = Bugzilla->dbh->selectall_arrayref( - "SELECT * FROM $table WHERE user_id = ?", - { Slice => {} }, - $user_id, - ); - foreach my $row (@$rows) { - unless (defined $row->{$name_field}) { - print "$user_id $table $name_field\n"; - die; - } - $result->{$row->{$name_field}} = { - id => $row->{id}, - count => $row->{count}, - dirty => 0, - } + my ($user_id, $table, $name_field) = @_; + my $result = {}; + my $rows + = Bugzilla->dbh->selectall_arrayref("SELECT * FROM $table WHERE user_id = ?", + {Slice => {}}, $user_id,); + foreach my $row (@$rows) { + unless (defined $row->{$name_field}) { + print "$user_id $table $name_field\n"; + die; } - return $result; + $result->{$row->{$name_field}} + = {id => $row->{id}, count => $row->{count}, dirty => 0,}; + } + return $result; } sub _set_stats { - my ($statistics, $user_id, $table, $name_field) = @_; - my $dbh = Bugzilla->dbh; - foreach my $name (keys %$statistics) { - next unless $statistics->{$name}->{dirty}; - if ($statistics->{$name}->{id}) { - $dbh->do( - "UPDATE $table SET count = ? WHERE user_id = ? AND $name_field = ?", - undef, - $statistics->{$name}->{count}, $user_id, $name, - ); - } else { - $dbh->do( - "INSERT INTO $table(user_id, $name_field, count) VALUES (?, ?, ?)", - undef, - $user_id, $name, $statistics->{$name}->{count}, - ); - } + my ($statistics, $user_id, $table, $name_field) = @_; + my $dbh = Bugzilla->dbh; + foreach my $name (keys %$statistics) { + next unless $statistics->{$name}->{dirty}; + if ($statistics->{$name}->{id}) { + $dbh->do( + "UPDATE $table SET count = ? WHERE user_id = ? AND $name_field = ?", + undef, $statistics->{$name}->{count}, + $user_id, $name, + ); + } + else { + $dbh->do( + "INSERT INTO $table(user_id, $name_field, count) VALUES (?, ?, ?)", + undef, $user_id, $name, $statistics->{$name}->{count}, + ); } + } } sub _has_dirty { - my ($statistics) = @_; - foreach my $name (keys %$statistics) { - return 1 if $statistics->{$name}->{dirty}; - } - return 0; + my ($statistics) = @_; + foreach my $name (keys %$statistics) { + return 1 if $statistics->{$name}->{dirty}; + } + return 0; } 1; |