archery-dml放宽审批流程

与数据交流的路上

已于 2023-11-21 18:09:42 修改

阅读量895

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签：数据库

于 2021-09-07 18:41:46 首次发布

本文链接：https://blog.youkuaiyun.com/line_on_database/article/details/120155041

mysql 专栏收录该内容

58 篇文章

订阅专栏

本文档介绍了如何修改 Archery 系统以实现 DML 审核流程的简化，当 DML 工单影响行数小于特定阈值时，免去 DBA 审核，以此减轻 DBA 的工作负担。主要涉及 `workflow_audit.py` 文件的修改，增加针对 DML 类型的判断，并调整前端配置项，以允许配置是否开启 DML 审批分流及最大更新行数。此外，还修复了归档功能的 bug，确保语法类型在所有工单类型中都能正确引用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、背景

dml审核的时候只要研发leader通过即可，这样可以减少dba工作量

二、修改

修改archery代码

vim /sql/utils/workflow_audit.py

 def audit(audit_id, audit_status, audit_user, audit_remark):  
      # 获取工单信息
        if workflow_type == WorkflowDict.workflow_type['query']:
            # 添加的信息
            # syntax_type = workflow_detail.syntax_type，最开始这么写的，但是会导致某些功能bug
            syntax_type = 0
        elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
            # 添加的部分
            syntax_type = workflow_detail.syntax_type
        elif workflow_type == WorkflowDict.workflow_type['archive']:
            # 添加的部分
            syntax_type = workflow_detail.syntax_type
          

   
    else:
            # 向审核主表插入待审核数据
            audit_detail = WorkflowAudit()
            audit_detail.group_id = group_id
            audit_detail.group_name = group_name
            audit_detail.workflow_id = workflow_id
            audit_detail.workflow_type = workflow_type
            audit_detail.workflow_title = workflow_title
            audit_detail.workflow_remark = workflow_remark


            # 修改的部分
            # 根据是dml还是ddl，排除组
            # 如果是dml，排除dba组，这样dml的审核就不用dba了,这里的dba组指最后一个审核组，根据自己的设定来定
            # ((0, '其他'), (1, 'DDL'), (2, 'DML'))
            # 这是DDL直接由dba审核
            group_dba_id = Group.objects.get(name='dba').id
            if syntax_type == 1:
                audit_auth_groups_list = [str(group_dba_id)]
            elif syntax_type == 2:
                audit_auth_groups_list.remove(str(group_dba_id))
            # 也可以不变原来审核流程，只在DML的时候做特殊处理
            # if syntax_type == 2 and str(group_dba_id) in audit_auth_groups_list:
            #     audit_auth_groups_list.remove(str(group_dba_id))       
            audit_detail.audit_auth_groups = ','.join(audit_auth_groups_list)
            audit_detail.current_audit = audit_auth_groups_list[0]

            # 判断有无下级审核
            if len(audit_auth_groups_list) == 1:
                audit_detail.next_audit = '-1'
            else:
                audit_detail.next_audit = audit_auth_groups_list[1]

新版也可采用如下方式改动

import json
# 获取工单信息
        if workflow_type == WorkflowDict.workflow_type["query"]:
            workflow_detail = 
        elif workflow_type == WorkflowDict.workflow_type["sqlreview"]:
            workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
            workflow_title = workflow_detail.workflow_name
            group_id = workflow_detail.group_id
            group_name = workflow_detail.group_name
            create_user = workflow_detail.engineer
            create_user_display = workflow_detail.engineer_display
            audit_auth_groups = workflow_detail.audit_auth_groups
            workflow_remark = ""
            # 新加部分
	        syntax_type = workflow_detail.syntax_type
	        all_affected_rows = 0
	        review_content = workflow_detail.sqlworkflowcontent.review_content
	        for review_row in json.loads(review_content):
	            all_affected_rows += int(review_row["affected_rows"])   
	        # 新加完毕
        else:
            result["msg"] = "工单类型不存在"
            raise Exception(result["msg"])
            
		if audit_auth_groups == "":
            result["msg"] = "审批流程不能为空，请先配置审批流程"
            raise Exception(result["msg"])
        else:
            audit_auth_groups_list = audit_auth_groups.split(",")
            # 新加的部分
            if syntax_type == 2 and all_affected_rows < 100:
                group_dba_id = Group.objects.get(name='dba').id
                audit_auth_groups_list.remove(str(group_dba_id))
            # 新加完毕

三、重启

重启archery，即可

四、加到配置项里

现在让我们忘记前面所做的改动，然后重新开始

1.更改前端

更改文件：/Archery-master/common/templates/config.html



