A Raw Nodejs SQL Builder(Raw Version)

sql_converter.js

var expect = require('chai').expect;
var SQLBuilder = require('../services/libs/sql_builder');
var util = require('util');

describe('SQL Builder Tests',function(){
    var builder = new SQLBuilder();
    beforeEach(function(){
        builder.reset();
    });
    context('correct usages',function(){
        it('simple selct test',function(){
            var sql = builder.select().from('t_bill_order').toSQL();
            expect(sql.toLowerCase()).to.equal('SELECT * FROM t_bill_order'.toLowerCase());
        });
        it('simple where clause with number',function(){
            var sql = builder.select().from('t_bill_order').where('account_id = %s').setParameter(1).toSQL();
            expect(sql.toLowerCase()).to.equal('SELECT * FROM t_bill_order WHERE account_id = 1'.toLowerCase());
        });
        it('where clause with string',function(){
            var sql = builder.select().from('t_bill_order').where('account_id = %s').setParameter('1').toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT * FROM t_bill_order WHERE account_id = '1'".toLowerCase());
        });
        it('limit number test',function() {
            var sql = builder.select().from('t_bill_order').where('account_id = %d').setParameter(1).limit(5).toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT * FROM t_bill_order WHERE account_id = 1 LIMIT 5".toLowerCase());
        });
        it('group by test',function() {
            var timestamp = +new Date();
            var sql = builder.select().from('t_bill_order').where('date = %d').setParameter(timestamp).groupBy('account_id').limit(5).toSQL();
            expect(sql.toLowerCase()).to.equal(util.format("SELECT * FROM t_bill_order WHERE date = %d GROUP BY account_id LIMIT 5",timestamp).toLowerCase());
        });
        it('alias table_name test',function(){
            var sql = builder.select('u.account_id','u.date').from('t_bill_order','u').where('date = %s').setParameter(132222414).toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT u.account_id,u.date FROM t_bill_order AS u WHERE date = 132222414".toLowerCase());
        });
        it('order by test', function () {
            var sql = builder.select().from('t_bill_order').orderBy('account_id','ASC').toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT * FROM t_bill_order ORDER BY account_id ASC".toLowerCase());
        });
        it('multi order by test', function () {
            var sql = builder.select().from('t_bill_order').orderBy('account_id','ASC').addOrderBy('date').toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT * FROM t_bill_order ORDER BY account_id ASC,date".toLowerCase());
        });
        it('multi group by test', function () {
            var timestamp = +new Date();
            var sql = builder.select().from('t_bill_order').where('date = %d').setParameter(timestamp).groupBy('account_id').addGroupBy('date_type').limit(5).toSQL();
            expect(sql.toLowerCase()).to.equal(util.format("SELECT * FROM t_bill_order WHERE date = %d GROUP BY account_id,date_type LIMIT 5",timestamp).toLowerCase());
        });
        it('multi where clause with and test',function() {
            var sql = builder.select().from('t_bill_order').where('account_id = %s').andWhere('date_type = %s').setParameter(1,'day').toSQL();
            expect(sql.toLowerCase()).to.equal("SELECT * FROM t_bill_order WHERE account_id = 1 AND date_type = 'day'".toLowerCase());
        });
    });
    context('join tests',function() {
        it('simple left join test',function(){
            var sql = builder.select('a.*','b.*').from('atom_v1c_day','a').leftJoin('a','t_member','b','a.account_id=b.top_organization_id').where("date = %d").setParameter(1433088000).toSQL();
            expect(sql.toLowerCase()).to.equal("select a.*,b.* from atom_v1c_day AS a left join t_member b on a.account_id=b.top_organization_id where date = 1433088000".toLowerCase());
        });
        it('sub-query join test', function () {
            var sub_query = builder.select('manager','top_organization_id').from('t_member').groupBy('top_organization_id');
            expect(sub_query.toSQL().toLowerCase()).to.equal("select manager,top_organization_id from t_member group by top_organization_id".toLowerCase());
            var query = (new SQLBuilder).select('a.*','b.*').from('atom_v1c_day','a').leftJoin('a',sub_query,'b','a.account_id=b.top_organization_id').where("date = %d").setParameter(1433088000).toSQL();
            expect(query.toLowerCase()).to.equal("SELECT a.*,b.* FROM atom_v1c_day AS a LEFT JOIN ( SELECT manager,top_organization_id FROM t_member GROUP BY top_organization_id ) b ON a.account_id=b.top_organization_id where date = 1433088000".toLowerCase());
        });
    });
    context('incorrect usages',function(){
        it('should throw undefined error',function(){
            var err_msg = null;
            try {
                builder.from('t_bill_order').toSQL();
            } catch(ex) {
                err_msg = ex.message;
            }
            expect(err_msg).not.to.be.null;
            expect(err_msg).to.equal('Undefined Action!');
        });
    });
});

