SQL (Structured Query Language) is a powerful language for extracting and processing data. Tibbo AggreGate Server 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 AggreGate Server is called AggreGate Query Language.
AggreGate Query Language is highly integrated into AggreGate Server. Since all AggreGate Server 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.
|
This section of the manual is not a tutorial of the SQL language itself. Rather, it assumes you already know SQL and SQL syntax. This isn't because we want to make your life hard -- it's just because SQL is an extensive topic, and great minds have already wrote excellent books and tutorials on the topic. So, if you're unfamiliar with SQL, please read a bit about the subject generally. Once you understand SQL as a wider subject, come back to this chapter and learn how we've implemented it in AggreGate. We've listed some places where you can learn more about SQL in the SQL References section. |
Purpose Of The Query Language
AggreGate Query Language has two primary uses:
| 1. | Obtaining data from server objects or hardware devices, filtering, sorting and grouping it and presenting it to the user in a convenient form (as a table). |
| 2. | Modifying the properties of various contexts (so as to configure various hardware devices, for example) by entering values for settings in the table. |
The first task is performed using a normal data selection ("SELECT") query, creating a Data Table viewable through a AggreGate Server 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 AggreGate Server 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.
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 AggreGate Server 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 childInfo, 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).
|
People familiar with classic SQL language may use the following analogy to understand the AggreGate Query Language quickly: a query in AggreGate is exactly like a normal SQL query with some exceptions:
|
Query Execution Flow
This section describes the step-by-step process of how a query text is used to generate query results.
| 1. | At first stage, the query processing engine finds all Context References in the FROM clause of the query text. These references are then used to build ad-hoc tables, filled with data from the actual contexts, on which the query will later be executed. This process is described here. |
| 2. | A Classic SQL query is executed using the tables that were created at the previous step. The query produces a table, built according to the query syntax. |
| 3. | This table is now converted to a Data Table. This makes it simpler to understand: the final Data Table contains all field descriptions, formatting and validation rules and any other metadata that may be fetched from the Data Tables on which the query was executed. So, for all intents and purposes. AggreGate uses a query to generate a Data Table. The only reason we mentioned the previous step (2) is to give you a glimpse at the internals of the system. |
| 4. | The query result is shown to the user. If the results are editable, the user can now make changes to the data presented. |
| 5. | If the user saves the modified results, the query processing engine writes all modified data back to the context variables from which it came. |
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:
| • | The primary SELECT clause, defining which fields of the original tables should be included in the query result. |
| • | The FROM clause, indicating the source table or tables from which the data is to be retrieved. The FROM clause can include optional JOIN clauses, joining related tables with each other based on user-specified criteria. |
| • | The WHERE clause includes a conditional expression, which is used to filter data returned by the query. The WHERE clause is applied before the GROUP BY clause. Technically speaking, the WHERE clause eliminates all rows from the result set where the conditional expression does not evaluate to True. |
| • | The GROUP BY clause is used to combine, or group, rows with related values. GROUP BY is often used to calculate statistics (total, average, etc) of similar rows or to eliminate duplicate rows from a result set. |
| • | The HAVING clause includes a conditional expression used to eliminate rows after the GROUP BY clause is applied to the result set. |
| • | The UNION clause allows to combine the results of two or more SELECT statements into a single result. |
| • | The ORDER BY clause is used to designate the columns used to sort resulting data, and whether they should be ascending or descending. The order of rows returned by an SQL query is never guaranteed unless an ORDER BY clause is specified. |
| • | The LIMIT clause restricts the final result to a predefined range of rows. This is just a numeric range -- for example, if you have a query resulting in 100 records, but you just want 10 arbitrary records (the first ten, last ten, ten records starting from the 15th record, etc.), you can use a LIMIT clause to get just as many records as you want. |
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.
Administering Queries
Two contexts are used to administer queries: One is the general Queries context, which serves as a container. The other is the Query context, which holds the information for a single query. See descriptions below. |
|
|
Every user has his own set of Queries. |
Several queries are built into AggreGate Server and appear under default administrator's user account:
| • | All Users. This query allows viewing and editing the settings of all user accounts in a single table. |
| • | Active Alerts. Shows pending and escalated alerts summary. |
These queries are described in the Query Examples chapter.
For more information on SQL language check the following links:
http://en.wikipedia.org/wiki/SQL