12. Data API

This endpoint expects a get request of the form: /data/{presetref}?parameters where the presetref is in the path of the URL. It is important to note that the preset influences what is expected in the parameters of the request. If the preset does not have a sql_query field, the endpoint is meant to change the parameters of one or more filters in one preset. In this case, the wanted changes to the filters are expected in the parameters of the URL. In case the preset does have the field sql_query, the endpoint is meant to execute the SQL query found in the parameters part of the request. In both cases, a proper request will return a dictionary data type, which holds data of the request, including the result of the query with the changed filter parameters or the SQL query.

12.1. non sql_query

12.1.1. Examples

• Change the value of 1 or multiple filters by separating different filters

  with “&”. Example: /data/{presetref}?reference_date=1999-12-31&filparam=1. Here the parameters of the filter reference_date and filparam are being changed.

• Change multiple parameters of a filter. Example:

  /data/{presetref}?reference_date=1999-12-31,2000-01-01. Here the reference_date filter will acquire “1999-12-31” and “2000-01-01” as parameters.

• A combination of the above can be made. Example: /data/{presetref}?reference_date=1999-12-31,2000-01-01&filparam=1. Here the parameter of the filparam filter will become “1” and the parameters of the reference_date filter will become 1999-12-31 and 2000-01-01.

Warning

Filters cannot be added to presets, i.e., existing filter parameters can only be changed.

Warning

A parameter can only be given to the filter if it is already a predefined filter parameter or if the parameter can be successfully parsed by the parser predefined in the userexpr field of the filter.

Note

This endpoint makes use of a short-term cache to decrease execution time when multiple requests are being made on the same preset.

12.2. sql_query

For this type of request, a new type of preset is introduced.

12.2.1. Preset definition

The following attributes may be used in a sql_query preset definition:

• name – presentation name as displayed to the end-user.

• ref – unique identifier used to reference this preset.

• description – presentation description as displayed to the end-user.

• category – view category, a string referencing the category as defined in the preset section.

• sql_query – This holds the SQL query to be executed. The query has the following formatting rules:

  • table names must be escaped using { and } brackets.

  • tables must be present in the tables attribute below that specifies how a table should be filtered for a particular explorer user.

  • filter values must be escaped using %(filter_name). These instances will be automatically filled by the provided filter values in the preset.

  • Only SELECT queries are supported.

  • Common table expressions are not supported. The different query parts cannot be effectively considered in unison in most of the supported backends, which make CTEs presets hazardous to system performance.

• sql_query_options – A dictionary that holds all the placeholders for the filters and tables.

  • params – A dictionary for all the filter id s and their data types.

  • tables – A dictionary for all the table id s and the fields required for the row filter.

    • rowfilter – The rowfilter is defined in the authconf section.

    • expression – The expression used for the rowfilter.

12.2.2. Preset example

preset:
   name: sql_preset_join
   ref: SQL_PRESET_JOIN
   sql_query: |
     SELECT
       *
     FROM
         {explorer} ph
       JOIN
         {explorer} pr
       ON
         ph.organization_id = pr.organization_id
     WHERE
       pr.reference_date=%(reference_date)s AND
       pr.item_int=%(item_int)s
   description: preset for free SQL with a join
   sql_query_options:
      params:
         reference_date:
         datatype: date
         item_int:
         datatype: integer
      tables:
         explorer:
         rowfilter:
            expression: "organization_id = %(organization_id)s"
            params:
               organization_id: 1
   category: predefined

12.2.3. Example

For the following examples, the example preset is used.

• Join 2 tables with a rowfilter. Example: /data/{presetref}?reference_date=2000-01-01&item_int=10. Get entities where the reference_date field equals 200-01-01 and the item_int field equals 10

Note

For the request to succeed all the placeholders must be filled.