February, 2012

  • Interoperability @ Microsoft

    Open Source OData Tools for MySQL and PHP Developers


    To enable more interoperability scenarios, Microsoft has released today two open source tools that provide support for the Open Data Protocol (OData) for PHP and MySQL developers working on any platform.

    The growing popularity of OData is creating new opportunities for developers working with a wide variety of platforms and languages. An ever increasing number of data sources are being exposed as OData producers, and a variety of OData consumers can be used to query these data sources via OData’s simple REST API.

    In this post, we’ll take a look at the latest releases of two open source tools that help PHP developers implement OData producer support quickly and easily on Windows and Linux platforms:

    • The OData Producer Library for PHP, an open source server library that helps PHP developers expose data sources for querying via OData. (This is essentially a PHP port of certain aspects of the OData functionality found in System.Data.Services.)
    • The OData Connector for MySQL, an open source command-line tool that generates an implementation of the OData Producer Library for PHP from a specified MySQL database.

    These tools are written in platform-agnostic PHP, with no dependencies on .NET.

    OData Producer Library for PHP


    Last September, my colleague Claudio Caldato announced the first release of the Odata Producer Library for PHP, an open-source cross-platform PHP library available on Codeplex. This library has evolved in response to community feedback, and the latest build (Version 1.1) includes performance optimizations, finer-grained control of data query behavior, and comprehensive documentation.

    OData can be used with any data source described by an Entity Data Model (EDM). The structure of relational databases, XML files, spreadsheets, and many other data sources can be mapped to an EDM, and that mapping takes the form of a set of metadata to describe the entities, associations and properties of the data source. The details of EDM are beyond the scope of this blog, but if you’re curious here’s a simple example of how EDM can be used to build a conceptual model of a data source.

    The OData Producer Library for PHP is essentially an open source reference implementation of OData-relevant parts of the .NET framework’s System.Data.Services namespace, allowing developers on non-.NET platforms to more easily build OData providers. To use it, you define your data source through the IDataServiceMetadataProvider (IDSMP) interface, and then you can define an associated implementation of the IDataServiceQueryProvider (IDSQP) interface to retrieve data for OData queries. If your data source contains binary objects, you can also implement the optional IDataServiceStreamProvider interface to handle streaming of blobs such as media files.

    Once you’ve deployed your implementation, the flow of processing an OData client request is as follows:

    1. The OData server receives the submitted request, which includes the URI to the target resource and may also include $filter, $orderby, $expand and $skiptoken clauses to be applied to the target resource.
    2. The OData server parses and validates the headers associated with the request.
    3. The OData server parses the URI to resource, parses the query options to check their syntax, and verifies that the current service configuration allows access to the specified resource.
    4. Once all of the above steps are completed, the OData Producer for PHP library code is ready to process the request via your custom IDataServiceQueryProvider and return the results to the client.

    These processing steps are the same in .NET as they are in the OData Producer Library for PHP, but in the .NET implementation a LINQ query is generated from the parsed request. PHP doesn’t have support for LINQ, so the producer provides hooks which can be used to generate the PHP expression by default from the parsed expression tree. For example, in the case of a MySQL data source, a MySQL query expression would be generated.

    The net result is that PHP developers can offer the same querying functionality on Linux and other platforms as a .NET developer can offer through System.Data.Services. Here are a few other details worth nothing:

    • In C#/.NET, the System.Linq.Expressions namespace contains classes for building expression trees, and the OData Producer Library for PHP has its own classes for this purpose.
    • The IDSQP interface in the OData Producer Library for PHP differs slightly from .NET’s IDSQP interface (due to the lack of support for LINQ in PHP).
    • System.Data.Services uses WCF to host the OData provider service, whereas the OData Producer Library for PHP uses a web server (IIS or Apache) and urlrewrite to host the service.
    • The design of Writer (to serialize the returned query results) is the same for both .NET and PHP, allowing serialization of either .NET objects or PHP objects as Atom/JSON.

    For a deeper look at some of the technical details, check out Anu Chandy’s blog post on the OData Producer Library for PHP or see the OData Producer for PHP documentation available on Codeplex.

    OData Connector for MySQL

    The OData Producer for PHP can be used to expose any type of data source via OData, and one of the most popular data sources for PHP developers is MySQL. A new code generator tool, the open source OData Connector for MySQL, is now available to help PHP developers implement OData producer support for MySQL databases quickly and simply.

    The OData Connector for MySQL generates code to implement the interfaces necessary to create an OData feed for a MySQL database. The syntax for using the connector is simple and straightforward:

    php MySQLConnector.php /db=mysqldb_name /srvc=odata_service_name /u=db_user_name /pw=db_password /h=db_host_name

    figure2The MySQLConnector generates an EDMX file containing metadata that describes the data source, and then prompts the user for whether to continue with code generation or stop to allow manual editing of the metadata before the code generation step.

    EDMX is the Entity Data Model XML format, and an EDMX file contains a conceptual model, a storage model, and the mapping between those models. In order to generate an EDMX from a MySQL database, the OData Connector for MySQL needs to be able to do database schema introspection, and it does this through the Doctrine DBAL (Database Abstraction Layer). You don’t need to understand the details of EDMX in order to use the OData Connector for MySQL, but if you’re curious see the .edmx File Overview article on MSDN.

    If you’re familiar with EDMX and wish to have very fine-grained control of the exposed OData feeds, you can edit the metadata as shown in the diagram, but this step is not necessary. You can also set access rights for specific entities in the DataService::InitializeService method after the code has been generated, as described below.

    If you stopped the process to edit the EDMX, one additional command is needed to complete the generation of code for the interfaces used by the OData Producer Library for PHP:

    php MySQLConnector.php /srvc=odata_service_name

    Note that the generated code will expose all of the tables in the MySQL database as OData feeds. In a typical production scenario, however, you would probably want to fine-tune the interface code to remove entities that aren’t appropriate for OData feeds. The simplest way to do this is to use the DataServiceConfiguration object in the DataService::InitializeService method to set the access rights to NONE for any entities that should not be exposed. For example, you may be creating an OData provider for a CMS, and you don’t want to allow OData queries against the table of users, or tables that are only used for internal purposes within your CMS.

    For more detailed information about working with the OData Connector for MySQL, refer to the user guide available on the project site on Codeplex.

    These tools are open-source (BSD license), so you can download them and start using them immediately at no cost, on Linux, Windows, or any PHP platform. Our team will continue to work to enable more OData scenarios, and we’re always interested in your thoughts. What other tools would you like to see available for working with OData?

  • Interoperability @ Microsoft

    SAG Awards Drupal Website Moves to Windows Azure


    The success of the recent Screen Actors Guild (SAG) Awards ceremony was buoyed by the move of its Drupal-based website hosted on internal Linux servers to one hosted on Windows Azure.

    The SAG Awards site is a highly visible, high-traffic website running on Drupal. Hosting it on Azure provides a scalable, public cloud environment for SAG team. They can tune up or down the compute and storage requirements according to expected website loads, thereby getting a more scalable, manageable and cost-effective solution for running their site.

    SAG also gets the benefits of PaaS – no need to manage the operating system patches, virtual machine images, network topology etc. This is particularly useful for SAG as the site has stable traffic for nine months, but which spikes for the three months from when award nominations open to the night of the event itself.

    The SAG Awards site was previously hosted on internal Linux boxes. In previous years, performance was negatively impacted by site outages and slow performance during peak-usage days, with SAG having to consistently upgrade their hardware to meet demand for those days. That upgraded hardware was then not optimally used during the rest of the year.

    The usage pattern for the SAG Awards site fluctuates, but spikes between November and February when the site is used for SAG award nominations in early November to the actual announcement of nominations in in mid-December. Peak usage is on the night of the awards ceremony where multiple uploads of pictures, news articles, and site visits happen.

    What is even more impressive is that both visits and page views almost doubled on the night of the event. In 2011, some 222,816 people visited the site and 434,743 pages were viewed, while this year there were some 325,303 site visits and 789,310 page views, reflecting the stability and performance of the site on Windows Azure.

    Microsoft started working with the SAG Awards team in May 2011, when their CIO Erin Griffin joined the Interoperability Executive Council (IEC) - founded by Microsoft in 2006 with a goal of identifying the industry’s greatest areas of need and to work together to create solutions - and attended a council meeting.

    In September Mike Story, SAG’s chief architect, attended an IEC work stream meeting and asked for Microsoft’s support in porting the site to Azure. The Business Platform Division’s Customer Experience (CAT) team, the Interoperability group and Windows Azure all started working with SAG in early October and, on December 20, 2011, the site went live on Windows Azure.

    “We moved to Windows Azure after looking at the services it offered,” said Erin Griffin, CIO at SAG. “Understanding the best usage scenario for us took time and effort, but with help from Microsoft, we successfully moved our site to Windows Azure and the biggest traffic day for us went off with flying colors.”

    This is just one real world outcome from the IEC, which has counseled Microsoft on many interoperability topics and introduced a number of real world scenarios for discussion. The IEC, working together with Microsoft, has developed a number of solutions for these scenarios, with this one for the SAG Awards being the latest.

    Curt Peterson, Microsoft’s Principal Group Program Manager, BPD Customer Experience, notes that the success of Sunday’s SAG Awards ceremony underscores how Windows Azure is a scalable, open Cloud platform ready for production use. “We are committed to making it easier for all our customers to use cloud computing on their terms with Windows Azure,” he says.

Page 1 of 1 (2 items)