denet-node-server/model/mysql_db.js

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

var mysql = require('mysql');
var { db_config } = require('../config/config.js')
const logger = require('./logger')

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 opts = {
    host: host,
    user: username,
    password: password,
    port: port,
    database: database,
}
var MYSQL_INSTANCE = null;

// opts.connectionLimit = 10
// MYSQL_INSTANCE = mysql.createPool(opts);
createDefMysqlConnect();

function createDefMysqlConnect() {
    disDefMysqlConnect();
    logger.log('createDefMysqlConnect')
    MYSQL_INSTANCE = mysql.createConnection(opts);
    MYSQL_INSTANCE.connect();
}

function disDefMysqlConnect() {
    if (MYSQL_INSTANCE) {
        MYSQL_INSTANCE.end();
        MYSQL_INSTANCE = null;
        logger.debug('dis mysql')
    }
}

// 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 =>');
});

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

MYSQL_INSTANCE.on('connection', function (connection) {
    logger.log('mysql connection', connection);
});
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);
});

// MYSQL_INSTANCE

MYSQL_INSTANCE.on('uncaughtException', function (err) {
    logger.error('mysql uncaughtException  =>', err);
    if (err.code == "PROTOCOL_CONNECTION_LOST") {
        // mysql.restart();
    }
});



function queryCompanyInfoFromId(id) {
    createDefMysqlConnect()
    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) => {
        // MYSQL_INSTANCE.getConnection(function (err, connection) {
        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) {
    createDefMysqlConnect()
    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) => {

        // MYSQL_INSTANCE.getConnection(function (err, connection) {
        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);
    createDefMysqlConnect()
    return new Promise((resolve) => {
        // MYSQL_INSTANCE.getConnection(function (err, connection) {
        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,
};