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 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 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)
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
# 交由系统执行
if mode == "auto":
# 修改工单状态为排队中
SqlWorkflow(id=workflow_id,
status="workflow_queuing").save(update_fields=['status'])
# 删除定时执行任务
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
request.user,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_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)
# 线下手工执行
elif mode == "manual":
# 将流程状态修改为执行结束
SqlWorkflow(id=workflow_id,
status="workflow_finish",
finish_time=datetime.datetime.now()).save(
update_fields=['status', 'finish_time'])
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='手工工单',
operation_info='确认手工执行结束',
operator=request.user.username,
operator_display=request.user.display)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute_select(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')
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)
fileName = datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".txt"
output_file = "/tmp/" + fileName
def output_data():
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance = workflow_detail.instance
sql_content = workflow_detail.sqlworkflowcontent.sql_content.replace(
";", "").replace("'", '"')
sql_content = sql_content + " limit 10000;" if "limit" not in sql_content else sql_content
sql_cmd = f"""/usr/bin/mysql -u{instance.user} -p'{instance.raw_password}' --default-character-set=utf8 -h{instance.host} -P{instance.port} -e 'use {workflow_detail.db_name};{sql_content}' >{output_file}"""
result = os.system(sql_cmd)
return result
if output_data() == 0 and os.path.exists(output_file):
def file_iterator(fileName, chunk_size=512):
with open(fileName, 'r') as f:
while True:
c = f.read(chunk_size)
if c:
yield c
else:
break
response = StreamingHttpResponse(file_iterator(output_file))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename="{0}"'.format(
fileName)
return response
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def post(self, request):
# 参数验证
serializer = ExecuteWorkflowSerializer(data=request.data)
if not serializer.is_valid():
return Response(serializer.errors,
status=status.HTTP_400_BAD_REQUEST)
workflow_type = request.data['workflow_type']
workflow_id = request.data['workflow_id']
# 执行SQL上线工单
if workflow_type == 2:
mode = request.data['mode']
engineer = request.data['engineer']
user = Users.objects.get(username=engineer)
# 校验多个权限
if not (user.has_perm('sql.sql_execute')
or user.has_perm('sql.sql_execute_for_resource_group')):
raise serializers.ValidationError({"errors": "你无权执行当前工单！"})
if can_execute(user, workflow_id) is False:
raise serializers.ValidationError({"errors": "你无权执行当前工单！"})
if on_correct_time_period(workflow_id) is False:
raise serializers.ValidationError(
{"errors": "不在可执行时间范围内，如果需要修改执行时间请重新提交工单!"})
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 交由系统执行
if mode == "auto":
# 修改工单状态为排队中
SqlWorkflow(
id=workflow_id,
status="workflow_queuing").save(update_fields=['status'])
# 删除定时执行任务
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
user,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_id}')
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='工单执行排队中',
operator=user.username,
operator_display=user.display)
# 线下手工执行
elif mode == "manual":
# 将流程状态修改为执行结束
SqlWorkflow(id=workflow_id,
status="workflow_finish",
finish_time=datetime.datetime.now()).save(
update_fields=['status', 'finish_time'])
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='手工工单',
operation_info='确认手工执行结束',
operator=user.username,
operator_display=user.display)
# 开启了Execute阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Execute' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
notify_for_execute(SqlWorkflow.objects.get(id=workflow_id))
# 执行数据归档工单
elif workflow_type == 3:
async_task('sql.archiver.archive',
workflow_id,
timeout=-1,
task_name=f'archive-{workflow_id}')
return Response({'msg': '开始执行，执行结果请到工单详情页查看'})
