Performing Database Migrations using DbFlow

In Open Event Organizer Android App we decided to add database migrations for every change in database while development. Two of the reasons behind this –

  1. The users have some version of the app installed and then during development the developers had to modify the database, let’s say, add a table or modify existing ones. This makes the existing database incompatible with the new app. On adding database migration the upgradation of database takes place automatically and prevent the situation of reinstalling the app.
  2. Migrations makes it possible to rollback or upgrade to some particular database state. Thus, help in debugging certain changes in isolation.

Let’s discuss the implementation details. Consider the scenario when a user adds a new table named SpeakersCall. For creating migration for this change we need to add migration class inside OrgaDatabase class annotated with @Database. We will break it down to look closely at each step.

  1. Use @Migration annotation in DbFlow library and specify the new database version (by incrementing the existing version) and the database class.
  2. Extend BaseMigration and override migrate method.
  3. The logic used inside the migrate method can be different for different tasks. In the present case we first need to delete any existing table (if exists) with the name SpeakersCall and then recreate that table in database.
  4. Create an array of java classes which are created/modified.
  5. We wrap the SQL query inside a Database Wrapper class which prevents it from running recursively.
  6. FlowManager uses reflection to look up and construct the generated database holder class used in defining the structure for all databases used in this application. So we will getModelAdapter for the class to be recreated and use creation query returned by Model Adapter and execute it.
@Migration(version = 15, database = OrgaDatabase.class)
public static class MigrationTo15 extends BaseMigration {

@Override
public void migrate(@NonNull DatabaseWrapper databaseWrapper) {
Timber.d(“Running migration for DB version 14”);

Class<?>[] recreated = new Class[] {SpeakersCall.class};

for (Class<?> recreate: recreated) {
ModelAdapter modelAdapter = FlowManager.getModelAdapter(recreate);
databaseWrapper.execSQL(DROP_TABLE + modelAdapter.getTableName());
databaseWrapper.execSQL(modelAdapter.getCreationQuery());
}
}
}

Similarly, we can write migration for changing a column of table(s).

Making custom dialog boxes in PSLab app

This blog covers the topic of how to create custom dialog boxes in an Android app by taking an example of how it was implemented in PSLab Android application. The custom dialog box that will be illustrated in this blog will have a layout similar to that from my PR feat: added a guide for Logic Analyzer Instrument using Alert Dialog Box in PSLab Android repository. But all the main functionalities that can be added to a custom dialog will be illustrated in this blog.

How custom dialog was implemented in the PSLab app?

  • The first step is to make a layout for the custom dialog box. The layout of custom dialog should be such that it shouldn’t occupy the whole screen as the idea here is to overlay the dialog over the activity and not replace the whole activity with the dialog layout. The layout can be implemented by using the following attributes :
<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
   android:layout_width="match_parent"
   android:layout_height="match_parent">

   <RelativeLayout
       android:layout_width="match_parent"
       android:layout_height="wrap_content">

       <TextView
           android:id="@+id/custom_dialog_text"
           android:layout_width="match_parent"
           android:layout_height="wrap_content"/>

       <ImageView
           android:id="@+id/custom_dialog_schematic"
           android:layout_width="wrap_content"
           android:layout_height="@dimen/schematic_height" />

       <TextView
           android:id="@+id/description_text"
           android:layout_width="match_parent"
           android:layout_height="wrap_content" />

       <CheckBox
           android:id="@+id/toggle_show_again"
           android:layout_width="wrap_content"
           android:layout_height="wrap_content" />

       <RelativeLayout
           android:layout_width="match_parent"
           android:layout_height="wrap_content"
           android:layout_below="@id/toggle_show_again">

           <Button
               android:id="@+id/dismiss_button"
               android:layout_width="wrap_content"
               android:layout_height="wrap_content" />

       </RelativeLayout>
   </RelativeLayout>
</ScrollView>

The result of the above layout after adding proper margins and padding is as shown in figure 1.

Figure 1. The output of the layout made for custom dialog

  • Now as the layout is ready, we can focus on adding Java code to inflate the layout over a particular activity. Make a function in the activity or the fragment in which the custom dialog is to be inflated. Add the following code (if the layout is same as that in PSLab app else modify the code as per the layout) in the function to display the custom dialog box.
