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'])
def tearDown(self):
archer_config = SysConfig()
archer_config.set('mail_smtp_server', '')
archer_config.set('mail_smtp_user', '')
archer_config.set('mail_smtp_password', '')
archer_config.set('mail_smtp_port', '')
archer_config.set('mail_ssl', '')
def test_purge(self):
archer_config = SysConfig()
archer_config.set('some_key', 'some_value')
archer_config.purge()
self.assertEqual({}, archer_config.sys_config)
archer_config2 = SysConfig()
self.assertEqual({}, archer_config2.sys_config)
def setUp(self):
self.username = '******'
self.password = '******'
self.u1 = User(username=self.username, password=self.password, display='用户1')
self.u1.save()
self.resource_group1 = ResourceGroup.objects.create(group_name='some_group')
sys_config = SysConfig()
sys_config.set('default_resource_group', self.resource_group1.group_name)
def tearDown(self):
self.u1.delete()
self.u2.delete()
self.slave1.delete()
self.query_apply_1.delete()
QueryPrivileges.objects.all().delete()
archer_config = SysConfig()
archer_config.set('disable_star', False)
def setUp(self):
"""
创建默认组给注册关联用户, 打开注册
"""
archer_config = SysConfig()
archer_config.set('sign_up_enabled', 'true')
archer_config.get_all_config()
self.client = Client()
Group.objects.create(id=1, name='默认组')
def testSenderInit(self):
sender = MsgSender()
self.assertEqual(sender.MAIL_REVIEW_SMTP_PORT, self.smtp_port)
archer_config = SysConfig()
archer_config.set('mail_smtp_port', '')
sender = MsgSender()
self.assertEqual(sender.MAIL_REVIEW_SMTP_PORT, 465)
archer_config.set('mail_ssl', False)
sender = MsgSender()
self.assertEqual(sender.MAIL_REVIEW_SMTP_PORT, 25)
def testSSLSendMail(self, _quit, sendmail, login, _):
"""SSL 测试"""
some_sub = 'test_subject'
some_body = 'mail_body'
some_to = ['mail_to']
archer_config = SysConfig()
archer_config.set('mail_ssl', True)
sender = MsgSender()
sender.send_email(some_sub, some_body, some_to)
sendmail.assert_called_with(self.smtp_user, some_to, ANY)
_quit.assert_called_once()
def testNoPasswordSendMail(self, _quit, sendmail, login, _):
"""无密码测试"""
some_sub = 'test_subject'
some_body = 'mail_body'
some_to = ['mail_to']
archer_config = SysConfig()
archer_config.set('mail_ssl', '')
archer_config.set('mail_smtp_password', '')
sender2 = MsgSender()
sender2.send_email(some_sub, some_body, some_to)
login.assert_not_called()
def setUp(self):
archer_config = SysConfig()
self.smtp_server = 'test_smtp_server'
self.smtp_user = '******'
self.smtp_password = '******'
self.smtp_port = 1234
self.smtp_ssl = True
archer_config.set('mail_smtp_server', self.smtp_server)
archer_config.set('mail_smtp_user', self.smtp_user)
archer_config.set('mail_smtp_password', self.smtp_password)
archer_config.set('mail_smtp_port', self.smtp_port)
archer_config.set('mail_ssl', self.smtp_ssl)
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)
class TestOracle(TestCase):
"""Oracle 测试"""
def setUp(self):
self.ins = Instance.objects.create(instance_name='some_ins', type='slave', db_type='oracle',
host='some_host', port=3306, user='******', password='******',
sid='some_id')
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.ins,
db_name='some_db',
syntax_type=1
)
SqlWorkflowContent.objects.create(workflow=self.wf)
self.sys_config = SysConfig()
def tearDown(self):
self.ins.delete()
SqlWorkflow.objects.all().delete()
SqlWorkflowContent.objects.all().delete()
@patch('cx_Oracle.makedsn')
@patch('cx_Oracle.connect')
def test_get_connection(self, _connect, _makedsn):
# 填写 sid 测试
new_engine = OracleEngine(self.ins)
new_engine.get_connection()
_connect.assert_called_once()
_makedsn.assert_called_once()
# 填写 service_name 测试
_connect.reset_mock()
_makedsn.reset_mock()
self.ins.service_name = 'some_service'
self.ins.sid = ''
self.ins.save()
new_engine = OracleEngine(self.ins)
new_engine.get_connection()
_connect.assert_called_once()
_makedsn.assert_called_once()
# 都不填写, 检测 ValueError
_connect.reset_mock()
_makedsn.reset_mock()
self.ins.service_name = ''
self.ins.sid = ''
self.ins.save()
new_engine = OracleEngine(self.ins)
with self.assertRaises(ValueError):
new_engine.get_connection()
@patch('cx_Oracle.connect.cursor.execute')
@patch('cx_Oracle.connect.cursor')
@patch('cx_Oracle.connect')
def test_query(self, _conn, _cursor, _execute):
_conn.return_value.cursor.return_value.fetchmany.return_value = [(1,)]
new_engine = OracleEngine(instance=self.ins)
query_result = new_engine.query(db_name=0, sql='select 1', limit_num=100)
self.assertIsInstance(query_result, ResultSet)
self.assertListEqual(query_result.rows, [(1,)])
@patch('cx_Oracle.connect.cursor.execute')
@patch('cx_Oracle.connect.cursor')
@patch('cx_Oracle.connect')
def test_query_not_limit(self, _conn, _cursor, _execute):
_conn.return_value.cursor.return_value.fetchall.return_value = [(1,)]
new_engine = OracleEngine(instance=self.ins)
query_result = new_engine.query(db_name=0, sql='select 1', limit_num=0)
self.assertIsInstance(query_result, ResultSet)
self.assertListEqual(query_result.rows, [(1,)])
@patch('sql.engines.oracle.OracleEngine.query',
return_value=ResultSet(rows=[('AUD_SYS',), ('archery',), ('ANONYMOUS',)]))
def test_get_all_databases(self, _query):
new_engine = OracleEngine(instance=self.ins)
dbs = new_engine.get_all_databases()
self.assertListEqual(dbs.rows, ['archery'])
@patch('sql.engines.oracle.OracleEngine.query',
return_value=ResultSet(rows=[('ANONYMOUS',), ('archery',), ('SYSTEM',)]))
def test_get_all_schemas(self, _query):
new_engine = OracleEngine(instance=self.ins)
schemas = new_engine._get_all_schemas()
self.assertListEqual(schemas.rows, ['archery'])
@patch('sql.engines.oracle.OracleEngine.query', return_value=ResultSet(rows=[('test',), ('test2',)]))
def test_get_all_tables(self, _query):
new_engine = OracleEngine(instance=self.ins)
tables = new_engine.get_all_tables(db_name='archery')
self.assertListEqual(tables.rows, ['test2'])
@patch('sql.engines.oracle.OracleEngine.query',
return_value=ResultSet(rows=[('id',), ('name',)]))
def test_get_all_columns_by_tb(self, _query):
new_engine = OracleEngine(instance=self.ins)
columns = new_engine.get_all_columns_by_tb(db_name='archery', tb_name='test2')
self.assertListEqual(columns.rows, ['id', 'name'])
@patch('sql.engines.oracle.OracleEngine.query',
return_value=ResultSet(rows=[('archery',), ('template1',), ('template0',)]))
def test_describe_table(self, _query):
new_engine = OracleEngine(instance=self.ins)
describe = new_engine.describe_table(db_name='archery', tb_name='text')
self.assertIsInstance(describe, ResultSet)
def test_query_check_disable_sql(self):
sql = "update xxx set a=1 "
new_engine = OracleEngine(instance=self.ins)
check_result = new_engine.query_check(db_name='archery', sql=sql)
self.assertDictEqual(check_result,
{'msg': '仅支持^select语法!', 'bad_query': True, 'filtered_sql': sql.strip(),
'has_star': False})
def test_query_check_star_sql(self):
sql = "select * from xx "
new_engine = OracleEngine(instance=self.ins)
check_result = new_engine.query_check(db_name='archery', sql=sql)
self.assertDictEqual(check_result,
{'msg': '禁止使用 * 关键词\n', 'bad_query': False, 'filtered_sql': sql.strip(), 'has_star': True})
def test_filter_sql_with_delimiter(self):
sql = "select * from xx;"
new_engine = OracleEngine(instance=self.ins)
check_result = new_engine.filter_sql(sql=sql, limit_num=100)
self.assertEqual(check_result, "select * from xx WHERE ROWNUM <= 100")
def test_filter_sql_without_delimiter(self):
sql = "select * from xx"
new_engine = OracleEngine(instance=self.ins)
check_result = new_engine.filter_sql(sql=sql, limit_num=100)
self.assertEqual(check_result, "select * from xx WHERE ROWNUM <= 100")
def test_filter_sql_with_limit(self):
sql = "select * from xx limit 10"
new_engine = OracleEngine(instance=self.ins)
check_result = new_engine.filter_sql(sql=sql, limit_num=1)
self.assertEqual(check_result, "select * from xx limit 10 WHERE ROWNUM <= 1")
def test_query_masking(self):
query_result = ResultSet()
new_engine = OracleEngine(instance=self.ins)
masking_result = new_engine.query_masking(schema_name='', sql='', 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='驳回不支持语句',
errormessage='仅支持DML和DDL语句，查询语句请使用SQL查询功能！',
sql=sql)
new_engine = OracleEngine(instance=self.ins)
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 = OracleEngine(instance=self.ins)
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_normal_sql(self):
self.sys_config.purge()
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, )
new_engine = OracleEngine(instance=self.ins)
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('cx_Oracle.connect.cursor.execute')
@patch('cx_Oracle.connect.cursor')
@patch('cx_Oracle.connect')
def test_execute_workflow_success(self, _conn, _cursor, _execute):
sql = 'update user set id=1'
row = ReviewResult(id=1,
errlevel=0,
stagestatus='Execute Successfully',
errormessage='None',
sql=sql,
affected_rows=0,
execute_time=0,
full_sql=sql)
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.ins,
db_name='some_db',
syntax_type=1
)
SqlWorkflowContent.objects.create(workflow=wf, sql_content=sql)
new_engine = OracleEngine(instance=self.ins)
execute_result = new_engine.execute_workflow(workflow=wf)
self.assertIsInstance(execute_result, ReviewSet)
self.assertEqual(execute_result.rows[0].__dict__.keys(), row.__dict__.keys())
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)
def test_set_other_data(self):
archer_config = SysConfig()
archer_config.set('other_config', 'testvalue3')
self.assertEqual(archer_config.sys_config['other_config'],
'testvalue3')
def test_set_bool_transform(self):
archer_config = SysConfig()
archer_config.set('boolconfig3', False)
self.assertEqual(archer_config.sys_config['boolconfig3'], False)
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 TestPlugin(TestCase):
"""
测试Plugin调用
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.sys_config.replace(json.dumps({}))
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_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 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 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_sql_advisor_generate_args2cmd(self):
args = {
"h": 'mysql',
"P": 3306,
"u": 'root',
"p": '',
"d": 'archery',
"v": 1,
"q": 'select 1;'
}
self.sys_config.set('sqladvisor',
'/opt/archery/src/plugins/SQLAdvisor')
self.sys_config.get_all_config()
sql_advisor = SQLAdvisor()
cmd_args = sql_advisor.generate_args2cmd(args, False)
self.assertIsInstance(cmd_args, list)
cmd_args = sql_advisor.generate_args2cmd(args, True)
self.assertIsInstance(cmd_args, str)
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)
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'])
class TestPlugin(TestCase):
"""
测试Plugin调用
"""
def setUp(self):
self.superuser = User(username='******', is_superuser=True)
self.superuser.save()
self.sys_config = SysConfig()
self.client = Client()
self.client.force_login(self.superuser)
def tearDown(self):
self.superuser.delete()
self.sys_config.replace(json.dumps({}))
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_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 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 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_sql_advisor_generate_args2cmd(self):
args = {
"h": 'mysql',
"P": 3306,
"u": 'root',
"p": '',
"d": 'archery',
"v": 1,
"q": 'select 1;'
}
self.sys_config.set('sqladvisor',
'/opt/archery/src/plugins/SQLAdvisor')
self.sys_config.get_all_config()
sql_advisor = SQLAdvisor()
cmd_args = sql_advisor.generate_args2cmd(args, False)
self.assertIsInstance(cmd_args, list)
cmd_args = sql_advisor.generate_args2cmd(args, True)
self.assertIsInstance(cmd_args, str)
def test_schema_sync_generate_args2cmd(self):
args = {
"sync-auto-inc":
True,
"sync-comments":
True,
"tag":
'tag_v',
"output-directory":
'',
"source":
r"mysql://{user}:{pwd}@{host}:{port}/{database}".format(
user='******',
pwd='123456',
host='127.0.0.1',
port=3306,
database='*'),
"target":
r"mysql://{user}:{pwd}@{host}:{port}/{database}".format(
user='******',
pwd='123456',
host='127.0.0.1',
port=3306,
database='*')
}
self.sys_config.set('schemasync',
'/opt/venv4schemasync/bin/schemasync')
self.sys_config.get_all_config()
schema_sync = SchemaSync()
cmd_args = schema_sync.generate_args2cmd(args, False)
self.assertIsInstance(cmd_args, list)
cmd_args = schema_sync.generate_args2cmd(args, True)
self.assertIsInstance(cmd_args, str)
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)
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()