Query Build

QueryBuild.js

/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 */


/**
 * @author Afranio Martins<afranioce@gmail.com.br>
 * Usando como exemplo DBAL QueryBuilder Doctrine 2.0
 * 
 */
var QueryBuilder = function(connection){
    /* The query types. */
    this.SELECT = 0;
    this.DELETE = 1;
    this.UPDATE = 2;
    this.INSERT = 3;

    /** The builder states. */
    this.STATE_DIRTY = 0;
    this.STATE_CLEAN = 1;

    this.EQ       = '=';
    this.NEQ      = '<>';
    this.LT       = '<';
    this.LTE      = '<=';
    this.GT       = '>';
    this.GTE      = '>=';
    this.IN       = 'IN';
    this.NOT_IN   = 'NOT IN';
    this.LIKE     = 'LIKE';
    this.NOT_LIKE = 'NOT LIKE';
    
    /**
     * Constant that represents an AND composite expression
     */
    this.TYPE_AND = 'AND';
    
    /**
     * Constant that represents an OR composite expression
     */
    this.TYPE_OR  = 'OR';
    
    var _sql;
    var _type = this.SELECT;
    var _state = this.STATE_CLEAN;
    var _firstResult = null;
    var _maxResults = null;
    var _result = null;
    var _db;
    var _sqlParts = {
        'select'  : [],
        'from'    : [],
        'join'    : [],
        'set'     : [],
        'where'   : null,
        'groupBy' : [],
        'having'  : null,
        'orderBy' : [],
        'field'   : {
            'fields' : [],
            'values' : []
        }
    };
    var _operators = [
        this.EQ,
        this.NEQ,
        this.LT,
        this.LTE,
        this.GT,
        this.GTE,
        this.IN,
        this.NOT_IN,
        this.LIKE,
        this.NOT_LIKE
    ];


    this.getConnection = function(){
        return connection;
    }

    this.getState = function(){
        return _state;
    }

    this.execute = function(){
        var database = this.getConnection() +'.sqlite';
        var file = Titanium.Filesystem.getFile(
            Titanium.Filesystem.applicationSupportDirectory
            + '/db/' + database
        );

        //Install Database
        if(!file.exists()) {
            Ti.Database.install(database, 'db');
        }

        //Open DB
        _db = Ti.Database.open('db');

        _result = _db.execute(this.getSQL());
        return this;
    }

    this.fetchAll = function(){
        if(_.isNull(_result))
            return this;

        var rows = [];
        var index = 0;

        while (_result.isValidRow()){
            var row = {};
            row[_result.getFieldName(index)] = _result.field(index);

            rows.push(row);
            index++;
            _result.next();
        }

        //Close result set
        _result.close();
        //Close database
        _db.close();

        return rows;
    }

    this.getSQL = function(){
        if (_sql !== null && _state === this.STATE_CLEAN) {
            return _sql;
        }

        _sql = '';

        switch (_type) {
            case this.DELETE:
                _sql = getSQLForDelete();
                break;

            case this.UPDATE:
                _sql = getSQLForUpdate();
                break;

            case this.INSERT:
                _sql = getSQLForInsert();
                break;

            case this.SELECT:
            default:
                _sql = getSQLForSelect();
                break;
        }

        _state = this.STATE_CLEAN;
        _sql = _sql;

        return _sql;
    }

    this.setFirstResult = function(firstResult){
        _state = this.STATE_DIRTY;
        _firstResult = firstResult;
        return this;
    }

    this.getFirstResult = function(){
        return _firstResult;
    }

    this.setMaxResults = function(maxResults){
        _state = this.STATE_DIRTY;
        _maxResults = maxResults;
        return this;
    }

    this.getMaxResults = function (){
        return _maxResults;
    }

    this.add = function(sqlPartName, sqlPart, append){
        if(_.isUndefined(append)) append = false;

        var isObject = _.isObject(sqlPart);
        var isMultiple = _.isObject(_sqlParts[sqlPartName]);

        if (isMultiple && !isObject) {
            sqlPart = [sqlPart];
        }

        _state = this.STATE_DIRTY;
        if (append) {
            if(/field/i.exec(sqlPartName)){
                _.each(_.keys(sqlPart), function(key){
                    _sqlParts[sqlPartName][key] = _.union(_sqlParts[sqlPartName][key], sqlPart[key]);
                });
            } else if(/orderBy|groupBy|select|set|where|having/i.exec(sqlPartName)){
                if(/where|having/i.exec(sqlPartName))
                    _sqlParts[sqlPartName] = _.union(_.toArray(_sqlParts[sqlPartName]), [sqlPart]);
                else
                    _sqlParts[sqlPartName] = _.union(_.toArray(_sqlParts[sqlPartName]), _.toArray(sqlPart));
            } else if (isObject && _.isObject(sqlPart[_.keys(sqlPart)])) {
                var key = _.keys(sqlPart);
                _sqlParts[sqlPartName][key] = _.union(_.toArray(_sqlParts[sqlPartName][key]), [sqlPart[key]]);
            } else if (isMultiple) {
                _.extend(_sqlParts[sqlPartName], [sqlPart]);
            } else {
                _.extend(_sqlParts[sqlPartName], [sqlPart]);
            }

            return this;
        }

        _sqlParts[sqlPartName] = sqlPart;

        return this;
    }

    this.select = function(select){
        _type = this.SELECT;

        if (_.isUndefined(select)) {
            select = ['*'];
        }

        var selects = _.isArray(select) ? select : arguments;

        return this.add('select', selects, false);
    }

    this.addSelect = function(select){
        _type = this.SELECT;

        if (select.length == 0) {
            return this;
        }

        var selects = _.isArray(select) ? select : arguments;

        return this.add('select', selects, true);
    }

    this.delete = function(del, alias){
        _type = this.DELETE;

        if (!del) {
            return this;
        }

        return this.add('from', {
            'table': del,
            'alias': alias
        });
    }

    this.update = function(update, alias){
        _type = this.UPDATE;

        if (!update) {
            return this;
        }

        return this.add('from', {
            'table': update,
            'alias': alias
        });
    }

    this.insert = function(insert){
        _type = this.INSERT;

        if (!insert) {
            return this;
        }

        return this.add('from', {
            'table': insert
        });
    }

    this.from = function(from, alias){
        return this.add('from', {
            'table': from,
            'alias': alias
        }, true);
    }

    this.join = function(fromAlias, join, alias, condition){
        if(_.isUndefined(condition)) condition = null;

        return this.innerJoin(fromAlias, join, alias, condition);
    }

    this.innerJoin = function(fromAlias, join, alias, condition){
        if(_.isUndefined(condition)) condition = null;

        var obj = {};
        obj[fromAlias] = {
            'joinType': 'inner',
            'joinTable': join,
            'joinAlias': alias,
            'joinCondition': condition
        };

        return this.add('join', obj, true);
    }

    this.leftJoin = function(fromAlias, join, alias, condition){
        if(_.isUndefined(condition)) condition = null;

        var obj = {};
        obj[fromAlias] = {
            'joinType': 'left',
            'joinTable': join,
            'joinAlias': alias,
            'joinCondition': condition
        };

        return this.add('join', obj, true);
    }

    this.rightJoin = function(fromAlias, join, alias, condition){
        if(_.isUndefined(condition)) condition = null;
        var obj = {};
        obj[fromAlias] = {
            'joinType': 'right',
            'joinTable': join,
            'joinAlias': alias,
            'joinCondition': condition
        };

        return this.add('join', obj, true);
    }

    this.set = function(key, value){
        return this.add('set', key + ' = ' + value, true);
    }

    this.where = function(field, value, operator){
        return this.condition('where', null, field, value, operator);
    }

    this.andWhere = function(field, value, operator){
        return this.condition('where', this.TYPE_AND, field, value, operator);
    }

    this.orWhere = function(field, value, operator){
        return this.condition('where', this.TYPE_OR, field, value, operator);
    }

    this.isNull = function(field){
        return this.condition('where', this.TYPE_OR, field, null, 'IS NULL');
    }

    this.isNotNull = function(field){
        return this.condition('where', this.TYPE_OR, field, null, 'IS NOT NULL');
    }

    this.condition = function(condition, type, field, value, operator){
        value = !_.isNull(value) ? value : '';
        if (_.isArray(value)) {
            operator = !_.isUndefined(operator) ? operator : this.IN;
            operator = operator.toUpperCase().trim();
            var con = [this.IN, this.NOT_IN];

            operator = con[_.indexOf(con, operator)];
            value = "('" + implode("', '", value) + "')";
        }
        operator = !_.isUndefined(operator) && _.indexOf(_operators, operator) ? operator : this.EQ;

        var parts = field + ' ' + operator + ' ' + value;

        return this.add(condition, {
            'type': !_.isNull(type) ? type : '',
            'parts': parts.trim()
        }, true);
    }

    this.groupBy = function(group){
        if (group.length == 0) {
            return this;
        }

        var groups = _.isArray(group) ? group : arguments;

        return this.add('groupBy', groups, false);
    }

    this.addGroupBy = function(group){
        if (group.length == 0) {
            return this;
        }

        var groups = _.isArray(group) ? group : arguments;

        return this.add('groupBy', groups, true);
    }

    this.having = function(field, value, operator){
        return this.condition('having', '', field, value, operator);
    }

    this.andHaving = function(field, value, operator){
        return this.condition('having', this.TYPE_AND, field, value, operator);
    }

    this.orHaving = function(field, value, operator){
        return this.condition('having', this.TYPE_OR, field, value, operator);
    }

    this.orderBy = function(sort, order){
        return this.add('orderBy', sort + ' ' + (!order ? 'ASC' : order), false);
    }

    this.addOrderBy = function(sort, order){
        return this.add('orderBy', sort + ' ' + (!order ? 'ASC' : order), true);
    }
    
    this.field = function(field, value){
        var fields = {};
        fields[field] = value;
        return this.fields(fields);
    }
    
    this.fields = function(fields){
        if(!_.isObject(fields)){
            return this;
        }

        return this.add('field', {
            'fields': _.keys(fields),
            'values': _.values(fields)
        }, true);
    }

    this.getQueryPart = function(queryPartName){
        return _sqlParts[queryPartName];
    }

    this.getQueryParts = function(){
        return _sqlParts;
    }

    this.resetQueryParts = function(queryPartNames){
        if (_.isUndefined(queryPartNames)) {
            queryPartNames = key(_sqlParts);
        }

        for (var queryPartName in queryPartNames) {
            this.resetQueryPart(queryPartNames[queryPartName]);
        }

        return this;
    }

    this.resetQueryPart = function(queryPartName){
        _sqlParts[queryPartName] = _.isObject(_sqlParts[queryPartName])
        ? {} : null;

        _state = this.STATE_DIRTY;

        return this;
    }

    var getSQLForSelect = function(){
        var query = 'SELECT ' + implode(', ', _sqlParts['select']) + ' FROM ';

        var fromClauses = {};

        // Loop through all FROM clauses
        _.each(_sqlParts['from'], function(from){
            var fromClause = from['table'] + ' ' + from['alias'];

            if (!_.isUndefined(_sqlParts['join'][from['alias']])) {
                _.each(_sqlParts['join'][from['alias']], function(join){
                    fromClause += ' ' + join['joinType'].toUpperCase()
                    + ' JOIN ' + join['joinTable'] + ' ' + join['joinAlias']
                    + ' ON ' + (join['joinCondition']);
                })
            }
            fromClauses[from['alias']] = fromClause;

        })

        // loop through all JOIN clauses for validation purpose
        _.each(_sqlParts['join'], function(fromAlias, joins){
            if (_.isEmpty(fromClauses[fromAlias])) {
                Ti.API.Error('Error: ' + fromAlias + ' >>> ' + key(fromClauses));
            }
        });

        query += implode(', ', _(fromClauses).values())
        + (!_.isNull(_sqlParts['where']) ? ' WHERE' + doCondition('where') : '')
        + (!_.isEmpty(_sqlParts['groupBy']) ? ' GROUP BY '+ implode(', ', _sqlParts['groupBy']) : '')
        + (!_.isNull(_sqlParts['having']) ? ' HAVING' + doCondition('having') : '')
        + (!_.isEmpty(_sqlParts['orderBy']) ? 'ORDER BY ' + implode(', ', _sqlParts['orderBy']) : '')
        return (_.isNull(_maxResults) && _.isNull(_firstResult))
        ? query
        : doOffset(query, _maxResults, _firstResult);
    }

    var doCondition = function(condition){
        var conditions = '';
        _.each(_sqlParts[condition], function(cond){
            conditions += cond['type'] + ' (' + cond['parts'] + ') ';
        });
        return conditions;
    }

    var doOffset = function(query, limit, offset){
        if (!_.isNull(limit)) {
            query += ' LIMIT ' + parseInt(limit, 10);
        }

        if (offset !== null) {
            var offset = parseInt(offset, 10);
            if (offset < 0) {
                Ti.API.error("Error: LIMIT argument offset=offset is not valid");
            }
            query += ' OFFSET ' + offset;
        }

        return query;
    }

    /**
     * Converts this instance into an UPDATE string in SQL.
     *
     * @return string
     */
    var getSQLForUpdate = function(){
        var table = _sqlParts['from']['table']
        + (_sqlParts['from']['alias'] ? ' ' + _sqlParts['from']['alias'] : '');
        var query = 'UPDATE ' + table
        + ' SET ' + implode(', ', _sqlParts['set'])
        + (!_.isNull(_sqlParts['where']) ? ' WHERE' + doCondition('where') : '');

        return query;
    }

    this.__toString = function(){
        return this.getSQL();
    }

    /**
     * Converts this instance into a DELETE string in SQL.
     *
     * @return string
     */
    var getSQLForDelete = function (){
        var table = _sqlParts['from']['table'] + (_sqlParts['from']['alias'] ? ' ' 
            + _sqlParts['from']['alias'] : '');
        var query = 'DELETE FROM ' + table
        + (!_.isNull(_sqlParts['where']) ? ' WHERE' + doCondition('where') : '');

        return query;
    }

    /**
     * Converts this instance into a INSERT string in SQL.
     *
     * @return string
     */
    var getSQLForInsert = function (){
        var query = 'INSERT INTO ' + _sqlParts['from']['table'];
        if(!_.isUndefined(_sqlParts['field']['fields']))
            query += '('+ implode(', ', _sqlParts['field']['fields']) + ") VALUES ('";

        query += implode("\', \'", _sqlParts['field']['values']) + "\')";

        return query;
    }

    var implode = function(separator, array){
        var array = _.toArray(array);
        if(!_.isEmpty(array))
            return array.length === 1 ? array[0] : array.join(separator)
    }
}

