Reducing the YouTube response time by 90%

In this blog post, we are going to cover how the audio from Youtube is being used in SUSI Smart Speaker and how we reduced the response time from ~40 seconds to ~4 seconds for an average music video length.

First Approach

Earlier, we were using MPV player’s inbuilt feature to fetch the YouTube music. However, MPV player was a bulky option and the music server had to be started every time before initiating a music video.

video_process = subprocess.Popen([‘mpv’, ‘–no-video’, ‘https://www.youtube.com/watch?v=’ + video_url[4:], ‘–really-quiet’]) # nosec #pylint-disable type: ignore requests.get(‘http://localhost:7070/song/’ + video_url) self.video_process = video_process stopAction.run() stopAction.detector.terminate()

Making it Efficient

To reduce the response time, we created a custom Music Server based on Flask,python-vlc and python-pafy which accepts requests from the main client and instructs the System to play the music with just 90% more efficiency.

app = Flask(__name__)

Instance = vlc.Instance(‘–no-video’)

player = Instance.media_player_new()

url = @app.route(‘/song’, methods=[‘GET’])

def youtube():

    vid = request.args.get(‘vid’)

    url = ‘https://www.youtube.com/watch?v=’ + vid

    video = pafy.new(url)
    streams = video.audiostreams 

    best = streams[3]

    playurl = best.url

    Media = Instance.media_new(playurl)

    Media.get_mrl()

    player.set_media(Media)

    player.play()

    display_message = {“song”:“started”}

    resp = jsonify(display_message)

    resp.status_code = 200

    return resp

However, shifting to this Server removed the ability to process multiple queries and hence we were unable to pause/play/stop the music until it completed the time duration. We wanted to retain the ability to have ‘play/pause/stop’ actions without implementing multiprocessing or multithreading as it would’ve required extensive testing to successfully implement them without creating deadlocks and would’ve been overkill for a simple feature.

Bringing Back the Lost Functionalities

The first Step we took was to remove the vlc-python module and implement a way to obtain an URL that we use in another asynchronous music player.

@app.route(‘/song’, methods=[‘GET’])
def youtube():

    vid = request.args.get(‘vid’)

    streams = video.audiostreams

    best = streams[3]

    playurl = best.url 

    display_message = {“song”: “started”, “url”: playurl}

    resp = jsonify(display_message)

    resp.status_code = 200

    return resp

The next issue was to actually find a way to run the Music Player asynchronously. We used the `subprocess. Popen` method and cvlc to play the songs asynchronously.

try:

    x = requests.get(‘http://localhost:7070/song?vid=’ + video_url[4:])

    data = x.json()

    url = data[‘url’]

    video_process = subprocess.Popen([‘cvlc’, ‘http’ + url[5:], ‘–no-video’])

    self.video_process = video_process

except Exception as e:

    logger.error(e);

And this is how we were able to increase the efficiency of the music player while maintaining the functionalities.

References

Continue ReadingReducing the YouTube response time by 90%

Cloud Function For Sending Mail On Badge Generation in Badgeyay

The task of badgeyay is to generate badges for events and if the user has provided a large data set, then the system will take time to generate badges and we cannot hold the user on the same page for that time. So instead of showing the user the link of the generated badge on the form, what we can do is that we can send a mail to user with the link of generated badge. However listening for the completion of generated badge from the cloud function is not possible, as cloud functions are based on serverless architecture. This can be done using the listeners for the realtime database events.

Generated badge from the backend will be uploaded to the Firebase Storage, but applying a listener for storage events, will not give us the information of the sender and some other metadata. So after uploading the link on the database, we can use the public link generated and can push a dict to the realtime database with the necessary user information for sending mail.

Procedure

  1. Fetching the necessary information to be pushed on the Firebase realtime database.
def send_badge_mail(badgeId, userId, badgeLink):
  ref = firebase_db.reference(‘badgeMails’)
  print(‘Pushing badge generation mail to : ‘, badgeId)
  ref.child(userId).child(datetime.datetime.utcnow().isoformat().replace(‘-‘, ‘_’).replace(‘:’, ‘U’).replace(‘.’, ‘D’)).set({
      ‘badgeId’: badgeId,
      ‘badgeLink’: badgeLink
  })
  print(‘Pushed badge generation mail to : ‘, badgeId)

 

Payload consists of the downloadable link of the badge and the root node is the userID. So whenever any node gets created in this format them the cloud function will be called.

  1. Listening for the database changes at the state of node.
exports.sendBadgeMail = functions.database.ref(‘/badgeMails/{userId}/{date}’)
.onCreate((snapshot, context) => {
  const payload = snapshot.val();
  const uid = context.params.userId;
  return admin.auth().getUser(uid)
    .then(record => {
      return sendBadgeGenMail(uid, record.email, record.displayName, payload[‘badgeId’], payload[‘badgeLink’]);
    })
    .catch(() => { return -1 });
});

 

For the realtime database listener, it should listen to node in the form of badgeMails/<user_id>/<date> as whenever a node of such form will be created in the database the function will get triggered and not for any other data insertions in db. This will save the quota for the cloud function execution.

  1. Sending mail to the user from the payload
function sendBadgeGenMail(uid, email, displayName, badgeId, badgeLink) {
const mailOptions = {
  from: `${APP_NAME}<noreply@firebase.com>`,
  to: email,
};

mailOptions.subject = `Badge Generated ${badgeId}`;
mailOptions.html = `<p> Hello ${displayName || }! Your badge is generated successfully, please visit the <a href=${badgeLink}>link</a> to download badge</p>`;
return mailTransport.sendMail(mailOptions).then(() => {
  writeMailData(uid, “success”, 3);
  return console.log(‘Badge mail sent to: ‘, email)
}).catch((err) => {
  console.error(err.message);
  return -1;
});
}

 

This will send the mail to the user with the generated link.

Pull Request for the above feature at link : Link

Outcome:

 

Resources

Continue ReadingCloud Function For Sending Mail On Badge Generation in Badgeyay

Onsite Attendee in Open Event Server

The Open Event Server enables organizers to manage events from concerts to conferences and meetups. It offers features for events with several tracks and venues. The Event organizers may add orders on behalf of others and accept payments onsite. This blog post goes over the implementation of the onsite attendee feature in the Open Event Server.

Route

Normally we expect the payload for a POST request of order to contain already created attendees also. In this case we want to create the attendees internally inside the server. Hence we need some way to differentiate between the two types of orders. The most basic and easy to implement option is to use a query parameter to specify if the attendees are onsite or not. We use ?onsite=true in order to specify that the attendees are onsite and hence should be created internally.

In the POST request, we check if the query parameters contains the onsite param as true or not. If it is true then we create the attendees using a helper function. The helper function will be discussed in detail later in the article.

# Create on site attendees.
if request.args.get('onsite', False):
    create_onsite_attendees_for_order(data)
elif data.get('on_site_tickets'):
    del data['on_site_tickets']
require_relationship(['ticket_holders'], data)

 

OnsiteTicketSchema

In order to create attendees on the server, we need the information about each ticket bought and it’s quantity. This data is expected in the format declared in the OnsiteTicketSchema.

class OnSiteTicketSchema(SoftDeletionSchema):
    class Meta:
        type_ = 'on-site-ticket'
        inflect = dasherize

    id = fields.Str(load_only=True, required=True)
    quantity = fields.Str(load_only=True, required=True)

Creating onsite Attendees

Following are the few points which we need to focus on when creating onsite attendees:

  1. Validate if the ticket’s data is provided or not. We raise an error if the ticket data is not provided.
  2. Verify if the ticket is sold out or not. We raise an error if the ticket is sold out.
  3. In case an error is raised in any step then we delete the already created attendees. This is a very important point to keep in mind.

if not on_site_tickets:
        raise UnprocessableEntity({'pointer': 'data/attributes/on_site_tickets'}, 'on_site_tickets info missing')

ticket_sold_count = get_count(db.session.query(TicketHolder.id).
                                      filter_by(ticket_id=int(ticket.id), deleted_at=None))

        # Check if the ticket is already sold out or not.
        if ticket_sold_count + quantity > ticket.quantity:
            # delete the already created attendees.
            for holder in data['ticket_holders']:
                ticket_holder = db.session.query(TicketHolder).filter(id == int(holder)).one()
                db.session.delete(ticket_holder)
                try:
                    db.session.commit()
                except Exception as e:
                    logging.error('DB Exception! %s' % e)
                    db.session.rollback()

            raise ConflictException(
                {'pointer': '/data/attributes/on_site_tickets'},
                "Ticket with id: {} already sold out. You can buy at most {} tickets".format(ticket_id,
                                                                                             ticket.quantity -
                                                                                             ticket_sold_count)
            )

The complete method can be checked here.

References

 

 

Continue ReadingOnsite Attendee in Open Event Server

Using a Flask Server to Connect to SUSI smart speaker

A smart speaker becomes significantly smarter when it is connected to a Smart-Phone.

So, we added a way to connect the Smart-Phone to the Smart Speaker and initiate the first way towards a Smart Home.

Use a simple HTTP connection protocol and deploy a light-weight server on the Raspberry Pi to allow connection from a mobile phone.

Step 1: Setting Up the server

Use flask to deploy a light-weight server on the raspberry pi. We’ll install flask using raspbian repos.

 

1>Install Flask by using the following command

sudo apt-get install python3-flask

 

2> Setting up the boilerplate code.

Open the terminal and type the following commands

`

mkdir server_app
cd server_app

touch app.py

`

 

Add the following code to your app.py file. This create a server at localhost:5000

 

from flask import Flask

app = Flask(__name__)

@app.route(‘/’)
def index():
   return ‘Hello world’

if __name__ == ‘__main__’:
   app.run(debug=False, host=‘0.0.0.0’)  #This will allow the server to be accessible on all devices

 

Step 2: Adding Endpoints

Now , add endpoints which will trigger the scripts during initialisation of the raspberry Pi. This will trigger the respective endpoints

@app.route(‘/auth/<auth>/<email>/<passwd>’)
def login(auth, email, passwd):
os.system(‘sudo ./login.sh {} {} {}’.format(auth, email,passwd)) #nosec #pylint-disable type: ignore
return ‘Authenticated’ # pylint-enable@app.route(‘/wifi_credentials/<wifissid>/<wifipassd>’)
def wifi_config(wifissid,wifipassd):
wifi_ssid = wifissid
wifi_password = wifipassd
os.system(‘sudo ./home/pi/SUSI.AI/susi_linux/access_point/wifi_search.sh {} {}’.format(wifi_ssid,wifi_password))  #nosec #pylint-disable type: ignore
return ‘Wifi Configured’ # pylint-enable

 

Step 3: Connecting to the endpoints

Now, try and hit the API endpoints to get the response.

eg.As shown in the above example, you will be getting a single line response and will execute a bash script behind the scenes

Now you can access the other endpoints and configure the clients with the SUSI Smart Speaker

References

 

Tags

fossasia,GSoC,Python, Flask , raspberryPi, SUSI,smart-speaker,FOSSASIA

Continue ReadingUsing a Flask Server to Connect to SUSI smart speaker

Adding device names’ support for check-ins to Open Event Server

The Open Event Server provides backend support to Open Event Organizer Android App which is used to check-in attendees in an event. When checking in attendees, it is important for any event organizer to keep track of the device that was used to check someone in. For this, we provide an option in the Organizer App settings to set the device name. But this device name should have support in the server as well.

The problem is to be able to add device name data corresponding to each check-in time. Currently attendees model has an attribute called `checkin-times`, which is a csv of time strings. For each value in the csv, there has to be a corresponding device name value. This could be achieved by providing a similar csv key-value pair for “device-name-checkin”.

The constraints that we need to check for while handling device names are as follows:

  • If there’s `device_name_checkin` in the request, there must be `is_checked_in` and `checkin_times` in the data as well.
  • Number of items in checkin_times csv in data should be equal to the length of the device_name_checkin csv.
  • If there’s checkin_times in data, and device-name-checkin is absent, it must be set to `-` indicating no set device name.
if ‘device_name_checkin’ in data and data[‘device_name_checkin’] is not None:
  if ‘is_checked_in’ not in data or not data[‘is_checked_in’]:
       raise UnprocessableEntity(
           {‘pointer’: ‘/data/attributes/device_name_checkin’},
           “Attendee needs to be checked in first”
       )
  elif ‘checkin_times’ not in data or data[‘checkin_times’] is None:
      raise UnprocessableEntity(
          {‘pointer’: ‘/data/attributes/device_name_checkin’},
           “Check in Times missing”
      )
  elif len(data[‘checkin_times’].split(“,”)) != len(data[‘device_name_checkin’].split(“,”)):
     raise UnprocessableEntity(
           {‘pointer’: ‘/data/attributes/device_name_checkin’},
           “Check in Times missing for the corresponding device name”
         )
 if ‘checkin_times’ in data:
   if ‘device_name_checkin’ not in data or data[‘device_name_checkin’] is None:
       data[‘device_name_checkin’] = ‘-‘

The case is a little different for a PATCH request since we need to check for the number of items differently like this:

if ‘device_name_checkin’ in data and data[‘device_name_checkin’] is not None:
            if obj.device_name_checkin is not None:
               data[‘device_name_checkin’] = ‘{},{}’.format(obj.device_name_checkin, data[‘device_name_checkin’])                                                   
            if len(data[‘checkin_times’].split(“,”)) != len(data[‘device_name_checkin’].split(“,”)):
               raise UnprocessableEntity(
                   {‘pointer’: ‘/data/attributes/device_name_checkin’},
                   “Check in Time missing for the corresponding device name”)

Since we expect only the latest value to be present in a PATCH request, we first add it to the object by formatting using:

'{},{}'.format(obj.device_name_checkin, data['device_name_checkin'])

and then compare the length of the obtained CSVs for check in times and device names, so that corresponding to each check in time, we have either a device name or the default fill in value ‘-’.

That’s all. Read the full code here.

Requests and Responses:

Resources

  1. SQLAlchemy Docs
    https://docs.sqlalchemy.org/en/latest/
  2. Alembic Docs
    http://alembic.zzzcomputing.com/en/latest/
  3. Flask REST JSON API Classical CRUD operation
    https://flask-rest-jsonapi.readthedocs.io/en/latest/quickstart.html#classical-crud-operations
Continue ReadingAdding device names’ support for check-ins to Open Event Server

Modifying Allowed Usage for a User

Badgeyay has been progressing in a very good pace. There are a lot of features being developed and modified in this project. One such feature that has been added is the increasing allowed usage of a user by an admin.

What is Allowed Usage?

Allowed usage is an integer associated with a particular user that determines the number of badges that a person can generate using a single email id. This will allow us to keep track of the number of badges being produced by a particular ID and all.

Modifying the Allowed Usage

This feature is basically an Admin feature, that will allow an admin to increase or decrease the allowed usage of a particular user. This will ensure that if incase a particular user has his/her usage finished, then by contacting the admin, he/she can get the usage refilled.

Adding the functionality

The functionality required us to to add two things

  • A schema for modifying allowed user
  • A route in backend to carry out the functionality

So, Let us start by creating the schema

class UserAllowedUsage(Schema):
class Meta:
type_ =
‘user_allowed_usage’
kwargs = {
‘id’: ‘<id>’}

id = fields.Str(required=True, dump_only=True)
allowed_usage = fields.Str(required=
True, dump_only=True)

Once we have our schema created, then we can create a route to modify the allowed usage for a particular user.

This route will be made accessible to the admin of Badgeyay.

@router.route(‘/add_usage’, methods=[‘POST’])
def admin_add_usage():
try:
data = request.get_json()[
‘data’]
print(data)
except Exception:
return ErrorResponse(JsonNotFound().message, 422, {‘Content-Type’: ‘application/json’}).respond()

uid = data[‘uid’]
allowed_usage = data[
‘allowed_usage’]
user = User.getUser(user_id=uid)
user.allowed_usage = user.allowed_usage + allowed_usage
db.session.commit()

return jsonify(UserAllowedUsage().dump(user).data)

The add_usage route is given above. We can use this route to increase the usage of a particular user.

Given below is an image that shows the API working.

Resources

Continue ReadingModifying Allowed Usage for a User

Implementing Checkout Times for Attendees on Open Event Server

As of this writing, Open Event Server did not have the functionality to add, manipulate and delete checkout times of attendees. Event organizers should have access to log and update attendee checkout times. So it was decided to implement this functionality in the server. This boiled down to having an additional attribute checkout_times in the ticket holder model of the server.

So the first step was to add a string column named checkout_times in the ticket holder database model, since this was going to be a place for comma-separated values (CSV) of attendee checkout times. An additional boolean attribute named is_checked_out was also added to convey whether an attendee has checked out or not. After the addition of these attributes in the model, we saved the file and performed the required database migration:

To create the migration file for the above changes:

$ python manage.py db migrate

To upgrade the database instance:

$ python manage.py db upgrade

Once the migration was done, the API schema file was modified accordingly:

class AttendeeSchemaPublic(SoftDeletionSchema):
    """
    Api schema for Ticket Holder Model
    """
    
    checkout_times = fields.Str(allow_none=True)  # ←
    is_checked_out = fields.Boolean()  # ←
    

After the schema change, the attendees API file had to have code to incorporate these new fields. The way it works is that when we receive an update request on the server, we add the current time in the checkout times CSV to indicate a checkout time, so the checkout times field is essentially read-only:

from datetime import datetime
...
class AttendeeDetail(ResourceDetail):
    def before_update_object(self, obj, data, kwargs):
        
        if 'is_checked_out' in data and data['is_checked_out']:
        ...
        else:
            if obj.checkout_times and data['checkout_times'] not in \
obj.checkout_times.split(","):
                data['checkout_times'] = '{},{},{}'.format(
                    obj.checkout_times,
                    data['checkout_times'],
                    datetime.utcnow())

 

This completes the implementation of checkout times, so now organizers can process attendee checkouts on the server with ease.

Resources

Continue ReadingImplementing Checkout Times for Attendees on Open Event Server

Adding Tickets Relationship with Discount Codes in Open Event Server

Recently (as of this writing), it was discovered that the relationship between discount codes and tickets was not implemented yet in Open Event Server. It turns out that the server has two types of discount codes – discount codes for entire events and discount codes for individual tickets of a specific event. More information on how discount code themselves are implemented in the server can be found in this blog post from 2017 – Discount Codes in Open Event Server.

So, for implementing the relationship of discount codes with tickets, it was decided to be present only for discount codes that have the DiscountCodeSchemaTicket schema, since those are the discount codes that are used for individual tickets. As a first step, the `tickets` attribute of the discount code model was removed, as it was redundant. The already implemented used_for attribute did the same job, and with better validation. At the same time, discount code was added as an attribute.

In the ticket model file:

discount_code_id = db.Column(db.Integer, db.ForeignKey('discount_codes.id', ondelete='CASCADE'))
discount_code = db.relationship('DiscountCode', backref="tickets")

Also, in the __init__ constructor:

def __init__(self, ..., discount_code_id=None, ...):
    ...
    ...
    self.discount_code_id = discount_code_id

After that, we added a discount_code_id field in the ticket schema file:

discount_code_id = fields.Integer(allow_none=True)

In this file, we also removed the redundant tickets field.

Now, we migrated the Open Event Server database via the following commands:

$ python manage.py db migrate

then

$ python manage.py db upgrade

Next, in the discount code schema file, we added the tickets relationship. Note that this is a one-to-many relationship. One discount code (for tickets) can be mapped to many tickets. Here is the code for that relationship, in the discount code schema file, under the DiscountCodeSchemaTicket class:

tickets = Relationship(attribute='tickets',
self_view='v1.discount_code_tickets',
self_view_kwargs={'id': '<id>'},
related_view='v1.ticket_list',
related_view_kwargs={'discount_code_id': '<id>'},
schema='TicketSchemaPublic',
many=True,
type_='ticket')

For this, we, of course, imported the TicketSchemaPublic in this file first. After that, we created a DiscountCodeTicketRelationship class in the discount codes API file:

class DiscountCodeTicketRelationship(ResourceRelationship):
    """
    DiscountCode Ticket Relationship
    """
    decorators = (jwt_required,)
    methods = ['GET', 'PATCH']
    schema = DiscountCodeSchemaTicket
    data_layer = {'session': db.session, 'model': DiscountCode}

The next step was to add the query code to fetch the tickets related to a particular discount code from the database. For this, we added the following snippet to the query() method of the TicketList class in the tickets API file:

if view_kwargs.get('discount_code_id'):
    discount_code = safe_query(self, DiscountCode, 'id', view_kwargs['discount_code_id'], 'discount_code_id')
    # discount_code - ticket :: one-to-many relationship
    query_ = self.session.query(Ticket).filter_by(discount_code_id=discount_code.id)

The only thing that remains now is adding the API routes for this relationship. We do that in the project’s __init__.py file:

api.route(TicketList, 'ticket_list', '/events/<int:event_id>/tickets',
'/events/<event_identifier>/tickets', '/ticket-tags/<int:ticket_tag_id>/tickets',
'/access-codes/<int:access_code_id>/tickets', '/orders/<order_identifier>/tickets',
'/discount-codes/<int:discount_code_id>/tickets')

api.route(DiscountCodeTicketRelationship, 'discount_code_tickets',
'/discount-codes/<int:id>/relationships/tickets')

 

Many routes already map to TicketList, we added one for that comes from discount codes API. Now we can use Postman to check this relationship, and it indeed works as expected, as seen below!

Screen Shot 2018-08-21 at 1.54.22 PM

Here’s the end:

Screen Shot 2018-08-21 at 1.54.35 PM.png

References:

Continue ReadingAdding Tickets Relationship with Discount Codes in Open Event Server

Implementing Event Average Rating with SQLAlchemy

While implementing Open Event Server version 2, we decided to have a better way of ranking events by their quality. To define the “quality” of events, the programmers decided to accumulate the feedbacks of specific events and take the average of the ratings involved. Thus, the average rating of an event proves to be a good (enough) measure of its quality. While there are many ways to implement aggregate relationships in an app, here I demonstrate a rather modern methodology which insists on storing such aggregates once they’re computed.

Since there is always a space-time/computation tradeoff in software development, this task was no exception. At first, the straightforward idea that came to my mind was to query the Postgres database every time a request for average rating was made. This sounds simple, but with hundreds of events stored on a server, and potentially thousands of users querying for events, this seemed to be a computationally expensive approach. It was costly because the average rating aggregate would be computed for each request, and there could potentially be thousands of such concurrent requests. Therefore, a better idea is to compute the aggregate once, store it in the database (compromising space in the tradeoff mentioned above, but saving a large amount of computation at the same time), and update only when a change is made. In our specific case, the update should happen only when a new rating is added, a rating is deleted or an existing rating is modified. Since the advantages outnumbered the disadvantages, this was the strategy to be implemented.

The first step in implementing average rating was to modify the database model of events accordingly. For this, I performed the necessary imports in the events’ database model file:

from sqlalchemy_utils import aggregated
from app.models.feedback import Feedback

Now comes the tricky part. We want an average_rating column in the events table, that contains the mean rating of events. The values in this column should be updated every time a change is made to the feedbacks table. To perform this sort of functionality, the best, raw tool is a Postgres trigger. A trigger should be created that is fired after every update to the feedbacks table, which should update the average rating values in the events table. Here’s how the raw code of such a trigger looks like:

create or replace function UpdateAverageRating() returns trigger AS
$$
BEGIN
UPDATE events SET average_rating=(
SELECT avg(rating) FROM feedbacks
WHERE event_id=NEW.event_id
GROUP BY event_id
)

WHERE id = NEW.event_id
END
$$
language plpgsql

Fortunately, the translation of such a trigger into SQLAlchemy-speak is not only easy, but also very elegant. The imports I showed above already set the context for this translation.

The event model class looks like the following:

class Event(db.Model):
    """Event object table"""
    __tablename__ = 'events'
    __versioned__ = {
'exclude': ['schedule_published_on', 'created_at']
}
    id = db.Column(db.Integer, primary_key=True)
    identifier = db.Column(db.String)
    name = db.Column(db.String, nullable=False)
    external_event_url = db.Column(db.String)

    

    

    

The list of attributes continues, and to the end of this list, we now add a decorated method:




xcal_url = db.Column(db.String)
is_sponsors_enabled = db.Column(db.Boolean, default=False)
discount_code_id = db.Column(db.Integer, db.ForeignKey(
'discount_codes.id', ondelete='CASCADE'))

@aggregated('feedbacks', db.Column(db.Float))
def average_rating(self):
    return db.func.avg(Feedback.rating)

That’s it with the translation – this slick, decorated method can be thought of as a bridge between Python and the trigger shown earlier that’s usually implemented in the database itself. Once this method is added, we save the model file and perform a database migration:

$ python manage.py db migrate

This generates a migration file associated with our changes. This file shows the following alembic migration code:

"""empty message

Revision ID: 1471fe0d04ee
Revises: 49f3a33f5437
Create Date: 2018-06-08 19:32:47.485543

"""

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1471fe0d04ee'
down_revision = '49f3a33f5437'


def upgrade():
    op.add_column('events', sa.Column('average_rating', sa.Float(), nullable=True))
    op.add_column('events_version', sa.Column('average_rating', sa.Float(), autoincrement=False, nullable=True))

def downgrade():
    op.drop_column('events_version', 'average_rating')
    op.drop_column('events', 'average_rating')

Now that the file is generated, we upgrade our database state by utilizing this migration file:

$ python manage.py db upgrade

And here are the successful migration logs that immediately follow the upgrade command:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 49f3a33f5437 -> 1471fe0d04ee, empty message

 

This completes the implementation of the average rating attribute of events. We can use the same technique to implement other attributes in our server, like sum, and minimum, just to name a couple. We saw how SQLAlchemy so elegantly manages to map all the mapping from Python code to database commands. This is just one of the plethora of advantages of using database object-relational-mappers (ORMs), and Open Event Server utilizes them to full extent.

Resources:

Continue ReadingImplementing Event Average Rating with SQLAlchemy

Enforcing Constraints Throughout a Flask Back-End

Recently it was discovered that Open Event Server does not validate attendees’ tickets. Specifically, it was possible to create an arbitrary number of attendees who’d be attending an event on the same ticket! To fix this, a constraint had to be set up across different layers of Open Event Server, which is based on Flask and Postgres. This post will demonstrate how the constraint was added in the server, and these steps should apply in general to any Flask-based server with a relational back-end.

First of all, the immediate idea that comes after investigating such an issue, is to add a UNIQUE constraint to the database. For this specific case, the problem was in ticket_holders table of the Open Event database. There was originally no check imposed on the ticket_id and event_id columns.

As can be seen in the ticket_holders schema (using the \d+ ticket_holders command), there is no mention of uniqueness on either column. The initial guess was that the combination of ticket_id and event_id should be unique throughout the table to avoid multiple holders attending on the same ticket. However,imposing uniqueness on just the ticket_id column would’ve also worked. So, to be on the safer side, I moved ahead by adding uniqueness on both the columns.

To fix this, we need to make changes to the ticket_holder model. So, in the ticket_holder model file, we add a __table_args__ attribute to the TicketHolder class. This attribute represents the various constraints imposed on the ticket_holders table:

class TicketHolder(db.Model):
    __tablename__ = "ticket_holders"
    __table_args__ = (
db.UniqueConstraint('ticket_id', 'event_id', name='ticket_event'),
) # this is the constraint we add

    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String, nullable=False)
    lastname = db.Column(db.String, nullable=False)






The TicketHolder class has attributes named ticket_id and event_id, so to add a unique constraint over them, we pass their names to the UniqueConstraint constructor. Also, any suitable name can be given to the constraint, I chose ‘ticket_event’ to simply emphasize the relationship. Now that we’ve edited the database model file, we have to perform a database migration.

Before we command the migration, we have to remove the entries that potentially violate the constraint we just imposed. As a temporary fix, I connected to the database and deleted all non-unique rows via plain SQL. For a more consistent fix, I will implement this simple deletion code in the database migration file, if need be. So, once the non-unique rows are gone, we perform the database migration as follows:

$ python manage.py db migrate

And then,

$ python manage.py db upgrade

These commands may be different for different projects, but their purpose is the same – to update the database. The upgrade command generates a migration file which looks as follows:

from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils


# revision identifiers, used by Alembic.
revision = '9d21de792967'
down_revision = '194a5a2a44ef'


def upgrade():
op.create_unique_constraint('ticket_event', 'ticket_holders', ['ticket_id', 'event_id'])

def downgrade():
op.drop_constraint('ticket_event', 'ticket_holders', type_='unique')

We can see that the upgrade() function has the command for adding our constraint. Once the database has been upgraded, we can revisit the schema of ticket_holders table (using the \d+ ticket_holders command again). Now we can see that our constraint is added very well in the table schema.

Now, if one tries to create multiple attendees that attend on the same ticket, s/he gets a 500 server error. Here are the related server logs:

2018-06-05 22:04:03.824 IST [46705] ERROR:  duplicate key value violates unique constraint "ticket_event"
2018-06-05 22:04:03.824 IST [46705] DETAIL:  Key (ticket_id, event_id)=(2, 6) already exists.
2018-06-05 22:04:03.824 IST [46705] STATEMENT:  UPDATE ticket_holders SET event_id=6 WHERE ticket_holders.id = 16
127.0.0.1 - - [05/Jun/2018 22:04:03] "POST /v1/attendees HTTP/1.1" 500 -
INFO:werkzeug:127.0.0.1 - - [05/Jun/2018 22:04:03] "POST /v1/attendees HTTP/1.1" 500 -

To get a more graceful error, we also need to make changes in the API schema. This will also allow to validate the data before it gets to the database. So, in the attendees.py file, we need to add a check. This check should extract the ticket and event ids from the data posted and see whether there is already an attendee in the database attending that event on the same ticket. If such an attendee is discovered, the check should raise an error and report it back to the API caller. The suitable place for this check is the before_post() method of the AttendeeListPost class. In any Flask app serving a REST API, such a method (perhaps of a different name) should exist in the API file corresponding to a model. Our check looks like the following within the before_post() method:

from flask_rest_jsonapi import ResourceList
from app.api.helpers.exceptions import ConflictException
from app.models import db
from app.models.ticket_holder import TicketHolder






class AttendeeListPost(ResourceList):
"""
List and create Attendees through direct URL
"""

def before_post(self, args, kwargs, data):
"""
Before post method to check for required relationship and proper permissions
:param args:
:param kwargs:
:param data:
:return:
"""
require_relationship(['ticket', 'event'], data)







if db.session.query(TicketHolder.id).filter_by(
ticket_id=int(data['ticket']), event_id=int(data['event'])
).scalar() is not None:
raise ConflictException(
{'pointer': '/data/attributes/ticket_id'},
"Attendee with this ticket already exists for the same event"
)

Once this check is implemented, we’re all good to go. Now, if an attendee is created that maps to a ticket belonging to an already existing attendee, the following error is sent back to the API caller:

{
"errors": [
{
"status": 409,
"source": {
"pointer": "/data/attributes/ticket_id"
},
"title": "Conflict",
"detail": "Attendee with this ticket already exists for the same event"
}
],
"jsonapi": {
"version": "1.0"
}
}

This completes our work of enforcing this constraint throughout our Flask server. This leads to a more consistent database and potentially avoids confusion at actual events!

Resources:

Continue ReadingEnforcing Constraints Throughout a Flask Back-End