Build new applications in the cloud - or use interoperable services that run on Microsoft infrastructure to extend and enhance your existing applications. You choose what's right for you.

Table of Contents

 


 

Lab 2.2 — Windows Azure Data Storage

Table Services

 

In this lab, you will learn how to use Windows Azure Storage Service by applying it to Table Services.

 


 

Task 0 — Prerequisites

 

Before doing this lab, if you not done so:

 

  1. Complete install of Windows Azure for Eclipse
  2. Complete introduction lab Getting Started
  3. Complete first storage lab Getting Started with Storage using Blobs
  4. This lab requires that you register for a developer account in order perform tasks pertaining to Windows Azure Platform account.

     

    To register, please visit Windows Azure Platform — Account Information.

  5. Get Tables Lab:   Download

 


 

Part 1 — Windows Azure Toolkit 4 Eclipse

 


 

 

Task 1 — Windows Azure Table Storage

 

Windows Azure Table provides scalable, available, and durable structured storage in the form of tables. The tables contain entities, and the entities contain properties. The tables are scalable to billions of entities and terabytes of data, and may be partitioned across thousands of servers. The tables support ACID transactions over single entities and rich queries over the entire table. Simple and familiar .NET and REST programming interfaces are provided via ADO.NET Data Services.

 

A table holds some number of entities. An entity contains zero or more properties, each with a name, a type, and a value. A variety of types are supported, including Binary, Bool, DateTime, Double, GUID, Int, Int64, and String, and a property can take on different types at different times depending on the value stored in it. Furthermore, there's no requirement that all properties in an entity have the same type—a developer is free to do what makes the most sense for her application.

 

Whatever it contains, an entity can be up to one megabyte in size, and it's always accessed as a unit. Reading an entity returns all of its properties, and writing one atomically replaces all of its properties.

Non-Relational Structured Tables

Windows Azure Storage tables are different from relational tables in a number of ways. Most obviously, they're not tables in the usual sense. Also, they can't be accessed using ordinary ADO.NET, nor do they support SQL queries. And tables in Windows Azure Storage enforce no schema—the properties in a single entity can be of different types, and those types can change over time. The obvious question is: Why? Why not just support ordinary relational tables with standard SQL queries?

 

The answer grows out of the primary Windows Azure goal of supporting massively scalable applications. Traditional relational databases can scale up, handling more and more users by running the DBMS on ever-larger machines. But to support truly large numbers of simultaneous users, storage needs to scale out, not up. To allow this, the storage mechanism needs to get simpler: Traditional relational tables with standard SQL don't work anymore. What's needed is the kind of structure provided by Windows Azure tables.

 

Using tables requires some re-thinking on the part of developers, since familiar relational structures can't be applied unchanged. Still, for creating very scalable applications, this approach makes sense. For one thing, it frees developers from worrying about scale—just create new tables, add new entities, and Windows Azure takes care of the rest. It also eliminates much of the work required to maintain a DBMS, since Windows Azure does this for you. The goal is to let developers focus on their application rather than on the mechanics of storing and administering large amounts of data.

 

Table Data Model

The following summarizes the data model for Windows Azure Table:

 

Table RESTful Interface

Like everything else in Windows Azure Storage, tables are accessed RESTfully. A .NET application can use ADO.NET Data Services or Language Integrated Query (LINQ) to do this, both of which hide the underlying HTTP requests. Any application, .NET or otherwise, is also free to make these requests directly. For example, a query against a particular table is expressed as an HTTP GET against a URI formatted like this:

 

http://<StorageAccount>.table.core.windows.net/<TableName>?$filter=<Query>

 

Here, <TableName> specifies the table being queried, while <Query> contains the query to be executed against this table. If the query returns a large number of results, a developer can get a continuation token that can be passed in on the next query. Doing this repetitively allows retrieving the complete result set in chunks.

 

Windows Azure SDK for PHP

This SDK provides access through PHP class to Windows Azure's storage, computation and management interfaces by abstracting the REST/XML interface Windows Azure provides into a simple PHP API.

 

     Learn more...

 


 

Task 2 — Create Windows Azure PHP Project for Table Storage

 