var db = new QueryBuilder('db_teste')

.select('type')
.addSelect('body', 'created', 't.tid')
.from('node', 'n')
.setFirstResult(2)
.setMaxResults(10)
.orderBy('nid', 'DESC')
.addOrderBy('tid', 'ASC')
.groupBy('tid')
.addGroupBy('nid')
.innerJoin('n', 'taxonomy_term', 't', 'n.nid = t.tid')
.leftJoin('n', 'taxonomy_term_data', 'td', 'n.nid = td.tid')
.where('n.nid', '2')
.andWhere('n.nid', '4')
.orWhere('n.nid', '5')
.andWhere('n.nid', [1, 2, 3])
.orWhere('n.nid', [1, 2, 3], 'NOT IN')
.having('t.tid','1', '=')
.andHaving('t.tid2','1', '=')
.isNull('n.nid')
.isNotNull('n.nid');
/*
.delete('sdfs')
.where('n.nid', '5')
.andWhere('n.nid', [1, 2, 3])
.orWhere('n.nid', [1, 2, 3], 'NOT IN')

.update('node', 'n')
.set('nid', 2)
.where('n.nid', '5')
.andWhere('n.nid', [1, 2, 3])
.orWhere('n.nid', [1, 2, 3], 'NOT IN')

.insert('node')
.field('nid', 1)
.field('title', 1)
.fields({
    'title': 'teste de titulo',
    'body' : '<p>sdfsdf</p>'
})
/*
db.trucate(table);
db.drop('table').exists();
db.create('node').notExists().fields({
    'nid': {
        'type': 'serial',
        'null': false,
        'description': 'sdfsdf',
    },
    'title': {
        'type': 'string',
        'length': 32,
        'null': false,
        'description': 'sdfsdf',
    }
})
.primaryKey('nid')
.uniqueKey()
.indexes()
.foreingKey()
 */
window.onload = document.write(db.getSQL());
//module.exports = QueryBuilder;