基于sql的操作日志:CI版本实现

作者: NO3340971 | 来源:发表于2017-10-27 11:32 被阅读192次

基于sql的操作日志:CI版本实现
可能是全网最详细的 Spark Sql Aggregate 源码
Laravel 打印执行sql语句
Delta Lake 分区表覆盖写入操作
5. GitLab CI/CD实践 - .gitlab-ci.y
JDK源码分析（3）HashMap
Microsoft SQL Server数据库完整性约束、索引、
flink sql if case when then 条件判断
Continuous Integration ★ Jenkins
从ReentrantLock学习AQS

这家公司使用的是CI2.1,刚来的时候对系统不熟,CI框架也从来没有接触过,接到日志功能开发任务的时候一开始是是很懵的,难道是要我往每一个已有的功能点加入日志语句(或许需求真的是这样),还好本人懒,没去这么做,采取了另一种思路:利用CI的钩子拦截每一条sql语句,对其分析,并记录数据的变动.

第一步: 建表

-- ----------------------
-- 日志主表
-- ----------------------
CREATE TABLE `mt_sql_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL COMMENT '管理员id',
  `desc` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '操作描述',
  `create_at` int(11) unsigned NOT NULL COMMENT '日志创建时间(格林威治标准时间)',
  `ip` int(11) unsigned NOT NULL COMMENT '操作的ip地址',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='sql操作日志表';
-- ---------------------
-- 日志详情表
-- ---------------------
CREATE TABLE `mt_sql_log_detail` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sql_log_id` bigint(20) unsigned NOT NULL,
  `tbid` int(11) unsigned NOT NULL COMMENT '被记录日志的表的主键id',
  `tbname` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT '表名',
  `type` enum('insert','update','delete') COLLATE utf8_unicode_ci NOT NULL COMMENT '操作类型：insert新增update修改delete删除',
  `old` text COLLATE utf8_unicode_ci,
  `new` text COLLATE utf8_unicode_ci,
  `sql` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'sql语句',
  PRIMARY KEY (`id`),
  KEY `sql_log_id` (`sql_log_id`),
  KEY `tbid` (`tbid`),
  KEY `tbname` (`tbname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='sql日志详情';

第二步: 定义配置文件 sql_log.php

$config['includeTbname'] = [
    'tbname_1',
    'tbname_2',
    'tbname_3'
];
$config['excludeTbname'] = [

];

第三步: 编写核心钩子文件

class SqlLogHook
{
    private static $log_id = 0;
    private static $uid = 0;

    private static $pattern = [
        'insert' => '/^insert\s+into\s+([^\(\s]+)/is',
        'delete' => '/^delete\s+from\s+(.*?)\s+where\s+(.*?)$/is',
        'update' => '/^update\s+(.*?)\s+set\s+.*?\s+where\s+(.*?)$/is'
    ];

    private static $cache = [];

    private static $mode = 'include';
    
    // 判断是否需要记录日志
    private static function isAllowTb($tbname){
        if (!self::$cache['conf']) {
            $ci = &get_instance();
            $user = $ci->session->userdata('users');
            self::$uid = $user['id'];
            
            $ci->config->load('sql_log', TRUE);
            self::$cache['conf'] = [
                'include' => $ci->config->item('includeTbname', 'sql_log'),
                'exclude' => $ci->config->item('excludeTbname', 'sql_log')
            ];

        }
        if (!self::$uid) {
            return false;
        }
        if (self::$mode == 'include') {
            return in_array(trim($tbname), self::$cache['conf']['include']);
        }else{
            if (false !== strpos($tbname, 'sql_log')) {
                return false;
            }
            return !in_array(trim($tbname), self::$cache['conf']['exclude']);
        }
    }

    //$type:  insert,update,delete
    private static function parseSql($sql, $type){
        $sql = trim(str_replace('`', ' ', $sql)," \t\r\n".PHP_EOL);
        if(isset(self::$pattern[$type]) && preg_match(self::$pattern[$type], $sql, $match)){
            if (!self::isAllowTb($match[1])) {
                return false;
            }
            $ci = &get_instance();
            $fields = $ci->db->field_data($match[1]);
            $pk = 'id';
            foreach ($fields as $field){
               if($field->primary_key == 1){
                    $pk = $field->name;
                    break;
               }
            }
            return [
                'tb' => trim($match[1]),
                'condition' => trim($match[2]),
                'pk' => $pk
            ];
        }else{
            return false;
        }
    }

    private static function actionInfo(){
        $ci = &get_instance();
        $permission_key = $ci->router->directory.$ci->router->class.'/'.$ci->router->method;
        $md5 = md5($permission_key);
        if (isset(self::$cache[$md5])) {
            return self::$cache[$md5];
        }
        //操作行为描述(这里是查的权限表,根据自己的项目而定)
        $sql = "select id,description,parent_id from mt_permission where `class_name` ='{$ci->router->directory}{$ci->router->class}' and `function_name`='{$ci->router->method}'";
        $row = $ci->db->query($sql)->row_array();
        if ($row['id']) {
            //找到各级父节点
            $ids = [$row['id']];
            $track = [$row];
            $id = $row['parent_id'];
            while ($id){
                $sql = "select id,description,parent_id from mt_permission where id={$id}";
                $rs = $ci->db->query($sql)->row_array();
                $id = $rs['parent_id'];
                array_unshift($ids, $id);
                array_unshift($track, $rs);
            }
            $desc = [];
            foreach ($track as $v) {
                array_push($desc, $v['description']);
            }
            self::$cache[$md5] = implode('=>', $desc);
        }else{
            self::$cache[$md5] = $permission_key;
        }
        return self::$cache[$md5];
    }

    private static function executeInsert($tbname, array $data){
        $fields = '';
        $values = '';
        foreach ($data as $field => $value) {
            $fields .= "`{$field}`,";
            $value = mysql_real_escape_string ( $value );
            $values .= "'{$value}',";
        }
        $fields = trim($fields, ',');
        $values = trim($values, ',');
        $sql = "insert into `{$tbname}` ({$fields})values({$values})";
        $ci = &get_instance();
        $ci->db->query($sql);
    }

    private static function log(array $detail = []){
        $ci = &get_instance();
        if (self::$log_id < 1) {
            $data = [
                'desc' => self::actionInfo(),
                'user_id' => self::$uid,
                'create_at' => time() - date('Z'), //保存格林威治时间
                'ip' => ip2long($ci->input->ip_address())
            ];
            self::executeInsert('mt_sql_log', $data);
            $re = $ci->db->query('SELECT LAST_INSERT_ID() AS id')->row_array();
            self::$log_id = $re['id'];
        }
        $detail['sql_log_id'] = self::$log_id;
        $detail['old'] = isset($detail['old']) ? json_encode($detail['old'], JSON_UNESCAPED_UNICODE) : '{}';
        $detail['new'] = isset($detail['new']) ? json_encode($detail['new'], JSON_UNESCAPED_UNICODE) : '{}';
        self::executeInsert('mt_sql_log_detail', $detail);
    }
    
    public static function update_before($sql){
        if($info = self::parseSql($sql, 'update')){
            $ci = &get_instance();
            $md5 = md5($sql);
            //找到现有数据
            $_sql = "select * from {$info['tb']} where {$info['condition']}";
            $items = $ci->db->query($_sql)->result_array();
            foreach ($items as $v) {
                //把旧数据缓存
                self::$cache[$md5.'-'.$v[$info['pk']]] = $v;
            }
        }
    }

    public static function delete_before($sql){
        if($info = self::parseSql($sql, 'delete')){
            $ci = &get_instance();
            //找到现有数据
            $_sql = "select * from {$info['tb']} where {$info['condition']}";
            $items = $ci->db->query($_sql)->result_array();
            foreach ($items as $old) {
                $old = array_filter($old,function($v){
                    if(is_null($v) || $v === '')return false;else return true;
                });
                self::log([
                    'tbid' => $old[$info['pk']],
                    'tbname' => $info['tb'],
                    'type' => 'delete',
                    'sql' => $sql,
                    'old' => $old
                ]);
            }
        }
    }

    public static function update_after($sql){
        if($info = self::parseSql($sql, 'update')){
            $ci = &get_instance();
            $md5 = md5($sql);
            //找到现有数据
            $_sql = "select * from {$info['tb']} where {$info['condition']}";
            $items = $ci->db->query($_sql)->result_array();
            foreach ($items as $new) {
                $cacheKey = $md5.'-'.$new[$info['pk']];
                //取出缓存的旧数据
                $old = self::$cache[$cacheKey];
                $_old = array_diff_assoc($old, $new);
                $_new = array_diff_assoc($new, $old);
                //保留主键信息
                $_old[$info['pk']] = $new[$info['pk']];
                $_new[$info['pk']] = $new[$info['pk']];
                self::log([
                    'tbid' => $new[$info['pk']],
                    'tbname' => $info['tb'],
                    'type' => 'update',
                    'sql' => $sql,
                    'old' => $_old,
                    'new' => $_new,
                ]);
                unset(self::$cache[$cacheKey]);
            }
        }
    }

    public static function insert_after($sql){
        if($info = self::parseSql($sql, 'insert')){
            $ci = &get_instance();
            $pkid = $ci->db->insert_id();
            //找到现有数据
            $_sql = "select * from `{$info['tb']}` where `{$info['pk']}`='{$pkid}'";
            $new = $ci->db->query($_sql)->row_array();
            $new = array_filter($new,function($v){
                if(is_null($v) || $v === '')return false;else return true;
            });
            self::log([
                'tbid' => $pkid,
                'tbname' => $info['tb'],
                'type' => 'insert',
                'sql' => $sql,
                'new' => $new
            ]);
        }
    }
}

