The Baja Query Language (BQL) is an SQL-like query language that provides a mechanism for identifying various sets of data. It provides an ad hoc way to search for data based on some criteria. By including BQL in an ord, the results can be easily book marked or embedded in graphics views. This makes BQL an excellent tool for building reports.
The select query is the most common type of BQL query. It is very similar to the select statement in SQL. The syntax is as follows:
select <projection> from <extent> where <predicate> <having> <order by>
The select statement always returns a table even if the result is actually a single object.
The first concept to understand about the above query is the extent. The extent is specified in the "from" clause of the query. The extent works together with the ord base to determine the general set of objects in the result. The rest of the query only narrows the general result. This is best explained with a few examples.
slot:/a/b/c|bql:select name, toString from control:ControlPoint
In the above query, the base of the "bql" query is the "slot" query. The slot scheme is used to access data in a Baja component tree. In this case, "slot:/a/b/c" identifies the root of the tree where the BQL query processing will start. From that point, the query will recursively search the tree for components of type "control:ControlPoint". So, when the base of the "bql" query is a slot path, the path identifies the subtree that will be searched by the query, and the extent identifies the type of component to search for. This query would get the name and toString for all control points under the /a/b/c in the component tree.
history:|bql:select timestamp, value, status from /myStation/myHistory
In this query, the base of the "bql" query is a "history" query. The history scheme is used to access data in the Baja history database. In this case, "history:" identifies the entire set of histories in the database. The query extent "/myStation/myHistory" identifies a specific history in the database. This query would get the timestamp, value, and status of all records in the history with the id "/myStation/myHistory".
The projection is a comma separated list of the columns that will be returned in the result. Each element in the list must have a column specification and may have a display name specified with the 'as' clause. Beginning in Niagara 3.5, columns may be arbitrary expressions. The most frequent type of expression is a path expression, but you can also call scalar or aggregate functions.
select name, toString from baja:Component select name as 'Point', out.value as 'Output Value', out.status from control:NumericPoint select MAX(out.value), MIN(out.value) from control:NumericPoint select (out.value * 100) + '%' as 'Percent' from control:NumericPoint
In the second query, we know that all numeric points have an "out" property that is a StatusValue. A StatusValue is a structure that contains both a status and a value. In this query, we use a path to dive into the structure and extract the value and status individually.
In the third query, we use two aggregate functions, MAX and MIN, to find the largest and smallest value of all the control:ControlPoints in our query. The result will only have one row. See the section on BQL functions for more details
In the fourth query, we perform a calculation on the out.value to make it a percent, and then append the '%' character to the result so that the column values display with a percent sign. The column name is aliased as 'Percent'.
The predicate must be a boolean expression. Its purpose is to apply criteria for filtering objects out of the extent. Look at this query:
history:|bql:select timestamp, value from /weatherStation/outsideAirTemp
This query would retrieve the timestamp and value of all records in the specified history. That's often not a useful query and depending on how long the history has been collected, it may return a lot more data than we care to see. Instead, let's find all records where the value exceeds 80 degrees.
history:|bql:select timestamp, value from /weatherStation/outsideAirTemp where value > 80
By adding the "where" clause with "value > 80", all records with a value less than 80 are filtered out of the result. To learn more about BQL expressions, see BQL Expressions.
The "having" clause must be a boolean expression. The having clause has the same semantics as in SQL. You can use the having clause to filter the results of your query based on aggregate functions. Consider this query:
select displayName, SUM(out.value) from control:NumericPoint having SUM(out.value) > 100
First, note that this query could return multiple rows since its projection contains both scalar columns ("displayName") and aggregate columns ("SUM(out.value)"). Each row will contain a distinct displayName, and the SUM of all the "out.value" values for the objects with that displayName. The HAVING clause will further restrict the result to only contain rows where the SUM of all the out.value values is greater than 100.
Note that if the above query had only asked for "SUM(out.value)" and did not ask for the displayName, there would only be one row in the result. It would contain the SUM of all the "out.value" values regardless of the object's displayName. It would not be very useful to include a HAVING clause in such a query.
The "order by" clause can be used to sort the results of the bql query. It also has similar semantics to SQL. You can order by a path expression, a column alias, or column position (using a 1-based index). Further, you can specify whether you want the ordering to be done in ascending (ASC) or descending (DESC) order. ASC is assumed if not specified. For example,
select displayName, slotPath as 'Path' from control:NumericPoint order by out.value, 1, 'Path' DESC
BQL does not have a GROUP BY clause. If you mention ANY path expression in a query that contains aggregate functions, BQL implicitly defines a distinct grouping based on all the unique path expressions in your query. Consider:
select displayName, MAX(out.value) from control:NumericPoint where isWritablePointThis query will cause the bql engine to define an implicit grouping based on the "displayName" and "isWritablePoint" values.
In some cases, it may be desirable to fetch a single value instead of a table of objects. You can can accomplish that with BQL by using a simple BQL expression.
slot:/a/b/c|bql:handle
Putting a simple path expression in the BQL ord, causes the expression to be evaluated relative to the base. Resolving this ord just returns the value of the expression. In this case the result is the handle of the component identified by "/a/b/c". Note: If you run this query in Workbench, you will get a "No views are accessible" error since the there are no views registered on the simple type "java.lang.String", which is the type of the "handle" path expression.
Beginning in Niagara 3.5, you can evaluate multiple expressions against the base and have the results returned in a table with a single row.
slot:/a/b/c|bql:{handle, out.value * 100, displayName + ' is my name'}
Each of the expressions in the list is evaluated against the component at "slot:/a/b/c". The result is a table with a single row with the result of evaluating each expression in its corresponding column.
Beginning in Niagara 3.6, you can alias the expressions. The column for that expression will have the alias as its display name in the resulting table.
slot:/a/b/c|bql:{handle as 'h', out.value / 2 as 'half', displayName}
BQL paths are an important element of any BQL query. A path can be used to specify column content or to filter the rows in a query result. In all cases, a path is relative to the set of objects defined by the extent.
A path is a dot-separated list of fields. Consider the following example:
slot:/a/b|bql:select name, historyConfig.capacity from history:HistoryExt
This retrieves the name and configured capacity of all history extensions under "/a/b". The extent tells me that I am only looking for history extensions. The second column specifier tells me to look inside the historyConfig and extract the value of the "capacity" property. The same concept can be applied in the "where" clause.
slot:/a/b|bql:select name, out from control:NumericPoint where out.value > 50
In this case, the extent tells me that I am only looking for numeric points. The where clause looks at the "value" property of the "out" property of each numeric point in "/a/b" and only includes the ones that are greater than 50.
Presenting a list of all available fields in a path is not feasible. The fields that can be accessed in a path include all frozen and dynamic properties of any component or struct (given sufficient security permissions) plus many of the methods on the target type. The Bajadoc reference is the best place to find this information for a particular type.
A method is accessible via a path if it is public and returns a non-void value and takes either no parameters or only a Context as a parameter. Methods that match the "getX" pattern are handled specially. To access a getter from BQL, the "get" is dropped and the next letter is changed to lowercase resulting in the name of the desired value rather than the method name for getting it.
getX -> x getCurrentTemperature -> currentTemperature
A few methods are used particularly often. "name" gets the slot name of a value on its parent. "parent" get the parent component. "parent" is useful because it allows you to look up the component tree.
slot:/foo/bar|bql:select parent.name, parent.slotPath from schedule:BooleanSchedule
This query finds the name and path of all containers that contain a BooleanSchedule.
For more examples, see BQL Examples.
For more information about expressions, see BQL Expressions.
BQL supports two types of function expressions: 1) scalar functions and 2) aggregate functions. Scalar functions operate on a single value and return a single value. In this respect they are similar to path expressions. Aggregate functions operate on a set of values, and return a single, summarizing value. BQL also supports the ability for programmers to create their own scalar and aggregate functions. In all cases, the syntax for calling a function is
(<type spec>.)<function name>(<parameter list>)
The type spec is only required when the function is not part of the built-in BQL library. This is described in more detail in the sections below.
BQL provides the following built-in scalar functions
select substr(displayName, 0, 1) from baja:Folder select slotPath, displayName from baja:Component where slotExists('out')
The first query returns the first letter of all BFolders. The second query returns the slot path of every BComponent that has an 'out' slot.
In this example, we show how to create a new scalar function "strlen" that returns the
length of a BString. To create a new scalar function you simply define a new
public static
method in one of your BObjects where the first
parameter is a BObject (the target object to work with), and the rest of the
parameters match the type of the parameters for your method. The return type
of all BQL functions must be a BObject.
public BLib extends BObject {
/** Define the strlen function */
public static BInteger strlen(BObject target, BString str) {
return BInteger.make(str.getString().length());
}
public static final Type TYPE = Sys.loadType(BBLib.class);
public Type getType() { return TYPE; }
}
That's it! Pretty straight-forward. Assuming this function was in a module called "MyBql", here is how you could use it to get the displayName and its length for every BFolder (note the use of the BTypeSpec to call the function):
select displayName, MyBql:Lib.strlen(displayName) from baja:Folder
BQL provides the following built-in aggregate functions:
select MAX(out), MIN(out), AVG(out), SUM(out) from control:NumericWritable select substr(displayName, 0, 1), COUNT(*) from baja:Folder
The first query returns the max, min, average, and sum of all the out properties of all control:NumericWritables. The resulting table will have a single row with four columns. The second query gets the first letter of every folder and then counts how many folders start with that letter.
Note: The ability to create user-defined aggregate functions is still considered experimental. The steps to create aggregate functions may change in the future.
In this example we show how to create and implement the AVG() aggregate function provided by BQL. Creating an aggregate function is a two-step process. The process is outlined below, and then a code example is provided.
public void
method called "aggregate"
with a single parameter that is the type you want to aggregate on. This
method will be called for each object in the result set. This is where the
aggregating should be done.public static final Type[] <function name>
in
one of your module's classes. The <function name> is the actual
function name that would be used in a bql statement. If you have multiple
implementations of the aggregate function (perhaps to support different argument types), include
them all in the array. The BQL engine will search the list of implementing
classes until it finds one that implements an "aggregate(<type>)" method that matches
the type of the current object.Here is an implementation of AVG that supports averaging BNumbers and BStatusNumerics. This code example shows how to implement step 1 above.
public final class BAverage extends BObject implements BIAggregator {
/** Aggregate a BNumber */
public void aggregate(BNumber value) {
++count;
sum += value.getDouble();
}
/** Only aggregates if the status is valid. Otherwise it is skipped */
public void aggregate(BStatusNumeric value) {
if (value.getStatus().isValid()) {
++count;
sum += value.getValue();
}
}
/** Calculate the average and return the result */
public BDouble commit() {
if (count == 0)
return BDouble.NaN;
else
return BDouble.make(sum/count);
}
public static final Type TYPE = Sys.loadType(BAvg.class);
public Type getType() { return TYPE; }
private double sum;
private long count;
}
In the scalar example above, we created a class "BLib" in the "MyBql" module to create the "strlen()" function. Here is how we can modify that class to define the AVG function we just created. This shows how to implement step 2 from the outline above.
public BLib extends BObject {
/** Define the strlen function */
public static BInteger strlen(BObject target, BString str) {
return BInteger.make(str.getString().length());
}
// Declare the AVG aggregate function (step 2)
public static final Type[] avg = { BAverage.TYPE };
public static final Type TYPE = Sys.loadType(BBLib.class);
public Type getType() { return TYPE; }
}
Note that the name of the aggregate function is determined by its declaration in step 2, it is NOT the name of the class that implements the aggregation logic. Also, aggregate names are case-insensitive. Here is how you would call your implementation of the average aggregate function (note the use of the BTypeSpec)
select MyBql:Lib.avg(out) from control:NumericWritable
BQL query results can easily be displayed in a table or chart in a user interface. However, the results may also be examined in code using the Baja API. The result of a "select" query is always a BITable. The items in the table depend on the query. If the projection is omitted, the result is a table of objects in the extent that matched the predicate requirements.
BOrd ord = BOrd.make("slot:/foo/bar|bql:select from control:NumericPoint");
BITable result = (BITable)ord.resolve(base).get();
Cursor c = result.cursor();
double total = 0d;
while (c.next())
{
total += ((BNumericPoint)c.get()).getOut().getValue();
}
If the query has a projection, the result is a BITable and must be accessed that way to get the column data.
BOrd ord = BOrd.make("slot:/foo/bar|bql:select name, out.value from control:NumericPoint");
BITable result = (BITable)ord.resolve(base).get();
ColumnList columns = result.getColumns();
Column valueColumn = columns.get(1);
TableCursor c = (TableCursor)result.cursor();
double total = 0d;
while (c.next())
{
total += ((BINumeric)c.get(valueColumn)).getNumeric();
}
Since Niagara AX 3.5 you have been able to perform BQL queries against unmounted components. This is useful when you are programmatically constructing component trees, and want to query the tree structure, but the components are not mounted in a station or bog. The example below illustrates how to do this.
// NOTE: using setOut() for numeric writables because set() doesn't work when not mounted.
BFolder folder = new BFolder();
BNumericWritable nw1 = new BNumericWritable();
nw1.setOut(new BStatusNumeric(50.0));
folder.add("a", nw1);
nw1 = new BNumericWritable();
nw1.setOut(new BStatusNumeric(100.0));
folder.add("b", nw1);
String bql = "select sum(out.value) from control:NumericWritable";
// Create the unmounted OrdTarget using new "unmounted" factory method
OrdTarget target = OrdTarget.unmounted(folder);
// Query the unmounted folder to get the sum of all children
// control:NumericWritables out.value values.
BITable coll = (BITable)BqlQuery.make(bql).resolve(target).get();
Copyright © 2000-2019 Tridium Inc. All rights reserved.