Scripting

A query script is composed of:

  • one or more declarations of a query statement or expression,
  • an execution statement,
  • one or more INCLUDE directives (optional) and
  • an INIT block (optional)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
INIT(
  input_arg:'bar',
  _config_param:'conf_value'
);

/* next line is commented out */
//INCLUDE './path/to/script.sx';

$local_const = 30;
$local_expression = 'foo ' + UPPER($input_arg);
@generate_fields=dual(5) | select(i);

@generate_fields
| select(i, $input_arg, $local_const, $local_expression, config('query.config_param'))
| select(*, pi:i+0.14)
| limit(3)
;
where:
  • line 1 defines INIT block
  • line 2 defines input argument named ‘input_arg’ with initial string value ‘bar’
  • line 3 assigns to Query Configuration parameter ‘_query.config_param’ initial string value ‘conf_value’
  • line 6 has a comment (can be applied to multiple lines too)
  • line 7 has a commented out INCLUDE directive
  • line 9 declares a constant variable
  • line 10 declaration of an user defined function
  • line 11 declaration of a query using DUAL and select
  • lines 13-16 execution statement followed by query process commands

Glossary

query stream
A stream of structured data (collection of tuples) passed between query commands.
statement
Any single or pipelined query command or function returning query stream

Syntax

Statements in SXQL script must be terminated with a semicolon (“;”).

A statement can be written on several lines to increase readability. The line feeds and insignificant whitespaces are ignored by interpreter.

Declarations can be written in any order, i.e it has no effect on the sequence of execution, which is determined solely by their actual dependencies.

Two comment styles are supported for inserting comments:

  1. Multi-line:

    /*
    comment
    comment
    */
    
  2. Single line:

    // comment
    

Declarations

Return Type

From query processing standpoint it is important to know beforehand (i.e at compile time) if the return value is a structured query stream or a single value. Therefore the return type must be specified at declaration:

  1. expression variable names (returning single value) must be prepended by dollar “$” character:
$variable_name = expression;
  1. stream variable names must be prepended by “@” character:
@variable_name = statement;

Scope

Variables declared outside of the INIT block are visible inside the script, i.e they are local variables.

Variables declared within the INIT block are script input arguments, i.e their value can be specified by the calling statement.

Naming Conventions

A variable name must begin with a letter. Subsequent characters can be letters, numbers or underscore ([a-zA-Z][a-zA-Z0-9_]*).

A variable name must be unique in the scope of a script (and included scripts).

Example

1
2
3
$local_const = 30;
$local_expression = 'foo ' + UPPER($input_arg);
@generate_fields=dual(5) | select(i);
where line:
  1. declaration of a local variable pointing to a constant expression
  2. declaration of a local variable pointing to a string concatenation expression
  3. declaration of a query statement

Execution

A query script gets executed by calling a statement declaration - called the execution statement. This can be either record stream creation command or reference to declared (or included) stream type variable.

1
2
3
4
5
@generate_fields
| select(i, $public_arg, $private_arg, $private_const, config('query.config_param'))
| select(*, pi:i+0.14)
| limit(3)
;

There can be only one execution statement in a script. Other declared streams and expressions may depend on it, in which case they are executed in their respective dependency order.

Note

A script may have no execution statement. In this case, the script can not be used as a view. They can only be used for including by other scripts.

Execution of a script takes place in the following sequence:

  1. Initialization (if present).
  2. The execution dependency tree is constructed
  3. Statements are executed in the order dictated by the dependency tree

Initialization

The INIT directive allows declaring input arguments and initializing Query Configuration parameter values.

The INIT directive must be the first statement of a script. Parameters are separated by a comma and enclosed in parenthesis. Parameter/argument name and value are separated by a colon “:”.

INIT'(' name':'(value | ':'type) [, ...] ')'
where:
  • name - name of the parameter or input argument. Must begin with a letter or underscore, subsequent characters can be letters, numbers or underscore ([_a-zA-Z][a-zA-Z0-9_]*).
  • value - the initial value of the parameter or argument.
  • type - the data type of the argument prepended with a colon “:”. Makes argument mandatory at calling.

A parameter name beginning with underscore symbol “_” is treated as a Query Configuration value, otherwise as an input argument.

Note

When declaring Query configuration: items:

  • Values can not be declared using type.
  • When specifying namespace then enclose the name in single or double-quotes.
  • Declared parameter names will be renamed during the execution of the query by removing the leading underscore symbol.
  • Parameter names without namespace are mapped to ‘query’.

Declaring an input argument with value makes it optional when calling script. Declaring an input argument with a type makes it mandatory when calling script.

Example

1
2
3
4
5
6
7
INIT(
 username:'john',
 min_age::INTEGER,
 _timezone:'GMT',
 '_query.locale':'et'
 '_query.parse.ignoreErrors':true,
);
where:
  • line 2: declares optional input argument ‘username’ with default value ‘john’
  • line 3: declares mandatory input argument ‘min_age’ type ref:ft_integer
  • line 4: specifying configuration parameter name ‘_timezone’ without namespace results in initializing ‘_query.timezone’ to string value ‘GMT’
  • line 5: sets query configuration parameter ‘ignoreErrors’ in namespace ‘query.parse’ to boolean value ‘true’

Including Scripts

Complex and long scripts can be hard to read and understand. Capturing reusable parts of code is beneficial both for readability as well as productivity.

Note

The names of declared variables must remain unique in the scope of current and included scripts!

There are two ways to include a script: automatically or explicitly.

Automatic Include

A script named _include.sx will be included automatically by any script created in the same -or subfolder to it.

For example if you execute script /team/folder/subfolder/myscript.sx then automatically are included (if existing and readable) in this order:

  1. /team/_include.sx
  2. /team/folder/_include.sx
  3. /team/folder/subfolder/_include.sx

You can use this feature for conveniently executing frequently used queries.

Example: declare a stream variable which executes a script providing COVID-19 statistics in the /user/_include.sx:

@covid_infection_rates = @[/user/varia/covid19/infection_rates.sx];

Now, in any of the script created directly in /user directory or in its subdirectories (use CTRL+N shortcut) I can refer to this variable.

Hint

pressing CTRL+SPACE after typing first letters of included variable will reveal drop-down list with its full name.

Explicit Include

The INCLUDE directive brings target script into the visibility scope of current - i.e all declarations become accessible as if they have been declared within the current script. The execution statement of the included script is not executed.

INCLUDE "/path/to/script.sx";
where:
  • The argument “/path/to/script.sx” is string pointing to script file in resource tree.
  • The argument must be enclosed in single or double quotes.
  • The path can be either absolute (starting from root of the resource tree) or relative to the current script location.

Note

The current script must be saved in order to use the relative path (i.e it must have a place in the resource tree).

Example. We have defined a commonly used convenience function for calculating and displaying nicely formatted ratio between two digits in the /shared/utils/tools.sx folder. To use it:

1
2
3
include "/shared/utils/tools.sx";

dual | select($calcRatio(1.1, 10)); // function $calcRatio(value, total) is defined in included script file
calcRatio
11.0%