第四步: 对CI框架源码进行修改

system/database/DB_driver.php 文件

public function simple_query($sql)
{
    if ( ! $this->conn_id)
    {
        $this->initialize();
    }

    return $this->_execute($sql);
}
// 改为:
protected static $last_inset_id = 0;  //增加一个属性,用于记录last_inset_id,修复SqlLogHook类的日志sql产生的影响
public function simple_query($sql)
{
    if ( ! $this->conn_id)
    {
        $this->initialize();
    }
    //是否需要记录sql日志,即:是否是一条正常的sql,而非记录日志的sql
    $needSqlLog = (false === strpos($sql, 'mt_sql_log'));
    if ($needSqlLog) {
        $queries = $this->queries;
        $query_times = $this->query_times;
        $EXT =& load_class('Hooks', 'core');
        $type = strtoupper(substr(trim($sql), 0, 6));

        switch ($type) {
            case 'UPDATE':
                $EXT->_call_hook('sql_update_before', $sql);
                break;
            case 'DELETE':
                $EXT->_call_hook('sql_delete_before', $sql);
                break;
            default:
                # code...
                break;
        }
    }
    if ($needSqlLog && $type == 'INSERT') {
        self::$last_inset_id = 0;
    }

    $re = $this->_execute($sql);

    if ($needSqlLog && $type == 'INSERT') {
        self::$last_inset_id = $this->insert_id();
    }
    if ($needSqlLog) {
        switch ($type) {
            case 'UPDATE':
                $EXT->_call_hook('sql_update_after', $sql);
                break;
            case 'INSERT':
                $EXT->_call_hook('sql_insert_after', $sql);
                break;
            default:
                # code...
                break;
        }
        $this->queries = $queries;
        $this->query_times = $query_times;
    }
    return $re;
}

