Making Email IDs Case Insensitive
In this Blog-Post, I will show how I made email-id case insensitive so that the open-event-server won’t allow multiple accounts with a single handle. Previously, email ID was stored in the open-event-server database as raw string, i.e., case sensitive, so if a user entered the email with upper case alphabets, he/she needs to enter the same way during Auth, which could have lead to creation of multiple accounts with one email id and so is a major bug. Migration updation: First, we need to handle the cases on the production database where people have made multiple accounts with a single handle because of this bug. For this, we need to write a complex SQL query, so let’s just make a new migration instead of updating the models. def upgrade(): op.execute("UPDATE users SET deleted_at = current_timestamp, _email = concat(_email, '_') where _email not in (SELECT DISTINCT ON (upper(_email)) _email FROM users);", execution_options=None) The nested query will return the list of all Emails which are not distinct on upper(_email). In simple words, the following query says: “Get the _email from users table where upper(_email) is distinct”. We want to delete the emails which are not returned in this query. For that, we set “deleted_at = current_timestamp” for those row. Make ‘_email’ column case insensitive: We need to change the column data type to something that treats email as case-insensitive. That’s where “citext” comes into play. But we will still get an error on applying citext to “_email” column. Any guesses why? Remember we just updated “deleted_at” column of duplicate _email rows. So there still exists duplicate entries of emails in case we treat them case-insensitive, which defies the “_email” column rule to have unique entries defined in “user” Model. As it’s never a good idea to delete entries on the production database, we add a “_” to distinguish those email ids. Now the following lines will change the column type of “_email” to citext. op.execute("create extension citext;", execution_options=None) Now, we need to write the opposite logic in case someone downgrades from the migration. def downgrade(): op.execute("alter table users alter column _email type text;",execution_options=None) op.execute("UPDATE users SET deleted_at = null, _email = left(_email, length(_email)-1) where right(_email, 1) = '_';",execution_options=None) op.execute("drop extension citext;",execution_options=None) This will solve our bug, and won’t allow multiple accounts from the same handle. Resources: Issue: fossasia/open-event-server#5643Pull Request : fossasia/open-event-server#5728Documentation | Postgresql: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCTDocumentation | Citext: https://www.postgresql.org/docs/9.1/citext.html
