Erik's blog

Code, notes, recipes, general musings

Archive for February 2010

Ruby utility for YQL storage

with one comment

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:

  1. Save the code below into a file
  2. Edit the file to change all occurrences of ‘{your domain}’ to your domain
  3. Use (ha!) the YQL storage addresses defined in the key/val table setup for the use() settings
  4. 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:
Squirrel yawning
Photo credit: _temaki_

Written by Erik

February 21, 2010 at 9:05 pm

Posted in code

Tagged with , , ,

initial attempt at simple key/val layer on YQL storage

with one comment

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:

usage:

  1. Save the code below to a file on your server, eg kv.xml
  2. Edit the file to change all occurrences of ‘{your domain}’ to your domain
  3. Create a file called kv.env and define your table url and YQL storage addresses into it, as described in the YQL documentation
  4. 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:

Flickr picture of a mallard

Photo credit: foxypar4

Written by Erik

February 21, 2010 at 6:43 pm

Posted in code

Tagged with ,

A simple Ruby Rack router

leave a comment »

Motivation

  • I want to be able to handle http requests.  I like the simplicity of Rack, but I want more control over the initial mapping.  I like Sinatra, but I want to easily handle request methods other than GET, PUT, POST, DELETE, and HEAD. So, the Router class below maps regular expressions to handler functions.

Requirements/Environment

  • Mac os x 10.5.8
  • Ruby 1.8.6 (2008-08-11 patchlevel 287) [universal-darwin9.0]
  • Rack gem version 1.1.0

Code

class Router
  def initialize(routes)    
    @routes = routes
  end
  def default
    [ 404, {'Content-Type' => 'text/plain'}, 'file not found' ]
  end
  def call(env)
    @routes.each do |route|
      match = env['REQUEST_PATH'].match(route[:pattern])
      if match
        return route[:controller].call( env, match )
      end
    end
    default
  end
end

Usage

# assumes router code is in router.rb
require 'router'

use Rack::CommonLogger
use Rack::ShowExceptions
use Rack::Lint
use Rack::Static, :urls => ["/static"]

run Router.new([
  {
    :pattern => %r{^/page1$}, 
    :controller => lambda do |env, match|
    
      [ 200, {'Content-Type' => 'text/html'}, 'page 1' ]

    end
  },
  {
    :pattern => %r{^/}, 
    :controller => lambda do |env, match|
    
      [ 200, {'Content-Type' => 'text/html'}, 'index!' ]
  
    end
  }
  
]);

Run it on the command line using Rack’s native Rackup ($ rackup config.ru) or via Ryan Tomayko’s shotgun, which conveniently auto-reloads.

Reference

Update Mar 5, 2011

  • because Ruby < 1.9 doesn't preserve ordering in hashes, I’ve updated the code to use an array of routes. It’s more verbose, but now the route matching correctly runs top to bottom.
  • I also added a method for overriding the default response from the router

Update Mar 9, 2011

Written by Erik

February 21, 2010 at 1:58 am

Posted in code

Tagged with , ,

updated Ruby utility for simple SQLite3 key/val storage

with one comment

motivation:

  • modify my previous sqlite utility to allow for table name definition & auto table creation

requirements/environment:

  • the requirements and environment are the same as in my previous post

code:

require 'rubygems'
require 'sqlite3'

class Storage
  def initialize
    @db = SQLite3::Database.new( 'sqlite' )
  end
  def create(table)
    begin
    @db.execute( %{
      CREATE TABLE #{table}
      (key varchar(100) PRIMARY KEY,
      value varchar(1000))
    } )
    rescue SQLite3::SQLException => details
      # puts details
    end
  end
  def use(table)
    @table = table
    self.create( table )
    return self
  end
  def get(key)
    results = @db.get_first_row( %{
      SELECT value FROM #{@table} WHERE key='#{key}'
    } )
    if results
      return results[0]
    end
  end
  def set(key, val)
    result = @db.execute( %{
      REPLACE INTO %s
      (key, value)
      VALUES ('%s', '%s')
    } % [@table, key, val ] )
  end
end

usage:

require 'rubygems'
require 'sqlite3'
require 'json'

Storage.new.use( 'user' ).set( 'user123', { :str => 'orale!' }.to_json )

json = Storage.new.use( 'user' ).get( 'user123' )

p JSON.parse json

notes:

  • DataMapper looks like it might provide much of this functionality. If my script’s complexity continues to increase, I’m inclined to incorporate a more sophisticated solution to run it. Thoughts?

Written by Erik

February 20, 2010 at 5:00 pm

Posted in code

Tagged with ,

a simple SQLite key/val store in Ruby

with 5 comments

motivation:

  • I just like having a place to store blobs of data on my local machine

environment/requirements:

  • Mac os x 10.5.8
  • ruby 1.8.6 (2008-08-11 patchlevel 287) [universal-darwin9.0]
  • SQLite3
  • sqlite3-ruby (1.2.1) gem

code:

require 'rubygems'
require 'sqlite3'
class Storage
  def self.init
    db = SQLite3::Database.new( 'sqlite' )
    db.execute( %{
      CREATE TABLE foo 
      (key varchar(100) PRIMARY KEY, 
      value varchar(1000), 
      modified timestamp(20))
    } )
  end
  def self.get(key)
    db = SQLite3::Database.new( 'sqlite' )
    db.get_first_row( %{
      SELECT * FROM foo WHERE key='#{key}'
    } )
  end
  def self.set(key, val)
    db = SQLite3::Database.new( 'sqlite' )
    result = db.execute( %{
      REPLACE INTO foo
      (key, value, modified) 
      VALUES ('%s', '%s', %d)
    } % [key, val, Time.now.to_i] )
  end
end

usage:

require 'rubygems'
require 'sqlite3'
require 'json'
...
# init table once (anyone have a table detection query?)
Storage.init
...
# put data in
value = {:foo => 'bar'}
Storage.set( '123', value.to_json )
...
# get data out
row = Storage.get( '123' )
...

Written by Erik

February 20, 2010 at 2:33 am

Posted in code

Tagged with ,

Ruby YQL utility function example

leave a comment »

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 🙂

Written by Erik

February 18, 2010 at 1:49 am

Posted in code

Tagged with ,

1st attempt at a Ruby YQL utility function

with 2 comments

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

Written by Erik

February 18, 2010 at 1:08 am

Posted in code

Tagged with ,