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': '开始执行,执行结果请到工单详情页查看'})