def execute_call_back(workflow_id, instance_name, url):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
try:
# 交给inception先split，再执行
(finalStatus, finalList) = InceptionDao(instance_name=instance_name).executeFinal(workflow_detail)
# 封装成JSON格式存进数据库字段里
str_json_result = json.dumps(finalList)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
workflow_detail.execute_result = str_json_result
workflow_detail.finish_time = timezone.now()
workflow_detail.status = finalStatus
workflow_detail.is_manual = 0
workflow_detail.audit_remark = ''
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
except Exception:
logger.error(traceback.format_exc())
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(workflow_detail.status),
operator='',
operator_display='系统'
)
# 发送消息
send_msg(workflow_detail, url)
def queryprivaudit(request):
# 获取用户信息
user = request.user
result = {'status': 0, 'msg': 'ok', 'data': []}
apply_id = int(request.POST['apply_id'])
audit_status = int(request.POST['audit_status'])
audit_remark = request.POST.get('audit_remark')
if audit_remark is None:
audit_remark = ''
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 获取audit_id
audit_id = Workflow.auditinfobyworkflow_id(workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query']).audit_id
# 调用工作流接口审核
auditresult = workflowOb.auditworkflow(request, audit_id, audit_status, user.username, audit_remark)
# 按照审核结果更新业务表审核状态
auditInfo = Workflow.auditinfo(audit_id)
if auditInfo.workflow_type == WorkflowDict.workflow_type['query']:
# 更新业务表审核状态,插入权限信息
query_audit_call_back(auditInfo.workflow_id, auditresult['data']['workflow_status'])
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:queryapplydetail', args=(apply_id,)))
def detail(request, workflow_id):
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in (Const.workflowStatus['finish'], Const.workflowStatus['exception']) \
and workflow_detail.is_manual == 0:
rows = workflow_detail.execute_result
else:
rows = workflow_detail.review_content
list_content = json.loads(rows)
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != Const.workflowStatus['autoreviewwrong']:
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Workflow.can_review(request.user, workflow_id, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflow_id)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflow_id)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflow_id)
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
# 获取定时执行任务信息
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = job_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# sql结果
column_list = [
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL',
'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
context = {
'workflow_detail': workflow_detail,
'column_list': column_list,
'rows': rows,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def queryapplydetail(request, apply_id):
workflowDetail = QueryPrivilegesApply.objects.get(apply_id=apply_id)
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(apply_id, 1)
# 是否可审核
is_can_review = Workflow.can_review(request.user, apply_id, 1)
context = {'workflowDetail': workflowDetail, 'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group, 'is_can_review': is_can_review}
return render(request, 'queryapplydetail.html', context)
def timingtask(request):
workflow_id = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
url = get_detail_url(request, workflow_id)
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = Const.workflowStatus['timingtask']
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id, url)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type[
'sqlreview']).audit_id
Workflow().add_workflow_log(audit_id=audit_id,
operation_type=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display
)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
def execute_job(workflow_id, url):
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
logger.debug('execute_job:' + job_id + ' start')
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
# 服务器端二次验证，当前工单状态必须为定时执行过状态
if workflow_detail.status != Const.workflowStatus['timingtask']:
raise Exception('工单不是定时执行状态')
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['executing']
workflow_detail.reviewok_time = timezone.now()
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
logger.debug('execute_job:' + job_id + ' executing')
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
split_review_result = InceptionDao(
instance_name=instance_name).sqlautoReview(workflow_detail.sql_content,
db_name,
isSplit='yes')
workflow_detail.review_content = json.dumps(split_review_result)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflow_id, instance_name, url))
t.start()
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
def workflowsdetail(request, audit_id):
# 按照不同的workflow_type返回不同的详情
auditInfo = Workflow.auditinfo(audit_id)
if auditInfo.workflow_type == WorkflowDict.workflow_type['query']:
return HttpResponseRedirect(reverse('sql:queryapplydetail', args=(auditInfo.workflow_id,)))
elif auditInfo.workflow_type == WorkflowDict.workflow_type['sqlreview']:
return HttpResponseRedirect(reverse('sql:detail', args=(auditInfo.workflow_id,)))
def auditors(request):
group_name = request.POST.get('group_name')
workflow_type = request.POST['workflow_type']
result = {'status': 0, 'msg': 'ok', 'data': {'auditors': '', 'auditors_display': ''}}
if group_name:
group_id = SqlGroup.objects.get(group_name=group_name).group_id
audit_auth_groups = Workflow.audit_settings(group_id=group_id, workflow_type=workflow_type)
else:
result['status'] = 1
result['msg'] = '参数错误'
return HttpResponse(json.dumps(result), content_type='application/json')
# 获取权限组名称
if audit_auth_groups:
# 校验配置
for auth_group_id in audit_auth_groups.split(','):
try:
Group.objects.get(id=auth_group_id)
except Exception:
result['status'] = 1
result['msg'] = '审批流程权限组不存在，请重新配置！'
return HttpResponse(json.dumps(result), content_type='application/json')
audit_auth_groups_name = '->'.join(
[Group.objects.get(id=auth_group_id).name for auth_group_id in audit_auth_groups.split(',')])
result['data']['auditors'] = audit_auth_groups
result['data']['auditors_display'] = audit_auth_groups_name
return HttpResponse(json.dumps(result), content_type='application/json')
def changeauditors(request):
auth_groups = request.POST.get('audit_auth_groups')
group_name = request.POST.get('group_name')
workflow_type = request.POST.get('workflow_type')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 调用工作流修改审核配置
group_id = SqlGroup.objects.get(group_name=group_name).group_id
audit_auth_groups = [str(Group.objects.get(name=auth_group).id) for auth_group in auth_groups.split(',')]
try:
Workflow.changesettings(group_id, workflow_type, ','.join(audit_auth_groups))
except Exception as msg:
result['msg'] = str(msg)
result['status'] = 1
# 返回结果
return HttpResponse(json.dumps(result), content_type='application/json')
def execute_skipinc_call_back(workflowId, instance_name, db_name, sql_content,
url):
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
try:
# 执行sql
t_start = time.time()
execute_result = Dao(instance_name=instance_name).mysql_execute(
db_name, sql_content)
t_end = time.time()
execute_time = "%5s" % "{:.4f}".format(t_end - t_start)
execute_result['execute_time'] = execute_time + 'sec'
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
if execute_result.get('Warning'):
workflowDetail.status = Const.workflowStatus['exception']
elif execute_result.get('Error'):
workflowDetail.status = Const.workflowStatus['exception']
else:
workflowDetail.status = Const.workflowStatus['finish']
workflowDetail.finish_time = timezone.now()
workflowDetail.execute_result = json.dumps(execute_result)
workflowDetail.is_manual = 1
workflowDetail.audit_remark = ''
workflowDetail.is_backup = '否'
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception:
logger.error(traceback.format_exc())
# 增加工单日志
# 获取audit_id
audit_id = Workflow.auditinfobyworkflow_id(
workflow_id=workflowId,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflowDetail.status),
operator='',
operator_display='系统')
# 发送消息
send_msg(workflowDetail, url)
def cancel(request):
workflowId = request.POST['workflowid']
if workflowId == '' or workflowId is None:
context = {'errMsg': 'workflowId参数为空.'}
return render(request, 'error.html', context)
workflowId = int(workflowId)
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
audit_remark = request.POST.get('audit_remark')
if audit_remark is None:
context = {'errMsg': '终止原因不能为空'}
return render(request, 'error.html', context)
user = request.user
if can_cancel(request.user, workflowId) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口取消或者驳回
# 获取audit_id
audit_id = Workflow.auditinfobyworkflow_id(
workflow_id=workflowId,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflowDetail.status != Const.workflowStatus['manreviewing']:
pass
else:
if user.username == workflowDetail.engineer:
workflowOb.auditworkflow(
request, audit_id,
WorkflowDict.workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
workflowOb.auditworkflow(
request, audit_id,
WorkflowDict.workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise PermissionDenied
# 删除定时执行job
if workflowDetail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflowId)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
workflowDetail.status = Const.workflowStatus['abort']
workflowDetail.audit_remark = audit_remark
workflowDetail.save()
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def queryprivaudit(request):
# 获取用户信息
user = request.user
apply_id = int(request.POST['apply_id'])
audit_status = int(request.POST['audit_status'])
audit_remark = request.POST.get('audit_remark')
if audit_remark is None:
audit_remark = ''
if Workflow.can_review(request.user, apply_id, 1) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query']).audit_id
# 调用工作流接口审核
audit_result = workflowOb.auditworkflow(request, audit_id,
audit_status,
user.username,
audit_remark)
# 按照审核结果更新业务表审核状态
audit_detail = Workflow.audit_detail(audit_id)
if audit_detail.workflow_type == WorkflowDict.workflow_type[
'query']:
# 更新业务表审核状态,插入权限信息
query_audit_call_back(audit_detail.workflow_id,
audit_result['data']['workflow_status'])
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(apply_id, )))
def passed(request):
workflow_id = request.POST['workflow_id']
if workflow_id == '' or workflow_id is None:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_id = int(workflow_id)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
audit_remark = request.POST.get('audit_remark', '')
user = request.user
if Workflow.can_review(request.user, workflow_id, 2) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
audit_result = workflowOb.auditworkflow(
request, audit_id,
WorkflowDict.workflow_status['audit_success'], user.username,
audit_remark)
# 按照审核结果更新业务表审核状态
if audit_result['data'][
'workflow_status'] == WorkflowDict.workflow_status[
'audit_success']:
# 将流程状态修改为审核通过，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['pass']
workflow_detail.reviewok_time = timezone.now()
workflow_detail.audit_remark = audit_remark
workflow_detail.save()
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def can_cancel(user, workflow_id):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
result = False
# 审核中的工单，审核人和提交人可终止
if workflow_detail.status == Const.workflowStatus['manreviewing']:
from sql.utils.workflow import Workflow
if Workflow.can_review(user, workflow_id, 2) or user.username == workflow_detail.engineer:
result = True
# 审核通过但未执行的工单，执行人可以打回
if workflow_detail.status in [Const.workflowStatus['pass'], Const.workflowStatus['timingtask']]:
result = True if can_execute(user, workflow_id) else False
return result
def can_cancel(user, workflow_id):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
result = False
# 结束的工单不可终止
if workflow_detail.status == Const.workflowStatus['manreviewing']:
from sql.utils.workflow import Workflow
if Workflow.can_review(user, workflow_id, 2) or user.username == workflow_detail.engineer:
result = True
elif workflow_detail.status in [Const.workflowStatus['pass'], Const.workflowStatus['timingtask']]:
# 当前登录用户必须为有审核权限的组内用户
group_ids = [group.group_id for group in user_groups(user)]
if workflow_detail.group_id in group_ids and user.has_perm('sql.sql_review'):
result = True
return result
def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
workflow_id = task.args[0]
workflow = SqlWorkflow.objects.get(id=workflow_id)
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回字符串
workflow.status = Const.workflowStatus['exception']
elif task.result.warning or task.result.error:
workflow.status = Const.workflowStatus['exception']
execute_result = task.result
else:
workflow.status = Const.workflowStatus['finish']
execute_result = task.result
workflow.execute_result = execute_result.json()
workflow.audit_remark = ''
workflow.save()
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(workflow.status),
operator='',
operator_display='系统'
)
# 发送消息
send_msg(workflow)
def global_info(request):
"""存放用户，菜单信息等."""
user = request.user
if user:
# 获取待办数量
try:
todo = Workflow().audit_todo(user)
except Exception:
todo = 0
else:
todo = 0
return {
'todo': todo,
}
def global_info(request):
"""存放用户，菜单信息等."""
user = request.user
if user:
# 获取待办数量
try:
todo = Workflow().auditlist(user, 0, 0, 1)['data']['auditlistCount']
except Exception:
todo = 0
else:
todo = 0
return {
'todo': todo,
}
def send_msg(workflowDetail, url):
mailSender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(workflowDetail.id, 2)
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'], workflowDetail.status,
workflowDetail.id)
msg_content = '''发起人：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflowDetail.engineer_display, audit_auth_group, workflowDetail.workflow_name, url,
workflowDetail.sql_content[0:500])
if sys_config.get('mail') == 'true':
# 邮件通知申请人，审核人，抄送DBA
notify_users = workflowDetail.review_man.split(',')
notify_users.append(workflowDetail.engineer)
listToAddr = [email['email'] for email in Users.objects.filter(username__in=notify_users).values('email')]
listCcAddr = [email['email'] for email in
auth_group_users(auth_group_names=['DBA'], group_id=workflowDetail.group_id).values('email')]
mailSender.send_email(msg_title, msg_content, listToAddr, listCcAddr=listCcAddr)
if sys_config.get('ding') == 'true':
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = SqlGroup.objects.get(group_id=workflowDetail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
def getDetailUrl(request, workflow_id):
scheme = request.scheme
host = request.META['HTTP_HOST']
from sql.utils.workflow import Workflow
audit_id = Workflow.auditinfobyworkflow_id(workflow_id, 2).audit_id
return "{}://{}/workflow/{}/".format(scheme, host, audit_id)
def autoreview(request):
workflow_id = request.POST.get('workflow_id')
sql_content = request.POST['sql_content']
workflow_title = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = SqlGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST['instance_name']
db_name = request.POST.get('db_name')
is_backup = request.POST['is_backup']
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, 'master').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sql_content = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
if sql_content[-1] != ";":
context = {'errMsg': "SQL语句结尾没有以;结尾，请后退重新修改并提交！"}
return render(request, 'error.html', context)
# 交给inception进行自动审核
try:
inception_result = InceptionDao(
instance_name=instance_name).sqlauto_review(sql_content, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if inception_result is None or len(inception_result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
json_result = json.dumps(inception_result)
# 遍历result，看是否有任何自动审核不通过的地方，并且按配置确定是标记审核不通过还是放行，放行的可以在工单内跳过inception直接执行
sys_config = SysConfig().sys_config
is_manual = 0
workflow_status = Const.workflowStatus['manreviewing']
for row in inception_result:
# 1表示警告，不影响执行
if row[2] == 1 and sys_config.get('auto_review_wrong', '') == '1':
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 2表示严重错误，或者inception不支持的语法，标记手工执行，可以跳过inception直接执行
elif row[2] == 2:
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
elif re.match(r"\w*comments\w*", row[4]):
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for row in sql_content.strip(';').split(';'):
if re.match(r"^alter|^create|^drop|^truncate|^rename",
row.strip().lower()):
sql_syntax = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.user.username
if not workflow_id:
sql_workflow = SqlWorkflow()
sql_workflow.create_time = timezone.now()
else:
sql_workflow = SqlWorkflow.objects.get(id=int(workflow_id))
sql_workflow.workflow_name = workflow_title
sql_workflow.group_id = group_id
sql_workflow.group_name = group_name
sql_workflow.engineer = engineer
sql_workflow.engineer_display = request.user.display
sql_workflow.audit_auth_groups = Workflow.audit_settings(
group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflow_status
sql_workflow.is_backup = is_backup
sql_workflow.review_content = json_result
sql_workflow.instance_name = instance_name
sql_workflow.db_name = db_name
sql_workflow.sql_content = sql_content
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
list_cc_addr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
workflowOb.addworkflowaudit(
request,
WorkflowDict.workflow_type['sqlreview'],
workflow_id,
list_cc_addr=list_cc_addr)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
from common.config import SysConfig
from common.utils.const import Const, WorkflowDict
from common.utils.extend_json_encoder import ExtendJSONEncoder
from sql.models import SqlGroup, Users
from sql.utils.execute_sql import execute_call_back, execute_skipinc_call_back
from sql.utils.group import user_groups, user_instances
from sql.utils.inception import InceptionDao
from sql.utils.jobs import add_sqlcronjob, del_sqlcronjob
from sql.utils.sql_review import can_timingtask, get_detail_url, can_cancel, can_execute
from sql.utils.workflow import Workflow
from .models import SqlWorkflow
logger = logging.getLogger('default')
sqlSHA1_cache = {
} # 存储SQL文本与SHA1值的对应关系，尽量减少与数据库的交互次数,提高效率。格式: {工单ID1:{SQL内容1:sqlSHA1值1, SQL内容2:sqlSHA1值2},}
workflowOb = Workflow()
# 获取审核列表
@permission_required('sql.menu_sqlworkflow', raise_exception=True)
def sqlworkflow_list(request):
limit = int(request.POST.get('limit'))
offset = int(request.POST.get('offset'))
limit = offset + limit
search = request.POST.get('search', '')
# 获取筛选参数
navStatus = request.POST.get('navStatus')
# 管理员可以看到全部工单，其他人能看到自己提交和审核的工单
user = request.user
def send_msg(workflowDetail, url):
mailSender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflowDetail.id, 2)
audit_id = Workflow.auditinfobyworkflow_id(workflowDetail.id, 2).audit_id
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'], workflowDetail.status,
audit_id)
msg_content = '''发起人：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflowDetail.engineer_display, audit_auth_group,
workflowDetail.workflow_name, url,
re.sub('[\r\n\f]{2,}', '\n',
workflowDetail.sql_content[0:500].replace('\r', '')))
if sys_config.get('mail'):
# 邮件通知申请人，审核人，抄送DBA
notify_users = workflowDetail.audit_auth_groups.split(',')
notify_users.append(workflowDetail.engineer)
listToAddr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
listCcAddr = [
email['email'] for email in
auth_group_users(auth_group_names=['DBA'],
group_id=workflowDetail.group_id).values('email')
]
mailSender.send_email(msg_title,
msg_content,
listToAddr,
listCcAddr=listCcAddr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = SqlGroup.objects.get(
group_id=workflowDetail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group', None) \
and workflowDetail.status == '已正常结束':
# 判断上线语句是否存在DDL，存在则通知相关人员
sql_content = workflowDetail.sql_content
# 删除注释语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
# 匹配DDL语句CREATE、ALTER（排除索引变更）、DROP、TRUNCATE、RENAME
send = 0
for row in sql_content.strip(';').split(';'):
# alter语法
if re.match(
r"^alter\s+table\s+\S+\s+(add|alter|change|drop|rename|modify)\s+(?!.*(index|key|unique))",
row.strip().lower()):
send = 1
break
# create语法
elif re.match(r"^create\s+(temporary\s+)?(database|schema|table)",
row.strip().lower()):
send = 1
break
# drop语法
elif re.match(r"^drop", row.strip().lower()):
send = 1
break
# rename语法
elif re.match(r"^rename", row.strip().lower()):
send = 1
break
# truncate语法
elif re.match(r"^truncate", row.strip().lower()):
send = 1
break
if send == 1:
# 消息内容通知
msg_title = '[Archer]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflowDetail.engineer).display,
workflowDetail.group_name, workflowDetail.instance_name,
workflowDetail.db_name, workflowDetail.workflow_name, url,
workflowDetail.sql_content[0:500])
# 获取通知成员
msg_to = [
email['email']
for email in Users.objects.filter(groups__name=sys_config.get(
'ddl_notify_auth_group')).values('email')
]
# 发送
mailSender.send_email(msg_title, msg_content, msg_to)
def detail(request, workflowId):
workflowDetail = get_object_or_404(SqlWorkflow, pk=workflowId)
if workflowDetail.status in (Const.workflowStatus['finish'], Const.workflowStatus['exception']) \
and workflowDetail.is_manual == 0:
listContent = json.loads(workflowDetail.execute_result)
else:
listContent = json.loads(workflowDetail.review_content)
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Workflow.review_info(
workflowId, 2)
# 是否可审核
is_can_review = Workflow.can_review(request.user, workflowId, 2)
# 是否可执行
is_can_execute = can_execute(request.user, workflowId)
# 是否可定时执行
is_can_timingtask = can_timingtask(request.user, workflowId)
# 是否可取消
is_can_cancel = can_cancel(request.user, workflowId)
# 获取定时执行任务信息
if workflowDetail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
job = job_info(job_id)
if job:
run_date = job.next_run_time
else:
run_date = ''
else:
run_date = ''
# sql结果
column_list = [
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL',
'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
rows = []
for row_index, row_item in enumerate(listContent):
row = {}
row['ID'] = row_index + 1
row['stage'] = row_item[1]
row['errlevel'] = row_item[2]
row['stagestatus'] = row_item[3]
row['errormessage'] = row_item[4]
row['SQL'] = row_item[5]
row['Affected_rows'] = row_item[6]
row['sequence'] = row_item[7]
row['backup_dbname'] = row_item[8]
row['execute_time'] = row_item[9]
# row['sqlsha1'] = row_item[10]
rows.append(row)
if workflowDetail.status == '执行中':
row['stagestatus'] = ''.join([
"<div id=\"td_" + str(row['ID']) + "\" class=\"form-inline\">",
" <div class=\"progress form-group\" style=\"width: 80%; height: 18px; float: left;\">",
" <div id=\"div_" + str(row['ID']) +
"\" class=\"progress-bar\" role=\"progressbar\"",
" aria-valuenow=\"60\"",
" aria-valuemin=\"0\" aria-valuemax=\"100\">",
" <span id=\"span_" + str(row['ID']) + "\"></span>",
" </div>", " </div>",
" <div class=\"form-group\" style=\"width: 10%; height: 18px; float: right;\">",
" <form method=\"post\">",
" <input type=\"hidden\" name=\"workflowid\" value=\""
+ str(workflowDetail.id) + "\">",
" <button id=\"btnstop_" + str(row['ID']) +
"\" value=\"" + str(row['ID']) + "\"",
" type=\"button\" class=\"close\" style=\"display: none\" title=\"停止pt-OSC进程\">",
" <span class=\"glyphicons glyphicons-stop\">×</span>",
" </button>", " </form>", " </div>", "</div>"
])
context = {
'workflowDetail': workflowDetail,
'column_list': column_list,
'rows': rows,
'is_can_review': is_can_review,
'is_can_execute': is_can_execute,
'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel,
'audit_auth_group': audit_auth_group,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
def applyforprivileges(request):
title = request.POST['title']
cluster_name = request.POST['cluster_name']
group_name = request.POST['group_name']
group_id = SqlGroup.objects.get(group_name=group_name).group_id
priv_type = request.POST['priv_type']
db_name = request.POST['db_name']
valid_date = request.POST['valid_date']
limit_num = request.POST['limit_num']
try:
workflow_remark = request.POST['apply_remark']
except Exception:
workflow_remark = ''
# 获取用户信息
user = request.user
# 服务端参数校验
result = {'status': 0, 'msg': 'ok', 'data': []}
if int(priv_type) == 1:
db_list = request.POST['db_list']
if title is None or cluster_name is None or db_list is None or valid_date is None or limit_num is None:
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
elif int(priv_type) == 2:
table_list = request.POST['table_list']
if title is None or cluster_name is None or db_name is None or valid_date is None or table_list is None or limit_num is None:
result['status'] = 1
result['msg'] = '请填写完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_slaves(request.user).get(cluster_name=cluster_name)
except Exception:
context = {'errMsg': '你所在组未关联该从库！'}
return render(request, 'error.html', context)
# 判断是否需要限制到表级别的权限
# 库权限
if int(priv_type) == 1:
db_list = db_list.split(',')
# 检查申请账号是否已拥整个库的查询权限
own_dbs = QueryPrivileges.objects.filter(
cluster_name=cluster_name,
user_name=user.username,
db_name__in=db_list,
valid_date__gte=datetime.datetime.now(),
priv_type=1,
is_deleted=0).values('db_name')
own_db_list = [table_info['db_name'] for table_info in own_dbs]
if own_db_list is None:
pass
else:
for db_name in db_list:
if db_name in own_db_list:
result['status'] = 1
result[
'msg'] = '你已拥有' + cluster_name + '实例' + db_name + '库的全部查询权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 表权限
elif int(priv_type) == 2:
table_list = table_list.split(',')
# 检查申请账号是否已拥有该表的查询权限
own_tables = QueryPrivileges.objects.filter(
cluster_name=cluster_name,
user_name=user.username,
db_name=db_name,
table_name__in=table_list,
valid_date__gte=datetime.datetime.now(),
priv_type=2,
is_deleted=0).values('table_name')
own_table_list = [
table_info['table_name'] for table_info in own_tables
]
if own_table_list is None:
pass
else:
for table_name in table_list:
if table_name in own_table_list:
result['status'] = 1
result[
'msg'] = '你已拥有' + cluster_name + '实例' + db_name + '.' + table_name + '表的查询权限，不能重复申请'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 保存申请信息到数据库
applyinfo = QueryPrivilegesApply()
applyinfo.title = title
applyinfo.group_id = group_id
applyinfo.group_name = group_name
applyinfo.audit_users = Workflow.auditsettings(
group_id, WorkflowDict.workflow_type['query'])
applyinfo.user_name = user.username
applyinfo.user_display = user.display
applyinfo.cluster_name = cluster_name
if int(priv_type) == 1:
applyinfo.db_list = ','.join(db_list)
applyinfo.table_list = ''
elif int(priv_type) == 2:
applyinfo.db_list = db_name
applyinfo.table_list = ','.join(table_list)
applyinfo.priv_type = int(priv_type)
applyinfo.valid_date = valid_date
applyinfo.status = WorkflowDict.workflow_status[
'audit_wait'] # 待审核
applyinfo.limit_num = limit_num
applyinfo.create_user = user.username
applyinfo.save()
apply_id = applyinfo.apply_id
# 调用工作流插入审核信息,查询权限申请workflow_type=1
auditresult = workflowOb.addworkflowaudit(
request, WorkflowDict.workflow_type['query'], apply_id)
if auditresult['status'] == 0:
# 更新业务表审核状态,判断是否插入权限信息
query_audit_call_back(apply_id,
auditresult['data']['workflow_status'])
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
else:
result = auditresult
return HttpResponse(json.dumps(result), content_type='application/json')
def execute(request):
workflow_id = request.POST['workflow_id']
if workflow_id == '' or workflow_id is None:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_id = int(workflow_id)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
url = get_detail_url(request, workflow_id)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['executing']
workflow_detail.reviewok_time = timezone.now()
workflow_detail.save()
# 判断是通过inception执行还是直接执行，is_manual=0则通过inception执行，is_manual=1代表inception审核不通过，需要直接执行
if workflow_detail.is_manual == 0:
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
split_review_result = InceptionDao(
instance_name=instance_name).sqlauto_review(
workflow_detail.sql_content, db_name, is_split='yes')
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflow_detail.review_content = json.dumps(split_review_result)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflow_id, instance_name, url))
t.start()
else:
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_skipinc_call_back,
args=(workflow_id, instance_name, db_name,
workflow_detail.sql_content, url))
t.start()
# 删除定时执行job
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
del_sqlcronjob(job_id)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
workflowOb.add_workflow_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def send_msg(workflow_detail):
mail_sender = MailSender()
sys_config = SysConfig().sys_config
# 获取当前审批和审批流程
BASE_URL = sys_config.get('archery_base_url','http://127.0.0.1:8000')
BASE_URL = BASE_URL.rstrip('/') # 防止填写类似 http://127.0.0.1:8000/ 的地址
url = '{0}/detail/{1}/'.format(BASE_URL, workflow_detail.id)
audit_auth_group, current_audit_auth_group = Workflow.review_info(workflow_detail.id, 2)
audit_id = Workflow.audit_info_by_workflow_id(workflow_detail.id, 2).audit_id
# 如果执行完毕了，则根据配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'], workflow_detail.status, audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_detail.engineer_display, workflow_detail.group_name, audit_auth_group, workflow_detail.workflow_name,
url,
re.sub('[\r\n\f]{2,}', '\n', workflow_detail.sql_content[0:500].replace('\r', '')))
if sys_config.get('mail'):
# 邮件通知申请人，抄送DBA
list_to_addr = [email['email'] for email in
Users.objects.filter(username=workflow_detail.engineer).values('email')]
list_cc_addr = [email['email'] for email in
auth_group_users(auth_group_names=['DBA'], group_id=workflow_detail.group_id).values('email')]
logger.debug('发送执行结果通知，消息audit_id={}'.format(audit_id))
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title, list_to_addr + list_cc_addr, msg_content))
mail_sender.send_email(msg_title, msg_content, list_to_addr, list_cc_addr=list_cc_addr)
if sys_config.get('ding'):
# 钉钉通知申请人，审核人，抄送DBA
webhook_url = ResourceGroup.objects.get(group_id=workflow_detail.group_id).ding_webhook
MailSender.send_ding(webhook_url, msg_title + '\n' + msg_content)
if sys_config.get('mail') and sys_config.get('ddl_notify_auth_group', None) \
and workflow_detail.status == '已正常结束':
# 判断上线语句是否存在DDL，存在则通知相关人员
sql_content = workflow_detail.sql_content
# 删除注释语句
sql_content = ''.join(
map(lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
sql_content.splitlines(1))).strip()
# 去除空行
sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
# 匹配DDL语句CREATE、ALTER（排除索引变更）、DROP、TRUNCATE、RENAME
send = 0
for statement in sqlparse.split(sql_content):
# alter语法
if re.match(r"^alter\s+table\s+\S+\s+(add|alter|change|drop|rename|modify)\s+(?!.*(index|key|unique))",
statement.strip().lower()):
send = 1
break
# create语法
elif re.match(r"^create\s+(temporary\s+)?(database|schema|table)", statement.strip().lower()):
send = 1
break
# drop语法
elif re.match(r"^drop|^rename|^truncate", statement.strip().lower()):
send = 1
break
# rename语法
elif re.match(r"", statement.strip().lower()):
send = 1
break
# truncate语法
elif re.match(r"", statement.strip().lower()):
send = 1
break
if send == 1:
# 消息内容通知
msg_title = '[archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow_detail.engineer).display,
workflow_detail.group_name,
workflow_detail.instance_name,
workflow_detail.db_name,
workflow_detail.workflow_name,
url,
workflow_detail.sql_content[0:500])
# 获取通知成员
msg_to = [email['email'] for email in
Users.objects.filter(groups__name=sys_config.get('ddl_notify_auth_group')).values('email')]
# 发送
logger.debug('发送DDL通知，消息audit_id={}'.format(audit_id))
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title, msg_to, msg_content))
mail_sender.send_email(msg_title, msg_content, msg_to)
def autoreview(request):
workflowid = request.POST.get('workflowid')
sqlContent = request.POST['sql_content']
workflowName = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = SqlGroup.objects.get(group_name=group_name).group_id
clusterName = request.POST['cluster_name']
db_name = request.POST.get('db_name')
isBackup = request.POST['is_backup']
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sqlContent is None or workflowName is None or clusterName is None or db_name is None or isBackup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
GroupRelations.objects.get(group_name=group_name,
object_name=clusterName,
object_type=2)
except Exception:
context = {'errMsg': '该组不存在所选主库！'}
return render(request, 'error.html', context)
try:
user_masters(request.user).get(cluster_name=clusterName)
except Exception:
context = {'errMsg': '你所在组未关联该主库！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sqlContent = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sqlContent.splitlines(1))).strip()
# # 去除空行
# sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent)
sqlContent = sqlContent.strip()
if sqlContent[-1] != ";":
context = {'errMsg': "SQL语句结尾没有以;结尾，请后退重新修改并提交！"}
return render(request, 'error.html', context)
# 交给inception进行自动审核
try:
result = InceptionDao().sqlautoReview(sqlContent, clusterName, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if result is None or len(result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
jsonResult = json.dumps(result)
# 遍历result，看是否有任何自动审核不通过的地方，一旦有，则需要设置is_manual = 0，跳过inception直接执行
workflowStatus = Const.workflowStatus['manreviewing']
# inception审核不通过的工单，标记手动执行标签
is_manual = 0
for row in result:
if row[2] == 2:
is_manual = 1
break
elif re.match(r"\w*comments\w*", row[4]):
is_manual = 1
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for row in sqlContent.strip(';').split(';'):
if re.match(r"^alter|^create|^drop|^truncate|^rename",
row.strip().lower()):
sql_syntax = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.user.username
if not workflowid:
sql_workflow = SqlWorkflow()
sql_workflow.create_time = timezone.now()
else:
sql_workflow = SqlWorkflow.objects.get(id=int(workflowid))
sql_workflow.workflow_name = workflowName
sql_workflow.group_id = group_id
sql_workflow.group_name = group_name
sql_workflow.engineer = engineer
sql_workflow.engineer_display = request.user.display
sql_workflow.review_man = Workflow.auditsettings(
group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflowStatus
sql_workflow.is_backup = isBackup
sql_workflow.review_content = jsonResult
sql_workflow.cluster_name = clusterName
sql_workflow.db_name = db_name
sql_workflow.sql_content = sqlContent
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflowId = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflowStatus == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
listCcAddr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
workflowOb.addworkflowaudit(
request,
WorkflowDict.workflow_type['sqlreview'],
workflowId,
listCcAddr=listCcAddr)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def cancel(request):
workflow_id = request.POST['workflow_id']
if workflow_id == '' or workflow_id is None:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_id = int(workflow_id)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
audit_remark = request.POST.get('cancel_remark')
if audit_remark is None:
context = {'errMsg': '终止原因不能为空'}
return render(request, 'error.html', context)
user = request.user
if can_cancel(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口取消或者驳回
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflow_detail.status != Const.workflowStatus['manreviewing']:
# 增加工单日志
if user.username == workflow_detail.engineer:
workflowOb.add_workflow_log(
audit_id=audit_id,
operation_type=3,
operation_type_desc='取消执行',
operation_info="取消原因：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
workflowOb.add_workflow_log(
audit_id=audit_id,
operation_type=2,
operation_type_desc='审批不通过',
operation_info="审批备注：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
if user.username == workflow_detail.engineer:
workflowOb.auditworkflow(
request, audit_id,
WorkflowDict.workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
workflowOb.auditworkflow(
request, audit_id,
WorkflowDict.workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise PermissionDenied
# 删除定时执行job
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflow_id)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
workflow_detail.status = Const.workflowStatus['abort']
workflow_detail.audit_remark = audit_remark
workflow_detail.save()
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def _send(audit_info, msg_type=0, **kwargs):
audit_id = audit_info.audit_id
workflow_audit_remark = kwargs.get('audit_remark', '')
workflow_url = kwargs.get('workflow_url', '')
msg_email_cc = kwargs.get('email_cc', [])
workflow_id = audit_info.workflow_id
workflow_type = audit_info.workflow_type
status = audit_info.current_status
workflow_title = audit_info.workflow_title
workflow_from = audit_info.create_user_display
group_name = audit_info.group_name
webhook_url = SqlGroup.objects.get(
group_id=audit_info.group_id).ding_webhook
# 获取当前审批和审批流程
from sql.utils.workflow import Workflow
workflow_auditors, current_workflow_auditors = Workflow.review_info(
audit_info.workflow_id, audit_info.workflow_type)
# 准备消息内容
if workflow_type == WorkflowDict.workflow_type['query']:
workflow_type_display = WorkflowDict.workflow_type['query_display']
workflow_detail = QueryPrivilegesApply.objects.get(
apply_id=workflow_id)
if workflow_detail.priv_type == 1:
workflow_content = '''数据库清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
elif workflow_detail.priv_type == 2:
workflow_content = '''数据库：{}\n表清单：{}\n授权截止时间：{}\n结果集：{}\n'''.format(
workflow_detail.db_list, workflow_detail.table_list,
datetime.datetime.strftime(workflow_detail.valid_date,
'%Y-%m-%d %H:%M:%S'),
workflow_detail.limit_num)
else:
workflow_content = ''
elif workflow_type == WorkflowDict.workflow_type['sqlreview']:
workflow_type_display = WorkflowDict.workflow_type['sqlreview_display']
workflow_detail = SqlWorkflow.objects.get(pk=workflow_id)
workflow_content = re.sub(
'[\r\n\f]{2,}', '\n',
workflow_detail.sql_content[0:500].replace('\r', ''))
else:
raise Exception('工单类型不正确')
# 准备消息格式
if status == WorkflowDict.workflow_status['audit_wait']: # 申请阶段
msg_title = "[{}]新的工单申请#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = Group.objects.get(id=audit_info.current_audit).name
msg_email_reciver = [
user.email for user in auth_group_users([auth_group_names],
audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n当前审批：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_name, workflow_auditors,
current_workflow_auditors, workflow_title, workflow_url,
workflow_content)
elif status == WorkflowDict.workflow_status['audit_success']: # 审核通过
msg_title = "[{}]工单审核通过#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow_from, group_name, workflow_auditors, workflow_title,
workflow_url, workflow_content)
elif status == WorkflowDict.workflow_status['audit_reject']: # 审核驳回
msg_title = "[{}]工单被驳回#{}".format(workflow_type_display, audit_id)
# 接收人，仅发送给申请人
msg_email_reciver = [
Users.objects.get(username=audit_info.create_user).email
]
# 消息内容
msg_content = '''工单名称：{}\n工单地址：{}\n驳回原因：{}\n提醒：此工单被审核不通过，请按照驳回原因进行修改！'''.format(
workflow_title, workflow_url, workflow_audit_remark)
elif status == WorkflowDict.workflow_status['audit_abort']: # 审核取消，通知所有审核人
msg_title = "[{}]提交人主动终止工单#{}".format(workflow_type_display, audit_id)
# 接收人，发送给该资源组内对应权限组所有的用户
auth_group_names = [
Group.objects.get(id=auth_group_id).name
for auth_group_id in audit_info.audit_auth_groups.split(',')
]
msg_email_reciver = [
user.email
for user in auth_group_users(auth_group_names, audit_info.group_id)
]
# 消息内容
msg_content = '''发起人：{}\n组：{}\n工单名称：{}\n工单地址：{}\n提醒：提交人主动终止流程'''.format(
workflow_from, group_name, workflow_title, workflow_url)
else:
raise Exception('工单状态不正确')
# 判断是发送钉钉还是发送邮件
msg_sender = MailSender()
logger.debug('消息标题:{}\n通知对象：{}\n消息内容：{}'.format(msg_title,
msg_email_reciver,
msg_content))
if msg_type == 0:
sys_config = SysConfig().sys_config
if sys_config.get('mail'):
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
if sys_config.get('ding'):
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
elif msg_type == 1:
msg_sender.send_email(msg_title,
msg_content,
msg_email_reciver,
list_cc_addr=msg_email_cc)
elif msg_type == 2:
msg_sender.send_ding(webhook_url, msg_title + '\n' + msg_content)