public void howToConnectDialog(String title, String intro, int iconID, String desc) {
   try {
       final AlertDialog.Builder builder = new AlertDialog.Builder(getContext());
       LayoutInflater inflater = getLayoutInflater();
       View dialogView = inflater.inflate(R.layout.custom_dialog_box, null);

 // Find and set all the attributes used in the layout and then create the dialog as shown
       
       builder.setView(dialogView);
       builder.setTitle(title);
       final AlertDialog dialog = builder.create();
       ok_button.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
                dialog.dismiss();
           }
       });
       dialog.show();
   } catch (Exception e) {
       e.printStackTrace();
   }
}

Here, the LayoutInflater inflates the custom dialog layout in the Alertdialog builder. The Alertdialog builder is used to hold and set values for all the views present in the inflated layout. The builder then creates a final custom dialog when builder.create() method is called. Finally, the dialog is shown by calling method dialog.show() and is dismissed by calling method dialog.dismiss().

  • Now the dialog box is ready and it will be inflated when the above method is called. But the “Don’t show again” checkbox currently has no functionality and so the dialog box will pop up although “Don’t show again” checkbox is ticked mark by the user. For adding this functionality, we will need to use Shared Preferences to save the state of the dialog box. For using Shared Preferences, first, add the following lines of code in the above method
final SharedPreferences settings = getActivity().getSharedPreferences(PREFS_NAME, 0);
Boolean skipMessage = settings.getBoolean("skipMessage", false);

The PREFS_NAME is the key to distinctly identify the state of the given dialog box from the stored state of many dialog boxes (if any). A suggested key name is “customDialogPreference”. The skipMessage is a boolean used here to check the state if the dialog box should be inflated or not.

Now format the onClickListener of the OK button with the following code :

Boolean checkBoxResult = false;
if (dontShowAgain.isChecked())
   checkBoxResult = true;
SharedPreferences.Editor editor = settings.edit();
editor.putBoolean("skipMessage", checkBoxResult);
editor.apply();
dialog.dismiss();

The following code checks the state of “Don’t show again” dialog and then inflates the custom dialog if applicable. If the checkbox is ticked mark then on pressing the OK button the dialog won’t be inflated again.

Resources

  1. https://developer.android.com/guide/topics/ui/dialogs – Android Documentation on Dialogs (Great Resource to make a custom dialog)

Option to add description to an image in the Phimpme Android app

In the Phimpme Android application, users can perform various operations on images such as editing an image, sharing an image, moving the image to another folder, printing a pdf version of the image and many more. However, another important functionality that has been implemented is the option to add some description to the image. So in this blog post, I will be discussing how we achieved the functionality to add a description to any image.

Step 1

First, we need to add an option in the overflow menu to add description for the image being viewed. The option to add description to an image has been achieved by adding the following lines of code in the  menu_view_pager.xml file.

<item
  android:id=“@+id/action_description”
  android:title=“Description”
  app:showAsAction=“never” />

Step 2

Now after the user chooses the option to add description to the image, an alertdialog with an edittext would be displayed to the user to enter the description. The dialog box with edittext has been implemented with the following lines of XML code.

