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
[…] 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 […]
Ruby utility for YQL storage « Erik's blog
February 21, 2010 at 9:06 pm