30 Days of Zumo.v2 (Azure Mobile Apps): Day 11 – Table Access

There are times when accessing one table for your data is just not enough. Let’s take an example. I have a simple task list app right now. I’ve got it running on all the mobile platforms – iOS, Android and Windows. But I can only see my tasks. What if I want to show my tasks to someone else? I’m going to put together a fairly simple adjustment to my application during this article. Firstly, I’m going to add a “Friends” page to my Universal Windows application – where I can enter the email address of a friend and then they can see my tasks (with a different icon)

The Friends Table

To start with, I need a friends table. This is a new table called friend.js but is just like the todoitem.js table:

var azureMobileApps = require('azure-mobile-apps');

// Create a new table definition
var table = azureMobileApps.table();

// Require authentication
table.access = 'authenticated';

// CREATE operation
table.insert(function (context) {
    context.item.userId = context.user.emailaddress;
    return context.execute();
});

// READ operation
table.read(function (context) {
    context.query.where({ userId: context.user.emailaddress });
    return context.execute();
});

// UPDATE operation
table.update(function (context) {
    context.query.where({ userId: context.user.emailaddress });
    context.item.userId = context.user.emailaddress;
    return context.execute();
});

// DELETE operation
table.delete(function (context) {
    context.query.where({ userId: context.user.emailaddress });
    return context.execute();
});

module.exports = table;

I’ve also created a new Universal Windows client that processes this table. You can find the client at my GitHub repository. It has a People tab and a Tasks tab. The People tab allows you to enter email addresses in to the table so that other people can view your data. Note that this is in no way how I would write this code in reality. There are way too many mechanisms by which you can abuse this. However, it’s good to view as a proof of concept. I’m also not going to cover the .NET client – this is about the backend code, not the frontend.

The question for this blog post is this: How do I implement a mechanism whereby my table view is limited by another table? In this case, I want to view all records that I am allowed to see – either because I own them or because someone has given me permission to view them. This will be a change to the read() method in the todoitem.js table controller. I am splitting this into two halves. In the first half I will get a list of all the user IDs that I can view:

// READ operation
table.read(function (context) {
    return context.tables('friend')
        .where({ viewer: context.user.emailaddress })
        .select('userId')
        .read()
        .then(function (friends) {
            console.log('READ: Response from SQL Query = ', friends);
            var list = friends.map(function (f) { return f.userId; }).push(context.user.emailaddress);
            console.log('READ: List of user ids = ', list);
            
            // TODO: Adjust the query according to the requirements
            return context.execute();
        });
});

Let’s take this line by line. Line 18 gets a reference to the friend table. I then construct a query whereby all records where the current user is marked as a viewer. The table itself has just two columns – userId is the “owner” and viewer is the email address I place in the table in the UI. It reads “viewer can read the tasks for userId”. Once I’ve got that, I’m going to limit the returned columns to just userId and then finally (line 21) execute the query and read the results. The result of this is a Promise that resolves to the list of userIds. I’ll get an array of objects – each object will have one property (the userId field).

There are more methods that you can use that are “LINQ-like”. I’ve already introduced where, select and read. You can also use:

  • orderBy(field) and orderByDescending(field) to do sorting
  • skip(count) and take(count) to implement paging
  • includeTotalCount() to add a total number of records field to the results

Once I’ve resolved the promise, I need to convert the list to a list of email addresses – these become the list of email addresses to use as the viewership. Notice that I add the current users email address to the list.

Next, I need to adjust my search of the database to return tasks owned by anyone in the list of email addresses I’ve just created. This is actually quite hard to do. One of the methods of altering the query that is sent to the database is to provide a function:

// READ operation
table.read(function (context) {
    return context.tables('friend')
    .where({ viewer: context.user.emailaddress })
    .select('userId')
    .read()
    .then(function (friends) {
        var list = friends.map(function (f) { return f.userId; })
        list.push(context.user.emailaddress);
        context.query.where(function(list) { return this.userId in list; }, list);
        return context.execute().then(function (results) {
          // Process the results
          console.log('results = ', results);
          return results;
        });
    });
});

This is a different mechanism to altering the query. Underneath, the query is compiled to an AST by esprima, and then converted to SQL from the compiled form. You never have to worry about this until it bites you because you see JavaScript and assume you can use any JavaScript function in there where method. There is a limited set of things you can do within a function call. In the context of the function, this refers to the current item. You can pass in one or two arguments (such as lists) and anything you need to use must be passed in. You can’t rely on the context that the function appears to be running in – it’s not running a function. Once inside the function, there is a set of methods you can use:

  • toUpperCase()
  • toLowerCase()
  • trim()
  • indexOf()
  • concat()
  • substring() or substr()
  • replace()
  • getFullYear()
  • getUTCFullYear()
  • getYear()
  • getDate()
  • getUTCDate()
  • in

Obviously, most of these only work on specific datatypes – strings and dates, most normally. However, you can use in with arrays. You can also use math capabilities as well as boolean logic inside the function. I’m using this functionality to change the query to be “records I am allowed to view”. My final step in this rather complicated read logic is to change the results so that I return a computed “shared” flag:

// READ operation
table.read(function (context) {
    return context.tables('friend')
    .where({ viewer: context.user.emailaddress })
    .select('userId')
    .read()
    .then(function (friends) {
        var list = friends.map(function (f) { return f.userId; })
        list.push(context.user.emailaddress);
        context.query.where(function(list) { return this.userId in list; }, list);
        return context.execute().then(function (results) {
          results.forEach(function (item) {
            // Item is shared if the owner of item is not current owner
            item.shared = (item.userId !== context.user.emailaddress);
          });
          return results;
        });
    });
});

Here, the highlighted lines add in a computed field called shared that is true when the record does not belong to the current user. I also need to add in some logic to REMOVE the shared flag from the incoming records:

// CREATE operation
table.insert(function (context) {
    context.item.userId = context.user.emailaddress;
    delete context.item.shared;
    delete context.item.Shared;
    return context.execute();
});

// UPDATE operation
table.update(function (context) {
    context.query.where({ userId: context.user.emailaddress });
    context.item.userId = context.user.emailaddress;
    delete context.item.shared;
    delete context.item.Shared;
    return context.execute();
});

Next Steps

Today, I’ve gone through the more esoteric adjustments to the query you can do, doing sub-queries and manipulating the results. In the next article, I’m going to start looking at doing conflict resolution.

In the mean time, you can download the client and server from my GitHub Repository