<android.support.v7.widget.CardView
  android:layout_width=“match_parent”
  android:layout_height=“wrap_content”
  app:cardCornerRadius=“2dp”
  android:id=“@+id/description_dialog_card”>
  <ScrollView
      android:layout_width=“match_parent”
      android:layout_height=“match_parent”>
      <LinearLayout
          android:layout_width=“match_parent”
          android:layout_height=“wrap_content”
          android:orientation=“vertical”>
          <TextView
              android:id=“@+id/description_dialog_title”
              android:layout_width=“match_parent”
              android:textColor=“@color/md_dark_primary_text”
              android:layout_height=“wrap_content”
              android:background=“@color/md_dark_appbar”
              android:padding=“24dp”
              android:text=“@string/type_description”
              android:textSize=“18sp”
              android:textStyle=“bold” />
          <LinearLayout
              android:id=“@+id/rl_description_dialog”
              android:layout_width=“match_parent”
              android:layout_height=“wrap_content”
              android:orientation=“horizontal”
              android:padding=“15dp”>
              <EditText
                  android:id=“@+id/description_edittxt”
                  android:layout_width=“fill_parent”
                  android:layout_height=“wrap_content”
                  android:padding=“15dp”
                  android:hint=“@string/description_hint”
                  android:textColorHint=“@color/grey”
                  android:layout_margin=“20dp”
                  android:gravity=“top|left”
                  android:inputType=“textCapSentences|textMultiLine”
                  android:maxLength=“2000”
                  android:maxLines=“4”
                  android:selectAllOnFocus=“true”/>
              <ImageButton
                  android:layout_width=“@dimen/mic_image”
                  android:layout_height=“@dimen/mic_image”
                  android:layout_alignRight=“@+id/description_edittxt”
                  app2:srcCompat=“@drawable/ic_mic_black”
                  android:layout_gravity=“center”
                  android:background=“@color/transparent”
                  android:paddingEnd=“10dp”
                  android:paddingTop=“12dp”
                  android:id=“@+id/voice_input”/>
          </LinearLayout>
      </LinearLayout>
  </ScrollView>
</android.support.v7.widget.CardView>

The screenshot of the dialog to enter description has been provided below.

Step 3

Now after retrieving the description added by the user, the description is saved in the realm database using the realm model object ImageDescModel which will contain the path of the image and the description added as its attributes. The path of the image has been used as the primary key for the description table. The realm model class used for the above-mentioned operation is described below.

public class ImageDescModel extends RealmObject {
  @PrimaryKey
  private String path;
  private String desc;

  public ImageDescModel() {
  }

  public ImageDescModel(String path, String title) {
      this.path = path;
      this.desc = title;
  }

  public String getId() {
      return path;
  }

  public void setId(String path) {
      this.path = path;
  }

  public String getTitle() {
      return desc;
  }

  public void setTitle(String description) {
      this.desc = description;
  }
}

This is how we have implemented the functionality to add description to images in the Phimpme Android application. To get the full source code, please refer to the Phimpme Android Github repository listed in the resource section below.

Resources

1.Realm for Android – https://realm.io/blog/realm-for-android/

2.Github-Phimpme Android Repository – https://github.com/fossasia/phimpme-android/

3.Working with realm tutorial – https://www.androidhive.info/2016/05/android-working-with-realm-database-replacing-sqlite-core-data/

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:

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:

Offline support for Open Event Android with ROOM

So until now we were fetching data from the server and directly displaying it to the user in the Open Event Android app. There are several problems in this approach if the user changes the fragment and then returns to the same fragment he will have to fetch the data again, valuable network gets wasted. There is also no offline support. So we decided to introduce a local database in the app. ROOM was without a doubt our first choice

What is ROOM?

According to the official documentation,

The Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.

The library helps you create a cache of your app’s data on a device that’s running your app. This cache, which serves as your app’s single source of truth, allows users to view a consistent copy of key information within your app, regardless of whether users have an internet connection.

Let’s get started

Integration of the ROOM database majorly consists of 3 steps

1 Create an entity

2 Create a DAO

3 Create a Database

That’s it, you are done !

Create the entity

There are just 2 requirements in order to make a model an entity

First use @Entity annotation on the model class to make it an entity. Secondly you need at least one field with @PrimaryKey annotation.

This entity class represents a table in database and all its fields are the columns of the table.

@Type("event")
@JsonNaming(PropertyNamingStrategy.KebabCaseStrategy::class)
@Entity
data class Event(
@Id(LongIdHandler::class)
@PrimaryKey
val id: Long,
val name: String,
val identifier: String,
val startsAt: String)

Create DAO

Data Access Object or DAO for short are used to tell the database how to to put the data.

We can use the following annotations to perform simple SQL queries in ROOM

@Insert, @Update, @Delete for proper actions: inserting, updating and deleting records

@Query for creating queries — we can make select from the database

