Ruby utility for YQL storage

motivation:

I want to be able to interact w/ YQL storage as easily as I can w/ SQLite on my own machine. Ideally, I’d just like to be able to say storage.use(‘table’).set(‘foo’, ‘bar’) and forget about it.

overview:

This class is based on the SQLite utility class, YQL utility function, and simple key/val layer for YQL storage I recently posted. The methods available are use(), set(), and get(). The use() method accepts the select, update, and execute addresses of a YQL storage record. Calling get() or set() fires off a request to read or write, respectively, a key using the YQL key/val table.

requirements/environment:

An “installation” (it’s just a couple static files on your server) of the YQL key/val table mentioned above
All other requirements are the same as for the SQLite class & YQL fn mentioned above

code:

class YqlStorage
  def use(settings={})
    @settings = settings
    return self
  end
  def get(key)
    
    response = yql( %{
      use 'http://{your domain}/kv.xml' as kv;
      select * from kv where key = '%s' and select = '%s'
    } % [ key, @settings[:select] ] )
    
    if response['error']
      raise 'error: %s ' % response['error']['description']
    elsif !response['query']['results']
      return nil
    end
    
    return response['query']['results']['result']
  end
  
  def set(key, val)

    response = yql( %{
      insert into kv (key, val) values ('%s', '%s')
    } % [ key, val ], { 'env' => 'http://{your domain}/kv.env' } )
    
    if response['error']
      raise 'YQL error: %s ' % response['error']['description']
    end
    
    return response
  end
end

usage:

Save the code below into a file
Edit the file to change all occurrences of ‘{your domain}’ to your domain
Use (ha!) the YQL storage addresses defined in the key/val table setup for the use() settings

here’s some example code

store = YqlStorage.new.use( {
  # get these from YQL: http://developer.yahoo.com/yql/console/#h=desc%20yql.storage.admin
  :execute => 'store://h5Y4iRockdwzZdEHvGbBkCe',
  :select => 'store://deGTN05aNePaper04EOL30W',
  :update => 'store://qG4Scissors8917SHDjv88Wb'
} )
store.set( 'foo', 'bar' )
p store.get( 'foo' )

Please let me know if you’ve got any suggestions/questions.

And now, to lighten the mood, here’s a picture of a squirrel yawning:

Photo credit: _temaki_

Posted on February 21, 2010November 17, 2020 by Erik 1

initial attempt at simple key/val layer on YQL storage

motivation:

YQL storage is very nice. It’s globally distributed, performant, and free(!), but if you want to say “get me value where key = ‘foo'”, you need to apply a layer to map our keys to YQL’s keys. Here’s my first attempt at this.

overview:

A single YQL storage record is used to store a JavaScript hash, JSON encoded. When a value is requested, the table extracts the JSON from storage, decodes it, and applies the key passed w/ the request (we’ll let JS manage the mapping of keys to values ;). If the request is to modify or delete the value, the updated hash is re-encoded to JSON and saved back to storage.

requirements/environement:

Familiarity w/ YQL Open Tables
A server w/ valid domain to host the code below
A copy of Douglas Crockford’s json2 javascript JSON library on your server
YQL build 4265, as defined in the diagnostics section of the YQL output (“<build-version>4265</build-version>”)
A YQL storage record. You can create one easily in the YQL console. Note the select, update, and execute addresses for usage below

usage:

Save the code below to a file on your server, eg kv.xml
Edit the file to change all occurrences of ‘{your domain}’ to your domain
Create a file called kv.env and define your table url and YQL storage addresses into it, as described in the YQL documentation
For select queries, just pass the select address in the request. For all other queries, set env={your domain}/kv.env in the request URL

code:

