machbase-neo
TQL
SCRIPT()

SCRIPT()

Syntax: SCRIPT(language, [{init_code},] {main_code})

  • language : specify the script language (set "js" for Javascript)
  • init_code : initialize code (optional)
  • main_code : script code

The init_code is optional and runs only once at the beginning. The main_code is mandatory and cannot be omitted.

Javascript

TQL supports the SCRIPT() function, which utilizes JavaScript (ECMA5) in both SRC and MAP contexts Since v8.0.36 . This feature offers developers the flexibility to use a familiar programming language, enhancing their ability to create more dynamic and powerful scripts within TQL.

Machbase-neo exposes the $ variable as the context object. JavaScript can access and yield records and database through this context.

  • $.payload Input data of the request.
  • $.params Input query parameters of the request.
  • $.result Specifies the names and types of the result columns yielded by the SCRIPT() function.
  • $.key, $.values Javascript access point to the key and values of the current record. It is only available if the SCRIPT() is a MAP function.
  • $.yield() Yield a new record with values
  • $.yieldKey() Yield a new record with key and values
  • $.db() Returns a new database connection.
  • $.db().query() Execute SQL query.
  • $.db().exec() Execute non-SELECT SQL.
  • $.request().do() Request HTTP to the remote server.

Caveat Emptor

  • use strict does nothing.

  • ECMA5 only. Some ES6 features e.g. Typed Arrays and back-tick string interpolation are not supported.

  • Regular expression is not fully compatible with the ECMA5 specification. The following regular expression syntax is incompatible:

    • (?=) Lookahead (positive), it produces a parsing error
    • (?!) Lookahead (backhead), it produces a parsing error
    • \1,\2,\3, … Backreference, it produces a parsing error

$.payload

JavaScript can access the input data of the request using $.payload. If there is no input data, $.payload will be undefined.

1
2
3
4
5
6
7
8
9

SCRIPT("js", {
    var data = $.payload;
    if (data === undefined) {
        data = '{ "prefix": "name", "offset": 0, "limit": 10}';
    }
    var obj = JSON.parse(data);
    $.yield(obj.prefix, obj.offset, obj.limit);
})
CSV()

Call the tql file without any request body which makes the $.payload is undefined.

curl -o - -X POST http://127.0.0.1:5654/db/tql/test.tql

Then the result is the default values: name,0,10.

Call the tql file with a custom data.

curl -o - -X POST http://127.0.0.1:5654/db/tql/test.tql \
-d '{"prefix":"testing", "offset":10, "limit":10}'

Then the result is: testing,10,10

$.params

JavaScript can access the request’s query parameters using $.params. The value of a query parameter can be accessed in two ways: using dot notation ($.params.name) or bracket notation ($.params["name"]). Both forms are interchangeable and can be used based on the context or preference.

1
2
3
4
5
6
7

SCRIPT("js", {
    var prefix = $.params.prefix ? $.params.prefix : "name";
    var offset = $.params.offset ? $.params.offset : 0;
    var limit = $.params.limit ? $.params.limit: 10;
    $.yield(obj.prefix, obj.offset, obj.limit);
})
CSV()

Call the tql file without parameters.

curl -o - -X POST http://127.0.0.1:5654/db/tql/test.tql

The result will be the default values: name,0,10.

Call the tql file with parameters.

curl -o - -X POST "http://127.0.0.1:5654/db/tql/test.tql?"\
"prefix=testing&offset=12&limit=20"

The result is: testing,12,20.

$.result

Specifies the type of result data that the SCRIPT function yields. It works within the init code section, as shown in the example below.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SCRIPT("js", {
    $.result = {
        columns: ["val", "sig"],
        types: ["double", "double"] 
    }
},{
    for (i = 1.0; i <= 5.0; i+=0.03) {
        val = Math.round(i*100)/100;
        sig = Math.sin( 1.2*2*Math.PI*val );
        $.yield( val, sig );
    }
})
JSON()

