Queries

SQL (Structured Query Language) is a powerful language for extracting and processing data. Tibbo LinkServer natively supports SQL, making it easy to access its internal data, as well as data collected from hardware devices. The specific SQL dialect supported by LinkServer is called AggreGate Query Language.

AggreGate Query Language is highly integrated into LinkServer. Since all LinkServer internal data is maintained as Data Tables, it's easy to use SQL queries to modify the properties (settings, variables) of various system objects. Data Tables contain actual data, as well as data validation rules, detailed information each field, etc.

Purpose Of The Query Language

AggreGate Query Language has two primary uses:

The first task is performed using a normal data selection ("SELECT") query, creating a Data Table viewable through a LinkServer User Interface (such as AggreGate Client).

For example, you can write a query that shows the traffic for all Device Servers in the system in a single convenient sortable table:

Queries can also present editable results, allowing users to enter data, for example to configure several instances of the same device. So you can use a query to access the settings of all Device Servers in the system and edit them in a single table:

Queries can be also used in different LinkServer facilities. For example, you can set up an alert that will be raised if a query result satisfies some condition (see Hardware Devices in Trouble example in Alerts chapter for details). Query results may be converted into a printable report when they're browsed (e.g. using the Generate Report feature of Data Table Editor in AggreGate Client). Widgets may also be used to present data previously aggregated and filtered by a query.

Administering Queries

Query Configuration

Here is a list of query properties:

These properties may be accessed through the childInfo variable.

How AggreGate Query Language Compares to Normal SQL

There are two primary differences between "normal" SQL used by most modern Database Engines (like MySQL or Oracle) and AggreGate Query Language.

FIRST DIFFERENCE: Tables vs. contexts

Normal SQL uses tables as the primary data source. Every SELECT statement refers to one or more tables from which data is selected. These references appear in the FROM clause.

AggreGate Query Language works with data stored within LinkServer contexts. This data may be accessed by getting context variables (properties) or calling context functions. The value of every context variable is represented by a Data Table, a data structure that has fields, records and cells. These Data Tables are used by the Query Language engine as the tables from which data is selected.

The same method may be used to refer to tables containing the output values of context functions. When a SELECT statement refers to such a table (containing output values of a function), the reference should also include the input parameters for that function. When the query is executed, the function is called with these parameters. That function then generates a data table containing its output, which in turn will be used by the query.

In the Query Language Syntax article, these references to context variables or functions are called contextReference. Every contextReference may have an alias (tableAlias) that is used to refer to it from other parts of the query.

Just like normal SQL, AggreGate Query Language supports nested SELECT statements, where the FROM section of a SELECT statement contains another SELECT statement.

SECOND DIFFERENCE: FIELD references in 'FROM' CLAUSE

The second difference concerns referring to the fields of tables mentioned in the FROM clause.

In normal SQL, a reference to a table field includes two parts: tableAlias.fieldName. The first part is table's name, an alias given to it in the FROM clause or an alias given to the nested SELECT query. The second part is the field's name.

In AggreGate, a reference to a table field consists of three parts. It looks like this: tableAlias.fieldAlias. tableAlias is the alias assigned to the table generated from context's data (see First Difference, above). fieldAlias is in the form entityName$fieldName. entityName is the name of a context variable or function (such as userInfo, which is the "user information" variable of the user context). fieldName is a specific field of that variable or function output (such as firstname, the user's first name, for the userInfo context).

Query Execution Flow

This section describes the step-by-step process of how a query text is used to generate query results.

As stated in the previous chapter, the difference between AggreGate query and a normal SQL query is that it is executed on "virtual" tables generated from context data rather than on "plain" tables.

Query Syntax Overview

The only operation supported in AggreGate Query Language is the data selection operation which is performed with the SELECT keyword. SELECT retrieves data from a specified table, or from multiple related tables, that are created ad hoc when the Context References in the query are processed.

Each query has several clauses:

AggreGate Query Language supports most features of SQL standard selection queries. It has support for nested SELECT queries, SQL aggregation functions used to perform calculations on various data values (COUNT, MIN, MAX, SUM, AVG, etc), and built-in functions (numerical, string, date/time and system).

