Python / JS Full Stack Developer for Open Event Project

STATUS: ACTIVE

As a Full Stack Engineer at FOSSASIA, you’ll work together with a global community to develop the Open Event project and to maintain our web service at eventyay.com. Our services are entirely Free/Open Source. We use Flask as a backend and Ember.js (transitioning to React) as a frontend technology. The system is deployed on a Hetzner cloud server. You’ll own what you build, making technical decisions and applying our best practices to every piece of code. The team uses scrum emails for the daily standup and Gitter for continuous chat communication.

Candidates who have started contributing code to one of our projects and to the Open Event backend and frontend specifically will have an advantage.

Number of Positions: Several

[Apply Here]

About the team

  • We are a team working with a community of FOSS developers
  • We work remotely in different timezones
  • Our system is built using Ember (frontend) and we need someone who can help us to transition to ReactJS
  • We use Flask/Python for the backend
  • We have an informal and collaborative environment
  • We embrace Continuous Integration

Responsibilities

  • Assume leadership and responsibility for the technical tasks and deliverables
  • Become project owner of Open Event development and provide daily code commits
  • Deploy project on the server using Docker
  • Write unit tests for all portions of our applications
  • Automate the software deployment process
  • Support community developers and review PRs
  • Work according to FOSSASIA Best Practices
  • Provide daily scrums, code contributions and communicate on chat
  • Participate and lead weekly developer meeting
  • Ensure related apps (Android/iOS) work with APIs and review PRs
  • Take on responsibilities in other FOSSASIA projects as needed

Requirements

  • Experience working in a remote setting, or with a remote team and across multiple time zones
  • Professional experience using HTML, CSS, and Javascript
  • Experience with Flask/Python
  • Experience with Ember.js and ReactJS (or the desire to learn it)
  • Experience with Continuous Integration and Docker
  • Understanding of best practices for web development and software design
  • Not afraid of going deep on backend code to understand features
  • Enjoy writing tested and modular code
  • Working time overlaps with European and Singapore time
  • You have good spoken/written English
  • Self-motivated and independent

Code

Please check out the project on GitHub before applying.

Open Event Server: https://github.com/fossasia/open-event-server

Open Event Frontend: https://github.com/fossasia/open-event-frontend

Salary

Depending on position or freelance agreement.

Benefits

  • Visit Singapore and participate in annual FOSSASIA Summit
  • Participate in local Open Source meetups and conferences
  • Work with a community of enthusiastic software developers

Location

Worldwide

Contact

Please apply through our online form.

[Submit Application Here]

Links

Continue ReadingPython / JS Full Stack Developer for Open Event Project

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%

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

Open Event Server – Export Speakers as PDF File

FOSSASIA‘s Open Event Server is the REST API backend for the event management platform, Open Event. Here, the event organizers can create their events, add tickets for it and manage all aspects from the schedule to the speakers. Also, once he/she makes his event public, others can view it and buy tickets if interested.

The organizer can see all the speakers in a very detailed view in the event management dashboard. He can see the statuses of all the speakers. The possible statuses are pending, accepted, and rejected. He/she can take actions such as editing the speakers.

If the organizer wants to download the list of all the speakers as a PDF file, he or she can do it very easily by simply clicking on the Export As PDF button in the top right-hand corner.

Let us see how this is done on the server.

Server side – generating the Speakers PDF file

Here we will be using the pisa package which is used to convert from HTML to PDF. It is a html2pdf converter which uses ReportLab Toolkit, the HTML5lib and pyPdf. It supports HTML5 and CSS 2.1 (and some of CSS 3). It is completely written in pure Python so it is platform independent.

from xhtml2pdf import pisa<

We have a utility method create_save_pdf which creates and saves PDFs from HTML. It takes the following arguments:

  • pdf_data – This contains the HTML template which has to be converted to PDF.
  • key – This contains the file name
  • dir_path – This contains the directory

It returns the newly formed PDF file. The code is as follows:

def create_save_pdf(pdf_data, key, dir_path='/static/uploads/pdf/temp/'):
   filedir = current_app.config.get('BASE_DIR') + dir_path

   if not os.path.isdir(filedir):
       os.makedirs(filedir)

   filename = get_file_name() + '.pdf'
   dest = filedir + filename

   file = open(dest, "wb")
   pisa.CreatePDF(io.BytesIO(pdf_data.encode('utf-8')), file)
   file.close()

   uploaded_file = UploadedFile(dest, filename)
   upload_path = key.format(identifier=get_file_name())
   new_file = upload(uploaded_file, upload_path)
   # Removing old file created
   os.remove(dest)

   return new_file

