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')