For a detailed description of AggreGate Query Language syntax, click here.

Context References

A Context Reference looks like this:

contextMask { :contextEntityReference [: ...] }

contextMask is a mask of contexts whose entities (variables or functions) will be used to build tables on which the query will be executed. This mask can be a plain context name (without any wildcards) or resolve to several contexts.

contextEntityReference may point to a context variable, a group of variables or function. It looks like:

{ contextVariableName | contextVariableGroupName.* | contextFunctionName ( contextFunctionParameters ) }

The pipe marks above denote alternatives -- they're not actually included in the contextEntityReference. Here is the simplest example of a context reference:

users.admin:userInfo

this reference points to the value of the userInfo variable from the users.admin context.

The colon (:) mark is used to separate the context mask from the entity. An "entity" is a fancy name for a variable or a function. So, once you specify a mask (i.e, "where" you want to go), you then put a colon and specify the entity (i.e, "what" you want to find out when you get to where you're going). You can even use a wildcard after the colon, to refer to a group of variables (such as user.charlie.deviceservers.ds10:ds_settings.*, which will give you all settings (variables) of the ds10 Device Server which belongs to the user charlie).

Every Context Reference may combine context masks and several entity references (even of different types):

users.*:userInfo:variableGroup.*:status()

This reference points to the value of the userInfo variable, all variables that belong to the variableGroup group and to the output of the status function called without parameters. Values are fetched from all contexts corresponding to the mask users.*, i.e. to the context of all users in the system that are visible with the permission level of the user who executes this query.

Building Source Tables from Context References

When a query is executed, the first thing which happens is that every Context Reference is used to build a single table, on which the  query will be performed. Building of this table is a complex process which includes several steps:

Click here to see where the Context References may appear in Query Language Syntax.

SPECIFYING FUNCTION PARAMETERS

When a context function is called during Context Reference resolution, it usually has to get some input values. These values are specified in a list containing string parameters. The parameters are all in one long string, and are sepearated by commas (","). The list may contain the following value types:

When the parameter list is resolved, it is used to build a Data Table according to the function input format. The value of each cell is composed from the corresponding argument. So, let's say you have a function, func1 (just an arbitrary name), which expects the following parameters:

You would then call it like this: func1('abc', '123', 'xxx', '456'). Note that even the integer values have single quotes. So, since you have two parameters per row, the first two parameters are used to fill the first row. The next two parameters are used to fill the second row, etc.

examples of field references

Let's assume you have two contexts, path.name and path.name2. The path.* context mask matches both.

path.name has two variables:

var1:

var2:

This context also defines the func1 function, which returns the following value:

path.name2 has the same variable names, but with different values:

var1:

var2:

This context also defines a func1 function which returns the following value:

Example 1

Context Reference: path.name:var2

Table built from this simple context reference will be exactly the same as value of var2:

Example 2

Context Reference: path.name:var1:var2

This reference will resolve to a table with three fields, one from the value of var1 and two from the value of var2. There's just one line, because both variables come from the same context.

Example 3

Context Reference: path.*:var1:var2

This reference points to a context mask, so it will result to the table with three fields, like in the previous example, but with two records, one per every context:

Example 4

Context Reference: path.*:func1

This reference points to a single entity (function) with a value containing multiple records, so the resulting table will have the same fields as the function output. The total number of records in the table is five, since two records are provided by the value of func1 in path.name and three records by the value of func1 in path.name2

Example 5

Context Reference: path.*:var1:func1

Since several entities (var1 variable and func1 function) are referred here, the multi-line values of func1 will be put in nested tables. Fields in these nested tables can not be referred using Field References.

Field References

The Context References section describes how context references are used to build the tables on which queries are executed. These tables contain multiple fields that may be referred in different parts of the query, i.e. in the WHERE clause.

Field references may comprise of two or three parts:

1. When a query has just one context reference in the FROM clause, fields in the table that was built from this reference may be referred with a two-part Field References:

contextEntityName$dataTableFieldName

In this Field Reference, contextEntityName is the name of the context variable or function that was used by the single Context Reference in the query. dataTableFieldName is the name of the field in the Record Format of the Data Table containing the value of that entity.

2. If a query has more then one Context Reference, each reference must have an alias so you'd be able to refer to the fields of the table that was built from it. The alias is assigned using the AS keyword:

contextReference AS tableAlias

The generic form of a Field Reference that includes an alias is:

tableAlias.contextEntityName$dataTableFieldName

Click here to see where Field References may appear in Query Language Syntax.

Debugging Queries

Some queries may execute without errors but return unpredictable or unexpected results. Such behaviour may be caused by non-critical errors that occur during query execution. The Query debugging feature helps view these errors. To run a query in debug mode, use the Debug Query action defined in the Query Context. This action lets you browse a query debug report before showing execution result.

The debug report has the following fields:

Here is an example of debug report (screenshot made in AggreGate Client):

Editable Query Results

Results of execution of some queries are editable. This is a unique feature of AggreGate Query Language, that helps collect properties of different objects or settings of hardware devices in a single table (including only the necessary fields, sorted, grouped etc) and edit these properties using a convenient form.

A field in a query result is editable if several conditions are met:

Example 1:

SELECT * FROM users.*:userInfo

Most fields in the result of this query will be editable, because:

Some fields (i.e. "Username" field) will not be editable, because they are not defined as editable in the format of the userInfo variable.

Example 2:

SELECT userInfo$firstname, userInfo$lastname FROM users.*:userInfo

The result of this query will have no editable fields, because fields to be selected are listed explicitly in the SELECT clause and write-back fields are not defined in the query text.

WRITE-BACK FIELDS

There is a special way to select a pre-defined number of fields by explicitly listing them in the SELECT clause and still make these fields editable. To do this, you have to add three so-called "write-back" fields to the list of Field References listed in the SELECT clause. These fields help the query processing engine figure out where the information contained in the editable query results should be stored when the modified result is saved.

The format of these write-back field references is as follows:

tableAlias.CONTEXT_ID, tableAlias.PARENT_ID, tableAlias.RECORD_INDEX

where tableAlias is name of alias given to the Context Reference in the FROM clause, CONTEXT_ID, PARENT_ID and RECORD_INDEX are predefined constants. Just put them in the query text as-is, without modifying anything.

These write-back fields must be always used all together.

Example:

Assuming we have a query that shows statistics of traffic generated by all Device Servers accessible by the user executing it:

SELECT

info.deviceServerInfo$owner,

info.deviceServerInfo$name,

info.deviceServerInfo$description,

info.deviceServerInfo$blocked,

info.status$servertods,

info.status$dstoserver,

FROM

users.*.deviceservers.*:status:deviceServerInfo as info

Result of this query is not editable, because selected fields are explicitly listed in the SELECT clause:

To make it editable, we should add write-back fields to SELECT clause:

SELECT

info.deviceServerInfo$owner,

info.deviceServerInfo$name,

info.deviceServerInfo$description,

info.deviceServerInfo$blocked,

info.status$servertods,

info.status$dstoserver,

info.CONTEXT_ID,

info.PARENT_ID,

info.RECORD_INDEX

FROM

users.*.deviceservers.*:status:deviceServerInfo as info

This correction will make Device Server description and its "blocked" status editable in the query result. Other fields will remain read-only, since they are defined as read-only in the format of the deviceServerInfo ("Device Server Info") and status ("Device Server Status") variables:

If some columns are not editable due to the absence of write-back fields, this is indicated in the query debug report:

Parameterized Queries

Parameterized queries are used to specify some parameters during query execution. Source data for the parameterization engine is written XML format, rather than in AggreGate Query Language. When the Parameterized option is enabled in the query settings, the query engine considers the query text as parameterization data. See Parameterization Engine for more info.

Here is an example of parameterization data:

Query Text:

<form>

<format>

  <![CDATA[

        <<byusername><B><D=Filter by User Name>>

        <<username><S><D=User Name>>

  ]]>

</format>

<expression>

  SELECT * FROM users.*:userInfo <p enabled="{byusername}">WHERE userInfo$username LIKE '%<e>{username}</e>%'</p>

</expression>

</form>

This query will ask for two parameters upon execution:

If the user checks "Filter by User Name", final parameterized query text will be

SELECT * FROM users.*:userInfo WHERE userInfo$username LIKE '%text_entered_in_User_Name_field%'

i.e. only users whose usernames include the specified text will be shown.

If "Filter by User Name" is not checked, all users will be shown because parameterized query text will be

SELECT * FROM users.*:userInfo

Built-In Queries

Several queries are built into LinkServer and appear under every user account:

These queries are described below, in the Examples chapter.

SQL References

For more information on SQL language check the following links:

http://en.wikipedia.org/wiki/SQL

http://www.w3schools.com/sql/sql_select.asp

http://sqlzoo.net/

http://www.fluffycat.com/SQL/

http://www.baycongroup.com/tocsql.htm

Examples

EXAMPLE 1: VIEWING/EDITING USER ACCOUNTS

Viewing or changing various settings of User Accounts is a common administrative task. The query language may help LinkServer administrators to perform bulk changes to several user accounts, or view certain settings for multiple user accounts in a single ordered table.

To access the basic User Account settings we'll use a variable called "userInfo" ("User Information") defined in the User context. The value of this variable has a single record with several fields containing user's first/last name, country etc.

Here is a typical view of the User Info variable:

Now let's use one of the simplest possible queries to get the value of this variable from just one context.

Query Text:

SELECT * FROM users.admin:userInfo

Result of this query is a table showing the value of the userInfo variable defined in the users.admin context.

This query contains two clauses: SELECT and FROM. The SELECT clauses says that we should select all fields ("*"). The FROM clause contains a single Context Reference ("users.admin:userInfo") used to build a table on which the query will be executed. The contents and format of this table will match the value of the userInfo variable exactly.

All fields in the result of this query will be editable except for the fields that are defined as read-only in the format of userInfo variable.

Query Result:

Now let's select the properties of all users in the system:

Query Text:

SELECT

*

FROM

users.*:userInfo

The Context Reference (users.*:userInfo) contained in the text of this query is actually a mask of contexts. During query execution this mask will resolve to the list of all user contexts accessible by the user executing the query. See Context References chapter for more info.

This query results in a table with the values of the userInfo variable of every user account accessible by the user who is executing the query. It will probably contain multiple records, one per every user account. The result of this query will also be editable.

This query is built-in to the LinkServer distribution. It is called All Users.

Query Result:

For example, you can now change the first name and last name of some user and save the changes:

Changes will be immediately visible in other parts of the system:

Now let's sort the table.

Query Text:

SELECT

*

FROM

users.*:userInfo order by userInfo$username desc

This query outputs almost the same table as the previous one, but the rows in this table are sorted by Username (username field in the format of the userInfo variable) in descending order. The query contains a two-component Field Reference (userInfo$username), used to refer to a field within the table built when resolving the Context Reference users.*:userInfo.

Query Result:

We can edit the query and assign an alias, info, to our Context Reference, and then use a three-component Field Reference that includes this alias:

SELECT

*

FROM

users.*:userInfo as info

ORDER BY

info.userInfo$username desc

The result of this query is exactly like the previous one.

Now let's select just a limited number of fields by further modifying the same query.

Query Text:

SELECT

userInfo$username, userInfo$firstname, userInfo$lastname

FROM

users.*:userInfo

ORDER BY

userInfo$username desc

This query contains a number of Field References in the SELECT clause. Since the fields are specified explicitly, the query result will be not editable, unless we add the write-back fields. We'll add them to this query in one of the next examples.

The result of this query will contain just three columns.

Query Result:

Let's now use a Query Language expression to show the first and last name of users in a single column, to make browsing more convenient. We'll also add a new column to the query result, user's country.

Query Text:

SELECT

userInfo$username,

CASEWHEN((userInfo$firstname IS NULL), '', userInfo$firstname)  || ' ' || CASEWHEN((userInfo$lastname IS NULL), '', userInfo$lastname),

userInfo$country

FROM

users.*:userInfo

ORDER BY

userInfo$username desc

This query selects three fields from the source table. The first and third fields contain the user's username and country respectively. The value of the second field is calculated using an expression. This expression gets a user's first name and appends a space and the user's last name to it. The double pipe marks (||) used are just a standard SQL operator, used for concatenating the strings. The first and last names are transformed using the CASEWHEN built-in function to make sure that an empty string is shown if value of the first or last name is NULL ("<Not set>"). Note that the name for the second column is generated automatically.

Query Result:

Now let's assign a custom name ("label") to the second column to make the result more readable.

Query Text:

SELECT

userInfo$username,

CASEWHEN((userInfo$firstname IS NULL), '', userInfo$firstname)  || ' ' || CASEWHEN((userInfo$lastname IS NULL), '', userInfo$lastname)        as name,

userInfo$country

FROM

users.*:userInfo

ORDER BY

userInfo$username desc

Second column is now called "name" in query result.

Query Result:

Now let's make it possible to change the users' countries -- add write-back fields to make the result editable. To add them we should assign an alias to the Context Reference ("table alias"), because write-back fields (CONTEXT_ID, PARENT_ID and RECORD_INDEX) have to be specified for a given context reference. To specify which reference should be written back, you have to use an alias. Carefully read the example below to figure it all out.

Query Text:

SELECT

info.userInfo$username,

CASEWHEN((info.userInfo$firstname IS NULL), '', info.userInfo$firstname)  || ' ' || CASEWHEN((info.userInfo$lastname IS NULL), '',        info.userInfo$lastname) as name,

info.userInfo$country,

info.CONTEXT_ID,

info.PARENT_ID,

info.RECORD_INDEX

FROM

users.*:userInfo as info

ORDER BY

info.userInfo$username desc

In the result of this query, Country column will be editable. Username column is still read-only because it is read-only in the source variable ("userInfo"). Name column is read-only because it is calculated using an expression.

Query Result:

Now let's add some filtering rules to the query. As an example, let's select only users whose usernames doesn't contain word "test".

Query Text:

SELECT

info.userInfo$username,

CASEWHEN((info.userInfo$firstname IS NULL), '', info.userInfo$firstname)  || ' ' || CASEWHEN((info.userInfo$lastname IS NULL), '',        info.userInfo$lastname) as name,

info.userInfo$country,

info.CONTEXT_ID,

info.PARENT_ID,

info.RECORD_INDEX

FROM

users.*:userInfo as info

WHERE

info.userInfo$username NOT LIKE '%test%'

ORDER BY

info.userInfo$username desc

This query contains an additional WHERE clause, specifying which records should be included in the result.

Query Result:

The last line of the next example shows the LIMIT clause, used to restrict query result to a specified range of rows.

Query Text:

SELECT

info.userInfo$username,

CASEWHEN((info.userInfo$firstname IS NULL), '', info.userInfo$firstname)  || ' ' || CASEWHEN((info.userInfo$lastname IS NULL), '',        info.userInfo$lastname) as name,

info.userInfo$country,

info.CONTEXT_ID,

info.PARENT_ID,

info.RECORD_INDEX

FROM

users.*:userInfo as info

WHERE

info.userInfo$username NOT LIKE '%test%'

ORDER BY

info.userInfo$username desc

LIMIT 2 OFFSET 1

Result of this query includes just two (LIMIT 2) records (2nd and 3rd) from the result of previous one. We use OFFSET 1 here.  This means we're going to get 2 records (LIMIT 2) starting from the second record (OFFSET 1). The OFFSET clause tells AggreGate  to start taking record from the second record onwards (the first record's offset is 0).