@Dao
interface EventDao {
@Insert(onConflict = REPLACE)
fun insertEvents(events: List<Event>)

@Insert(onConflict = REPLACE)
fun insertEvent(event: Event)

@Query("DELETE FROM Event")
fun deleteAll()

@Query("SELECT * from Event ORDER BY startsAt DESC")
fun getAllEvents(): Flowable<List<Event>>

@Query("SELECT * from Event WHERE id = :id")
fun getEvent(id: Long): Flowable<Event>
}

Create the Database

We need to create a public abstract class that extends RoomDatabase

After that annotate the class to be a Room database, declare the entities that belong in the database and set the version number. Listing the entities will create tables in the database.

Define the DAOs that work with the database. Make the database a singleton to prevent having multiple instances of the database opened at the same time. A lot has been said let’s have a look at the code now.

@Database(entities = [Event::class, User::class], version = 1)
abstract class OpenEventDatabase : RoomDatabase() {

abstract fun eventDao(): EventDao

abstract fun userDao(): UserDao
}
Room.databaseBuilder(androidApplication(),
OpenEventDatabase::class.java, "open_event_database")
.fallbackToDestructiveMigration()
.build()

The important thing here is that all operations must be done in the background thread. You can do it by using AsyncTask, Handler, RxJava or anything else.

Resources

  1. Room Official Documentation : https://developer.android.com/topic/libraries/architecture/room
  2. Google Code Lab :https://codelabs.developers.google.com/codelabs/android-room-with-a-view/#0

Adding Check-in Attributes to Tickets

Recently, it was decided by the Open Event Orga App team that the event ticket API response from Open Event Server should have two additional attributes for specifying event check-in access. At first sight, it seemed that adding these options will only require changes in the orga app, but it turned out that the entire Ticket API from the server will need this addition.

Implementing these attributes turned out to be quite straightforward. Specifically, the fields to be added were boolean is_checkin_restricted and auto_checkin_enabled. By default, checkin is not automatic and is restricted. Therefore, the default values for these fields were chosen to be True and False respectively. To add them, the ticket model file was changed first – due to the addition of these two columns:

class Ticket(SoftDeletionModel):
    ...
    is_checkin_restricted = db.Column(db.Boolean)  # <--
    auto_checkin_enabled = db.Column(db.Boolean)  # <--
    ...
    def __init__(self,
        name=None,
        event_id=None,
        ...
        is_checkin_restricted=True,
        auto_checkin_enabled=False):

        self.name = name
        ...
        self.is_checkin_restricted = is_checkin_restricted
        self.auto_checkin_enabled = auto_checkin_enabled
        ...

Since the ticket database model was updated, a migration had to be performed. Following shell commands (at the open event server project root) did the migration and database update and a migration file was then generated:

$ python manage.py db migrate
$ python manage.py db upgrade

Here’s the generated migration file:

from alembic import op
import sqlalchemy as sa

revision = '6440077182f0'
down_revision = 'eaa029ebb260'

def upgrade():
    op.add_column('tickets', sa.Column('auto_checkin_enabled', sa.Boolean(), nullable=True))
    op.add_column('tickets', sa.Column('is_checkin_restricted', sa.Boolean(), nullable=True))

def downgrade():
    op.drop_column('tickets', 'is_checkin_restricted')
    op.drop_column('tickets', 'auto_checkin_enabled')

The next code change was required in the ticket API schema. The change was essentially the same as the one added in the model file – just these 2 new fields were added:

class TicketSchemaPublic(SoftDeletionSchema):
    ...
    id = fields.Str(dump_only=True)
    name = fields.Str(required=True)
    ...
    is_checkin_restricted = fields.Boolean(default=True)  # <--
    auto_checkin_enabled = fields.Boolean(default=False)  # <--
    event = Relationship(attribute='event',
    self_view='v1.ticket_event',
    self_view_kwargs={'id': '<id>'},
    related_view='v1.event_detail',
    related_view_kwargs={'ticket_id': '<id>'},
    schema='EventSchemaPublic',
    type_='event')
    ...

Now all that remained were changes in the API documentation, which were made accordingly. This completed the addition of these two checkin attributes in the ticket API, and eventually made way to the orga app. And, these can be requested as usual by the front-end and user app as well.