In this task, because Table is a serviced Windows Azure Data Storage type, you will create a Windows Azure PHP Project that uses Windows Azure Data Storage.

 

  1. Start Eclipse with Windows Azure PHP Projects plug-in installed.
  2. Assign or Create workspace folder as Eclipse starts.
  3. Change Eclipse perspective from PHP to PHP Windows Azure.
    1. In Eclipse menu-bar, select Window menu.
    2. In Window menu, select Open Perspective menu-item.
    3. In Open Perspective menu-item, select Other... cascading menu-item.
    4. In Open Perspective panel, select PHP Windows Azure
  4. In the PHP Windows Azure perspective, create a new Windows Azure Web Project.
    1. In Eclipse menu-bar, select File menu.
    2. In File menu, select New menu-item.
    3. In New menu-item, select Windows Azure Web Project cascading menu-item.
    4. In new PHP Azure Project panel:
      1. Provide project name TableStorage
      2. In Data Storage Options, select Windows Azure Data Storage
      3. Enable JavaScript support.
      4. Select Finish button
  5. Note that in the PHP Explorer, examine the structure of the created solution.

    Two PHP projects have been created:

    • TableStorage
    • TableStorage_WebRole

 

Task 3 — Windows Azure Data Storage — Table Sample Files

 

In this task you will replace default PHP Blob sample files provided by toolkit when creating a PHP Windows Azure Web Project intended for using Windows Azure Data Storage with downloaded PHP Table sample files.

 

When creating a PHP Windows Azure Web Project and setting Data Storage Option to Windows Azure Data Storage, the toolkit will include within the Web Role PHP project sample files using Blob storage service.

 

In this task, you will replace the Blob Sample files with Table Sample files. Table Sample files are found within download performed in Task 0.

 

Table Sample Setup

Replace the Blob Sample files with Table Sample files.

Delete Blob Sample Files

Delete within Web Role project

Add Table Sample Files

Add from download's TableSample folder into Web Role project:

Table Sample Files

index.php

Essentially a simple phpinfo() page and provides a link to perform Windows Azure blob storage operations by loading boiler-plate PHP sample file TableSample.php.

SampleEntity.php

Support file for boiler-plate PHP sample file TableSample.php defining an entity class.

TableSample.php

A boiler-plate PHP sample file to get started using Table Services within Windows Azure Data Storage programming with Windows Azure SDK for PHP.

WindowsAzureAccountInfo.php

This configuration file defines Windows Azure Storage Account information for dev and cloud storage.

 


 

Task 4 — Run in local Development Fabric

 

In this task, you will run Table Service locally in the Development Fabric.

 

  1. Start Development Fabric and Storage:
    1. Right-click its icon in the system tray and select Start Development Fabric Service
    2. Right-click its icon in the system tray and select Start Development Storage Service
  2. Start deployment of Web Role in local Development Fabric:
    1. Open PHP Explorer view
    2. Select either Service PHP project TableStorage or Web Role PHP project TableStorage_WebRole
    3. In Eclipse menu-bar, select Windows Azure menu.
    4. In Windows Azure menu, select Run in Development Fabric menu-item.
  3. When service begins:
    • Default browse should open and present the defaultDocument of index.php at the next available port, typically at URL http://127.0.0.1:81/index.php
    • Explorer should open to workspace folder of Service project TableStorage.
    • Development Storage UI shows that Table storage is Running.
  4. With defaultDocument opened to index.php, select link within:

     

    Click here to perform Windows Azure Table Storage Operations.

     

  5. Within the browser, it should present results from actions executed within TableStorage.php.


 

Task 5 — Deploy to remote Windows Azure Cloud

 

