Database

While developing a chat based android application, one of the most important things is keeping track of user’s messages. Since the user might want to access them in the absence of Internet connectivity (i.e remotely) as well, storing them locally is also important.

In SUSI we are using Realm to keep things organized in a systematic manner and constructing model (or adding appropriate attributes) for every new data type which the application needs. Right now we have three main models namely ChatMessage, WebLink and WebSearchModel. These three java classes define the structure of each possible message. ChatMessage evaluates and classifies incoming response from server either to be an image or map or pie chart or web search url or other valid types of response. WebSearchModel and WebLink models are there to manage those results which contains link to various web searches.

Various result based lists are maintained for smooth flow of application. Messages sent in absence of Internet are stored in a list – nonDelivered. All the messages have an attribute isDelivered which is set to true if and only if they have been queried, otherwise the attribute is set to false which puts it in the nonDelivered list. Once the phone is connected back to the internet and the app is active in foreground, the messages are sent to server, queried and we get the response back in the app’s database where the attributes are assigned accordingly.

I will explain a functionality below that will give a more clear view about our coding practices and work flow.

When a user long taps a message, few options are listed(these actions are defined in recycleradapters->ChatFeedRecyclerAdapter.java) from which you may select one. In the code, this triggers the method onActionsItemClicked(). In this Overridden method, we handle what happens when a user clicks on one of the following options from item menu. In this post I’ll be covering only about the star/important message option.

We have the count of messages which were selected. Each message having a unique id is looped through and the attribute “isImportant” of each message object is modified accordingly. To modify this field, We call the method markImportant() and pass the id of message which has to be updated.

One of the many feature requests that we got for our open event organizer server or the eventyay website is ticket ordering. The event organizers wanted to show the tickets in a particular order in the website and wanted to control the ordering of the ticket. This was a common request by many and also an important enhancement. There were two main things to deal with when ticket ordering was concerned. Firstly, how do we store the position of the ticket in the set of tickets. Secondly, we needed to give an UI in the event creation/edit wizard to control the order or position of a ticket. In this blog, I will talk about how we store the position of the tickets in the backend and use it to show in our public page of the event.

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 –

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.

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.

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

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

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

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:

Super-Admin

Admin

Organizer

Co organizer

Track organizer

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:

Tracks

Microlocations

Speakers

Sessions

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

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:

user.can_create()

user.can_read()

user.can_update()

user.can_delete()

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