def test_get_other_data(self): new_config = json.dumps([{ 'key': 'other_config', 'value': 'testvalue' }]) archer_config = SysConfig() archer_config.replace(new_config) self.assertEqual(archer_config.sys_config['other_config'], 'testvalue')
def test_replace_configs(self): archer_config = SysConfig() new_config = json.dumps( [{'key': 'numconfig', 'value': 1}, {'key': 'strconfig', 'value': 'strconfig'}, {'key': 'boolconfig', 'value': 'false'}]) archer_config.replace(new_config) archer_config.get_all_config() expected_config = { 'numconfig': '1', 'strconfig': 'strconfig', 'boolconfig': False } self.assertEqual(archer_config.sys_config, expected_config)
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 test_get_bool_transform(self): bool_config = json.dumps([{'key': 'boolconfig2', 'value': 'false'}]) archer_config = SysConfig() archer_config.replace(bool_config) self.assertEqual(archer_config.sys_config['boolconfig2'], 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 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 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 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'])
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 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 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)