$.key

Access the key of the current record. This is defined only when SCRIPT is used as a MAP function. If SCRIPT is used as an SRC function, it will be undefined.

1
2
3
4
5
6
7
8
9

SCRIPT("js", {
    for( i = 0; i < 3; i++) {
        $.yieldKey(i, "hello-"+(i+1));
    }
})
SCRIPT("js", {
    $.yieldKey($.key, $.values[0], 'key is '+$.key);
})
CSV()
hello-1,key is 0
hello-2,key is 1
hello-3,key is 2

$.values

Access the values of the current record. This is defined only when SCRIPT is used as a MAP function. If SCRIPT is used as an SRC function, it will be undefined.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SCRIPT("js", {
        $.yield("string", 10, 3.14);
})
SCRIPT("js", {
    $.yield(
        "the first value is "+$.values[0],
        "2nd value is "+$.values[1],
        "3rd is "+$.values[2]
    );
})
CSV()

The output is:

the first value is string,2nd value is 10,3rd is 3.14

$.yield()

Yield the new record to the next step, with the key automatically assigned as a sequentially increasing number.

$.yieldKey()

yieldKey() functions similarly to $.yield(), with the exception that the first argument specifies the key of the record.

$.db()

Returns a new database connection. The connection provides query(), exec() functions.

If the option object is specified as a parameter, for example, $.db({bridge: "sqlite"}), it returns a new connection to the bridged database instead of the machbase database.

option

The option parameter is supproted Since v8.0.37

{
    bridge: "name", // bridge name
}

$.db().query()

JavaScript can query the database using $.db().query(). Apply a callback function with forEach() to the return value of query() to iterate over the query results.

If the callback function of .forEach() explicitly returns false, the iteration stops immediately. If the callback function returns true or does not return anything (which means it returns undefined), the iteration continues until the end of the query result.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SCRIPT("js", {
    var data = $.payload;
    if (data === undefined) {
        data = '{ "tag": "name", "offset": 0, "limit": 5 }';
    }
    var obj = JSON.parse(data);
    $.db().query("SELECT name, time, value FROM example WHERE name = ? LIMIT ?, ?",
        obj.tag, obj.offset, obj.limit
    ).forEach( function(row){
        name = row[0]
        time = row[1]
        value = row[2]
        $.yield(name, time, value);
    })
})
CSV()
cpu.percent,1725330085908925000,73.9
cpu.percent,1725343895315420000,73.6
cpu.percent,1725343898315887000,6.1
cpu.percent,1725343901314955000,10.8
cpu.percent,1725343904315952000,40
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SCRIPT("js", {
    var data = $.payload;
    if (data === undefined) {
        data = '{ "tag": "testing", "offset": 0, "limit": 5 }';
    }
    var obj = JSON.parse(data);
    $.db({bridge:"mem"})
     .query("SELECT name, time, value FROM example WHERE name = ? LIMIT ?, ?",
        obj.tag, obj.offset, obj.limit
    ).forEach( function(row){
        name = row[0]
        time = row[1]
        value = row[2]
        $.yield(name, time, value);
    })
})
CSV()
testing,1732589744886,16.70559756851126
testing,1732589744886,49.93214293713331
testing,1732589744886,54.485508690434905

$.db().exec()

If the SQL is not a SELECT statement, use $.db().exec() to execute INSERT, DELETE, CREATE TABLE statements.

 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

SCRIPT("js", {
    for( i = 0; i < 3; i++) {
        ts = Date.now()*1000000; // ms to ns
        $.yield("testing", ts, Math.random()*100);
    }
})
SCRIPT("js", {
    // This section contains initialization code
    // that runs once before processing the first record.
    err = $.db().exec("CREATE TAG TABLE IF NOT EXISTS example ("+
        "NAME varchar(80) primary key,"+
        "TIME datetime basetime,"+
        "VALUE double"+
    ")");
    if (err instanceof Error) {
        console.error("Fail to create table", err.message);
    }
}, {
    // This section contains the main code
    // that runs over every record.
    err = $.db().exec("INSERT INTO example values(?, ?, ?)", 
        $.values[0], $.values[1], $.values[2]);
    if (err instanceof Error) {
        console.error("Fail to insert", err.message);
    } else {
        $.yield($.values[0], $.values[1], $.values[2]);
    }
})
CSV()
 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

