Doing a table join in Android without using rawQuery

The Open Event Android App, downloads data from the API (about events, sessions speakers etc), and saves them locally in an SQLite database, so that the app can work even without internet connection.

Since there are multiple entities like Sessions, Speakers, Events etc, and each Session has ids of speakers, and id of it’s venue etc, we often need to use JOIN queries to join data from two tables.

 

Android has some really nice SQLite helper classes and methods. And the ones I like the most are the SQLiteDatabase.query, SQLiteDatabase.update, SQLiteDatabase.insert ones, because they take away quite a bit of pain for typing out SQL commands by hand.

But unfortunately, if you have to use a JOIN, then usually you have to go and use the SQLiteDatabase.rawQuery method and end up having to type your commands by hand.

But but but, if the two tables you are joining do not have any common column names (actually it is good design to have them so – by having all column names prefixed by tablename_ maybe), then you can hack the usual SQLiteDatabase.query() method to get a JOINed query.

Now ideally, to get the Session where speaker_id was 1, a nice looking SQL query should be like this –

SELECT * FROM speaker INNER JOIN session
ON speaker_id = session_speaker_id
WHERE speaker_id = 1

Which, in android, can be done like this –

String rawQuery = "SELECT * FROM " + SpeakerTable.TABLE_NAME + " INNER JOIN " + SessionTable.TABLE_NAME
        + " ON " + SessionTable.EXP_ID + " = " + SpeakerTable.ID
        + " WHERE " + SessionTable.ID + " = " +  id;
Cursor c = db.rawQuery(
        rawQuery,
        null
);

But of course, because of SQLite’s backward compatible support of the primitive way of querying, we turn that command into

SELECT *
FROM session, speaker
WHERE speaker_id = session_speaker_id AND speaker_id = 1

Now this we can write by hacking the terminology used by the #query() method –

Cursor c = db.query(
        SessionTable.TABLE_NAME + " , " + SpeakerTable.TABLE_NAME,
        Utils.concat(SessionTable.PROJECTION, SpeakerTable.PROJECTION),
        SessionTable.EXP_ID + " = " + SpeakerTable.ID + " AND " + SpeakerTable.ID + " = " +  id,
        null,
        null,
        null,
        null
);

To explain a bit, the first argument String tableName can take table1, table2 as well safely, The second argument takes a String array of column names, I concatenated the two projections of the two classes. and finally, put by WHERE clause into the String selection argument.

You can see the code for all database operations in the android app here  https://github.com/fossasia/open-event-android/blob/master/android/app/src/main/java/org/fossasia/openevent/dbutils/DatabaseOperations.java

Implementing Admin Trash in Open Event

So last week I had the task of implementing a trash system for the Admin. It was observed that sometimes a user may delete an item and then realize that the item needs to be restores. Thus a trash system works well in this case. Presently the items that are being moved to the trash are:

  • Deleted Users
  • Deleted Events
  • Deleted Sessions

So it works like this. I added a column in_trash to the tables User, Event and Sessions to mark whether the item is in the trash or not

in_trash = db.Column(db.Boolean, default=False)

So depending on whether the value is True or False the item will be in the trash of the admin. Thus for a normal user on deleting an event, user or session a message would flash that the item is deleted and the item would not be shown in the table list of the user. However it would not be deleted from the database.

trash4.png

trash5.png

Thus for the user the item is deleted. The item’s in_trash property is set to True and it gets moved to the trash. The items are displayed in the “Deleted Items” section of the Admin panel

trash1trash2trash3

The items deleted are displayed in the trash and as soon as they deleted in the trash they are deleted from the database permanently. A message will flash for the Admin when it is deleted

trash11

trash10.png

Thus the trash is implemented. 🙂

Two more things are left:

  • To restore items from trash
  • To automatically delete the items in trash after an inactivity of 30 days

This will soon be implemented 🙂

Open-Event Permissions System and integrating it with decorators

All the large scale applications require a permissions system. Thus we also implemented a permissions system in our open-event organization server. It consists of certain pre-decided roles:

  1. Super-Admin
  2. Admin
  3. Organizer
  4. Co organizer
  5. Track organizer
  6. Anonymous user

Now we had to decide the permissions which each role would have. Hence we created a documentation regarding what URLs can be accessed by each role. We developed a list of services which the roles could use their permissions to access:

  1. Tracks
  2. Microlocations
  3. Speakers
  4. Sessions
  5. Sponsors

Thus the final step was to implement the permissions system to the appropriate views or URLs. Here comes the power of Flask decorators . I created a individual decorators @is_organizer, @is_admin, @is_super_admin etc… to check the respective roles. I created one main decorator @can_access to see whether the role can access the particular URL or view function

decorator

So in the above decorator I have simply take in the url and check whether it has ‘create’, ‘edit’ or ‘delete’ words in it. Depending on that the control goes in the particular IF statement. Now once it is decided what operation is being performed it checks what service is being accessed by the user. For example: if the operation is edit then it will check whether the service being edited is an event, session, sponsor etc…

Similar checks are performed by each operation. A check is performed of the request.url to see whether the string for that service is present in it. After it knows what service is being accessed its just a matter of using the CRUD functions of user table to check if the role accessing the resource has the requested permission using the functions:

  1. user.can_create()
  2. user.can_read()
  3. user.can_update()
  4. user.can_delete()

After this its just a matter of adding the decorator to each of the view functions and the system is implemented.  🙂