Examples of Open Data Tables with JavaScript

Hello World Table

The following Open Data Table allows you to search a fictional table in which "a" is the path and "b" is the term.

This table showcases the following:

use of E4X to form the response

Run this example in the YQL console.

Yahoo! Messenger Status

The following Open Data Table allows you to see the status of a Yahoo! Messenger user.

The table showcases the following:

use of JavaScript to check Yahoo! Messenger status
use of E4X to form the response

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
  <meta>
    <sampleQuery>select * from {table} where u='tom_croucher_y';</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="" produces="XML">
      <urls>
        <url>http://opi.yahoo.com/online?m=t</url>
      </urls>
      <inputs>
        <key id='u' type='xs:string' paramType='query' required="true" />
      </inputs>
      <execute><![CDATA[
          
          //get plain text back from OPI endpoint
          rawStatus = request.get().response;

//check if users is not offline
          if (!rawStatus.match("NOT ONLINE")) {
              status = "online";
          } else {
              status = "offline";
          }

//return results as XML using e4x
          response.object = 
          <messengerstatus>
             <yahoo_id>{u}</yahoo_id>
             <status>{status}</status>
          </messengerstatus>;
      ]]></execute>
    </select> 
  </bindings>
</table>

Run this example in the YQL console.

OAuth Signed Request to Netflix

The following Open Data Table allows you to make a two-legged OAuth signed request to Netflix. It performs a search on the Netflix catalog for specific titles.

This table showcases the following:

access an authenticating API that requires signatures
use an external JavaScript library

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
    <meta>
        <author>Paul Donnelly</author>
        <documentationURL>http://developer.netflix.com/docs/REST_API_Reference#0_52696</documentationURL>
    </meta>
      <bindings>
        <select itemPath="" produces="XML" >
          <urls>
            <url env="all">http://api.netflix.com/catalog/titles/</url>
          </urls>
         <paging model="offset">
           <start id="start_index" default="0" />
           <pagesize id="max_results" max="100" />
           <total default="10" />
         </paging>
          <inputs>
                <key id="term" type="xs:string" paramType="query" required="true" />
                <key id="ck" type="xs:string" paramType="variable" required="true" />
                <key id="cks" type="xs:string" paramType="variable" required="true" />
          </inputs>
          <execute><![CDATA[
// Include the OAuth libraries from oauth.net
y.include("http://oauth.googlecode.com/svn/code/javascript/oauth.js");
y.include("http://oauth.googlecode.com/svn/code/javascript/sha1.js");

// Collect all the parameters
var encodedurl = request.url;
var accessor = { consumerSecret: cks, tokenSecret: ""};
var message = { action: encodedurl, method: "GET", parameters: [["oauth_consumer_key",ck],["oauth_version","1.0"]]};
OAuth.setTimestampAndNonce(message);

// Sign the request
OAuth.SignatureMethod.sign(message, accessor);

try {
   // get the content from service along with the OAuth header, and return the result back out
   response.object = request.contentType('application/xml').header("Authorization", OAuth.getAuthorizationHeader("netflix.com", message.parameters)).get().response;
} catch(err) {
   response.object = {'result':'failure', 'error': err};
}
          ]]></execute>
        </select>
      </bindings>
</table>

Run this example in the YQL console.

Request for a Flickr frob

The following Open Data Table example returns the frob, which is analogous to the request token in OAuth.

This table showcases the following:

access an authenticating API that requires signatures
use an external JavaScript library
sign a request, then send the request using y.rest
require the HTTPS protocol (since private keys are being transmitted)

<?xml version="1.0" encoding="UTF-8" ?>
// https="true" ensures that only HTTPs connections are allowed
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
  <meta>
    <sampleQuery> select * from {table}</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="rsp" produces="XML">
      <urls>
        <url>http://api.flickr.com/services/rest/</url>
      </urls>
      <inputs>
        <key id='method' type='xs:string' paramType='variable' const="true" default="flickr.auth.getFrob" />
        <key id='api_key' type='xs:string' paramType='variable' required="true" />
        <key id='secret' type='xs:string' paramType='variable' required="true" />
      </inputs>
      <execute><![CDATA[
// Include the flickr signing library
y.include("http://www.yqlblog.net/samples/flickr.js");
// GET the flickr result using a signed url
var fs = new flickrSigner(api_key,secret);
response.object = y.rest(fs.createUrl({method:method, format:""})).get().response();
      ]]></execute>
    </select>
  </bindings>
