1. 云栖社区>
  2. PHP教程>
  3. 正文

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

作者:用户 来源:互联网 时间:2017-11-30 20:46:33

sql日志实现版本操作基于

基于sql的操作日志:CI版本实现 - 摘要: 本文讲的是基于sql的操作日志:CI版本实现, 这家公司使用的是CI2.1,刚来的时候对系统不熟,CI框架也从来没有接触过,接到日志功能开发任务的时候一开始是是很懵的,难道是要我往每一个已有的功能点加入日志语句(或许需求真的是这样),还好本人懒,没去这么做,采取了另一种思路:利用CI



这家公司使用的是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,具体操作名,变动的字段值.



基于sql的操作日志:CI版本实现-
14.1.png

基于sql的操作日志:CI版本实现-
14.2.png



以上是云栖社区小编为您精心准备的的内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索sql , 日志 , 实现 , 版本 , 操作 基于 ,以便于您获取更多的相关知识。