35 lines
1.2 KiB
SQL
35 lines
1.2 KiB
SQL
-- Safely normalize emails to lowercase, handling duplicates
|
|
-- For password table: delete mixed-case versions if lowercase already exists, then lowercase the rest
|
|
|
|
-- Password table (email is primary key)
|
|
DELETE FROM password
|
|
WHERE email != LOWER(email)
|
|
AND LOWER(email) IN (SELECT email FROM password WHERE email = LOWER(email));
|
|
|
|
UPDATE password SET email = LOWER(email) WHERE email != LOWER(email);
|
|
|
|
-- Usr table (composite key workspace_id + username, but email should be unique per workspace)
|
|
DELETE FROM usr
|
|
WHERE email != LOWER(email)
|
|
AND EXISTS (
|
|
SELECT 1 FROM usr u2
|
|
WHERE u2.workspace_id = usr.workspace_id
|
|
AND u2.email = LOWER(usr.email)
|
|
);
|
|
|
|
UPDATE usr SET email = LOWER(email) WHERE email != LOWER(email);
|
|
|
|
-- Email_to_igroup table
|
|
DELETE FROM email_to_igroup
|
|
WHERE email != LOWER(email)
|
|
AND EXISTS (
|
|
SELECT 1 FROM email_to_igroup e2
|
|
WHERE e2.igroup = email_to_igroup.igroup
|
|
AND e2.email = LOWER(email_to_igroup.email)
|
|
);
|
|
|
|
UPDATE email_to_igroup SET email = LOWER(email) WHERE email != LOWER(email);
|
|
|
|
-- Token table (token is primary key, email is just a column - duplicates are OK)
|
|
UPDATE token SET email = LOWER(email) WHERE email != LOWER(email);
|