def setUp(self):
self.superuser = User.objects.create(username='******',
is_superuser=True)
self.user = User.objects.create(username='******')
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.group = ResourceGroup.objects.create(group_id=1,
group_name='group_name')
self.wf1 = SqlWorkflow.objects.create(
workflow_name='workflow_name',
group_id=self.group.group_id,
group_name=self.group.group_name,
engineer=self.superuser.username,
engineer_display=self.superuser.display,
audit_auth_groups='audit_auth_groups',
create_time=datetime.datetime.now(),
status='workflow_review_pass',
is_backup=True,
instance=self.master,
db_name='db_name',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(workflow=self.wf1,
sql_content='some_sql',
execute_result='')
def setUp(self):
self.superuser1 = User(username='******', display='中文显示', is_active=True, is_superuser=True,
email='*****@*****.**')
self.superuser1.save()
self.slave1 = Instance(instance_name='some_name', host='some_host', type='slave', db_type='mysql',
user='******', port=1234, password='******')
self.slave1.save()
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance', type='slave', db_type='mysql',
host='testhost', port=3306, user='******', password='******')
self.slave1.save()
User = get_user_model()
self.u1 = User(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = User(username='******', display='中文显示', is_active=True)
self.u2.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(instance_name='test_instance', type='master', db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
class QueryTest(TestCase):
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance', type='slave', db_type='mysql',
host='testhost', port=3306, user='******', password='******')
self.slave1.save()
User = get_user_model()
self.u1 = User(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = User(username='******', display='中文显示', is_active=True)
self.u2.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
def testcorrectSQL(self):
c = Client()
some_sql = 'select some from some_table limit 100;'
some_db = 'some_db'
some_limit = 100
c.force_login(self.u1)
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit})
self.assertEqual(r.status_code, 403)
c.force_login(self.u2)
q_result = ResultSet(full_sql=some_sql, rows=['value'])
q_result.column_list = ['some']
mock_engine = MysqlEngine
mock_engine.query = MagicMock(return_value=q_result)
mock_engine.query_masking = MagicMock(return_value=q_result)
mock_query = query
mock_query.query_priv_check = MagicMock(return_value={'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}})
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit})
mock_engine.query.assert_called_once_with(db_name=some_db, sql=some_sql, limit_num=some_limit)
r_json = r.json()
self.assertEqual(r_json['data']['rows'], ['value'])
self.assertEqual(r_json['data']['column_list'], ['some'])
def testMasking(self):
pass
def tearDown(self):
self.u1.delete()
self.u2.delete()
self.slave1.delete()
def setUpClass(cls):
cls.u1 = User(username='******', display='用户1')
cls.u1.save()
cls.ins1 = Instance(instance_name='some_ins', type='master', db_type='mssql', host='some_host',
port=1366, user='******', password='******')
cls.ins1.save()
cls.wf1 = SqlWorkflow.objects.create(
workflow_name='some_name',
group_id=1,
group_name='g1',
engineer=cls.u1.username,
engineer_display=cls.u1.display,
audit_auth_groups='some_group',
create_time=datetime.now() - timedelta(days=1),
status='workflow_finish',
is_backup='是',
instance=cls.ins1,
db_name='some_db',
syntax_type=1
)
cls.wfc1 = SqlWorkflowContent.objects.create(
workflow=cls.wf1,
sql_content='some_sql',
execute_result=json.dumps([{
'id': 1,
'sql': 'some_content'
}]))
cls.wf1.save()
class TestSchemaSync(TestCase):
"""
测试SchemaSync
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
def test_schema_sync(self):
"""
测试SchemaSync
:return:
"""
data = {
"instance_name": "test_instance",
"db_name": "*",
"target_instance_name": "test_instance",
"target_db_name": "*",
"sync_auto_inc": True,
"sync_comments": False
}
r = self.client.post(path='/instance/schemasync/', data=data)
self.assertEqual(json.loads(r.content)['status'], 1)
self.assertEqual(json.loads(r.content)['msg'], '请配置SchemaSync路径！')
self.sys_config.set('schemasync',
'/opt/venv4schemasync/bin/schemasync')
self.sys_config.get_all_config()
r = self.client.post(path='/instance/schemasync/', data=data)
self.assertEqual(json.loads(r.content)['status'], 0)
def setUpClass(cls):
cls.ins = Instance(instance_name='some_ins',
type='slave',
db_type='pgsql',
host='some_host',
port=1366,
user='******',
password='******')
cls.ins.save()
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance',
type='slave',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.slave1.save()
User = get_user_model()
self.u1 = User(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = User(username='******', display='中文显示', is_active=True)
self.u2.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
tomorrow = datetime.now() + timedelta(days=1)
self.query_apply_1 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db,some_db2',
limit_num=100,
valid_date=tomorrow,
priv_type=1,
status=0,
audit_auth_groups='some_audit_group')
self.query_apply_1.save()
self.query_apply_2 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db',
table_list='some_table,some_tb2',
limit_num=100,
valid_date=tomorrow,
priv_type=2,
status=0,
audit_auth_groups='some_audit_group')
self.query_apply_2.save()
def setUp(self):
self.ins1 = Instance(instance_name='some_ins', type='slave', db_type='mysql', host='some_host',
port=1366, user='******', password='******')
self.ins1.save()
self.sys_config = SysConfig()
self.wf = SqlWorkflow.objects.create(
workflow_name='some_name',
group_id=1,
group_name='g1',
engineer_display='',
audit_auth_groups='some_group',
create_time=datetime.now() - timedelta(days=1),
status='workflow_finish',
is_backup=True,
instance=self.ins1,
db_name='some_db',
syntax_type=1
)
SqlWorkflowContent.objects.create(workflow=self.wf)
def setUpClass(cls):
cls.ins1 = Instance(instance_name='some_ins',
type='slave',
db_type='mssql',
host='some_host',
port=1366,
user='******',
password='******')
cls.ins1.save()
cls.engine = MssqlEngine(instance=cls.ins1)
def setUp(self):
self.now = datetime.now()
self.u1 = User(username='******', display='用户1')
self.u1.save()
self.master1 = Instance(instance_name='test_master_instance',
type='master',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.master1.save()
self.wf1 = SqlWorkflow.objects.create(
workflow_name='some_name2',
group_id=1,
group_name='g1',
engineer=self.u1.username,
engineer_display=self.u1.display,
audit_auth_groups='some_group',
create_time=self.now - timedelta(days=1),
status='workflow_executing',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(workflow=self.wf1,
sql_content='some_sql',
execute_result='')
# 初始化工单执行返回对象
self.task_result = MagicMock()
self.task_result.args = [self.wf1.id]
self.task_result.success = True
self.task_result.stopped = self.now
self.task_result.result.json.return_value = json.dumps([{
'id':
1,
'sql':
'some_content'
}])
self.task_result.result.warning = ''
self.task_result.result.error = ''
def setUpClass(cls):
cls.u1 = User(username='******', display='用户1')
cls.u1.save()
cls.u2 = User(username='******', display='用户2')
cls.u2.save()
cls.superuser1 = User(username='******', is_superuser=True)
cls.superuser1.save()
cls.now = datetime.datetime.now()
cls.slave1 = Instance(instance_name='test_slave_instance', type='slave', db_type='mysql',
host='testhost', port=3306, user='******', password='******')
cls.slave1.save()
# 批量创建数据 ddl ,u1 ,g1, yesterday 组, 2 个数据
ddl_workflow = [SqlWorkflow(
workflow_name='ddl %s' % i,
group_id=1,
group_name='g1',
engineer=cls.u1.username,
engineer_display=cls.u1.display,
audit_auth_groups='some_group',
create_time=cls.now - datetime.timedelta(days=1),
status='workflow_finish',
is_backup=True,
instance=cls.slave1,
db_name='some_db',
syntax_type=1
) for i in range(2)]
# 批量创建数据 dml ,u1 ,g2, the day before yesterday 组, 3 个数据
dml_workflow = [SqlWorkflow(
workflow_name='Test %s' % i,
group_id=2,
group_name='g2',
engineer=cls.u2.username,
engineer_display=cls.u2.display,
audit_auth_groups='some_group',
create_time=cls.now - datetime.timedelta(days=2),
status='workflow_finish',
is_backup=True,
instance=cls.slave1,
db_name='some_db',
syntax_type=2
) for i in range(3)]
SqlWorkflow.objects.bulk_create(ddl_workflow + dml_workflow)
# 保存内容数据
ddl_workflow_content = [SqlWorkflowContent(
workflow=SqlWorkflow.objects.get(workflow_name='ddl %s' % i),
sql_content='some_sql',
) for i in range(2)]
dml_workflow_content = [SqlWorkflowContent(
workflow=SqlWorkflow.objects.get(workflow_name='Test %s' % i),
sql_content='some_sql',
) for i in range(3)]
SqlWorkflowContent.objects.bulk_create(ddl_workflow_content + dml_workflow_content)
def setUpClass(cls):
"""
初始化补全引擎
:return:
"""
# 使用 travis.ci 时实例和测试service保持一致
cls.master = Instance(instance_name='test_instance', type='master', db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
cls.master.save()
cls.comp_engine = get_comp_engine(instance=cls.master, db_name=settings.DATABASES['default']['TEST']['NAME'])
# 等待completion_refresher刷新完成
while cls.comp_engine.completion_refresher.is_refreshing():
import time
time.sleep(1)
def mirage(request):
"""迁移加密的Instance数据，保留一定版本后删除"""
try:
pc = Prpcrypt()
mg_user = Migrator(app="sql", model="Instance", field="user")
mg_password = Migrator(app="sql", model="Instance", field="password")
# 还原密码
for ins in Instance.objects.all():
# 忽略解密错误的数据(本身为异常数据)
try:
Instance(pk=ins.pk, password=pc.decrypt(ins.password)).save(update_fields=['password'])
except:
pass
# 使用django-mirage-field重新加密
mg_user.encrypt()
mg_password.encrypt()
return JsonResponse({"msg": "ok"})
except Exception as msg:
return JsonResponse({"msg": f"{msg}"})
class CheckTest(TestCase):
"""检查功能测试"""
def setUp(self):
self.superuser1 = User(username='******',
display='中文显示',
is_active=True,
is_superuser=True,
email='*****@*****.**')
self.superuser1.save()
self.slave1 = Instance(instance_name='some_name',
host='some_host',
type='slave',
db_type='mysql',
user='******',
port=1234,
password='******')
self.slave1.save()
def tearDown(self):
self.superuser1.delete()
@patch.object(MsgSender, '__init__', return_value=None)
@patch.object(MsgSender, 'send_email')
def testEmailCheck(self, send_email, mailsender):
"""邮箱配置检查"""
mail_switch = 'true'
smtp_ssl = 'false'
smtp_server = 'some_server'
smtp_port = '1234'
smtp_user = '******'
smtp_pass = '******'
# 略过superuser校验
# 未开启mail开关
mail_switch = 'false'
c = Client()
c.force_login(self.superuser1)
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '请先开启邮件通知！')
mail_switch = 'true'
# 填写非正整数端口号
smtp_port = '-3'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '端口号只能为正整数')
smtp_port = '1234'
# 未填写用户邮箱
self.superuser1.email = ''
self.superuser1.save()
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '请先完善当前用户邮箱信息！')
self.superuser1.email = '*****@*****.**'
self.superuser1.save()
# 发送失败, 显示traceback
send_email.return_value = 'some traceback'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertIn('some traceback', r_json['msg'])
send_email.reset_mock() # 重置``Mock``的调用计数
mailsender.reset_mock()
# 发送成功
send_email.return_value = 'success'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
mailsender.assert_called_once_with(server=smtp_server,
port=int(smtp_port),
user=smtp_user,
password=smtp_pass,
ssl=False)
send_email.called_once_with('Archery 邮件发送测试', 'Archery 邮件发送测试...',
[self.superuser1.email])
self.assertEqual(r_json['status'], 0)
self.assertEqual(r_json['msg'], 'ok')
@patch('MySQLdb.connect')
def testInstanceCheck(self, connect):
cur = MagicMock()
cur.return_value.execute = MagicMock()
cur.return_value.close = MagicMock()
connect.return_value.cursor = cur
connect.return_value.close = MagicMock()
c = Client()
c.force_login(self.superuser1)
r = c.post('/check/instance/', data={'instance_id': self.slave1.id})
r_json = r.json()
self.assertEqual(r_json['status'], 0)
connect.assert_called_once_with(host=self.slave1.host,
port=self.slave1.port,
user=self.slave1.user,
passwd=self.slave1.raw_password,
charset=ANY)
cur.assert_called_once()
cur.return_value.execute.assert_called_once()
cur.return_value.close.assert_called_once()
connect.return_value.close.assert_called_once()
# exception
cur.return_value.execute.side_effect = NameError('some error')
r = c.post('/check/instance/', data={'instance_id': self.slave1.id})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertIn('无法连接实例', r_json['msg'])
self.assertIn('some error', r_json['msg'])
class QueryTest(TestCase):
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance',
type='slave',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.slave1.save()
User = get_user_model()
self.u1 = User(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = User(username='******', display='中文显示', is_active=True)
self.u2.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
tomorrow = datetime.now() + timedelta(days=1)
self.query_apply_1 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db,some_db2',
limit_num=100,
valid_date=tomorrow,
priv_type=1,
status=0,
audit_auth_groups='some_audit_group')
self.query_apply_1.save()
self.query_apply_2 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db',
table_list='some_table,some_tb2',
limit_num=100,
valid_date=tomorrow,
priv_type=2,
status=0,
audit_auth_groups='some_audit_group')
self.query_apply_2.save()
def tearDown(self):
self.u1.delete()
self.u2.delete()
self.slave1.delete()
self.query_apply_1.delete()
QueryPrivileges.objects.all().delete()
def testQueryAuditCallback(self):
"""测试权限申请工单回调"""
# 工单状态改为审核失败, 验证工单状态
query.query_audit_call_back(self.query_apply_1.apply_id, 2)
self.query_apply_1.refresh_from_db()
self.assertEqual(self.query_apply_1.status, 2)
for db in self.query_apply_1.db_list.split(','):
self.assertEqual(
len(
QueryPrivileges.objects.filter(
user_name=self.query_apply_1.user_name,
db_name=db,
limit_num=100)), 0)
# 工单改为审核成功, 验证工单状态和权限状态
query.query_audit_call_back(self.query_apply_1.apply_id, 1)
self.query_apply_1.refresh_from_db()
self.assertEqual(self.query_apply_1.status, 1)
for db in self.query_apply_1.db_list.split(','):
self.assertEqual(
len(
QueryPrivileges.objects.filter(
user_name=self.query_apply_1.user_name,
db_name=db,
limit_num=100)), 1)
# 表权限申请测试, 只测试审核成功
query.query_audit_call_back(self.query_apply_2.apply_id, 1)
self.query_apply_2.refresh_from_db()
self.assertEqual(self.query_apply_2.status, 1)
for tb in self.query_apply_2.table_list.split(','):
self.assertEqual(
len(
QueryPrivileges.objects.filter(
user_name=self.query_apply_2.user_name,
db_name=self.query_apply_2.db_list,
table_name=tb,
limit_num=self.query_apply_2.limit_num)), 1)
def testCorrectSQL(self):
c = Client()
some_sql = 'select some from some_table limit 100;'
some_db = 'some_db'
some_limit = 100
c.force_login(self.u1)
r = c.post('/query/',
data={
'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit
})
self.assertEqual(r.status_code, 403)
c.force_login(self.u2)
q_result = ResultSet(full_sql=some_sql, rows=['value'])
q_result.column_list = ['some']
mock_engine = MysqlEngine
mock_engine.query = MagicMock(return_value=q_result)
mock_engine.query_masking = MagicMock(return_value=q_result)
mock_query = query
mock_query.query_priv_check = MagicMock(return_value={
'status': 0,
'data': {
'limit_num': 100,
'priv_check': 1
}
})
r = c.post('/query/',
data={
'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit
})
mock_engine.query.assert_called_once_with(db_name=some_db,
sql=some_sql,
limit_num=some_limit)
r_json = r.json()
self.assertEqual(r_json['data']['rows'], ['value'])
self.assertEqual(r_json['data']['column_list'], ['some'])
class CheckTest(TestCase):
"""检查功能测试"""
def setUp(self):
self.superuser1 = User(username='******',
display='中文显示',
is_active=True,
is_superuser=True,
email='*****@*****.**')
self.superuser1.save()
self.slave1 = Instance(instance_name='some_name',
host='some_host',
type='slave',
db_type='mysql',
user='******',
port=1234,
password='******')
self.slave1.save()
def tearDown(self):
self.superuser1.delete()
@patch.object(MsgSender, '__init__', return_value=None)
@patch.object(MsgSender, 'send_email')
def testEmailCheck(self, send_email, mailsender):
"""邮箱配置检查"""
mail_switch = 'true'
smtp_ssl = 'false'
smtp_server = 'some_server'
smtp_port = '1234'
smtp_user = '******'
smtp_pass = '******'
# 略过superuser校验
# 未开启mail开关
mail_switch = 'false'
c = Client()
c.force_login(self.superuser1)
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '请先开启邮件通知！')
mail_switch = 'true'
# 填写非正整数端口号
smtp_port = '-3'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '端口号只能为正整数')
smtp_port = '1234'
# 未填写用户邮箱
self.superuser1.email = ''
self.superuser1.save()
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertEqual(r_json['msg'], '请先完善当前用户邮箱信息！')
self.superuser1.email = '*****@*****.**'
self.superuser1.save()
# 发送失败, 显示traceback
send_email.return_value = 'some traceback'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
self.assertEqual(r_json['status'], 1)
self.assertIn('some traceback', r_json['msg'])
send_email.reset_mock() # 重置``Mock``的调用计数
mailsender.reset_mock()
# 发送成功
send_email.return_value = 'success'
r = c.post('/check/email/',
data={
'mail': mail_switch,
'mail_ssl': smtp_ssl,
'mail_smtp_server': smtp_server,
'mail_smtp_port': smtp_port,
'mail_smtp_user': smtp_user,
'mail_smtp_password': smtp_pass
})
r_json = r.json()
mailsender.assert_called_once_with(server=smtp_server,
port=int(smtp_port),
user=smtp_user,
password=smtp_pass,
ssl=False)
send_email.called_once_with('Archery 邮件发送测试', 'Archery 邮件发送测试...',
[self.superuser1.email])
self.assertEqual(r_json['status'], 0)
self.assertEqual(r_json['msg'], 'ok')
@patch('MySQLdb.connect')
@patch('common.check.get_engine', return_value=EngineBase)
def testInstanceCheck(self, _get_engine, _conn):
_get_engine.return_value.get_connection = _conn
c = Client()
c.force_login(self.superuser1)
r = c.post('/check/instance/', data={'instance_id': self.slave1.id})
r_json = r.json()
self.assertEqual(r_json['status'], 0)
@patch('MySQLdb.connect')
def test_inception_check(self, _conn):
c = Client()
c.force_login(self.superuser1)
data = {
"inception_host": "inception",
"inception_port": "6669",
"inception_remote_backup_host": "mysql",
"inception_remote_backup_port": 3306,
"inception_remote_backup_user": "******",
"inception_remote_backup_password": "******"
}
r = c.post('/check/inception/', data=data)
r_json = r.json()
self.assertEqual(r_json['status'], 0)
@patch('MySQLdb.connect')
def test_go_inception_check(self, _conn):
c = Client()
c.force_login(self.superuser1)
data = {
"go_inception_host": "inception",
"go_inception_port": "6669",
"inception_remote_backup_host": "mysql",
"inception_remote_backup_port": 3306,
"inception_remote_backup_user": "******",
"inception_remote_backup_password": "******"
}
r = c.post('/check/go_inception/', data=data)
r_json = r.json()
self.assertEqual(r_json['status'], 0)
class TestBinLog(TestCase):
"""
测试Binlog相关
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
def test_binlog_list_instance_not_exist(self):
"""
测试获取binlog列表，实例不存在
:return:
"""
data = {"instance_name": 'some_instance'}
r = self.client.post(path='/binlog/list/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 1,
'msg': '实例不存在',
'data': []
})
def test_binlog_list_instance(self):
"""
测试获取binlog列表，实例存在
:return:
"""
data = {"instance_name": 'test_instance'}
r = self.client.post(path='/binlog/list/', data=data)
self.assertEqual(json.loads(r.content).get('status'), 0)
def test_binlog2sql_path_not_exist(self):
"""
测试获取解析binlog，path未设置
:return:
"""
data = {
"instance_name": "test_instance",
"save_sql": "false",
"no_pk": "false",
"flashback": "false",
"back_interval": "",
"num": "",
"start_file": "mysql-bin.000045",
"start_pos": "",
"end_file": "",
"end_pos": "",
"stop_time": "",
"start_time": "",
"only_schemas": "",
"only_dml": "true",
"sql_type": ""
}
r = self.client.post(path='/binlog/binlog2sql/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 1,
'msg': '可执行文件路径不能为空！',
'data': {}
})
@patch('sql.plugins.plugin.subprocess')
def test_binlog2sql(self, _subprocess):
"""
测试获取解析binlog，path设置
:param _subprocess:
:return:
"""
self.sys_config.set('binlog2sql', '/opt/binlog2sql')
self.sys_config.get_all_config()
data = {
"instance_name": "test_instance",
"save_sql": "1",
"no_pk": "false",
"flashback": "false",
"back_interval": "",
"num": "1",
"start_file": "mysql-bin.000045",
"start_pos": "",
"end_file": "",
"end_pos": "",
"stop_time": "",
"start_time": "",
"only_schemas": "",
"only_dml": "true",
"sql_type": ""
}
r = self.client.post(path='/binlog/binlog2sql/', data=data)
self.assertEqual(json.loads(r.content), {
"status": 0,
"msg": "ok",
"data": [{
"sql": {},
"binlog_info": {}
}]
})
@patch('builtins.iter')
@patch('sql.plugins.plugin.subprocess')
def test_binlog2sql_file(self, _subprocess, _iter):
"""
测试保存文件
:param _subprocess:
:return:
"""
args = {
"instance_name": "test_instance",
"save_sql": "1",
"no_pk": "false",
"flashback": "false",
"back_interval": "",
"num": "1",
"start_file": "",
"start_pos": "",
"end_file": "",
"end_pos": "",
"stop_time": "",
"start_time": "",
"only_schemas": "",
"only_dml": "true",
"sql_type": "",
"instance": self.master
}
_subprocess.Popen.return_value.stdout.return_value.readline.return_value = 'sql'
_iter.return_value = ''
r = binlog2sql_file(args=args, user=self.superuser)
self.assertEqual(self.superuser, r[0])
def test_del_binlog_instance_not_exist(self):
"""
测试删除binlog，实例不存在
:return:
"""
data = {
"instance_id": 0,
"binlog": "mysql-bin.000001",
}
r = self.client.post(path='/binlog/del_log/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 1,
'msg': '实例不存在',
'data': []
})
def test_del_binlog_binlog_not_exist(self):
"""
测试删除binlog，实例存在,binlog 不存在
:return:
"""
data = {"instance_id": self.master.id, "binlog": ''}
r = self.client.post(path='/binlog/del_log/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 1,
'msg': 'Error:未选择binlog！',
'data': ''
})
@patch('sql.engines.mysql.MysqlEngine.query')
@patch('sql.engines.get_engine')
def test_del_binlog(self, _get_engine, _query):
"""
测试删除binlog
:return:
"""
data = {"instance_id": self.master.id, "binlog": "mysql-bin.000001"}
_query.return_value = ResultSet(full_sql='select 1')
r = self.client.post(path='/binlog/del_log/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 0,
'msg': '清理成功',
'data': ''
})
@patch('sql.engines.mysql.MysqlEngine.query')
@patch('sql.engines.get_engine')
def test_del_binlog_wrong(self, _get_engine, _query):
"""
测试删除binlog
:return:
"""
data = {"instance_id": self.master.id, "binlog": "mysql-bin.000001"}
_query.return_value = ResultSet(full_sql='select 1')
_query.return_value.error = '清理失败'
r = self.client.post(path='/binlog/del_log/', data=data)
self.assertEqual(json.loads(r.content), {
'status': 2,
'msg': '清理失败,Error:清理失败',
'data': ''
})
class TestOptimize(TestCase):
"""
测试SQL优化
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(instance_name='test_instance', type='master', db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
def test_sqladvisor(self):
"""
测试SQLAdvisor报告
:return:
"""
r = self.client.post(path='/slowquery/optimize_sqladvisor/')
self.assertEqual(json.loads(r.content), {'status': 1, 'msg': '页面提交参数可能为空', 'data': []})
r = self.client.post(path='/slowquery/optimize_sqladvisor/',
data={"sql_content": "select 1;", "instance_name": "test_instance"})
self.assertEqual(json.loads(r.content), {'status': 1, 'msg': '请配置SQLAdvisor路径！', 'data': []})
self.sys_config.set('sqladvisor', '/opt/archery/src/plugins/sqladvisor')
self.sys_config.get_all_config()
r = self.client.post(path='/slowquery/optimize_sqladvisor/',
data={"sql_content": "select 1;", "instance_name": "test_instance"})
self.assertEqual(json.loads(r.content)['status'], 0)
def test_soar(self):
"""
测试SOAR报告
:return:
"""
r = self.client.post(path='/slowquery/optimize_soar/')
self.assertEqual(json.loads(r.content), {'status': 1, 'msg': '页面提交参数可能为空', 'data': []})
r = self.client.post(path='/slowquery/optimize_soar/',
data={"sql": "select 1;", "instance_name": "test_instance", "db_name": "mysql"})
self.assertEqual(json.loads(r.content), {'status': 1, 'msg': '请配置soar_path和test_dsn！', 'data': []})
self.sys_config.set('soar', '/opt/archery/src/plugins/soar')
self.sys_config.set('soar_test_dsn', 'root:@127.0.0.1:3306/information_schema')
self.sys_config.get_all_config()
r = self.client.post(path='/slowquery/optimize_soar/',
data={"sql": "select 1;", "instance_name": "test_instance", "db_name": "mysql"})
self.assertEqual(json.loads(r.content)['status'], 0)
def test_tuning(self):
"""
测试SQLTuning报告
:return:
"""
data = {"sql_content": "select * from test_archery.sql_users;",
"instance_name": "test_instance",
"db_name": settings.DATABASES['default']['TEST']['NAME']
}
r = self.client.post(path='/slowquery/optimize_sqltuning/')
self.assertEqual(json.loads(r.content), {'status': 1, 'msg': '实例不存在', 'data': []})
# 获取sys_parm
data['option[]'] = 'sys_parm'
r = self.client.post(path='/slowquery/optimize_sqltuning/', data=data)
self.assertListEqual(list(json.loads(r.content)['data'].keys()),
['basic_information', 'sys_parameter', 'optimizer_switch', 'sqltext'])
# 获取sql_plan
data['option[]'] = 'sql_plan'
r = self.client.post(path='/slowquery/optimize_sqltuning/', data=data)
self.assertListEqual(list(json.loads(r.content)['data'].keys()),
['optimizer_rewrite_sql', 'plan', 'sqltext'])
# 获取obj_stat
data['option[]'] = 'obj_stat'
r = self.client.post(path='/slowquery/optimize_sqltuning/', data=data)
self.assertListEqual(list(json.loads(r.content)['data'].keys()),
['object_statistics', 'sqltext'])
# 获取sql_profile
data['option[]'] = 'sql_profile'
r = self.client.post(path='/slowquery/optimize_sqltuning/', data=data)
self.assertListEqual(list(json.loads(r.content)['data'].keys()), ['session_status', 'sqltext'])
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance', type='slave', db_type='mysql',
host='testhost', port=3306, user='******', password='******')
self.slave2 = Instance(instance_name='test_instance_non_mysql', type='slave', db_type='mssql',
host='some_host2', port=3306, user='******', password='******')
self.slave1.save()
self.slave2.save()
archer_user = get_user_model()
self.superuser1 = archer_user(username='******', is_superuser=True)
self.superuser1.save()
self.u1 = archer_user(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = archer_user(username='******', display='中文显示', is_active=True)
self.u2.save()
self.u3 = archer_user(username='******', display='中文显示', is_active=True)
self.u3.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
self.u3.user_permissions.add(sql_query_perm)
tomorrow = datetime.now() + timedelta(days=1)
self.query_apply_1 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db,some_db2',
limit_num=100,
valid_date=tomorrow,
priv_type=1,
status=0,
audit_auth_groups='some_audit_group'
)
self.query_apply_1.save()
self.query_apply_2 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db',
table_list='some_table,some_tb2',
limit_num=100,
valid_date=tomorrow,
priv_type=2,
status=0,
audit_auth_groups='some_audit_group'
)
self.query_apply_2.save()
self.db_priv_for_user3 = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave1.instance_name,
db_name='some_db',
table_name='',
valid_date=tomorrow,
limit_num=70,
priv_type=1)
self.db_priv_for_user3.save()
self.table_priv_for_user3 = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave1.instance_name,
db_name='another_db',
table_name='some_table',
valid_date=tomorrow,
limit_num=60,
priv_type=2)
self.table_priv_for_user3.save()
self.db_priv_for_user3_another_instance = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave2.instance_name,
db_name='some_db_another_instance',
table_name='',
valid_date=tomorrow,
limit_num=50,
priv_type=1)
self.db_priv_for_user3_another_instance.save()
class QueryTest(TestCase):
def setUp(self):
self.slave1 = Instance(instance_name='test_slave_instance', type='slave', db_type='mysql',
host='testhost', port=3306, user='******', password='******')
self.slave2 = Instance(instance_name='test_instance_non_mysql', type='slave', db_type='mssql',
host='some_host2', port=3306, user='******', password='******')
self.slave1.save()
self.slave2.save()
archer_user = get_user_model()
self.superuser1 = archer_user(username='******', is_superuser=True)
self.superuser1.save()
self.u1 = archer_user(username='******', display='中文显示', is_active=True)
self.u1.save()
self.u2 = archer_user(username='******', display='中文显示', is_active=True)
self.u2.save()
self.u3 = archer_user(username='******', display='中文显示', is_active=True)
self.u3.save()
sql_query_perm = Permission.objects.get(codename='query_submit')
self.u2.user_permissions.add(sql_query_perm)
self.u3.user_permissions.add(sql_query_perm)
tomorrow = datetime.now() + timedelta(days=1)
self.query_apply_1 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db,some_db2',
limit_num=100,
valid_date=tomorrow,
priv_type=1,
status=0,
audit_auth_groups='some_audit_group'
)
self.query_apply_1.save()
self.query_apply_2 = QueryPrivilegesApply(
group_id=1,
group_name='some_group',
title='some_title',
user_name='some_user',
instance_name='some_ins',
db_list='some_db',
table_list='some_table,some_tb2',
limit_num=100,
valid_date=tomorrow,
priv_type=2,
status=0,
audit_auth_groups='some_audit_group'
)
self.query_apply_2.save()
self.db_priv_for_user3 = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave1.instance_name,
db_name='some_db',
table_name='',
valid_date=tomorrow,
limit_num=70,
priv_type=1)
self.db_priv_for_user3.save()
self.table_priv_for_user3 = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave1.instance_name,
db_name='another_db',
table_name='some_table',
valid_date=tomorrow,
limit_num=60,
priv_type=2)
self.table_priv_for_user3.save()
self.db_priv_for_user3_another_instance = QueryPrivileges(
user_name=self.u3.username,
user_display=self.u3.display,
instance_name=self.slave2.instance_name,
db_name='some_db_another_instance',
table_name='',
valid_date=tomorrow,
limit_num=50,
priv_type=1)
self.db_priv_for_user3_another_instance.save()
def tearDown(self):
self.u1.delete()
self.u2.delete()
self.u3.delete()
self.slave1.delete()
self.slave2.delete()
self.query_apply_1.delete()
self.query_apply_2.delete()
QueryPrivileges.objects.all().delete()
archer_config = SysConfig()
archer_config.set('disable_star', False)
def testQueryAuditCallback(self):
"""测试权限申请工单回调"""
# 工单状态改为审核失败, 验证工单状态
query.query_audit_call_back(self.query_apply_1.apply_id, 2)
self.query_apply_1.refresh_from_db()
self.assertEqual(self.query_apply_1.status, 2)
for db in self.query_apply_1.db_list.split(','):
self.assertEqual(len(QueryPrivileges.objects.filter(
user_name=self.query_apply_1.user_name,
db_name=db,
limit_num=100)), 0)
# 工单改为审核成功, 验证工单状态和权限状态
query.query_audit_call_back(self.query_apply_1.apply_id, 1)
self.query_apply_1.refresh_from_db()
self.assertEqual(self.query_apply_1.status, 1)
for db in self.query_apply_1.db_list.split(','):
self.assertEqual(len(QueryPrivileges.objects.filter(
user_name=self.query_apply_1.user_name,
db_name=db,
limit_num=100)), 1)
# 表权限申请测试, 只测试审核成功
query.query_audit_call_back(self.query_apply_2.apply_id, 1)
self.query_apply_2.refresh_from_db()
self.assertEqual(self.query_apply_2.status, 1)
for tb in self.query_apply_2.table_list.split(','):
self.assertEqual(len(QueryPrivileges.objects.filter(
user_name=self.query_apply_2.user_name,
db_name=self.query_apply_2.db_list,
table_name=tb,
limit_num=self.query_apply_2.limit_num)), 1)
@patch('sql.engines.mysql.MysqlEngine.query')
@patch('sql.engines.mysql.MysqlEngine.query_masking')
@patch('sql.query.query_priv_check')
def testCorrectSQL(self, _priv_check, _query_masking, _query):
c = Client()
some_sql = 'select some from some_table limit 100;'
some_db = 'some_db'
some_limit = 100
c.force_login(self.u1)
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit})
self.assertEqual(r.status_code, 403)
c.force_login(self.u2)
q_result = ResultSet(full_sql=some_sql, rows=['value'])
q_result.column_list = ['some']
_query.return_value = q_result
_query_masking.return_value = q_result
_priv_check.return_value = {'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}}
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': some_sql,
'db_name': some_db,
'limit_num': some_limit})
_query.assert_called_once_with(db_name=some_db, sql=some_sql, limit_num=some_limit)
r_json = r.json()
self.assertEqual(r_json['data']['rows'], ['value'])
self.assertEqual(r_json['data']['column_list'], ['some'])
@patch('sql.engines.mysql.MysqlEngine.query')
@patch('sql.engines.mysql.MysqlEngine.query_masking')
@patch('sql.query.query_priv_check')
def testSQLWithoutLimit(self, _priv_check, _query_masking, _query):
c = Client()
some_limit = 100
sql_without_limit = 'select some from some_table'
sql_with_limit = 'select some from some_table limit {0};'.format(some_limit)
some_db = 'some_db'
c.force_login(self.u2)
q_result = ResultSet(full_sql=sql_without_limit, rows=['value'])
q_result.column_list = ['some']
_query.return_value = q_result
_query_masking.return_value = q_result
_priv_check.return_value = {'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}}
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': sql_without_limit,
'db_name': some_db,
'limit_num': some_limit})
_query.assert_called_once_with(db_name=some_db, sql=sql_with_limit, limit_num=some_limit)
r_json = r.json()
self.assertEqual(r_json['data']['rows'], ['value'])
self.assertEqual(r_json['data']['column_list'], ['some'])
# 带 * 且不带 limit 的sql
sql_with_star = 'select * from some_table'
filtered_sql_with_star = 'select * from some_table limit {0};'.format(some_limit)
_query.reset_mock()
c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': sql_with_star,
'db_name': some_db,
'limit_num': some_limit})
_query.assert_called_once_with(db_name=some_db, sql=filtered_sql_with_star, limit_num=some_limit)
@patch('sql.query.query_priv_check')
def testStarOptionOn(self, _priv_check):
c = Client()
c.force_login(self.u2)
some_limit = 100
sql_with_star = 'select * from some_table'
some_db = 'some_db'
_priv_check.return_value = {'status': 0, 'data': {'limit_num': 100, 'priv_check': 1}}
archer_config = SysConfig()
archer_config.set('disable_star', True)
r = c.post('/query/', data={'instance_name': self.slave1.instance_name,
'sql_content': sql_with_star,
'db_name': some_db,
'limit_num': some_limit})
archer_config.set('disable_star', False)
r_json = r.json()
self.assertEqual(1, r_json['status'])
@patch('sql.query.Masking')
def test_query_priv_check(self, mock_masking):
# 超级用户直接返回
superuser_limit = query.query_priv_check(self.superuser1, self.slave1.instance_name,
'some_db', 'some_sql', 100)
self.assertEqual(superuser_limit['status'], 0)
self.assertEqual(superuser_limit['data']['limit_num'], 100)
# 无语法树解析，只校验db_name
limit_without_tree_analyse = query.query_priv_check(self.u3, self.slave2,
'some_db_another_instance','some_sql', 1000)
self.assertEqual(limit_without_tree_analyse['data']['limit_num'], self.db_priv_for_user3_another_instance.limit_num)
# 无语法树解析， 无权限的情况
limit_without_tree_analyse = query.query_priv_check(self.u3, self.slave2,
'some_db_does_not_exist','some_sql', 1000)
self.assertEqual(limit_without_tree_analyse['status'],1)
self.assertIn('some_db_does_not_exist', limit_without_tree_analyse['msg'])
# 有语法树解析, 有库权限
mock_masking.return_value.query_table_ref.return_value = {
'status': 0,
'data': [{
'db': 'another_db',
'table': 'some_table'
}]}
limit_with_tree_analyse = query.query_priv_check(self.u3, self.slave1,
'some_db','some_sql',1000)
mock_masking.return_value.query_table_ref.assert_called_once()
self.assertEqual(limit_with_tree_analyse['data']['limit_num'], self.table_priv_for_user3.limit_num)
class WorkflowViewTest(TransactionTestCase):
def setUp(self):
self.now = datetime.now()
can_view_permission = Permission.objects.get(
codename='menu_sqlworkflow')
self.u1 = User(username='******', display='用户1')
self.u1.save()
self.u1.user_permissions.add(can_view_permission)
self.u2 = User(username='******', display='用户2')
self.u2.save()
self.u2.user_permissions.add(can_view_permission)
self.u3 = User(username='******', display='用户3')
self.u3.save()
self.u3.user_permissions.add(can_view_permission)
self.superuser1 = User(username='******', is_superuser=True)
self.superuser1.save()
self.master1 = Instance(instance_name='test_master_instance',
type='master',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.master1.save()
self.wf1 = SqlWorkflow.objects.create(
workflow_name='some_name',
group_id=1,
group_name='g1',
engineer=self.u1.username,
engineer_display=self.u1.display,
audit_auth_groups='some_group',
create_time=self.now - timedelta(days=1),
status='workflow_finish',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(
workflow=self.wf1,
sql_content='some_sql',
execute_result=json.dumps([{
'id': 1,
'sql': 'some_content'
}]))
self.wf2 = SqlWorkflow.objects.create(workflow_name='some_name2',
group_id=1,
group_name='g1',
engineer=self.u2.username,
engineer_display=self.u2.display,
audit_auth_groups='some_group',
create_time=self.now -
timedelta(days=1),
status='workflow_manreviewing',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1)
self.wfc2 = SqlWorkflowContent.objects.create(
workflow=self.wf2,
sql_content='some_sql',
execute_result=json.dumps([{
'id': 1,
'sql': 'some_content'
}]))
self.resource_group1 = ResourceGroup(group_name='some_group')
self.resource_group1.save()
def tearDown(self):
SqlWorkflowContent.objects.all().delete()
SqlWorkflow.objects.all().delete()
self.master1.delete()
self.u1.delete()
self.superuser1.delete()
self.resource_group1.delete()
def testWorkflowStatus(self):
c = Client(header={})
c.force_login(self.u1)
r = c.post('/getWorkflowStatus/', {'workflow_id': self.wf1.id})
r_json = r.json()
self.assertEqual(r_json['status'], 'workflow_finish')
@patch('sql.utils.workflow_audit.Audit.logs')
@patch('sql.utils.workflow_audit.Audit.detail_by_workflow_id')
@patch('sql.utils.workflow_audit.Audit.review_info')
@patch('sql.utils.workflow_audit.Audit.can_review')
def testWorkflowDetailView(self, _can_review, _review_info, _detail_by_id,
_logs):
_review_info.return_value = ('some_auth_group', 'current_auth_group')
_can_review.return_value = False
_detail_by_id.return_value.audit_id = 123
_logs.return_value.latest('id').operation_info = ''
c = Client()
c.force_login(self.u1)
r = c.get('/detail/{}/'.format(self.wf1.id))
expected_status_display = r"""id="workflow_detail_disaply">已正常结束"""
self.assertContains(r, expected_status_display)
exepcted_status = r"""id="workflow_detail_status">workflow_finish"""
self.assertContains(r, exepcted_status)
def testWorkflowListView(self):
c = Client()
c.force_login(self.superuser1)
r = c.post('/sqlworkflow_list/', {
'limit': 10,
'offset': 0,
'navStatus': 'all'
})
r_json = r.json()
self.assertEqual(r_json['total'], 2)
# 列表按创建时间倒序排列, 第二个是wf1 , 是已正常结束
self.assertEqual(r_json['rows'][1]['status'], 'workflow_finish')
# u1拿到u1的
c.force_login(self.u1)
r = c.post('/sqlworkflow_list/', {
'limit': 10,
'offset': 0,
'navStatus': 'all'
})
r_json = r.json()
self.assertEqual(r_json['total'], 1)
self.assertEqual(r_json['rows'][0]['id'], self.wf1.id)
# u3拿到None
c.force_login(self.u3)
r = c.post('/sqlworkflow_list/', {
'limit': 10,
'offset': 0,
'navStatus': 'all'
})
r_json = r.json()
self.assertEqual(r_json['total'], 0)
@patch('sql.utils.workflow_audit.Audit.detail_by_workflow_id')
@patch('sql.utils.workflow_audit.Audit.audit')
@patch('sql.utils.workflow_audit.Audit.can_review')
def testWorkflowPassedView(self, _can_review, _audit, _detail_by_id):
c = Client()
c.force_login(self.superuser1)
r = c.post('/passed/')
self.assertContains(r, 'workflow_id参数为空.')
_can_review.return_value = False
r = c.post('/passed/', {'workflow_id': self.wf1.id})
self.assertContains(r, '你无权操作当前工单！')
_can_review.return_value = True
_detail_by_id.return_value.audit_id = 123
_audit.return_value = {
"data": {
"workflow_status": 1 # TODO 改为audit_success
}
}
r = c.post('/passed/',
data={
'workflow_id': self.wf1.id,
'audit_remark': 'some_audit'
},
follow=False)
self.assertRedirects(r,
'/detail/{}/'.format(self.wf1.id),
fetch_redirect_response=False)
self.wf1.refresh_from_db()
self.assertEqual(self.wf1.status, 'workflow_review_pass')
@patch('sql.sql_workflow.Audit.add_log')
@patch('sql.sql_workflow.Audit.detail_by_workflow_id')
@patch('sql.sql_workflow.Audit.audit')
# patch view里的can_cancel 而不是原始位置的can_cancel ,因为在调用时, 已经 import 了真的 can_cancel ,会导致mock失效
# 在import 静态函数时需要注意这一点, 动态对象因为每次都会重新生成,也可以 mock 原函数/方法/对象
# 参见 : https://docs.python.org/3/library/unittest.mock.html#where-to-patch
@patch('sql.sql_workflow.can_cancel')
def testWorkflowCancelView(self, _can_cancel, _audit, _detail_by_id,
_add_log):
c = Client()
c.force_login(self.u2)
r = c.post('/cancel/')
self.assertContains(r, 'workflow_id参数为空.')
r = c.post('/cancel/', data={'workflow_id': self.wf2.id})
self.assertContains(r, '终止原因不能为空')
_can_cancel.return_value = False
r = c.post('/cancel/',
data={
'workflow_id': self.wf2.id,
'cancel_remark': 'some_reason'
})
self.assertContains(r, '你无权操作当前工单！')
_can_cancel.return_value = True
_detail_by_id = 123
r = c.post('/cancel/',
data={
'workflow_id': self.wf2.id,
'cancel_remark': 'some_reason'
})
self.wf2.refresh_from_db()
self.assertEqual('workflow_abort', self.wf2.status)
@patch('sql.sql_workflow.async_task')
@patch('sql.sql_workflow.Audit')
@patch('sql.sql_workflow.get_engine')
@patch('sql.sql_workflow.user_instances')
def test_workflow_auto_review_view(self, mock_user_instances,
mock_get_engine, mock_audit,
mock_async_task):
c = Client()
c.force_login(self.superuser1)
request_data = {
'sql_content': "update some_db set some_key=\'some value\';",
'workflow_name': 'some_title',
'group_name': self.resource_group1.group_name,
'group_id': self.resource_group1.group_id,
'instance_name': self.master1.instance_name,
'db_name': 'some_db',
'is_backup': '是',
'notify_users': ''
}
mock_user_instances.return_value.get.return_value = None
mock_get_engine.return_value.execute_check.return_value.rows = []
mock_get_engine.return_value.execute_check.return_value.json.return_value = json.dumps(
[{
"id": 1,
"stage": "CHECKED",
"errlevel": 0,
"stagestatus": "Audit completed",
"errormessage": "None",
"sql": "use thirdservice_db",
"affected_rows": 0,
"sequence": "'0_0_0'",
"backup_dbname": "None",
"execute_time": "0",
"sqlsha1": "",
"actual_affected_rows": None
}])
mock_audit.settings.return_value = 'some_group,another_group'
mock_audit.add.return_value = None
mock_async_task.return_value = None
r = c.post('/autoreview/', data=request_data, follow=False)
self.assertIn('detail', r.url)
workflow_id = int(re.search(r'\/detail\/(\d+)\/', r.url).groups()[0])
self.assertEqual(request_data['workflow_name'],
SqlWorkflow.objects.get(id=workflow_id).workflow_name)
def setUp(self):
self.now = datetime.now()
can_view_permission = Permission.objects.get(
codename='menu_sqlworkflow')
self.u1 = User(username='******', display='用户1')
self.u1.save()
self.u1.user_permissions.add(can_view_permission)
self.u2 = User(username='******', display='用户2')
self.u2.save()
self.u2.user_permissions.add(can_view_permission)
self.u3 = User(username='******', display='用户3')
self.u3.save()
self.u3.user_permissions.add(can_view_permission)
self.superuser1 = User(username='******', is_superuser=True)
self.superuser1.save()
self.master1 = Instance(instance_name='test_master_instance',
type='master',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.master1.save()
self.wf1 = SqlWorkflow.objects.create(
workflow_name='some_name',
group_id=1,
group_name='g1',
engineer=self.u1.username,
engineer_display=self.u1.display,
audit_auth_groups='some_group',
create_time=self.now - timedelta(days=1),
status='workflow_finish',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(
workflow=self.wf1,
sql_content='some_sql',
execute_result=json.dumps([{
'id': 1,
'sql': 'some_content'
}]))
self.wf2 = SqlWorkflow.objects.create(workflow_name='some_name2',
group_id=1,
group_name='g1',
engineer=self.u2.username,
engineer_display=self.u2.display,
audit_auth_groups='some_group',
create_time=self.now -
timedelta(days=1),
status='workflow_manreviewing',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1)
self.wfc2 = SqlWorkflowContent.objects.create(
workflow=self.wf2,
sql_content='some_sql',
execute_result=json.dumps([{
'id': 1,
'sql': 'some_content'
}]))
self.resource_group1 = ResourceGroup(group_name='some_group')
self.resource_group1.save()
class TestMysql(TestCase):
def setUp(self):
self.ins1 = Instance(instance_name='some_ins',
type='slave',
db_type='mysql',
host='some_host',
port=1366,
user='******',
password='******')
self.ins1.save()
self.sys_config = SysConfig()
self.wf = SqlWorkflow.objects.create(workflow_name='some_name',
group_id=1,
group_name='g1',
engineer_display='',
audit_auth_groups='some_group',
create_time=datetime.now() -
timedelta(days=1),
status='workflow_finish',
is_backup='是',
instance=self.ins1,
db_name='some_db',
syntax_type=1)
SqlWorkflowContent.objects.create(workflow=self.wf)
def tearDown(self):
self.ins1.delete()
self.sys_config.replace(json.dumps({}))
SqlWorkflow.objects.all().delete()
SqlWorkflowContent.objects.all().delete()
@patch('MySQLdb.connect')
def testGetConnection(self, connect):
new_engine = MysqlEngine(instance=self.ins1)
new_engine.get_connection()
connect.assert_called_once()
@patch('MySQLdb.connect')
def testQuery(self, connect):
cur = Mock()
connect.return_value.cursor = cur
cur.return_value.execute = Mock()
cur.return_value.fetchmany.return_value = (('v1', 'v2'), )
cur.return_value.description = (('k1', 'some_other_des'),
('k2', 'some_other_des'))
new_engine = MysqlEngine(instance=self.ins1)
query_result = new_engine.query(sql='some_str', limit_num=100)
cur.return_value.execute.assert_called()
cur.return_value.fetchmany.assert_called_once_with(size=100)
connect.return_value.close.assert_called_once()
self.assertIsInstance(query_result, ResultSet)
@patch.object(MysqlEngine, 'query')
def testAllDb(self, mock_query):
db_result = ResultSet()
db_result.rows = [('db_1', ), ('db_2', )]
mock_query.return_value = db_result
new_engine = MysqlEngine(instance=self.ins1)
dbs = new_engine.get_all_databases()
self.assertEqual(dbs, ['db_1', 'db_2'])
@patch.object(MysqlEngine, 'query')
def testAllTables(self, mock_query):
table_result = ResultSet()
table_result.rows = [('tb_1', 'some_des'), ('tb_2', 'some_des')]
mock_query.return_value = table_result
new_engine = MysqlEngine(instance=self.ins1)
tables = new_engine.get_all_tables('some_db')
mock_query.assert_called_once_with(db_name='some_db', sql=ANY)
self.assertEqual(tables, ['tb_1', 'tb_2'])
@patch.object(MysqlEngine, 'query')
def testAllColumns(self, mock_query):
db_result = ResultSet()
db_result.rows = [('col_1', 'type'), ('col_2', 'type2')]
mock_query.return_value = db_result
new_engine = MysqlEngine(instance=self.ins1)
dbs = new_engine.get_all_columns_by_tb('some_db', 'some_tb')
self.assertEqual(dbs, ['col_1', 'col_2'])
@patch.object(MysqlEngine, 'query')
def testDescribe(self, mock_query):
new_engine = MysqlEngine(instance=self.ins1)
new_engine.describe_table('some_db', 'some_db')
mock_query.assert_called_once()
def testQueryCheck(self):
new_engine = MysqlEngine(instance=self.ins1)
sql_without_limit = '-- 测试\n select user from usertable'
check_result = new_engine.query_check(db_name='some_db',
sql=sql_without_limit)
self.assertEqual(check_result['filtered_sql'],
'select user from usertable')
def test_query_check_wrong_sql(self):
new_engine = MysqlEngine(instance=self.ins1)
wrong_sql = '-- 测试'
check_result = new_engine.query_check(db_name='some_db', sql=wrong_sql)
self.assertDictEqual(
check_result, {
'msg': '不支持的查询语法类型!',
'bad_query': True,
'filtered_sql': '-- 测试',
'has_star': True
})
def test_query_check_update_sql(self):
new_engine = MysqlEngine(instance=self.ins1)
update_sql = 'update user set id=0'
check_result = new_engine.query_check(db_name='some_db',
sql=update_sql)
self.assertDictEqual(
check_result, {
'msg': '不支持的查询语法类型!',
'bad_query': True,
'filtered_sql': 'update user set id=0',
'has_star': False
})
def test_filter_sql_with_delimiter(self):
new_engine = MysqlEngine(instance=self.ins1)
sql_without_limit = 'select user from usertable;'
check_result = new_engine.filter_sql(sql=sql_without_limit,
limit_num=100)
self.assertEqual(check_result, 'select user from usertable limit 100;')
def test_filter_sql_without_delimiter(self):
new_engine = MysqlEngine(instance=self.ins1)
sql_without_limit = 'select user from usertable'
check_result = new_engine.filter_sql(sql=sql_without_limit,
limit_num=100)
self.assertEqual(check_result, 'select user from usertable limit 100;')
def test_filter_sql_with_limit(self):
new_engine = MysqlEngine(instance=self.ins1)
sql_without_limit = 'select user from usertable limit 10'
check_result = new_engine.filter_sql(sql=sql_without_limit,
limit_num=1)
self.assertEqual(check_result, 'select user from usertable limit 10;')
@patch('sql.engines.mysql.data_masking', return_value=ResultSet())
def test_query_masking(self, _data_masking):
query_result = ResultSet()
new_engine = MysqlEngine(instance=self.ins1)
masking_result = new_engine.query_masking(db_name='archery',
sql='select 1',
resultset=query_result)
self.assertIsInstance(masking_result, ResultSet)
@patch('sql.engines.mysql.data_masking', return_value=ResultSet())
def test_query_masking_not_select(self, _data_masking):
query_result = ResultSet()
new_engine = MysqlEngine(instance=self.ins1)
masking_result = new_engine.query_masking(db_name='archery',
sql='explain select 1',
resultset=query_result)
self.assertEqual(masking_result, query_result)
def test_execute_check_select_sql(self):
sql = 'select * from user'
row = ReviewResult(id=1,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sql)
new_engine = MysqlEngine(instance=self.ins1)
check_result = new_engine.execute_check(db_name='archery', sql=sql)
self.assertIsInstance(check_result, ReviewSet)
self.assertEqual(check_result.rows[0].__dict__, row.__dict__)
def test_execute_check_critical_sql(self):
self.sys_config.set('critical_ddl_regex', '^|update')
self.sys_config.get_all_config()
sql = 'update user set id=1'
row = ReviewResult(id=1,
errlevel=2,
stagestatus='驳回高危SQL',
errormessage='禁止提交匹配' + '^|update' + '条件的语句！',
sql=sql)
new_engine = MysqlEngine(instance=self.ins1)
check_result = new_engine.execute_check(db_name='archery', sql=sql)
self.assertIsInstance(check_result, ReviewSet)
self.assertEqual(check_result.rows[0].__dict__, row.__dict__)
@patch('sql.engines.mysql.InceptionEngine')
def test_execute_check_normal_sql(self, _inception_engine):
sql = 'update user set id=1'
row = ReviewResult(
id=1,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sql,
affected_rows=0,
execute_time=0,
)
_inception_engine.return_value.execute_check.return_value = ReviewSet(
full_sql=sql, rows=[row])
new_engine = MysqlEngine(instance=self.ins1)
check_result = new_engine.execute_check(db_name='archery', sql=sql)
self.assertIsInstance(check_result, ReviewSet)
self.assertEqual(check_result.rows[0].__dict__, row.__dict__)
@patch('sql.engines.mysql.InceptionEngine')
def test_execute_check_normal_sql_with_Exception(self, _inception_engine):
sql = 'update user set id=1'
_inception_engine.return_value.execute_check.side_effect = RuntimeError(
)
new_engine = MysqlEngine(instance=self.ins1)
with self.assertRaises(RuntimeError):
new_engine.execute_check(db_name=0, sql=sql)
@patch('sql.engines.mysql.InceptionEngine')
def test_execute_workflow(self, _inception_engine):
sql = 'update user set id=1'
_inception_engine.return_value.execute.return_value = ReviewSet(
full_sql=sql)
new_engine = MysqlEngine(instance=self.ins1)
execute_result = new_engine.execute_workflow(self.wf)
self.assertIsInstance(execute_result, ReviewSet)
@patch('MySQLdb.connect.cursor.execute')
@patch('MySQLdb.connect.cursor')
@patch('MySQLdb.connect')
def test_execute(self, _connect, _cursor, _execute):
new_engine = MysqlEngine(instance=self.ins1)
execute_result = new_engine.execute(self.wf)
self.assertIsInstance(execute_result, ResultSet)
@patch.object(MysqlEngine, 'query')
def test_server_version(self, _query):
_query.return_value.rows = (('5.7.20', ), )
new_engine = MysqlEngine(instance=self.ins1)
server_version = new_engine.server_version
self.assertTupleEqual(server_version, (5, 7, 20))
@patch.object(MysqlEngine, 'query')
def test_get_variables_not_filter(self, _query):
new_engine = MysqlEngine(instance=self.ins1)
new_engine.get_variables()
_query.assert_called_once()
@patch.object(MysqlEngine, 'query')
def test_get_variables_filter(self, _query):
new_engine = MysqlEngine(instance=self.ins1)
new_engine.get_variables(variables=['binlog_format'])
_query.assert_called()
@patch.object(MysqlEngine, 'query')
def test_set_variable(self, _query):
new_engine = MysqlEngine(instance=self.ins1)
new_engine.set_variable('binlog_format', 'ROW')
_query.assert_called_once()
class AsyncTest(TestCase):
def setUp(self):
self.now = datetime.now()
self.u1 = User(username='******', display='用户1')
self.u1.save()
self.master1 = Instance(instance_name='test_master_instance',
type='master',
db_type='mysql',
host='testhost',
port=3306,
user='******',
password='******')
self.master1.save()
self.wf1 = SqlWorkflow.objects.create(
workflow_name='some_name2',
group_id=1,
group_name='g1',
engineer=self.u1.username,
engineer_display=self.u1.display,
audit_auth_groups='some_group',
create_time=self.now - timedelta(days=1),
status='workflow_executing',
is_backup='是',
instance=self.master1,
db_name='some_db',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(workflow=self.wf1,
sql_content='some_sql',
execute_result='')
# 初始化工单执行返回对象
self.task_result = MagicMock()
self.task_result.args = [self.wf1.id]
self.task_result.success = True
self.task_result.stopped = self.now
self.task_result.result.json.return_value = json.dumps([{
'id':
1,
'sql':
'some_content'
}])
self.task_result.result.warning = ''
self.task_result.result.error = ''
def tearDown(self):
self.wf1.delete()
self.u1.delete()
self.task_result = None
self.master1.delete()
@patch('sql.utils.execute_sql.notify_for_execute')
@patch('sql.utils.execute_sql.Audit')
def test_call_back(self, mock_audit, mock_notify):
mock_audit.detail_by_workflow_id.return_value.audit_id = 123
mock_audit.add_log.return_value = 'any thing'
execute_callback(self.task_result)
mock_audit.detail_by_workflow_id.assert_called_with(
workflow_id=self.wf1.id, workflow_type=ANY)
mock_audit.add_log.assert_called_with(
audit_id=123,
operation_type=ANY,
operation_type_desc=ANY,
operation_info="执行结果：已正常结束",
operator=ANY,
operator_display=ANY,
)
mock_notify.assert_called_once()
class TestSQLReview(TestCase):
"""
测试sql review内的方法
"""
def setUp(self):
self.superuser = User.objects.create(username='******',
is_superuser=True)
self.user = User.objects.create(username='******')
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.group = ResourceGroup.objects.create(group_id=1,
group_name='group_name')
self.wf1 = SqlWorkflow.objects.create(
workflow_name='workflow_name',
group_id=self.group.group_id,
group_name=self.group.group_name,
engineer=self.superuser.username,
engineer_display=self.superuser.display,
audit_auth_groups='audit_auth_groups',
create_time=datetime.datetime.now(),
status='workflow_review_pass',
is_backup=True,
instance=self.master,
db_name='db_name',
syntax_type=1,
)
self.wfc1 = SqlWorkflowContent.objects.create(workflow=self.wf1,
sql_content='some_sql',
execute_result='')
def tearDown(self):
self.wf1.delete()
self.group.delete()
self.superuser.delete()
self.user.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
@patch('sql.engines.get_engine')
def test_auto_review_hit_review_regex(
self,
_get_engine,
):
"""
测试自动审批通过的判定条件，命中判断正则
:return:
"""
# 开启自动审批设置
self.sys_config.set('auto_review', 'true')
self.sys_config.set('auto_review_regex', '^drop') # drop语句需要审批
self.sys_config.set('auto_review_max_update_rows',
'50') # update影响行数大于50需要审批
self.sys_config.get_all_config()
# 修改工单为drop
self.wfc1.sql_content = "drop table users;"
self.wfc1.save(update_fields=('sql_content', ))
r = is_auto_review(self.wfc1.workflow_id)
self.assertFalse(r)
@patch('sql.engines.mysql.MysqlEngine.execute_check')
@patch('sql.engines.get_engine')
def test_auto_review_gt_max_update_rows(self, _get_engine, _execute_check):
"""
测试自动审批通过的判定条件，影响行数大于auto_review_max_update_rows
:return:
"""
# 开启自动审批设置
self.sys_config.set('auto_review', 'true')
self.sys_config.set('auto_review_regex', '^drop') # drop语句需要审批
self.sys_config.set('auto_review_max_update_rows',
'2') # update影响行数大于2需要审批
self.sys_config.get_all_config()
# 修改工单为update
self.wfc1.sql_content = "update table users set email='';"
self.wfc1.save(update_fields=('sql_content', ))
# mock返回值，update影响行数=3
_execute_check.return_value.to_dict.return_value = [{
"id":
1,
"stage":
"CHECKED",
"errlevel":
0,
"stagestatus":
"Audit completed",
"errormessage":
"None",
"sql":
"use archer_test",
"affected_rows":
0,
"sequence":
"'0_0_0'",
"backup_dbname":
"None",
"execute_time":
"0",
"sqlsha1":
"",
"actual_affected_rows":
'null'
}, {
"id":
2,
"stage":
"CHECKED",
"errlevel":
0,
"stagestatus":
"Audit completed",
"errormessage":
"None",
"sql":
"update table users set email=''",
"affected_rows":
3,
"sequence":
"'0_0_1'",
"backup_dbname":
"mysql_3306_archer_test",
"execute_time":
"0",
"sqlsha1":
"",
"actual_affected_rows":
'null'
}]
r = is_auto_review(self.wfc1.workflow_id)
self.assertFalse(r)
@patch('sql.engines.mysql.MysqlEngine.execute_check')
@patch('sql.engines.get_engine')
def test_auto_review_true(self, _get_engine, _execute_check):
"""
测试自动审批通过的判定条件，
:return:
"""
# 开启自动审批设置
self.sys_config.set('auto_review', 'true')
self.sys_config.set('auto_review_regex', '^drop') # drop语句需要审批
self.sys_config.set('auto_review_max_update_rows',
'2') # update影响行数大于2需要审批
self.sys_config.get_all_config()
# 修改工单为update
self.wfc1.sql_content = "update table users set email='';"
self.wfc1.save(update_fields=('sql_content', ))
# mock返回值，update影响行数=3
_execute_check.return_value.to_dict.return_value = [{
"id":
1,
"stage":
"CHECKED",
"errlevel":
0,
"stagestatus":
"Audit completed",
"errormessage":
"None",
"sql":
"use archer_test",
"affected_rows":
0,
"sequence":
"'0_0_0'",
"backup_dbname":
"None",
"execute_time":
"0",
"sqlsha1":
"",
"actual_affected_rows":
'null'
}, {
"id":
2,
"stage":
"CHECKED",
"errlevel":
0,
"stagestatus":
"Audit completed",
"errormessage":
"None",
"sql":
"update table users set email=''",
"affected_rows":
1,
"sequence":
"'0_0_1'",
"backup_dbname":
"mysql_3306_archer_test",
"execute_time":
"0",
"sqlsha1":
"",
"actual_affected_rows":
'null'
}]
r = is_auto_review(self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_for_resource_group(self, ):
"""
测试是否能执行的判定条件,登录用户有资源组粒度执行权限，并且为组内用户
:return:
"""
# 修改工单为workflow_review_pass，登录用户有资源组粒度执行权限，并且为组内用户
self.wf1.status = 'workflow_review_pass'
self.wf1.save(update_fields=('status', ))
sql_execute_for_resource_group = Permission.objects.get(
codename='sql_execute_for_resource_group')
self.user.user_permissions.add(sql_execute_for_resource_group)
ResourceGroupRelations.objects.create(object_type=0,
object_id=self.user.id,
group_id=self.group.group_id)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_true(self, ):
"""
测试是否能执行的判定条件,当前登录用户为提交人，并且有执行权限,工单状态为审核通过
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_workflow_timing_task(self, ):
"""
测试是否能执行的判定条件,当前登录用户为提交人，并且有执行权限,工单状态为定时执行
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_timingtask'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_execute_false_no_permission(self, ):
"""
当前登录用户为提交人，但是没有执行权限
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_timingtask'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def test_can_execute_false_not_in_group(self, ):
"""
当前登录用户为提交人，有资源组粒度执行权限，但是不是组内用户
:return:
"""
# 修改工单为workflow_review_pass，有资源组粒度执行权限，但是不是组内用户
self.wf1.status = 'workflow_review_pass'
self.wf1.save(update_fields=('status', ))
sql_execute_for_resource_group = Permission.objects.get(
codename='sql_execute_for_resource_group')
self.user.user_permissions.add(sql_execute_for_resource_group)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def test_can_execute_false_wrong_status(self, ):
"""
当前登录用户为提交人，前登录用户为提交人，并且有执行权限,但是工单状态为待审核
:return:
"""
# 修改工单为workflow_manreviewing，当前登录用户为提交人，并且有执行权限, 但是工单状态为待审核
self.wf1.status = 'workflow_manreviewing'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_execute(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
def test_can_timingtask_true(self, ):
"""
测试是否能定时执行的判定条件,当前登录用户为提交人，并且有执行权限,工单状态为审核通过
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_timingtask(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
def test_can_timingtask_false(self, ):
"""
测试是否能定时执行的判定条件,当前登录有执行权限,工单状态为审核通过，但用户不是提交人
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人，并且有执行权限
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.superuser.username
self.wf1.save(update_fields=('status', 'engineer'))
sql_execute = Permission.objects.get(codename='sql_execute')
self.user.user_permissions.add(sql_execute)
r = can_timingtask(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
@patch('sql.utils.workflow_audit.Audit.can_review')
def test_can_cancel_true_for_apply_user(self, _can_review):
"""
测试是否能取消，审核中的工单，提交人可终止
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人
self.wf1.status = 'workflow_manreviewing'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
_can_review.return_value = False
r = can_cancel(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
@patch('sql.utils.workflow_audit.Audit.can_review')
def test_can_cancel_true_for_audit_user(self, _can_review):
"""
测试是否能取消，审核中的工单，审核人可终止
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人
self.wf1.status = 'workflow_manreviewing'
self.wf1.engineer = self.superuser.username
self.wf1.save(update_fields=('status', 'engineer'))
_can_review.return_value = True
r = can_cancel(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
@patch('sql.utils.sql_review.can_execute')
def test_can_cancel_true_for_execute_user(self, _can_execute):
"""
测试是否能取消，审核通过但未执行的工单，有执行权限的用户终止
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
_can_execute.return_value = True
r = can_cancel(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertTrue(r)
@patch('sql.utils.sql_review.can_execute')
def test_can_cancel_false(self, _can_execute):
"""
测试是否能取消，审核通过但未执行的工单，无执行权限的用户无法终止
:return:
"""
# 修改工单为workflow_review_pass，当前登录用户为提交人
self.wf1.status = 'workflow_review_pass'
self.wf1.engineer = self.user.username
self.wf1.save(update_fields=('status', 'engineer'))
_can_execute.return_value = False
r = can_cancel(user=self.user, workflow_id=self.wfc1.workflow_id)
self.assertFalse(r)
class TestSQLAnalyze(TestCase):
"""
测试SQL分析
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
# 使用 travis.ci 时实例和测试service保持一致
self.master = Instance(
instance_name='test_instance',
type='master',
db_type='mysql',
host=settings.DATABASES['default']['HOST'],
port=settings.DATABASES['default']['PORT'],
user=settings.DATABASES['default']['USER'],
password=settings.DATABASES['default']['PASSWORD'])
self.master.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.master.delete()
self.sys_config.replace(json.dumps({}))
def test_generate_text_None(self):
"""
测试解析SQL，text为空
:return:
"""
r = self.client.post(path='/sql_analyze/generate/', data={})
self.assertEqual(json.loads(r.content), {'rows': [], 'total': 0})
def test_generate_text_not_None(self):
"""
测试解析SQL，text不为空
:return:
"""
text = "select * from sql_user;select * from sql_workflow;"
r = self.client.post(path='/sql_analyze/generate/',
data={"text": text})
self.assertEqual(
json.loads(r.content), {
"total":
2,
"rows": [{
"sql_id": 1,
"sql": "select * from sql_user;"
}, {
"sql_id": 2,
"sql": "select * from sql_workflow;"
}]
})
def test_analyze_text_None(self):
"""
测试分析SQL，text为空
:return:
"""
r = self.client.post(path='/sql_analyze/analyze/', data={})
self.assertEqual(json.loads(r.content), {'rows': [], 'total': 0})
def test_analyze_text_not_None(self):
"""
测试分析SQL，text不为空
:return:
"""
text = "select * from sql_user;select * from sql_workflow;"
instance_name = self.master.instance_name
db_name = settings.DATABASES['default']['TEST']['NAME']
r = self.client.post(path='/sql_analyze/analyze/',
data={
"text": text,
"instance_name": instance_name,
"db_name": db_name
})
self.assertListEqual(list(json.loads(r.content)['rows'][0].keys()),
['sql_id', 'sql', 'report'])
