~ubuntu-branches/ubuntu/trusty/drizzle/trusty

``JS()`` executes a JavaScript code snippet and returns the value of the last executed statement. Additional arguments are passed to the JavaScript environment and are available in the ``arguments[]`` array. If the optional ``AS arg_name`` is used, the same argument value is made available as a global variable with that name.

.. _js_loading:

-------

This plugin is loaded by default.

If you want to prevent the loading of this plugin, start :program:`drizzled` with::

--plugin-remove=js

.. _js_examples:

Examples

--------

The first argument is required and should be a string of valid JavaScript code. The value of the last statement is returned, note that you should not use a ``return`` keyword. This is a top level JavaScript code snippet, not a JavaScript function.

.. code-block:: mysql

SELECT JS('var d = new Date(); "Drizzle started running JavaScript at: " + d;');

Will output

+----------------------------------------------------------------------------------+

| JS('var d = new Date(); "Drizzle started running JavaScript at: " + d;') |

+==================================================================================+

| Drizzle started running JavaScript at: Mon Aug 29 2011 00:23:31 GMT+0300 (EEST) |

+----------------------------------------------------------------------------------+

Additional arguments are passed to the JavaScript environment and are available in the ``arguments[]`` array.

.. code-block:: mysql

SELECT JS("arguments[0] + arguments[1] + arguments[2];", 1, 2, 3) AS 'JS(...)';

Will output

+--------------+

| JS(...) |

+==============+

| 6 |

+--------------+

If the optional ``AS arg_name`` is used, the same argument value is made available as a global variable with that name.

.. code-block:: mysql

SELECT JS("first + second + third;", 1 AS 'first', 2.0 AS 'second', 3.5 AS 'third') AS 'JS(...)';

Will output

+--------------+

| JS(...) |

+==============+

| 6.5 |

+--------------+

.. _json_parse:

Using JS() to parse JSON documents

-----------------------------------

JavaScript includes a JSON parser. This means you can use ``JS()`` as a JSON parser, and optionally use JavaScript to manipulate or select fragments of the JSON document. To do this, pass your JSON document as an argument, and use the ``JSON.parse()`` method to return it as a JavaScript object:

.. code-block:: mysql

SELECT JS("var jsondoc = JSON.parse(arguments[0]); jsondoc['name']['firstname'];",

'{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') AS 'JS(...)';

Will output

+--------------+

| JS(...) |

+==============+

| Henrik |

+--------------+

To return a JSON document from JavaScript, use ``JSON.stringify()``:

.. code-block:: mysql

SELECT JS("var jsondoc = JSON.parse(arguments[0]);

100

JSON.stringify(jsondoc['name']);",

101

'{ "name" : {"firstname" : "Henrik", "lastname" : "Ingo"} }') AS 'JS(...)';

102

103

104

Will output

105

106

+------------------------------------------+

107

| JS(...) |

108

+==========================================+

109

| {"firstname":"Henrik","lastname":"Ingo"} |

110

+------------------------------------------+

111

112

Note that since a Drizzle function can only return scalar values, if you want to return arrays or objects from your JavaScript, JSON is a recommended way of doing that.

113

114

.. _js_queries:

115

116

Using JS in queries, passing columns as arguments

117

-------------------------------------------------

118

119

Naturally, the arguments can also be columns in a query. For instance in the case of JSON data, if you have stored JSON documents as TEXT or BLOB in a table, you can now use ``JSON.parse()`` to select individual fields out of it:

120

121

.. code-block:: mysql

122

123

CREATE TABLE t (k INT PRIMARY KEY auto_increment, v TEXT);

124

INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Roland", "lastname" : "Bouman" } }');

125

INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Henrik", "lastname" : "Ingo" } }');

126

INSERT INTO t (v) VALUES ('{ "person" : { "firstname" : "Brian", "lastname" : "Aker" } }');

127

SELECT JS('var person = JSON.parse(jsondoc); person["person"]["firstname"];',

128

v as jsondoc) AS 'JS(...)'

129

FROM t WHERE k=2;

130

131

132

Will output

133

134

+--------------+

135

| JS(...) |

136

+==============+

137

| Henrik |

138

+--------------+

