Filtering Expressions

In order to support sophisticated queries, Devii features a powerful filtering expression syntax. This mini-language is deliberately designed to be similar, but not identical, to SQL "WHERE-clause" syntax. Specifically, it can do a few things that SQL WHERE-clauses cannot, such as comparing equal or not-equal to NULL, comparing directly to an aggregate function's return value, and issuing subquery checks against related tables.

In terms of syntax, the expression language recognizes standard literal values and operator symbols: strings may be single or double-quoted, parentheses may be used for grouping and precedence, the set of comparison and mathematical operators is what a C/C++, Java, Javascript, or Python programmer would generally expect, along with a couple of operators borrowed from SQL, such as the LIKE and ILIKE operators. Whitespace is not significant, and all word-based operators are case-insensitive. Function calls use syntax like SQL or the above programming languages: function(arg1, arg2,...argN).

These are the different classes defined:

• Column: A column reference. This is used to look up column names.

• Role Attributes. This is special; the current role has certain attributes which can be looked up to compare against table values. The syntax is $_PRINCIPAL.[attribute]. Available attributes are roleid, capabilities, classes, children, parentid, and tenantid.

• Value Comparisons: A comparison between two values. The values may be literals, column references, function calls, or math operation expressions. Operators are =, !=, <, >, <=, >=, IN, LIKE, or ILIKE. The IN operator requires a list as an argument, expressed with [] brackets, like so: [1,2,3] or ['a', 'b', 'c']. LIKE and ILIKE require string arguments, with % used as a wildcard, like the SQL operator.

• Function calls: The functions available are whitelisted in the package_functions table in the Devii schema. Function call arguments may be literals, column references, math operations, or other function calls.

• Math: A binary mathematical operation. The values may be literals, column references, function calls, or math (or unitary math) operation expressions. Operators are +, -, *, /, %, ^, & (bitwise AND), | (bitwise OR), # (bitwise XOR), <<, and >>.

• Unitary Math: A unitary mathematical operation. The operator is prefixed to a value, which may be a literal, column reference, function call, or math/unitary math operation expression. Operators are @ (absolute value), |/ (square root), ! (factorial), and ~ (bitwise NOT).

• AND operation: Two subexpressions joined by the word AND. As expected, this evalutes True if both subexpressions are True.

• OR operation: Two subexpressions joined by the word OR. Evalutes True if one subexpression is True.

• NOT operation: NOT preceeding one subexpression. Inverts the subexpression's truth value.

• ANY operation: Relation-subquery lookup. Syntax is $COLLECTION_ATTRIBUTE, chaining through related tables if necessary, followed by the word ANY and then a ValueComparison expression in parens, followed by a "." and an attribute of the associated table to look up. For example, to match all employees whose companyid is equal to the user's companyid, given a relationship attrbute on the employee object called "company", the expression would be: company.employees_collection ANY(roleid = $\_PRINCIPAL.roleid).companyid. This is transformed into an EXISTS subquery on the associated table.

Here are some example expressions and how they are interpreted:

• id = 1

  Simple equality comparison. This will match a record whose 'id' column = 1.

• name ilike "foo%"

  This is another simple comparison, this time using the SQL ILIKE operator for string matching. This will match a record whose 'name' column starts with the string 'foo'.

• id % 3 = 0

  Computing a mathematical operation using column values. In this case, the id is run through a modulo 3, and compared to 0; this will effectively match every third record, starting with id = 3.

• id in [1, 2, 3]

  List syntax works with the IN operator. This will match records whose id attributes equal 1, 2, or 3.

• id = round(|/ 2)

  Computing unitary mathematical operations. This will match the record whose id is the square root of 2, rounded down, i.e. 1. This is a silly example, but such operations are useful for querying scientific datasets.

• creatorid = $_PRINCIPAL.roleid AND createdate > "2020-01-01"

  AND expression. Both the left and right expressions must return true to match. In this case, the creatorid must equal the current role's roleid, and the createdate must be after January 1 of 2020.

• company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).companyid

  A relation-subquery lookup. Chaining through the relationship between the current table and a table of companies, the employees whose companyid matches the companyid of the current role will be have their companyids returned. This effectively matches on any employee within the same company as the current role.

• createdate = max(createdate)

  Illustrates the use of aggregates in direct comparisons. This will match the record with the latest createdate. The query will issue a small subquery to handle the aggregate lookup.

• regionid != null

  Illustrates using standard equality operators on null. The = will be translated to an IS, and != to ISNOT, as needed.

• st_contains(geom, st_setsrid(st_makepoint(-96.8, 46.9), 4326))

  This uses geospatial operations to match records whose geometry contains the latlong point 46.9 N, 96.8 W (in northern Fargo, ND), ensuring that the coordinate is in the WGS-84 coordinate system.