SQLBuilder.js

var util = require('util');
var utils = require('./utils');
var _ = require('underscore');
var crypto = require('crypto');

function SQLBuilder() {
    this.reset();
}

SQLBuilder.prototype.reset = function () {
    this.table_name = null;
    this.select_columns = [];
    this.in_join_mode = false;
    this.action = null;
    this.pattern = "";
    this.condition = null;
    this.limit_condition = null;
    this.groupby_condition = null;
    this.orderby_condition = null;
    this.alias_names = {};
    this.join_parts = {};
}

SQLBuilder.prototype.from = function(table_name,alias) {
    this.table_name = table_name;
    if(alias) {
        this._addAlias(table_name,alias);
        if(this.action === 'SELECT') {
            this.pattern += wrap('AS') + alias;
        }
    }
    return this;
}

SQLBuilder.prototype._addAlias = function(table_name,alias) {
    if(this.alias_names.hasOwnProperty(alias) && this.alias_names[alias] != table_name)
        throw new Error('Not Unique Alias!');
    this.alias_names[alias] = table_name;
}

SQLBuilder.prototype.getAllAlias = function() {
    return this.alias_names;
}

/**
 * get table name by alias name
 * @param table_name
 * @returns {boolean|*}
 */
SQLBuilder.prototype.getTableNameByAlias = function(table_name) {
    return _.invert(this.alias_names).hasOwnProperty(table_name) && this.alias_names[table_name];
}

SQLBuilder.prototype.select = function () {
    this.action = 'SELECT';
    this.pattern = "SELECT %s FROM %s";
    var args = utils.array_slice(arguments);
    if(args.length == 0) {
        this.select_columns = '*';
    } else {
        this.select_columns = args;
    }
    return this;
}

SQLBuilder.prototype.setParameter = function() {
    if(this.action === null) {
        throw new Error('Unknown condition!');
    } else if(this.action != null && arguments.length == 0) {
        throw new Error('Invalid arguments!');
    }
    var args = [this.condition];
    var params = _.map(utils.array_slice(arguments),function(val) {
        if(typeof val === 'string')
            return "\'"+ val +"\'";
        return val;
    });
    var tmp = util.format.apply(null,args.concat(params));
    this.condition = tmp;
    return this;
}

SQLBuilder.prototype.leftJoin = function (fromAlias,join,alias,condition) {
    this.in_join_mode = true;
    if(typeof join === 'string') {
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(join,alias);
        this.join_parts[fromAlias].push({joinType:'left',joinTable:join,joinAlias: alias,joinCondition: condition,tableType:'tableName'});
    } else if(join instanceof SQLBuilder) {
        var shasum = crypto.createHash('sha1');
        shasum.update(join.toSQL().replace(/\s/g,''));
        fromAlias = shasum.digest('hex');
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(shasum,alias);
        if(utils.obj_compare(this.alias_names,join.getAllAlias()))
            throw new Error('Not Unique Alias!');
        else
            _.extend(this.alias_names,join.getAllAlias());
        this.join_parts[fromAlias].push({joinType:'left',joinTable:join.toSQL(),joinAlias: alias,joinCondition: condition,tableType: 'subQuery'});
    } else {
        throw new Error('Unknown type of join table!');
    }
    return this;
}

SQLBuilder.prototype.rightJoin = function (table_a,join_table,join_table_alias,association) {
    this.in_join_mode = true;
    if(typeof join === 'string') {
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(join,alias);
        this.join_parts[fromAlias].push({joinType:'right',joinTable:join,joinAlias: alias,joinCondition: condition,tableType:'tableName'});
    } else if(join instanceof SQLBuilder) {
        var shasum = crypto.createHash('sha1');
        shasum.update(join.toSQL().replace(/\s/g,''));
        fromAlias = shasum.digest('hex');
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(shasum,alias);
        if(utils.obj_compare(this.alias_names,join.getAllAlias()))
            throw new Error('Not Unique Alias!');
        else
            _.extend(this.alias_names,join.getAllAlias());
        this.join_parts[fromAlias].push({joinType:'right',joinTable:join.toSQL(),joinAlias: alias,joinCondition: condition,tableType: 'subQuery'});
    } else {
        throw new Error('Unknown type of join table!');
    }
    return this;
}