The HTML file is formed using the render_template method of flask. This method takes the HTML template and its required variables as the arguments. In our case, we pass in ‘pdf/speakers_pdf.html’(template) and speakers. Here, speakers is the list of speakers to be included in the PDF file. In the template, we loop through each item of speakers. We print his name, email, list of its sessions, mobile, a short biography, organization, and position. All these fields form a row in the table. Hence, each speaker is a row in our PDF file.

The various columns are as follows:

<thead>
<tr>
   <th>
       {{ ("Name") }}
   </th>
   <th>
       {{ ("Email") }}
   </th>
   <th>
       {{ ("Sessions") }}
   </th>
   <th>
       {{ ("Mobile") }}
   </th>
   <th>
       {{ ("Short Biography") }}
   </th>
   <th>
       {{ ("Organisation") }}
   </th>
   <th>
       {{ ("Position") }}
   </th>
</tr>
</thead>

A snippet of the code which handles iterating over the speakers’ list and forming a row is as follows:

{% for speaker in speakers %}
   <tr class="padded" style="text-align:center; margin-top: 5px">
       <td>
           {% if speaker.name %}
               {{ speaker.name }}
           {% else %}
               {{ "-" }}
           {% endif %}
       </td>
       <td>
           {% if speaker.email %}
               {{ speaker.email }}
           {% else %}
               {{ "-" }}
           {% endif %}
       </td>
       <td>
           {% if speaker.sessions %}
               {% for session in speaker.sessions %}
                   {{ session.name }}<br>
               {% endfor %}
           {% else %}
               {{ "-" }}
           {% endif %}
       </td>
      …. So on
   </tr>
{% endfor %}

The full template can be found here.

Obtaining the Speakers PDF file:

Firstly, we have an API endpoint which starts the task on the server.

GET - /v1/events/{event_identifier}/export/speakers/pdf

Here, event_identifier is the unique ID of the event. This endpoint starts a celery task on the server to export the speakers of the event as a PDF file. It returns the URL of the task to get the status of the export task. A sample response is as follows:

{
  "task_url": "/v1/tasks/b7ca7088-876e-4c29-a0ee-b8029a64849a"
}

The user can go to the above-returned URL and check the status of his/her Celery task. If the task completed successfully he/she will get the download URL. The endpoint to check the status of the task is:

and the corresponding response from the server –

{
  "result": {
    "download_url": "/v1/events/1/exports/http://localhost/static/media/exports/1/zip/OGpMM0w2RH/event1.zip"
  },
  "state": "SUCCESS"
}

The file can be downloaded from the above-mentioned URL.

Resources

Continue ReadingOpen Event Server – Export Speakers as PDF File

Open Event Server – Export Sessions as PDF File

FOSSASIA‘s Open Event Server is the REST API backend for the event management platform, Open Event. Here, the event organizers can create their events, add tickets for it and manage all aspects from the schedule to the speakers. Also, once he/she makes his event public, others can view it and buy tickets if interested.

The organizer can see all the sessions in a very detailed view in the event management dashboard. He can see the statuses of all the sessions. The possible statuses are pending, accepted, confirmed and rejected. He/she can take actions such as accepting/rejecting the sessions.

If the organizer wants to download the list of all the sessions as a PDF file, he or she can do it very easily by simply clicking on the Export As PDF button in the top right-hand corner.

Let us see how this is done on the server.

Server side – generating the Sessions PDF file

Here we will be using the pisa package which is used to convert from HTML to PDF. It is a html2pdf converter which uses ReportLab Toolkit, the HTML5lib and pyPdf. It supports HTML5 and CSS 2.1 (and some of CSS 3). It is completely written in pure Python so it is platform independent.

from xhtml2pdf import pisa

We have a utility method create_save_pdf which creates and saves PDFs from HTML. It takes the following arguments:

  • pdf_data – This contains the HTML template which has to be converted to PDF.
  • key – This contains the file name
  • dir_path – This contains the directory

It returns the newly formed PDF file. The code is as follows:

