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});
                });
            });
        })
    }
    objToKVString(obj:any,joinStr?:string,keyPrefix?:string){
        const strArr:Array<string> = [];

        Object.keys(obj).forEach(key =>{
            let v = obj[key];
            if(key=='extraStr'){
                if(Array.isArray(v)){
                    strArr.push(v.join(joinStr));
                }else{
                    strArr.push(v);
                }
            }else{
                if(typeof(v)=='string'){
                    if(v[0]=='='){
                        v = v.substring(1,-1);
                    }else{
                        v = `"${v}"`;
                    }
                }
                keyPrefix = keyPrefix?keyPrefix:'';
                const sqlkey = `${keyPrefix}\`${key}\``;
                strArr.push(`${sqlkey}=${v}`);
                
            }
        })
        if(joinStr) return strArr.join(joinStr);
        return strArr.join(",");
    }
    async update(tablename:string,updateObj:any,whereObj:any){
        if(!tablename || !updateObj) return;
        let whereRule="";
        const updateRule = this.objToKVString(updateObj);
        if(whereObj){
            whereRule = ` where ${this.objToKVString(whereObj,' and ')}`;
        }
        const sqlStr = `update ${tablename} set ${updateRule} ${whereRule}`;
        return await this.querySync(sqlStr);
    }
    async select(tablename:string,queryField?:Array<string>,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 ${this.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 ${this.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);
    }
}


//连接池
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;
    }
}