Basics

Primitive types

TQL has four primitive types: string, number, boolean and time.

string

Define constant strings with quotation marks, as in traditional programming languages: single (’), double (") and backtick. Braces can also enclose a string. A backtick string is useful when you need to define a string in multiple lines that includes quotation marks, such as a long SQL statement.

When multi-line content contains another backtick or brace characters ({, }), use tagged raw literals to avoid boundary conflicts.

• Tagged backtick: `<<TAG ... TAG`
• Tagged brace block: {<<TAG ... TAG}

Both forms treat the body as raw text and close at the tagged closing line.

Example) Escaping single quote with backslash(\')

1
2

SQL( 'select * from example where name=\'temperature\' limit 10' )
CSV()

Example) Double quote string

1
2

SQL( "select * from example where name='temperature' limit 10" )
CSV()

Example) Use multi-lines sql statement without escaping by backtick(`)

1
2
3
4
5

SQL( `select *
      from example
      where name='temperature'
      limit 10` )
CSV()

1
2
3
4
5

SCRIPT(`<<JS
// this is a function return '{'
function a () { return '{' }
JS`)
CSV()

1
2
3
4
5
6

MARKDOWN({<<MD
```mermaid
erDiagram
    CUSTOMER ||--o{ ORDER :places
```
MD})

There is a convenient way to specify a JSON string in a TQL script by using double braces ({{ }}). It doesn’t require escaping quotation marks.

The two string expressions used below are equivalent.

1
2
3
4
5
6
7
8
9

STRING({{
    "name": "Connan",
    "hired": true,
    "company": {
        "name":"acme",
        "employee": 123
    }
}})
CSV()

1
2
3
4
5
6
7
8
9

STRING(`{
    "name": "Connan",
    "hired": true,
    "company": {
        "name":"acme",
        "employee": 123
    }
}`)
CSV()

number

TQL treats all numeric constants as 64bit floating point numbers.

1
2

SQL_SELECT( 'time', 'value', from('example', 'temperature'), limit(10))
CSV()

1
2

FAKE( oscillator( freq(12.34, 20), range("now", "1s", "100ms")) )
CSV()

boolean

The boolean constants are true and false.

1
2

FAKE( linspace(0, 1, 1))
CSV( heading(false) )

time

Time type values can be created by calling time(), parseTime() functions, or retrieved from a DATETIME column of a SQL query result.

timeZone

TimeZone type values can be created by calling tz() function.

ex) tz('UTC'), tz('Local'), tz('Asia/Seoul')

list

A list is an array of other values, it can be created by calling list() function.

ex) list(1, 2, 3)

dictionary

A dictionary is a set of (string) name and value pairs, created by calling dict() function.

ex) dict("name", "pi", "value", 3.14)

Statements

Every statement in TQL should be a function call except the literal constants of string, number and boolean.

// A comment line starts with '//'
SQL_SELECT(
    'time', 'value',
    from('example', 'temperature'),
    limit(10)
)
CSV()

SRC and SINK

Every .tql script should start with one source (SRC) statement which generates a record or records. For example, SQL(), SQL_SELECT() and SCRIPT() that generates records with $.yield(), $.yieldKey() can be a source. And the last statement should be a sink (SINK) statement that encodes the result or writes it into the database. For example, CSV(), JSON(), INSERT(), APPEND() and all CHART() functions can be a sink.

MAP functions

There may be zero or more map functions between source and sink statements. The names of all map functions are in capital letters; in contrast, lower case camel notation functions are used as arguments of the other map functions.

1
2
3
4
5
6
7
8

SQL_SELECT(
    'time', 'value',
    from('example', 'temperature'),
    limit(10)
)
DROP(5)
TAKE(5)
CSV()

Param

When external applications call a .tql script via HTTP, they can provide arguments as query parameters. The function param() retrieves the values of the query parameters in a TQL script.

If the script below is saved as hello2.tql, applications can call it by HTTP GET method with http://127.0.0.1:5654/db/tql/hello2.tql?name=temperature&count=10. Then param('name') returns "temperature" and param('count') returns the string "10".

1
2
3
4
5
6

SQL_SELECT(
    'time', 'value',
    from('example', param('name')),
    limit( param('count') )
)
CSV()

Example

SQL( `select * from example where name = ?`, param('name'))
CSV()

curl "http://127.0.0.1:5654/db/tql/param.tql?name=TAG0"

Operators

Arithmetic Operators

The arithmetic operators perform addition +, subtraction -, multiplication *, division / operations.

1
2
3

FAKE(linspace(1, 10, 5))
MAPVALUE( 1, value(0) * 100 )
CSV()

1,100
3.25,325
5.5,550
7.75,775
10,1000

Modulo Operator

The modulo operator (also known as the modulus operator), denoted by %, is an arithmetic operator. It produces the remainder of an integer division.

1
2
3

FAKE(arrange(1, 10, 1))
FILTER(value(0) % 3 == 0)
CSV()

3
6
9

Concatenation

If operator + takes strings as its operands, it returns the concatenated string.

1
2
3
4
5

FAKE(json({
    ["hello", "world"]
}))
MAPVALUE(2, value(0) + " " + value(1) + "?")
CSV()

hello,world,hello world?

Relational Operator

1
2
3

FAKE(linspace(1, 5, 5))
FILTER( value(0) >= 4 )
CSV()

4
5

Logical Operators

Logical operators perform and, or, and not operations.

1
2
3

FAKE(linspace(1, 5, 5))
FILTER( value(0) > 0  && mod(value(0), 2) == 0 )
CSV()

2
4

IN Operator

A in (args...) returns true if the args contains A, otherwise it returns false.

1
2
3
4
5
6
7
8

FAKE(json({
    ["A", 1.0],
    ["B", 1.5],
    ["C", 2.0],
    ["D", 2.5]
}))
FILTER( value(0) in ("A", "C") )
CSV()

1
2
3
4
5
6
7
8

FAKE(json({
    ["A", 1.0],
    ["B", 1.5],
    ["C", 2.0],
    ["D", 2.5]
}))
FILTER( value(1) in (1.5, 2.5) )
CSV()

Ternary Operator

The ternary operator ? : is similar to the if-else statement in other programming languages, as it selects a value by the same logic as an if-else statement.

• Whether param('name') is defined

1
2
3
4
5
6
7
8

SQL_SELECT(
    'time', 'value',
    from('example',
        param('name') == NULL ? 'temperature' : param('name')
    ),
    limit( param('count') ?? 10 )
)
CSV()

• Conditional value changes

1
2
3

FAKE(linspace(1, 5, 5))
MAPVALUE(0, mod(value(0), 2) == 0 ? value(0)*10 : value(0))
CSV()

1
20
3
40
5

Nil coalescing

The ?? operator takes a left and a right operand. If the left operand is defined, it returns its value; otherwise it returns the right operand. The example below shows the common use case of the ?? operator. If the caller did not provide query parameters, the right operand is taken as the default value.

1
2
3
4
5
6

SQL_SELECT(
    'time', 'value',
    from('example', param('name') ?? 'temperature'),
    limit( param('count') ?? 10 )
)
CSV()

When a TQL script is saved, the editor shows the link icon on the top right corner. Click it to copy the address of the script file.

Example

SQL( `select * from example limit ?`, param('limit') ?? 1)
CSV()

curl http://127.0.0.1:5654/db/tql/param-default.tql

TAG0,1628694000000000000,10

curl http://127.0.0.1:5654/db/tql/param-default.tql?limit=2

TAG0,1628694000000000000,10
TAG0,1628780400000000000,11

Pragma

The //+ name=value directive provides instructions on how Machbase Neo should execute the TQL script.

log-level

Set the log-level to one of [TRACE | DEBUG | INFO | WARN | ERROR]. The default is ERROR, which suppresses most log messages when called from HTTP and MQTT APIs.

1
2
3
4

//+ log-level=TRACE
SQL(`select * from my_table where name = ?`, param("name"))
WHEN(true, doLog('hello world'))
CSV()

sql-thread-lock

This pragma ensures that the specified SQL() runs on a dedicated native thread, which is terminated once the TQL script completes. It works only with the SRC SQL().

According to internal performance tests with a hundred simultaneous HTTP client requests to execute the TQL file, enabling this option increases response latency by 35% but significantly reduces memory release delay.

1
2
3
4

//+ sql-thread-lock
SQL(`select * from my_table where name = ?`, param("name"))
WHEN(true, doLog('hello world'))
CSV()