Paolo Salvatori's Blog

Adventures in the magic world of Windows Azure

How to use Windows Azure Table Storage in Windows Azure Mobile Services

How to use Windows Azure Table Storage in Windows Azure Mobile Services

Rate This
  • Comments 6

Introduction

This sample demonstrates how to use Windows Azure Table Storage in Windows Azure Mobile Service to store data in place of SQL Azure. In particular, this solution demonstrates how to modify the quickstart Todo app to store data in a table in Windows Azure Storage in place of a table in SQL Azure. You can download the code from MSDN Code Gallery.

 

Architecture

The following picture shows the architecture of the solution.
  

Description

The server side scripts of the Todo app have been modified to use data stored in a TodoItems table in Windows Azure Storage. As a consequence, the companion Windows Store and HTML5 client applications have been modified to use the PartitionKey and RowKey fields used to uniquely identify items in the table. Besides, the Todo Mobile Service and client applications have been configured to authenticate users using a Microsoft account. For more information on this topic, see the following resources:

Prerequisites

Building the Sample

Proceed as follows to set up the solution.

Create the Todo Mobile Service

Follow the steps in the tutorial to create the Todo mobile service.
  1. Log into the Management Portal.
  2. At the bottom of the navigation pane, click +NEW.
  3. Expand Mobile Service, then click Create.
    This displays the New Mobile Service dialog.
  4. In the Create a mobile service page, type a subdomain name for the new mobile service in the URL textbox and wait for name verification. Once name verification completes, click the right arrow button to go to the next page.
    This displays the Specify database settings page.
    Note
    As part of this tutorial, you create a new SQL Database instance and server. You can reuse this new database and administer it as you would any other SQL Database instance. If you already have a database in the same region as the new mobile service, you can instead choose Use existing Database and then select that database. The use of a database in a different region is not recommended because of additional bandwidth costs and higher latencies.
  5. In Name, type the name of the new database, then type Login name, which is the administrator login name for the new SQL Database server, type and confirm the password, and click the check button to complete the process.
Note: defining a SQL Azure database is a mandatory step when creating a Mobile Service. However the database created in this step will not be used by this solution as data will be stored in a table in Windows Azure Storage.

Configure the application to authenticate users

Follow these steps to configure the application to authanticate users using a Microsoft Account login. For more information, see:
  1. Log on to the Windows Azure Management Portal, click Mobile Services, and then click your mobile service.
  2. Click the Dashboard tab and make a note of the Site URL value.
  3. Navigate to the My Applications page in the Live Connect Developer Center, and log on with your Microsoft account, if required.
  4. Click Create application, then type an Application name and click I accept.
    This registers the application with Live Connect.
  5. Click Application settings page, then API Settings and make a note of the values of the Client ID and Client secret.
    Security Note
    The client secret is an important security credential. Do not share the client secret with anyone or distribute it with your app.
  6. In Redirect domain, enter the URL of your mobile service, and then click Save.
  7. Back in the Management Portal, click the Identity tab, enter the Client Id and Client Secret obtained at the previous step in the microsoft account settings, and click Save.

 Restrict permissions to authenticated users

  1. In the Management Portal, click the Data tab, and then click the TodoItem table.
  2. Click the Permissions tab, set all permissions to Only authenticated users, and then click Save. This will ensure that all operations against the TodoItem table require an authenticated user. This also simplifies the scripts in the next tutorial because they will not have to allow for the possibility of anonymous users.

Create a Windows Azure Storage Account

Follow these steps to create a storage account. For more information, see How to Create a Storage Account.
  1. Sign in to the Management Portal.
  2. Click Create New, click Storage, and then click Quick Create.
    NewStorageAccount
  3. In URL, enter a subdomain name to use in the storage account URL. To access an object in storage, you will append the object's location to the endpoint. For example, the URL for accessing a blob might be http://myaccount.blob.core.windows.net/mycontainer/myblob.
  4. In Region/Affinity Group, select a region or affinity group for the storage. Select an affinity group instead of a region if you want your storage services to be in the same data center with other Windows Azure services that you are using. Note: make sure you select a Region/Affinity Group in the same data center as your mobile service.
  5. In Subscription, enter the Windows Azure subscription that you want to use the storage account with.
  6. If you don't want geo-replication for your storage account, clear the Enable Geo-Replication check box.
  7. Click Create Storage Account.
    It can take a while for the storage account to be created. To check the status, you can monitor the notifications at the bottom of the portal. After the storage account has been created, your new storage account has Online status and is ready for use.
    StoragePage

Define server side scripts