Resources and Links:

Patching an Attribute Type Across a Flask App

Recently, it was discovered by a contributor that the rating attribute for event feedbacks in Open Event was of type String. The type was incorrect, indeed. After a discussion, developers came concluded that it should be of type Float. In this post, I explain how to perform this simple migration task of changing a data type across a typical Flask app’s stack.

To begin this change, we first, we modify the database model. The model file for feedbacks (feedback.py) looks like the following:

from app.models import db


class Feedback(db.Model):
"""Feedback model class"""
__tablename__ = 'feedback'
id = db.Column(db.Integer, primary_key=True)
rating = db.Column(db.String, nullable=False)  # ←-- should be float
comment = db.Column(db.String, nullable=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.id', ondelete='CASCADE'))
event_id = db.Column(db.Integer,
db.ForeignKey('events.id', ondelete='CASCADE'))

def __init__(self, rating=None, comment=None, event_id=None,                                        user_id=None):
self.rating = rating  # ←-- cast here for safety
self.comment = comment
self.event_id = event_id
self.user_id = user_id





The change here is quite straightforward, and spans just 2 lines:

rating = db.Column(db.Float, nullable=False)

and

self.rating = float(rating)

We now perform the database migration using a couple of manage.py commands on the terminal. This file is different for different projects, but the migration commands essentially look the same. For Open Event Server, the manage.py file is at the root of the project directory (as is conventional). After cd’ing to the root, we execute the following commands:

$ python manage.py db migrate

and then

$ python manage.py db upgrade

These commands update our Open Event database so that the rating is now stored as a Float. However, if we execute these commands one after the other, we note that an exception is thrown:

sqlalchemy.exc.ProgrammingError: column "rating" cannot be cast automatically to type float
HINT:  Specify a USING expression to perform the conversion.
'ALTER TABLE feedback ALTER COLUMN rating TYPE FLOAT USING rating::double precision'

This happens because the migration code is ambiguous about what precision to use after converting to type float. It hints us to utilize the USING clause of PostgreSQL to do that. We accomplish this manually by using the psql client to connect to our database and command it the type change:

$ psql oevent
psql (10.1)
Type "help" for help.

oevent=# ALTER TABLE feedback ALTER COLUMN rating TYPE FLOAT USING rating::double precision

We now exit the psql shell and run the above migration commands again. We see that the migration commands pass successfully this time, and a migration file is generated. For our migration, the file looks like the following:

from alembic import op
import sqlalchemy as sa


# These values would be different for your migrations.
revision = '194a5a2a44ef'

down_revision = '4cac94c86047'


def upgrade():
op.alter_column('feedback', 'rating',
existing_type=sa.VARCHAR(),
type_=sa.Float(),
existing_nullable=False)


def downgrade():
op.alter_column('feedback', 'rating',
existing_type=sa.Float(),
type_=sa.VARCHAR(),
existing_nullable=False)

This is an auto-generated file (built by the database migration tool Alembic) and we need to specify the extra commands we used while migrating our database. Since we did use an extra command to specify the precision, we need to add it here. PostgreSQL USING clause can be added to alembic migration files via the postgresql_using keyword argument. Thus, the edited version of the upgrade function looks like the following:

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

This completes our work on database migration. Migration files are useful for a variety of purposes – they allow us to easily get to a previous database state, or a new database state as suggested by a project collaborator. Just like git, they allow for easy version control and collaboration.

We didn’t finish this work after database migration. We also decided to impose limits on the rating value. We concluded that 0-5 would be a good range for rating. Furthermore, we also decided to round off the rating value to the “nearest 0.5”, so if the input rating is 2.3, it becomes 2.5. Also, if it is 4.1, it becomes 4.0. This was decided because such values are conventional for ratings across numerous web and mobile apps. So this will hopefully enable easier adoption for new users.

For the validation part, marshmallow came to rescue. It is a simple object serialization and deserialization tool for Python. So it basically allows to convert complex Python objects to JSON data for communicating over HTTP and vice-versa, among other things. It also facilitates pre-processing input data and therefore, allows clean validation of payloads. In our case, marshmallow was specifically used to validate the range of the rating attribute of feedbacks. The original feedbacks schema file looked like the following:

from marshmallow_jsonapi import fields
from marshmallow_jsonapi.flask import Schema, Relationship

from app.api.helpers.utilities import dasherize


class FeedbackSchema(Schema):
"""
Api schema for Feedback Model
"""
class Meta:
"""
Meta class for Feedback Api Schema
"""
type_ = 'feedback'
self_view = 'v1.feedback_detail'
self_view_kwargs = {'id': '<id>'}
inflect = dasherize

id = fields.Str(dump_only=True)
rating = fields.Str(required=True)  # ← need to validate this
comment = fields.Str(required=False)
event = Relationship(attribute='event',
self_view='v1.feedback_event',
self_view_kwargs={'id': '<id>'},
related_view='v1.event_detail',
related_view_kwargs={'feedback_id': '<id>'},
schema='EventSchemaPublic',
type_='event')




To validate the rating attribute, we use marshmallow’s Range class:

from marshmallow.validate import Range

Now we change the line

rating = fields.Str(required=True)

to

rating = fields.Float(required=True, validate=Range(min=0, max=5))

So with marshmallow, just about 2 lines of work implements rating validation for us!

After the validation part, what’s remaining is the rounding-off business logic. This is simple mathematics, and for getting to the “nearest 0.5” number, the formula goes as follows:

rating * 2 --> round off --> divide the result by 2

We will use Python’s built-in function (BIF) to accomplish this. To implement the business logic, we go back to the feedback model class and modify its constructor. Before this type change, the constructor looked like the following:

def __init__(self, rating=None, comment=None, event_id=None, user_id=None):
self.rating = rating
self.comment = comment
self.event_id = event_id
self.user_id = user_id

We change this by first converting the input rating to float, rounding it off and then finally assigning the result to feedback’s rating attribute. The new constructor is shown below:

def __init__(self, rating=None, comment=None, event_id=None, user_id=None):
rating = float(rating)
self.rating = round(rating*2, 0) / 2  # Rounds to nearest 0.5

self.comment = comment
self.event_id = event_id
self.user_id = user_id

This completes the rounding-off part and ultimately concludes rating’s type change from String to Float. We saw how a simple high-level type change requires editing code across multiple files and the use of different tools in between. In doing so, we thus also learned the utility of alembic and marshmallow in database migration and data validation, respectively.


Resources

Open Event Server – Pages API

This article illustrates how the Pages API has been designed and implemented on the server side, i.e., FOSSASIA‘s Open Event Server. Pages endpoint is used to create static pages such as “About Page” or any other page that doesn’t need to be updated frequently and only a specific content is to be shown.

Parameters

  1. name – This stores the name of the page.
      1. Type – String
      2. Required – Yes
  2. title – This stores the title of the page.
      1. Type – String
      2. Required – No
  3. url – This stores the url of the page.
      1. Type – String
      2. Required – Yes
  4. description – This stores the description of the page.
      1. Type – String
      2. Required – Yes
  5. language – This stores the language of the page.
      1. Type – String
      2. Required – No
  6. index – This stores the position of the page.
      1. Type – Integer
      2. Required – No
      3. Default – 0
  7. place – Location where the page will be placed.
      1. Type – String
      2. Required – No
      3. Accepted Values – ‘footer’ and ‘event’

These are the allowed parameters for the endpoint.

Model

Lets see how we model this API. The ORM looks like this :

__tablename__ = 'pages'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String, nullable=False)
title = db.Column(db.String)
url = db.Column(db.String, nullable=False)
description = db.Column(db.String)
place = db.Column(db.String)
language = db.Column(db.String)
index = db.Column(db.Integer, default=0)