SCRIPT("js", {
    for( i = 0; i < 3; i++) {
        ts = Date.now(); // ms
        $.yield("testing", ts, Math.random()*100);
    }
})
SCRIPT("js", {
    // This section contains initialization code
    // that runs once before processing the first record.
    err = $.db({bridge:"mem"}).exec("CREATE TABLE IF NOT EXISTS example ("+
        "NAME TEXT,"+
        "TIME INTEGER,"+
        "VALUE REAL"+
    ")");
    if (err instanceof Error) {
        console.error("Fail to create table", err.message);
    }
}, {
    // This section contains the main code
    // that runs over every record.
    err = $.db({bridge:"mem"}).exec("INSERT INTO example values(?, ?, ?)", 
        $.values[0], $.values[1], $.values[2]);
    if (err instanceof Error) {
        console.error("Fail to insert", err.message);
    } else {
        $.yield($.values[0], $.values[1], $.values[2]);
    }
})
CSV()

To query a bridged database in the SQL editor, use the -- env: bridge=name notation for the query.

-- env: bridge=mem
SELECT
    name,
    datetime(time/1000, 'unixepoch', 'localtime') as time,
    value
FROM
    example;
-- env: reset

$.request().do()

Syntax: $.request(url [, option]).do(callback)

Request option

{
    method: "GET|POST|PUT|DELETE", // default is "GET"
    headers: { "Authorization": "Bearer auth-token"}, // key value map
    body: "body content if the method is POST or PUT"
}

The actual request is made when .then() is called with a callback function to handle the response. The callback function receives a Response object as an argument, which provides several properties and methods.

Response

PropertyTypeDescription
.okBooleantrue if the status code of the response is success. (200<= status < 300)
.statusNumberhttp response code
.statusTextStringstatus code and message. e.g. 200 OK
.urlStringrequest url
.headersMapresponse headers

The Response object provides useful methods that serves the body content of the response.

MethodDescription
.text(callback(txt))Call the callback with content in a string
.blob(callback(bin))Call the callback with content in a binary array
.csv(callback(row))Parse the content into CSV format and call callback() for each row (record).

Usage

$.request("https://server/path", {
    method: "GET",
    headers: { "Authorization": "Bearer auth-token" }
  }).do( function(rsp){
    console.log("ok:", rsp.ok);
    console.log("status:", rsp.status);
    console.log("statusText:", rsp.statusText);
    console.log("url:", rsp.url);
    console.log("Content-Type:", rsp.headers["Content-Type"]);
});

finalize()

If SCRIPT("js") contains function finalize(){} function in the Javascript, it will be called by system at the end after all records are processed.

FAKE( arrange(1, 3, 1) )
SCRIPT("js", {
    function finalize() {
        $.yield(999);
    }
    $.yield($.values[0]);
})
CSV()

This example yields 4 records: 1,2,3,999.

Examples

Hello World 

SCRIPT("js", {
    console.log("Hello World?");
})
DISCARD()

Result

Builtin Math object

Javascript builtin functions are available:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

FAKE(meshgrid(linspace(0,2*3.1415,30), linspace(0, 3.1415, 20)))

SCRIPT("js", {
  x = Math.cos($.values[0]) * Math.sin($.values[1]);
  y = Math.sin($.values[0]) * Math.sin($.values[1]);
  z = Math.cos($.values[1]);
  $.yield([x,y,z]);
})

