def optimize_soar(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
sql = request.POST.get('sql')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if not (instance_name and db_name and sql):
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result), content_type='application/json')
try:
user_instances(request.user, type='all', db_type='mysql').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例'
return HttpResponse(json.dumps(result), content_type='application/json')
# 检查测试实例的连接信息和soar程序路径
soar_test_dsn = SysConfig().get('soar_test_dsn')
soar_path = SysConfig().get('soar')
if not (soar_path and soar_test_dsn):
result['status'] = 1
result['msg'] = '请配置soar_path和test_dsn！'
return HttpResponse(json.dumps(result), content_type='application/json')
# 目标实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(user=instance_info.user,
pwd=instance_info.raw_password,
host=instance_info.host,
port=instance_info.port,
db=db_name)
# 提交给soar获取分析报告
soar = Soar()
# 准备参数
args = {"online-dsn": online_dsn,
"test-dsn": soar_test_dsn,
"allow-online-as-test": "false",
"report-type": "markdown",
"query": sql.strip().replace('"', '\\"').replace('`', '').replace('\n', ' ')
}
# 参数检查
args_check_result = soar.check_args(args)
if args_check_result['status'] == 1:
return HttpResponse(json.dumps(args_check_result), content_type='application/json')
# 参数转换
cmd_args = soar.generate_args2cmd(args, shell=True)
# 执行命令
try:
stdout, stderr = soar.execute_cmd(cmd_args, shell=True).communicate()
result['data'] = stdout if stdout else stderr
except RuntimeError as e:
result['status'] = 1
result['msg'] = str(e)
return HttpResponse(json.dumps(result), content_type='application/json')
def soar(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
sql = request.POST.get('sql')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if not (instance_name and db_name and sql):
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_instances(request.user, 'all').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql = sql.strip().replace('"', '\\"').replace('`', '').replace('\n', ' ')
# 目标实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(
user=instance_info.user,
pwd=instance_info.raw_password,
host=instance_info.host,
port=instance_info.port,
db=db_name)
# 获取测试实例的连接信息和soar程序路径
soar_cfg = Soar()
test_dsn = soar_cfg.soar_test_dsn
soar_path = soar_cfg.soar_path
if not (soar_path and test_dsn):
result['status'] = 1
result['msg'] = '请配置soar_path和test_dsn！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 提交给soar获取分析报告
try:
p = subprocess.Popen(
soar_path + ' -allow-online-as-test=false -report-type=markdown' +
' -query "{}" -online-dsn "{}" -test-dsn "{}" '.format(
sql.strip(), online_dsn, test_dsn),
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
shell=True,
universal_newlines=True)
stdout, stderr = p.communicate()
result['data'] = stdout
except Exception:
logger.error(traceback.format_exc())
result['data'] = 'soar运行报错，请检查相关日志'
return HttpResponse(json.dumps(result), content_type='application/json')
def optimize_sqladvisor(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
verbose = request.POST.get('verbose', 1)
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if sql_content is None or instance_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result), content_type='application/json')
try:
user_instances(request.user, type='all', db_type='mysql').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例！'
return HttpResponse(json.dumps(result), content_type='application/json')
# 检查sqladvisor程序路径
sqladvisor_path = SysConfig().get('sqladvisor')
if sqladvisor_path is None:
result['status'] = 1
result['msg'] = '请配置SQLAdvisor路径！'
return HttpResponse(json.dumps(result), content_type='application/json')
# 取出实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
# 提交给sqladvisor获取分析报告
sqladvisor = SQLAdvisor()
# 准备参数
args = {"h": instance_info.host,
"P": instance_info.port,
"u": instance_info.user,
"p": instance_info.raw_password,
"d": db_name,
"v": verbose,
"q": sql_content.strip().replace('"', '\\"').replace('`', '').replace('\n', ' ')
}
# 参数检查
args_check_result = sqladvisor.check_args(args)
if args_check_result['status'] == 1:
return HttpResponse(json.dumps(args_check_result), content_type='application/json')
# 参数转换
cmd_args = sqladvisor.generate_args2cmd(args, shell=True)
# 执行命令
try:
stdout, stderr = sqladvisor.execute_cmd(cmd_args, shell=True).communicate()
result['data'] = f'{stdout}{stderr}'
except RuntimeError as e:
result['status'] = 1
result['msg'] = str(e)
return HttpResponse(json.dumps(result), content_type='application/json')
def sqladvisor(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
verbose = request.POST.get('verbose')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if sql_content is None or instance_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = sql_content.strip()
try:
user_instances(request.user, 'all').get(instance_name=instance_name)
except Exception:
result['status'] = 1
result['msg'] = '你所在组未关联该实例！'
return HttpResponse(json.dumps(result),
content_type='application/json')
if verbose is None or verbose == '':
verbose = 1
# 取出实例的连接信息
instance_info = Instance.objects.get(instance_name=instance_name)
# 提交给sqladvisor获取审核结果
sqladvisor_path = SysConfig().sys_config.get('sqladvisor')
sql_content = sql_content.strip().replace('"',
'\\"').replace('`', '').replace(
'\n', ' ')
try:
p = subprocess.Popen(
sqladvisor_path +
' -h "%s" -P "%s" -u "%s" -p "%s\" -d "%s" -v %s -q "%s"' %
(str(instance_info.host), str(
instance_info.port), str(instance_info.user),
str(Prpcrypt().decrypt(instance_info.password), ), str(db_name),
verbose, sql_content),
stdin=subprocess.PIPE,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
shell=True,
universal_newlines=True)
stdout, stderr = p.communicate()
result['data'] = stdout
except Exception:
logger.error(traceback.format_exc())
result['data'] = 'sqladvisor运行报错，请检查日志'
return HttpResponse(json.dumps(result), content_type='application/json')
def edit(request):
"""编辑/录入数据库"""
instance_id = request.POST.get('instance_id', 0)
db_name = request.POST.get('db_name')
owner = request.POST.get('owner', '')
remark = request.POST.get('remark', '')
if not all([db_name]):
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
try:
owner_display = Users.objects.get(username=owner).display
except Users.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '负责人不存在', 'data': []})
# 更新或者录入信息
InstanceDatabase.objects.update_or_create(
instance=instance,
db_name=db_name,
defaults={"owner": owner, "owner_display": owner_display, "remark": remark})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def kill_session(request):
instance_name = request.POST.get('instance_name')
thread_ids = request.POST.get('ThreadIDs')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
instance = user_instances(request.user, db_type=['mysql']).get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '你所在组未关联该实例', 'data': []}
return HttpResponse(json.dumps(result), content_type='application/json')
# 判断是RDS还是其他实例
if AliyunRdsConfig.objects.filter(instance=instance, is_enable=True).exists():
result = aliyun_kill_session(request)
else:
thread_ids = thread_ids.replace('[', '').replace(']', '')
engine = get_engine(instance=instance)
sql = "select concat('kill ', id, ';') from information_schema.processlist where id in ({});".format(thread_ids)
all_kill_sql = engine.query('information_schema', sql)
kill_sql = ''
for row in all_kill_sql.rows:
kill_sql = kill_sql + row[0]
engine.execute('information_schema', kill_sql)
# 返回查询结果
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def create(request):
"""创建数据库"""
instance_id = request.POST.get('instance_id', 0)
db_name = request.POST.get('db_name')
owner = request.POST.get('owner', '')
remark = request.POST.get('remark', '')
if not all([db_name]):
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
try:
owner_display = Users.objects.get(username=owner).display
except Users.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '负责人不存在', 'data': []})
engine = get_engine(instance=instance)
exec_result = engine.execute(db_name='information_schema', sql=f"create database {db_name};")
if exec_result.error:
return JsonResponse({'status': 1, 'msg': exec_result.error})
# 保存到数据库
else:
InstanceDatabase.objects.create(
instance=instance, db_name=db_name, owner=owner, owner_display=owner_display, remark=remark)
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def lock(request):
"""锁定/解锁账号"""
instance_id = request.POST.get('instance_id', 0)
user_host = request.POST.get('user_host')
is_locked = request.POST.get('is_locked')
lock_sql = ''
if not all([user_host]):
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
# escape
user_host = MySQLdb.escape_string(user_host).decode('utf-8')
if is_locked == 'N':
lock_sql = f"ALTER USER {user_host} ACCOUNT LOCK;"
elif is_locked == 'Y':
lock_sql = f"ALTER USER {user_host} ACCOUNT UNLOCK;"
engine = get_engine(instance=instance)
exec_result = engine.execute(db_name='mysql', sql=lock_sql)
if exec_result.error:
return JsonResponse({'status': 1, 'msg': exec_result.error})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def instance_param(request):
# 获取用户关联实例列表
instances = user_instances(request.user,
type='all',
db_type=['mysql', 'inception', 'goinception'])
context = {'tab': 'param_tab', 'instances': instances}
return render(request, 'param.html', context)
def edit(request):
"""修改、录入数据库账号"""
instance_id = request.POST.get('instance_id', 0)
user = request.POST.get('user')
host = request.POST.get('host')
password = request.POST.get('password')
remark = request.POST.get('remark', '')
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
if not all([user, host]):
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
# 保存到数据库
if password:
InstanceAccount.objects.update_or_create(
instance=instance, user=user, host=host,
defaults={
"password": password,
"remark": remark
}
)
else:
InstanceAccount.objects.update_or_create(
instance=instance, user=user, host=host,
defaults={
"remark": remark
}
)
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def binlog2sql(request):
# 获取实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
return render(request, 'binlog2sql.html', {'instances': instances})
def schemasync(request):
# 获取实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
return render(request, 'schemasync.html', {'instances': instances})
def delete(request):
"""删除账号"""
instance_id = request.POST.get('instance_id', 0)
user_host = request.POST.get('user_host')
user = request.POST.get('user')
host = request.POST.get('host')
if not all([user_host]):
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
# escape
user_host = MySQLdb.escape_string(user_host).decode('utf-8')
engine = get_engine(instance=instance)
exec_result = engine.execute(db_name='information_schema', sql=f"DROP USER {user_host};")
if exec_result.error:
return JsonResponse({'status': 1, 'msg': exec_result.error})
# 删除数据库对应记录
else:
InstanceAccount.objects.filter(instance=instance, user=user, host=host).delete()
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def process(request):
instance_name = request.POST.get('instance_name')
command_type = request.POST.get('command_type')
try:
instance = user_instances(request.user, db_type=['mysql']).get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '你所在组未关联该实例', 'data': []}
return HttpResponse(json.dumps(result), content_type='application/json')
base_sql = "select id, user, host, db, command, time, state, ifnull(info,'') as info from information_schema.processlist"
# 判断是RDS还是其他实例
if AliyunRdsConfig.objects.filter(instance=instance, is_enable=True).exists():
result = aliyun_process_status(request)
else:
if command_type == 'All':
sql = base_sql + ";"
elif command_type == 'Not Sleep':
sql = "{} where command<>'Sleep';".format(base_sql)
else:
sql = "{} where command= '{}';".format(base_sql, command_type)
query_engine = get_engine(instance=instance)
query_result = query_engine.query('information_schema', sql)
if not query_result.error:
processlist = query_result.to_dict()
result = {'status': 0, 'msg': 'ok', 'rows': processlist}
else:
result = {'status': 1, 'msg': query_result.error}
# 返回查询结果
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def databases(request):
"""获取实例数据库列表"""
instance_id = request.POST.get('instance_id')
saved = True if request.POST.get('saved') == 'true' else False # 平台是否保存
if not instance_id:
return JsonResponse({'status': 0, 'msg': '', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
# 获取已录入数据库
cnf_dbs = dict()
for db in InstanceDatabase.objects.filter(
instance=instance).values('id', 'db_name', 'owner', 'owner_display', 'remark'):
db['saved'] = True
cnf_dbs[f"{db['db_name']}"] = db
# 获取所有数据库
sql_get_db = """SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'test', 'sys');"""
query_engine = get_engine(instance=instance)
query_result = query_engine.query('information_schema', sql_get_db, close_conn=False)
if not query_result.error:
dbs = query_result.rows
# 获取数据库关联用户信息
rows = []
for db in dbs:
db_name = db[0]
sql_get_bind_users = f"""select group_concat(distinct(GRANTEE)),TABLE_SCHEMA
from information_schema.SCHEMA_PRIVILEGES
where TABLE_SCHEMA='{db_name}'
group by TABLE_SCHEMA;"""
bind_users = query_engine.query('information_schema', sql_get_bind_users, close_conn=False).rows
row = {
'db_name': db_name,
'charset': db[1],
'collation': db[2],
'grantees': bind_users[0][0].split(',') if bind_users else [],
'saved': False
}
# 合并数据
if db_name in cnf_dbs.keys():
row = dict(row, **cnf_dbs[db_name])
rows.append(row)
# 过滤参数
if saved:
rows = [row for row in rows if row['saved']]
result = {'status': 0, 'msg': 'ok', 'rows': rows}
else:
result = {'status': 1, 'msg': query_result.error}
# 关闭连接
query_engine.close()
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def optimize_sqltuning(request):
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
sqltext = request.POST.get('sql_content')
option = request.POST.getlist('option[]')
sqltext = sqlparse.format(sqltext, strip_comments=True)
sqltext = sqlparse.split(sqltext)[0]
if re.match(r"^select|^show|^explain", sqltext, re.I) is None:
result = {'status': 1, 'msg': '只支持查询SQL！', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
user_instances(request.user).get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '你所在组未关联该实例！', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
# escape
db_name = MySQLdb.escape_string(db_name).decode('utf-8')
sql_tunning = SqlTuning(instance_name=instance_name,
db_name=db_name,
sqltext=sqltext)
result = {'status': 0, 'msg': 'ok', 'data': {}}
if 'sys_parm' in option:
basic_information = sql_tunning.basic_information()
sys_parameter = sql_tunning.sys_parameter()
optimizer_switch = sql_tunning.optimizer_switch()
result['data']['basic_information'] = basic_information
result['data']['sys_parameter'] = sys_parameter
result['data']['optimizer_switch'] = optimizer_switch
if 'sql_plan' in option:
plan, optimizer_rewrite_sql = sql_tunning.sqlplan()
result['data']['optimizer_rewrite_sql'] = optimizer_rewrite_sql
result['data']['plan'] = plan
if 'obj_stat' in option:
result['data']['object_statistics'] = sql_tunning.object_statistics()
if 'sql_profile' in option:
session_status = sql_tunning.exec_sql()
result['data']['session_status'] = session_status
# 关闭连接
sql_tunning.engine.close()
result['data']['sqltext'] = sqltext
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def sqlquery(request):
# 获取实例支持查询的标签id
tag_id = InstanceTag.objects.get(tag_code='can_read').id
# 获取用户关联实例列表
instances = [slave for slave in user_instances(request.user, type='all', db_type='all', tags=[tag_id])]
context = {'instances': instances}
return render(request, 'sqlquery.html', context)
def sqlquery(request):
# 获取用户关联实例列表
instances = [
slave.instance_name for slave in user_instances(request.user, 'slave')
]
context = {'instances': instances}
return render(request, 'sqlquery.html', context)
def sqlanalyze(request):
"""SQL分析页面"""
# 获取实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
return render(request, 'sqlanalyze.html', {'instances': instances})
def sqlquery(request):
# 获取用户关联实例列表
instances = [
slave
for slave in user_instances(request.user, type='slave', db_type='all')
]
context = {'instances': instances}
return render(request, 'sqlquery.html', context)
def sqlanalyze(request):
"""
SQL分析页面
:param request:
:return:
"""
# 获取实例列表
instances = [instance.instance_name for instance in user_instances(request.user, 'all')]
return render(request, 'sqlanalyze.html', {'instances': instances})
def users(request):
"""获取实例用户列表"""
instance_id = request.POST.get('instance_id')
saved = True if request.POST.get('saved') == 'true' else False # 平台是否保存
if not instance_id:
return JsonResponse({'status': 0, 'msg': '', 'data': []})
try:
instance = user_instances(request.user, db_type=['mysql']).get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '你所在组未关联该实例', 'data': []})
# 获取已录入用户
cnf_users = dict()
for user in InstanceAccount.objects.filter(instance=instance).values('id', 'user', 'host', 'remark'):
user['saved'] = True
cnf_users[f"`{user['user']}`@`{user['host']}`"] = user
# 获取所有用户
query_engine = get_engine(instance=instance)
server_version = query_engine.server_version
# MySQL 5.7.6版本起支持ACCOUNT LOCK
if server_version >= (5, 7, 6):
sql_get_user = "******"
else:
sql_get_user = "******"
query_result = query_engine.query('mysql', sql_get_user)
if not query_result.error:
db_users = query_result.rows
# 获取用户权限信息
rows = []
for db_user in db_users:
user_host = db_user[0]
user_priv = query_engine.query('mysql', 'show grants for {};'.format(user_host), close_conn=False).rows
row = {
'user_host': user_host,
'user': db_user[1],
'host': db_user[2],
'privileges': user_priv,
'saved': False,
'is_locked': db_user[3] if server_version >= (5, 7, 6) else None
}
# 合并数据
if user_host in cnf_users.keys():
row = dict(row, **cnf_users[user_host])
rows.append(row)
# 过滤参数
if saved:
rows = [row for row in rows if row['saved']]
result = {'status': 0, 'msg': 'ok', 'rows': rows}
else:
result = {'status': 1, 'msg': query_result.error}
# 关闭连接
query_engine.close()
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def archive(request):
"""归档列表页面"""
# 获取资源组
group_list = user_groups(request.user)
ins_list = user_instances(request.user, db_type=['mysql'])
return render(request, 'archive.html', {
'group_list': group_list,
'ins_list': ins_list
})
def dbdiagnostic(request):
# 获取用户关联实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
context = {'tab': 'process', 'instances': instances}
return render(request, 'dbdiagnostic.html', context)
def sqladvisor(request):
# 获取用户关联实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
context = {'instances': instances}
return render(request, 'sqladvisor.html', context)
def slowquery(request):
# 获取用户关联实例列表
instances = [
instance.instance_name
for instance in user_instances(request.user, 'all')
]
context = {'tab': 'slowquery', 'instances': instances}
return render(request, 'slowquery.html', context)
def optimize_sqltuningadvisor(request):
"""
sqltuningadvisor工具获取优化报告
:param request:
:return:
"""
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('schema_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 服务器端参数验证
if sql_content is None or instance_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
instance = user_instances(
request.user).get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
# 不删除注释语句，已获取加hints的SQL优化建议，进行语法判断，执行第一条有效sql
sql_content = sqlparse.format(sql_content.strip(), strip_comments=False)
# 对单引号加转义符,支持plsql语法
sql_content = sql_content.replace("'", "''")
try:
sql_content = sqlparse.split(sql_content)[0]
except IndexError:
result['status'] = 1
result['msg'] = '没有有效的SQL语句'
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
# 过滤非Oracle语句
if not instance.db_type == 'oracle':
result['status'] = 1
result['msg'] = 'SQLTuningAdvisor仅支持oracle数据库的检查'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 执行获取优化报告
query_engine = get_engine(instance=instance)
sql_result = query_engine.sqltuningadvisor(str(db_name),
sql_content).to_sep_dict()
result['data'] = sql_result
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def user_all_instances(request):
"""获取用户所有实例列表（通过资源组间接关联）"""
user = request.user
type = request.GET.get('type')
db_type = request.GET.getlist('db_type[]')
tag_codes = request.GET.getlist('tag_codes[]')
instances = user_instances(user, type, db_type, tag_codes).values('id', 'type', 'db_type', 'instance_name')
rows = [row for row in instances]
result = {'status': 0, 'msg': 'ok', "data": rows}
return HttpResponse(json.dumps(result), content_type='application/json')
def slowquery(request):
"""SQL慢日志页面"""
# 获取用户关联实例列表
instances = [
instance.instance_name for instance in user_instances(
request.user, type='all', db_type='mysql')
]
context = {'tab': 'slowquery', 'instances': instances}
return render(request, 'slowquery.html', context)
def archive(request):
"""归档列表页面"""
# 获取资源组
group_list = user_groups(request.user)
ins_list = user_instances(request.user, db_type=['mysql']).order_by(
Convert('instance_name', 'gbk').asc())
return render(request, 'archive.html', {
'group_list': group_list,
'ins_list': ins_list
})