As you can see, we created a table called “pages”. This table has 8 columns, 7 of which are the parameters that I have mentioned above. The column “id” is an Integer column and is the primary key column. This will help to differentiate between the various entries in the table.

The visualisation for this table looks as follows :

API

We support the following operations:

  1. GET all the pages in the database
  2. POST create a new page
  3. GET details of a single page as per id
  4. PATCH a single page by id
  5. DELETE a single page by id

To implement this we first add the routes in our python file as follows :

api.route(PageList, 'page_list', '/pages')
api.route(PageDetail, 'page_detail', '/pages/<int:id>')

Then we define these classes to handle the requests. The first route looks as follows:

class PageList(ResourceList):
   """
   List and create page
   """
   decorators = (api.has_permission('is_admin', methods="POST"),)
   schema = PageSchema
   data_layer = {'session': db.session,
                 'model': Page}

As can be seen above, this request requires the user to be an admin. It uses the Page model described above and handles a POST request.

The second route is:

class PageDetail(ResourceDetail):
   """
   Page detail by id
   """
   schema = PageSchema
   decorators = (api.has_permission('is_admin', methods="PATCH,DELETE"),)
   data_layer = {'session': db.session,
                 'model': Page}

This route also requires the user to be an admin. It uses the Page model and handles PATCH, DELETE requests.

