Thanks for the comments! Your knowledge here is very helpful.

And here are the results from Helsinki... (Netherlands, 12 points.)


drizzle> select js_eval("var jsondoc = JSON.parse(arguments[0]); jsondoc['firstname'];", '{"firstname" : "Henrik", "lastname" : "Ingo"}') as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| Henrik |
+--------------+
1 row in set (11.7978 sec)
 
drizzle> select js_eval("var jsondoc = JSON.parse(arguments[0]); jsondoc['name'];", '{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') as 'js_eval(...)';
+-----------------+
| js_eval(...) |
+-----------------+
| [object Object] |
+-----------------+
1 row in set (0.004471 sec)
 
drizzle> select js_eval("var jsondoc = JSON.parse(arguments[0]); jsondoc['name']['firstname'];", '{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| Henrik |
+--------------+
1 row in set (0.024345 sec)
 
drizzle> select js_eval("var jsondoc = JSON.parse(arguments[0]); JSON.stringify(jsondoc['name']['firstname']);", '{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| "Henrik" |
+--------------+
1 row in set (0.008467 sec)
 
drizzle> select js_eval("var jsondoc = JSON.parse(arguments[0]); JSON.stringify(jsondoc['name']);", '{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') as 'js_eval(...)';
+------------------------------------------+
| js_eval(...) |
+------------------------------------------+
| {"firstname":"Henrik","lastname":"Ingo"} |
+------------------------------------------+
1 row in set (0.006754 sec)
 
drizzle> select js_eval("var jsondoc = JSON.parse(jsondata); jsondoc['name']['firstname'];", '{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }' as jsondata ) as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| Henrik |
+--------------+
1 row in set (0.004395 sec)
 
drizzle> select js_eval("arguments[0] + arguments[1] + arguments[2];", 1, 2, 3) as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| 6 |
+--------------+
1 row in set (0.039269 sec)
 
drizzle> select js_eval("arguments[0] + arguments[1] + arguments[2];", 1, 2.0, 3.5) as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| 6.5 |
+--------------+
1 row in set (0.022482 sec)
 
drizzle> select js_eval("first + second + third;", 1 AS 'first', 2.0 AS 'second', 3.5 AS 'third') as 'js_eval(...)';
+--------------+
| js_eval(...) |
+--------------+
| 6.5 |
+--------------+
1 row in set (0.0045 sec)


So, implemented your suggestions, correctly pass also integer and decimal/number types, and support for named arguments, the name becoming a global variable name in javascript.

And to answer your standing question, no I'm not caching the compiled script nor are there any other optimizations. But functionality-wise it's getting quite good!

And here's an example of using this against a table. This is in fact pretty much the use case I had in mind. (Or at least a first step in a larger picture involving triggers and indexes...)


drizzle> create table t (k INT PRIMARY KEY auto_increment, v TEXT);Query OK, 0 rows affected (0.004813 sec)
 
drizzle> INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Roland", "lastname" : "Bouman" } }');
Query OK, 1 row affected (0.002912 sec)
 
drizzle> INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Henrik", "lastname" : "Ingo" } }');
Query OK, 1 row affected (0.001796 sec)
 
drizzle> INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Brian", "lastname" : "Aker" } }');
Query OK, 1 row affected (0.002644 sec)
 
drizzle> SELECT js_eval('var person = JSON.parse(jsondoc); person["person"]["firstname"];', v as jsondoc) AS 'js_eval(...)' FROM t WHERE k=2;
+--------------+
| js_eval(...) |
+--------------+
| Henrik |
+--------------+
1 row in set (0.005119 sec)
 
drizzle> SELECT k, js_eval('var person = JSON.parse(jsondoc); person["person"]["firstname"];', v as jsondoc) AS 'firstname', js_eval('var person = JSON.parse(jsondoc); person["person"]["lastname"];', v as jsondoc) AS 'lastname' FROM t;
+---+-----------+----------+
| k | firstname | lastname |
+---+-----------+----------+
| 1 | Roland | Bouman |
| 2 | Henrik | Ingo |
| 3 | Brian | Aker |
+---+-----------+----------+
3 rows in set (0.017192 sec)


And finally, to pay homage to the title of this blog post:

- But Henrik, this is not stored procedure. You just implemented a function to run javascript snippets.
- Ok, so let's store the javascript code in the database:


drizzle> CREATE TABLE sp (name VARCHAR(255) PRIMARY KEY, script TEXT);
Query OK, 0 rows affected (0.003578 sec)
 
drizzle> INSERT INTO sp (name, script) VALUES ('get_person_property', 'var person = JSON.parse(jsondoc); person["person"][property];');
Query OK, 1 row affected (0.002341 sec)
 
drizzle> SELECT k, js_eval(sp.script, v as jsondoc, 'firstname' as 'property') AS 'firstname', js_eval(sp.script, v as jsondoc, 'lastname' as 'property') AS 'lastname' FROM t JOIN sp WHERE sp.name='get_person_property';
+---+-----------+----------+
| k | firstname | lastname |
+---+-----------+----------+
| 1 | Roland | Bouman |
| 2 | Henrik | Ingo |
| 3 | Brian | Aker |
+---+-----------+----------+
3 rows in set (0.028613 sec)


Now it's a *stored* script :-)