修改各个驱动的 insert_id 方法,例如 system/database/drivers/mysql/mysql_driver.php

    function insert_id()
    {
        return @mysql_insert_id($this->conn_id);
    }
    // 改为:
    function insert_id()
    {
        //每个驱动都增加这个if语句
        if (self::$last_inset_id > 0) {
            return self::$last_inset_id;
        }
        return @mysql_insert_id($this->conn_id);
    }

改造钩子源码 system/core/Hooks.php, 以支持参数传递

public function _call_hook($which = '', $params = null) //-----增加了第二个参数
{
    if ( ! $this->enabled OR ! isset($this->hooks[$which]))
    {
        return FALSE;
    }

    if (isset($this->hooks[$which][0]) AND is_array($this->hooks[$which][0]))
    {
        foreach ($this->hooks[$which] as $val)
        {
            if(!is_null($params)) $val['params'] = $params;//-----增加的行
            $this->_run_hook($val);
        }
    }
    else
    {
        if(!is_null($params)) $this->hooks[$which]['params'] = $params;//-----增加的行
        $this->_run_hook($this->hooks[$which]);
    }

    return TRUE;
}

第五步: 配置文件中增加钩子的配置

$hook['sql_update_before'] = array(
    'class' => 'SqlLogHook',
    'function' => 'update_before',
    'filename' => 'SqlLogHook.php',
    'filepath' => 'hooks',
);
$hook['sql_delete_before'] = array(
    'class' => 'SqlLogHook',
    'function' => 'delete_before',
    'filename' => 'SqlLogHook.php',
    'filepath' => 'hooks',
);
$hook['sql_update_after'] = array(
    'class' => 'SqlLogHook',
    'function' => 'update_after',
    'filename' => 'SqlLogHook.php',
    'filepath' => 'hooks',
);
$hook['sql_insert_after'] = array(
    'class' => 'SqlLogHook',
    'function' => 'insert_after',
    'filename' => 'SqlLogHook.php',
    'filepath' => 'hooks',
);