</table>

Run this example in the YQL console.

Celebrity Birthday Search using IMDB

The following Open Data Table retrieves information about celebrities whose birthday is today by default, or optionally, on a specific date.

This table showcases the following:

Creating an API/table from HTML data
Mixing and matching Web service requests with HTML scraping
Using E4X for creating new objects, filtering, and searching
Parallel dispatching of query/REST calls
Handling page parameters

<?xml version="1.0" encoding="UTF-8" ?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
  <meta>
    <sampleQuery> select * from {table}</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="birthdays.person" produces="XML">
      <urls>
        <url></url>
      </urls>
      <paging model="offset">
        <pagesize id="count" max="300" />
        <total default="10" />
      </paging>
      <inputs>
        <key id='date' type='xs:string' paramType='variable' />
      </inputs>
      <execute><![CDATA[

//object to query imdb to extract bio info for a person
var celebInfo = function(name,url) {
   this.url = url;
   this.name =  name;
   var querystring = "select * from html where url = '"+url+"' and xpath=\"//div[@id='tn15']\"";
   this.query = y.query(querystring);
}

//actually extract the info and return an xml object
celebInfo.prototype.getData=function() {
   default xml namespace ='';
    var d = this.query.results;
   var img = d..div.(@["id"]=="tn15lhs").div.a.img;
    var content = d..div.(@['id'] =="tn15content");
   var bio = "";
   //this is pretty hacky
   for each (var node in content.p) {
      if (node.text().toString().trim().length>100) {
         bio = node.*;
          break;
      }
   }
   var anchors = content.a;
   var bornInYear = null;
   var bornWhere = null;
   var diedInYear = null;
   var onThisDay = [];
   //TODO see if there is a wildcard way of pulling these out using e4x/xpath
   for each (var a in anchors) {
      var href = a.@['href'].toString();
      if (href.indexOf("/BornInYear")==0) {
         bornInYear = a.toString().trim();
         continue;
      }
      if (href.indexOf("/DiedInYear")==0) {
         diedInYear = a.toString().trim();
         continue;
      }
      if (href.indexOf("/BornWhere")==0) {
         bornWhere = a.toString().trim();
         continue;
      }
      if (href.indexOf("/OnThisDay")==0) {
         onThisDay.push(a.text().toString().trim());
         continue;
      }
   }
   var bornDayMonth=null;
   var diedDayMonth=null;
   if (onThisDay.length>0) {
      bornDayMonth = onThisDay[0].replace(/^\s*(\d{1,2})[\s]+(\w+)\s*/,'$1 $2'); //tidy up whitespace around text
      if (diedInYear && onThisDay.length>1) {
         diedDayMonth= onThisDay[1].replace(/^\s*(\d{1,2})[\s]+(\w+)\s*/,'$1 $2'); //tidy up whitespace around text
      }
   }
   var url = this.url;
   var name = this.name;
   var bornTime = null;
   if (bornDayMonth) {
      var daymonth = bornDayMonth.split(" ");
      bornTime=new Date(bornInYear,Date.getMonthFromString(daymonth[1]),parseInt(daymonth[0])).getTime()/1000;
   }
   var diedTime = null;
   if (diedDayMonth) {
      var daymonth = diedDayMonth.split(" ");
      diedTime=new Date(diedInYear,Date.getMonthFromString(daymonth[1]),parseInt(daymonth[0])).getTime()/1000;
   }
   var person = <person url={url}><name>{name}</name>{img}<born utime={bornTime}>{bornDayMonth} {bornInYear}</born></person>;
   if (diedTime) person.person+=<died utime={diedTime}>{diedDayMonth} {diedInYear}</died>;
   if (bio) person.person+=<bio>{bio}</bio>;
   return person;
}

//general useful routines
String.prototype.trim =function() {
   return this.replace(/^[\s]*/,'').replace(/[\s]*$/,'');
}
Date.getMonthFromString = function(month) {
   return {'January':0, 'February':1, 'March':2, 'April':3, 'May':4, 'June':5, 'July':6, 'August':7, 'September':8, 'October':9, 'November':10, 'December':11}[month];
}
Date.prototype.getMonthName = function() {
   return ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'][this.getMonth()];
}

//the main object that uses boss to get the list (also gets peoples "death" days too)
celebSearch = function(when,start,count) {
   //search yahoo/boss using the current day and month only on bio pages on imdb
   var bornDayMonth = when.getDate()+" "+when.getMonthName();
   var ud = Math.round(when.getTime()/1000);
   var search = 'site:www.imdb.com "Date of birth" "'+bornDayMonth+'" title:biography'
   var query = "select * from search.web("+start+","+count+") where query='"+search+"'";
   var celebs = y.query(query).results;

//go through each result and start to get the persons name and their imdb info page out
   var results = [];
   default xml namespace ='http://www.inktomi.com/'; //make sure our e4x is in the right namespace. IMPORTANT
   for each (var celeb in celebs.result) {
      //discard any hits on the date of death that also match in our yahoo search
      //(this is going to hurt our paging)
      if (celeb["abstract"].toString().indexOf("<b>Date of Birth</b>. <b>"+bornDayMonth)<0) continue;
      var j = celeb.title.toString().indexOf("-");   //use text up to "dash" from title for name
      var name = celeb.title.toString().substring(0,j).trim();
      //start parsing these entries by pulling from imdb directly
      results.push(new celebInfo(name,celeb.url));
   }

//loop through each imdb fetch result, and create the result object
   default xml namespace = '';
   var data = <birthdays utime={ud} date={when} />;
   for each (var celeb in results) {
      data.birthdays+=celeb.getData();
   }
   return data;
}

//run it for today if no date was provided
var when = new Date();
if (date && date.length>0) {
   when = new Date(date); //TODO needs a well formed date including year
}
response.object = new celebSearch(when,0,count);

]]></execute>
    </select>
  </bindings>
