Enabling Technologies — plv8 in xTuple version 4.9

We recently announced the 4.9.0 release candidate of xTuple ERP and the final release should be ready soon. You probably did not know that 4.9.0 is the first version of xTuple ERP that requires plv8 [see below] for all databases and clients.

There are a few things you must do to get ready for this change.

We've documented the steps to install plv8 on our GitHub wiki. In short you might need to do the following:

• Install some additional software on your database server
• Add a line to your PostgreSQL configuration file
• Add the plv8 extension to your database

We recognize this addition may be painful for a number of our users. The same was true many years ago when we started using the pgcrypto extension. The main benefit at the time was the ability to process credit card transactions. That infrastructure change later allowed us to add FTP file transfers to xTuple Connect and make other general improvements. plv8 is similarly enabling.

The requirement for plv8 now is similar: there are some problems that are just plain easier to solve in JavaScript than the other two database programming languages we have been using — SQL and PL/pgSQL.

What is plv8?

plv8 is a PostgreSQL procedural language wrapper around Google's v8 JavaScript engine. This allows us (and you!) to write JavaScript functions that can be called from SQL and other procedural languages. Here are simple examples of a plpgsql function, an equivalent plv8 function, and similar plv8 anonymous code block:

create or replace function quotesDominate() returns boolean as $$
declare
  _r record;
begin
  select count(*), cohead_wasquote into _r
    from cohead
   group by cohead_wasquote
   order by 1 desc limit 1;
  return _r.cohead_wasquote;
end
$$ language plpgsql;

create or replace function quotesDominate() returns boolean as $$
  var _r = plv8.execute("select count(*), cohead_wasquote"
                      + " from cohead"
                      + " group by cohead_wasquote"
                      + " order by 1 desc limit 1;");
  return _r[0].cohead_wasquote;
$$ language plv8;

do $$
  var _r = plv8.execute("select count(*), cohead_wasquote"
                      + "  from cohead"
                      + " group by cohead_wasquote"
                      + " order by 1 desc limit 1;");
  plv8.elog(NOTICE, _r[0].cohead_wasquote);
$$ language plv8;

These examples show the simplicity of using plv8 once it's installed. They do not show the power of plv8 nor how to choose when to use plv8, plpgsql, or just plain SQL for that matter.

What are we doing with plv8?

We started using plv8 as infrastructure for our mobile web client. It was an obvious choice since both the client and middle tier code were best written in JavaScript - the client running in the web browser and the middle tier in Node.js. JavaScript in the database server reduces the friction between layers.

Once we had JavaScript and an ORM (object-relational mapping) for the mobile web client, it was relatively straightforward to create the REST API used by xTupleCommerce. Don't get me wrong — the REST API took a lot of work; just not as much as would have been required without JavaScript infrastructure.

Why now?

The obvious questions are "Why is xTuple requiring plv8 now? Why make everyone install it?" We thought long and hard about these questions. There was a lot of internal discussion weighing the pain this would cause against the perceived benefits. The answers aren't simple but the summary is: plv8 is a powerful enabling technology — it's one more tool we added to our toolbox.

Two projects motivated the forced adoption of plv8 in 4.9.0

• Workflow is a feature introduced as part of the mobile web client's inventory management and quality management modules. As such it was written entirely in JavaScript. The feature sponsors requested that the desktop client also generate workflow. We had a choice — translate a significant feature into a different language or add a plv8 requirement.
• Registration Management is another extension we have been developing. It adds features to the desktop client and xTuple Commerce for managing classes, conferences, and similar events. Watch for an announcement or blog post soon on that topic. The important part for now is that we needed a MetaSQL processor in the database for this project. Again, we had a choice — translate the MetaSQL processor into JavaScript and add a plv8 requirement or wrap the existing MetaSQL processor in a PostgreSQL extension and add a MetaSQL extension requirement.

We double-checked to make sure plv8 would run on Linux, Mac OS X, and Windows before going down this path. Our instructions for installing plv8 include links to download the plv8 library and a simple installer script for each of these three platforms. Cross-platform ERP is important to us!

And yes, you read that correctly — 4.9.0 includes a MetaSQL processor callable as a stored procedure in all editions, PostBooks and commercial. You can write dynamic queries inside stored procedures the same way you write them for reports, desktop extensions, and the core C++:

dev=# select xt.parsemetasql('select <? literal("col") ?> from cohead where cohead_id = <? value("id") ?>;',
dev(#                        '{ "params": { "col": "cohead_number", "id": "15" }}');
                       parsemetasql                       
----------------------------------------------------------
 select cohead_number from cohead where cohead_id = '15';
(1 row)

Do I need to install plv8?

For most xTuple ERP administrators planning to upgrade to xTuple ERP 4.9.0, the answer is "yes." Otherwise the Updater won't let you upgrade or you'll get an error that looks like this when you runbuild_app.js or restore one of our reference databases (empty, quickstart, or demo):

ERROR: could not open extension control file "/Library/PostgreSQL/9.3/share/postgresql/extension/plv8.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS plv8 WITH SCHEMA pg_catalog;

If you have been using the Mobile Web client or xTupleCommerce, or if you manage your database server with xtuple-server or xtuple-utility, you probably already have plv8 installed. Simply double-check that your database server and xTuple databases have the extension defined.