到此,日志功能已经实现了,剩下的就是把日志数据展示出来,下面是我的截图,可以记录操作人,IP,具体操作名,变动的字段值.

14.1.png

14.2.png

基于sql的操作日志:CI版本实现
这家公司使用的是CI2.1,刚来的时候对系统不熟,CI框架也从来没有接触过,接到日志功能开发任务的时候一开始是是很...
可能是全网最详细的 Spark Sql Aggregate 源码
本文基于 Spark 2.3.0 源码，其他版本实现可能会略有不同默认情况下，下文的张杰基于以下 sql 进行分...
Laravel 打印执行sql语句
Laravel 5.* 版本在 sql执行前开启日志在sql执行后获取日志并打印示例：返回结果
Delta Lake 分区表覆盖写入操作
Delta Lake当前版本（0.5）只支持API操作的，但是实现 Insert SQL 语法也不难，可以参考 D...
5. GitLab CI/CD实践 - .gitlab-ci.y
GitLab CI/CD功能基于每个项目根目录下的.gitlab-ci.yml配置文件来实现。注意：.gitla...
JDK源码分析（3）HashMap
JDK版本 HashMap简介 HashMap基于哈希表的 Map 接口的实现。此实现提供所有可选的映射操作，并允...
Microsoft SQL Server数据库完整性约束、索引、
本文介绍基于Microsoft SQL Server软件，实现数据库表完整性约束、索引与视图创建等操作的方法。...
flink sql if case when then 条件判断
1. 场景：根据条件判断取值逻辑，用在flink的sql查询，版本：1.9 2. 参考文档：https://ci....
Continuous Integration ★ Jenkins
持续集成CI [Chapter 零] * Ref. 基于内部研发框架pactera jeesit版本 1.2.11...
从ReentrantLock学习AQS
本章java版本是java1.8 ReentrantLock 基于AQS实现，lock和unlock操作对应acq...