Movatterモバイル変換

This section provides information about and examples of use of JavaScript libraries in JavaScript stored programs as supported by the Multilingual Engine (MLE) in MySQL Enterprise Edition. (SeeSection 7.5.7, “Multilingual Engine Component (MLE)”).

First we create a databasejslib, and make it the current database, like this:

mysql> CREATE DATABASE IF NOT EXISTS jslib;Query OK, 0 rows affected (0.02 sec)mysql> USE jslib;Database changed

Using the twoCREATE LIBRARY statements shown here, we create two JavaScript libraries, each exporting one function. To be importable, the function must be declared with theexport keyword. (This is true of all JavaScript values that you wish to import into another routine; seeexport, in the Mozilla Developer documentation for more information).

mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib1 LANGUAGE JAVASCRIPT    ->     AS $$    $>       export function f(n) {    $>         return n    $>       }    $>     $$;Query OK, 0 rows affected (0.02 sec)mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib2 LANGUAGE JAVASCRIPT    ->     AS $$    $>       export function g(n) {    $>         return n * 2    $>       }    $>     $$;Query OK, 0 rows affected (0.00 sec)

You can optionally declare one function within a given library asexport default. In this case, the function must be called by the importing routine aslibname.default().

You can obtain general information about JavaScript libraries by querying the Information SchemaLIBRARIES table; the Information SchemaROUTINE_LIBRARIES shows imports into stored routines. The rows corresponding to the librariesjslib.lib1 andjslib.lib2 in these two tables are shown by the following queries:

mysql> SELECT * FROM information_schema.LIBRARIES    -> WHERE LIBRARY_SCHEMA='jslib'\G*************************** 1. row ***************************   LIBRARY_CATALOG: def    LIBRARY_SCHEMA: jslib      LIBRARY_NAME: lib1LIBRARY_DEFINITION:       export function f(n) {        return n      }              LANGUAGE: JAVASCRIPT           CREATED: 2024-12-16 09:20:26      LAST_ALTERED: 2024-12-16 09:20:26          SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION           CREATOR: me@localhost*************************** 2. row ***************************   LIBRARY_CATALOG: def    LIBRARY_SCHEMA: jslib      LIBRARY_NAME: lib2LIBRARY_DEFINITION:       export function g(n) {        return n * 2      }              LANGUAGE: JAVASCRIPT           CREATED: 2024-12-16 09:20:26      LAST_ALTERED: 2024-12-16 09:20:26          SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION           CREATOR: me@localhost2 rows in set (0.00 sec)mysql> SELECT * FROM information_schema.ROUTINE_LIBRARIES     -> WHERE LIBRARY_SCHEMA='jslib'\G*************************** 1. row ***************************ROUTINE_CATALOG: def ROUTINE_SCHEMA: jslib   ROUTINE_NAME: foo   ROUTINE_TYPE: FUNCTIONLIBRARY_CATALOG: def LIBRARY_SCHEMA: jslib   LIBRARY_NAME: lib1LIBRARY_VERSION: NULL*************************** 2. row ***************************ROUTINE_CATALOG: def ROUTINE_SCHEMA: jslib   ROUTINE_NAME: foo   ROUTINE_TYPE: FUNCTIONLIBRARY_CATALOG: def LIBRARY_SCHEMA: jslib   LIBRARY_NAME: lib2LIBRARY_VERSION: NULL2 rows in set (0.00 sec)

The second query answers the question,“Which stored routines import fromjslib, and what do they import?”

TheLIBRARIES andROUTINE_LIBRARIES tables are provided by the MLE component, and are not present if the component is not installed.

If you have the necessary privileges, you can also view a library's JavaScript code using theSHOW CREATE LIBRARY statement. See the description of this statement for more information and examples.

You can also useSHOW LIBRARY STATUS to obtain basic information about one or more JavaScript libraries, including name, database, creator (definer), and dates of creation and most recent modification. SeeSection 15.7.7.25, “SHOW LIBRARY STATUS Statement”, for more information and examples.

To create a JavaScript function that uses the two libraries, include theUSING keyword together with a list of libraries to be imported as part ofCREATE FUNCTION, like this:

mysql> CREATE FUNCTION foo(n INTEGER) RETURNS INTEGER LANGUAGE JAVASCRIPT    ->         USING (jslib.lib1 AS mylib, jslib.lib2 AS yourlib)    ->         AS $$    $>           return mylib.f(n) + yourlib.g(n)    $>         $$;Query OK, 0 rows affected (0.00 sec)

The alias (AS keyword and clause) is generally optional, but if specified, you must use this for the library name when including functions from it in your own stored programs. A library identifier—the name, or its alias if there is one, exclusive of database name—must be unique within a given JavaScript stored function. You can useAS withCREATE FUNCTION to avoid name collisions between libraries. For example, to include a library namedourlib in the current database along with one having the same name but residing in theother database, you could use the statement shown here:

