workspace_templates_location_update.js
/**
### /workspace/templates/location_update
The location_update layer query updates a record in the layer table identified where layer qID matches the provided id property.
The keys of the POST body are the field names. A key may only contain letters, numbers and underscores.
The values of the POST body are substituted as query params. A value is never written into the SQL string. The params are assigned with a `body_` prefix, so a body key can't overwrite a param of the template, eg. the table.
@module /workspace/templates/location_update
*/
/**
Field names [body keys] must be SQL identifiers.
*/
const FIELD = /^[A-Za-z_][A-Za-z0-9_]*$/;
export default (_) => {
const keys = Object.keys(_.body);
// The location ID must not be altered. Unquoted identifiers are case-insensitive in Postgres.
if (keys.some((key) => isReserved(key, _.layer))) {
throw new Error(
`Layer ${_.layer.key}: You cannot update the ${_.layer.qID} or ID field as it is a reserved parameter.`,
);
}
const fields = keys.map((key) => {
const value = _.body[key];
// The param for the value substitution.
const param = `body_${key}`;
checkField(key, _.layer);
// Value is null
if (value === null) {
return `${key} = null`;
}
// Value is geometry.
if (value.coordinates) {
_[param] = JSON.stringify(value);
return `${key} = ST_SetSRID(ST_MakeValid(ST_GeomFromGeoJSON(%{${param}})),${_.layer.srid})`;
}
// The jsonb object is merged into a jsonb field. The key is the infoj entry field.
if (
typeof value === 'object' &&
!Array.isArray(value) &&
value.jsonb instanceof Object
) {
const jsonb_field = Object.keys(value.jsonb)[0];
checkField(jsonb_field, _.layer);
_[param] = JSON.stringify(value.jsonb[jsonb_field]);
return `${jsonb_field} = coalesce(${jsonb_field}::jsonb,'{}'::jsonb) || %{${param}}::jsonb`;
}
// Value is an object and must be stringified.
if (typeof value === 'object' && !Array.isArray(value)) {
_[param] = JSON.stringify(value);
return `${key} = %{${param}}`;
}
// Value is a string, array, boolean, or number.
_[param] = value;
return `${key} = %{${param}}`;
});
return `
UPDATE ${_.table}
SET ${fields.join()}
WHERE ${_.layer.qID} = %{id};`;
};
/**
@function checkField
@description
Throws an error if the field name is not an SQL identifier. The field name is written into the SQL string.
@param {string} field The field name from the POST body.
@param {Object} layer The layer of the query.
*/
function checkField(field, layer) {
// FIELD.test(undefined) would test the string 'undefined'.
if (typeof field !== 'string' || !FIELD.test(field)) {
throw new Error(
`Layer ${layer.key}: Invalid field name in the location_update request body.`,
);
}
}
/**
@function isReserved
@description
Returns true if the field is the layer qID or the id field. Unquoted identifiers are case-insensitive in Postgres, so the comparison is case-insensitive.
@param {string} field The field name from the POST body.
@param {Object} layer The layer of the query.
@returns {boolean} The field is reserved.
*/
export function isReserved(field, layer) {
const name = field.toLowerCase();
return name === 'id' || name === String(layer.qID).toLowerCase();
}