AQL Proposed Functions (discussion)

This was taken out from the AQL spec v1.0.0, since it was a proposal, not really part of the spec:

1.5.2. AQL features that are proposed, but not finalised

1.5.2.1. Arithmetic functions

It has been proposed that AQL would support some basic arithmetic functions, such as addition (plus) , subtraction (minus) . Multiplication, and division may be supported as well. We do not have any scenario requiring for these two functions yet.

Most cases, it is required that the left operand and the right operand must be the same type in order to do the calculation, e.g. numeric types. openEHR RM supports addition and subtraction for the types of: DV_QUANTITY, DV_COUNT, DV_INTERVAL, DV_PROPORTION, DV_DATE_TIME, DV_DATE, and DV_TIME. Again, it is required that the left and right operands must be the same type. However, one exception to this is that openEHR RM also supports subtraction between all subtypes of DV_TEMPORAL and type of DV_DURATION. AQL would support these arithmetic functions that openEHR RM supports.

The example below shows a scenario which requires that a Composition report (c1) is issued in the last year. It illustrates that a type of ISO 8601 date string (current-date()) subtracts a type of ISO 8601 interval string (P1Y).

1.5.2.2. Other functions

It is proposed that AQL may also support other functions, such as:

• current-date(): a build-in function returning the current date value in ISO date string format.

• current-date-time(): a build-in function returning the current date time value in ISO date/time string format.

• max: a build-in function returning the max value out of an expression.

Tasks:

1. Define which arithmetic functions should be supported, over what types those could operate, and how parameters could be specified (constants, paths, parameters, other functions, etc.)

2. Define which “other functions” should be supported, at least for a first basic set of functions, also define the parameter types, return types, operand references, etc.

3. On all cases, we need to specified if there will be some type checking, and which errors should be returned on those cases (could be at parsing time or at evaluation time).

First ideas:

1. Parameters: for any operand/parameter for the functions, support reference by path, constant, parameter and function (composing functions should be supported).

2. When processing queries, type checking should be in place, at least for constants, parameters and functions. Type checking for paths will require to access the correspondent information OPT/ADL to check for the type. It is possible to do it at a validation stage, but will not work to do that at run time in real time because of the overhead will affect performance.

3. Date-related functions are needed to simplify some queries. For instance, it is normal to check for data that was created “X months ago” or “in the last Y years”, we have some functions for that: “age_in_years(date)” returns how many years passed since date until now, “age_in_months(date)” does the same in months. This is similar to the example on the spec but that does the calculation in the query: path > current-date() - P1Y would be the same as age_in_years(path) < 1. Still, current-date() and current-date-time() might be useful, BUT we might need to use underscores instead of hyphens because of the minus operator/arithmetic function could be mismatched in the parser.

4. If the date-related functions return an ISO8601 string, we should specify if any ISO8601 format is preferred, the datetime function should mention something about the timezone returned.

5. Basic aggregation functions will also be useful for the projections in the SELECT, but if that is in place, also something like GROUP BY is needed like in SQL. This could be tricky. A workaround is to specify that no aggregation functions are supported and that a post-processing step on the result set is needed to do the required calculations.