User Defined Functions

When built-in functions are not enough you can extend SpectX query language by creating your own, a user defined function (UDF). You can use any of the built-in functions to combine an expression that suits your purpose or you can also implement your function entirely in JavaScript.

Functions are allowed to take input arguments, a comma-separated list of named arguments, enclosed in parentheses. Input arguments are optional. When not present the parentheses can be omitted.

Expression UDF

User-defined functions composed of expressions are called expression UDF’s. At the declaration, the function name must be prepended with ‘$’, indicating return value being single value type.

The simplest UDF is a single expression with no input arguments, declared as expression variables. When you want to pass input arguments you just need to specify them in the comma-separated list enclosed in parentheses:

$function_name( args, ... ) = expression;

Example:

1
$calcRatio(prim, total) = ($prim/FLOAT($total))*100;

To declare a UDF consisting of multiple expressions you need to define a function with a body (the space between curly braces):

$function_name( args, ... ) {
   $local_variable_name = expression;
   ...
   return ret_value;
}

The expression declarations within the function body are separated by semicolons. The variables within the body are local, i.e they are visible only within the body of the function.

The body must end with a return statement, returning a value of SpectX data type.

You can use newlines and whitespaces between expressions to place them in more readable fashion (they are ignored by the interpreter).

Example:

1
2
3
4
5
6
$cost(start::TIMESTAMP, stop::TIMESTAMP, price) {
  $end = IF($stop IS null, now(), $stop);
  $dur = ($end - $start)/3600000.0;
  $px  = $dur * $price;
  return LONG($px * 100)/100.0;
};

JavaScript UDF

In the declaration, the function name must be prepended with the type identifier ‘$’. The function is declared using CREATE FUNCTION directive:

$function_name( args, ... ) = CREATE FUNCTION (
    lang: "javascript",
    include: "/path/to/included/file.js, ... ",
    returns: data_type,
    code: function_body
)
where:
  • args: is one or more function input arguments
  • lang: is a string specifying the language. Currently only javascript is supported.
  • returns: specifies the type that the return value will be converted to (see the details here).
  • include: a string specifying the path to the included source code file. You can declare multiple paths separated by a comma. Optional.
  • function_body: a string or a variable referencing a string containing the body of a function. The function body must end with the return statement returning the value of the type specified by returns argument.

Example:

1
2
3
4
5
$multiplyInputs(x::LONG, y::LONG) = CREATE FUNCTION(
  lang:"javascript",
  returns::LONG,
  code:"return x * y;"
);

When developing complex JavaScript functions it is more convenient to declare body in a separate Javascript source file, which provides syntax highlighting. To use declared functions you need to include the source file(s) and call functions from the body:

Example:

example.js
1
2
3
4
5
6
function isYoung(age) {
  ret = true;
  if(age > 18)
    ret = false;
  return ret;
}
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
$person(nameStr::STRING, ageInt::INTEGER) = CREATE FUNCTION(
  lang: "javascript",
  library: "./example.js",
  returns: {name:"", age:0, underAge:true},
  code: $body,
);

$body = "return {name:nameStr, age:ageInt, underAge:isYoung(ageInt)};"

dual.select($person('John', 8), $person('Anna', 22));
where:
  • lines 1-2 declares a user defined Javascript function by name ‘person’
  • line 3 includes source file “example.js” located in the same directory as calling script
  • line 4: declares TUPLE as return type, with three elements with default values.
  • line 5: declares function body using ‘$body’ variable as reference.

Return Data Type Conversion

SpectX is quite relaxed in handling return value conversions. If conversion from the returned value to a declared SpectX data type is possible, it will do so. In case the conversion is impossible or function does not return the declared value, the NULL return value is assumed.

INTEGER, LONG, FLOAT, DOUBLE, BOOLEAN, STRING

JavaScript Number, Boolean and String types are directly converted to SpectX numeric types.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
$ret_int()=CREATE FUNCTION(
    lang:"js",
    returns::INTEGER,
    code:"return 1;"
);

$ret_long()=CREATE FUNCTION(
    lang:"js",
    returns::LONG,
    code:"return 1;"
);

$ret_float()=CREATE FUNCTION(
    lang:"js",
    returns::FLOAT,
    code:"return 1.1;"
);

$ret_double()=CREATE FUNCTION(
    lang:"js",
    returns::DOUBLE,
    code:"return 1.1;"
);

$ret_boolean()=CREATE FUNCTION(
    lang:"js",
    returns::BOOLEAN,
    code:"return true"
);

$ret_string()=CREATE FUNCTION(
    lang:"js",
    returns::STRING,
    code:"return 'Hello world!'; "
);

TIMESTAMP

Return JavaScript Date object or numeric value, representing the number of milliseconds since the beginning of Unix timestamp (Epoch) <https://en.wikipedia.org/wiki/Unix_time>, to convert to the TIMESTAMP type (with millisecond precision).

Note

When working with Unix timestamp value (seconds) you must multiply it by 1000 to convert it to milliseconds

1
2
3
4
5
6
7
8
9
$ret_timestamp_ts()=CREATE FUNCTION(
    lang:"js",
    code:"return new Date()",
    returns::TIMESTAMP);

$ret_timestamp_long()=CREATE FUNCTION(
    lang:"js",
    code:"return 1510569553403",
    returns::TIMESTAMP);
where:
  • line 3: returned Date object is converted to TIMESTAMP
  • line 8: returned numeric value is converted to TIMESTAMP

TIMESTAMP_NANO

