The query module exports the [SQL] query method to pass queries to the stored dbs_connections available to the XYZ instance.
- Source
Requires
- module:/utils/dbs
- module:/utils/sqlFilter
- module:/utils/roles
- module:/utils/logger
- module:/workspace/cache
- module:/workspace/getTemplate
- module:/workspace/getLayer
Methods
(async, inner) executeQuery(req, res, template, query)
The method send a parameterised query to a database connection.
The dbs for the query is determined primarily by the template. The layer.dbs is used for layer queries if the dbs on the template is not implicit. The locale.dbs is assumed as the layer.dbs if not defined in JSON layer. The workspace.dbs will be used as fallback if no template, layer, or locale dbs can be determined.
| Name | Type | Description |
|---|---|---|
req | req | HTTP request. |
res | res | HTTP response. |
template | Object | Request template. |
query | string | SQL query. |
| Name | Type | Attributes | Description |
|---|---|---|---|
req.params | Object | <optional> | Request params. |
- Source
(inner) getQueryFromTemplate(req, template)
In order to prevent SQL injections queries must be build from templates stored in the workspace.templates{}.
A template may have a render method which returns a query string assigned as template.template.
Parameter to be replaced in the SQL query string must be checked to only contain whitelisted character to prevent SQL injection.
Any variables to be replaced on query execution in the database must be replaced with indices in the query string. eg. $1, $2, ...
The substitute values are stored in the ordered params.SQL[] array.
An error will be returned if the substitution fails.
| Name | Type | Description |
|---|---|---|
req | req | HTTP request. |
template | Object | Request template. |
| Name | Type | Description |
|---|---|---|
req.params | Object | Request params. |
params.filter | Object | JSON filter which must be turned into a SQL filter string for substitution. |
params.SQL | Array | Substitute parameter for SQL query. |
template.render | function | Method to render template string. |
template.template | string | SQL template string. |
- Source
(async, inner) layerQuery(req, res)
Queries which reference a layer must be checked against the layer JSON in the workspace.
Layer queries have restricted viewport and filter params. These params can not be substituted in the database but must be replaced in the SQL query string.
Any query which references a layer and locale will be passed through the layer query method. The getLayer method will fail return an error if the locale is not defined as param or the layer is not a member of the locale.
/api/query?template=query&locale=uk&layer=retail
| Name | Type | Attributes | Description |
|---|---|---|---|
req.params | Object | Request params. | |
params.filter | Object | JSON filter which must be turned into a SQL filter string for substitution. | |
params.SQL | Array | Substitute parameter for SQL query. | |
params.user | Object | <optional> | Requesting user. |
user.roles | Array | <optional> | User roles. |
- Source
(async, inner) query(req, res)
The [SQL] query method requests a query template from the getTemplate method and checks whether the requesting user is permitted to execute the query.
The layerQuery() method must be awaited for queries that reference a layer. The layerQuery must be run before the getTemplate() request since the query template may be defined in the layer [template].
A template is turned into a query by the getQueryFromTemplate() method.
The query is executed by the executeQuery() method.
| Name | Type | Attributes | Description |
|---|---|---|---|
req.params | Object | Request params. | |
params.user | Object | <optional> | Requesting user. |
user.roles | Array | <optional> | User roles. |
- Source
(inner) sendRows(req, res, template, rows)
The method formats the rows returned from a SQL query and sends the formated rows through the HTTP response object.
| Name | Type | Description |
|---|---|---|
req | req | HTTP request. |
res | res | HTTP response. |
template | Object | Request template. |
rows | array | The response from a SQL query. |
- Source