<?xml version="1.0"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
    <meta>
        <author>Erik Eldridge</author>
        <documentationURL></documentationURL>
        <sampleQuery>use 'http://{your domain}/kv.xml' as kv; select * from kv where key='foo' and select='store://{select store id}'</sampleQuery>
    </meta>
    <bindings>
        <select itemPath="" produces="XML">
            <urls><url></url></urls>
            <inputs>
                <key id="key" type="xs:string" paramType="variable" required="true"/>
                <key id="select" type="xs:string" paramType="variable" required="true"/>
            </inputs>
            <execute>                
                // http://www.JSON.org/json2.js w/ alert removed
                y.include('http://{your domain}/json2.js');
                
                // supplant fn (credit: http://www.crockford.com/javascript/remedial.html)
                if(!String.prototype.supplant){String.prototype.supplant=function(o){return this.replace(/{([^{}]*)}/g,function(a,b){var r=o[b];return typeof r==='string'||typeof r==='number'?r:a;});};}
                
                // response
                response.object = function () {
                    var queries = [],
                        results = [];
                
                    queries[0] = "select * from yql.storage where name = '{select}'"
                        .supplant( { 'select' : select } );
                    results[0] = y.xmlToJson( y.query(queries[0]).results );
                    
                    if ( results[0].results.result.value ) {
                        return results[0].results.result.value[key];
                    }
                }();
            </execute>
        </select>
        <insert>
            <!-- sample query: use 'http://{your domain}/kv.xml' as kv; insert into kv (key, val) values ('foo', 'bar')
            <!-- note: use env file to define table url, select, & update -->
            <urls><url></url></urls>
            <inputs>
                <key id="select" type="xs:string" paramType="variable" required="true"/>
                <key id="update" type="xs:string" paramType="variable" required="true"/>
                <value id="key" type="xs:string" paramType="variable" required="true"/>
                <value id="val" type="xs:string" paramType="variable" required="true"/>
            </inputs>
            <execute>
                // http://www.JSON.org/json2.js w/ alert removed
                y.include('http://{your domain}/json2.js');
                
                // supplant fn (credit: http://www.crockford.com/javascript/remedial.html)
                if(!String.prototype.supplant){String.prototype.supplant=function(o){return this.replace(/{([^{}]*)}/g,function(a,b){var r=o[b];return typeof r==='string'||typeof r==='number'?r:a;});};}
                
                // response
                response.object = function () {
                    var queries = [],
                        results = [];
                    
                    queries[0] = "select * from yql.storage where name = '{select}'"
                        .supplant( { 'select' : select } );
                    results[0] = y.xmlToJson( y.query(queries[0]).results );
                    
                    results[0].results.result.value[key] = val;
                    
                    queries[1] = "update yql.storage set value = '{value}' where name = '{update}'"
                        .supplant( { 
                            'value' : JSON.stringify( results[0].results.result.value ), 
                            'update' : update
                        } );
                    results[1] = y.xmlToJson( y.query( queries[1] ).results );
                    
                    return results[1].results
                }();
            </execute>
        </insert>
        <update>
            <!-- note: use env file to define table url, select, & update -->
            <urls><url></url></urls>
            <inputs>
                <key id="select" type="xs:string" paramType="variable" required="true"/>
                <key id="update" type="xs:string" paramType="variable" required="true"/>
                <key id="key" type="xs:string" paramType="variable" required="true"/>
                <value id="val" type="xs:string" paramType="variable" required="true"/>
            </inputs>
            <execute>
                // http://www.JSON.org/json2.js w/ alert removed
                y.include('http://{your domain}/json2.js');
                
                // supplant fn (credit: http://www.crockford.com/javascript/remedial.html)
                if(!String.prototype.supplant){String.prototype.supplant=function(o){return this.replace(/{([^{}]*)}/g,function(a,b){var r=o[b];return typeof r==='string'||typeof r==='number'?r:a;});};}
                
                // response
                response.object = function () {
                    var queries = [],
                        results = [];
                    
                    queries[0] = "select * from yql.storage where name = '{select}'"
                        .supplant( { 'select' : select } );
                    results[0] = y.xmlToJson( y.query(queries[0]).results );
                    
                    if ( !results[0].results.result.value || !results[0].results.result.value[key] ) {
                        return {
                            error : 'key not found'
                        }
                    }
                    
                    results[0].results.result.value[key] = val;
                    
                    queries[1] = "update yql.storage set value='{value}' where name='{update}'"
                        .supplant( { 
                            'value' : JSON.stringify( results[0].results.result.value ), 
                            'update' : update
                        } );
                    results[1] = y.xmlToJson( y.query( queries[1] ).results );
                    
                    return results[1].results
                }();
            </execute>
        </update>
        <delete>
            <!-- sample query: use 'http://{your domain}/kv.xml' as kv; delete from kv where key='foo' -->
            <!-- note: use env file to define table url, select, & update -->
            <urls><url></url></urls>
            <inputs>
                <key id="select" type="xs:string" paramType="variable" required="true"/>
                <key id="update" type="xs:string" paramType="variable" required="true"/>
                <key id="key" type="xs:string" paramType="variable" required="true"/>
            </inputs>
            <execute>
                // http://www.JSON.org/json2.js w/ alert removed
                y.include('http://{your domain}/json2.js');
                
                // supplant fn (credit: http://www.crockford.com/javascript/remedial.html)
                if(!String.prototype.supplant){String.prototype.supplant=function(o){return this.replace(/{([^{}]*)}/g,function(a,b){var r=o[b];return typeof r==='string'||typeof r==='number'?r:a;});};}
                
                // response
                response.object = function () {
                    var queries = [],
                        results = [];
                    
                    queries[0] = "select * from yql.storage where name = '{select}'"
                        .supplant( { 'select' : select } );
                    results[0] = y.xmlToJson( y.query(queries[0]).results );
                    
                    if ( !results[0].results.result.value || !results[0].results.result.value[key] ) {
                        return {
                            error : 'key not found'
                        }
                    }
                    
                    delete results[0].results.result.value[key];
                    
                    queries[1] = "update yql.storage set value='{value}' where name='{update}'"
                        .supplant( { 
                            'value' : JSON.stringify( results[0].results.result.value ), 
                            'update' : update
                        } );
                    results[1] = y.xmlToJson( y.query( queries[1] ).results );
                    
                    return results[1].results
                }();
            </execute>
        </delete>
    </bindings>