def create_save_pdf(pdf_data, key, dir_path='/static/uploads/pdf/temp/'):
   filedir = current_app.config.get('BASE_DIR') + dir_path

   if not os.path.isdir(filedir):
       os.makedirs(filedir)

   filename = get_file_name() + '.pdf'
   dest = filedir + filename

   file = open(dest, "wb")
   pisa.CreatePDF(io.BytesIO(pdf_data.encode('utf-8')), file)
   file.close()

   uploaded_file = UploadedFile(dest, filename)
   upload_path = key.format(identifier=get_file_name())
   new_file = upload(uploaded_file, upload_path)
   # Removing old file created
   os.remove(dest)

   return new_file

The HTML file is formed using the render_template method of flask. This method takes the HTML template and its required variables as the arguments. In our case, we pass in ‘pdf/sessions_pdf.html’(template) and sessions. Here, sessions is the list of sessions to be included in the PDF file. In the template, we loop through each item of sessions and check if it is deleted or not. If it not deleted then we print its title, state, list of its speakers, track, created at and has an email been sent or not. All these fields form a row in the table. Hence, each session is a row in our PDF file.

The various columns are as follows:

<thead>
<tr>
   <th>
       {{ ("Title") }}
   </th>
   <th>
       {{ ("State") }}
   </th>
   <th>
       {{ ("Speakers") }}
   </th>
   <th>
       {{ ("Track") }}
   </th>
   <th>
       {{ ("Created At") }}
   </th>
   <th>
       {{ ("Email Sent") }}
   </th>
</tr>
</thead>

A snippet of the code which handles iterating over the sessions list and forming a row is as follows:

{% for session in sessions %}
   {% if not session.deleted_at %}
       <tr class="padded" style="text-align:center; margin-top: 5px">
           <td>
               {% if session.title %}
                   {{ session.title }}
               {% else %}
                   {{ "-" }}
               {% endif %}
           </td>
           <td>
               {% if session.state %}
                   {{ session.state }}
               {% else %}
                   {{ "-" }}
               {% endif %}
           </td>
           <td>
               {% if session.speakers %}
                   {% for speaker in session.speakers %}
                       {{ speaker.name }}<br>
                   {% endfor %}
               {% else %}
                   {{ "-" }}
               {% endif %}
           </td>
          ….. And so on
       </tr>
   {% endif %}
{% endfor %}

The full template can be found here.

Obtaining the Sessions PDF file:

Firstly, we have an API endpoint which starts the task on the server.

GET - /v1/events/{event_identifier}/export/sessions/pdf

Here, event_identifier is the unique ID of the event. This endpoint starts a celery task on the server to export the sessions of the event as a PDF file. It returns the URL of the task to get the status of the export task. A sample response is as follows:

{
  "task_url": "/v1/tasks/b7ca7088-876e-4c29-a0ee-b8029a64849a"
}

The user can go to the above-returned URL and check the status of his/her Celery task. If the task completed successfully he/she will get the download URL. The endpoint to check the status of the task is:

and the corresponding response from the server –

{
  "result": {
    "download_url": "/v1/events/1/exports/http://localhost/static/media/exports/1/zip/OGpMM0w2RH/event1.zip"
  },
  "state": "SUCCESS"
}

The file can be downloaded from the above-mentioned URL.

Resources

Continue ReadingOpen Event Server – Export Sessions as PDF File

Upgrading Open Event to Use Sendgrid API v3

Sendgrid recently upgraded their web API to send emails, and support for previous versions was deprecated. As a result, Open Event Server’s mail sending tasks were rendered unsuccessful, because the requests they were sending to Sendgrid were not being processed. On top of that, it was also found out later that the existing Sendgrid API key on the development server was expired. This had to be fixed at the earliest because emails are a core part of Open Event functionality.

The existing way for emails to be sent via Sendgrid used to hit the endpoint “https://api.sendgrid.com/api/mail.send.json” to send emails. Also, the payload structure was as follows:

payload = {
    'to': to,
    'from': email_from,
    'subject': subject,
    'html': html
}

Also, a header  “Authorization”: “Bearer ” accompanied the above payload. However, Sendgrid changed the payload structure to be of the following format:

{

“personalizations”: [

{“to”: [

{“email”: “example@example.com“}

]

}

],

“from”: {

“email”: “example@example.com

},

“subject”: “Hello, World!”,

“content”: [

{

“type”: “text/plain”,

“value”: “Heya!”

}

]

}

