504433 - Make tokens table query friendly when parsing tokens.eventdata

Status: RESOLVED DUPLICATE of bug 504411

(Bugzilla :: Administration, task)

Description

[Michael Thomas (Mockodin)]

Open to address issue found in Bug 504411

Issue:
Parsing of of tokens.eventdata. Currently value changes such as an email address change, store both the new and old values in the eventdata field

Issue Example:

Email Change Tokens used by Bugzilla::User->is_available_username():

<userid>| <issuedate> | <token> | <tokentype> |       <eventdata>
1           <date>      rtwretc   oldemail      [e-mail]:sgirl@gmail
1           <date>      qwxerqw   newemail      [e-mail]:sgirl@gmail


To re-extract the email, the eventdata field must be parsed for the location of ':' then run through a substring function.

Example: (Bugzilla::User)
my $eventdata = $dbh->selectrow_array(
        SELECT eventdata
           FROM tokens
          WHERE (tokentype = 'emailold'
                AND SUBSTRING(eventdata, 1, (" .
                    $dbh->sql_position(q{':'}, 'eventdata') . "-  1)) = ?)
             OR (tokentype = 'emailnew'
                AND SUBSTRING(eventdata, (" .
                    $dbh->sql_position(q{':'}, 'eventdata') . "+ 1 ), length( eventdata ) ) = ?)",
         undef, ($username, $username));

Bug 487443 was created to address MSSQL requirements for length to always be included. This change exposed the issue reported in Bug 504411.

Expected Functionality:

Eventdata should be easily queried via a simple select where eventdata equals statement.

Recommended Solution:
Split eventdata into two fields:

   eventdata_oldvalue
   eventdata_newvalue

Both fields should allow null or default as empty string

Example New query with modified table structure:
my $eventdata = $dbh->selectrow_array(
        SELECT eventdata
           FROM tokens
          WHERE (tokentype = 'emailold'
                AND eventdata_oldvalue = ?)
             OR (tokentype = 'emailnew'
                AND eventdata_newvalue = ?)",
         undef, ($username, $username));


This update will require all touch points for tokens to reviewed for update.
Existing data in tokens.eventdata should be parsed in to the new fields and the old eventdata column dropped.

Several indexes should be applied to the new columns to increase processing speed as well.

Index possibilities:
ind1: tokentype                    NonUnique
ind2: eventdata_olddata            NonUnique
ind3: eventdata_newdata            NonUnique
ind4: tokentype,eventdata_newdata  NonUnique
ind5: tokentype,eventdata_newdata  NonUnique

Indexes are not my strong point so comments on which indexes are redundant would be helpful, else all should be included.

Comment 1

[Frédéric Buclin]

Wait, I disagree with this. The old and new email address must be tied together, else you have no idea which old email address is related to which new one. Moreover, you still haven't answered my comment in bug 504411 where I say that the problem described there cannot happen.

Comment 2

[Michael Thomas (Mockodin)]

They remain tied together in separate fields same record.

This is purely splitting the field in two there is not loss of data or correlation. Also performance gains be applying indexes, which should probably exist anyhow as is.

Comment 3

[Max Kanat-Alexander]

You don't need separate bugs to address other bugs.