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)
)
