def test_truncating_querylogs(self):
QueryLogFactory(sql="foo")
QueryLog.objects.filter(sql="foo").update(run_at=datetime.now() - timedelta(days=30))
QueryLogFactory(sql="bar")
QueryLog.objects.filter(sql="bar").update(run_at=datetime.now() - timedelta(days=29))
truncate_querylogs(30)
self.assertEqual(QueryLog.objects.count(), 1)
def test_cleanup_temporary_query_tables(self, mock_connections, mock_databases_data):
mock_cursor = Mock()
mock_connection = Mock()
mock_cursor_ctx_manager = MagicMock()
mock_cursor_ctx_manager.__enter__.return_value = mock_cursor
mock_connection.cursor.return_value = mock_cursor_ctx_manager
mock_connections.__getitem__.return_value = mock_connection
mock_databases_data.__getitem__.return_value = {
"USER": "******",
"NAME": "my_database" "",
}
user = UserFactory()
user.profile.sso_id = (
"00000000-0000-0000-0000-000000000000" # yields a short hexdigest of 12b9377c
)
user.profile.save()
# last run 1 day and 1 hour ago so its materialized view should be deleted
with freeze_time(datetime.utcnow() - timedelta(days=1, hours=1)):
query_log_1 = QueryLogFactory.create(run_by_user=user)
# last run 2 hours ago so its materialized view should be kept
with freeze_time(datetime.utcnow() - timedelta(hours=2)):
QueryLogFactory.create(run_by_user=user)
cleanup_temporary_query_tables()
expected_calls = [
call("GRANT _user_12b9377c TO postgres"),
call(f"DROP TABLE IF EXISTS _user_12b9377c._data_explorer_tmp_query_{query_log_1.id}"),
call("REVOKE _user_12b9377c FROM postgres"),
]
mock_cursor.execute.assert_has_calls(expected_calls)
def test_chart_list_view(staff_user, staff_client, user):
ChartBuilderChartFactory.create(
created_by=staff_user, query_log=QueryLogFactory(run_by_user=staff_user)
)
ChartBuilderChartFactory.create(created_by=user, query_log=QueryLogFactory(run_by_user=user))
response = staff_client.get(reverse("charts:list-charts"))
assert response.status_code == 200
assert response.context_data["charts"].count() == 1
def test_user_can_only_see_their_own_queries_on_log_page(self, staff_user, staff_client):
other_user = UserFactory(email="*****@*****.**")
QueryLogFactory(sql="select 1234", run_by_user=other_user)
QueryLogFactory(sql="select 9876", run_by_user=staff_user)
resp = staff_client.get(reverse("explorer:explorer_logs"))
assert "select 9876" in resp.content.decode(resp.charset)
assert "select 1234" not in resp.content.decode(resp.charset)
def test_query_owned_by_other_user(self, staff_user, staff_client):
QueryLogFactory(sql="select 123", run_by_user=staff_user)
query_log = QueryLogFactory(
sql="select 456", run_by_user=UserFactory(email='*****@*****.**'))
resp = staff_client.get(
reverse('explorer:querylog_results', args=(query_log.id, )))
assert resp.status_code == 200
assert (resp.json()['error'] ==
'Error fetching results. Please try running your query again.')
def test_can_only_load_query_log_run_by_current_user(self, staff_user, staff_client):
user = UserFactory(email="*****@*****.**")
my_querylog = QueryLogFactory(run_by_user=staff_user)
other_querylog = QueryLogFactory(run_by_user=user)
resp = staff_client.get("%s?querylog_id=%s" % (reverse("explorer:index"), my_querylog.id))
assert resp.status_code == 200
assert "FOUR" in resp.content.decode(resp.charset)
resp = staff_client.get(
"%s?querylog_id=%s" % (reverse("explorer:index"), other_querylog.id)
)
assert resp.status_code == 404
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_other_users_query_results(user):
client = Client(raise_request_exception=False, **get_http_sso_data(user))
ql = QueryLogFactory.create(run_by_user=factories.UserFactory.create())
response = client.get(reverse("explorer:running_query", args=(ql.id, )))
assert response.status_code == 403
assert "You can collaborate on Data Explorer queries" in response.content.decode(
response.charset)
def test_chart_delete_view(staff_client, staff_user):
query_log = QueryLogFactory(run_by_user=staff_user)
chart = ChartBuilderChartFactory.create(created_by=staff_user, query_log=query_log)
num_charts = models.ChartBuilderChart.objects.count()
response = staff_client.post(reverse("charts:delete-chart", args=(chart.id,)))
assert response.status_code == 302
assert models.ChartBuilderChart.objects.count() == num_charts - 1
def test_chart_query_results_not_owner(staff_client, user):
chart = ChartBuilderChartFactory.create(
created_by=user,
query_log=QueryLogFactory(run_by_user=user),
chart_config={"some": "config"},
)
response = staff_client.get(reverse("charts:chart-query-results", args=(chart.id,)))
assert response.status_code == 404
def test_create_chart(self, staff_user, staff_client):
query_log = QueryLogFactory(run_by_user=staff_user)
num_query_logs = QueryLog.objects.count()
num_charts = ChartBuilderChart.objects.count()
response = staff_client.get(reverse("explorer:create_chart", args=(query_log.id,)))
assert QueryLog.objects.count() == num_query_logs + 1
assert ChartBuilderChart.objects.count() == num_charts + 1
assert response.status_code == 302
def test_chart_query_status(staff_client, staff_user):
chart = ChartBuilderChartFactory.create(
created_by=staff_user,
query_log=QueryLogFactory(run_by_user=staff_user),
chart_config={"some": "config"},
)
response = staff_client.get(reverse("charts:chart-query-status", args=(chart.id,)))
assert response.status_code == 200
assert response.json() == {"columns": [], "error": None, "state": 0}
def test_writing_csv_unicode_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
('a', 'b'),
[(1, None), (u'Jenét', 1)],
None,
)
res = CSVExporter(request=self.request, querylog=QueryLogFactory()).get_output()
assert res == 'a,b\r\n1,\r\nJenét,1\r\n'
def test_writing_json_datetimes_async(
self, mock_fetch_query_results, mock_connection_settings
):
# Mock the field names returned by SELECT * FROM ({query}) sq LIMIT 0
self.mock_cursor.description = [(None, 23)]
mock_fetch_query_results.return_value = (["a", "b"], [[1, date.today()]], None)
res = JSONExporter(request=self.request, querylog=QueryLogFactory()).get_output()
assert res == json.dumps([{"a": 1, "b": date.today()}], cls=DjangoJSONEncoder)
def test_writing_excel_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
("a", "b"),
[(1, None), ("Jenét", datetime.now())],
None,
)
res = ExcelExporter(request=self.request,
querylog=QueryLogFactory()).get_output()
assert res[:2] == six.b("PK")
def test_writing_csv_custom_delimiter_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
("?column?", "?column?"),
[(1, 2)],
None,
)
res = CSVExporter(request=self.request,
querylog=QueryLogFactory()).get_output(delim="|")
assert res == "?column?|?column?\r\n1|2\r\n"
def test_writing_json_unicode_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
('a', 'b'),
[(1, None), (u'Jenét', '1')],
None,
)
res = JSONExporter(
request=self.request, querylog=QueryLogFactory()
).get_output()
assert res == json.dumps([{'a': 1, 'b': None}, {'a': 'Jenét', 'b': '1'}])
def test_writing_excel_async(self, mock_fetch_query_results, mock_connection_settings):
# Mock the field names returned by SELECT * FROM ({query}) sq LIMIT 0
self.mock_cursor.description = [(None, 23)]
mock_fetch_query_results.return_value = (
["a", "b"],
[[1, None], ["Jenét", datetime.now()]],
None,
)
res = ExcelExporter(request=self.request, querylog=QueryLogFactory()).get_output()
assert res[:2] == six.b("PK")
def test_writing_json_unicode_async(self, mock_fetch_query_results, mock_connection_settings):
# Mock the field names returned by SELECT * FROM ({query}) sq LIMIT 0
self.mock_cursor.description = [(None, 23)]
mock_fetch_query_results.return_value = (
["a", "b"],
[[1, None], ["Jen\xe9t", "1"]],
None,
)
res = JSONExporter(request=self.request, querylog=QueryLogFactory()).get_output()
assert res == json.dumps([{"a": 1, "b": None}, {"a": "Jenét", "b": "1"}])
def test_writing_csv_unicode(self, mock_fetch_query_results, mock_connection_settings):
# Mock the field names returned by SELECT * FROM ({query}) sq LIMIT 0
self.mock_cursor.description = [(None, 23)]
mock_fetch_query_results.return_value = (
["a", "b"],
[[1, None], ["Jen\xe9t", "1"]],
None,
)
res = CSVExporter(request=self.request, querylog=QueryLogFactory()).get_output()
assert res == "a,b\r\n1,\r\nJenét,1\r\n"
def test_writing_json_datetimes_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
('a', 'b'),
[(1, date.today())],
None,
)
res = JSONExporter(
request=self.request, querylog=QueryLogFactory()
).get_output()
assert res == json.dumps([{'a': 1, 'b': date.today()}], cls=DjangoJSONEncoder)
def test_writing_excel_dict_fields_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
('a', 'b'),
[(1, ['foo', 'bar']), (2, {'foo': 'bar'})],
None,
)
res = ExcelExporter(
request=self.request, querylog=QueryLogFactory()
).get_output()
assert res[:2] == six.b('PK')
def test_downloading_from_playground(self, staff_user, staff_client):
querylog = QueryLogFactory.create(sql="select 1, 2", run_by_user=staff_user)
create_temporary_results_table(querylog)
resp = staff_client.get(
reverse("explorer:download_querylog", kwargs=dict(querylog_id=querylog.id))
)
assert "attachment" in resp["Content-Disposition"]
assert "text/csv" in resp["content-type"]
assert 'filename="Playground_-_select_1_2.csv"' in resp["Content-Disposition"]
def test_query_running(self, staff_user, staff_client):
query_log = QueryLogFactory(
sql="select 123", run_by_user=staff_user, state=QueryLogState.RUNNING
)
resp = staff_client.get(reverse("explorer:querylog_results", args=(query_log.id,)))
assert resp.status_code == 200
json_response = resp.json()
assert json_response["query_log_id"] == query_log.id
assert json_response["state"] == query_log.state
assert json_response["error"] is None
assert "Your query is currently being executed by Data Explorer" in json_response["html"]
def test_cant_query_with_unregistered_connection(self):
query = QueryLogFactory(
sql="select '$$foo:bar$$', '$$qux$$';",
connection="not_registered",
)
with pytest.raises(InvalidExplorerConnectionException):
_run_querylog_query(
query.id,
1,
100,
10000,
)
def test_writing_excel_dict_fields_sync(self, mock_connection_settings):
self.mock_fetch_query_results.return_value = (
("a", "b"),
[(1, ["foo", "bar"]), (2, {
"foo": "bar"
})],
None,
)
res = ExcelExporter(request=self.request,
querylog=QueryLogFactory()).get_output()
assert res[:2] == six.b("PK")
def test_cannot_download_someone_elses_querylog(self, staff_user,
staff_client):
other_user = UserFactory(email='*****@*****.**')
my_querylog = QueryLogFactory(sql="select 1,2", run_by_user=other_user)
create_temporary_results_table(my_querylog)
url = (reverse(
"explorer:download_querylog",
kwargs=dict(querylog_id=my_querylog.id),
) + '?format=json')
response = staff_client.get(url)
assert response.status_code == 404
def test_query_running(self, staff_user, staff_client):
query_log = QueryLogFactory(sql="select 123",
run_by_user=staff_user,
state=QueryLog.STATE_RUNNING)
resp = staff_client.get(
reverse('explorer:querylog_results', args=(query_log.id, )))
assert resp.status_code == 200
json_response = resp.json()
assert json_response['query_log_id'] == query_log.id
assert json_response['state'] == query_log.state
assert json_response['error'] is None
assert ('Your query is currently being executed by Data Explorer'
in json_response['html'])
def test_writing_csv_custom_delimiter(
self, mock_fetch_query_results, mock_connection_settings
):
# Mock the field names returned by SELECT * FROM ({query}) sq LIMIT 0
self.mock_cursor.description = [(None, 23)]
mock_fetch_query_results.return_value = (
["?column?", "?column?"],
[[1, 2]],
None,
)
res = CSVExporter(request=self.request, querylog=QueryLogFactory()).get_output(delim="|")
assert res == "?column?|?column?\r\n1|2\r\n"
def test_chart_edit(staff_client, staff_user):
query_log = QueryLogFactory(run_by_user=staff_user)
chart = ChartBuilderChartFactory.create(
created_by=staff_user, query_log=query_log, chart_config={"some": "config"}
)
response = staff_client.post(
reverse("charts:edit-chart", args=(chart.id,)),
json.dumps({"config": {"updated": "config"}}),
content_type="application/json",
)
assert response.status_code == 200
chart.refresh_from_db()
assert chart.chart_config == {"updated": "config"}