背景

前文中有寫道，在 MySQL 上加行級(jí)觸發(fā)器扒俯，反向調(diào)用 Web 服務(wù)器奶卓，以達(dá)到 Web 服務(wù)器被動(dòng)感知數(shù)據(jù)庫(kù)變更的需求一疯。

問(wèn)題

有個(gè)問(wèn)題就是，MySQL 的觸發(fā)器只能建在行上夺姑，所以有多少行發(fā)生變更墩邀，就會(huì)觸發(fā)多少次這個(gè)網(wǎng)絡(luò)請(qǐng)求，有時(shí)這個(gè)開(kāi)銷是不必要的盏浙。

解決方案

在 Web 服務(wù)器上作一個(gè)定時(shí)眉睹，在一個(gè)時(shí)間周期內(nèi)緩存收到的變更請(qǐng)求，如果在延時(shí)周期內(nèi)收到新的請(qǐng)求废膘，則順延周期時(shí)間竹海。

Code

const express = require('express');
const router = express.Router(null);
const schemas = require('../../models/schemas');
const poolCluster = require('../../conf/mysql.conf').poolCluster;
const roleUpdateCache = new Set();
let setTimeOuter = null;
const timeOutHandler = () => {
    const userPrivilegeModel = schemas.UserPrivilegeModel;
    console.log(1);
    userPrivilegeModel.remove(() => {
        console.log(2);
        poolCluster.getConnection((err, connection) => {
            console.log(err);
            console.log(3);
            connection.query('CALL proc_ht_getInitialPriv();', [], (err, rows) => {
                console.log('result');
                if (err) {
                    throw err;
                }
                const result = {};
                for (let index = 0, len = rows[0].length; index < len; index++) {
                    const element = rows[0][index];
                    if (!result[element.roleId]) {
                        result[element.roleId] = new userPrivilegeModel();
                        result[element.roleId].roleId = element.roleId;
                    }
                    result[element.roleId].privilegeInfo.privilegeCode.push(element.privilegeCode);
                    result[element.roleId].privilegeInfo.privilegeLevel.push(element.privilegeLevel);
                    result[element.roleId].privilegeInfo.privilegeScope.push(element.privilegeScope);
                    result[element.roleId].privilegeInfo.privilegeRoute.push(element.privilegeRoute);
                }
                userPrivilegeModel.insertMany(Object.values(result)).then(() => {
                    schemas.SessionModel.find({
                        'session.roleId': {$in: Array.from(roleUpdateCache)}
                    }, 'session.uid', (err, result) => {
                        console.log('Old roleId and New roleId:');
                        console.log(roleUpdateCache);
                        roleUpdateCache.clear();
                        // TODO: Exception handler.
                        console.log(result);
                        if (result.length !== 0) {
                            const uid = result[0]['session']['uid'];
                            const wsMap = require('../../models/entities');
                            const wsInstance = wsMap.get(uid);
                            wsInstance ? wsInstance.send('Test Message.') : console.log('Can not find this user\'s ws handle in wsMap!\n Maybe you should reboot your app.');
                        }
                    })
                });
            });
        });
    });
};
router.get('/role-privilege-update', (req, res) => {
    const params = req.query;
    const oldRoleId = params['old_roleid'];
    const newRoleId = params['new_roleid'];
    res.end();
    roleUpdateCache.add(+oldRoleId);
    roleUpdateCache.add(+newRoleId);
    console.log(roleUpdateCache);
    if (setTimeOuter) {
        clearTimeout(setTimeOuter);
    }
    setTimeOuter = setTimeout(() => {
        console.log(123);
        console.log(roleUpdateCache);
        timeOutHandler();
    }, 2000);
});
router.get('/role-privilege-delete-or-insert', (req, res) => {
    const params = req.query;
    const roleIdConcerned = params['roleid_concerned'];
    res.end();
    roleUpdateCache.add(+roleIdConcerned);
    if (setTimeOuter) {
        clearTimeout(setTimeOuter);
    }
    setTimeOuter = setTimeout(() => {
        timeOutHandler();
    }, 2000);
});
module.exports = router;