In this task, you will run Table Service remotely on Windows Azure using a Windows Azure Storage Account.

 

  1. Configure file WindowsAzureAccountInfo.php with your Cloud Storage account information.
    PHP — WindowsAzureAccountInfo.php default Cloud Storage account settings.
    /**
     * Windows Azure Storage Account for Cloud Environment
     */
    define('CLOUD_STORAGE_ACCOUNT', "MyAccountName");
    define('CLOUD_STORAGE_KEY', "MyAccountKey");
    

     

  2. Create a Windows Azure Service Package.
    1. Open PHP Explorer view
    2. Select either:
      • Service project TableStorage
      • or Web Role project TableStorage_WebRole
    3. In Eclipse menu-bar, select Windows Azure menu.
    4. In Windows Azure menu, select Package Application menu-item.

      This action will open a portal to Windows Azure, which will be gone in more detail later in this lab.

  3. When toolkit finishes creating Service Package, it opens default browser to Windows Live Sign-In in order to access your Windows Azure account.
  4. After sign-in, you are redirected to Windows Azure Portal
  5. Select deployment to Staging service.
  6. In Staging Deployment page
    1. From Service project TableStorage workspace directory, browse to provide:
      • Application PackageTableStorage.cspkg
      • Configuration SettingsServiceConfiguration.cscfg
    2. Set Service Deployment NameTableStorage
    3. Select Deploy button and Staging begins copying over the two files for TableStorage deployment.
  7. In Staging page, after deployment is Allocated, select Run button.
  8. In Staging page, after deployment is Ready, select Web Site URL link.
  9. The Web Site URL from Staging Cloud should show the defaultDocument index.php.
  10. Select the link within index.php, and it should show the results from TableSample.php.

 

 


 

Part 2 — Windows Azure SDK for PHP — Table Storage

 


 

 

Task 6 — Lab Files — Table Storage

 

At this point of the lab, we will be focusing upon what Windows Azure SDK for PHP provides for handling Table Storage.

 

Table Storage is offered by Windows Azure as a REST API which is wrapped by the Windows Azure SDK for PHP in order to provide a native PHP interface to the storage account.

 

Table Storage Example

Within this lab's download, there is a PHP file we will be using within this lab:

  1. TableStorageExample.php
    • class Table_Storage_Example that encapsulates some of the key Table Storage functionality of Windows Azure SDK for PHP.
    • TableEntityExample.php — Enforced schema table entity extending class Microsoft_WindowsAzure_Storage_TableEntity.

 

Copy PHP file TableStorageExample.php into PHP project TableStorage_WebRole.

 

 

Table_Storage_Example::main()

Open file TableEntityExample.php. Within class Table_Storage_Example, there is a static function main() which will be used to execute example functions using Table Storage methods from Windows Azure SDK for PHP.

 

What occurs in static function main() are:

  1. Creates an Table Storage client
  2. Default using Azure Storage Account
  3. Generates a unique table name
  4. Executes code samples

 

