def test_writing_excel(self): """ This is a pretty crap test. It at least exercises the code. If anyone wants to go through the brain damage of actually building an 'expected' xlsx output and comparing it see https://github.com/jmcnamara/XlsxWriter/blob/master/xlsxwriter/test/helperfunctions.py for reference by all means submit a pull request! """ res = QueryResult( SimpleQueryFactory( sql='select 1 as "a", 2', title='\\/*[]:?this title is longer than 32 characters', ).sql, connections[settings.EXPLORER_DEFAULT_CONNECTION], 1, 1000, 10000, ) res.execute_query() res.process() d = datetime.now() d = timezone.make_aware(d, timezone.get_current_timezone()) res._data = [[1, None], [u"Jenét", d]] res = ( ExcelExporter(user=None, query=SimpleQueryFactory()) ._get_output(res) .getvalue() ) expected = b('PK') assert res[:2] == expected
def test_submit_query_for_execution_with_duplicated_column_names( self, mock_connection_settings, mock_schema_suffix ): mock_schema_suffix.return_value = "12b9377c" # See utils.TYPE_CODES_REVERSED for data type codes returned in cursor description self.mock_cursor.description = [("bar", 23), ("bar", 25)] query = SimpleQueryFactory(sql="select * from foo", connection="conn", id=1) submit_query_for_execution( query.final_sql(), query.connection, query.id, self.user.id, 1, 100, 10000 ) query_log_id = QueryLog.objects.first().id expected_calls = [ call("SET statement_timeout = 10000"), call("SELECT * FROM (select * from foo) sq LIMIT 0"), call( f"CREATE TABLE _user_12b9377c._data_explorer_tmp_query_{query_log_id}" ' ("col_1_bar" integer, "col_2_bar" text)' ), call( f"INSERT INTO _user_12b9377c._data_explorer_tmp_query_{query_log_id}" " SELECT * FROM (select * from foo) sq LIMIT 100" ), call("SELECT COUNT(*) FROM (select * from foo) sq"), ] self.mock_cursor.execute.assert_has_calls(expected_calls)
def test_submit_query_for_execution_with_pagination( self, mock_connection_settings, mock_schema_suffix): mock_schema_suffix.return_value = '12b9377c' # See utils.TYPE_CODES_REVERSED for data type codes returned in cursor description self.mock_cursor.description = [('foo', 23), ('bar', 25)] query = SimpleQueryFactory(sql='select * from foo', connection='conn', id=1) submit_query_for_execution(query.final_sql(), query.connection, query.id, self.user.id, 2, 100, 10000) query_log_id = QueryLog.objects.first().id expected_calls = [ call('SET statement_timeout = 10000'), call('SELECT * FROM (select * from foo) sq LIMIT 0'), call( f'CREATE TABLE _user_12b9377c._data_explorer_tmp_query_{query_log_id} ("foo" integer, "bar" text)' ), call( f'INSERT INTO _user_12b9377c._data_explorer_tmp_query_{query_log_id}' ' SELECT * FROM (select * from foo) sq LIMIT 100 OFFSET 100'), call('SELECT COUNT(*) FROM (select * from foo) sq'), ] self.mock_cursor.execute.assert_has_calls(expected_calls)
def test_writing_dict_fields(self): res = QueryResult( SimpleQueryFactory( sql='select 1 as "a", 2', title='\\/*[]:?this title is longer than 32 characters', ).sql, connections[settings.EXPLORER_DEFAULT_CONNECTION], 1, 1000, 10000, ) res.execute_query() res.process() res._data = [[1, ['foo', 'bar']], [2, {'foo': 'bar'}]] res = ( ExcelExporter(user=None, query=SimpleQueryFactory()) ._get_output(res) .getvalue() ) expected = b('PK') assert res[:2] == expected
def test_log_saves_duration(self): user = UserFactory() q = SimpleQueryFactory() res, _ = q.execute_with_logging(user, None, 10, 10000) log = QueryLog.objects.first() assert log.duration == pytest.approx(res.duration, rel=1e-9)
def test_get_run_count(self): q = SimpleQueryFactory() assert q.get_run_count() == 0 expected = 4 for _ in range(0, expected): QueryLogFactory(query=q) assert q.get_run_count() == expected
def test_query_logs_final_sql(self): q = SimpleQueryFactory(sql="select '$$foo$$';") q.params = {'foo': 'bar'} q.log(None) assert QueryLog.objects.count() == 1 log = QueryLog.objects.first() assert log.sql == "select 'bar';"
def test_query_log(self): assert QueryLog.objects.count() == 0 q = SimpleQueryFactory(connection='Alt') q.log(None) assert QueryLog.objects.count() == 1 log = QueryLog.objects.first() assert log.run_by_user is None assert log.query == q assert log.is_playground is False assert log.connection == q.connection
def test_cannot_open_playground_with_another_users_query( self, staff_client): other_user = UserFactory(email='*****@*****.**') query = SimpleQueryFactory(sql="select 1;", created_by_user=other_user) resp = staff_client.get('%s?query_id=%s' % (reverse("explorer:index"), query.id)) assert resp.status_code == 404
def test_query_saves_to_log(self, staff_user, staff_client): query = SimpleQueryFactory(created_by_user=staff_user) data = model_to_dict(query) data['sql'] = 'select 12345;' data['action'] = 'run' staff_client.post(reverse("explorer:index") + f"?query_id={query.id}", data) assert QueryLog.objects.count() == 1
def test_playground_renders_with_query_sql(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select 1;", created_by_user=staff_user) resp = staff_client.get( '%s?query_id=%s' % (reverse("explorer:index"), query.id) ) assert resp.status_code == 200 assert 'select 1;' in resp.content.decode(resp.charset)
def test_params_in_download(self, staff_user, staff_client): q = SimpleQueryFactory(sql="select '$$foo$$';", created_by_user=staff_user) url = '%s?params=%s' % ( reverse("explorer:download_query", kwargs={'query_id': q.id}), 'foo:1234567890', ) resp = staff_client.get(url) assert "1234567890" in resp.content.decode(resp.charset)
def test_modified_date_gets_updated_after_viewing_query(self, staff_user, staff_client): query = SimpleQueryFactory(created_by_user=staff_user) old = query.last_run_date time.sleep(0.1) staff_client.get(reverse("explorer:query_detail", kwargs={"query_id": query.id})) assert old != Query.objects.get(pk=query.id).last_run_date
def test_new_query_gets_created_by_logged_in_user(self, staff_user, staff_client): query = SimpleQueryFactory.build(created_by_user=staff_user) data = model_to_dict(query) del data["id"] staff_client.post(reverse("explorer:query_create"), {**data, "action": "save"}) query = Query.objects.first() assert query.created_by_user_id == staff_user.id
def test_retrieving_query_works_with_params(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select $$swap$$;", created_by_user=staff_user) resp = staff_client.get( reverse("explorer:index") + f"?query_id={query.id}¶ms=swap:1234567890") assert "1234567890" in resp.content.decode(resp.charset)
def test_bad_query_redirects_to_query_view(self, staff_user, staff_client): query = SimpleQueryFactory(sql='bad', created_by_user=staff_user) url = reverse("explorer:download_query", args=[query.pk]) + '?format=csv' response = staff_client.get(url) assert response.status_code == 302 assert response.url == f'/data-explorer/queries/{query.pk}/'
def test_cannot_view_another_users_query(self, staff_user, staff_client): other_user = UserFactory(email="*****@*****.**") other_query = SimpleQueryFactory(created_by_user=other_user) resp = staff_client.get( reverse("explorer:query_detail", kwargs={"query_id": other_query.id}) ) assert resp.status_code == 404
def test_doesnt_render_results_if_show_is_none(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select 6870+1;", created_by_user=staff_user) resp = staff_client.get( reverse("explorer:query_detail", kwargs={"query_id": query.id}) + "?show=0" ) assert "6871" not in resp.content.decode(resp.charset)
def test_download_csv(self, staff_user, staff_client): query = SimpleQueryFactory(created_by_user=staff_user) url = reverse("explorer:download_query", args=[query.pk]) + '?format=csv' response = staff_client.get(url) assert response.status_code == 200 assert response['content-type'] == 'text/csv'
def test_cant_query_with_unregistered_connection(self): from dataworkspace.apps.explorer.utils import ( # pylint: disable=import-outside-toplevel InvalidExplorerConnectionException, ) user = UserFactory() q = SimpleQueryFactory.create(sql="select '$$foo:bar$$', '$$qux$$';", connection='not_registered') with pytest.raises(InvalidExplorerConnectionException): q.execute(user, None, 10, 10000)
def test_doesnt_render_results_on_page(self, staff_user, staff_client): query = SimpleQueryFactory(sql='select 6870+1;', created_by_user=staff_user) resp = staff_client.post( reverse("explorer:query_detail", kwargs={'query_id': query.id}), {'sql': 'select 6870+2;', 'action': 'save'}, ) assert '6872' not in resp.content.decode(resp.charset)
def test_cannot_post_to_another_users_query(self, staff_client): other_user = UserFactory(email="*****@*****.**") query = SimpleQueryFactory(sql="select 1;", created_by_user=other_user) resp = staff_client.post( reverse("explorer:index") + f"?query_id={query.id}", {"title": "test", "sql": "select 1+3400;", "action": "save"}, ) assert resp.status_code == 404
def test_doesnt_render_results_on_page(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select 6870+1;", created_by_user=staff_user) resp = staff_client.post( reverse("explorer:query_detail", kwargs={"query_id": query.id}), {"sql": "select 6870+2;", "action": "save"}, ) assert "6872" not in resp.content.decode(resp.charset)
def test_valid_query(self, staff_user, staff_client): query = SimpleQueryFactory.build(sql="SELECT 1;", created_by_user=staff_user) data = model_to_dict(query) data["action"] = "save" del data["id"] del data["created_by_user"] staff_client.post(reverse("explorer:query_create"), data) assert Query.objects.all()[0].sql == "SELECT 1;"
def test_posting_query_saves_correctly(self, staff_user, staff_client): expected = "select 2;" query = SimpleQueryFactory(sql="select 1;", created_by_user=staff_user) data = model_to_dict(query) data["sql"] = expected data["action"] = "save" staff_client.post(reverse("explorer:query_detail", kwargs={"query_id": query.id}), data) assert Query.objects.get(pk=query.id).sql == expected
def test_get_params_for_request(self): q = SimpleQueryFactory(params={'a': 1, 'b': 2}) # For some reason the order of the params is non-deterministic, # causing the following to periodically fail: # self.assertEqual(get_params_for_url(q), 'a:1|b:2') # So instead we go for the following, convoluted, asserts: res = get_params_for_url(q) res = res.split('|') expected = ['a:1', 'b:2'] for e in expected: self.assertIn(e, res)
def test_invalid_query_saved(self, staff_user, staff_client): query = SimpleQueryFactory.build(sql='SELECT foo; DELETE FROM foo;', created_by_user=staff_user) data = model_to_dict(query) data['action'] = "save" del data['id'] del data['created_by_user'] staff_client.post(reverse("explorer:query_create"), data) assert Query.objects.all()[0].sql == 'SELECT foo; DELETE FROM foo;'
def test_change_permission_required_to_save_query(self, staff_user, staff_client): query = SimpleQueryFactory(created_by_user=staff_user) expected = query.sql staff_client.get(reverse("explorer:query_detail", kwargs={"query_id": query.id})) staff_client.post( reverse("explorer:query_detail", kwargs={"query_id": query.id}), {"sql": "select 1;"}, ) assert Query.objects.get(pk=query.id).sql == expected
def test_query_with_bad_sql_fails_on_save(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select 1;", created_by_user=staff_user) resp = staff_client.post( reverse("explorer:query_detail", kwargs={"query_id": query.id}), data={"title": query.title, "sql": "error", "action": "save"}, ) assert ( "Enter a SQL statement starting with SELECT, WITH or EXPLAIN" in resp.content.decode(resp.charset) )
def test_renders_back_link(self, staff_user, staff_client): query = SimpleQueryFactory(sql="select 6870+1;", created_by_user=staff_user) play_sql = PlaygroundSQLFactory(sql="select 1+6870;", created_by_user=staff_user) response = staff_client.get( reverse("explorer:query_detail", kwargs={"query_id": query.id}), {"play_id": play_sql.id}, ) assert ( f'<a href="/data-explorer/?query_id={query.id}&play_id={play_sql.id}" class="govuk-back-link">Back</a>' in response.content.decode(response.charset) )