To summarise our APIs are:

GET

/v1/pages{?sort,filter}

POST

/v1/pages{?sort,filter}

GET

/v1/pages/{page_id}

PATCH

/v1/pages/{page_id}

DELETE

/v1/pages/{page_id}

References

Implementing Database Migrations to Badgeyay

Badgeyay project is divided into two parts i.e front-end of Ember JS and back-end with REST-API programmed in Python.

We have integrated PostgreSQL as the object-relational database in Badgeyay and we are using SQLAlchemy SQL Toolkit and Object Relational Mapper tools for working with databases and Python. As we have Flask microframework for Python, so we are having Flask-SQLAlchemy as an extension for Flask that adds support for SQLAlchemy to work with the ORM.

One of the challenging jobs is to manage changes we make to the models and propagate these changes in the database. For this purpose, I have added Added Migrations to Flask SQLAlchemy for handling database changes using the Flask-Migrate extension.

In this blog, I will be discussing how I added Migrations to Flask SQLAlchemy for handling Database changes using the Flask-Migrate extension in my Pull Request.

First, Let’s understand Database Models, Migrations, and Flask Migrate extension. Then we will move onto adding migrations using Flask-Migrate. Let’s get started and understand it step by step.

What are Database Models?

A Database model defines the logical design and structure of a database which includes the relationships and constraints that determine how data can be stored and accessed. Presently, we are having a User and file Models in the project.

What are Migrations?

Database migration is a process, which usually includes assessment, database schema conversion. Migrations enable us to manipulate modifications we make to the models and propagate these adjustments in the database. For example, if later on, we make a change to a field in one of the models, all we will want to do is create and do a migration, and the database will replicate the change.

What is Flask Migrate?

Flask-Migrate is an extension that handles SQLAlchemy database migrations for Flask applications using Alembic. The database operations are made available through the Flask command-line interface or through the Flask-Script extension.

Now let’s add support for migration in Badgeyay.

Step 1 :

pip install flask-migrate

 

Step 2 :

We will need to edit run.py and it will look like this :

import os
from flask import Flask
from flask_migrate import Migrate  // Imported Flask Migrate

from api.db import db
from api.config import config

......

db.init_app(app)
migrate = Migrate(app, db) // It will allow us to run migrations
......

@app.before_first_request
def create_tables():
    db.create_all()

if __name__ == '__main__':
    app.run()

 

Step 3 :

Creation of Migration Directory.

 export FLASK_APP=run.py
 flask db init

 

This will create Migration Directory in the backend API folder.

└── migrations
    ├── README
    ├── alembic.ini
    ├── env.py
    ├── script.py.mako
    └── versions

 

Step 4 :

We will do our first Migration by the following command.

flask db migrate

 

Step 5 :

We will apply the migrations by the following command.

flask db upgrade

 

Now we are all done with setting up Migrations to Flask SQLAlchemy for handling database changes in the badgeyay repository. We can verify the Migration by checking the database tables in the Database.

This is how I have added Migrations to Flask SQLAlchemy for handling Database changes using the Flask-Migrate extension in my Pull Request.

Resources:

  • PostgreSQL Docs    – Link
  • Flask Migrate Docs  – Link
  • SQLAlchemy Docs  – Link
  • Flask SQLAlchemy Docs – Link