PHP — Table_Storage_Example::main()
  /**
   * Main method for executing example methods using Table.
   * 
   * @return void
   */
  public static function main ()
  {
    echo __METHOD__ . "\n";
    
    try {
      /*
       * Create new table storage client.
       */
      $example = new Table_Storage_Example();
      if(!$example->isValid()) {
        printf("%s::%d >> Failed %s\n", __METHOD__, __LINE__, "__construct");
        return;
      }
      
      /*
       * Delete all tables if they exist.
       */
      $example->doDeleteAllTables();
      
      /*
       * Generate a unique table name.
       */
      $strTableName = "testtable";
      $strTableName = uniqid( $strTableName );
      echo $strTableName . "\n";
  
      /*
       * Save unique table which will be used by distructor
       * if execution of this example goes out of scope.
       */
      $example->setTableName( $strTableName);
      
      /*
       * START OF EXAMPLES
       */

 

Run Table Storage Example

To execute provided Table Storage example, perform either:

  1. Select file TableEntityExample.php, and Run or Debug as PHP Script within Eclipse.
  2. OR, open command window within directory PHP project TableStorage_WebRole, and execute php TableEntityExample.php.

If everything goes well, then execution of TableEntityExample.php should end with "Success".

 

Console — Executing PHP Script TableEntityExample.php
  Table_Storage_Example::main
  Table_Storage_Example::__construct
  Table_Storage_Example::doCreateTableStorageClient
  Table_Storage_Example::doDeleteAllTables
  Unique table name: testtable4b16f67584992
  Table_Storage_Example::doCreateTable
  Table_Storage_Example::doRetrieveEntitiesAll
  Table_Storage_Example::doRetrieveEntitiesByQueryAll
  Table_Storage_Example::doInsertEntity
  Timestamp: 2009-12-02T23:21:26.53Z
  Table_Storage_Example::doInsertEntity
  Timestamp: 2009-12-02T23:21:26.563Z
  Table_Storage_Example::doRetrieveEntitiesAll
  Array
  (
      [0] => stdClass Object
          (
              [Name] => Name
              [Type] => Edm.String
              [Value] => Maarten
          )

      [1] => stdClass Object
          (
              [Name] => Age
              [Type] => Edm.Int64
              [Value] => 27
          )

      [2] => stdClass Object
          (
              [Name] => ID
              [Type] => Edm.Guid
              [Value] => a3f9d242-6680-6fd4-2de6-1b3d20000a57
          )

  )

  Array
  (
      [0] => stdClass Object
          (
              [Name] => Name
              [Type] => Edm.String
              [Value] => Jeff
          )

      [1] => stdClass Object
          (
              [Name] => Age
              [Type] => Edm.Int64
              [Value] => 100
          )

      [2] => stdClass Object
          (
              [Name] => ID
              [Type] => Edm.Guid
              [Value] => 5c1bc6c3-4caa-df24-21b5-be596f03da58
          )

  )

  Table_Storage_Example::doRetrieveEntityByKeys
  Maarten
  Table_Storage_Example::doRetrieveEntitiesByFilterUseKeys
  Maarten
  Table_Storage_Example::doRetrieveEntitiesByQueryUseKeysInWhere
  Maarten
  Table_Storage_Example::doRetrieveEntitiesByQueryUseWhereKeys
  Maarten
  Table_Storage_Example::doRetrieveEntitiesByQueryUseName
  Maarten
  Table_Storage_Example::doRetrieveEntitiesByFilterUsePartitionKey
  Maarten
  Jeff
  Table_Storage_Example::doListTables
  Table name is: testtable4b16f67584992
  Table_Storage_Example::doDeleteEntity
  Table_Storage_Example::doDeleteEntity
  Table_Storage_Example::doDeleteTable
  Success
  Table_Storage_Example::__destruct
  Table_Storage_Example::doDeleteTable

 


 

Task 7 — SDK — Table Storage Methods

 

The focus of this task is upon the key methods that pertain specifically to tables, and not entities.

 

Table Storage Client

Create Instance

Class Microsoft_WindowsAzure_Storage_Table &ndash To start using Table Storage, you need to create an instance of this class to create a Table Storage client.

 

This class encapsulates support for Windows Azure Data Storage's Table Services.

 

It is an extension of class Microsoft_WindowsAzure_Storage which encapsulates a storage host's policy, credentials, and proxy; and it contains constants for use within cloud or development environment.

 

Method Microsoft_WindowsAzure_Storage_Table::__construct() is called when instance is created.

 

Retry Policy

The retry policies are there to help your application to perform storage operations even if the network is unreliable. Each of the policies works a different way, but generally they attempt to retry operations against storage that fail.

 

For example, if the failure is network-related, then trying again should succeed.

 

PHP — Create a Table Storage Client
  /**
   * Create a Table Storage Client
   * 
   * @return boolean True upon success
   */
  protected function doCreateTableStorageClient() 
  {
    echo __METHOD__ . "\n";
    
    $fSuccess = false;
    
    if (isset($_SERVER['USERDOMAIN']) && $_SERVER['USERDOMAIN'] == 'CIS')
    {
      /* Production Cloud */
      $host = Microsoft_WindowsAzure_Storage::URL_CLOUD_TABLE;
      $accountName = CLOUD_STORAGE_ACCOUNT;
      $accountKey = CLOUD_STORAGE_KEY;
      $usePathStyleUri = false;
    }
    else
    {
      /* Development Fabric */
      $host = Microsoft_WindowsAzure_Storage::URL_DEV_TABLE;
      $accountName = DEV_STORAGE_ACCOUNT;
      $accountKey = DEV_STORAGE_KEY;
      $usePathStyleUri = true;
    }
  
    try {
      $retryPolicy = Microsoft_WindowsAzure_RetryPolicy::retryN(10, 250);
        
      $this->storageClient 
          = new Microsoft_WindowsAzure_Storage_Table(
                              $host,
                              $accountName,
                              $accountKey,
                              $usePathStyleUri,
                              $retryPolicy
                              );
                              
      $fSuccess = true;
      
    } catch ( Microsoft_WindowsAzure_Exception $e ) {
      printf("%s::%d >> %s\n", __METHOD__, __LINE__, $e->getMessage());
    } catch ( Exception $e ) {
      printf("%s::%d >> %s\n", __METHOD__, __LINE__, $e->getMessage());
    }
    
    return $fSuccess;
  }

 

Creating a Table

PHP — Create a Table
      if (!$this->storageClient->tableExists( $strTableName )) {
        $this->storageClient->createTable( $strTableName );
      }
          
      $fSuccess = $this->storageClient->tableExists( $strTableName);

 

Listing Tables

PHP — List Tables
      $aTableInstances = $this->storageClient->listTables();
      foreach ($aTableInstances as $table)
      {
        echo 'Table name is: ' . $table->Name . "\r\n";
      }

 

Deleting a Table

PHP — Deleting a Table
      if ($this->storageClient->tableExists( $strTableName)) {
        $this->storageClient->deleteTable( $strTableName);
        
        $fSuccess = !$this->storageClient->tableExists( $strTableName);
      } else {
        $fSuccess = true;
      }

 

Deleting All Tables in Table Storage Account

To delete all tables within a Table storage account requires getting a list of all tables and then deleting them iteratively by table name.

 

PHP — Delete All Tables in Table Storage Account
      $aTableInstances = $this->storageClient->listTables();
      if (!empty($aTableInstances)) {
        foreach($aTableInstances as $tableInstance) {
          $tableName = $tableInstance->Name;
          $this->storageClient->deleteTable($tableName);
        }
        
        $aTableInstances = $this->storageClient->listTables();
        $fSuccess = empty($aTableInstances);
      } else {
        $fSuccess = true;
      }

 


 

Task 8 — SDK — Defining a Table Entity

 

Tables store data as collections of entities. Entities are similar to rows. An entity has a primary key and a set of properties. A property is a name, typed-value pair, similar to a column.

 

The Table service does not enforce any schema for tables, so two entities in the same table may have different sets of properties. Developers may choose to enforce a schema on the client side. A table may contain any number of entities.

Supported Entity Types

This SDK provides support for these two entity types

  1. Enforced Schema Entities – Entity metadata defined at design-time using class Microsoft_WindowsAzure_Storage_TableEntity
  2. Dynamic Schema Entities – Entity metadata defined at run-time using class Microsoft_WindowsAzure_Storage_DynamicTableEntity

 

Defining Entity Properties

The Windows Azure SDK for PHP will map any class inherited from class Microsoft_WindowsAzure_Storage_TableEntity to Windows Azure table storage entities with the correct data type and property name. All there is to storing a property in Windows Azure is adding a docblock comment to a public property or public getter/setter, in the following format:

PHP — Defining Entity Property Template
  /**
   * @azure <property name in Windows Azure> <optional property type>
   */
  public $<property name in PHP>;

 

Let's see how to define an entity property "Age" as an integer on Windows Azure table storage:

PHP — Defining Entity Property Example
  /**
   * @azure Age Edm.Int64
   */
  public $Age;

 

Supported Entity Data Model (EDM) Simple Primative Types

When defining an entity property, the following Entity Data Model (EDM) Simple Primative Types are supported:

Property Type Details
Edm.Binary An array of bytes up to 64 KB in size.
Edm.Boolean A Boolean value.
Edm.DateTime A 64-bit value expressed as UTC time. The supported range of values is 1/1/1601 to 12/31/9999.
Edm.Double A 64-bit floating point value.
Edm.Guid A 128-bit globally unique identifier.
Edm.Int32 A 32-bit integer.
Edm.Int64 A 64-bit integer.
Edm.String A UTF-16-encoded value. String values may be up to 64 KB in size.

 

Defining an Enforced Schema Entity

To enforce a schema on the client side using Windows Azure SDK for PHP, you can create a class which inherits class Microsoft_WindowsAzure_Storage_TableEntity. This class provides some basic functionality for the Windows Azure SDK for PHP to work with a client-side schema.

 

The base system properties provided by class Microsoft_WindowsAzure_Storage_TableEntity are:

 

Example Implementation

Here's an example entity class inheriting class Microsoft_WindowsAzure_Storage_TableEntity:

 

Defined are:

PHP — Implementing a Table Entity
/**
 * @see Microsoft_WindowsAzure_Storage_TableEntity
 */
require_once 'Microsoft/WindowsAzure/Storage/Table.php';
 
class Table_Entity_Example extends Microsoft_WindowsAzure_Storage_TableEntity
{
  /**
   * Partition Key.
   * @var string
   */
  const PARTITIONKEY = "testexample";
  
  /**
   * Constructor
   */
  public function __construct($partitionKey = '', $rowKey = '')
  {
    if ( $partitionKey === '' && $rowKey === '' ) {
      $this->setPartitionKey(self::PARTITIONKEY);
      $this->setRowKey( uniqid ());
    } else {
      parent::__construct($partitionKey, $rowKey);
    }
  }
  
  /**
   * @azure Name Edm.String
   */
  public $Name;
  
  /**
   * @azure Age Edm.Int64
   */
  public $Age;
  
  /**
   * @azure Visible Edm.Boolean
   */
  public $Visible = false;
  
}

 

Dynamic Schema Entities

To use Windows Azure SDK for PHP without defining a schema, you can make use of the class Microsoft_WindowsAzure_Storage_DynamicTableEntity.

 

This class inherits class Microsoft_WindowsAzure_Storage_TableEntity like an enforced schema class does, but contains additional logic to make it dynamic and not bound to a schema.

 

The purpose of using this class is to define an entity's properties at run-time. Their Windows Azure table storage type will be determined at run-time:

PHP — Dynamic Schema Entity Assignments at Run-Time
  $entity = new Microsoft_WindowsAzure_Storage_DynamicTableEntity('partition1', '000001');
  $entity->Name = 'Name'; // Will add property "Name" of type "Edm.String"
  $entity->Age  = 25; // Will add property "Age" of type "Edm.Int32"

 

If necessary instead of property type is assigned dynamically, then property type can be enforced using method Microsoft_WindowsAzure_Storage_DynamicTableEntity::setAzurePropertyType():

 

PHP — Enforced Schema Entity Assignments at Run-Time
  $entity = new Microsoft_WindowsAzure_Storage_DynamicTableEntity('partition1', '000001');
  $entity->Name = 'Name'; // Will add property "Name" of type "Edm.String"
  $entity->Age  = 25; // Will add property "Age" of type "Edm.Int32"
 
  $entity->setAzurePropertyType('Age', 'Edm.Int64'); // Changes type of property "Age" to "Edm.Int32"

 


 

Task 9 — SDK — Table Entity Methods

 

Insert a Table Entity

To insert a new entity into an existing table:

  1. Create a new entity instance by constructing a subclass derived from Microsoft_WindowsAzure_Storage_TableEntity
  2. New instance inserted using Microsoft_WindowsAzure_Storage_Table::insertEntity()
PHP — Insert a Table Entity
      $rowKey = 'rowKey_';
      $rowKey = uniqid($rowKey);
      
      $entity = new Table_Entity_Example(self::PARTITION_KEY, $rowKey );
      $entity->Name = $strName;
      $entity->Age = $intAge;
      
      $objEntity = $this->storageClient->insertEntity($tableName, $entity);
      
      if ( !is_null($objEntity) && 
            is_a( $objEntity, 'Microsoft_WindowsAzure_Storage_TableEntity')) {
        // Check the timestamp and etag of the newly inserted entity
        echo 'Timestamp: ' . $objEntity->getTimestamp() . "\n";
        
        $fSuccess = true;
      }

 

Deleting a Table Entity

To delete an entity from a table:

  1. Retrieve entity with partition and row keys using Microsoft_WindowsAzure_Storage_Table::retrieveEntityById()
  2. Microsoft_WindowsAzure_Storage_Table::deleteEntity()
PHP — Delete a Table Entity
      $objEntity = $this->storageClient->retrieveEntityById (
                                              $tableName, 
                                              $partitionKey, 
                                              $rowKey,
                                              'Table_Entity_Example' 
                                              );
      
      if ( !is_null($entity) && 
            is_a( $objEntity, 'Microsoft_WindowsAzure_Storage_TableEntity')) {
 
        $this->storageClient->deleteEntity($tableName, $objEntity);
      }
      
      $fSuccess = true;

 

Task 10 — SDK — Retrieving Table Entities

 

There are three approaches in retrieving entities from a table:

 

Retrieve Table Entities using Filters

Table entity queries using that use filters returns only entities that satisfy the specified filter.

 

The filter string as a query option returns only the elements from the target set of resources that satisfy the expression of the filter. The result of the expression must be a Boolean or nullable Boolean.

 

For more information, see Filter Query Option: $filter (ADO.NET Data Services Framework).

 

Note that no more than 15 discrete comparisons are permitted within a filter string.

 

Supported Comparison Operators

Within a $filter clause, you can use comparison operators to specify the criteria against which to filter the query results.

 

For all property types, the following comparison operators are supported:

 

Comparison Operator URI Expression
Equal eq
GreaterThan gt
GreaterThanOrEqual ge
LessThan lt
LessThanOrEqual le
NotEqual ne

 

Supported Boolean Operators

The following operators are supported for Boolean properties:

Boolean Operator URI Expression
And and
Not not
Or or

 

The following characters must be encoded if they are to be used in a query string:

Constructing Filter Strings

When constructing a filter string, keep these rules in mind:

Sample Entity Query Expressions

Filtering on String Properties

When filtering on string properties, enclose the string constant in single quotes.

 

The following example filters on the PartitionKey and RowKey properties; additional non-key properties could also be added to the query string.

 

PHP — Filtering on String Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "PartitionKey eq '{$partitionKey}' and RowKey eq '{$rowKey}'",
      $entityClass
  );

 

The following example filters on a FirstName and LastName property:

 

PHP — Filtering on String Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "LastName eq 'Smith' and FirstName eq 'John'",
      $entityClass
  );

 

