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 Audit.can_review(request.user, apply_id, 1) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query']).audit_id
# 调用工作流接口审核
audit_result = Audit.audit(audit_id, audit_status, user.username,
audit_remark)
# 按照审核结果更新业务表审核状态
audit_detail = 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)
else:
# 消息通知
sys_config = SysConfig()
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_detail = Audit.detail_by_workflow_id(
workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query'])
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(
base_url=base_url, audit_id=audit_detail.audit_id)
async_task(notify,
audit_info=audit_detail,
workflow_url=workflow_url,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(apply_id, )))
def passed(request):
workflow_id = request.POST.get('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 Audit.can_review(request.user, workflow_id, 2) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
audit_result = Audit.audit(
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 = 'workflow_review_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)
else:
# 消息通知
sys_config = SysConfig()
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(
base_url=base_url, audit_id=audit_detail.audit_id)
async_task(notify,
audit_info=audit_detail,
workflow_url=workflow_url,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def query(workflow_id):
"""为延时或异步任务准备的queryx, 传入工单ID即可"""
workflow = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow.status == 'workflow_timingtask':
# 将工单状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
query_engine = get_engine(instance=workflow.instance)
with FuncTimer() as t:
query_result = query_engine.query(
workflow.db_name, workflow.sqlworkflowcontent.sql_content)
# if workflow.instance.db_type == 'pgsql': # TODO 此处判断待优化，请在 修改传参方式后去除
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content,
# schema_name=workflow.schema_name)
# else:
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content)
query_result.query_time = t.cost
return query_result
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)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = 'workflow_executing'
workflow_detail.reviewok_time = timezone.now()
workflow_detail.save()
async_task('sql.utils.execute_sql.execute',
workflow_detail.id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1)
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_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 execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
# https://stackoverflow.com/questions/7835272/django-operationalerror-2006-mysql-server-has-gone-away
if connection.connection and not connection.is_usable():
close_old_connections()
workflow_id = task.args[0]
# 判断工单状态，如果不是执行中的，不允许更新信息，直接抛错记录日志
with transaction.atomic():
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.status != 'workflow_executing':
raise Exception(f'工单{workflow.id}状态不正确，禁止重复更新执行结果！')
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
execute_result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content)
execute_result.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
else:
execute_result = task.result
workflow.status = 'workflow_finish'
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result.json()
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# DDL工单结束后清空实例资源缓存
if workflow.syntax_type == 1:
r = get_redis_connection("default")
for key in r.scan_iter(match='*insRes*', count=2000):
r.delete(key)
# 发送消息
notify_for_execute(workflow)
def execute(workflow_id, user=None):
"""为延时或异步任务准备的execute, 传入工单ID和执行人信息"""
# 使用当前读防止重复执行
with transaction.atomic():
workflow_detail = SqlWorkflow.objects.select_for_update().get(
id=workflow_id)
# 只有审核通过和定时执行的数据才可以继续执行
if workflow_detail.status not in [
'workflow_review_pass', 'workflow_timingtask'
]:
raise Exception('工单状态不正确，禁止执行！')
# 将工单状态修改为执行中
else:
SqlWorkflow(
id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
# 增加执行日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='人工操作执行' if user else '系统定时执行',
operator=user.username if user else '',
operator_display=user.display if user else '系统')
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
def queryapplydetail(request, apply_id):
"""查询权限申请详情页面"""
workflow_detail = QueryPrivilegesApply.objects.get(apply_id=apply_id)
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(apply_id, 1)
# 是否可审核
is_can_review = Audit.can_review(request.user, apply_id, 1)
# 获取审核日志
if workflow_detail.status == 2:
try:
audit_id = Audit.detail_by_workflow_id(workflow_id=apply_id,
workflow_type=1).audit_id
last_operation_info = Audit.logs(
audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
last_operation_info = ''
context = {
'workflow_detail': workflow_detail,
'audit_auth_group': audit_auth_group,
'last_operation_info': last_operation_info,
'current_audit_auth_group': current_audit_auth_group,
'is_can_review': is_can_review
}
return render(request, 'queryapplydetail.html', context)
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.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(workflow.status),
operator='',
operator_display='系统')
# 发送消息
send_msg(workflow)
def execute(request):
"""
执行SQL
:param request:
:return:
"""
# 校验多个权限
if not (request.user.has_perm('sql.sql_execute')
or request.user.has_perm('sql.sql_execute_for_resource_group')):
raise PermissionDenied
workflow_id = int(request.POST.get('workflow_id', 0))
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
if on_correct_time_period(workflow_id) is False:
context = {'errMsg': '不在可执行时间范围内，如果需要修改执行时间请重新提交工单!'}
return render(request, 'error.html', context)
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
if mode == "auto":
status = "workflow_executing"
operation_type = 5
operation_type_desc = '执行工单'
operation_info = "自动操作执行"
finish_time = None
else:
status = "workflow_finish"
operation_type = 6
operation_type_desc = '手工工单'
operation_info = "确认手工执行结束"
finish_time = datetime.datetime.now()
# 将流程状态修改为对应状态
SqlWorkflow(
id=workflow_id, status=status,
finish_time=finish_time).save(update_fields=['status', 'finish_time'])
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=operation_type,
operation_type_desc=operation_type_desc,
operation_info=operation_info,
operator=request.user.username,
operator_display=request.user.display)
if mode == "auto":
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_id}')
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def archive_detail(request, id):
"""归档详情页面"""
archive_config = ArchiveConfig.objects.get(pk=id)
# 获取当前审批和审批流程、是否可审核
try:
audit_auth_group, current_audit_auth_group = Audit.review_info(id, 3)
is_can_review = Audit.can_review(request.user, id, 3)
except Exception as e:
logger.debug(f'归档配置{id}无审核信息，{e}')
audit_auth_group, current_audit_auth_group = None, None
is_can_review = False
# 获取审核日志
if archive_config.status == 2:
try:
audit_id = Audit.detail_by_workflow_id(workflow_id=id, workflow_type=3).audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'归档配置{id}无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
last_operation_info = ''
context = {'archive_config': archive_config, 'audit_auth_group': audit_auth_group,
'last_operation_info': last_operation_info, 'current_audit_auth_group': current_audit_auth_group,
'is_can_review': is_can_review}
return render(request, 'archivedetail.html', context)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
sys_config = SysConfig()
wx_status = sys_config.get('wx')
if not sys_config.get('mail') and not sys_config.get('ding') and not sys_config.get('ding_to_person') \
and not wx_status:
logger.info('未开启消息通知，可在系统设置中开启')
return None
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url,
audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(
WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起人：{}\n组：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.engineer_display, workflow.group_name, audit_auth_group,
workflow.workflow_name, url,
re.sub(
'[\r\n\f]{2,}', '\n',
workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'],
group_id=workflow.group_id)
# 处理接收人
webhook_url = ResourceGroup.objects.get(
group_id=workflow.group_id).ding_webhook
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, webhook_url=webhook_url)
# DDL通知
if sys_config.get(
'ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name, workflow.instance.instance_name,
workflow.db_name, workflow.workflow_name, url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
msg_to = Users.objects.filter(
groups__name=sys_config.get('ddl_notify_auth_group'))
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc)
def notify_for_execute(workflow):
"""
工单执行结束的通知
:param workflow:
:return:
"""
# 判断是否开启消息通知，未开启直接返回
if not __notify_cnf_status():
return None
sys_config = SysConfig()
# 获取当前审批和审批流程
base_url = sys_config.get('archery_base_url', 'http://127.0.0.1:8000').rstrip('/')
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow.id, 2)
audit_id = Audit.detail_by_workflow_id(workflow.id, 2).audit_id
url = "{base_url}/workflow/{audit_id}".format(base_url=base_url, audit_id=audit_id)
msg_title = "[{}]工单{}#{}".format(WorkflowDict.workflow_type['sqlreview_display'],
workflow.get_status_display(), audit_id)
msg_content = '''发起时间：{}\n发起人：{}\n组：{}\n目标实例：{}\n数据库：{}\n审批流程：{}\n工单名称：{}\n工单地址：{}\n工单详情预览：{}\n'''.format(
workflow.create_time.strftime('%Y-%m-%d %H:%M:%S'),
workflow.engineer_display,
workflow.group_name,
workflow.instance.instance_name,
workflow.db_name,
audit_auth_group,
workflow.workflow_name,
url,
re.sub('[\r\n\f]{2,}', '\n', workflow.sqlworkflowcontent.sql_content[0:500].replace('\r', '')))
# 邮件通知申请人，抄送DBA
msg_to = Users.objects.filter(username=workflow.engineer)
msg_cc = auth_group_users(auth_group_names=['DBA'], group_id=workflow.group_id)
# 处理接收人
dingding_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).ding_webhook
feishu_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).feishu_webhook
qywx_webhook = ResourceGroup.objects.get(group_id=workflow.group_id).qywx_webhook
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc, dingding_webhook=dingding_webhook, feishu_webhook=feishu_webhook,
qywx_webhook=qywx_webhook)
# DDL通知
if sys_config.get('ddl_notify_auth_group') and workflow.status == 'workflow_finish':
# 判断上线语句是否存在DDL，存在则通知相关人员
if workflow.syntax_type == 1:
# 消息内容通知
msg_title = '[Archery]有新的DDL语句执行完成#{}'.format(audit_id)
msg_content = '''发起人：{}\n变更组：{}\n变更实例：{}\n变更数据库：{}\n工单名称：{}\n工单地址：{}\n工单预览：{}\n'''.format(
Users.objects.get(username=workflow.engineer).display,
workflow.group_name,
workflow.instance.instance_name,
workflow.db_name,
workflow.workflow_name,
url,
workflow.sqlworkflowcontent.sql_content[0:500])
# 获取通知成员ddl_notify_auth_group
ddl_notify_auth_group = sys_config.get('ddl_notify_auth_group', '').split(',')
msg_to = Users.objects.filter(groups__name__in=ddl_notify_auth_group)
# 发送通知
__send(msg_title, msg_content, msg_to, msg_cc)
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 = 'workflow_exception'
if workflow.sqlworkflowcontent.execute_result:
execute_result = json.loads(
workflow.sqlworkflowcontent.execute_result)
else:
execute_result = []
execute_result.append(
ReviewResult(id=0,
stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql='执行异常信息',
affected_rows=0,
actual_affected_rows=0,
sequence='0_0_0',
backup_dbname=None,
execute_time=0,
sqlsha1='').__dict__)
execute_result = json.dumps(execute_result)
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
execute_result = execute_result.json()
else:
execute_result = task.result
workflow.status = 'workflow_finish'
execute_result = execute_result.json()
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# 发送消息
notify_for_execute(workflow)
def detail(request, workflow_id):
"""展示SQL工单详细页面"""
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if not can_view(request.user, workflow_id):
raise PermissionDenied
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(workflow_id, 2)
# 是否可审核
is_can_review = Audit.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)
# 是否可查看回滚信息
is_can_rollback = can_rollback(request.user, workflow_id)
# 获取审核日志
try:
audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
last_operation_info = Audit.logs(audit_id=audit_id).latest('id').operation_info
except Exception as e:
logger.debug(f'无审核日志记录，错误信息{e}')
last_operation_info = ''
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
is_can_rollback = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
job = task_info(job_id)
if job:
run_date = job.next_run
else:
run_date = ''
else:
run_date = ''
# 获取是否开启手工执行确认
manual = SysConfig().get('manual')
context = {'workflow_detail': workflow_detail, 'last_operation_info': last_operation_info,
'is_can_review': is_can_review, 'is_can_execute': is_can_execute, 'is_can_timingtask': is_can_timingtask,
'is_can_cancel': is_can_cancel, 'is_can_rollback': is_can_rollback, 'audit_auth_group': audit_auth_group,
'manual': manual, 'current_audit_auth_group': current_audit_auth_group, 'run_date': run_date}
return render(request, 'detail.html', context)
def passed(request):
"""
审核通过，不执行
:param request:
:return:
"""
workflow_id = int(request.POST.get('workflow_id', 0))
audit_remark = request.POST.get('audit_remark', '')
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
sql_workflow = SqlWorkflow.objects.get(id=workflow_id)
user = request.user
if Audit.can_review(user, workflow_id, 2) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
audit_result = Audit.audit(
audit_id, WorkflowDict.workflow_status['audit_success'],
user.username, audit_remark)
# 按照审核结果更新业务表审核状态
if audit_result['data'][
'workflow_status'] == WorkflowDict.workflow_status[
'audit_success']:
if sql_workflow.order_type == 'common_order':
sql_workflow.status = 'workflow_review_pass'
elif sql_workflow.order_type == 'sqlcron_order':
sql_workflow.schedule.repeats = -1 # 开启调度任务
sql_workflow.schedule.save()
sql_workflow.status = 'workflow_review_pass'
sql_workflow.save()
except Exception as msg:
logger.error(f"审核工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'sqlreview-pass-{workflow_id}')
redirect = reverse('sql:sqlcrondetail', args=(workflow_id,))\
if sql_workflow.order_type == 'sqlcron_order' \
else reverse('sql:detail', args=(workflow_id,))
return HttpResponseRedirect(redirect)
def timing_task(request):
"""
定时执行SQL
:param request:
:return:
"""
# 校验多个权限
if not (request.user.has_perm('sql.sql_execute')
or request.user.has_perm('sql.sql_execute_for_resource_group')):
raise PermissionDenied
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)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
schedule_name = f"sqlreview-timing-{workflow_id}"
if on_correct_time_period(workflow_id, run_date) is False:
context = {'errMsg': '不在可执行时间范围内，如果需要修改执 行时间请重新提交工单!'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sql_schedule(schedule_name, run_date, workflow_id)
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_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(f"定时执行工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def query_priv_audit(request):
"""
查询权限审核
:param request:
:return:
"""
# 获取用户信息
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 Audit.can_review(request.user, apply_id, 1) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=apply_id,
workflow_type=WorkflowDict.workflow_type['query']).audit_id
# 调用工作流接口审核
audit_result = Audit.audit(audit_id, audit_status, user.username,
audit_remark)
# 按照审核结果更新业务表审核状态
audit_detail = Audit.detail(audit_id)
if audit_detail.workflow_type == WorkflowDict.workflow_type[
'query']:
# 更新业务表审核状态,插入权限信息
_query_apply_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)
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'query-priv-audit-{apply_id}')
return HttpResponseRedirect(
reverse('sql:queryapplydetail', args=(apply_id, )))
def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
close_old_connections()
workflow_id = task.args[0]
workflow = SqlWorkflow.objects.get(id=workflow_id)
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
execute_result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content)
execute_result.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
else:
execute_result = task.result
workflow.status = 'workflow_finish'
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result.json()
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# DDL工单结束后清空实例资源缓存
if workflow.syntax_type == 1:
r = get_redis_connection("default")
for key in r.scan_iter(match='*insRes*', count=2000):
r.delete(key)
# 发送消息
notify_for_execute(workflow)
def stop(request):
user = request.user
workflow_id = request.POST.get('workflow_id')
stop_remark = request.POST.get('stop_remark')
if not workflow_id:
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
if not stop_remark:
return JsonResponse({'status': 1, 'msg': '终止原因不能为空', 'data': []})
try:
group_list = user_groups(user)
group_ids = [group.group_id for group in group_list]
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.group_id not in group_ids:
return JsonResponse({'status': 1, 'msg': '您无权操作', 'data': []})
# 只有工单流转起来后，才能停止
if workflow.status not in [
'workflow_finish', 'workflow_review_pass',
'workflow_exception', 'workflow_pause'
]:
return JsonResponse({
'status': 1,
'msg': '该工单无法停止，操作非法',
'data': []
})
with transaction.atomic():
Schedule.objects.filter(name=f'sqlcron-{workflow_id}').update(
repeats=0)
workflow.status = 'workflow_stop'
workflow.save()
# 添加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(
audit_id=audit_id,
operation_type=6,
operation_type_desc='终止执行',
operation_info='终止原因：{}'.format(stop_remark),
operator=user.username,
operator_display=user.display,
)
except Exception as e:
logger.error(f'发生异常，错误信息：{traceback.format_exc()}')
return JsonResponse({'status': 1, 'msg': f'发生异常，错误信息：{e}'})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def archive_audit(request):
"""
审核数据归档申请
:param request:
:return:
"""
# 获取用户信息
user = request.user
archive_id = int(request.POST['archive_id'])
audit_status = int(request.POST['audit_status'])
audit_remark = request.POST.get('audit_remark')
if audit_remark is None:
audit_remark = ''
if Audit.can_review(request.user, archive_id, 3) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=archive_id,
workflow_type=WorkflowDict.workflow_type['archive']).audit_id
# 调用工作流插入审核信息，更新业务表审核状态
audit_status = Audit.audit(audit_id, audit_status, user.username,
audit_remark)['data']['workflow_status']
ArchiveConfig(id=archive_id,
status=audit_status,
state=True if audit_status
== WorkflowDict.workflow_status['audit_success'] else
False).save(update_fields=['status', 'state'])
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'archive-audit-{archive_id}')
return HttpResponseRedirect(
reverse('sql:archive_detail', args=(archive_id, )))
def passed(request):
"""
审核通过，不执行
:param request:
:return:
"""
workflow_id = int(request.POST.get('workflow_id', 0))
audit_remark = request.POST.get('audit_remark', '')
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
user = request.user
if Audit.can_review(user, workflow_id, 2) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
audit_result = Audit.audit(
audit_id, WorkflowDict.workflow_status['audit_success'],
user.username, audit_remark)
# 按照审核结果更新业务表审核状态
if audit_result['data'][
'workflow_status'] == WorkflowDict.workflow_status[
'audit_success']:
# 将流程状态修改为审核通过
SqlWorkflow(id=workflow_id,
status='workflow_review_pass').save(
update_fields=['status'])
except Exception as msg:
logger.error(f"审核工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def pause(request):
user = request.user
workflow_id = request.POST.get('workflow_id')
if not workflow_id:
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
group_list = user_groups(user)
group_ids = [group.group_id for group in group_list]
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.group_id not in group_ids:
return JsonResponse({'status': 1, 'msg': '您无权操作', 'data': []})
# 在‘已审核’、‘在执行’、‘已执行’的状态下工单才能被暂停
if workflow.status not in [
'workflow_review_pass', 'workflow_executing', 'workflow_finish'
]:
return JsonResponse({
'status': 1,
'msg': '该工单非暂停状态，操作非法',
'data': []
})
with transaction.atomic():
Schedule.objects.filter(name=f'sqlcron-{workflow_id}').update(
repeats=0)
workflow.status = 'workflow_pause'
workflow.save()
# 添加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(
audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='执行结果：已暂停任务',
operator=user.username,
operator_display=user.display,
)
except Exception as e:
logger.error(f'发生异常，错误信息：{traceback.format_exc()}')
return JsonResponse({'status': 1, 'msg': f'发生异常，错误信息：{e}'})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def execute(workflow_id):
"""为延时或异步任务准备的execute, 传入工单ID即可"""
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow_detail.status == Const.workflowStatus['timingtask']:
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
execute_engine = get_engine(workflow=workflow_detail)
return execute_engine.execute_workflow()
def passed(request):
workflow_id = request.POST.get('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 Audit.can_review(request.user, workflow_id, 2) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
audit_result = Audit.audit(
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 = 'workflow_review_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)
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute(workflow_id):
"""为延时或异步任务准备的execute, 传入工单ID即可"""
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow_detail.status == 'workflow_timingtask':
# 将工单状态修改为执行中
SqlWorkflow(id=workflow_id, status='workflow_executing').save(update_fields=['status'])
audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统'
)
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
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)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id)
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type[
'sqlreview']).audit_id
Audit.add_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(request):
"""
执行SQL
:param request:
:return:
"""
# 校验多个权限
if not (request.user.has_perm('sql.sql_execute')
or request.user.has_perm('sql.sql_execute_for_resource_group')):
raise PermissionDenied
workflow_id = int(request.POST.get('workflow_id', 0))
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def detail(request, workflow_id):
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in ['workflow_finish', 'workflow_exception'] \
and workflow_detail.is_manual == 0:
rows = workflow_detail.sqlworkflowcontent.execute_result
else:
rows = workflow_detail.sqlworkflowcontent.review_content
# 自动审批不通过的不需要获取下列信息
if workflow_detail.status != 'workflow_autoreviewwrong':
# 获取当前审批和审批流程
audit_auth_group, current_audit_auth_group = Audit.review_info(
workflow_id, 2)
# 是否可审核
is_can_review = Audit.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)
# 获取审核日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
last_operation_info = Audit.logs(
audit_id=audit_id).latest('id').operation_info
else:
audit_auth_group = '系统自动驳回'
current_audit_auth_group = '系统自动驳回'
is_can_review = False
is_can_execute = False
is_can_timingtask = False
is_can_cancel = False
last_operation_info = None
# 获取定时执行任务信息
if workflow_detail.status == 'workflow_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 = ''
# 兼容旧数据'[[]]'格式，转换为新格式[{}]
if isinstance(json.loads(rows)[0], list):
review_result = ReviewSet()
for r in json.loads(rows):
review_result.rows += [ReviewResult(inception_result=r)]
rows = review_result.json()
context = {
'workflow_detail': workflow_detail,
'rows': rows,
'last_operation_info': last_operation_info,
'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 cancel(request):
"""
终止流程
:param request:
:return:
"""
workflow_id = int(request.POST.get('workflow_id', 0))
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
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.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflow_detail.status != 'workflow_manreviewing':
# 增加工单日志
if user.username == workflow_detail.engineer:
Audit.add_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:
Audit.add_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:
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise PermissionDenied
# 删除定时执行job
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflow_id)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
workflow_detail.status = 'workflow_abort'
workflow_detail.save()
except Exception as msg:
logger.error(f"取消工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 仅未审核通过又取消的工单需要发送消息，审核通过的不发送
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
if audit_detail.current_status == WorkflowDict.workflow_status[
'audit_abort']:
async_task(notify_for_audit,
audit_id=audit_detail.audit_id,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def submit(request):
"""正式提交SQL, 此处生成工单"""
sql_content = request.POST['sql_content'].strip()
workflow_title = request.POST['workflow_name']
# 检查用户是否有权限涉及到资源组等， 比较复杂， 可以把检查权限改成一个独立的方法
# 工单表中可以考虑不存储资源组相关信息
# 工单和实例关联， 实例和资源组关联， 资源组和用户关联。（reply 一个实例可以被多个资源组关联，无法去除）
group_name = request.POST['group_name']
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST['instance_name']
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
is_backup = True if request.POST['is_backup'] == 'True' else False
notify_users = request.POST.getlist('notify_users')
list_cc_addr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
# 服务器端参数验证
if None in [sql_content, db_name, instance_name, db_name, is_backup]:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, type='master',
db_type='all').get(instance_name=instance_name)
except instance.DoesNotExist:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# 再次交给engine进行检测，防止绕过
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
context = {'errMsg': str(e)}
return render(request, 'error.html', context)
# 按照系统配置确定是自动驳回还是放行
sys_config = SysConfig()
auto_review_wrong = sys_config.get('auto_review_wrong',
'') # 1表示出现警告就驳回，2和空表示出现错误才驳回
workflow_status = 'workflow_manreviewing'
if check_result.warning_count > 0 and auto_review_wrong == '1':
workflow_status = 'workflow_autoreviewwrong'
elif check_result.error_count > 0 and auto_review_wrong in ('', '1', '2'):
workflow_status = 'workflow_autoreviewwrong'
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
workflow_name=workflow_title,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status=workflow_status,
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=check_result.syntax_type,
create_time=timezone.now())
SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == 'workflow_manreviewing':
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], workflow_id)
except Exception as msg:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 自动审核通过才进行消息通知
if workflow_status == 'workflow_manreviewing':
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
email_cc=list_cc_addr,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