Query Result:

EXAMPLE 2:  VIEWING/EDITING Device Server ACCOUNTS

This example is similar to selecting the basic properties of all users as described in the previous example, but here we use the deviceServerInfo variable ("Device Server Information") defined in Device Server context.

Query Text:

SELECT

*

FROM

users.*.deviceservers.*:deviceServerInfo

The text of this query includes a single Context Reference (users.*.deviceservers.*:deviceServerInfo) with a context mask that expands to all Device Server contexts which are accessible with the permissions of the user executing the query. The results of this query are editable, except for the fields that are read-only in the definition of the deviceServerInfo variable.

This query is built-in to the LinkServer distribution. It is called "All Device Servers".

Query Result:

EXAMPLE 3:  VIEWING Device Server TRAFFIC STATISTICS

This example shows the usage of Context References that include multiple variables/fields.

Query Text:

SELECT

info.deviceServerInfo$owner,

info.deviceServerInfo$name,

info.status$servertods,

info.status$dstoserver

FROM

users.*.deviceservers.*:status:deviceServerInfo as info

ORDER BY

info.deviceServerInfo$owner,

info.deviceServerInfo$name

Context Reference in this query ("users.*.deviceservers.*:status:deviceServerInfo") refers two variables defined in Device Server context: "deviceServerInfo" mentioned in previous example and "status" ("Device Server Status") that contains some real-time values related to Device Server functioning. The values of both variables contain just one record, so the table that will be built after resolving this context reference will contain all fields that appear in the values of both variables and one record per every Device Server accessible by the user executing query.