CHART(
  plugins("gl"),
  size("600px", "600px"),
  chartOption({
    grid3D:{}, xAxis3D:{}, yAxis3D:{}, zAxis3D:{},
    visualMap:[{  min:-1, max:1, 
      inRange:{color:["#313695",  "#74add1", "#ffffbf","#f46d43", "#a50026"]
    }}],
    series:[ { type:"scatter3D", data: column(0)} ]
  })
)

The equivalent result using SET-MAP functions instead of Javascript is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

FAKE(meshgrid(linspace(0,2*3.1415,30), linspace(0, 3.1415, 20)))

SET(x, cos(value(0))*sin(value(1)))
SET(y, sin(value(0))*sin(value(1)))
SET(z, cos(value(1)))

MAPVALUE(0, list($x, $y, $z))
POPVALUE(1)

CHART(
  plugins("gl"),
  size("600px", "600px"),
  chartOption({
    grid3D:{}, xAxis3D:{}, yAxis3D:{}, zAxis3D:{},
    visualMap:[{  min:-1, max:1, 
      inRange:{color:["#313695",  "#74add1", "#ffffbf","#f46d43", "#a50026"]
    }}],
    series:[ { type:"scatter3D", data: column(0)} ]
  })
)

Result

JSON parser 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SCRIPT("js", {
    $.result = {
        columns: ["NAME", "AGE", "IS_MEMBER", "HOBBY"],
        types: ["string", "int32", "bool", "string"],
    }
},{
    content = $.payload;
    if (content === undefined) {
        content = '{"name":"James", "age": 24, "isMember": true, "hobby": ["book", "game"]}';
    }
    obj = JSON.parse(content);
    $.yield(obj.name, obj.age, obj.isMember, obj.hobby.join(","));
})
JSON()

Result

{
    "data": {
        "columns": [ "NAME", "AGE", "IS_MEMBER", "HOBBY" ],
        "types": [ "string", "int32", "bool", "string" ],
        "rows": [ [ "James", 24, true, "book,game" ] ]
    },
    "success": true,
    "reason": "success",
    "elapse": "627.958µs"
}

Request CSV 

 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

SCRIPT("js", {
    $.result = {
        columns: ["SepalLen", "SepalWidth", "PetalLen", "PetalWidth", "Species"],
        types: ["double", "double", "double", "double", "string"]
    };
},{
    $.request("https://docs.machbase.com/assets/example/iris.csv")
     .do(function(rsp){
        console.log("ok:", rsp.ok);
        console.log("status:", rsp.status);
        console.log("statusText:", rsp.statusText);
        console.log("url:", rsp.url);
        console.log("Content-Type:", rsp.headers["Content-Type"]);
        if ( rsp.error() !== undefined) {
            console.error(rsp.error())
        }
        var err = rsp.csv(function(fields){
            $.yield(fields[0], fields[1], fields[2], fields[3], fields[4]);
        })
        if (err !== undefined) {
            console.warn(err);
        }
    })
})
CSV(header(true))

Request JSON text

This example demonstrates how to fetch JSON content from a remote server and parse it using Javascript.

 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

SCRIPT("js", {
    $.result = {
        columns: ["ID", "USER_ID", "TITLE", "COMPLETED"],
        types: ["int64", "int64", "string", "boolean"]
    };
},{
    $.request("https://jsonplaceholder.typicode.com/todos")
     .do(function(rsp){
        console.log("ok:", rsp.ok);
        console.log("status:", rsp.status);
        console.log("statusText:", rsp.statusText);
        console.log("URL:", rsp.url);
        console.log("Content-Type:", rsp.headers["Content-Type"]);
        if ( rsp.error() !== undefined) {
            console.error(rsp.error())
        }
        rsp.text( function(txt){
            list = JSON.parse(txt);
            for (i = 0; i < list.length; i++) {
                obj = list[i];
                $.yield(obj.id, obj.userId, obj.title, obj.completed);
            }
        })
    })
})
CSV(header(false))
Last updated on
Utility FunctionsGROUP()