def archive_switch(request):
"""开启关闭归档任务"""
archive_id = request.POST.get('archive_id')
state = True if request.POST.get('state') == 'true' else False
# 更新启用状态
try:
ArchiveConfig(id=archive_id, state=state).save(update_fields=['state'])
return JsonResponse({'status': 0, 'msg': 'ok', 'data': {}})
except Exception as msg:
return JsonResponse({'status': 1, 'msg': f'{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 archive(archive_id):
"""
执行数据库归档
:return:
"""
archive_info = ArchiveConfig.objects.get(id=archive_id)
s_ins = archive_info.src_instance
src_db_name = archive_info.src_db_name
src_table_name = archive_info.src_table_name
condition = archive_info.condition
no_delete = archive_info.no_delete
sleep = archive_info.sleep
mode = archive_info.mode
# 获取归档表的字符集信息
s_engine = get_engine(s_ins)
db = s_engine.schema_object.databases[src_db_name]
tb = db.tables[src_table_name]
charset = tb.options['charset'].value
if charset is None:
charset = db.options['charset'].value
pt_archiver = PtArchiver()
# 准备参数
source = fr"h={s_ins.host},u={s_ins.user},p={s_ins.password},P={s_ins.port},D={src_db_name},t={src_table_name}"
args = {
"no-version-check": True,
"source": source,
"where": condition,
"progress": 5000,
"statistics": True,
"charset": charset,
"limit": 10000,
"txn-size": 1000,
"sleep": sleep
}
# 归档到目标实例
if mode == 'dest':
d_ins = archive_info.dest_instance
dest_db_name = archive_info.dest_db_name
dest_table_name = archive_info.dest_table_name
dest = fr"h={d_ins.host},u={d_ins.user},p={d_ins.password},P={d_ins.port},D={dest_db_name},t={dest_table_name}"
args['dest'] = dest
args['bulk-insert'] = True
if no_delete:
args['no-delete'] = True
else:
args['bulk-delete'] = True
elif mode == 'file':
output_directory = os.path.join(settings.BASE_DIR,
'downloads/archiver')
os.makedirs(output_directory, exist_ok=True)
args[
'file'] = f'{output_directory}/{s_ins.instance_name}-{src_db_name}-{src_table_name}.txt'
if no_delete:
args['no-delete'] = True
else:
args['bulk-delete'] = True
elif mode == 'purge':
args['purge'] = True
# 参数检查
args_check_result = pt_archiver.check_args(args)
if args_check_result['status'] == 1:
return JsonResponse(args_check_result)
# 参数转换
cmd_args = pt_archiver.generate_args2cmd(args, shell=True)
# 执行命令，获取结果
select_cnt = 0
insert_cnt = 0
delete_cnt = 0
with FuncTimer() as t:
p = pt_archiver.execute_cmd(cmd_args, shell=True)
stdout = ''
for line in iter(p.stdout.readline, ''):
if re.match(r'^SELECT\s(\d+)$', line, re.I):
select_cnt = re.findall(r'^SELECT\s(\d+)$', line)
elif re.match(r'^INSERT\s(\d+)$', line, re.I):
insert_cnt = re.findall(r'^INSERT\s(\d+)$', line)
elif re.match(r'^DELETE\s(\d+)$', line, re.I):
delete_cnt = re.findall(r'^DELETE\s(\d+)$', line)
stdout += f'{line}\n'
statistics = stdout
# 获取异常信息
stderr = p.stderr.read()
if stderr:
statistics = stdout + stderr
# 判断归档结果
select_cnt = int(select_cnt[0]) if select_cnt else 0
insert_cnt = int(insert_cnt[0]) if insert_cnt else 0
delete_cnt = int(delete_cnt[0]) if delete_cnt else 0
error_info = ''
success = True
if stderr:
error_info = f'命令执行报错:{stderr}'
success = False
if mode == 'dest':
# 删除源数据，判断删除数量和写入数量
if not no_delete and (insert_cnt != delete_cnt):
error_info = f"删除和写入数量不一致:{insert_cnt}!={delete_cnt}"
success = False
elif mode == 'file':
# 删除源数据，判断查询数量和删除数量
if not no_delete and (select_cnt != delete_cnt):
error_info = f"查询和删除数量不一致:{select_cnt}!={delete_cnt}"
success = False
elif mode == 'purge':
# 直接删除。判断查询数量和删除数量
if select_cnt != delete_cnt:
error_info = f"查询和删除数量不一致:{select_cnt}!={delete_cnt}"
success = False
# 执行信息保存到数据库
if connection.connection and not connection.is_usable():
close_old_connections()
# 更新最后归档时间
ArchiveConfig(
id=archive_id,
last_archive_time=t.end).save(update_fields=['last_archive_time'])
# 替换密码信息后保存
ArchiveLog.objects.create(
archive=archive_info,
cmd=cmd_args.replace(s_ins.password, '***').replace(
d_ins.password, '***') if mode == 'dest' else cmd_args.replace(
s_ins.password, '***'),
condition=condition,
mode=mode,
no_delete=no_delete,
sleep=sleep,
select_cnt=select_cnt,
insert_cnt=insert_cnt,
delete_cnt=delete_cnt,
statistics=statistics,
success=success,
error_info=error_info,
start_time=t.start,
end_time=t.end)
if not success:
raise Exception(f'{error_info}\n{statistics}')
def post(self, request):
# 参数验证
serializer = AuditWorkflowSerializer(data=request.data)
if not serializer.is_valid():
return Response(serializer.errors,
status=status.HTTP_400_BAD_REQUEST)
audit_type = request.data['audit_type']
workflow_type = request.data['workflow_type']
workflow_id = request.data['workflow_id']
audit_remark = request.data['audit_remark']
engineer = request.data['engineer']
user = Users.objects.get(username=engineer)
# 审核查询权限申请
if workflow_type == 1:
audit_status = 1 if audit_type == 'pass' else 2
if audit_remark is None:
audit_remark = ''
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_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())
raise serializers.ValidationError({'errors': msg})
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'query-priv-audit-{workflow_id}')
return Response({
'msg': 'passed'
}) if audit_type == 'pass' else Response({'msg': 'canceled'})
# 审核SQL上线申请
elif workflow_type == 2:
# SQL上线申请通过
if audit_type == 'pass':
# 权限验证
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
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(traceback.format_exc())
raise serializers.ValidationError({'errors': msg})
else:
# 开启了Pass阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Pass' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'sqlreview-pass-{workflow_id}')
return Response({'msg': 'passed'})
# SQL上线申请驳回/取消
elif audit_type == 'cancel':
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if audit_remark is None:
raise serializers.ValidationError({"errors": "终止原因不能为空"})
if can_cancel(user, workflow_id) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
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=user.username,
operator_display=user.display)
else:
Audit.add_log(audit_id=audit_id,
operation_type=2,
operation_type_desc='审批不通过',
operation_info="审批备注：{}".format(
audit_remark),
operator=user.username,
operator_display=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 serializers.ValidationError(
{"errors": "Permission Denied"})
# 删除定时执行task
if workflow_detail.status == 'workflow_timingtask':
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 将流程状态修改为人工终止流程
workflow_detail.status = 'workflow_abort'
workflow_detail.save()
except Exception as msg:
logger.error(f"取消工单报错，错误信息：{traceback.format_exc()}")
raise serializers.ValidationError({'errors': msg})
else:
# 发送取消、驳回通知，开启了Cancel阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Cancel' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.
workflow_type['sqlreview'])
if audit_detail.current_status in (
WorkflowDict.workflow_status['audit_abort'],
WorkflowDict.workflow_status['audit_reject']):
async_task(
notify_for_audit,
audit_id=audit_detail.audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'sqlreview-cancel-{workflow_id}')
return Response({'msg': 'canceled'})
# 审核数据归档申请
elif workflow_type == 3:
audit_status = 1 if audit_type == 'pass' else 2
if audit_remark is None:
audit_remark = ''
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_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=workflow_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())
raise serializers.ValidationError({'errors': msg})
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'archive-audit-{workflow_id}')
return Response({
'msg': 'passed'
}) if audit_type == 'pass' else Response({'msg': 'canceled'})