</table>

Please post back w/ suggestions/questions. Here’s a duck for good luck:

Photo credit: foxypar4

Posted on February 21, 2010November 17, 2020 by Erik 1

Ruby YQL utility function example

I can’t tell whether it’s YQL, Ruby, or my lil’ YQL utility function, but I’m having fun. Here’s an example of usage:

json = yql(%{
  use 'http://www.datatables.org/github/github.repo.xml' as github;
  select * from github where id = 'yql' and repo = 'yql-tables'
})

Dig the multiline string syntax (inspiration: benschwarz’s Smoke gem). YQL allows POST requests for select statements (to work around URL-length limits), so I can continue to use POST for everything 🙂

Posted on February 18, 2010November 17, 2020 by Erik

1st attempt at a Ruby YQL utility function

Motivation:
I use YQL a lot and I find myself writing query = “select * from …”, passing the query to the YQL webservice, and then JSON-parsing the response, repeat … I searched (briefly) for a Ruby gem for YQL, but couldn’t find one, so I made (a very basic) function to perform the actions listed above.

Usage:
Drop the code below in a file and run it

Code:

require 'net/http'
require 'rubygems'
require 'json'

def yql(query)
  uri = "http://query.yahooapis.com/v1/public/yql"

  # everything's requested via POST, which is all I needed when I wrote this
  # likewise, everything coming back is json encoded
  response = Net::HTTP.post_form( URI.parse( uri ), {
    'q' => query,
    'format' => 'json'
  } )

  json = JSON.parse( response.body )
  return json
end

Posted on February 18, 2010November 17, 2020 by Erik 2

WebDAV pseudo-PUT request code w/ YQL

My previous post presented the YQL code required to handle a WebDAV GET request for a “file” in yql storage. To update the file, we need an additional table.

Prerequisites:

A sherpa record w/ this value in it: {“file1″:”content 1″, “file2″, “content 2″}. The keys and values w/in the JSON can be anything. If you haven’t worked w/ YQL storage before, check out the documentation.
The code below edited to use your storage record’s select address

