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):
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.
- Unpack the OData Producer Library for PHP in a convenient place of your Web server document root
- Delete everything in the services/ directory, as it has some samples we won’t use
- Move the service.config.xml generated by the MySQL Connector (OutputFiles directory) to the services/ directory
- 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
- 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:
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!