chat_ws/framework/lib/TableHelper.js

"use strict";

var MySql = require('./OujMySql');

class TableHelper {
    /**
     * 基本类，提供增删改查
     * @param {string} tableName 表名
     * @param {string} dbKey
     */
    constructor(tableName, dbKey) {
        this.tableName = tableName;
        this.dbKey = dbKey || 'default';
        this.objMySql = new MySql(this.dbKey);
    }

    setTableName(tableName) {
        this.tableName = tableName;
    }

    /**
     * 读取数据
     * @param  {object} where   参数列表，特殊参数前缀
     * @param  {object} keyWord 查询关键字['_field', '_where', '_limit', '_sortKey', '_sortDir', '_lockRow', '_tableName', '_groupby']
     * @return {object}
     */
    getOne(where, keyWord) {
        var sql = this.buildSelectSql(where, keyWord);
        return this.objMySql.getOne(sql);
    }

    getCol(where, keyWord) {
        var sql = this.buildSelectSql(where, keyWord);
        return this.objMySql.getCol(sql);
    }

    getRow(where, keyWord) {
        var sql = this.buildSelectSql(where, keyWord);
        return this.objMySql.getRow(sql);
    }

    getAll(where, keyWord) {
        var sql = this.buildSelectSql(where, keyWord);
        return this.objMySql.getAll(sql, 0);
    }

    updateObject(newData, where) {
        if (!where) {
            throw new SQLException('updateObject没有传入where');
        }

        var _where = this.buildWhereSql(where);
        var sql = 'UPDATE `' + this.tableName + '` SET ';
        sql += this.buildValueSql(newData) + ' WHERE ' + _where;

        return this.objMySql.update(sql);
    }

    delObject(where) {
        if (!where) {
            throw new SQLException('updateObject没有传入where');
        }

        var _where = this.buildWhereSql(where);
        var sql = 'DELETE FROM `' + this.tableName + '` WHERE ' + _where;

        return this.objMySql.update(sql);
    }


    replaceObject(newData) {
        var sql = 'REPLACE INTO `' + this.tableName + '` SET ';
        sql += this.buildValueSql(newData);
        return this.objMySql.update(sql);
    }

    addObject(data) {
        var sql = 'INSERT `' + this.tableName + '` SET ';
        sql += this.buildValueSql(data);

        return this.objMySql.update(sql);
    }

    close() {
        return this.objMySql.close();
    }

    buildValueSql(data, separator) {
        separator = separator || ',';
        var pool = this.objMySql.getPool();
        var values = [];
        for (let key in data) {
            values.push(pool.escapeId(key) + '=' + pool.escape(data[key]));
        }

        return values.join(separator);
    }

    buildWhereSql(where, keyWord) {
        keyWord = keyWord || {};
        var _sql = keyWord['_where'] || '1';
        var pool = this.objMySql.getPool();

        where = where || {};
        for (let key in where) {
            _sql += " AND " + pool.escapeId(key) + "=" + pool.escape(where[key]);
        }

        if (keyWord['_groupby']) {
            _sql += " GROUP BY " + keyWord['_groupby'];
        }

        if (keyWord['_sortKey']) {
            _sql += " ORDER BY " + keyWord['_sortKey'];
            if (keyWord['_sortDir']) {
                _sql += " " + keyWord['_sortDir'];
            }
        }

        return _sql;
    }

    buildSelectSql(where, keyWord) {
        keyWord = keyWord || {};
        var _field = keyWord['_field'] || '*';
        var pool = this.objMySql.getPool();

        var tableName = keyWord['_tableName'] || this.tableName;
        var _where = this.buildWhereSql(where, keyWord);
        tableName = pool.escapeId(tableName);

        return `SELECT ${_field} FROM ${tableName} WHERE ${_where}`;
    }

}

module.exports = TableHelper;