Flow:

The WebDAV client issues a PUT/POST request with “path” set to the key of the value to change, and “contents” set to the updated value.
The YQL table will extract the stored record, decode it, update the value, encode it, and store it back

Code:

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
    <meta>
        <author>Erik Eldridge</author>
        <description>
        </description>
        <sampleQuery></sampleQuery>
    </meta>
    <bindings>
        <insert produces="XML">
            <inputs>
                <value id="path" type="xs:string" paramType="variable"/>
                <value id="contents" type="xs:string" paramType="variable"/>

            </inputs>
            <execute><![CDATA[
                var execute = 'store://{your store val}',
                    select = 'store://{your store val}',
                    update = 'store://{your store val}';
                
                // http://www.json.org/json2.js
                y.include('http://{your domain}/json2.js');
                    
                //credit: http://javascript.crockford.com/remedial.html
                if (typeof String.prototype.supplant !== 'function') {
                    String.prototype.supplant = function (o) { 
                        return this.replace(/{([^{}]*)}/g, 
                            function (a, b) {  
                                var r = o[b];
                                return typeof r === 'string' ? r : a; 
                            }); 
                    };
                }
                
                response.object = function () {

                    //put queries and results in arrays so we can reuse the var w/o overwriting values
                    var queries = [],
                        results = [];

                    queries[0] = 'select * from yql.storage where name="{select}"'.supplant({'select':select}),
                    results[0] = y.xmlToJson( y.query( queries[0] ).results );
                    
                    if (results[0].results.result.value[path]) {
                        
                        results[0].results.result.value[path] = contents;
                        
                        queries[1] = "update yql.storage set value='{json}' where name='{update}'"
                            .supplant({
                                'json' : JSON.stringify(results[0].results.result.value),
                                'update' : update
                            });
                        results[1] = y.xmlToJson( y.query( queries[1] ).results );

                        return {
                           "headers" : {
                               "HTTP/1.1 status" : "204",
                               "Date" : new Date().getTime(),
                               "Location" : "/webdav/" + path,
                               "Content-Length" : contents.length,
                               "Connection" : "close",
                               "Content-Type" : "text/plain; charset=UTF-8"
                           }
                        }
                    }

                    results[0].results.result.value[path] = contents;

                    queries[1] = "update yql.storage set value='{json}' where name='{update}'"
                        .supplant({
                            'json' : JSON.stringify(results[0].results.result.value),
                            'update' : update
                        });
                    results[1] = y.xmlToJson( y.query( queries[1] ).results );

                    return {
                        headers : {
                            "HTTP/1.1 status" : "201",
                            "Date" : new Date().getTime(),
                            "Location" : '/webdav/' + path,
                            "Content-Length" : contents.length,
                            "Connection" : "close",
                            "Content-Type" : "text/plain; charset=UTF-8"
                        }
                    };
                }();
            ]]></execute>
        </insert>
    </bindings>
</table>

Notes:

We could use some ruby like this in a rack app to intermediate between YQL and the WebDAV client:

        request = Rack::Request.new(env)
        
        contents = '';
        while part = request.body.read(8192)
          contents += part
        end
        
        query = "use '{the uri for the YQL table file using the code above}/put.xml' as table;"+
          "insert into table (path, contents) values ('#{file}', '#{contents}')"
        host = 'http://query.yahooapis.com'
        path = '/v1/public/yql'
        
        response = Net::HTTP.post_form( URI.parse( "#{host}#{path}" ), {
          'q' => query,
          'debug' => true
        } )
        doc = REXML::Document.new(response.body)
        headers = REXML::XPath.first( doc, "///headers" )
        # you could dump out the headers here
        # p(headers[0].text)
        
        # return an empty success response
        [204, {}, '']

Posted on February 10, 2010November 17, 2020 by Erik

running a WebDav GET request against YQL

This builds off my previous post. Suppose you’ve got content in YQL that you’d like to GET (ha!) out. The table is super simple. Ok, this is really just an unexciting GET request to YQL, but it’s cool because we’re starting to think of YQL as a file store accessible via WebDAV methods.