<!--新加部分-->
<div class="form-group">
                                <label for="dml_audit"
                                       class="col-sm-4 control-label">DML_AUDIT</label>
                                <div class="col-sm-8">
                                    <div class="switch switch-small">
                                        <label>
                                            <input id="dml_audit"
                                                   key="dml_audit"
                                                   value="{{ config.dml_audit }}"
                                                   type="checkbox">
                                            是否开启DML审批分流
                                        </label>
                                    </div>
                                </div>
                            </div>
                            <div id="div-dml-audit-config" style="display: none">
                            <div class="form-group">
                                <label for="auto_dml_max_update_rows"
                                     class="col-sm-4 control-label">MAX_UPDATE_ROWS</label>
                                <div class="col-sm-5">
                                     <input type="number" class="form-control"
                                         id="auto_dml_max_update_rows"
                                         key="auto_dml_max_update_rows"
                                         value="{{ config.auto_dml_max_update_rows }}"
                                         placeholder="自动审批允许工单最大更新行数">
                                 </div>
                                </div>
                            </div>
<!--新加完毕-->

  } else if (id === 'auto_review') {
  $("#div-auto-review-config").show();
  <!--新加部分-->
    } else if (id === 'dml_audit') {
        $("#div-dml-audit-config").show();
  <!--新加完毕-->
    } else if (id === 'wx') {
        $("#div-wx-config").show();
    } else if (id === 'feishu') {
        $("#div-feishu-config").show();
    } else if (id === 'data_masking') {
        $("#div-inception-config").show();
    }
} else {
    $(this).val(false);
    if (id === 'mail') {
        $("#mail_ssl").val(false);
        $("#div-mail-config").hide();
    } else if (id === 'ding_to_person') {
        $("#div-ding-config").hide();
    } else if (id === 'auto_review') {
        $("#div-auto-review-config").hide();
        <!--新加部分-->
    } else if (id === 'dml_audit') {
        $("#div-dml-audit-config").hide();
        <!--新加完毕-->

前端新加的效果
在这里插入图片描述

2.更改后端

文件：Archery-master/sql/utils/workflow_audit.py

import json
 # 获取工单信息
if workflow_type == WorkflowDict.workflow_type['query']:
    workflow_detail = QueryPrivilegesApply.objects.get(apply_id=workflow_id)
    workflow_title = workflow_detail.title
    # ...
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
    workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
    workflow_title = workflow_detail.workflow_name
    group_id = workflow_detail.group_id
    group_name = workflow_detail.group_name
    create_user = workflow_detail.engineer
    create_user_display = workflow_detail.engineer_display
    audit_auth_groups = workflow_detail.audit_auth_groups
    workflow_remark = ''
    # 新加部分
    syntax_type = workflow_detail.syntax_type
    all_affected_rows = 0
    review_content = workflow_detail.sqlworkflowcontent.review_content
    for review_row in json.loads(review_content):
        all_affected_rows += int(review_row["affected_rows"])
    # 新加完毕
elif workflow_type == WorkflowDict.workflow_type['archive']:
    workflow_detail = ArchiveConfig.objects.get(pk=workflow_id)
   
# 校验是否配置审批流程
if audit_auth_groups == '':
     result['msg'] = '审批流程不能为空，请先配置审批流程'
     raise Exception(result['msg'])
 else:
     audit_auth_groups_list = audit_auth_groups.split(',')
     # 新加部分
     if SysConfig().get("dml_audit"):
         dml_max_rows = int(SysConfig().get("auto_dml_max_update_rows"))
         if syntax_type == 2 and all_affected_rows < dml_max_rows:
             group_dba_id = Group.objects.get(name="DBA").id
             audit_auth_groups_list.remove(str(group_dba_id))
     # 新加完毕

3.bug更改

如上面的变动之后，再使用归档功能的时候会出现如下报错

[2023-11-21 16:58:08,129][Thread-148726:139746844022528][task_id:default][archiver.py:172][ERROR]- Traceback (most recent call last):
  File "/data/deploy/Archery-master/sql/archiver.py", line 170, in archive_apply
    audit_result = Audit.add(WorkflowDict.workflow_type['archive'], archive_id)
  File "/data/deploy/Archery-master/sql/utils/workflow_audit.py", line 73, in add
    if syntax_type == 2 and all_affected_rows < dml_max_rows:
UnboundLocalError: local variable 'syntax_type' referenced before assignment

这是因为获取syntax_type是在sql工单处，归档处并没有获取，所以需要在sql/utils/workflow_audit.py上给定一个syntax_type的初始值
更改：

class Audit(object):
    @staticmethod
    def add(workflow_type, workflow_id):
        result = {'status': 0, 'msg': '', 'data': []}

        workflow_info = WorkflowAudit.objects.filter(workflow_type=workflow_type, workflow_id=workflow_id,
                                                     current_status=WorkflowDict.workflow_status['audit_wait'])
        # 新加的值
        syntax_type = 0