Query Log

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);
        }));
    }
}