Filtering on Numeric Properties

To filter on an integer or floating-point number, specify the constant value without single quotes.

 

This example returns all entities with an Age property whose value is greater than 30:

 

PHP — Filtering on Numeric Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "Age gt 30",
      $entityClass
  );

 

This example returns all entities with an AmountDue property whose value is less than or equal to 100.25:

 

PHP — Filtering on Numeric Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "AmountDue le 100.25",
      $entityClass
  );

 

Filtering on Boolean Properties

To filter on a Boolean value, specify true or false without single quotes.

 

The following example returns all entities where the IsActive property is set to true:

 

PHP — Filtering on Boolean Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "IsActive eq true",
      $entityClass
  );

 

Filtering on DateTime Properties

To filter on a DateTime value, specify the datetime keyword, followed by the date/time constant in single quotes. The date/time constant must be in combined UTC format, as described in Formatting DateTime Property Values.

 

The following example returns entities where the CustomerSince property is equal to July 10, 2008:

 

PHP — Filtering on DateTime Properties
  $entities = $storageClient->retrieveEntities(
      $tableName,
      "CustomerSince eq datetime'2008-07-10T00:00:00Z'",
      $entityClass
  );

 

Retrieve Table Entities using Fluent Queries

In software engineering, a fluent interface is a way of implementing an object oriented API in a way that aims to provide for more readable code.

 