CREATE FUNCTION myfunc(x INTEGER) RETURNS INTEGER LANGUAGE JAVASCRIPT     USING (ourlib, other.ourlib AS theirlib)...;

In the case just shown, there are two libraries having the same name; to avoid any conflicts, it is necessary to use an alias for at least one of them.

If one (or more) of the included libraries does not exist, or if the user does not have the required privileges to access it, theCREATE FUNCTION statement referencing it is rejected with an error.

References to an imported library within a JavaScript stored routine are expected to match the library name as declared. Note that the name as employed in theUSING clause need not have the same lettercasing; for example,USING (MY_LIB) can be used to import a library namedmy_lib, although references to the library within the stored routine body must usemy_lib.

You can verify that the function was created by checking the Information SchemaROUTINES table, with a query similar to that which is shown here:

mysql> SELECT    ->   SPECIFIC_NAME, ROUTINE_NAME, ROUTINE_SCHEMA,    ->   DATA_TYPE, ROUTINE_DEFINITION    -> FROM information_schema.ROUTINES    -> WHERE ROUTINE_NAME='foo'\G*************************** 1. row ***************************     SPECIFIC_NAME: foo      ROUTINE_NAME: foo    ROUTINE_SCHEMA: jslib         DATA_TYPE: intROUTINE_DEFINITION:       return mylib.f(n) + otherlib.g(n)1 row in set (0.00 sec)

We can invoke the function just created just as we would any other stored function.

mysql> SELECT foo(2), foo(3), foo(-10), foo(1.5), foo(1.2);+--------+--------+----------+----------+----------+| foo(2) | foo(3) | foo(-10) | foo(1.5) | foo(1.2) |+--------+--------+----------+----------+----------+|      6 |      9 |      -30 |        6 |        3 |+--------+--------+----------+----------+----------+1 row in set (0.00 sec)

Because the input parameter is of typeINTEGER, rounding as if usingMath.round() takes place before the value is used in any calculations, 1.5 is evaluated as2 + (2 * 2) = 6, and 1.2 as1 + (2 * 1) = 3.

JavaScript syntax is checked at library creation time, as shown here:

mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib3 LANGUAGE JAVASCRIPT    ->     AS $$    $>       export function f(n) {    $>         return n $ 2    $>       }    $>     $$;ERROR 6113 (HY000): JavaScript> SyntaxError: lib3:3:17 Expected ; but found $        return n $ 2                 ^

TheCREATE LIBRARY statement executes successfully after correcting the typographical error, as shown here:

mysql> CREATE LIBRARY IF NOT EXISTS jslib.lib3 LANGUAGE JAVASCRIPT    ->     AS $$    $>       export function h(n) {    $>         return n - 2    $>       }    $>     $$;Query OK, 0 rows affected (0.01 sec)

It is also possible to perform dynamic imports, which do not have to be specified with aUSING clause. You should be aware that a dynamic import returns aPromise; useawait to obtain the imported library. In general, it is recommended that you useawait to wait for anyPromise created in your code.

You can useawait in the top level of stored functions and stored procedures, as shown here:

mysql> CREATE DATABASE db1;Query OK, 0 rows affected (0.01 sec)mysql> CREATE LIBRARY db1.lib1 LANGUAGE JAVASCRIPT    -> AS $$    $>   export function myAdd(x, y) {returns x + y}    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> CREATE FUNCTION use_dynamic_import() RETURNS INT LANGUAGE JAVASCRIPT    -> AS $$    $>   let m = await import("/db1/lib1")    $>   return m.myAdd(1, 2)    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> SELECT use_dynamic_import(); +-----------------------+| uses_dynamic_import() |+-----------------------+|                     3 |+-----------------------+1 row in set (0.00 sec)

Usingawait causes thePromise returned byimport() to be resolved. Resolution can be pending, fulfilled, or rejected; a“resolved” or“settled”Promise is one that is no longer pending, and can be either fulfilled or rejected.

import() takes the path of the imported library, which must be a string of the form"/db_name/lib_name"; it returns aPromise of an ECMAScript module.

The following example demonstrates how you can determine which of multiple libraries to load at runtime. First we create two libraries—each of which exports multiple functions and objects, and has a default export—like this:

