ai crud.js

crud.js

var crud = {
    objects: {},
    api: {
        get: function (params) {

            var QUERY_STRING,
                QUERY_PARAMS = [],
                WHERE_STRING,
                WHERE_PIECES = [],
                HOW = params.how || 'and',
                x = 1;

            QUERY_STRING = "SELECT * FROM " + params.what;

            if (!!params.data && Object.keys(params.data).length > 0) {

                if (!!params.how && sys.api.help.is.array(params.data)) {
                    for (var col = 0, t = params.data.length; col < t; col++) {
                        for (var row in params.data[col]) {
                            if (params.data[col].hasOwnProperty(row)) {

                                if (null === params.data[col][row]) {
                                    WHERE_STRING = row + ' IS NULL';
                                } else {
                                    WHERE_STRING = row;
                                    WHERE_STRING += '=';
                                    WHERE_STRING += "$" + x;
                                    QUERY_PARAMS.push(params.data[col][row]);
                                }

                                WHERE_PIECES.push(WHERE_STRING);

                            }
                        }
                        x++;
                    }
                } else {
                    for (var i in params.data) {
                        if (params.data.hasOwnProperty(i)) {

                            if (null === params.data[i]) {
                                WHERE_STRING = i + ' IS NULL';
                            } else {
                                WHERE_STRING = i;
                                WHERE_STRING += '=';
                                WHERE_STRING += "$" + x;
                                QUERY_PARAMS.push(params.data[i]);

                                x++;
                            }

                            WHERE_PIECES.push(WHERE_STRING);

                        }
                    }
                }

                QUERY_STRING += " WHERE " + WHERE_PIECES.join(' ' + HOW + ' ');

            }

            if (!!params.order) {
                if (sys.api.help.is.array(params.order)) {
                    var order_params = [];

                    for (var i = 0, t = params.order.length; i < t; i++) {
                        var key = Object.keys(params.order[i])[0];
                        order_params.push('"' + key + '" ' + params.order[i][key]);
                    }

                    QUERY_STRING += " ORDER BY " + order_params.join(', ');
                } else if (Object.keys(params.order).length > 0) {
                    var order_params = [];

                    for (var i in params.order) {
                        if (params.order.hasOwnProperty(i)) {
                            order_params.push('"' + i + '" ' + params.order[i]);
                        }
                    }

                    QUERY_STRING += " ORDER BY " + order_params.join(', ');
                }
            }

            if (!!params.limit && params.limit.length > 0) {
                QUERY_STRING += " LIMIT " + params.limit[1] + ' OFFSET ' + params.limit[0];
            }

            return {string: QUERY_STRING, params: QUERY_PARAMS, hash: params.hash};

        },
        add: function (params) {

            var QUERY_STRING,
                QUERY_PARAMS = [],
                ADD_PIECES_COLUMNS = [],
                ADD_PIECES_VALUES = [],
                x = 1;

            for (var i in params.data) {
                if (params.data.hasOwnProperty(i)) {
                    ADD_PIECES_COLUMNS.push(i);
                    ADD_PIECES_VALUES.push('$' + x);
                    QUERY_PARAMS.push(params.data[i]);
                    x++;
                }
            }

            QUERY_STRING = "INSERT INTO " + params.what + " (" + ADD_PIECES_COLUMNS.join(', ') + ") VALUES(" + ADD_PIECES_VALUES.join(", ") + ") RETURNING *";

            return {string: QUERY_STRING, params: QUERY_PARAMS};
        },
        set: function (params) {

            var QUERY_STRING,
                QUERY_PARAMS = [],
                WHERE_STRING = '',
                WHERE_PIECES = [],
                SET_STRING = '',
                SET_PIECES = [],
                x = 1;

            QUERY_STRING = "UPDATE " + params.what;

            for (var i in params.data) {
                if (params.data.hasOwnProperty(i)) {
                    QUERY_PARAMS.push(params.data[i]);

                    SET_STRING = i;
                    SET_STRING += '=';
                    SET_STRING += "$" + x;
                    SET_PIECES.push(SET_STRING);

                    if (/^\d+$/.test(params.data[i])) {
                        params.data[i] = parseInt(params.data[i]);
                    }
                    x++;
                }
            }

            QUERY_STRING += " SET " + SET_PIECES.join(', ');

            for (var i in params.where) {
                if (params.where.hasOwnProperty(i)) {
                    QUERY_PARAMS.push(params.where[i]);

                    WHERE_STRING = i;
                    WHERE_STRING += '=';
                    WHERE_STRING += "$" + x;
                    WHERE_PIECES.push(WHERE_STRING);

                    if (/^\d+$/.test(params.where[i])) {
                        params.where[i] = parseInt(params.where[i]);
                    }
                    x++;
                }
            }


            QUERY_STRING += "  WHERE " + WHERE_PIECES.join(', ');

            if (!!params.order) {
                if (sys.api.help.is.array(params.order)) {
                    var order_params = [];

                    for (var i = 0, t = params.order.length; i < t; i++) {
                        var key = Object.keys(params.order[i])[0];
                        order_params.push(key + ' ' + params.order[i][key]);
                    }

                    QUERY_STRING += " ORDER BY " + order_params.join(', ');
                } else if (Object.keys(params.order).length > 0) {
                    var order_params = [];

                    for (var i in params.order) {
                        if (params.order.hasOwnProperty(i)) {
                            order_params.push(i + ' ' + params.order[i]);
                        }
                    }

                    QUERY_STRING += " ORDER BY " + order_params.join(', ');
                }
            }


            if (!!params.limit && params.limit.length > 0) {
                QUERY_STRING += " LIMIT " + params.limit[1] + ' OFFSET ' + params.limit[0];
            }

            QUERY_STRING += " RETURNING * ";

            return {string: QUERY_STRING, params: QUERY_PARAMS};
        },
        rem: function (params) {

            var QUERY_STRING,
                QUERY_PARAMS = [],
                REM_STRING = '',
                REM_PIECES = [],
                x = 0;

            for (var i in params.data) {

                if (params.data.hasOwnProperty(i)) {

                    QUERY_PARAMS.push(params.data[i]);

                    x++;

                    REM_STRING = i;
                    REM_STRING += '=';
                    REM_STRING += "$" + x + "";
                    REM_PIECES.push(REM_STRING);

                    if (/^\d+$/.test(params.data[i])) {
                        params.data[i] = parseInt(params.data[i]);
                    }

                }

            }

            QUERY_STRING = "DELETE FROM " + params.what + " WHERE " + REM_PIECES.join(' AND ');

            return {string: QUERY_STRING, params: QUERY_PARAMS};
        },
        new: function (params) {
            var QUERY_STRING = 'TRUNCATE TABLE ' + params.what + '; ALTER SEQUENCE ' + params.what + '_id_seq RESTART WITH 1';

            return {string: QUERY_STRING, params: []};
        },
        query: function (params) {
            return {string: params.data, params: []};
        }
    }
};

module.exports = crud;