</table>

Run this example in the YQL console.

Shared Yahoo! Applications

The following Open Data Table provides a list of Yahoo! Applications that you and your friends have installed, indicating whether each app is installed exclusively by you, your friends, or both.

This table showcases the following:

complex E4X usage, including namespaces, filtering, searching, and creation
authenticated calls to Yahoo! Social APIs using y.query
setting a security level to user to force authenticated calls only
optional variable that changes the function (searches on a specific friend)
handling page parameters

<?xml version="1.0" encoding="UTF-8" ?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" securityLevel="user">
  <meta>
    <sampleQuery> select * from {table}</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="root.install.app" produces="XML">
      <urls>
        <url></url>
      </urls>
      <inputs>
        <key id='friendguid' type='xs:string' paramType='variable' />
      </inputs>
      <execute><![CDATA[
      function createInstallElement(update,type) {
         var bits = update.itemurl.toString().split("/");
         var appid = bits[bits.length-2].substring(1);//get the appid from the install url
         var title = update.title.toString();
         default xml namespace = '';
         var el =  <app who={type} id={appid}>{title}</app>;
         default xml namespace = 'http:://social.yahooapis.com/v1/updates/schema.rng';
         return el;
      }

default xml namespace = '';
      var root = <install/>;

//get my friends installs from updates
      var friendapp_installs = null;
      if (friendguid) {
         //only do deltas to this friend
         friendapp_installs = y.query('select title, itemtxt, itemurl from social.updates(1000) where guid=@guid and type="appInstall" | unique(field="itemtxt")',{guid:friendguid});
      } else {
         //all friends
         friendapp_installs = y.query('select title, itemtxt, itemurl from social.updates(1000) where guid in (select guid from social.connections(0) where owner_guid=me) and type="appInstall" | unique(field="itemtxt")');
      }
      //get my installs from updates
      var myapp_installs = y.query('select title, itemtxt, itemurl from social.updates(1000) where guid=me and type="appInstall" | unique(field="itemtxt")');
      //we're going to keep a collection for each variant of the diff between my installs and my friend(s)
      var myapp_installs = myapp_installs.results;
      var friendapp_installs = friendapp_installs.results;
      default xml namespace = 'http:://social.yahooapis.com/v1/updates/schema.rng';
      for each (var myupdate in myapp_installs.update) {
         y.log("myupdate "+myupdate.localName());
         //use e4x to search for matching node in friendapp with the same itemtxt (appid)
         var matching = friendapp_installs.update.(itemtxt==myupdate.itemtxt.toString());
         if (matching.length()>0) {
            //found, we both have it
            root.install+=createInstallElement(myupdate,"shared");
            //y.log("Found "+myupdate.title+" in both");
            myupdate.@matched = true;
            matching.@matched = true;
         } else {
            //not in my friends apps, so add it to me only list
            //y.log("Found "+myupdate.title+" in mine only");
            root.install+=createInstallElement(myupdate,"me");
            myupdate.@matched = true;
         }
      }
      //anything left in the friends app list that doesnt have a "match" attribute is not installed by me
      for each (var friendupdate in friendapp_installs.update.(@matched!=true)) {
         //y.log("Found "+friendupdate.title+" in my friends only");
         root.install+=createInstallElement(friendupdate,"friend");
      }
      //return the three sets of results
      default xml namespace = '';
      response.object = <root>{root}</root>;
      ]]></execute>
    </select>
  </bindings>