From this table we select four columns: two that appear in the value of deviceServerInfo (Owner and name of Device Server) and two from status (Incoming and outgoing traffic). The table is sorted by two fields: first by Device Server owner, then by Device Server name (because some Device Servers may have the same owner).

This query is built-in to the LinkServer distribution. It is called "Device Server Traffic Statistics".

Query Result:

EXAMPLE 4:  BUZZING DEVICE SERVERS

This example shows how context functions may be used in the query.

Query Text:

SELECT

*

FROM

external_device_servers.*:buzz()

This query is very interesting because it doesn't produce any output, but causes a side-effect on the server. It includes one Context Reference ("external_device_servers.*:buzz()") that involves the buzz() function ("Buzz Device Server") defined in the External Device Server context. This function is called without parameters, as specified by the empty parenthesis. The buzz function causes Device Server to blink its LEDs, helping to locate visually. Thus, this query helps to locate all Device Server that are visible by the LinkServer in the local network segment. It it very important to run the Discover Device Server action before executing this query. This action forces LinkServer to find all local Device Servers by sending broadcast network commands. See External Device Servers for more info.

If discovery was not performed, the External Device Servers context will not have any children, the external_device_servers.*context mask will not resolve to any context and no action will be performed on query execution.

The output of buzz() function contains no fields of records, so this query doesn't return any data. But its execution causes all External Device Server to buzz helping to locate them.

