July 8, 2009

YQL: INSERT INTO internet

The Yahoo! Query Language lets you query, filter, and join data across any web data source or service on the web. Using our YQL web service, apps run faster with fewer lines of code and a smaller network footprint. YQL uses a SQL-like language because it is a familiar and intuitive method for developers to access data. YQL treats the entire web as a source of table data, enabling developers to select * from internet.

Today, the YQL team has extended the platform with the "missing" SQL verbs to write and modify data on web services and applications: INSERT, UPDATE and DELETE. This enables YQL's Open Data Tables to insert new Twitter status messages, not just list them; to add new comments to a blog, as well as read them; to store data in a remote database; to INSERT INTO internet.

Open Data Table developers can add support for these verbs into their tables by creating new binding types in their definitions. They can then perform the update/insert/delete on the remote web service by creating an execute element that will run their Javascript to create the right content payload and send it to the remote service. To complement the new YQL verbs, we've extended the capabilities in our server-side Javascript, so y.rest() can now POST, PUT and DELETE.

Example

Here's a simple twitter.status table that enables users to query information about particular tweets, create new tweets, or delete tweets:

<?xml version="1.0" encoding="UTF-8" ?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd" https="true">
  <meta>
    <sampleQuery>insert into {table} (status,username,password) values ("new tweet from YQL", "twitterusernamehere","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>
    <insert itemPath="" produces="XML">
      <urls>
        <url>http://twitter.com/statuses/update.xml</url>
      </urls>
      <inputs>
        <value id="username" type="xs:string" required="true" paramType="variable"/>
        <value 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);
        var content = "status="+status;
        response.object = request.header("Authorization",authheader).post(content).response;
      ]]></execute>
    </insert>
    <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);
        response.object = request.header("Authorization",authheader).del().response;
      ]]></execute>
    </delete>
  </bindings>
</table>

The new important pieces of this simple example are the “insert" and "delete" binding elements. Similar to the select element, these tell YQL which SQL verbs this table supports, and how to go about running the INSERT, UPDATE or DELETE. These bindings support a new input type, "value", that lets YQL know that certain keys can only appear in the INSERT's VALUE clause or the UPDATE's SET clause, like the twitter status.

The execute element in INSERT uses the new y.rest().post(content) method to send the status over to twitter. The execute element in DELETE simply takes the URL that gets created with the tweet ID in and invokes the del() on the request object to send the DELETE HTTP verb to twitter.

Also, since this table needs your twitter credentials to work, we've marked it as requiring HTTPS to keep them safe. Most Open Data Tables that support I/U/D will require some kind of credentials and should be used over https.

Try creating a new tweet from the YQL console, follow this link to run this:

use 'http://www.yqlblog.net/samples/twitter.status.xml';
insert into twitter.status (status,username,password)
    values ("Playing with INSERT, UPDATE and DELETE in YQL",     "twitterusername","twitterpassword")


Neat eh?!

Other examples

If you're a fan of URL shortener services you should try our other sample table, bit.ly, that enables you to shorten a URL.
Give it a go:

use 'http://yqlblog.net/samples/bitly.shorten.xml';
insert into bitly.shorten (longUrl,login,apiKey) values
    ('http://cnn.com','bitlyapidemo','R_0da49e0a9118ff35f52f629d2d71bf07')


Want to try a Yahoo! example? Update your Yahoo profile presence status from the YQL console:

update social.profile.status set status="Playing with the YQL console" where guid=me


Those of you with an Amazon simpleDB account can try out the aws.simpledb Open Data Tables, already in our community repository, by loading them up in the console

If you've developed some Open Data Tables already and the source supports some type of update, you might want to take this opportunity to go back and add the capability in. Take a look at the documentation for more details on the new capabilities. If you just want to INSERT INTO internet, stay tuned for more example tables and demos that we'll be covering in the coming weeks.

Querying with YQL made the web as easy as select * from internet. Now update, delete and insert makes Read/Write APIs just as easy to use.

Jonathan Trevor
YQL Team

Posted at July 8, 2009 8:51 AM | Permalink

Bookmark this on Delicious

Comments

Posted by: Matt C. at July 8, 2009 11:16 AM

Posted by: Jonathan Trevor at July 8, 2009 3:05 PM

Posted by: John S. at July 9, 2009 6:55 AM

Posted by: Jonathan Trevor at July 9, 2009 1:20 PM

Posted by: Greg at July 9, 2009 3:46 PM

Posted by: Dian at July 10, 2009 6:56 PM

Posted by: Varun at October 9, 2009 4:31 AM