General

Pure Linux-based oData producers: PHP and MySQL

I’m a big fan of oData, and if you’ve been following me for the past months, you know I’ve been fiddling around with the Ecuadorian Census data and other sources of information, kind of a stats/data freak. So open data was, of course, the natural path.

What I like about oData is that is simple, it’s Web-based, and it’s densely documented so that anyone can access the data served via oData: open data makes no sense if you need a software factory with all developers and DBAs to help you use it. And oData excels in consumer-side support, with plugins for in-memory BI solutions such as Tableau or PowerPivot and for other databases and Web-, Desktop-, Mobile- based applications.

Furthermore, govements can use free cloud services to publish their data (such as the Open Govement Data Initiative) but what abouts projects that need more tweaking and perhaps a deeper in-house integration?

I operate several servers with Debian, and I wanted to serve content via oData. My test scenario is the Ecuadorian Census data on ICT usage, based on about 70K interviews they perform every six months or so. They publish the database as a SAV file, which is an SPSS file. I can open this file with PSPP in either Windows or Linux and then puke data back in CSV format using a simple PSPP program (or, for the pragmatics among you, type the following two commands in the pspp shell):

GET FILE=”/tmp/inec.sav”
SAVE TRANSLATE /OUTFILE=”/tmp/inec.csv” /TYPE=CSV

Then, from the PSPP dictionary, I can see the fields and create a table (or two, or three) on a MySQL database. This is a lengthy process. I type really fast so I just transcribed all the column names from PSPP, but I’m sure there’s an easier way to get the column names automagically. So I create the about 150 columns of the INEC database and then I use LOAD DATA INFILE to get the CSV inside MySQL. Easy process, and I now have the 70K interviews in a database.

Let me introduce you to Microsoft’s (yes) OData Connector for MySQL. So this little guy here uses Doctrine to connect to the MySQL database and produce a set of PHP libraries that can be later converted into oData producers. The package will need PHP 5.3, and the php-xsl package in place. You will also need to download the OData Producer Library for PHP. Both libraries are open source and run on Linux, and won’t depend on .NET stuff (which of course wouldn’t be an issue, but…)

Make sure you download Doctrine ORM (I used 2.2.2) and it’s available (you can symlink) as the Doctrine directory wherever the MySQLConnector.php file is. Then run php MySQLConnector.php and follow the instructions. Note the service name you define here, you will need it later on. Make sure your tables all have primary keys defined, by the way. After it’s done, the library will puke files in ODataConnectorForMySQL/OutputFiles. We will use them later.

Now:

  1. Unpack the OData Producer Library for PHP in a convenient place of your Web server document root
  2. Delete everything in the services/ directory, as it has some samples we won’t use
  3. Move the service.config.xml generated by the MySQL Connector (OutputFiles directory) to the services/ directory
  4. Create a directory under services/ with the name of the service you defined when connecting to MySQL, and move the rest of OutputFiles in there
  5. Depending on your setup, you might need to symlink ODataProducer to library/ODataProducer in your DocumentRoot

A few backslashes made it to a couple of PHP files. I’ve reported the issue in Codeplex, but you will find error messages in error.log about them, if you want to correct them manually.

In terms of Apache configuration, you just need to make sure that all requests to resources ending in .svc get redirected to the index.php file, and that PHP searches in the DocumentRoot and DocumentRoot/library locations. I accomplish this as follows:

                RewriteEngine on
                RewriteRule (.svc.*) index.php
                php_value include_path “/var/www/odata:/var/www/odata/library”

You’re all set. Now Apache will be serving an oData feed with an URI similar to http://server/service.svc, where service is the name of the service you defined when running the MySQL Connector. You can consume the feed with several of oData consumers.

Happy hacking with open data!
Standard

6 thoughts on “Pure Linux-based oData producers: PHP and MySQL

  1. codeadict says:

    Nice post. I’m exploring oData a little but i don’t find any implementation with Python that is my lang of choice, seems to be interesting, i will play a bit with PHP. Nice articles on your blog, regards from a Cuban Hacer in Quito.

  2. José says:

    There’s something at http://code.google.com/p/odata-py/ but definitely <br/>nothing ready-ready. You could use Python’s own capabilities to <br/>consume Web Services as OData is entirely Web-based. But I’ll let you <br/>know if either Microsoft or someone else at the OData community <br/>publishes something.

  3. ryrobes says:

    Struggling a bit getting this all to work (seems I’m not the only one either).I’m still getting "No configuration info found for sample.svc" when trying to use the service, another odd thing – when I created the php libs using MySQLConnector.php on the second step: php MySQLConnector.php /srvc=whatever I get:MetadataProvider class has generated Successfully.Error:Error: tweetsConnectionParams.xml file not found in Output service Folder…!!!Bizarre, since all the files are there and linked (same versions you used as well). Also on Ubuntu.Can you think of anything else you did to get it running that isn’t in this post?Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s