denet-node-server/model/db/mysql_db.js

// mysql 学习资料
//https://www.runoob.com/nodejs/nodejs-mysql.html
//https://blog.csdn.net/hbiao68/article/details/89097853
//https://github.com/mysqljs/mysql

var mysql = require('mysql');
var { db_config } = require('../../config/config.js')
const logger = require('../logger')
const utils = require('../utils.js')
var port = db_config.mysql.PORT
var host = db_config.mysql.HOST
var username = db_config.mysql.USERNAME
var password = db_config.mysql.PASSWORD
var database = db_config.mysql.DATABASE
var tablename = db_config.mysql.TABLENAME
var open_pool = db_config.mysql.OPEN_POOL
var pool_size = db_config.mysql.POOL_SIZE

var opts = {
    host: host,
    user: username,
    password: password,
    port: port,
    database: database,
}
var MYSQL_INSTANCE = null;

//程序启动默认创建一个 mysql 连接
createDefMysqlConnect();
// 开始创建
function createDefMysqlConnect() {
    disDefMysqlConnect();
    logger.log('createDefMysqlConnect',database)
    if (open_pool) {//创建连接池
        opts.connectionLimit = pool_size
        MYSQL_INSTANCE = mysql.createPool(opts);
        addConnEvent()
    } else {//创建单连接
        MYSQL_INSTANCE = mysql.createConnection(opts);
        addConnEvent()
        MYSQL_INSTANCE.connect();
    }
    return MYSQL_INSTANCE;
}
//关闭 mysql
function disDefMysqlConnect() {
    if (MYSQL_INSTANCE) {
        if (open_pool) {
            MYSQL_INSTANCE.end();
        } else {
            MYSQL_INSTANCE.end();
        }
        MYSQL_INSTANCE = null;
        logger.debug('disDefMysqlConnect')
    }
}

function getMySqlInstance() {
    if (MYSQL_INSTANCE) return MYSQL_INSTANCE
    return createDefMysqlConnect();
}

function addConnEvent() {
    if (!MYSQL_INSTANCE) return
    if (open_pool) {
        MYSQL_INSTANCE.on('acquire', function (connection) {
            logger.log('Connection %d acquired', connection.threadId);
        });

        MYSQL_INSTANCE.on('connection', function (connection) {
            logger.log('mysql connection', connection.threadId);
        });
        MYSQL_INSTANCE.on('enqueue', function () {
            logger.log('Waiting for available connection slot');
        });

        MYSQL_INSTANCE.on('release', function (connection) {
            logger.log('Connection %d released', connection.threadId);
        });
    } else {
        // logger.log('connected to mysql ps=', db_config.mysql)
        MYSQL_INSTANCE.on('connect', () => {
            logger.log('connected to mysql')
        })

        MYSQL_INSTANCE.on('error', function (err) {
            logger.error('mysql  Error =>', err);
        });


        MYSQL_INSTANCE.on('restart', function () {
            logger.error('mysql  restart =>');
        });
    }
}



function queryCompanyInfoFromId(id) {
    var sql_main = 'select * from '
    var sql_table_name = 'company_key_manage'
    var sql_where = ' WHERE '
    var sql_where_name = ' id= ? '
    var sql_where_value = id
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryCompanyPrivateKeyFromId new_sql=', new_sql);
    return new Promise((resolve) => {
        if (open_pool) {
            getMySqlInstance().getConnection(function (err, connection) {
                if (err) {
                    logger.error('queryCompanyInfoFromId', err)
                    logger.error('new_sql', new_sql, id)
                    resolve(null);
                    return;
                }
                connection.query(
                    new_sql, [sql_where_value],
                    function selectCb(error, results) {
                        if (error) {
                            logger.error('queryCompanyInfoFromId', error)
                            logger.error('new_sql', new_sql, id)
                            resolve(null);
                            return;
                        }
                        logger.log('queryCompanyPrivateKeyFromId ret=', error, results);
                        //用完当前连接需要释放,归还给连接池
                        connection.release();

                        var ret;
                        if (results && Array.isArray(results) && results.length > 0) {
                            ret = results[0]
                        } else {
                            ret = null
                        }
                        // resolve 只能传递一个参数
                        resolve({
                            err: error,
                            results: ret
                        });
                    }
                );
            })
        } else {
            createDefMysqlConnect()
            MYSQL_INSTANCE.query(
                new_sql, [sql_where_value],
                function selectCb(err, results) {
                    logger.log('queryCompanyPrivateKeyFromId ret=', err, results);
                    var ret;
                    if (results && Array.isArray(results) && results.length > 0) {
                        ret = results[0]
                    } else {
                        ret = null
                    }
                    // resolve 只能传递一个参数
                    resolve({
                        err: err,
                        results: ret
                    });
                    disDefMysqlConnect();
                }
            );
        }


    })
}

