plugin.js

/**
 * @class SqlSupport
 * @memberof module:plugins
 * @description Allows to export rules as a SQL WHERE statement as well as populating the builder from an SQL query.
 * @param {object} [options]
 * @param {boolean} [options.boolean_as_integer=true] - `true` to convert boolean values to integer in the SQL output
 */
QueryBuilder.define('sql-support', function(options) {

}, {
    boolean_as_integer: true
});

QueryBuilder.defaults({
    // operators for internal -> SQL conversion
    sqlOperators: {
        equal: { op: '= ?' },
        not_equal: { op: '!= ?' },
        in: { op: 'IN(?)', sep: ', ' },
        not_in: { op: 'NOT IN(?)', sep: ', ' },
        less: { op: '< ?' },
        less_or_equal: { op: '<= ?' },
        greater: { op: '> ?' },
        greater_or_equal: { op: '>= ?' },
        between: { op: 'BETWEEN ?', sep: ' AND ' },
        not_between: { op: 'NOT BETWEEN ?', sep: ' AND ' },
        begins_with: { op: 'LIKE ?', mod: '{0}%', escape: '%_' },
        not_begins_with: { op: 'NOT LIKE ?', mod: '{0}%', escape: '%_' },
        contains: { op: 'LIKE ?', mod: '%{0}%', escape: '%_' },
        not_contains: { op: 'NOT LIKE ?', mod: '%{0}%', escape: '%_' },
        ends_with: { op: 'LIKE ?', mod: '%{0}', escape: '%_' },
        not_ends_with: { op: 'NOT LIKE ?', mod: '%{0}', escape: '%_' },
        is_empty: { op: '= \'\'' },
        is_not_empty: { op: '!= \'\'' },
        is_null: { op: 'IS NULL' },
        is_not_null: { op: 'IS NOT NULL' }
    },

    // operators for SQL -> internal conversion
    sqlRuleOperator: {
        '=': function(v) {
            return {
                val: v,
                op: v === '' ? 'is_empty' : 'equal'
            };
        },
        '!=': function(v) {
            return {
                val: v,
                op: v === '' ? 'is_not_empty' : 'not_equal'
            };
        },
        'LIKE': function(v) {
            if (v.slice(0, 1) == '%' && v.slice(-1) == '%') {
                return {
                    val: v.slice(1, -1),
                    op: 'contains'
                };
            }
            else if (v.slice(0, 1) == '%') {
                return {
                    val: v.slice(1),
                    op: 'ends_with'
                };
            }
            else if (v.slice(-1) == '%') {
                return {
                    val: v.slice(0, -1),
                    op: 'begins_with'
                };
            }
            else {
                Utils.error('SQLParse', 'Invalid value for LIKE operator "{0}"', v);
            }
        },
        'NOT LIKE': function(v) {
            if (v.slice(0, 1) == '%' && v.slice(-1) == '%') {
                return {
                    val: v.slice(1, -1),
                    op: 'not_contains'
                };
            }
            else if (v.slice(0, 1) == '%') {
                return {
                    val: v.slice(1),
                    op: 'not_ends_with'
                };
            }
            else if (v.slice(-1) == '%') {
                return {
                    val: v.slice(0, -1),
                    op: 'not_begins_with'
                };
            }
            else {
                Utils.error('SQLParse', 'Invalid value for NOT LIKE operator "{0}"', v);
            }
        },
        'IN': function(v) {
            return { val: v, op: 'in' };
        },
        'NOT IN': function(v) {
            return { val: v, op: 'not_in' };
        },
        '<': function(v) {
            return { val: v, op: 'less' };
        },
        '<=': function(v) {
            return { val: v, op: 'less_or_equal' };
        },
        '>': function(v) {
            return { val: v, op: 'greater' };
        },
        '>=': function(v) {
            return { val: v, op: 'greater_or_equal' };
        },
        'BETWEEN': function(v) {
            return { val: v, op: 'between' };
        },
        'NOT BETWEEN': function(v) {
            return { val: v, op: 'not_between' };
        },
        'IS': function(v) {
            if (v !== null) {
                Utils.error('SQLParse', 'Invalid value for IS operator');
            }
            return { val: null, op: 'is_null' };
        },
        'IS NOT': function(v) {
            if (v !== null) {
                Utils.error('SQLParse', 'Invalid value for IS operator');
            }
            return { val: null, op: 'is_not_null' };
        }
    },

    // statements for internal -> SQL conversion
    sqlStatements: {
        'question_mark': function() {
            var params = [];
            return {
                add: function(rule, value) {
                    params.push(value);
                    return '?';
                },
                run: function() {
                    return params;
                }
            };
        },

        'numbered': function(char) {
            if (!char || char.length > 1) char = '$';
            var index = 0;
            var params = [];
            return {
                add: function(rule, value) {
                    params.push(value);
                    index++;
                    return char + index;
                },
                run: function() {
                    return params;
                }
            };
        },

        'named': function(char) {
            if (!char || char.length > 1) char = ':';
            var indexes = {};
            var params = {};
            return {
                add: function(rule, value) {
                    if (!indexes[rule.field]) indexes[rule.field] = 1;
                    var key = rule.field + '_' + (indexes[rule.field]++);
                    params[key] = value;
                    return char + key;
                },
                run: function() {
                    return params;
                }
            };
        }
    },

    // statements for SQL -> internal conversion
    sqlRuleStatement: {
        'question_mark': function(values) {
            var index = 0;
            return {
                parse: function(v) {
                    return v == '?' ? values[index++] : v;
                },
                esc: function(sql) {
                    return sql.replace(/\?/g, '\'?\'');
                }
            };
        },

        'numbered': function(values, char) {
            if (!char || char.length > 1) char = '$';
            var regex1 = new RegExp('^\\' + char + '[0-9]+$');
            var regex2 = new RegExp('\\' + char + '([0-9]+)', 'g');
            return {
                parse: function(v) {
                    return regex1.test(v) ? values[v.slice(1) - 1] : v;
                },
                esc: function(sql) {
                    return sql.replace(regex2, '\'' + (char == '$' ? '$$' : char) + '$1\'');
                }
            };
        },

        'named': function(values, char) {
            if (!char || char.length > 1) char = ':';
            var regex1 = new RegExp('^\\' + char);
            var regex2 = new RegExp('\\' + char + '(' + Object.keys(values).join('|') + ')\\b', 'g');
            return {
                parse: function(v) {
                    return regex1.test(v) ? values[v.slice(1)] : v;
                },
                esc: function(sql) {
                    return sql.replace(regex2, '\'' + (char == '$' ? '$$' : char) + '$1\'');
                }
            };
        }
    }
});

