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#5643
Pull Request : fossasia/open-event-server#5728
Documentation | Postgresql: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
Documentation | Citext: https://www.postgresql.org/docs/9.1/citext.html 


Continue ReadingMaking Email IDs Case Insensitive

Allow Same Discount/Access Code for Multiple Events in the Open Event Server

In this Blog-Post, I will show how to allow the system to create the same Discount/Access Code for multiple events in the Open Event Server.

What was the issue:

The main problem was that the server used to identify the discount code and access code based on the discount code/access code itself, which did not allow multiple events to have the same discount/access codes.

Can you think of a better solution to this?
Yes, we should have been searching for it based on the discount/access code as well as the event they are associated with.

Changing the endpoint:

Now to do so, we want to pass the id of the event as well as discount/access code itself with the endpoint so that we can search the database based on the event_id and the code itself.  

Changes in Discount/Access Code Endpoint:

'/event/<int:discount_event_id>/discount-code/<code>'
'/event/<int:access_event_id>/access-code/<code>'

Change logic for database search:

Now when searching for discount/access code in the database, we need to pass the event_id along with the discount/access code, so that we can get the column of discount/access code associated with that event, even if we have multiple discount/access code with the same name for a different event. 

Changes in Database search logic:

access = db.session.query(AccessCode).filter_by(code=kwargs.get('code')
event_id = kwargs.get('access_event_id')).first()

discount = db.session.query(DiscountCode).filter_by(code=kwargs.get('code'),
event_id = kwargs.get('discount_event_id')).first()

Change endpoint in API docs and update Dredd hooks:

Now that we have changed the endpoint to get a discount/access code, we need to change API docs as well as Dredd hooks to accommodate the change in API docs.

Changes in API docs:

## Get Discount Code Detail using the code [/v1/event/{event_id}/discount-code/{code}]
## Get Access Code Detail using the code [/v1/event/{event_id}/access-code/{code}]

Changes in Dredd Hooks:

In discount code hook:

discount_code.event_id = 1

In access code hook:

event = EventFactoryBasic()
db.session.add(event)
db.session.commit()

Resources:

Link to Issue: fossasia/open-event-server#6027
Link to PR: fossasia/open-event-server#6208

Continue ReadingAllow Same Discount/Access Code for Multiple Events in the Open Event Server