Usernames are not normalized consistently in databases

[ermsdev]

Some usernames used to be all lowercase, but now most are Sentence.Case when received from university authentication. Students with inconsistent usernames do not show up in tables joined on username.

Usernames should be consistently normalized across the python server, either before saving after receiving the username from the university, or before usage.

Ideally, we should join on wwuid where possible.

[ermsdev]

A temporary fix is to propagate the users table usernames to the profiles table and the profileviews table.

I used the following script to update the tables in people.db. Hopefully most of the queries outside of people.db query the users table, use wwuid, or use LIKE instead of = in conditions.

SELECT 
    users.username as u_uname,
    users.wwuid as u_wwuid,
    profiles.username as p_uname,
    profiles.wwuid as p_wwuid
FROM users JOIN profiles ON u_wwuid = p_wwuid
WHERE u_uname != p_uname;

DROP TABLE IF EXISTS users_profiles;

/* create a table that relates new usernames in users to old usernames in profiles */
CREATE TEMP TABLE users_profiles 
AS SELECT 
    users.username as u_uname,
    users.wwuid as u_wwuid,
    profiles.username as p_uname,
    profiles.wwuid as p_wwuid
FROM users JOIN profiles ON u_wwuid = p_wwuid
WHERE u_uname != p_uname;

SELECT * FROM users_profiles;

/* update the profileviews table to the new usernames from users where similar to profiles table*/
UPDATE profileviews SET viewed = (
    SELECT u_uname
    FROM users_profiles
    WHERE profileviews.viewed LIKE p_uname
)
WHERE EXISTS (
    SELECT u_uname
    FROM users_profiles
    WHERE profileviews.viewed LIKE p_uname
);

/* update the profileviews table to the new usernames from users where similar to users table*/
UPDATE profileviews SET viewed = (
    SELECT u_uname
    FROM users_profiles
    WHERE profileviews.viewed LIKE u_uname
)
WHERE EXISTS (
    SELECT u_uname
    FROM users_profiles
    WHERE profileviews.viewed LIKE u_uname
);

/* update the profiles table to the new usernames */
UPDATE profiles SET username = (
    SELECT u_uname
    FROM users_profiles
    WHERE profiles.wwuid = p_wwuid
)
WHERE EXISTS (
    SELECT u_uname
    FROM users_profiles
    WHERE profiles.wwuid = u_wwuid
);

DROP TABLE IF EXISTS users_profiles;

SELECT 
    users.username as u_uname,
    users.wwuid as u_wwuid,
    profiles.username as p_uname,
    profiles.wwuid as p_wwuid
FROM users JOIN profiles ON u_wwuid = p_wwuid
WHERE u_uname != p_uname;

SELECT * FROM profileviews;
SELECT * FROM users;
SELECT * FROM profiles;

The profileviews.viewed field is normally taken from the profiles.username column when someone receives a view, so it is not updated when the user receives a new username from the university.