zxy-comm/lib/mysql.js

var mysql = require('mysql');
var queues = require('mysql-queues');
var db = {};
var zxy = require('zxy-comm');
var verify = require('./comm/verify');
var promise = require('bluebird');
var fs = require('fs');
var path = require('path');

var runMysql = function (config) {
    if (config) {
        this.db = db = config;
        this.query = query;
        this.run = run;
        if (!db.database) {
            db.database = "localdb";
        }
        var sql = "SET GLOBAL max_connections = 16384;";
        query(sql);
        var file = path.join(__dirname, './db/upload');
        sql = fs.readFileSync(file, "utf-8");
        query(sql);
        file = path.join(__dirname, './db/ucpaas');
        sql = fs.readFileSync(file, "utf-8");
        query(sql);
        file = path.join(__dirname, './db/api');
        sql = fs.readFileSync(file, "utf-8");
        query(sql);
        if (zxy.app.v_singleCoreProcess) {
            console.info("a single core process[" + process.pid + "] mysql connected to", config.host, ":", config.port);
        }
        return this;
    }
    else {
        return undefined;
    }
}
exports.runMysql = runMysql;

//执行mysql
var query_connect = function (sql, callback) {
    var client = mysql.createConnection(db);
    client.connect(function (err) {
        if (err) {
            callback && callback(false);
            return;
        }
        client.query(sql, null, function (err, results) {
            client.end();
            if (err) {
                callback && callback(false);
            }
            else {
                callback && callback(results);
            }
        });
    });
}

// //执行mysql 使用连接池
// var query = function (sql, callback) {
//     var pool = mysql.createPool(db);
//     var conn;
//     pool.getConnection(function (err, connection) {
//         if (!err) {
//             conn = connection;
//             conn.query(sql, function (err, result) {
//                 conn && conn.release();
//                 if (err) {
//                     callback && callback(false);
//                 }
//                 else {
//                     callback && callback(result);
//                 }
//             });
//         }
//         else {
//             callback && callback(false);
//         }
//     });
// }

//执行mysql 使用连接池和参数化
var query = function () {
    var sql = "";
    var data = null;
    var callback = null;
    if (arguments && arguments.length) {
        sql = arguments[0];
        data = arguments[1];
        callback = arguments[2];
    }
    if (!callback && typeof data == 'function') {
        callback = data;
        data = null;
    }
    var pool = mysql.createPool(db);
    var conn;
    pool.getConnection(function (err, connection) {
        if (!err) {
            conn = connection;
            if (data && verify.isJson(data)) {
                sql = makeSQL(sql, data);
            }
            // console.info(sql);
            conn.query(sql, function (err, result) {
                conn && conn.release();
                if (err) {
                    callback && callback(false);
                }
                else {
                    callback && callback(result);
                }
            });
        }
        else {
            console.error("mysql connect error:", err.toString());
            callback && callback(false);
        }
    });
}

var makeSQL = function (sql, options) {
    for (var k in options) {
        if (typeof options[k] != 'function') {
            var reg = new RegExp("@" + k, "g");
            if (typeof options[k] == 'string' && options[k].substr(options[k].length - 1, 1) == '$') {
                options[k] += '$';
            }
            sql = sql.replace(reg, mysql.escape(options[k]));
        }
    }
    return sql;
}

var run = function (sql, data) {
    return new promise(function (resolve, reject) {
        var pool = mysql.createPool(db);
        var conn;
        pool.getConnection(function (err_conn, connection) {
            if (!err_conn) {
                conn = connection;
                if (data && verify.isJson(data)) {
                    sql = makeSQL(sql, data);
                }
                conn.query(sql, function (err, result) {
                    conn && conn.release();
                    if (err) {
                        reject(err);
                    }
                    else {
                        resolve(result);
                    }
                });
            }
            else {
                reject(err_conn);
            }
        });
    });
}