Server scripts are registered in a mobile service and can be used to perform a wide range of operations on data being inserted and updated, including validation and data modification. In this sample, they are used to customize the default behavior of a mobile service to write and read data from a table in Windows Azure Storage. For more information on server scripts, see the following resources:
To use Windows Azure storage, you need to use the Node.js azure package in server scripts. This package includes a set of convenience libraries that communicate with the storage REST services. For more information on the Node.js azure package, see the following resources:
Follow these steps to create server scripts:
  1. In the Management Portal, click the Data tab, and then click the TodoItem table.
  2. Click the scripts tab and select the insert, update, read or del script from the drow-down list.
  3. Modify the code of the selected script to add your business logic to the function.

Insert Script

The following table contains the code for the insert script.
JavaScript
Edit|Remove
function insert(item, user, request) {      var message;            // Data validation      if (!item.text) {          message = 'The text cannot be null or empty.';          console.error(message);          request.respond(statusCodes.BAD_REQUEST, message);          return;      }        // Access table service      var azure = require('azure');       var tableService = azure.createTableService('STORAGE_ACCOUNT_NAME', 'STORAGE_ACCOUNT_KEY');            // Set retry count and retry interval      var retryCount = 3;      var retryInterval = 100 // milliseconds;        // Call insertEntity function      insertEntity();        function insertEntity() {          // Create table if not exists          tableService.createTableIfNotExists('TodoItems', function(error) {              if (error) {                  if (retryCount > 0) {                      console.error('Failed to access or create TodoItems table:\r\nuserId=[' + user.userId +                                     ']\r\nretryCount=[' + retryCount +                                    ']\r\nerror=[' + error + ']');                      retryCount--;                      setTimeout(insertEntity, retryInterval);                  }                  else {                      message = 'Failed to access or create TodoItems table:\r\nuserId=[' + user.userId +                                 ']\r\nerror=[' + error + ']';                      console.error(message);                      request.respond(statusCodes.BAD_REQUEST, message);                  }              }                          else {                  // Check for duplicates: an item is considered a duplicate if                   // it has the same text and is not complete                  var tableQuery = azure.TableQuery                                  .select()                                  .from('TodoItems')                                  .where('PartitionKey eq ?', user.userId)                                  .and('text eq ?', item.text)                                  .and('complete eq ?', 'false');                    tableService.queryEntities(tableQuery, function(error, entities) {                      if (entities.length > 0) {                          console.log('Duplicate item detected:\r\nuserId = [' +  user.userId +                                       ']\r\ntext = [' + item.text + ']')                          request.respond(statusCodes.OK, item);                      }                      else {                          // Enrich the item with the createdAt property                          var date = new Date();                          var createdAt = date.getFullYear() + "/" +                                          (date.getMonth() + 1) + "/" +                                          date.getDate() + " " +                                          date.getHours() + ":" +                                          date.getMinutes() + ":" +                                          date.getSeconds();                          var entity = {                              PartitionKey :  user.userId || 'default',                              RowKey : (new Date()).getTime(),                              text : item.text,                              complete : item.complete,                              createdAt : createdAt                          };                                                    // Insert the item in the storage table                          tableService.insertEntity('TodoItems', entity, function(error) {                              if (error) {                                  if (retryCount > 0) {                                      console.error('Failed to insert the item in the TodoItems table:\r\nuserId=[' + user.userId +                                                     ']\r\nretryCount=[' + retryCount +                                                    ']\r\nerror=[' + error + ']');                                      retryCount--;                                      setTimeout(insertEntity, retryInterval);                                  }                                  else {                                      message = 'Failed to insert the item in the TodoItems table:\r\nuserId=[' + user.userId +                                                 ']\r\nerror=[' + error + ']';                                      console.error(message);                                      request.respond(statusCodes.BAD_REQUEST, message);                                  }                              }                              else {                                  console.log('Item successfully inserted for user [' + user.userId + ']:\r\n', item);                                  request.respond(statusCodes.OK, entity);                              }                          });                      }                  });              }          });      }  }
The insert script performs the following actions:
  • Validates input data. In particular, it checks that the text property is not null or empty.
  • The script uses a retry policy: in case of error, if the retry count is greater than zero, the insertEntity function calls itself recursively after the retry interval (defined in milliseconds).
  • Calls the azure.createTableService method to get access to the table storage using the corresponding storage account name and key.
  • Calls the tableService.createTableIfNotExists. This function returns the specified table if it exists or create a new table with the specified name if it does not already exist.
  • Uses the tableService.queryEntities method to check for duplicates. A todo item is considered a duplicate if an incomplete item with the same text already exists in the table for the current user.
  • Insert a new item with the specified text in the table. In particular:
    • The current userId is used as PartitionKey of the new item.
    • The value for the RowKey is calculated using the getTime method of the Date object that returns the time value in a Date Object as the number of milliseconds since midnight January 1, 1970.

Update Script

The following table contains the code for the update script.
JavaScript
Edit|Remove
function update(item, user, request) {var message;        // Data validationif (!item.PartitionKey) {          message = 'The PartitionKey cannot be null or empty.';          console.error(message);          request.respond(statusCodes.BAD_REQUEST, message);          return;      }if (!item.RowKey) {          message = 'The RowKey cannot be null or empty.';          console.error(message);          request.respond(statusCodes.BAD_REQUEST, message);          return;      }// Get access to the storage table servicevar azure = require('azure');       var tableService = azure.createTableService('STORAGE_ACCOUNT_NAME', 'STORAGE_ACCOUNT_KEY');        // Set retry count and retry intervalvar retryCount = 3;      var retryInterval = 100// milliseconds;// Call queryEntity function      updateEntity();            function updateEntity() {          tableService.queryEntity('TodoItems', item.PartitionKey, item.RowKey, function(error, entity) {if (error) {var message = 'The TodoItem table does not contain the following item:\r\nPartitionKey=[' + item.PartitionKey +                                ']\r\nRowKey=[' + item.RowKey + ']';                  console.error(message);                  request.respond(statusCodes.BAD_REQUEST, message);              }else{if (item.hasOwnProperty("complete")) {                      entity.complete = item.complete;                  }if (item.hasOwnProperty("text")) {                      entity.text = item.text;                  }                  tableService.updateEntity('TodoItems', entity, function(error){if (error)                      {if (retryCount > 0) {                              console.error('Failed to update an item in the TodoItems table:\r\nPartitionKey=[' + item.PartitionKey +                                             ']\r\nRowKey=[' + item.RowKey + ']' +                                            ']\r\nretryCount=[' + retryCount +                                            ']\r\nerror=[' + error + ']');                              retryCount--;                              setTimeout(updateEntity, retryInterval);                          }else{                              message = 'Failed to update an item in the TodoItems table:\r\nPartitionKey=[' + item.PartitionKey +                                             ']\r\nRowKey=[' + item.RowKey + ']' +                                            ']\r\nerror=[' + error + ']';                              console.error(message);                              request.respond(statusCodes.BAD_REQUEST, message);                          }}else{                          console.log('Item successfully updated:\r\n', item);                          request.respond(statusCodes.OK, item);                      }});              }});      }}
The update script performs the following actions:
  • Validates input data. In particular, it checks that the value of the PartitionKey and RowKey properties of the item are not null or empty.
  • The script uses a retry policy: in case of error, if the retry count is greater than zero, the updateEntity function calls itself recursively after the retry interval (defined in milliseconds).
  • Calls the azure.createTableService method to get access to the table storage using the corresponding storage account name and key.
  • Invokes the tableService.queryEntity to check that an entity with the given PartitionKey and RowKey actually exists in the TodoItems table.
  • Calls the tableService.updateEntity method to update the current item in the table.

Delete Script

The following table contains the code for the delete script.
JavaScript
Edit|Remove
function del(id, user, request) {                  // Get access to the storage table service      var azure = require('azure');       var tableService = azure.createTableService('STORAGE_ACCOUNT_NAME', 'STORAGE_ACCOUNT_KEY');            // Set retry count and retry interval      var retryCount = 3;      var retryInterval = 100 // milliseconds;        // Call deleteItem function      deleteItem();        function deleteItem() {          tableService.deleteEntity('TodoItems',                                     {PartitionKey : user.userId,                                     RowKey : id},                                     function(error) {              if (error) {                  if (retryCount > 0) {                      console.error('Failed to delete item:\r\nuserId=[' + user.userId +                                     ']\r\nitemId=[' + id +                                    ']\r\nretryCount=[' + retryCount +                                    ']\r\nerror=[' + error + ']');                      retryCount--;                      setTimeout(deleteItem, retryInterval);                  }                  else {                      var message = 'Failed to delete item:\r\nuserId=[' + user.userId +                                      ']\r\nitemId=[' + id +                                     ']\r\nerror=[' + error + ']';                      console.error(message);                      request.respond(statusCodes.BAD_REQUEST, message);                  }              }              else {                  console.log('Item successfully deleted:\r\nuserId=[' + user.userId +                                ']\r\nitemId=[' + id + ']');                  request.respond(statusCodes.OK, id);              }          });      }  }
The del script performs the following actions:
  • The script uses a retry policy: in case of error, if the retry count is greater than zero, the deleteEntity function calls itself recursively after the retry interval (defined in milliseconds).
  • Calls the azure.createTableService method to get access to the table storage using the corresponding storage account name and key.
  • Calls the tableService.deleteEntity method to delete the current item from the table.

Read Script

The following table contains the code for the read script.
JavaScript
Edit|Remove
function read(query, user, request) {              // Get access to the storage table      var azure = require('azure');       var tableService = azure.createTableService('STORAGE_ACCOUNT_NAME', 'STORAGE_ACCOUNT_KEY');            // Define query to retrieve only todo items for the current user      var tableQuery = azure.TableQuery      .select()      .from('TodoItems')      .where('PartitionKey eq ?', user.userId)      .and('complete eq ?', 'false');        // Retrieve todo items      queryEntities();        function queryEntities() {          tableService.queryEntities(tableQuery, function (error, entities) {              if (error) {                  var message = 'Failed to retrieve entities for user [' + user.userId + '].';                  console.error(message);                  request.respond(statusCodes.BAD_REQUEST, message);              }              else {                  console.log('Retrieved [' + entities.length + '] items for user [' + user.userId + '].');                  for (var i = 0; i < entities.length; i++) {                      entities[i].id = parseInt(entities[i].RowKey, 0);                  }                  request.respond(statusCodes.OK, entities);              }          });      }  }
The read script performs the following actions:
  • Calls the azure.createTableService method to get access to the table storage using the corresponding storage account name and key.
  • Creates a query to retrieve the todo items for the current user by using the azure.TableQuery method.
  • Calls the tableService.queryEntities method to retrieve the todo items for the current user.

HTML5 Client Application

In order to use the table storage, the HTML5 client application needs to be modified to handle the PartitionKey and RowKey properties. To accomplish this task, you need to change the code of the app.js script as illustrated in the table below. This file contains the client-side business logic of the quickstart app.
JavaScript
Edit|Remove
$(function() {      var client = new WindowsAzure.MobileServiceClient('https://YOURMOBILESERVICENAME.azure-mobile.net/', 'YOURMOBILESERVICEKEY'),          todoItemTable = client.getTable('todoitem');        // Read current data and rebuild UI.      // If you plan to generate complex UIs like this, consider using a JavaScript templating library.      function refreshTodoItems() {                    // Note: the where condition is not necessary because the Read           // server-side script already filters out items with complete = true          var query = todoItemTable.where({ complete: false });            query.read().then(function(todoItems) {              var listItems = $.map(todoItems, function (item) {                  var checked;                  switch (typeof item.complete) {                      case "string":                          checked = item.complete.toLowerCase() == "true";                          break;                      case "boolean":                          checked = item.complete;                          break;                      default:;                  }                  return $('<li>')                      .attr('data-todoitem-partitionkey', item.PartitionKey)                      .attr('data-todoitem-rowkey', item.RowKey)                      .append($('<button class="item-delete">Delete</button>'))                      .append($('<input type="checkbox" class="item-complete">').prop('checked', checked))                      .append($('<div>')                      .append($('<input class="item-text">').val(item.text))                      .append($('<span class="timestamp">' + (item.createdAt || '') + '</span>')));              });                            $('#todo-items').empty().append(listItems).toggle(listItems.length > 0);              $('#summary').html('<strong>' + todoItems.length + '</strong> item(s)');          });      }        function getTodoItemId(formElement) {          return parseInt(getTodoItemRowKey(formElement));      }        function getTodoItemPartitionKey(formElement) {          return $(formElement).closest('li').attr('data-todoitem-partitionkey');      }        function getTodoItemRowKey(formElement) {          return $(formElement).closest('li').attr('data-todoitem-rowkey');      }        function getTodoItemText(formElement) {          return $(formElement).closest('li').find('.item-text').val();      }        // Handle insert      $('#add-item').submit(function(evt) {          var textbox = $('#new-item-text'),              itemText = textbox.val();          if (itemText !== '') {              todoItemTable.insert({ text: itemText, complete: false })                            .then(function () {                              $('#summary').html('<strong>Item successfully inserted.</strong>');                          })                            .then(refreshTodoItems, function (error) {                              $('#summary').html('<strong>' + error.request.responseText + '</strong>');                          });          }          textbox.val('').focus();          evt.preventDefault();      });        // Handle update      $(document.body).on('change', '.item-text', function() {          var newText = $(this).val();          var item = { id: getTodoItemId(this),                       PartitionKey: getTodoItemPartitionKey(this),                        RowKey: getTodoItemRowKey(this),                        text: newText          };          todoItemTable.update(item)                       .then(function () {                          $('#summary').html('<strong>Item successfully updated.</strong>');                      }, function (error) {                          $('#summary').html('<strong>' + error.request.responseText + '</strong>');                      });      });        $(document.body).on('change', '.item-complete', function() {          var isComplete = $(this).prop('checked');          var item = { id: getTodoItemId(this),                       PartitionKey: getTodoItemPartitionKey(this),                        RowKey: getTodoItemRowKey(this),                        complete: isComplete,                       text: getTodoItemText(this)          };          todoItemTable.update(item)                       .then(function() {                           $('#summary').html('<strong>Item successfully updated.</strong>');                       })                       .then(refreshTodoItems, function (error) {                          $('#summary').html('<strong>' + error.request.responseText + '</strong>');                       });      });        // Handle delete      $(document.body).on('click', '.item-delete', function () {          todoItemTable.del({ id: getTodoItemId(this) })                       .then(function () {                           $('#summary').html('<strong>Item successfully deleted.</strong>');                       })                       .then(refreshTodoItems);      });        // On initial load, start by fetching the current data      // refreshTodoItems();      function refreshAuthDisplay() {          var isLoggedIn = client.currentUser !== null;          $("#logged-in").toggle(isLoggedIn);          $("#logged-out").toggle(!isLoggedIn);            if (isLoggedIn) {              $("#login-name").text(client.currentUser.userId);              refreshTodoItems();          }          else          {              $('#todo-items').empty();          }      }        function logIn() {          client.login("microsoftaccount").then(refreshAuthDisplay, function(error){              alert(error);          });      }        function logOut() {          client.logout();          refreshAuthDisplay();          $('#summary').html('<strong>You must login to access data.</strong>');      }        // On page init, fetch the data and set up event handlers      $(function () {          refreshAuthDisplay();          $('#summary').html('<strong>You must login to access data.</strong>');                    $("#logged-out button").click(logIn);          $("#logged-in button").click(logOut);      });  });
Note: when invoking any of the methods (insert, update, del) exposed by the todoItemTable object, you need to define an id property for the item. In fact, server scripts expect that the input item contains such a property of type integer.
The client code has been modified to require the user to login with his/her Microsoft Account. For more information on how to extend a client application to authenticate against one of the identity providers supported by Mobile Services, see Get started with authentication in Mobile Services.
    

Windows Store App

In order to use the table storage, the Windows Store app created out of the box by the quickstart tutorial needs to be modified handle the PartitionKey and RowKey properties. In particular, the class that models the TodoItem entity needs to be changed as follows:
C#
Edit|Remove
public class TodoItem  {      public long Id { get; set; }        [DataMember(Name = "PartitionKey")]      public string PartitionKey { get; set; }        [DataMember(Name = "RowKey")]      public string RowKey { get; set; }        [DataMember(Name = "Timestamp")]      public DateTime Timestamp { get; set; }        [DataMember(Name = "text")]      public string Text { get; set; }        [DataMember(Name = "complete")]      public bool Complete { get; set; }        [DataMember(Name = "createdAt")]      public string CreatedAt { get; set; }  }
Make sure to add authentication against Microsoft Account identity provider using one of the following methods:

Conclusions

Mobile services can easily be extended to get advantage of the services provided by Windows Azure, like table and blob storage services, Service Bus and Push Notifications. To do so, it's sufficient to use the objects and Node.js packages that Mobile Services make available to developers. For more information, see Mobile Services server script reference. You can download the code from MSDN Code Gallery. See also the following articles on Windows Azure Mobile Services:
  • Too much Javascript, my head hurts...

  • Ah ah ah... nice comment Erik ;)

  • Nice article. But in the read function you totaly ignore the query parameter. Is it possible to parse "query" into a tablequery?

  • Hi Lyfexu,

    the answer is pretty straightforward: in my sample I just wanted to retrieve incomplete items for the current user. What you want to achieve is another scenario: the client app sends a generic query and the server script interprets, validates and executes that query. ;) So why do I ignore the query object? Because I implement another scenario. ;) Said that, you can customize my sample and use the query object! Hope this helps! :)

    Ciao

    Paolo

  • Can't I call the table storage REST service from mobile service?

  • Hi Mathi

    the answer is yes. More in general, you can use the request, http and https Node.js modules to invoke any REST service, hence, also the table storage REST service. See http://nodejs.org/api/all.html for more info.

    Ciao

    Paolo

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
Search Blogs