Return Javascript TimestampNano object or numeric value representing nanoseconds from the beginning of Unix time to convert to TIMESTAMP_NANO.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$ret_timestamp_nano_tsnano()=CREATE FUNCTION(
    lang:"js",
    code:"return new TimestampNano(1510569553403000064)",
    returns::TIMESTAMP_NANO
);

$ret_timestamp_nano_long()=CREATE FUNCTION(
    lang:"js",
    code:"return 1510569553403000064",
    returns::TIMESTAMP_NANO
);
where:

IPADDR

Return the Javascript IpAddr object (representing both IPv4 and IPv6 addresses) to convert to IPADDR.

Alternatively, return an integer value representing an IPv4 address or a string representing the IPv6 address.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ret_ipaddr_from_int(addr::INTEGER)=CREATE FUNCTION(
    lang:"js",
    code:"return new IpAddr(addr);",
    returns::IPADDR
);

$ret_ipaddr()=CREATE FUNCTION(
    lang:"js",
    code:"return -1392442369;",
    returns::IPADDR
);

$ret_ipv6addr_from_str(ipv6addrStr::STRING)=CREATE FUNCTION(
    lang:"js",
    code:"return IpAddr.parse(ipv6addrStr);",
    returns::IPADDR
);

$ret_ipv6addr()=CREATE FUNCTION(
    lang:"js",
    code:"return '0102:0304::f1f2'; ",
    returns::IPADDR
);

IPNET

Return the Javascript IpNet object (representing both IPv4 and IPv6 net blocks) to convert to IPNET.

Alternatively, you can return a string value representing the IPv4 or IPv6 network.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ret_ipnet_from_int(addr::INTEGER, mask::INTEGER)=CREATE FUNCTION(
    lang:"js",
    code:"return new IpNet(addr, mask);",
    returns::IPNET
);

$ret_ipnet_from_longs(low::LONG, high::LONG, mask::INTEGER)=CREATE FUNCTION(
    lang:"js",
    code:"return new IpNet(high, low, mask);",
    returns::IPNET
);

$ret_ipv6net()=CREATE FUNCTION(
    lang:"js",
    code:"return '0102:0304::f1f2/24'; ",
    returns::IPNET
);

IPSOCKET

Return the Javascript IpSocket object (representing both IPv4 and IPv6 sockets) to convert to IPSOCKET.

Alternatively, you can return a string value representing the IPv4 or IPv6 socket.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ret_ipsocket_from_int_ipv4(addr::INTEGER, port::INTEGER)=CREATE FUNCTION(
    lang:"js",
    code:"return new IpSocket(addr, port);",
    returns::IPSOCKET
);

$ret_ipv6socket_from_long_ipv6(highAddr::LONG, lowAddr::LONG, port::INTEGER)=CREATE FUNCTION(
    lang:"js",
    code:"return new IpSocket(highAddr, lowAddr, port);",
    returns::IPSOCKET
);

$ret_ipv6socket()=CREATE FUNCTION(
    lang:"js",
    code:"return '[0102:0304::f1f2]:8080'; ",
    returns::IPSOCKET
);

MACADDR

Return Javascript MacAddr object to convert to MACADDR.

Alternatively, you can return a numeric value representing the MAC address.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
$ret_macaddr_from_long(addr::LONG)=CREATE FUNCTION(
    lang:"js",
    code:"return new MacAddr(addr);",
    returns::MACADDR
);

$ret_macaddr_from_str(addr::STRING)=CREATE FUNCTION(
    lang:"js",
    code:"return MacAddr.parse(addr);",
    returns::MACADDR
);

$ret_macaddr()=CREATE FUNCTION(
    lang:"js",
    code:"return 1234;",
    returns::MACADDR
);

GEOPOINT

Return Javascript GeoPoint object to convert to GEOPOINT.

Example

1
2
3
4
5
$ret_geopoint(longitude::FLOAT, latitude::FLOAT)=CREATE FUNCTION(
    lang:"js",
    code:"return new GeoPoint(longitude, latitude);",
    returns::GEOPOINT
);

ARRAY

Use array literal notation to specify type in the returns argument of CREATE FUNCTION directive. The default values of array members can also be specified.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$ret_array()=CREATE FUNCTION (
  lang:'js',
  returns:[ipsocket()],
  code:"return ['127.0.0.1:22',null,IpSocket.parse('127.0.0.1:80')];"
);

$ret_array1()=CREATE FUNCTION (
  lang:'js',
  returns:[],
  code:"return [0,1,2];"
);

TUPLE

Use tuple literal notation to specify type in the returns argument of CREATE FUNCTION directive. All members with their default values must be specified.

Example

1
2
3
4
5
$ret_tuple()=CREATE FUNCTION (
  lang:'js',
  returns:{a:0, b:'Moe', c:0d},
  code:"return {a:1, b:'Homer', c:3.0};"
);

Logging

You can use console methods for logging in query log tab for debugging purposes. It has following methods:

console.log(String arg)
console.debug(String arg)
console.error(String arg)
console.info(String arg)
console.trace(String arg)
console.warn(String arg)

to log with different log levels.

Input Arguments

Input arguments can be declared either by type, with the default value or leaving it indefinite.

1. Declaring an argument with explicit type:

arg_name::type

Assigning type to an argument will enforce a runtime type check at compile.

Example

1
$example_function(a::INT, b::STRING, c::BOOLEAN) = ...

2. Declaring an argument with default value:

arg_name:expr

Declaring argument with the default value allows omitting that parameter at calling.

Example

1
$example_function(x:"default") = ...

3. Declaring an argument with indefinite type:

arg_name

Declaring argument as indefinite allows specifying different types of arguments at calling.

Example

1
$example_function(y) = ...