/**
 * 查询用户私钥
 * 
 * @param {*} user_address 
 * @returns 
 */
function queryUserPrivateKeyFromUserAddress(user_address) {
    var sql_main = 'select * from '
    var sql_table_name = tablename
    var sql_where = ' WHERE '
    var sql_where_name = ' user_address= ? '
    var sql_where_value = user_address
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryUserPrivateKeyFromUserAddress new_sql=', new_sql);
    return new Promise((resolve) => {
        if (open_pool) {
            getMySqlInstance().getConnection(function (err, connection) {
                if (err) {
                    logger.error('queryUserPrivateKeyFromUserAddress', err)
                    logger.error('new_sql', new_sql, id)
                    resolve(null);
                    return;
                }
                connection.query(
                    new_sql, [sql_where_value],
                    function selectCb(error, results) {
                        if (error) {
                            logger.error('queryUserPrivateKeyFromUserAddress', error)
                            logger.error('new_sql', new_sql, id)
                            resolve(null);
                            return;
                        }
                        logger.log('queryUserPrivateKeyFromUserAddress ret=', error, results);
                        //用完当前连接需要释放,归还给连接池
                        connection.release();
                        var ret;
                        if (results && Array.isArray(results) && results.length > 0) {
                            ret = results[0].user_private_key
                        } else {
                            ret = null
                        }
                        // resolve 只能传递一个参数
                        resolve({
                            err: err,
                            results: ret
                        });
                    }
                );
            })
        } else {
            createDefMysqlConnect()
            MYSQL_INSTANCE.query(
                new_sql, [sql_where_value],
                function selectCb(err, results) {
                    logger.log('queryUserPrivateKeyFromUserAddress ret=', err, results);
                    var ret;
                    if (results && Array.isArray(results) && results.length > 0) {
                        ret = results[0].user_private_key
                    } else {
                        ret = null
                    }
                    // resolve 只能传递一个参数
                    resolve({
                        err: err,
                        results: ret
                    });
                    disDefMysqlConnect();
                }
            );
        }
    })
}


/**
 * 查询用户私钥
 * 
 * @param {*} user_address 
 * @returns 
 */
function queryAllUserPrivateKeyAndUserAddress() {
    var sql_main = 'select * from '
    var sql_table_name = db_config.mysql.TABLENAME
    var sql_where = ' order by id desc limit 3'
    var sql_where_name = '  '
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryAllUserPrivateKeyAndUserAddress new_sql=', new_sql);
    return new Promise((resolve) => {
        if (open_pool) {
            getMySqlInstance().getConnection(function (err, connection) {
                if (err) {
                    logger.error('queryAllUserPrivateKeyAndUserAddress', err)
                    logger.error('new_sql', new_sql, id)
                    resolve(null);
                    return;
                }
                connection.query(
                    new_sql, [],
                    function selectCb(error, results) {
                        if (error) {
                            logger.error('queryAllUserPrivateKeyAndUserAddress', error)
                            logger.error('new_sql', new_sql, id)
                            resolve(null);
                            return;
                        }

                        //用完当前连接需要释放,归还给连接池
                        connection.release();

                        if (results && Array.isArray(results) && results.length > 0) {
                            resolve(results);
                        } else {
                        }
                    }
                );
            })
        } else {
            createDefMysqlConnect()
            MYSQL_INSTANCE.query(
                new_sql, [],
                function selectCb(err, results) {
                    if (results && Array.isArray(results) && results.length > 0) {
                        resolve(results);
                    } else {
                    }
                }
            );
            disDefMysqlConnect();
        }
    })
}

//查询语句
//SELECT * FROM user_key_manage WHERE user_address='0x049D33EEE7432DB14c50911fAE4a7C38624313aA'



module.exports = {
    queryUserPrivateKeyFromUserAddress,
    queryAllUserPrivateKeyAndUserAddress,
    queryCompanyInfoFromId,
    getMySqlInstance,
};