思路借鑒 淺談管理系統(tǒng)操作日志設(shè)計(jì)
說(shuō)明
采用中間件的方式 純屬原創(chuàng) 請(qǐng)轉(zhuǎn)載標(biāo)明來(lái)處 謝謝
該方案目前只支持單條數(shù)據(jù)的操作
需求
1.需要去記錄后臺(tái)管理系統(tǒng)的 一些增刪改敏感操作 能夠查詢到 操作詳情
以及每個(gè)字段的新舊值
- 不能去影響現(xiàn)有的業(yè)務(wù)代碼
實(shí)現(xiàn)思路
因?yàn)?不能去影響已經(jīng)編寫(xiě)好的業(yè)務(wù)代碼 那只能去通過(guò)中間件的方式去實(shí)現(xiàn) 而我又要去拿到 表名和操作對(duì)象ID
- 通過(guò)restful 機(jī)制 的method 去 區(qū)分增刪改 如 POST 為增加操作
PUT 為修改操作 DELETE 為刪除操作 - 通過(guò)gin 框架的 Param 機(jī)制 去獲取 操作對(duì)象的ID
- 通過(guò)gin handler 名 去 映射 表名
- 通過(guò)表名和操作對(duì)象ID 去獲取數(shù)據(jù)庫(kù)的comment
| 操作 | 說(shuō)明 |
|---|---|
| INSERT | 在INSERT后執(zhí)行 |
| UPDATE | 在UPDATE前后都要執(zhí)行，操作前獲取操作前數(shù)據(jù)蛔翅，操作后獲取操作后數(shù)據(jù) |
| DELETE | 在DELETE前執(zhí)行 |
實(shí)現(xiàn)環(huán)境
- DB:postgresql
- 框架: gin
- 語(yǔ)言: go
準(zhǔn)備工作
創(chuàng)建一張日志記錄表和測(cè)試業(yè)務(wù)表并添加comment
-- 日志記錄表
CREATE table log_operation(
id serial NOT NULL PRIMARY KEY,
operation_id VARCHAR NOT NULL DEFAULT '', -- 操作對(duì)象ID
operation_table VARCHAR NOT NULL DEFAULT '', -- 操作表
operation_type SMALLINT NOT NULL DEFAULT 0, -- 操作類(lèi)型 1:查詢 2:新增 3:編輯 4:刪除
operation_ip VARCHAR NOT NULL DEFAULT '', -- ip
comment VARCHAR NOT NULL DEFAULT '', -- 描述
request_info jsonb NOT NULL DEFAULT '{}', -- 請(qǐng)求信息
column_info jsonb NOT NULL DEFAULT '[]', -- 列變更信息
user_id INT NOT NULL DEFAULT 0, -- 用戶id
user_role VARCHAR NOT NULL DEFAULT '', -- 用戶角色
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- 添加時(shí)間
);
-- 測(cè)試業(yè)務(wù)表
CREATE table notice(
id serial NOT NULL PRIMARY KEY,
notice_name VARCHAR(100) NOT NULL DEFAULT '', -- 公告名
notice_content TEXT NOT NULL DEFAULT '', -- 公告內(nèi)容
notice_type SMALLINT NOT NULL DEFAULT 1, -- 公告類(lèi)型 1:用戶公告 2:代理公告
status SMALLINT NOT NULL DEFAULT 0, -- 公告狀態(tài) 0:關(guān)閉 1:開(kāi)啟
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- 添加時(shí)間
);
-- 給測(cè)試業(yè)務(wù)表添加comment
COMMENT ON TABLE notice IS '公告';
COMMENT ON COLUMN notice.notice_name IS '公告名';
COMMENT ON COLUMN notice.notice_content IS '公告內(nèi)容';
COMMENT ON COLUMN notice.notice_type IS '公告類(lèi)型';
COMMENT ON COLUMN notice.status IS '公告狀態(tài)';
COMMENT ON COLUMN notice.add_time IS '添加時(shí)間';
編寫(xiě)main.go
package main
import (
"github.com/gin-gonic/gin"
"database/sql"
_ "github.com/lib/pq"
"fmt"
)
var Db *sql.DB
func init() {
// 初始化數(shù)據(jù)庫(kù)
db, err := sql.Open("postgres", "host=127.0.0.1 port=5432 user=postgres password=123456 dbname=test sslmode=disable")
if err != nil {
fmt.Println(err)
}
Db = db
}
func main() {
r := gin.Default()
// handler 和 表名的映射
handleTableName := map[string]string{
"AddNotice": "notice",
"EditNotice": "notice",
"DelNotice": "notice",
}
r.POST("/notices/", AddNotice)
r.PUT("/notices/:pk/", EditNotice)
r.DELETE("/notices/:pk/", DelNotice)
r.Use(Operation(handleTableName, Db))
r.Run()
}
編寫(xiě)handles.go
package main
import (
"github.com/gin-gonic/gin"
"fmt"
)
func AddNotice(c *gin.Context) {
var Id int64
if err := Db.QueryRow("INSERT INTO notice(notice_name) VALUES($1) RETURNING id;", "測(cè)試增加").Scan(&Id); err != nil {
fmt.Println(err)
c.String(400, "server error")
return
}
// 由于 新增操作 需要插入數(shù)據(jù)庫(kù)后才能知道對(duì)象ID 在獲取對(duì)象ID 后 需要傳遞給中間件
c.Set("pk", Id)
c.String(200, "success")
}
func EditNotice(c *gin.Context) {
pk := c.Param("pk")
if _, err := Db.Exec("UPDATE notice set notice_name=$1 WHERE id=$2", "測(cè)試修改", pk); err != nil {
fmt.Println(err)
c.String(400, "server error")
return
}
c.String(200, "success")
}
func DelNotice(c *gin.Context) {
pk := c.Param("pk")
if _, err := Db.Exec("DELETE FORM notice WHERE id=$1", pk); err != nil {
fmt.Println(err)
c.String(400, "server error")
return
}
c.String(200, "success")
}
編寫(xiě)operation.go 中間件
package main
import (
"github.com/gin-gonic/gin"
"fmt"
"time"
"strings"
"log"
"encoding/json"
"net/http"
"io/ioutil"
"bytes"
"database/sql"
)
type operation struct {
DB *sql.DB
}
// Operation 操作日志中間件
// 1:查詢 2:新增 3:編輯 4:刪除
func Operation(handlerTableName map[string]string, DB *sql.DB) gin.HandlerFunc {
opt := &operation{
DB: DB,
}
return func(c *gin.Context) {
// 獲取當(dāng)前用戶ID 可通過(guò)jwt 中間件獲取
userId, ok := c.Get("userId")
if !ok {
userId = 0
}
handlerName := strings.Split(c.HandlerName(), ".")[1]
switch c.Request.Method {
case "PUT":
// Read the Body content
var bodyBytes []byte
if c.Request.Body != nil {
bodyBytes, _ = ioutil.ReadAll(c.Request.Body)
}
body := strings.Join(strings.Fields(string(bodyBytes)), "")
// Restore the io.ReadCloser to its original state
c.Request.Body = ioutil.NopCloser(bytes.NewBuffer(bodyBytes))
// 獲取修改對(duì)象ID
if c.Param("pk") != "" {
// 根據(jù)HandlerName 映射表名
if tableName, ok := handlerTableName[handlerName]; ok {
dataOld, err := opt.getData(fmt.Sprintf(`select * from %s where id=$1`, tableName), c.Param("pk"))
if err != nil && len(dataOld) > 0 {
log.Println(err)
} else {
c.Set("operation", map[string]interface{}{"tableName": tableName,
"pk": c.Param("pk"), "dataOld": dataOld[0]})
}
}
}
c.Next()
if c.Writer.Status() != 200 {
c.Abort()
return
}
// 獲取修改對(duì)象
operation, ok := c.Get("operation")
if !ok {
c.Abort()
return
}
item := operation.(map[string]interface{})
tableName := item["tableName"].(string)
dataNow, err := opt.getData(fmt.Sprintf(`select * from %s where id=$1`, tableName), item["pk"])
if err != nil {
log.Println(err)
c.Abort()
return
}
colComment := make(map[string]interface{})
var tbComment string
// 查詢表注解
if err := opt.getTbComment(tableName, &tbComment); err != nil {
log.Println(err)
c.Abort()
return
}
// 查詢列注解
if err := opt.getColComment(tableName, colComment); err != nil {
log.Println(err)
c.Abort()
return
}
colInfo := make([]map[string]interface{}, 0)
if len(dataNow) > 0 {
// 進(jìn)行對(duì)比
for k, v := range dataNow[0] {
oldValue := item["dataOld"].(map[string]interface{})[k]
if v != oldValue {
entry := make(map[string]interface{})
entry["col_name"] = k
entry["comment"] = colComment[k]
entry["old_value"] = oldValue
entry["new_value"] = v
colInfo = append(colInfo, entry)
}
}
colInfoJson, _ := json.Marshal(colInfo)
// 日志記錄
if err := opt.insertLog(item["pk"], userId, tableName, c.ClientIP(), tbComment,
opt.getRequestJson(c.Request, body), string(colInfoJson), 2); err != nil {
log.Println(err)
c.Abort()
return
}
}
case "POST":
// Read the Body content
var bodyBytes []byte
if c.Request.Body != nil {
bodyBytes, _ = ioutil.ReadAll(c.Request.Body)
}
body := strings.Join(strings.Fields(string(bodyBytes)), "")
// Restore the io.ReadCloser to its original state
c.Request.Body = ioutil.NopCloser(bytes.NewBuffer(bodyBytes))
c.Next()
if c.Writer.Status() != 200 {
c.Abort()
return
}
pk, ok := c.Get("pk")
if !ok {
c.Abort()
return
}
// 根據(jù)HandlerName 映射表名
tableName, ok := handlerTableName[handlerName]
if !ok {
c.Abort()
return
}
// 查詢對(duì)象
dataNow, err := opt.getData(fmt.Sprintf(`select * from %s where id=$1`, tableName), pk)
if err != nil {
log.Println(err)
c.Abort()
return
}
if len(dataNow) > 0 {
colComment := make(map[string]interface{})
var tbComment string
// 查詢表注解
if err := opt.getTbComment(tableName, &tbComment); err != nil {
log.Println(err)
c.Abort()
return
}
// 查詢列注解
if err := opt.getColComment(tableName, colComment); err != nil {
log.Println(err)
c.Abort()
return
}
colInfo := make([]map[string]interface{}, 0)
for k, v := range dataNow[0] {
entry := make(map[string]interface{})
entry["col_name"] = k
entry["comment"] = colComment[k]
entry["old_value"] = v
entry["new_value"] = ""
colInfo = append(colInfo, entry)
}
colInfoJson, _ := json.Marshal(colInfo)
// 日志記錄
if err := opt.insertLog(pk, userId, tableName, c.ClientIP(), tbComment,
opt.getRequestJson(c.Request, body), string(colInfoJson), 3); err != nil {
log.Println(err)
c.Abort()
return
}
}
case "DELETE":
if c.Param("pk") != "" {
// 根據(jù)HandlerName 映射表名
if tableName, ok := handlerTableName[handlerName]; ok {
dataOld, err := opt.getData(fmt.Sprintf(`select * from %s where id=$1`, tableName), c.Param("pk"))
if err != nil && len(dataOld) > 0 {
log.Println(err)
} else {
c.Set("operation", map[string]interface{}{"tableName": tableName,
"pk": c.Param("pk"), "dataOld": dataOld[0]})
}
}
}
c.Next()
if c.Writer.Status() != 200 {
c.Abort()
return
}
// 獲取修改對(duì)象
operation, ok := c.Get("operation")
if !ok {
c.Abort()
return
}
item := operation.(map[string]interface{})
tableName := item["tableName"].(string)
colComment := make(map[string]interface{})
var tbComment string
// 查詢表注解
if err := opt.getTbComment(tableName, &tbComment); err != nil {
log.Println(err)
c.Abort()
return
}
// 查詢列注解
if err := opt.getColComment(tableName, colComment); err != nil {
log.Println(err)
c.Abort()
return
}
colInfo := make([]map[string]interface{}, 0)
for k, v := range item["dataOld"].(map[string]interface{}) {
entry := make(map[string]interface{})
entry["col_name"] = k
entry["comment"] = colComment[k]
entry["old_value"] = v
entry["new_value"] = ""
colInfo = append(colInfo, entry)
}
colInfoJson, _ := json.Marshal(colInfo)
// 日志記錄
if err := opt.insertLog(item["pk"], userId, tableName, c.ClientIP(), tbComment,
opt.getRequestJson(c.Request, ""), string(colInfoJson), 4); err != nil {
log.Println(err)
c.Abort()
return
}
}
}
}
func (opt *operation) getData(sql string, args ...interface{}) ([]map[string]interface{}, error) {
data := make([]map[string]interface{}, 0)
rows, err := opt.DB.Query(sql, args...)
defer rows.Close()
if err != nil {
return nil, err
}
columns, err := rows.Columns()
if err != nil {
return nil, err
}
values := make([]interface{}, len(columns))
scanArgs := make([]interface{}, len(columns))
for i := range values {
scanArgs[i] = &values[i]
}
for rows.Next() {
if err := rows.Scan(scanArgs...); err != nil {
return nil, err
}
entry := make(map[string]interface{})
for i, col := range columns {
var v interface{}
val := values[i]
if b, ok := val.([]byte); ok {
v = string(b)
} else if t, ok := val.(time.Time); ok {
list := strings.Split(fmt.Sprintf("%v", t), " ")
dateTime := fmt.Sprintf("\"%s\"", t.Format("2006-01-02 15:04:05"))
if len(list) >= 2 && list[1] == "00:00:00" {
dateTime = fmt.Sprintf("\"%s\"", t.Format("2006-01-02"))
}
v = dateTime
} else {
v = val
}
entry[col] = v
}
data = append(data, entry)
}
return data, nil
}
// 查詢表注釋
func (opt *operation) getTbComment(tbName string, tbComment *string) error {
if err := opt.DB.QueryRow(fmt.Sprintf(`select
COALESCE(obj_description('%s'::regclass),'');`, tbName)).Scan(tbComment); err != nil {
return err
}
return nil
}
// 查詢字段注釋
func (opt *operation) getColComment(tbName string, colComment map[string]interface{}) error {
sql := `
SELECT
cols.column_name,
COALESCE((
SELECT
pg_catalog.col_description(c.oid, cols.ordinal_position::int)
FROM pg_catalog.pg_class c
WHERE
c.oid = (SELECT cols.table_name::regclass::oid) AND
c.relname = cols.table_name
),'')
as column_comment
FROM information_schema.columns cols
WHERE
cols.table_name='%s';
`
datas, err := opt.getData(fmt.Sprintf(sql, tbName))
if err != nil {
return err
}
for _, item := range datas {
colComment[fmt.Sprintf("%s", item["column_name"])] = item["column_comment"]
}
return nil
}
// 日志記錄
func (opt *operation) insertLog(operationId, userId interface{}, operationTable, operationIp,
comment, requestInfo, columnInfo string, operationType int) error {
if _, err := opt.DB.Exec(`insert into log_operation(operation_id,
operation_table,operation_type,operation_ip,comment,request_info,column_info,user_id)
values($1,$2,$3,$4,$5,$6,$7,$8);
`, operationId, operationTable, operationType, operationIp,
comment, requestInfo, columnInfo, userId); err != nil {
return err
}
return nil
}
// 獲取請(qǐng)求相關(guān)參數(shù)json
func (opt *operation) getRequestJson(req *http.Request, body string) string {
proto := "http"
if req.TLS != nil || req.Header.Get("X-Forwarded-Proto") == "https" {
proto = "https"
}
data := map[string]interface{}{
"Method": req.Method,
"Cookies": req.Header.Get("Cookie"),
"Query": req.URL.Query().Encode(),
"URL": proto + "://" + req.Host + req.URL.Path,
"Headers": make(map[string]string, len(req.Header)),
}
data["Host"] = req.Host
for k, v := range req.Header {
data["Headers"].(map[string]string)[k] = strings.Join(v, ",")
}
data["Body"] = body
jsonStr, _ := json.Marshal(data)
return string(jsonStr)
}
-
顯示log_operation 數(shù)據(jù)示例
image.png
image.png