mysql> CREATE LIBRARY db1.lib_rectangle LANGUAGE JAVASCRIPT    -> AS $$    $>  export class Rectangle {    $>    constructor(height, width) {    $>      this.height = height    $>      this.width = width    $>    }    $>    print() {    $>      return "Rectangle of size " + this.height + " by " + this.width    $>    }    $>  }    $>  export function area(x) {return x.height * x.width}    $>  const r = new Rectangle(2, 3)    $>  export default r    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> CREATE LIBRARY db1.lib_square LANGUAGE JAVASCRIPT    -> AS $$    $>  export class Square {    $>    constructor(a) {    $>      this.a = a    $>    }    $>    print() {return "Square of size " + this.a}    $>  }    $>  export function area(x) {return x.a * x.a}    $>  const s = new Square(2)    $>  export default s    $> $$;Query OK, 0 rows affected (0.01 sec)

TheprintObject() function determines the library to import at runtime, based on the value passed to it, as shown here:

mysql> CREATE FUNCTION printObject(object_type VARCHAR(16)) RETURNS TEXT LANGUAGE JAVASCRIPT    -> AS $$    $>  let module = await import(`/db1/lib_${object_type}`)    $>  // both libraries have default exports with print methods    $>  return module.default.print()    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> SELECT printObject("square");+-----------------------+| printObject("square") |+-----------------------+|      Square of size 2 |+-----------------------+1 row in set (0.00 sec)mysql> SELECT printObject("rectangle");+--------------------------+| printObject("rectangle") |+--------------------------+| Rectangle of size 2 by 3 |+--------------------------+1 row in set (0.00 sec)

In addition, the namespace object returned after awaiting thePromise can be destructed like any other object; the default and other exports can easily be renamed for use within the importing stored program, as shown here:

mysql> CREATE FUNCTION computeRectangle() RETURNS INT LANGUAGE JAVASCRIPT    -> AS $$    $>  let {default: myRectangle, area: area} = await import(`/db1/lib_rectangle`)    $>  return area(myRectangle)    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> SELECT computeRectangle();+--------------------+| computeRectangle() |+--------------------+|                  6 |+--------------------+1 row in set (0.00 sec)

It is possible to import libraries or portions of them into other libraries, as shown in this example where functionfoo() is imported from librarymylib into librarytheirlib and used in a functionbar() defined intheirlib, which is then imported into stored functionmyfunc() which invokesbar():

mysql> CREATE LIBRARY mylib LANGUAGE JAVASCRIPT    -> AS $$    $>   export function foo(){return 42}    $> $$;Query OK, 0 rows affected (0.04 sec)mysql> CREATE LIBRARY theirlib LANGUAGE JAVASCRIPT    -> AS $$    $>   import {foo} from "/db1/mylib"    $>   export function bar(){return 2 * foo()}    $> $$;Query OK, 0 rows affected (0.01 sec)mysql> CREATE FUNCTION myfunc(x INTEGER) RETURNS INT     -> LANGUAGE JAVASCRIPT    -> NO SQL    -> USING (theirlib)    -> AS $$    $>   let result = theirlib.bar()    $>    $>   result += x    $>    $>   return result    $> $$;Query OK, 0 rows affected (0.00 sec)mysql> SELECT myfunc(1), myfunc(10);+-----------+------------+| myfunc(1) | myfunc(10) |+-----------+------------+|        85 |         94 |+-----------+------------+1 row in set (0.00 sec)

Library functions can be invoked only within the library or stored routine into which their containing library is imported. For example, the following stored functionmyfunc2() importstheirlib, andtheirlib importsmylib. TheCREATE FUNCTION statement in this case succeeds, but a direct attempt to invoke a function originating inmylib is rejected at runtime, as shown here:

mysql> CREATE FUNCTION myfunc2(x INTEGER) RETURNS INT    -> LANGUAGE JAVASCRIPT    -> NO SQL    -> USING (theirlib)    -> AS $$    $>   return mylib.foo()    $> $$;Query OK, 0 rows affected (0.00 sec)mysql> SELECT myfunc2(1), myfunc2(10);ERROR 6113 (HY000): JavaScript> ReferenceError: mylib is not defined

MLE JavaScript library code is executed only when invoked as part of a stored routine which includes the library. Library code is not executed by any of the following statements:

For example, these are validCREATE LIBRARY andCREATE FUNCTION statements, since the code is not actually executed:

mysql> CREATE LIBRARY my_lib LANGUAGE JAVASCRIPT    -> AS $$    $>   throw "MyError"    $> $$;Query OK, 0 rows affected (0.02 sec)mysql> CREATE FUNCTION my_func(x INTEGER)     -> RETURNS INTEGER LANGUAGE JAVASCRIPT NO SQL    -> USING(my_lib)    -> AS $$    $>   return x * 10    $> $$;Query OK, 0 rows affected (0.02 sec)

Invoking the function that imports the library actually invokes the library code, which causes an error, as shown here:

mysql> SELECT my_func(8);ERROR 6113 (HY000): JavaScript> MyError