作者：Jason
就职于捷信消费金融有限公司，担任 DBA 工作。先后从事过 Oracle 、Mongo 、MySQL 的 DBA ，以及大数据 ETL 的开发工作。对 NEWSQL 以及云原生分布式数据库具有浓厚的兴趣爱好。
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
SQLE 是由上海爱可生信息技术股份有限公司 开发并开源，支持SQL审核、索引优化、事前审核、事后审核、支持标准化上线流程、原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
官方主页 https://opensource.actionsky.com/sqle/
官方文档 Introduction · SQLE manual (actiontech.github.io)
(！！！事先声明：二次开发纯属于个人技术研究，不得进行任何商业盈利行为)
大家好！ 本次来分享的是如何开发一条自定义的review rule 的规则（基于MySQL 5.7的语法）。
在开发代码前，我们先从后台API调用的角度梳理一下审核SQL的具体流程：
我们可以先用 POSTMAN 从调用一遍代码审核这个方法：
首先，先确定规则验证的API接口：
http://10.25.15.83:10000/v1/tasks/audits
与API相对应的源代码方法的入口是：
api.controller.v1.task.go --> func CreateAndAuditTask(c echo.Context)
由于系统做了JWT的登录验证, 我们需要先模拟一下登录：
1）调用登录的API 10.25.15.83:10000/v1/login，获得token: ****************************
默认的是 admin/admin ,登录成功后，我们可拿到 token
2）我们调用一下规则验证的API接口： http://10.25.15.83:10000/v1/tasks/audits
我们准备了一下简单的SQL语句：
create table test (id int not null, name varchar(20));
这个语句不符合审计的规则如下：
1)无主键
2)无列和表级别的comment
3)无innodb engine 指定
我们首先设置参数： 可以参考request的结构体。
type CreateAuditTaskReqV1 struct {
InstanceName string `json:"instance_name" form:"instance_name" example:"inst_1" valid:"required"`
InstanceSchema string `json:"instance_schema" form:"instance_schema" example:"db1"`
Sql string `json:"sql" form:"sql" example:"alter table tb1 drop columns c1"`
}参数1： InstanceName -》 DBA
参数2： instance_schema =》 testdb
参数3： sql =》 create table test (id int not null, name varchar(20));
我们还需要设置一下 token: 这个token 从登录的API返回值可以获取到
下面我们尝试调用一下审计接口：http://10.25.15.83:10000/v1/tasks/audits
我们来看一下返回给我们的response的含义 :
{
"code": 0,
"message": "ok", --表示api调用成功
"data": {
"task_id": 1, --返回的审核结果保存在 task_is =1的任务中
"instance_name": "DBA", --实例名
"instance_schema": "testdb", --DB名
"audit_level": "error", --ERROR 级别的错误
"pass_rate": 0, --通过率为0
"status": "audited", --已经审计的状态
"sql_source": "form_data" --直接获得SQL语句的方式
}
}我们要想知道具体的审计返回的结果需要查询数据库： （“task_id”: 1, --返回的审核结果保存在 task_is =1的任务中）
mysql> select audit_result from execute_sql_detail where task_id=1\G
*************************** 1. row ***************************
audit_result: [notice]列建议添加注释
[error]表必须有主键
[notice]必须使用Innodb数据库引擎
[notice]表建议添加注释
1 row in set (0.00 sec)
从 response 的信息来看 是完全符合我们之前的预期的结果。
下面我们要代码的角度追踪一下这个审计的整个流程：
SQLE 采用的是类似数据库driver插件的开发模式，接口 driver.go 已经定义好了抽象的函数，具体的实现需要每一种具体的数据库来完成
例如 mysql.go, oracle.go
下面是如何添加一条新的rule 到审核平台的具体步骤：
New Rule的规则是:
我们想要在新建的TABLE 表中必须含有指定的列， 像是cdate cdate,creator,edate,editor 这几个审计字段。
首先找到对应的数据库里面rule的表是 rule:
mysql> select * from rules \G
*************************** 28. row ***************************
name: ddl_check_object_name_using_keyword
db_type: mysql
desc: 数据库对象命名禁止使用保留字
level: error
type: 命名规范
params: NULL
*************************** 29. row ***************************
name: ddl_check_pk_name
db_type: mysql
desc: 建议主键命名为"PK_表名"
level: notice
type: 命名规范
params: NULL
....
我们来手动插入一条新rule的记录：
mysql> INSERT INTO sqle.rules
-> (name, db_type, `desc`, `level`, `type`, params)
-> VALUES('ddl_check_audit_column', 'mysql', '建表语句需要包含4个审计列(cdate,creator,edate,editor)', 'notice', '命名规范', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
我们可以从页面看到这条新加的规则已经出现在了列表里面：
我们把这条新的规则追加到我们自定义的规则模板中：
最后一步，提交规则的模板信息。
下面我们从页面上测试一下我们新加的规则：（当然了，对应的规则要添加对应的后台代码，这个后面会有介绍~）
我们新建一个审核任务：
输入建表语句：
create table test (id int not null, name varchar(20));
点击审核按钮
我们可以看到我们自定义的审核规则已经生效：
最后我们来看看后台代码是如何开发实现这个规则的：
后台对应的这条规则的逻辑代码是：（入门级别的简单代码）这段代码加入到 sqle/driver/mysql/rule/rule.go中
用Map的结构体来判断，必要的列的名字是否存在review 语句的表中
//可以在linux下进行命令行的代码单元测试
// dlv test github.com/actiontech/sqle/sqle/driver/mysql -- -test.run ^TestCheckAuditColumn$
func checkAuditColumn(ctx *session.Context, rule driver.Rule, res *driver.AuditResult, node ast.Node) error {
var auditCols = [4]string{"cdate","edate","creator","editor"}
var set map[string]struct{}
set = make(map[string]struct{})
for _, value := range auditCols{
set[value] = struct{}{}
}
var cnt int = 0
switch stmt := node.(type) {
case *ast.CreateTableStmt:
for _,value := range stmt.Cols {
fmt.Println(value.Name.Name)
if _, ok := set[value.Name.Name.String()];ok {
cnt++
}
}
if cnt != 4{
addResult(res, rule, rule.Name)
}
}
return nil
}
同时我们添加rule的mapping 规则： sqle/driver/mysql/rule/rule.go中
{
Rule: driver.Rule{
Name: DDLCheckAuditColumn,
Desc: "建表语句必须包含审计列(cdate,creator,edate,editor)",
Level: driver.RuleLevelError,
Category: RuleTypeUsageSuggestion,
},
Message: "建表语句必须包含审计列(cdate,creator,edate,editor)",
AllowOffline: true,
Func: checkAuditColumn,
},添加常量定义： sqle/driver/mysql/rule/rule.go中
DDLCheckAuditColumn = "ddl_check_audit_column"
我们还可以在 sqle/driver/mysql/audit_offline_test.go 进行unit testing:
func TestCheckAuditColumn(t *testing.T) {
fmt.Println("start..............")
runSingleRuleInspectCase(rulepkg.RuleHandlerMap[rulepkg.DDLCheckAuditColumn].Rule, t,
"create table test (id int not null, name varchar(20)); ",
DefaultMysqlInspectOffline(),
`create table test (id int not null,
name varchar(20),
cdate datetime,
edate datetime,
creator1 varchar(20),
editor1 varchar(20));`,
newTestResult().addResult(rulepkg.DDLCheckAuditColumn),
)
}
最后测试通过后，我们即可提交代码，发布新的程序来从页面上验证我们自己定义的规则了。
总体来说，爱可生的代码是十分规范的，便于二次代码开发和维护。如果你是golang的开发爱好者，亦可作为学习项目的经典案例。