Prerequisites

A sherpa record w/ this value in it: {“file1″:”content 1”, “file2”, “content 2”}. The keys and values w/in the JSON can be anything. If you haven’t worked w/ YQL storage before, check out the documentation.
The code below edited to use your storage record’s select address

Flow

You make a GET request to YQL w/ a query param path set the the value of one of your keys in the JSON object described above, eg path=’file1′
YQL retrieves the storage record, converts it to JSON, and returns the value associated w/ the path you sent

Code

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
    <meta>
        <author>Erik Eldridge</author>
        <description>
        </description>
        <sampleQuery></sampleQuery>
    </meta>
    <bindings>
        <select produces="XML">
            <inputs>
                <key id="path" type="xs:string" paramType="variable"/>
            </inputs>
            <execute><![CDATA[
                response.object = function () {

                    //fetch 'files'
                    var query = 'select * from yql.storage where name="store://{select store id}"',
                        results = y.xmlToJson(y.query(query).results);

                    return results.results.result.value[path];
                }();
            ]]></execute>
        </select>
    </bindings>
</table>

Notes

If you wanted to use a WebDAV client w/ this output, you could run something like this Ruby code in a Rack app, and point your WebDAV client at it:

file = 'file1'
query = "use 'http://example.com/get.xml' as table; select * from table where path='#{file}'"
host = 'http://query.yahooapis.com'
path = '/v1/public/yql'
q = Rack::Utils.escape(query)

# setting debug to true turns off YQL's caching, which is good when testing
uri = "#{host}#{path}?q=#{q}&debug=true"

res = Net::HTTP.get_response( URI.parse(uri) )
doc = REXML::Document.new(res.body)

# extract the 'results' element
result = REXML::XPath.first( doc, "//results" )

# return the flattened xml
[200, {"Content-Type" => "application/xml"}, '<?xml version="1.0" encoding="utf-8"?>' + result.elements[1].to_s]

Posted on February 9, 2010November 17, 2020 by Erik 1

generating webdav propfind xml from yql

E4X support makes YQL is a great XML-generation engine. Here’s some code to create the response xml for a WebDAV PROPFIND request for a directory called webdav containing an empty file called foo.txt.

Note: to initially get a handle on what XML WebDAV outputs, I turned on WebDAV support in apache and made a curl request to it like this:
curl -X PROPFIND –header “Depth:1” {user}:{pass}@{your ip address}/webdav/

