summaryrefslogtreecommitdiff
path: root/src/app/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/app/db')
-rw-r--r--src/app/db/configure.js58
-rw-r--r--src/app/db/helpers.js248
-rw-r--r--src/app/db/models/Shoe.js17
-rw-r--r--src/app/db/models/index.js18
-rw-r--r--src/app/db/query.js148
-rw-r--r--src/app/db/service/base/helpers.js240
-rw-r--r--src/app/db/service/base/index.js137
-rw-r--r--src/app/db/service/base/many.js205
-rw-r--r--src/app/db/service/base/methods.js217
-rw-r--r--src/app/db/service/base/response.js118
-rw-r--r--src/app/db/service/pivot/helpers.js113
-rw-r--r--src/app/db/service/pivot/index.js47
-rw-r--r--src/app/db/service/pivot/methods.js330
13 files changed, 1896 insertions, 0 deletions
diff --git a/src/app/db/configure.js b/src/app/db/configure.js
new file mode 100644
index 0000000..1581901
--- /dev/null
+++ b/src/app/db/configure.js
@@ -0,0 +1,58 @@
+/**
+ * Configure our database.
+ * @module app/db/configure
+ */
+
+import _knex from "knex";
+import _bookshelf from "bookshelf";
+import jsonColumns from "bookshelf-json-columns";
+import cascadeDelete from "bookshelf-cascade-delete";
+
+import { knexfile } from "app/constants";
+import { registerModels } from "app/db/models";
+
+import { types } from "pg";
+
+/**
+ * PostgreSQL type conversion
+ */
+types.setTypeParser(types.builtins.INT8, (value) => parseInt(value));
+types.setTypeParser(types.builtins.FLOAT8, (value) => parseFloat(value));
+types.setTypeParser(types.builtins.NUMERIC, (value) => parseFloat(value));
+
+/**
+ * Memoized copies of the current knex and bookshelf objects.
+ */
+let knex, bookshelf;
+
+/**
+ * Configure a knex instance based on the knexfile.
+ */
+function configureKnex() {
+ const environment = process.env.NODE_ENV ?? "development";
+
+ if (!knexfile[environment]) {
+ throw new Error("Please specify a valid NODE_ENV");
+ }
+
+ knex = _knex(knexfile[environment]);
+ return knex;
+}
+
+/**
+ * Configure knex and bookshelf. By default, memoizes the
+ * knex object so other methods can call `configure()` and get it.
+ * The database is configured this way to be interoperable with the test environment.
+ *
+ * @param {Object} __knex An existing knex instance, mainly used in testing.
+ */
+export default function configure(__knex) {
+ if (!knex || (__knex && knex !== __knex)) {
+ knex = __knex || configureKnex();
+ bookshelf = _bookshelf(knex);
+ bookshelf.plugin(jsonColumns);
+ bookshelf.plugin(cascadeDelete);
+ registerModels(bookshelf);
+ }
+ return { bookshelf, knex };
+}
diff --git a/src/app/db/helpers.js b/src/app/db/helpers.js
new file mode 100644
index 0000000..0b19833
--- /dev/null
+++ b/src/app/db/helpers.js
@@ -0,0 +1,248 @@
+/**
+ * Helper functions for database queries.
+ * @module app/db/helpers
+ */
+
+import { capitalizeWord } from "app/utils/text_utils";
+
+/**
+ * Build an index query.
+ * @param {knex.builder} builder the Knex query builder
+ * @param {bookshelf.Model} Model the affected model
+ * @param {Object} query the query parameters
+ * @param {Object} paginate pagination settings
+ * @param {User} user the request.user
+ * @param {function} queryBuilder optional, function to build the query
+ * @param {Object} columns valid columns on the model
+ * @returns {knex.builder} the supplied Knex query builder object
+ */
+export function buildIndexQuery({
+ builder,
+ Model,
+ query,
+ paginate,
+ queryBuilder,
+ columns,
+}) {
+ const { offset, limit } = getOffsetAndLimit(query, paginate);
+ const { sortField, sortDirection, sortCount } = getSort(query, Model);
+
+ const tableName = Model.prototype.tableName;
+ const idAttribute = Model.prototype.idAttribute;
+ const sortTableName = tableName + "_" + sortField;
+ const sortOrderName = sortField + "_count";
+
+ // Get the filtered columns
+ let filters = getQueryFilters(query);
+
+ if (sortCount) {
+ // Sort by a pivot relation
+ builder.select("*", function () {
+ // Note: Knex syntax requires "this"
+ return this.from(sortTableName)
+ .count("*")
+ .whereRaw("?? = ??", [
+ `${sortTableName}.${idAttribute}`,
+ `${tableName}.${idAttribute}`,
+ ])
+ .as(sortOrderName);
+ });
+ }
+
+ // If there are filters, use the supplied query builder
+ if (Object.keys(filters).length > 0) {
+ if (queryBuilder) {
+ queryBuilder(builder, columns, filters);
+ } else {
+ builder.where(filters);
+ }
+ }
+
+ // Sorting
+ if (sortCount) {
+ builder.orderBy(sortOrderName, sortDirection);
+ } else {
+ builder.orderBy(sortField, sortDirection);
+ }
+
+ // Pagination
+ if (limit) {
+ builder.limit(limit);
+ }
+ if (offset) {
+ builder.offset(offset);
+ }
+
+ // console.log(builder)
+ return builder;
+}
+
+/**
+ * Build a count query.
+ * @param {knex.builder} builder the Knex query builder
+ * @param {Object} query the query parameters
+ * @param {User} user the request.user
+ * @param {function} queryBuilder optional, function to build the query
+ * @param {Object} columns valid columns on the model
+ * @returns {knex.builder} the supplied Knex query builder object
+ */
+export function buildCountQuery({ builder, query, queryBuilder, columns }) {
+ let filters = getQueryFilters(query);
+
+ if (Object.keys(filters).length > 0) {
+ if (queryBuilder) {
+ queryBuilder(builder, columns, filters);
+ } else {
+ builder.where(filters);
+ }
+ }
+
+ return builder;
+}
+
+/**
+ * Build a pagination response for a count request.
+ * @param {number} rowCount the total number of response returned from count()
+ * @param {Object} query the query parameters
+ * @param {Object} paginate the service pagination
+ * @returns {knex.builder} the response object
+ */
+export function buildPaginationResponse({ rowCount, query, paginate }) {
+ const { page, limit } = getOffsetAndLimit(query, paginate);
+ return {
+ rowCount: rowCount || 0,
+ page: page,
+ pageSize: limit,
+ pages: limit ? Math.ceil(rowCount / limit) : 1,
+ };
+}
+
+/**
+ * Query builder for index queries. Override in subclasses.
+ * @param {knex.builder} Knex query builder
+ * @param {Object} query parameters, minus offset/sort parameters
+ */
+export function indexQueryBuilder(builder, columns, filters) {
+ const validFilters = Object.keys(columns).reduce((valid, name) => {
+ if (name in filters) {
+ valid[name] = filters[name];
+ }
+ return valid;
+ }, {});
+ builder.where(validFilters);
+}
+
+/**
+ * Get the offset and limit for a given query.
+ * @param {Object} query the query object
+ * @param {number} query.offset numeric offset into results
+ * @param {number} query.limit maximum number of results to return
+ * @param {number} query.pageSize size of a page of results (synonym for limit)
+ * @param {number} query.page which page to return, zero-indexed
+ * @param {Object} paginate pagination settings
+ * @param {number} paginate.pageSize default page size
+ * @param {number} paginate.pageSizeLimit maximum results to return, caps limit
+ * @return {Object} query offset, limit, and page
+ */
+export function getOffsetAndLimit(query, paginate) {
+ let { offset, limit, pageSize, page } = query;
+
+ page = parseInt(page) || 0;
+
+ if (paginate) {
+ limit = parseInt(limit) || parseInt(pageSize) || paginate.pageSize;
+ if (paginate.pageSizeLimit) {
+ limit = Math.min(limit, paginate.pageSizeLimit);
+ }
+ } else if (limit) {
+ limit = parseInt(limit);
+ } else {
+ limit = 0;
+ }
+
+ if (page) {
+ offset = page * limit;
+ } else if (offset) {
+ offset = parseInt(offset);
+ } else {
+ offset = 0;
+ }
+ return { offset, limit, page };
+}
+
+/**
+ * Get the sort options for a query.
+ * @param {Object} query the query object
+ * @param {string} query.sort field to sort by
+ * @param {string} query.order sort direction, can be asc or desc
+ * @param {bookshelf.Model} Model the model to query, used to get the primary key
+ */
+export function getSort(query, Model) {
+ let { sort, order } = query;
+ let sortParts, sortField, sortDirection;
+ let sortCount = false;
+ if (order) {
+ sortField = sort;
+ sortDirection = order;
+ } else {
+ sortParts = (sort || "id desc").split(" ", 1);
+ sortField = sortParts[0] || "id";
+ sortDirection = sortParts[1] || "desc";
+ }
+ if (sortField === "id") {
+ sortField = Model.prototype.idAttribute;
+ }
+ if (sortField.match("_count")) {
+ sortField = sortField.split("_count")[0];
+ sortCount = true;
+ }
+ return { sortField, sortDirection, sortCount };
+}
+
+/**
+ * Get the columns to filter by, without offset/sort options
+ * @param {Object} query the query object
+ * @return {Object} fields unrelated to offset/sort
+ */
+export function getQueryFilters(query) {
+ // eslint-disable-next-line no-unused-vars
+ let { offset, limit, pageSize, page, sort, order, ...filters } = query;
+ return filters;
+}
+
+/**
+ * Load a table's columns from the database
+ * @param {bookshelf.Model} Model the model to query
+ * @return {Object} columns the columns
+ */
+export async function loadColumns(bookshelf, Model) {
+ if (!Model) {
+ return {};
+ }
+ const columns = await bookshelf.knex(Model.prototype.tableName).columnInfo();
+ return columns;
+}
+
+/**
+ * Filter the data in an object, leaving only prdetermined columns
+ * @param {Object} data data to filter, typically a request.body
+ * @param {Object} columns columns lookup returned from loadColumns
+ * @param {Array} privateFields columns not to include in the returned data
+ * @return {Object} the filtered data
+ */
+export function reduceValidColumns(data, columns, privateFields = []) {
+ return Object.keys(columns).reduce((item, columnName) => {
+ if (columnName in data && !privateFields.includes(columnName)) {
+ item[columnName] = data[columnName];
+ }
+ return item;
+ }, {});
+}
+
+/**
+ * Convert a table name to a modelname (camel case, capitalized)
+ * @param {string} tableName the table name
+ * @return {string} the model name
+ */
+export const tableNameToModelName = (tableName = "") =>
+ tableName.split("_").map(capitalizeWord).join("");
diff --git a/src/app/db/models/Shoe.js b/src/app/db/models/Shoe.js
new file mode 100644
index 0000000..a35da40
--- /dev/null
+++ b/src/app/db/models/Shoe.js
@@ -0,0 +1,17 @@
+/**
+ * Shoe model.
+ * @module app/db/models/Shoe
+ */
+
+export default function registerModel(bookshelf) {
+ bookshelf.model(
+ "Shoe",
+ {
+ tableName: "shoe",
+ idAttribute: "shoe_id",
+ },
+ {
+ jsonColumns: ["metadata"],
+ }
+ );
+}
diff --git a/src/app/db/models/index.js b/src/app/db/models/index.js
new file mode 100644
index 0000000..424be66
--- /dev/null
+++ b/src/app/db/models/index.js
@@ -0,0 +1,18 @@
+/**
+ * Register the models used by our application.
+ * @module app/db/models/index
+ */
+
+import registerShoe from "./Shoe";
+
+/**
+ * Register our models with a Bookshelf instance.
+ *
+ * Some modules define multiple models for pivot tables. In these cases,
+ * the models should be stored in the file for the closest related concept.
+ *
+ * @param {Object} bookshelf The bookshelf instance to bind to
+ */
+export function registerModels(bookshelf) {
+ registerShoe(bookshelf);
+}
diff --git a/src/app/db/query.js b/src/app/db/query.js
new file mode 100644
index 0000000..65a9157
--- /dev/null
+++ b/src/app/db/query.js
@@ -0,0 +1,148 @@
+/**
+ * Define standard database queries
+ * @module app/db/query
+ */
+
+import {
+ buildIndexQuery,
+ buildCountQuery,
+ buildPaginationResponse,
+} from "app/db/helpers";
+
+/**
+ * Fetch a set of database objects matching a query
+ * @param {bookshelf.Model} Model the model to query
+ * @param {Object} query query options
+ * @param {Object} paginate optional pagination options
+ * @param {User} user the current `request.user`
+ * @param {function} queryBuilder a custom function to build the query
+ * @param {Object} columns valid columns on the model
+ * @param {Array} withRelated a custom function to build the query
+ * @returns {Promise} a query which resolves to the desired objects
+ */
+export function index({
+ Model,
+ query,
+ paginate,
+ user,
+ columns,
+ queryBuilder,
+ withRelated,
+}) {
+ return Model.query((builder) =>
+ buildIndexQuery({
+ builder,
+ Model,
+ query,
+ paginate,
+ user,
+ columns,
+ queryBuilder,
+ })
+ ).fetchAll({ withRelated });
+}
+
+/**
+ * Count database objects matching a query
+ * @param {bookshelf.Model} Model the model to query
+ * @param {Object} query query options
+ * @param {Object} paginate optional pagination options
+ * @param {User} user the current `request.user`
+ * @param {function} queryBuilder a custom function to build the query
+ * @param {Object} columns valid columns on the model
+ * @returns {Promise} a query which resolves to the count
+ */
+export function count({ Model, query, paginate, user, queryBuilder, columns }) {
+ return Model.query((builder) =>
+ buildCountQuery({
+ builder,
+ Model,
+ query,
+ paginate,
+ user,
+ queryBuilder,
+ columns,
+ })
+ )
+ .count()
+ .then((rowCount) => {
+ return buildPaginationResponse({ rowCount, query, paginate });
+ });
+}
+
+/**
+ * Fetch single object by ID
+ * @param {bookshelf.Model} Model the model to query
+ * @param {number} objectID the primary key of the object
+ * @param {string} field optional, the field to query
+ * @param {Object} criteria additional criteria for the show query (when specifying a pivot table by its relations)
+ * @returns {Promise} a query which resolves to the desired object
+ */
+export function show({ Model, objectID, field, criteria, withRelated }) {
+ return new Model({
+ ...criteria,
+ [field || Model.prototype.idAttribute]: objectID,
+ }).fetch({ withRelated });
+}
+
+/**
+ * Fetch several objects by ID
+ * @param {bookshelf.Model} Model the model to query
+ * @param {Array} ids a list of keys to query
+ * @param {string} field optional, the field to query
+ * @returns {Promise} a query which resolves to the desired objects
+ */
+export function showIDs({ Model, ids, field, queryBuilder, withRelated }) {
+ return Model.query((builder) => {
+ builder.whereIn([field || Model.prototype.idAttribute], ids);
+ if (queryBuilder) {
+ queryBuilder(builder);
+ }
+ return builder;
+ }).fetchAll({ withRelated });
+}
+
+/**
+ * Create a record
+ * @param {bookshelf.Model} Model the model to create
+ * @param {Object} data the data to insert
+ * @param {bookshelf.transaction} transaction optional transaction
+ * @returns {Promise} a query which resolves to the new object
+ */
+export function create({ Model, data, transaction }) {
+ return new Model(data).save({}, { transacting: transaction });
+}
+
+/**
+ * Update a complete record
+ * @param {Object} instance an instance of a bookshelf model
+ * @param {Object} data the complete updated record.
+ * @param {bookshelf.transaction} transaction optional transaction
+ * @returns {Promise} a query which resolves to the updated object
+ */
+export function update({ instance, data, transaction }) {
+ return instance.save(data, { transacting: transaction });
+}
+
+/**
+ * Partially update a record by ID
+ * @param {bookshelf.Model} Model the model to affect
+ * @param {number} objectID the primary key of the object
+ * @param {Object} data the fields to update.
+ * @returns {Promise} a query which resolves to the updated object
+ */
+export function patch({ Model, objectID, data }) {
+ return Model.forge({ [Model.prototype.idAttribute]: objectID }).save(data, {
+ patch: true,
+ });
+}
+
+/**
+ * Destroy a record by ID
+ * @param {bookshelf.Model} Model the model to affect
+ * @param {number} objectID the primary key of the object
+ * @returns {Promise} a query which resolves when the object is destroyed
+ */
+export function destroy({ Model, objectID }) {
+ return new Model({ [Model.prototype.idAttribute]: objectID }).destroy();
+}
diff --git a/src/app/db/service/base/helpers.js b/src/app/db/service/base/helpers.js
new file mode 100644
index 0000000..9ab1119
--- /dev/null
+++ b/src/app/db/service/base/helpers.js
@@ -0,0 +1,240 @@
+/**
+ * Service construction helpers
+ * @module app/db/service/base/helpers
+ */
+
+import {
+ checkPermission,
+ checkParentPermission,
+} from "app/services/permission/helpers";
+import { sendResponse } from "app/db/service/base/response";
+
+/**
+ * Returns a helper function used by an iterator when creating custom routes.
+ * @param {Service} service the affected service
+ * @param {Service} methods the CRUD methods associated with the service
+ * @returns {Function} the method called on the iterable
+ */
+export function createCustomRoute(service, methods) {
+ const { parent } = service.options;
+ /**
+ * Create a custom route. If the route contains `/:id`, the record will be fetched before calling the handlers.
+ * @param {string} method the method name
+ * @param {string} route the route to mount on
+ * @param {Array} handlers a list of middleware
+ * @param {Object} permissions a permissions object
+ */
+ return function addCustomRouteHandler({
+ method,
+ route,
+ handlers,
+ permissions,
+ }) {
+ service.router[(method || "get").toLowerCase()](
+ route,
+ (parent ? [checkParentPermission(parent)] : [])
+ .concat(permissions ? [checkPermission(permissions)] : [])
+ .concat(route.match(/^\/:id/) ? methods.show(service) : [])
+ .concat(handlers)
+ .concat([sendResponse])
+ );
+ };
+}
+
+/**
+ * Returns a helper function used by an iterator when creating CRUD routes.
+ * @param {Service} service the affected service
+ * @param {Object} methods a set of methods to use
+ * @returns {Function} the method called on the iterable
+ */
+export function createCrudRoute(service, methods) {
+ const { parent, permissions, hooks, logging } = service.options;
+ const loggingHook = logging ? [logging] : [];
+ /**
+ * Create a CRUD route
+ * @param {string} type the type of route to create
+ */
+ return function addEnabledRoute(type) {
+ const beforeHooks = hooks?.before[type] || [];
+ const afterHooks = hooks?.after[type] || [];
+ let verb, route, handlers;
+ switch (type) {
+ case "index":
+ verb = "get";
+ route = "/";
+ handlers = [
+ checkPermission(permissions, "read"),
+ ...beforeHooks,
+ methods.index(service),
+ ];
+ break;
+ case "show":
+ verb = "get";
+ route = "/:id";
+ handlers = [
+ checkPermission(permissions, "read"),
+ ...beforeHooks,
+ methods.show(service),
+ ];
+ break;
+ case "create":
+ verb = "post";
+ route = "/";
+ handlers = [
+ checkPermission(permissions, "create"),
+ ...beforeHooks,
+ methods.create(service),
+ ...loggingHook,
+ ];
+ break;
+ case "update":
+ verb = "put";
+ route = "/:id";
+ handlers = [
+ checkPermission(permissions, "read"),
+ ...(hooks?.before?.show || []),
+ methods.show(service),
+ ...beforeHooks,
+ checkPermission(permissions, "update"),
+ methods.update(service),
+ ...loggingHook,
+ ];
+ break;
+ case "updateMany":
+ verb = "put";
+ route = "/";
+ handlers = [
+ checkPermission(permissions, "read"),
+ methods.showMany(service),
+ ...beforeHooks,
+ checkPermission(permissions, "update"),
+ methods.updateMany(service),
+ ...loggingHook,
+ ];
+ break;
+ case "sort":
+ verb = "post";
+ route = "/sort";
+ handlers = [
+ checkPermission(permissions, "read"),
+ ...beforeHooks,
+ checkPermission(permissions, "update"),
+ methods.sort(service),
+ // ...loggingHook,
+ ];
+ break;
+ case "destroy":
+ verb = "delete";
+ route = "/:id";
+ handlers = [
+ checkPermission(permissions, "read"),
+ methods.show(service),
+ ...beforeHooks,
+ checkPermission(permissions, "destroy"),
+ methods.destroy(service),
+ ...loggingHook,
+ ];
+ break;
+ case "destroyMany":
+ verb = "delete";
+ route = "/";
+ handlers = [
+ checkPermission(permissions, "read"),
+ ...beforeHooks,
+ // above, i'm using show to see if the records exist before deleting them
+ // but if deleting many, this is probably being done on the pivot table,
+ // and where the check is accomplished implicitly by the destroy middleware.
+ // if delete permission is ALLOW_FOR_OWNER, we still have to make the check.
+ // methods.showMany(service),
+ checkPermission(permissions, "destroy"),
+ methods.destroyMany(service),
+ ...loggingHook,
+ ];
+ break;
+ default:
+ console.error(`Unknown route: ${type}`);
+ return;
+ }
+ let routeHandlers = []
+ .concat(
+ parent
+ ? [formatParentParameters(service), checkParentPermission(parent)]
+ : []
+ )
+ .concat(handlers)
+ .concat(afterHooks)
+ .concat(type === "destroy" ? [onDestroyCleanupResponse] : [])
+ .concat([sendResponse]);
+ service.router[verb](
+ route,
+ routeHandlers.filter((handler) => !!handler)
+ );
+ };
+}
+
+/**
+ * Helper function to convert request.params to integers where appropriate
+ */
+export function formatParentParameters(service) {
+ const parentIdAttributes = service.idAttributes.slice(1);
+ return function formatParentParametersMiddleware(request, response, next) {
+ const hasInvalidParams = parentIdAttributes.some((parentIdAttribute) => {
+ const value = parseInt(request.params[parentIdAttribute]);
+ if (!value) {
+ return true;
+ }
+ request.params[parentIdAttribute] = value;
+ return false;
+ });
+ if (hasInvalidParams) {
+ return next(new Error("malformed parent ID"));
+ }
+ next();
+ };
+}
+
+/**
+ * Helper function to clean up local data. We make the deleted object available to middleware, but not to the client.
+ * @param {express.request} request the request object
+ * @param {express.response} response the response object
+ * @param {function} next function to proceed to the next middleware
+ */
+export function onDestroyCleanupResponse(request, response, next) {
+ delete response.locals.data;
+ delete response.locals.child;
+ next();
+}
+
+/**
+ * For each returned object, fetch the count of a particular relation
+ * @param {string} relation name of relation
+ * @param {string} valueName (optional) name of the value to set on the output object, defaults to `{relation}_count``
+ * @return {Function} middleware function (`after` hook)
+ */
+export function getRelationCount(relation, as, where) {
+ return async function getRelationCountMiddleware(request, response, next) {
+ if (!response.locals.data) {
+ return next();
+ }
+ const countField = relation + "_count";
+ const items =
+ "length" in response.locals.data
+ ? response.locals.data
+ : [response.locals.data];
+ const getCountPromises = items.map(async (item) => {
+ if (!item || !item.related || countField in item) return;
+ const itemRelation = item.related(relation);
+ if (where) {
+ itemRelation.where(where);
+ }
+ const count = await itemRelation.count();
+ item.set(as || countField, count);
+ });
+ try {
+ await Promise.all(getCountPromises);
+ } catch (error) {
+ console.error(error);
+ }
+ next();
+ };
+}
diff --git a/src/app/db/service/base/index.js b/src/app/db/service/base/index.js
new file mode 100644
index 0000000..188d86a
--- /dev/null
+++ b/src/app/db/service/base/index.js
@@ -0,0 +1,137 @@
+/**
+ * Abstract API service
+ * @module app/db/service/base/index
+ */
+
+import { Router } from "express";
+import {
+ checkAccessToken,
+ checkUserIsActive,
+} from "app/services/authentication/helpers";
+import { indexQueryBuilder, loadColumns } from "app/db/helpers";
+
+import * as serviceMethods from "app/db/service/base/methods";
+import { handleError } from "app/db/service/base/response";
+import {
+ createCustomRoute,
+ createCrudRoute,
+} from "app/db/service/base/helpers";
+
+/**
+ * By default, no API routes are enabled.
+ */
+// const enableAll = ["index", "show", "create", "update", "destroy"];
+const noRoutesEnabled = [];
+
+/**
+ * Create an API service.
+ * @param {Object} options the options dictionary
+ * @param {bookshelf} options.bookshelf the Bookshelf instance created by the server
+ * @param {Service} options.parent the Service that this inherits from
+ * @param {Model|string} options.Model the name of a Bookshelf model, or the model itself
+ * @param {string} options.name optional, the name of this resource. Used for logging
+ * @param {string[]} options.enabled[] specific CRUD APIs to enable
+ * @param {Object} options.hooks extra middleware functions for each endpoint
+ * @param {Object} options.hooks.before middleware called before a DB call
+ * @param {function[]} options.hooks.before.index[] middleware called before index queries
+ * @param {function[]} options.hooks.before.show[] middleware called before show queries
+ * @param {function[]} options.hooks.before.create[] middleware called before create queries
+ * @param {function[]} options.hooks.before.update[] middleware called before update queries
+ * @param {function[]} options.hooks.before.destroy[] middleware called before destroy queries
+ * @param {function} options.logging middleware hook which runs after create/update/destroy queries, to record events
+ * @param {Object} options.hooks.after middleware called after a DB call, similar to `before`
+ * @param {boolean} options.authenticate set to false to disable authentication for this service
+ * @param {Object} options.permissions per-API permissions
+ * @param {Object} options.permissions.read permissions applied when reading records
+ * @param {number[]} options.permissions.read.roles[] allow access with matching user role
+ * @param {boolean} options.permissions.read.owner allow access with matching `user_id`
+ * @param {Object} options.permissions.create permissions applied when creating records
+ * @param {Object} options.permissions.update permissions applied when editing records
+ * @param {Object} options.permissions.destroy permissions applied when destroying records
+ * @param {Object[]} options.routes[] additional list of non-CRUD routes
+ * @param {string} options.routes[].method method to use, get/post/put/delete
+ * @param {route} options.routes[].route endpoint to use, e.g. `/login`
+ * @param {function[]} options.routes[].handlers[] list of middleware functions
+ * @param {privateFields} options.privateFields list of fields which should not be editable through the API
+ * @param {Object} methods optional method lookup, for alternative CRUD middleware
+ * @return {Service} the service object
+ */
+export default async function Service(options, methods = serviceMethods) {
+ const service = { type: "base", options, children: {} };
+
+ const { bookshelf, parent, Model, enabled, routes, authenticate } = options;
+
+ /** Locate the Model specified in the configuration */
+ service.Model =
+ Model && typeof Model === "string" ? bookshelf.model(Model) : options.Model;
+
+ /** Use the model to identify the service's resource name */
+ service.resource = options.name || service.Model?.prototype.tableName;
+ if (!service.resource) {
+ throw new Error("No name or model defined for resource");
+ }
+
+ /** Get the ID attributes of this model, and any parent models */
+ if (service.Model) {
+ if (parent) {
+ service.idAttributes = [service.Model.prototype.idAttribute].concat(
+ parent.idAttributes
+ );
+ } else {
+ service.idAttributes = [service.Model.prototype.idAttribute];
+ }
+ }
+
+ /** Load the column names for this service's Model */
+ service.columns =
+ service.Model && (await loadColumns(bookshelf, service.Model));
+
+ /** Specify the `index` queryBuilder */
+ service.queryBuilder = options.queryBuilder || indexQueryBuilder;
+
+ // console.log("* Creating", service.resource, "service");
+
+ /** Instantiate the Express router. If this is a sub-router, merge the parent's params. */
+ service.router = Router({ mergeParams: !!parent });
+
+ /** Associate the parent resource with this resource */
+ if (parent) {
+ service.parent = parent;
+ }
+
+ /** Most APIs need access to the database, so supply this on the request object */
+ service.router.use((request, response, next) => {
+ request.service = service;
+ request.bookshelf = bookshelf;
+ next();
+ });
+
+ /** Most APIs authenticate, but this can be disabled (for the authentication service) */
+ if (authenticate !== false) {
+ service.router.use(checkAccessToken(options.authenticationOptions || {}));
+ service.router.use(checkUserIsActive);
+ }
+
+ /** Add custom routes first */
+ if (routes) {
+ routes.forEach(createCustomRoute(service, methods));
+ }
+
+ /** Add all enabled CRUD routes, if this service has a Model. */
+ const enabledRoutes = (service.Model && enabled) || noRoutesEnabled;
+ enabledRoutes.forEach(createCrudRoute(service, methods));
+
+ /** Add error-handling middleware */
+ service.router.use(handleError);
+
+ /** Method to attach a child service to this service's router
+ * @param {string} route base route of this service
+ * @param {Service} childService instance of the child service
+ */
+ service.use = (route, childService) => {
+ service.router.use(`${route}`, childService.router);
+ service.children[route] = childService;
+ };
+
+ return service;
+}
diff --git a/src/app/db/service/base/many.js b/src/app/db/service/base/many.js
new file mode 100644
index 0000000..1791694
--- /dev/null
+++ b/src/app/db/service/base/many.js
@@ -0,0 +1,205 @@
+/**
+ * Service API methods that affect multiple records
+ * @module app/db/service/base/methods
+ */
+
+import * as db from "app/db/query";
+import { reduceValidColumns } from "app/db/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * API to query for multiple records by ID
+ */
+export function showMany(service) {
+ const { Model, parent, resource } = service;
+ const { childRelation } = service.options;
+ const idAttribute = service.idAttributes[0];
+ return async function showManyMiddleware(request, response, next) {
+ const { user, permission, body } = request;
+ const ids = body.map((item) => item[idAttribute]).filter((id) => !!id);
+ let data;
+ try {
+ if (parent) {
+ // Fetch the immediate parent of the pivot table based on the name of the parent resource.
+ // This instance is added to the `request.parents` object when performing the permissions check.
+ const parentInstance = request.parents[parent.resource];
+ data = await parentInstance
+ .related(childRelation)
+ .query((builder) => builder.whereIn(idAttribute, ids));
+ } else {
+ data = await db.showIDs({
+ Model,
+ ids,
+ });
+ }
+ } catch (error) {
+ debug(`${resource} Show error`);
+ debug(error);
+ return next(error);
+ }
+ if (!data) {
+ response.locals = { data: [] };
+ next();
+ } else if (
+ permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+ data.some((item) => item.get("user_id") !== user.user_id)
+ ) {
+ next(new Error("PermissionsError"));
+ } else {
+ response.locals = { data };
+ next();
+ }
+ };
+}
+
+/**
+ * API to update multiple records
+ */
+export function updateMany(service) {
+ return async function updateManyMiddleware(request, response, next) {
+ const data = await handleUpdateManyWithTransaction(
+ service,
+ request,
+ response
+ );
+ response.locals.data = data;
+ next();
+ };
+}
+
+/**
+ * Update multiple records using a transaction
+ */
+export async function handleUpdateManyWithTransaction(
+ service,
+ request,
+ response
+) {
+ const { Model, idAttributes, columns } = service;
+ const { bookshelf, privateFields } = service.options;
+ const idAttribute = idAttributes[0];
+ return await bookshelf.transaction((transaction) => {
+ const { data: instances } = response.locals;
+ const { params, user, permission, body } = request;
+ const instanceLookup = instances.reduce((lookup, instance) => {
+ lookup[instance.id] = instance;
+ return lookup;
+ }, {});
+ const promises = body.map((item) => {
+ const itemId = item[idAttribute];
+ if (!itemId) {
+ return handleCreate({
+ Model,
+ body: item,
+ idAttributes,
+ params,
+ columns,
+ privateFields,
+ transaction,
+ });
+ } else if (itemId in instanceLookup) {
+ return handleUpdate({
+ instance: instanceLookup[itemId],
+ body: item,
+ user,
+ permission,
+ columns,
+ privateFields,
+ transaction,
+ });
+ } else {
+ throw new Error("item id not found on this record");
+ }
+ });
+ return Promise.all(promises);
+ });
+}
+
+/**
+ * API to destroy multiple records
+ */
+export function destroyMany(service) {
+ const { Model, idAttributes } = service;
+ const [idAttribute, ...parentIdAttributes] = idAttributes;
+ return async function destroyManyMiddleware(request, response, next) {
+ const idsToDelete = request.body[idAttribute];
+ let instances;
+ try {
+ instances = await db.showIDs({ Model, ids: idsToDelete });
+ instances.forEach((instance) => {
+ parentIdAttributes.forEach((parentIdAttribute) => {
+ if (
+ instance.get(parentIdAttribute) !==
+ request.params[parentIdAttribute]
+ ) {
+ throw new Error("parent mismatch");
+ }
+ });
+ });
+ await Promise.all(instances.map((instance) => instance.destroy()));
+ } catch (error) {
+ debug(`${service.resource} destroy many error`);
+ debug(error);
+ return next(new Error(error));
+ }
+ response.locals.data = instances;
+ response.locals.success = true;
+ response.locals.id = idsToDelete;
+ next();
+ };
+}
+
+/**
+ * Insert a single record
+ */
+export function handleCreate({
+ idAttributes,
+ params,
+ columns,
+ body,
+ Model,
+ privateFields,
+ transaction,
+}) {
+ body = reduceValidColumns(body, columns, privateFields);
+ if (idAttributes) {
+ idAttributes.forEach((idAttribute) => {
+ if (idAttribute in params && idAttribute in columns) {
+ body[idAttribute] = parseInt(params[idAttribute]);
+ }
+ });
+ }
+ return db.create({ Model: Model, data: body, transaction });
+}
+
+/**
+ * Update a single record
+ */
+export function handleUpdate({
+ instance,
+ body,
+ user,
+ permission,
+ columns,
+ privateFields,
+ transaction,
+}) {
+ if (
+ permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+ instance.get("user_id") !== user.user_id
+ ) {
+ throw new Error("PermissionsError");
+ }
+ body = reduceValidColumns(body, columns, privateFields);
+ return db.update({
+ instance,
+ data: body,
+ transaction,
+ });
+}
diff --git a/src/app/db/service/base/methods.js b/src/app/db/service/base/methods.js
new file mode 100644
index 0000000..be84d0c
--- /dev/null
+++ b/src/app/db/service/base/methods.js
@@ -0,0 +1,217 @@
+/**
+ * Service API methods
+ * @module app/db/service/base/methods
+ */
+
+import * as db from "app/db/query";
+import { reduceValidColumns } from "app/db/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Export methods for dealing with multiple records
+ */
+export { showMany, updateMany, destroyMany } from "app/db/service/base/many";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * API to filter and paginate lists of resources
+ */
+export function index(service) {
+ const { columns } = service;
+ return async function indexMiddleware(request, response, next) {
+ const { query: userQuery, params, permission, user } = request;
+ const { paginate } = service.options;
+ const queryBuilder = service.queryBuilder;
+ const query = {
+ ...userQuery,
+ ...params,
+ };
+ const withRelated = userQuery.related ? userQuery.related.split(",") : [];
+ if (permission === PERMISSIONS.ALLOW_FOR_OWNER) {
+ query.user_id = user.user_id;
+ }
+ let data, pagination;
+ let promises = [
+ db.index({
+ Model: service.Model,
+ query,
+ paginate,
+ user,
+ columns,
+ queryBuilder,
+ withRelated,
+ }),
+ ];
+ if (paginate) {
+ promises.push(
+ db.count({
+ Model: service.Model,
+ query,
+ paginate,
+ user,
+ columns,
+ queryBuilder,
+ })
+ );
+ } else {
+ promises.push(new Promise((resolve) => resolve({})));
+ }
+ try {
+ [data, pagination] = await Promise.all(promises);
+ } catch (error) {
+ debug(`${service.resource} Index error`);
+ debug(error);
+ console.error(error);
+ return next(error);
+ }
+ response.locals = {
+ data,
+ pagination,
+ query,
+ };
+ next();
+ };
+}
+
+/**
+ * API to query for a single record by ID
+ */
+export function show(service) {
+ return async function showMiddleware(request, response, next) {
+ const { user, permission, query } = request;
+ const withRelated = query.related ? query.related.split(",") : [];
+ let data;
+ try {
+ data = await db.show({
+ Model: service.Model,
+ objectID: parseInt(request.params.id),
+ withRelated,
+ });
+ } catch (error) {
+ debug(`${service.resource} Show error`);
+ debug(error);
+ return next(error);
+ }
+ if (!data) {
+ response.locals = {};
+ next();
+ } else if (
+ permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+ data.get("user_id") !== user.user_id
+ ) {
+ next(new Error("PermissionsError"));
+ } else {
+ response.locals = { data };
+ next();
+ }
+ };
+}
+
+/**
+ * API to insert a new record
+ */
+export function create(service) {
+ return async function createMiddleware(request, response, next) {
+ const body = reduceValidColumns(
+ request.body,
+ service.columns,
+ service.options.privateFields
+ );
+ if (service.options.parent) {
+ service.idAttributes.forEach((idAttribute) => {
+ if (idAttribute in request.params && idAttribute in service.columns) {
+ body[idAttribute] = request.params[idAttribute];
+ }
+ });
+ }
+ let data;
+ try {
+ data = await db.create({ Model: service.Model, data: body });
+ } catch (error) {
+ debug(`${service.resource} create error`);
+ console.error(error);
+ return next(error);
+ }
+ response.locals = { data };
+ next();
+ };
+}
+
+/**
+ * API to update a single record
+ */
+export function update(service) {
+ return async function updateMiddleware(request, response, next) {
+ const { data: instance } = response.locals;
+ const { user, permission } = request;
+ if (
+ permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+ instance.get("user_id") !== user.user_id
+ ) {
+ return next(new Error("PermissionsError"));
+ }
+ const body = reduceValidColumns(
+ request.body,
+ service.columns,
+ service.options.privateFields
+ );
+ let data;
+ try {
+ data = await db.update({
+ instance,
+ data: body,
+ });
+ } catch (error) {
+ debug(`${service.resource} update error`);
+ debug(error);
+ next(new Error(error));
+ }
+ response.locals = { data };
+ next();
+ };
+}
+
+/**
+ * API to sort records
+ */
+export function sort(service) {
+ return async function sortMiddleware(request, response, next) {
+ const { ids } = request.body;
+ const items = await db.showIDs({
+ Model: service.Model,
+ ids,
+ });
+ for (let item of items) {
+ item.set("sort_order", ids.indexOf(item.id));
+ }
+ await Promise.all(items.map((item) => item.save()));
+ response.locals.success = true;
+ next();
+ };
+}
+
+/**
+ * API to destroy a single record
+ */
+export function destroy(service) {
+ return async function destroyMiddleware(request, response, next) {
+ try {
+ await db.destroy({
+ Model: service.Model,
+ objectID: request.params.id,
+ });
+ } catch (error) {
+ debug(`${service.resource} destroy error`);
+ console.error(error);
+ return next(new Error(error));
+ }
+ response.locals.success = true;
+ response.locals.id = request.params.id;
+ next();
+ };
+}
diff --git a/src/app/db/service/base/response.js b/src/app/db/service/base/response.js
new file mode 100644
index 0000000..a4bac60
--- /dev/null
+++ b/src/app/db/service/base/response.js
@@ -0,0 +1,118 @@
+/**
+ * Service API responses
+ * @module app/db/service/base/response
+ */
+
+import { zipCSVs, stringifyCSV, sanitizeCSV } from "app/utils/file_utils";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service");
+
+/**
+ * Middleware to return the response as JSON or CSV
+ */
+export async function sendResponse(request, response) {
+ if (response.locals.csv) {
+ if (response.locals.csv.files) {
+ await sendCSVZipResponse(response);
+ } else {
+ await sendCSVResponse(response);
+ }
+ } else {
+ await sendJSONResponse(response);
+ }
+}
+
+/**
+ * Respond with a ZIP file containing multiple CSVs
+ * @param {Response} response the response object
+ */
+async function sendCSVZipResponse(response) {
+ const zipData = await zipCSVs(response.locals.csv.files);
+ response.set("Content-Type", "application/zip");
+ response.set(
+ "Content-Disposition",
+ `attachment; filename=${response.locals.csv.filename}`
+ );
+ response.set("Content-Length", zipData.length);
+ response.set("Access-Control-Expose-Headers", "Content-Disposition");
+ response.write(zipData, "binary");
+ response.end(null, "binary");
+}
+
+/**
+ * Respond with a single CSV
+ * @param {Response} response the response object
+ */
+async function sendCSVResponse(response) {
+ const csvData = await stringifyCSV(sanitizeCSV(response.locals.csv.data));
+ response.set("Content-Type", "text/csv; charset=utf-8");
+ response.set(
+ "Content-Disposition",
+ `attachment; filename=${response.locals.csv.filename}`
+ );
+ response.set("Content-Length", csvData.length);
+ response.set("Access-Control-Expose-Headers", "Content-Disposition");
+ response.send(csvData);
+}
+
+/**
+ * Respond with JSON
+ * @param {Response} response the response object
+ */
+async function sendJSONResponse(response) {
+ response.json(response.locals);
+}
+
+/**
+ * Error-handling middleware
+ * @param {Error} error an error object
+ * @param {express.request} request the request object
+ * @param {express.response} response the response object
+ * @param {function} next function to proceed to the next middleware
+ */
+export function handleError(error, request, response, next) {
+ const { errors } = request.service.options;
+ debug("Error", error.name, error.message);
+ let message;
+
+ if (errors) {
+ Object.keys(errors).some((key) => {
+ if (error.message.match(key)) {
+ message = errors[key];
+ return true;
+ }
+ return false;
+ });
+ if (message) {
+ response.status(403).send({ code: 403, error: message });
+ return;
+ }
+ }
+
+ if (error.name === "UnauthorizedError") {
+ debug("Unauthorized");
+ response.status(401).send({ code: 401, error: error.message });
+ } else if (error.message === "EmptyResponse") {
+ debug("Not found");
+ response.status(404).send({ code: 404, error: error.message });
+ } else if (error.message === "UserNotActive") {
+ debug("User not active");
+ response.status(401).send({ code: 401, error: error.message });
+ } else if (error.message === "PermissionsError") {
+ debug("Insufficient permissions");
+ response.status(401).send({ code: 401, error: error.message });
+ } else {
+ debug("Unexpected error");
+ // debug(error);
+ response.status(403).send({
+ code: 403,
+ error: error.name || error.message,
+ message: error.message,
+ });
+ next();
+ }
+}
diff --git a/src/app/db/service/pivot/helpers.js b/src/app/db/service/pivot/helpers.js
new file mode 100644
index 0000000..1d8f7f2
--- /dev/null
+++ b/src/app/db/service/pivot/helpers.js
@@ -0,0 +1,113 @@
+/**
+ * Pivot Table Helpers
+ * @module app/db/service/pivot/helpers
+ */
+
+import * as db from "app/db/query";
+
+/**
+ * Fetch a set of database objects from a pivot table via relations
+ * @param {bookshelf.Model} Model the pivot table model to query
+ * @param {string} parentIdAttribute the parent ID attribute on the pivot
+ * @param {number} parentId the parent ID to query
+ * @param {string} childIdAttribute the child ID attribute on the pivot
+ * @param {number} childId the child ID to query
+ */
+export function indexPivotTable({
+ Model,
+ parentIdAttribute,
+ parentId,
+ childIdAttribute,
+ childId,
+}) {
+ return Model.query((builder) => {
+ builder.where(parentIdAttribute, parentId);
+ if (Array.isArray(childId)) {
+ builder.whereIn(childIdAttribute, childId);
+ } else {
+ builder.andWhere(childIdAttribute, childId);
+ }
+ }).fetchAll();
+}
+
+/**
+ * Get the parent, child, and pivot models from a service, as well as their ID attribute names
+ * @param {Service} service the pivot table service
+ * @return {object} the models and ID attribute names
+ */
+export function getPivotModels(service) {
+ const {
+ parent,
+ Model,
+ ChildModel,
+ childRelation,
+ parentPivotRelation,
+ pivotChildRelation,
+ } = service.options;
+ const { Model: ParentModel } = service.options.parent;
+ const parentIdAttribute = ParentModel.prototype.idAttribute;
+ const pivotIdAttribute = Model.prototype.idAttribute;
+ const childIdAttribute = ChildModel.prototype.idAttribute;
+ const parentTableName = ParentModel.prototype.tableName;
+ const pivotTableName = Model.prototype.tableName;
+ const childTableName = ChildModel.prototype.tableName;
+ return {
+ parent,
+ Model,
+ ParentModel,
+ ChildModel,
+ parentTableName,
+ pivotTableName,
+ childTableName,
+ parentIdAttribute,
+ pivotIdAttribute,
+ childIdAttribute,
+ parentPivotRelation,
+ pivotChildRelation,
+ childRelation,
+ };
+}
+
+/**
+ * Create a single instance
+ * @param {bookshelf.Model} options.Model the model
+ * @param {Object} options.data data to add
+ * @param {Array} options.instances fetched instances to dedupe
+ * @return {Object} result
+ */
+export async function handleCreateOne({ Model, data, instances }) {
+ if (instances.length) {
+ throw new Error("pivot relation already exists");
+ }
+ return await db.create({ Model, data });
+}
+
+/**
+ * Create multiple instances
+ * @param {bookshelf.Model} options.Model the model
+ * @param {Object} options.data data to add
+ * @param {Array} options.instances fetched instances to dedupe
+ * @param {string} options.childIdAttribute the child ID, found on the body, which should point to an array
+ * @return {Object} result
+ */
+export async function handleCreateMany({
+ Model,
+ data,
+ childIdAttribute,
+ instances,
+}) {
+ let { [childIdAttribute]: child_ids, ...columns } = data;
+ const matched = new Set(
+ instances.map((instance) => instance.get(childIdAttribute))
+ );
+ return await Promise.all(
+ Array.from(new Set(child_ids))
+ .filter((child_id) => !matched.has(child_id))
+ .map((child_id) =>
+ db.create({
+ Model,
+ data: { ...columns, [childIdAttribute]: child_id },
+ })
+ )
+ );
+}
diff --git a/src/app/db/service/pivot/index.js b/src/app/db/service/pivot/index.js
new file mode 100644
index 0000000..ded69b8
--- /dev/null
+++ b/src/app/db/service/pivot/index.js
@@ -0,0 +1,47 @@
+/**
+ * Pivot Table API Service
+ * @module app/db/service/pivot/index
+ */
+
+import Service from "app/db/service/base";
+import * as pivotMethods from "app/db/service/pivot/methods";
+import { loadColumns } from "app/db/helpers";
+
+/**
+ * Create a relational API service to access models via a pivot table.
+ * All options are the same as on a normal service, but with a few changes:
+ * @param {Model|string} options.Model the Bookshelf pivot model
+ * @param {string} options.ChildModel the Bookshelf model inheriting from the pivot
+ * @param {string} options.parentPivotRelation relation method on the parent that will access the pivot
+ * @param {string} options.pivotChildRelation relation method on the pivot that will access the child
+ * @param {string} options.childRelation relation method on the parent that will access the children
+ * @return {Service} the service object
+ */
+export default async function PivotTableService(options) {
+ const { ChildModel, bookshelf } = options;
+
+ /** Locate the Model specified in the configuration */
+ options.ChildModel =
+ ChildModel && typeof ChildModel === "string"
+ ? bookshelf.model(ChildModel)
+ : ChildModel;
+
+ /** Due to the way English is inflected, sometimes these are the same and only one needs to be specified */
+ options.pivotChildRelation =
+ options.pivotChildRelation || options.childRelation;
+
+ options.pivotColumns =
+ ChildModel && (await loadColumns(bookshelf, ChildModel));
+
+ // /** Use the model to identify the service's resource name */
+ // options.parentResource = options.name || service.Model?.prototype.tableName;
+ // if (!service.resource) {
+ // throw new Error("No name or model defined for resource");
+ // }
+
+ const service = await Service(options, pivotMethods);
+ service.type = "pivot";
+ service.ChildModel = options.ChildModel;
+
+ return service;
+}
diff --git a/src/app/db/service/pivot/methods.js b/src/app/db/service/pivot/methods.js
new file mode 100644
index 0000000..1f54345
--- /dev/null
+++ b/src/app/db/service/pivot/methods.js
@@ -0,0 +1,330 @@
+/**
+ * Pivot Table Service API Methods
+ * @module app/db/service/pivot/methods
+ */
+
+import * as db from "app/db/query";
+import {
+ buildPaginationResponse,
+ getOffsetAndLimit,
+ getSort,
+ getQueryFilters,
+ reduceValidColumns,
+ tableNameToModelName,
+} from "app/db/helpers";
+import {
+ indexPivotTable,
+ getPivotModels,
+ handleCreateOne,
+ handleCreateMany,
+} from "app/db/service/pivot/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service:pivot");
+
+/**
+ * API to index a model via a pivot table. Allows pagination, filtering.
+ */
+export function index(service) {
+ const { queryBuilder } = service;
+ const { pivotColumns, paginate } = service.options;
+ const {
+ parent,
+ Model,
+ ChildModel,
+ pivotTableName,
+ childTableName,
+ parentIdAttribute,
+ childIdAttribute,
+ parentPivotRelation,
+ childRelation,
+ } = getPivotModels(service);
+
+ const pivotAttribute = Model.prototype.idAttribute.replace(/_id$/, "");
+ // console.log(pivotIdAttribute, pivotAttribute);
+
+ return async function indexPivotMiddleware(request, response, next) {
+ const { query } = request;
+ const withRelated = query.related ? query.related.split(",") : [];
+
+ const filters = getQueryFilters(query);
+ const hasFilters = Object.keys(filters).length > 0;
+ const { offset, limit } = getOffsetAndLimit(query, paginate);
+ const { sortField, sortDirection, sortCount } = getSort(query, ChildModel);
+
+ // Fetch the immediate parent of the pivot table based on the name of the parent resource.
+ // This instance is added to the `request.parents` object when performing the permissions check.
+ const parentInstance = request.parents[parent.resource];
+
+ // Fetch the children of the pivot table.
+ let childData;
+
+ if (sortCount) {
+ // Sort by the count of a secondary pivot relation on the child
+ const sortTableName = childTableName + "_" + sortField;
+ const sortOrderName = sortField + "_count";
+ const sortTableModel = request.bookshelf.model(
+ tableNameToModelName(childTableName + "_" + sortField)
+ );
+ if (!sortTableModel) {
+ return next(new Error("No such pivot table"));
+ }
+ let query = request.bookshelf.knex
+ .from(childTableName)
+ .select(
+ childTableName + ".*",
+ request.bookshelf
+ .knex(sortTableName)
+ .count("*")
+ .whereRaw("?? = ??", [
+ sortTableName + "." + childIdAttribute,
+ childTableName + "." + childIdAttribute,
+ ])
+ .as(sortOrderName)
+ )
+ .leftJoin(
+ pivotTableName,
+ pivotTableName + "." + childIdAttribute,
+ childTableName + "." + childIdAttribute
+ )
+ .where(
+ pivotTableName + "." + parentIdAttribute,
+ "=",
+ parentInstance.id
+ );
+
+ if (hasFilters) {
+ query.andWhere((builder) =>
+ queryBuilder(builder, pivotColumns, filters)
+ );
+ }
+
+ childData = await query
+ .orderBy(sortOrderName, sortDirection)
+ .offset(offset)
+ .limit(limit);
+ } else {
+ // Typical sort
+ childData = await parentInstance
+ .related(childRelation)
+ .query((builder) => {
+ if (hasFilters) {
+ builder.where((builder) =>
+ queryBuilder(builder, pivotColumns, filters)
+ );
+ }
+ builder.orderBy(sortField, sortDirection);
+ if (limit) {
+ builder.limit(limit);
+ }
+ if (offset) {
+ builder.offset(offset);
+ }
+ return builder;
+ })
+ .fetch({ withRelated });
+
+ // Fetch the pivot table in case there are any necessary values on it
+ await Promise.all(
+ childData.map(async (item) => {
+ item.set(pivotAttribute, await item.pivot.fetch());
+ })
+ );
+ }
+
+ // Count the pivot table and generate pagination
+ let rowCount;
+ if (hasFilters) {
+ rowCount = await parentInstance
+ .related(childRelation)
+ .where((builder) => queryBuilder(builder, pivotColumns, filters))
+ .count();
+ } else {
+ rowCount = await parentInstance.related(parentPivotRelation).count();
+ }
+ const pagination = buildPaginationResponse({ rowCount, query, paginate });
+
+ response.locals = { data: childData, pagination, query };
+ next();
+ };
+}
+
+/**
+ * API to fetch a single relation via the pivot table.
+ */
+export function show(service) {
+ const {
+ parent,
+ Model,
+ parentIdAttribute,
+ childIdAttribute,
+ pivotChildRelation,
+ } = getPivotModels(service);
+
+ return async function showPivotMiddleware(request, response, next) {
+ const { query, parents } = request;
+ const parentInstance = parents[parent.resource];
+ const withRelated = query.related ? query.related.split(",") : [];
+
+ let child, data;
+
+ try {
+ data = await db.show({
+ Model: Model,
+ field: childIdAttribute,
+ objectID: parseInt(request.params.id),
+ criteria: {
+ [parentIdAttribute]: parentInstance.get(parentIdAttribute),
+ },
+ withRelated,
+ });
+ } catch (error) {
+ debug(`${service.resource} Error fetching pivot`);
+ debug(error);
+ return next(error);
+ }
+
+ if (!data) {
+ response.locals = {};
+ next();
+ }
+
+ try {
+ child = await data.related(pivotChildRelation).fetch();
+ } catch (error) {
+ debug(`${service.resource} Error fetching child`);
+ debug(error);
+ return next(error);
+ }
+
+ response.locals = { child, data };
+ next();
+ };
+}
+
+/**
+ * API to insert a new record
+ */
+export function create(service) {
+ const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+ service
+ );
+ return async function createPivotMiddleware(request, response, next) {
+ const { params } = request;
+ let data;
+ const body = reduceValidColumns(
+ request.body,
+ service.columns,
+ service.options.privateFields
+ );
+ if (service.options.parent) {
+ service.idAttributes.forEach((idAttribute) => {
+ if (idAttribute in params && idAttribute in service.columns) {
+ body[idAttribute] = parseInt(params[idAttribute]);
+ }
+ });
+ }
+ try {
+ const instances = await indexPivotTable({
+ Model,
+ parentIdAttribute,
+ parentId: body[parentIdAttribute],
+ childIdAttribute,
+ childId: body[childIdAttribute],
+ });
+ if (Array.isArray(body[childIdAttribute])) {
+ data = await handleCreateMany({
+ Model: service.Model,
+ data: body,
+ childIdAttribute,
+ instances,
+ });
+ } else {
+ data = await handleCreateOne({
+ Model: service.Model,
+ data: body,
+ instances,
+ });
+ }
+ } catch (error) {
+ debug(`${service.resource} create error`);
+ console.error(error);
+ return next(error);
+ }
+ response.locals = { data };
+ next();
+ };
+}
+
+/**
+ * API to update a single record
+ */
+export function update(service) {
+ return async function updatePivotMiddleware(request, response, next) {
+ const { data: instance } = response.locals;
+ const { user, permission } = request;
+ if (
+ permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+ instance.get("user_id") !== user.user_id
+ ) {
+ return next(new Error("PermissionsError"));
+ }
+ const body = reduceValidColumns(
+ request.body,
+ service.columns,
+ service.options.privateFields
+ );
+ let data;
+ try {
+ data = await db.update({
+ instance,
+ data: body,
+ });
+ } catch (error) {
+ debug(`${service.resource} update error`);
+ debug(error);
+ next(new Error(error));
+ }
+ response.locals = { data };
+ next();
+ };
+}
+
+/**
+ * API to destroy a pivot table relation.
+ */
+export function destroy(service) {
+ const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+ service
+ );
+ return async function destroyPivotMiddleware(request, response, next) {
+ const idsToDelete = request.params.id || request.body[childIdAttribute];
+ try {
+ const instances = await indexPivotTable({
+ Model,
+ parentIdAttribute,
+ parentId: request.params[parentIdAttribute],
+ childIdAttribute,
+ childId: idsToDelete,
+ });
+ await Promise.all(instances.map((instance) => instance.destroy()));
+ } catch (error) {
+ debug(`${service.resource} destroy error`);
+ debug(error);
+ return next(new Error(error));
+ }
+ response.locals.success = true;
+ response.locals.id = idsToDelete;
+ next();
+ };
+}
+
+/**
+ * API to destroy many records. Note that the normal destroy API accomplishes this.
+ * @type {Function}
+ */
+export const destroyMany = destroy;
generated by cgit v1.2.3-70-g09d2 (git 2.51.0) at 2025-12-10 09:07:29 +0000