The SDK for Tables has a basic SQL fluent interface that is provided by:

 

The query is liked together by instances of class Microsoft_WindowsAzure_Storage_TableEntityQuery. For example:

 

PHP — Table Entity Query
SELECT * FROM $tableName
  $this->storageClient->select()
                        ->from($tableName),

 

PHP — Table Entity Query
SELECT * FROM $tableName WHERE PartitionKey = $partitionKey
  $this->storageClient->select()
                        ->from($tableName)
                        ->where('PartitionKey eq ?', $partitionKey),

 

PHP — Table Entity Query
SELECT * FROM $tableName WHERE PartitionKey = $partitionKey AND RowKey = $rowKey
  $this->storageClient->select()
                        ->from($tableName)
                        ->where('PartitionKey eq ?', $partitionKey)
                        ->andWhere('RowKey eq ?', $rowKey),

 

PHP — Table Entity Query
SELECT * FROM $tableName WHERE PartitionKey = $partitionKey AND RowKey = $rowKey
  $this->storageClient->select()
                        ->from ($tableName)
                        ->wherePartitionKey ($partitionKey)
                        ->whereRowKey ($rowKey),

 

Retrieve Table Entity using System Keys

As mentioned earlier, the PartitionKey combined with the RowKey uniquely identifies an entity in a table, because:

 

