def analyze(request):
"""
利用soar分析SQL
:param request:
:return:
"""
text = request.POST.get('text')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
if not (text and instance_name and db_name):
result = {"total": 0, "rows": []}
else:
soar = Soar()
soar_test_dsn = SysConfig().get('soar_test_dsn')
# 目标实例的连接信息
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)
args = {"report-type": "markdown",
"query": '',
"online-dsn": online_dsn,
"test-dsn": soar_test_dsn,
"allow-online-as-test": "false"}
rows = generate_sql(text)
for row in rows:
args['query'] = row['sql'].replace('"', '\\"').replace('`', '').replace('\n', ' ')
cmd_args = soar.generate_args2cmd(args=args, shell=True)
row['report'] = soar.execute_cmd(cmd_args, shell=True)
result = {"total": len(rows), "rows": rows}
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
def test_check_args_required(self):
"""
测试必选参数
:return:
"""
args = {
"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
}
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
soar.required_args = ['query']
args_check_result = soar.check_args(args)
self.assertDictEqual(args_check_result, {
'status': 1,
'msg': '必须指定query参数',
'data': {}
})
args['query'] = ""
args_check_result = soar.check_args(args)
self.assertDictEqual(args_check_result, {
'status': 1,
'msg': 'query参数值不能为空',
'data': {}
})
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 analyze(request):
"""
利用soar分析SQL
:param request:
:return:
"""
text = request.POST.get('text')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
if not text:
result = {"total": 0, "rows": []}
else:
soar = Soar()
if instance_name != '' and db_name != '':
try:
instance_info = user_instances(
request.user,
db_type=['mysql']).get(instance_name=instance_name)
except Instance.DoesNotExist:
return JsonResponse({
'status': 1,
'msg': '你所在组未关联该实例！',
'data': []
})
soar_test_dsn = SysConfig().get('soar_test_dsn')
# 获取实例连接信息
online_dsn = "{user}:{pwd}@{host}:{port}/{db}".format(
user=instance_info.user,
pwd=instance_info.password,
host=instance_info.host,
port=instance_info.port,
db=db_name)
else:
online_dsn = ''
soar_test_dsn = ''
args = {
"report-type": "markdown",
"query": '',
"online-dsn": online_dsn,
"test-dsn": soar_test_dsn,
"allow-online-as-test": "false"
}
rows = generate_sql(text)
for row in rows:
args['query'] = row['sql']
cmd_args = soar.generate_args2cmd(args=args, shell=True)
stdout, stderr = soar.execute_cmd(cmd_args,
shell=True).communicate()
row['report'] = stdout if stdout else stderr
result = {"total": len(rows), "rows": rows}
return HttpResponse(json.dumps(result,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def test_soar_generate_args2cmd(self):
args = {"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
"query": "select 1;"
}
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
cmd_args = soar.generate_args2cmd(args, False)
self.assertIsInstance(cmd_args, list)
cmd_args = soar.generate_args2cmd(args, True)
self.assertIsInstance(cmd_args, str)
def test_check_args_disable(self):
"""
测试禁用参数
:return:
"""
args = {"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
"query": "select 1;"
}
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
soar.disable_args = ['allow-online-as-test']
args_check_result = soar.check_args(args)
self.assertDictEqual(args_check_result, {'status': 1, 'msg': 'allow-online-as-test参数已被禁用', 'data': {}})
def setUpClass(cls):
soar_path = '/opt/archery/src/plugins/soar' # 修改为本机的soar路径
cls.superuser = User(username='******', is_superuser=True)
cls.superuser.save()
cls.client = Client()
cls.client.force_login(cls.superuser)
cls.sys_config = SysConfig()
cls.sys_config.set('soar', soar_path)
cls.sys_config.get_all_config()
cls.soar = Soar()
def test_check_args_path(self):
"""
测试路径
:return:
"""
args = {
"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
"query": "select 1;"
}
self.sys_config.set('soar', '')
self.sys_config.get_all_config()
soar = Soar()
args_check_result = soar.check_args(args)
self.assertDictEqual(args_check_result, {
'status': 1,
'msg': '可执行文件路径不能为空！',
'data': {}
})
# 路径不为空
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
args_check_result = soar.check_args(args)
self.assertDictEqual(args_check_result, {
'status': 0,
'msg': 'ok',
'data': {}
})
def test_execute_cmd(self, mock_subprocess):
args = {
"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
"query": "select 1;"
}
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
cmd_args = soar.generate_args2cmd(args, True)
mock_subprocess.Popen.return_value.communicate.return_value = (
'some_stdout', 'some_stderr')
result = soar.execute_cmd(cmd_args, True)
mock_subprocess.Popen.assert_called_once_with(cmd_args,
shell=True,
stdout=ANY,
stderr=ANY,
universal_newlines=ANY)
self.assertIn('some_stdout', result)
# 异常
mock_subprocess.Popen.side_effect = Exception('Boom! some exception!')
with self.assertRaises(RuntimeError):
soar.execute_cmd(cmd_args, False)
def test_execute_cmd(self):
args = {"online-dsn": '',
"test-dsn": '',
"allow-online-as-test": "false",
"report-type": "markdown",
"query": "select 1;"
}
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.get_all_config()
soar = Soar()
cmd_args = soar.generate_args2cmd(args, True)
result = soar.execute_cmd(cmd_args, True)
self.assertIn('/opt/archery/src/plugins/soar', result)
# 异常
with self.assertRaises(RuntimeError):
soar.execute_cmd(cmd_args, False)