From 8de2aaf9e213651afaf12ec10b1091c22b7a9c55 Mon Sep 17 00:00:00 2001 From: Dylan William Hardison Date: Tue, 10 Jul 2018 15:17:08 -0400 Subject: Bug 1469911 - Make user autocompletion faster --- Bugzilla/DB.pm | 8 +++ Bugzilla/Install/DB.pm | 12 +++++ Bugzilla/User.pm | 35 +++++++++---- Bugzilla/WebService/Server/REST/Resources/User.pm | 5 ++ Bugzilla/WebService/User.pm | 62 +++++++++++++++++++++++ js/field.js | 2 +- 6 files changed, 112 insertions(+), 12 deletions(-) diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 33801e989..80404131a 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -350,6 +350,14 @@ sub import { $Exporter::ExportLevel-- if $is_exporter; } +sub sql_prefix_match { + my ($self, $column, $str) = @_; + my $must_escape = $str =~ s/([_%!])/!$1/g; + my $escape = $must_escape ? q/ESCAPE '!'/ : ''; + my $quoted_str = $self->quote("$str%"); + return "$column LIKE $quoted_str $escape"; +} + sub sql_istrcmp { my ($self, $left, $right, $op) = @_; $op ||= "="; diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm index d81bcfbdc..2e5ae5ff2 100644 --- a/Bugzilla/Install/DB.pm +++ b/Bugzilla/Install/DB.pm @@ -773,6 +773,7 @@ sub update_table_definitions { $dbh->bz_add_index('profiles', 'profiles_realname_ft_idx', {TYPE => 'FULLTEXT', FIELDS => ['realname']}); + _migrate_nicknames(); ################################################################ # New --TABLE-- changes should go *** A B O V E *** this point # @@ -3911,6 +3912,17 @@ sub _migrate_group_owners { $dbh->do('UPDATE groups SET owner_user_id = ?', undef, $nobody->id); } +sub _migrate_nicknames { + my $dbh = Bugzilla->dbh; + my $sth = $dbh->prepare('SELECT userid FROM profiles WHERE realname LIKE "%:%" AND is_enabled = 1 AND NOT nickname'); + $sth->execute(); + while (my ($user_id) = $sth->fetchrow_array) { + my $user = Bugzilla::User->new($user_id); + $user->set_name($user->name); + $user->update(); + } +} + sub _migrate_preference_categories { my $dbh = Bugzilla->dbh; return if $dbh->bz_column_info('setting', 'category'); diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm index dc8f60565..9faed25cc 100644 --- a/Bugzilla/User.pm +++ b/Bugzilla/User.pm @@ -80,7 +80,8 @@ sub DB_COLUMNS { 'profiles.password_change_required', 'profiles.password_change_reason', 'profiles.mfa', - 'profiles.mfa_required_date' + 'profiles.mfa_required_date', + 'profiles.nickname' ), } @@ -94,6 +95,7 @@ use constant VALIDATORS => { disabledtext => \&_check_disabledtext, login_name => \&check_login_name_for_creation, realname => \&_check_realname, + nickname => \&_check_realname, extern_id => \&_check_extern_id, is_enabled => \&_check_is_enabled, password_change_required => \&Bugzilla::Object::check_boolean, @@ -114,6 +116,7 @@ sub UPDATE_COLUMNS { password_change_reason mfa mfa_required_date + nickname ); push(@cols, 'cryptpassword') if exists $self->{cryptpassword}; return @cols; @@ -478,10 +481,25 @@ sub set_login { delete $self->{nick}; } +sub _generate_nickname { + my ($name, $login) = @_; + my ($nick) = extract_nicks($name); + if (!$nick) { + $nick = ""; + } + return $nick; +} + sub set_name { my ($self, $name) = @_; $self->set('realname', $name); delete $self->{identity}; + $self->set('nickname', _generate_nickname($name, $self->login)); +} + +sub set_nick { + my ($self, $nick) = @_; + $self->set('nickname', $nick); } sub set_password { @@ -726,12 +744,8 @@ sub nick { my $self = shift; return "" unless $self->id; - - if (!defined $self->{nick}) { - $self->{nick} = (split(/@/, $self->login, 2))[0]; - } - - return $self->{nick}; + return $self->{nickname} if $self->{nickname}; + return $self->{nick} //= (split(/@/, $self->login, 2))[0]; } sub queries { @@ -2514,13 +2528,12 @@ sub get_userlist { } sub create { - my $invocant = shift; - my $class = ref($invocant) || $invocant; + my ($class, $params) = @_; my $dbh = Bugzilla->dbh; $dbh->bz_start_transaction(); - - my $user = $class->SUPER::create(@_); + $params->{nickname} = _generate_nickname($params->{realname}, $params->{login_name}); + my $user = $class->SUPER::create($params); # Turn on all email for the new user require Bugzilla::BugMail; diff --git a/Bugzilla/WebService/Server/REST/Resources/User.pm b/Bugzilla/WebService/Server/REST/Resources/User.pm index eb44e9d2d..6185237fb 100644 --- a/Bugzilla/WebService/Server/REST/Resources/User.pm +++ b/Bugzilla/WebService/Server/REST/Resources/User.pm @@ -20,6 +20,11 @@ BEGIN { sub _rest_resources { my $rest_resources = [ + qr{^/user/suggest$}, { + GET => { + method => 'suggest', + }, + }, qr{^/valid_login$}, { GET => { method => 'valid_login' diff --git a/Bugzilla/WebService/User.pm b/Bugzilla/WebService/User.pm index 5f9b54787..5bb5e32c1 100644 --- a/Bugzilla/WebService/User.pm +++ b/Bugzilla/WebService/User.pm @@ -24,6 +24,7 @@ use Bugzilla::WebService::Util qw(filter filter_wants validate use Bugzilla::Hook; use List::Util qw(first); +use Taint::Util qw(untaint); # Don't need auth to login use constant LOGIN_EXEMPT => { @@ -33,6 +34,7 @@ use constant LOGIN_EXEMPT => { use constant READ_ONLY => qw( get + suggest ); use constant PUBLIC_METHODS => qw( @@ -135,6 +137,66 @@ sub create { return { id => $self->type('int', $user->id) }; } +sub suggest { + my ($self, $params) = @_; + + Bugzilla->switch_to_shadow_db(); + + ThrowCodeError('params_required', { function => 'User.suggest', params => ['match'] }) + unless defined $params->{match}; + + ThrowUserError('user_access_by_match_denied') + unless Bugzilla->user->id; + + untaint($params->{match}); + my $s = $params->{match}; + trim($s); + return { users => [] } if length($s) < 3; + + my $dbh = Bugzilla->dbh; + my @select = ('realname AS real_name', 'login_name AS name'); + my $order = 'last_seen_date DESC'; + my $where; + state $have_mysql = $dbh->isa('Bugzilla::DB::Mysql'); + + if ($s =~ /^[:@](.+)$/s) { + $where = $dbh->sql_prefix_match(nickname => $1); + } + elsif ($s =~ /@/) { + $where = $dbh->sql_prefix_match(login_name => $s); + } + else { + if ($have_mysql && ( $s =~ /[[:space:]]/ || $s =~ /[^[:ascii:]]/ ) ) { + my $match = sprintf 'MATCH(realname) AGAINST (%s) ', $dbh->quote($s); + push @select, "$match AS relevance"; + $order = 'relevance DESC'; + $where = $match; + } + elsif ($have_mysql && $s =~ /^[[:upper:]]/) { + my $match = sprintf 'MATCH(realname) AGAINST (%s) ', $dbh->quote($s); + $where = join ' OR ', + $match, + $dbh->sql_prefix_match( nickname => $s ), + $dbh->sql_prefix_match( login_name => $s ); + } + else { + $where = join ' OR ', $dbh->sql_prefix_match( nickname => $s ), $dbh->sql_prefix_match( login_name => $s ); + } + } + $where = "($where) AND is_enabled = 1"; + + my $sql = 'SELECT ' . join(', ', @select) . " FROM profiles WHERE $where ORDER BY $order LIMIT 25"; + my $results = $dbh->selectall_arrayref($sql, { Slice => {} }); + + my @users = map { + { + real_name => $self->type(string => $_->{real_name}), + name => $self->type(email => $_->{name}), + } + } @$results; + + return { users => \@users }; +} # function to return user information by passing either user ids or # login names or both together: diff --git a/js/field.js b/js/field.js index a5e204f8d..ddf6b8b1c 100644 --- a/js/field.js +++ b/js/field.js @@ -715,7 +715,7 @@ $(function() { var options_user = { appendTo: $('#main-inner'), forceFixPosition: true, - serviceUrl: 'rest/elastic/suggest_users', + serviceUrl: 'rest/user/suggest', params: { Bugzilla_api_token: BUGZILLA.api_token, }, -- cgit v1.2.3-24-g4f1b