The SDK provides a method for retrieving a specific entity by providing its unique key pair PartitionKey and RowKey:

 

Retrieve Table Entity Examples

Example – Retrieve All Entities from a Table

The coding samples in this section each do the same thing – Retrieve all the entities from a table.

 

PHP — Retrieve All Entities from Table
  /**
   * Retrieve all Entities from a Table
   * 
   * @param IN string $strTableName
   * @return boolean True upon success
   */
  public function doRetrieveEntitiesAll( $strTableName ) 
  {
    self::_doDisplay( __METHOD__ . "\n" );
    $fSuccess = false;
    
    try {
      $entities = $this->storageClient->retrieveEntities($strTableName);
        
      if (!is_null($entities) && is_array($entities)) {
        foreach ($entities as $entity)
        {
          $aAzureValues = array_splice($entity->getAzureValues(), 1, 3);
          self::_doDisplay( print_r($aAzureValues, true) . "\n" );
        }
        $fSuccess = true;
      }
    } catch ( Microsoft_WindowsAzure_Exception $e ) {
      self::_doDisplay( sprintf("Failed: %s::%d >> %s\n", __METHOD__, __LINE__, $e->getMessage()));
    } catch ( Exception $e ) {
      self::_doDisplay( sprintf("Failed: %s::%d >> %s\n", __METHOD__, __LINE__, $e->getMessage()));
    }
    
    return $fSuccess;
  }

 

