def post(self, request):
data = format_request(request)
sqlContent = data.get('sql_content').strip()
sqlSplit = []
for stmt in sqlparse.split(sqlContent):
sql = sqlparse.parse(stmt)[0]
sql_comment = sql.token_first()
if isinstance(sql_comment, sqlparse.sql.Comment):
sqlSplit.append({'comment': sql_comment.value, 'sql': sql.value.replace(sql_comment.value, '')})
else:
sqlSplit.append({'comment': '', 'sql': sql.value})
beautifySQL_list = []
try:
for row in sqlSplit:
comment = row['comment']
sql = row['sql']
res = sqlparse.parse(sql)
if res[0].tokens[0].ttype[1] == 'DML':
sqlFormat = sqlparse.format(sql, keyword_case='upper', reindent=True)
beautifySQL_list.append(comment + sqlFormat)
elif res[0].tokens[0].ttype[1] == 'DDL':
sqlFormat = sqlparse.format(sql, keyword_case='upper')
beautifySQL_list.append(comment + sqlFormat)
beautifySQL = '\n\n'.join(beautifySQL_list)
context = {'data': beautifySQL}
except Exception as err:
raise OSError(err)
context = {'errCode': 400, 'errMsg': "注释不合法, 请检查"}
return HttpResponse(json.dumps(context))
def get(self, request):
data = format_request(request)
taskid = ast.literal_eval(data.get('taskid'))
print(taskid)
result = IncepMakeExecTask.objects.filter(taskid=taskid).values_list(
'sql_content', flat=True)
return HttpResponse(json.dumps({'data': list(result)}))
def get(self, request):
data = format_request(request)
id = data.get('id')
kwargs = json.loads(PeriodicTask.objects.get(pk=id).kwargs)
backup_dir = kwargs.get('backup_dir')
mysqldump_backup_dir = os.path.join(backup_dir, 'mysqldump')
xtrabackup_backup_dir = os.path.join(backup_dir, 'xtrabackup')
paramiko_conn = ParamikoOutput(ssh_user=kwargs.get('ssh_user'),
ssh_password=kwargs.get('ssh_password'),
ssh_host=kwargs.get('ssh_host'),
ssh_port=kwargs.get('ssh_port'))
cmd = f"du -sh {mysqldump_backup_dir} {xtrabackup_backup_dir} && df -h {backup_dir}"
data = paramiko_conn.run(cmd)
if data['status'] == 0:
result = {}
for i in data['data'][:2]:
result[i.split('\t')[1]] = i.split('\t')[0]
df = [i for i in data['data'][-1].split()]
result.update({
'total_size': df[-5],
'used_size': df[-4],
'free_size': df[-3],
'used_percent (%)': int(df[-2].split('%')[0]),
'free_percent (%)': 100 - int(df[-2].split('%')[0])
})
context = {'status': 0, 'data': result}
else:
context = data
return HttpResponse(json.dumps(context))
def get(self, request):
data = format_request(request)
id = data.get('id')
show_type = data.get('type')
kwargs = json.loads(PeriodicTask.objects.get(pk=id).kwargs)
backup_dir = os.path.join(kwargs.get('backup_dir'), show_type)
cmd = f"du -sh {backup_dir}/* --time"
paramiko_conn = ParamikoOutput(ssh_user=kwargs.get('ssh_user'),
ssh_password=kwargs.get('ssh_password'),
ssh_host=kwargs.get('ssh_host'),
ssh_port=kwargs.get('ssh_port'))
data = paramiko_conn.run(cmd)
if data['status'] == 0:
result = []
for i in data['data']:
split_i = i.split('\t')
file_size = split_i[0]
file_time = split_i[1]
file_name = split_i[2]
result.append({
'file_name': file_name,
'file_size': file_size,
'file_time': file_time
})
result.reverse()
context = result
else:
context = []
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
group_id = data.get('group_id')
result = []
if group_id:
role_list = PermissionDetail.objects.annotate(
role_name=F('role__role_name'),
permission_name=F('permission__permission_name')).filter(
permission__permission_name__in=('can_approve', 'can_execute')
).values_list('role_name', 'permission_name')
for i in role_list:
role_name = i[0]
can_priv = i[1]
uid = RolesDetail.objects.annotate(uid=F('user__uid')).filter(
role__role_name=role_name).values_list(
'uid', flat=True)
data = GroupsDetail.objects.annotate(
uid=F('user__uid'),
username=F('user__username'),
email=F('user__email'),
).filter(group__group_id=group_id).filter(user__uid__in=uid).values('uid', 'username', 'email')
result.append({'priv': can_priv, 'user': list(data)})
return JsonResponse(result, safe=False)
def get(self, request):
"""
type=0：线下数据库
type=1：线上数据库
purpose=0：审核目的
purpose=1：查询目的
group: 筛选指定项目的主机
"""
data = format_request(request)
type = data.get('type')
purpose = data.get('purpose')
user_in_group = request.session.get('groups')
selected_group = data.get('selected_group')
if type and purpose:
result = InceptionHostConfigDetail.objects.annotate(comment=F('config__comment'),
).filter(config__type=type). \
filter(config__purpose=purpose). \
filter(config__is_enable=0). \
filter(group__group_id=selected_group). \
values('comment').annotate(Count('id'))
else:
result = InceptionHostConfigDetail.objects.annotate(comment=F('config__comment')
).filter(config__is_enable=0). \
filter(group__group_id__in=user_in_group). \
values('comment').annotate(Count('id'))
return JsonResponse(list(result), safe=False)
def post(self, request):
data = format_request(request)
action = data['action']
del data['csrfmiddlewaretoken']
del data['action']
if action == 'new_crontab':
ndata = dict([(k, v.replace(' ', '')) for k, v in data.items()])
crobj = schedules.crontab(**ndata)
ModelEntry.to_model_schedule(crobj)
context = {'status': 0, 'msg': '创建成功'}
elif action == 'delete_crontab':
id = data.get('id')
for i in id.split(','):
CrontabSchedule.objects.get(id=i).delete()
refresh_periodic_tasks()
context = {'status': 0, 'msg': '删除成功'}
elif action == 'edit_crontab':
# 删除无用的元素
del data['0']
CrontabSchedule.objects.filter(id=data.get('id')).update(**data)
# refresh_periodic_tasks()
context = {'status': 0, 'msg': '修改成功'}
else:
context = {'status': 2, 'msg': '操作失败'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
sql_content = data.get('sql_content').strip()
sql_split = []
for stmt in sqlparse.split(sql_content):
sql = sqlparse.parse(stmt)[0]
sql_comment = sql.token_first()
if isinstance(sql_comment, sqlparse.sql.Comment):
sql_split.append({
'comment':
sql_comment.value,
'sql':
sql.value.replace(sql_comment.value, '')
})
else:
sql_split.append({'comment': '', 'sql': sql.value})
beautify_sql_list = []
try:
for row in sql_split:
comment = row['comment']
sql = row['sql']
res = sqlparse.parse(sql)
if res[0].tokens[0].ttype[1] == 'DDL':
sql_format = sqlparse.format(sql)
beautify_sql_list.append(comment + sql_format)
else:
sql_format = sqlparse.format(sql, reindent=True)
beautify_sql_list.append(comment + sql_format)
context = {'data': '\n\n'.join(beautify_sql_list)}
except Exception as err:
context = {'status': 2, 'msg': '注释或语法错误'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
tasks = data.get('tasks')
# 获取任务工单总数
query = f"select id,count(x.id) as num from" \
f"(select id from auditsql_work_order where tasks='{tasks}' group by title) as x"
for row in AuditContents.objects.raw(query):
count_num = row.num
query_finish = f"select id, " \
f"max(if(envi_desc = 0 , progress , - 1)) as test, " \
f"max(if(envi_desc = 1 , progress , - 1)) as staging, " \
f"max(if(envi_desc = 2 , progress , - 1)) as product " \
f"from auditsql_work_order " \
f"where tasks = '{tasks}' group by title"
status = []
for row in AuditContents.objects.raw(query_finish):
status.append([row.test, row.staging, row.product])
# 计算完成的数量
i = 0
finish_status = ['4', '6']
for x in status:
if all(False for y in x if y not in finish_status):
i += 1
# 关闭状态的也计入已完成状态
if '5' in x:
i += 1
finish_value = '/'.join((str(i), str(count_num)))
return JsonResponse({'num': finish_value}, safe=False)
def post(self, request):
data = format_request(request)
id = data.get('id')
for i in id.split(','):
PeriodicTask.objects.get(pk=i).delete()
context = {'status': 0, 'msg': '删除成功'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
sql_content = data.get('sql_content').strip()
sql_split = []
for stmt in sqlparse.split(sql_content):
sql = sqlparse.parse(stmt)[0]
sql_comment = sql.token_first()
if isinstance(sql_comment, sqlparse.sql.Comment):
sql_split.append({
'comment':
sql_comment.value,
'sql':
sql.value.replace(sql_comment.value, '')
})
else:
sql_split.append({'comment': '', 'sql': sql.value})
beautify_sql_list = []
for row in sql_split:
comment = row['comment']
sql = row['sql']
res = sqlparse.parse(sql)
syntax_type = res[0].token_first().ttype.__str__()
if syntax_type == 'Token.Keyword.DDL':
sql_format = sqlparse.format(sql)
beautify_sql_list.append(comment + sql_format)
elif syntax_type == 'Token.Keyword.DML':
sql_format = sqlparse.format(sql, reindent=True)
beautify_sql_list.append(comment + sql_format)
elif syntax_type == 'Token.Keyword':
beautify_sql_list.append(comment + sql)
context = {'data': '\n\n'.join(beautify_sql_list)}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
querys = data.get('contents')
host = data.get('host')
database = data.get('database')
mysql_query = MySQLQuery(querys, host, database)
result = mysql_query.query(request)
return JsonResponse(result, safe=False)
def post(self, request):
data = format_request(request)
id = data.get('id')
obj = IncepMakeExecTask.objects.get(id=id)
host = obj.dst_host
database = obj.dst_database
if obj.exec_status in ('0', '3', '4'):
context = {'status': 2, 'msg': '请不要重复操作'}
else:
# 获取回滚语句
rollback_sql = GetBackupApi({
'backupdbName': obj.backup_dbname,
'sequence': obj.sequence
}).get_rollback_statement()
if rollback_sql is None:
context = {'status': 2, 'msg': '没有找到备份记录，回滚失败'}
else:
incep_of_audit = IncepSqlCheck(rollback_sql, obj.dst_host,
obj.dst_database,
request.user.username)
result = incep_of_audit.make_sqlsha1()[1]
rollback_sql = result['SQL'] + ';'
rollback_sqlsha1 = result['sqlsha1']
# 将任务进度设置为：回滚中
obj.exec_status = 3
obj.rollback_sqlsha1 = rollback_sqlsha1
obj.save()
if result['sqlsha1']:
# 异步执行SQL任务
r = incep_async_tasks.delay(user=request.user.username,
id=id,
host=host,
database=database,
sql=rollback_sql,
sqlsha1=rollback_sqlsha1,
exec_status=4)
task_id = r.task_id
# 将celery task_id写入到表
obj.celery_task_id = task_id
obj.save()
# 获取OSC执行进度
get_osc_percent.delay(task_id=task_id)
context = {'status': 0, 'msg': '提交处理，请查看输出'}
else:
incep_async_tasks.delay(user=request.user.username,
id=id,
sql=rollback_sql,
host=host,
database=database,
exec_status=4)
context = {'status': 0, 'msg': '提交处理，请查看输出'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
form = OlDataExportForm(data)
if form.is_valid():
context = form.is_save(request)
else:
error = form.errors.as_text()
context = {'status': 2, 'msg': error}
return HttpResponse(json.dumps(context))
def get(self, request):
result = format_request(request)
host, port, schema = result.get('schema').split('-')
if len(schema.split('.')) == 2:
data = GetTableMetaInfo(host, port, schema).get_stru_info()
context = {'status': 0, 'data': data}
else:
context = {'status': 2, 'msg': ''}
return JsonResponse(context, safe=False)
def post(self, request):
data = format_request(request)
tasks = data.get('tasks')
query = f"select id,title,proposer,tasks,b.mobile, " \
f"max(if(envi_desc=0, progress, -1)) as test, " \
f"max(if(envi_desc=1, progress, -1)) as staging, " \
f"max(if(envi_desc=2, progress, -1)) as product " \
f"from auditsql_work_order a join auditsql_useraccount b on a.proposer = b.username " \
f"where tasks='{tasks}' group by title order by id desc"
result = []
for row in AuditContents.objects.raw(query):
result.append({
'id': row.id,
'title': row.title,
'mobile': row.mobile,
'proposer': row.proposer,
'tasks': row.tasks,
'test': row.test,
'staging': row.staging,
'product': row.product
})
# 获取今天为周几
today = datetime.now().weekday() + 1
# 如果是周二，通知预发布环境未完成工单的开发
analyze_result = []
if today == 2:
for row in result:
if not row['staging'] in ('4', '5', '6'):
# 剔除test环境未关闭的工单，已关闭的不做处理
if row['test'] != '5':
analyze_result.append(row)
if analyze_result:
ding_notice_pull.delay(analyze_result, today)
context = {'status': 0, 'msg': '已钉'}
else:
context = {'status': 2, 'msg': '预发布环境任务已完成，无法钉'}
# 如果是周三，通知生产环境布未完成工单的开发
elif today == 3:
for row in result:
if not row['product'] in ('4', '5', '6'):
# 剔除test环境未关闭的工单，已关闭的不做处理
if row['staging'] not in ('-1', '5'):
analyze_result.append(row)
if analyze_result:
ding_notice_pull.delay(analyze_result, today)
context = {'status': 0, 'msg': '已钉'}
else:
context = {'status': 2, 'msg': '生产环境任务已完成，无法钉'}
else:
context = {'status': 2, 'msg': '只能在指定周二或周三使用该功能'}
return HttpResponse(json.dumps(context))
def get(self, request):
data = format_request(request)
form = OnlineAuditRecordForm(data)
result = {}
if form.is_valid():
cleaned_data = form.cleaned_data
envi_desc = cleaned_data.get('envi_desc')
limit_size = cleaned_data.get('limit_size')
offset_size = cleaned_data.get('offset_size')
search_content = cleaned_data.get('search_content')
query = AuditContents.objects.filter(envi_desc=envi_desc).annotate(
progress_value=Case(
When(progress='0', then=Value('待批准')),
When(progress='1', then=Value('未批准')),
When(progress='2', then=Value('已批准')),
When(progress='3', then=Value('处理中')),
When(progress='4', then=Value('已完成')),
When(progress='5', then=Value('已关闭')),
When(progress='6', then=Value('已勾住')),
output_field=CharField(),
),
progress_color=Case(
When(progress__in=('0',), then=Value('btn-primary')),
When(progress__in=('2',), then=Value('btn-warning')),
When(progress__in=('1', '5'), then=Value('btn-danger')),
When(progress__in=('3',), then=Value('btn-info')),
When(progress__in=('4',), then=Value('btn-success')),
When(progress__in=('6',), then=Value('btn-default')),
output_field=CharField(),
),
remark_desc=Case(
When(remark=0, then=Value('周三上线')),
When(remark=1, then=Value('紧急上线')),
When(remark=2, then=Value('数据修复')),
output_field=CharField(),
),
)
if search_content:
obj = query.filter(Q(tasks__icontains=search_content) | Q(title__icontains=search_content) | Q(
proposer__icontains=search_content) | Q(envi_desc__icontains=search_content) | Q(
host__icontains=search_content) | Q(host__icontains=search_content) | Q(
database__icontains=search_content) | Q(contents__icontains=search_content))
else:
obj = query
ol_total = obj.count()
ol_records = obj.values('progress_color', 'tasks', 'host', 'operate_type',
'database', 'progress_value', 'id', 'envi_desc',
'title', 'proposer', 'operator',
'created_at', 'remark_desc'
).order_by('-created_at')[offset_size:limit_size]
result = {'total': ol_total, 'rows': list(ol_records)}
return JsonResponse(result, safe=False)
def get(self, request):
result = format_request(request)
envi = result.get('envi').split(',')
host, port = result.get('hostname').split(',')
obj = MysqlSchemaInfo.objects.filter(envi__in=envi,
host=host,
port=port).values_list('schema',
flat=True)
schema = tuple(obj)
context = GetTableMetaInfo(host, port, schema).get_offline_tables()
return JsonResponse(context, safe=False)
def post(self, request):
"""线上审核内容提交处理"""
data = format_request(request)
form = OnlineAuditForm(data)
if form.is_valid():
context = form.is_save(request)
return HttpResponse(json.dumps(context))
else:
error = form.errors.as_text()
context = {'status': 2, 'msg': error}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
host = data['host']
obj = InceptionHostConfig.objects.get(host=host)
result = check_mysql_conn(obj.user, host, obj.password, obj.port)
if result['status'] == 'INFO':
db_list = GetSchemaInfo(host).get_values()
context = {'status': 0, 'msg': '', 'data': db_list}
else:
context = {'status': 2, 'msg': f'获取列表失败，不能连接到mysql服务器：{host}'}
return HttpResponse(json.dumps(context))
def get(self, request):
result = format_request(request)
envi = result.get('envi').split(',')
is_master = result.get('is_master')
data = MysqlSchemaInfo.objects.filter(
envi__in=envi,
is_master=is_master).filter(comment__isnull=False).values(
'host', 'port', 'comment').distinct()
return JsonResponse(list(data), safe=False)
def post(self, request):
data = format_request(request)
host = data['host']
obj = InceptionHostConfig.objects.get(comment=host)
status, msg = check_db_account(obj.user, obj.host, obj.password,
obj.port)
if status:
db_list = GetSchemaInfo(host).get_values()
context = {'status': 0, 'msg': '', 'data': db_list}
else:
context = {'status': 2, 'msg': msg}
return HttpResponse(json.dumps(context))
def get(self, request):
data = format_request(request)
config_type = data.get('type')
user_in_group = request.session.get('groups')
if config_type:
result = InceptionHostConfigDetail.objects.annotate(host=F('config__host'),
comment=F('config__comment')).filter(
config__type=config_type).filter(group__group_id__in=user_in_group).values('host', 'comment')
else:
result = InceptionHostConfigDetail.objects.annotate(host=F('config__host'),
comment=F('config__comment')).filter(
group__group_id__in=user_in_group).values('host', 'comment')
return JsonResponse(list(result), safe=False)
def get(self, request):
data = format_request(request)
form = OlAuditRecordForm(data)
result = {}
if form.is_valid():
cleaned_data = form.cleaned_data
limit_size = cleaned_data.get('limit_size')
offset_size = cleaned_data.get('offset_size')
search_content = cleaned_data.get('search_content')
user_in_group = request.session['groups']
query = AuditContents.objects.all().annotate(
progress_value=Case(
When(progress='0', then=Value('待批准')),
When(progress='1', then=Value('未批准')),
When(progress='2', then=Value('已批准')),
When(progress='3', then=Value('处理中')),
When(progress='4', then=Value('已完成')),
When(progress='5', then=Value('已关闭')),
output_field=CharField(),
),
progress_color=Case(
When(progress__in=('0',), then=Value('btn-primary')),
When(progress__in=('2',), then=Value('btn-warning')),
When(progress__in=('1', '5'), then=Value('btn-danger')),
When(progress__in=('3',), then=Value('btn-info')),
When(progress__in=('4',), then=Value('btn-success')),
output_field=CharField(),
),
type=Case(
When(audit_type='0', then=Value('数据变更')),
When(audit_type='1', then=Value('数据导出')),
output_field=CharField(),
),
group_name=F('group__group_name'),
group_id=F('group__group_id'),
)
if search_content:
obj = query.filter(contents__icontains=search_content)
else:
obj = query
ol_total = obj.filter(group_id__in=user_in_group).count()
ol_records = obj.filter(group_id__in=user_in_group).values('group_name', 'progress_color', 'type',
'progress_value', 'id', 'group_id', 'title',
'proposer', 'operator', 'verifier', 'created_at'
).order_by('-created_at')[offset_size:limit_size]
result = {'total': ol_total, 'rows': list(ol_records)}
return JsonResponse(result, safe=False)
def post(self, request):
data = format_request(request).get('schema')
host, port, schema = data.split(',')
status, msg = check_db_conn_status(host, port)
if status:
table_list = GetTableMetaInfo(host, port, schema).get_column_info()
context = {'status': 0, 'msg': '', 'data': table_list}
else:
context = {
'status': 2,
'msg': f'无法连接到数据库，请联系DBA\n主机: {host}\n端口: {port}'
}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
taskid = data.get('taskid')
query = f"select * from auditsql_incep_tasks where taskid={taskid} order by id asc"
key = ast.literal_eval(taskid)
if 'run' == cache.get(key):
context = {'status': 1, 'msg': '当前任务正在运行，请不要重复执行'}
else:
cache.set(key, 'run', timeout=600)
incep_multi_tasks.delay(username=request.user.username,
query=query,
key=key)
context = {'status': 1, 'msg': '任务已提交，请查看输出'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
host, port, schema = data.get('schema').split('-')
if len(schema.split('.')) == 2:
schema = schema.split('.')[0]
querys = data.get('contents')
mysql_query = MySQLQuery(querys=querys,
host=host,
port=port,
schema=schema,
rw='rw',
envi=1)
result = mysql_query.query(request)
return JsonResponse(result, safe=False)
def post(self, request):
data = format_request(request)
tasks = data.get('tasks')
expire_time = data.get('expire_time')
action = data.get('action')
id = data.get('id')
if action == 'new_tasks':
if AuditTasks.objects.filter(tasks=tasks).first():
context = {'status': 2, 'msg': '记录已存在，不能重复创建'}
else:
AuditTasks.objects.create(tasks=tasks, expire_time=expire_time, username=request.user.displayname)
context = {'status': 0, 'msg': '创建成功'}
elif action == 'delete_tasks':
for i in id.split(','):
AuditTasks.objects.get(pk=i).delete()
context = {'status': 0, 'msg': '删除成功'}
return HttpResponse(json.dumps(context))
def post(self, request):
data = format_request(request)
form = PrivModifyForm(data)
context = {}
if form.is_valid():
cleaned_data = form.cleaned_data
db_host = cleaned_data.get('db_host')
user = cleaned_data.get('user')
action = cleaned_data.get('action')
host = data.get('host')
password = data.get('password')
schema = data.get('schema')
privileges = data.get('privileges')
username = user + '@' + '"' + host + '"'
data = InceptionHostConfig.objects.get(comment=db_host)
protection_user = []
if len(list(data.protection_user.split(','))) == 1:
protection_user = data.protection_user.split(',')
protection_user.append('')
else:
protection_user = data.protection_user.split(',')
protection_user_tuple = tuple([x.strip() for x in protection_user])
if user in protection_user_tuple:
context = {'status': 1, 'msg': f'该用户({user})已被保护，无法操作'}
else:
mysql_user_mamager = MysqlUserManager(locals())
if action == "modify_privileges":
context = mysql_user_mamager.priv_modify()
elif action == "new_host":
context = mysql_user_mamager.new_host()
elif action == 'delete_host':
context = mysql_user_mamager.delete_host()
elif action == 'new_user':
context = mysql_user_mamager.new_host()
return HttpResponse(json.dumps(context))
else:
error = form.errors.as_text()
context = {'status': 2, 'msg': error}
return HttpResponse(json.dumps(context))
def get(self, request):
result = format_request(request)
envi = result.get('envi').split(',')
is_master = result.get('is_master')
# host存在，非生产环境
if result.get('host'):
host, port = result.get('host').split(',')
data = MysqlSchemaInfo.objects.filter(envi__in=envi,
host=host,
port=port).values(
'id', 'host', 'port',
'schema', 'comment')
else:
# 否则生产环境
data = MysqlSchemaInfo.objects.filter(envi__in=envi,
is_master=is_master).values(
'id', 'host', 'port',
'schema', 'comment')
return JsonResponse(list(data), safe=False)