/**
 * @typedef {object} SqlQuery
 * @memberof module:plugins.SqlSupport
 * @property {string} sql
 * @property {object} params
 */

QueryBuilder.extend(/** @lends module:plugins.SqlSupport.prototype */ {
    /**
     * Returns rules as a SQL query
     * @param {boolean|string} [stmt] - use prepared statements: false, 'question_mark', 'numbered', 'numbered(@)', 'named', 'named(@)'
     * @param {boolean} [nl=false] output with new lines
     * @param {object} [data] - current rules by default
     * @returns {module:plugins.SqlSupport.SqlQuery}
     * @fires module:plugins.SqlSupport.changer:getSQLField
     * @fires module:plugins.SqlSupport.changer:ruleToSQL
     * @fires module:plugins.SqlSupport.changer:groupToSQL
     * @throws UndefinedSQLConditionError, UndefinedSQLOperatorError
     */
    getSQL: function(stmt, nl, data) {
        data = (data === undefined) ? this.getRules() : data;

        if (!data) {
            return null;
        }

        nl = !!nl ? '\n' : ' ';
        var boolean_as_integer = this.getPluginOptions('sql-support', 'boolean_as_integer');

        if (stmt === true) {
            stmt = 'question_mark';
        }
        if (typeof stmt == 'string') {
            var config = getStmtConfig(stmt);
            stmt = this.settings.sqlStatements[config[1]](config[2]);
        }

        var self = this;

        var sql = (function parse(group) {
            if (!group.condition) {
                group.condition = self.settings.default_condition;
            }
            if (['AND', 'OR'].indexOf(group.condition.toUpperCase()) === -1) {
                Utils.error('UndefinedSQLCondition', 'Unable to build SQL query with condition "{0}"', group.condition);
            }

            if (!group.rules) {
                return '';
            }

            var parts = [];

            group.rules.forEach(function(rule) {
                if (rule.rules && rule.rules.length > 0) {
                    parts.push('(' + nl + parse(rule) + nl + ')' + nl);
                }
                else {
                    var sql = self.settings.sqlOperators[rule.operator];
                    var ope = self.getOperatorByType(rule.operator);
                    var value = '';

                    if (sql === undefined) {
                        Utils.error('UndefinedSQLOperator', 'Unknown SQL operation for operator "{0}"', rule.operator);
                    }

                    if (ope.nb_inputs !== 0) {
                        if (!(rule.value instanceof Array)) {
                            rule.value = [rule.value];
                        }

                        rule.value.forEach(function(v, i) {
                            if (i > 0) {
                                value += sql.sep;
                            }

                            if (rule.type == 'boolean' && boolean_as_integer) {
                                v = v ? 1 : 0;
                            }
                            else if (!stmt && rule.type !== 'integer' && rule.type !== 'double' && rule.type !== 'boolean') {
                                v = Utils.escapeString(v, sql.escape);
                            }

                            if (sql.mod) {
                                v = Utils.fmt(sql.mod, v);
                            }

                            if (stmt) {
                                value += stmt.add(rule, v);
                            }
                            else {
                                if (typeof v == 'string') {
                                    v = '\'' + v + '\'';
                                }

                                value += v;
                            }
                        });
                    }

                    var sqlFn = function(v) {
                        return sql.op.replace('?', function() {
                            return v;
                        });
                    };

                    /**
                     * Modifies the SQL field used by a rule
                     * @event changer:getSQLField
                     * @memberof module:plugins.SqlSupport
                     * @param {string} field
                     * @param {Rule} rule
                     * @returns {string}
                     */
                    var field = self.change('getSQLField', rule.field, rule);

                    var ruleExpression = field + ' ' + sqlFn(value);

                    /**
                     * Modifies the SQL generated for a rule
                     * @event changer:ruleToSQL
                     * @memberof module:plugins.SqlSupport
                     * @param {string} expression
                     * @param {Rule} rule
                     * @param {*} value
                     * @param {function} valueWrapper - function that takes the value and adds the operator
                     * @returns {string}
                     */
                    parts.push(self.change('ruleToSQL', ruleExpression, rule, value, sqlFn));
                }
            });

            var groupExpression = parts.join(' ' + group.condition + nl);

            /**
             * Modifies the SQL generated for a group
             * @event changer:groupToSQL
             * @memberof module:plugins.SqlSupport
             * @param {string} expression
             * @param {Group} group
             * @returns {string}
             */
            return self.change('groupToSQL', groupExpression, group);
        }(data));

        if (stmt) {
            return {
                sql: sql,
                params: stmt.run()
            };
        }
        else {
            return {
                sql: sql
            };
        }
    },

    /**
     * Convert a SQL query to rules
     * @param {string|module:plugins.SqlSupport.SqlQuery} query
     * @param {boolean|string} stmt
     * @returns {object}
     * @fires module:plugins.SqlSupport.changer:parseSQLNode
     * @fires module:plugins.SqlSupport.changer:getSQLFieldID
     * @fires module:plugins.SqlSupport.changer:sqlToRule
     * @fires module:plugins.SqlSupport.changer:sqlToGroup
     * @throws MissingLibraryError, SQLParseError, UndefinedSQLOperatorError
     */
    getRulesFromSQL: function(query, stmt) {
        if (!('SQLParser' in window)) {
            Utils.error('MissingLibrary', 'SQLParser is required to parse SQL queries. Get it here https://github.com/mistic100/sql-parser');
        }

        var self = this;

        if (typeof query == 'string') {
            query = { sql: query };
        }

        if (stmt === true) stmt = 'question_mark';
        if (typeof stmt == 'string') {
            var config = getStmtConfig(stmt);
            stmt = this.settings.sqlRuleStatement[config[1]](query.params, config[2]);
        }

        if (stmt) {
            query.sql = stmt.esc(query.sql);
        }

        if (query.sql.toUpperCase().indexOf('SELECT') !== 0) {
            query.sql = 'SELECT * FROM table WHERE ' + query.sql;
        }

        var parsed = SQLParser.parse(query.sql);

        if (!parsed.where) {
            Utils.error('SQLParse', 'No WHERE clause found');
        }

        /**
         * Custom parsing of an AST node generated by SQLParser, you can return a sub-part of the tree, or a well formed group or rule JSON
         * @event changer:parseSQLNode
         * @memberof module:plugins.SqlSupport
         * @param {object} AST node
         * @returns {object} tree, rule or group
         */
        var data = self.change('parseSQLNode', parsed.where.conditions);

        // a plugin returned a group
        if ('rules' in data && 'condition' in data) {
            return data;
        }

        // a plugin returned a rule
        if ('id' in data && 'operator' in data && 'value' in data) {
            return {
                condition: this.settings.default_condition,
                rules: [data]
            };
        }

        // create root group
        var out = self.change('sqlToGroup', {
            condition: this.settings.default_condition,
            rules: []
        }, data);

        // keep track of current group
        var curr = out;

        (function flatten(data, i) {
            if (data === null) {
                return;
            }

            // allow plugins to manually parse or handle special cases
            data = self.change('parseSQLNode', data);

            // a plugin returned a group
            if ('rules' in data && 'condition' in data) {
                curr.rules.push(data);
                return;
            }

            // a plugin returned a rule
            if ('id' in data && 'operator' in data && 'value' in data) {
                curr.rules.push(data);
                return;
            }

            // data must be a SQL parser node
            if (!('left' in data) || !('right' in data) || !('operation' in data)) {
                Utils.error('SQLParse', 'Unable to parse WHERE clause');
            }

            // it's a node
            if (['AND', 'OR'].indexOf(data.operation.toUpperCase()) !== -1) {
                // create a sub-group if the condition is not the same and it's not the first level

                /**
                 * Given an existing group and an AST node, determines if a sub-group must be created
                 * @event changer:sqlGroupsDistinct
                 * @memberof module:plugins.SqlSupport
                 * @param {boolean} create - true by default if the group condition is different
                 * @param {object} group
                 * @param {object} AST
                 * @param {int} current group level
                 * @returns {boolean}
                 */
                var createGroup = self.change('sqlGroupsDistinct', i > 0 && curr.condition != data.operation.toUpperCase(), curr, data, i);

                if (createGroup) {
                    /**
                     * Modifies the group generated from the SQL expression (this is called before the group is filled with rules)
                     * @event changer:sqlToGroup
                     * @memberof module:plugins.SqlSupport
                     * @param {object} group
                     * @param {object} AST
                     * @returns {object}
                     */
                    var group = self.change('sqlToGroup', {
                        condition: self.settings.default_condition,
                        rules: []
                    }, data);

                    curr.rules.push(group);
                    curr = group;
                }

                curr.condition = data.operation.toUpperCase();
                i++;

                // some magic !
                var next = curr;
                flatten(data.left, i);

                curr = next;
                flatten(data.right, i);
            }
            // it's a leaf
            else {
                if ($.isPlainObject(data.right.value)) {
                    Utils.error('SQLParse', 'Value format not supported for {0}.', data.left.value);
                }

                // convert array
                var value;
                if ($.isArray(data.right.value)) {
                    value = data.right.value.map(function(v) {
                        return v.value;
                    });
                }
                else {
                    value = data.right.value;
                }

                // get actual values
                if (stmt) {
                    if ($.isArray(value)) {
                        value = value.map(stmt.parse);
                    }
                    else {
                        value = stmt.parse(value);
                    }
                }

                // convert operator
                var operator = data.operation.toUpperCase();
                if (operator == '<>') {
                    operator = '!=';
                }

                var sqlrl = self.settings.sqlRuleOperator[operator];
                if (sqlrl === undefined) {
                    Utils.error('UndefinedSQLOperator', 'Invalid SQL operation "{0}".', data.operation);
                }

                var opVal = sqlrl.call(this, value, data.operation);

                // find field name
                var field;
                if ('values' in data.left) {
                    field = data.left.values.join('.');
                }
                else if ('value' in data.left) {
                    field = data.left.value;
                }
                else {
                    Utils.error('SQLParse', 'Cannot find field name in {0}', JSON.stringify(data.left));
                }

                // unescape chars declared by the operator
                var finalValue = opVal.val;
                var sql = self.settings.sqlOperators[opVal.op];
                if (!stmt && sql && sql.escape) {
                    var searchChars = sql.escape.split('').map(function(c) {
                        return '\\\\' + c;
                    }).join('|');
                    finalValue = finalValue
                        .replace(new RegExp('(' + searchChars + ')', 'g'), function(s) {
                            return s[1];
                        });
                }

                var id = self.getSQLFieldID(field, value);

                /**
                 * Modifies the rule generated from the SQL expression
                 * @event changer:sqlToRule
                 * @memberof module:plugins.SqlSupport
                 * @param {object} rule
                 * @param {object} AST
                 * @returns {object}
                 */
                var rule = self.change('sqlToRule', {
                    id: id,
                    field: field,
                    operator: opVal.op,
                    value: finalValue
                }, data);

                curr.rules.push(rule);
            }
        }(data, 0));

        return out;
    },

    /**
     * Sets the builder's rules from a SQL query
     * @see module:plugins.SqlSupport.getRulesFromSQL
     */
    setRulesFromSQL: function(query, stmt) {
        this.setRules(this.getRulesFromSQL(query, stmt));
    },

    /**
     * Returns a filter identifier from the SQL field.
     * Automatically use the only one filter with a matching field, fires a changer otherwise.
     * @param {string} field
     * @param {*} value
     * @fires module:plugins.SqlSupport:changer:getSQLFieldID
     * @returns {string}
     * @private
     */
    getSQLFieldID: function(field, value) {
        var matchingFilters = this.filters.filter(function(filter) {
            return filter.field.toLowerCase() === field.toLowerCase();
        });

        var id;
        if (matchingFilters.length === 1) {
            id = matchingFilters[0].id;
        }
        else {
            /**
             * Returns a filter identifier from the SQL field
             * @event changer:getSQLFieldID
             * @memberof module:plugins.SqlSupport
             * @param {string} field
             * @param {*} value
             * @returns {string}
             */
            id = this.change('getSQLFieldID', field, value);
        }

        return id;
    }
});

/**
 * Parses the statement configuration
 * @memberof module:plugins.SqlSupport
 * @param {string} stmt
 * @returns {Array} null, mode, option
 * @private
 */
function getStmtConfig(stmt) {
    var config = stmt.match(/(question_mark|numbered|named)(?:\((.)\))?/);
    if (!config) config = [null, 'question_mark', undefined];
    return config;
}