def table_list(request):
"""数据字典获取表列表"""
instance_name = request.GET.get('instance_name', '')
db_name = request.GET.get('db_name', '')
db_type = request.GET.get('db_type', '')
if instance_name and db_name:
if db_type == 'mysql':
try:
data = {}
instance = Instance.objects.get(instance_name=instance_name,
db_type='mysql')
query_engine = get_engine(instance=instance)
# escape
db_name = MySQLdb.escape_string(db_name).decode('utf-8')
sql = f"""SELECT
TABLE_NAME,
TABLE_COMMENT
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA='{db_name}';"""
result = query_engine.query(db_name=db_name, sql=sql)
for row in result.rows:
table_name, table_cmt = row[0], row[1]
if table_name[0] not in data:
data[table_name[0]] = list()
data[table_name[0]].append([table_name, table_cmt])
res = {'status': 0, 'data': data}
except Instance.DoesNotExist:
res = {'status': 1, 'msg': 'Instance.DoesNotExist'}
except Exception as e:
res = {'status': 1, 'msg': str(e)}
elif db_type == 'oracle':
try:
data = {}
instance = Instance.objects.get(instance_name=instance_name,
db_type='oracle')
query_engine = get_engine(instance=instance)
table_list_sql = f"""SELECT table_name, comments FROM dba_tab_comments WHERE owner = '{db_name}'"""
result = query_engine.query(db_name=db_name,
sql=table_list_sql)
for row in result.rows:
table_name, table_cmt = row[0], row[1]
if table_name[0] not in data:
data[table_name[0]] = list()
data[table_name[0]].append([table_name, table_cmt])
res = {'status': 0, 'data': data}
except Instance.DoesNotExist:
res = {'status': 1, 'msg': 'Instance.DoesNotExist'}
except Exception as e:
res = {'status': 1, 'msg': str(e)}
else:
res = {'status': 1, 'msg': '非法调用！'}
return HttpResponse(json.dumps(res,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def get_column_name_list(request):
instance_name = request.POST.get('instance_name')
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
db_name = request.POST.get('db_name')
tb_name = request.POST.get('tb_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
# 取出该实例的连接方式，为了后面连进去获取表的所有字段
query_engine = get_engine(instance=instance)
col_list = query_engine.get_all_columns_by_tb(db_name, tb_name)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
result['data'] = col_list
if not col_list:
result['status'] = 1
result['msg'] = '字段列表为空, 可能是权限或配置有误'
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
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 query(workflow_id):
"""为延时或异步任务准备的queryx, 传入工单ID即可"""
workflow = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow.status == 'workflow_timingtask':
# 将工单状态修改为执行中
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='',
operator_display='系统')
query_engine = get_engine(instance=workflow.instance)
with FuncTimer() as t:
query_result = query_engine.query(
workflow.db_name, workflow.sqlworkflowcontent.sql_content)
# if workflow.instance.db_type == 'pgsql': # TODO 此处判断待优化，请在 修改传参方式后去除
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content,
# schema_name=workflow.schema_name)
# else:
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content)
query_result.query_time = t.cost
return query_result
def kill_session(request):
instance_name = request.POST.get('instance_name')
request_params = request.POST.get('request_params')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
# 判断是RDS还是其他实例
if len(AliyunRdsConfig.objects.filter(instance=instance,
is_enable=True)) > 0:
result = aliyun_kill_session(request)
else:
kill_sql = request_params
execute_engine = get_engine(instance=instance)
execute_engine.execute('information_schema', kill_sql)
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def rollback(request):
workflow_id = request.GET['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 = SqlWorkflow.objects.get(id=workflow_id)
try:
query_engine = get_engine(workflow=workflow)
list_backup_sql = query_engine.get_rollback()
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
workflow_title = workflow_detail.workflow_name
rollback_workflow_name = "【回滚工单】原工单Id:%s ,%s" % (workflow_id,
workflow_title)
context = {
'list_backup_sql': list_backup_sql,
'workflow_detail': workflow_detail,
'rollback_workflow_name': rollback_workflow_name
}
return render(request, 'rollback.html', context)
def users(request):
"""获取实例用户列表"""
instance_id = request.POST.get('instance_id')
try:
instance = Instance.objects.get(id=instance_id)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_get_user = '''select concat("\'", user, "\'", '@', "\'", host,"\'") as query from mysql.user;'''
query_engine = get_engine(instance=instance)
db_users = query_engine.query('mysql', sql_get_user).rows
# 获取用户权限信息
data = []
for db_user in db_users:
user_info = {}
user_priv = query_engine.query('mysql',
'show grants for {};'.format(
db_user[0]),
close_conn=False).rows
user_info['user'] = db_user[0]
user_info['privileges'] = user_priv
data.append(user_info)
# 关闭连接
query_engine.close()
result = {'status': 0, 'msg': 'ok', 'rows': data}
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
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 describe(request):
"""获取表结构"""
instance_name = request.POST.get('instance_name')
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
db_name = request.POST.get('db_name')
schema_name = request.POST.get('schema_name')
tb_name = request.POST.get('tb_name')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
query_engine = get_engine(instance=instance)
if schema_name:
query_result = query_engine.describe_table(db_name, tb_name,
schema_name)
else:
query_result = query_engine.describe_table(db_name, tb_name)
result['data'] = query_result.__dict__
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return HttpResponse(json.dumps(result), content_type='application/json')
def create_kill_session(request):
instance_name = request.POST.get('instance_name')
thread_ids = request.POST.get('ThreadIDs')
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result), content_type='application/json')
result = {'status': 0, 'msg': 'ok', 'data': []}
# 判断是RDS还是其他实例
if len(AliyunRdsConfig.objects.filter(instance_name=instance_name, is_enable=1)) > 0:
result = aliyun_create_kill_session(request)
else:
thread_ids = thread_ids.replace('[', '').replace(']', '')
query_engine = get_engine(instance=instance)
sql = "select concat('kill ', id, ';') from information_schema.processlist where id in ({});".format(thread_ids)
all_kill_sql = query_engine.query('information_schema', sql)
kill_sql = ''
for row in all_kill_sql.rows:
kill_sql = kill_sql + row[0]
result['data'] = kill_sql
# 返回查询结果
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def __init__(self, instance_name, db_name, sqltext):
instance = Instance.objects.get(instance_name=instance_name)
query_engine = get_engine(instance=instance)
self.engine = query_engine
self.db_name = db_name
self.sqltext = sqltext
self.sql_variable = '''
select
lower(variable_name),
variable_value
from performance_schema.global_variables
where upper(variable_name) in ('%s')
order by variable_name;''' % ('\',\''.join(SQLTuning.SYS_PARM_FILTER))
self.sql_optimizer_switch = '''
select variable_value
from performance_schema.global_variables
where upper(variable_name) = 'OPTIMIZER_SWITCH';
'''
self.sql_table_info = '''
select
table_name,
engine,
row_format as format,
table_rows,
avg_row_length as avg_row,
round((data_length + index_length) / 1024 / 1024, 2) as total_mb,
round((data_length) / 1024 / 1024, 2) as data_mb,
round((index_length) / 1024 / 1024, 2) as index_mb
from information_schema.tables
where table_schema = '%s' and table_name = '%s'
'''
self.sql_table_index = '''
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 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 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 check(request):
"""SQL检测按钮, 此处没有产生工单"""
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sql_content is None or instance_name is None or db_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 交给engine进行检测
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
result['status'] = 1
result['msg'] = str(e)
return HttpResponse(json.dumps(result),
content_type='application/json')
# 处理检测结果
result['data']['rows'] = check_result.to_dict()
result['data']['CheckWarningCount'] = check_result.warning_count
result['data']['CheckErrorCount'] = check_result.error_count
return HttpResponse(json.dumps(result), content_type='application/json')
def del_binlog(request):
instance_id = request.POST.get('instance_id')
binlog = request.POST.get('binlog', '')
try:
instance = Instance.objects.get(id=instance_id)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
if binlog:
query_engine = get_engine(instance=instance)
query_result = query_engine.query(
sql=fr"purge master logs to '{binlog}';")
if query_result.error is None:
result = {'status': 0, 'msg': '清理成功', 'data': ''}
else:
result = {
'status': 2,
'msg': f'清理失败,Error:{query_result.error}',
'data': ''
}
else:
result = {'status': 1, 'msg': 'Error:未选择binlog！', 'data': ''}
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def binlog_list(request):
"""
获取binlog列表
:param request:
:return:
"""
instance_name = request.POST.get('instance_name')
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
query_engine = get_engine(instance=instance)
query_result = query_engine.query('information_schema',
'show binary logs;')
if not query_result.error:
column_list = query_result.column_list
rows = []
for row in query_result.rows:
row_info = {}
for row_index, row_item in enumerate(row):
row_info[column_list[row_index]] = row_item
rows.append(row_info)
result = {'status': 0, 'msg': 'ok', 'data': rows}
else:
result = {'status': 1, 'msg': query_result.error}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
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 = Instance.objects.get(id=instance_id)
except Instance.DoesNotExist:
return JsonResponse({'status': 1, 'msg': '实例不存在', 'data': []})
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 execute(workflow_id, user=None):
"""为延时或异步任务准备的execute, 传入工单ID和执行人信息"""
# 使用当前读防止重复执行
with transaction.atomic():
workflow_detail = SqlWorkflow.objects.select_for_update().get(
id=workflow_id)
# 只有审核通过和定时执行的数据才可以继续执行
if workflow_detail.status not in [
'workflow_review_pass', 'workflow_timingtask'
]:
raise Exception('工单状态不正确，禁止执行！')
# 将工单状态修改为执行中
else:
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='人工操作执行' if user else '系统定时执行',
operator=user.username if user else '',
operator_display=user.display if user else '系统')
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
def simplecheck(request):
"""SQL检测按钮, 此处没有产生工单"""
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sql_content is None or instance_name is None or db_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
for statement in sqlparse.split(sql_content):
# 删除注释语句
statement = sqlparse.format(statement, strip_comments=True)
if re.match(r"^select", statement.lower()):
result['status'] = 1
result['msg'] = '仅支持DML和DDL语句，查询语句请使用SQL查询功能！'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 交给inception进行自动审核
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content.strip())
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = 'Inception审核报错，请检查Inception配置，错误信息：\n{}'.format(str(e))
return HttpResponse(json.dumps(result),
content_type='application/json')
if not check_result:
result['status'] = 1
result['msg'] = 'inception返回的结果集为空！可能是SQL语句有语法错误'
return JsonResponse(result)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
column_list = [
'id', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'sql',
'affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
check_warning_count = 0
check_error_count = 0
for row_item in check_result.rows:
if row_item.errlevel == 1:
check_warning_count = check_warning_count + 1
elif row_item.errlevel == 2:
check_error_count = check_error_count + 1
result['data']['rows'] = [r.__dict__ for r in check_result.rows]
result['data']['column_list'] = column_list
result['data']['CheckWarningCount'] = check_warning_count
result['data']['CheckErrorCount'] = check_error_count
return HttpResponse(json.dumps(result), content_type='application/json')
def simplecheck(request):
"""SQL检测按钮, 此处没有产生工单"""
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
instance = Instance.objects.get(instance_name=instance_name)
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sql_content is None or instance_name is None or db_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
# # 删除注释语句
# sql_content = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
# 交给inception进行自动审核
try:
check_engine = get_engine(instance=instance)
check_result = check_engine.execute_check(db_name=db_name,
sql=sql_content)
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = 'Inception审核报错，请检查Inception配置，错误信息：\n{}'.format(str(e))
return HttpResponse(json.dumps(result),
content_type='application/json')
if not check_result:
result['status'] = 1
result['msg'] = 'inception返回的结果集为空！可能是SQL语句有语法错误'
return JsonResponse(result)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
column_list = [
'id', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'sql',
'affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
check_warning_count = 0
check_error_count = 0
for row_item in check_result.rows:
if row_item.errlevel == 1:
check_warning_count = check_warning_count + 1
elif row_item.errlevel == 2:
check_error_count = check_error_count + 1
result['data']['rows'] = [r.__dict__ for r in check_result.rows]
result['data']['column_list'] = column_list
result['data']['CheckWarningCount'] = check_warning_count
result['data']['CheckErrorCount'] = check_error_count
return HttpResponse(json.dumps(result), content_type='application/json')
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 = Instance.objects.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
# 获取所有用户
sql_get_user = "******"
query_engine = get_engine(instance=instance)
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
}
# 合并数据
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 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 instance_resource(request):
"""
获取实例内的资源信息，database、schema、table、column
:param request:
:return:
"""
instance_id = request.GET.get('instance_id')
instance_name = request.GET.get('instance_name')
db_name = request.GET.get('db_name')
schema_name = request.GET.get('schema_name')
tb_name = request.GET.get('tb_name')
resource_type = request.GET.get('resource_type')
if instance_id:
instance = Instance.objects.get(id=instance_id)
else:
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
result = {'status': 0, 'msg': 'ok', 'data': []}
try:
query_engine = get_engine(instance=instance)
if resource_type == 'database':
resource = query_engine.get_all_databases()
elif resource_type == 'schema' and db_name:
resource = query_engine.get_all_schemas(db_name=db_name)
elif resource_type == 'table' and db_name:
if schema_name:
resource = query_engine.get_all_tables(db_name=db_name,
schema_name=schema_name)
else:
resource = query_engine.get_all_tables(db_name=db_name)
elif resource_type == 'column' and db_name and tb_name:
if schema_name:
resource = query_engine.get_all_columns_by_tb(
db_name=db_name, schema_name=schema_name, tb_name=tb_name)
else:
resource = query_engine.get_all_columns_by_tb(db_name=db_name,
tb_name=tb_name)
else:
raise TypeError('不支持的资源类型或者参数不完整！')
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
else:
if resource.error:
result['status'] = 1
result['msg'] = resource.error
else:
result['data'] = resource.rows
return HttpResponse(json.dumps(result), content_type='application/json')
def innodb_trx(request):
instance_name = request.POST.get('instance_name')
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')
query_engine = get_engine(instance=instance)
sql = '''select trx.trx_started,
trx.trx_state,
trx.trx_operation_state,
trx.trx_mysql_thread_id,
trx.trx_tables_locked,
trx.trx_rows_locked,
trx.trx_rows_modified,
trx.trx_is_read_only,
trx.trx_isolation_level,
p.user,
p.host,
p.db,
TO_SECONDS(NOW()) - TO_SECONDS(trx.trx_started) trx_idle_time,
p.time thread_time,
IFNULL((SELECT
GROUP_CONCAT(t1.sql_text SEPARATOR ';
')
FROM performance_schema.events_statements_history t1
INNER JOIN performance_schema.threads t2
ON t1.thread_id = t2.thread_id
WHERE t2.PROCESSLIST_ID = p.id), '') info
FROM information_schema.INNODB_TRX trx
INNER JOIN information_schema.PROCESSLIST p
ON trx.trx_mysql_thread_id = p.id
WHERE trx.trx_state = 'RUNNING'
AND p.COMMAND = 'Sleep'
AND P.time > 3
ORDER BY trx.trx_started ASC;'''
query_result = query_engine.query('information_schema', sql)
if not query_result.error:
trx = query_result.to_dict()
result = {'status': 0, 'msg': 'ok', 'rows': trx}
else:
result = {'status': 1, 'msg': query_result.error}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def explain(request):
"""
SQL优化界面获取SQL执行计划
:param request:
:return:
"""
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_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')
# 删除注释语句，进行语法判断，执行第一条有效sql
sql_content = sqlparse.format(sql_content.strip(), strip_comments=True)
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:
# 过滤非explain的语句
if not re.match(r"^explain", sql_content, re.I):
result['status'] = 1
result['msg'] = '仅支持explain开头的语句，请检查'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 执行获取执行计划语句
query_engine = get_engine(instance=instance)
sql_result = query_engine.query(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 instance(request):
result = {'status': 0, 'msg': 'ok', 'data': []}
instance_id = request.POST.get('instance_id')
instance = Instance.objects.get(id=instance_id)
try:
engine = get_engine(instance=instance)
engine.get_connection()
except Exception as e:
result['status'] = 1
result['msg'] = '无法连接实例,\n{}'.format(str(e))
# 返回结果
return HttpResponse(json.dumps(result), content_type='application/json')
def trxandlocks(request):
instance_name = request.POST.get('instance_name')
try:
instance = Instance.objects.get(instance_name=instance_name)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
sql = '''
SELECT
rtrx.`trx_state` AS "等待的状态",
rtrx.`trx_started` AS "等待事务开始时间",
rtrx.`trx_wait_started` AS "等待事务等待开始时间",
lw.`requesting_trx_id` AS "等待事务ID",
rtrx.trx_mysql_thread_id AS "等待事务线程ID",
rtrx.`trx_query` AS "等待事务的sql",
CONCAT(rl.`lock_mode`, '-', rl.`lock_table`, '(', rl.`lock_index`, ')') AS "等待的表信息",
rl.`lock_id` AS "等待的锁id",
lw.`blocking_trx_id` AS "运行的事务id",
trx.trx_mysql_thread_id AS "运行的事务线程id",
CONCAT(l.`lock_mode`, '-', l.`lock_table`, '(', l.`lock_index`, ')') AS "运行的表信息",
l.lock_id AS "运行的锁id",
trx.`trx_state` AS "运行事务的状态",
trx.`trx_started` AS "运行事务的时间",
trx.`trx_wait_started` AS "运行事务的等待开始时间",
trx.`trx_query` AS "运行事务的sql"
FROM information_schema.`INNODB_LOCKS` rl
, information_schema.`INNODB_LOCKS` l
, information_schema.`INNODB_LOCK_WAITS` lw
, information_schema.`INNODB_TRX` rtrx
, information_schema.`INNODB_TRX` trx
WHERE rl.`lock_id` = lw.`requested_lock_id`
AND l.`lock_id` = lw.`blocking_lock_id`
AND lw.requesting_trx_id = rtrx.trx_id
AND lw.blocking_trx_id = trx.trx_id;'''
query_engine = get_engine(instance=instance)
query_result = query_engine.query('information_schema', sql)
if not query_result.error:
trxandlocks = query_result.to_dict()
result = {'status': 0, 'msg': 'ok', 'rows': trxandlocks}
else:
result = {'status': 1, 'msg': query_result.error}
# 返回查询结果
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def param_list(request):
"""
获取实例参数列表
:param request:
:return:
"""
instance_id = request.POST.get('instance_id')
editable = True if request.POST.get('editable') else False
search = request.POST.get('search', '')
try:
ins = Instance.objects.get(id=instance_id)
except Instance.DoesNotExist:
result = {'status': 1, 'msg': '实例不存在', 'data': []}
return HttpResponse(json.dumps(result),
content_type='application/json')
# 获取已配置参数列表
cnf_params = dict()
for param in ParamTemplate.objects.filter(
db_type=ins.db_type,
variable_name__contains=search).values('variable_name',
'default_value',
'valid_values',
'description', 'editable'):
param['variable_name'] = param['variable_name'].lower()
cnf_params[param['variable_name']] = param
# 获取实例参数列表
engine = get_engine(instance=ins)
ins_variables = engine.get_variables()
# 处理结果
rows = list()
for variable in ins_variables.rows:
variable_name = variable[0].lower()
row = {
'variable_name': variable_name,
'runtime_value': variable[1],
'editable': False,
}
if variable_name in cnf_params.keys():
row = dict(row, **cnf_params[variable_name])
rows.append(row)
# 过滤参数
if editable:
rows = [row for row in rows if row['editable']]
else:
rows = [row for row in rows if not row['editable']]
return HttpResponse(json.dumps(rows,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
