Generic Data Access REST API
Overview
DWP provides various methods to read generically data over the REST API from the specified Data Definition and matching the required filtering criteria.
/api/dataQuery
The Web Service provides a few methods to read data from the database following the contract specified in the Data Query, like Data Definition to read and which columns/attributes to return
/api/dataQuery/relationItems
The method returns the data defined by the Schema Relation and in a format specified in the Data Query
/api/widgetCommon/GetData
Generically reads the data with criteria specified in the Layout Data Source, like Data Definition, Data Query, or Entity.
/api/entity/fragments
The method returns list Display Names of the specified Data Definition.
/api/data
Public API. Generic Data Service.
Security
Most Solution Builder cases (like filling the grids with data, or showing data for dialog and previews ) are based on the generic mechanism for obtaining data. The following diagram presents various generic methods for reading data and which security restrictions are applied
Filtering response data
All the REST API methods that allow generically read data from the Production database support the filtering capabilities.
ASQL Where Expression
The methods support the string input argument with a plain ASQL Where Expression, which is directly applied to the Database Request to restrict the final result set.
https://{server}/m42Services/api/entity/fragments/? entityClass=SPSArticleClassBase&filter= EXISTS(SUBQUERY(SVCServiceBookingClassBase AS B, B.ID, base.ID = B.Service AND B.ProvisioningStatus = 1 AND B.Uninstalled = 0 AND B.Consumer = '54cba550-a32d-ec11-b6ed-6045bd8a02f0' AND B.OrderType <> 20))
Starting from version 12.0.5, due to security reasons, the generic data access API does not support result set filtering based on plain ASQL Where Expression is composed on the Client-side.
Starting from version 12.0.3 this functionality works only for Administrators or by activating the setting “AllowParameterizedFiltersOnly” in "SPSGlobalConfigurationClassBase". By default, in v. 12.0.3, this setting is disabled and therefore supports both types of data filtering, standard dynamic and parametrized.
For cases when the Generic Data Access API is used in integration scenarios (not in UUX Layouts), and for whatever reasons the Public API cannot be used, the following workaround can be used:
-
Create a placeholder Layout (e.g. Landing Page) with the Additional Data Source where the required Filter Expression is configured
-
Configure layout Audience to grant permissions only for authorized roles or persons.
-
Use API calls
Parametrized Filter Expression
Starting from version 12.0.3 REST API for generically reading data supports the Parametrized Filter Expressions.
The Filter Expression for filtering data is defined in the Layout Designer while configuring the Additional Data Source or specifying the filter expression of the control descriptor with filtering capabilities. Only the Layout author (Administrator role) has the possibility to review and modify the Filter Expression, which is serialized in the related Layout metadata.
The Web Requests for getting data generically do not send the filter as an argument, but only reference the pre-defined Filter expression by ID and provide the parameters values for the Filter Expression.
Expression Syntax
Parametrized Filter Expression is a standard ASQL Where Expression with additional syntax for declaring and handling parameters.
Declare Parameters
Use the syntax @{param_name}:{type} to declare a parameter in the Filter Expression.
{param_name}– Name of the parameter;{type}– Data type of the parameter. Supported values:intint[]guidguid[]stringstring[]datetime,datetime[]
The Boolean type for the parameter is not supported. Please utilize the 'int' type instead. The Data Modeler facilitates the direct assignment of Boolean variables to Integer parameters, wherein during runtime, Boolean values 'true' and 'false' are automatically converted to '1' and '0', respectively
Example:
EXISTS(SUBQUERY(SPSUserClassBase as U, U.ID, U.ID IN (@ids:guid[]) AND U.EmploymentType = @emp:int))
IFDEF(@{param_name}:{type}, {true ASQL statement}, {false ASQL Statement} )
Imposes conditions on the execution of an ASQL statement. The ASQL statement that follows an IFDEF keyword and its condition is executed if the parameter referenced in the condition is defined and is not null. The optional ELSE keyword introduces another ASQL statement that is executed when the parameter referenced in IFDEF condition is not set.
Example:
IFDEF(@id:guid, ID = @id, 1 = 1)
Configure Parametrized Filter Expression
Parametrized Filter Expression is configured in the Layout Designer for the following cases:
-
Picker controls Filter Expression
-
Objects Grid and Object Relations Grid Filter
-
Charts control Filter
Data Query and Data Definition Additional Data Sources
The Filter Expression can be defined for the corresponding Additional Data Source either by adding or editing in the text area “Source Filter Expression”
The provided Expression is immediately validated, and all detected parameters are added under the property $parameters for the corresponding Data Source branch in the Data Source panel.
Set Filter for Pickers, Grids, and Charts controls
There is a set of standard UUX controls that use dynamic filters for handling generically obtained data:
- Pickers: Object Picker, Objects Picker, Related Objects Picker, Hierarchical Picker
- Charts: Combo Chart, Pie Chart, Bar Chart, Stacked Bar Chart, Staked Column Chart, Column Chart
- Grids: Objects Grid, Object Relations Grid
In the same way as for the Data Sources, the controls also accept the Parametrized Filter Expression, which is immediately evaluated, and all detected parameters are automatically added to the Parameters collection.
Call Service Method with Parametrized Filter Expression
The Configured Filter Expression always stays on the Server in the corresponding Layout data or in the table PDRCustomFilterExpressions.
Example 1: Read Layout Data Source data
POST https://{server}/m42Services/api/WidgetCommon/{layout_id}/{datasource_id}/{datasource_name}
Body:
List of parameters
[{"Name":"@name","Value":"inc"}, {"Name":"@param2","Value":"123"}]
Where:
layout_id – object ID of the layout with configured Filter Expression (PDRContentWidgetClassBase.[Expression-ObjectID]);
datasource_id – id of the Additional Datasource (PDRContentWidgetTemplateClassBase.DataModelExtention);
Example 2: Read related Incidents filtered by predefined Filter Expression
POST https://{server}/m42Services/api/dataQuery/relationItems
Body:
{
"FragmentId": "78148e3f-755c-ee11-4092-98e743a2cbc2",
"EntityClassName": "SPSActivityClassBase",
"EntityTypeName": "SPSActivityTypeIncident",
"RelationName": "ChildActivities",
"Columns": "TicketID,Summary,Priority,State,User,Responsible,ResponsibleRole,Owner,CreationDate",
"FilterExpression": {
"Id": "{filter-expression-id}",
"Params": [{"Name":"@name","Value":"inc"}, {"Name":"@param2","Value":"123"}]
}
}
Where:
{filter-expression-id} - ID of the PDRCustomFilterExpressions table which keeps the predefined Filter Expression
Migration to Parametrized Filter Expression
The System Diagnostic rules list the layouts that still use the deprecated Dynamic Filter expressions and have not been migrated automatically on Update. Such layouts must be migrated manually till the update to version 12.0.5.
In version 12.0.3 the Diagnostic Rule "Validate that layout source/control filters are secured" is not running by default, and can be activated by setting RunControlsSecurityDiagnosticRule in the table PDRDwpConfigurationClass
The "Validate that layout source/control filters are secured" rule detects the affected layouts and lists the data sources and control descriptors that should be reworked and reconfigured according to the improved and secured filtering methods:
Click Zoom in for more details on the detected miscofigurations:
Click on the Layout name to open it in the Layout Designer and migrate it.
Migrating Data Sources
Some Custom Data Sources with dynamic filter expressions defined in layouts before version 12.0.3 will not be automatically transformed to Parametrized filter Expressions and need to be migrated manually till the update to version 12.0.4. In version 12.0.3 such Data Sources still support Dynamic Filter expressions.
To rework such Data Sources to Parametrized Filter Expression, uncheck “Allow dynamic expression” and provide the valid Filter Expression.
As far as the Dynamic Filters are supported for Administrators it is not mandatory to migrate such filters in the Layouts accessible only by Administrators. For such cases use the flag "Ignore the unsafe warning and keep using dynamic Filter" to exclude the Data Source from the System Diagnostic report "Validate that layout source/control filters are secured"
Migrating Controls
Some Controls with dynamic filter expressions defined in layouts before version 12.0.3 will not be automatically transformed to Parametrized filter Expressions and need to be migrated manually till the update to version 12.0.5. The Layout Designer highlights as a "warning" all the controls with not migrated Dynamic Filters. On manual the migration the original Filter definition needs to be reset and the corresponding Parametrized Filter Expression is provided instead.
For Administrative layouts, the migration is not necessary, and in the same way as for the Data Sources the "migration" warning can be eliminated from the Layout Designer and from the System Diagnostic report by setting the flag "Ignore the unsafe warning and keep using dynamic Filter" for the corresponding Filter property.