Furthermore, the endpoint was changed to be “https://api.sendgrid.com/v3/mail/send”. To incorporate all these changes with the minimum number of modified lines in the codebase, it was required for that the structure change itself happens at a fairly low level. This was because there are lots of features in the server that perform a wide variety of email actions. Thus, it was clear that changing all of them will not be the most efficient thing to do. So the perfect place to implement the API changes was the function send_email() in mail.py, because all other higher-level email functions are built on top of this function. But this was not the only change, because this function itself used another function, called send_email_task() in tasks.py, specifically for sending email via Sendgrid. So, in conclusion, the header modifications were made in send_email() and payload structure as well as endpoint modifications were made within send_email_task(). This brought the server codebase back on track to send emails successfully. Finally, the key for development server was also renewed and added to its settings in the Heroku Postgres database.

Screenshots:

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

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

Resources

Continue ReadingUpgrading Open Event to Use Sendgrid API v3

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

Migrating Event Ratings of Open Event with Stored Procedures

Many developers know about procedural languages and have used them in some form or another, but this is really an unpopular tool, despite its power. There are many advantages (and few disadvantages) of these languages, which we will learn about soon. Having a right amount of database-stored procedure code with the help of these languages can really enhance the speed and responsiveness of an application. This article will teach us how procedural languages can be utilized in database management and how they were used recently for a bug fix in Open Event Server.

PostgreSQL, like any other powerful, relational database management system (RDBMS), provides the functionality to create and use stored procedures. Essentially, a stored procedure is database logic code which is saved on the database server. This code can be executed directly in the database, and can (and is!) often used to shift business logic from the application layer of a software to the database layer. This simple shift often has many advantages – including faster execution (as code executes at a lower stack level) and better security. When firing database queries from the application layer (i.e., the code that programmers write for storing programmable objects, performing business logic and so on), it often happens that parameters from the programming language itself are passed in to SQL, which then generates a complete SQL query. For example, here’s how a novice query might look like:

import psycopg2
conn = psycopg2.connect(dbname="oevent", user="john", password="start")
cur = conn.cursor()
name = "Sam"
cur.execute("SELECT * FROM users WHERE name='%s'" % name)  # DANGEROUS!

This is an extremely “exposed” code that can be exploited for malicious access, with a technique called SQL injection. This technique essentially “injects” malicious code via these passed parameters, like the variable name mentioned in the above code. With having stored procedures for business logic, there is no room for SQL injection. They solve this problem by writing the query beforehand, and having the parameterized data as a different entity. The pre-processed query within the corresponding stored procedure now looks like

SELECT * FROM users WHERE name=?

 

The database driver sends the name of this stored procedure (or, in standard parameterised queries, just the query text itself) and a list of parameters, as distinct separate entities in the protocol. More details on how stored procedures enhance security can be found here.

After learning so much about the advantages of stored procedures (which are enabled by procedural languages), let’s write one! Postgres supports multiple languages for writing stored procedures; here we will use PL/pgSQL, which is the most popular choice for Postgres. This procedural language, inspired (heavily) by Oracle’s PL/SQL language, looks very similar to SQL. To use this procedural language, we have to first install it. In Postgres, procedural languages are installed per-database, not server-wide. We can use the popular Postgres client psql for this purpose, or simply the createlang command on the command line:

$ createlang plpgsql yourdb

 

Now let’s create a simple procedure that prints the corresponding grades for the event ratings. In the psql shell, type the following:

CREATE OR REPLACE FUNCTION grade(rating NUMERIC) RETURNS TEXT AS
$$
BEGIN
IF rating < 1 THEN
RETURN 'D';
ELSIF rating < 2 THEN
RETURN 'C';
ELSIF rating < 3 THEN
RETURN 'B';
ELSIF rating < 4 THEN
RETURN 'A';
ELSE
RETURN 'A+';
END IF;
END;
$$ STRICT LANGUAGE plpgsql IMMUTABLE;

 

The first line defines the function signature – its name, parameters’ type and return type. The line specifies which procedural language to use. The IMMUTABLE keyword specifies that the stored procedure – this function, cannot modify the database by itself. The STRICT part says that the function should always return null when any of the arguments are null. So, the function is not executed when there are null arguments, rather a null result is assumed automatically. Finally, everything within the $$ quotes is the logic of our function, in PL/pgSQL syntax. As we can see, the syntax is almost identical to that of plain SQL.

Let us now create some dummy data to test the above stored procedure. In the same psql shell, type the following:

