waiterio/api

View on GitHub
common/databaseHelpers.js

Summary

Maintainability
A
1 hr
Test Coverage
'use strict';

/**
 * This helper function constructs the ORDER BY statement
 * Fields that are to be sorted by can be passed comma separated.
 * A - before the column that is to be sorted, indicates a descending
 * sort order, if there is no dash, ascending order will be used.
 *
 * sortQuery example: -username,id,-email
 *           result: username DESC, id ASC, email DESC
 *
 * @param sortQuery
 * @returns {string}
 */
module.exports.getOrderByQuery = function(sortQuery) {
    if (typeof sortQuery !== 'undefined') {
        const orderByList = [];
        const sortables = sortQuery.toString().split(',');

        sortables.forEach(function(value) {
            let sortOrder = '';
            let sortField = '';

            const matches = value.match(/^([\-])(.*)/);
            if (matches !== null) {
                sortOrder = 'DESC';
                sortField = matches[2].trim();
            } else {
                sortOrder = 'ASC';
                sortField = value.trim();
            }

            orderByList.push(`${sortField} ${sortOrder}`);
        });

        if (orderByList.length > 0) {
            return ` ORDER BY ${orderByList.join(', ')}`;
        }
    }

    return ' ORDER BY id ASC';
};

/**
 * This function returns an object containing information that
 * may be used to build an insert query.
 *
 * Sample data:
 * const passwordData = [
     { field: 'username', input: req.body.username, rules: { notEmpty: true, type: 'string' } },
     { field: 'password', input: hashedPassword, rules: { notEmpty: true, type: 'string' } },
     { field: 'role', input: req.body.role, rules: { notEmpty: true, type: 'string' } },
     { field: 'email', input: req.body.email, rules: { notEmpty: true, type: 'string' } } ];
 *
 * @param data
 * @returns {object}
 */
module.exports.getInsertQueryData = function(data) {
    const queryFields = [];
    const queryValues = [];
    const replacementVars = [];

    if (typeof data !== 'undefined') {
        let valueCounter = 1;

        data.forEach(function(value) {
            if (typeof value.input !== 'undefined') {
                queryFields.push(value.field);
                queryValues.push('?');
                replacementVars.push(value.input);

                valueCounter++;
            }
        });
    }

    return { fields: queryFields, values: queryValues, fieldQuery: `(${queryFields.join()})`, valueQuery: `(${queryValues.join()})`, vars: replacementVars };
};

/**
 * @param data
 * @returns {{updateQuery: string, vars: Array}}
 */
module.exports.getUpdateQueryData = function(data) {
    const queryParts = [];
    const replacementVars = [];

    if (typeof data !== 'undefined') {
        data.forEach(function(value) {
            if (typeof value.input !== 'undefined') {
                queryParts.push(`${value.field}=?`);
                replacementVars.push(value.input);
            }
        });
    }

    return { updateQuery: `${queryParts.join()}`, vars: replacementVars };
};

/**
 * @param limitNumber
 * @returns {string}
 */
module.exports.getLimitQuery = function(limitNumber) {
    if (typeof limitNumber !== 'undefined') {
        if (!isNaN(limitNumber)) {
            let limit = parseInt(limitNumber, 10);

            if (limit <= 0) {
                limit = 0;
            }

            return ` LIMIT ${limit}`;
        }
    }

    return '';
};

/**
 * @param queryOptions
 * @returns {*}
 */
module.exports.prepareQueryOptions = function(queryOptions) {
    const preparedQueryOptions = queryOptions;

    if (typeof preparedQueryOptions !== 'undefined' && preparedQueryOptions !== null) {
        if (typeof queryOptions.fields === 'undefined') {
            preparedQueryOptions.fields = '*';
        }

        if (typeof queryOptions.orderBy === 'undefined') {
            preparedQueryOptions.orderBy = '';
        }

        if (typeof queryOptions.limit === 'undefined') {
            preparedQueryOptions.limit = '';
        }

        if (typeof queryOptions.table === 'undefined') {
            throw new Error('table cannot be undefined');
        }
    }

    return preparedQueryOptions;
};