PHP — Retrieve All Entities with Table Entity Query
  $entities = $this->storageClient->retrieveEntities(
                        $this->storageClient->select()->from($tableName),
                        'Table_Entity_Example'
                        );

 

Example – Retrieving Entity using System Property Keys

 

The coding samples in this section each do the same thing – Retrieve an entity based upon its assigned system keys: PartitionKey and RowKey.

 

PHP — Getting Entity using retrieveEntityById()
  $entity = $this->storageClient->retrieveEntityById (
                                          $tableName, 
                                          $partitionKey, 
                                          $rowKey,
                                          'Table_Entity_Example' 
                                          );

 

PHP — Table Entity Filtering using System Property Keys
  $entities = $this->storageClient->retrieveEntities (
                        $tableName, 
                        "PartitionKey eq '{$partitionKey}' and RowKey eq '{$rowKey}'",
                        'Table_Entity_Example' 
                        );

 

PHP — Table Entity Query using System Property Keys
  $entities = $this->storageClient->retrieveEntities(
                        $this->storageClient->select()
                                              ->from($tableName)
                                              ->where('PartitionKey eq ?', $partitionKey)
                                              ->andWhere('RowKey eq ?', $rowKey),
                        'Table_Entity_Example'
                        );

 

PHP — Table Entity Query using Special Where clauses for System Property Keys
  $entities = $this->storageClient->retrieveEntities(
        $this->storageClient->select()
                              ->from ($tableName)
                              ->wherePartitionKey ($partitionKey)
                              ->whereRowKey ($rowKey),
        'Table_Entity_Example'
      );

 


 

Summary

 

In this lab, you have learned how to...

  1. Configure Table Service to use Windows Azure Data Storage for both:
    • local Development Fabric
    • remote Windows Azure Cloud
  2. Use Windows Azure Table Storage API through Windows Azure SDK for PHP to create an application that saves and retrieves image data stored as structured non-relational tables.

 

Good job!! You are done with this Lab.