Saturday, March 9, 2013

Loading Useful Modules in PLV8

PLV8 is a trusted language. In fact it arguably safer than pretty much any non-core loadable language, since the interpreter is naturally sandboxed. So there's no way to load external processing modules from the file system. However, pure JavaScript modules can be cleanly loaded from the database itself. A couple of extremely useful ones I have found are Underscore and node-jPath. The former can be loaded with no modification whatever. The latter is written for use with node.js and needs a small amount of adjustment before it's loaded in plv8.

So how do you do this? Well, first you need to grab the code:
curl -o underscore.js http://underscorejs.org/underscore-min.js
curl -o jpath.js https://raw.github.com/stsvilik/node-jpath/master/lib/node-jpath.js
Then edit jpath.js so that, at the bottom, instead of assigning to module.exports it creates an object called jpath and adds the select and filter methods to it.

Then you'll need a table to load the modules from, and to put the modules in this table. You'll also need a function to load them. These can be accomplished with the following psql script:
\set underscore `cat underscore-min.js`
\set jpath `cat jpath.js`

create table plv8_modules(modname text primary key, load_on_start boolean, code text);

insert into plv8_modules values ('underscore',true,:'underscore'),
    ('jpath',true,:'jpath');

create or replace function plv8_startup()
returns void
language plv8
as
$$
load_module = function(modname)
{
    var rows = plv8.execute("SELECT code from plv8_modules " +
                            " where modname = $1", [modname]);
    for (var r = 0; r < rows.length; r++)
    {
        var code = rows[r].code;
        eval("(function() { " + code + "})")();
    }
        
};
$$;

Now test it out:
select plv8_startup();
do language plv8 ' load_module("underscore"); load_module("jpath"); ';
-- test the underscore module's extend function
do language plv8 $$ 
   x = {'a':1}; 
   y=_.extend(x,{'a':2,'b':3},{'b':4,'c':5}); 
   plv8.elog(NOTICE,JSON.stringify(y)); 
$$;
-- test jpath module's filter function
do language plv8 $$
  var jsonData = {
    people: [
        {name: "John", age:26, gender:"male"},
        {name: "Steve", age:24, gender:"male"},
        {name: "Susan", age:22, gender:"female"},
        {name: "Linda", age:30, gender:"female"},
        {name: "Adam", age:32, gender:"male"}
    ]
  };
  //We want to get all males younger then 25
  var match = jpath.filter(jsonData, "people[gender=male && age < 25]");
  plv8.elog(NOTICE,JSON.stringify(match));
$$;
You should see output like:
NOTICE:  {"a":2,"b":4,"c":5}
DO
NOTICE:  [{"name":"Steve","age":24,"gender":"male"}]
DO
Now, the final step is to make these get loaded automatically. Alter your plv8_startup() function by adding to following at the end:
// now load all the modules marked for loading on start
var rows = plv8.execute("SELECT modname, code from plv8_modules where load_on_start");
for (var r = 0; r < rows.length; r++)
{
 var code = rows[r].code;
 eval("(function() { " + code + "})")();
}
and finally add
plv8.start_proc = 'plv8_startup'
to you postgresql.conf file, and restart. You should now be able to use these modules anywhere in your PLv8 code.


11 comments:

  1. Using Extension Templates it's going to be even easier shipping those extra JS files in 9.3, and with the Event Triggers capabilities it will even be possible to include the whole fetching and template installation as part of the CREATE EXTENSION command... stay tuned :)

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. sorry, not working only
    plv8.start_proc = 'plv8_startup'
    in postgresql.conf!

    Thanks for the great idea! :)

    ReplyDelete
  4. Hmm, it looks like you need to mangle jpath some to make it postgres friendly. I must have forgotten that when I wrote this post.

    Thanks for letting me know. Maybe I will fork it for use with PLV8.

    ReplyDelete
    Replies
    1. Actually, I did mention that in the post, and you seem to have missed it: "Then edit jpath.js so that, at the bottom, instead of assigning to module.exports it creates an object called jpath and adds the select and filter methods to it."

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. also this: for (var r = 0; r > rows.length; r++)

    should be: for (var r = 0; r < rows.length; r++)

    ReplyDelete
  7. You might want to look at this project. It's less painful. It includes unit testing, migrations, logging, bundling ... https://github.com/mgutz/plv8-bedrock

    ReplyDelete