SQLBuilder.prototype.innerJoin = function (table_a,join_table,join_table_alias,association) {
    this.in_join_mode = true;
    if(typeof join === 'string') {
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(join,alias);
        this.join_parts[fromAlias].push({joinType:'inner',joinTable:join,joinAlias: alias,joinCondition: condition,tableType:'tableName'});
    } else if(join instanceof SQLBuilder) {
        var shasum = crypto.createHash('sha1');
        shasum.update(join.toSQL().replace(/\s/g,''));
        fromAlias = shasum.digest('hex');
        if(!this.join_parts.hasOwnProperty(fromAlias))
            this.join_parts[fromAlias] = [];
        this._addAlias(shasum,alias);
        if(utils.obj_compare(this.alias_names,join.getAllAlias()))
            throw new Error('Not Unique Alias!');
        else
            _.extend(this.alias_names,join.getAllAlias());
        this.join_parts[fromAlias].push({joinType:'inner',joinTable:join.toSQL(),joinAlias: alias,joinCondition: condition,tableType: 'subQuery'});
    } else {
        throw new Error('Unknown type of join table!');
    }
    return this;
}

SQLBuilder.prototype.where = function(condition) {
    this.condition = condition;
    return this;
}

SQLBuilder.prototype.andWhere = function (condition) {
    this.condition += wrap('AND') + condition;
    return this;
}

SQLBuilder.prototype.orWhere = function (condition) {
    this.condition += wrap('OR') + condition;
    return this;
}

SQLBuilder.prototype.limit = function(number) {
    this.limit_condition = +number;
    return this;
}

SQLBuilder.prototype.groupBy = function(column) {
    if(this.groupby_condition === null)
        this.groupby_condition = [];
    this.groupby_condition.push(column);
    return this;
}

SQLBuilder.prototype.addGroupBy = function(column) {
    if(this.groupby_condition === null)
        throw new Error('Group by condition not defined!');
    this.groupby_condition.push(column);
    return this;
}

SQLBuilder.prototype.orderBy = function(column,sort) {
    if(this.orderby_condition === null)
        this.orderby_condition = [];
    sort === undefined ? this.orderby_condition.push(column) : this.orderby_condition.push(column + " " + sort);
    return this;
}

SQLBuilder.prototype.addOrderBy = function(column,sort) {
    if(this.orderby_condition === null)
        throw new Error('Order by condition not defined!');
    sort === undefined ? this.orderby_condition.push(column) : this.orderby_condition.push(column + " " + sort);
    return this;
}

SQLBuilder.prototype._buildJoinPart = function() {
    var sql = '';
    _.each(this.join_parts,function(val){
        _.each(val, function (ele) {
            sql += ' ' + ele['joinType'].toUpperCase() + ' JOIN ' + ( ele['tableType'] === 'subQuery' ? '( ' + ele['joinTable'] +  ' )' : ele['joinTable'] ) + ' ' + ele['joinAlias'] + ' ON ' + ele['joinCondition'];
        })
    });
    return sql;
}

/**
 * get the final SQL str
 * @returns {String} result
 */
SQLBuilder.prototype.toSQL = function() {
    if(this.action === null) {
        throw new Error('Undefined Action!');
    }
    var tmp = util.format(this.pattern,utils.type_of(this.select_columns,'Array') ? this.select_columns.join(',') : this.select_columns,this.table_name);
    if(this.in_join_mode)
        tmp += this._buildJoinPart();
    if(this.condition)
        tmp += wrap("WHERE") + this.condition;
    if(this.groupby_condition)
        tmp += wrap("GROUP BY") + this.groupby_condition.join(',');
    if(_.isArray(this.orderby_condition))
        tmp += wrap("ORDER BY") + this.orderby_condition.join(',');
    if(this.limit_condition) {
        tmp += wrap("LIMIT") + this.limit_condition;
    }
    return tmp;
}

function wrap(word,wrapper) {
    if(!wrapper)
        return " " + word + " ";
    return wrapper + word + wrapper;
}

module.exports = SQLBuilder;

utils.js

var _ = require('underscore');
var call = Function.prototype.call;
var unCurryingThis = function(f) {
    return function() {
        return call.apply(f,arguments);
    }
};

var array_slice = unCurryingThis(Array.prototype.slice);
var object_toString = unCurryingThis(Object.prototype.toString);
var type_of = function(object,type){
    return object_toString(object).toLowerCase() === ('[Object ' + type + ']').toLowerCase();
};
var obj_compare = function (obj1,obj2) {
    var has_conflict = false;
    _.each(obj1,function(val,key){
        if(obj2.hasOwnProperty(key) && obj2[key] != val) {
            has_conflict = true;
        }
    });
    return has_conflict;
};
module.exports.unCurryingThis = unCurryingThis;
module.exports.array_slice = array_slice;
module.exports.object_toString = object_toString;
module.exports.type_of = type_of;
module.exports.obj_compare = obj_compare;