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(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 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()
async_task('sql.utils.execute_sql.execute',
workflow_detail.id,
hook='sql.utils.execute_sql.execute_callback')
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.execute_result
else:
rows = workflow_detail.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)
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 == '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,
'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 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 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 execute(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)
instance_name = workflowDetail.instance_name
db_name = workflowDetail.db_name
url = getDetailUrl(request) + str(workflowId) + '/'
user = request.user
if can_execute(request.user, workflowId) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflowDetail.status = Const.workflowStatus['executing']
workflowDetail.reviewok_time = timezone.now()
workflowDetail.save()
# 判断是通过inception执行还是直接执行，is_manual=0则通过inception执行，is_manual=1代表inception审核不通过，需要直接执行
if workflowDetail.is_manual == 0:
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
splitReviewResult = InceptionDao().sqlautoReview(
workflowDetail.sql_content,
workflowDetail.instance_name,
db_name,
isSplit='yes')
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflowDetail.review_content = json.dumps(splitReviewResult)
try:
workflowDetail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflowId, instance_name, url))
t.start()
else:
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_skipinc_call_back,
args=(workflowId, instance_name, db_name,
workflowDetail.sql_content, url))
t.start()
# 删除定时执行job
if workflowDetail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
del_sqlcronjob(job_id)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def test_can_execute_false_no_permission(self, ):
"""
当前登录用户为提交人，但是没有执行权限
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_timingtask'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def test_can_execute_false_not_in_group(self, ):
"""
当前登录用户为提交人，有资源组粒度执行权限，但是不是组内用户
:return:
"""
# 修改工单为workflow_review_pass，有资源组粒度执行权限，但是不是组内用户
self.wf1.status = 'workflow_review_pass'
self.wf1.save(update_fields=('status',))
sql_execute_for_resource_group = Permission.objects.get(codename='sql_execute_for_resource_group')
self.user.user_permissions.add(sql_execute_for_resource_group)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def test_can_execute_for_resource_group(self, ):
"""
测试是否能执行的判定条件,登录用户有资源组粒度执行权限，并且为组内用户
:return:
"""
# 修改工单为workflow_review_pass，登录用户有资源组粒度执行权限，并且为组内用户
self.wf1.status = 'workflow_review_pass'
self.wf1.save(update_fields=('status',))
sql_execute_for_resource_group = Permission.objects.get(codename='sql_execute_for_resource_group')
self.user.user_permissions.add(sql_execute_for_resource_group)
ResourceGroupRelations.objects.create(object_type=0, object_id=self.user.id, group_id=self.group.group_id)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_workflow_timing_task(self, ):
"""
测试是否能执行的判定条件,当前登录用户为提交人，并且有执行权限,工单状态为定时执行
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_timingtask'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_false_wrong_status(self, ):
"""
当前登录用户为提交人，前登录用户为提交人，并且有执行权限,但是工单状态为待审核
:return:
"""
# 修改工单为workflow_manreviewing，当前登录用户为提交人，并且有执行权限, 但是工单状态为待审核
self.wf1.status = 'workflow_manreviewing'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def stop_osc_progress(request):
"""
中止该SQL的pt-OSC进程
"""
workflow_id = request.POST['workflow_id']
sql_id = request.POST['sqlID']
if workflow_id == '' or workflow_id is None or sql_id == '' or sql_id is None:
context = {"status": -1, 'msg': 'workflow_id或sqlID参数为空.', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 服务器端二次验证，当前工单状态必须为等待人工审核,正在执行人工审核动作的当前登录用户必须为审核人. 避免攻击或被接口测试工具强行绕过
if workflow_detail.status != Const.workflowStatus['executing']:
context = {"status": -1, "msg": '当前工单状态不是"执行中"，请刷新当前页面！', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
if can_execute(request.user, workflow_id) is False:
context = {"status": -1, "msg": '你无权操作当前工单，请刷新当前页面！', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
workflow_id = int(workflow_id)
sql_id = int(sql_id)
if workflow_id in sqlSHA1_cache:
dict_sha1 = sqlSHA1_cache[workflow_id]
else:
dict_sha1 = get_sql_sha1(workflow_id)
if dict_sha1 != {} and sql_id in dict_sha1:
sql_sha1 = dict_sha1[sql_id]
try:
opt_result = InceptionDao().stop_osc_progress(sql_sha1)
except Exception as msg:
logger.error(traceback.format_exc())
result = {'status': 1, 'msg': msg, 'data': ''}
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
opt_result = {"status": 4, "msg": "不是由pt-OSC执行的", "data": ""}
return HttpResponse(json.dumps(opt_result),
content_type='application/json')
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, 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 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': '开始执行，执行结果请到工单详情页查看'})
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 detail(request, workflow_id):
"""展示SQL工单详细页面"""
workflow_detail = get_object_or_404(SqlWorkflow, pk=workflow_id)
if workflow_detail.status in ['workflow_finish', 'workflow_exception']:
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)
# 获取审核日志
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
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')
review_result = ReviewSet()
if rows:
try:
# 检验rows能不能正常解析
loaded_rows = json.loads(rows)
# 兼容旧数据'[[]]'格式，转换为新格式[{}]
if isinstance(loaded_rows[-1], list):
for r in loaded_rows:
review_result.rows += [ReviewResult(inception_result=r)]
rows = review_result.json()
except IndexError:
review_result.rows += [
ReviewResult(
id=1,
sql=workflow_detail.sqlworkflowcontent.sql_content,
errormessage="Json decode failed."
"执行结果Json解析失败, 请联系管理员")
]
rows = review_result.json()
except json.decoder.JSONDecodeError:
review_result.rows += [
ReviewResult(
id=1,
sql=workflow_detail.sqlworkflowcontent.sql_content,
# 迫于无法单元测试这里加上英文报错信息
errormessage="Json decode failed."
"执行结果Json解析失败, 请联系管理员")
]
rows = review_result.json()
else:
rows = workflow_detail.sqlworkflowcontent.review_content
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,
'manual': manual,
'current_audit_auth_group': current_audit_auth_group,
'run_date': run_date
}
return render(request, 'detail.html', context)
