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"}