</table>

Run this example in the YQL console.

CSS Selector for HTML

The following Open Data Table allows you to filter HTML using CSS selectors.

This table showcases the following:

importing external JavaScript utility functions
calling a YQL query within execute

Run this example in the YQL console.

Twitter Updates (Create and Delete)

The following Open Data Table allows you create or delete a status on Twitter.

This table showcases the following:

importing external JavaScript utility functions
calling a YQL query within execute
allowing UPDATE and DELETE statements
peforming HTTP POST and DELETE with JavaScript methods

<?xml version="1.0" encoding="UTF-8" ?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
  <meta>
    <sampleQuery>update {table} set status="new tweet" where username="twitterusernamehere" and password="twitterpasswordhere"</sampleQuery>
    <sampleQuery>select * from {table} where id="2108869549" and username="twitterusernamehere" and password="twitterpasswordhere"</sampleQuery>
  </meta>
  <bindings>
    <select itemPath="" produces="XML">
      <urls>
        <url>http://twitter.com/statuses/show/{id}.xml</url>
      </urls>
      <inputs>
        <key id='username' type='xs:string' required="false" paramType='variable' />
        <key id='password' type='xs:string' required="false" paramType='variable' />
        <key id='id' type='xs:integer' required="true" paramType='path' />
      </inputs>
      <execute><![CDATA[
      var r = null;
      if (username && password) {
         y.include("http://yqlblog.net/samples/base64.js");
         var authheader = "Basic "+Base64.encode(username+":"+password);
          r = request.header("Authorization",authheader).get().response;
      } else {
         r = request.get().response;
      }
      response.object = r;
      ]]></execute>
    </select>
    <update itemPath="" produces="XML">
      <urls>
        <url>http://twitter.com/statuses/update.xml</url>
      </urls>
      <inputs>
        <key id='username' type='xs:string' required="true" paramType='variable' />
        <key id='password' type='xs:string' required="true" paramType='variable' />
        <value id='status' type='xs:string' required="true" paramType='variable' />
      </inputs>
      <execute><![CDATA[
      y.include("http://yqlblog.net/samples/base64.js");
      var authheader = "Basic "+Base64.encode(username+":"+password);
      y.log(authheader);
      var content = "status="+status;
      response.object = request.header("Authorization",authheader).post(content).response;
      ]]></execute>
    </update> 
    <delete itemPath="" produces="XML">
      <urls>
        <url>http://twitter.com/statuses/destroy/{id}.xml</url>
      </urls>
      <inputs>
        <key id='username' type='xs:string' required="true" paramType='variable' />
        <key id='password' type='xs:string' required="true" paramType='variable' />
        <key id='id' type='xs:string' required="true" paramType="path" />
      </inputs>
      <execute><![CDATA[
      y.include("http://yqlblog.net/samples/base64.js");
      var authheader = "Basic "+Base64.encode(username+":"+password);
      y.log(authheader);
      response.object = request.header("Authorization",authheader).del().response;
      ]]></execute>
    </delete> 
  </bindings>
</table>

Run this example in the YQL console.