CREATE TABLE ratings(id INTEGER, rating NUMERIC);
INSERT INTO ratings VALUES (1, 2.3), (2, 0.5), (3, 1.7), (4, 4.5), (5, 3.3);

 

The ratings table now looks as follows:

yourdb=# SELECT * FROM ratings;
id | rating
----+--------
1 |    2.3
2 |    0.5
3 |    1.7
4 |    4.5
5 |    3.3
(5 rows)

To test our stored procedure, we fire the following query:

SELECT id, grade(rating) FROM ratings;

The result is:

id | grade
----+-------
1 | B
2 | D
3 | C
4 | A+
5 | A
(5 rows)

Thus, our stored procedure works perfectly! This shows how you can write code for your application in the database itself, the code which would otherwise be present in the application layer. For writing more complex stored procedures, you can always consult the excellent PostgreSQL documentation on this topic!

PL/pgSQL turned out to be especially useful for a recent bug patch in Open Event Server. The rating attribute of event feedbacks was initially (and incorrectly) of String type. When converting it to Float type, I had migrated the schema, but the migration of existing data was nontrivial. Since the earlier ratings were of type String, we weren’t really sure whether the values in the rating column will all contain numbers. Furthermore, we also wanted to round those numbers to the “nearest 0.5”, so that ratings like 4.4 and 2.2 get converted to more conventional values like 4.5 and 2.0 respectively. To accomplish this, I had to add a stored procedure in the related migration file. Here’s how it looked:

class ReplaceableObject(object):
    def __init__(self, name, sqltext):
        self.name = name
        self.sqltext = sqltext

update_rating_func = ReplaceableObject(
    "update_rating(rating text)",
    """
    RETURNS text AS $$
    DECLARE
        r NUMERIC;
    BEGIN
        r = cast(rating as NUMERIC);
        IF r < 0 OR r > 5 THEN
            RETURN '0';
        END IF;
        r = round(r*2) / 2;
        RETURN cast(r as VARCHAR);
    EXCEPTION WHEN invalid_text_representation THEN
        RETURN '0';
    END;
    $$
    STRICT
    LANGUAGE plpgsql IMMUTABLE;
    """)

We created a simple class ReplaceableObject so that the name of the function is, sort of, a separate attribute from its definition, as required by the migration library alembic. Once the stored procedure was defined as above, the upgrade() function of the migration file was modified accordingly:

def upgrade():
    op.create_or_replace_sp(update_rating_func)
    op.execute("UPDATE feedback SET rating=update_rating(rating)")
    op.execute("DROP FUNCTION update_rating(text)")

    op.alter_column('feedback', 'rating',
        existing_type=sa.VARCHAR(),
        type_=sa.Float(),
        existing_nullable=False,
        postgresql_using='rating::double precision')

Notice how the UPDATE query calls our stored procedure update_rating() to make existing ratings of the form we desire. Once that is done, the procedure is no longer needed, so it is DROP-ed and then, finally, the type is changed to Float. So indeed, procedural languages are used for real!

Also, what’s more exciting is that one can write Postgres stored procedures in Python itself! Python is among the four procedural languages supported by PostgreSQL, the other two being Perl and Tcl. Here’s a simple Python example of the grading code we wrote earlier:

CREATE OR REPLACE FUNCTION grade(rating NUMERIC) RETURNS TEXT AS
$$
    if rating < 1:
        return 'D'
    elif rating < 2:
        return 'C'
    elif rating < 3:
        return 'B'
    elif rating < 4:
        return 'A'
    else
        return 'A+'
$$ STRICT LANGUAGE plpython3u IMMUTABLE;

This should work identical to the PL/pgSQL equivalent code above. But don’t forget to change the command for installing the procedural language, for Python it looks like:

$ createlang plpython3u yourdb

Needless to say, the 3 here stands for Python3.

Even with all this flexibility and power, one must note that procedural languages are not designed to replace programming languages. Procedural code can easily become difficult to maintain, and one often needs superuser privileges to create stored procedures. Moreover, portability is a mess with them. That is especially true when moving from one database system (like Postgres) to another (like Microsoft SQL Server). Therefore, for optimum application performance and code maintainability, ensure that there is a healthy balance between your business logic residing in stored procedures and the application layer.

This post covered a lot of information about code residing in the database and its use cases. If you want to know more about best practices involving separation of business logic, please check out this thread on StackExchange’s software engineering website.

Resources

Continue ReadingMigrating Event Ratings of Open Event with Stored Procedures