CloudSQLAdminPanel/routes/databaseList.js

const express = require('express');
const router = express.Router();
const crypto = require('crypto');
const events = require('events');
const db = require('../common/mysqlConnection');

/**
 * 获取数据库列表
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getDatabaseList(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            pageIndex = req.query.pageIndex - 1,
            orderBy = req.query.orderBy,
            searchValue = req.query.search;

        emitter.on('ok', (results, count) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results,
                    'count': count
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectDatabseInfoFromDbOwnTable', () => {
            switch (orderBy) {
                case 'userId':
                    orderBy = 'dbOwnUuid';
                    break;

                case 'databaseName':
                    orderBy = 'dbName';
                    break;

                case 'createDate':
                    orderBy = 'createDate';
                    break;

                default:
                    orderBy = 'dbOwnUuid';
                    break;
            }

            if (pageIndex > 0) {
                pageIndex = parseInt(pageIndex, 10) * 20;
            } else {
                pageIndex = 0;
            }

            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT db.dbName, db.dbOwnUuid, user.account, db.createDate FROM dbOwn_test AS db, user_test AS user WHERE db.dbOwnUuid = user.uuid ORDER BY ? ASC) a LIMIT ?, 20; SELECT FOUND_ROWS()',
                value = [orderBy, pageIndex];

            if (searchValue) {
                // sqlString = 'SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT db.dbName, db.dbOwnUuid, user.account, db.createDate FROM dbOwn_test AS db, user_test AS user WHERE db.dbOwnUuid = user.uuid AND db.dbName LIKE ? OR db.dbOwnUuid LIKE ? OR user.account LIKE ? OR db.createDate LIKE ? ORDER BY ? ASC) a LIMIT ?, 20; SELECT FOUND_ROWS()';
                sqlString = 'SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT * FROM (SELECT db.dbName, db.dbOwnUuid, user.account, db.createDate FROM dbOwn_test AS db, user_test AS user WHERE db.dbOwnUuid = user.uuid) AS r WHERE r.dbName LIKE ? OR r.dbOwnUuid LIKE ? OR r.account LIKE ? OR r.createDate LIKE ? ORDER BY ? ASC) a LIMIT ?, 20; SELECT FOUND_ROWS()';
                searchValue = '%' + searchValue + '%';
                value = [searchValue, searchValue, searchValue, searchValue, orderBy, pageIndex];
            }

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results[0], results[1][0]['FOUND_ROWS()']);
                }
            });
        });

        emitter.emit('selectDatabseInfoFromDbOwnTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 获取数据库总数
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getDatabaseCount(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter();

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectUserCountFromUserTable', () => {

            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT COUNT(1) FROM dbOwn_test',
                value = null;

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results[0]['COUNT(1)']);
                }
            });
        });

        emitter.emit('selectUserCountFromUserTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 获取数据库所有表
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getDatabaseTables(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            dbName = req.query.dbName;

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectDatabseTables', () => {
            //定义SQL语句查询
            let pool = db.pool.userDatabasePool,
                sqlString = 'SELECT * FROM information_schema.`tables` a WHERE a.TABLE_SCHEMA = ?',
                value = [dbName];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results);
                }
            });
        });

        emitter.emit('selectDatabseTables');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 删除数据库
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function dropDatabase(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            dbName = req.body.dbName;

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '删除成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('deleteRow', () => {
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'DELETE FROM dbOwn_test WHERE dbName = ?',
                value = [dbName];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, 'sql执行出错');
                } else {
                    emitter.emit('ok', results);
                }
            });
        })

        emitter.on('dropDb', () => {
            //定义SQL语句
            let pool = db.pool.userDatabasePool,
                sqlString = 'DROP DATABASE ' + dbName,
                value = null;

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, 'sql执行出错');
                } else {
                    emitter.emit('deleteRow', results);
                }
            });
        });

        emitter.emit('dropDb');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 清空数据库
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function emptyDatabase(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            dbName = req.body.dbName;

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '删除成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('dropTable', (results) => {
            let pool = db.pool.userDatabasePool,
                value = null;

            results.forEach((item, index) => {
                let sqlString = 'USE ' + dbName + '; ' + item['CONCAT("DROP TABLE ", table_name, ";")'];

                db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                    if (err) {
                        emitter.emit('err', err.err, 'sql执行出错');
                    } else {
                        emitter.emit('ok', results);
                    }
                });
            }, this);
        });

        emitter.on('getAllTable', () => {
            //定义SQL语句查询
            let pool = db.pool.userDatabasePool,
                sqlString = 'SELECT CONCAT("DROP TABLE ", table_name, ";") FROM information_schema.tables WHERE table_schema = ?',
                value = [dbName];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, 'sql执行出错');
                } else {
                    if (results.length === 0) {
                        emitter.emit('err', null, '该数据库无数据表');
                    } else {
                        emitter.emit('dropTable', results);
                    }
                }
            });
        });

        emitter.emit('getAllTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

router.get('/', getDatabaseList);
router.get('/getDatabaseCount', getDatabaseCount);
router.get('/getDatabaseAllTables', getDatabaseTables);
router.post('/dropDatabase', dropDatabase);
router.post('/emptyDatabase', emptyDatabase);


module.exports = router