139

140

141

And

142

143

.. code-block:: mysql

144

145

SELECT k, JS('var person = JSON.parse(jsondoc); person["person"]["firstname"];',

146

v as jsondoc) AS 'firstname',

147

JS('var person = JSON.parse(jsondoc); person["person"]["lastname"];',

148

v as jsondoc) AS 'lastname'

149

FROM t;

150

151

Will break your unstructured JSON data back into a relational table:

152

153

+---+-----------+----------+

154

| k | firstname | lastname |

155

+===+===========+==========+

156

| 1 | Roland | Bouman |

157

+---+-----------+----------+

158

| 2 | Henrik | Ingo |

159

+---+-----------+----------+

160

| 3 | Brian | Aker |

161

+---+-----------+----------+

162

163

.. _js_stored_procedure_surrogate:

164

165

Using JS as surrogate for stored procedures:

166

--------------------------------------------

167

168

Especially if the JavaScript you want to use is more complex, it might be a good idea to store the javascript itself in a table in Drizzle, or alternatively a variable. This simplifies queries that use the script:

169

170

.. code-block:: mysql

171

172

CREATE TABLE sp (name VARCHAR(255) PRIMARY KEY, script TEXT);

173

INSERT INTO sp (name, script) VALUES ('get_person_property', 'var person = JSON.parse(jsondoc); person["person"][property];');

174

SELECT k, JS( (SELECT script FROM sp WHERE name='get_person_property'),

175

v as jsondoc, 'firstname' as 'property') AS 'firstname',

176

JS( (SELECT script FROM sp WHERE name='get_person_property'),

177

v as jsondoc, 'lastname' as 'property') AS 'lastname'

178

FROM t;

179

180

181

Will output the same result as above:

182

183

+---+-----------+----------+

184

| k | firstname | lastname |

185

+===+===========+==========+

186

| 1 | Roland | Bouman |

187

+---+-----------+----------+

188

| 2 | Henrik | Ingo |

189

+---+-----------+----------+

190

| 3 | Brian | Aker |

191

+---+-----------+----------+

192

193

.. _js_future_work:

194

195

Limitations and future work

196

---------------------------

197

198

The current version of ``JS()`` is complete in the sense that any type of arguments (integer, real, decimal, string, date) can be used, JavaScript code can be of arbitrary complexity and scalar values of any type can be returned. However, apart from the input parameters and the return value, there is no way to interact with Drizzle from the JavaScript environment. The plan is that in a future version ``JS()`` will expose some Drizzle API's, such as the ``Execute()`` API, so that one could query Drizzle tables and call other Drizzle functions from the JavaScript environment. This would essentially make JS() a form of JavaScript stored procedures. Of course, a next step after that could be to actually support ``STORED PROCEDURE`` syntax and permissions.

199

200

Values of type ``DECIMAL`` will be passed as JavaScript ``Double`` values. This may lead to loss of precision. If you want to keep the precision, you must explicitly cast ``DECIMAL`` values into ``CHAR`` when you pass them as arguments. Note that this will affect how the JavaScript ``+`` operator works on the value (string concatenation instead of addition).

201

202

The current version lacks several obvious performance optimizations. Most importantly the v8 JavaScript engine is single threaded, so heavy use of ``JS()`` on busy production servers is not recommended. A future version will use the v8 Isolate class to run several instances of the single threaded v8 engine.

203

204

.. _js_authors:

205

206

Authors

207

-------

208

209

Henrik Ingo

210

211

Thanks to Roland Bouman for suggesting to use v8 engine instead of just a JSON parser and for review and comments on JavaScript and JSON conventions.

212

213

.. _js_version:

214

215

Version

216

-------

217

218

This documentation applies to **js 0.9**.

219

220

To see which version of the plugin a Drizzle server is running, execute:

221

222

.. code-block:: mysql

223

224

SELECT MODULE_VERSION FROM DATA_DICTIONARY.MODULES WHERE MODULE_NAME='js'

225

226

227

Changelog

228

---------

229

230

v0.9

231

^^^^

232

* First release. Complete JS() functionality, but no APIs back to Drizzle are exposed yet and several performance optimizations were left for later release.

Older »