Friday, December 7, 2012

My experiments with Windows Azure Mobile Services- Part II

The first post on this topic was written in October here. Though I wanted to follow it up quickly with Part II, I got distracted, but here I am with the second part of Azure Mobile Services.

One of the things that wasn’t detailed in the first post was the Ratings class. So I will start from there. The ratings class is simply an instance of the ratings table in the database. It hence inherits the AzureMobileTable class that we saw in the previous post.

The table has the columns listed below


The scenario that we will deal with in this post, is to calculate the Average ratings for any given movie. This can be  done in two ways

  • Get all the movies to the service and calculate the average in the service. The downside of this is that if I have a lot of ratings for a movie I have to fetch all the records which is inefficient.
  • The second option is to calculate the Average in Azure Mobile Services and return the records. The question though is, how to do this with Azure Mobile Services.

Azure Mobile Services has support for scripting. Every table has a tab called script and when you click on it, you will see that you can write your script for 4 operations; Insert, Update, Delete and Read. This is what we will use for calculating averages. But there is a catch. Each operation just supports one implementation of the script. The read, for example by default executes read all, which we have already used. So you cannot write another read script to do Averages.

One way to get around this limitation is to create another Azure Mobile Service table called Averages and script the read operation of this table to calculate averages for the ratings table. Some code snippet is in order. So here goes

   1: function read(query, user, request) {
   2:     mssql.query("SELECT AVG(Value) as Average from Ratings WHERE MovieID = ?", [], {
   3:         success:function(results) {
   4:             request.respond(200, results[0]);    
   5:         }
   6:     });
   7:     //console.log(query);
   8:     //console.log(query.getComponents());
   9: }

The script calculates the average for a given movie id and returns the status of 200 along with the results. The commented code just show you how to use logging.

Now, what is left to do is to call this from our service described in Part I. There is a method in the Ratings class that calls this script.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Web;
   6: namespace TMDBHelperService.AzureMobileServices
   7: {
   8:     public class Ratings: AzureMobileTable
   9:     {
  10:         public Ratings():base("ratings")
  11:         {
  13:         }
  15:         public AzureMobileQuery getAverage(int movieId)
  16:         {
  17:             return new AzureMobileQuery("tables/averages/" + movieId.ToString());
  18:         }
  19:     }
  20: }

Note the getAverage method queries against the Averages table and not the ratings table. The only other thing left to do is to call this method from our broker.

   1: public string getAverageRatings(int movieId)
   2:         {
   3:             var _ratingsTable = new Ratings();
   4:             AzureMobileQuery _query = _ratingsTable.getAverage(movieId);
   5:             if (_query!=null)
   6:             {
   7:                 return _helperClass.Execute(_query);    
   8:             }
   9:             return String.Empty;
  10:         }

This solution works very well if you just have one or two operations. But it quickly becomes unmanageable if you have write a bunch of custom operations on a table.

The better way of doing this is to use custom parameters. You can pass custom parameters by appending them as query strings to the table uri. Something like below


Then change the ratings read query to do different operations based on these parameters.

   1: function read(query, user, request) {
   2:     if(request.parameters.ops == "avg")
   3:     {
   4:         console.log(request.parameters.ops);
   5:         console.log(request.parameters.movieid);
   6:         mssql.query("SELECT AVG(Value) as Average from Ratings WHERE MovieID = ?", [request.parameters.movieid], {
   7:             success:function(results) {
   8:                 request.respond(200, results[0]);    
   9:             }
  10:         });
  11:     }
  12:     else
  13:     {
  14:         request.execute();
  15:     }
  16: }

This is a bit more elegant way of “overloading” the read operation without the overhead of additional tables.

I find myself using Azure Mobile Services more and more. I love the simplicity of it for small applications and the speed with which I can get it up and running. They are not perfect but if you are on the fence, I highly encourage you to try them.