You can run the code below in the YQL console.

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd">
    <meta>
        <author>Erik Eldridge</author>
        <description>
        </description>
        <sampleQuery></sampleQuery>
    </meta>
    <bindings>
        <select produces="XML">
            <inputs>
                <key id="method" type="xs:string" paramType="variable"/>
                <key id="path" type="xs:string" paramType="variable"/>
            </inputs>
            <execute><![CDATA[
                response.object = function () {
                    var xml = <D:multistatus xmlns:D="DAV:">
                        <D:response xmlns:lp1="DAV:" xmlns:lp2="http://apache.org/dav/props/">
                           <D:href>/webdav/</D:href>
                           <D:propstat>
                              <D:prop>
                                 <lp1:resourcetype>
                                    <D:collection/>
                                 </lp1:resourcetype>
                                 <lp1:creationdate>2010-01-02T19:43:01Z</lp1:creationdate>
                                 <lp1:getlastmodified>Sat, 02 Jan 2010 19:43:01 GMT</lp1:getlastmodified>
                                 <lp1:getetag>"19013d-1000-b2283b40"</lp1:getetag>
                                 <D:supportedlock>
                                    <D:lockentry>
                                       <D:lockscope>
                                          <D:exclusive/>
                                       </D:lockscope>
                                       <D:locktype>
                                          <D:write/>
                                       </D:locktype>
                                    </D:lockentry>
                                    <D:lockentry>
                                       <D:lockscope>
                                          <D:shared/>
                                       </D:lockscope>
                                       <D:locktype>
                                          <D:write/>
                                       </D:locktype>
                                    </D:lockentry>
                                 </D:supportedlock>
                                 <D:lockdiscovery/>
                                 <D:getcontenttype>httpd/unix-directory</D:getcontenttype>
                              </D:prop>
                              <D:status>HTTP/1.1 200 OK</D:status>
                           </D:propstat>
                        </D:response>
                        <D:response xmlns:lp1="DAV:" xmlns:lp2="http://apache.org/dav/props/">
                           <D:href>/webdav/foo.txt</D:href>
                           <D:propstat>
                              <D:prop>
                                 <lp1:resourcetype/>
                                 <lp1:creationdate>2010-01-02T19:43:01Z</lp1:creationdate>
                                 <lp1:getcontentlength>0</lp1:getcontentlength>
                                 <lp1:getlastmodified>Sat, 02 Jan 2010 19:43:01 GMT</lp1:getlastmodified>
                                 <lp1:getetag>"19013f-0-b2283b40"</lp1:getetag>
                                 <lp2:executable>F</lp2:executable>
                                 <D:supportedlock>
                                    <D:lockentry>
                                       <D:lockscope>
                                          <D:exclusive/>
                                       </D:lockscope>
                                       <D:locktype>
                                          <D:write/>
                                       </D:locktype>
                                    </D:lockentry>
                                    <D:lockentry>
                                       <D:lockscope>
                                          <D:shared/>
                                       </D:lockscope>
                                       <D:locktype>
                                          <D:write/>
                                       </D:locktype>
                                    </D:lockentry>
                                 </D:supportedlock>
                                 <D:lockdiscovery/>
                                 <D:getcontenttype>text/plain</D:getcontenttype>
                              </D:prop>
                              <D:status>HTTP/1.1 200 OK</D:status>
                           </D:propstat>
                        </D:response>
                    </D:multistatus>;
                    return xml;
                }();
            ]]></execute>
        </select>
    </bindings>
</table>

Posted on February 8, 2010November 17, 2020 by Erik 1

Dav Glass’ YQL module for YUI 3 is awesome

sample app:


<ul>
 <li><img/></li>
</ul>
<script type="text/javascript" src="http://yui.yahooapis.com/3.0.0/build/yui/yui-min.js"></script>
<script type="text/javascript" src="http://github.com/davglass/yui-yql/raw/master/yql-min.js"></script>
<script>
//ref: http://davglass.github.com/yui-yql/
YUI().use('yql', 'node', function(Y) {
 var q1 = new Y.yql('select source from flickr.photos.sizes where photo_id in (select id from flickr.photos.search where text="panda" and safe_search="true")');
 q1.on('query', function(r) {
     var li = Y.get('li');
     for (var i = 0; i < r.results.size.length; i++) {
         if (-1 !== r.results.size[i].source.indexOf('_s')) {
             var clone = li.cloneNode(true);
             clone.query('img').set('src', r.results.size[i].source);
             Y.get('ul').append(clone);
         }
     }

 });

});
</script>

Update 12/13/10 YUI 3 now supports YQL natively: http://developer.yahoo.com/yui/3/yql/

Posted on December 24, 2009November 17, 2020 by Erik

notes: Bayjax Meetup (7/27) @ Yahoo! Sunnyvale: Jon Leblanc on YQL + YUI

Meetup: http://www.meetup.com/BayJax/calendar/10852424/

jonleblanc on using yui w/ yql

– “select * from internet” gets a laugh

– the console (developer.yahoo.com/yql/console) is an easy way to get started w/ yql

– there are ~80 tables of y! data

– 253 community tables on github.com/yql

– yql execute

– yql insert|update|delete

– christian heilmann’s “geomaker” tool scrapes a url, extracts locations, and plots them on a map

– github.com/jsleblanc/yql-utilities

— js-yql-display

— yql can return jsonp and jsonpx

— yql plays well w/ yui

– links are on http://speakerrate.com/jcleblanc

– questions

— rate limiting

— 1000/hr/ip w/ oauth

— w/ oauth 100k/day/ip

— caching?

— defaults to 5 min

— build your own table or append a var to url to break the cache