sql 存取 log 可以用來查尋所有的操作軌跡,這邊使用 codeigniter 的 hooks,來達成自動記錄 query 的功能。
資料庫
CREATE TABLE `query_logs` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`type` CHAR(10) NOT NULL,
`location` VARCHAR(255) NOT NULL,
`content` TEXT NOT NULL,
`admin_id` INT NOT NULL,
INDEX(`admin_id`),
`created_at` DATETIME NOT NULL
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
type 用來記錄 sql 的處理方式,例如說是 update 還是 delete,content 的部分存整句的語法,admin_id 是我後台的管理者 id,我這邊的範例只打算記錄後台操作 insert, update, delete 三種行為,所以我用 admin_id 當作是否要記錄的依據,如果要做更細的設定可以再行配置。
設定
application/config/autoload.php
$autoload['libraries'] = array('database', 'session');
$autoload['config'] = array('site');
application/config/hooks.php
$hook['post_controller'] = [
'class' => 'QueryLogger',
'function' => 'save',
'filename' => 'QueryLogger.php',
'filepath' => 'hooks',
'params' => ''
];
application/config/site.php
$config['query_types'] = [
'insert',
'update',
'delete'
];
我目前只紀錄了這三個事件,如果有其他需求可以透過 config 增減
Hook 功能
application/hooks/QueryLogger.php
<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class QueryLogger
{
private $ci;
private $types;
public function __construct()
{
$this->ci =& get_instance();
$this->types = $this->ci->config->item('query_types');
}
public function save()
{
if ($this->can_saved()) {
foreach ($this->ci->db->queries as $query) {
$query = str_replace("\n", '', $query);
$type = $this->get_type($query);
if ($type !== '') {
$this->ci->db->insert('query_logs', [
'type' => $type,
'content' => $query,
'location' => $this->ci->router->fetch_class().'/'.$this->ci->router->fetch_method(),
'admin_id' => $this->ci->session->admin_id,
'created_at' => date('Y-m-d H:i:s')
]);
}
}
}
}
public function can_saved()
{
if (count($this->ci->db->queries) > 0 && $this->ci->session->has_userdata('admin_id')) {
return true;
}
return false;
}
public function get_type($query)
{
return implode('', array_filter($this->types, function ($type) use ($query) {
return stristr($query, $type);
}));
}
}
沒有留言:
張貼留言