SugarNode-TS/source/system/lib/extra/mysqldb.ts

/**
 * @Author  HonorLee (dev@honorlee.me)
 * @Version 1.0 (2018-05-04)
 * @License MIT
 */
import Mysql = require('mysql');
interface DBClient{
    getConnection:(callback:(err:Error,connection:any)=>void|null)=>void;
}
//数据对象
export class MysqlDB{
    private _pool:DBClient;
    constructor(option:DBOption){
        this._pool = new MysqlPool().create(option);
    }
    public query(queryStr:string,callback:(err:Error,ret:any,fields:any)=>void){
        this._pool.getConnection((err,con)=>{
            if(err) return callback(err,null,null);
            con.query(queryStr,(err:Error, ret:any, fields:any)=>{
                if(err){
                    LOGGER.error(`MysqlDB: "${queryStr}" error`);
                    if(err.stack) LOGGER.error(err.stack);
                }
                con.release();
                if(callback) callback(err,ret,fields);
            });
        });
    }
    public querySync(queryStr:string){
        return new Promise((resolve)=>{
            this._pool.getConnection((err,con)=>{
                if(err){
                    if(err.stack) LOGGER.error(err.stack);
                    return resolve({err:err,data:[]});
                }
                con.query(queryStr,(err:Error, ret:any, fields:any)=>{
                    con.release();
                    if(err){
                        LOGGER.error(`MysqlDB: "${queryStr}" error`);
                        if(err.stack) LOGGER.error(err.stack);
                        return resolve({err:err,data:[]});
                    }
                    resolve({err:null,data:ret});
                });
            });
        })
    }
    async update(tablename:string,updateObj:any,whereObj:any){
        if(!tablename || !updateObj) return;
        let whereRule="";
        const updateRule = MysqlUtil.objToKVString(updateObj);
        if(whereObj){
            whereRule = ` where ${MysqlUtil.objToKVString(whereObj,' and ')}`;
        }
        const sqlStr = `update ${tablename} set ${updateRule} ${whereRule}`;
        return await this.querySync(sqlStr);
    }
    async select(tablename:string,queryField?:Array<string>|null,whereObj?:any){
        if(!tablename){
            const err = new Error("Missing table name")
            if(err.stack) LOGGER.error(err.stack);
            return {err:err,data:null};
        }
        let queryStr = "*",whereStr = "";
        if(queryField) queryStr = queryField.join(",");
        if(whereObj) whereStr = ` where ${MysqlUtil.objToKVString(whereObj,' and ')}`;

        const sql = `select ${queryStr} from ${tablename} ${whereStr}`;
        return await this.querySync(sql);
    }
    async insert(tablename:string,insertData:any){
        if(!tablename){
            const err = new Error("Missing table name")
            if(err.stack) LOGGER.error(err.stack);
            return {err:err,data:null};
        }
        if(!insertData){
            const err = new Error("Missing insertData")
            if(err.stack) LOGGER.error(err.stack);
            return {err:err,data:null};
        }
        let sql = `insert into ${tablename} `;
        if(insertData.constructor == Object){
            sql += `set ${MysqlUtil.objToKVString(insertData)}`;
        }else if(insertData.constructor == Array && insertData.length>0 && insertData[0].constructor == Object){
            const fields = Object.keys(insertData[0]);
            const values = [];
            for(const data of insertData){
                const valueArr = [];
                for(const key of fields){
                    if(data[key] != null || data[key] != undefined){
                        if(data[key].constructor == String){
                            data[key] = `"${data[key]}"`;
                        }else if(data[key].constructor == Number){
                            data[key] = Number(data[key]);
                        }
                    }
                    valueArr.push(data[key]);
                }
                values.push(`(${valueArr.join(',')})`);
            }
            for(const i in fields){
                fields[i] = `\`${fields[i]}\``;
            }
            sql += `(${fields.join(',')}) values ${values.join(',')}`;            
        }else{
            const err = new Error("InsertData type not support")
            if(err.stack) LOGGER.error(err.stack);
            return {err:err,data:null};
        }
        return await this.querySync(sql);
    }
}
export class MysqlUtil{
    /**
     * K-V数据转为Sql语句结构
    **/
    public static objToKVString(obj:any,joinStr?:string,keyPrefix?:string){
        const strArr:Array<string> = [];

        const keys = Object.keys(obj);
        let _extraSql = '',_extraLink = '';
        keyPrefix = keyPrefix?`${keyPrefix}.`:'';
        if(keys.length>0){
            keys.forEach(key =>{
                let v = obj[key];
                if(key=='_extra'){
                    if(Array.isArray(v)){
                        _extraSql = v.join(joinStr);
                    }else if(v.constructor == Object){
                        if(v.sql){
                            _extraSql = v.sql;
                            _extraLink = v.link||'and';
                        }
                    }else{
                        _extraSql = v;
                    }
                }else{
                    if(typeof(v)=='string'){
                        if(v[0]=='='){
                            v = v.substring(1,-1);
                        }else{
                            if(!(v.substr(0,1)=="'" && v.substr(-1,1)=="'") && !(v.substr(0,1)=='"' && v.substr(-1,1)=='"')){
                                v = `"${v}"`;
                            }else{
                                v = v;
                            }
                        }
                    }
                    keyPrefix = keyPrefix?`${keyPrefix}`:'';
                    const sqlkey = `${keyPrefix}\`${key}\``;
                    strArr.push(`${sqlkey}=${v}`);
                    
                }
            })
            const sql = `${strArr.join(joinStr||',')} ${_extraLink} ${_extraSql}`;
            return sql;
        }else{
            return '';
        }
    }
    /** 
     * 自动处理数据模型与数据,返回处理后符合模型的数据对象,包含数据安全性处理
     * @param module    数据模型
     * @param data      待处理数据
     * @param filter    自动过滤非模型字段数据(可选,默认不过滤)
     * @prarm reverse   反向处理,由写入处理变为取出处理,并自动根据模型字段类型进行可视化处理(可选,默认写入状态)
    **/
    public static analyzeFields(module:any,data:any,filter?:boolean,reverse?:boolean){
        const outData:AnyKeyString = {};
        Object.keys(data).forEach(key=>{
            if(module[key]!=undefined && data[key]!=null){
                let value = data[key];
                switch(module[key].type){
                    case String:
                        value = String(value);
                        if(!reverse) value = Mysql.escape(value)
                        break;
                    case Number:
                        value = Number(value)||0;
                        break;
                    case 'Date_Timestamp':
                    case 'DateTime_Timestamp':
                    case 'Time_Timestamp':
                        value = Number(value)||0;
                        if(value>0 && reverse){
                            if(module[key].type=='Date_Timestamp') value = Moment.unix(value).format('YYYY-MM-DD');
                            if(module[key].type=='DateTime_Timestamp') value = Moment.unix(value).format('YYYY-MM-DD HH:mm:ss');
                            if(module[key].type=='Time_Timestamp') value = Moment.unix(value).format('HH:mm:ss');
                        }
                        break;
                }
                if(module[key].reverseKey && reverse) key = module[key].reverseKey;
                outData[key] = value;
            }else{
                if(!filter) outData[key] = data[key];
            }
        })
        return outData;
    }
    public static escape(value:any){
        return Mysql.escape(value);
    }
    public static createPageLimit(page?:number,page_size?:number,orderField?:string){
        page = page||0;
        page_size = page_size||10;
        let sql = ``;
        if(orderField) sql += `ORDER BY \`${orderField}\` `;
        sql += `LIMIT ${page*page_size},${page_size}`;
        return sql;
    }
    public static createOrderBy(order_filed?:string,order_desc?:boolean){
        let orderStr = '';
        if(order_filed){
            orderStr = `order by \`${order_filed}\``;
            if(order_desc) orderStr += ' desc';
        }
        return orderStr;
    }
}


//连接池
class MysqlPool{
    private _option:DBOption|null = null;
    private _pool:any;
    public Client:DBClient|null = null;

    create(option:DBOption):DBClient{
        this._option = option;
        this._pool   = Mysql.createPool(option);
        this.Client  = {
            getConnection:(callback)=>{
                if(!this._pool) throw new Error("Mysql pool not created!");
                if(!callback || typeof(callback)!='function') throw new Error("Mysql pool get connection lost callback!");
                this._pool.getConnection((err:any,connection:any)=>{
                    callback(err,connection);
                });
            }
        }
        return this.Client;
    }
}