This query is built-in to the LinkServer distribution. It is called "Buzz All External Device Servers".

EXAMPLE 5:  CALCULATING TOTAL TRAFFIC OF ALL DEVICE SERVERS

This example shows how aggregation functions can be used.

Query Text:

SELECT

SUM(status$servertods) as server_to_ds,

SUM(status$dstoserver) as ds_to_server

FROM

users.*.deviceservers.*:status

In one of the previous examples we've mentioned the status variable ("Device Server Status") that is defined in the Device Server context and contains statistics of traffic between LinkServer and the Device Server. This query calculates how many bytes were sent to all currently visible Device Server and how many bytes were received from them. The query result contain one record, because the SUM aggregation function is used.

Query Result:

EXAMPLE 6: EXECUTING QUERY ON MULTIPLE TABLES

This example shows how to execute queries on multiple tables (built from multiple Context References).

Query Text:

SELECT

d.deviceServerInfo$owner || '.' || d.deviceServerInfo$name as device_server,

d.deviceServerInfo$blocked,

u.userInfo$city,

u.userInfo$country

FROM

users.*:userInfo as u, users.*.deviceservers.*:deviceServerInfo as d

WHERE

u.userInfo$username = d.deviceServerInfo$owner

This query has two Context References in the FROM clause. The first one is used to build a table containing all basic settings of all users, and the second one results in a table of all basic settings of all Device Server. These tables are then joined together based on the formula contained in WHERE clause ("u.userInfo$username = d.deviceServerInfo$owner"). The query result has four fields: first contains full Device Server name in the form "Owner Name"."Device Server Name". Second shows "Blocked" status of the Device Server. Two other columns show city and country of the user who owns the Device Server (which are probably the city and country where Device Server is located).

Query Result: