Dabbling with YQL – Getting more from DabbleDb

I’m a big fan of DabbleDb. It’s a slick product and simple enough that anyone should be able to dive in there and start building an online database. For those of you who are interested DabbleDb supports both free and paid plans for their service, with the only conditions on a free plan is that the data is made publicly available on a Creative Commons licence.

Some integration limitations of DabbleDb
For all I enjoy working with DabbleDb, there are some limitations when it comes to using that data in applications. At one stage, DabbleDb offered some integration APIs that could be used on the server-side but they have since been retired in favour of a javascript library. The javascript library is nice, but limited. For instance, from my personal research I couldn’t find a way to bring back a subset of results using some kind of query syntax. To do this I would have to iterate over the records returned from the javascript and then programmatically determine whether or not to include the row. That’s a little frustrating.

YQL to the rescue
Luckily, Yahoo have released a developer API called YQL and this can be used to do some of the heavy lifting for you and make it simpler to integrate DabbleDb with your application code. Additionally it will even cache the results for you which will reduce round-trips back to DabbleDb.

To illustrate, lets explore a specific example. Back in 2007, O’Reilly’s emerging technology conference used DabbleDb to store the conference schedule. If I wanted to take that data and create a small application that used that data, I could very simply take either the RSS or JSON data feeds that DabbleDb exports and do some cool things with that. Or alternatively I could use the DabbleDb javascript API. I would probably recommend either the JSON or the RSS as it gives you more flexibility down the track.

Well that’s great, but what if in my app I wanted to show all of the sessions that were going to be held in a particular room at the conference. As mentioned before, I could probably use some logic to find those myself programmatically, or I guess O’Reillys could make some changes and expose that information through DabbleDb. Alternatively I could use YQL to return my a subset of the data.

Firstly, fire up the YQL developer console. You should see something similar to what is displayed below:

YQL Console

The top left area is where you enter your query. To select the data from the O’Reilly’s events DabbleDb, I would use the following query:

SELECT * 
FROM rss 
WHERE url='http://etech.dabbledb.com/publish/etech2007/38590f89-8fe6-461b-b76e-da333c62fcaa/events.rss'

Executing the above would return an YQL resultset in XML format (or JSON if specified). The general format of an xml resultset is displayed below:

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="48" yahoo:created="2009-05-28T10:28:57Z" yahoo:lang="en-US" yahoo:updated="2009-05-28T10:28:57Z" yahoo:uri="http://query.yahooapis.com/v1/yql?q=select+*+%0Afrom+rss+%0Awhere+url%3D%27http%3A%2F%2Fetech.dabbledb.com%2Fpublish%2Fetech2007%2F38590f89-8fe6-461b-b76e-da333c62fcaa%2Fevents.rss%27%0Aand+room+like+%27Douglas%25%27">
    <diagnostics>
        <publiclyCallable>true</publiclyCallable>
        <url execution-time="232"><![CDATA[http://etech.dabbledb.com/publish/etech2007/38590f89-8fe6-461b-b76e-da333c62fcaa/events.rss]]></url>
        <user-time>242</user-time>
        <service-time>232</service-time>
        <build-version>1678</build-version>
    </diagnostics>
    <results>
        <item>
        ...
        </item>
    </results>
</query>

Filtering Results
YQL features similar to SQL (have to admit I haven’t tried joining two datasets together yet), so you can take this dataset and apply some logic to it to only return results that match a certain criteria.

In our case, we might only want sessions at the conference that are going to be held in any of the Douglas rooms. So we would use the YQL statement below:

SELECT * 
FROM rss 
WHERE url='http://etech.dabbledb.com/publish/etech2007/38590f89-8fe6-461b-b76e-da333c62fcaa/events.rss'
AND room LIKE 'Douglas%'

Works a treat. Once you have designed up your query in the YQL console, you can then easily copy and paste the RESTful link to your query so you can use it in code.

Leave a Reply